XLOOKUP funkcija
Sadržaj
Uvod
Pokušao sam objasniti načine pretraživanja podataka u Excelu preko funkcija XLOOKUP, VLOOKUP i INDEX/MATCH.
Željeni podaci
Ovo su podaci koje želimo dobiti pretraživanjem po nazivu osobe. Konkretno, zanimaju nas svi podaci koji se odnose na osobu “Pero”.
A ovo su podaci koje želimo dobiti pretraživanjem po plaći za siječanj. Traži se osoba i svi njezini podaci čija je plaća jednaka ili prva veća od tražene.
VLOOKUP i zašto ne?
Oni koji su radili u Excelu teško da su izbjegli raditi sa funkcijom VLOOKUP. Vjerujem da pripadam većini kojoj se nisu svidjele neke njezine mane i ograničenja. Posebno tu mislim na potrebu prilagođavanja sadržaja kako bi se formula VLOOKUP uopće mogla koristiti.
Princip rada formule je slijedeći: odredi se područje podataka u kojem se obavlja pretraživanje i vraća se određena vrijednost. Pretraživanje se obavlja po podacima u prvoj koloni unutar označenog ranga, a izlazni podaci mogu biti iz bilo koje kolone unutar određenog područja, počevši od prve kolone. Ako ste na primjer željeli obaviti pretraživanje po imenu osobe koja se nalazi u Koloni “A”, sve je u redu. Ali ako želite obaviti pretraživanje po najbližoj plaći iz kolone “C”, ne možete dobiti podatke iz prethodnih kolona “A” i “B”!
INDEX & MATCH
Većina Excel korisnika bi brzo uvidjela njezine mane i ograničenja, pa bi prešli na kombinaciju INDEX i MATCH (nadalje IM) funkcija. Jednom probaš IM i više se ne vraćaš na VLOOKUP. IM je nešto kompliciraniji izraz, kod kombiniranja sintaksi možda i nelogičan slijed parametara. Ali to je kombinacija koja je odrađivala svoj posao.
XLOOKUP
A onda se dogodio Office 365 i rođen je XLOOKUP. Neću lagati ako kažem da je ta funkcija trebala biti odavno kreirana. Neću posebno pisati sintaksu za upotrebu funkcije. Sve o njoj možete naći na službenoj Microsoft stranici. Ono što kod nje oduševljava je njezina jednostavnost i efikasnost.
Pogledajmo funkciju XLOOKUP u jednostavnoj akciji.
Primjer 1
Želimo znati u kojem Odjelu radi Pero? Ako ima više osoba koje se zovu Pero, uzima se prva po redu (moguće je izmijeniti način pretraživanja, ali da ne kompliciramo). U ćeliju H2 unesemo slijedeću formulu:
=XLOOKUP(Osoba;A2:A9;B2:B9;0)
Pogledamo li sadržaj kolone H2 u koju je unijeta formula, vidimo da smo dobili rezultat “A”. To je podatak iz ćelije B5, jer je prvi Pero nađen u retku 5, a podatak koji smo željeli dobiti – Odjel nalazi se u koloni B.
Primjer 2 – prikaz više podataka
Sada želimo da nam se prikažu SVI podaci vezani za Peru. U ćeliji H2 upišemo slijedeću formulu koja će kao izlazne obuhvatiti sve nađene podatke od B2 do E9. Znači, želimo dobiti sve podatke za nađenu osobu, osim samog naziva osobe. U ćeliju H2 upišemo slijedeću formulu:
=XLOOKUP(G2;A2:A9;B2:E9;0)
Dobili smo podatke koji se ne odnose samo na Odjel, već i na plaće po svim mjesecima.
Pogledajmo što se nalazi u traci za formule ako označimo ćeliju I2; tu se nalazi formula svijetlo sive boje (inače se ćelije označavaju s različitim bojama), što znači da je sadržaj ove ćelije dobiven prema formuli iz neke druge ćelije (ovdje iz H2) i ista se ne može mijenjati.
Primjer 3 – upotreba đokera
Poželimo li pretraživanje po dijelu teksta, funkcija XLOOKUP radi sa zamjenskim znakovima ili đokerima. Npr. znak “*” zamjenjuje bilo koji niz znakova i ako tražimo osobu koja u nazivu sadrži tekst “ero”, pronaći se će “Pero“. Ovo je formula:
=XLOOKUP(“*” & G2 & “*”;A2:A9;B2:E9;”Nema podataka”;2,1)
Primjer 4 – TRANSPOSE
I na kraju, ako Vas možda nervira način prikaza izlaznih podataka u retku, to se jednostavno može promijeniti ugnježđivanjem funkcije XLOOKUP unutar funkcije TRANSPOSE. Samo se upiše TRANSPOSE sa otvorenom i zatvorenom zagradom unutar kojih se kopira formula XLOOKUP:
=XLOOKUP(“*” & G2 & “*”;A2:A9;B2:E9;”Nema podataka”;2,1)
Primjer 5 – pretraživanje po plaći
I sada dolazimo do onoga što ne može napraviti funkcija VLOOKUP. Tražimo sve podatke iz retka u kojem je plaća za siječanj najbliža vrijednosti 11.000 kn, ali da je jednaka ili veća od tog iznosa. U ćeliji H3 unesemo slijedeću formulu:
=TRANSPOSE(XLOOKUP(H3;C4:C11;A4:B11;”Nema podataka”;1))
Pretraživanje plaće iz kolone “C” dobijemo bez ikakvih problema i podatke iz kolona “A” i “B”, što se preko VLOOKUP-a ne može dobiti.
U nastavku se nalazi video zapis nekih od prethodno navedenih primjera: