Datu meklēšana programmā Excel 2010, izmantojot HLOOKUP un VLOOKUP

Populārākās no uzmeklēšanas funkcijām programmā Excel 2010 ir HLOOKUP (horizontālajai uzmeklēšanai) un VLOOKUP (vertikālajai uzmeklēšanai). Šīs funkcijas atrodas kategorijā Uzmeklēšana un atsauce lentes cilnē Formulas un dialoglodziņā Funkcijas ievietošana. Tie ir daļa no spēcīgas funkciju grupas, kas var atgriezt vērtības, meklējot tās datu tabulās.

Funkcija HLOOKUP meklē horizontāli (no kreisās puses uz labo) uzmeklēšanas tabulas augšējā rindā, līdz tā atrod vērtību, kas atbilst vai gandrīz atbilst meklētajai vērtībai. Funkcija VLOOKUP meklē vertikāli (no augšas uz leju) uzmeklēšanas tabulas vistālāk kreisajā kolonnā, līdz tā atrod vērtību, kas atbilst vai gandrīz atbilst tai, kuru meklējat.

Izmantojot funkcijas VLOOKUP un HLOOKUP, teksta vai ciparu ierakstiem kolonnā vai rindā Uzmeklēšana (tas ir, vertikālās uzmeklēšanas tabulas galējā kreisajā kolonnā vai horizontālās uzmeklēšanas tabulas augšējā rindā) ir jābūt unikāliem. Šie ieraksti arī jāsakārto vai jāsakārto augošā secībā; tas ir, alfabētiskā secībā teksta ierakstiem un secībā no zemākās līdz augstākajai ciparu ierakstiem.

Funkcija HLOOKUP izmanto šādu sintaksi:

=UZKLĀŠANA(uzmeklēšanas_vērtība,tabulas_masīvs,rindas_indeksa_numurs,[diapazona_uzmeklēšana])

Funkcija VLOOKUP ievēro gandrīz identisku sintakse:

=UZKLĀŠANA(uzmeklēšanas_vērtība,tabulas_masīvs,kolas_indeksa_numurs,[diapazona_uzmeklēšana])

Abās funkcijās arguments lookup_value ir vērtība, kuru vēlaties meklēt tabulā, un table_array ir šūnu diapazons vai tabulas nosaukums, kurā ir gan uzmeklējamā vērtība, gan saistītā vērtība, kas jāatgriež. Row_index_num arguments HLOOKUP funkcija ir numurs pēc kārtas, kura vērtība, ko jūs vēlaties atgriezties; kolonnas_indeksa_numurs arguments VLOOKUP funkcija ir numurs kolonnas, kura vērtība, ko jūs vēlaties, lai atgrieztos.

Izvēles diapazona_uzmeklēšana arguments gan VLOOKUP un HLOOKUP funkcijas ir loģisks patiess vai nepatiess, kas nosaka, vai jūs vēlaties, Excel, lai atrastu precīzu vai aptuvenu sakritību uzmeklējamā_vērtība ar tabulas_masīvs . Kad norādāt TRUE vai izlaižat argumentu range_lookup , programma Excel atrod aptuvenu atbilstību. Norādot FALSE kā argumentu range_lookup , programma Excel atrod tikai precīzas atbilstības.

Aptuveno atbilstību var atrast tikai tad, ja meklējat ciparu ierakstus (nevis tekstu). Kad Excel neatrod precīza atbilstība šajā Uzmeklēšanas kolonnā vai rindā, tas atrod nākamo augstāko vērtību, kas nepārsniedz šo uzmeklējamā_vērtība argumentu, un pēc tam atgriež vērtību kolonnā vai rindā, ko izraudzījusies kolonnas_indeksa_numurs vai row_index_num argumentiem.

Tālāk esošajā attēlā ir parādīts funkcijas VLOOKUP izmantošanas piemērs, lai atgrieztu 15% vai 20% dzeramnaudu no dzeramnaudas tabulas. Šūnā F3 ir VLOOKUP funkcija:

=VLOOKUP(Pirmsnodokļa_kopā,Padomu_tabula,IF(Padoma_procenti=0,15,2,3))

Šī formula atgriež dzeramnaudas summu, pamatojoties uz dzeramnaudas procentuālo daudzumu šūnā F1 un čeka pirmsnodokļu summu šūnā F2.

Izmantojot funkciju VLOOKUP, lai atgrieztu dzeramnaudas summu, kas jāpievieno no uzmeklēšanas tabulas.

Lai izmantotu šo dzeramnaudas tabulu, ievadiet dzeramnaudas procentuālo daļu (15% vai 20%) šūnā F1 (nosaukums Tip_Percentage) un čeka summu pirms nodokļu nomaksas šūnā F2 (ar nosaukumu Pretax_Total). Pēc tam Excel atrod vērtību, ko ievadāt uzmeklēšanas tabulas šūnā Pretax_Total, kas ietver šūnu diapazonu A2:C101 un kura nosaukums ir Tip_Table.

Pēc tam Excel pārvieto uz leju vērtības Tip_Table pirmajā kolonnā, līdz tiek atrasta atbilstība. Ja programma Excel konstatē, ka šūnā Pretax_Total ievadītā vērtība (šajā piemērā 16,50 ASV dolāri) precīzi neatbilst kādai no Tip_Table pirmajā kolonnā esošajām vērtībām, programma turpina meklēšanu salīdzināšanas diapazonā, līdz tiek atrasta pirmā vērtība, kas pārsniedz pirmsnodokļu kopsumma (šajā piemērā šūnā A19 17.00). Excel tad pārceļas atpakaļ līdz iepriekšējā rindā tabulā un atgriež vērtību kolonnā, kas atbilst kolonnas_indeksa_numurs argumentu VLOOKUP funkciju (tas ir tāpēc, ka izvēles diapazona_uzmeklēšana arguments ir izlaists no funkciju).

Ņemiet vērā, ka arguments col_index_num izmanto priekšrakstu IF, lai noteiktu, kuras kolonnas vērtību atgriezt. Šajā gadījumā, ja Tip_Percentage vērtība ir 0,15, funkcija atgriež vērtību tabulas otrajā kolonnā . Pretējā gadījumā tas atgriež vērtību tās pašas rindas trešajā kolonnā.