Formula levo iskanje po Excelu Uporaba VLOOKUP

01 od 03

Poiščite podatke v levo

Excel levo iskanje Formula Pregled

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 levem stolpcu tabele podatkov, funkcija pa vrne drugo polje podatkov, ki se nahajajo v isti vrstici na desni strani te vrednosti.

S kombiniranjem VLOOKUP-a s funkcijo CHOOSE ; vendar lahko ustvarite levo lookup formulo, ki bo:

• dovoljuje, da se izbirna vrednost izbere iz katerega koli stolpca v podatkovni tabeli
• informacije o vrnitvi, ki se nahajajo v katerem koli stolpcu levo od iskalne vrednosti

Primer: Uporaba funkcij VLOOKUP in CHOOSE v levi formuli za iskanje

Spodaj opisani koraki ustvarjajo levo formulo za iskanje, prikazano na zgornji sliki.

Formula

= VLOOKUP ($ D $ 2, IZBERITE ({1,2}, $ F: $ F, $ D: $ D), 2, FALSE)

omogoča, da najdete del, ki ga ponujajo različna podjetja, navedena v stolpcu 3 tabele podatkov.

Naloga funkcije CHOOSE v formuli je, da VLOOKUP prevzame prepričanje, da je stolpec 3 dejansko stolpec 1. Zato lahko ime podjetja uporabimo kot vrednost za iskanje, da bi našli ime dela, ki ga zagotovi vsaka družba.

Vadbeni koraki - Vnos podatkov o vadnicah

1. V navedene celice vnesite naslednje naslove: D1 - dobavitelj E1 - del
2. V tabelo podatkov, prikazanih na sliki zgoraj, vnesite celice od D4 do F9
3. Vrstice 2 in 3 ostanejo prazna, da se prilagodi iskalnim kriterijem in levi formulaciji, ki je bila ustvarjena v tej vadnici

Začetek formule levo iskanje - Odprite pogovorno okno VLOOKUP

Čeprav je mogoče neposredno natipkati formulo zgoraj neposredno v celico F1 v delovnem listu, mnogi ljudje težko sintakso s formulo.

Druga možnost, v tem primeru, je uporaba pogovornega okna VLOOKUP. Skoraj vse funkcije Excel imajo pogovorno okno, ki vam omogoča vnos vseh argumentov funkcije v ločeni vrstici.

Vadbeni koraki

1. Kliknite na celico E2 delovnega lista - mesto, na katerem bodo prikazani rezultati leve lookup formule
2. Kliknite jeziček Formule v traku
3. V traku kliknite možnost » Iskanje in referenca «, da odprete spustni seznam funkcij
4. Kliknite na VLOOKUP na seznamu, da odpre pogovorno okno funkcije

02 od 03

Vnos argumentov v pogovorno okno VLOOKUP - Kliknite, če si želite ogledati večjo sliko

Argumenti VLOOKUPA

Argumenti funkcije so vrednosti, ki jih funkcija uporablja za izračun rezultata.

V pogovornem oknu funkcije se ime vsakega argumenta nahaja v ločeni vrstici, ki mu sledi polje, v katerem se vnese vrednost.

Za vsako od VLOOKUP-ovih argumentov v pravilni vrstici pogovornega okna vnesite naslednje vrednosti, kot je prikazano na zgornji 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.

Vadbeni koraki

1. V pogovornem oknu kliknite vrstico lookup_value
2. Kliknite celico D2, če želite dodati referenco celice na vrstico lookup_value
3. Pritisnite tipko F4 na tipkovnici, da bo celica referenca absolutna - $ D $ 2

Opomba: Absolutne referenčne številke celic so uporabljene za iskanje vrednosti in razpredelnice nizov tabel, da preprečimo napake, če se formula za iskanje kopira v druge celice v delovnem listu.

Tabela Array: Vstop v funkcijo CHOOSE

Array table array je blok sosednjih podatkov, iz katerih se pridobijo specifične informacije.

Običajno se VLOOKUP sklicuje neposredno na argument 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:

1. ustvari tabelo, ki je le dva stolpca široka - stolpce D in F
2. spremeni desno na levi vrstni red stolpcev v matriki tabele, tako da je stolpec F prvi, stolpec D pa drugi

Podrobnosti o tem, kako funkcijo CHOOSE opravlja te naloge, najdete na strani 3 vadnice .

Vadbeni koraki

Opomba: Pri vnosu funkcij ročno, morajo biti vsi argumenti funkcije ločeni z vejico "," .

