Знайдіть одне значення за допомогою 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 або номер_рядка аргументи в функції ВПР і ГПР, необхідно ввести більше значення , ніж нуль , який не перевищує загальну кількість рядків або стовпців в таблиці.

Необов’язковий аргумент range_lookup як у функціях VLOOKUP, так і у функціях HLOOKUP є логічною TRUE або FALSE, яка вказує, чи потрібно, щоб Excel знайшов точну чи приблизну відповідність для lookup_value в масиві table_array.

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

Знайти приблизні збіги можна лише тоді, коли ви шукаєте числові записи (а не текст) у першому стовпці або рядку вертикальної або горизонтальної таблиці пошуку. Коли Excel не знаходить точну відповідність в цьому стовпці підстановок або рядках, він знаходить наступне по величині значення , яке не перевищує Lookup_Value аргументу і повертає значення в стовпці або рядку позначеного Col_Index_Num або номер_рядка аргументів.

При використанні функцій VLOOKUP і HLOOKUP текстові або числові записи в стовпці або рядку пошуку (тобто крайній лівий стовпець вертикальної таблиці пошуку або верхній рядок горизонтальної таблиці пошуку) повинні бути унікальними. Ці записи також мають бути впорядковані або відсортовані в порядку зростання; тобто алфавітний порядок для текстових записів і порядок від найнижчого до найвищого для цифрових записів.

На малюнку показано приклад використання функції VLOOKUP для повернення 15% або 20% чайових з таблиці чайових, залежно від загальної суми чека до оподаткування. У клітинці F3 міститься функція VLOOKUP:

=VLOOKUP(Усього перед оподаткуванням,Таблиця_підказки,IF(Відсоток_підказки=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 негайно оновить нову ціну в списку щоденних продажів, де б не продається товар.