Связываем таблицы по общему ключу
В реальной работе данные почти никогда не живут в одной таблице. Есть рабочая таблица — журнал продаж, список операций, реестр заявок. И есть справочники — список клиентов, прайс-лист, таблица ставок, каталог регионов. Между ними есть связь: в рабочей таблице есть идентификатор (код, ИНН, артикул, номер договора), а в справочнике по этому же идентификатору хранится нужная информация.
Функции переноса данных автоматически находят строку в справочнике по идентификатору и вытаскивают из неё нужное поле — без ручного копирования и без ВПР-вручную по каждой строке.
Как это выглядит на практике
| Рабочая таблица | Идентификатор | Справочник | Нужно получить |
|---|---|---|---|
| Журнал продаж | Код клиента | База клиентов | Имя, регион, категория |
| Реестр заявок | Артикул товара | Прайс-лист | Цена, единица измерения |
| Расчёт налогов | Код операции | Таблица ставок | Ставка НДС или налога |
| Табель сотрудников | Табельный номер | Справочник персонала | ФИО, должность, отдел |
Ключевой принцип
Чтобы функция переноса сработала, нужны три вещи:
- Ключ (идентификатор) — уникальное значение, которое есть и в рабочей таблице, и в справочнике.
- Таблица-источник (справочник) — откуда берём данные.
- Указание, что именно вернуть — из какого столбца или строки справочника нужно взять значение.
Excel находит строку в справочнике, где ключ совпадает, и возвращает нужное поле. Это и есть суть работы ВПР, ГПР, и связки ПОИСКПОЗ + ИНДЕКС.
Три функции этого курса
- ВПР (VLOOKUP) — ищет ключ в первом столбце диапазона и возвращает значение из указанного столбца той же строки. Самая популярная.
- ГПР (HLOOKUP) — горизонтальный аналог ВПР: ищет ключ в первой строке, возвращает значение из указанной строки того же столбца.
- ПОИСКПОЗ + ИНДЕКС — гибкая связка без ограничений ВПР/ГПР: поиск в любом столбце, возврат данных в любом направлении.