Përmbajtje
Të gjitha funksionet klasike të kërkimit dhe zëvendësimit të tipit VPR (VLOOKUP), GPR (HLOOKUP), ME TE EKSPOZUAR (ndeshje) dhe ata si ata kanë një veçori të rëndësishme – kërkojnë nga fillimi deri në fund, dmth nga e majta në të djathtë ose nga lart poshtë në të dhënat burimore. Sapo të gjendet përputhja e parë, kërkimi ndalon dhe gjendet vetëm shfaqja e parë e elementit që na nevojitet.
Çfarë duhet të bëjmë nëse duhet të gjejmë jo ndodhjen e parë, por të fundit? Për shembull, transaksioni i fundit për klientin, pagesa e fundit, porosia më e fundit, etj.?
Metoda 1: Gjetja e rreshtit të fundit me një formulë vargu
Nëse tabela origjinale nuk ka një kolonë me një datë ose një numër serial të një rreshti (porosia, pagesa ...), atëherë detyra jonë është, në fakt, të gjejmë rreshtin e fundit që plotëson kushtin e dhënë. Kjo mund të bëhet me formulën e grupit të mëposhtëm:
këtu:
- funksion IF (NESE) kontrollon të gjitha qelizat në një kolonë një nga një Klient dhe shfaq numrin e linjës nëse përmban emrin që na nevojitet. Numri i rreshtit në fletë na jepet nga funksioni LINE (ROW), por duke qenë se ne kemi nevojë për numrin e rreshtit në tabelë, duhet të zbresim edhe 1, sepse kemi një kokë në tabelë.
- Pastaj funksioni MAX (MAX) zgjedh vlerën maksimale nga grupi i formuar i numrave të rreshtave, gjegjësisht numrin e linjës më të fundit të klientit.
- funksion INDEKSI (INDEX) kthen përmbajtjen e qelizës me numrin e fundit të gjetur nga çdo kolonë tjetër e kërkuar e tabelës (Kodi i porosisë).
E gjithë kjo duhet të futet si formula e grupit, dmth:
- Në Office 365 me përditësimet më të fundit të instaluara dhe mbështetje për grupe dinamike, thjesht mund të shtypni Shkruani.
- Në të gjitha versionet e tjera, pasi të futni formulën, do të duhet të shtypni shkurtoren e tastierës Ctrl+Ndryshim+Shkruani, i cili automatikisht do t'i shtojë atij në shiritin e formulave kllapa kaçurrelë.
Metoda 2: Kërkim i kundërt me funksionin e ri LOOKUP
Tashmë kam shkruar një artikull të gjatë me një video në lidhje me një veçori të re VIEW (XLOOKUP), i cili u shfaq në versionet më të fundit të Office për të zëvendësuar VLOOKUP-in e vjetër (VLOOKUP). Me ndihmën e BROWSE, detyra jonë zgjidhet mjaft elementare, sepse. për këtë funksion (ndryshe nga VLOOKUP), mund të vendosni në mënyrë eksplicite drejtimin e kërkimit: nga lart-poshtë ose nga poshtë-lart - argumenti i tij i fundit (-1) është përgjegjës për këtë:
Metoda 3. Kërkoni për një varg me datën më të fundit
Nëse në të dhënat burimore kemi një kolonë me një numër serial ose një datë që luan një rol të ngjashëm, atëherë detyra modifikohet - nuk duhet të gjejmë rreshtin e fundit (më të ulët) me një ndeshje, por rreshtin me më të fundit ( maksimumi) data.
Unë kam diskutuar tashmë në detaje se si ta bëjmë këtë duke përdorur funksionet klasike, dhe tani le të përpiqemi të përdorim fuqinë e funksioneve të reja të grupit dinamik. Për bukuri dhe komoditet më të madh, ne gjithashtu e konvertojmë tabelën origjinale në një tabelë "të zgjuar" duke përdorur një shkurtore tastiere Ctrl+T ose komandat Faqja kryesore – Formatoni si tabelë (Shtëpia - Formatoni si tabelë).
Me ndihmën e tyre, ky "çift vrasës" e zgjidh problemin tonë me shumë hijeshi:
këtu:
- Funksiononi së pari Filtër (FILTER) zgjedh vetëm ato rreshta nga tabela jonë ku në kolonë Klient - emri që na nevojitet.
- Pastaj funksioni KLASA (Rendit) rendit rreshtat e zgjedhur sipas datës në rend zbritës, me marrëveshjen më të fundit në krye.
- funksion INDEKSI (INDEX) nxjerr rreshtin e parë, pra kthen tregtinë e fundit që na nevojitet.
- Dhe, së fundi, funksioni FILTER i jashtëm heq kolonat shtesë të 1-rë dhe të tretë nga rezultatet (Kodi i porosisë и Klient) dhe lë vetëm datën dhe shumën. Për këtë, përdoret një grup konstantesh. {0;1;0;1}, duke përcaktuar se cilat kolona duam (1) ose nuk duam të shfaqim (0).
Metoda 4: Gjetja e përputhjes së fundit në Power Query
Epo, për hir të plotësisë, le të shohim një zgjidhje për problemin tonë të kërkimit të kundërt duke përdorur shtesën Power Query. Me ndihmën e saj, gjithçka zgjidhet shumë shpejt dhe bukur.
1. Le ta shndërrojmë tabelën tonë origjinale në një "të zgjuar" duke përdorur një shkurtore tastiere Ctrl+T ose komandat Faqja kryesore – Formatoni si tabelë (Shtëpia - Formatoni si tabelë).
2. Ngarkoni atë në Power Query me butonin Nga tabela/gama tab Data (Të dhënat - nga tabela/vargu).
3. Ne renditim (nëpërmjet listës rënëse të filtrit në kokë) tabelën tonë në rend zbritës të datës, në mënyrë që transaksionet më të fundit të jenë në krye.
4… Në skedën Transformim zgjidhni një ekip Grupo nga (Transformimi - Grupi Sipas) dhe vendosni grupimin sipas klientëve dhe si funksion grumbullues, zgjidhni opsionin Të gjitha linjat (Të gjitha rreshtat). Ju mund ta emërtoni kolonën e re çdo gjë që ju pëlqen - për shembull Detaje.
Pas grupimit, ne do të marrim një listë me emra unikë të klientëve tanë dhe në kolonë Detaje – tabela me të gjitha transaksionet e secilit prej tyre, ku rreshti i parë do të jetë transaksioni më i fundit, çka na nevojitet:
5. Shtoni një kolonë të re të llogaritur me butonin Kolona e personalizuar tab Shtoni kolonën (Shto kolonë - Shto kolonë të personalizuar)dhe shkruani formulën e mëposhtme:
Këtu Detaje – kjo është kolona nga e cila marrim tabelat sipas klientëve, dhe 0 {} është numri i rreshtit që duam të nxjerrim (numërimi i rreshtave në Power Query fillon nga zero). Ne marrim një kolonë me regjistrime (rekord), ku çdo hyrje është rreshti i parë nga çdo tabelë:
Mbetet për të zgjeruar përmbajtjen e të gjitha regjistrimeve me butonin me shigjeta të dyfishta në kokën e kolonës Marrëveshja e fundit duke zgjedhur kolonat e dëshiruara:
… dhe më pas fshini kolonën që nuk është më e nevojshme Detaje duke klikuar me të djathtën mbi titullin e saj - Hiq kolonat (Hiq kolonat).
Pas ngarkimit të rezultateve në fletë përmes Faqja kryesore — Mbyll dhe ngarko — Mbylle dhe ngarko brenda (Shtëpia - Mbyll & Ngarko - Mbylle & Ngarko për…) do të marrim një tabelë kaq të bukur me një listë të transaksioneve të fundit, siç dëshironim:
Kur ndryshoni të dhënat burimore, nuk duhet të harroni të përditësoni rezultatet duke klikuar me të djathtën mbi to - komandën Përditëso & Ruaj (Rifresko) ose shkurtore të tastierës Ctrl+Ndalet+F5.
- Funksioni LOOKUP është pasardhës i VLOOKUP
- Si të përdorni funksionet e reja të grupit dinamik SORT, FILTER dhe UNIC
- Gjetja e qelizës së fundit jo bosh në një rresht ose kolonë me funksionin LOOKUP