Poiščite več poljskih podatkov z Excelom VLOOKUP

Anonim

S kombiniranjem ExceloveVLOOKUP deluje zCOLUMN lahko ustvarimo formulo za iskanje, ki vam omogoča, da vrnete več vrednosti iz ene vrstice baze podatkov ali tabele podatkov.

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 COLUMN funkcija je ugnezdena znotraj VLOOKUP. Nesting funkcija vključuje vnos druge funkcije kot enega od argumentov za prvo funkcijo.

V tej vadnici je COLUMN funkcija se vnese kot stolpec številka argument za VLOOKUP. Zadnji korak v vadbi vključuje kopiranje formule za iskanje v dodatne stolpce, da pridobite dodatne vrednosti za izbrani del.

02 od 10

Vnesite podatke o vadnicah

Prvi korak v vadbi je vnos podatkov v delovni list Excel. Če želite slediti korakom v vajah, vnesite podatke, prikazane na zgornji sliki, v naslednje celice:

  • Vnesite najvišjo paleto podatkov v celice D1 do G1.
  • Vnesite drugi obseg v celice D4 do G10.

Vključeni bodo iskalni kriteriji in formula za iskanje, ki so bila ustvarjena med temi tutoriali vrstica 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.

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.

Ime obsega ne vključuje naslovov ali poljskih imen za podatke (vrstica 4), ampak le sami podatki.

  1. Označite celice D5 do G10 na delovnem listu, da jih izberete.
  2. Kliknite na Ime polja zgoraj stolpec A.
  3. Tip Tabela v Ime polja.
  4. Pritisnite tipko Vnesite tipko na tipkovnici.
  5. Celice D5 do G10 zdaj imajo ime območja Tabela. Uporabili bomo ime za VLOOKUP namizni niz argument kasneje v tutorialu.
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.

Alternativa, v tem primeru, je uporaba VLOOKUP pogovorno okno. Skoraj vse funkcije Excel imajo pogovorno okno, ki vam omogoča vnos vseh argumentov funkcije v ločeni vrstici.

Vadbeni koraki

  1. Kliknite na celica E2 delovnega lista - mesto, na katerem bodo prikazani rezultati dvodimenzionalne formule za iskanje.
  2. Kliknite na Zavihek Formule od trak.
  3. Kliknite na Iskanje in referenca možnost v trak da odprete funkcijo spustnega menija.
  4. Kliknite na VLOOKUP na seznamu, da odprete pogovorno okno funkcije.
05 od 10

Vnos argumenta za iskanje po vrednosti

Običajno je Poglej gor vrednost se ujema s poljem podatkov v prvem stolpcu tabele s podatki. V našem primeru je Poglej gor vrednost se nanaša na ime dela strojne opreme, o katerem želimo najti informacije. Dovoljene vrste podatkov za Poglej gor vrednost so besedilni podatki, logične vrednosti, številke in reference celic.

Absolutne celične reference

Običajno, ko se v Excelu kopirajo formule, se celične reference spremenijo, da odražajo njihovo novo lokacijo. Če se to zgodi, D2, referenca celice za Poglej gor vrednost, se bo spremenila, ko bo kopirana formulacija, ki ustvarja napake v celice F2 in G2.

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

Če želite preprečiti napake, bomo pretvorili referenco celice D2 v absolutno referenco celice. Absolutne referenčne celice se ustvarijo s pritiskom na F4 tipko na tipkovnici. Na ta način se dodajajo znaki dolarja okoli sklicevanja na celico, npr $ D $ 2.

  1. Kliknite na lookup_value vrstico v pogovornem oknu.
  2. Kliknite na celica D2 dodati to sklicevanje na celico lookup_value line - to je celica, kjer bomo vnesli ime dela, o katerem iščemo informacije
  3. Če ne premaknete točke vstavljanja, pritisnite F4 tipko na tipkovnici za pretvorbo D2 v absolutno referenčno celico $ D $ 2.
  4. Pusti VLOOKUP pogovorno okno za funkcijo, odprto 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), medtem ko bo drugi stolpec iskal 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.

  1. Kliknite na table_array vrstico v pogovornem oknu.
  2. Tip Tabela da vnesete ime razdalje za ta argument.
  3. Pusti VLOOKUP pogovorno okno za funkcijo, odprto za naslednji korak v vadnici.
07 od 10

Zamenjava funkcije COLUMN

Običajno VLOOKUP samo vrača podatke iz enega stolpca tabele podatkov in ta stolpec nastavi številka stolpca stolpca prepir.

V tem primeru pa imamo tri stolpce, od katerih želimo vrniti podatke, zato potrebujemo način, kako enostavno spremeniti številka stolpca stolpca brez urejanja naše formule za iskanje - tukaj je COLUMN funkcija prihaja.

Z vnosom kot številka stolpca stolpca argument, se bo spremenil, ko bo kopirana formula za iskanje celica D2 do celice E2 in F2 pozneje v vadnici.

Nesting funkcije

The COLUMN zato deluje kot VLOOKUPje številka stolpca stolpca prepir; to se doseže z gnezditvijo COLUMN funkcijo znotraj VLOOKUP v Col_index_num vrstica pogovornega okna.

Ročno vnašanje funkcije COLUMN

