Как быстро ранжировать данные в нескольких диапазонах и листах Excel?
Если у вас есть три листа, содержащих некоторые оценки студентов, и вы хотите ранжировать эти оценки по всем трем листам, как показано на скриншоте ниже, как можно быстро справиться с этой задачей, не сравнивая значения один за другим в Excel?
Ранжирование значений в нескольких диапазонах и листах с помощью формул
Ранжирование значений в одном диапазоне на нескольких листах с помощью Kutools для Excel
Ранжирование значений в нескольких диапазонах и листах с помощью формул
Чтобы ранжировать несколько диапазонов на разных листах, сначала нужно присвоить этим диапазонам имя, а затем применить формулу.
1. Активируйте используемую книгу, нажмите Формулы > Диспетчер имен, затем в окне Диспетчер имен нажмите кнопку Создать . См. скриншот:
2. Затем в окне Новое имя укажите имя для диапазона и выберите Книга из выпадающего списка Область затем вставьте эту формулу ={"Class1!$E$2:$E$5","Class2!$B$2:$B$5","Class3!$B$8:$B$11"}в поле Ссылается на текстовое поле. См. скриншот:
В формуле Class1!$E$2:$E$5, Class2!$B$2:$B$5 и Class3!$B$8:$B$11 — это диапазоны, которые вы хотите ранжировать.
3. Нажмите ОК > Закрыть чтобы закрыть диалоговые окна. Теперь перейдите к пустой ячейке рядом со значением, которое вы хотите ранжировать, введите эту формулу =IF(SUMPRODUCT(COUNTIF(INDIRECT(Score),E2))=0,"N/A",SUMPRODUCT(COUNTIF(INDIRECT(Score),">"&E2))+1), и протяните маркер автозаполнения вниз, чтобы применить эту формулу. См. скриншот:
В формуле E2 — это ячейка, которую вы хотите ранжировать, Score — это имя диапазона, указанное на шаге 2. Вы можете изменить ссылки и применить их к другим диапазонам.
Ранжирование значений в одном диапазоне на нескольких листах с помощью Kutools для Excel
Если вы хотите ранжировать значения, находящиеся в одном диапазоне, например I2:I5, на трех листах: Лист Class1, Лист Class2 и Лист Class3, вы можете использовать утилиту Kutools для Excel’s Автоматическое инкрементирование ссылок на листе для извлечения ячеек в список, а затем ранжировать их.
После установки Kutools для Excel выполните следующие действия:
1. Откройте пустой лист, например, Лист Master, выберите Ячейку I2, и нажмите Kutools > Больше (в группе Формулы) > Автоматическое инкрементирование ссылок на листе. См. скриншот:
2. Затем в диалоговом окне Заполнить ссылки на листы выберите Заполнить по строке, затем по столбцу, и отметьте листы, диапазоны которых вы хотите использовать для ранжирования. См. скриншот:
3. Нажмите Заполнить диапазон, а затем закройте диалоговое окно Заполнение ссылок на листы Теперь все ячейки I2 в Листе Class1, Class2 и Class3 заполнены, затем протяните маркер заполнения вниз до тех пор, пока не появятся нулевые значения. См. скриншот:
4. Теперь все оценки занесены в диапазон, и вы можете их ранжировать. Выберите пустую ячейку, введите формулу =RANK(I2,$I$2:$K$6), затем протяните маркер автозаполнения вниз, затем вправо, чтобы заполнить эту формулу в нужные ячейки. См. скриншот:
Демонстрация
Лучшие инструменты для повышения продуктивности в Office
Повысьте свои навыки работы в Excel с помощью Kutools для Excel и ощутите эффективность на новом уровне. Kutools для Excel предлагает более300 расширенных функций для повышения производительности и экономии времени. Нажмите здесь, чтобы выбрать функцию, которая вам нужнее всего...
Office Tab добавляет вкладки в Office и делает вашу работу намного проще
- Включите режим вкладок для редактирования и чтения в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна вместо новых отдельных окон.
- Увеличьте свою продуктивность на50% и уменьшите количество щелчков мышью на сотни ежедневно!
Все надстройки Kutools. Один установщик
Пакет Kutools for Office включает надстройки для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro — идеально для команд, работающих в разных приложениях Office.
- Комплексный набор — надстройки для Excel, Word, Outlook и PowerPoint плюс Office Tab Pro
- Один установщик, одна лицензия — настройка занимает считанные минуты (MSI-совместимо)
- Совместная работа — максимальная эффективность между приложениями Office
- 30-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек