Импортируем только выбранные (в выпадающих списках) столбцы с данными из 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)