Как работать в гугл таблицах

Google-таблицы: получение данных с других веб-сайтов — узнаем курс рубля, температуру, последние новости, заголовки H1 и др. (+ несколько полезных примеров, которые «сложно» провернуть в Excel)

Доброго времени всем!

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

Excel, конечно, вещь хорошая, но для некоторых задач его не совсем удобно использовать (а то и вовсе невозможно). И в этом случае весьма неплохим выбором может стать Google-таблица (тем паче, что она бесплатна и некоторые вещи здесь можно решать без знания скриптов!).👌

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

Ремарка: чтобы создать свою первую «Гугл-табличку» — перейдите на сервис 📌Google Drive, зарегистрируйтесь (или войдите под своим логином), кликните правой кнопкой мыши по свободному месту диска, и выберите функцию «Создать таблицу» . См. скрин ниже.

Создать пустую таблицу (Google Drive)

Примеры работ с Google-таблицей

Импорт в табличку нужных данных с сайтов

Начать заметку я хочу с одной довольно замечательной формулы: IMPORTXML(ссылка; запрос-xpath) . Она позволяет получать практически любые данных с открытых веб-страничек в сети Интернет. Например, вы можете подгрузить в свою таблицу:

• курс рубля к доллару (и других валют);
• последние новости;
• списки фильмов/музыки;
• данные из других таблиц и т.д. и т.п.

📌 Как этим пользоваться (на примере):

