Потърсете една стойност с VLOOKUP и HLOOKUP в Excel

Най-популярните функции за търсене в Excel 2013 са функциите HLOOKUP (за хоризонтално търсене) и VLOOKUP (за вертикално търсене). Тези функции се намират в падащото меню Търсене и справка в раздела Формули на лентата, както и в категорията Търсене и справка в диалоговия прозорец Вмъкване на функция.

Те са част от мощна група функции, които могат да връщат стойности, като ги търсят в таблици с данни.

Функцията VLOOKUP търси вертикално (отгоре надолу) най-лявата колона на таблицата за търсене, докато програмата намери стойност, която съвпада или надвишава тази, която търсите. Функцията HLOOKUP търси хоризонтално (отляво надясно) в най-горния ред на таблицата за търсене, докато не намери стойност, която съвпада или надвишава тази, която търсите.

Функцията VLOOKUP използва следния синтаксис:

VLOOKUP(стойност на търсене, масив_таблица, номер_индекс_на_столче,[търсен_обхват])

Функцията HLOOKUP следва почти идентичния синтаксис:

HLOOKUP(търсенна_стойност,таблица_масив,ред_индекс_номер,[диапазон_търсене])

И в двете функции аргументът lookup_value е стойността, която искате да търсите в таблицата за търсене, а table_array е диапазонът от клетки или името на таблицата за търсене, която съдържа както стойността за търсене, така и свързаната стойност за връщане.

Аргументът col_index_num във функцията VLOOKUP е номерът на колоната, чиито стойности се сравняват с аргумента lookup_value във вертикална таблица. Аргументът row_index_num във функцията HLOOKUP е номерът на реда, чиито стойности се сравняват с lookup_value в хоризонтална таблица.

При въвеждане на col_index_num или row_index_num аргументи в функциите на VLOOKUP и HLOOKUP, трябва да въведете стойност по-голяма от нула, което не надвишава общия брой на колони или редове в таблицата търсене.

Незадължителният аргумент range_lookup както във функциите VLOOKUP, така и във функциите HLOOKUP е логическото TRUE или FALSE, което указва дали искате Excel да намери точно или приблизително съвпадение за lookup_value в table_array.

Когато посочите TRUE или пропуснете аргумента range_lookup във функцията VLOOKUP или HLOOKUP, Excel намира приблизително съвпадение. Когато посочите FALSE като аргумент range_lookup , Excel намира само точни съвпадения.

Намирането на приблизителни съвпадения се отнася само когато търсите цифрови записи (а не текст) в първата колона или ред на вертикалната или хоризонталната таблица за търсене. Когато Excel не намери точно съвпадение в тази колона търсене или ред, той открива следващата най-висока стойност, която не надвишава търсена_стойност аргумент и след това се връща стойността в колона или ред, определен от col_index_num или row_index_num аргументи.

Когато използвате функциите VLOOKUP и HLOOKUP, текстът или числовите записи в колоната или реда за справка (тоест най-лявата колона на вертикална справочна таблица или горния ред на хоризонтална справочна таблица) трябва да са уникални. Тези записи също трябва да бъдат подредени или сортирани във възходящ ред; тоест азбучен ред за текстовите записи и ред от най-ниския към най-високия за цифровите записи.

Фигурата показва пример за използване на функцията VLOOKUP за връщане на 15% или 20% бакшиш от таблицата за бакшиши, в зависимост от общата сума на чека преди облагане с данъци. Клетка F3 съдържа функцията VLOOKUP:

=VLOOKUP(Общо_предварителни данъци,Table_Table,IF(Tip_Percentage=0,15,2,3))

Тази формула връща сумата на бакшиша въз основа на процента на върха в клетка F1 и сумата преди облагане с данък на чека в клетка F2.

За да използвате тази таблица с бакшиши, въведете процента на бакшиша (15% или 20%) в клетка F1 (наречена Tip_Percentage) и сумата на чека преди данък в клетка F2 (наречена Pretax_Total). След това Excel търси стойността, която въвеждате в клетката Pretax_Total в първата колона на таблицата за справка, която включва диапазона от клетки A2:C101 и е наречена Tip_Table.

След това Excel премества стойностите в първата колона на Tip_Table, докато намери съвпадение, при което програмата използва аргумента col_index_num във функцията VLOOKUP, за да определи коя сума на съвет от този ред на таблицата да се върне в клетка F3.

Ако Excel установи, че стойността, въведена в клетката Pretax_Total ($16,50 в този пример), не съвпада точно с една от стойностите в първата колона на Tip_Table, програмата продължава да търси надолу в диапазона за сравнение, докато не срещне първата стойност, която надвишава общата сума преди данъци (17,00 в клетка A19 в този пример).

След това Excel се придвижва обратно към предишния ред в таблицата и връща стойността в колоната, която съответства на аргумента col_index_num на функцията VLOOKUP. (Това е така, защото незадължителният аргумент range_lookup е пропуснат от функцията.)

Обърнете внимание, че примерът за таблица с съвети използва функция IF за определяне на аргумента col_index_num за функцията VLOOKUP в клетка F3.

Функцията IF определя номера на колоната, който да се използва в таблицата със съвети, като съпоставя процента, въведен в Tip_Percentage (клетка F1) с 0,15. Ако съвпадат, функцията връща 2 като аргумент col_index_num , а функцията VLOOKUP връща стойност от втората колона (15% колона B) в диапазона Tip_Table.

В противен случай функцията IF връща 3 като аргумент col_index_num , а функцията VLOOKUP връща стойност от третата колона (20% колона C) в диапазона Tip_Table.

Следващата фигура показва пример, който използва функцията HLOOKUP, за да търси цената на всеки пекарски артикул, съхранен в отделна таблица за търсене на цени и след това да върне тази цена в колоната Цена/Доз на списъка с ежедневни продажби. Клетка F3 съдържа оригиналната формула с функцията HLOOKUP, която след това се копира надолу в колона F:

=HLOOKUP(елемент,таблица_цени,2,FALSE)

В тази функция HLOOKUP името на диапазона Item, което е дадено на колоната Item в диапазона C3:C62, се дефинира като аргумент lookup_value и таблицата с име на диапазона на клетката, която се дава на диапазона от клетки I1:M2, е аргументът table_array .

Аргументът row_index_num е 2, защото искате Excel да върне цените във втория ред на таблицата за търсене на цени, а незадължителният аргумент range_lookup е FALSE, тъй като името на артикула в списъка с ежедневни продажби трябва да съвпада точно с името на артикула в таблицата за търсене на цени .

Като накарате функцията HLOOKUP да използва диапазона на таблицата с цени, за да въведе цената на дузина за всеки артикул за хлебни изделия в списъка за ежедневни продажби, вие правите много лесно актуализирането на която и да е от продажбите в списъка.

Всичко, което трябва да направите, е да промените цената/цената му в този диапазон и функцията HLOOKUP незабавно актуализира новата цена в списъка с ежедневни продажби, където и да се продава артикулът.