Poiščite več poljskih podatkov z Excelom VLOOKUP

S kombiniranjem funkcije Excelove VLOOKUP s funkcijo COLUMN lahko ustvarimo formulo za iskanje, ki vam omogoča, da vrnete več vrednosti iz ene vrstice baze podatkov ali tabele podatkov.

V primeru, prikazanem na zgornji sliki, formula za iskanje omogoča enostavno vrnitev vseh vrednosti - kot so cena, številka dela in dobavitelj - povezani z različnimi deli strojne opreme.

01 od 10

Vrni več vrednosti z Excelom VLOOKUP

Po spodaj navedenih korakih ustvari formulo za iskanje, prikazano na zgornji sliki, ki bo vrnila več vrednosti iz enega podatkovnega zapisa.

Formula za iskanje zahteva, da je funkcija COLUMN ugnezdena znotraj VLOOKUP-a.

Nesting funkcija vključuje vnos druge funkcije kot enega od argumentov za prvo funkcijo.

V tem vadnici bo funkcija stolpca vnesena kot številka stolpca indeksnega števila za VLOOKUP.

Zadnji korak v vadbi vključuje kopiranje formule za iskanje v dodatne stolpce, da pridobite dodatne vrednosti za izbrani del.

Vadnica Vsebina

• Vnos podatkov o vadnicah
• Ustvarjanje imenskega obsega za tabelo podatkov
• Zagon funkcije VLOOKUP
• Vnos argumenta poizvedbe vrednosti z uporabo referenc Absolute Cell
• Vnos argumenta Array tabele
• Vstop v funkcijo Vneseni stolpec
• Dokončanje funkcije VLOOKUP
• Kopiranje Formule za iskanje z ročico za polnjenje
• Pridobivanje podatkov s formulo za iskanje

02 od 10

Vnesite podatke o vadnicah

Prvi korak v vadbi je vnos podatkov v delovni list Excel.

Če želite slediti korakom v vadnici, vnesite podatke, prikazane na zgornji sliki, v naslednje celice .

• V vrsto podatkov vnesite v celice D1 do G1
• V drugem obsegu vnesite celice od D4 do G10

Merila za iskanje in formula za iskanje, ustvarjena v tem vadnici, bodo vpisana v vrstico 2 delovnega lista.

Vadnica ne vključuje oblikovanja, ki je vidna na sliki, vendar to ne bo vplivalo na delovanje formule za iskanje.

Informacije o možnostih oblikovanja, podobne tistim, ki so bile prikazane zgoraj, so na voljo v tem Tutorialu za oblikovanje osnovnega Excel-a .

Vadbeni koraki

1. Vnesite podatke, kot so prikazani na zgornji sliki, v celice D1 do G10

03 od 10

Ustvarjanje imenskega obsega za tabelo podatkov

Imenovani obseg je enostaven način za sklicevanje na vrsto podatkov v formuli. Namesto da vnesete celične reference za podatke, lahko vnesete samo ime obsega.

Druga prednost za uporabo imenovanega območja je, da se številke celic za to območje nikoli ne spreminjajo, tudi če je formula prepisana v druge celice v delovnem listu.

Imena razpona so zato alternativa uporabi absolutnih referenc celic, da preprečimo napake pri kopiranju formul.

Opomba: Ime obsega ne vključuje imen ali polj za podatke (vrstica 4), temveč samo podatke samega.

Vadbeni koraki

1. Označite celice D5 do G10 na delovnem listu, da jih izberete
2. Kliknite polje z imenom, ki se nahaja nad stolpcem A
3. Vnesite "tabelo" (brez narekovajev) v polje Ime
4. Pritisnite tipko ENTER na tipkovnici
5. Celice D5 do G10 imajo zdaj naziv "tabele". V nadaljevanju bomo uporabili ime za argument tabele VLOOKUP tabele

04 od 10

Odpiranje pogovornega okna VLOOKUP

Čeprav je možno, da našo formulo za iskanje vnesemo neposredno v celico v delovnem listu, mnogim ljudem težko ohranjajo sintakso naravnost - še posebej za kompleksno formulo, kot je tista, ki jo uporabljamo v tem vadnici.

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 dvodimenzionalne formule za iskanje
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 odprete pogovorno okno funkcije

05 od 10

Vnos argumenta poizvedbe vrednosti z uporabo referenc Absolute Cell

