Kui teil on vaja loendit sorteerida, on teie käsutuses palju võimalusi, millest lihtsaim on sortimise nupud vahekaardil või menüüs kuupäev (Andmed — sortimine). Siiski on olukordi, kus loendi sorteerimine tuleb teha automaatselt ehk valemeid. Seda võib vaja minna näiteks rippmenüü jaoks andmete genereerimisel, diagrammide andmete arvutamisel jne. Kuidas sorteerida loendit valemiga käigu pealt?
Meetod 1. Numbrilised andmed
Kui loend sisaldab ainult numbrilist teavet, saab seda funktsioonide abil hõlpsalt sorteerida VÄHEMALT (VÄIKE) и LINE (RIDA):
funktsioon VÄHEMALT (VÄIKE) tõmbab massiivist (veerg A) välja n-nda väikseima elemendi reas. Need. VÄIKE(A:A;1) on väikseim arv veerus, VÄIKE(A:A;2) on suuruselt teine jne.
funktsioon LINE (RIDA) tagastab määratud lahtri reanumbri, st ROW(A1)=1, ROW(A2)=2 jne. Sel juhul kasutatakse seda lihtsalt numbrijada n=1,2,3… generaatorina meie sorteeritud loend. Sama eduga oli võimalik teha täiendav veerg, täita see käsitsi numbrijadaga 1,2,3 … ja viidata sellele funktsiooni ROW asemel.
Meetod 2. Tekstiloend ja tavavalemid
Kui loend sisaldab mitte numbreid, vaid teksti, siis funktsioon VÄIKE enam ei tööta, seega tuleb minna teist, veidi pikemat teed.
Esmalt lisame teenuse veerg valemiga, kus iga nime seerianumber tulevases järjestatud loendis arvutatakse funktsiooni abil COUNTIF (COUNTIF):
Ingliskeelses versioonis on see:
=LOENDIF(A:A,»<"&A1)+COUNTIF($A$1:A1,"="&A1)
Esimene liige on funktsioon praegusest väiksemate lahtrite arvu loendamiseks. Teine on turvavõrk juhuks, kui mõni nimi esineb rohkem kui üks kord. Siis ei ole neil sama, vaid järjest suurenev arv.
Nüüd tuleb saadud numbrid järjestada kasvavas järjekorras. Selleks saate kasutada funktsiooni VÄHEMALT (VÄIKE) esimesest viisist:
Noh, lõpuks jääb üle vaid nimed nende numbrite järgi loendist välja tõmmata. Selleks võite kasutada järgmist valemit:
funktsioon ROHKEM AVALDATUD (MATCH) otsib veerust B soovitud seerianumbrit (1, 2, 3 jne) ja tagastab tegelikult selle rea numbri, kus see number asub. Funktsioon INDEX (INDEX) tõmbab veerust A välja selle reanumbri nime.
3. meetod: massiivi valem
See meetod on tegelikult sama paigutusalgoritm, mis meetodis 2, kuid seda rakendatakse massiivivalemi abil. Valemi lihtsustamiseks anti lahtrite vahemikule C1:C10 nimi nimekiri (valige lahtrid, vajutage Ctrl + F3 ja nuppu Looma):
Kopeerige lahtrisse E1 meie valem:
=INDEKS(loend; MATCH(VÄIKE(LOENDIF(loend; “<"&loend); ROW(1:1)); COUNTIF(loend; "<"&loend); 0))
Või ingliskeelses versioonis:
=INDEKS(loend, MATCH(VÄIKE(LOENDIF(loend, «<"&loend), ROW(1:1)), COUNTIF(loend, "<"&loend), 0))
ja lükake Ctrl + Tõstuklahv + Sisestaet sisestada see massiivivalemina. Seejärel saab saadud valemi kogu loendi pikkuses alla kopeerida.
Kui soovite, et valem ei võtaks arvesse kindlat vahemikku, vaid saaks loendisse uute elementide lisamisel kohandada, peate strateegiat veidi muutma.
Esiteks tuleb loendi vahemik dünaamiliselt seadistada. Selleks peate loomisel määrama mitte fikseeritud vahemiku C3:C10, vaid spetsiaalse valemi, mis viitab kõigile saadaolevatele väärtustele, olenemata nende arvust. Klõpsake Alt + F3 või avage vahekaart Valemid – nimehaldur (Valemid – nimehaldur), looge uus nimi ja väljale on siin (Viide) sisestage järgmine valem (oletan, et sorteeritavate andmete vahemik algab lahtrist C1):
=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)
=OFFSET(C1,0,0;1;1000;SCHÖTZ(C1:CXNUMX),XNUMX)
Teiseks tuleb ülaltoodud massiivivalemit veerisega alla venitada – eeldades, et tulevikus sisestatakse täiendavaid andmeid. Sel juhul hakkab massiivivalem andma veateadet #NUMBER lahtrites, mis pole veel täidetud. Selle pealtkuulamiseks võite kasutada funktsiooni VIGA, mis tuleb lisada meie massiivi valemi ümber:
=IFERROR(INDEX(loend; MATCH(VÄIKE(COUNTIF(loend; “<”&loend); ROW(1:1)); COUNTIF(loend; "<"&loend; 0));»»)
=IFERROR(NDEX(loend, MATCH(VÄIKE(LOENDIF(loend, «<"&loend), ROW(1:1)), COUNTIF(loend, "<"&loend), 0));"")
See tabab vea #NUMBER ja väljastab selle asemel tühiku (tühjad jutumärgid).
:
- Sorteeri vahemik värvi järgi
- Mis on massiivivalemid ja miks neid vaja on
- SORTERI sorteerimine ja dünaamilised massiivid uues Office 365-s