Note: The other languages of the website are Google-translated. Back to English

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 termelékenységi eszközök

A Kutools for Excel megoldja a legtöbb problémát, és 80% -kal növeli a termelékenységet

  • újrafelhasználás: Gyorsan helyezze be összetett képletek, diagramok és bármi, amit korábban használt; Cellák titkosítása jelszóval; Levelezőlista létrehozása és e-maileket küldeni ...
  • Szuper Formula Bár (könnyedén szerkeszthet több szöveget és képletet); Olvasás elrendezés (könnyen olvasható és szerkeszthető nagyszámú cella); Beillesztés a Szűrt tartományba...
  • Cellák / sorok / oszlopok egyesítése az adatok elvesztése nélkül; Osztott cellák tartalma; Kombinálja a duplikált sorokat / oszlopokat... megakadályozza az ismétlődő cellákat; Hasonlítsa össze a tartományokat...
  • Válassza a Másolat vagy az Egyedi lehetőséget Sorok; Válassza az Üres sorok lehetőséget (az összes cella üres); Super Find és Fuzzy Find sok munkafüzetben; Véletlenszerű kiválasztás ...
  • Pontos másolás Több cella a képletreferencia megváltoztatása nélkül; Automatikus referenciák létrehozása több lapra; Helyezze be a golyókat, Jelölőnégyzetek és még sok más ...
  • Kivonat szöveg, Szöveg hozzáadása, Eltávolítás pozíció szerint, Hely eltávolítása; Hozz létre és nyomtasson személyhívó részösszegeket; Konvertálás a cellatartalom és a megjegyzések között...
  • Szuper szűrő (mentse el és alkalmazza a szűrősémákat más lapokra); Haladó rendezés hónap / hét / nap, gyakoriság és egyebek szerint; Speciális szűrő félkövér, dőlt betűvel ...
  • Kombinálja a munkafüzeteket és a munkalapokat; Táblázatok egyesítése kulcsoszlopok alapján; Az adatok felosztása több lapra; Kötegelt konvertálás xls, xlsx és PDF...
  • Több mint 300 hatékony funkció. Támogatja az Office / Excel 2007-2021 és 365 verziókat. Minden nyelvet támogat. Könnyű üzembe helyezés vállalatában vagy szervezetében. Teljes funkciók 30 napos ingyenes próbaverzió. 60 napos pénzvisszafizetési garancia.
kte lap 201905

Az Office fül a füles felületet hozza 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!
officetab alja
A megjegyzések rendezése szerint
Hozzászólások (0)
Még nincs értékelés. Legyen Ön az első, aki értékel!
Még senki sem írt megjegyzést
Hagyja meg észrevételeit
Feladás vendégként
×
Értékeld ezt a bejegyzést:
0   Karakterek
Javasolt helyek

Kövess minket

Copyright © 2009 - www.extendoffice.com. | Minden jog fenntartva. Powered by ExtendOffice. | Oldaltérkép
A Microsoft és az Office logó a Microsoft Corporation védjegyei vagy bejegyzett védjegyei az Egyesült Államokban és / vagy más országokban.
Sectigo SSL védi