Мы используем cookie, чтобы сайтом было удобно пользоваться
Главная статья импорта. Руководство по функции IMPORTRANGE (загрузка данных из другой Google Таблицы)
Если в Excel в формулах мы можем ссылаться и на другие листы, и на другие книги (файлы), то в Google Таблицах — только на другие листы, а вот из других таблиц (файлов) можем загружать данные с помощью функции IMPORTRANGE «в режиме реального времени».
Cинтаксис функции
Два текстовых аргумента — ссылка на источник (таблицу) и ссылка на диапазон (лист + диапазон или именованный диапазон или просто диапазон — подробнее ниже).
=IMPORTRANGE (ссылка на файл; ссылка на лист и диапазон)
Функция IMPORTRANGE выводит массив данных, а не одну ячейку (в частном случае она может возвращать и одну ячейку, если вы указали такой диапазон во втором аргументе. Но в большинстве случаев она все же используется для загрузки таблиц, а не одиночных ячеек). Это значит, что справа и снизу от него должно быть достаточно пустых ячеек для вывода этих данных. Если в ячейках есть данные, то функция не сможет их «перезаписать» выводимым массивом и вернет ошибку.
IMPORTRANGE возвращает только значения и числовые форматы, но не переносит стилевое форматирование — заливку, шрифт и так далее. Форматирование нужно настраивать отдельно в каждой таблице.
=IMPORTRANGE (ссылка на файл; ссылка на лист и диапазон)
Функция IMPORTRANGE выводит массив данных, а не одну ячейку (в частном случае она может возвращать и одну ячейку, если вы указали такой диапазон во втором аргументе. Но в большинстве случаев она все же используется для загрузки таблиц, а не одиночных ячеек). Это значит, что справа и снизу от него должно быть достаточно пустых ячеек для вывода этих данных. Если в ячейках есть данные, то функция не сможет их «перезаписать» выводимым массивом и вернет ошибку.
IMPORTRANGE возвращает только значения и числовые форматы, но не переносит стилевое форматирование — заливку, шрифт и так далее. Форматирование нужно настраивать отдельно в каждой таблице.
Ссылка на файл
Как текстовый аргумент любой функции, этот может быть получен следующим образом:
— с помощью других функций (функций поиска типа VLOOKUP или XLOOKUP или с помощью текстовых формул - например, в результате "склейки" нескольких фрагментов);
— в кавычках прямо в формуле (плюс в том, что мы не занимаем в таком случае отдельные ячейки);
— в виде ссылки на ячейку, где хранится ссылка (плюс в том, что можно быстро перейти в исходник).
Что касается самой ссылки, она может быть как полной, так и в виде ключа.
Примеры ссылок на файл, которые будут работать одинаково в функции IMPORTRANGE (первый аргумент).
Полная ссылка (сама ссылка не рабочая — это просто пример) с указанием номера листа вида edit#gid=1556931255:
https://docs.google.com/spreadsheets/d/ 1wWrgdcgIPeS3THHjZzkcPGMqwFCDHSTVlW4j1G6nppc/edit#gid=1556931255
Полная ссылка без номера листа:
https://docs.google.com/spreadsheets/d/ 1wWrgdcgIPeS3THHjZzkcPGMqwFCDHSTVlW4j1G6nppc
В любом случае лист будет указываться отдельно во втором аргументе, в ссылке (первом аргументе функции IMPORTRANGE) его отсутствие или присутствие ни на что не влияет!
Только ключ (то есть ID таблицы, который вы видите в ссылке в браузере, когда в ней находитесь):
1wWrgdcgIPeS3THHjZzkcPGMqwFCDHSTVlW4j1G6nppc
С одним ключом формула будет самой короткой. Но зато сложнее будет по ней "восстановить" ссылку, если надо будет в источник перейти. Впрочем, если вам нужно часто переходить в файл-источник, вставьте ссылку в ячейку и ссылайтесь на нее из IMPORTRANGE. Тогда у вас будет активная ссылка всегда под рукой.
Второй аргумент — диапазон — тоже можно брать как из ячейки, так и указывать в кавычках внутри функции. Он может задаваться несколькими способами:
— с помощью других функций (функций поиска типа VLOOKUP или XLOOKUP или с помощью текстовых формул - например, в результате "склейки" нескольких фрагментов);
— в кавычках прямо в формуле (плюс в том, что мы не занимаем в таком случае отдельные ячейки);
— в виде ссылки на ячейку, где хранится ссылка (плюс в том, что можно быстро перейти в исходник).
Что касается самой ссылки, она может быть как полной, так и в виде ключа.
Примеры ссылок на файл, которые будут работать одинаково в функции IMPORTRANGE (первый аргумент).
Полная ссылка (сама ссылка не рабочая — это просто пример) с указанием номера листа вида edit#gid=1556931255:
https://docs.google.com/spreadsheets/d/ 1wWrgdcgIPeS3THHjZzkcPGMqwFCDHSTVlW4j1G6nppc/edit#gid=1556931255
Полная ссылка без номера листа:
https://docs.google.com/spreadsheets/d/ 1wWrgdcgIPeS3THHjZzkcPGMqwFCDHSTVlW4j1G6nppc
В любом случае лист будет указываться отдельно во втором аргументе, в ссылке (первом аргументе функции IMPORTRANGE) его отсутствие или присутствие ни на что не влияет!
Только ключ (то есть ID таблицы, который вы видите в ссылке в браузере, когда в ней находитесь):
1wWrgdcgIPeS3THHjZzkcPGMqwFCDHSTVlW4j1G6nppc
С одним ключом формула будет самой короткой. Но зато сложнее будет по ней "восстановить" ссылку, если надо будет в источник перейти. Впрочем, если вам нужно часто переходить в файл-источник, вставьте ссылку в ячейку и ссылайтесь на нее из IMPORTRANGE. Тогда у вас будет активная ссылка всегда под рукой.
Второй аргумент — диапазон — тоже можно брать как из ячейки, так и указывать в кавычках внутри функции. Он может задаваться несколькими способами:
- Без указания названия листа — например, "B2:E" — в таком случае данные будут тянуться из диапазона B2:E с первого по порядку листа в исходном документе (то есть это штука не очень надежная: кто знает, не будет ли меняться порядок листов в исходнике?)
- С указанием названия листа — "Продажи!A2:D" или "Продажи!A1:L20".
- С использованием имени диапазона, если в исходном файле есть таковые. Например, "Налог". Тогда лист указывать не нужно.
Разрешение на доступ
Для загрузки вам нужно иметь доступ к этому файлу — на редактирование или на комментирование/просмотр.
Если доступ открыт для всех по ссылке — то можно сразу импортировать.
Если доступ открыт не всем, то вы должны дать доступ при первом импорте из источника — нажать на кнопку «Разрешить доступ» (Allow access), которая появится при первой попытке загрузить данные.
Если доступ открыт для всех по ссылке — то можно сразу импортировать.
Если доступ открыт не всем, то вы должны дать доступ при первом импорте из источника — нажать на кнопку «Разрешить доступ» (Allow access), которая появится при первой попытке загрузить данные.
После этого все пользователи целевого файла смогут импортировать данные из источника. Это несложно, но если нужно повторять действие много раз, то смотрите статью Михаила Смирнова:
Программно даём доступ для IMPORTRANGE к другой таблице
Программно даём доступ для IMPORTRANGE к другой таблице
Загружаем отдельные столбцы из исходника
Самый простой вариант — в каждом столбце разместить отдельные функции IMPORTRANGE. Вам нужны столбцы C и E из исходника — значит, в файле с импортом у вас в двух соседних столбцах будут IMPORTRANGE("ссылка"; "Лист!C:C") и IMPORTRANGE("ссылка"; "Лист!E:E"). Минус тут в том, что надо вводить все это вручную и что в исходной таблице может поменяться порядок столбцов.
Также можно использовать функцию CHOOSECOLS.
Например, следующая формула выдаст первый, третий и четвертый столбцы:
=CHOOSECOLS(IMPORTRANGE(...);1;3;4)
А следующая — первые N:
=CHOOSECOLS(IMPORTRANGE(...);SEQUENCE(N))
Если вы хотите выбирать в выпадающем списке заголовки тех столбцов из другой таблицы, которые хотите импортировать, а не грузить все подряд и не вводить столбцы вручную, то смотрите следующий пункт.
Также можно использовать функцию CHOOSECOLS.
Например, следующая формула выдаст первый, третий и четвертый столбцы:
=CHOOSECOLS(IMPORTRANGE(...);1;3;4)
А следующая — первые N:
=CHOOSECOLS(IMPORTRANGE(...);SEQUENCE(N))
Если вы хотите выбирать в выпадающем списке заголовки тех столбцов из другой таблицы, которые хотите импортировать, а не грузить все подряд и не вводить столбцы вручную, то смотрите следующий пункт.
Импорт выбранных столбцов
1. Загрузить заголовки из источника (ТРАНСП / TRANSPOSE — чтобы в столбик) на отдельный лист:
=TRANSPOSE(IMPORTRANGE("ссылка на источник";"Название_листа!1:1"))
2. Настроить проверку данных на основе этого списка — чтобы выбирать в выпадающем списке заголовки.
3. Далее нужно сделать формулу, которая в общем виде будет делать следующее: импортировать (IMPORTRANGE) только выбранные столбцы (QUERY). Запрос для QUERY будет вида Select ColN, ColM, ... , где N,M и так далее — порядковые номера нужных нам столбцов, определяемые функцией ПОИСКПОЗ / MATCH.
Вот таблица с двумя вариантами формул — по ссылке.
=TRANSPOSE(IMPORTRANGE("ссылка на источник";"Название_листа!1:1"))
2. Настроить проверку данных на основе этого списка — чтобы выбирать в выпадающем списке заголовки.
3. Далее нужно сделать формулу, которая в общем виде будет делать следующее: импортировать (IMPORTRANGE) только выбранные столбцы (QUERY). Запрос для QUERY будет вида Select ColN, ColM, ... , где N,M и так далее — порядковые номера нужных нам столбцов, определяемые функцией ПОИСКПОЗ / MATCH.
Вот таблица с двумя вариантами формул — по ссылке.
Объединяем данные из нескольких источников
Если вам нужно импортировать данные одинаковой структуры из разных таблиц, их надо объединить в один массив. Это можно сделать через фигурные скобки или функцию VSTACK (которая объединяет аргументы в один массив, вертикально, один под другим).
Заголовки берем только из первого файла, а из последующих — начиная с первой строки самих данных, чтобы заголовки не болтались посреди них в результате.
={IMPORTRANGE(первый файл; диапазон с заголовками) ; IMPORTRANGE(второй файл; диапазон без заголовков) ; ... }
Или через функцию:
=VSTACK(IMPORTRANGE(первый файл; диапазон с заголовками) ; IMPORTRANGE(второй файл; диапазон без заголовков) ; ... )
Что если предполагается добавление новых строк с данными? Тогда есть смысл ссылаться на открытые диапазоны вида A2:F. Но это приведет к тому, что будут импортироваться пустые строки (в конце диапазонов), а так как мы диапазоны склеиваем, то в итоговых данных будут эти ненужные прослойки. Поэтому лучше отфильтровать пустые строки — например, с помощью QUERY:
=QUERY( VSTACK(IMPORTRANGE(первый файл; диапазон с заголовками) ; IMPORTRANGE(второй файл; диапазон без заголовков) ; ... ) ; "Where Col1 is not null")
P.S. Можно использовать и одну функцию IMPORTRANGE как аргумент QUERY, разумеется — если нужно импортировать и после этого фильтровать/сортировать/агрегировать данные сразу. В таком случае также нужно обращаться к столбцам по номерам Col1, Col2 и т.д.
Заголовки берем только из первого файла, а из последующих — начиная с первой строки самих данных, чтобы заголовки не болтались посреди них в результате.
={IMPORTRANGE(первый файл; диапазон с заголовками) ; IMPORTRANGE(второй файл; диапазон без заголовков) ; ... }
Или через функцию:
=VSTACK(IMPORTRANGE(первый файл; диапазон с заголовками) ; IMPORTRANGE(второй файл; диапазон без заголовков) ; ... )
Что если предполагается добавление новых строк с данными? Тогда есть смысл ссылаться на открытые диапазоны вида A2:F. Но это приведет к тому, что будут импортироваться пустые строки (в конце диапазонов), а так как мы диапазоны склеиваем, то в итоговых данных будут эти ненужные прослойки. Поэтому лучше отфильтровать пустые строки — например, с помощью QUERY:
=QUERY( VSTACK(IMPORTRANGE(первый файл; диапазон с заголовками) ; IMPORTRANGE(второй файл; диапазон без заголовков) ; ... ) ; "Where Col1 is not null")
P.S. Можно использовать и одну функцию IMPORTRANGE как аргумент QUERY, разумеется — если нужно импортировать и после этого фильтровать/сортировать/агрегировать данные сразу. В таком случае также нужно обращаться к столбцам по номерам Col1, Col2 и т.д.
Загружаем данные из списка (который может меняться) таблиц
Дано: есть набор однотипных таблиц. Нужно загружать данные из всех таблиц в списке, при этом список может меняться — могут добавиться новые, могут уйти старые.
Решение: пробегаемся по массиву ссылок, и импортируем IMPORTRANGE данные из каждого, последовательно собирая (это будет делать функция VSTACK, в такой же логике, как в предыдущем примере) в один массив с помощью REDUCE и LAMBDA. В статье — несколько вариантов формул.
Решение: пробегаемся по массиву ссылок, и импортируем IMPORTRANGE данные из каждого, последовательно собирая (это будет делать функция VSTACK, в такой же логике, как в предыдущем примере) в один массив с помощью REDUCE и LAMBDA. В статье — несколько вариантов формул.
Проблемы с загрузкой данных IMPORTRANGE
Что делать, если данные не импортируются, так как их слишком много? Можно разбить импорт на отдельные функции. Если у вас одна функция IMPORTRANGE тянет 50 тысяч строк из 20 столбцов, попробуйте разбить на 2 по 10 или на 4 по 10 и так далее, до победного результата. Или используйте скрипты, чтобы вставлять данные как значения.
“
Для копирования данных есть скрипт, который полюбился читателям нашего канала «Google Таблицы». Используйте его для того, чтобы копировать данные из Таблиц в другие Таблицы по расписанию: Собиратор 4.0.
Если вам нужен производный результат, в котором будет немного ячеек, то не импортируйте все данные, сделайте расчет в исходнике (если возможно, у вас есть доступ и т. д.) и импортируйте только результат.
“
Еще в справке рекомендуют не делать циклических ссылок — когда вы импортируете данные из таблицы 1 в таблицу 2 и наоборот.
Вложенные функции или отдельный лист?
Можно делать так: не импортировать данные в отдельный диапазон, а сразу засовывать функцию IMPORTRANGE как аргумент — например, куда-нибудь в функцию VLOOKUP, которая ведет поиск в «виртуальном» импортируемом массиве.
=VLOOKUP (значение для поиска; IMPORTRANGE (…); номер столбца; 0)
Ранее это могло влиять на быстродействие (так как если у вас 10 000 формул — то и 10 000 импортов). Но — спасибо Михаилу Смирнову за находку — судя по всему, с определенного момента результат IMPORTRANGE кэшируется. Так что можно спокойно импортировать одно и то же много раз. Диапазон при этом должен точно совпадать во всех функциях. То есть если у вас будет отличие в регистре или в самом диапазоне — будет отдельный кэш, данные будут грузиться отдельно.
Итак, если вам лучше видеть сами данные — не жалейте ячейки (их все-таки в Таблицах можно до 10 миллионов создать) и вставляйте IMPORTRANGE один раз на отдельный лист, а потом крутите формулами в таблице уже этот лист как вам вздумается. А если хотите — обрабатывайте импортируемые данные «на лету» в формулах.
=VLOOKUP (значение для поиска; IMPORTRANGE (…); номер столбца; 0)
Ранее это могло влиять на быстродействие (так как если у вас 10 000 формул — то и 10 000 импортов). Но — спасибо Михаилу Смирнову за находку — судя по всему, с определенного момента результат IMPORTRANGE кэшируется. Так что можно спокойно импортировать одно и то же много раз. Диапазон при этом должен точно совпадать во всех функциях. То есть если у вас будет отличие в регистре или в самом диапазоне — будет отдельный кэш, данные будут грузиться отдельно.
Итак, если вам лучше видеть сами данные — не жалейте ячейки (их все-таки в Таблицах можно до 10 миллионов создать) и вставляйте IMPORTRANGE один раз на отдельный лист, а потом крутите формулами в таблице уже этот лист как вам вздумается. А если хотите — обрабатывайте импортируемые данные «на лету» в формулах.
Как часто обновляются данные
В справке утверждается, что не реже одного раза в час. Обновление вкладки или повторное открытие таблицы обновление не вызывают.
Когда данные в исходнике меняются и все формулы пересчитываются — сразу начинают обновляться результаты в открытых таблицах, которые импортируют оттуда данные.
При этом данные обновляются при изменении формулы с IMPORTRANGE. Так что если очень хочется, можно создать своего рода "кнопку" (а точнее, флажок) для принудительного обновления данных.
Например, поставить флажок в любую ячейку, а формулу загрузки данных модифицировать следующим образом:
=IF(ячейка с флажком; IMPORTRANGE(1) ; IMPORTRANGE(2))
Главное — чтобы диапазоны в двух функциях IMPORTRANGE отличались. Например, можете в первом IMPORTRANGE указать диапазон вида A:E, а во втором — A1:E. Результат будет одинаковый. Но при переключении флажка данные будут сразу пересчитываться, так как функция IF / ЕСЛИ будет выдавать другой аргумент, в котором другая функция.
Когда данные в исходнике меняются и все формулы пересчитываются — сразу начинают обновляться результаты в открытых таблицах, которые импортируют оттуда данные.
При этом данные обновляются при изменении формулы с IMPORTRANGE. Так что если очень хочется, можно создать своего рода "кнопку" (а точнее, флажок) для принудительного обновления данных.
Например, поставить флажок в любую ячейку, а формулу загрузки данных модифицировать следующим образом:
=IF(ячейка с флажком; IMPORTRANGE(1) ; IMPORTRANGE(2))
Главное — чтобы диапазоны в двух функциях IMPORTRANGE отличались. Например, можете в первом IMPORTRANGE указать диапазон вида A:E, а во втором — A1:E. Результат будет одинаковый. Но при переключении флажка данные будут сразу пересчитываться, так как функция IF / ЕСЛИ будет выдавать другой аргумент, в котором другая функция.
Как вытащить из IMPORTRANGE ссылку и сделать активной
Если нужно вытащить из IMPORTRANGE ссылку на источник и превратить ее в кликабельную ссылку в отдельной ячейке, воспользуйтесь следующей формулой. Функция FORMULATEXT превращает формулу в текстовое значение. А далее мы из него вытаскиваем (REGEXEXTRACT) ссылку и делаем ее активной (HYPERLINK):
=HYPERLINK(REGEXEXTRACT(FORMULATEXT(ссылка на формулу) ; """(.+?)""") ; "Ссылка")
Второй аргумент HYPERLINK — то, как будет выглядеть ссылка в ячейке (текст ссылки).
=HYPERLINK(REGEXEXTRACT(FORMULATEXT(ссылка на формулу) ; """(.+?)""") ; "Ссылка")
Второй аргумент HYPERLINK — то, как будет выглядеть ссылка в ячейке (текст ссылки).
Как вытащить из IMPORTRANGE ссылку и сделать активной
Другие материалы по IMPORTRANGE
Справка
IMPORTRANGE: от простого к сложному (статья Михаила Смирнова) — в частности, там есть формулы для того, чтобы подписывать источник данных.
Видеоурок по IMPORTRANGE
Собиратор 4.0
Справка
IMPORTRANGE: от простого к сложному (статья Михаила Смирнова) — в частности, там есть формулы для того, чтобы подписывать источник данных.
Видеоурок по IMPORTRANGE
Собиратор 4.0
Другие статьи