Programmā Excel atrodiet vienu vērtību, izmantojot VLOOKUP un HLOOKUP

Populārākās no uzmeklēšanas funkcijām programmā Excel 2013 ir funkcijas HLOOKUP (horizontālajai uzmeklēšanai) un VLOOKUP (vertikālajai uzmeklēšanai). Šīs funkcijas atrodas lentes cilnes Formulas nolaižamajā izvēlnē Uzmeklēšana un atsauce, kā arī dialoglodziņa Funkcijas ievietošana kategorijā Uzmeklēšana un atsauce.

Tie ir daļa no spēcīgas funkciju grupas, kas var atgriezt vērtības, meklējot tās datu tabulās.

Funkcija VLOOKUP meklē vertikāli (no augšas uz leju) uzmeklēšanas tabulas galējā kreisajā kolonnā, līdz programma atrod vērtību, kas atbilst vai pārsniedz to, kuru meklējat. Funkcija HLOOKUP meklē horizontāli (no kreisās uz labo) uzmeklēšanas tabulas augšējā rindā, līdz tā atrod vērtību, kas atbilst vai pārsniedz to, kuru meklējat.

Funkcija VLOOKUP izmanto šādu sintaksi:

VLOOKUP(uzmeklēšanas_vērtība,tabulas_masīvs,kolas_indeksa_numurs,[diapazona_uzmeklēšana])

Funkcija HLOOKUP ievēro gandrīz identisku sintakse:

HLOOKUP(uzmeklēšanas_vērtība,tabulas_masīvs,rindas_indeksa_numurs,[diapazona_uzmeklēšana])

Abās funkcijās arguments lookup_value ir vērtība, kuru vēlaties meklēt uzmeklēšanas tabulā, un table_array ir uzmeklēšanas tabulas šūnu diapazons vai nosaukums, kas satur gan meklējamo vērtību, gan saistīto vērtību, kas jāatgriež.

Kolonnas_indeksa_numurs arguments VLOOKUP funkcija ir numurs kolonnas, kuru vērtības tiek salīdzinātas ar uzmeklējamā_vērtība argumentu vertikālā tabulā. Row_index_num arguments HLOOKUP funkcija ir numurs pēc kārtas, kuru vērtības tiek salīdzinātas ar uzmeklējamā_vērtība horizontālā tabulā.

Ievadot kolonnas_indeksa_numurs vai row_index_num argumentus VLOOKUP un HLOOKUP funkcijas, jums ir jāievada vērtība ir lielāka par nulli, kas nepārsniedz kopējo skaitu kolonnu vai rindu Lookup tabulā.

Izvēles arguments range_lookup gan funkcijās VLOOKUP, gan HLOOKUP ir loģisks TRUE vai FALSE, kas norāda, vai vēlaties, lai programma Excel atrastu precīzu vai aptuvenu atbilstību uzmeklēšanas_vērtībai tabulā_masīvs.

Kad funkcijā VLOOKUP vai HLOOKUP 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 vertikālās vai horizontālās uzmeklēšanas tabulas pirmajā kolonnā vai rindā 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.

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.

Attēlā parādīts piemērs funkcijas VLOOKUP izmantošanai, lai atgrieztu 15% vai 20% dzeramnaudu no dzeramnaudas tabulas atkarībā no čeka pirmsnodokļu kopsummas. Šū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.

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 pirmās kolonnas šū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 atrod atbilstību, pēc tam programma izmanto col_index_num argumentu funkcijā VLOOKUP, lai noteiktu, kura padoma summa no šīs tabulas rindas jāatgriežas šūnā F3.

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 funkcijā ir izlaists izvēles arguments range_lookup .)

Ņemiet vērā, ka padomu tabulas piemērā tiek izmantota funkcija IF, lai noteiktu col_index_num argumentu funkcijai VLOOKUP šūnā F3.

Funkcija IF nosaka kolonnas numuru, kas jāizmanto padomu tabulā, saskaņojot procentuālo daļu, kas ievadīta Tip_Percentage (šūna F1) ar 0,15. Ja tie atbilst, funkcija atgriež 2 kā argumentu col_index_num, un funkcija VLOOKUP atgriež vērtību no otrās kolonnas (15% kolonnas B) Tip_Table diapazonā.

Pretējā gadījumā funkcija IF atgriež 3 kā argumentu col_index_num, bet funkcija VLOOKUP atgriež vērtību no trešās kolonnas (20% kolonnas C) diapazonā Tip_Table.

Nākamajā attēlā ir parādīts piemērs, kurā tiek izmantota funkcija HLOOKUP, lai meklētu katra maizes izstrādājuma cenu, kas saglabāta atsevišķā cenu meklēšanas tabulā, un pēc tam atgrieztu šo cenu saraksta Dienas pārdošanas kolonnā Cena/Doz. Šūnā F3 ir ietverta sākotnējā formula ar funkciju HLOOKUP, kas pēc tam tiek kopēta kolonnā F:

=UZLABOŠANA(prece,cenu_tabula,2,FALSE)

Šajā funkcijā HLOOKUP diapazona nosaukums Vienums, kas tiek piešķirts kolonnai Vienums diapazonā C3:C62, ir definēts kā arguments lookup_value, un šūnu diapazona nosaukuma tabula Cenu, kas tiek piešķirta šūnu diapazonam I1:M2, ir arguments table_array .

Row_index_num arguments ir 2, jo jūs vēlaties Excel atgriezties cenas otrajā rindā cenu uzmeklēšanas tabulu un izvēles diapazona_uzmeklēšana arguments ir nepatiess, jo prece nosaukums Daily pārdošanas sarakstā ir jāatbilst precīzi vienības nosaukumu cenu uzmeklēšanas tabulu .

Ja funkcija HLOOKUP izmanto cenu tabulas diapazonu, lai ievadītu cenu par duci katrai maizes izstrādājumu precei sarakstā Ikdienas izpārdošanas, jūs padarāt to ļoti vienkāršu, lai atjauninātu kādu no sarakstā esošajiem pārdošanas darījumiem.

Viss, kas jums jādara, ir jāmaina cena/doza cena šajā diapazonā, un funkcija HLOOKUP nekavējoties atjaunina jauno cenu ikdienas pārdošanas sarakstā, kur vien prece tiek pārdota.