01 od 03
Poiščite približne primerjave podatkov z Excelovim VLOOKUP
Kako deluje funkcija VLOOKUP?
Excelova funkcija VLOOKUP, ki stoji za navidezno iskanje , lahko uporabite za iskanje določenih informacij, ki se nahajajo v tabeli podatkov ali baze podatkov.
VLOOKUP običajno vrne eno samo polje podatkov kot njegovo izhodišče. Kako to naredi:
- Vnesete ime ali lookup_value, ki pove VLOOKUP, v kateri vrstici ali zapisu tabele podatkov, da poiščete želene podatke
- Številko stolpca - znano kot col_index_num - dobite podatke, ki jih iščete
- Funkcija išče lookup_value v prvem stolpcu tabele podatkov
- VLOOKUP nato najde in vrne informacije, ki jih iščete iz drugega polja istega zapisa, s pomočjo priložene številke stolpca
Sortiranje podatkov najprej
Čeprav ni vedno zahtevano, je najpogosteje najbolje najprej razvrstiti obseg podatkov, ki jih VLOOKUP išče v naraščajočem vrstnem redu z uporabo prvega stolpca območja za tipski ključ.
Če podatki niso razvrščeni, lahko VLOOKUP vrne napačen rezultat.
Sintaksa in argumenti VLOOKUP funkcije
Sintaksa funkcije se nanaša na postavitev funkcije in vključuje ime, oklepaj in argumente funkcije.
Sintaksa za funkcijo VLOOKUP je:
= VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)
lookup _value - (potrebna) vrednost za iskanje - na primer količina, prodana na zgornji sliki
table_array - (obvezno) to je tabela podatkov, ki jih VLOOKUP išče, če želite poiskati informacije, ki ste jih izvedli.
- Table_array mora vsebovati vsaj dva stolpca podatkov
- Prvi stolpec običajno vsebuje lookup_value
col_index_num - (potrebna) številka stolpca želene vrednosti.
- Oštevilčevanje se začne s stolpcem search_key kot stolpcem 1
- Če je col_index_num nastavljen na število, ki je večje od števila stolpcev, izbranih v argumentu table_array , #REF! funkcija vrne napako
Range_lookup - (neobvezno) označuje, ali je obseg razvrščen po naraščajočem vrstnem redu.
- Podatki v prvem stolpcu se uporabljajo kot sortni ključ
- Boolova vrednost - TRUE ali FALSE so edine sprejemljive vrednosti
- Če izpuščena, je vrednost privzeto nastavljena na TRUE
- Če je nastavljen na TRUE ali izpuščen in prvi stolpec razpona ni razvrščen po naraščajočem vrstnem redu, se lahko pojavi napačen rezultat
- Če je nastavljen na TRUE ali izpuščen in natančno ujemanje z vrednostjo iskanja ni mogoče najti, se kot iskalni ključ uporablja najbližje ujemanje, ki je manjše po velikosti ali vrednosti
- Če je nastavljen na FALSE, VLOOKUP sprejema le natančno ujemanje za iskalno vrednost . Če obstaja več primerjalnih vrednosti, se vrne prva ustrezna vrednost
- Če je nastavljeno na FALSE in ni najdena nobena ustrezna vrednost za search_key , funkcija vrne # N / A
Primer: poiščite diskontno stopnjo za kupljeno količino
Primer na zgornji sliki uporablja funkcijo VLOOKUP, da najde diskontno stopnjo, ki se razlikuje glede na količino kupljenih predmetov.
Primer kaže, da je popust za nakup 19 postavk 2%. To je zato, ker stolpec Količina vsebuje obsege vrednosti. Zato VLOOKUP ne najde natančnega ujemanja. Namesto tega je treba najti približno ujemanje, da se vrne pravilna diskontna stopnja.
Iskanje približnih zadetkov:
- podatke razvrstite v tabelo v naraščajočem vrstnem redu;
- nastavite argument range_lookup na TRUE
V tem primeru se za iskanje popusta za količine kupljenega blaga uporabi naslednja formula, ki vsebuje funkcijo VLOOKUP.
= VLOOKUP (C2, $ C $ 5: $ D $ 8,2, TRUE)
Čeprav je ta formula mogoče vnesti v celico delovnega lista, je druga možnost, kot je uporabljena s spodaj navedenimi koraki, uporabiti pogovorno okno funkcije za vnos svojih argumentov.
- Uporaba pogovornega okna pogosto olajša pravilno vnašanje argumentov funkcije.
Odpiranje pogovornega okna VLOOKUP
Koraki, uporabljeni za vstop v funkcijo VLOOKUP, prikazane na sliki zgoraj, v celico B2 so:
- Kliknite celico B2, da postanete aktivna celica - mesto, na katerem so prikazane funkcije funkcije VLOOKUP
- Kliknite zavihek Formule .
- Iz traku izberite Lookup & Reference in odprite funkcijski spustni seznam
- Kliknite na VLOOKUP na seznamu, da odpre pogovorno okno funkcije
02 od 03
Vnesite argumente funkcije VLOOKUP funkcije Excel
Kazanje na celične reference
Argumenti za funkcijo VLOOKUP se vnesejo v ločene črte pogovornega okna, kot je prikazano na zgornji sliki.
Referenčne številke celic, ki jih je treba uporabiti kot argumente, lahko vnesete v pravilno vrstico ali pa, kot je storjeno v spodnjih korakih, ki kažejo, kar vključuje poudarjanje želenega obsega celic z miškinim kazalcem, jih lahko uporabite za vnos v pogovorno okno .
Prednosti uporabe kazalcev so:
- Hitreje kot tipkanje;
- V pravilne referenčne številke so vključene manj napak.
Uporaba relativnih in absolutnih celičnih referenc z argumenti
Ni redko uporabljati več kopij VLOOKUP, da bi vrnili različne podatke iz iste tabele podatkov. Da bi to olajšali, se lahko pogosto VLOOKUP kopira iz ene celice v drugo. Ko so funkcije kopirane v druge celice, je treba paziti, da so referenčne številke celic pravilne glede na novo lokacijo funkcije.
Na zgornji sliki dolarski znaki ( $ ) obsegajo referenčne celice za argument table_array , ki nakazujejo, da so absolutne reference celic , kar pomeni, da se ne bodo spremenile, če se funkcija kopira v drugo celico. To je zaželeno, ker bi večkratne kopije VLOOKUP vse omenile isto tabelo podatkov kot vir informacij.
Oznaka celice, uporabljena za lookup_value, na drugi strani ni obkrožena z znaki dolarja, zaradi česar je relativna referenca celic. Referenčne reference celic se spreminjajo, ko se kopirajo, da odražajo njihovo novo lokacijo glede na položaj podatkov, na katere se nanašajo.
Vnos argumentov funkcij
- V pogovornem oknu VLOOKUP kliknite vrstico za poizvedbo _value
- Kliknite celico C2 na delovnem listu, da vnesete referenco celice kot argument za iskanje
- Kliknite na vrstico Table_array pogovornega okna
- Označite celice C5 do D8 na delovnem listu, da vnesete to območje kot argument Table_array - tabele v tabeli niso vključene
- Pritisnite tipko F4 na tipkovnici, da spremenite obseg v absolutne reference celic
- Kliknite vrstico Col_index_num v pogovornem oknu
- V tej vrstici vnesite 2 kot argument Col_index_num , ker so diskontne stopnje v stolpcu 2 v tabeli Table_array
- Kliknite vrstico Range_lookup pogovornega okna
- Vnesite besedo True kot argument Range_lookup
- Pritisnite tipko Enter na tipkovnici, da zaprete pogovorno okno in se vrnete na delovni list
- Odgovor 2% (diskontna stopnja za kupljeno količino) se mora pojaviti v celici D2 delovnega lista
- Ko kliknete celico D2, se v vrstici s formulo nad delovnim listom prikaže celotna funkcija = VLOOKUP (C2, $ C $ 5: $ D $ 8,2, TRUE)
Zakaj je VLOOKUP vrnil 2% kot rezultat
- V primeru kolona Količina ne vsebuje točnega ujemanja za vrednost iskanja 19.
- Ker je argument is_sorted nastavljen na TRUE, bo VLOOKUP poiskal približno vrednost za vrednost iskanja .
- Najbližja vrednost v velikosti, ki je še vedno manjša od vrednosti iskanja 19, je 11.
- VLOOKUP zato išče popustni odstotek v vrstici, ki vsebuje 11, in posledično vrne diskontno stopnjo 2%.
03 od 03
Excel VLOOKUP ne deluje: # N / A in #REF napake
Sporočila o napakah VLOOKUP
Naslednja sporočila o napakah so povezana z VLOOKUP.
A # N / A ("vrednost ni na voljo") Prikazana je napaka Če:
- Lookup _value ni v prvem stolpcu argumenta za obseg
- Argument Table_array je netočen. Na primer, argument lahko vključuje prazne stolpce na levi strani območja
- Argument Range_lookup je nastavljen na FALSE in natančno ujemanje s parametrom search_key ni mogoče najti v prvem stolpcu območja
- Argument Range_lookup je nastavljen na TRUE in vse vrednosti v prvem stolpcu razpona so večje od iskanja_key
#REF! ("referenca izven obsega") Napaka je prikazana, če:
- Argument Col_index_num je večji od števila stolpcev v tabeli