В этом модуле вы узнаете, как автоматически добавлять в таблицу столбцы из других таблиц, искать столбцы по заданным параметрам и диапазонам.
Чтобы повторять все действия за преподавателем, скопируйте себе нашу учебную табличку.
Открыть учебную Google-таблицу.
Excel позволяет брать таблицу и присоединять к ней столбцы из другой таблицы по определенным правилам. Для этого используют две функции: ВПР и ИНДЕКС (поиск позиции).
Объединяем таблицы с помощью ВПР (VLOOKUP)
ВПР — это вертикальный поиск, аналог ГПР, горизонтального поиска. Работают они по одним и тем же правилам.
Например, у вас две таблицы — в одной записан Product ID, а в другой — Product ID и название продукта. И вы захотели узнать, что у вас за продукты, сколько продуктов было продано, что у них за названия. Для этого нужно использовать VLOOKUP (ВПР). На вход команде подается три параметра:
- Запрос — куда мы хотим перейти (например, первый ключ из левой таблички в нашем примере).
- Диапазон, в котором мы будем искать это значение.
- Если ключ найдется, вы должны указать третьим аргументом номер столбца, из которого надо взять значение.
У нас и в левой, и в правой таблице есть Product ID — это определенное число. И если оно найдется, мы получим значение из столбца, а если не найдется, то нет. Соответственно, наша задача – предварительно почистить данные так, чтобы ключи были одинаковыми.
Если этого не сделать, могут возникнуть проблемы. Например, если в нашей табличке есть дубликаты, оператор возьмет первый из них, а остальные значения проигнорирует. Поиск идет слева направо.
Функция ИНДЕКС и ПОИСКПОЗ
Эти функции позволяют искать примеры. Например, если у нас есть таблица, в которой ключ находится не на первом месте — то есть условие, по которому вы планируете объединить две таблицы, находится левее ключа.
Функция ИНДЕКС работает очень просто. По сути, мы задаем ей массив и табличку, а потом говорим: «Верни мне, пожалуйста, значения на пересечении столбца и строки».
Функция ПОИСКПОЗ помогает найти позицию: у нас есть исходное значение, и мы хотим найти его позицию в столбике. Например, нам надо найти порядковый номер Екатеринбурга в столбике. В функции три аргумента:
- Значение, которое мы ищем.
- Массив, который мы ищем.
- Аргумент, который отвечает за тип поиска — то есть должно ли это быть точное или примерное совпадение.
Функции ИНДЕКС и ПОИСКПОЗ можно совмещать последовательно. В примере с этого урока мы используем их вместе, чтобы получить некоторые значения из правой таблички, которые понадобятся в левой. Логика простая: сначала мы находим порядковый номер ключа в правой табличке при помощи ПОИСКПОЗ. Потом используем ИНДЕКС для поиска нужных данных.
