Saturs
Ja jums ir nepieciešams sakārtot sarakstu, jūsu rīcībā ir daudz veidu, no kuriem vienkāršākais ir kārtošanas pogas cilnē vai izvēlnē Datums (Dati — Kārtot). Taču ir situācijas, kad saraksta kārtošana jāveic automātiski, proti, formulas. Tas var būt nepieciešams, piemēram, ģenerējot datus nolaižamajam sarakstam, aprēķinot datus diagrammām utt. Kā kārtot sarakstu ar formulu lidojuma laikā?
1. metode. Skaitliskie dati
Ja sarakstā ir tikai skaitliska informācija, tad to var viegli sakārtot, izmantojot funkcijas Vismazāk (MAZS) и LINE (ROW):
funkcija Vismazāk (MAZS) izvelk no masīva (A ailes) n-to mazāko elementu pēc kārtas. Tie. SMALL(A:A;1) ir mazākais skaitlis kolonnā, MAZAIS(A:A;2) ir otrs mazākais utt.
funkcija LINE (ROW) atgriež rindas numuru norādītajai šūnai, ti, ROW(A1)=1, ROW(A2)=2 utt. Šajā gadījumā to izmanto vienkārši kā skaitļu virknes ģeneratoru n=1,2,3… mūsu sakārtotais saraksts. Ar tādiem pašiem panākumiem bija iespējams izveidot papildu kolonnu, aizpildīt to manuāli ar ciparu secību 1,2,3 … un atsaukties uz to funkcijas ROW vietā.
2. metode. Tekstu saraksts un regulāras formulas
Ja sarakstā ir nevis skaitļi, bet teksts, tad funkcija MAZAIS vairs nedarbosies, tāpēc jāiet cits, nedaudz garāks ceļš.
Vispirms pievienosim pakalpojumu kolonnu ar formulu, kurā tiks aprēķināts katra nosaukuma kārtas numurs turpmākajā sakārtotajā sarakstā, izmantojot funkciju COUNTIF (COUNTIF):
Angļu valodas versijā tas būs:
=SKAITĪT, JA(A:A,»<"&A1)+SKAITS ($1:A1,"="&A1)
Pirmais termins ir funkcija to šūnu skaita skaitīšanai, kas ir mazākas par pašreizējo. Otrais ir drošības tīkls gadījumam, ja kāds vārds parādās vairāk nekā vienu reizi. Tad viņiem nebūs vienādi, bet secīgi pieaugoši skaitļi.
Tagad saņemtie numuri ir jāsakārto secīgi augošā secībā. Šim nolūkam varat izmantot funkciju Vismazāk (MAZS) no pirmā ceļa:
Visbeidzot, atliek tikai izvilkt vārdus no saraksta pēc to numuriem. Lai to izdarītu, varat izmantot šādu formulu:
funkcija VAIRĀK ATKLĀTA (MATCH) ailē B meklē vajadzīgo sērijas numuru (1, 2, 3 utt.) un faktiski atgriež rindas numuru, kurā atrodas šis numurs. Funkcija INDEX (INDEX) izvelk no A kolonnas nosaukumu pie šīs rindas numura.
3. metode: masīva formula
Šī metode faktiski ir tāds pats izvietošanas algoritms kā 2. metodē, taču to īsteno ar masīva formulu. Lai vienkāršotu formulu, šūnu diapazonam C1:C10 tika piešķirts nosaukums saraksts (atlasiet šūnas, nospiediet Ctrl + F3 un pogu izveidot):
Šūnā E1 kopējiet mūsu formulu:
=INDEKSS(saraksts; MATCH(SMALL(COUNTIF(saraksts; “<"&saraksts); ROW(1:1)); COUNTIF(saraksts; "<"&saraksts); 0))
Vai arī angļu versijā:
=INDEKSS(saraksts, ATBILSTĪBA(SMALL(SKAITS(saraksts, «<"&saraksts), ROW(1:1)), COUNTIF(saraksts, "<"&saraksts), 0))
un spied Ctrl + Shift + Enterlai ievadītu to kā masīva formulu. Pēc tam iegūto formulu var kopēt visā saraksta garumā.
Ja vēlaties, lai formula ņemtu vērā nevis fiksētu diapazonu, bet varētu pielāgoties, pievienojot sarakstam jaunus elementus, tad jums būs nedaudz jāmaina stratēģija.
Pirmkārt, saraksta diapazons būs jāiestata dinamiski. Lai to izdarītu, veidojot, jānorāda nevis fiksēts diapazons C3:C10, bet gan īpaša formula, kas atsauksies uz visām pieejamajām vērtībām neatkarīgi no to skaita. Klikšķis Alt+F3 vai atveriet cilni Formulas – vārdu pārvaldnieks (Formulas — vārdu pārvaldnieks), izveidojiet jaunu nosaukumu un laukā saite (Atsauce) ievadiet šādu formulu (es pieņemu, ka kārtojamo datu diapazons sākas no šūnas C1):
=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)
=OFFSET(C1,0,0,SCHÖTZ(C1:C1000),1)
Otrkārt, iepriekš minētā masīva formula būs jāpavelk uz leju ar rezervi – ar cerībām, ka nākotnē tiks ievadīti papildu dati. Šādā gadījumā masīva formula sāks rādīt kļūdu #NUMBER šūnās, kas vēl nav aizpildītas. Lai to pārtvertu, varat izmantot funkciju IFERROR, kas jāpievieno "ap" mūsu masīva formulu:
=IFERROR(INDEX(saraksts; MATCH(SMALL(COUNTIF(saraksts; “<"&saraksts); ROW(1:1)); COUNTIF(saraksts; "<"&saraksts); 0));»»)
=IFERROR(NDEX(saraksts, ATBILSTĪBA(SMALL(SKAITS(saraksts, «<"&saraksts), ROW(1:1)), COUNTIF(saraksts, "<"&saraksts), 0));"")
Tas uztver kļūdu #NUMBER un tā vietā izvada tukšumu (tukšas pēdiņas).
:
- Kārtot diapazonu pēc krāsas
- Kas ir masīvu formulas un kāpēc tās ir vajadzīgas
- KĀRTOT kārtošanu un dinamiskos masīvus jaunajā Office 365