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

Добавил пользователь Алексей Ф.
Обновлено: 04.10.2024

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

Знакомимся с Google Docs

Основное преимущество Google Docs — возможность совместной работы в режиме онлайн, просмотра изменений, сделанных каждым участником, и автоматического сохранения актуальной версии. Особенно полезны для работы с данными Google Spreadsheets, или Google Таблицы — аналог приложения Excel. Google Docs могут пригодиться вам в следующих целях:

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

Все данные в примерах ниже вымышлены.

Удалить неиспользуемые строки на каждой вкладке (по умолчанию создается тысяча строк — если у вас на вкладке сейчас используется 200, удалите лишние 800, а при необходимости просто добавьте нужное количество) и столбцы (аналогично).
Оптимизировать количество вкладок (если есть несколько вкладок с маленькими таблицами или списками — попробуйте объединить их в одну).
Если есть формулы поиска данных, например ВПР/VLOOKUP, ИНДЕКС/INDEX, ПОИСКПОЗ/MATCH и другие, попробуйте сохранить часть формул как значения (если не нужно будет эти значения обновлять). Например, если у вас подтягиваются данные за много месяцев с помощью VLOOKUP — оставляйте текущий месяц формулами, а остальные данные сохраняйте как значения.
Не заливать строки/столбцы цветом целиком (и вообще стараться избегать излишнего форматирования).
Проверить, нет ли условного форматирования на (излишне) большом диапазоне ячеек.
Не ставить фильтр на все столбцы.
Очистить примечания, если их много и они не нужны
Выяснить, нет ли проверки данных на большом диапазоне ячеек.

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

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

Если ваша задача — только вычислить количество уникальных элементов в списке, понадобится функция COUNTUNIQUE. Она работает аналогично, но возвращает лишь количество уникальных элементов.Но что, если исходный список будет со временем меняться (то есть к нему станут добавляться новые строки)? Не менять же формулу каждый раз. Решение есть.Чтобы функция UNIQUE автоматически обновляла список уникальных значений при обновлении исходного списка (а COUNTUNIQUE, соответственно, обновляла количество), в качестве аргумента укажите не диапазон A2:A14, а диапазон A2:A.

Ставим ссылки автоматически. Функция HYPERLINK (ГИПЕРССЫЛКА)

Функция HYPERLINK (ГИПЕРССЫЛКА) возвращает ссылку на страницу в сети. Ее первый аргумент — собственно ссылка (записывается в кавычках), второй — текст, который будет отображаться в ячейке (тоже в кавычках):

Результатом работы этой формулы будет ссылка. При выделении ячейки с ссылкой сам адрес появится во всплывающей ячейке:

источник

Эта формула проверяет, на каком языке указано название книги в ячейке A2. И если язык английский, то выдает ссылку на поиск этого названия в Амазоне, а иначе — на поиск его же в Озоне (формально — если текст не на английском, по факту это означает русский в моем файле).

Функция IMPORTRANGE (перенос данных из файла в файл)

В файл, в котором вы введете эту формулу, будет отображаться диапазон A1:CM500 с Листа 1 из файла, который находится по соответствующей ссылке. Кроме того, ссылки на файл и диапазон можно вводить не в саму формулу, а в ячейки вашего документа и ссылаться на них. Так, если в ячейку A2 вы введете ссылку на документ, из которого нужно загрузить данные, а в ячейку B2 — ссылку на лист и диапазон, то загружать данные можно будет с помощью следующей формулы:=IMPORTRANGE(A2;B2)

Функция MATCH (сравнение двух списков)

MATCH (искомое_значение; список; точный поиск);
искомое значение — то, что мы ищем (обычно ячейка с текстом), список — диапазон, в котором мы ищем. Последний аргумент должен равняться нулю, если вы хотите вести точный поиск (обычно именно это и нужно).

Переводим текст прямо в Google Таблице: функция GOOGLETRANSLATE

text — это текст, который нужно переводить; можно, конечно, взять текст в кавычки и записать прямо в формулу, а можно сослаться на ячейку, в которой он содержится;
[source_language] — язык, с которого мы переводим;
[target_language] — соответственно, язык, на который мы переводим.

А как быть, если мы все-таки хотим переводить не только на английский, но и на другие языки? И при этом не хотим каждый раз указывать язык исходника вручную? Тут пригодится функция DETECTLANGUAGE. У нее единственный аргумент — текст, язык которого нужно определить:

Как и с любой другой функцией, прелесть здесь в автоматизации. Можно быстро поменять текст или язык; быстро перевести одну фразу на десять языков и так дале. Конечно, мы понимаем, что это текст онлайн-переводчика — качество будет соответствующим. Quel merveilleux tableau!

Онлайн-сервис Google бесплатный.
Удобно реализована совместная работа — не надо тысячу раз пересылать друг другу файлы.
Изменения сохраняются автоматически.
Есть история версий — при необходимости можно откатить на тот момент, с которого все пошло не так.
Можно настроить автоматический импорт данных из сторонних источников — сервисов аналитики, рекламных кабинетов, кол-трекинга и т. д.

Начало работы с Google Sheets

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

Google Sheets поддерживает таблицы Excel. Открыть такой файл в сервисе можно двумя способами:

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

Панель инструментов Google Sheets

