Excel Sortera med en formel med SORT och SORTBY - Excel Tips

Den här veckan på Ignite Conference i Orlando Florida debuterade Microsoft en serie nya, enklare matrisformler i Excel. Jag kommer att täcka dessa nya formler varje dag denna vecka, men om du vill läsa framåt:

Måndag täckte den nya = A2: A20-formeln, SPILL-felet och den nya SINGLE-funktionen som krävs i stället för Implicit korsning
Idag kommer SORT och SORTBY att täckas
Onsdagen kommer att täcka FILTER
Torsdagen kommer att täcka UNIKT
Fredag täcker SEQUENCE och RANDARRAY-funktionerna

Sortering med en formel i Excel brukade kräva en galen kombination av formler. Ta en titt på dessa uppgifter som kommer att användas i den här artikeln.

Data i A3: C11.

För att sortera detta med en formel före den här veckan måste du bara slå ut RANK, COUNTIF, MATCH, INDEX och INDEX. När du är klar med denna uppsättning formler är du redo för en tupplur.

Det gamla sättet att sortera med en formel

Joe McDaid och hans team har gett oss SORT och SORTBY.

Låt oss börja med SORT. Här är syntaxen=SORT(Array, (Sort Index), (Sort Order), (By Column))

SORT-funktionen

Låt oss säga att du vill sortera A3: C16 efter fältet Poäng. Poäng är den tredje kolumnen i matrisen, så ditt sorteringsindex blir 3.

Alternativen för sorteringsordningen är 1 för stigande eller -1 för fallande. Jag klagar inte, men det kommer aldrig att finnas stöd för Sortera efter färg, Sortera efter formel eller Sortera efter anpassad lista med den här funktionen.

Ange 3 som sorteringskolumn och -1 som sorteringsordning för fallande.

Det fjärde argumentet kommer sällan att användas. Det är möjligt i sorteringsdialogen att sortera efter kolumn istället för rader. 99,9% av människor sorterar efter rader. Om du behöver sortera efter kolumn anger du True i det sista argumentet. Detta argument är valfritt och är som standard Falsk.

Om du behöver sortera efter kolumner, använd True i det fjärde argumentet

Här är resultaten av formeln. Tack vare den nya kalkmotorn sprids formeln i intilliggande celler. En formel i O2 producerar denna lösning.

Det finns inget behov av att trycka på Ctrl + Skift + Enter

Originaldata sorteras

Vad händer om du behöver sortera två nivåer? Sortera efter kolumn 2 stigande och kolumn 3 fallande? Ange en arraykonstant för det andra och tredje argumentet:=SORT(A2:C17,(2;3),(1;-1))

Två-nivå sortering

Med SORTBY-funktionen kan du sortera efter något som inte finns i resultaten

SORTBY-funktionens syntax är =SORTBY(array, by_array1, sort_order1,)

SORTBY något annat

Återgår till originaldata. Säg att du vill sortera efter lag och sedan göra poäng, men bara visa namnen. Du kan använda SORTBY som visas här.

Sortera kolumn A efter kolumn B och kolumn C

Slumpmässig drogtestning och slumpmässig utan upprepningar

Svåra scenarier som slumpmässig drogtestning och slumpmässiga utan upprepningar blir otroligt enkla när du kombinerar SORT med RANDARRAY.

I figuren nedan vill du sortera de 13 namnen slumpmässigt utan upprepningar. Använd =SORTBY(A4:A16,RANDARRAY(13)). Läs mer om RANDARRAY på fredagen.

Sortera slumpmässigt utan upprepningar

Är Ctrl + Skift + Enter helt död? Nej. Det finns fortfarande en användning för det. Låt oss säga att du bara ville ha de tre bästa resultaten från SORT-funktionen. Du kan välja tre celler, skriva SORT-funktionen och följa den med Ctrl + Shift + Enter. Detta kommer att förhindra att resultaten sprids utanför gränserna för den ursprungliga formeln.