Werrej
Il-funzjonijiet kollha ta' tfittxija klassika u ta' sostituzzjoni tat-tip VPR (LOOKUP), GPR (LOOKUP), AKTAR ESPOSTI (TAQBIL) u dawk bħalhom għandhom karatteristika waħda importanti – ifittxu mill-bidu sat-tmiem, jiġifieri mix-xellug għal-lemin jew minn fuq għal isfel fid-dejta tas-sors. Hekk kif tinstab l-ewwel taqbila li tqabbel, it-tfittxija tieqaf u tinstab biss l-ewwel okkorrenza tal-element li għandna bżonn.
X'għandu nagħmlu jekk ikollna bżonn insibu mhux l-ewwel, iżda l-aħħar okkorrenza? Per eżempju, l-aħħar transazzjoni għall-klijent, l-aħħar ħlas, l-aktar ordni riċenti, eċċ.?
Metodu 1: Sib l-Aħħar Ringiela b'Formula Array
Jekk it-tabella oriġinali m'għandhiex kolonna b'data jew numru tas-serje ta' ringiela (ordni, ħlas ...), allura l-kompitu tagħna huwa, fil-fatt, li nsibu l-aħħar ringiela li tissodisfa l-kundizzjoni mogħtija. Dan jista 'jsir bil-formula ta' firxa li ġejja:
Hawnhekk:
- funzjoni IF (JEKK) jiċċekkja ċ-ċelloli kollha f'kolonna waħda waħda Klijent u juri n-numru tal-linja jekk ikun fih l-isem li għandna bżonn. In-numru tal-linja fuq il-folja huwa mogħti lilna mill-funzjoni LINJA (filliera), iżda peress li neħtieġu n-numru tar-ringiela fit-tabella, irridu wkoll nnaqqsu 1, għax għandna header fit-tabella.
- Imbagħad il-funzjoni MAX (MAX) jagħżel il-valur massimu mis-sett iffurmat ta' numri ta' ringiela, jiġifieri n-numru tal-linja l-aktar riċenti tal-klijent.
- funzjoni INDIĊI (INDIĊI) jirritorna l-kontenut taċ-ċellula bl-aħħar numru misjub minn kwalunkwe kolonna oħra tat-tabella meħtieġa (Kodiċi tal-ordni).
Dan kollu għandu jiddaħħal bħala formula tal-firxa, jiġifieri:
- Fl-Office 365 bl-aħħar aġġornamenti installati u appoġġ għal arrays dinamiċi, tista 'sempliċement tagħfas Ikteb.
- Fil-verżjonijiet l-oħra kollha, wara li ddaħħal il-formula, ikollok tagħfas is-shortcut tat-tastiera Ctrl+Shift+Ikteb, li awtomatikament iżid ċineg kaboċċi miegħu fil-bar tal-formula.
Metodu 2: Reverse Lookup bil-funzjoni l-ġdida LOOKUP
Diġà ktibt artiklu twil b'filmat dwar karatteristika ġdida VIEW (XLOOKUP), li deher fl-aħħar verżjonijiet tal-Uffiċċju biex jissostitwixxi l-VLOOKUP l-antik (LOOKUP). Bl-għajnuna ta 'BOWSE, il-kompitu tagħna huwa solvut pjuttost elementari, għaliex. għal din il-funzjoni (b'differenza VLOOKUP), tista 'tissettja espliċitament id-direzzjoni tat-tfittxija: minn fuq għal isfel jew minn isfel għal fuq - l-aħħar argument tiegħu (-1) huwa responsabbli għal dan:
Metodu 3. Fittex għal string bl-aħħar data
Jekk fid-dejta tas-sors ikollna kolonna b'numru tas-serje jew data li għandha rwol simili, allura l-kompitu jiġi modifikat - irridu nsibu mhux l-aħħar linja (l-inqas) b'taqbila, iżda l-linja bl-aħħar ( massimu) data.
Diġà ddiskutejt fid-dettall kif tagħmel dan billi tuża funzjonijiet klassiċi, u issa ejja nippruvaw nużaw il-qawwa tal-funzjonijiet l-ġodda tal-firxa dinamika. Għal sbuħija u konvenjenza akbar, aħna wkoll nikkonverti t-tabella oriġinali f'tabella "intelliġenti" billi tuża shortcut tat-tastiera Ctrl+T jew jikkmanda Home – Format bħala tabella (Id-dar — Format bħala Tabella).
Bl-għajnuna tagħhom, din il-“koppja qattiel” issolvi l-problema tagħna b’mod grazzjuż ħafna:
Hawnhekk:
- Funzjoni l-ewwel Iffiltra (FILTRI) jagħżel biss dawk ir-ringieli mit-tabella tagħna fejn fil-kolonna Klijent – l-isem li għandna bżonn.
- Imbagħad il-funzjoni GRAD (SORT) issortja r-ringieli magħżula skond id-data f'ordni dixxendenti, bl-aktar ftehim reċenti fil-quċċata.
- funzjoni INDIĊI (INDIĊI) estratti l-ewwel ringiela, jiġifieri jirritorna l-aħħar kummerċ li għandna bżonn.
- U, fl-aħħarnett, il-funzjoni esterna FILTER tneħħi l-ewwel u t-1 kolonni żejda mir-riżultati (Kodiċi tal-ordni и Klijent) u tħalli biss id-data u l-ammont. Għal dan, tintuża firxa ta 'kostanti. {0;1;0;1}, tiddefinixxi liema kolonni rridu (1) jew ma rridux li (0) juru.
Metodu 4: Sib l-Aħħar Taqbila fil-Power Query
Ukoll, għall-fini tal-kompletezza, ejja nħarsu lejn soluzzjoni għall-problema tagħna ta 'tfittxija b'lura bl-użu tal-Power Query add-in. Bl-għajnuna tagħha, kollox jiġi solvut malajr ħafna u sabiħ.
1. Ejja nikkonverti t-tabella oriġinali tagħna f'waħda "intelliġenti" billi tuża shortcut tat-tastiera Ctrl+T jew jikkmanda Home – Format bħala tabella (Id-dar — Format bħala Tabella).
2. Tgħabbiha fil-Power Query bil-buttuna Minn Tabella/Firxa tab Data (Data — Minn Tabella/Medda).
3. Aħna issortja (permezz tal-lista drop-down tal-filtru fl-intestatura) it-tabella tagħna f'ordni dixxendenti tad-data, sabiex it-tranżazzjonijiet l-aktar riċenti jkunu fuq nett.
4… Fit-tab Trasformazzjoni agħżel tim Grupp minn (Trasforma — Group By) u ssettja l-grupp mill-klijenti, u bħala funzjoni ta 'aggregazzjoni, agħżel l-għażla Il-linji kollha (Ir-ringieli kollha). Tista 'ssemmi l-kolonna l-ġdida kull ħaġa li tixtieq - pereżempju dettalji.
Wara l-grupp, aħna se tikseb lista ta 'ismijiet uniċi tal-klijenti tagħna u fil-kolonna dettalji – tabelli bit-tranżazzjonijiet kollha ta’ kull wieħed minnhom, fejn l-ewwel linja tkun l-aħħar tranżazzjoni, li huwa dak li neħtieġu:
5. Żid kolonna kkalkulata ġdida bil-buttuna Kolonna tad-dwana tab Żid kolonna (Żid kolonna — Żid kolonna tad-dwana)u daħħal il-formula li ġejja:
Hawnhekk dettalji – din hija l-kolonna li minnha nieħdu tabelli mill-klijenti, u 0 {} huwa n-numru tar-ringiela li rridu nieħdu (in-numerazzjoni tar-ringiela f'Power Query tibda minn żero). Ikollna kolonna bir-rekords (rekord), fejn kull entrata hija l-ewwel ringiela minn kull tabella:
Jibqa' jespandi l-kontenut tar-rekords kollha bil-buttuna bi vleġeġ doppji fl-intestatura tal-kolonna L-aħħar ftehim tagħżel il-kolonni mixtieqa:
... u mbagħad ħassar il-kolonna li m'għadhiex meħtieġa dettalji billi tikklikkja bil-lemin fuq it-titlu tagħha - Neħħi l-kolonni (Neħħi l-kolonni).
Wara li ttella r-riżultati fil-folja permezz Id-dar — Agħlaq u tagħbija — Agħlaq u tagħbija ġewwa (Dar — Agħlaq u Tagħbija — Agħlaq u Tagħbija għal...) se jkollna tabella sabiħa bħal din b'lista ta 'tranżazzjonijiet reċenti, kif ridna:
Meta tibdel id-dejta tas-sors, m'għandekx tinsa li taġġorna r-riżultati billi tikklikkja bil-lemin fuqhom - il-kmand Aġġorna u Issejvja (Aġġorna) jew shortcut tat-tastiera Ctrl+Alt+F5.
- Il-funzjoni LOOKUP hija dixxendent ta' VLOOKUP
- Kif tuża l-funzjonijiet il-ġodda tal-firxa dinamika SORT, FILTER, u UNIC
- Sib l-aħħar ċellula mhux vojta f'ringiela jew kolonna bil-funzjoni LOOKUP