В сервисе Google этот элементы выглядит проще, чем в Excel. Однако, если вы привыкли работать с программой MS Office, понадобиться время, чтобы привыкнуть.

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

Отменить и повторить последнее действие, распечатать документ, скопировать форматирование. Последний инструмент удобно использовать, когда нужно применить одинаковое форматирование к нескольким ячейкам. Выберите ячейку, кликните по инструменту на панели, а затем — по ячейке, которую надо отформатировать.
Изменить масштаб. Делает таблицу крупнее или мельче, диапазон — от 50 до 200 %.
Изменить формат данных в ячейках — выбрать денежный или процентный, увеличить и уменьшить количество знаков после запятой в числовом, выбрать другие форматы в выпадающем меню.
Шрифт.
Размер шрифта.
Форматирование текста — начертание, цвет текста и фона.
Форматирование ячеек — заливка цветом, границы, объединение.
Выравнивание текста — по-горизонтали, по-вертикали, настройки переноса и поворота.
Прочие инструменты:

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

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

Операции с ячейками, строками и столбцами

Как закрепить строки

Как перемещать элементы таблицы

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

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

Совместный доступ

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

Как удалить и восстановить таблицу

Как редактировать Google Sheets

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

Как защитить данные от редактирования

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

Комментарии и примечания

Форматы данных

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

текст;
число;
процент;
финансы;
валюта;
дата;
время.

Условное форматирование данных

Укажите диапазон.
Задайте условия форматирования.
Настройте формат — цвет и начертание текста, заливку ячейки.

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

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

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

Фильтры и сортировка

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

Фильтры скрывают из таблицы данные, которые сейчас не нужны. Это удобно, когда работаешь с большими массивами — посмотреть публикации по отдельной площадке или типу контента в объемном контент-плане, проанализировать данные по достижению одной цели в аналитическом отчете.

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

Проверка данных

В правом верхнем углу ячеек с неверными данными появятся красные метки, при наведении на которые появляется окно с пояснениями.

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

Сводные таблицы

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

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

Мы делаем ставку на омниканальность – занимаемся комплексным продвижением бизнеса в интернете. Подробнее

Для визуализации данных существуют более функциональные и удобные инструменты — Google Data Studio, Power BI и другие. Однако иногда бывает полезно добавить диаграмму или график прямо в таблицу, чтобы наглядно представить данные.

Посмотрим, что еще мы можем сделать с диаграммой:

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

Настроек много, но все они интуитивно понятны.

Созданные в Google Sheets диаграммы можно сохранить как изображение и опубликовать на сайте, встроив в страницу код. Для этого откройте выпадающее меню в правом верхнем углу диаграммы и выберите соответствующие пункты.

Работа с функциями

Все функции вводятся по одному принципу:

Рассмотрим на примере простой функции, которую часто используют SEO-шники и специалисты по контекстной рекламе — ДЛСТР — вычисляет длину строки.

Подробно описывать каждую функцию не будем — их около 400. Скажем лишь, что в с помощью формул здесь можно сделать все тоже самое, что и в Excel. Конечно, там есть формулы, которых нет в сервисе Google, и наоборот, но таких не много. Например, функции GOOGLETRANSLATE, которая переводит текст с одного языка на другой, в Excel нет.

О полезных для интернет-маркетологов функций электронных таблиц MS Office мы уже писали, все они отлично работают в сервисе Google. Принцип работы этих функций тот же, но может отличаться синтаксис. Список всех формул Google Sheets с описанием и синтаксисом есть в справочнике.

Интеграция с другими инструментами Google

Неоспоримое преимущество Google Таблиц для интернет-маркетологов в том, что они могут взаимодействовать и обмениваться данными с другими сервисами Google. Посмотрим на примере двух продуктов — Google Формы и Google Analytics.

Взаимодействие с Google Forms

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

Интеграция с Google Analytics

С полученными данными можно работать — сортировать, фильтровать, обрабатывать с помощью формул и отображать в сводных таблицах.

Полезные дополнения Google Sheets

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

Ctrl + пробел — выделить столбец;
Shift + пробел — выделить строку;
Ctrl + Enter / D / R — заполнить диапазон / вниз / вправо;
Ctrl + K — вставить ссылку;
Home — перейти в начало строки;
Ctrl + Home — перейти в начало листа;
Ctrl + Backspace — перейти к активной ячейке.

Вот мы и рассмотрели ключевые возможности Google Sheets.

Активно пользуетесь Таблицами и знаете хитрости и лайфхаки, которые ускоряют и упрощают работу? Будет здорово, если вы поделитесь ими в комментариях и поможете другим работать эффективнее ;-)

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

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

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

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

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

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

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

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

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

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

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

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

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

Доступ к специальной вставке в Google Таблицах

Напоминаем, что вы можете скопировать данные в Google Таблицы несколькими простыми способами. Как только вы это сделаете, вы получите доступ к специальной вставке.

Чтобы скопировать данные:

Чтобы открыть Специальную вставку:

Вставить специальные параметры в Google Таблицы

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

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

Вставить только значения

Вставить только формат

Вставить все, кроме границ

Вставить только ширину столбца

Вставить только формулу

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

Вставить только проверку данных

Вставить только условное форматирование

Вставить транспонированный

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

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