Як створити динамічний список перших 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.
Найкращі інструменти для підвищення продуктивності офісу
Kutools для Excel вирішує більшість ваших проблем і збільшує продуктивність на 80%
- Повторне використання: Швидко вставте складні формули, діаграми і все, що ви використовували раніше; Шифрувати комірки з паролем; Створити список розсилки та надсилати електронні листи ...
- Супер формула бар (легко редагувати кілька рядків тексту та формули); Макет читання (легко читати та редагувати велику кількість комірок); Вставте у відфільтрований діапазон...
- Об’єднати клітинки / рядки / стовпці без втрати даних; Вміст розділених комірок; Об'єднати повторювані рядки / стовпці... Запобігання дублюючим клітинам; Порівняйте діапазони...
- Виберіть Повторюваний або Унікальний Рядки; Виберіть Пусті рядки (усі клітинки порожні); Супер знахідка та нечітка знахідка у багатьох робочих зошитах; Випадковий вибір ...
- Точна копія Кілька клітинок без зміни посилання на формулу; Автоматичне створення посилань на кілька аркушів; Вставте кулі, Прапорці та інше ...
- Витяг тексту, Додати текст, Видалити за позицією, Видаліть пробіл; Створення та друк проміжних підсумків підкачки; Перетворення вмісту комірок та коментарів...
- Супер фільтр (зберегти та застосувати схеми фільтрів до інших аркушів); Розширене сортування за місяцем / тижнем / днем, частотою та іншим; Спеціальний фільтр жирним, курсивом ...
- Поєднайте робочі зошити та робочі аркуші; Об’єднати таблиці на основі ключових стовпців; Розділіть дані на кілька аркушів; Пакетне перетворення xls, xlsx та PDF...
- Понад 300 потужних функцій. Підтримує Office / Excel 2007-2021 і 365. Підтримує всі мови. Легке розгортання на вашому підприємстві чи в організації. 30-денна безкоштовна пробна версія повних функцій. 60-денна гарантія повернення грошей.
Вкладка Office забезпечує інтерфейс з вкладками для Office і значно спрощує вашу роботу
- Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
- Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
- Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
