Hvis du trenger å sortere listen, er det mange måter til din tjeneste, den enkleste er sorteringsknappene på fanen eller i menyen Data (Data – Sorter). Det er imidlertid situasjoner hvor sorteringen av listen må gjøres automatisk, dvs. formler. Dette kan være nødvendig, for eksempel ved generering av data for en nedtrekksliste, ved beregning av data for diagrammer osv. Hvordan sortere en liste med en formel i farten?
Metode 1. Numeriske data
Hvis listen bare inneholder numerisk informasjon, kan sorteringen enkelt gjøres ved hjelp av funksjonene MINST (LITEN) и LINE (RAD):
Funksjon MINST (LITEN) trekker ut fra matrisen (kolonne A) det n-te minste elementet i en rad. De. SMALL(A:A;1) er det minste tallet i kolonnen, SMALL(A:A;2) er det nest minste, og så videre.
Funksjon LINE (RAD) returnerer radnummeret for den angitte cellen, dvs. ROW(A1)=1, ROW(A2)=2 osv. I dette tilfellet brukes det ganske enkelt som en generator av en tallsekvens n=1,2,3... for vår sorterte liste. Med samme suksess var det mulig å lage en ekstra kolonne, fylle den manuelt med den numeriske sekvensen 1,2,3 ... og referere til den i stedet for ROW-funksjonen.
Metode 2. Tekstliste og vanlige formler
Hvis listen ikke inneholder tall, men tekst, fungerer ikke LITEN-funksjonen lenger, så du må gå en annen, litt lengre, vei.
Først, la oss legge til en tjenestekolonne med en formel der serienummeret til hvert navn i den fremtidige sorterte listen vil bli beregnet ved hjelp av funksjonen ANTALL.HVIS (COUNTIF):
I den engelske versjonen blir det:
=COUNTIF(A:A,»<"&A1)+COUNTIF($A$1:A1,"="&A1)
Det første leddet er en funksjon for å telle antall celler som er mindre enn den gjeldende. Den andre er et sikkerhetsnett i tilfelle et navn forekommer mer enn én gang. Da vil de ikke ha det samme, men suksessivt økende antall.
Nå må de mottatte tallene ordnes sekvensielt i stigende rekkefølge. Til dette kan du bruke funksjonen MINST (LITEN) fra første vei:
Vel, til slutt gjenstår det bare å trekke ut navnene fra listen etter tallene deres. For å gjøre dette kan du bruke følgende formel:
Funksjon MER UTSATT (KAMP) søker i kolonne B etter ønsket serienummer (1, 2, 3 osv.) og returnerer faktisk nummeret på linjen der dette nummeret står. Funksjon INDEX (INDEKS) trekker ut fra kolonne A navnet på dette linjenummeret.
Metode 3: Matriseformel
Denne metoden er faktisk den samme plasseringsalgoritmen som i Metode-2, men implementert av en matriseformel. For å forenkle formelen ble celleområdet C1:C10 gitt navnet Liste (velg celler, trykk Ctrl + F3 og knapp Opprett):
I celle E1 kopierer du formelen vår:
= Indeks (liste; match (liten (countif (liste; “<" & list); rad (1: 1)); countif (list; "<" & list); 0)))
Eller i engelsk versjon:
= Indeks (liste, match (liten (countif (liste, «<" & liste), rad (1: 1)), countif (liste, "<" & list), 0)))
og dytt Ctrl + Shift + Enterfor å angi den som en matriseformel. Deretter kan den resulterende formelen kopieres ned i hele lengden av listen.
Hvis du vil at formelen ikke skal ta hensyn til et fast område, men være i stand til å justere når du legger til nye elementer i listen, må du endre strategien litt.
Først må listeområdet stilles inn dynamisk. For å gjøre dette, når du oppretter, må du ikke spesifisere et fast område C3:C10, men en spesiell formel som vil referere til alle tilgjengelige verdier, uavhengig av antallet. Klikk Alt + F3 eller åpne fanen Formler – Navnebehandler (Formler – navnebehandler), opprette et nytt navn og i feltet link (Henvisning) skriv inn følgende formel (jeg antar at dataområdet som 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 andre må arrayformelen ovenfor strekkes ned med en margin – med forventning om ytterligere data som legges inn i fremtiden. I dette tilfellet vil matriseformelen begynne å gi en feil #NUMBER på celler som ennå ikke er fylt. For å avskjære den kan du bruke funksjonen FEIL, som må legges til "rundt" matriseformelen vår:
=IFERROR(INDEKS(Liste; MATCH(LITEN(ANTALL(Liste; «<"&Liste); RAD(1:1)); ANTALLHVIS(Liste; "<"&Liste; 0));»»)
=IFERROR(NDEX(Liste, MATCH(LITEN(ANTALL(Liste, «<"&Liste), RAD(1:1)), ANTALLHVIS(Liste, "<"&Liste), 0));"")
Den fanger #NUMBER-feilen og sender ut et tomrom (tomme anførselstegn) i stedet.
:
- Sorter rekkevidde etter farge
- Hva er matriseformler og hvorfor trengs de
- SORT sortering og dynamiske matriser i nye Office 365