Kërkimi i të dhënave në Excel 2010 me HLOOKUP dhe VLOOKUP

Funksionet më të njohura të kërkimit në Excel 2010 janë HLOOKUP (për kërkimin horizontal) dhe VLOOKUP (për kërkimin vertikal). Këto funksione janë të vendosura në kategorinë Lookup & Reference në skedën Formulat të Ribbon dhe në kutinë e dialogut Insert Function. Ato janë pjesë e një grupi të fuqishëm funksionesh që mund të kthejnë vlera duke i kërkuar në tabelat e të dhënave.

Funksioni HLOOKUP kërkon horizontalisht (nga e majta në të djathtë) rreshtin më të lartë të një tabele Kërkimi derisa të gjejë një vlerë që përputhet ose gati përputhet me atë që po kërkoni. Funksioni VLOOKUP kërkon vertikalisht (nga lart poshtë) kolonën më të majtë të një tabele Kërkimi derisa të gjejë një vlerë që përputhet ose pothuajse përputhet me atë që po kërkoni.

Kur përdorni funksionet VLOOKUP dhe HLOOKUP, teksti ose hyrjet numerike në kolonën ose rreshtin e kërkimit (d.m.th., kolona më e majtë e një tabele kërkimi vertikal ose rreshti i sipërm i një tabele kërkimi horizontal) duhet të jenë unik. Këto shënime duhet gjithashtu të renditen ose renditen në rend rritës; d.m.th., renditja alfabetike për hyrjet e tekstit dhe renditja nga më e ulëta në më e larta për hyrjet numerike.

Funksioni HLOOKUP përdor sintaksën e mëposhtme:

=HLOOKUP(vlera_kërkuese, grup_tabele, numri_indeksi_rreshti, [kërkimi_range])

Funksioni VLOOKUP ndjek sintaksën pothuajse identike:

=VLOOKUP(vlera_kërkuese, grup_tabele, numri_indeksi_kole, [kërkimi_range])

Në të dy funksionet, argumenti lookup_value është vlera që dëshironi të kërkoni në tabelë dhe tabela_array është diapazoni i qelizave ose emri i tabelës që përmban vlerën që duhet kërkuar dhe vlerën përkatëse për t'u kthyer. Row_index_num argument në funksion HLOOKUP është numri i rresht vlera e të cilave ju duan të kthehen; col_index_num argument në funksion VLOOKUP është numri i kolonës vlera e të cilave ju duan të kthehen.

Opsional range_lookup Argumenti në të dyja funksionet HLOOKUP e VLOOKUP dhe është TRUE logjike apo e rreme që specifikon nëse doni Excel për të gjetur një ndeshje e saktë apo të përafërt për lookup_value në table_array . Kur specifikoni TRUE ose hiqni argumentin range_lookup , Excel gjen një përputhje të përafërt. Kur specifikoni FALSE si argument range_lookup , Excel gjen vetëm përputhje të sakta.

Gjetja e përputhjeve të përafërta i përket vetëm kur kërkoni hyrje numerike (në vend të tekstit). Kur Excel nuk gjen një ndeshje e saktë në këtë kolonë lookup ose rresht, ajo ndodhet vlerën e ardhshëm më të lartë që nuk e tejkalon lookup_value argumentin dhe pastaj e kthen vlerën në kolonën apo rresht të caktuar nga col_index_num ose row_index_num argumente.

Figura më poshtë tregon një shembull të përdorimit të funksionit VLOOKUP për të kthyer një bakshish 15% ose 20% nga një tabelë këshillash. Qeliza F3 përmban funksionin VLOOKUP:

=VLOOKUP(Taksimi_Total,Tabela_Këshilla,IF(Përqindja_Bashkë=0.15,2,3))

Kjo formulë kthen shumën e bakshishit bazuar në përqindjen e bakshishit në qelizën F1 dhe shumën para tatimit të çekut në qelizën F2.

Përdorimi i funksionit VLOOKUP për të kthyer sasinë e majës për të shtuar nga një tabelë kërkimi.

Për të përdorur këtë tabelë të bakshishit, vendosni përqindjen e bakshishit (15% ose 20%) në qelizën F1 (e quajtur Tip_Percentage) dhe shumën e çekut para taksës në qelizën F2 (e quajtur Pretax_Total). Më pas, Excel kërkon vlerën që futni në qelizën Pretax_Total në tabelën e kërkimit, e cila përfshin gamën e qelizave A2:C101 dhe quhet Tip_Table.

Më pas, Excel lëviz poshtë vlerat në kolonën e parë të Tip_Table derisa të gjejë një përputhje. Nëse Excel zbulon se vlera e futur në qelizën Pretax_Total (16,50 dollarë në këtë shembull) nuk përputhet saktësisht me një nga vlerat në kolonën e parë të Tip_Table, programi vazhdon të kërkojë në intervalin e krahasimit derisa të ndeshet me vlerën e parë që tejkalon totali para tatimit (17.00 në qelizën A19 në këtë shembull). Më pas, Excel lëviz përsëri në rreshtin e mëparshëm në tabelë dhe kthen vlerën në kolonë që përputhet me argumentin col_index_num të funksionit VLOOKUP (kjo për shkak se argumenti opsional range_lookup është hequr nga funksioni).

Vini re se argumenti col_index_num përdor një deklaratë IF për të përcaktuar vlerën e cilës kolonë të kthehet. Në këtë rast, nëse vlera Tip_Percentage është 0.15, atëherë funksioni kthen vlerën në kolonën e dytë të tabelës . Përndryshe, ai kthen vlerën në kolonën e tretë të të njëjtit rresht.