Andmete otsimine rakenduses Excel 2007 funktsioonidega HLOOKUP ja VLOOKUP

Excel 2007 otsingufunktsioonidest on kõige populaarsemad HLOOKUP (horisontaalseks otsinguks) ja VLOOKUP (vertikaalseks otsinguks). Need funktsioonid asuvad lindi vahekaardi Valemid kategoorias Otsimine ja viide ja dialoogiboksis Funktsiooni lisamine. Need on osa võimsast funktsioonide rühmast, mis võivad andmetabelitest üles otsida väärtusi tagastada.

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

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.

Funktsioon HLOOKUP kasutab järgmist süntaksit:

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

Funktsioon VLOOKUP järgib peaaegu identset süntaksit:

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

Mõlema funktsiooni puhul on argument lookup_value väärtus, mida soovite tabelist otsida, tabeli_massiiv on lahtrivahemik või tabeli nimi, mis sisaldab nii otsitavat väärtust kui ka seotud väärtust, mida tagastada. Funktsiooni HLOOKUP argument row_index_num on selle rea number, mille väärtust soovite tagastada; col_index_num argument VLOOKUP funktsiooni on number veerus, mille väärtus soovite naasta.

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 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 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.

Alloleval 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.

Funktsiooni VLOOKUP kasutamine otsingutabelist lisatava jootraha summa tagastamiseks.

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.

Seejärel liigutab Excel Tip_Table esimeses veerus olevaid väärtusi allapoole, kuni leiab vaste. 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 sellepärast, et vabatahtlik vastendustüüp argument on jäetud funktsiooni).

Pange tähele, et argument col_index_number kasutab IF-lauset, et määrata, millise veeru väärtus tagastada. Sel juhul, kui Tip_Percentage väärtus on 0,15, tagastab funktsioon tabeli teises veerus oleva väärtuse selles reas, mis tuvastati otsingu_väärtusega. Vastasel juhul tagastab see sama rea ​​kolmandas veerus oleva väärtuse.