Uporaba in definicija te Excelove funkcije
VLOOKUP je Excelova funkcija, s katero lahko uporabnik išče in ocenjuje vsebino tabele. Ta funkcija je na voljo v različicah programa Excel 2007 za Windows in Mac.
Kaj je VLOOKUP?
Možne uporabe VLOOKUP -a je treba razložiti tukaj s primerom: V tem ste velik ljubitelj literature in ste zato ustvarili svojo lastno preglednico Excel, v kateri lahko skrbno razvrstite knjige, ki ste jih zbrali. Vsako delo je vneseno z informacijami o naslednjih kategorijah:
-
avtor
-
naslov
-
Številka strani
-
Leto izdaje
Zdaj bi želeli prijatelju dati knjižni namig, ki bi ga imel s seboj pri naslednjem srečanju. Na žalost lahko pomislite le na avtorja, ne pa na naslov knjige. Tu nastopi VLOOKUP, ki lahko s to vhodno vrednostjo z enim zamikom zavrže informacije, ki jih iščete.
Kako se uporablja VLOOKUP?
Preden sploh razmišljamo o formuliranju formul, je treba ugotoviti, kje se bodo pozneje nahajala vnosna polja in različna izhodna polja. Če želite to narediti, je smiselno ustvariti ločeno tabelo, ki je sprva prazna in tako omogoča prostor za omenjene informacije. Če boste oblikovali to novo tabelo na primeru obstoječe tabele, boste imeli kasneje prihranek časa.
Na tej podlagi lahko formulo VLOOKUP ustvarite ročno ali samodejno ustvarite v Excelu. Za začetnike je vredno uporabiti slednji pristop, da postopoma spoznajo zgradbo in učinek formule. To naredite tako, da na zavihku "Formule" izberete gumb za "Vstavi funkcijo". VLOOKUP je skrit v oknu, ki se odpre. Po potrditvi se znova odpre okno, v katerem lahko vnesete štiri parametre formule. To so:
-
Merilo iskanja
-
matrika
-
Indeks stolpca
-
Area_reference
Surovi osnutek formule je torej videti tako:
= VLOOKUP (iskalni kriterij, matrika, indeks stolpca, obseg_povezave)
in v eni od možnih aplikacij, kot je ta:
= VLOOKUP (H3; A3: E40; 5)
Merilo iskanja
Da funkcija ve, katero vrednost je treba uporabiti kot izhodišče, je vrstica, ki je bila dva koraka prej izbrana kot vnosno polje, zabeležena v polju "Merilo iskanja". V našem primeru je tam vneseno ime avtorja knjige "Phillip Pulmann". Zaradi tega je formula prilagodljiva in je ni treba znova prilagajati takoj, ko se vnesena vrednost spremeni.
matrika
Vnosno polje "Matrix" opisuje tabelo, v kateri je mogoče najti informacije, ki jih je treba izvesti. Ta posebna matrika tako vsebuje tudi stolpce za naslov knjige, številko strani in leto izdaje.
Matrica je enkrat v celoti izbrana brez naslovov od zgornjega levega do spodnjega desnega roba. Na ta način Excel ve, katero vsebino je treba upoštevati pri ocenjevanju.
Indeks stolpca
Polje za vnos "indeksa stolpca" uporabnika pozove, da določi stolpec matrike, v katerem je navedena samo iskana vrednost. Dodelitev stolpcev je oštevilčena kronološko. To pomeni, da prvi stolpec tabele prejme vrednost 1, drugi vrednost 2 itd. V našem primeru to ustreza indeksu stolpca 1 za avtorja, indeksu stolpca 2 za naslov, indeksu stolpca 3 za številko strani in stolpec indeks 4 za leto objave.
Da bo tabela čim bolj prilagodljiva, je mogoče namesto številke povezati naslov stolpca. Prednost tega je, da lahko formulo brez težav prenesemo tudi v druge vrstice, saj lahko naslov stolpca vsakič prilagodljivo prilagodimo.
Pozor: VLOOKUP bere matriko od leve proti desni, zato mora biti indeks stolpca postavljen desno od stolpca za iskalno merilo, da ga funkcija upošteva!
Area_reference
Parameter "Range_Lookup" dokonča formulo VLOOKUP z določitvijo natančnosti, s katero se tabela ovrednoti. Vendar se razlikuje od prej omenjenih sestavin formule, ker ni obvezna. Če vrednost 0 vnesete za "napačno", Excel išče samo vrednost, ki je bila podana kot merilo iskanja. Z vrednostjo 1 za "true" pa se iskanje očitnih vrednosti nadaljuje, če natančne vrednosti ni mogoče najti.
Določanje tega parametra ni obvezno, ker je vrednost 1 privzeto nastavljena. Ta nastavitev bo uporabna pozneje v naprednem VLOOKUP -u z več kriteriji iskanja.
Združitev
Takoj, ko so nastavljeni vsi potrebni parametri, lahko uporabite VLOOKUP. Ko vnesete iskalno merilo in potrdite funkcijo, se vrednost, ki jo iščete, prikaže v vrstici, ki je bila definirana kot izhodno polje.
V našem primeru je zdaj prikazan naslov knjige "Zlati kompas", ki ustreza avtorju. Če želite hitro ugotoviti številko strani in leto objave, ni treba storiti nič drugega kot povleči obstoječo formulo VLOOKUP v naslednje celice. To je tako enostavno, ker je indeks stolpcev VLOOKUP povezan z naslovom stolpca prve tabele, druga tabela pa je strukturirana v istem vrstnem redu.
V primeru, da se tabele med seboj razlikujejo ali da kljub vsemu pride do napake, lahko formulo VLOOKUP spremenite tudi ročno. Če želite to narediti, se predzadnja številka indeksa stolpca ujema s stolpcem nove vrednosti, ki jo želite izpisati.
VLOOKUP z več iskalnimi merili
Dovolj pogosto se zgodi, da eno samo iskalno merilo ni dovolj za natančno oceno velike Excelove tabele. Potem je smiselno zagnati VLOOKUP z več iskalnimi merili. Če želite to narediti, je treba obstoječo formulo dopolniti z dodatno funkcijo IF. Na ta način je med prijavo mogoče upoštevati do osem različnih iskalnih kriterijev.
VLOOKUP v več Excelovih preglednicah
Če iskalnega kriterija ni mogoče najti le v eni tabeli, ampak po možnosti tudi v drugi, lahko formulo VLOOKUP ustrezno prilagodimo. V ta namen morate pred obstoječo formulo postaviti funkcijo if in funkcijo ISERROR. Za to je potrebnih pet parametrov:
-
Merilo iskanja
-
Matrix1 in Matrix 2
-
Indeks stolpca1 in indeks stolpca2
Rezultat izgleda takole:
= IF (ISERROR (VLOOKUP (iskalni kriterij, matrika1, stolpec-indeks1, 0));
VLOOKUP (iskalno merilo; matrika2; indeks stolpca2,0); VLOOKUP (iskalni kriterij; matrika1; indeks stolpca1;))
in v eni od možnih aplikacij, kot je ta:
= IF (NAPAKA (VLOOKUP (E5, A5: B9,2, 0)), VLOOKUP (E5, A13: B17,2, 0), VLOOKUP (E5, A5: B9,2, 0))
Merilo iskanja se uporablja za vstavljanje vrednosti, ki jo je treba iskati, v dve tabeli. Matrix1 in Matrix2 definirata ustrezna območja celic obeh tabel. Indeks stolpca1 in indeks stolpca2 se uporabljata za podrobnejšo določitev stolpcev ustreznih tabel.
Če se vrednost, ki jo iščete, pojavi v obeh tabelah, bo Excel prikazal rezultat iz prve tabele. Če pa vrednosti ne najdete v nobeni od obeh tabel, se prikaže sporočilo o napaki. Prednost formule je, da ni nujno, da sta seznama enake strukture ali enake velikosti.
Z VLOOKUP dodelite vrednosti kategorijam
Dodatna funkcija VLOOKUP omogoča, da se navedene vrednosti samodejno razdelijo na črke in predikate po vaši izbiri. V našem prejšnjem primeru je treba za vrsto knjige vstaviti dodaten stolpec tabele. Knjige z dolžino do 50 strani bi morale spadati v žanr novele, knjige od 51 do 150 strani pa so dodeljene noveli in od 151 strani romanu. Da bi to omogočili, v VLOOKUP -u ni potrebna dodatna formula, samo uporaba kodrastih oklepajev »{}«. Končana formula izgleda tako:
= VLOOKUP (B1; {1. "Kratka zgodba"; 51. "Novela"; 151. "Roman"}; 2)
Vsebina kodrastih oklepajev označuje matriko, ki opredeljuje območje ustrezne vrste knjige. Dodelitev dolžine stranice ustreznemu rodu je zato umeščena v kodraste oklepaje. Formula uporablja pare vrednosti, od katerih je vsaka ločena s točko. Matrika {1. "Kratka zgodba"; 51. "Novela"; 151. "Novela"} se bere na naslednji način:
"Od 1 oddaje kratka zgodba, od 51 prikaži novelo, od 151 prikaži roman."
To matrico je mogoče enostavno prilagoditi različnim nalogam. To na eni strani zadeva velikost in število matric ter njihovo oznako. Tako je mogoče namesto posameznih črk izpisati nize ali številke. Vse kar morate storiti je, da prilagodite črke v formuli.
VLOOKUP na več delovnih listih
Druga funkcija VLOOKUP -a uporabnikom omogoča povezovanje vsebine, ki se nahaja v različnih preglednicah. V našem primeru je ta možnost lahko uporabna, ko se informacije najprej razvrstijo na različnih delovnih listih in nato posodobijo v tabeli povzetka.
Predstavljajte si, da poleg knjig v preglednici Excel navedete tudi zbrane filme. Nato združite obe zbirki v eno veliko tabelo.
Prednost tega postopka ni le v povečanem vrstnem redu, ampak tudi v izogibanju morebitnim napakam. Če želite ustvariti nov vnos ali posodobiti obstoječega, vam ni treba iskati v veliki tabeli, ampak lahko dostopate do manjših. Vrednosti se nato samodejno prenesejo v zbirno tabelo Excel. Zaradi tega je ponovno pisanje v veliko tabelo odveč, kar se v najboljšem primeru izogne nesrečni potezi in kasnejšemu povezovanju sporočil o napakah.
Kako izgleda formula?
Ta funkcija je znova omogočena z vstavitvijo druge formule. Medtem ko je za iskanje po več merilih potrebna dodatna formula IF, je za delo z več delovnimi listi potrebna formula INDIRECT. To omogoča, da se za matriko VLOOKUP določi obseg iz druge preglednice.
= VLOOKUP (iskalno merilo; INDIRECT (matrika); indeks stolpca; obseg_povezave)
Pozor: Ta formula bo delovala le, če imajo posamezne tabele na različnih listih enaka imena kot naslovi stolpcev splošne tabele. Celotne tabele lahko poimenujete v "polju z imenom" zgoraj levo nad mrežo celic. Tabele, ki so že poimenovane, si lahko ogledate s kombinacijo tipk Ctrl + F3.
Obravnavanje nastajajočih sporočil o napakah
Delo s povezanimi tabelami Excel lahko povzroči neželene težave. To vključuje zlasti izpis napačnih vrednosti. V primeru, da se prikaže napačna vrednost 0, je v nastavitvah Excela majhna težava, ki jo je mogoče hitro odpraviti.
Pogosto sporočilo o napaki #NV pa je namerna funkcija VLOOKUP -a, ki uporabniku pokaže, da zahtevana vrednost ni na voljo. To opombo lahko s pomočjo formule oblikujemo drugače.
VLOOKUP - pregled
VLOOKUP je uporabna Excelova funkcija, ki jo lahko uporabite za iskanje in ocenjevanje tabel. Njegove prednosti se kažejo v uporabniku prijazni in prilagodljivi uporabi. Na ta način lahko ima funkcija vsakdo, ki redno dela z Excelovimi tabelami. Naj gre za zasebnega zbiralca, ki ustvarja lastne majhne tabele, ali velikega podjetja, ki obdeluje bistveno obsežnejše nabore podatkov.
Če pa imate še vedno neodgovorjene zahteve, ki jih VLOOKUP ni mogel izpolniti, se lahko veselite dodatne možnosti Excel: Microsoft uporabnikom programa Excel 365 od začetka 2022-2023–2022 ponuja nov XLOOKUP. To temelji na kompetencah VLOOKUP -a in jih dopolnjuje z dodatnimi, včasih celo enostavnejšimi funkcijami. Zato se na tej točki odpre tudi nova rutina pri vrednotenju podatkov.
