Импортируем только выбранные (в выпадающих списках) столбцы с данными из Google Таблицы

Задача: импортировать из одной таблицы в другую не все столбцы, а только выбранные заголовки в любом порядке. Заголовки выбираются в выпадающих списках, а список формируется на основе полного списка всех заголовков в исходнике. Все это обновляется автоматом (и данные при выборе разных столбцов, и выпадающие списки).

Просто загрузить данные — дело нехитрое, нужна функция IMPORTRANGE. Но мы хотим выбирать, какие столбцы загружать. Для этого сгодится, например, QUERY — в запросе этой функции мы можем перечислить конкретные столбцы.

Разберем всю задачу по порядку.

Создаем выпадающие списки

Начнем с простого. Сделаем отдельный лист, куда подтянем все заголовки из исходника. Просто импортируем первую строку. Предварительно транспонируем (поворачиваем на 90 градусов), чтобы сделать в виде вертикального списка — тут нам оформлять это как заголовки ни к чему. Ссылаемся на всю строку, так что новые заголовки сюда попадут автоматом.

=ТРАНСП(IMPORTRANGE(ссылка на файл ;"'Название листа'!1:1"))

Получили заголовки из исходника в виде списка

Дальше на листе, куда будем загружать, выделяем всю первую строку (можно предварительно добавить столбцов, смотря сколько примерно вы хотите импортировать) и создаем в ней проверку данных на основе этого справочника с заголовками:

Формируем запрос для QUERY — получаем номера столбцов

Напомню, если в QUERY в качестве данных используется IMPORTRANGE или массив из нескольких диапазонов, то столбцы в запросе указываются в виде ColN (а не A, B, C etc.):

=QUERY(IMPORTRANGE(ссылка на файл; ссылка на диапазон);
 "SELECT Col1, Col3, Col7")

Такой формулой вытянем 1, 3 и 7-й столбцы из импортируемого файла с того листа и диапазона, которые укажем во втором аргументе IMPORTRANGE.

Но нам нужно выбирать столбцы. Пойдем следующим образом: выясним номера нужных нам столбцов в исходном документе, а потом соберем из этого текстовую конструкцию Select ColX, ColY, ..., ColZ

Чтобы получить номера столбцов, нужна функция ПОИСКПОЗ / MATCH.

=ArrayFormula(
ПОИСКПОЗ($1:$1;IMPORTRANGE(ссылка на файл;'Название листа'!1:1");0))

Такая конструкция будет тянуть номера всех столбцов из первой строки (то есть выбранных в выпадающем списке). Но так как мы можем использовать не все выпадающие списки, то для пустых заголовков она будет выдавать ошибку #Н/Д:

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

=ArrayFormula(
ПОИСКПОЗ(СМЕЩ($A1;0;0;1;СЧЁТЗ($1:$1));
IMPORTRANGE(ссылка на файл;"'Название листа'!1:1");0))

Здесь с помощью СЧЁТЗ / COUNTA считаем, сколько заголовков выбрано (заполнено) в первой строке и создаем с помощью СМЕЩ массив соответствующий ширины, стартующий от A1, первого заголовка.

Теперь для пустых столбцов ничего не выводим, ошибок нет.

Есть и другой вариант — заменять ошибки #Н/Д на пустую текстовую строку с помощью функции IFNA:

=IFNA(ПОИСКПОЗ($1:$1;
IMPORTRANGE(ссылка на файл;"'Название листа'!1:1");0);"")

Формируем текстовую строку

Итак, у нас есть формула, выдающая номера нужных нам столбцов, осталось сделать из них запрос для QUERY.

Возьмем начало запроса "Select Col" и к нему присоединим функцию JOIN, которая будет объединять все номера, между ними добавляя разделитесь ", Col":

Исключительно для демонстрации - так это будет работать с 4 числами. Ну а мы засунем вместо этого массива формулу, которую сделаем

="Select Col"&
JOIN(", Col";
ArrayFormula(ПОИСКПОЗ(СМЕЩ($A1;0;0;1;СЧЁТЗ($1:$1));
IMPORTRANGE(ссылка на файл;"'Название листа'!1:1");0)))

Если не использовать СМЕЩ, а заменять ошибки в ПОИСКПОЗ на пустоту (через IFNA, как описывали выше), то текстовую строку можно собрать с помощью TEXTJOIN, у этой функции есть аргумент, позволяющий пропускать пустые строки:

="Select Col"&
TEXTJOIN(", Col";ИСТИНА;
ArrayFormula(ifna(ПОИСКПОЗ($1:$1;
IMPORTRANGE(ссылка на файл;"'Название листа'!1:1");0);"")))

Собираем все в одну формулу

Итак, у нас есть формула, которая выкатывает запрос для QUERY, вот ее мы и засунем во второй аргумент этой функции (в примере беру диапазон из 100 столбцов — от A до CV — вы можете взять любой, какой нужен, разумеется, в том числе с запасом на создание новых в исходнике)

=QUERY(IMPORTRANGE(ссылка на файл;"'Название листа'!A2:CV");
"Select Col"&
JOIN(", Col";
ArrayFormula(ПОИСКПОЗ(
СМЕЩ($A1;0;0;1;СЧЁТЗ($1:$1));
IMPORTRANGE(ссылка на файл;"'Название листа'!1:1");0))))

Или — через TEXTJOIN :

=QUERY(IMPORTRANGE(ссылка на файл;"'Название листа'!A2:CV");
"Select Col"&
TEXTJOIN(", Col";
ИСТИНА;
ArrayFormula(IFNA(ПОИСКПОЗ($1:$1;
IMPORTRANGE(ссылка на файл;"'Название листа'!1:1");0)
;""))))

Все, успех! Можно выбирать столбцы из выпадающего списка и данные будут тянуться автоматом. Если выберете один и тот же столбец дважды — будет ошибка #ЗНАЧ, т.к. QUERY не разрешает повторять столбцы в запросе. Можно отлавливать ошибку — это оставляем на ваше усмотрение :) Про ошибки писали здесь.

Пример

Понятно, что в примере еще не так много столбцов, наиболее актуальна эта конструкция будет для случаев, когда вам нужно загрузить несколько десятков столбцов из таблицы, где их 50-100 или даже больше. Но формула будет работать для любого размера, так что забирайте примеры и используйте на здоровье:

Ссылка на таблицу, из которой импортируем

Ссылка на таблицу, куда импортируем (есть отдельные вкладки: решение через OFFSET + JOIN и решение через IFNA + TEXTJOIN)