Contents [show]
As u die lys moet sorteer, is daar baie maniere tot u diens, waarvan die maklikste die sorteerknoppies op die blad of in die spyskaart is. data (Data — Sorteer). Daar is egter situasies wanneer die sortering van die lys outomaties gedoen moet word, dws formules. Dit kan nodig wees, byvoorbeeld wanneer data vir 'n aftreklys gegenereer word, wanneer data vir kaarte bereken word, ens. Hoe om 'n lys met 'n formule op die vlieg te sorteer?
Metode 1. Numeriese data
As die lys slegs numeriese inligting bevat, kan dit maklik gesorteer word met behulp van die funksies MINSTENS (KLEIN) и LINE (RY):
funksie MINSTENS (KLEIN) trek die n-de kleinste element in 'n ry uit die skikking (kolom A). Dié. KLEIN(A:A;1) is die kleinste getal in die kolom, KLEIN(A:A;2) is die tweede kleinste, ensovoorts.
funksie LINE (RY) gee die rynommer vir die gespesifiseerde sel terug, dws RY(A1)=1, RY(A2)=2 ens. In hierdie geval word dit bloot gebruik as 'n genereerder van 'n reeks getalle n=1,2,3... vir ons gesorteerde lys. Met dieselfde sukses was dit moontlik om 'n bykomende kolom te maak, dit met die hand te vul met die numeriese ry 1,2,3 … en daarna te verwys in plaas van die RY-funksie.
Metode 2. Tekslys en gereelde formules
As die lys nie nommers bevat nie, maar teks, dan sal die SMALL-funksie nie meer werk nie, so jy moet 'n ander, effens langer, pad gaan.
Kom ons voeg eers 'n dienskolom by met 'n formule waar die reeksnommer van elke naam in die toekomstige gesorteerde lys met behulp van die funksie bereken sal word COUNTIF (COUNTIF):
In die Engelse weergawe sal dit wees:
=COUNTIF(A:A,»<"&A1)+COUNTIF($A$1:A1,"="&A1)
Die eerste term is 'n funksie om die aantal selle te tel wat minder as die huidige een is. Die tweede is 'n veiligheidsnet ingeval enige naam meer as een keer voorkom. Dan sal hulle nie dieselfde hê nie, maar agtereenvolgens toenemende getalle.
Nou moet die ontvangde nommers opeenvolgend in stygende volgorde gerangskik word. Hiervoor kan jy die funksie gebruik MINSTENS (KLEIN) vanaf die eerste manier:
Wel, uiteindelik bly dit net om die name volgens hul nommers uit die lys te trek. Om dit te doen, kan jy die volgende formule gebruik:
funksie MEER BLOOTSTELLING (MATCH) soek in kolom B na die verlangde reeksnommer (1, 2, 3, ens.) en gee in werklikheid die nommer van die reël waar hierdie nommer geleë is. Funksie INDEKS (INDEKS) trek uit kolom A die naam by hierdie reëlnommer uit.
Metode 3: Skikkingsformule
Hierdie metode is in werklikheid dieselfde plasingsalgoritme as in Metode-2, maar geïmplementeer deur 'n skikkingsformule. Om die formule te vereenvoudig, het die reeks selle C1:C10 die naam gekry lys (kies selle, druk Ctrl + F3 en knoppie Skep):
Kopieer ons formule in sel E1:
=INDEKS(Lys; MATCH(KLEIN(COUNTIF(Lys; "<"&Lys); RY(1:1)); COUNTIF(Lys; "<"&Lys; 0))
Of in die Engelse weergawe:
=INDEKS(Lys, MATCH(KLEIN(COUNTIF(Lys, «<"&Lys), RY(1:1)), COUNTIF(Lys, "<"&Lys), 0))
en druk Ctrl + Shift + Enterom dit as 'n skikkingsformule in te voer. Dan kan die resulterende formule oor die hele lengte van die lys gekopieer word.
As jy wil hê die formule moet nie 'n vaste reeks in ag neem nie, maar kan aanpas wanneer nuwe elemente by die lys gevoeg word, dan sal jy die strategie effens moet verander.
Eerstens sal die lysreeks dinamies gestel moet word. Om dit te doen, wanneer jy skep, moet jy nie 'n vaste reeks C3:C10 spesifiseer nie, maar 'n spesiale formule wat sal verwys na alle beskikbare waardes, ongeag hul nommer. Klik Alt + F3 of maak die oortjie oop Formules – Naambestuurder (Formules — Naambestuurder), skep 'n nuwe naam en in die veld Link (Verwysing) voer die volgende formule in (ek neem aan dat die reeks data wat gesorteer moet word by sel C1 begin):
=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)
=AFSET(C1,0,0;1;1000;SCHÖTZ(C1:CXNUMX);XNUMX)
Tweedens sal bogenoemde skikkingsformule met 'n marge uitgerek moet word - met die verwagting dat bykomende data in die toekoms ingevoer word. In hierdie geval sal die skikkingsformule 'n fout #NUMBER begin gee op selle wat nog nie gevul is nie. Om dit te onderskep, kan jy die funksie gebruik IFERROR, wat "om" ons skikkingsformule bygevoeg moet word:
=AANGESIEN(INDEX(Lys; MATCH(KLEIN(COUNTIF(Lys; "<"&Lys); RY(1:1)); COUNTIF(Lys; "<"&Lys; 0));»»)
=IFERROR(NDEX(Lys, MATCH(SMALL(COUNTIF(Lys, «<"&Lys), ROW(1:1)), COUNTIF(Lys, "<"&Lys), 0));"")
Dit vang die #NUMBER-fout op en voer eerder 'n leemte uit (leë aanhalingstekens).
:
- Sorteer reeks volgens kleur
- Wat is skikkingsformules en hoekom is dit nodig
- SORTEER sortering en dinamiese skikkings in die nuwe Office 365