Как быстро ранжировать данные в нескольких диапазонах и листах 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), затем протяните маркер автозаполнения вниз, затем вправо, чтобы заполнить эту формулу в нужные ячейки. См. скриншот:

Совет. Если вы хотите бесплатно опробовать функцию Автоматическое инкрементирование ссылок на листе, сначала перейдите по ссылке для бесплатной пробной версии Kutools для Excel, а затем выполните операцию согласно вышеуказанным шагам.

Демонстрация