Običajno se iskalna vrednost ujema s poljem podatkov v prvem stolpcu tabele podatkov.

V našem primeru se vrednost za iskanje nanaša na ime dela strojne opreme, o katerem želimo najti informacije.

Dovoljene vrste podatkov za iskalno vrednost so:

• besedilni podatki
• logična vrednost (samo TRUE ali FALSE)
• številka
• sklic na celico na vrednost v delovnem listu

V tem primeru bomo vnesli sklic na celico, kamor naj se nahaja ime - celica D2.

Absolutne celične reference

V naslednjem koraku v vadnici bomo kopirali formulo za iskanje v celico E2 v celice F2 in G2.

Običajno, ko se v Excelu kopirajo formule, se celične reference spremenijo, da odražajo njihovo novo lokacijo.

Če se to zgodi, se D2 - sklic celice za vrednost iskanja - spremeni, ko se kopira formula, ki ustvari napake v celicah F2 in G2.

Da bi preprečili napake, bomo referenčno celico D2 pretvorili v absolutno referenčno celico .

Absolutne reference celic se ne spreminjajo, ko se kopirajo formule.

Absolutne reference celic se ustvarjajo s pritiskom na tipko F4 na tipkovnici. Na ta način se doda znaka dolarja okoli referenčne številke celice, kot je $ D $ 2

Vadbeni koraki

1. V pogovornem oknu kliknite vrstico lookup_value
2. Kliknite celico D2, če želite dodati referenco celice na vrstico lookup_value . To je celica, kjer bomo vnesli ime dela, o katerem iščemo informacije
3. Brez premikanja točke vstavljanja pritisnite tipko F4 na tipkovnici za pretvorbo D2 v absolutno referenčno celico $ D $ 2
4. Odprite pogovorno okno funkcije VLOOKUP, ki je odprt za naslednji korak v vadnici

06 od 10

Vnos argumenta Array tabele

Tabela je tabela podatkov, ki jo išče formula za iskanje, da bi našli želene informacije.

Tabela mize mora vsebovati vsaj dva stolpca podatkov .

• prvi stolpec vsebuje argument lookup value (prejšnji korak v vadnici)
• drugi in vsi dodatni stolpci bodo iskali po formuli za iskanje, da bi našli informacije, ki jih določimo.

Argument array table mora biti vnesen kot obseg, ki vsebuje reference celic za tabelo podatkov ali kot ime razdalje .

V tem primeru bomo uporabili ime razdalje, ki smo ga ustvarili v 3. koraku vadnice.

Vadbeni koraki

1. V pogovornem oknu kliknite vrstico table_array
2. Vnesite "tabelo" (brez narekovajev), da vnesete ime obsega za ta argument
3. Odprite pogovorno okno funkcije VLOOKUP, ki je odprt za naslednji korak v vadnici

07 od 10

Zamenjava funkcije COLUMN

Običajno VLOOKUP vrne samo podatke iz enega stolpca podatkovne tabele in ta stolpec je nastavljen z argumentom števila indeksov stolpcev .

V tem primeru pa imamo tri stolpce, od katerih želimo vrniti podatke, zato potrebujemo način, kako enostavno spremeniti številko indeksa stolpca brez urejanja naše formule za iskanje.

Tu se prikaže funkcija COLUMN. Če jo vnesete kot argument števila indeksov stolpca , se bo spremenil, ko se formular lookupa kopira iz celice D2 v celice E2 in F2 pozneje v vadnici.

Nesting funkcije

Funkcija COLUMN torej deluje kot argument VLOOKUP-ovega stolpca .

To se doseže z vdiranjem funkcije COLUMN znotraj VLOOKUP v vrstici Col_index_num v pogovornem oknu.

Ročno vnašanje funkcije COLUMN

Pri nenadzirnih funkcijah Excel ne omogoča odpiranja pogovornega okna druge funkcije, da bi vnesli svoje argumente.

Funkcijo COLUMN je zato treba vnesti ročno v vrstico Col_index_num .

Funkcija COLUMN ima samo en argument - referenčni argument, ki je referenca celice.

Izbira referenčnega argumenta funkcije COLUMN

Naloga funkcije COLUMN je, da vrne številko stolpca, ki je podan kot referenčni argument.

Z drugimi besedami, spremeni črko stolpca v številko, pri čemer je stolpec A prvi stolpec, stolpec B drugi in tako naprej.

