Excel razvrsti s formulo z uporabo SORT in SORTBY - Excel Nasveti

Ta teden je Microsoft na konferenci Ignite v Orlandu Florida predstavil vrsto novih, lažjih formul matrike v Excelu. Te nove formule bom pokrival vsak dan v tem tednu, če pa bi radi prebrali naprej:

Ponedeljek je zajemal novo = A2: formulo A20, napako SPILL in novo funkcijo ENO, ki je potrebna namesto implicitnega križišča
Danes bomo pokrivali SORT in SORTBY
Sreda bo zajela FILTER
Četrtek bo zajemal ENOTEN
Petek bo zajemal funkcije SEQUENCE in RANDARRAY

Razvrščanje s formulo v Excelu je nekoč zahtevalo noro kombinacijo formul. Oglejte si te podatke, ki bodo uporabljeni v tem članku.

Podatki v A3: C11.

Če želite to razvrstiti s formulo pred tem tednom, morate samo izničiti RANK, COUNTIF, MATCH, INDEX in INDEX. Ko končate s tem nizom formul, bi bili pripravljeni na dremež.

Stari način razvrščanja s formulo

Joe McDaid in njegova ekipa so nam prinesli SORT in SORTBY.

Začnimo s SORT. Tu je sintaksa=SORT(Array, (Sort Index), (Sort Order), (By Column))

Funkcija SORT

Recimo, da želite razvrstiti A3: C16 po polju Rezultat. Rezultat je tretji stolpec v matriki, zato bo vaš indeks razvrščanja 3.

Za vrstni red razvrščanja lahko izbirate med 1 za naraščajočo ali -1 za padajočo. Ne pritožujem se, vendar s to funkcijo nikoli ne bo podpore za Razvrsti po barvi, Razvrsti po formuli ali Razvrsti po seznamu po meri.

Določite 3 kot stolpec za razvrščanje in -1 kot vrstni red razvrščanja za padajoče.

Četrti argument se bo uporabljal redko. V pogovornem oknu Razvrsti je mogoče razvrstiti po stolpcu namesto po vrsticah. 99,9% ljudi razvršča po vrstah. Če morate razvrstiti po stolpcu, v zadnjem argumentu podajte True. Ta argument ni obvezen, privzeto pa je False.

Če morate razvrstiti po stolpcih, v četrtem argumentu uporabite True

Tu so rezultati formule. Po zaslugi novega kalcinskega motorja se formula razlije v sosednje celice. Ena formula v O2 proizvaja to raztopino.

Ni treba pritisniti Ctrl + Shift + Enter

Izvirni podatki so razvrščeni

Kaj če potrebujete dvostopenjsko razvrščanje? Razvrsti po stolpcu naraščajoče in stolpcu 3 padajoče? Navedite konstanto matrike za 2. in 3. argument:=SORT(A2:C17,(2;3),(1;-1))

Dvostopenjsko razvrščanje

Funkcija SORTBY vam omogoča razvrščanje po nečem, kar ni v rezultatih

Sintaksa funkcije SORTBY je =SORTBY(array, by_array1, sort_order1,)

UREDITE še kaj

Vrnitev na prvotne podatke. Recimo, da želite razvrstiti po moštvu, nato pa rezultat, vendar samo imena. Lahko uporabite SORTBY, kot je prikazano tukaj.

Razvrsti stolpec A po stolpcu B in stolpcu C

Naključno testiranje drog in naključno brez ponovitev

Težki scenariji, kot sta naključno testiranje drog in naključno brez ponovitev, postanejo osupljivo preprosti, če kombinirate SORT in RANDARRAY.

Na spodnji sliki želite 13 imen razvrstiti naključno brez ponovitev. Uporabi =SORTBY(A4:A16,RANDARRAY(13)). Preberite več o RANDARRAY v petek.

Razvrščanje naključno brez ponovitev

Je Ctrl + Shift + Enter popolnoma mrtev? Ne. Še vedno se uporablja. Recimo, da ste želeli samo 3 najboljše rezultate iz funkcije SORT. Lahko izberete tri celice, vtipkate funkcijo SORT in ji sledite s Ctrl + Shift + Enter. To bo preprečilo, da bi se rezultati prelivali čez meje prvotne formule.