1. V pogovornem oknu funkcije VLOOKUP kliknite vrstico Table_array
2. Vnesite to funkcijo CHOOSE
3. 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. V tej formuli; vendar se 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 iskane informacije - ime imena - v stolpcu D, mora biti vrednost argumenta stolpca indeksa nastavljena na 2.

Vadbeni koraki

1. V pogovornem oknu kliknite vrstico Col_index_num
2. V tej vrstici vnesite 2

Iskanje razdalje

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 je FALSE, bo VLOOKUP uporabil natančno ujemanje samo 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, bo Range_lookup nastavljen na False, tako da bo formula vnesla le natančna ujemanja.

Vadbeni koraki

1. V pogovornem oknu kliknite vrstico Range_lookup
2. V tej vrstici vnesite besedo False, ki označuje, da želimo VLOOKUP vrniti natančno ujemanje podatkov, ki jih iščemo
3. Kliknite V redu, da dokončate levo lookup formulo in zaprete pogovorno okno
4. Ker še nismo vnesli imena podjetja v celico D2, mora biti napaka # N / A v celici E2

03 od 03

Testiranje leve formule za iskanje

Vračanje podatkov z levo formulo za iskanje

Če želite ugotoviti, katera podjetja dobavljajo dele, vnesite ime podjetja v celico D2 in pritisnite tipko ENTER na tipkovnici.

Ime dela bo prikazano v celici E2.

Vadbeni koraki

1. Kliknite na celico D2 v vašem delovnem listu
2. Vtipkajte Gadgets Plus v celico D2 in pritisnite tipko ENTER na tipkovnici
3. Gadgets za besedilo - del, ki ga ponuja družba Gadgets Plus - morajo biti prikazani v celici E2
4. Preizkusite formulo za iskanje, tako da vtipkate druga imena podjetij v celico D2 in ustrezno ime imena se mora pojaviti v celici E2

Sporočila o napakah VLOOKUP

Če se v celici E2 pojavi sporočilo o napaki, kot je # N / A , najprej preverite črkovalne napake v celici D2.

Če črkovanje ni problem, lahko ta seznam sporočil o napakah VLOOKUP pomaga določiti, kje je težava.

Prekinjanje dela funkcije CHOOSE

Kot je že omenjeno, ima v tej formuli funkcija CHOOSE dve nalogi:

• ustvari tabelo, ki je le dva stolpca široka - stolpce D in F
• spremeni desno na levi vrstni red stolpcev v matriki tabele, tako da je stolpec F prvi, stolpec D pa drugi

Ustvarjanje mize dveh stolpcev

Sintaksa za funkcijo CHOOSE je:

= IZBERI (indeksna_številka, vrednost1, vrednost2, ... vrednost254)

Funkcija CHOOSE običajno vrne eno vrednost s seznama vrednosti (vrednost1 do vrednosti254) glede na vneseno številko indeksa.

Če je indeksna številka 1, funkcija vrne vrednost 1 s seznama; če je indeksna številka 2, funkcija vrne vrednost2 s seznama in tako naprej.

Z vnosom več indeksnih številk; vendar bo funkcija vrnila več vrednosti v želenem vrstnem redu. Pridobivanje CHOOSE za vrnitev več vrednosti se naredi z ustvarjanjem matrike .

Vnos matrike se izvede z obkroževanjem številk, vnesenih 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 dveh stolpcev. Z vključitvijo dodatne številke v matriko - na primer {1,2,3} - in dodatnega razpona v argumentu vrednosti lahko ustvarite tri stolpce.

Dodatni stolpci vam omogočajo, da z levo formulo vračate različne podatke preprosto tako, da spremenite argument VLOOKUP-ovega indeksa stolpca na številko stolpca, ki vsebuje želene informacije.

Spreminjanje vrstnega reda stolpcev s funkcijo CHOOSE

V funkciji CHOOSE, uporabljeni v tej formuli: IZBERITE ({1,2}, $ F: $ F, $ D: $ D) , je obseg stolpca F naveden pred stolpcem D.

Ker funkcija CHOOSE nastavi tabelo VLOOKUP-a - vir podatkov za to funkcijo - preklop vrstnega reda stolpcev v funkcijo CHOOSE preide na VLOOKUP.

Zdaj, 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, bo VLOOKUP lahko opravil običajne naloge iskanja pri iskanju podatkov, ki se nahajajo levo od iskalne vrednosti.

Zato lahko VLOOKUP uporablja ime podjetja, da bi našel del, ki ga dobavlja.