Перейти к содержимому

Как создать динамический список топ-10 или n в Excel?

Author: Xiaoyang Last Modified: 2025-08-07

Список лидеров используется для ранжирования компаний или отдельных лиц на основе значений. Предположим, у вас есть список оценок студентов класса, и теперь вы хотите создать динамический список топ-10 студентов, как показано на скриншоте ниже. В этой статье я представлю несколько формул для создания списка топ-10 или n на листе Excel.

Создание динамического списка топ-10 в Excel

В Excel 2019 и более ранних версиях для извлечения списка топ-10 или списка топ-10 с критериями следует применить следующие формулы:

Формулы для создания динамического списка топ-10

1. Сначала вам нужно извлечь топ-10 значений из диапазона данных, пожалуйста, используйте следующую формулу в пустой ячейке - G2, и протяните маркер заполнения вниз, чтобы получить топ-10 значений, см. скриншот:

=НАИБОЛЬШИЙ($B$2:$B$20;СТРОКА(B$2:B2))
Примечание: В этой формуле B2:B20 — это список данных, из которого вы хотите получить топ-10 значений, а B2 — первая ячейка списка данных.

apply a formula to extract the top 10 values

2. Затем продолжайте применять следующую формулу в ячейку - F2, и нажмите клавиши Ctrl + Shift + Enter вместе, чтобы получить первый результат, см. скриншот:

=ИНДЕКС($A$2:$A$20;НАИМЕНЬШИЙ(ЕСЛИ($B$2:$B$20=G2;СТРОКА($B$2:$B$20)-СТРОКА($B$1));СЧЁТЕСЛИ($G$2:G2;G2)))
Примечание: В приведенной выше формуле A2:A20 — это список данных, из которого вы хотите извлечь имена топ-10 значений, B2:B20 — это список данных, содержащий все значения, G2 — это ячейка наибольшего значения, извлеченного из столбца B, а B1 — заголовочная ячейка списка значений.

use a formula to get relative item

3. После получения первого результата выберите ячейку с формулой и протяните маркер заполнения вниз, чтобы сразу получить другие имена, см. скриншот:

drag and fill the formula to other cells

a screenshot of kutools for excel ai

Раскройте магию Excel с Kutools AI

  • Умное выполнение: Выполняйте операции с ячейками, анализируйте данные и создавайте диаграммы — всё это посредством простых команд.
  • Пользовательские формулы: Создавайте индивидуальные формулы для оптимизации ваших рабочих процессов.
  • Кодирование VBA: Пишите и внедряйте код VBA без особых усилий.
  • Интерпретация формул: Легко разбирайтесь в сложных формулах.
  • Перевод текста: Преодолейте языковые барьеры в ваших таблицах.
Улучшите возможности Excel с помощью инструментов на базе ИИ. Скачать сейчас и испытайте беспрецедентную эффективность!
Формулы для создания динамического списка топ-10 с критериями

Иногда вам может понадобиться получить список топ-10 на основе критериев. Например, чтобы получить топ-10 имен и баллов для Класса 1, как показано на скриншоте ниже.

create a dynamic top 10 list with criteria

1. Чтобы получить список топ-10, вам также нужно извлечь топ-10 баллов с помощью этой формулы:

=НАИБОЛЬШИЙ(ЕСЛИ($B$2:$B$25=$F$2;$C$2:$C$25);СТРОКА(I2)-СТРОКА(I$1))

2. И затем нажмите клавиши Ctrl + Shift + Enter вместе, чтобы получить первый результат, затем протяните эту формулу вниз, чтобы отобразить другие значения, см. скриншот:

apply a formula to extract the top 10 values based on criteria

3. Затем скопируйте и вставьте следующую формулу в ячейку - I2, и одновременно нажмите клавиши Ctrl + Shift + Enter, чтобы извлечь первый результат, затем протяните эту формулу, чтобы заполнить другие ячейки, и будут отображены топ-10 имен.

=ИНДЕКС($A$2:$A$25;НАИМЕНЬШИЙ(ЕСЛИ(($C$2:$C$25=J2)*($B$2:$B$25=$F$2);СТРОКА($C$2:$C$25)-СТРОКА($C$1));СЧЁТЕСЛИ(J2:$J$2;J2)))

Создание динамического списка топ-10 в Office 365

Вышеупомянутые формулы могут быть сложными для понимания, если вы используете Office 365, с его функциями ИНДЕКС, СОРТИРОВКА и ПОСЛЕДОВАТЕЛЬНОСТЬ, вы можете создать простые формулы для выполнения этой задачи.

Формула для создания динамического списка топ-10

Чтобы получить список топ-10 данных, пожалуйста, используйте следующую формулу:

=ИНДЕКС(СОРТИРОВКА(A2:B20;2;-1);ПОСЛЕДОВАТЕЛЬНОСТЬ(10);{1;2})

И затем просто нажмите клавишу Enter, и все данные списка топ-10 отобразятся сразу, см. скриншот:

use a formula to create a dynamic top 10 list in Office 365

Советы:

Функция СОРТИРОВКА:

=СОРТИРОВКА(массив; [индекс_сортировки]; [порядок_сортировки]; [по_столбцу])

  • массив: Диапазон ячеек, который вы хотите отсортировать;
  • [индекс_сортировки]: Номер столбца или строки для сортировки массива. Например, чтобы отсортировать по второму столбцу диапазона данных, индекс сортировки будет равен 2;
  • [порядок_сортировки]: Число 1 (или опущено) указывает на сортировку по возрастанию; число -1, сортировка по убыванию;
  • [по_столбцу]: Направление сортировки. TRUE, сортировка по столбцам, FALSE или опущено, сортировка по строкам.

