Funkcija VLOOKUP. Uporaba funkcije VLOOKUP. Excel - VPR

Aplikacijski program

Ena od dobro znanih formul Excel je navpično gledanje. Uporaba funkcije VLOOK na prvi pogled se zdi precej zapletena, vendar je šele na začetku.

Kako deluje Excel

Pri delu s formulo VLR je treba upoštevati, da iskano vrednost išče izključno v stolpcih in ne po vrsticah. Za uporabo funkcije je potrebno najmanjše število stolpcev - dve, največja je odsotna.

Funkcija VLOOK išče določen kriterij, ki ima v tabeli kakršno koli obliko (besedilo, številčno, denarno, datum in čas itd.). V primeru iskanja zapisa izpiše (nadomesti) vrednost, ki je vnesena v isto vrstico, vendar iz zahtevanega stolpca tabele, to pomeni, da ustreza določenemu kriteriju. Če želene vrednosti ni mogoče najti, se izpiše napaka # N / A (v angleški različici # N / A).

Potreba po uporabi

Funkcija VLOOKUP prihaja do pomoči operaterja, kadar je v nadaljnjih izračunih, analizah ali napovedih hitro iskanje in uporaba določene vrednosti iz večje tabele. Glavna stvar pri uporabi te formule je zagotoviti, da je določeno območje iskanja pravilno izbrano. Vsebovati mora vse zapise, to je od prvega do zadnjega.

Najpogostejši primer uporabe VLF (Funkcija Excel) - to je primerjava ali dodajanje podatkov v dveh tabelah z uporabo določenega merila. Območja iskanja so velika in lahko sprejmejo na tisoče polj, ki so na različnih listih ali knjigah.

Funkcija VLF je prikazana, kako jo uporabiti, kako izvesti izračune, kot primer na zgornji sliki. Tu se obravnava tabela velikosti prodaje na drobno, odvisno od regije in upravitelja. Kriterij iskanja je določen upravitelj (njegovo ime in priimek) in iskana vrednost je znesek njegove prodaje.

Zaradi funkcije VLOOKUP se ustvari nova tabela, v kateri določeni iskani upravitelj hitro primerja svojo prodajno količino.

Algoritem za polnjenje formule

Formula VLOOK se nahaja v zavihku "Čarovnik za funkcije" in razdelek "Reference in nizi". Pogovorno okno funkcije ima naslednji prikaz:

Argumenti v formuli se vnesejo v vrstni red čakalne vrste:

• Vrednost iskanja je tisto, kar bi morala najti funkcija, opcije pa so celične vrednosti, njen naslov, ime, ki ga je dala operator. V našem primeru je to priimek in ime upravitelja.
• Tabela - obseg vrstic in stolpcev, v katerih se išče kriterij.
• Številka stolpca je njegova redna številka, v kateri se nahaja znesek prodaje, to je rezultat formule.
• Intervalni pogled. Ima vrednost bodisi FALSE ali TRUE. In FALSE vrne samo natančno ujemanje, TRUE - omogoča iskanje približne vrednosti.

Primer uporabe funkcije

Funkcija VLOOKUP ima lahko naslednji primer: pri obravnavanju podjetja v preglednici Excel stolpec A označuje ime izdelka in v stolpcu B ustrezno ceno. Če želite napisati stolpec v stolpcu C, morate poiskati strošek za določen izdelek, ki ga želite izpisati v stolpcu D.

Formula, napisana v D, bo videti takole: = VLOOKUP (C1-A1: V5- 2 0), tj = CDF (vrednotni želeni niz podatkov table- stolbtsa- zaporedno številko 0). Kot četrti argument, namesto 0, lahko uporabite FALSE.

Če želite izpolniti tabelo stavkov, je treba izpisano formulo kopirati v celoten stolpec D.

Če želite določiti območje delovnega območja podatkov, lahko uporabite absolutne reference. Če želite to narediti, $ znak pred ročno pred abecednimi in številskimi vrednostmi naslovov skrajnih levih in desnih celic tabele. V našem primeru formula ima obliko: = VPR (C1 - $ A $ 1: $ B $ 5- 2 - 0).

Napake v uporabi

Funkcija VLOOKUP ne deluje in v izhodnem stolpcu se prikaže sporočilo o napaki (# N / A ali # N / A). To se zgodi v takih primerih:

1. Formula je uvedena in kolona zahtevanih meril ni izpolnjena (v tem primeru stolpec C).
2. Stolpec C vsebuje manjkajočo vrednost v stolpcu A (v območju iskanja podatkov). Če želite preveriti razpoložljivost želene vrednosti, izberite stolpec meril in na zavihku »Uredi« - »Najdi« vstavite ta vnos in začnite iskanje. Če ga program ne najde, potem manjka.
3. Oblike celic stolpcev A in C (zahtevana merila) so različna, na primer ena ima eno besedo, druga pa številčno. Format celic lahko spremenite, če greste na urejanje celic (F2). Te težave običajno nastanejo pri uvozu podatkov iz drugih aplikacij. Da bi se izognili takim napakam, se v VLOOK formulo lahko vključijo naslednje funkcije: KEY ali TEXT. Izvajanje teh algoritmov samodejno pretvarja obliko celic.
4. V funkcijski kodi so nepopisni znaki ali presledki. Potem morate skrbno preveriti formulo za prisotnost vhodnih napak.
5. Določeno je približno iskanje, torej četrti argument funkcije VLOOK ima vrednost 1 ali TRUE in tabela ni razvrščena po naraščajoči vrednosti. V tem primeru mora biti stolpec iskanih kriterijev razvrščen po naraščajočem vrstnem redu.

Poleg tega so lahko pri organizaciji nove zbirne tabele navedeni iskalni kriteriji v poljubnem vrstnem redu in zaporedju in jih ni treba vključiti v celoten seznam (delna izbira).

Značilnosti uporabe kot intervalni pogled 1 ali TRUE

Napaka pod številko 5 je precej pogosta in je jasno prikazana na spodnji sliki.

V tem primeru je seznam imen glede na oštevilčenje razvrščen po naraščajočem vrstnem redu, vendar v padajočem vrstnem redu. In kot uporabljen intervalni pogled merilo je TRUE (1), ki takoj prekine iskanje, ko se najde večja vrednost od iskane, zato se ustvari napaka.

Pri uporabi 1 ali TRUE v četrtem argumentu se morate prepričati, da je stolpec z iskanimi kriteriji razvrščen po naraščajočem vrstnem redu. Z uporabo 0 ali FID ta potreba izgine, vendar tudi ni možnosti intervalnega skeniranja.

Upoštevajte, da je še posebej pomembno, da razvrstite intervalne tabele. V nasprotnem primeru bo funkcija VLOOKUP v celice oddala napačne podatke.

Druge nianse pri delu s funkcijo VLOOKUP

Za udobno delo s takšno formulo lahko podate obseg tabele, v kateri se izvede iskanje (drugi argument), kot je prikazano na sliki.

V tem primeru je upravičena prodajna miza. V ta namen je izbrana tabela, z izjemo naslovov stolpcev, in ime polja (na levi strani pod vrstico tabulatorja) je ime.

Druga možnost - naslov - pomeni izbiro vrste podatkov, nato pa pojdite v meni "Vstavi" - "Ime" - "Določi".

Če želite uporabiti podatke na drugem listu delovnega zvezka, uporabite funkcijo VLOOKUP, morate v drugem argumentu formule določiti lokacijo podatkovnega območja. Na primer, = VPR (A1-List2! $ A $ 1: $ B $ 5- 2-0), kjer je list 2! - je povezava na zahtevani list knjige in $ A $ 1: $ v $ 5 - naslov obsega dostopa do podatkov.

Primer organizacije izobraževalnega procesa z VPR

Primerno je, da v Excelu uporablja funkcijo VPR ne samo za podjetja, ki se ukvarjajo s trgovino, temveč tudi za izobraževalne ustanove, da optimizirajo proces primerjanja učencev (študentov) z njihovimi ocenami. Primeri teh nalog so prikazani na spodnjih slikah.

Obstajajo dve tabeli s seznami študentov. Ena s svojimi ocenami, druga označuje starost. Potrebno je primerjati obe tabeli, tako da se skupaj s starostjo študentov izpišejo tudi njihovi razredi, to je, da v drugem seznamu vnesete dodaten stolpec.

Funkcija VLOOK se odlično spoprijema z rešitvijo tega problema. V stolpcu G pod naslovom "Ocene" je zapisana ustrezna formula: = VPR (E4, B3: C13, 2, 0). Kopirati jo je treba v celoten stolpec tabele.

Zato bo funkcija VLR izdelala ocene, ki jih bodo prejeli nekateri študenti.

Primer organizacije iskalnika z VLOOK

Drug primer uporabe funkcije VLOOK je organizacija iskalnika, ko je v bazi podatkov v skladu z določenim merilom treba najti ustrezno vrednost. Torej, na sliki je prikazan seznam nad vzdevki živali in njihova pripadnost določeni vrsti.

S pomočjo VLP se ustvari nova tabela, v kateri je ime živali mogoče najti z imenom živali. Ti iskalniki so pomembni pri delu z velikimi seznami. Da ne bi ročno pregledali vseh zapisov, lahko hitro uporabite iskanje in dosežete želeni rezultat.