На практике постоянно приходится объединять 2 и более таблиц в одну. Делаешь это в старом добром Excel, в базах SQL и любимом питончике в рамках пандас-датафреймов. Поэтому решил систематизироваться и описать эти объединения в рамках соответствующих статей. Если у вас есть пожелания на раскрытие подобных тем, пишите их в комментариях, в новых статьях постараюсь описать свое видение ваших кейсов.
- Поиск по точному совпадению ВПР, ПРОСМОТРХ.
- Поиск приблизительного значения в интервалах.
1. Поиск по точному совпадению ВПР, ПРОСМОТРХ
Используется, когда есть исходная таблица Эксель с некоторыми значениями, но ее нужно обогатить данными из справочника. Например, есть объемы товара, но пока мы никак не сможем понять, а какой это тип товара (овощи или фрукты).
Изначально в такой задаче нужен Справочник, в котором будут уникальные Наименования и к ним проставлен тип товара:
Задача теперь сводится к тому, чтобы:
- взять Наименование в основной рабочей таблице;
- сходить в справочник, найти там это наименование, взять его Тип;
- проставить значение типа основной таблице в столбце Овощь/фрукт.
Для этого могут быть использованы 2 функции:
Для ячейки С2 формула с ВПР будет такой: =ВПР(A2;Справочник!$A$1:$B$21;2;0), где:
- А2 - Искомое значение, в нем у нас Картофель;
- Справочник!$A$1:$B$21 - Таблица, массив справочника. В нашем случае он на отдельном листе Справочник. Важный момент, мы добавили $ для ссылок на ячейки. Чтобы они стали Абсолютными. Иначе, при копировании функции вниз, у нас будут "съезжать" и ссылки на справочник, для нижних ячеек он уже станет неполным и/или пустым.
- 2 - номер столбца в справочнике Из которого брать значения. Первым столбцом в выделенном массиве должен быть столбец с тем значением, какое ищем (наименование фрукта), а вот брать Тип, значения для занесения в основную таблицу мы можем уже из 2го, 3го... любого последующего столбца.
- 0 - интервальный просмотр. Ставим 0, когда ищем по такому точному совпадению.
Со второй новой функцией формула будет уже такой: =ПРОСМОТРX(A2;Справочник!$A$2:$A$21;Справочник!$B$2:$B$21;"Данных нет в справочнике";0), где:
- A2 - искомое значение, как и в ВПР;
- Справочник!$A$2:$A$21 - Просматриваемый массив, где ищем наше наименование, т.е. в первый столбец в Справочнике;
- Справочник!$B$2:$B$21 - это уже новый параметр Возвращаемый массив, указывается отдельно откуда надо брать данные, которые отобразим в основной таблице. В нашем случае там лежит тип Наименования;
- "Данных нет в справочнике" - текст, который показать, если не нашли ключ в справочнике;
- 0 - Режим сопоставления для точного совпадения.
2. Поиск значения в интервалах
Такая задача может возникнуть, когда у нас в справочнике может не быть точного совпадения с ключом. Например, есть курсы валюты на определенную дату. Но, не факт, что запрашиваемая нами дата точно совпадет с той, что есть в справочнике. Поэтому, мы в нашу целевую ячейку вполне можем подставить значение курса, который был на предыдущую известную дату. Для примера нашел интересный файл на сайте ЦБ с данными курса рубля к доллару в XX веке.
Тут формула имеет вид: =ВПР(F3;A:C;3;1)
- F3 - ячейка, откуда берем дату, на какую нужен курс;
- A:C - столбцы с таблицей курса, указали их полностью, без указания ячеек;
- 3 - номер столбца в справочнике (в первом - дата, втором - Единиц; 3 - Курс.
- 1 - интервальный просмотр, неточный уже.
В последующих статьях опишу возможные варианты объединения для пандаса и SQL.