Os oes angen i chi ddidoli'r rhestr, yna mae yna lawer o ffyrdd yn eich gwasanaeth, a'r hawsaf yw'r botymau didoli ar y tab neu yn y ddewislen Dyddiad (Data - Trefnu). Fodd bynnag, mae sefyllfaoedd pan fydd angen didoli’r rhestr yn awtomatig, h.y. fformiwlâu. Efallai y bydd angen hyn, er enghraifft, wrth gynhyrchu data ar gyfer cwymprestr, wrth gyfrifo data ar gyfer siartiau, ac ati. Sut i ddidoli rhestr gyda fformiwla ar y hedfan?
Dull 1. Data rhifol
Os yw'r rhestr yn cynnwys gwybodaeth rifiadol yn unig, yna gellir ei didoli'n hawdd gan ddefnyddio'r swyddogaethau LEAST (BACH) и LLINELL (ROW):
swyddogaeth LEAST (BACH) yn tynnu allan o'r arae (colofn A) yr n-fed elfen leiaf yn olynol. Y rhai. SMALL(A:A; 1) yw'r nifer lleiaf yn y golofn, SMALL(A:A; 2) yw'r ail leiaf, ac ati.
swyddogaeth LLINELL (ROW) yn dychwelyd y rhif rhes ar gyfer y gell benodedig, h.y. ROW(A1)=1, ROW(A2)=2 ac ati. Yn yr achos hwn, fe'i defnyddir yn syml fel generadur o ddilyniant o rifau n=1,2,3… ar gyfer ein rhestr wedi'i didoli. Gyda'r un llwyddiant, bu modd gwneud colofn ychwanegol, ei llenwi â llaw gyda'r dilyniant rhifiadol 1,2,3 … a chyfeirio ati yn lle'r ffwythiant HT.
Dull 2. Rhestr testun a fformiwlâu rheolaidd
Os nad yw'r rhestr yn cynnwys rhifau, ond testun, yna ni fydd y swyddogaeth BACH yn gweithio mwyach, felly mae'n rhaid i chi fynd ar lwybr gwahanol, ychydig yn hirach.
Yn gyntaf, gadewch i ni ychwanegu colofn gwasanaeth gyda fformiwla lle bydd rhif cyfresol pob enw yn y rhestr didoli yn y dyfodol yn cael ei gyfrifo gan ddefnyddio'r swyddogaeth COUNTIF (COUNTIF):
Yn y fersiwn Saesneg bydd yn:
=COUNTIF(A:A,»<"&A1)+COUNTIF($A$1:A1,"="&A1)
Mae'r term cyntaf yn swyddogaeth ar gyfer cyfrif nifer y celloedd sy'n llai na'r un gyfredol. Mae'r ail yn rhwyd ddiogelwch rhag ofn i unrhyw enw ddigwydd fwy nag unwaith. Yna ni fydd ganddynt yr un, ond niferoedd cynyddol yn olynol.
Nawr mae'n rhaid i'r niferoedd a dderbyniwyd gael eu trefnu'n ddilyniannol mewn trefn esgynnol. Ar gyfer hyn gallwch ddefnyddio'r swyddogaeth LEAST (BACH) o'r ffordd gyntaf:
Wel, yn olaf, dim ond tynnu'r enwau allan o'r rhestr yn ôl eu niferoedd. I wneud hyn, gallwch ddefnyddio'r fformiwla ganlynol:
swyddogaeth MWY AMLWG (MATCH) yn chwilio yng ngholofn B am y rhif cyfresol dymunol (1, 2, 3, ac ati) ac, mewn gwirionedd, yn dychwelyd rhif y llinell lle mae'r rhif hwn. Swyddogaeth MYNEGAI (MYNEGAI) yn tynnu allan o golofn A yr enw yn y rhif llinell hwn.
Dull 3: Fformiwla Arae
Mae'r dull hwn, mewn gwirionedd, yr un algorithm lleoli ag yn Method-2, ond yn cael ei weithredu gan fformiwla arae. I symleiddio'r fformiwla, rhoddwyd yr enw i'r ystod o gelloedd C1:C10 rhestr (dewiswch gelloedd, gwasgwch Ctrl + F3 a botwm Creu):
Yng nghell E1, copïwch ein fformiwla:
=MYNEGAI(Rhestr; MATCH(SMALL(COUNTIF(Rhestr; “<"&Rhestr); ROW(1:1)); COUNTIF(Rhestr; "<"&Rhestr); 0))
Neu yn y fersiwn Saesneg:
=MYNEGAI(Rhestr, MATCH(BACH(COUNTIF(Rhestr, «<"&Rhestr), ROW(1:1)), COUNTIF(Rhestr,"<"&Rhestr), 0))
a gwthio Ctrl + Shift + Enteri'w nodi fel fformiwla arae. Yna gellir copïo'r fformiwla ddilynol i lawr hyd cyfan y rhestr.
Os ydych chi am i'r fformiwla gymryd i ystyriaeth nid ystod sefydlog, ond yn gallu addasu wrth ychwanegu elfennau newydd at y rhestr, yna bydd angen i chi newid y strategaeth ychydig.
Yn gyntaf, bydd angen gosod yr ystod Rhestr yn ddeinamig. I wneud hyn, wrth greu, mae angen i chi nodi nid ystod sefydlog C3: C10, ond fformiwla arbennig a fydd yn cyfeirio at yr holl werthoedd sydd ar gael, waeth beth fo'u rhif. Cliciwch Alt + F3 neu agor y tab Fformiwlâu - Rheolwr Enw (Fformiwlâu - Rheolwr Enw), creu enw newydd ac yn y maes Cyswllt (Cyfeirnod) rhowch y fformiwla ganlynol (rwy'n cymryd bod yr ystod o ddata sydd i'w didoli yn cychwyn o gell C1):
=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)
=GOFFSET(C1,0,0,SCHÖTZ(C1:C1000),1)
Yn ail, bydd angen ymestyn y fformiwla arae uchod gydag ymyl – gan ddisgwyl i ddata ychwanegol gael ei gofnodi yn y dyfodol. Yn yr achos hwn, bydd y fformiwla arae yn dechrau rhoi gwall #NUMBER ar gelloedd nad ydynt wedi'u llenwi eto. Er mwyn ei ryng-gipio, gallwch ddefnyddio'r swyddogaeth IFERROR, y mae angen ei ychwanegu “o gwmpas” ein fformiwla arae:
=IFERROR(MYNEGAI(Rhestr; MATCH(BACH(COUNTIF(Rhestr; “<"&Rhestr); ROW(1:1)); COUNTIF(Rhestr; "<"&Rhestr); 0));»»)
=IFERROR(NDEX(Rhestr, MATCH(SMALL(COUNTIF(Rhestr, «<"&Rhestr), ROW(1:1)), COUNTIF(Rhestr, "<"&Rhestr), 0));"")
Mae'n dal y gwall #NUMBER ac yn allbynnu bwlch (dyfynbrisiau gwag) yn lle hynny.
:
- Trefnu ystod yn ôl lliw
- Beth yw fformiwlâu arae a pham mae eu hangen
- Trefnu SORT ac araeau deinamig yn yr Office 365 newydd