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

Обновлено: 23.09.2022

Основное преимущество Google Docs - возможность совместной работы в режиме "онлайн", просмотра изменений,
сделанных каждым участником, и автоматическим сохранением актуальной версии (не будет больше обидных вылетов и выключений компьютера без сохраненного текста или документа!).

Особенно полезны Google Spreadsheets, или Google таблицы - аналог приложения Excel.

Они могут пригодиться вам для:

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

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

Функция IMPORTRANGE

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

Для чего она может пригодиться?

Например, вам нужны актуальные данные из файла ваших коллег;

или вы хотите обрабатывать данные из файла, к которому у вас есть доступ "Только для просмотра";

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

Эта функция позволяет получить копию диапазона из другой Google таблицы. Форматирование при этом не переносится - только данные.

Синтаксис функции следующий:

spreadsheet_key (ключ_таблицы) – последовательность символов в атрибуте "key left">Иначе говоря, ключ таблицы - это последовательность символов в ссылке на таблицы после "spreadsheets/d/"

Вместо ключа таблицы вы можете использовать полную ссылку на документ:

В файле, в котором вы введете эту формулу, будет отображаться диапазон A1:CM500 с Листа1 из файла, который находится по соответствующей ссылке.

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

Так, если в ячейку A2 вы введете ссылку на документ, из которого нужно загрузить данные, а в ячейку B2 - ссылку на лист и диапазон, то загружать данные можно будет с помощью следующей формулы:



Видео:

IMPORTRANGE как аргумент другой функции

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

Рассмотрим простой пример - среднее значение по продажам из диапазона, находящегося в другом документе.


Это исходный документ. Пусть данные будут добавляться и нам нужно среднее по продажам 2016 (то есть от ячейки D2 и до упора вниз)

Сначала импортируем этот диапазон:

А потом используем это как аргумент функции СРЗНАЧ (AVERAGE):


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

Функция ВПР (VLOOKUP)

Это функция - аналог функции ВПР в Excel. Она необходима для переноса данных из одной таблицы в другую.

У нее следующие аргументы:

VLOOKUP (искомое значение; таблица; номер столбца; интервальный_просмотр)

искомое значение - это то значение, которое мы будем искать в другой таблице.

Например, в отдельном файле у вас есть стандартная ставка перевода книг с английского - по трем категориям сложности.

А в таблице с текущими проектами вы просто указываете категорию сложности (это и будет искомое значение), по которому функцией ВПР (VLOOKUP) подтягиваете ставку.

Таблица - это диапазон данных, из которого вы подтягиваете информацию.

Аргумент "номер столбца" определяет , из какого столбца ТАБЛИЦЫ (а не листа! это важно) вы будете брать данные.
Интервальный_просмотр обычно равен нулю - в таком случае будет вестись точный, а не приблизительный поиск.

Функция ПОИСКПОЗ (MATCH)

Функция ПОИСКЗПОЗ (в английской версии Excel и Google Таблиц она называется MATCH) позволяет определить порядковый номер элемента (обычно - текста, записанного в ячейке) в определенном списке.

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

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

Синтаксис функции следующий:

MATCH (искомое_значение; список; точный поиск)

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


Сочетание функций ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH)

К сожалению, функция ВПР (VLOOKUP) не работает, когда искомые значения в исходной таблице находятся не в первом столбце.

Но вы можете воспользоваться сочетанием функций ПОИСКПОЗ (MATCH - обсуждалась ранее) и ИНДЕКС (INDEX).

Функция ИНДЕКС возвращает элемент из списка по его порядковому номеру.

А порядковый номер вы определяете с помощью MATCH.


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

Чтобы использовать сервисы Google на работе или в учебном заведении более эффективно, подпишитесь на бесплатную пробную версию Google Workspace .

Из этого раздела вы узнаете:

6.1. Как предоставить доступ к таблице

Excel:
предоставление доступа к книге

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

Excel 2013

Excel 2010

Как предоставить доступ отдельным пользователям


Как предоставить доступ по ссылке

  1. Откройте файл и нажмите Настройки доступа.
  2. В разделе "Получить ссылку" нажмите Скопироватьссылку.
  3. Чтобы изменить уровень разрешений, нажмите Изменить, выберите Читатель, Комментатор или Редактор.
  4. Скопируйте ссылку и вставьте ее в письмо или опубликуйте в Интернете.

