Excel Dva Way Iskanje z uporabo VLOOKUP 2. del

01 od 06

Začetek funkcije gesla MATCH

Vrni se na 1. del

Vstop v funkcijo MATCH kot argument številka indeksa stolpca

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

Vendar v tem primeru imamo tri stolpce, za katere želimo najti podatke, zato potrebujemo način, kako enostavno spremeniti številko indeksa stolpca brez urejanja naše formule za iskanje.

Tu je funkcija MATCH vključena. Omogočili bomo, da se številka polja v podmeniju ujema z imenom polja - januarja, februarja ali marca -, ki jo vnesemo v celico E2 na delovnem listu.

Nesting funkcije

Funkcija MATCH torej deluje kot argument VLOOKUP-ovega števila indeksov stolpcev .

To dosežemo tako, da se v oknu VLOOKUP v liniji Col_index_num v pogovornem oknu vnese funkcija MATCH.

Ročno vnašanje funkcije MATCH

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

Zato je treba funkcijo MATCH vnesti ročno v vrstico Col_index_num .

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

Vadbeni koraki

Vnos argumenta Lookup_value argumenta MATCH

Prvi korak pri vnosu ugnežene funkcije MATCH je vnos argumenta Lookup_value .

Lookup_value bo referenca lokacije ali celice za iskalni izraz, ki ga želimo ujemati v podatkovni bazi.

1. V pogovornem oknu funkcije VLOOKUP kliknite vrstico Col_index_num .
2. Vnesite ujemanje imena funkcij, ki mu sledi odprt okrogel nosilec " ( "
3. Kliknite celico E2, da v pogovorno okno vnesete referenco celice.
4. Vnesite vejico "," po sklicu celice E3 za dokončanje vnosa argumenta Lookup_value funkcije MATCH.
5. Odprite pogovorno okno funkcije VLOOKUP, ki je odprt za naslednji korak v vadnici.

V zadnjem koraku vadnice se Lookup_values ​​vnesejo v celice D2 in E2 na delovnem listu .

02 od 06

Dodajanje Lookup_array za funkcijo MATCH

Dodajanje Lookup_array za funkcijo MATCH

Ta korak pokriva dodajanje argumenta Lookup_array za ugnezdeno funkcijo MATCH.

Lookup_array je obseg celic, ki jih bo funkcija MATCH poiskala, da bi našli argument Lookup_value , dodan v prejšnjem koraku vadnice.

V tem primeru želimo, da funkcija MATCH poišče celice D5 do G5 za ujemanje z imenom meseca, ki bo vnesen v celico E2.

Vadbeni koraki

Te korake je treba vnesti po vejici, ki je bila v prejšnjem koraku v vrstici Col_index_num v pogovornem oknu funkcije VLOOKUP.

1. Če je potrebno, kliknite vrstico Col_index_num za vejico, da vstavite točko vstavljanja na konec trenutnega vnosa.
2. Označite celice D5 do G5 na delovnem listu, da vnesete te referenčne številke kot obseg, za katero želite iskati funkcijo.
3. Pritisnite tipko F4 na tipkovnici, da spremenite to območje v absolutne reference celic . S tem bo omogočeno kopiranje končane formule za iskanje na druge lokacije v delovnem listu v zadnjem koraku vadnice
4. Vnesite vejico "," po sklicu celice E3 za dokončanje vnosa argumenta Lookup_array funkcije MATCH.

03 od 06

Dodajanje vrste ujemanja in zaključek funkcije MATCH

Dodajanje vrste ujemanja in zaključek funkcije MATCH

Tretji in zadnji argument funkcije MATCH je argument Match_type.

Ta argument pove Excelu, kako naj ustreza Lookup_value z vrednostmi v Lookup_array. Izbira je: -1, 0 ali 1.

Ta argument je neobvezen. Če je izpuščena, funkcija uporabi privzeto vrednost 1.

• če je Match_type = 1 ali je izpuščen: MATCH najde največjo vrednost, ki je manjša ali enaka Lookup_value. Če je ta vrednost izbrana, je treba podatke Lookup_array sortirati po naraščajočem vrstnem redu.
• če Match_type = 0: MATCH najde prvo vrednost, ki je točno enaka Lookup_value. Podatke Lookup_array lahko razvrstite v poljubnem vrstnem redu.
• če Match_type = 1: MATCH najde najmanjšo vrednost, ki je večja ali enaka vrednosti Lookup_value. Če je ta vrednost izbrana, je treba podatke Lookup_array sortirati po padajočem vrstnem redu.

Vadbeni koraki

Te korake je treba vnesti po vejici, vpisani v prejšnjem koraku v vrstici Row_num v pogovornem oknu funkcije VLOOKUP.

1. Po drugi vejici na liniji Col_index_num vnesite nič " 0 ", ker želimo, da ugnezdena funkcija vrne točno ujemanje na mesec, vnesite v celico E2.
2. Vnesite zaključni oklepni nosilec » ) «, da dokončate funkcijo MATCH.
3. Odprite pogovorno okno funkcije VLOOKUP, ki je odprt za naslednji korak v vadnici.

04 od 06

Vstop v argument VLOOKUP Range Range Lookup

Argument za 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 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, saj iščemo prodajne številke za določen mesec, bomo nastavili Range_lookup enak 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 dvodimenzionalno formulo za iskanje in zaprete pogovorno okno
4. Ker v celice D2 in E2 še nismo vnesli kriterijev za iskanje, bo v celici F2 prisotna napaka # N / A
5. Ta napaka bo popravljena v naslednjem koraku v vadnici, ko bomo v naslednji korak vadbe dodali kriterije za iskanje.

05 od 06

Testiranje dvosmerne formule za iskanje

Testiranje dvosmerne formule za iskanje

Če želite uporabiti dvosmerno formulo za iskanje, poiščite mesečne podatke o prodaji za različne piškotke, navedene v matriki tabele, vnesite ime piškotka v celico D2, mesec v celico E2 in pritisnite tipko ENTER na tipkovnici.

Podatki o prodaji bodo prikazani v celici F2.

Vadbeni koraki

1. Kliknite na celico D2 v vašem delovnem listu
2. V ovitek D2 vnesite ovseno moko in pritisnite tipko ENTER na tipkovnici
3. Kliknite na celico E2
4. Vnesite februar v celico E2 in pritisnite tipko ENTER na tipkovnici
5. V celici F2 je treba prikazati vrednost $ 1,345 - znesek prodaje za ovsene piškote v mesecu februarju
6. V tem trenutku se vaš delovni list ujema s primerom na 1. strani tega vaja
7. Preizkusite formulo za iskanje, tako da vnesete poljubno kombinacijo piškotkov in mesecev, ki so prisotni v tabeli, in številke prodaje morajo biti prikazane v celici F2
8. Zadnji korak v vadnici zajema kopiranje formule za iskanje s pomočjo gumba za polnjenje .

Če se prikaže sporočilo o napaki, kot je #REF! se prikaže v celici F2, ta seznam sporočil o napakah VLOOKUP vam lahko pomaga določiti, kje je težava.

06 od 06

Kopiranje dvodimenzionalne formule za iskanje z ročico za polnjenje

Kopiranje dvodimenzionalne formule za iskanje z ročico za polnjenje

Če želite poenostaviti primerjavo podatkov za različne mesece ali različne piškotke, se formula za iskanje lahko kopira v druge celice, tako da je mogoče istočasno prikazati več količin.

Ker so podatki v delovnem listu v rednem vzorcu, lahko kopiramo formulo za iskanje v celici F2 v celico F3.

Ker je formula kopirana, bo Excel posodobil relativne referenčne celice, da bi odražal novo lokacijo formule. V tem primeru D2 postane D3 in E2 postane E3,

Excel tudi ohranja absolutno referenčno celico enako, tako da absolutno območje $ D $ 5: $ G $ 5 ostane enako, ko 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 D3 v vašem delovnem listu
2. V ovitek D3 vnesite ovseno kašo in pritisnite tipko ENTER na tipkovnici
3. Kliknite na celico E3
4. Vnesite March v celico E3 in pritisnite tipko ENTER na tipkovnici
5. Kliknite na celico F2, da postane aktivna celica
6. Postavite miškin kazalec čez črni kvadrat v spodnjem desnem kotu. Kazalec se bo spremenil v znak plus + - to je ročica polnjenja
7. Kliknite levi gumb miške in povlecite ročico za polnjenje na celico F3
8. Spustite gumb miške in celica F3 mora vsebovati dvodimenzionalno formulo za iskanje
9. V celici F3 je treba prikazati vrednost 1287 $ - znesek prodaje za ovsene piškotke v mesecu marcu