Přejít k hlavnímu obsahu

Jak vytvořit dynamický seznam top 10 nebo n v aplikaci Excel?

Nejlepší seznam se používá k seřazení společností nebo jednotlivců na základě hodnot. Předpokládejme, že máte seznam známek studentů třídy, nyní chcete vytvořit dynamický seznam 10 nejlepších studentů, jak je znázorněno na obrázku níže. V tomto článku představím některé vzorce pro vytvoření seznamu 10 nejlepších nebo n v listu aplikace Excel.

Vytvořte dynamický seznam 10 nejlepších v Excelu

Chcete-li v Excelu 2019 a starších verzích extrahovat seznam 10 nejlepších nebo 10 nejlepších s kritérii, měli byste použít následující vzorce:

Vzorce pro vytvoření dynamického seznamu 10 nejlepších

1. Nejprve byste měli extrahovat 10 nejlepších hodnot z rozsahu dat, použijte prosím níže uvedený vzorec do prázdné buňky - G2 a přetáhněte úchyt výplně dolů, abyste získali 10 nejlepších hodnot, viz snímek obrazovky:

=LARGE($B$2:$B$20,ROWS(B$2:B2))
Poznámka: V tomto vzorci B2: B20 je seznam dat, kde chcete získat 10 nejlepších hodnot a B2 je první buňka seznamu dat.

2. Poté pokračujte v aplikaci následujícího vzorce do buňky - F2 a stiskněte Ctrl + Shift + Enter společně získáte první výsledek, viz screenshot:

=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)))
Poznámka: Ve výše uvedeném vzorci, A2: A20 je seznam dat, ze kterého chcete získat názvy 10 nejlepších hodnot, B2: B20 je seznam dat, který obsahuje všechny hodnoty, G2 je buňka s největší hodnotou extrahovanou ze sloupce B a B1 je buňka záhlaví seznamu hodnot.

3. Po získání prvního výsledku vyberte buňku vzorce a přetáhněte úchyt výplně dolů, abyste získali další názvy najednou, viz snímek obrazovky:

Vzorce pro vytvoření dynamického seznamu 10 nejlepších s kritérii

Někdy možná budete muset získat seznam 10 nejlepších na základě kritérií. Chcete-li například získat 10 nejlepších jmen a skóre třídy 1, jak je uvedeno níže.

1. Chcete-li získat seznam 10 nejlepších, měli byste také extrahovat 10 nejlepších skóre pomocí tohoto vzorce:

=LARGE(IF($B$2:$B$25=$F$2,$C$2:$C$25),ROW(I2)-ROW(I$1))

2. A pak stiskněte Ctrl + Shift + Enter klávesy dohromady, abyste získali první výsledek, pak přetažením tohoto vzorce dolů zobrazte další hodnoty, viz snímek obrazovky:

3. Poté prosím zkopírujte a vložte následující vzorec do buňky - I2 a stiskněte Ctrl + Shift + Enter stiskněte současně klávesy pro extrahování prvního výsledku a poté přetažením tohoto vzorce jej vyplňte do dalších buněk a zobrazí se prvních 10 názvů, jak je uvedeno níže:

=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)))

Vytvořte dynamický seznam 10 nejlepších v Office 365

Výše uvedené vzorce pro nás mohou být obtížně srozumitelné, pokud používáte Office 365 s funkcemi INDEX, SORT a SEQUENCE, můžete vytvořit jednoduché vzorce pro dokončení tohoto úkolu.

Vzorec pro vytvoření dynamického seznamu 10 nejlepších

Chcete-li získat seznam 10 údajů, použijte prosím níže uvedený vzorec:

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

A pak stačí stisknout vstoupit klíč, všechna data ze seznamu 10 nejlepších se zobrazí najednou, viz snímek obrazovky:

Tipy:

Funkce SORT:

=SORT(pole; [index_řazení]; [pořadí_řazení]; [podle_sloupce])

  • řada: Rozsah buněk, které chcete seřadit;
  • [index_řazení]: Číslo sloupce nebo řádku, podle kterého se má třídit do pole. Například pro řazení podle druhého sloupce rozsahu dat by index řazení byl 2;
  • [sort_order]: Číslo 1 (nebo vynechané) označuje řazení ve vzestupném pořadí; číslo -1, seřadit sestupně;
  • [podle_sloupce]: Směr řazení. PRAVDA, řazení podle sloupců, NEPRAVDA nebo vynecháno, řazení podle řádků.

Ve vzorci používáme funkci SORT takto:

SEŘADIT(A2:B20,2;1;-XNUMX): Znamená seřadit rozsah buněk A2:A20 ve druhém sloupci v sestupném pořadí.

Funkce SEQUENCE:

=SEQUENCE(řádky; [sloupce]; [začátek]; [krok])

  • Řádky: Počet řádků, které se mají vrátit,
  • [sloupce]: Počet sloupců, které se mají vrátit. Pokud je vynechán, vrátí jeden sloupec.
  • [Start]: První číslo v pořadí. Pokud je vynechán, začne od 1.
  • [krok]: Přírůstek mezi jednotlivými čísly. Pokud je vyloučeno, každý přírůstek bude 1.

V tomto vzorci používáme SEQUENCE(10) k vytvoření seznamu od 1 do 10.

Nakonec vložte funkce SORT i SEQUENCE do funkce INDEX: =INDEX(SORT(A2:B20,2,-1),SEQUENCE(10),{1,2}), vrátí prvních 10 záznamů z rozsahu dat a vrátí sloupce 1 a 2.

Vzorec pro vytvoření dynamického seznamu 10 nejlepších s kritérii

Chcete-li zobrazit seznam 10 nejlepších s kritérii, měli byste do funkce SORT vložit funkci FILTER takto:

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

A stačí stisknout vstoupit klíč, všechna data z top 10 seznamu na základě daných kritérií se zobrazí najednou, viz snímek obrazovky:

Tipy:

Funkce FILTER:

=FILTER(pole, zahrnout, [pokud_prázdný])

  • řada: Rozsah buněk k filtrování.
  • obsahovat: Podmínka, kterou použijete k filtrování pole, abyste získali pole s výsledkem TRUE nebo FALSE, takže hodnoty TRUE budou ve filtru zachovány.
  • [if_empty]: Hodnota, která se zobrazí, pokud nebudou vráceny žádné odpovídající výsledky.

V tomto vzorci: =FILTER(A2:C25,B2:B25=F2) slouží k filtrování v rozsahu A2:C25, kde se hodnoty z B2:B25 rovnají konkrétní buňce F2.

Nejlepší nástroje pro produktivitu v kanceláři

Populární funkce: Najít, zvýraznit nebo identifikovat duplikáty   |  Odstranit prázdné řádky   |  Kombinujte sloupce nebo buňky bez ztráty dat   |   Kolo bez vzorce ...
Super vyhledávání: Více kritérií VLookup  |   VLookup s více hodnotami  |   VLookup na více listech   |   Fuzzy vyhledávání ....
Pokročilý rozevírací seznam: Rychle vytvořte rozevírací seznam   |  Závislý rozbalovací seznam   |  Vícenásobný výběr rozevíracího seznamu ....
Správce sloupců: Přidejte konkrétní počet sloupců  |  Přesunout sloupce  |  Přepnout stav viditelnosti skrytých sloupců  |  Porovnejte rozsahy a sloupce ...
Doporučené funkce: Zaměření mřížky   |  Návrhové zobrazení   |   Velký Formula Bar   |  Správce sešitů a listů   |  Knihovna zdrojů (Automatický text)   |  Výběr data   |  Zkombinujte pracovní listy   |  Šifrovat/dešifrovat buňky   |  Odesílat e-maily podle seznamu   |  Super filtr   |   Speciální filtr (filtr tučné/kurzíva/přeškrtnuté...) ...
Top 15 sad nástrojů:  12 Text Tools (doplnit text, Odebrat znaky, ...)   |   50 + Graf Typ nemovitosti (Ganttův diagram, ...)   |   40+ Praktické Vzorce (Vypočítejte věk na základě narozenin, ...)   |   19 Vložení Tools (Vložte QR kód, Vložit obrázek z cesty, ...)   |   12 Konverze Tools (Čísla na slova, Přepočet měny, ...)   |   7 Sloučit a rozdělit Tools (Pokročilé kombinování řádků, Rozdělit buňky, ...)   |   ... a více

Rozšiřte své excelentní dovednosti pomocí Kutools for Excela zažijte efektivitu jako nikdy předtím. Kutools for Excel Nabízí více než 300 pokročilých funkcí pro zvýšení produktivity a úsporu času.  Kliknutím sem získáte funkci, kterou nejvíce potřebujete...

karta kte 201905

Office Tab Přináší rozhraní s kartami do Office a usnadňuje vám práci

  • Povolte úpravy a čtení na kartách ve Wordu, Excelu, PowerPointu, Publisher, Access, Visio a Project.
  • Otevřete a vytvořte více dokumentů na nových kartách ve stejném okně, nikoli v nových oknech.
  • Zvyšuje vaši produktivitu o 50%a snižuje stovky kliknutí myší každý den!
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