Preskoči na glavno vsebino

Kako ustvariti dinamičen seznam 10 ali n v Excelu?

Top seznam se uporablja za razvrščanje podjetij ali posameznikov na podlagi vrednot. Recimo, da imate seznam ocen učencev v razredu, zdaj pa želite ustvariti dinamičen seznam 10 najboljših učencev, kot je prikazano na spodnjem posnetku zaslona. V tem članku bom predstavil nekaj formul za ustvarjanje seznama 10 najboljših ali n v Excelovem delovnem listu.


Ustvarite dinamičen seznam 10 najboljših v Excelu

Če želite v Excelu 2019 in starejših različicah izvleči seznam 10 najboljših ali seznam 10 najboljših s kriteriji, uporabite naslednje formule:

Formule za ustvarjanje dinamičnega seznama 10 najboljših

1. Najprej bi morali iz obsega podatkov izvleči prvih 10 vrednosti, uporabite spodnjo formulo v prazno celico - G2 in povlecite ročico za polnjenje navzdol, da dobite prvih 10 vrednosti, glejte sliko zaslona:

=LARGE($B$2:$B$20,ROWS(B$2:B2))
Opombe: V tej formuli, B2: B20 je seznam podatkov, kjer želite pridobiti prvih 10 vrednosti, in B2 je prva celica na seznamu podatkov.

2. Nato nadaljujte z uporabo naslednje formule v celici - F2 in pritisnite Ctrl + Shift + Enter tipke skupaj, da dobite prvi rezultat, glejte posnetek zaslona:

=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)))
Opombe: V zgornji formuli, A2: A20 je seznam podatkov, iz katerega želite pridobiti imena prvih 10 vrednosti, B2: B20 je seznam podatkov, ki vsebuje vse vrednosti, G2 je celica z največjo vrednostjo, ekstrahirano iz stolpca B in B1 je celica glave seznama vrednosti.

3. Ko dobite prvi rezultat, izberite celico formule in povlecite ročico za polnjenje navzdol, da dobite druga imena hkrati, glejte sliko zaslona:


Formule za ustvarjanje dinamičnega seznama 10 najboljših s kriteriji

Včasih boste morda morali dobiti seznam 10 najboljših na podlagi meril. Na primer, da dobite 10 najboljših imen in rezultatov Class1, kot je prikazano na spodnjem posnetku zaslona.

1. Če želite dobiti seznam 10 najboljših, morate izluščiti tudi 10 najboljših rezultatov s to formulo:

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

2. In nato pritisnite Ctrl + Shift + Enter tipke skupaj, da dobite prvi rezultat, nato povlecite to formulo navzdol, da prikažete druge vrednosti, glejte posnetek zaslona:

3. Nato kopirajte in prilepite naslednjo formulo v celico - I2 in pritisnite Ctrl + Shift + Enter tipki hkrati, da izvlečete prvi rezultat, in nato povlecite to formulo, da jo zapolnite v druge celice, in prvih 10 imen je prikazanih, kot je prikazano na spodnjem posnetku zaslona:

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

Ustvarite dinamičen seznam 10 najboljših v storitvi Office 365

Zgornje formule morda težko razumemo, če uporabljate Office 365 z njegovimi funkcijami INDEX, SORT in SEQUENCE, lahko ustvarite preproste formule za dokončanje te naloge.

Formula za ustvarjanje dinamičnega seznama 10 najboljših

Za pridobitev seznama 10 podatkov uporabite spodnjo formulo:

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

In potem samo pritisnite Vnesite ključ, vsi podatki seznama 10 najboljših so prikazani hkrati, glejte posnetek zaslona:

nasveti:

Funkcija SORT:

=SORT(matrika, [indeks_razvrščanja], [vrstni_vrstni_vrstek], [po_stolpcu])

  • matrika: obseg celic, ki jih želite razvrstiti;
  • [sort_index]: številka stolpca ali vrstice, po kateri želite razvrstiti matriko. Če želite na primer razvrstiti po drugem stolpcu obsega podatkov, bi bil indeks razvrščanja 2;
  • [razvrsti_vrstni red]: Številka 1 (ali izpuščena) pomeni razvrščanje v naraščajočem vrstnem redu; število -1, razvrsti v padajočem vrstnem redu;
  • [by_col]: Smer razvrščanja. TRUE, razvrsti po stolpcih, FALSE ali izpuščeno, razvrsti po vrsticah.

V formuli uporabljamo funkcijo SORT kot to:

