Как да създадете динамичен списък с топ 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, сортирайте в низходящ ред;
- [по_кола]: Посоката на сортиране. 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:
=ФИЛТЪР(масив, включване, [ако_празно])
- масив: Диапазонът от клетки за филтриране.
- include: Условието, което използвате, за да филтрирате масива, за да получите масив с резултат TRUE или FALSE, така че стойностите TRUE да бъдат запазени във филтъра.
- [ако_празно]: Стойността, която трябва да се покаже, ако не бъдат върнати съответстващи резултати.
В тази формула: =FILTER(A2:C25,B2:B25=F2) се използва за филтриране в диапазона A2:C25, където стойностите от B2:B25 са равни на конкретната клетка F2.
Най-добрите инструменти за продуктивност в офиса
Усъвършенствайте уменията си за Excel с Kutools за Excel и изпитайте ефективност, както никога досега. Kutools за Excel предлага над 300 разширени функции за повишаване на производителността и спестяване на време. Щракнете тук, за да получите функцията, от която се нуждаете най-много...
Раздел Office Внася интерфейс с раздели в Office и прави работата ви много по-лесна
- Разрешете редактиране и четене с раздели в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Отваряйте и създавайте множество документи в нови раздели на един и същ прозорец, а не в нови прозорци.
- Увеличава вашата производителност с 50% и намалява стотици кликвания на мишката за вас всеки ден!
