Om du behöver sortera listan finns det många sätt till din tjänst, det enklaste är sorteringsknapparna på fliken eller i menyn Data (Data — Sortera). Det finns dock situationer då sorteringen av listan behöver göras automatiskt, dvs formler. Detta kan krävas, till exempel när man genererar data för en rullgardinslista, vid beräkning av data för diagram, etc. Hur sorterar man en lista med en formel i farten?
Metod 1. Numeriska data
Om listan endast innehåller numerisk information, kan sorteringen enkelt göras med hjälp av funktionerna MINST (SMÅ) и LINJE (RAD):
Funktion MINST (SMÅ) drar ut det n:te minsta elementet i rad från arrayen (kolumn A). De där. SMALL(A:A;1) är det minsta talet i kolumnen, SMALL(A:A;2) är det näst minsta, och så vidare.
Funktion LINJE (RAD) returnerar radnumret för den angivna cellen, dvs ROW(A1)=1, ROW(A2)=2 etc. I det här fallet används det helt enkelt som en generator av en talföljd n=1,2,3... för vår sorterade lista. Med samma framgång var det möjligt att skapa ytterligare en kolumn, fylla den manuellt med den numeriska sekvensen 1,2,3 … och referera till den istället för ROW-funktionen.
Metod 2. Textlista och vanliga formler
Om listan inte innehåller siffror, utan text, så fungerar inte SMALL-funktionen längre, så du måste gå en annan, lite längre, väg.
Låt oss först lägga till en tjänstkolumn med en formel där serienumret för varje namn i den framtida sorterade listan kommer att beräknas med funktionen COUNTIF (COUNTIF):
I den engelska versionen blir det:
=COUNTIF(A:A,»<"&A1)+COUNTIF($A$1:A1,"="&A1)
Den första termen är en funktion för att räkna antalet celler som är mindre än den nuvarande. Det andra är ett skyddsnät om något namn förekommer mer än en gång. Då kommer de inte att ha samma, utan successivt ökande antal.
Nu måste de mottagna numren ordnas sekventiellt i stigande ordning. För detta kan du använda funktionen MINST (SMÅ) från första vägen:
Nåväl, äntligen återstår det bara att dra ut namnen från listan efter deras nummer. För att göra detta kan du använda följande formel:
Funktion MER EXPONERAD (MATCH) söker i kolumn B efter önskat serienummer (1, 2, 3, etc.) och returnerar faktiskt numret på raden där detta nummer finns. Fungera INDEX (INDEX) drar ut namnet vid detta radnummer från kolumn A.
Metod 3: Matrisformel
Denna metod är i själva verket samma placeringsalgoritm som i metod-2, men implementerad av en matrisformel. För att förenkla formeln fick cellområdet C1:C10 namnet Lista (välj celler, tryck Ctrl + F3 och knapp Skapa):
Kopiera vår formel i cell E1:
= Index (lista; match (liten (countif (lista; “<" & lista); rad (1: 1)); countif (lista; "<" & lista); 0))
Eller i den engelska versionen:
= Index (lista, match (liten (countif (lista, «<" & lista), rad (1: 1)), countif (lista, "<" & lista), 0))
och tryck Ctrl + Skift + Enterför att ange den som en matrisformel. Sedan kan den resulterande formeln kopieras ner längs hela listan.
Om du vill att formeln inte ska ta hänsyn till ett fast intervall, utan kunna justera när du lägger till nya element till listan, måste du ändra strategin något.
Först måste listintervallet ställas in dynamiskt. För att göra detta, när du skapar, måste du ange inte ett fast intervall C3:C10, utan en speciell formel som kommer att referera till alla tillgängliga värden, oavsett deras antal. Klick Alt + F3 eller öppna fliken Formler – Namnhanterare (Formler – Namnhanterare), skapa ett nytt namn och i fältet Länk (Referens) ange följande formel (jag antar att dataintervallet som ska sorteras börjar från cell C1):
=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)
=OFFSET(C1,0,0;1;1000;SCHÖTZ(C1:CXNUMX);XNUMX)
För det andra kommer ovanstående arrayformel att behöva sträckas ut med en marginal – med förväntning om ytterligare data in i framtiden. I det här fallet kommer matrisformeln att börja ge ett fel #NUMBER på celler som ännu inte är ifyllda. För att fånga upp det kan du använda funktionen FEL, som måste läggas till "runt" vår matrisformel:
=IFERROR(INDEX(Lista; MATCH(LITEN(ANTALOM(Lista; “<”&Lista); RAD(1:1)); ANTALOM(Lista; "<"&Lista; 0));»»)
= Iferror (ndex (lista, match (liten (countif (lista, «<" & lista), rad (1: 1)), countif (lista, "<" & list), 0)); "")
Den fångar #NUMBER-felet och matar ut ett tomrum (tomma citattecken) istället.
:
- Sortera intervall efter färg
- Vad är matrisformler och varför behövs de
- SORTERA sortering och dynamiska arrayer i nya Office 365