6.2. Как добавлять комментарии и назначать задачи

Excel:
добавление комментариев

Таблицы:
добавление комментариев и назначение задач

Excel 2013

Excel 2010

  1. В Документах, Таблицах или Презентациях выделите текст, который хотите прокомментировать.
  2. Нажмите "Добавить комментарий" .
  3. Введите комментарий в поле.
  4. Чтобы адресовать задачу или комментарий конкретному человеку, поставьте знак плюса (+) и укажите адрес электронной почты. Вы можете добавить любое количество пользователей. Указанные пользователи получат письмо с вашим комментарием и ссылкой на файл.
  5. Если вы хотите адресовать комментарий конкретному пользователю, установите флажок Назначить задачу.
  6. Нажмите Добавить или Назначить задачу.

6.3. Как написать соавторам по электронной почте

Excel:
отправка письма с прикрепленным файлом

Таблицы:
отправка электронного письма соавторам

Excel 2013

Excel 2010

6.4. Как вернуться к предыдущей версии или переименовать ее

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

Как посмотреть разные версии таблицы и восстановить нужную

  1. Выберите ФайлИстория версийСмотреть историю версий.
  2. Чтобы показать только версии, которым присвоены названия, установите переключатель Только версии с названиями в положение ВКЛ.
  3. Выберите время изменения, чтобы открыть предыдущую версию файла.

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


Как задать название версии

6.5. Как создавать фильтры и представления фильтров

Excel:
фильтрация данных

Таблицы:
фильтры и представления фильтров

Excel 2013

Excel 2010

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

Как создать фильтр

  1. Выделите диапазон ячеек.
  2. Нажмите ДанныеСоздать фильтр.
  3. Вверху диапазона нажмите Фильтр , чтобы увидеть доступные варианты.

Как создать представление фильтра

  1. Выберите ячейку с данными.
  2. Выберите ДанныеФильтрыСоздать новый фильтр.
  3. В заголовке столбца откройте список и выберите данные, к которым нужно применить фильтр, или найдите их с помощью поля поиска.
  4. Нажмите ОК.
  5. Введите название фильтра в поле Имя.
  6. После этого фильтр будет доступен в меню ДанныеФильтры.

6.6. Как защитить контент

Excel:
защита листа или книги

Таблицы:
защищенные листы и диапазоны

Excel 2013

Excel 2010

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

  1. Откройте таблицу и выберите Данные >Настроить защищенные листы и диапазоны.
  2. Нажмите + Добавить лист или диапазон.
  3. Выберите Диапазон или Лист в зависимости от того, какие данные вы хотите защитить.
  4. Нажмите Задать разрешения и укажите, кто может редактировать данные и нужно ли показывать предупреждение.
  5. Нажмите Готово.

6.7. Как ограничить доступ, запретить скачивание, печать или копирование

Excel:
шифрование с паролем

Таблицы:
ограничение доступа к данным

Excel 2013

Excel 2010

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

  1. В верхей части листа нажмите Настройки доступа.
  2. Внизу экрана нажмите Расширенные.
  3. Установите флажок Запретить скачивание, печать и копирование для пользователей с правами на комментирование и просмотр.
  4. Нажмите Готово.

6.8. Как указать срок действия доступа

Как настроить срок действия доступа

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

Как указать срок действия доступа

  1. В верхей части листа нажмите Настройки доступа.
  2. Настройте доступ, если ещё не сделали этого.
  3. Выберите Расширенные.
  4. Наведите указатель мыши на имя пользователя и нажмите "Открыть временно" .
  5. Укажите, когда нужно автоматически закрыть доступ.
  6. Нажмите Сохранить измененияГотово.

Примечание. Окончание периода доступа не может приходиться на текущую дату. Если вам требуется отозвать права пользователя на работу с файлом немедленно, просто закройте доступ.

6.9. Как узнать, кто просматривал таблицу

Как узнать, кто просматривал таблицу

Если у вас есть права на редактирование таблицы, вы можете посмотреть:

