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

Обновлено: 25.04.2024

Основное преимущество 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, а другие работают в Microsoft Excel. Поэтому иногда возникает потребность перенести данные из веб-сервиса непосредственно в программу.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  
• Сеет встретит сдержит свяжет погадает погасит запутает развлекает распишет
  
• Связала яйца раба
  
• Как связать сумку из полипропиленовых ниток
  
• Как связать топор
  
• Связать юбку зигзаг спицами