Hur man returnerar de översta eller nedersta n-posterna utan filter eller pivottabell i Excel

Bild: Imam Fathoni, Getty Images/iStockPhoto

Återlämna toppen eller botten n Det är inte svårt att registrera en datamängd i Excel, och det finns många sätt att göra det. Du kan filtrera, använda uttryck eller till och med en pivottabell; din rutt kan bero på hur du använder resultaten. Men nu, tack vare de nya dynamiska array-funktionerna, kan du använda ett uttryck för att returnera så många kolumner och rader av källdata som du vill. I den här artikeln ska jag prata lite om dessa funktioner. Sedan kommer vi att använda dem för att returnera toppen och botten n uppgifter.

SER: 83 Excel-tips som alla användare bör behärska (TechRepublic)

Måste läsa Windows-täckning

Jag använder Microsoft 365 (skrivbord) på ett Windows 10 64-bitarssystem. Båda dynamiska array-funktionerna i denna lösning är tillgängliga i Microsoft 365 och Excel 2021, Excel för webben, Excel för iPad och iPhone, Excel för Android-surfplattor och telefoner. För din bekvämlighet kan du ladda ner demonstrationsfilen .xlsx. Den här artikeln förutsätter att du har grundläggande Excel-kunskaper, men även en nybörjare bör kunna följa instruktionerna för att lyckas.

Komma ikapp

Tidigare återvände toppen eller botten n krävde lite arbete och viss specialkunskap. Du kan använda ett avancerat filter, ett uttryck eller till och med en pivottabell. Det fina med de nya dynamiska array-funktionerna är att du inte ändrar källdata; dessa funktioner skapar en ny datamängd.

Om du inte är bekant med de äldre sätten att returnera toppen eller botten n kan du läsa några av följande artiklar om detta ämne:

De är inte nödvändigtvis föråldrade, särskilt om du vill arbeta med källdata på plats. Om du vill arbeta med en ny datamängd är de nya funktionerna lösningen.

Bild A visar en enkel datamängd som en tabell. Vi vill returnera toppen och botten n värden i kolumnen Värde. En kombination av funktionerna som granskas i nästa avsnitt kommer tillbaka n uppgifter. Arkets namn är Data; du kommer att behöva det om ett tag.

Bild A

Om funktionerna i Excel

Vi kommer att använda tre funktioner: SORT(), SEQUENCE() och INDEX(). De två första är dynamiska array-funktioner och ganska nya för Microsoft 365. INDEX() har funnits länge, och du kanske redan är bekant med det. Men först, vad är en dynamisk array-funktion?

Om du någonsin har skrivit in ett uttryck med Ctrl + Shift + Enter, är du redan bekant med hur Excel brukade arbeta med dynamiska arrayer. Tack vare den nya dynamiska array-funktionen kan dessa typer av uttryck vara mycket enklare att skapa och underhålla. Resultaten spill i cellerna nedan och fyll i så många som behövs för att slutföra uttryckets beräkningar. Det kallas det spillområde. Om du ser ett spillfel är intervallet som behövs för att uppfylla funktionen inte tillgängligt.

Nu till funktionerna.

SORT() returnerar en sorterad array med följande syntax:

SORT(array,[sort_index],[sort_order],[by_col])

där array är det enda nödvändiga argumentet och identifierar intervallet som ska sorteras. De valfria argumenten följer:

• sort_index: Ett numeriskt offsetvärde som identifierar raden eller kolumnen att sortera efter
• sort_order: Siffran 1 för stigande sortering eller -1 för fallande, där 1 är standard
• by_col: De logiska värdena TRUE för en radsortering och FALSE för en kolumnsortering, med TRUE som standard

För att returnera toppen eller botten n poster måste datamängden sorteras och vi använder SORT()-funktionen istället för en manuell rutt.

SEQUENCE() returnerar en serie värden med följande syntax:

=SEKVENS(rader,[columns],[start],[step])

där rader krävs och anger antalet rader som ska fyllas. De valfria argumenten följer:

• kolumn: antalet kolumner som ska returneras
• start: den första siffran i sekvensen
• steg: det belopp som ska ökas med

I sin enklaste form kan du använda den här funktionen för att returnera en serie fasta värden, men den lyser verkligen när du vill returnera alla kolumner i källdatauppsättningen. Genom att kombinera de två kan du returnera en fullständigt sorterad datamängd.

Den sista funktionen, INDEX(), returnerar ett värde eller referensen till ett värde från en tabell eller ett område med hjälp av syntaxen:

INDEX(matris, rad_nummer, [column_num])

där array krävs och refererar till ett intervall eller en arraykonstant. Om matrisen bara innehåller en rad eller kolumn, är motsvarande argument rad_num eller kolumn_num valfritt. Om matrisen har mer än en rad och mer än en kolumn, och endast radnummer eller kolumnnummer används, returnerar INDEX() en matris med hela raden eller kolumnen i matrisen. De två sista argumenten kan behövas eller inte:

• row_num krävs om inte kolumn_num finns. Den väljer raden i arrayen från vilken ett värde ska returneras. Om rad_nummer utelämnas krävs kolumnnummer.
• kolumn_num är valfritt och väljer kolumnen i matrisen från vilken ett värde ska returneras. Om kolumn_num utelämnas krävs radnummer.

