Cuprins
Dacă trebuie să sortați lista, există o mulțime de moduri la dispoziție, dintre care cea mai simplă este butoanele de sortare din filă sau din meniu Date (Date — Sortare). Există însă situații în care sortarea listei trebuie făcută automat, adică formule. Acest lucru poate fi necesar, de exemplu, când se generează date pentru o listă derulantă, când se calculează date pentru diagrame etc. Cum să sortați o listă cu o formulă din mers?
Metoda 1. Date numerice
Dacă lista conține doar informații numerice, atunci sortarea se poate face cu ușurință folosind funcțiile CEL MAI PUŢIN (MIC) и LINE (RÂND):
Funcţie CEL MAI PUŢIN (MIC) scoate din matrice (coloana A) al n-lea cel mai mic element dintr-un rând. Acestea. MIC(A:A;1) este cel mai mic număr din coloană, MIC(A:A;2) este al doilea cel mai mic și așa mai departe.
Funcţie LINE (RÂND) returnează numărul rândului pentru celula specificată, adică ROW(A1)=1, ROW(A2)=2 etc. În acest caz, este folosit pur și simplu ca generator al unei secvențe de numere n=1,2,3... pentru lista noastră sortată. Cu același succes, a fost posibil să se creeze o coloană suplimentară, să o completeze manual cu secvența numerică 1,2,3 ... și să se facă referire la ea în loc de funcția ROW.
Metoda 2. Lista de text și formule obișnuite
Dacă lista nu conține numere, ci text, atunci funcția MICĂ nu va mai funcționa, așa că trebuie să mergeți pe o cale diferită, puțin mai lungă.
Mai întâi, să adăugăm o coloană de serviciu cu o formulă în care numărul de serie al fiecărui nume din lista sortată viitoare va fi calculat folosind funcția COUNTIF (COUNTIF):
În versiunea în limba engleză va fi:
=NUMĂRĂDAC(A:A,»<"&A1)+CONTĂRĂDAC($A$1:A1,"="&A1)
Primul termen este o funcție pentru numărarea numărului de celule care sunt mai mici decât cel curent. Al doilea este o plasă de siguranță în cazul în care orice nume apare de mai multe ori. Atunci nu vor avea numere identice, dar crescătoare succesive.
Acum numerele primite trebuie aranjate secvenţial în ordine crescătoare. Pentru aceasta puteți folosi funcția CEL MAI PUŢIN (MIC) din prima cale:
Ei bine, în sfârșit, rămâne doar să scoatem numele din listă după numerele lor. Pentru a face acest lucru, puteți utiliza următoarea formulă:
Funcţie MAI EXPUSĂ (MECI) caută în coloana B numărul de serie dorit (1, 2, 3 etc.) și, de fapt, returnează numărul liniei în care se află acest număr. Funcţie INDEX (INDEX) scoate din coloana A numele de la acest număr de rând.
Metoda 3: Formula matrice
Această metodă este, de fapt, același algoritm de plasare ca în Metoda-2, dar implementată printr-o formulă matrice. Pentru a simplifica formula, intervalul de celule C1:C10 a primit numele Listă (selectați celule, apăsați Ctrl + F3 și buton Crea):
În celula E1, copiați formula noastră:
=INDEX(Lista; MATCH(MIC(NUMĂRĂDAC(Lista; „<"&Lista); RÂND(1:1)); COUNTIF(Lista; „<"&Lista); 0))
Sau în versiunea în engleză:
=INDEX(Lista, MATCH(SMALL(COUNTIF(Lista, «<"&Lista), ROW(1:1)), COUNTIF(Lista, "<"&Lista), 0))
și împingeți Ctrl + Shift + Enterpentru a o introduce ca formulă matrice. Apoi formula rezultată poate fi copiată pe toată lungimea listei.
Dacă doriți ca formula să nu ia în considerare un interval fix, ci să se poată ajusta atunci când adăugați elemente noi la listă, atunci va trebui să schimbați ușor strategia.
În primul rând, intervalul Listă va trebui setat dinamic. Pentru a face acest lucru, atunci când creați, trebuie să specificați nu un interval fix C3:C10, ci o formulă specială care se va referi la toate valorile disponibile, indiferent de numărul acestora. Clic Alt + F3 sau deschide fila Formule – Manager de nume (Formule — Manager de nume), creați un nume nou și în câmp Link (Referinţă) introduceți următoarea formulă (presupun că intervalul de date care trebuie sortat începe de la celula C1):
=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)
=OFFSET(C1,0,0,SCHÖTZ(C1:C1000),1)
În al doilea rând, formula matricei de mai sus va trebui extinsă cu o marjă - cu așteptarea unor date suplimentare introduse în viitor. În acest caz, formula matricei va începe să dea o eroare #NUMĂR pe celulele care nu sunt încă completate. Pentru a-l intercepta, puteți folosi funcția DACA EROARE, care trebuie adăugat „în jurul” formulei noastre matrice:
=DACA EROARE(INDEX(Lista; MATCH(MIC(NUMĂRĂDAC(Lista; „<"&Lista); RÂND(1:1)); COUNTIF(Lista; „<"&Lista); 0));»»)
=DACEROARE(NDEX(Lista, MATCH(SMALL(COUNTIF(Lista, «<"&Lista), ROW(1:1)), COUNTIF(Lista, "<"&Lista), 0));"")
Captează eroarea #NUMĂR și emite un void (ghilimele goale).
:
- Sortați gama după culoare
- Ce sunt formulele matrice și de ce sunt necesare
- Sortarea și matricele dinamice SORT în noul Office 365