Перейти до основного матеріалу

Як створити динамічний список перших 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 відображаються одночасно, дивіться знімок екрана:

Tips :

Функція 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 найкращих за заданими критеріями відображаються одразу, дивіться знімок екрана:

Tips :

Функція FILTER:

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

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

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

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

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

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

вкладка kte 201905

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

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