Hvis du har brug for at sortere listen, så er der mange måder til din tjeneste, hvoraf den nemmeste er sorteringsknapperne på fanen eller i menuen data (Data — Sorter). Der er dog situationer, hvor sorteringen af listen skal ske automatisk, altså formler. Dette kan være nødvendigt, for eksempel ved generering af data til en rulleliste, ved beregning af data til diagrammer osv. Hvordan sorterer man en liste med en formel i farten?
Metode 1. Numeriske data
Hvis listen kun indeholder numerisk information, kan sorteringen let ske ved hjælp af funktionerne MINDST (LILLE) и LINE (RÆKKE):
Funktion MINDST (LILLE) trækker det n-te mindste element i rækken ud fra arrayet (kolonne A). De der. SMALL(A:A;1) er det mindste tal i kolonnen, SMALL(A:A;2) er det næstmindste, og så videre.
Funktion LINE (RÆKKE) returnerer rækkenummeret for den angivne celle, dvs. ROW(A1)=1, ROW(A2)=2 osv. I dette tilfælde bruges det blot som en generator af en talfølge n=1,2,3... for vores sorterede liste. Med samme succes var det muligt at lave en ekstra kolonne, udfylde den manuelt med den numeriske sekvens 1,2,3 … og henvise til den i stedet for ROW-funktionen.
Metode 2. Tekstliste og regulære formler
Hvis listen ikke indeholder tal, men tekst, så virker SMALL-funktionen ikke længere, så du skal gå en anden, lidt længere, vej.
Lad os først tilføje en servicekolonne med en formel, hvor serienummeret for hvert navn i den fremtidige sorterede liste vil blive beregnet ved hjælp af funktionen TÆL.HVIS (COUNTIF):
I den engelske version vil det være:
=COUNTIF(A:A,»<"&A1)+COUNTIF($A$1:A1,"="&A1)
Det første led er en funktion til at tælle antallet af celler, der er mindre end den nuværende. Det andet er et sikkerhedsnet, hvis et navn forekommer mere end én gang. Så vil de ikke have det samme, men successivt stigende antal.
Nu skal de modtagne numre arrangeres sekventielt i stigende rækkefølge. Til dette kan du bruge funktionen MINDST (LILLE) fra den første vej:
Nå, endelig er det bare at trække navnene ud fra listen efter deres numre. For at gøre dette kan du bruge følgende formel:
Funktion MERE UDSAT (MATCH) søger i kolonne B efter det ønskede serienummer (1, 2, 3 osv.) og returnerer faktisk nummeret på den linje, hvor dette nummer er placeret. Fungere INDEX (INDEKS) trækker fra kolonne A navnet ved dette linjenummer.
Metode 3: Matrixformel
Denne metode er faktisk den samme placeringsalgoritme som i Metode-2, men implementeret af en matrixformel. For at forenkle formlen fik celleområdet C1:C10 navnet Liste (vælg celler, tryk på Ctrl + F3 og knappen Opret):
I celle E1 skal du kopiere vores formel:
=INDEKS(Liste; MATCH(SMÅL(TÆLHVIS(Liste; "<"&Liste); RÆKKE(1:1)); ANTALHVIS(Liste; "<"&Liste; 0))
Eller i den engelske version:
=INDEKS(Liste, SAMMENLIGN(SMÅL(TÆLHVIS(Liste, «<"&Liste), RÆKKE(1:1)), ANTALHVIS(Liste, "<"&Liste), 0))
og skub Ctrl + Skift + Enterfor at indtaste det som en matrixformel. Derefter kan den resulterende formel kopieres ned i hele listens længde.
Hvis du ønsker, at formlen ikke skal tage højde for et fast interval, men være i stand til at justere, når du tilføjer nye elementer til listen, skal du ændre strategien lidt.
Først skal listeområdet indstilles dynamisk. For at gøre dette, når du opretter, skal du ikke angive et fast område C3:C10, men en speciel formel, der vil referere til alle tilgængelige værdier, uanset deres antal. Klik Alt + F3 eller åbn fanen Formler – Navneadministrator (Formler – Navneadministrator), opret et nyt navn og i feltet Link (Reference) indtast følgende formel (jeg antager, at rækken af data, der skal sorteres, starter fra celle C1):
=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)
=OFFSET(C1,0,0;1;1000;SCHØTZ(C1:CXNUMX);XNUMX)
For det andet skal ovenstående matrixformel strækkes ned med en margin - med forventning om yderligere data indtastet i fremtiden. I dette tilfælde vil matrixformlen begynde at give en fejl #NUMBER på celler, der endnu ikke er udfyldt. For at opsnappe det, kan du bruge funktionen FEJL, som skal tilføjes "omkring" vores matrixformel:
=IFERROR(INDEKS(Liste; SAMMENLIGN(SMÅ(TÆLHVIS(Liste; "<"&Liste); RÆKKE(1:1)); ANTALHVIS(Liste; "<"&Liste; 0));»»)
=IFERROR(NDEX(Liste, MATCH(SMALL(COUNTIF(Liste, «<"&Liste), ROW(1:1)), COUNTIF(Liste, "<"&Liste), 0));"")
Den fanger #NUMBER-fejlen og udsender et tomrum (tomme anførselstegn) i stedet.
:
- Sorter rækkevidde efter farve
- Hvad er matrixformler, og hvorfor er de nødvendige
- SORT sortering og dynamiske arrays i den nye Office 365