Как создать динамический список 10 или n в Excel?
Список лучших используется для ранжирования компаний или отдельных лиц на основе ценностей. Предположим, у вас есть список оценок учеников класса, теперь вы хотите создать динамический список 10 лучших учеников, как показано ниже. В этой статье я представлю некоторые формулы для создания списка 10 лучших или n на листе Excel.
Создайте динамический список 10 лучших в Excel
В Excel 2019 и более ранних версиях, чтобы извлечь список 10 лучших или список 10 лучших с критериями, вы должны применить следующие формулы:
Формулы для создания динамического списка 10 лучших
1. Во-первых, вы должны извлечь первые 10 значений из диапазона данных, используйте приведенную ниже формулу в пустой ячейке — G2 и перетащите маркер заполнения вниз, чтобы получить первые 10 значений, см. снимок экрана:
2. Затем продолжайте применять следующую формулу в ячейке - F2 и нажмите Shift + Ctrl + Enter вместе, чтобы получить первый результат, см. снимок экрана:
3. Получив первый результат, выберите ячейку формулы и перетащите дескриптор заполнения вниз, чтобы сразу получить другие имена, см. снимок экрана:
Формулы для создания динамического списка 10 лучших с критериями
Иногда вам может понадобиться получить список 10 лучших на основе критериев. Например, чтобы получить 10 лучших имен и оценок Class1, как показано ниже.
1. Чтобы получить список 10 лучших, вам также необходимо извлечь 10 лучших результатов с помощью этой формулы:
2, Затем нажмите Shift + Ctrl + Enter клавиши вместе, чтобы получить первый результат, затем перетащите эту формулу вниз, чтобы отобразить другие значения, см. снимок экрана:
3. Затем скопируйте и вставьте следующую формулу в ячейку - I2 и нажмите Shift + Ctrl + Enter клавиши одновременно, чтобы извлечь первый результат, а затем перетащите эту формулу, чтобы заполнить ее в другие ячейки, и 10 лучших имен отображаются, как показано ниже:
Создайте динамический список 10 лучших в Office 365
Нам может быть сложно понять приведенные выше формулы, если вы используете Office 365 с его функциями ИНДЕКС, СОРТИРОВКА и ПОСЛЕДОВАТЕЛЬНОСТЬ, вы можете создать простые формулы для выполнения этой задачи.
Формула для создания динамического списка 10 лучших
Чтобы получить список данных10, используйте приведенную ниже формулу:
А затем просто нажмите 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 лучших с критериями, вы должны встроить функцию ФИЛЬТР в функцию СОРТИРОВКИ следующим образом:
И просто нажмите Enter ключ, все данные списка 10 лучших по заданным критериям отображаются сразу, см. снимок экрана:
Функция ФИЛЬТР:
=ФИЛЬТР(массив, включить, [if_empty])
- массив: диапазон ячеек для фильтрации.
- включают: условие, которое вы используете для фильтрации массива, чтобы получить массив результатов ИСТИНА или ЛОЖЬ, так что значения ИСТИНА будут сохранены в фильтре.
- [если_пусто]: значение, которое будет отображаться, если совпадающие результаты не возвращаются.
В этой формуле: =FILTER(A2:C25,B2:B25=F2) используется для фильтрации в диапазоне A2:C25, где значения из B2:B25 равны конкретной ячейке F2.
Лучшие инструменты для офисной работы
Улучшите свои навыки работы с Excel с помощью Kutools for Excel и почувствуйте эффективность, как никогда раньше. Kutools for Excel предлагает более 300 расширенных функций для повышения производительности и экономии времени. Нажмите здесь, чтобы получить функцию, которая вам нужна больше всего...
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!