Завантажте зразок робочої книги
Цей підручник демонструє, як користуватися Функція Excel HLOOKUP в Excel, щоб знайти значення.
Огляд функції HLOOKUP
Функція HLOOKUP Hlookup означає горизонтальний пошук. Він шукає значення у верхньому рядку таблиці. Потім повертає значення певної кількості рядків вниз від знайденого значення. Це те саме, що і vlookup, за винятком того, що він шукає значення горизонтально, а не вертикально.
(Зверніть увагу, як виглядають вхідні дані формул)
Синтаксис і вхід функції HLOOKUP:
| 1 | = HLOOKUP (lookup_value, table_array, row_index_num, range_lookup) |
lookup_value - Значення, яке ви хочете шукати.
table_array -Таблиця, з якої можна отримати дані.
row_index_num - Номер рядка, з якого потрібно отримати дані.
range_lookup -[необов’язково] Логічне значення, яке вказує точну або приблизну відповідність. За замовчуванням = TRUE = приблизне збіг.
Що таке функція HLOOKUP?
Як одна зі старих функцій у світі електронних таблиць, функція HLOOKUP використовується Hорізонтальний Пошук. Він має кілька обмежень, які часто долаються іншими функціями, такими як INDEX/MATCH. Крім того, більшість таблиць побудовано вертикально, але буває, що корисно здійснювати пошук по горизонталі.
Основний приклад
Давайте подивимося на зразок даних з підручника. Ми розглянемо кілька прикладів вилучення інформації для конкретних студентів.
Якщо ми хочемо знайти, до якого класу належить Боб, ми написали б формулу:
| 1 | = HLOOKUP ("Боб", A1: E3, 2, FALSE) |
Важливо пам’ятати, що товар, який ми шукаємо (Боб), повинен знаходитися в першому рядку нашого діапазону пошуку (A1: E3). Ми сказали функції, що хочемо повернути значення з 2nd рядок діапазону пошуку, який у цьому випадку - це рядок 2. Нарешті, ми вказали, що хочемо зробити an точна відповідність розмістивши False як останній аргумент. Тут відповідь буде «Читати».
Бічна порада: Ви також можете використовувати число 0 замість False як останній аргумент, оскільки вони мають однакове значення. Деякі люди вважають за краще це, оскільки швидше писати. Просто знайте, що обидва прийнятні.
Зміщені дані
Щоб додати деякі пояснення до нашого першого прикладу, елемент пошуку не обов’язково повинен бути у першому рядку вашої електронної таблиці, а лише у першому рядку вашого діапазону пошуку. Давайте використаємо той самий набір даних:
Тепер давайте знайдемо оцінку для класу природознавства. Наша формула буде такою
| 1 | = HLOOKUP ("Наука", A2: E3, 2, FALSE) |
Це ще дійсна формула, оскільки перший рядок нашого діапазону пошуку - це рядок 2, де буде знайдено наш пошуковий термін "Наука". Ми повертаємо значення з 2nd рядок діапазону пошуку, який у цьому випадку-це рядок 3. Відповідь тоді-"A-".
Використання підстановки
Функція HLOOKUP підтримує використання символів підстановки “*” та “?” при проведенні обшуків. Наприклад, припустимо, що ми забули, як пишеться ім’я Франка, і просто хотіли шукати ім’я, яке починається на „F”. Ми могли б записати формулу
| 1 | = HLOOKUP ("F*", A1: E3, 2, FALSE) |
Це дозволить знайти ім'я Франк у стовпці E, а потім повернути значення з 2nd відносний ряд. У цьому випадку відповідь буде «Наука».
Неточне збіг
Здебільшого ви хочете переконатися, що останній аргумент у HLOOKUP є хибним (або 0), щоб отримати точну відповідність. Однак є випадки, коли ви можете шукати неточне збіг. Якщо у вас є список відсортованих даних, ви також можете скористатися HLOOKUP, щоб повернути результат для того самого елемента або наступного найменшого. Це часто використовується при роботі зі збільшенням діапазонів цифр, наприклад у таблиці податків або преміях до комісійних.
Припустимо, що ви хочете знайти ставку податку для доходу, введеного у комірку H2. Формула в Н4 може бути такою:
| 1 | = HLOOKUP (H2, B1: F2, 2, TRUE) |
Відмінність цієї формули в тому, що наш останній аргумент - «Істина». У нашому конкретному прикладі ми можемо побачити, що коли наші особи вносять дохід у розмірі 45 000 доларів США, вони матимуть ставку податку 15%.
Примітка: Хоча ми зазвичай хочемо точного збігу з False як аргумент, але ви забуваєте вказати 4го аргументом у HLOOKUP, за замовчуванням - True. Це може призвести до отримання несподіваних результатів, особливо при роботі з текстовими значеннями.
Динамічний рядок
HLOOKUP вимагає від вас аргументу, в якому рядку ви хочете повернути значення, але може виникнути випадок, коли ви не знаєте, де буде цей рядок, або ви хочете дозволити своєму користувачеві змінити, з якого рядка повертатися. У цих випадках може бути корисним використовувати функцію MATCH для визначення номера рядка.
Давайте знову розглянемо наш приклад зошита з деякими введеннями в G2 та G4. Щоб отримати номер стовпця, ми могли б написати формулу
| 1 | = MATCH (G2, A1: A3, 0) |
Це спробує знайти точну позицію “Оцінка” в діапазоні A1: A3. Відповідь буде 3. Знаючи це, ми можемо підключити його до функції HLOOKUP і записати формулу в G6 так:
| 1 | = HLOOKUP (G4, A1: E3, MATCH (G2, A1: A3, 0), 0) |
Отже, функція MATCH буде оцінюватися до 3, і це повідомляє HLOOKUP повернути результат з 3rd рядок в діапазоні A1: E3. Загалом, тоді ми отримуємо бажаний результат "С". Наша формула зараз є динамічною, оскільки ми можемо змінити або рядок для перегляду, або ім’я для пошуку.
Обмеження HLOOKUP
Як згадувалося на початку статті, найбільшим падінням HLOOKUP є те, що він вимагає, щоб пошуковий термін знаходився в самому лівому стовпці діапазону пошуку. Хоча є деякі химерні хитрощі, які можна зробити, щоб подолати це, загальною альтернативою є використання INDEX та MATCH. Ця комбінація дає вам більше гнучкості, а іноді навіть може бути швидшим розрахунком.
HLOOKUP у Google Таблицях
Функція HLOOKUP працює точно так само в Google Таблицях, як і в Excel:
додаткові нотатки
Використовуйте функцію HLOOKUP для здійснення горизонтального пошуку. Якщо ви вже знайомі з функцією VLOOKUP, HLOOKUP працює точно так само, за винятком того, що пошук виконується горизонтально, а не вертикально. HLOOKUP шукає точну відповідність (range_lookup = FALSE) або найближчий збіг, який дорівнює або менше значення lookup_value (range_lookup = ІСТИНА, лише числові значення) у першому рядку табличного_масиву. Потім він повертає відповідне значення, n кількість рядків нижче відповідності.
Використовуючи HLOOKUP для пошуку точної відповідності, спочатку ви визначаєте ідентифікаційне значення, яке потрібно шукати як lookup_value. Цим ідентифікуючим значенням може бути SSN, ідентифікатор працівника, ім’я або інший унікальний ідентифікатор.
Далі ви визначаєте діапазон (який називається table_array), що містить ідентифікатори у верхньому рядку та будь -які значення, які ви зрештою хочете шукати у рядках під ним. ВАЖЛИВО: Унікальні ідентифікатори мають бути у верхньому рядку. Якщо це не так, ви повинні або перемістити рядок у верхню частину, або використовувати MATCH / INDEX замість HLOOKUP.
По -третє, визначте номер рядка (row_index) з table_array що ти хочеш повернутися. Майте на увазі, що перший рядок, що містить унікальні ідентифікатори, - це рядок 1. Другий рядок - це рядок 2 тощо.
Нарешті, ви повинні вказати, чи шукати точну відповідність (FALSE) або найближчу відповідність (TRUE) у файлі range_lookup. Якщо вибрано опцію точної відповідності, а точної відповідності не знайдено, повертається помилка (#Н/Д). Щоб формула поверталася порожньою або "не знайдена", або будь -яким іншим значенням замість значення помилки (#Н/Д), використовуйте функцію IFERROR з HLOOKUP.
Щоб скористатися функцією HLOOKUP, щоб повернути приблизний набір відповідностей: range_lookup = ІСТИНА. Ця опція доступна лише для числових значень. Значення потрібно сортувати в порядку зростання.
Приклади HLOOKUP у VBA
Ви також можете використовувати функцію HLOOKUP у VBA. Тип:application.worksheetfunction.hlookup (lookup_value, table_array, row_index_num, range_lookup)
Виконання наступних операторів VBA
| 123456 | Діапазон ("G2") = Application.WorksheetFunction.HLookup (Діапазон ("C1"), Діапазон ("A1: E3"), 1)Діапазон ("H2") = Application.WorksheetFunction.HLookup (Діапазон ("C1"), Діапазон ("A1: E3"), 2)Діапазон ("I2") = Application.WorksheetFunction.HLookup (Діапазон ("C1"), Діапазон ("A1: E3"), 3)Діапазон ("G3") = Application.WorksheetFunction.HLookup (Діапазон ("D1"), Діапазон ("A1: E3"), 1)Діапазон ("H3") = Application.WorksheetFunction.HLookup (Діапазон ("D1"), Діапазон ("A1: E3"), 2)Діапазон ("I3") = Application.WorksheetFunction.HLookup (Діапазон ("D1"), Діапазон ("A1: E3"), 3) |
дасть такі результати
Для аргументів функції (lookup_value тощо) ви можете або ввести їх безпосередньо у функцію, або визначити змінні для використання замість них.
Повернення до списку всіх функцій Excel