В правом верхнем углу нажмите "История изменений" .

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

В меню Файл выберите пункт Импорт:


Затем перейдите на вкладку Загрузка, нажмите на единственную кнопку Выберите файл на компьютере или перетащите иконку с файлом в это окно:


После того как вы выберете или перетащите файл, появится следующее окно импорта:


В первом случае (Создать таблицу) появится новый документ, во втором (Вставить лист(ы)) — новые листы в текущем документе, а в третьем все данные в текущем документе будут заменены на данные из импортированного файла Excel.

После того как вы выберете подходящий вариант, нажмите кнопку Импортировать.

Экспорт в Excel

Чтобы сохранить таблицу на локальный диск в формате Excel, проделайте следующий путь:


Книга сохранится на ваш локальный диск.

Обратите внимание, что при экспорте в Excel не сохранятся изображения, которые вы загрузили с помощью функции IMAGE, а результаты работы функций, которых нет в Excel, сохранятся — но как значения. Это касается, например, функций SPLIT, IMPORTRANGE и других функций импорта (IMPORTXML, IMPORTDATA, IMPORTHTML), UNIQUE и COUNTUNIQUE, QUERY, REGEXEXTRACT, GOOGLEFINANCE.

Функции SPARKLINE превратятся в обычные спарклайны Excel.

Отсутствующие в Excel функции при экспорте превращаются в ЕСЛИОШИБКА (IFERROR), где в качестве первого аргумента будет запись вида __xludf.DUMMYFUNCTION (функция), которая и выдаст ошибку в Excel, а в качестве второго аргумента — то значение, которое возвращала эта функция в момент экспорта.

Как перенести данные из Google Таблиц в Excel

Среди пользователей, создающих электронные таблицы, есть те, которые предпочитают онлайн-сервис от Google, а другие работают в Microsoft Excel. Поэтому иногда возникает потребность перенести данные из веб-сервиса непосредственно в программу.

Сделать это можно тремя разными методами.

Способ 1: Скачивание таблицы в формате XLSX

Если вы еще не знаете, то Google Таблицы поддерживают скачивание созданного документа в одном из шести форматов, среди которых есть и XLSX – фирменный формат Microsoft Excel. У этого способа есть свой минус, поскольку вы не сможете путем нажатия одной кнопки обновлять данные и просматривать, какие изменения были внесены онлайн. Однако если это и не нужно, скачивание будет самым простым и быстрым решением.

Открытие файла для переноса данных из Google Таблиц в Excel

Дождитесь окончания загрузки и откройте документ через программу.

Разрешение редактирования файла для переноса данных из Google Таблиц в Excel

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

Редактирование файла для переноса данных из Google Таблиц в Excel

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

Способ 2: Импорт запроса

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

Переход к настройкам для переноса данных из Google Таблиц в Excel

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

Открытие доступа по ссылке для переноса данных из Google Таблиц в Excel

В появившемся окне щелкните по ссылке «Разрешить доступ всем, у кого есть ссылка».

Копирование ссылки для переноса данных из Google Таблиц в Excel

Скопируйте полученную ссылку. Уровень доступа при этом не имеет значения, поэтому можете оставить стандартное значение – «Читатель».

Переход к импорту из интернета для переноса данных из Google Таблиц в Excel

Откройте пустой лист в Excel, перейдите на вкладку «Данные», разверните меню «Создать запрос», выберите «Из других источников» и щелкните по варианту «Из Интернета».

Вставка ссылки для переноса данных из Google Таблиц в Excel

Вставьте ссылку в появившемся окне.

Редактирование ссылки для переноса данных из Google Таблиц в Excel

Суть изменения заключается только в исправлении последней части на export?format=xlsx вместо edit?usp=sharing.

Выбор листа для переноса данных из Google Таблиц в Excel

После этого нажмите «ОК» и дождитесь появления нового окна. В нем выберите импорт нескольких листов или укажите конкретный.

Предпросмотр содержимого для переноса данных из Google Таблиц в Excel

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

Просмотр листа для переноса данных из Google Таблиц в Excel

Импорт прошел успешно, хоть и форматирование может немного отличаться от оригинального.

Обновление данных для переноса данных из Google Таблиц в Excel

Для проверки изменений на вкладке «Данные» используйте кнопку «Обновить все».

Просмотр обновления данных для переноса данных из Google Таблиц в Excel

Как видно, кто-то внес изменения в Google Таблицах, и они успешно подгрузились в Excel без необходимости снова импортировать всю таблицу.

Открытие настроек обновления для переноса данных из Google Таблиц в Excel

Для настройки времени автоматического обновления в меню «Обновить все» нажмите на «Свойства подключения».

Настройка обновления данных для переноса данных из Google Таблиц в Excel

Активируйте «Обновлять каждые» и укажите желаемое количество минут.

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

Способ 3: Импорт файла CSV

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

Переход к публикации для переноса данных из Google Таблиц в Excel

В Google Таблицах откройте меню «Файл», наведите курсор на «Открыть доступ» и щелкните по варианту «Опубликовать в интернете».

Выбор листа для публикации для переноса данных из Google Таблиц в Excel

Решите, будет ли это публикация всего документа или только одного листа.

Выбор формата публикации для переноса данных из Google Таблиц в Excel

Из следующего меню выберите вариант «Файл CSV».

Автоматическое обновление после изменений для переноса данных из Google Таблиц в Excel

Активируйте автоматическую публикацию после внесения изменений.

Публикация документа для переноса данных из Google Таблиц в Excel

По завершении нажмите кнопку «Начать публикацию».

Копирование ссылки после публикации для переноса данных из Google Таблиц в Excel

Скопируйте полученную ссылку и переходите в Excel.

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

На вкладке «Данные» выберите вариант создания запроса «Из файла» и «Из CSV».

Вставка ссылки на файл для переноса данных из Google Таблиц в Excel

Вместо имени файла в новом окне вставьте полученную ссылку и нажмите на «Открыть».

Выбор кодировки для переноса данных из Google Таблиц в Excel

Загрузка данных займет пару минут, после чего появится таблица, но вместо всех надписей будут «кракозябры». Для исправления разверните список «Источник файла» и выберите «Юникод (UTF-8)».

Подтверждение импорта для переноса данных из Google Таблиц в Excel

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

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

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

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

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

Способ 1: Функция IMPORTRANGE

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

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

Переход к настройке доступа для импорта данных в Google Таблицу

Откройте файл, который хотите перенести в другую таблицу, и щелкните по кнопке «Настройки доступа».

Сохранение названия таблицы для импорта данных в Google Таблицу

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

Предоставление доступа для импорта данных в Google Таблицу

Щелкните по ссылке «Разрешить доступ всем, у кого есть ссылка».

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

Ошибка доступа при импорте данных в Google Таблицу

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

Объявление функции для импорта данных в Google Таблицу

Выберите пустую клетку, которая послужит началом новой таблицы. Убедитесь в том, что пустых клеток вокруг нее достаточно для импорта данных, иначе действие завершится ошибкой и все равно придется стирать лишнее или выбирать другую ячейку. Напишите =IMPORTRANGE() для объявления соответствующей функции.

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

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

Вставка кода из ссылки для импорта данных в Google Таблицу

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

Синтаксис функции для импорта данных в Google Таблицу

Поставьте точку с запятой и снова добавьте кавычки.

Добавление названия листа для импорта данных в Google Таблицу

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

Проверка названия листа для импорта данных в Google Таблицу

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

Выбор диапазона данных для импорта данных в Google Таблицу

Далее определитесь с тем, какой диапазон данных хотите импортировать.

Ввод диапазона данных для импорта данных в Google Таблицу

Затем после восклицательного знака введите этот диапазон в формате A1:B4, где A1 - первая необходимая клетка сверху, а B4 – последняя справа снизу.

Проверка результата для импорта данных в Google Таблицу

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

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

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

Способ 2: Импорт через меню «Файл»

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

Переход к меню Файл для импорта данных в Google Таблицу

Откройте первую таблицу, вызовите меню «Файл» и в нем нажмите кнопку «Импортировать».

Выбор таблицы для импорта данных в Google Таблицу

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

Выбор метода вставки для импорта данных в Google Таблицу

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

Проверка нового листа для импорта данных в Google Таблицу

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

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

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

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