Låt oss nu börja använda dessa funktioner, börja med SORT()

Hur man använder SORT() i Excel

För att returnera toppen eller botten n värden för valfri datamängd behöver du en sorterad datamängd. Tack vare SORT() är en manuell sortering inte nödvändig. För att se hur SORT() fungerar, låt oss använda den för att returnera en sorterad datamängd av exempeldatauppsättningen (Bild A). Först måste du kopiera kolumnrubrikerna till en annan del av arket, eller till och med ett annat ark. Jag kommer att använda ett annat ark, så att du kan se hur lätt detta uppnås. Kopiera rubrikcellerna B2:F2 till ett andra ark. Skriv sedan in i B3 (på det andra bladet) följande funktion:

=SORT(Data!B3:F13,2)

Bild B visar källdata sorterade efter fältet Värde. Så här fungerar argumenten i SORT()-funktionen:

• Data! är arknamnet där källdata finns.
• B3:F13 är den ursprungliga datamängden.
• 2 är indexvärdet, som anger kolumnen Värde – den andra kolumnen i B3:F13. Genom att ändra indexvärdet från 2 till 1, 3, 4 eller 5 kan du visa en eller alla kolumner.

Bild B

Var inte det lätt?

Om du väljer dessa referenser kommer Excel att visa strukturreferenser eftersom källdata är en tabell. Du vill formatera resultaten eftersom SORT()-funktionen inte gör det. Det är lite av en olägenhet när man försöker använda detta i en instrumentpanel. Du vill lägga till en VBA-procedur som tillämpar formateringen. Det här är den brist jag har stött på.

Det är en enkel sorts, men för att uppnå en topp- eller bottendatauppsättning måste du lägga till SEQUENCE() och INDEX().

Lösningen

Funktionen SORT() kan enkelt returnera hela datamängden i stigande eller fallande ordning. Vi vill också begränsa antalet poster som returneras, så vi lägger till SEQUENCE() och INDEX() i formuläret

=INDEX(SORT(array,sort_index,sort_order),SEQUENCE(inga_poster),SEQUENCE(första_kolumn,sista_kolumn))

Argumenten för den första SEQUENCE()-funktionen kommer att vara indatavärden, så att användaren kan ändra antalet poster som returneras. Det betyder att vi behöver indataceller. Använda sig av Bild C som en guide för att ställa in topp- och bottenintervall och indatacellintervall. Eftersom jag infogade rader överst för att rymma indatacellerna, kommer intervallreferenserna du har sett att vara annorlunda, så låt det inte förvirra dig.

Bild C

Låt oss börja med den översta datamängden, som kräver en fallande sortering, uttryckt med -1 i SORT()-funktionens sort_order-argument. I H6 anger du funktionen

=INDEX(SORT(B6:F16;2;-1);SEKVENS(I2);SEKVENS(1;5))

Det kommer att returnera ett beräkningsfel eftersom det inte finns något värde i I2—indatacellen. Ange 3 i I2 för att returnera datamängden som visas i Bild D. Du vet redan hur SORT()-funktionen fungerar; i det här fallet fungerar den som funktionen INDEX() arrayargument. SEKVENS(I2) är argumentet radnummer och returnerar 3 eftersom ingångsvärdet i I2 är 3. Följaktligen returnerar den en fallande datamängd med tre rader. SEKVENS(1,5) anger kolumnerna 1 till 5 i källdatauppsättningen. Allt tillsammans SORT() returnerar en fullständigt sorterad datamängd, men de två SEQUENCE()-funktionerna begränsar den till tre rader och inkluderar alla fem kolumner.

Bild D

Bottenfunktionen fungerar på liknande sätt, men den utelämnar sort_order-argumentet eftersom stigande (1) är standard. Dessutom refererar den till I3 som ingångscellen. Du kan bara använda en indatacell och låta dem båda referera till den, men på detta sätt kan båda datamängderna vara olika antal rader.

I N6 anger du funktionen

=INDEX(SORT(B6:F16;2);SEKVENS(I3);SEKVENS(1;5))

Muttrarna och bultarna är i stort sett desamma. Standardargumentet sort_order, som inte anges, returnerar en stigande ordning, så att du får de lägsta värdena överst i sorteringen. Genom att referera till I3 bestämmer du hur många rader som ska returneras. Bild E visar inmatningsvärdet 4. Följaktligen returnerar combo-funktionen alla fem kolumnerna för de första fyra raderna – de fyra nedersta värdena i Value.

Bild E

Till en början verkar allt lite mer komplext än du kanske gillar, men när du väl har blivit bekant med de nya dynamiska array-funktionerna, desto enklare blir dina lösningar för dig.

Värt att nämna

Jag nämnde tidigare att dessa funktioner kan returnera ett fel om spillområdet inte är tillgängligt. Om detta händer, välj hela spillområdet och ta bort allt – data, format, allt och se om det inte hjälper. Av särskild notering är sammanslagna celler. Av någon anledning rensar man inte bort dem helt. Du kan behöva flytta hela verken till ett nytt ark.

Håll ögonen öppna

Den enda begränsningen jag har märkt är funktionernas misslyckande att behålla formateringen. I en framtida artikel kommer jag att dela en VBA-procedur som tillämpar lämplig formatering för dig när du ändrar en indatacell.