Търсене на данни в Excel 2010 с HLOOKUP и VLOOKUP

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

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

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

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

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

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

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

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

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

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

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

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

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

Използване на функцията VLOOKUP за връщане на сумата на бакшиша за добавяне от таблица за търсене.

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

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

Забележете, че аргументът col_index_num използва оператор IF, за да определи стойността на колоната да върне. В този случай, ако стойността Tip_Percentage е 0,15, тогава функцията връща стойността във втората колона на таблицата . В противен случай той връща стойността в третата колона на същия ред.