Edukiak
Bilaketa eta mota ordezkatzeko funtzio klasiko guztiak VPR (VLOOKUP), GPR (HLOOKUP), ERABILERA GEHIAGO (PARTIDA) eta haiek bezalakoek ezaugarri garrantzitsu bat dute: hasieratik amaierara egiten dute bilaketa, hau da, ezkerretik eskuinera edo goitik beherako datu iturrietan. Bat datorren lehenengo bat-etortzea aurkitu bezain laster, bilaketa gelditzen da eta behar dugun elementuaren lehen agerraldia baino ez da aurkitzen.
Zer egin lehenengoa ez, azken agerraldia aurkitu behar badugu? Adibidez, bezeroaren azken transakzioa, azken ordainketa, azken eskaera, etab.?
1. metodoa: Azken errenkada aurkitzea Array formula batekin
Jatorrizko taulak ez badu data edo errenkada baten serie-zenbakia duen zutaberik (eskaera, ordainketa...), orduan gure zeregina da, hain zuzen ere, emandako baldintza betetzen duen azken errenkada aurkitzea. Hau array formula honekin egin daiteke:
Here:
- Funtzio IF (BADA) zutabe bateko gelaxka guztiak banan-banan egiaztatzen ditu Customer eta lerro zenbakia bistaratzen du behar dugun izena badu. Orriko lerro-zenbakia funtzioak ematen digu LINE (ILARA), baina taulako errenkada-zenbakia behar dugunez, gainera, 1 kendu behar dugu, taulan goiburua dugulako.
- Ondoren funtzioa MAX (MAX) eratutako errenkada-zenbakien multzotik gehienezko balioa hautatzen du, hau da, bezeroaren azken lerroaren zenbakia.
- Funtzio INDEX (AURKIBIDEA) Aurkitutako azken zenbakia duen gelaxkaren edukia itzultzen du beharrezko beste edozein taula zutabetatik (Eskaera kodea).
Hau guztia bezala sartu behar da array formula, hau da:
- Office 365-en azken eguneraketak instalatuta eta matrize dinamikoetarako laguntzarekin, sakatu besterik ez duzu egin Sartu.
- Beste bertsio guztietan, formula sartu ondoren, teklatuko lasterbidea sakatu beharko duzu Ctrl+Shift+Sartu, eta horrek automatikoki gehituko dizkio formula-barran giltza kizkurrak.
2. metodoa: Alderantzizko bilaketa LOOKUP funtzio berriarekin
Dagoeneko artikulu luze bat idatzi nuen funtzio berri bati buruzko bideo batekin VIEW (XLOOKUP), VLOOKUP zaharra ordezkatzeko Office-ren azken bertsioetan agertu zena (VLOOKUP). BROWSE-ren laguntzarekin, gure zeregina nahiko oinarrizkoa da, zeren eta. funtzio honetarako (VLOOKUP ez bezala), bilaketaren norabidea esplizituki ezar dezakezu: goitik behera edo behetik gora - bere azken argumentua (-1) da honen arduraduna:
3. metodoa. Bilatu azken data duen kate bat
Iturburuko datuetan serie-zenbaki bat edo antzeko zeregina duen data duen zutabe bat badugu, orduan zeregina aldatu egiten da; ez dugu bat datorren azken lerroa (baxuena) aurkitu behar, baizik eta azkeneko lerroa ( gehienez) data.
Dagoeneko zehatz-mehatz eztabaidatu dut nola egin funtzio klasikoak erabiliz, eta orain saia gaitezen array funtzio dinamiko berrien boterea erabiltzen. Edertasun eta erosotasun handiagoa lortzeko, jatorrizko taula mahai "adimentsu" batean bihurtzen dugu teklatuko lasterbide baten bidez Ctrl+T edo aginduak Hasiera - Taula gisa formatu (Hasiera — Taula gisa formatu).
Haien laguntzarekin, "bikote hiltzaile" honek oso dotore konpontzen du gure arazoa:
Here:
- Funtzioa lehenik FILTER (IRAGAZKI) zutabean dauden gure taulako errenkada horiek bakarrik hautatzen ditu Customer – behar dugun izena.
- Ondoren funtzioa GRADE (ORDENATU) Hautatutako errenkadak dataren arabera ordenatzen ditu beheranzko ordenan, azken akordioa goian duela.
- Funtzio INDEX (AURKIBIDEA) lehenengo errenkada ateratzen du, hau da, behar dugun azken merkataritza itzultzen du.
- Eta, azkenik, kanpoko FILTER funtzioak 1. eta 3. zutabe gehigarriak kentzen ditu emaitzetatik (Eskaera kodea и Customer) eta data eta zenbatekoa bakarrik uzten ditu. Horretarako, konstanteen array bat erabiltzen da. {0;1;0;1}, zein zutabe (1) edo ez (0) erakutsi nahi ditugun definituz.
4. metodoa: Power Query-n azken bat etortzea aurkitzea
Beno, osotasunaren mesedetan, ikus dezagun gure alderantzizko bilaketa-arazoaren irtenbidea Power Query gehigarria erabiliz. Bere laguntzarekin, dena oso azkar eta ederki konpontzen da.
1. Bihur dezagun gure jatorrizko taula "adimentsu" batean, teklatuko lasterbide bat erabiliz Ctrl+T edo aginduak Hasiera - Taula gisa formatu (Hasiera — Taula gisa formatu).
2. Kargatu Power Query-n botoiarekin Taula/Barrutitik fitxa Data (Datuak — Taula/Barrutitik).
3. Gure taula (goiburuko iragazkien goitibeherako zerrendaren bidez) ordenatzen dugu dataren beheranzko ordenan, azken transakzioak goian egon daitezen.
4… Fitxan Eraldaketa talde bat aukeratu Talde arabera (Eraldatu — Taldekatu) eta ezarri bezeroen araberako taldekatzea, eta agregazio-funtzio gisa, hautatu aukera Lerro guztiak (Errenkada guztiak). Zutabe berriari nahi duzun edozer izenda diezaiokezu, adibidez Xehetasunak.
Taldekatu ondoren, gure bezeroen izen berezien zerrenda eta zutabean jasoko dugu Xehetasunak – horietako bakoitzaren transakzio guztiekin taulak, non lehen lerroa azken transakzioa izango den, hau da, behar duguna:
5. Gehitu kalkulatutako zutabe berri bat botoiarekin Zutabe pertsonalizatua fitxa Gehitu zutabea (Gehitu zutabea — Gehitu zutabe pertsonalizatua)eta sartu formula hau:
Here Xehetasunak – hau da bezeroen arabera taulak hartzen ditugun zutabea, eta 0 {} atera nahi dugun errenkadaren zenbakia da (Power Query-n errenkaden zenbakia zerotik hasten da). Erregistroak dituen zutabe bat lortzen dugu (Grabatu), non sarrera bakoitza taula bakoitzeko lehen errenkada den:
Erregistro guztien edukia zabaltzea geratzen da zutabeen goiburuko gezi bikoitzak dituen botoiarekin Azken akordioa nahi diren zutabeak hautatuz:
… eta gero ezabatu behar ez den zutabea Xehetasunak bere izenburuan eskuineko botoiarekin klik eginez - Kendu zutabeak (Kendu zutabeak).
Emaitzak fitxara igo ondoren Hasiera — Itxi eta kargatu — Itxi eta kargatu (Hasiera — Itxi eta Kargatu — Itxi eta Kargatu hona...) Taula polit bat lortuko dugu azken transakzioen zerrendarekin, nahi genuen bezala:
Iturburuko datuak aldatzen dituzunean, ez duzu ahaztu behar emaitzak eguneratzea haietan eskuineko botoiarekin klik eginez - komandoa Eguneratu eta Gorde (Freskatu) edo teklatuko lasterbidea Ctrl+Alt+F5.
- LOOKUP funtzioa VLOOKUP-en ondorengoa da
- Nola erabili matrize dinamikoen funtzio berriak SORT, FILTER eta UNIC
- Errenkada edo zutabe batean hutsik ez dagoen azken gelaxka BILATZA funtzioarekin bilatzea