Как быстро ранжировать данные в нескольких диапазонах и на разных листах 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. Нажмите OK → Закрыть, чтобы закрыть диалоговые окна. Затем перейдите к пустой ячейке рядом со значением, которое нужно ранжировать, введите формулу =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 «Автоматическое инкрементирование ссылок на листе», чтобы собрать ячейки в единый список, а затем проранжировать их.
После установки 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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек