Google Таблицы

Данные с разных листов, на которых разная структура

Что делать в более тяжелом случае, когда данные на разных листах и еще разбросаны как попало (все в разных столбцах; нужный столбец то левее, то правее столбца с ключом для поиска)?

Тут сложнее. Можно решить эту задачу так: через ИНДЕКС+ПОИСКПОЗ (INDEX + MATCH) вместо ВПР (про эту комбинацию мы уже писали), чтобы все работало при любом порядке столбцов.

Через СМЕЩ / OFFSET будем формировать ссылку на столбец для поиска и на столбец с нужными данными. Находить их будем по заголовкам (заголовок будет находиться через ПОИСКПОЗ / MATCH, и это будет использоваться в функции СМЕЩ / OFFSET для смещения по столбцам, чтобы попасть на нужный).

Вот ингредиенты нашего коктейля:
INDIRECT("'"&название листа&"'!диапазон") - ссылка на ячейку или диапазон на нужном листе, с которого тянем данные

MATCH(заголовок;INDIRECT("'"&название листа&"'!диапазон");0) - поиск нужного нам заголовка (столбца, из которого нужно тянуть данные)

ROWS(INDIRECT("'"&название листа&"'!A:A") - число строк на листе, с которого нужно тащить данные)

OFFSET(INDIRECT("'"&название листа&"'!A1"); 0;MATCH (ищем заголовок, как выше) - 1; число строк, как выше;1) - ссылка на диапазон на нужном листе шириной 1 столбец со всеми строками, с отступом от A1 до нужного нам заголовка.

А логика формулы в общем виде такая:

=ИНДЕКС(СМЕЩ(который дает ссылку на диапазон на нужном листе в нужном столбце, с которого нужно тащить данные;
ПОИСКПОЗ(ключ для поиска;СМЕЩ(который дает ссылку на диапазон на нужном листе в столбце, в котором находятся ключи для поиска, например, названия товаров);0))

Таблица с примером

Telegram

Google Таблицы

ВПР-им с разных листов

Если вам нужно "подтягивать" данные с помощью ВПР / VLOOKUP с разных листов (например, на каждый город/месяц/склад у вас отдельный лист с данными), можно собрать ссылку с помощью INDIRECT / ДВССЫЛ.

Обычная ссылка на другой лист выглядит…

www.tgoop.com/google_sheets/1308

12.9K viewsDec 9, 2023 at 08:01

Данные с разных листов, на которых разная структура

Что делать в более тяжелом случае, когда данные на разных листах и еще разбросаны как попало (все в разных столбцах; нужный столбец то левее, то правее столбца с ключом для поиска)?

Тут сложнее. Можно решить эту задачу так: через ИНДЕКС+ПОИСКПОЗ (INDEX + MATCH) вместо ВПР (про эту комбинацию мы уже писали), чтобы все работало при любом порядке столбцов.

Через СМЕЩ / OFFSET будем формировать ссылку на столбец для поиска и на столбец с нужными данными. Находить их будем по заголовкам (заголовок будет находиться через ПОИСКПОЗ / MATCH, и это будет использоваться в функции СМЕЩ / OFFSET для смещения по столбцам, чтобы попасть на нужный).

Вот ингредиенты нашего коктейля:
INDIRECT("'"&название листа&"'!диапазон") - ссылка на ячейку или диапазон на нужном листе, с которого тянем данные

MATCH(заголовок;INDIRECT("'"&название листа&"'!диапазон");0) - поиск нужного нам заголовка (столбца, из которого нужно тянуть данные)

ROWS(INDIRECT("'"&название листа&"'!A:A") - число строк на листе, с которого нужно тащить данные)

OFFSET(INDIRECT("'"&название листа&"'!A1"); 0;MATCH (ищем заголовок, как выше) - 1; число строк, как выше;1) - ссылка на диапазон на нужном листе шириной 1 столбец со всеми строками, с отступом от A1 до нужного нам заголовка.

А логика формулы в общем виде такая:

=ИНДЕКС(СМЕЩ(который дает ссылку на диапазон на нужном листе в нужном столбце, с которого нужно тащить данные;
ПОИСКПОЗ(ключ для поиска;СМЕЩ(который дает ссылку на диапазон на нужном листе в столбце, в котором находятся ключи для поиска, например, названия товаров);0))

Таблица с примером

BY Google Таблицы