Otsige Excelis üles üks väärtus VLOOKUP ja HLOOKUP abil

Excel 2013 otsingufunktsioonidest on kõige populaarsemad funktsioonid HLOOKUP (horisontaalseks otsinguks) ja VLOOKUP (vertikaalseks otsinguks). Need funktsioonid asuvad lindi vahekaardi Valemid rippmenüüs Otsi ja viide ning dialoogiboksi Funktsiooni lisamine kategoorias Otsimine ja viide.

Need on osa võimsast funktsioonide rühmast, mis võivad andmetabelitest üles otsida väärtusi tagastada.

Funktsioon VLOOKUP otsib vertikaalselt (ülevalt alla) otsingutabeli vasakpoolseimast veerust, kuni programm leiab väärtuse, mis vastab otsitavale või ületab seda. Funktsioon HLOOKUP otsib horisontaalselt (vasakult paremale) otsingutabeli ülemist rida, kuni see leiab väärtuse, mis vastab otsitavale või ületab seda.

Funktsioon VLOOKUP kasutab järgmist süntaksit:

VLOOKUP(otsingu_väärtus,tabeli_massiiv,veeruindeksi_arv,[vahemiku_otsing])

Funktsioon HLOOKUP järgib peaaegu identset süntaksit:

HLOOKUP(otsingu_väärtus,tabeli_massiiv,rea_indeksi_arv,[vahemiku_otsing])

Mõlema funktsiooni puhul on argument lookup_value väärtus, mida soovite otsingutabelist otsida, ja tabeli_massiiv on otsingutabeli lahtrivahemik või nimi, mis sisaldab nii otsitavat väärtust kui ka seotud väärtust, mida tagastada.

Col_index_num argumendiks VLOOKUP funktsiooniks on mitmeid kolonni, mille väärtust võrreldakse lookup_value argument vertikaalses tabelis. Funktsiooni HLOOKUP argument row_index_num on selle rea number, mille väärtusi võrreldakse horisontaalse tabelis oleva otsingu_väärtusega .

Sisestades col_index_num või row_index_num argumente VLOOKUP ja HLOOKUP funktsioone, siis tuleb sisestada väärtus on suurem kui null, mis ei ületa kokku veerud või read otsing tabelis.

Valikuline vastendustüüp argument nii VLOOKUP ja HLOOKUP funktsioonid on loogiline TRUE või FALSE, mis määrab, kas soovite Excel leida täpne või ligikaudne vaste lookup_value on table_array.

Kui määrate funktsioonis VLOOKUP või HLOOKUP väärtuse TRUE või jätate argumendi range_lookup välja, leiab Excel ligikaudse vaste. Kui määrate vahemiku_otsingu argumendiks FALSE , leiab Excel ainult täpsed vasted.

Ligikaudsete vastete leidmine käib ainult siis, kui otsite vertikaalse või horisontaalse otsingutabeli esimesest veerust või reast numbrilisi kirjeid (mitte teksti). Kui Excel ei leia täpset vastet selles Otsing veerus või reas, siis otsib järgmise kõrgeim väärtus, mis ei ületa lookup_value argument ja siis tagastab väärtuse veeru või rea määratud col_index_num või row_index_num argumendid.

Funktsioonide VLOOKUP ja HLOOKUP kasutamisel peavad otsingu veerus või reas (st vertikaalse otsingutabeli vasakpoolseimas veerus või horisontaalse otsingutabeli ülemises reas) olevad teksti- või numbrikirjed olema kordumatud. Need kirjed tuleb samuti järjestada või sortida kasvavas järjekorras; st tähestikuline järjestus tekstikirjete puhul ja madalaimast kõrgeima järjekord numbrikirjete puhul.

Joonisel on näide funktsiooni VLOOKUP kasutamisest, et tagastada jootraha tabelist kas 15% või 20% jootraha, olenevalt tšeki maksueelsest kogusummast. Lahter F3 sisaldab funktsiooni VLOOKUP:

