Ugrás a tartalomra

Hogyan készítsünk dinamikus top 10 vagy n listát az Excelben?

A toplista segítségével rangsorolják a cégeket vagy személyeket értékek alapján. Tételezzük fel, hogy van egy osztály tanulóinak osztályzatainak listája, és most egy dinamikus top 10-es listát szeretne létrehozni a tanulókról az alábbi képernyőképen látható módon. Ebben a cikkben bemutatok néhány képletet a top 10 vagy n lista létrehozásához egy Excel munkalapon.

Hozzon létre egy dinamikus top 10-listát az Excelben

Az Excel 2019 és korábbi verzióiban a top 10 lista vagy a top 10 lista kritériumokkal történő kinyeréséhez a következő képleteket kell alkalmaznia:

Képletek dinamikus top 10-lista létrehozásához

1. Először is ki kell bontania az adattartományból az első 10 értéket, használja az alábbi képletet egy üres cellába - G2, majd húzza le a kitöltő fogantyút, hogy megkapja a legjobb 10 értéket, lásd a képernyőképet:

=LARGE($B$2:$B$20,ROWS(B$2:B2))
Megjegyzések: Ebben a képletben B2: B20 az az adatlista, ahol a 10 legjobb értéket szeretné lekérni, és B2 az adatlista első cellája.

2. Ezután folytassa a következő képlet alkalmazását az F2 cellába, és nyomja meg a gombot Ctrl + Shift + Enter gombok együtt az első eredmény eléréséhez, lásd a képernyőképet:

=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)))
Megjegyzések: A fenti képletben A2: A20 az az adatlista, amelyből a 10 legjobb érték nevét ki szeretné gyűjteni, B2: B20 az összes értéket tartalmazó adatlista, G2 az oszlopból kivont legnagyobb értékű cella B és a B1 az értéklista fejléccellája.

3. Az első eredmény megszerzése után jelölje ki a képlet cellát, és húzza le a kitöltő fogantyút, hogy más neveket kapjon egyszerre, lásd a képernyőképet:

Képletek dinamikus top 10-lista létrehozásához feltételekkel

Néha előfordulhat, hogy kritériumok alapján kell megszereznie a legjobb 10 listát. Például az 10. osztály 1 legjobb nevének és pontszámának beszerzéséhez, ahogy az alábbi képernyőképen látható.

1. A legjobb 10-es lista eléréséhez a legjobb 10 pontszámot is ki kell gyűjtenie ezzel a képlettel:

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

2. Ezután nyomja meg a gombot Ctrl + Shift + Enter billentyűket össze, hogy megkapja az első eredményt, majd húzza le ezt a képletet a többi érték megjelenítéséhez, lásd a képernyőképet:

3. Ezután másolja és illessze be a következő képletet az - I2 cellába, majd nyomja meg a gombot Ctrl + Shift + Enter billentyűket egyidejűleg az első eredmény kinyeréséhez, majd húzza ezt a képletet, hogy más cellákba töltse ki, és a legjobb 10 név az alábbi képernyőkép szerint jelenik meg:

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

Hozzon létre egy dinamikus top 10-listát az Office 365-ben

A fenti képletek nehezen érthetőek lehetnek, ha az Office 365-öt használja, az INDEX, SORT és SEQUENCE funkcióival, egyszerű képleteket készíthet a feladat elvégzéséhez.

Képlet dinamikus top 10-lista létrehozásához

A 10 adatlista megtekintéséhez használja az alábbi képletet:

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

És akkor csak nyomja meg belép gombot, a top 10 lista összes adata egyszerre jelenik meg, lásd a képernyőképet:

tippek:

SORT funkció:

=RENDEZÉS(tömb, [rendezési_index], [rendezési_sorrend], [oszlop szerint])

  • sor: A rendezni kívánt cellák tartománya;
  • [sort_index]: A tömbbe rendezendő oszlop vagy sor száma. Például az adattartomány második oszlopa szerinti rendezéshez a rendezési index 2;
  • [sorrend]: Az 1-es szám (vagy kihagyva) azt jelzi, hogy növekvő sorrendben kell rendezni; a szám -1, rendezd csökkenő sorrendbe;
  • [by_col]: A válogatás iránya. IGAZ, rendezés oszlopok szerint, HAMIS vagy kihagyott, rendezés sorok szerint.

A képletben a SORT függvényt a következőképpen használjuk:

RENDEZÉS(A2:B20,2;1;-XNUMX): A második oszlop A2:A20 cellatartományának csökkenő sorrendbe rendezését jelenti.

SEQUENCE funkció:

