vsebina
Vse klasične funkcije iskanja in zamenjave tipov VPR (VLOOKUP), GPR (HLOOKUP), BOLJ IZPOSTAVLJENO (TEKMA) in njim podobni imajo eno pomembno lastnost – iščejo od začetka do konca, torej od leve proti desni ali od zgoraj navzdol po izvornih podatkih. Takoj, ko je najdeno prvo ujemanje, se iskanje ustavi in najde se samo prva pojavitev elementa, ki ga potrebujemo.
Kaj storiti, če moramo najti ne prvi, ampak zadnji pojav? Na primer zadnja transakcija za stranko, zadnje plačilo, zadnje naročilo itd.?
1. način: Iskanje zadnje vrstice z matrično formulo
Če originalna tabela nima stolpca z datumom ali zaporedno številko vrstice (naročilo, plačilo …), potem je naša naloga pravzaprav poiskati zadnjo vrstico, ki izpolnjuje dani pogoj. To lahko storite z naslednjo matrično formulo:
Tukaj:
- funkcija IF (ČE) eno za drugo preveri vse celice v stolpcu Pomoč in prikaže številko vrstice, če vsebuje ime, ki ga potrebujemo. Številko vrstice na listu nam poda funkcija LINE (VRSTICA), ker pa potrebujemo številko vrstice v tabeli, moramo dodatno odšteti 1, ker imamo glavo v tabeli.
- Nato funkcija MAX (MAX) izbere največjo vrednost iz oblikovanega niza številk vrstic, to je številko zadnje vrstice odjemalca.
- funkcija INDEX (KAZALO) vrne vsebino celice z najdeno zadnjo številko iz katerega koli drugega zahtevanega stolpca tabele (Oznaka naročila).
Vse to je treba vnesti kot formula matrike, tj:
- V Office 365 z nameščenimi najnovejšimi posodobitvami in podporo za dinamična polja lahko preprosto pritisnete Vnesite.
- V vseh drugih različicah boste morali po vnosu formule pritisniti bližnjico na tipkovnici Ctrl+Shift+Vnesite, ki ji bo samodejno dodal zavite oklepaje v vrstici s formulami.
2. način: Povratno iskanje z novo funkcijo LOOKUP
Napisal sem že dolg članek z videom o novi funkciji OGLED (XLOOKUP), ki se je pojavil v najnovejših različicah Officea, da bi nadomestil stari VLOOKUP (VLOOKUP). S pomočjo BROWSE je naša naloga rešena precej elementarno, ker. za to funkcijo (za razliko od VLOOKUP) lahko eksplicitno nastavite smer iskanja: od zgoraj navzdol ali od spodaj navzgor – za to je odgovoren njen zadnji argument (-1):
3. način. Poiščite niz z zadnjim datumom
Če imamo v izvornih podatkih stolpec s serijsko številko ali datumom, ki ima podobno vlogo, je naloga spremenjena – poiskati moramo ne zadnjo (najnižjo) vrstico z ujemanjem, temveč vrstico z zadnjim ( najvišji) datum.
Podrobno sem že razpravljal o tem, kako to storiti s klasičnimi funkcijami, zdaj pa poskusimo uporabiti moč novih funkcij dinamičnega polja. Za večjo lepoto in udobje originalno mizo pretvorimo tudi v »pametno« mizo s pomočjo bližnjice na tipkovnici Ctrl+T ali ukazi Domov – Oblikuj kot tabelo (Domov — Oblikuj kot tabelo).
Z njihovo pomočjo ta »ubijalski par« zelo elegantno reši naš problem:
Tukaj:
- Najprej funkcija FILTER (FILTER) izbere samo tiste vrstice iz naše tabele, kjer je v stolpcu Pomoč – ime, ki ga potrebujemo.
- Nato funkcija GRADE (RAZVRSTI) razvrsti izbrane vrstice po datumu v padajočem vrstnem redu, pri čemer je najnovejši posel na vrhu.
- funkcija INDEX (KAZALO) izvleče prvo vrstico, tj. vrne zadnjo trgovino, ki jo potrebujemo.
- In končno, zunanja funkcija FILTER odstrani dodatni 1. in 3. stolpec iz rezultatov (Oznaka naročila и Pomoč) in pusti samo datum in znesek. Za to se uporablja niz konstant. {0;1;0;1}, ki določa, katere stolpce želimo (1) ali ne (0) prikazati.
4. način: Iskanje zadnjega ujemanja v Power Query
No, zaradi popolnosti si poglejmo rešitev za naš problem povratnega iskanja z uporabo dodatka Power Query. Z njeno pomočjo se vse reši zelo hitro in lepo.
1. Pretvorimo našo izvirno tabelo v »pametno« s pomočjo bližnjice na tipkovnici Ctrl+T ali ukazi Domov – Oblikuj kot tabelo (Domov — Oblikuj kot tabelo).
2. Naložite ga v Power Query z gumbom Iz tabele/razpona tab datum (Podatki — iz tabele/razpona).
3. Našo tabelo razvrstimo (skozi spustni seznam filtra v glavi) po padajočem vrstnem redu datuma, tako da so zadnje transakcije na vrhu.
4… V zavihku Preoblikovanje izberite ekipo Skupina z (Pretvorba — Združi po) in nastavite razvrščanje po strankah ter kot združevalno funkcijo izberite možnost Vse vrstice (Vse vrstice). Nov stolpec lahko poimenujete poljubno – npr podrobnosti.
Po grupiranju bomo dobili seznam unikatnih imen naših strank in v stolpcu podrobnosti – tabele z vsemi transakcijami vsakega od njih, kjer bo prva vrstica zadnja transakcija, kar potrebujemo:
5. Z gumbom dodajte nov izračunani stolpec Stolpec po meri tab Dodaj stolpec (Dodaj stolpec — Dodaj stolpec po meri)in vnesite naslednjo formulo:
Tukaj podrobnosti – to je stolpec, iz katerega vzamemo tabele po strankah, in 0 {} je številka vrstice, ki jo želimo ekstrahirati (številčenje vrstic v Power Query se začne od nič). Dobimo stolpec z zapisi (Record), kjer je vsak vnos prva vrstica iz vsake tabele:
Ostaja še razširiti vsebino vseh zapisov z gumbom z dvojnimi puščicami v glavi stolpca Zadnji dogovor izbiranje želenih stolpcev:
… in nato izbrišite stolpec, ki ga ne potrebujete več podrobnosti z desnim klikom na naslov – Odstrani stolpce (Odstrani stolpce).
Po nalaganju rezultatov na list skozi Domov — Zapri in naloži — Zapri in naloži (Domov — Zapri in naloži — Zapri in naloži v…) dobili bomo tako lepo tabelo s seznamom zadnjih transakcij, kot smo si želeli:
Ko spremenite izvorne podatke, ne smete pozabiti posodobiti rezultatov z desnim klikom nanje – ukaz Posodobi in shrani (Osveži) ali bližnjico na tipkovnici Ctrl+druga+F5.
- Funkcija LOOKUP je potomec funkcije VLOOKUP
- Kako uporabljati nove funkcije dinamičnega polja SORT, FILTER in UNIC
- Iskanje zadnje neprazne celice v vrstici ali stolpcu s funkcijo LOOKUP