Kaundan
Tanan nga klasiko nga pagpangita ug pagpuli sa tipo nga mga gimbuhaton VPR (VLOOKUP), GPR (HLOOKUP), MAS EXPOSED (PAGTABANG) ug kadtong sama kanila adunay usa ka importante nga bahin - sila mangita gikan sa sinugdanan hangtud sa katapusan, ie wala-pa-tuo o ibabaw-pa-ubos sa tinubdan nga datos. Sa diha nga makit-an ang una nga pagpares, ang pagpangita mohunong ug ang una nga panghitabo sa elemento nga atong gikinahanglan ang makit-an.
Unsa ang buhaton kung kinahanglan naton pangitaon dili ang una, apan ang katapusan nga panghitabo? Pananglitan, ang katapusan nga transaksyon alang sa kliyente, ang katapusan nga pagbayad, ang pinakabag-o nga order, ug uban pa?
Pamaagi 1: Pagpangita sa Katapusan nga Laray gamit ang Array Formula
Kung ang orihinal nga lamesa walay kolum nga adunay petsa o serial number sa usa ka laray (order, pagbayad ...), nan ang among tahas mao, sa tinuud, pagpangita sa katapusan nga laray nga makatagbaw sa gihatag nga kondisyon. Mahimo kini gamit ang mosunod nga pormula sa array:
Dinhi:
- function IF (KUNG) gisusi ang tanan nga mga cell sa usa ka kolum sa tinagsa Customer ug gipakita ang numero sa linya kung kini adunay ngalan nga among gikinahanglan. Ang numero sa linya sa sheet gihatag kanamo pinaagi sa function PAGTULUN (ROW), apan tungod kay kinahanglan naton ang numero sa laray sa lamesa, kinahanglan usab naton nga ibawas ang 1, tungod kay adunay usa ka header sa lamesa.
- Unya ang function MAX (MAX) mopili sa pinakataas nga bili gikan sa naporma nga set sa mga numero sa laray, ie ang gidaghanon sa pinakabag-o nga linya sa kliyente.
- function INDEX (INDEX) ibalik ang mga sulod sa cell nga adunay nakit-an nga katapusang numero gikan sa bisan unsang ubang gikinahanglan nga kolum sa lamesa (Code sa pag-order).
Kining tanan kinahanglang isulod isip pormula sa array, ie:
- Sa Office 365 nga adunay pinakabag-o nga mga update nga na-install ug suporta alang sa dinamikong mga arrays, mahimo nimong ipadayon Pagsulod.
- Sa tanan nga uban nga mga bersyon, pagkahuman sa pagsulod sa pormula, kinahanglan nimo nga ipadayon ang keyboard shortcut Ctrl+pagbalhin+Pagsulod, nga awtomatikong idugang ang mga kulot nga braces niini sa formula bar.
Pamaagi 2: Reverse lookup gamit ang bag-ong LOOKUP function
Nagsulat na ko og taas nga artikulo nga adunay video bahin sa bag-ong feature PANGUTANA (XLOOKUP), nga nagpakita sa pinakabag-o nga bersyon sa Office aron ilisan ang daan nga VLOOKUP (VLOOKUP). Sa tabang sa BROWSE, ang among buluhaton nasulbad nga medyo elementarily, tungod kay. alang niini nga function (dili sama sa VLOOKUP), mahimo nimong klaro nga itakda ang direksyon sa pagpangita: top-down o bottom-up - ang kataposang argumento niini (-1) maoy responsable niini:
Pamaagi 3. Pangitag string nga adunay pinakabag-o nga petsa
Kung sa gigikanan nga datos kita adunay usa ka kolum nga adunay usa ka serial number o usa ka petsa nga adunay parehas nga papel, nan ang buluhaton gibag-o - kinahanglan naton pangitaon dili ang katapusan (labing ubos) nga linya nga adunay usa ka tugma, apan ang linya nga adunay labing bag-o ( maximum) nga petsa.
Nahisgotan na nako sa detalye kung giunsa kini buhaton gamit ang mga klasiko nga function, ug karon sulayan naton nga gamiton ang gahum sa bag-ong mga function sa dinamikong array. Para sa mas dako nga katahom ug kasayon, among gi-convert usab ang orihinal nga lamesa ngadto sa “smart” table gamit ang keyboard shortcut Ctrl+T o mga sugo Panimalay - Pag-format ingon usa ka lamesa (Balay — Format isip Talaan).
Uban sa ilang tabang, kining "magpatay nga magtiayon" nakasulbad sa among problema nga maayo kaayo:
Dinhi:
- Function una filter (FILTER) nagpili lamang sa mga laray gikan sa among lamesa diin sa kolum Customer – ang ngalan nga atong gikinahanglan.
- Unya ang function GRADO (SORT) paghan-ay sa pinili nga mga laray pinaagi sa petsa sa paubos nga han-ay, uban sa pinaka-ulahing deal sa ibabaw.
- function INDEX (INDEX) gikuha ang unang laray, ie mibalik sa kataposang patigayon nga atong gikinahanglan.
- Ug, sa katapusan, ang eksternal nga FILTER function nagtangtang sa dugang nga 1st ug 3rd column gikan sa mga resulta (Code sa pag-order и Customer) ug nagbilin lamang sa petsa ug kantidad. Alang niini, usa ka han-ay sa mga constants ang gigamit. {0;1;0;1}, nga nagpatin-aw kung unsang mga kolum ang gusto namon (1) o dili gusto (0) nga ipakita.
Pamaagi 4: Pagpangita sa Katapusan nga Tugma sa Power Query
Aw, alang sa kaayohan sa pagkakompleto, atong tan-awon ang usa ka solusyon sa atong reverse search nga problema gamit ang Power Query add-in. Uban sa iyang tabang, ang tanan nasulbad nga dali ug matahum.
1. Atong i-convert ang atong orihinal nga lamesa ngadto sa “smart” gamit ang keyboard shortcut Ctrl+T o mga sugo Panimalay - Pag-format ingon usa ka lamesa (Balay — Format isip Talaan).
2. I-load kini sa Power Query gamit ang buton Gikan sa Table/Range tab Data (Data — Gikan sa Table/Range).
3. Gihan-ay namo (pinaagi sa drop-down list sa filter sa header) among table sa paubos nga han-ay sa petsa, aron ang pinakabag-o nga mga transaksyon anaa sa ibabaw.
4… Sa tab kausaban pagpili og team Grupo sa (Pagbag-o - Grupo Ni) ug itakda ang paggrupo sa mga kustomer, ug isip usa ka aggregating function, pilia ang kapilian Tanang linya (Tanan nga laray). Mahimo nimong hinganlan ang bag-ong kolum bisan unsang gusto nimo - pananglitan Details.
Pagkahuman sa paggrupo, makakuha kami usa ka lista sa mga talagsaon nga ngalan sa among mga kliyente ug sa kolum Details - mga lamesa nga adunay tanan nga mga transaksyon sa matag usa kanila, diin ang una nga linya mao ang labing bag-o nga transaksyon, nga kinahanglan namon:
5. Pagdugang usa ka bag-ong nakalkula nga kolum gamit ang buton Pasadya nga kolum tab Pagdugang kolum (Idugang ang kolum - Idugang ang naandan nga kolum)ug isulod ang mosunod nga pormula:
dinhi Details – kini mao ang kolum gikan sa nga kita sa mga lamesa sa mga kustomer, ug 0 {} mao ang numero sa laray nga gusto namong i-extract (pagnumero sa linya sa Power Query magsugod gikan sa zero). Nagkuha kami usa ka kolum nga adunay mga rekord (Record), diin ang matag entry mao ang unang laray gikan sa matag lamesa:
Nagpabilin kini aron mapalapad ang sulud sa tanan nga mga rekord gamit ang buton nga adunay doble nga mga pana sa ulohan sa kolum Katapusan nga deal pagpili sa gusto nga mga kolum:
… ug unya tangtanga ang kolum nga dili na kinahanglan Details pinaagi sa pag-right-click sa titulo niini - Kuhaa ang mga kolum (Kuhaa ang mga kolum).
Human ma-upload ang mga resulta sa sheet pinaagi sa Panimalay — Pagsira ug pagkarga — Pagsira ug pagkarga (Balay — Close & Load — Close & Load to…) makakuha kami usa ka nindot nga lamesa nga adunay usa ka lista sa bag-ong mga transaksyon, sama sa among gusto:
Kung imong usbon ang gigikanan nga datos, kinahanglan nga dili nimo kalimtan ang pag-update sa mga resulta pinaagi sa pag-right-click sa kanila - ang mando I-update ug I-save (I-refresh) o keyboard shortcut Ctrl+alt+F5.
- Ang LOOKUP function kay kaliwat sa VLOOKUP
- Giunsa paggamit ang bag-ong dynamic array function SORT, FILTER, ug UNIC
- Pagpangita sa katapusang dili blangko nga cell sa usa ka laray o kolum nga adunay LOOKUP function