Note: The other languages of the website are Google-translated. Back to English

Як створити динамічний список перших 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 і натисніть Ctrl + Shift + 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. А потім натисніть Ctrl + Shift + Enter ключі разом, щоб отримати перший результат, потім перетягніть цю формулу вниз, щоб відобразити інші значення, див. знімок екрана:

3. Потім скопіюйте та вставте наступну формулу в комірку - I2 і натисніть Ctrl + Shift + 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 із функціями INDEX, SORT і SEQUENCE, ви можете створити прості формули для виконання цього завдання.

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

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

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

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

чайові:

Функція SORT:

=СОРТ(масив, [індекс_сортування], [порядок_сортування], [за_стовпцем])

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

У формулі ми використовуємо функцію SORT так:

СОРТ(A2:B20,2;1;-XNUMX): означає сортування діапазону клітинок A2:A20 у другому стовпці в порядку спадання.


Функція SEQUENCE:

=SEQUENCE(рядки, [стовпці], [початок], [крок])

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

У цій формулі ми використовуємо SEQUENCE(10) для створення списку від 1 до 10.

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


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

Щоб відобразити перші 10 списку з критеріями, вам слід вбудувати функцію FILTER у функцію SORT таким чином:

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

І просто натисніть вводити ключ, усі дані списку 10 найкращих за заданими критеріями відображаються одразу, дивіться знімок екрана:

чайові:

Функція FILTER:

=ФІЛЬТР(масив, включити, [якщо_порожній])

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

У цій формулі: =FILTER(A2:C25,B2:B25=F2) використовується для фільтрації в діапазоні A2:C25, де значення з B2:B25 дорівнюють певній клітинці F2.


Найкращі інструменти для підвищення продуктивності офісу

Kutools для Excel вирішує більшість ваших проблем і збільшує продуктивність на 80%

  • Повторне використання: Швидко вставте складні формули, діаграми і все, що ви використовували раніше; Шифрувати комірки з паролем; Створити список розсилки та надсилати електронні листи ...
  • Супер формула бар (легко редагувати кілька рядків тексту та формули); Макет читання (легко читати та редагувати велику кількість комірок); Вставте у відфільтрований діапазон...
  • Об’єднати клітинки / рядки / стовпці без втрати даних; Вміст розділених комірок; Об'єднати повторювані рядки / стовпці... Запобігання дублюючим клітинам; Порівняйте діапазони...
  • Виберіть Повторюваний або Унікальний Рядки; Виберіть Пусті рядки (усі клітинки порожні); Супер знахідка та нечітка знахідка у багатьох робочих зошитах; Випадковий вибір ...
  • Точна копія Кілька клітинок без зміни посилання на формулу; Автоматичне створення посилань на кілька аркушів; Вставте кулі, Прапорці та інше ...
  • Витяг тексту, Додати текст, Видалити за позицією, Видаліть пробіл; Створення та друк проміжних підсумків підкачки; Перетворення вмісту комірок та коментарів...
  • Супер фільтр (зберегти та застосувати схеми фільтрів до інших аркушів); Розширене сортування за місяцем / тижнем / днем, частотою та іншим; Спеціальний фільтр жирним, курсивом ...
  • Поєднайте робочі зошити та робочі аркуші; Об’єднати таблиці на основі ключових стовпців; Розділіть дані на кілька аркушів; Пакетне перетворення xls, xlsx та PDF...
  • Понад 300 потужних функцій. Підтримує Office / Excel 2007-2021 і 365. Підтримує всі мови. Легке розгортання на вашому підприємстві чи в організації. 30-денна безкоштовна пробна версія повних функцій. 60-денна гарантія повернення грошей.
вкладка kte 201905

Вкладка Office забезпечує інтерфейс з вкладками для Office і значно спрощує вашу роботу

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
дно офісної таблиці
Коментарі (0)
Поки немає оцінок. Оцініть першим!
There are no comments posted here yet
Залишайте свої коментарі
Публікація як гість
×
Оцініть цю публікацію:
0   Персонажі
Рекомендовані місця

Слідуй за нами

Copyright © 2009 - WWW.extendoffice.com. | Всі права захищені. На основі ExtendOffice. | Карта сайту
Microsoft та логотип Office є товарними знаками або зареєстрованими товарними знаками Microsoft Corporation у США та / або інших країнах.
Захищений Sectigo SSL