30 Функцій excel за 30 днів ГПР (hlookup)
10-ий день марафону 30 функцій Excel за 30 днів ми присвятимо вивченню функції HLOOKUP (ГПР). Ця функція дуже схожа на VLOOKUP (ВВР). тільки вона працює з елементами горизонтального списку.
Нещасна функція HLOOKUP (ГПР) не так популярна, як її сестра, оскільки в більшості випадків дані в таблицях розташовані вертикально. Згадайте, коли в останній раз Ви хотіли виконати пошук по рядку? А повернути значення з того ж стовпця, але розташоване в одному з рядків нижче?
Функція 10: HLOOKUP (ГПР)
Функція HLOOKUP (ГПР) шукає значення в першому рядку таблиці і повертає інше значення з того ж стовпця таблиці.
Як можна використовувати функцію HLOOKUP (ГПР)?
Оскільки функція HLOOKUP (ГПР) може знайти точне або наближене значення в рядку, то вона зможе:
- Знайти підсумки продажів за обраним регіону.
- Знайти показник, актуальний для обраної дати.
Синтаксис HLOOKUP (ГПР)
Функція HLOOKUP (ГПР) має наступний синтаксис:
Пастки HLOOKUP (ГПР)
Як і VLOOKUP (ВВР), функція HLOOKUP (ГПР) може працювати повільно, особливо коли виконує пошук точного збігу текстового рядка в несортованої таблиці. У міру можливості, використовуйте приблизний пошук в таблиці, відсортованої по першому рядку по зростанню. Ви можете спочатку застосувати функцію MATCH (ПОИСКПОЗ) або COUNTIF (СЧЁТЕСЛІ), щоб переконатися, що шукане значення взагалі існує в першому рядку.
Інші функції, такі як INDEX (ЗМІСТ) і MATCH (ПОИСКПОЗ), можуть бути також використані для вилучення значень з таблиці, і вони більш ефективні. Ми розглянемо їх пізніше в рамках нашого марафону і побачимо, наскільки потужні й гнучкі вони можуть бути.
Приклад 1: Знайти значення продажів для обраного регіону
Ще раз нагадаю, що функція HLOOKUP (ГПР) шукає значення тільки в верхньому рядку таблиці. У цьому прикладі ми знайдемо підсумки продажів для обраного регіону. Нам важливо отримати правильне значення, тому використовуємо такі настройки:
- Ім'я регіону введено в осередку B7.
- Таблиця пошуку по регіону має два рядки і посідає діапазон C2: F3.
- Підсумки продажів знаходяться в рядку 2 нашої таблиці.
- Останній аргумент має значення FALSE (ЛОЖЬ), щоб знайти точний збіг при пошуку.
Формула в комірці C7 така:
Якщо назва регіону в першому рядку таблиці, не знайдено, результатом функції HLOOKUP (ГПР) буде # N / A (# Н / Д).
Приклад 2: Знайти показник для обраної дати
Зазвичай при використанні функції HLOOKUP (ГПР) потрібно точний збіг, але іноді приблизне збіг підходить більше. Наприклад, якщо показники змінюються на початку кожного кварталу, а в якості заголовків стовпців використовуються перші дні цих кварталів (див. Малюнок нижче). У такому випадку, за допомогою функції HLOOKUP (ГПР) і приблизного відповідності, Ви знайдете показник, який актуальний для заданої дати. У цьому прикладі:
- Дата записана в осередку C5.
- Таблиця пошуку показника має два рядки і розташована в діапазоні C2: F3.
- Таблиця пошуку відсортована по рядку з датами за зростанням.
- Показники записані в рядку 2 нашої таблиці.
- Останній аргумент функції має значення TRUE (ІСТИНА), щоб шукати наближене збіг.
Формула в комірці D5 така:
