Nilalaman
Lahat ng klasikong paghahanap at pagpapalit ng uri ng mga function VPR (VLOOKUP), GPR (HLOOKUP), MAS EXPOSED (MATCH) at ang mga tulad nila ay may isang mahalagang tampok - naghahanap sila mula sa simula hanggang sa dulo, ibig sabihin, kaliwa-pakanan o itaas-pababa sa source data. Sa sandaling matagpuan ang unang katugmang tugma, hihinto ang paghahanap at ang unang paglitaw lamang ng elementong kailangan namin ang makikita.
Ano ang gagawin kung kailangan nating hanapin hindi ang una, ngunit ang huling pangyayari? Halimbawa, ang huling transaksyon para sa kliyente, ang huling pagbabayad, ang pinakabagong order, atbp.?
Paraan 1: Paghahanap ng Huling Hilera na may Array Formula
Kung ang orihinal na talahanayan ay walang column na may petsa o serial number ng isang row (order, pagbabayad ...), kung gayon ang aming gawain ay, sa katunayan, upang mahanap ang huling row na nakakatugon sa ibinigay na kundisyon. Magagawa ito gamit ang sumusunod na formula ng array:
Dito:
- tungkulin IF (KUNG) sinusuri ang lahat ng mga cell sa isang column nang paisa-isa Parokyano at ipinapakita ang numero ng linya kung naglalaman ito ng pangalan na kailangan namin. Ang numero ng linya sa sheet ay ibinibigay sa amin ng function LINE (ROW), ngunit dahil kailangan natin ang numero ng hilera sa talahanayan, kailangan din nating ibawas ang 1, dahil mayroon tayong header sa talahanayan.
- Pagkatapos ang function MAX (MAX) pinipili ang pinakamataas na halaga mula sa nabuong hanay ng mga numero ng hilera, ibig sabihin, ang bilang ng pinakabagong linya ng kliyente.
- tungkulin INDEX (INDEX) ibinabalik ang mga nilalaman ng cell na may nakitang huling numero mula sa anumang iba pang kinakailangang hanay ng talahanayan (Order code).
Ang lahat ng ito ay dapat ipasok bilang formula ng array, ibig sabihin:
- Sa Office 365 na may mga pinakabagong update na naka-install at suporta para sa mga dynamic na array, maaari mong pindutin lamang Magpasok.
- Sa lahat ng iba pang mga bersyon, pagkatapos ipasok ang formula, kakailanganin mong pindutin ang keyboard shortcut Ctrl+Ilipat+Magpasok, na awtomatikong magdaragdag ng mga kulot na brace dito sa formula bar.
Paraan 2: Baliktarin ang paghahanap gamit ang bagong LOOKUP function
Nagsulat na ako ng mahabang artikulo na may video tungkol sa isang bagong feature VIEW (XLOOKUP), na lumabas sa mga pinakabagong bersyon ng Office upang palitan ang lumang VLOOKUP (VLOOKUP). Sa tulong ng BROWSE, ang aming gawain ay nalutas nang simple, dahil. para sa function na ito (hindi tulad ng VLOOKUP), maaari mong tahasang itakda ang direksyon ng paghahanap: top-down o bottom-up – ang huling argumento nito (-1) ay responsable para dito:
Paraan 3. Maghanap ng string na may pinakabagong petsa
Kung sa source data mayroon kaming isang column na may serial number o isang petsa na gumaganap ng isang katulad na papel, kung gayon ang gawain ay binago - kailangan naming hanapin hindi ang huling (pinakamababa) na linya na may tugma, ngunit ang linya na may pinakabagong ( maximum) petsa.
Napag-usapan ko na nang detalyado kung paano ito gagawin gamit ang mga klasikong function, at ngayon subukan nating gamitin ang kapangyarihan ng mga bagong dynamic na function ng array. Para sa higit na kagandahan at kaginhawahan, iko-convert din namin ang orihinal na talahanayan sa isang "matalinong" talahanayan gamit ang isang keyboard shortcut Ctrl+T o mga utos Tahanan – I-format bilang isang talahanayan (Home — Format bilang Talahanayan).
Sa tulong nila, malulutas ng “killer couple” na ito ang aming problema nang napakaganda:
Dito:
- Function muna FILTER (FILTER) pinipili lamang ang mga row mula sa aming table kung saan sa column Parokyano – ang pangalan na kailangan natin.
- Pagkatapos ang function GRADO (SORT) pinagbukud-bukod ang mga napiling row ayon sa petsa sa pababang pagkakasunud-sunod, kasama ang pinakakamakailang deal sa itaas.
- tungkulin INDEX (INDEX) kinukuha ang unang hilera, ibig sabihin, ibinabalik ang huling kalakalan na kailangan natin.
- At, sa wakas, ang panlabas na FILTER function ay nag-aalis ng mga karagdagang 1st at 3rd column mula sa mga resulta (Order code и Parokyano) at iniiwan lamang ang petsa at halaga. Para dito, ginagamit ang isang hanay ng mga constant. {0;1;0;1}, pagtukoy kung aling mga column ang gusto naming (1) o ayaw (0) ipakita.
Paraan 4: Paghahanap ng Huling Tugma sa Power Query
Well, para sa kapakanan ng pagiging kumpleto, tingnan natin ang isang solusyon sa aming reverse search na problema gamit ang Power Query add-in. Sa kanyang tulong, ang lahat ay nalutas nang napakabilis at maganda.
1. I-convert natin ang aming orihinal na talahanayan sa isang "matalino" gamit ang isang keyboard shortcut Ctrl+T o mga utos Tahanan – I-format bilang isang talahanayan (Home — Format bilang Talahanayan).
2. I-load ito sa Power Query gamit ang button Mula sa Table/Range tab data (Data — Mula sa Talahanayan/Saklaw).
3. Inuuri namin (sa pamamagitan ng drop-down na listahan ng filter sa header) ang aming talahanayan sa pababang pagkakasunud-sunod ng petsa, upang ang mga pinakabagong transaksyon ay nasa itaas.
4… Sa tab Pagbabagong-anyo pumili ng isang pangkat Grupo ng (Transform — Ipangkat Ayon) at itakda ang pagpapangkat ayon sa mga customer, at bilang isang pinagsama-samang function, piliin ang opsyon Lahat ng linya (Lahat ng row). Maaari mong pangalanan ang bagong column kahit anong gusto mo – halimbawa Detalye.
Pagkatapos ng pagpapangkat, makakakuha kami ng isang listahan ng mga natatanging pangalan ng aming mga kliyente at sa column Detalye – mga talahanayan na may lahat ng mga transaksyon ng bawat isa sa kanila, kung saan ang unang linya ay ang pinakabagong transaksyon, na kung ano ang kailangan namin:
5. Magdagdag ng bagong kalkuladong column gamit ang button Custom na column tab Magdagdag ng haligi (Magdagdag ng column — Magdagdag ng custom na column)at ipasok ang sumusunod na formula:
dito Detalye – ito ang column kung saan kami kumukuha ng mga talahanayan ng mga customer, at 0 {} ay ang numero ng row na gusto naming i-extract (nagsisimula sa zero ang row numbering sa Power Query). Kumuha kami ng column na may mga record (rekord), kung saan ang bawat entry ay ang unang hilera mula sa bawat talahanayan:
Ito ay nananatiling palawakin ang mga nilalaman ng lahat ng mga talaan gamit ang pindutan na may dobleng mga arrow sa header ng hanay Huling deal pagpili ng gustong mga column:
… at pagkatapos ay tanggalin ang column na hindi na kailangan Detalye sa pamamagitan ng pag-right click sa pamagat nito - Alisin ang mga column (Alisin ang mga column).
Pagkatapos i-upload ang mga resulta sa sheet sa pamamagitan ng Tahanan — Isara at i-load — Isara at i-load (Home — Isara at I-load — Isara at I-load sa…) makakakuha tayo ng napakagandang talahanayan na may listahan ng mga kamakailang transaksyon, ayon sa gusto natin:
Kapag binago mo ang source data, hindi mo dapat kalimutang i-update ang mga resulta sa pamamagitan ng pag-right click sa mga ito – ang command I-update at I-save (Refresh) o keyboard shortcut Ctrl+Alt+F5.
- Ang LOOKUP function ay isang inapo ng VLOOKUP
- Paano gamitin ang bagong mga function ng dynamic array SORT, FILTER, at UNIC
- Paghahanap ng huling hindi blangko na cell sa isang row o column na may LOOKUP function