Как связать две таблицы в гугл таблицах

Обновлено: 17.04.2024

Импортирует диапазон ячеек из одной электронной таблицы в другую.

Пример использования

IMPORTRANGE(" https://docs.google.com/spreadsheets/d/abcd123abcd123 ", "лист1!A1:C10")

Синтаксис

url_таблицы – URL таблицы, из которой импортируются данные.

Значение url_таблицы должно быть текстом, заключенным в кавычки, или ссылкой на ячейку, в которой содержится таблица.

диапазон – строка в формате "[название_листа!]диапазон" (например, "Лист1!A2:B6" или "A2:B6" ). Этот параметр указывает на диапазон, который нужно импортировать.

Компонент название_листа в параметре диапазон не является обязательным. По умолчанию IMPORTRANGE импортирует данные из заданного диапазона первого листа.

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

Технические сведения и рекомендации

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

Рекомендации

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

Изменения, внесенные при работе с функцией IMPORTRANGE , могут влиять на другие данные. Если на листе Б содержится функция IMPORTRANGE (листА), а на листе В – функция IMPORTRANGE (листБ), то при внесении изменений в данные на листе А будут также обновлены листы Б и В.

Рекомендации

Используйте меньше цепочек в рамках функции IMPORTRANGE .
Старайтесь не применять циклы в функции IMPORTRANGE .
Изменения, внесенные на исходном листе, могут появиться на принимающем листе не сразу. Если функция IMPORTRANGE реализована много раз в нескольких связанных документах, от момента внесения изменений на исходном листе до появления результатов на принимающем листе может пройти немало времени.

Функция IMPORTRANGE обновляется, когда документ становится активным, после того как его не открывали в течение последних пяти минут. Как и в случае с цепочкой обновлений, функция IMPORTRANGE вынуждена обращаться к каждому документу, из которого или в который импортируются данные.

Рекомендации

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

Разрешение и доступ

Когда таблица 1 впервые импортирует данные из таблицы 2 при выполнении функции IMPORTRANGE , система запрашивает разрешение на доступ к информации. После получения разрешения все редакторы таблицы 1 смогут использовать IMPORTRANGE для импорта любых данных из таблицы 2. Разрешение будет действовать до тех пор, пока пользователь, давший его, не будет лишен прав доступа к таблице 2. Учтите, что предоставление доступа к принимающему листу учитывается в ограничении в 600 пользователей общего диска, которое действует для исходного листа.

Производительность

Функция IMPORTRANGE использует внешние данные, как и функции IMPORTXML и GOOGLEFINANCE . Это означает, что для работы функции необходимо подключение к интернету. Google Таблицы скачивают весь нужный диапазон на компьютер и на их работе скажется низкая скорость подключения к интернету. При этом действует ограничение на объем полученных данных (10 МБ для одного запроса). Если функция IMPORTRANGE работает медленно, попробуйте уменьшить размер диапазонов, которые следует импортировать. Вы также можете перенести сводные расчеты в исходный документ. Это позволит вам перемещать меньше данных в листы, находящиеся на компьютере, и выполнять больше расчетов удаленно.

Примечание. Вам доступны другие похожие инструменты. Apps Script может принимать данные из других документов и срабатывать при внесении изменений или по расписанию. Подключенные таблицы обновляются по расписанию и больше подходят для загрузки и импорта крупных наборов данных.

Если у вас остались вопросы, вы можете посетить справочный форум Редакторов Документов.

Похожие функции

IMPORTXML : Импорт данных из источников в формате XML, HTML, CSV, TSV, а также RSS и ATOM XML..

IMPORTHTML : Импортирует данные из таблицы или списка на веб-странице..

IMPORTFEED : Импортирует фид RSS или Atom..

IMPORTDATA : Импортирует данные в формате CSV (значения, разделенные запятыми) или TSV (значения, разделенные табуляцией). Для импорта необходимо указать ссылку на источник данных..

Всем привет, меня зовут Артем Медведев, я представляю компанию Helpexcel.pro. Мы занимаемся созданием управленческих таблиц и умеем их интегрировать с другими приложениями.

На самом деле очень много задач, связанных с управленческими отчетами можно решить без программирования. Мы периодически публикуем материалы о том, как самостоятельно создать управленческий учет в Гугл таблицах. Делюсь тут ссылкой на соответствующий раздел сайта. Там пошаговая инструкция с разбором формул.

Кейс:
У каждого сотрудника своя таблица, где записываются учетные операции. Нужно сделать так, что бы в одной таблице собиралась информация из таблиц сотрудников в один сводный отчет.

