зміст
10 день марафону 30 функцій Excel за 30 днів ми присвятимо вивченню функції ПОСЛУГИ (GPR). Ця функція дуже схожа на ВЛООКУП (VLOOKUP), працює тільки з елементами горизонтального списку.
Невдала функція ПОСЛУГИ (GLOW) не такий популярний, як його сестра, оскільки в більшості випадків дані в таблицях розташовані вертикально. Пам’ятаєте, коли востаннє ви хотіли шукати рядок? Як щодо повернення значення з того самого стовпця, але розташованого в одному з рядків нижче?
У будь-якому випадку давайте наведемо особливості ПОСЛУГИ (GPR) заслужену хвилину слави та подивіться уважніше на інформацію про цю функцію, а також приклади її використання. Пам’ятайте, якщо у вас є цікаві ідеї чи приклади, поділіться ними в коментарях.
Функція 10: HLOOKUP
функція ПОСЛУГИ (HLOOKUP) шукає значення в першому рядку таблиці та повертає інше значення з того самого стовпця таблиці.
Як я можу використовувати функцію HLOOKUP (HLOOKUP)?
Оскільки функція ПОСЛУГИ (HLOOKUP) може знайти точне або приблизне значення в рядку, а потім:
- Знайти загальні продажі для вибраного регіону.
- Знайдіть показник, актуальний для обраної дати.
Синтаксис HLOOKUP
функція ПОСЛУГИ (HLOOKUP) має такий синтаксис:
HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
ГПР(искомое_значение;таблица;номер_строки;интервальный_просмотр)
- lookup_value (lookup_value): значення, яке потрібно знайти. Може бути значенням або посиланням на клітинку.
- table_array (таблиця): таблиця пошуку. Може бути посиланням на діапазон або іменованим діапазоном, що містить 2 або більше рядків.
- row_index_num (номер_рядка): Рядок, що містить значення, яке повертає функція. Встановлюється за номером рядка в таблиці.
- пошук_діапазону (range_lookup): використовуйте FALSE або 0, щоб знайти точний збіг; для приблизного пошуку TRUE (ІСТИНА) або 1. В останньому випадку рядок, в якому виконується пошук функції, повинен бути відсортований у порядку зростання.
Перехоплення HLOOKUP (GPR)
Люблю ВЛООКУП (VLOOKUP), функція ПОСЛУГИ (HLOOKUP) може працювати повільно, особливо під час пошуку точного збігу текстового рядка в несортованій таблиці. По можливості використовуйте приблизний пошук у таблиці, відсортованій за першим рядком у порядку зростання. Спочатку можна застосувати функцію MATCH (БІЛЬШ ВІДКРИТИЙ) або COUNTIF (COUNTIF), щоб переконатися, що значення, яке ви шукаєте, навіть існує в першому рядку.
Інші функції, як ІНДЕКС (ІНДЕКС) і MATCH (MATCH) також можна використовувати для отримання значень із таблиці та є більш ефективними. Ми розглянемо їх пізніше під час нашого марафону та побачимо, наскільки потужними та гнучкими вони можуть бути.
Приклад 1: Знайдіть значення продажів для вибраного регіону
Ще раз нагадаю, що функція ПОСЛУГИ (HLOOKUP) шукає лише значення у верхньому рядку таблиці. У цьому прикладі ми знайдемо загальні продажі для вибраного регіону. Для нас важливо отримати правильне значення, тому ми використовуємо такі налаштування:
- Назва регіону вводиться в клітинку B7.
- Таблиця регіонального пошуку складається з двох рядків і охоплює діапазон C2:F3.
- Підсумки продажів наведено у рядку 2 нашої таблиці.
- Для останнього аргументу встановлено значення FALSE, щоб знайти точний збіг під час пошуку.
Формула в клітинці C7 така:
=HLOOKUP(B7,C2:F3,2,FALSE)
=ГПР(B7;C2:F3;2;ЛОЖЬ)
Якщо назва регіону не знайдена в першому рядку таблиці, результат функції ПОСЛУГИ (GPR) буде #AT (#Н/Д).
Приклад 2: Знайдіть міру для вибраної дати
Зазвичай при використанні функції ПОСЛУГИ (HLOOKUP) вимагає точного збігу, але іноді приблизний збіг є більш прийнятним. Наприклад, якщо показники змінюються на початку кожного кварталу, а перші дні цих кварталів використовуються як заголовки стовпців (див. малюнок нижче). У цьому випадку за допомогою функції ПОСЛУГИ (HLOOKUP) і приблизний збіг, ви знайдете індикатор, актуальний для певної дати. У цьому прикладі:
- Дата записується в клітинку C5.
- Таблиця пошуку індикаторів має два рядки та розташована в діапазоні C2:F3.
- Пошукова таблиця сортується за рядком дати в порядку зростання.
- Показники записуємо в рядок 2 нашої таблиці.
- Останній аргумент функції має значення TRUE для пошуку приблизної відповідності.
Формула в клітинці D5 така:
=HLOOKUP(C5,C2:F3,2,TRUE)
=ГПР(C5;C2:F3;2;ИСТИНА)
Якщо дата не знайдена в першому рядку таблиці, функція ПОСЛУГИ (HLOOKUP) знайде найближче найбільше значення, яке менше аргументу lookup_value (пошукове_значення). У цьому прикладі шуканим значенням є березня 15. Його немає в рядку дати, тому формула прийме значення Січень 1 і повернутися 0,25.
