Как связать ячейки в гугл таблице на разных листах

Как связать ячейку в таблицах Google с ячейкой в ​​другом документе?

У меня есть ежемесячная электронная таблица, основанная на данных за предыдущий месяц. Я хотел бы импортировать эти значения динамически, а не вырезать и вставить их. Это возможно? Как это сделать?

IMPORTRANGE() кажется, что функция, которую вы хотите.

Google Spreadsheets позволяет вам ссылаться на другую рабочую книгу в электронной таблице, которую вы в настоящее время редактируете, с помощью функции ImportRange. ImportRange позволяет извлекать одно или несколько значений ячеек из одной электронной таблицы в другую. Чтобы создать собственные формулы ImportRange, введите = importRange (ключ таблицы, диапазон). Для языков, в которых запятая используется для десятичного разделения, используйте точку с запятой вместо запятой для разделения аргументов в формуле.

Spreadsheet-key — это STRING, значение ключа из URL-адреса электронной таблицы.

Range — это STRING, представляющая диапазон ячеек, которые вы хотите импортировать, при желании включая имя листа (по умолчанию первый лист). Вы также можете использовать имя диапазона, если хотите.

Учитывая, что два аргумента являются STRING, вам необходимо заключить их в кавычки или обратиться к ячейкам, в которых есть строковые значения.

Например:

= importrange («abcd123abcd123», «sheet1! A1: C10») «abcd123abcd123» — это значение в атрибуте «key =» в URL целевой электронной таблицы, а «sheet1! A1: C10» — это диапазон, который требуется импортироваться

= importrange (A1, B1) Ячейка A1 содержит строку ABCD123ABCD123, а ячейка B1 содержит лист1! A1: C10

Примечание. Чтобы использовать ImportRange, необходимо добавить его в качестве средства просмотра или соавтора в электронную таблицу, из которой ImportRange извлекает данные. В противном случае вы получите эту ошибку: «#REF! Error: запрошенный ключ электронной таблицы, заголовок листа или диапазон ячеек не найден».

«ключ» — это, конечно, строка в URL для электронной таблицы, которая соответствует key= параметру.

Я только что проверил это, создав две таблицы. В ячейку А1 первой я положил строку. В ячейку A1 второй я положил, =importRange(«tgR2P4UTz_KT0Lc270Ijb_A»,»A1″) и она отображала строку из первой электронной таблицы. (Ваш ключ, очевидно, будет другим.)

(Формат функции может зависеть от вашей местности во Франции формула не действует с запятой, так что вам нужно заменить его с запятой:. =importRange(«tgR2P4UTz_KT0Lc270Ijb_A»;»A1″) )

В настоящее время Google устанавливает жесткое ограничение в 50 «справочных формул кросс-книги» на электронную таблицу. Источник: Google Docs, Sheets и Slides ограничения по размеру . (ч / т Джей Джей Рорер )

«Новая» электронная таблица Google (скоро станет стандартом) устраняет 50-кратный 50-кратный справочник по формулам книги ( служба поддержки Google ) (х / т Джейкоб Ян Туинстра )

В «новых» Google Sheets вы также используете весь URL-адрес в качестве ключа ( поддержка Google ) (h / t Punchlinern )

Объединение таблиц в Google Таблицах

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

Способ 1: Копирование и вставка

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

1. Откройте исходную таблицу на официальном сайте рассматриваемого сервиса и выделите нужные ячейки, используя рамку при зажатой левой кнопке мыши. После этого на клавиатуре одновременно нажмите «CTRL+C» или воспользуйтесь пунктом «Копировать» в меню «Правка» на верхней панели.
2. Перейдите на один из листов в конечном документе, выберите ячейку для переноса данных и нажмите «CTRL+V». Также по аналогии с предыдущим шагом можете развернуть меню «Правка» и воспользоваться опцией «Вставить», но только при наличии установленного расширения «Google Документы офлайн».

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

Способ 2: Загрузка данных

Более сложный способ объединения таблиц сводится к использованию специальной формулы «IMPORTRANGE», призванной импортировать определенный диапазон ячеек из одного документа в другой. В данном случае между файлами будет установлена прямая связь, но при этом перенесенные один раз значения не будут автоматически обновляться.

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

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

