Excel Sorter med en formel ved hjelp av SORT og SORTBY - Excel-tips

Denne uken på Ignite Conference i Orlando Florida debuterte Microsoft en serie nye, enklere matriseformler i Excel. Jeg vil dekke disse nye formlene hver dag denne uken, men hvis du vil lese fremover:

Mandag dekket den nye = A2: A20-formelen, SPILL-feilen og den nye SINGLE-funksjonen som kreves i stedet for Implisitt kryss
I dag dekker SORT og SORTBY
Onsdag vil dekke FILTER
Torsdag vil dekke UNIKT
Fredag dekker funksjonene SEKVENS og RANDARRAY

Sortering med en formel i Excel pleide å kreve en vanvittig kombinasjon av formler. Ta en titt på disse dataene som vil bli brukt gjennom denne artikkelen.

Data i A3: C11.

For å sortere dette med en formel før denne uken, trenger du bare å slå ut RANK, ANTALL, MATCH, INDEKS og INDEKS. Når du er ferdig med dette settet med formler, vil du være klar for en lur.

Den gamle måten å sortere på med en formel

Joe McDaid og teamet hans har gitt oss SORT og SORTBY.

La oss starte med SORT. Her er syntaksen=SORT(Array, (Sort Index), (Sort Order), (By Column))

SORT-funksjonen

La oss si at du vil sortere A3: C16 etter Score-feltet. Score er den tredje kolonnen i matrisen, så Sorteringsindeksen din blir 3.

Valgene for sorteringsrekkefølgen er 1 for stigende eller -1 for synkende. Jeg klager ikke, men det vil aldri være støtte for Sorter etter farge, Sorter etter formel eller Sorter etter tilpasset liste ved hjelp av denne funksjonen.

Spesifiser 3 som sorteringskolonne og -1 som sorteringsrekkefølge for synkende.

Det fjerde argumentet kommer sjelden til å bli brukt. Det er mulig i sorteringsdialogen å sortere etter kolonne i stedet for rader. 99,9% av folk sorterer etter rader. Hvis du trenger å sortere etter kolonne, angir du True i det endelige argumentet. Dette argumentet er valgfritt og er som standard Falsk.

Hvis du trenger å sortere etter kolonner, bruker du True i det fjerde argumentet

Her er resultatene av formelen. Takket være den nye kalkmotoren, spills formelen inn i tilstøtende celler. En formel i O2 produserer denne løsningen.

Det er ikke nødvendig å trykke Ctrl + Shift + Enter

Originaldataene er sortert

Hva om du trenger en to-nivå sortering? Sorter etter kolonne 2 stigende og kolonne 3 synkende? Gi en matrisekonstant for 2. og 3. argument:=SORT(A2:C17,(2;3),(1;-1))

To-nivå sortering

SORTBY-funksjonen lar deg sortere etter noe som ikke er i resultatene

SORTBY-funksjonens syntaks er =SORTBY(array, by_array1, sort_order1,)

SORT noe annet

Gå tilbake til de opprinnelige dataene. Si at du vil sortere etter lag og deretter score, men bare vis navnene. Du kan bruke SORTBY som vist her.

Sorter kolonne A etter kolonne B og kolonne C

Tilfeldig stofftesting og tilfeldig uten gjentakelser

Vanskelige scenarier som tilfeldig legemiddeltesting og tilfeldig uten gjentakelser blir nummbingingly enkel når du kombinerer SORT med RANDARRAY.

I figuren nedenfor vil du sortere de 13 navnene tilfeldig uten gjentakelser. Bruk =SORTBY(A4:A16,RANDARRAY(13)). Les mer om RANDARRAY på fredag.

Sortering tilfeldig uten gjentakelser

Er Ctrl + Shift + Enter helt død? Nei. Det er fortsatt bruk for det. La oss si at du bare ønsket de tre beste resultatene fra SORT-funksjonen. Du kan velge tre celler, skrive SORT-funksjonen og følge den med Ctrl + Shift + Enter. Dette vil forhindre at resultatene spills utenfor grensene for den opprinnelige formelen.