1. вставляете в одну из ячеек URL-адрес веб-странички, на которой есть нужная вам информация (например, я добавил в A1 адрес: https://rambler.ru/ );
2. во вторую ячейку (в моем случае A2 ) нужно добавить запрос Xpath (его можно скопировать в браузере, чуть ниже покажу как);
3. теперь в одной из ячеек (например A3 ) используем формулу = IMPORTXML ( A1 ; A2 ) и получаем в ней то, что было на указанной странице и пути Xpath! В своем примере я просто получил курс рубля к доллару с Рамблера (и, разумеется, дальше этот курс будет обновляться автоматически, и вы эту информацию можете использовать для расчетов) .
4. Удобно?!

Это число Google-таблица подгрузила автоматически (и будет это делать регулярно раз в час!)

📌 Примечание : как узнать путь Xpath (в моем примере //*[@id=»main»]/div[2]/div/div/div/div/div[3]/div/div[2]/div/a[1]/span[2])

Для этого откройте веб-страницу в браузере Chrome и кликните правой кнопкой мыши по нужному элементу (цифре / заголовку и т.д.), и выберите в меню «Просмотреть код» .

Затем появится консоль разработчика — в ней кликните еще раз ПКМ по строке, на которую курсор установится автоматически, и выберите в меню «Copy Xpath» . Все, теперь этот элемент вы можете вставить в нужную вам ячейку таблицы (по Ctrl+V).

Получаем запрос Xpath в Chrome

📌 Еще один пример: получение списка фильмов, музыки и прочих данных из однотипных блоков

Для примера я взял URL «Кинопоиска» со списком из самых новых фильмов. Но тут больше всего интересен будет Xpath запрос:

Чтобы повторить что-то подобное : вам нужно на веб-странице найти повторяющийся div-блок (например, в котором заключены заголовки фильмов) и посмотреть его класс (делается это через консоль разработчика в Chrome || аналогично, как мы сделали чуть выше) . Далее заменить мой класс в примере на свой. В результате : получите в таблице целый список названий.

См. пару скринов ниже. 👇

Список новинок получен в табличку!

Скриншот с сайта Kinopoisk — просмотр класса div блока

Для работы с финансами (курсами валют, акциями и пр.)

Информация носит справочный характер. Не принимайте торговых решений на основе работы этой формулы (во всяком случае — перепроверяйте ее работу!).

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

Для получения курсов более оперативно есть в «Гугл-таблицах» другая формула:

См. ниже несколько примеров. Подставив свой тикер (или валюту) — вы сможете относительно-оперативно узнавать ее курс (а значит и пересчитывать свои значения в таблицах 😉👇).

Как работает формула GOOGLEFINANCE

Перевод текста с иностранного языка на русский

Допустим вы импортировали в табличку какие-нибудь данные с иностранных сайтов. И было бы неплохо теперь все эти строки перевести. В этом плане Google-таблица отлично решает проблему!

1. пишем формулу вида: =GOOGLETRANSLATE(A9;»en»;»ru») . Вместо «en» и «ru» нужно указать язык оригинала и перевода, а A9 — это ячейка с иностранным текстом;
2. далее просто «растягиваете» (копируете) формулу на все ячейки, напротив которых требуется перевод. См. пример ниже. 👇

Переводим все строки в таблице

Замена (или удаление) ненужных символов (автоматически)

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

=REGEXREPLACE(A30 ;»[^0-9]+»;»»)

где A30 — ячейка с исходным текстом;

«[^0-9]+» — всё, что не относится к цифрам — обработать! (если бы было «[0-9]+» — обработать только цифры, остальное не трогать). Значок «^» — решает!

«» — если в кавычках ничего нет — менять любые символы, кроме цифр, будем на пустоту!

Несколько формул ниже позволят легко понять логику работы (и подстроить их под свою задачу. ).

Регулярными выражениями очищаем строку

Кстати, для удаления лишних пробелов удобно также использовать формулу: =СЖПРОБЕЛЫ(«text»)

Совместная работа с документами

С документами на Google Drive могут одновременно работать неск. человек! Это очень удобно, если (скажем) в вашу табличку заносят данные 3-4 пользователя, а вы сразу же видите результат расчета (анализа) их данных.

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

Кстати, с телефона документ тоже будет доступен! (и это большой плюс Google-таблице)

📌 В помощь (по теме)!

Совместная работа над документами (текстом, таблицами и пр.), или как создать общую папку в интернете — см. неск. способов

Получить ссылку на документ

Пользователи видят, кто и что редактирует.

Вставка картинок с других сайтов

Если вам нужно вставить картинку в конкретную ячейку (и чтобы она из нее никуда не «делась») — воспользуйтесь формулой = IMAGE() . В скобках потребуется лишь указать ссылку на эту картинку.

Обратите внимание, что я привел два варианта: в одном размеры будут подобраны автоматически, во втором- указываются вручную! 👇

Как работать в гугл таблицах

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

2. Добавление цветовой шкалы

Цветовая шкала позволяет визуально выделять максимальные и минимальные значения в таблице. Чтобы ее создать, нужно выбрать нужные ячейки, нажать «Формат» → «Условное форматирование» и в открывшейся справа панели инструментов задать параметры шкалы.

Итог будет выглядеть примерно так.

3. Защита данных

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

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

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

5. Интеграция с Google Формами

Ответы, собранные в Google Формах, можно проанализировать и визуализировать в Google Таблицах. Чтобы отправить данные, в Google Формах выберите «Ответы», а затем щелкните значок Google Таблиц, чтобы отправить туда существующие (и будущие) ответы.

6. Перенос диаграмм из Google Таблиц в Google Документы

Для этого в Google Документах нужно выбрать «Вставить» → «Диаграмма» → «Из таблицы». Если в таблицу было внесено изменение, и диаграмма стала выглядеть иначе, ее можно не переносить в документ второй раз — для этого достаточно нажать на «Обновить».

7. Импорт данных с сайта или RSS-ленты

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

для импорта HTML-таблиц и списков; для импорта RSS-записей; для импорта CSV-файлов; для импорта пользовательского раздела веб-страницы, который можно идентифицировать с помощью Xpath.

Так выглядит импорт информации о всех запусках SpaceX из Википедии.

8. Разделение имен и других данных

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

9. Проверка правильности написания email-адресов

Если вы хотите убедиться, что все внесенные в документ адреса электронной почты обладают правильной структурой, то в Google Таблицах предусмотрена соответствующая проверка. Она не сможет показать, будут ли доставлены письма, но выделит все адреса с ошибками (например, пропущенными «@» или «.com»).

10. Создание QR-кодов

QR-коды — это простой способ отслеживать такие действия, как регистрация людей на каком-либо мероприятии. Их можно сгенерировать прямо в Google Таблицах.

Как Создать Вашу Первую Электронную Таблицу в Google Таблицах

Andrew Childress Apr 23, 2018

Russian (Pусский) translation by Andrey Rybin (you can also view the original English article)

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

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

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

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

Почему Стоит Использовать Google Таблицы?

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

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

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

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

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

Смотрите и Учитесь

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

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

Как Начать Использовать Google Таблицы

Чтобы начать пользоваться Google Таблицами, вам нужно создать бесплатный Google аккаунт. Если вы уже пользуетесь Gmail, например, то у вас уже есть доступ к Google Таблицам.

Чтобы открыть Таблицы, я всегда перехожу к домашней странице Google Диска. Возможно он вас уже знаком и вы используете его как хранилище и для резервных копий. У Google есть целый набор браузер-инструментов таких как Документы и Таблицы, которые вы можете использовать прямо в браузере.

Вы можете создать новые таблицы где угодно, используя Google Диск и нажав на кнопку Создать, а далее выбрав Google Таблицы.

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

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

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

Как Создать Вашу Первую Формулу

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

Чтобы начать создавать вашу первую формулу, напишите знак (“=”) в ячейке. Затем, вы можете начать печатать формулу. Вы также можете начать печатать формулу в верхней строке, или ссылаться на другие ячейки, чтобы использовать их данные, например так = А1+В1.

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

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

• Элементарные математические операции , такие как +, -, *, и /
• Формулы для изменения текста, такие как =UPPER, =LOWER, или =TRIM (Испробуйте их на тексте, чтобы увидеть эффект!)
• Функции для работы с датой и временем, такие как =TODAY, чтобы напечатать сегодняшнюю дату, или =MONTH чтобы напечатать месяц для конкретной даты.

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

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

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

Как Делиться Таблицами с Другими

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

Чтобы открыть доступ другим, нажмите на голубую кнопку НАСТРОЙКИ ДОСТУПА, которая расположена в верхнем левом углу таблиц. Теперь вы можете просто добавить электронные адреса тех, кому бы вы хотели предоставить доступ для совместной работы.

Чтобы открыть доступ другим, нажмите на голубую кнопку НАСТРОЙКИ ДОСТУПА, которая расположена в верхнем левом углу таблиц. Теперь вы можете просто добавить электронные адреса тех, кому бы вы хотели предоставить доступ для совместной работы.

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

Посмотрите это полной руководство о том, как вести совместную работу с Google Таблицами:

Как Пользоваться Простым Форматирование в Электронных Таблицах Google

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

Вот несколько основных инструментов форматирования, которые вы возможно захотите использовать:

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

Все эти инструменты находятся в панели инструментов, над основным документом с данными. Попробуйте их применить их к вашим данным в таблице.

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

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

Повторяем и Продолжаем Обучение

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

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

Как вы используете Google Таблицы? Какой был первый ваш проект, благодаря которому вы увидели их потенциал? Расскажите как вы используете Google Табилцы в комментариях ниже.

9 удивительных возможностей Google Таблиц

В закладки

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

У Google Sheet есть два явных достоинства: он бесплатен и работает через браузер/мобильные приложения (App Store/Google Play). Многие воспринимают его просто как удобное средство для просмотра табличек, присланных по почте. Но такая точка зрения в корне неверна.

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

1. Совместная работа над документами

В Google Sheet очень просто организовать совместную работу над файлом. Надо нажать на кнопку Настройки доступа, ввести email нужного человека и выставить для него права: редактирование, комментирование или чтение.

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

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

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

Инструкция по блокировке ячеек

1. Выделяем ячейки и нажимаем Защитить лист.

2. Даем имя диапазону и нажимаем на кнопку Задать разрешения.

3. Указываем, кто имеет право редактировать диапазон.

Еще в Google Sheets есть интересный способ указать пользователю на конкретное место в документе. Не надо писать человеку письмо “Привет, Василий! Проверь пожалуйста данные 125 ячейки 17 столбца 30 листа счета №343”. Надо просто нажать на эту ячейку правой кнопкой мыши, выбрать пункт меню “Добавить комментарий” и набрать “+ вопрос к нему”.

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

К слову, в Excel тоже можно совместно работать над документами через OneDrive и Office Online. Но стоит это удовольствие от 299 рублей в месяц.

2. Формулы

В Google Sheet 371 функция! Здесь их полный список с описаниями на русском языке. Они распределены по 15 разделам:

Для справки, в Excel их на сто функций больше. Если что-то очень нужное, как раз из этой сотни, это не повод отказываться от Google Spreadsheet. Ведь можно создать функцию под себя!

Инструкция по созданию собственных функций

1. Открываем редактор скриптов:

2. Набираем код функции:

[jscript]
function c100wN(x) <
x = x*100*1.4;
return x;
>
[/jscript]

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

P.S. В Excel тоже можно создавать пользовательские функции.

3. Автоматизации

В этой сфере возможности предмета статьи поражают воображения. Автоматизировать можно практически все. Надо только немного подумать.

Мощнейший Google Apps Script (расширенная версия Java Script для работы с сервисами Google) может связать Sheets со следующими приложениями:

• Google Docs;
• Gmail;
• Google Translate;
• Google Forms;
• Google Sites;
• Google Translate;
• Google Calendar;
• Google Contacts;
• Google Groups;
• Google Maps.

Тему использования этого языка совместно с Google Таблицами на iphones.ru мы поднимали неоднократно:

• 4 лайфхака Gmail и Google Apps Script
• Автоматизируем Google Таблицы на Google Apps Script
• Google Apps Script для Docs в примерах
• Простые автоматизации для Google Drive
• Планируем жизнь с Google Calendar и Google Apps Script
• Как правильно делать автоматизированные рассылки в Gmail

4. Коллекция дополнений

Если вам не хватает каких-то возможностей в Google Sheet, то можно попробовать найти что-нужно среди дополнений. Страница с доступными аддонами открывается из меню Дополнения -> Установить дополнения.

Я бы рекомендовала присмотреться к следующим дополнениям:

• Styles;
• Remove Blank Words;
• Advanced Find And Replace;
• Translate My Sheet;
• Fitbit Activity Importer;
• Magic JSON;
• Drive Links Exporter.

5. Google Forms

Предположим, что нам надо сделать онлайн-опрос и собрать данные в таблицу для последующей обработки. Есть очень быстрый и бесплатный способ это сделать.

Организуем опрос с помощью сервисов Google

1. Инструменты -> Создать форму

2. Заполняем информацию, придумываем вопросы.

3. Получаем ссылку на готовый опрос.

5. Смотрим свой ответ на листе в таблице.

Еще форму можно получить html-код формы и поставить ее на сайт.

6. Интеграция с Google Drive

Для хранения таблиц Google Sheets предоставляется 15 Гб места бесплатно. На том как крут этот сервис мы останавливаться не будем, а просто расскажем о беcценной фиче под названием ОФФЛАЙН РЕЖИМ. Он доступен для текстовых документов, таблиц и презентаций.

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

Стоит также уточнить, что использование Google Drive клиента на компьютере не позволяет работать с таблицами офлайн. Файлы Google Sheet хранятся на компьютере в виде файлов-ссылок, при открытии которых запускается браузер.

7. Горячие клавиши

У Google Sheet есть несколько десятков сочетаний клавиш практически для всех действий. Полные списки для PC, Mac, Chromebook и Android можно посмотреть здесь.

8. Экспорт данных

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

9. Мгновенная вставка картинок из интернета

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

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

Опрос: Что вы такого делаете в Excel, что не можете делать в Google Sheet?

В закладки