Якщо ви знайомі з функцією VLOOKUP (ВПР) або її горизонтальним аналогом HLOOKUP (ГПР), то повинні пам'ятати, що ці чудові функції шукають інформацію тільки по одному параметру, тобто в одновимірному масиві - по рядку або по стовпцю. А якщо нам необхідно вибирати дані з двовимірної таблиці за збігом відразу двох параметрів - і по рядку і по стовпцю одночасно? Давайте розглянемо кілька варіантів об'єднання таблиць.
Припустимо, що нам потрібно поєднати дві таблиці, які відображають кредитний портфель та портфель забезпечення:
Ми можемо почати використовувати функцію VLOOKUP, щоб поєднати кожний окремий стовпець, проте якщо у нас таблиці мають надзвичайно багато рядків та стовпців, то це занняття може перетворитися в справжню муку. Проте з цієї ситуації є досить простий вихід, оскільки Excel має такі дві чудові функції, як INDEX (ИНДЕКС) та MATCH (ПОИСКПОЗ) з категорії Посилання та масиви (Lookup and Reference), що в парі працюють як 2D VLOOKUP.
Гаразд, що нам необхідно зробити, щоб швидко поєднати дві таблиці? Давайте для початку скопіюємо шапку другої таблиці (ту, що ми будемо приєднувати) та вставимо її поруч з шапкою першої таблиці. По назві шапки функція MATCH (ПОИСКПОЗ) буде видавати нам порядковий номер колонки, а по номеру угоди - необхідний нам порядковий номер рядка.
Фактично, ми хочемо знайти значення комірки з перетину певного рядка та стовпця в таблиці. Для наочності, розіб'ємо завдання на три етапи.
Отже, поєднуючи все вище перелічене в одну формулу, отримуємо для комірки F14 наступну формулу:
=INDEX(J2:M18;MATCH(A14;K2:K18;0);MATCH(F1;J1:M1;0))
або =ИНДЕКС(J2:M18;ПОИСКПОЗ(A14;K2:K18;0);ПОИСКПОЗ(F1;J1:M1;0))
Таким чином, розтягуючи нашу формулу на весь діапазон, ми правильно приєднаємо додаткові поля. Також потрібно звернути увагу, що при розтягуванні формули, нам потрібно зафіксувати діапазони знаком долара ($) (для фіксування можна використати клавішу F4), для пошуку по шапці фіксуємо тільки рядок (F$1), для пошуку по номерам угод - тільки колонку ($A14).
Злегка модифікуємо попередній приклад, та припустимо, що у нас є аналогічні таблиці, проте їх заголовки є об'єднаними, тому функція MATCH нам не допоможе визначити правильно порядковий номер колонки.
В даному випадку, ми можемо створити технічне поле над таблицею, та проставити номера колонок вручну. Тоді, ми зможемо скористатися вже звичною нам функцією VLOOKUP.
Отже, формула для нашого другого варіанту буде наступною:
=VLOOKUP($A3;$J$3:$M$19;E$1;0)
=ВПР($A3;$J$3:$M$19;E$1;0)
Знову ж, не забуваємо фіксувати діапазон та відповідні пошукові поля знаком $, для того щоб формула корректно працювала та не видавала помилку при зміщенні діапазонів.
Поєднання таблиць (VLOOKUP, ВПР)