Inhalter
All klassesch Sich- an Typsubstitutiounsfunktiounen VPR (VLOOKUP), GPR (HLOOKUP), MÉI ausgesat (MATCH) an déi wéi si hunn eng wichteg Fonktioun - si sichen vun Ufank bis Enn, also lénks-zu-riets oder uewe bis ënnen an der Quell Daten. Soubal den éischte passende Match fonnt gëtt, stoppt d'Sich an nëmmen déi éischt Optriede vum Element dat mir brauchen ass fonnt.
Wat maache wa mir brauchen net déi éischt, mee déi lescht Optriede ze fannen? Zum Beispill, déi lescht Transaktioun fir de Client, déi lescht Bezuelung, déi lescht Bestellung, etc.?
Method 1: Fannt déi lescht Zeil mat enger Array Formel
Wann den ursprénglechen Dësch keng Kolonn mat engem Datum oder enger Seriennummer vun enger Zeil huet (Bestellung, Bezuelung ...), dann ass eis Aufgab tatsächlech déi lescht Zeil ze fannen déi de gegebene Konditioun entsprécht. Dëst kann mat der folgender Arrayformel gemaach ginn:
Hei:
- Funktioun IF (WANN) kontrolléiert all Zellen an enger Kolonn een nom aneren Client a weist d'Linnnummer wann et den Numm enthält dee mir brauchen. D'Linnnummer op der Blat gëtt eis vun der Funktioun uginn LINN (ROW), mee well mir d'Zeilennummer an der Tabell brauchen, musse mir zousätzlech 1 subtrahéieren, well mir en Header an der Tabell hunn.
- Dann d'Funktioun MAX (MAX) wielt de maximale Wäert aus dem geformte Set vu Reihennummeren, dh d'Zuel vun der leschter Linn vum Client.
- Funktioun INDEX (INDEX) gëtt den Inhalt vun der Zell mat der fonnter leschter Nummer vun enger aner erfuerderter Tabellkolonne zréck (Bestellcode).
All dëst muss agefouert ginn als Array Formel, dh:
- Am Office 365 mat de leschten Updates installéiert an Ënnerstëtzung fir dynamesch Arrays, kënnt Dir einfach drécken gitt.
- An all anere Versiounen, nodeems Dir d'Formel aginn hutt, musst Dir d'Tastatur Ofkiirzung drécken Ctrl+Verréckelung+gitt, déi automatesch gekrauselt Klameren an der Formelbar addéieren.
Method 2: Reverse Lookup mat der neier LOOKUP Funktioun
Ech hunn schonn e laangen Artikel mat engem Video iwwer eng nei Feature geschriwwen VIEW (XLOOKUP), déi an de leschte Versioune vum Office erschéngt fir den alen VLOOKUP ze ersetzen (VLOOKUP). Mat der Hëllef vu BROWSE gëtt eis Aufgab ganz elementar geléist, well. fir dës Funktioun (am Géigesaz zu VLOOKUP), kënnt Dir explizit d'Sichrichtung setzen: Top-down oder bottom-up - säi leschten Argument (-1) ass dofir verantwortlech:
Method 3. Sich no engem String mam leschten Datum
Wa mir an de Quelldaten eng Kolonn mat enger Seriennummer oder engem Datum hunn, deen eng ähnlech Roll spillt, da gëtt d'Aufgab geännert - mir mussen net déi lescht (niddregst) Linn mat engem Match fannen, awer d'Linn mat der leschter ( maximal) Datum.
Ech hu schonn am Detail diskutéiert wéi dëst mat klassesche Funktiounen ze maachen, a loosst eis elo probéieren d'Kraaft vun den neien dynamesche Arrayfunktiounen ze benotzen. Fir méi Schéinheet a Komfort konvertéiere mir och den originelle Dësch an e "Smart" Dësch mat enger Tastatur Ofkiirzung Ctrl+T oder Kommandoen Home - Format als Dësch (Home - Format als Table).
Mat hirer Hëllef léist dës "Killer Koppel" eise Problem ganz graziéis:
Hei:
- Fonktioun éischt FILTER (FILTER) wielt nëmmen déi Reihen aus eiser Tabell wou an der Kolonn Client - den Numm dee mir brauchen.
- Dann d'Funktioun Schouljoer (SORT) sortéiert déi gewielte Reihen no Datum an ofstigend Uerdnung, mat dem leschten Deal uewen.
- Funktioun INDEX (INDEX) Extrait déi éischt Zeil, dh gëtt de leschten Handel zréck mir brauchen.
- An, endlech, läscht déi extern FILTER Funktioun déi extra 1. an 3. Kolonnen aus de Resultater (Bestellcode и Client) a léisst nëmmen den Datum an de Betrag. Fir dëst gëtt eng Array vu Konstanten benotzt. {0;1;0;1}, definéieren wéi eng Kolonnen mir wëllen (1) oder net wëllen (0) ze weisen.
Method 4: De leschte Match an der Power Query fannen
Gutt, fir d'Vollständegkeet, loosst eis eng Léisung fir eis ëmgedréint Sichprobleem kucken mat der Power Query Add-in. Mat hirer Hëllef gëtt alles ganz séier a schéin geléist.
1. Loosst eis eisen originelle Dësch an e "Smart" ëmsetzen mat enger Tastatur Ofkiirzung Ctrl+T oder Kommandoen Home - Format als Dësch (Home - Format als Table).
2. Lued et an Power Query mam Knäppchen Vun Dësch / Gamme Reiter Daten (Daten - Vun Table / Range).
3. Mir sortéieren (duerch d'Dropdown-Lëscht vum Filter am Header) eisen Dësch an ofstigend Uerdnung vum Datum, sou datt déi lescht Transaktiounen uewen sinn.
4... An der Tab Transformatioun wielt eng Equipe Grupp duerch (Transforméieren - Group By) a setzt d'Gruppéierung vu Clienten, an als Aggregatiounsfunktioun wielt d'Optioun All Linnen (All Reihen). Dir kënnt déi nei Kolonn alles nennen wat Dir wëllt - zum Beispill Detailer.
No der Gruppéierung kréie mir eng Lëscht vun eenzegaartegen Nimm vun eise Clienten an an der Kolonn Detailer - Dëscher mat all Transaktioune vun all vun hinnen, wou déi éischt Zeil déi lescht Transaktioun ass, dat ass wat mir brauchen:
5. Füügt eng nei berechent Kolonn mat dem Knäppchen Benotzerdefinéiert Kolonn Reiter Füügt Kolonn (Füügt Kolonn - Füügt personaliséiert Kolonn derbäi)a gitt déi folgend Formel:
hei Detailer - dat ass d'Kolonn aus deem mir Dëscher vun Clienten huelen, an 0 {} ass d'Zuel vun der Zeil déi mir extrahéieren wëllen (Zeilnummeréierung an der Power Query fänkt vun Null un). Mir kréien eng Kolonn mat records (Rekord), wou all Entrée déi éischt Zeil vun all Dësch ass:
Et bleift fir den Inhalt vun allen Opzeechnungen auszebauen mam Knäppchen mat duebele Pfeile an der Kolonn Header Leschten Deal déi gewënschte Kolonnen auswielen:
... an dann déi Kolonn läschen déi net méi gebraucht gëtt Detailer andeems Dir op säin Titel klickt - Spalten ewechzehuelen (Spalten ewechhuelen).
Nodeems Dir d'Resultater op de Blat eropluet duerch Home - Zoumaachen a lueden - Zoumaachen a lueden (Home - Zoumaachen & Lueden - Zoumaachen & Lueden op ...) mir kréien esou e flotten Dësch mat enger Lëscht vun de rezenten Transaktiounen, wéi mir wollten:
Wann Dir d'Quelldaten ännert, däerf Dir net vergiessen d'Resultater ze aktualiséieren andeems Dir op se klickt - de Kommando Update & späicheren (Erfrëschen) oder Tastatur Ofkiirzung Ctrl+Alt+F5.
- D'LOOKUP Funktioun ass en Nofolger vu VLOOKUP
- Wéi benotzt Dir déi nei dynamesch Arrayfunktiounen SORT, FILTER, an UNIC
- Fannt déi lescht net eidel Zell an enger Zeil oder Kolonn mat der LOOKUP Funktioun