Pri nenadzirnih funkcijah Excel ne omogoča odpiranja pogovornega okna druge funkcije, da bi vnesli svoje argumente. The COLUMN Zato je treba ročno vnesti v Col_index_num line. The COLUMN funkcija ima samo en argument - Referenca argument, ki je referenca celice.

Izbira referenčnega argumenta funkcije COLUMN

The COLUMN naloga funkcije je vrniti številko stolpca, ki je podan kot Referenca prepir. Z drugimi besedami, pretvarja črko stolpca v številko z stolpec A ki je prvi stolpec, stolpec B drugo in tako naprej.

Od prvega polja podatkov, kar želimo vrniti, je cena izdelka, ki je v drugem stolpcu tabele podatkov. Izberemo lahko referenco celice za katero koli celico v stolpec B kot Referenca Argument, da bi dobili številko 2 za Col_index_num prepir.

  1. V Ljubljani VLOOKUP dialog pogovorno okno, kliknite na Col_index_num line.
  2. Vnesite ime funkcije stolpec sledi ji odprt okrogli nosilec.
  3. Kliknite na celica B1 na delovnem listu, da vnesete referenco celice kot Referenca prepir.
  4. Vnesite a zaključni oklepni nosilec za dokončanje COLUMN funkcijo
  5. Pusti VLOOKUP pogovorno okno za funkcijo, odprto za naslednji korak v vadnici.
08 od 10

Vstop v argument VLOOKUP Range Range Lookup

VLOOKUPje Range_lookup argument je logična vrednost (PRAV ali FALSE), ki označuje, ali želite VLOOKUP najti natančno ali približno ujemanje z Lookup_value.

  • Če PRAV ali če je ta argument izpuščen: VLOOKUP vrne natančno ujemanje na Lookup_valueAli, če natančno ujemanje ni mogoče najti, VLOOKUP vrne naslednjo največjo vrednost. Za formulo, ki to naredi, so podatki v prvem stolpcu Table_array je treba razvrščati po naraščajočem vrstnem redu.
  • Če FALSE: VLOOKUP bo uporabil le natančno ujemanje z Lookup_value. Če sta v prvem stolpcu v tabeli dve ali več vrednosti Table_array ki se ujemajo z vrednostjo iskanja, se uporabi prva ugotovljena vrednost. Če natančnega ujemanja ni mogoče najti, a # N / A napaka se vrne.

V tej vadnici, ker iščemo določene informacije o določenem elementu strojne opreme, bomo nastavili Range_lookup enako Lažno.

  1. Kliknite na Range_lookup vrstico v pogovornem oknu.
  2. Vnesite besedo Lažno v tej vrstici, ki označuje, da želimo VLOOKUP za natančno ujemanje podatkov, ki jih iščemo.
  3. Kliknite v redu da dokončate formulo za iskanje in zaprete pogovorno okno.
  4. Ker še nismo vnesli kriterijev iskanja celica D2 an # N / A napaka bo prisotna v celica E2.
  5. Ta napaka bo popravljena, ko bomo v zadnjem koraku vadnice dodali kriterije za iskanje.
09 od 10

Kopiranje obrazca za iskanje

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 tutorialu želimo pridobiti podatke iz stolpci 2, 3, in 4 podatkovne tabele - to je cena, številka dela in ime dobavitelja, ko vnesemo delno ime kot Lookup_value.

Ker so podatki v delovnem listu določeni v rednem vzorcu, lahko obliko iskanja kopiramo v celica E2 do celice F2 in G2.

Ker je formula kopirana, bo Excel posodobil relativno referenco celic v COLUMN funkcija (celica B1), da odraža novo lokacijo formule.

Excel tudi ne spremeni absolutne reference celic $ D $ 2 in imenovanega območja Tabela kot je formula prepisana.

Obstaja več kot en način kopiranja podatkov v Excelu, vendar je verjetno najlažji način z uporabo Polnjenje ročaja.

  1. Kliknite na celica E2, kjer se nahaja formula za iskanje, da postane aktivna celica.
  2. Postavite kazalec miške nad črni kvadrat v spodnjem desnem kotu - kazalec se spremeni v znak plus, da označite polnilo.
  3. Kliknite levi gumb miške in povlecite ročaj za polnjenje čez to celica G2.
  4. Sprostitev gumb miške in celica F3 mora vsebovati dvodimenzionalno formulo za iskanje.
  5. Če je storjeno pravilno, celice F2 in G2 zdaj mora vsebovati tudi # N / A napaka, ki je prisotna v celica 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 predmeta vnesite v Lookup_value celica (D2) in pritisnite Vnesite tipko 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.

  1. Kliknite na celica D2 v delovnem listu.
  2. Tip Widget v celica D2 in pritisnite Vnesite tipko na tipkovnici.
  3. Naslednje informacije morajo biti prikazane v celice E2 do G2.
    1. E2: 14,76 $ - cena pripomočka
    2. F2: PN-98769 - številka dela za pripomoček
    3. G2: Widgets Inc - ime dobavitelja za pripomočke
  4. Preizkusite VLOOKUP matrično formulo, tako da vnesete ime drugih delov v celica D2 in opazovanje rezultatov v celice E2 do G2

Izbira Urednika