Ker je prvo polje podatkov, ki ga želimo vrniti, je cena elementa - ki je v stolpcu podatkov v stolpcu 2 - lahko izberemo referenco celice za katero koli celico v stolpcu B kot referenčni argument, da dobimo številko 2 za argument Col_index_num .

Vadbeni koraki

1. V pogovornem oknu funkcije VLOOKUP kliknite vrstico Col_index_num
2. Vnesite stolpec z imenom funkcije, ki mu sledi odprt oklepni nosilec " ( "
3. Kliknite na celico B1 na delovnem listu, da vnesete referenco celice kot referenčni argument
4. Vnesite zapiralni okvir " ) " za dokončanje funkcije COLUMN
5. Odprite pogovorno okno funkcije VLOOKUP, ki je odprt za naslednji korak v vadnici

08 od 10

Vstop v argument VLOOKUP Range Range Lookup

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 s Lookup_value.

• Č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čene informacije o določenem elementu strojne opreme, bomo Range_lookup nastavili na vrednost False .

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 formulo za iskanje in zaprete pogovorno okno
4. Ker v celico D2 še nismo vnesli kriterijev za iskanje, bo v celici E2 prisotna napaka # N / A
5. Ta napaka bo popravljena, ko bomo v zadnjem koraku vadnice dodali kriterije za iskanje

09 od 10

Kopiranje Formule za iskanje z ročico za polnjenje

Formula za iskanje je namenjena hkratnemu pridobivanju podatkov iz več stolpcev tabele podatkov.

Če želite to narediti, mora formula za iskanje v vseh poljih, od katerih želimo informacije.

V tem vodiču želimo pridobiti podatke iz stolpcev 2, 3 in 4 tabele podatkov - to je cena, številka dela in ime dobavitelja, ko vnesemo ime dela kot Lookup_value.

Ker so podatki v delovnem listu v rednem vzorcu, lahko kopiramo formulo za iskanje v celici E2 v celice F2 in G2.

Ko je formula prepisana, bo Excel posodobil relativno referenco celic v funkciji COLUMN (B1), tako da bo odražal novo lokacijo formule.

Excel tudi ne spremeni absolutne referenčne vrednosti celice $ D $ 2 in imensko razpredelnico razpredelnic, ker je formula prepisana.

Obstaja več kot en način kopiranja podatkov v Excelu, vendar je verjetno najlažji način z uporabo gumba za polnjenje .

Vadbeni koraki

1. Kliknite na celico E2 - kjer se nahaja formula za iskanje - da bi bila aktivna celica
2. Postavite miškin kazalec čez črni kvadrat v spodnjem desnem kotu. Kazalec se bo spremenil v znak plus " + " - to je polnilo
3. Kliknite levi gumb miške in povlecite ročico za polnjenje v celico G2
4. Spustite gumb miške in celica F3 mora vsebovati dvodimenzionalno formulo za iskanje
5. Če je pravilno storjeno, morajo celice F2 in G2 vsebovati tudi napako # N / A, ki je prisotna v celici E2

10 od 10

Vnos kriterijev iskanja

Ko je formula za iskanje kopirana v zahtevane celice, se lahko uporabi za pridobivanje podatkov iz tabele s podatki.

V ta namen vnesite ime elementa, ki ga želite prenesti v celico Lookup_value (D2), in pritisnite tipko ENTER na tipkovnici.

Ko končate, mora vsaka celica, ki vsebuje formulo za iskanje, vsebovati drug del podatkov o elementu strojne opreme, ki ga iščete.

Vadbeni koraki

1. Kliknite na celico D2 v delovnem listu
2. Vtič vnesite v celico D2 in pritisnite tipko ENTER na tipkovnici
3. V celicah E2 do G2 morajo biti prikazane naslednje informacije:
   • E2 - 14,76 $ - cena pripomočka
   • F2 - PN-98769 - številka dela za pripomoček
   • G2 - Widgets Inc - ime dobavitelja za pripomočke
4. Preizkusite formulo VLOOKUP matrike s tipkanjem imena drugih delov v celico D2 in opazovanjem rezultatov v celicah E2 do G2

Če se prikaže sporočilo o napaki, kot je #REF! se prikaže v celicah E2, F2 ali G2, lahko ta seznam sporočil o napakah VLOOKUP pomaga ugotoviti, kje je težava.