vsebina
Če morate razvrstiti seznam, potem vam je na voljo veliko načinov, od katerih je najlažji gumb za razvrščanje na zavihku ali v meniju datum (Podatki — Razvrsti). Vendar pa obstajajo situacije, ko je treba razvrščanje seznama izvesti samodejno, tj. formule. To je morda potrebno na primer pri ustvarjanju podatkov za spustni seznam, pri izračunu podatkov za grafikone itd. Kako sproti razvrščati seznam s formulo?
Metoda 1. Numerični podatki
Če seznam vsebuje samo številske informacije, ga lahko preprosto razvrstite s funkcijami VSAJ (MAJHNO) и LINE (VRSTICA):
funkcija VSAJ (MAJHNO) potegne iz matrike (stolpec A) n-ti najmanjši element v vrsti. Tisti. SMALL(A:A;1) je najmanjše število v stolpcu, SMALL(A:A;2) je drugo najmanjše in tako naprej.
funkcija LINE (VRSTICA) vrne številko vrstice za navedeno celico, tj. ROW(A1)=1, ROW(A2)=2 itd. V tem primeru se uporablja preprosto kot generator zaporedja števil n=1,2,3… za naš urejen seznam. Z enakim uspehom je bilo mogoče narediti dodaten stolpec, ga ročno zapolniti s številskim zaporedjem 1,2,3 … in se sklicevati nanj namesto funkcije ROW.
Metoda 2. Besedilni seznam in navadne formule
Če seznam ne vsebuje številk, ampak besedilo, potem funkcija SMALL ne bo več delovala, zato morate iti po drugi, nekoliko daljši poti.
Najprej dodajmo storitveni stolpec s formulo, kjer bo serijska številka vsakega imena na prihodnjem razvrščenem seznamu izračunana s funkcijo COUNTIF (COUNTIF):
V angleški različici bo to:
=COUNTIF(A:A,»<"&A1)+COUNTIF($A$1:A1,"="&A1)
Prvi izraz je funkcija za štetje števila celic, ki so manjše od trenutne. Drugi je varnostna mreža, če se katero koli ime pojavi večkrat. Potem ne bodo imeli enakega, ampak zaporedno naraščajočega števila.
Zdaj je treba prejete številke razporediti zaporedno v naraščajočem vrstnem redu. Za to lahko uporabite funkcijo VSAJ (MAJHNO) iz prve poti:
No, končno ostane le, da potegnemo imena s seznama po njihovih številkah. Če želite to narediti, lahko uporabite naslednjo formulo:
funkcija BOLJ IZPOSTAVLJENO (TEKMA) išče v stolpcu B želeno zaporedno številko (1, 2, 3 itd.) in pravzaprav vrne številko vrstice, kjer se ta številka nahaja. funkcija INDEX (KAZALO) potegne iz stolpca A ime v tej številki vrstice.
3. način: matrična formula
Ta metoda je pravzaprav enak algoritem umestitve kot pri metodi 2, vendar se izvaja s formulo polja. Za poenostavitev formule je obseg celic C1:C10 dobil ime Seznam (izberite celice, pritisnite Ctrl + F3 in gumb ustvarjanje):
V celico E1 kopirajte našo formulo:
=KAZALO(Seznam; UJEMANJE(MAJHNO(ŠTEVILO(Seznam; “<"&Seznam); VRSTICA(1:1)); ŠTEVILO(Seznam; "<"&Seznam); 0))
Ali v angleški različici:
=INDEX(Seznam, MATCH(SMALL(COUNTIF(Seznam, «<"&Seznam), ROW(1:1)), COUNTIF(Seznam, "<"&Seznam), 0))
in potisnite Ctrl + Shift + Enterda ga vnesete kot matrično formulo. Nato lahko dobljeno formulo prekopirate po celotni dolžini seznama.
Če želite, da formula ne upošteva fiksnega obsega, ampak se lahko prilagodi pri dodajanju novih elementov na seznam, boste morali nekoliko spremeniti strategijo.
Najprej bo treba dinamično nastaviti obseg seznama. Če želite to narediti, pri ustvarjanju ne morate določiti fiksnega obsega C3: C10, temveč posebno formulo, ki se bo nanašala na vse razpoložljive vrednosti, ne glede na njihovo število. Kliknite Alt + F3 ali odprite zavihek Formule – Upravitelj imen (Formule — Upravitelj imen), ustvarite novo ime in v polju Link (Sklic) vnesite naslednjo formulo (predvidevam, da se obseg podatkov, ki jih je treba razvrstiti, začne od celice C1):
=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)
=OFFSET(C1,0,0;1;1000;SCHÖTZ(C1:CXNUMX);XNUMX)
Drugič, zgornjo matrično formulo bo treba raztegniti navzdol z robom – s pričakovanjem, da bodo v prihodnosti vneseni dodatni podatki. V tem primeru bo matrična formula začela dajati napako #ŠTEVILKA v celicah, ki še niso zapolnjene. Če ga želite prestreči, lahko uporabite funkcijo NAPAKA, ki ga je treba dodati "okrog" naše matrične formule:
=IFERROR(INDEX(Seznam; MATCH(SMALL(COUNTIF(Seznam; “<"&Seznam); VRST(1:1)); COUNTIF(Seznam; "<"&Seznam); 0));»»)
=IFERROR(NDEX(Seznam, MATCH(SMALL(COUNTIF(Seznam, «<"&Seznam), VRST(1:1)), COUNTIF(Seznam, "<"&Seznam), 0));"")
Ujame napako #ŠTEVILO in namesto tega izpiše praznino (prazne narekovaje).
:
- Razvrsti obseg po barvi
- Kaj so matrične formule in zakaj so potrebne
- SORT razvrščanje in dinamična polja v novi Office 365