Способ 3: Перенос листов

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

Управление листами

1. Чтобы перенести определенный лист из документа в другой файл Google Таблицы, на нижней панели кликните левой кнопкой мыши по нужной странице и во всплывающем меню разверните «Копировать в». Здесь, в свою очередь, нужно воспользоваться опцией «Существующая таблица».
2. В открывшемся окне, по необходимости используя тестовое поле для поиска, найдите и выберите целевой документ с помощью соответствующей кнопки в левом нижнем углу. Также можно выбирать чужие файлы, но только открытые для редактирования и расположенные в категории «Доступные мне».
3. Разобравшись с выполнением поставленной задачи и дождавшись появления сообщения «Лист скопирован», можете перейти к выбранной ранее таблице, чтобы убедиться в успешном переносе. Если все было сделано правильно, на нижней навигационной панели появится новая вкладка с пометкой «Копия».

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

Выполнение скрипта

1. Если перенести нужно множество листов, повторять описанный порядок действий может быть проблематично. Специально на такой случай можно воспользоваться достаточно простым скриптом на отдельной странице сервиса, перед этим открыв целевой документ, развернув меню «Расширения» и выбрав «Apps Script».
2. Вручную менять ничего не требуется, достаточно будет скопировать представленный ниже набор символов и заменить содержимое в текстовом поле на открывшейся вкладке. При этом, если вы хорошо разбираетесь в синтаксисе данного языка, некоторые детали можно с легкостью изменить, например, чтобы использовать идентификатор файла вместо полноценной ссылки.

function importSheets() <
var url = «ссылка_на_документ»;
var origin = SpreadsheetApp.openByUrl(url);
var result = SpreadsheetApp.getActiveSpreadsheet();
var sheets = origin.getSheets();
for (var sheet in sheets) <
sheets[sheet].copyTo(result)
>;
>

Данный способ позволяет объединять разные Google Таблицы посредством простого изменения URL-адреса в коде скрипта. Если вы хотите перенести данные в один файл из множества разных документов, рекомендуем ознакомиться со справкой по Apps Script и впоследствии инвертировать работу кода.

Вариант 2: Мобильное устройство

Официальный мобильный клиент Google Таблиц для Android и iOS предоставляет несколько ограниченный набор возможностей, но этого вполне будет достаточно для объединения документов сразу двумя способами. К сожалению, распространяется это исключительно на сами ячейки, в то время как переносить и копировать листы в данной версии не получится.

Способ 1: Копирование и вставка

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

1. Перейдите на главную страницу исходного документа, коснитесь одной из ячеек, растяните рамку от угла в нужную сторону и во всплывающем окне нажмите кнопку «Скопировать». Если вам нужно выделить сразу все содержимое листа без исключений, можете коснуться области в левой верхней части экрана.
2. Завершив выборку элементов, перейдите в другой документ, выберите стартовую ячейку для переноса данных из предыдущего файла и во всплывающем меню нажмите «Вставить». Если все было сделано правильно, в нужном месте появятся выделенные и скопированные ранее ячейки.

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

Способ 2: Загрузка данных

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

Откройте таблицу, в рамках которой нужно объединить данные с разных листов, выберите подходящую ячейку и коснитесь блока «Введите текст или формулу» в нижней части экрана. Сюда, в свою очередь, нужно вставить представленный ниже набор символов.

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

Как связать данные между таблицами в Google Таблицах

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

1. Как настроить право редактировать таблицы в 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’.

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

Как ссылаться на данные с другого листа

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

Как добавить данные с другого листа таблицы

1. Откройте страницу docs.google.com/spreadsheets/ на компьютере.
2. Выберите или создайте таблицу.
3. Выделите ячейку.
4. Введите = , затем название листа, восклицательный знак и номер копируемой ячейки, например =Sheet1!A1 или =’Sheet number two’!B4 .

Примечание. Если название листа содержит пробелы или специальные символы, заключите его в одиночные кавычки (как во втором примере).

Как добавить данные из другой таблицы

Внимание! Чтобы добавить ссылку на ячейку или диапазон ячеек из другой таблицы, используйте функцию IMPORTRANGE.