Приклади функцій ГПР у Microsoft Excel

Функція ГПР призначена для горизонтальних таблиць, про що свідчить її назва. Якщо ви працюєте зі стандартними вертикальними таблицями і не хочете перетворювати їх у горизонтальні, найзручніше скористатися аналогічною функцією ВПР, яка виконує вертикальний перегляд. Про неї вже написана стаття на нашому сайті.

Докладніше: Функція ВПР у програмі Microsoft Excel

Транспонування вертикальної таблиці

Якщо у вас вже є горизонтальна таблиця, відразу переходьте до одного з двох розділів з прикладами, а ми покажемо, як транспонувати вертикальну таблицю для тих, хто має потребу в її перетворенні. Стандартна функціональність Excel дозволяє реалізувати таке конвертування в кілька кліків.

1. Виділіть всю таблицю, утримуючи ліву кнопку миші, а потім клацніть правою кнопкою.
2. У контекстному меню натисніть кнопку "Копіювати".
3. Виберіть порожню клітинку для вставки таблиці або створіть окремий лист для спрощення цього завдання. Знову правим кліком викличте контекстне меню і натисніть "Спеціальна вставка".
4. З'явиться вікно налаштування, в якому серед усіх параметрів потрібно буде відзначити тільки "транспонувати", після чого натисніть "ОК".
5. Таблиця зберігає своє форматування та значення в клітинках, але тепер відображається в горизонтальному представленні, а це означає, що можна переходити до використання функції ГПР.

Приклад 1: Одне значення

Ознайомимося з найпростішим прикладом побудови функції ГПР, щоб у тих користувачів, які жодного разу не стикалися зі створенням подібних формул, не виникло труднощів при вказанні кожного аргументу.

1. Під основною горизонтальною таблицею у нас є початок іншої, де потрібно буде розрахувати, скільки одиниць товару було продано в зазначеному місяці.Звичайно, можна знайти значення самому в основній таблиці, однак це витратно за часом, якщо йдеться одразу про кілька десятків або сотень значень. Тоді створимо функцію ГПР, яка знайде значення автоматично.
2. Оголосіть її в необхідному блоці, поставте відкриваючу круглу дужку і переходьте далі.
3. В якості шуканого значення вказується клітинка, по якій слід орієнтуватися. У нашому випадку це "Квітень".
4. Після кожного оголошеного аргументу не забувайте ставити знак ";", який закриває його.
5. Далі виділіть всю таблицю, в якій здійснюється пошук значення.
6. Введіть номер рядка для пошуку. Якщо ви звернете увагу на наступний скріншот, то помітите, що він відповідає зазначеному раніше, а в якості номера виступає просто цифра.
7. Додайте в кінці "0", щоб позначити точне співпадіння, поставте закриваючу круглу дужку і підтвердіть створення функції натисканням клавіші Enter.
8. Порівняємо отримані дані з оригіналом, щоб переконатися в правильному складанні функції.
9. Якщо далі у вас є інші клітинки, значення рядків яких необхідно повертати, не створюйте функцію з нуля або не копіюйте її з подальшим редагуванням.
10. Виділіть вже готову клітинку і розтягніть її на необхідну відстань.
11. Підстановка значень пройшла успішно.

Використовуйте наведений вище приклад як основу, замінюючи шукане значення і таблицю для пошуку. Якщо потрібно, відкривайте вікно "Аргументи функції", щоб не заплутатися при введенні аргументів.

Приклад 2: Кілька значень

Якщо для таблиці, де використовується функція ГПР, необхідно розрахувати значення одразу в кількох рядах, для цього не обов'язково самостійно створювати функцію заново, вказувати діапазон і інші аргументи.Покажемо, як значно спростити це завдання.

1. Ми додали рядки до нашої таблиці та створили нові значення для місяців у нижній частині.
2. Використовуємо ту ж формулу, створену в першому прикладі.
3. Протягніть її вниз, щоб побачити, як відбудеться автоматичне заміщення для нових рядків.
4. Видно, що протягування пройшло успішно і значення підставилися коректно.
5. Одразу виділіть усі клітинки для протягування, щоб не робити це з кожним стовпцем.
6. Підрахунок успішно завершено, і всі дані відображаються правильно. Перевірити це можна, натиснувши на клітинку і подивившись на вказані для неї аргументи у функції ГПР.