Как связать данные между таблицами в Google Таблицах
Большинство из нас знакомы с основными операциями при использовании электронных таблиц в 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 таблицы — Как ссылаться на данные с другого листа с определенными ячейками?
В таблице 1 находятся данные в 2х колонках:
Яблоки Поставщик 1
Сливы Поставщик 2
Арбузы Поставщик 1
.
Нужно в таблицу 2 передать все строки, в которых Поставщик 1.
Так:
=IMPORTRANGE(«1N69gOQ46iVRI3h5dWWnC2AHNIEQYEISBXgs9Q25Dv1s»; «Лист!A1:A200»)
=IMPORTRANGE(«1N69gOQ46iVRI3h5dWWnC2AHNIEQYEISBXgs9Q25Dv1s»; «Лист!B1:B200»)
в таблицу 2 передаются все данные.
Как сделать условие, чтоб передавались только строки, в которых Поставщик 2?
Как ссылаться на данные с другого листа
Вы можете копировать данные и переносить их из одного листа таблицы на другой.
Как добавить данные с другого листа таблицы
- Откройте страницу docs.google.com/spreadsheets/ на компьютере.
- Выберите или создайте таблицу.
- Выделите ячейку.
- Введите = , затем название листа, восклицательный знак и номер копируемой ячейки, например =Sheet1!A1 или =’Sheet number two’!B4 .
Примечание. Если название листа содержит пробелы или специальные символы, заключите его в одиночные кавычки (как во втором примере).
Как добавить данные из другой таблицы
Внимание! Чтобы добавить ссылку на ячейку или диапазон ячеек из другой таблицы, используйте функцию IMPORTRANGE.
5 функций Google Таблиц, которые вы должны знать
Вероятно, вы знакомы с основами Google Таблиц, но электронные таблицы Google имеют множество функций, которые на первый взгляд не очевидны.
Google Таблицы – Пример Google Таблицы
Скорее всего вы уже знакомы с некоторыми базовыми формулами, такими как SUM и AVERAGE , а также хорошо знаете панель инструментов, но это лишь незначительная часть. Я давно пользуюсь электронными таблицами Google, но даже по сей день я все еще узнаю новые возможности в Google Таблицах.
Импорт таблиц данных
Google Таблицы – Импорт данных из таблицы на веб-странице
Если на веб-сайте есть таблица или список информации, которую вы хотите отслеживать, вы можете использовать функцию ImportHTML, чтобы вставить данные в Google таблицу. Данные автоматически обновляются всякий раз, когда вы открываете электронную таблицу Google. Функция выглядит следующим образом:
=ImportHTML(“ссылка”; «запрос»; индекс),
- ссылка – ссылка на веб-страницу. Должна содержать протокол (например, http://). Значение параметра ссылка должно быть заключено в кавычки или представлять собой ссылку на ячейку, содержащую соответствующий текст.
- запрос – чтобы получить данные из таблицы, введите параметр «table», а из списка – «list».
- индекс – порядковый номер элемента веб-страницы (начиная с 1), данные из которого будут импортированы. Индексы списков и таблиц хранятся отдельно друг от друга, поэтому на HTML-странице могут одновременно присутствовать и список, и таблица с номером 1.
Ссылки на данные из других таблиц Google
Google Таблицы – Пример ссылки на ячейку на другом листе
Если у вас есть несколько Google таблиц (или несколько листов в электронной таблице), вы можете ссылаться на ячейки с других листов (или на другую таблицу). Всякий раз, когда вы обновляете исходный лист или таблицу, значение в итоговом листе будет обновляться автоматически. Пример такой функции будет выглядеть так:
« Лист2 » — это имя листа с данными, а « B5 » — это ячейка, на которую вы хотите сослаться. Между ними ставится восклицательный знак. Если вы хотите ссылаться на данные из совершенно другой таблицы, вы должны использовать функцию IMPORTRANGE , например:
Условное форматирование
Google Таблицы – Условное форматирование
Условное форматирование позволяет изменить внешний вид ячейки на основе данных, содержащихся в ней. Вы можете получить доступ к этой функции, нажав « Формат » на панели инструментов, а затем выбрав команду « Условное форматирование ». В панели, которая открывается справа, вы можете настроить свои параметры. Например, вы можете выделить ячейку (или ячейки) в зеленый цвет, если число, которое они содержат, больше нуля.
Существует также функция IF , которая технически не является частью функции условного форматирования. Вы можете использовать ее для добавления определенного значения в отдельную ячейку, когда значение в активной ячейке является конкретным числом:
Таким образом, в этом примере, если значение ячейки D2 больше или равно 105 , вы можете автоматически установить значение текущей ячейки равным 25 . И показать 0 , если нет. Конечно, это простой пример, но с данной функцией многое можно сделать.
Вставка Google таблиц на веб-сайт
Google Таблицы – Публикация Google Таблицы на веб-сайте
Если вы создали расписание или список в Google Таблицах, которыми хотите поделиться с другими, но вам нужно дополнить его другой информацией, которая находится на вашем блоге или на веб-сайте, вы можете вставлять Google таблицы на веб-страницы.
Все, что вам нужно сделать, это перейти к Файл > Опубликовать в Интернете . Оттуда перейдите на вкладку « Встроить », а затем выберите, следует ли публиковать всю электронную таблицу Google или только конкретный лист. После этого просто скопируйте и вставьте код iFrame на свою веб-страницу.