В формуле мы используем функцию СОРТИРОВКА так:

СОРТИРОВКА(A2:B20;2;-1): Означает сортировку диапазона ячеек A2:A20 по второму столбцу в порядке убывания.

Функция ПОСЛЕДОВАТЕЛЬНОСТЬ:

=ПОСЛЕДОВАТЕЛЬНОСТЬ(строки; [столбцы]; [начало]; [шаг])

  • строки: Количество строк для возврата,
  • [столбцы]: Количество столбцов для возврата. Если опущено, вернется один столбец.
  • [начало]: Первое число в последовательности. Если опущено, начнется с 1.
  • [шаг]: Приращение между каждым числом. Если исключено, каждое приращение будет равно 1.

В этой формуле мы используем ПОСЛЕДОВАТЕЛЬНОСТЬ(10), чтобы создать список от 1 до 10.

В конце концов, поместите обе функции СОРТИРОВКА и ПОСЛЕДОВАТЕЛЬНОСТЬ в функцию ИНДЕКС: =ИНДЕКС(СОРТИРОВКА(A2:B20;2;-1);ПОСЛЕДОВАТЕЛЬНОСТЬ(10);{1;2}), это вернет первые 10 записей из диапазона данных и вернет столбцы 1 и 2.

Формула для создания динамического списка топ-10 с критериями

Чтобы отобразить список топ-10 с критериями, вы должны вложить функцию ФИЛЬТР в функцию СОРТИРОВКА так:

=ИНДЕКС(СОРТИРОВКА(ФИЛЬТР(A2:C25;B2:B25=F2);3;-1);ПОСЛЕДОВАТЕЛЬНОСТЬ(10);{1;3})

И просто нажмите клавишу Enter, и все данные списка топ-10 на основе заданных критериев отобразятся сразу, см. скриншот:

another formula to create a dynamic top 10 list with criteria in office 365

Советы:

Функция ФИЛЬТР:

=ФИЛЬТР(массив; включить; [если_пусто])

  • массив: Диапазон ячеек для фильтрации.
  • включить: Условие, которое вы используете для фильтрации массива, чтобы получить массив результатов TRUE или FALSE, так что значения TRUE будут сохранены в фильтре.
  • [если_пусто]: Значение, которое будет отображаться, если не найдено совпадений.

В этой формуле: =ФИЛЬТР(A2:C25;B2:B25=F2) используется для фильтрации в диапазоне A2:C25, где значения из B2:B25 равны конкретной ячейке F2.

Лучшие инструменты для повышения продуктивности в Office

🤖 Kutools AI Aide: Переворот в анализе данных на основе: Интеллектуальное выполнение   |  Сгенерировать код  |  Создать Пользовательские Формулы  |  Анализ данных и генерация диаграмм  |  Вызов Kutools Functions…
Популярные функции: Найти, выделить или отметить дубликаты   |  Удалить пустые строки   |  Объединить столбцы или ячейки без потери данных   |   Округлить...
Супер ПОИСК: VLookup с несколькими критериями  |   VLookup с несколькими значениями  |   Многолистовой поиск   |   Распознавание нечетких соответствий ....
Расширенный раскрывающийся список: Быстро создать раскр. список   |  Зависимый раскрывающийся список   |  Множественный выбор в раскрывающемся списке ....
Менеджер столбцов: Добавить определенное количество столбцов  |  Переместить столбцы  |  Переключить статус видимости скрытых столбцов  |  Сравнить диапазоны и столбцы ...
Рекомендуемые функции: Сетка фокусировки   |  Дизайн листа   |   Улучшенная строка формулы   |  Управление книгой и листами   |  Библиотека автотекста (Auto Text)   |  Выбор даты   |  Объединить данные   |  Шифрование/Расшифровка ячеек   |  Отправить письмо по списку   |  Супер фильтр   |   Специальный фильтр (фильтр по жирному/курсиву/зачеркиванию...) ...
Топ–15 наборов инструментов: 12 текстовых инструментов (Добавить текст, Удалить определенные символы, ...)   |   50+ типов диаграмм (Диаграмма Ганта, ...)   |   40+ практических формул (Расчет возраста на основе даты рождения, ...)   |   19 инструментов вставки (Вставить QR-код, Вставить изображение из пути, ...)   |  12 инструментов преобразования (Преобразовать в слова, Конвертация валюты, ...)   |  7 инструментов объединения и разделения (Расширенное объединение строк, Разделить ячейки, ...)   |   ... и многое другое
Используйте Kutools на вашем предпочитаемом языке – поддерживаются английский, испанский, немецкий, французский, китайский и более40 других языков!

Повысьте свои навыки работы в Excel с Kutools для Excel и ощутите новую эффективность. Kutools для Excel предлагает более300 расширенных функций для повышения продуктивности и экономии времени.  Щелкните здесь, чтобы получить наиболее нужную вам функцию...

Office Tab добавляет вкладочный интерфейс в Office, делая вашу работу значительно проще

  • Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
  • Открывайте и создавайте несколько документов во вкладках одного окна, а не в новых окнах.
  • Увеличьте свою продуктивность на50% и сократите сотни кликов мышью ежедневно!