ВПР-им с разных листов
Если вам нужно "подтягивать" данные с помощью ВПР / VLOOKUP с разных листов (например, на каждый город/месяц/склад у вас отдельный лист с данными), можно собрать ссылку с помощью INDIRECT / ДВССЫЛ.
Обычная ссылка на другой лист выглядит так:
='Москва'!A:B
Нам нужно подставлять внутри апострофов названия разных листов.
Сначала берем апостроф (в кавычках), потом к нему добавляем название листа, справа еще один апостроф, восклицательный знак и диапазон:
="'" & ячейка с названием листа & "'!диапазон"
Чтобы превратить полученную текстовую строку в ссылку, используем функцию INDIRECT — она ровно для этого и используется.
=INDIRECT ("'" & ячейка с названием листа & "'!диапазон")
И отправляем это внутрь ВПР'а как второй аргумент:
=VLOOKUP (значение для поиска; INDIRECT ("'" & ячейка с названием листа & "'!диапазон"
); номер столбца; 0)
Ссылка на таблицу с примером
Данные с разных листов, на которых разная структура
Что делать в более тяжелом случае, когда данные на разных листах и еще разбросаны как попало (все в разных столбцах; нужный столбец то левее, то правее столбца с ключом для поиска)?
Что делать в более тяжелом случае, когда данные на разных листах и еще разбросаны как попало (все в разных столбцах; нужный столбец то левее, то правее столбца с ключом для поиска)?
Тут сложнее. Можно решить эту задачу так: через ИНДЕКС+ПОИСКПОЗ (INDEX + MATCH) вместо ВПР, чтобы все работало при любом порядке столбцов.
Через СМЕЩ / OFFSET будем формировать ссылку на столбец для поиска и на столбец с нужными данными. Находить их будем по заголовкам (заголовок будет находиться через ПОИСКПОЗ / MATCH, и это будет использоваться в функции СМЕЩ / OFFSET для смещения по столбцам, чтобы попасть на нужный).
Вот ингредиенты нашего коктейля:
INDIRECT ("'"&название листа&"'!диапазон") — ссылка на ячейку или диапазон на нужном листе, с которого тянем данные
MATCH (заголовок;INDIRECT ("'"&название листа&"'!диапазон");0) — поиск нужного нам заголовка (столбца, из которого нужно тянуть данные)
ROWS (INDIRECT ("'"&название листа&"'!A:A") — число строк на листе, с которого нужно тащить данные.
OFFSET (INDIRECT ("'"&название листа&"'!A1"); 0;MATCH (ищем заголовок, как выше) — 1; число строк, как выше;1) — ссылка на диапазон на нужном листе шириной 1 столбец со всеми строками, с отступом от A1 до нужного нам заголовка.
А логика формулы в общем виде такая:
=ИНДЕКС (СМЕЩ (который дает ссылку на диапазон на нужном листе в нужном столбце, с которого нужно тащить данные;
ПОИСКПОЗ (ключ для поиска;СМЕЩ (который дает ссылку на диапазон на нужном листе в столбце, в котором находятся ключи для поиска, например, названия товаров);0))
Через СМЕЩ / OFFSET будем формировать ссылку на столбец для поиска и на столбец с нужными данными. Находить их будем по заголовкам (заголовок будет находиться через ПОИСКПОЗ / MATCH, и это будет использоваться в функции СМЕЩ / OFFSET для смещения по столбцам, чтобы попасть на нужный).
Вот ингредиенты нашего коктейля:
INDIRECT ("'"&название листа&"'!диапазон") — ссылка на ячейку или диапазон на нужном листе, с которого тянем данные
MATCH (заголовок;INDIRECT ("'"&название листа&"'!диапазон");0) — поиск нужного нам заголовка (столбца, из которого нужно тянуть данные)
ROWS (INDIRECT ("'"&название листа&"'!A:A") — число строк на листе, с которого нужно тащить данные.
OFFSET (INDIRECT ("'"&название листа&"'!A1"); 0;MATCH (ищем заголовок, как выше) — 1; число строк, как выше;1) — ссылка на диапазон на нужном листе шириной 1 столбец со всеми строками, с отступом от A1 до нужного нам заголовка.
А логика формулы в общем виде такая:
=ИНДЕКС (СМЕЩ (который дает ссылку на диапазон на нужном листе в нужном столбце, с которого нужно тащить данные;
ПОИСКПОЗ (ключ для поиска;СМЕЩ (который дает ссылку на диапазон на нужном листе в столбце, в котором находятся ключи для поиска, например, названия товаров);0))
