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

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 orodja za pisarniško produktivnost

Kutools za Excel rešuje večino vaših težav in poveča vašo produktivnost za 80%

  • Ponovna uporaba: Hitro vstavite zapletene formule, grafikoni in vse, kar ste že uporabljali; Šifriraj celice z geslom; Ustvari poštni seznam in pošiljanje e-pošte ...
  • Vrstica Super Formula (enostavno urejanje več vrstic besedila in formule); Bralna postavitev (enostavno branje in urejanje velikega števila celic); Prilepite v filtrirani obseg...
  • Združi celice / vrstice / stolpce brez izgube podatkov; Vsebina razdeljenih celic; Združi podvojene vrstice / stolpce... prepreči podvojene celice; Primerjaj obsege...
  • Izberite Duplicate ali Unique Vrstice; Izberite prazne vrstice (vse celice so prazne); Super Find in Fuzzy Find v mnogih delovnih zvezkih; Naključna izbira ...
  • Natančna kopija Več celic brez spreminjanja sklica formule; Samodejno ustvarjanje referenc na več listov; Vstavi oznake, Potrditvena polja in še več ...
  • Izvleček besedila, Dodaj besedilo, Odstrani po položaju, Odstrani presledek; Ustvari in natisni vmesne seštevke strani Pretvarjanje med vsebino celic in komentarji...
  • Super filter (shranite in uporabite sheme filtrov za druge liste); Napredno razvrščanje glede na mesec / teden / dan, pogostost in drugo; Poseben filter s krepko, ležeče ...
  • Združite delovne zvezke in delovne liste; Spoji tabele na podlagi ključnih stolpcev; Razdelite podatke na več listov; Paketna pretvorba xls, xlsx in PDF...
  • Več kot 300 zmogljivih funkcij. Podpira Office / Excel 2007-2021 in 365. Podpira vse jezike. Enostavna uvedba v vašem podjetju ali organizaciji. 30-dnevna brezplačna preizkusna različica vseh funkcij. 60-dnevna garancija vračila denarja.
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!
dno pisarniške mize
Komentarji (0)
Ocene še ni. Bodite prvi in ​​ocenite!
Tu še ni objavljenih komentarjev
Pustite vaše komentarje
Objava kot gost
×
Ocenite to objavo:
0   Znaki
Predlagane lokacije

Sledi nam

Copyright © 2009 - www.extendoffice.com. | Vse pravice pridržane. Poganja ga ExtendOffice. | Kazalo
Microsoft in logotip Office sta blagovni znamki ali registrirani blagovni znamki družbe Microsoft Corporation v ZDA in / ali drugih državah.
Zaščiteno s Sectigo SSL