Cynnwys
Holl swyddogaethau chwilio clasurol a math amnewid VPR (VLOOKUP), GPR (HLOOKUP), MWY AMLWG (MATCH) ac mae gan rai tebyg un nodwedd bwysig - maent yn chwilio o'r dechrau i'r diwedd, hy o'r chwith i'r dde neu o'r brig i'r gwaelod yn y data ffynhonnell. Cyn gynted ag y bydd y paru cyntaf yn cael ei ddarganfod, mae'r chwiliad yn stopio a dim ond digwyddiad cyntaf yr elfen sydd ei angen arnom sy'n cael ei ddarganfod.
Beth i'w wneud os bydd angen i ni ddod o hyd nid y cyntaf, ond y digwyddiad olaf? Er enghraifft, y trafodiad olaf ar gyfer y cleient, y taliad olaf, y gorchymyn mwyaf diweddar, ac ati?
Dull 1: Dod o Hyd i'r Rhes Olaf gyda Fformiwla Arae
Os nad oes gan y tabl gwreiddiol golofn gyda dyddiad neu rif cyfresol o res (gorchymyn, taliad ...), yna ein tasg, mewn gwirionedd, yw dod o hyd i'r rhes olaf sy'n bodloni'r amod a roddwyd. Gellir gwneud hyn gyda'r fformiwla arae ganlynol:
yma:
- swyddogaeth IF (OS) yn gwirio pob cell mewn colofn fesul un Cwsmeriaid ac yn dangos rhif y llinell os yw'n cynnwys yr enw sydd ei angen arnom. Rhoddir y rhif llinell ar y ddalen i ni gan y ffwythiant LLINELL (ROW), ond gan fod angen rhif y rhes yn y tabl, mae'n rhaid i ni hefyd dynnu 1, oherwydd mae gennym bennawd yn y tabl.
- Yna y swyddogaeth MAX (MAX) yn dewis y gwerth mwyaf o'r set ffurfiedig o rifau rhes, hy rhif llinell fwyaf diweddar y cleient.
- swyddogaeth MYNEGAI (MYNEGAI) yn dychwelyd cynnwys y gell gyda'r rhif olaf a ddarganfuwyd o unrhyw golofn tabl gofynnol arall (cod archeb).
Rhaid nodi hyn i gyd fel fformiwla arae, hy:
- Yn Office 365 gyda'r diweddariadau diweddaraf wedi'u gosod a chefnogaeth ar gyfer araeau deinamig, gallwch chi wasgu'n syml Rhowch.
- Ym mhob fersiwn arall, ar ôl mynd i mewn i'r fformiwla, bydd yn rhaid i chi wasgu'r llwybr byr bysellfwrdd Ctrl+Symud+Rhowch, a fydd yn ychwanegu braces cyrliog ato yn awtomatig yn y bar fformiwla.
Dull 2: Chwilio yn ôl gyda'r swyddogaeth LOOKUP newydd
Rwyf eisoes wedi ysgrifennu erthygl hir gyda fideo am nodwedd newydd GWELD (XLOOKUP), a ymddangosodd yn y fersiynau diweddaraf o Office i gymryd lle'r hen VLOOKUP (VLOOKUP). Gyda chymorth BROWSE, mae ein tasg yn cael ei datrys yn eithaf elfennol, oherwydd. ar gyfer y swyddogaeth hon (yn wahanol i VLOOKUP), gallwch osod y cyfeiriad chwilio yn benodol: o'r brig i lawr neu o'r gwaelod i fyny - ei ddadl olaf (-1) sy'n gyfrifol am hyn:
Dull 3. Chwiliwch am linyn gyda'r dyddiad diweddaraf
Os oes gennym ni yn y data ffynhonnell golofn gyda rhif cyfresol neu ddyddiad sy'n chwarae rôl debyg, yna mae'r dasg yn cael ei haddasu - mae angen i ni ddarganfod nid y llinell olaf (isaf) gyda chyfateb, ond y llinell gyda'r diweddaraf ( uchafswm) dyddiad.
Rwyf eisoes wedi trafod yn fanwl sut i wneud hyn gan ddefnyddio swyddogaethau clasurol, a nawr gadewch i ni geisio defnyddio pŵer y swyddogaethau arae deinamig newydd. Er mwy o harddwch a hwylustod, rydym hefyd yn trosi'r tabl gwreiddiol yn dabl “clyfar” gan ddefnyddio llwybr byr bysellfwrdd Ctrl+T neu orchmynion Cartref - Fformat fel bwrdd (Cartref - Fformat fel Tabl).
Gyda’u cymorth nhw, mae’r “cwpl llofrudd” hwn yn datrys ein problem yn osgeiddig iawn:
yma:
- Swyddogaeth yn gyntaf Hidlo (HILYDD) yn dewis y rhesi hynny yn unig o'n tabl lle yn y golofn Cwsmeriaid – yr enw sydd ei angen arnom.
- Yna y swyddogaeth GRADD (SORT) yn trefnu'r rhesi a ddewiswyd yn ôl dyddiad mewn trefn ddisgynnol, gyda'r fargen ddiweddaraf ar y brig.
- swyddogaeth MYNEGAI (MYNEGAI) yn tynnu'r rhes gyntaf, hy yn dychwelyd y fasnach olaf sydd ei hangen arnom.
- Ac, yn olaf, mae'r swyddogaeth FILTER allanol yn tynnu'r colofnau 1af a 3ydd ychwanegol o'r canlyniadau (cod archeb и Cwsmeriaid) ac yn gadael y dyddiad a'r swm yn unig. Ar gyfer hyn, defnyddir amrywiaeth o gysonion. {0;1;0;1}, gan ddiffinio pa golofnau yr ydym am (1) neu nad ydym am i (0) eu harddangos.
Dull 4: Dod o Hyd i'r Gêm Olaf mewn Ymholiad Pŵer
Wel, er mwyn cyflawnrwydd, gadewch i ni edrych ar ateb i'n problem chwilio o chwith gan ddefnyddio'r ategyn Power Query. Gyda'i help, mae popeth yn cael ei ddatrys yn gyflym iawn ac yn hyfryd.
1. Gadewch i ni drosi ein tabl gwreiddiol yn un “clyfar” gan ddefnyddio llwybr byr bysellfwrdd Ctrl+T neu orchmynion Cartref - Fformat fel bwrdd (Cartref - Fformat fel Tabl).
2. Llwythwch ef i Power Query gyda'r botwm O'r Tabl / Ystod tab Dyddiad (Data — O Dabl/Ystod).
3. Rydyn ni'n didoli (trwy gwymplen yr hidlydd yn y pennyn) ein tabl yn nhrefn dyddiad disgynnol, fel bod y trafodion diweddaraf ar y brig.
4… Yn y tab Trawsnewid dewis tîm Grŵp erbyn (Trawsnewid - Grŵp Gan) a gosod y grwpio yn ôl cwsmeriaid, ac fel swyddogaeth agregu, dewiswch yr opsiwn Pob llinell (Pob rhes). Gallwch enwi'r golofn newydd unrhyw beth rydych chi'n ei hoffi - er enghraifft manylion.
Ar ôl grwpio, byddwn yn cael rhestr o enwau unigryw ein cleientiaid ac yn y golofn manylion - tablau gyda holl drafodion pob un ohonynt, a'r llinell gyntaf fydd y trafodiad diweddaraf, sef yr hyn sydd ei angen arnom:
5. Ychwanegwch golofn gyfrifo newydd gyda'r botwm Colofn personol tab Ychwanegu colofn (Ychwanegu colofn - Ychwanegu colofn arferiad)a rhowch y fformiwla ganlynol:
Yma manylion – dyma’r golofn yr ydym yn cymryd tablau ohoni gan gwsmeriaid, a 0 {} yw rhif y rhes yr ydym am ei dynnu (mae rhif y rhes yn Power Query yn dechrau o sero). Rydyn ni'n cael colofn gyda chofnodion (cofnod), lle mae pob cofnod yn rhes gyntaf o bob tabl:
Erys i ehangu cynnwys pob cofnod gyda'r botwm gyda saethau dwbl ym mhennyn y golofn Bargen olaf dewis y colofnau dymunol:
… ac yna dilëwch y golofn nad oes ei hangen mwyach manylion trwy dde-glicio ar ei deitl - Tynnwch y colofnau (Dileu colofnau).
Ar ôl uwchlwytho'r canlyniadau i'r ddalen drwodd Cartref — Cau a llwytho — Cau a llwytho i mewn (Cartref - Cau a Llwytho - Cau a Llwytho i ...) byddwn yn cael bwrdd mor braf gyda rhestr o drafodion diweddar, fel yr oeddem yn dymuno:
Pan fyddwch chi'n newid y data ffynhonnell, rhaid i chi beidio ag anghofio diweddaru'r canlyniadau trwy dde-glicio arnyn nhw - y gorchymyn Diweddaru ac Arbed (Adnewyddu) neu lwybr byr bysellfwrdd Ctrl+Alt+F5.
- Mae'r swyddogaeth LOOKUP yn ddisgynnydd i VLOOKUP
- Sut i ddefnyddio'r swyddogaethau arae deinamig newydd SORT, FILTER, ac UNIC
- Dod o hyd i'r gell olaf nad yw'n wag mewn rhes neu golofn gyda'r swyddogaeth LOOKUP