Прикрепляю небольшой видеообзор итогового решения, что бы было понятней о чем речь)

Вот ссылка на папку с таблицами, которые будет описаны далее.

Рассмотрим ситуацию, когда с целью разграничения прав у каждого сотрудника своя таблица, где фиксируются учетные операции.

И есть таблица руководителя, содержащая форму отчета.

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

Для начала нужно сделать так, что бы данные, которые водятся в таблицы сотрудников появлялись в таблице руководителя.

Для этого создаем в таблице руководителя ровно столько листов, сколько таблиц сотрудников. Данные будем подтягивать формулой IMPORTRANGE()

Посмотрите короткий обзор применения формулы в данной ситуации.

В таблице «Отчетность» на листе «Данные» поставьте курсор в ячейку D3 и пропишите там формулу по аналогии с видеозаписью.

Подробнее о формуле:

=IMPORTRANGE(«Ссылка на таблицу»; «Диапазон, который нужно отобразить из этой таблицы»)

Обязательно заключайте аргументы в кавычки!

Пример функции:

Для самопроверки воспользуйтесь готовой формулой

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

Теперь нужно сделать так, что бы отчет заполнялся актуальными данными из таблиц сотрудников.

Для этого хорошо подходить формула СУММЕСЛИМН()

Подробнее о формуле:

=СУММЕСЛИМН(диапазон суммирования;диапазон критерия 1; критерий 1; диапазон критерия 2; критерий 2;…)

И эту формулу нужно приписать относительно каждой таблицы.

Вид формулы в ячейке отчета:

Про формулу СУММЕСЛИМН и про то как ее использовать для разных вычислений напишу статью немного позже.

А пока прикрепляю запись вебинара, где мы очень подробно разбираем IMPORTRANGE, СУММЕСЛИМН, ЕСЛИ, FILTER для создания модели отчета.

Если хотите получить записи курса по Гугл таблицам, то можно посмотреть информацию на этой страничке.

Какой-то велосипед из костылей и желудей. Вы про СУБД слышали, не?

Конечно слышал) мы этим и занимаемся!
Суть статьи в том, что бы каждый предприниматель смог простым способом настроить себе отчетность. Практика показывает, что большинство пользователей не знают элементарных вещей.

В начале статьи так и написано "много задач, связанных с управленческими отчетами можно решить без программирования".

Не все могут и хотят делать что то сложное. Да возможно это нецелесообразно. Есть простые и общедоступные инструменты.

Решать такого уровня задачи можно в любом гуе и тоже без программирования. Только там функций больше и импорт\экспорт в любой любимый табличный редактор наладить не проблема.

Например? Возможно читателям статьи будет интересно.

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

Тут очевидно, что без разработчиков не обойтись! Я писал выше о цели статьи.

Для того, что бы получить что то нужна функциональность, которая будет отправлять запросы в базу.

Люди это интерфейсом называют.

Тут очевидно, что без разработчиков не обойтись! Я писал выше о цели статьи.

Кому очевидно? Специалистам хелпэксел.про? Ну такое. Мне вот для нажатия далее-согласен-далее-далее-окей разработчик не нужен, и это всё ещё проще костылей в табличном редакторе с очень условной реляционностью.

Да в целом вы можете к нам на сайт перейти и будет понятно вообще чем

мы занимаемся
Пытаетесь продавать таблицы в экселе и на 40% не работающих чат-ботов.

Большинство из нас знакомы с основными операциями при использовании электронных таблиц в Google Таблицах, такими как использование определенных строк и столбцов, вычисление, создание круговых диаграмм, столбцов, строк из данных и т. Д. Но почему мы просто останавливаемся на этом. Вам необходимо улучшить электронные таблицы, например связать данные между электронными таблицами, точно взять данные или диапазоны данных и поместить их в совершенно новое положение на другом листе. В этой статье показано, как связать данные с помощью функций ЗАПРОС и ИМПОРТРАНЖ.

Как настроить право редактировать таблицы в Google Таблицах

Примечание. При связывании данных с другой таблицей необходимо щелкнуть поле и «Разрешить доступ» после ввода формулы, в противном случае данные не появятся.

Как связать данные с помощью функции ИМПОРТРАНЖ

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

= ИМПОРТРАНЖ («ключ_таблицы», «строка_диапазона»)