=SEQUENCE(sorok, [oszlopok], [kezdet], [lépés])

  • sorok: a visszaadandó sorok száma,
  • [oszlopok]: A visszaadandó oszlopok száma. Ha kihagyja, egyetlen oszlopot ad vissza.
  • [Rajt]: A sorozat első száma. Ha kimarad, 1-től kezdődik.
  • [lépés]: Az egyes számok közötti növekmény. Kizárás esetén minden növekmény 1 lesz.

Ebben a képletben a SEQUENCE(10) segítségével hozzunk létre egy listát 1-től 10-ig.

Végül helyezze a SORT és SEQUENCE függvényeket az INDEX függvénybe: =INDEX(SORT(A2:B20,2,-1),SEQUENCE(10),{1,2}), ez visszaadja az első 10 rekordot az adattartományból, és visszaadja az 1. és 2. oszlopot.

Képlet egy dinamikus top 10-es lista létrehozásához kritériumokkal

A 10 legjobb lista megjelenítéséhez a feltételekkel, a SZŰRŐ függvényt a RENDEZÉS funkcióba kell ágyaznia a következőképpen:

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

És csak nyomja meg belép gombot, a top 10-es lista összes adata az adott kritériumok alapján egyszerre megjelenik, lásd a képernyőképet:

tippek:

FILTER funkció:

=SZŰRŐ(tömb, tartalmazza, [ha_üres])

  • sor: A szűrendő cellák tartománya.
  • tartalmaz: Az a feltétel, amelyet a tömb szűrésére használ, hogy egy TRUE vagy FALSE eredményt kapjon, így az IGAZ értékek megmaradnak a szűrőben.
  • [ha_üres]: Az az érték, amely akkor jelenik meg, ha nem ad vissza egyező eredményt.

Ebben a képletben: =FILTER(A2:C25,B2:B25=F2) az A2:C25 tartomány szűrésére szolgál, ahol a B2:B25 értékek megegyeznek az adott F2 cellával.

A legjobb irodai hatékonyságnövelő eszközök

🤖 Kutools AI Aide: Forradalmasítsa az adatelemzést a következők alapján: Intelligens végrehajtás   |  Kód létrehozása  |  Hozzon létre egyéni képleteket  |  Adatok elemzése és diagramok létrehozása  |  A Kutools funkciók meghívása...
Népszerű szolgáltatások: Ismétlődések keresése, kiemelése vagy azonosítása   |  Üres sorok törlése   |  Oszlopok vagy cellák kombinálása adatvesztés nélkül   |   Kerek Formula nélkül ...
Szuper keresés: Több kritérium VLookup  |   Többértékű VLookup  |   VLookup több munkalapon   |   Fuzzy Lookup ....
Speciális legördülő lista: Gyors legördülő lista létrehozása   |  Függő legördülő lista   |  Többszörösen válassza ki a legördülő listát ....
Oszlopkezelő: Adjon meg egy adott számú oszlopot  |  Oszlopok mozgatása  |  Kapcsolja be a Rejtett oszlopok láthatósági állapotát  |  Tartományok és oszlopok összehasonlítása ...
Kiemelt funkciók: Rács fókusz   |  Design nézet   |   Nagy Formula bár   |  Munkafüzet és lapkezelő   |  Erőforrás-könyvtár (Auto szöveg)   |  Dátumválasztó   |  Kombinálja a munkalapokat   |  Cellák titkosítása/dekódolása   |  E-mailek küldése listánként   |  Szuper szűrő   |   Speciális szűrő (félkövér/dőlt/áthúzott szűrés...) ...
A 15 legjobb eszközkészlet:  12 szöveg Eszközök (Szöveg hozzáadása, Karakterek eltávolítása,...)   |   50 + Táblázatos Típusai (Gantt-diagram,...)   |   40+ Praktikus képletek (Számolja ki az életkort a születésnap alapján,...)   |   19 beszúrás Eszközök (Helyezze be a QR-kódot, Kép beszúrása az útvonalból,...)   |   12 Átalakítás Eszközök (Számok szavakig, Valuta átváltás,...)   |   7 Egyesítés és felosztás Eszközök (Haladó kombinált sorok, Hasított sejtek,...)   |   ... és több

Töltsd fel Excel-készségeidet a Kutools for Excel segítségével, és tapasztald meg a még soha nem látott hatékonyságot. A Kutools for Excel több mint 300 speciális funkciót kínál a termelékenység fokozásához és az időmegtakarításhoz.  Kattintson ide, hogy megszerezze a leginkább szükséges funkciót...

Leírás

Az Office lap füles felületet hoz az Office-ba, és sokkal könnyebbé teszi a munkáját

  • Füles szerkesztés és olvasás engedélyezése Wordben, Excelben és PowerPointban, Publisher, Access, Visio és Project.
  • Több dokumentum megnyitása és létrehozása ugyanazon ablak új lapjain, mint új ablakokban.
  • 50% -kal növeli a termelékenységet, és naponta több száz kattintással csökkenti az egér kattintását!
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