Iskanje podatkov v Excelu 2010 s HLOOKUP in VLOOKUP

Najbolj priljubljeni funkciji iskanja v Excelu 2010 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 iskalne tabele, dokler ne najde vrednosti, ki se ujema ali se skoraj ujema s tisto, ki jo iščete. Funkcija VLOOKUP išče navpično (od zgoraj navzdol) po skrajnem levem stolpcu iskalne tabele, dokler ne najde vrednosti, ki se ujema ali se skoraj ujema s 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 pa 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 samo na to, ko 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 % nasveta iz tabele nasvetov. 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 Pretax_Total v iskalni tabeli, 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_num 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 nasprotnem primeru vrne vrednost v tretjem stolpcu iste vrstice.