Excel | Funkcija XLOOKUP

12. 06. 2020 Računalniški tečaji

Pri delu s šifranti je pri zapisovanju podatkov v našo delovno tabelo nepogrešljiva uporaba funkcij VLOOKUP in HLOOKUP, ki sta dve izmed najmočnejših iskalnih funkcij v Excelu. Svoji zmogljivosti navkljub, pa imata tudi svoje omejitve:

• Znotraj šifranta morajo iskane vrednosti (na podlagi katerih izpisujemo iz šifranta v delovno tabelo) vedno nastopati v skrajno levem stolpcu pri VLOOKUP oz. v skrajno zgornji vrstici pri iskanju s HLOOKUP funkcijo, 
• Če iskane vrednosti ni v šifrantu, je rezultat funkcije #N/A, ki označuje napako. Če želimo takšne napake nadzorovati, jih izpisovati po svoje, moramo iskalno funkcijo gnezditi znotraj funkcije IFERROR, s katero nadzorujemo napake. 

Uporabnikom paketa Microsoft 365 je že na voljo funkcija XLOOKUP, ki lahko nadomesti obe zgoraj omenjeni funkciji, njena sintaksa pa vsebuje tudi neobvezen argument, s katerim lahko brez uporabe dodatnega gnezdenja nadzorujemo napake. Prav tako ni omejitev glede tega, kje v šifrantu se nahaja stolpec z iskanimi vrednostmi. 

Sintaksa XLOOKUP

=XLOOKUP (iskana_vrednost; iskalni_niz; vrni_niz; [če_ni_najdeno]; [način_ujemanja]; [način_iskanja]) 

Preizkusimo funkcijo na praktičnem primeru

V podjetju elektronsko beležimo prihod in odhod zaposlenih na delovno mesto. Z uporabo kartice se na glavnem vhodu v sistem beleži ID zaposlenega in njegovi uri prihoda ter odhoda. Vodstvo želi imeti vpogled v število ur, ki jih je  na določen dan v tednu zaposleni že dosegel. Iz sistema je možen izpis, ki prikazuje ID zaposlenega in preračunano število ur. Zaradi preglednejšega prikaza, bi želeli na podlagi ID zaposlenega iz splošnega šifranta vseh zaposlenih izpisati njegovo ime in priimek ter oddelek njegovega delovnega mesta. 

Poglejmo si, kako manjkajoče podatke s pomočjo funkcije XLOOKUP prenesemo iz šifranta v delovno tabelo sistemskega izpisa. 

Pri uvozu podatkov v Excel sta ustvarjena in s podatki zapolnjena stolpca ID zap. in Št. oprav. ur_teden. Dodamo še dva stolpca za podatke o imenu in priimku (Ime in priimek) zaposlenega in oddelku, ki mu zaposleni pripada (Oddelek). Manjkajoče podatke s pomočjo funkcije XLOOKUP prenesemo iz šifranta, ki vsebuje podatke zaposlenih. 

 

Šifrant: 

Iskana_vrednost v našem primeru je ID zap. Tej vrednosti želimo pripisati pripadajoča ime in priimek iz Šifrant_Zaposleni.

Pri argumentu iskalni_niz označimo celoten stolpec v tabeli šifranta, v katerem so nanizani ID zaposlenih (v našem šifrantu so ti zapisani v stolpcu A).

Pri tretjem argumentu, vrni_niz, moramo označiti stolpec v šifrantu, ki vsebuje podatke za izpis. V našem primeru se ime in priimek nahajata v D stolpcu šifranta. Ne pozabimo na fiksiranje celic drugega in tretjega argumenta. 

Sledijo neobvezni argumenti funkcije. Z argumentom [če_ni_najdeno] definiramo, kaj naj funkcija vrne kot rezultat, če iskane vrednosti (torej ID zap.) ne bo našla v šifrantu. V našem primeru smo določili, naj se izpiše besedilo: neustrezen ID.

Pri naslednjem argumentu [način_ujemanja] določimo vrsto ujemanja pri iskanju. Na voljo so nam naslednje možnosti: 

• Natančno ujemanje – privzeta nastavitev. Če funkcija ne bo našla iskane vrednosti, bo vrnila rezultat #N/A, 
• Natančno ujemanje ali naslednji manjši argument - če funkcija ne bo našla iskane vrednosti, bo kot rezultat izpisala naslednji manjši element, 
• Natančno ujemanje ali naslednji večji argument – če funkcija ne bo našla iskane vrednosti, bo kot rezultat izpisala naslednji večji element, 
• Ujemanje nadomestnega znaka – uporabimo lahko nadomestne znake kot primerjalne pogoje. 

Ker ima vsak zaposleni svoj lasten ID, bomo pod načinom ujemanja izbrali možnost natančnega ujemanja. Pri zadnjem argumentu [način_iskanja] določimo, kako naj funkcija izvede iskanje znotraj šifranta. Za naše potrebe pustimo privzeto možnost iskanja od prvega proti zadnjemu elementu šifranta.

• Od prvega proti zadnjemu elementu – privzeta nastavitev, 
• Od zadnjega proti prvemu, 
• Binarno iskanje, ki se opira na šifrant razvrščen po naraščajočem vrstnem redu, 
• Binarno iskanje, ki se opira na šifrant razvrščen v padajočem vrstnem redu. 

Po potrditvi funkcije se nam izpišeta pripadajoča ime in priimek prvemu ID-ju v naši tabeli z izpisom ur. Za zapis pripadajočega oddelka, znova uporabimo funkcijo XLOOKUP. Njeni argumenti so enaki kot v primeru izpisovanja imena in priimka, s tem da moramo spremeniti tretji argument, [vrni_niz], kjer za izpis oddelka v šifrantu označimo stolpec K.

 

Enak rezultat bi dosegli tudi z uporabo funkcije VLOOKUP. Kot je bilo omenjeno že na začetku, nam dodatni argumenti omogočajo nadzor napak brez uporabe dodatnih funkcij. Poleg tega je zdaj precej poenostavljeno definiranje stolpca, iz katerega želimo izpisovati podatke – pri uporabi npr. VLOOKUP funkcije smo morali stolpec izpisovanja definirati s številko stolpca znotraj šifranta, kar je lahko predstavljalo precej nerodno  in lahko zmotljivo nalogo. Pri funkciji XLOOKUP pa stolpec izpisovanja v šifrantu preprosto označimo.