Перейти к основному содержанию

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

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


Создайте динамический список 10 лучших в Excel

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

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

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

=LARGE($B$2:$B$20,ROWS(B$2:B2))
Внимание: В этой формуле B2: B20 это список данных, из которого вы хотите получить первые 10 значений, и B2 это первая ячейка списка данных.

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

=INDEX($A$2:$A$20,SMALL(IF($B$2:$B$20=G2,ROW($B$2:$B$20)-ROW($B$1)),COUNTIF($G$2:G2,G2)))
Внимание: В приведенной выше формуле A2: A20 это список данных, из которого вы хотите получить имена первых 10 значений, B2: B20 список данных, содержащий все значения, G2 это ячейка с наибольшим значением, извлеченным из столбца B и B1 является ячейкой заголовка списка значений.

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


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

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

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

=LARGE(IF($B$2:$B$25=$F$2,$C$2:$C$25),ROW(I2)-ROW(I$1))

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

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

=INDEX($A$2:$A$25,SMALL(IF(($C$2:$C$25=J2)*($B$2:$B$25=$F$2),ROW($C$2:$C$25)-ROW($C$1)),COUNTIF(J2:$J$2,J2)))

Создайте динамический список 10 лучших в Office 365

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

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

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

=INDEX(SORT(A2:B20,2,-1),SEQUENCE(10),{1,2})

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

Советы:

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

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

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

В формуле мы используем функцию SORT следующим образом:

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


ПОСЛЕДОВАТЕЛЬНОСТЬ:

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

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

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

Наконец, поместите обе функции SORT и SEQUENCE в функцию INDEX: =INDEX(SORT(A2:B20,2,-1),SEQUENCE(10),{1,2}), это вернет первые 10 записей из диапазона данных и вернет столбцы 1 и 2.


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

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

=INDEX(SORT(FILTER(A2:C25,B2:B25=F2),3,-1),SEQUENCE(10),{1,3})

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

Советы:

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

=ФИЛЬТР(массив, включить, [if_empty])

  • массив: диапазон ячеек для фильтрации.
  • включают: условие, которое вы используете для фильтрации массива, чтобы получить массив результатов ИСТИНА или ЛОЖЬ, так что значения ИСТИНА будут сохранены в фильтре.
  • [если_пусто]: значение, которое будет отображаться, если совпадающие результаты не возвращаются.

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

Лучшие инструменты для офисной работы

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

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

Описание


Вкладка Office: интерфейс с вкладками в Office и упрощение работы

  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations