Funkcija VLOOKUP programa Excel se uporablja za iskanje in vračanje podatkov iz tabele podatkov, ki temelji na iskani vrednosti, ki jo izberete.
Običajno VLOOKUP zahteva, da je vrednost iskanja v najmanjšem stolpcu tabele podatkov in funkcija vrne drugo polje podatkov, ki se nahajajo v isti vrstici desno od te vrednosti.
Vendar s kombiniranjem funkcije VLOOKUP s funkcijo CHOOSE lahko ustvarite levo formulo za iskanje, ki:
- Dovoljuje, da je vrednost za iskanje iz katerega koli stolpca v podatkovni tabeli
- Vrne informacije, ki se nahajajo v poljubnem stolpcu levo od iskalne vrednosti
Opomba: Ta navodila veljajo za Excelove različice 2019, 2016, 2013, 2010 in Excel za Office 365.
01 od 05Vadnica: Uporaba funkcij VLOOKUP in CHOOSE v levi formuli za iskanje
Če želite ustvariti levo lookup formulo, prikazano na sliki primera, uporabite formulo:
= VLOOKUP ($ D $ 2, IZBERITE ({1,2}, $ F: $ F, $ D: $ D), 2, FALSE)
V tem primeru formula omogoča iskanje dela, ki so ga dobavile različne družbe, navedene v stolpcu 3 tabele podatkov.
Naloga funkcije CHOOSE v formuli je, da VLOOKUP prevzame prepričanje, da je stolpec 3 stolpec 1. Zato lahko ime podjetja uporabimo kot vrednost za iskanje, da poiščemo ime dela, ki ga zagotovi vsaka družba.
Vnesite podatke o vadnicah
- Vnesite naslov Dobavitelj v celico D1.
- Vnesite naslov Del v celici E1.
- V tabele vnesite podatke, prikazane na zgornji sliki D4 do F9
- Vrstice 2 in 3 ostanejo prazni, da ustrezajo iskalnim kriterijem in levi formulaciji, ki je bila ustvarjena v tej vadnici
Odprite pogovorno okno VLOOKUP
Čeprav je mogoče formulo vnesti neposredno v celico F1 v delovnem listu, mnogi ljudje težko sintakso s formulo.
V tem primeru je bolj varno uporabiti pogovorno okno VLOOKUP. Skoraj vse funkcije Excel imajo pogovorno okno, ki vam omogoča vnos vseh argumentov funkcije v ločeni vrstici.
- Kliknite na celico E2 delovnega lista. E2 je lokacija, na kateri bodo prikazani rezultati leve lookup formule.
- Kliknite na Formule jeziček traku.
- Kliknite na Iskanje in referenca možnost v traku, da odprete spustni seznam funkcij.
- Kliknite na VLOOKUP na seznamu, da odpre pogovorno okno funkcije.
Vnos argumentov v pogovorno okno VLOOKUP
Argumenti funkcije so vrednosti, ki jih funkcija uporablja za izračun rezultata.
V pogovornem oknu funkcije je ime vsakega argumenta v ločeni vrstici, ki mu sledi polje, v katerega se vnese vrednost.
Za vsako od VLOOKUP-ovih argumentov v pravilni vrstici pogovornega okna vnesite naslednje vrednosti, kot je prikazano na priloženi sliki.
Vrednost iskanja
Vrednost iskanja je polje informacij, ki se uporablja za iskanje matrike. VLOOKUP vrne drugo polje podatkov iz iste vrstice kot vrednost iskanja.
Ta primer uporablja referenco celice na lokacijo, kjer bo ime podjetja vneseno v delovni list. Prednost tega je, da je enostavno spremeniti ime podjetja brez urejanja formule.
- Kliknite na lookup_value vrstico v pogovornem oknu.
- Kliknite na celico D2 dodati to sklicevanje na celico lookup_value line.
- Pritisnite tipko F4 tipko na tipkovnici, da bi celica referenca absolutno - $ D $ 2.
Absolutne celične reference
Absolutne reference celic se uporabljajo za vrednosti iskanja in argumentov matrike tabele, da preprečijo napake, če se formula za iskanje kopira v druge celice v delovnem listu.
Vstop v funkcijo CHOOSE
Array table array je blok sosednjih podatkov, iz katerih se pridobijo specifične informacije.
Običajno se VLOOKUP sklicuje na desno od argumenta lookup value za iskanje podatkov v matriki. Če želite pogledati v levo, morate VLOOKUP preoblikovati, tako da preuredite stolpce v tabelo z uporabo funkcije CHOOSE.
V tej formuli funkcija CHOOSE opravlja dve nalogi:
- Ustvari tabelo, ki je le dva stolpca široka (stolpca D in F).
- Spreminja se vrstni red od stolpcev v tabeli, tako da je stolpec F prvi in stolpec D je drugi.
Vnos funkcij
Ko ročno vnašate funkcije, morajo biti vsi argumenti funkcije ločeni z vejico.
- V pogovornem oknu funkcije VLOOKUP kliknite na Table_array line.
- Vnesite naslednje IZBERITE funkcija:IZBERITE ({1,2}, $ F: $ F, $ D: $ D)
Številka indeksa stolpca
Običajno številka indeksa stolpca označuje, kateri stolpec tabele v tabeli vsebuje podatke, ki ste po njem. Vendar se v tej formuli nanaša na vrstni red stolpcev, ki jih določa funkcija CHOOSE.
Funkcija CHOOSE ustvari tabelo, ki je široka z dvema stolpoma, s stolpcem F, ki ji sledi stolpec D. Ker so zahtevane informacije - ime imena v stolpcu D, mora biti vrednost argumenta stolpca indeksa nastavljena na 2.
- Kliknite na Col_index_num vrstico v pogovornem oknu.
- Vnesite a 2 v tej vrstici.
Iskanje po območju
VLOOKUP-ov argument Range_lookup je logična vrednost (samo TRUE ali FALSE), ki označuje, ali želite VLOOKUP najti točno ali približno ujemanje z vrednostjo iskanja.
- Če je TRUE ali če je ta argument izpuščen, VLOOKUP vrne natančno ujemanje na Lookup_value ali, če ni natančno ujemanje, VLOOKUP vrne naslednjo največjo vrednost. Za formulo, ki jo je treba opraviti, je treba podatke v prvem stolpcu tabele_predmetja sortirati po naraščajočem vrstnem redu.
- Če FALSE, VLOOKUP uporablja le natančno ujemanje s Lookup_value. Če v prvem stolpcu Table_array obstajata dve ali več vrednosti, ki se ujemajo z vrednostjo iskanja, se uporabi prva ugotovljena vrednost. Če natančno ujemanje ni mogoče najti, se vrne napaka # N / A.
V tej vadnici, ker iščemo določeno ime dela, je Range_lookup nastavljen na Lažno tako da se s formulo vrne le natančna ujemanja.
- Kliknite na Range_lookup vrstico v pogovornem oknu.
- Vnesite besedo Lažno v tej vrstici, ki označuje, da želimo VLOOKUP vrniti natančno ujemanje podatkov, ki jih iščemo.
- Kliknite v redu da dokončate levo lookup formulo in zaprete pogovorno okno.
- Ker v celico D2 še nismo vnesli imena podjetja, se v celici E2 pojavi napaka # N / A.
Vračanje podatkov z levo formulo za iskanje
Če želite ugotoviti, katera podjetja dobavljajo dele, vnesite ime podjetja v celico D2 in pritisnite ENTER tipko na tipkovnici.
Ime dela je prikazano v celici E2.
- Kliknite na celico D2 v vašem delovnem listu.
- Tip Gadgets Plus v celico D2 in pritisnite ENTER tipko na tipkovnici.
- Besedilo "Gadgets" - del, ki ga ponuja družba Gadgets Plus - mora biti prikazan v celici E2.
Preizkusite formulo za iskanje, tako da vtipkate druga imena podjetij v celico D2 in ustrezno ime imena se mora pojaviti v celici E2.
Če se v celici E2 pojavi sporočilo o napaki, kot je # N / A, preverite črkovalne napake v celici D2.
04 od 05Ustvarjanje stolpca z dvema stolpcema
Sintaksa za funkcijo CHOOSE je:
= IZBERI (indeksna_številka, vrednost1, vrednost2, … vrednost254)
Funkcija CHOOSE tipično vrne eno vrednost s seznama vrednosti (vrednost1 do vrednost254) na podlagi vnesene indeksne številke.
Če je indeksna številka 1, funkcija vrne vrednost 1 s seznama; če je indeksna številka 2, funkcija vrne vrednost2 s seznama in tako naprej.
Ko vnesete več indeksnih števil, funkcija vrne več vrednosti v poljubnem želenem vrstnem redu. Pridobivanje CHOOSE za vrnitev več vrednosti se naredi z ustvarjanjem matrike.
Če želite vnesti matriko, obkrožite številke, ki so vnesene z zavihanimi oporniki ali oklepaji. Za indeksno številko sta vneseni dve številki:{ 1,2 }.
Treba je opozoriti, da izbira IZBER ni omejena na ustvarjanje tabele z dvema stolpcema. Z vključitvijo dodatne številke v matriko - na primer {1,2,3} - in dodatnega obsega v argumentu vrednosti, CHOOSE ustvari tabelo s tremi stolpci.
Dodatni stolpci vam omogočajo, da z levo formulo iskanja vrnete različne podatke tako, da spremenite argument VLOOKUP-ovega števila indeksov stolpca na številko stolpca, ki vsebuje želene informacije.
05 od 05Spreminjanje vrstnega reda stolpcev S funkcijo CHOOSE
V funkciji CHOOSE, uporabljeni v tej formuli:
IZBERITE ({1,2}, $ F: $ F, $ D: $ D)
razpon za stolpec F je naveden pred stolpcem D.
Ker funkcija CHOOSE nastavi tabelo VLOOKUP-a (vir podatkov za to funkcijo), se preklopi na vrstni red stolpcev v funkciji CHOOSE in se prenese na VLOOKUP.
Kar zadeva VLOOKUP, je tabela miza le dva stolpca široka s stolpcem F na levi in stolpca D na desni. Ker stolpec F vsebuje ime podjetja, ki ga želimo iskati, in ker stolpec D vsebuje imena imen, lahko VLOOKUP opravi svoje redne naloge iskanja pri iskanju podatkov, ki se nahajajo levo od iskalne vrednosti.
Zato lahko VLOOKUP uporabi ime podjetja, da bi našel del, ki ga dobavlja.