=VLOOKUP(Maksueelne_summa,Nõpu_tabel,IF(Tip_protsent=0,15,2,3))

See valem tagastab jootraha summa, mis põhineb jootraha protsendil lahtris F1 ja tšeki maksueelsel summal lahtris F2.

Selle jootraha tabeli kasutamiseks sisestage jootraha protsent (15% või 20%) lahtrisse F1 (nimega Tip_Percentage) ja tšeki summa enne makse lahtrisse F2 (nimega Pretax_Total). Seejärel otsib Excel üles väärtuse, mille sisestate otsingutabeli esimeses veerus lahtrisse Pretax_Total, mis sisaldab lahtrivahemikku A2:C101 ja kannab nime Tip_Table.

Excel siis liigub mööda väärtused esimeses veerus Tip_Table kuni ta leiab vaste, misjärel programm kasutab col_index_num argument VLOOKUP funktsiooni määrata, milline ots summa sellel real tabeli naasta raku F3.

Kui Excel leiab, et lahtrisse Pretax_Total sisestatud väärtus (selles näites 16,50 dollarit) ei ühti täpselt ühega Tip_Table esimeses veerus olevatest väärtustest, jätkab programm otsingut võrdlusvahemikus allapoole, kuni leiab esimese väärtuse, mis ületab maksueelne kogusumma (selle näite puhul lahtris A19 17.00).

Excel siis liigub tagasi kuni eelmise tabeli ritta ja tagastab väärtuse veerus, mis sobib col_index_num argument VLOOKUP funktsiooni. (Seda seetõttu, et valikuline argument range_lookup on funktsioonist välja jäetud.)

Pange tähele, et tip tabelis näiteks kasutab IF funktsioon määrata col_index_num argument VLOOKUP funktsiooni raku F3.

Funktsioon IF määrab vihjetabelis kasutatava veeru numbri, sobitades lahtrisse Tip_Percentage (lahter F1) sisestatud protsendi 0,15-ga. Kui need ühtivad, tagastab funktsioon 2 argumendina col_index_num ja funktsioon VLOOKUP tagastab väärtuse Tip_Table vahemikus teisest veerust (15% veerust B).

Vastasel korral IF tagastab funktsioon 3 kuna col_index_num argument ja VLOOKUP funktsiooni taastumiseni väärtus vahemikus kolmandast veerust (20% veerus C) Tip_Table vahemikus.

Järgmisel joonisel on näide, mis kasutab funktsiooni HLOOKUP, et otsida iga eraldi hinnaotsingu tabelist salvestatud pagaritoote hinda ja seejärel tagastada see hind loendi Päevamüük veergu Hind/Doz. Lahter F3 sisaldab algset valemit koos funktsiooniga HLOOKUP, mis seejärel kopeeritakse veergu F:

=LOOKUP(kaup,hinnatabel,2,VÄÄR)

Selles funktsioonis HLOOKUP määratletakse vahemiku nimi Üksus, mis on antud veerule Üksus vahemikus C3:C62, argumendina lookup_value ja lahtrivahemiku nimetabel Hind, mis antakse lahtrivahemikule I1:M2, on argument table_array .

Row_index_num argument on 2, sest sa tahad Excel tagastada hinnad teises reas Hinnad Otsing tabeli ja vabatahtlik vastendustüüp argument on FALSE, sest üksust nime Daily Müük nimekirja peab vastama täpselt kirje nime Hinnad Otsing tabelis .

Kui funktsioon HLOOKUP kasutab tabeli Hindade vahemikku, et sisestada iga pagaritoodete hind tosina kohta loendis Päevamüük, muudate loendis oleva müügi värskendamise väga lihtsaks.

Kõik, mida pead tegema, on muuta selle Hind/Doz maksumust selles vahemikus ja funktsioon HLOOKUP värskendab viivitamatult uut hinda igapäevaste müügiloendis kõikjal, kus kaupa müüakse.