Kako uporabljati VLOOKUP v Excelu

Ste že kdaj imeli veliko preglednico s podatki v Excelu in potrebujete preprost način za filtriranje in črpanje določenih informacij iz njega? Če se naučite, kako uporabljati VLOOKUP v Excelu, lahko to iskanje izvedete samo z eno samo zmogljivo funkcijo Excela.

Funkcija VLOOKUP v Excelu marsikoga prestraši, ker ima veliko parametrov in obstaja več načinov za njegovo uporabo. V tem članku boste izvedeli vse načine uporabe VLOOKUP v Excelu in zakaj je funkcija tako močna.

VLOOKUP Parametri v Excelu

Ko začnete tipkati = VLOGA ( v katero koli celico v Excelu, se prikaže pojavno okno, ki prikazuje vse razpoložljive parametre funkcije.

Preučimo vsakega od teh parametrov in kaj pomenijo.

  • lookup_value: Vrednost, ki jo iščete v preglednici
  • table_array: Obseg celic na listu, ki ga želite iskati
  • col_index_num: Stolpec, iz katerega želite potegniti rezultat
  • [range_lookup]: Način ujemanja (TRUE = približno, FALSE = natančno)

Ti štirje parametri omogočajo veliko različnih, uporabnih iskanj podatkov v zelo velikih zbirkah podatkov.

Preprost primer Excela VLOOKUP

VLOOKUP ni eden izmed osnovne funkcije Excela ste se morda naučili, zato si poglejmo preprost primer za začetek.

Za naslednji primer bomo uporabili velika preglednica SAT rezultatov za šole v ZDA. Ta preglednica vsebuje več kot 450 šol, skupaj s posameznimi rezultati SAT za branje, matematiko in pisanje. Če želite nadaljevati, vas lahko prenesete. Obstaja zunanja povezava, ki potegne podatke, tako da boste ob odpiranju datoteke dobili opozorilo, vendar je varno.

Zelo zamudno bi bilo iskati tako velik nabor podatkov, da bi našli šolo, ki vas zanima.

Namesto tega lahko v praznih celicah na strani tabele ustvarite preprost obrazec. Če želite izvesti to iskanje, naredite samo eno polje za šolo in tri dodatna polja za rezultate branja, matematike in pisanja.

Nato morate za uporabo teh treh polj uporabiti funkcijo VLOOKUP v Excelu. V reading polje, ustvarite funkcijo VLOOKUP na naslednji način:

  1. tip = VLOGA (
  2. Izberite polje Šola, ki je v tem primeru I2. Vnesite vejico.
  3. Izberite celoten obseg celic, ki vsebujejo podatke, ki jih želite iskati. Vnesite vejico.

Ko izberete obseg, lahko začnete iz stolpca, ki ga uporabljate za iskanje (v tem primeru stolpec z imenom šole) in nato izberete vse druge stolpce in vrstice, ki vsebujejo podatke.

Opombe: Funkcija VLOOKUP v Excelu lahko išče le po celicah na desni strani iskalnega stolpca. V tem primeru mora biti stolpec z imenom šole levo od podatkov, ki jih iščete.

  1. Nato za pridobitev ocene branja boste morali izbrati 3. stolpec iz levega izbranega stolpca. Torej, vnesite a 3 in nato vnesite drugo vejico.
  2. Nazadnje, vnesite FALSE za natančno ujemanje in funkcijo zaprite s tipko a ).

Vaša končna funkcija VLOOKUP mora izgledati nekako takole:

= VLOOKUP (I2, B2: G461,3, FALSE)

Ko prvič pritisnete Enter in dokončate funkcijo, boste opazili, da polje Branje vsebuje # N / A.

To je zato, ker je polje School prazno in ni ničesar, kar bi našla funkcija VLOOKUP. Če vpišete ime katere koli srednje šole, ki jo želite pogledati, boste za točko branja videli pravilne rezultate iz te vrstice.

Kako se spoprijeti s tem, da je VLOOKUP občutljiv na velike in male črke

Morda boste opazili, da če v šolo ne vpišete imena šole v istem primeru, kot je navedeno v naboru podatkov, ne boste videli nobenih rezultatov.

To je zato, ker je funkcija VLOOKUP občutljiva na velike in male črke. To je lahko nadležno, še posebej za zelo velik nabor podatkov, kjer stolpec, ki ga iščete, ni v skladu z velikimi začetki.

Če želite to rešiti, lahko prisilite tisto, kar iščete, da preklopite na male črke, preden poiščete rezultate. Če želite to narediti, ustvarite nov stolpec poleg stolpca, ki ga iščete. Vnesite funkcijo:

= TRIM (NIZKA (B2))

To bo ime male šole in odstranilo vse zunanje črke (presledke), ki so lahko na levi ali desni strani imena.

Držite tipko Shift in miškin kazalec postavite čez spodnji desni kot prve celice, dokler se ne spremeni v dve vodoravni črti. Dvokliknite miško, da samodejno napolnite celoten stolpec.

Končno, ker bo VLOOKUP v teh celicah uporabil formulo in ne besedilo, jih morate vse pretvoriti samo v vrednosti. Če želite to narediti, kopirajte celoten stolpec, z desno miškino tipko kliknite prvo celico in prilepite samo vrednosti.

Zdaj, ko so v tem novem stolpcu očiščeni vsi vaši podatki, rahlo spremenite funkcijo VLOOKUP v Excelu, tako da uporabite nov stolpec namesto prejšnjega tako, da zaženete območje iskanja pri C2 namesto B2.

= VLOOKUP (I2, C2: G461,3, FALSE)

Zdaj boste opazili, da če iščete vedno vnesete mala črka, boste vedno dobili dober rezultat iskanja.

To je priročen Excel nasvet premagati dejstvo, da je VLOOKUP občutljiv na velike in male črke.

VLOOKUP Približna tekma

Medtem ko je primer LOOKUP-a z natančnim ujemanjem, opisan v prvem razdelku tega članka, precej preprost, je približno ujemanje nekoliko bolj zapleteno.

Približno ujemanje je najbolje uporabiti za iskanje po številih. Če želite to pravilno narediti, je treba območje iskanja ustrezno razvrstiti. Najboljši primer tega je funkcija VLOOKUP za iskanje stopnje črke, ki ustreza številski stopnji.

Če ima učitelj dolg seznam ocen domačih nalog učencev iz celega leta s končnim povprečenim stolpcem, bi bilo lepo, če se ocene o črkah, ki ustrezajo tej končni oceni, prikažejo samodejno.

To je mogoče s funkcijo VLOOKUP. Vse, kar je potrebno, je tabela za iskanje na desni strani, ki vsebuje ustrezno črko za vsako številčno število točk.

Zdaj s pomočjo funkcije VLOOKUP in približnega ujemanja najdete pravilno oceno črk, ki ustreza pravilnemu številčnemu razponu.

V tej funkciji VLOOKUP:

  • lookup_value: F2, končna povprečna ocena
  • table_array: I2: J8, območje iskanja črk črk
  • stolpec_indeksa: 2, drugi stolpec v iskalni tabeli
  • [range_lookup]: TRUE, približno ujemanje

Ko končate funkcijo VLOOKUP v G2 in pritisnete Enter, lahko napolnite preostale celice z istim pristopom, opisanim v zadnjem razdelku. Videli boste, da so vse črke pravilno izpolnjene.

Upoštevajte, da funkcija VLOOKUP v Excelu išče od spodnjega konca obsega razreda z dodeljeno oceno črke do vrha obsega naslednjega črka.

Torej, "C" mora biti črka, dodeljena spodnjem območju (75), B pa dno (najmanj) v svojem območju črk. VLOOKUP bo »našel« rezultat za 60 (D) kot najbližjo približno vrednost za karkoli med 60 do 75.

VLOOKUP v Excelu je zelo močna funkcija, ki je na voljo že dolgo. Uporabna je tudi za iskanje ujemajočih se vrednosti kjerkoli v Excelovem delovnem zvezku.

Upoštevajte pa, da imajo Microsoftovi uporabniki, ki imajo mesečno naročnino na Office 365, dostop do novejše funkcije XLOOKUP. Ta funkcija ima več parametrov in dodatno prilagodljivost. Uporabniki s polletno naročnino bodo morali počakati, da se posodobitev začne izvajati julija 2020.