Як створити динамічний список перших 10 або n у Excel?
Топ-лист використовується для ранжування компаній або окремих осіб на основі цінностей. Припустімо, що у вас є список оцінок учнів класу, тепер ви хочете створити динамічний список 10 найкращих студентів, як показано на знімку екрана нижче. У цій статті я познайомлю деякі формули для створення 10 найкращих або списку n на аркуші Excel.
Створіть динамічний список топ-10 у Excel
У Excel 2019 і попередніх версіях, щоб витягнути список 10 найкращих або список 10 найкращих із критеріями, слід застосувати такі формули:
Формули для створення динамічного списку топ-10
1. По-перше, вам слід витягти 10 найвищих значень із діапазону даних. Будь ласка, скористайтеся наведеною нижче формулою в порожню комірку - G2 і перетягніть ручку заповнення вниз, щоб отримати 10 найвищих значень, див. знімок екрана:
2. Потім продовжуйте застосовувати наступну формулу до комірки - F2 і натисніть Ctrl + Shift + Enter клавіші разом, щоб отримати перший результат, див. скріншот:
3. Після отримання першого результату виберіть комірку формули та перетягніть ручку заповнення вниз, щоб одразу отримати інші імена, див. знімок екрана:
Формули для створення динамічного списку топ-10 із критеріями
Іноді вам може знадобитися отримати список 10 найкращих на основі критеріїв. Наприклад, щоб отримати 10 найкращих імен і балів Class1, як показано на знімку екрана нижче.
1. Щоб отримати список 10 найкращих, вам також потрібно витягнути 10 найкращих результатів за цією формулою:
2. А потім натисніть Ctrl + Shift + Enter ключі разом, щоб отримати перший результат, потім перетягніть цю формулу вниз, щоб відобразити інші значення, див. знімок екрана:
3. Потім скопіюйте та вставте наступну формулу в комірку - I2 і натисніть Ctrl + Shift + Enter клавіші одночасно, щоб отримати перший результат, а потім перетягніть цю формулу, щоб заповнити її в інші комірки, і 10 найпопулярніших імен відображатимуться, як показано на знімку екрана нижче:
Створіть динамічний список топ-10 в Office 365
Нам може бути важко зрозуміти наведені вище формули. Якщо ви використовуєте Office 365 із функціями INDEX, SORT і SEQUENCE, ви можете створити прості формули для виконання цього завдання.
Формула для створення динамічного списку топ-10
Щоб отримати список 10 даних, скористайтеся наведеною нижче формулою:
А потім просто натисніть Що натомість? Створіть віртуальну версію себе у ключ, всі дані списку перших 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 таким чином:
І просто натисніть Що натомість? Створіть віртуальну версію себе у ключ, усі дані списку 10 найкращих за заданими критеріями відображаються одразу, дивіться знімок екрана:
Функція FILTER:
=ФІЛЬТР(масив, включити, [якщо_порожній])
- масив: діапазон комірок для фільтрування.
- включати: Умова, яку ви використовуєте для фільтрації масиву, щоб отримати результат TRUE або FALSE, щоб значення TRUE зберігалися у фільтрі.
- [якщо_порожній]: значення, яке буде відображено, якщо не буде отримано відповідних результатів.
У цій формулі: =FILTER(A2:C25,B2:B25=F2) використовується для фільтрації в діапазоні A2:C25, де значення з B2:B25 дорівнюють певній клітинці F2.
Найкращі інструменти продуктивності офісу
Покращуйте свої навички Excel за допомогою Kutools for Excel, і відчуйте ефективність, як ніколи раніше. Kutools for Excel Пропонує понад 300 додаткових функцій для підвищення продуктивності та економії часу. Натисніть тут, щоб отримати функцію, яка вам найбільше потрібна...
Office Tab Надає інтерфейс із вкладками в Office і значно полегшує вашу роботу
- Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
- Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
- Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
