Най-популярните от функциите за търсене на Excel 2016 са функциите HLOOKUP (за хоризонтално търсене) и VLOOKUP (за вертикално търсене). Тези функции се намират в падащото меню Търсене и справка в раздела Формули на лентата, както и в категорията Търсене и справка в диалоговия прозорец Вмъкване на функция. Те са част от мощна група функции, които могат да връщат стойности, като ги търсят в таблици с данни.
Функцията VLOOKUP търси вертикално (отгоре надолу) най-лявата колона на таблицата за търсене, докато програмата намери стойност, която съвпада или надвишава тази, която търсите. Функцията HLOOKUP търси хоризонтално (отляво надясно) в най-горния ред на таблицата за търсене, докато не намери стойност, която съвпада или надвишава тази, която търсите.
Функцията VLOOKUP използва следния синтаксис:
VLOOKUP(стойност на търсене, масив_таблица, номер_индекс_на_столче,[търсен_обхват])
Функцията HLOOKUP следва почти идентичния синтаксис:
HLOOKUP(търсенна_стойност,таблица_масив,ред_индекс_номер,[диапазон_търсене])
И в двете функции аргументът lookup_value е стойността, която искате да търсите в таблицата за търсене, а table_array е диапазонът от клетки или името на таблицата за търсене, която съдържа както стойността за търсене, така и свързаната стойност за връщане.
Аргументът col_index_num обозначава колоната на таблицата за търсене, съдържаща стойностите, които се връщат от функцията VLOOKUP въз основа на съвпадение на стойността на аргумента lookup_value спрямо тези в аргумента table_array. Вие определяте аргумента col_index_num , като броите колко колони е тази колона вдясно от първата колона на вертикалната таблица за справка и включвате първата колона на таблицата за справка в този брой.
Аргументът row_index_num обозначава реда, съдържащ стойностите, върнати от функцията HLOOKUP в хоризонтална таблица. Определяте аргумента row_index_num , като преброите колко реда надолу е този ред от горния ред на хоризонталната таблица за търсене. Отново включвате горния ред на таблицата за справка в този брой.
При въвеждане на col_index_num или row_index_num аргументи в функциите на VLOOKUP и HLOOKUP, стойността, която entercannot надвишава общия брой на колони или редове в таблицата търсене.
Незадължителният аргумент 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.
Използване на функцията VLOOKUP за връщане на сумата на бакшиша за добавяне от таблица за търсене.
За да използвате тази таблица с бакшиши, въведете процента на бакшиша (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 за връщане на цената на пекарна от таблица за търсене.
=HLOOKUP(елемент,таблица_цени,2,FALSE)
В тази функция HLOOKUP името на диапазона Item, което е дадено на колоната Item в диапазона C3:C62, се дефинира като аргумент lookup_value и таблицата с име на диапазона на клетката, която се дава на диапазона от клетки I1:M2, е аргументът table_array . Аргументът row_index_num е 2, защото искате Excel да върне цените във втория ред на таблицата за търсене на цени, а незадължителният аргумент range_lookup е FALSE, тъй като името на артикула в списъка с ежедневни продажби трябва да съвпада точно с името на артикула в таблицата за търсене на цени .
Като накарате функцията HLOOKUP да използва диапазона на таблицата с цени, за да въведе цената на дузина за всеки артикул за хлебни изделия в списъка за ежедневни продажби, вие правите много лесно актуализирането на която и да е от продажбите в списъка. Всичко, което трябва да направите, е да промените цената/цената му в този диапазон и функцията HLOOKUP незабавно актуализира новата цена в списъка с ежедневни продажби, където и да се продава артикулът.