«Ключ электронной таблицы» — это длинная строка цифр и букв в URL-адресе данной электронной таблицы. «Строка диапазона» — это точное имя электронной таблицы, которая принимает данные (по умолчанию называется «Sheet1», «Sheet2» и т. Д.), За которым следует ‘!’ и диапазон ячеек, которые хотят получить данные.

Ниже приведена таблица, в которой будут связаны данные:

В этой таблице мы получим данные из ячейки A1 в D100. Следовательно, у нас будет следующая формула:

= ImportRange («1ByTut9xooZdPIBF55gzQ0Cdi04owDTtLVc_gPGtOKY0», «Sheet1! A1: D100»)

Эта формула импортирует данные из электронной таблицы в другую таблицу. Вот данные из ячеек A1 до D100 из рабочего листа Sheet1. Данные, которые будут отображаться в другой таблице, сохранят тот же формат, что и исходный лист.

Используйте функцию ЗАПРОС для ввода условных данных

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

Например, здесь мы получим немецкие данные о проданных единицах.

Чтобы получить данные, введите следующую формулу:

= QUERY (ImportRange («1ByTut9xooZdPIBF55gzQ0Cdi04owDTtLVc_gPGtOKY0», «Sheet1! A1: O1000»), «выберите Col5, где Col2 = ‘Germany'»)

Здесь данные «ImportRange» правильно следуют предыдущему синтаксису, а затем мы укажем функцию для столбца 5 как столбца «Продано единиц», содержащего столбец 2 «Германия». Следовательно, в этом запросе есть два «аргумента»: ImportRange и выберите ColX, где ColY = ‘Z’.

Приведенные выше инструкции позволяют создавать связи между электронными таблицами и могут автоматически обновлять данные при изменении в электронной таблице.

Большинство из нас знакомы с основами использования электронной таблицы в Таблицы Google; Мы фиксируем некоторые строки и столбцы, мы создаем вычисления, мы организуем наши данные в кружки, графики и графики, чтобы помочь визуализировать их. Но почему на этом все заканчивается? Следующим логическим шагом в улучшении ваших электронных таблиц является связывание данных между различными электронными таблицами, ввод точных данных или диапазонов данных, которые вы хотите, и отправка их в совершенно новое место! Здесь мы покажем вам, как это сделать с помощью функции ЗАПРОС и ИМПОРТРАНЖ.

Примечание. Обратите внимание, что при связывании данных с другой таблицей вам нужно будет щелкнуть ячейку и «Разрешить доступ» после ввода формулы, иначе данные не появятся.

КАК СВЯЗАТЬ ДАННЫЕ С ПОМОЩЬЮ ИМПОРТРАНЖА

Первый и самый важный способ связать данные между листами - использовать функцию ИМПОРТРАНЖ. Вот синтаксис того, как IMPORTRANGE передает данные из одной электронной таблицы в другую:

«Spreadsheet_key» - длинная комбинация цифр и букв в URL-адресе данной электронной таблицы.

«Range_string» - это точное имя страницы, с которой вы извлекаете данные (по умолчанию она называется «лист1», «лист2» и т. Д.), За которым следует «!» и диапазон ячеек, из которых вы хотите получить данные i.

Вот лист, из которого мы будем извлекать данные:

Мы будем извлекать данные из этой таблицы между ячейками A1 и D100. Формула для этого вводится в будущую электронную таблицу следующим образом:

При этом импортируются данные из другой электронной таблицы, в частности, из внутреннего листа, называемого «лист 1», куда переносятся все данные между ячейками A1 и D100. После выполнения этой настройки данные должны появиться в исходном виде.

Используйте QUERY для импорта наиболее условных данных

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

В этом примере мы снова возьмем данные из таблицы ниже, но на этот раз мы собираемся получить только те данные, которые «Продано единиц» из Германии.

Чтобы получить нужные данные, нам нужно ввести следующее:

Здесь данные «IMPORTRANGE» следуют точно такой же формуле, как и раньше, но теперь мы собираемся использовать префикс QUERY), а затем указать ему выбрать столбец 5 (столбец «Продано единиц»), где данные находятся в столбце 2, т. Е. «Германия», поэтому в разделе «ЗАПРОС-ИМПОРТРАНЖ» есть два из «Аргументов» и выберите столбец X, где столбец Y = «Z».

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

Заключение

Приведенное выше руководство позволяет вам создавать листы, полные жизненно важных данных, когда исходный лист получает обновление. Такая настройка - это большие деньги и находка для тех, кто хочет объединить несколько таблиц с ценными подробными данными в один большой лист.

Вы открыли для себя какие-нибудь интересные уловки с Google Таблицами или у вас есть другой способ связать таблицы? Делитесь в комментариях.

Доступ к данным из одной книги в другую в Google Таблицах не так прост, как для любого другого программного обеспечения для работы с электронными таблицами. В основном это связано с онлайн-характером Google Таблиц.

И это имеет смысл, учитывая проблемы безопасности данных.

Но Google позволяет вам получать доступ и использовать данные из внешнего рабочего листа, если у вас есть соответствующее разрешение и доступ. Эта возможность обеспечивается функцией IMPORTRANGE .

Если вы когда-либо видели вызов функции IMPORTRANGE раньше, вы, возможно, испугались (на первый взгляд) ее длины и сложности.

Но, честно говоря, её синтаксис намного проще, чем кажется. В этом руководстве мы более подробно рассмотрим функцию IMPORTRANGE и поймем, как она работает, на простом примере.

Синтаксис функции IMPORTRANGE

Синтаксис функции IMPORTRANGE :

Функция принимает два параметра:

spreadsheet_url: это URL-адрес исходной электронной таблицы.
range_string: это строка, содержащая информацию о диапазоне ячеек для импорта.

Разберемся с этими параметрами немного подробнее.

URL таблицы или Ключ книги

Это первый параметр функции IMPORTRANGE. Он содержит URL-адрес электронной таблицы или ключ Google книги, из которой вы хотите выполнить импорт.

Ниже вы можете найти URL-адрес Google Таблиц.

И вы также можете использовать ключ книги вместо использования всего URL-адреса в функции IMPORTRANGE.

Этот параметр обычно представляет собой строковое значение. Он может содержать URL-адрес исходной таблицы (заключенный в двойные кавычки) или ссылку на ячейку, содержащую URL-адрес исходной электронной таблицы (без двойных кавычек).

URL-адрес электронной таблицы — это все местоположение, которое вы видите в адресной строке браузера, когда открыта исходная электронная таблица.

Первая часть URL-адреса — это основной URL-адрес Google Таблиц. За ней следует папка «d», за которой следует действительно большая строка буквенно-цифровых символов. Это ключ книги или идентификатор, который электронные таблицы Google используют для идентификации книги.

Чтобы указать первый параметр функции IMPORTRANGE, вы можете использовать полный URL-адрес электронной таблицы или только ее ключ. Либо одного достаточно, чтобы идентифицировать электронную таблицу, из которой вы хотите импортировать.

Строка диапазона

Строка диапазона — это второй параметр функции IMPORTRANGE. Это также строковое значение и имеет следующий формат:

Как видите, range_string состоит из двух частей:

sheet_name: это имя электронной таблицы, из которой вы хотите импортировать данные.
range: это диапазон ячеек, который вы хотите импортировать из исходного листа. Этот компонент может быть ссылкой на диапазон ячеек, одну ячейку или именованный диапазон.

Обратите внимание, что компонент sheet_name был представлен в квадратных скобках, потому что эта часть range_string является необязательной. Если вы не предоставите этот компонент, IMPORTRANGE по умолчанию импортирует первый лист указанной книги.

Вот несколько примеров строк диапазона:

«MySheet! A1: B10»: подобная строка range_string используется, когда вы хотите импортировать ячейки от A1 до B10 из рабочего листа с именем «MySheet».
«A1: B10»: подобная строка range_string используется, когда вы хотите импортировать ячейки от A1 до B10 с первого листа в исходной книге.
«MySheet! A5»: подобная строка range_string используется, когда вы хотите импортировать только одну ячейку, A5, из рабочего листа с именем «MySheet».

Пример — импорт диапазона ячеек из другого листа

Чтобы продемонстрировать, как использовать функцию IMPORTRANGE, давайте попробуем импортировать ячейки с A1 по B7 из листа с именем «Сотрудники» (англ. Employees), принадлежащего внешней книге.

Вот шаги, которые вам необходимо выполнить:

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

Таким образом, функцию IMPORTRANGE можно использовать для доступа к данным из других книг. Это становится особенно полезным, когда вы хотите собрать данные из нескольких книг на одном листе.

Например, с помощью IMPORTRANGE вы можете импортировать данные о сотрудниках из разных филиалов компании в один сводный лист.

Мы надеемся, что нам удалось помочь вам понять, что делает функция IMPORTRANGE и как ее использовать. Мы рекомендуем вам попробовать это на себе и убедиться, насколько это удобно и экономит время.

Читайте также: