Ako trebate sortirati listu, postoji mnogo načina na raspolaganju, od kojih su najlakši tipke za sortiranje na kartici ili u meniju podaci (Podaci — sortiranje). Međutim, postoje situacije kada se sortiranje liste mora izvršiti automatski, odnosno formula. Ovo može biti potrebno, na primjer, kada se generišu podaci za padajuću listu, kada se izračunaju podaci za grafikone, itd. Kako sortirati listu sa formulom u hodu?
Metoda 1. Numerički podaci
Ako lista sadrži samo numeričke informacije, sortiranje se može lako izvršiti pomoću funkcija LEAST (MALA) и LINE (RED):
funkcija LEAST (MALA) izvlači iz niza (kolona A) n-ti najmanji element u nizu. One. SMALL(A:A;1) je najmanji broj u koloni, SMALL(A:A;2) je drugi najmanji, i tako dalje.
funkcija LINE (RED) vraća broj reda 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šu sortiranu listu. S istim uspjehom bilo je moguće napraviti dodatnu kolonu, ručno je popuniti numeričkim nizom 1,2,3... i pozvati se na nju umjesto funkcije ROW.
Metoda 2. Lista teksta i regularne formule
Ako lista ne sadrži brojeve, već tekst, tada funkcija SMALL više neće raditi, tako da morate ići drugom, malo dužom, putanjom.
Prvo, dodajmo servisnu kolonu sa formulom u kojoj će se serijski broj svakog imena u budućoj sortiranoj listi izračunati pomoću funkcije COUNTIF (COUNTIF):
U engleskoj verziji to će biti:
=COUNTIF(A:A,»<"&A1)+COUNTIF($A$1:A1,"="&A1)
Prvi pojam je funkcija za brojanje broja ćelija koje su manje od trenutnog. Druga je sigurnosna mreža u slučaju da se neko ime pojavi više puta. Tada neće imati isti, već sukcesivno rastući broj.
Sada primljeni brojevi moraju biti poredani uzastopno u rastućem redosledu. Za to možete koristiti funkciju LEAST (MALA) sa prvog načina:
Pa, na kraju, ostaje samo da izvučemo imena sa liste po broju. Da biste to učinili, možete koristiti sljedeću formulu:
funkcija IZLOŽENIJI (UTAKMICA) traži u koloni B željeni serijski broj (1, 2, 3, itd.) i, zapravo, vraća broj reda u kojem se nalazi ovaj broj. Funkcija INDEX (INDEX) izvlači iz kolone A ime na ovom broju reda.
Metoda 3: Formula niza
Ova metoda je, u stvari, isti algoritam postavljanja kao u Metodi-2, ali implementiran pomoću formule niza. Da bi se formula pojednostavila, opseg ćelija C1:C10 je dobio ime Popis (odaberite ćelije, pritisnite Ctrl + F3 i dugme stvoriti):
U ćeliju E1 kopirajte našu formulu:
=INDEX(Lista; PODRŽAVA(MALA(BROJ(Lista; “<"&Lista); RED(1:1)); BROJ(Lista; "<"&Lista); 0))
Ili u engleskoj verziji:
=INDEX(Lista, PODRŽAVANJE(MALO(BROJ(Lista, «<"&Lista), RED(1:1)), BROJ(Lista, "<"&Lista), 0))
i gurati Ctrl + Shift + Enterda ga unesete kao formulu niza. Tada se rezultirajuća formula može kopirati niz cijelu dužinu liste.
Ako želite da formula ne uzima u obzir fiksni raspon, već da se može prilagoditi prilikom dodavanja novih elemenata na listu, tada ćete morati malo promijeniti strategiju.
Prvo, raspon liste će se morati dinamički postaviti. Da biste to učinili, prilikom kreiranja, morate navesti ne fiksni raspon C3:C10, već posebnu formulu koja će se odnositi na sve dostupne vrijednosti, bez obzira na njihov broj. Kliknite Alt + F3 ili otvorite karticu Formule – Menadžer imena (Formule — Menadžer imena), kreirajte novo ime i u polju link (Referenca) unesite sljedeću formulu (pretpostavljam da raspon podataka za sortiranje počinje od ćelije C1):
=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)
=OFFSET(C1,0,0,SCHÖTZ(C1:C1000),1)
Drugo, gornju formulu niza će trebati rastegnuti s marginom – uz očekivanje dodatnih podataka unesenih u budućnosti. U ovom slučaju, formula polja će početi da daje grešku #BROJ na ćelijama koje još nisu popunjene. Da biste ga presreli, možete koristiti funkciju IFERROR, koji treba dodati "oko" naše formule niza:
=AKO GREŠKA(INDEX(Lista; PODRŽAVANJE(MALO(BROJ(Lista; “<"&Lista); RED(1:1)); BROJ(Lista; "<"&Lista); 0));»»)
=IFGREŠKA(NDEX(Lista, PODRŽAVANJE(MALO(BROJ(Lista, «<"&Lista), RED(1:1)), BROJ(Lista, "<"&Lista), 0));"")
Hvata grešku #NUMBER i umjesto toga ispisuje void (prazne navodnike).
:
- Sortiraj raspon prema boji
- Šta su formule niza i zašto su potrebne
- SORT sortiranje i dinamičke nizove u novom Office 365