Če združite Excelovo funkcijo VLOOKUP s funkcijo COLUMN, lahko ustvarite formulo za iskanje, ki vrne več vrednosti iz ene vrstice zbirke podatkov ali tabele podatkov. Naučite se ustvariti formulo za iskanje, ki vrne več vrednosti iz enega podatkovnega zapisa.
Navodila v tem članku veljajo za Excel 2019, 2016, 2013, 2010; in Excel za Microsoft 365.
Spodnja vrstica
Iskalna formula zahteva, da je funkcija COLUMN ugnezdena znotraj VLOOKUP. Gnezdenje funkcije vključuje vnos druge funkcije kot enega od argumentov za prvo funkcijo.
Vnesite podatke o vadnici
V tej vadnici je funkcija COLUMN vnesena kot argument številke indeksa stolpca za VLOOKUP. Zadnji korak v vadnici vključuje kopiranje formule za iskanje v dodatne stolpce za pridobitev dodatnih vrednosti za izbrani del.
Prvi korak v tej vadnici je vnos podatkov v Excelov delovni list. Če želite slediti korakom v tej vadnici, vnesite podatke, prikazane na spodnji sliki, v naslednje celice:
- Vnesite zgornji obseg podatkov v celice D1 do G1.
- Vnesite drugi obseg v celice D4 do G10.
Iskalna merila in formula za iskanje, ustvarjena v tej vadnici, so vnesena v 2. vrstico delovnega lista.
Ta vadnica ne vključuje osnovnega Excelovega oblikovanja, prikazanega na sliki, vendar to ne vpliva na delovanje formule za iskanje.
Ustvarite imenovani obseg za podatkovno tabelo
Imenovani obseg je preprost način za sklicevanje na obseg podatkov v formuli. Namesto da vnesete sklice na celice za podatke, vnesite ime obsega.
Druga prednost uporabe imenovanega obsega je, da se sklice na celice za ta obseg nikoli ne spremenijo, tudi če je formula kopirana v druge celice na delovnem listu. Imena obsegov so alternativa uporabi absolutnih sklicev na celice za preprečevanje napak pri kopiranju formul.
Ime obsega ne vključuje naslovov ali imen polj za podatke (kot je prikazano v vrstici 4), ampak samo podatke.
-
Označite celice D5 do G10 na delovnem listu.
Image -
Postavite kazalec v polje za ime nad stolpcem A, vnesite Tabela, nato pritisnite Enter. Celice D5 do G10 imajo ime obsega Tabela.
Image - Ime obsega za argument polja tabele VLOOKUP je uporabljeno pozneje v tej vadnici.
Odprite pogovorno okno VLOOKUP
Čeprav je mogoče formulo za iskanje vnesti neposredno v celico na delovnem listu, mnogi ljudje težko ohranijo pravilno sintakso - še posebej za kompleksno formulo, kot je tista, uporabljena v tej vadnici.
Nadomestno možnost uporabite pogovorno okno Argumenti funkcije VLOOKUP. Skoraj vse Excelove funkcije imajo pogovorno okno, kjer je vsak argument funkcije vnesen v ločeno vrstico.
-
Izberite celica E2 delovnega lista. To je mesto, kjer bodo prikazani rezultati formule za dvodimenzionalno iskanje.
Image -
Na traku pojdite na zavihek Formulas in izberite Iskanje in referenca.
Image -
Izberite VLOOKUP, da odprete pogovorno okno Function Arguments.
Image - Pogovorno okno Argumenti funkcije je mesto, kjer se vnesejo parametri funkcije VLOOKUP.
Vnesite argument iskalne vrednosti
Običajno se iskalna vrednost ujema s podatkovnim poljem v prvem stolpcu podatkovne tabele. V tem primeru se iskalna vrednost nanaša na ime dela, v katerem želite najti informacije. Dovoljene vrste podatkov za iskalno vrednost so besedilni podatki, logične vrednosti, številke in sklice na celice.
Absolutne reference celic
Ko so formule kopirane v Excelu, se sklice na celice spremenijo tako, da odražajo novo lokacijo. Če se to zgodi, se D2, referenca celice za iskalno vrednost, spremeni in ustvari napake v celicah F2 in G2.
Absolutne reference celic se ne spremenijo, ko so formule kopirane.
Da preprečite napake, pretvorite referenco celice D2 v absolutno referenco celice. Če želite ustvariti absolutno referenco celice, pritisnite tipko F4. To doda znake za dolar okoli sklica celice, kot je $D$2.
-
V pogovornem oknu Argumenti funkcije postavite kazalec v besedilno polje lookup_value. Nato na delovnem listu izberite cell D2, da dodate sklic na to celico v lookup_value. V celico D2 bo vneseno ime dela.
Image -
Ne da bi premaknili točko vstavljanja, pritisnite tipko F4 za pretvorbo D2 v absolutno referenco celice $D$2.
Image - Pustite pogovorno okno funkcije VLOOKUP odprto za naslednji korak v vadnici.
Vnesite argument matrike tabele
Matrika tabel je tabela podatkov, ki jo iskalna formula išče, da bi našla želene informacije. Niz tabele mora vsebovati vsaj dva stolpca s podatki.
Prvi stolpec vsebuje argument iskalne vrednosti (ki je bil nastavljen v prejšnjem razdelku), drugi stolpec pa se išče z iskalno formulo za iskanje informacij, ki jih podate.
Argument matrike tabele je treba vnesti kot obseg, ki vsebuje sklice na celice za podatkovno tabelo, ali kot ime obsega.
Če želite dodati tabelo s podatki v funkcijo VLOOKUP, postavite kazalec v besedilno polje table_array v pogovornem oknu in vnesite Tableza vnos imena obsega za ta argument.
Ugnezdite funkcijo COLUMN
Običajno VLOOKUP vrne samo podatke iz enega stolpca podatkovne tabele. Ta stolpec je nastavljen z argumentom številke indeksa stolpca. V tem primeru pa so trije stolpci in številko indeksa stolpca je treba spremeniti brez urejanja formule za iskanje. Če želite to doseči, ugnezdite funkcijo COLUMN znotraj funkcije VLOOKUP kot argument Col_index_num.
Pri gnezdenju funkcij Excel ne odpre pogovornega okna druge funkcije za vnos njenih argumentov. Funkcijo COLUMN morate vnesti ročno. Funkcija COLUMN ima samo en argument, argument Reference, ki je sklic na celico.
Funkcija COLUMN vrne številko stolpca, navedenega kot argument Reference. Pretvori črko stolpca v številko.
Če želite najti ceno artikla, uporabite podatke v stolpcu 2 podatkovne tabele. Ta primer uporablja stolpec B kot referenco za vstavljanje 2 v argument Col_index_num.
-
V pogovornem oknu Argumenti funkcije postavite kazalec v besedilno polje Col_index_num in vnesite COLUMN(. (Ne pozabite vključiti odprtega okroglega oklepaja.)
Image -
Na delovnem listu izberite celica B1, da vnesete sklic na to celico kot argument Referenca.
Image - Vnesite zaprti oklepaj za dokončanje funkcije COLUMN.
Vnesite argument za iskanje obsega VLOOKUP
VLOOKUP-ov argument Range_lookup je logična vrednost (TRUE ali FALSE), ki označuje, ali naj VLOOKUP najde natančno ali približno ujemanje z Lookup_value.
- TRUE ali Izpuščeno: VLOOKUP vrne natančno ujemanje z Lookup_value. Če natančnega ujemanja ni mogoče najti, VLOOKUP vrne naslednjo največjo vrednost. Podatki v prvem stolpcu table_array morajo biti razvrščeni v naraščajočem vrstnem redu.
- FALSE: VLOOKUP uporablja natančno ujemanje z Lookup_value. Če sta v prvem stolpcu table_array dve ali več vrednosti, ki se ujemajo z iskalno vrednostjo, se uporabi prva najdena vrednost. Če natančnega ujemanja ni mogoče najti, se vrne napaka N/A.
V tej vadnici bodo poiskane specifične informacije o določenem elementu strojne opreme, zato je Range_lookup nastavljen na FALSE.
V pogovornem oknu Argumenti funkcije postavite kazalec v besedilno polje Range_lookup in vnesite False, da naročite VLOOKUP-u, naj vrne natančno ujemanje za podatke.
Izberite OK, da dokončate formulo za iskanje in zaprete pogovorno okno. Celica E2 bo vsebovala napako N/A, ker kriterij iskanja ni bil vnesen v celico D2. Ta napaka je začasna. Popravljeno bo, ko bodo kriteriji iskanja dodani v zadnjem koraku te vadnice.
Kopiraj formulo za iskanje in vnesi merila
Iskalna formula pridobi podatke iz več stolpcev podatkovne tabele hkrati. Če želite to narediti, mora biti iskalna formula v vseh poljih, iz katerih želite podatke.
Če želite pridobiti podatke iz stolpcev 2, 3 in 4 podatkovne tabele (cena, številka dela in ime dobavitelja), vnesite delno ime kot Lookup_value.
Ker so podatki na delovnem listu razporejeni po običajnem vzorcu, kopirajte formulo za iskanje v celico E2 v celice F2 in G2 Ko se formula kopira, Excel posodobi relativno referenco celice v funkciji COLUMN (celica B1), da odraža novo lokacijo formule. Excel ne spremeni absolutnega sklica na celico (kot je $D$2) in imenovanega obsega (tabela), ko se formula kopira.
Obstaja več kot en način za kopiranje podatkov v Excelu, vendar je najlažji način uporaba ročaja za polnjenje.
-
Izberite celica E2, kjer se nahaja formula za iskanje, da postane aktivna celica.
Image -
Povlecite ročico za polnjenje čez na celica G2. Celici F2 in G2 prikažeta napako N/A, ki je prisotna v celici E2.
Image -
Če želite uporabiti iskalne formule za pridobivanje informacij iz podatkovne tabele, na delovnem listu izberite celica D2, vnesite Widget in pritisnite Enter.
Image Naslednje informacije so prikazane v celicah E2 do G2.
- E2: 14,76 $ - cena gradnika
- F2: PN-98769 - številka dela za gradnik
- G2: Widgets Inc. - ime dobavitelja za pripomočke
-
Če želite preizkusiti matrično formulo VLOOKUP, vnesite imena drugih delov v celico D2 in opazujte rezultate v celicah E2 do G2.
Image - Vsaka celica, ki vsebuje formulo za iskanje, vsebuje drugačen podatek o strojni opremi, ki ste jo iskali.
Funkcija VLOOKUP z ugnezdenimi funkcijami, kot je COLUMN, zagotavlja zmogljivo metodo za iskanje podatkov znotraj tabele z uporabo drugih podatkov kot reference za iskanje.
