Inhalter
Wann Dir d'Lëscht zortéiere musst, da ginn et vill Weeër fir Äre Service, déi einfachst ass d'Sortknäppchen op der Tab oder am Menü Daten (Daten - Sort). Et ginn awer Situatiounen, wou d'Sortéierung vun der Lëscht automatesch muss gemaach ginn, also Formelen. Dëst kann erfuerderlech sinn, zum Beispill, wann Dir Daten fir eng Dropdown-Lëscht generéiert, wann Dir Daten fir Charts berechnen, etc.. Wéi eng Lëscht mat enger Formel op der Flucht ze sortéieren?
Method 1. Numeresch Donnéeën
Wann d'Lëscht nëmmen numeresch Informatioun enthält, da kann d'Sortéierung et einfach mat de Funktiounen gemaach ginn LESCHT (KLEIN) и LINN (ROW):
Funktioun LESCHT (KLEIN) zitt aus der Array (Kolonn A) dat n-ten klengst Element an enger Zeil eraus. Déi. SMALL(A:A;1) ass déi klengst Zuel an der Kolonn, SMALL(A:A;2) ass déi zweet klengst, a sou weider.
Funktioun LINN (ROW) gëtt d'Zeilnummer fir déi spezifizéiert Zell zréck, dh ROW(A1)=1, ROW(A2)=2 etc. An dësem Fall gëtt se einfach als Generator vun enger Sequenz vun Zuelen n=1,2,3 benotzt... eis zortéiert Lëscht. Mat deemselwechte Succès war et méiglech eng zousätzlech Kolonn ze maachen, se manuell mat der numerescher Sequenz 1,2,3 ze fëllen ... a bezéie sech op et amplaz vun der ROW Funktioun.
Method 2. Text Lëscht a regelméisseg Formelen
Wann d'Lëscht keng Zuelen enthält, mee Text, da funktionnéiert d'SMALL Funktioun net méi, also musst Dir en anere Wee goen, e bësse méi laang.
Als éischt, loosst eis eng Servicekolonne mat enger Formel addéieren, wou d'Seriennummer vun all Numm an der zukünfteg zortéierter Lëscht mat der Funktioun berechent gëtt COUNTIF (COUNTIF):
An der englescher Versioun wäert et sinn:
=COUNTIF(A:A,»<"&A1)+COUNTIF($A$1:A1,"="&A1)
Den éischte Begrëff ass eng Funktioun fir d'Zuel vun den Zellen ze zielen déi manner sinn wéi déi aktuell. Déi zweet ass e Sécherheetsnetz am Fall wou en Numm méi wéi eemol geschitt. Da wäerten se net déiselwecht, awer successiv wuessend Zuelen hunn.
Elo mussen déi kritt Zuelen sequenziell an opsteigend Uerdnung arrangéiert ginn. Fir dëst kënnt Dir d'Funktioun benotzen LESCHT (KLEIN) vum éischte Wee:
Gutt, endlech bleift et just d'Nimm aus der Lëscht mat hiren Zuelen erauszezéien. Fir dëst ze maachen, kënnt Dir déi folgend Formel benotzen:
Funktioun MÉI ausgesat (MATCH) sicht an der Kolonn B no der gewënschter Seriennummer (1, 2, 3, asw.) a gëtt tatsächlech d'Zuel vun der Linn zréck, wou dës Zuel läit. Funktioun INDEX (INDEX) zitt aus der Kolonn A den Numm op dëser Linnnummer eraus.
Method 3: Array Formel
Dës Method ass tatsächlech deeselwechte Placementalgorithmus wéi an der Method-2, awer duerch eng Arrayformel ëmgesat. Fir d'Formel ze vereinfachen, gouf d'Gamme vun den Zellen C1:C10 den Numm kritt Lëscht (wielt Zellen, dréckt Ctrl + F3 a Knäppchen schafen):
An Zell E1, kopéiert eis Formel:
=INDEX(Lëscht; MATCH(SMALL(COUNTIF(Lëscht; "<"&Lëscht); ROW(1:1)); COUNTIF(Lëscht; "<"&Lëscht); 0))
Oder an der englescher Versioun:
=INDEX(Lëscht, MATCH(SMALL(COUNTIF(Lëscht, «<"&Lëscht), ROW(1:1)), COUNTIF(Lëscht, "<"&Lëscht), 0))
an dréckt Ctrl + Umeldung + Enterfir et als Arrayformel anzeginn. Da kann déi resultéierend Formel déi ganz Längt vun der Lëscht kopéiert ginn.
Wann Dir wëllt datt d'Formel net e fixe Beräich berücksichtegt, awer fäeg ass ajustéieren wann Dir nei Elementer op d'Lëscht bäidréit, da musst Dir d'Strategie liicht änneren.
Als éischt muss d'Lëschtberäich dynamesch agestallt ginn. Fir dëst ze maachen, wann Dir schafft, musst Dir net e fixe Beräich C3:C10 spezifizéieren, awer eng speziell Formel déi op all verfügbare Wäerter referéiert, onofhängeg vun hirer Zuel. Klickt Alt + F3 oder den Tab opmaachen Formulen - Numm Manager (Formelen - Numm Manager), en neien Numm erstellen an am Feld Link (Referenz) gitt déi folgend Formel un (ech huelen un datt d'Gamme vun den Donnéeën ze sortéieren fänkt vun der Zell C1 un):
=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)
=OFFSET(C1,0,0,SCHÖTZ(C1:C1000),1)
Zweetens, déi uewe genannte Arrayformel muss mat enger Marge ausgedehnt ginn - mat der Erwaardung vun zousätzlech Daten, déi an der Zukunft agefouert ginn. An dësem Fall fänkt d'Arrayformel un e Feeler #NUMBER op Zellen ze ginn déi nach net ausgefëllt sinn. Fir et z'ënnerscheeden, kënnt Dir d'Funktioun benotzen IFERROR, déi muss "ronderëm" eis Arrayformel bäigefüügt ginn:
=IFERROR(INDEX(Lëscht; MATCH(SMALL(COUNTIF(Lëscht; "<"&Lëscht); ROW(1:1)); COUNTIF(Lëscht; "<"&Lëscht); 0));»»)
=IFERROR(NDEX(Lëscht, MATCH(SMALL(COUNTIF(Lëscht, «<"&Lëscht), ROW(1:1)), COUNTIF(Lëscht, "<"&Lëscht), 0));"")
Et fënnt den #NUMBER Feeler an stellt amplaz en Void eraus (eidel Zitater).
:
- Sortéiert Gamme no Faarf
- Wat sinn Arrayformelen a firwat si se gebraucht
- SORT Sortéieren an dynamesch Arrays am neien Office 365