Ako trebate sortirati popis, na usluzi vam je mnogo načina, od kojih je najlakši gumbi za sortiranje na kartici ili u izborniku Datum (Podaci — Sortiraj). Međutim, postoje situacije kada se sortiranje popisa mora raditi automatski, tj. formule. To može biti potrebno, na primjer, kada se generiraju podaci za padajući popis, kada se izračunavaju podaci za grafikone, itd. Kako sortirati popis s formulom u hodu?
Metoda 1. Numerički podaci
Ako popis sadrži samo numeričke informacije, onda se njegovo sortiranje može jednostavno izvršiti pomoću funkcija NAJMANJE (MALI) и ONLINE (RED):
funkcija NAJMANJE (MALI) izvlači iz niza (stupac A) n-ti najmanji element u nizu. Oni. SMALL(A:A;1) je najmanji broj u stupcu, SMALL(A:A;2) je drugi najmanji, i tako dalje.
funkcija ONLINE (RED) vraća broj retka za navedenu ćeliju, tj. ROW(A1)=1, ROW(A2)=2 itd. U ovom slučaju, koristi se jednostavno kao generator niza brojeva n=1,2,3… za naš sortirani popis. S istim uspjehom bilo je moguće napraviti dodatni stupac, ručno ga popuniti numeričkim nizom 1,2,3… i pozvati se na njega umjesto funkcije ROW.
Metoda 2. Popis teksta i regularne formule
Ako popis ne sadrži brojeve, već tekst, tada funkcija SMALL više neće raditi, pa morate ići drugim, malo dužim, putem.
Prvo, dodajmo uslužni stupac s formulom gdje će se serijski broj svakog imena u budućem sortiranom popisu izračunati pomoću funkcije COUNTIF (COUNTIF):
U engleskoj verziji to će biti:
=COUNTIF(A:A,»<"&A1)+COUNTIF($A$1:A1,"="&A1)
Prvi izraz je funkcija za brojanje ćelija koje su manje od trenutne. Drugo je sigurnosna mreža u slučaju da se bilo koje ime pojavi više puta. Tada neće imati isti, već sukcesivno sve veći broj.
Sada se primljeni brojevi moraju poredati uzlaznim redoslijedom. Za to možete koristiti funkciju NAJMANJE (MALI) iz prvog načina:
Pa, konačno, ostaje samo izvući imena s popisa po njihovim brojevima. Da biste to učinili, možete upotrijebiti sljedeću formulu:
funkcija EKSPONIRANIJI (UTAKMICA) traži u stupcu B željeni redni broj (1, 2, 3 itd.) i zapravo vraća broj retka u kojem se taj broj nalazi. Funkcija INDEKS (INDEKS) izvlači iz stupca A ime u ovom broju retka.
Metoda 3: Formula polja
Ova metoda je, zapravo, isti algoritam postavljanja kao u Metodi-2, ali implementiran formulom niza. Kako bismo pojednostavili formulu, raspon ćelija C1:C10 dobio je naziv Popis (odaberite ćelije, pritisnite Ctrl + F3 i gumb stvoriti):
U ćeliju E1 kopirajte našu formulu:
=INDEX(Lista; MATCH(MALO(COUNTIF(List; “<"&List); ROW(1:1)); COUNTIF(List; "<"&List); 0))
Ili u engleskoj verziji:
=INDEX(Lista, MATCH(SMALL(COUNTIF(List, «<"&List), ROW(1:1)), COUNTIF(List, "<"&List), 0))
i gurati Ctrl + Shift + Enterda ga unesete kao formulu polja. Zatim se dobivena formula može kopirati niz cijelu duljinu popisa.
Ako želite da formula ne uzima u obzir fiksni raspon, već da se može prilagoditi prilikom dodavanja novih elemenata na popis, tada ćete morati malo promijeniti strategiju.
Prvo, raspon popisa treba dinamički postaviti. Da biste to učinili, prilikom izrade morate navesti ne fiksni raspon C3: C10, već posebnu formulu koja će se odnositi na sve dostupne vrijednosti, bez obzira na njihov broj. Klik Alt + F3 ili otvorite karticu Formule – Upravitelj imena (Formule — Upravitelj naziva), stvorite novo ime i u polju Veza (Referenca) unesite sljedeću formulu (pretpostavljam da raspon podataka koji se sortiraju počinje od ćelije C1):
=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)
=POMAK(C1,0,0;1;1000;SCHÖTZ(C1:CXNUMX);XNUMX)
Drugo, gornju formulu polja trebat će rastegnuti prema dolje s marginom – uz očekivanje dodatnih podataka koji se unose u budućnosti. U tom će slučaju formula polja početi davati pogrešku #BROJ u ćelijama koje još nisu popunjene. Da biste ga presreli, možete koristiti funkciju AKO POGREŠKA, koji treba dodati "oko" naše formule polja:
=IFERROR(INDEX(Lista; MATCH(SMALL(COUNTIF(List; “<"&List); ROW(1:1)); COUNTIF(List; "<"&List); 0));»»)
=IFERROR(NDEX(List, MATCH(SMALL(COUNTIF(List, «<"&List), ROW(1:1)), COUNTIF(List, "<"&List), 0));"")
Hvata pogrešku #NUMBER i umjesto toga ispisuje prazninu (prazne navodnike).
:
- Razvrstaj raspon po boji
- Što su formule polja i zašto su potrebne
- SORT sortiranje i dinamički nizovi u novom Office 365