RAZVRSTI(A2:B20,2;1;-XNUMX): pomeni razvrščanje obsega celic A2:A20 v drugem stolpcu v padajočem vrstnem redu.


Funkcija SEQUENCE:

=SEQUENCE(vrstice, [stolpci], [začetek], [korak])

  • vrstic: število vrstic za vrnitev,
  • [stolpci]: število stolpcev za vrnitev. Če je izpuščeno, vrne en sam stolpec.
  • [začetek]: Prva številka v zaporedju. Če je izpuščeno, se bo začelo pri 1.
  • [korak]: korak med posameznimi številkami. Če je izključeno, bo vsak prirastek 1.

V tej formuli uporabljamo SEQUENCE(10) za ustvarjanje seznama od 1 do 10.

Končno vstavite funkciji SORT in SEQUENCE v funkcijo INDEX: =INDEX(SORT(A2:B20,2,-1),SEQUENCE(10),{1,2}), bo to vrnilo prvih 10 zapisov iz obsega podatkov in vrnilo stolpca 1 in 2.


Formula za ustvarjanje dinamičnega seznama 10 najboljših s kriteriji

Če želite prikazati seznam 10 najboljših s kriteriji, morate funkcijo FILTER vdelati v funkcijo SORT, kot sledi:

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

In samo pritisnite Vnesite ključ, vsi podatki seznama 10 najboljših na podlagi danih kriterijev so prikazani hkrati, glejte posnetek zaslona:

nasveti:

Funkcija FILTER:

=FILTER(matrika, vključi, [če_prazno])

  • matrika: obseg celic za filtriranje.
  • vključujejo: Pogoj, ki ga uporabite za filtriranje matrike, da dobite matriko z rezultatom TRUE ali FALSE, tako da bodo vrednosti TRUE ohranjene v filtru.
  • [če_prazno]: vrednost, ki bo prikazana, če ni vrnjenih nobenih ustreznih rezultatov.

V tej formuli: =FILTER(A2:C25,B2:B25=F2) se uporablja za filtriranje v obsegu A2:C25, kjer so vrednosti iz B2:B25 enake specifični celici F2.

Najboljša pisarniška orodja za produktivnost

Priljubljene funkcije: Poiščite, označite ali identificirajte dvojnike   |  Izbriši prazne vrstice   |  Združite stolpce ali celice brez izgube podatkov   |   Krog brez formule ...
Super iskanje: Več kriterijev VLookup    Multiple Value VLookup  |   VLookup na več listih   |   Nejasno iskanje ....
Napredni spustni seznam: Hitro ustvarite spustni seznam   |  Odvisni spustni seznam   |  Večkrat izberite spustni seznam ....
Upravitelj stolpcev: Dodajte določeno število stolpcev  |  Premakni stolpce  |  Preklop stanja vidnosti skritih stolpcev  |  Primerjaj obsege in stolpce ...
Predstavljene funkcije: Mrežni fokus   |  Pogled oblikovanja   |   Velika vrstica formule    Upravitelj delovnih zvezkov in listov   |  Knjižnica virov (Samodejno besedilo)   |  Izbirnik datuma   |  Združite delovne liste   |  Šifriranje/dešifriranje celic    Pošljite e-pošto po seznamu   |  Super filter   |   Poseben filter (filter krepko/ležeče/prečrtano ...) ...
15 najboljših kompletov orodij12 Besedilo Orodja (dodajanje besedila, Odstrani znake,...)   |   50 + Graf Vrste (Gantt Chart,...)   |   40+ Praktično Formule (Izračunajte starost glede na rojstni dan,...)   |   19 vstavljanje Orodja (Vstavite kodo QR, Vstavi sliko s poti,...)   |   12 Pretvorba Orodja (Številke v besede, Pretvorba valut,...)   |   7 Spoji in razdeli Orodja (Napredne kombinirane vrstice, Razdeljene celice,...)   |   ... in več

Napolnite svoje Excelove spretnosti s Kutools za Excel in izkusite učinkovitost kot še nikoli prej. Kutools za Excel ponuja več kot 300 naprednih funkcij za povečanje produktivnosti in prihranek časa.  Kliknite tukaj, če želite pridobiti funkcijo, ki jo najbolj potrebujete...

zavihek kte 201905


Kartica Office prinaša vmesnik z zavihki v Office in poenostavi vaše delo

  • Omogočite urejanje in branje z zavihki v Wordu, Excelu, PowerPointu, Publisher, Access, Visio in Project.
  • Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
  • Poveča vašo produktivnost za 50%in vsak dan zmanjša na stotine klikov miške za vas!
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations