Iskanje podatkov v Excelu 2007 s HLOOKUP in VLOOKUP

Najbolj priljubljeni funkciji iskanja v Excelu 2007 sta HLOOKUP (za vodoravno iskanje) in VLOOKUP (za vertikalno iskanje). Te funkcije se nahajajo v kategoriji Iskanje in sklic na zavihku Formule na traku in v pogovornem oknu Vstavi funkcijo. So del močne skupine funkcij, ki lahko vrnejo vrednosti tako, da jih poiščejo v podatkovnih tabelah.

Funkcija HLOOKUP išče vodoravno (od leve proti desni) najvišjo vrstico tabele, dokler ne najde vrednosti, ki se ujema ali presega tisto, ki jo iščete. Funkcija VLOOKUP išče navpično (od zgoraj navzdol) po skrajnem levem stolpcu tabele, dokler program ne najde vrednosti, ki se ujema ali presega tisto, ki jo iščete.

Pri uporabi funkcij VLOOKUP in HLOOKUP morajo biti besedilni ali številčni vnosi v stolpcu ali vrstici Iskanje (to je skrajni levi stolpec navpične iskalne tabele ali zgornja vrstica vodoravne iskalne tabele) edinstveni. Te vnose je treba tudi razvrstiti ali razvrstiti v naraščajočem vrstnem redu; to je abecedni vrstni red za vnose besedila in vrstni red od najnižje do najvišje za številske vnose.

Funkcija HLOOKUP uporablja naslednjo sintakso:

=HLOOKUP(iskana_vrednost,matrika_tabel,številka_indeks_vrstice,[iskanje_obsega])

Funkcija VLOOKUP sledi skoraj identični sintaksi:

=VLOOKUP(iskana_vrednost,matrika_tabel,številka_indeks_stolpca,[iskanje_obsega])

V obeh funkcijah je argument lookup_value vrednost, ki jo želite poiskati v tabeli, table_array je obseg celic ali ime tabele, ki vsebuje tako vrednost, ki jo je treba poiskati, kot povezano vrednost, ki jo je treba vrniti. Row_index_num argument v funkciji HLOOKUP je številka vrstice, katerih vrednost se želite vrniti; col_index_num argument v funkciji VLOOKUP je število stolpca, katerega vrednost se želite vrniti.

Opcijski obseg_iskanja argument tako v VLOOKUP in funkcij HLOOKUP je logično pravilna ali napačna, ki določa, ali želite, Excel, da bi našli natančno ali približno tekmo za lookup_value v table_array . Ko podate TRUE ali izpustite argument range_lookup , Excel najde približno ujemanje. Ko kot argument range_lookup podate FALSE , Excel najde samo natančna ujemanja.

Iskanje približnih ujemanj se nanaša le, če iščete številske vnose (namesto besedila). Ko Excel ne najde natančno ujemanje v tej Iskanje stolpec ali vrstico, da najde naslednjo najvišjo vrednost, ki ne presega lookup_value navedbe in nato vrne vrednost v stolpcu ali vrstici, ki jih imenuje col_index_num ali row_index_num argumentov.

Spodnja slika prikazuje primer uporabe funkcije VLOOKUP za vrnitev 15- ali 20-odstotne napitnine iz tabele napitnine, odvisno od vsote čeka pred obdavčitvijo. Celica F3 vsebuje funkcijo VLOOKUP:

=VLOOKUP(Predavek_skupaj,Tip_Tabela,IF(Odstotek_napitnine=0,15,2,3))

Ta formula vrne znesek napitnine na podlagi odstotka napitnine v celici F1 in zneska pred obdavčitvijo čeka v celici F2.

Uporaba funkcije VLOOKUP za vrnitev zneska napitnine za dodajanje iz tabele za iskanje.

Če želite uporabiti to tabelo napitnine, vnesite odstotek napitnine (15 % ali 20 %) v celico F1 (imenovano Tip_Percentage) in znesek čeka pred davkom v celico F2 (imenovano Pretax_Total). Excel nato poišče vrednost, ki jo vnesete v celico Predax_Total v prvem stolpcu iskalne tabele, ki vključuje obseg celic A2:C101 in se imenuje Tip_Table.

Excel nato premakne vrednosti v prvem stolpcu Tab_Table navzdol, dokler ne najde ujemanja. Če Excel ugotovi, da se vrednost, vnesena v celico Pretax_Total (16,50 USD v tem primeru), ne ujema natančno z eno od vrednosti v prvem stolpcu Tab_Table, program nadaljuje z iskanjem po obsegu primerjave, dokler ne naleti na prvo vrednost, ki presega vsota pred obdavčitvijo (17,00 v celici A19 v tem primeru). Excel nato premakne nazaj do prejšnje vrstice v tabeli in vrne vrednost v stolpcu, ki se ujema z col_index_num argument funkcije VLOOKUP (to je zato, ker ni obvezno obseg_iskanja je argument izpuščen iz funkcije).

Upoštevajte, da argument col_index_number uporablja stavek IF za določitev vrednosti, katerega stolpca naj vrne. V tem primeru, če je vrednost Tip_Percentage 0,15, potem funkcija vrne vrednost v drugem stolpcu tabele v vrstici, ki je bila identificirana z iskalno vrednostjo. V nasprotnem primeru vrne vrednost v tretjem stolpcu iste vrstice.