Гугл таблицы руководство скачать

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

СКАЧАТЬ ПАМЯТКУ

Шаг 1. Создайте таблицу

Чтобы создать таблицу, выполните следующие действия:

1. Откройте страницу sheets.google.com.
2. Нажмите на значок . Откроется новый документ.

Вы также можете создать таблицу, перейдя по ссылке sheets.google.com/create.

Шаг 2. Внесите изменения в таблицу

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

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

Шаг 3. Пригласите соавторов

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

На этой странице перечислены все учебные пособия Google Sheets по Statology.

*Операции
Как округлить до значащих цифр в Google Sheets
Как округлить до ближайших 5 или 10 в Google Sheets
Как округлить до ближайших 25 в Google Sheets
Как использовать проверку данных в Google Sheets
Как автоматически увеличивать значения в Google Sheets
Как подсчитать ячейки между двумя значениями в Google Sheets
Как использовать COUNTIF с несколькими диапазонами в Google Sheets
Как использовать COUNTIF из другого листа в Google Sheets
Как использовать СЧЁТЕСЛИ с ИЛИ в Google Sheets
Как использовать СЧЁТЕСЛИ с подстановочными знаками в Google Таблицах
Как использовать COUNTIFS с диапазоном дат в Google Sheets
Как использовать СУММЕСЛИМН с диапазоном дат в Google Таблицах
Как использовать СУММЕСЛИ с несколькими столбцами в Google Таблицах
Как использовать SUMIF в Google Таблицах
Как использовать СУММЕСЛИ с ИЛИ в Google Sheets
Как использовать SUMIF из другого листа в Google Sheets
Как использовать формулу БОЛЬШОЕ ЕСЛИ в Google Sheets
Как использовать формулу RANK IF в Google Sheets
Как использовать функцию УМНОЖИТЬ, ЕСЛИ в Google Sheets
Как сортировать по дате в Google Sheets
Как сортировать и игнорировать пробелы в Google Sheets
Как преобразовать дату в число в Google Sheets
Как подсчитать количество рабочих дней между датами в Google Sheets
Как написать описание дела в Google Sheets
Как использовать формулу СУММПРОИЗВ, ЕСЛИ в Google Sheets
Как суммировать каждую N-ю строку в Google Sheets
Как суммировать ячейки, если они не пустые в Google Sheets
Как суммировать по месяцам в Google Sheets
Как рассчитать среднее значение, если оно не пустое в Google Sheets
Как использовать СРЗНАЧЕСЛИМН в Google Таблицах
Как получить остаток в Google Sheets
Как использовать ISBLANK с диапазоном ячеек в Google Sheets
Как подсчитать ячейки с текстом в Google Sheets
Как считать символы в Google Sheets
Как интерполировать пропущенные значения в Google Sheets
Как использовать MAXIFS в Google Sheets
Как использовать несколько операторов IF в Google Sheets
Как ранжировать элементы по нескольким столбцам в Google Sheets
Как суммировать отфильтрованные строки в Google Sheets
Как подсчитать отфильтрованные строки в Google Sheets
Как усреднить отфильтрованные строки в Google Sheets
Как отфильтровать несколько столбцов в Google Sheets
Как фильтровать с другого листа в Google Таблицах
Как найти ближайшее значение в Google Sheets
Как найти 10% лучших значений в Google Sheets
Как умножить столбец на процент в Google Sheets
Как умножить столбец на константу в Google Sheets
Как отфильтровать ячейки, содержащие текст, в Google Sheets
Как фильтровать с помощью пользовательской формулы в Google Sheets
Как подсчитать количество вхождений в Google Sheets
Как заменить несколько значений в Google Sheets
Как подсчитать строки со значением в Google Sheets
Как сгруппировать данные по месяцам в Google Sheets
Как сгруппировать данные по неделям в Google Sheets
Как добавить месяцы к дате в Google Sheets
Как извлечь месяц из даты в Google Sheets
Как рассчитать количество месяцев между датами в Google Sheets
Как использовать биномиальное распределение в Google Sheets
Как заменить пустые ячейки нулем в Google Sheets
Как заменить значения #N/A в Google Sheets
Как заменить текст в Google Sheets
Как использовать ISERROR в Google Sheets
Как проверить, находится ли значение в диапазоне в Google Sheets
Как преобразовать название месяца и номер в Google Sheets
Как сравнить два столбца в Google Sheets
Как объединить функции ЕСЛИ и ИЛИ в Google Sheets
Как объединить функции ЕСЛИ и И в Google Sheets
Как рассчитать разницу между датами в Google Sheets
Как преобразовать метку времени в дату в Google Sheets
Как рассчитать разницу во времени в Google Sheets
Как создать таймер обратного отсчета в Google Sheets
Как сравнить даты в Google Sheets
Как фильтровать по диапазону дат в Google Sheets
Как преобразовать даты в строки в Google Sheets
Как преобразовать текст в числа в Google Sheets
Как фильтровать с несколькими условиями в Google Sheets
Как складывать и вычитать дни в Google Sheets
Как автоматически заполнять даты в Google Sheets
Как автоматически заполнять значения из другого листа в Google Sheets
Как заполнить серию в Google Sheets
Как создать кросс-таблицу в Google Sheets
Как сделать ссылку на другую вкладку в Google Sheets
Как рандомизировать список в Google Sheets
Как подсчитать уникальные значения в Google Sheets
Как создать уникальный список по нескольким столбцам в Google Sheets
Как выполнить обратную ВПР в Google Таблицах
Как использовать регистрозависимую функцию ВПР в Google Таблицах
Как использовать ЕСЛИОШИБКА с функцией ВПР в Google Таблицах
Как использовать ARRAYFORMULA с функцией VLOOKUP в Google Sheets
Как использовать INDEX MATCH с несколькими критериями в Google Sheets
Как использовать XLOOKUP в Google Таблицах
Как выбрать случайный образец в Google Sheets
Как выбрать случайное значение из списка в Google Sheets
Как извлечь подстроку в Google Sheets
Как извлечь числа из строки в Google Sheets
Как объединить столбцы в Google Sheets
Как рассчитать сложные проценты в Google Sheets
Как использовать функцию PMT в Google Sheets
Как использовать функцию ПОСЛЕДОВАТЕЛЬНОСТЬ в Google Sheets
Как суммировать несколько листов в Google Sheets
Как добавить вычисляемое поле в сводную таблицу в Google Sheets
Как отобразить процент от общего числа в сводной таблице в Google Sheets
Как создать сводную таблицу из нескольких листов в Google Sheets
Как форматировать сводные таблицы в Google Sheets
Как отсортировать сводную таблицу в Google Таблицах
Как выполнить анализ «что, если» в Google Sheets
Как удалить специальные символы в Google Sheets
Как игнорировать значения #N/A с формулами в Google Sheets
Как игнорировать пустые ячейки с формулами в Google Sheets
Как проверить, пуста ли ячейка в Google Sheets
Google Таблицы: условное форматирование с несколькими условиями
Таблицы Google: простая формула для «если содержит»
Таблицы Google: простая формула для «если не пусто»*

Описательная статистика
Как рассчитать описательную статистику в Google Sheets
Как рассчитать сводку из пяти чисел в Google Sheets
Как рассчитать среднее, медиану и моду в Google Sheets
Как рассчитать межквартильный диапазон в Google Sheets
Как рассчитать стандартное отклонение в Google Sheets
Как рассчитать средний диапазон в Google Sheets
Как рассчитать коэффициент вариации в Google Sheets
Как рассчитать асимметрию и эксцесс в Google Sheets
Как рассчитать стандартную ошибку среднего в Google Sheets
Как создать ковариационную матрицу в Google Sheets
Как создать корреляционную матрицу в Google Sheets
Как рассчитать скалярный продукт в Google Sheets
Как рассчитать ранговую корреляцию Спирмена в Google Sheets
Как вычислить квадратный корень и кубический корень в Google Sheets
Как нормализовать данные в Google Sheets
Как рассчитать частоты в Google Sheets
Как рассчитать ширину класса в Google Sheets
Как рассчитать среднее абсолютное отклонение в Google Таблицах
Как рассчитать среднее и стандартное отклонение в Google Sheets
Как рассчитать Z-баллы в Google Sheets
Как рассчитать децили в Google Sheets
Как рассчитать квинтили в Google Таблицах
Как рассчитать процентное изменение в Google Sheets
Как рассчитать совокупный процент в Google Sheets
Как рассчитать взвешенный процент в Google Sheets
Как найти выбросы в Google Sheets
Как найти строку наилучшего вписывания в Google Sheets
Как найти экспоненциальную скользящую среднюю в Google Sheets
Как рассчитать CAGR в Google Sheets
Как суммировать значения по категориям в Google Sheets
Как выполнить медианную функцию ЕСЛИ в Google Sheets
Как выполнить функцию стандартного отклонения ЕСЛИ в Google Sheets

Визуализации
Как создать блочную диаграмму в Google Sheets
Как создать пузырьковую диаграмму в Google Sheets
Как создать диаграмму с областями в Google Sheets
Как создать диаграмму Парето в Google Sheets
Как создать диаграмму квадрантов в Google Sheets
Как добавить среднюю линию на диаграмму в Google Sheets
Как создать точечную диаграмму с линиями в Google Sheets
Как добавить вторую ось Y в Google Sheets
Как добавить метки осей в Google Sheets
Как изменить масштаб оси в Google Sheets
Как построить несколько строк в Google Sheets
Как построить уравнение в Google Sheets
Как создать индикаторы выполнения в Google Sheets
Как создать круговую диаграмму в Google Sheets
Как создать точечный график в Google Sheets
Как создать калибровочную диаграмму в Google Sheets
Как создать кривую нормального распределения в Google Sheets
Как создать комбинированную диаграмму в Google Sheets
Как создать двойную гистограмму в Google Sheets
Как создать гистограмму с накоплением в Google Sheets
Как создать свечной график в Google Sheets
Как создать таблицу в Google Sheets
Как добавить планки погрешностей в диаграммы в Google Sheets
Как добавить метки к точкам диаграммы рассеяния в Google Sheets
Как добавить горизонтальную линию на диаграмму в Google Sheets
Как добавить вертикальную линию на диаграмму в Google Sheets
Как добавить линию тренда на диаграмму в Google Sheets
Как добавить несколько линий тренда на диаграмму в Google Sheets
Как создать диаграмму с несколькими диапазонами данных в Google Sheets

Проверка гипотез
Как выполнять t-тесты в Google Sheets
Как выполнить F-тест в Google Sheets
Как выполнить тест на нормальность в Google Sheets
Как найти P-значения в Google Sheets
Как найти критические значения в Google Sheets
Как рассчитать доверительные интервалы в Google Sheets

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

дисперсионный анализ
Однофакторный дисперсионный анализ в Google Sheets
Повторные измерения ANOVA в Google Sheets

Хи-квадрат Тесты
Хи-квадрат Проверка соответствия в Google Таблицах

Функции запроса
Запрос Google Sheets: как выбрать несколько столбцов
Запрос Google Sheets: выберите строки, содержащие строку
Запрос Google Sheets: как использовать «не равно» в запросе
Запрос Google Sheets: как игнорировать пустые ячейки в запросе
Запрос Google Sheets: как создать сводную таблицу
Запрос Google Sheets: как использовать Order By
Запрос Google Sheets: как использовать Group By
Запрос Google Sheets: как фильтровать по диапазону дат
Запрос Google Sheets: как использовать функцию TODAY() в запросе
Запрос Google Sheets: как использовать функцию COUNT
Запрос Google Sheets: как использовать функцию SUM
Запрос Google Sheets: как извлечь первые N значений из диапазона
Запрос Google Sheets: как вернуть только уникальные строки
Запрос Google Sheets: как сделать запрос из другого листа
Запрос Google Sheets: как сделать запрос из нескольких листов
Запрос Google Sheets: как использовать CONCAT с QUERY
Запрос Google Sheets: как использовать IMPORTRANGE с условиями
Запрос Google Sheets: используйте IMPORTRANGE с несколькими листами
Запрос Google Sheets: как сделать запрос, используя месяц
Запрос Google Sheets: как использовать ссылку на ячейку в формуле
Запрос Google Sheets: как использовать несколько критериев в запросе
Запрос Google Sheets: как использовать подстановочные знаки
Запрос Google Sheets: как использовать предложение Label

О книге «Google Таблицы. Это просто. Функции и приемы»

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

Произведение было опубликовано в 2018 году издательством Манн. На нашем сайте можно скачать книгу «Google Таблицы. Это просто. Функции и приемы» в формате fb2, rtf, epub, pdf, txt или читать онлайн. Здесь так же можно перед прочтением обратиться к отзывам читателей, уже знакомых с книгой, и узнать их мнение. В интернет-магазине нашего партнера вы можете купить и прочитать книгу в бумажном варианте.

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

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

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

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

3. Интеграция с другими инструментами от Google. Это тоже будет разобрано в одном из разделов ниже, но важно понимать, что такая особенность позволяет без каких-либо сложных настроек объединить несколько разных сервисов, связать их и облегчить работу.

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

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

Как создать Google Таблицу

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

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

Импорт файлов и данных

Давайте сразу разберемся с поддержкой электронных таблиц, созданных в других программах и хранящихся в виде файлов на вашем компьютере. Непосредственно на странице создания нового файла можете нажать на кнопку с папкой, чтобы перейти к загрузке существующего документа. Если уже открыли пустой лист Google Sheet, тогда разверните меню «Файл» и выберите пункт «Импорт». Вы можете добавить файлы формата XLSX и ODS. 

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

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

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

Основные элементы панели инструментов

Теперь давайте разберемся с тем, как редактировать Гугл Таблицы. Осуществляется это при помощи инструментов на верхней панели. Они практически точно повторяют те, что присутствуют в том же Excel и других текстовых редакторах, но имеют и свои особенности. Сначала я бы рекомендовал посмотреть на каждое выпадающее меню, кликнув поочередно по их названиям на панели сверху. Понятно, что при помощи раздела «Файл» осуществляется работа с самим документом, создание, распространение и экспорт. Основное внимание обратите на вкладки «Вставка» и «Данные». Там находятся кнопки, отвечающие за выполнение основных действий. С их помощью вставляются диаграммы, рисунки, дополнительные ячейки, осуществляется управление проверкой данных, их очистка и фильтрация. Об этом более детально еще пойдет речь ниже.

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

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

Операции с ячейками

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

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

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

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

Просмотр истории изменений ячейки

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

Подробнее: Как посмотреть историю изменений ячейки в Google Таблицах

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

Настройка общего доступа и защита листов

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

Подробнее: Общий доступ, защита листов и диапазонов в Google Таблицах

Сортировка и фильтрация данных

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

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

Создание раскрывающегося списка

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

Подробнее: Как создать раскрывающийся список в Google Таблицах

Полноценное формирование таблицы

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

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

Создание диаграммы

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

1. Сначала разверните меню «Вставка» и выберите в нем пункт «Диаграмма».

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

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

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

5. Откройте вкладку «Дополнительные» для изменения внешнего вида диаграммы. Задайте для нее фон, цвет границ и шрифт.

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

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

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

Закрепление столбцов и строк

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

Работа со ссылками

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

Подробнее: Объединение ссылок в Google Таблицах

Удаление пустых столбцов, пробелов и дубликатов

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

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

Конвертирование и отображение курсов валют

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

Подробнее: Как конвертировать валюту в Google Таблицах

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

Читайте также: Как импортировать текущий курс криптовалюты в Google Таблицы

Проверка электронных адресов

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

Подробнее: Как проверить допустимые адреса электронной почты в Google Таблицах

Перевод текста

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

Создание макроса с кнопкой

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

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

Подробнее: Как создать макрос в Google Таблицах и назначить для него кнопку

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

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

Подробнее: Создание Google Формы с выводом результатов в Google Таблицы

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

Подробнее: Задачи и напоминания в Google Таблицах

В рамках этой статьи были рассмотрены основные и самые интересные функции Google Sheets. Удачи! 

Плюсы и минусы

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

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

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

Даже если у вас дома или на работе резко отключат электричество, документ сохранится.

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

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

4. Логирование всех действий происходит автоматически.

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

5. Схожесть интерфейсов сервиса и Microsoft Excel.

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

1. Нужно знать формулы, иначе будет сложно работать с большими объемами данных (например, для нужд бухучета).
2. Нет дизайнерского элемента (но есть расширения для этих целей).
3. Нет традиционной службы поддержки.
4. Могут тормозить при операциях с большим количеством данных.
5. Ограниченное количество функций «из коробки».

Как создать новую таблицу в «Google Таблицах»

Вам понадобится аккаунт Google: пройдите авторизацию в нем и откройте сайт сервиса. Нажмите там кнопку «Открыть Google Таблицы»:

Попадаем на главный экран сервиса. Чтобы создать новую таблицу нажмите + (Пустой файл):

Чтобы указать название, кликните по полю «Новая таблица»:

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

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

Чтобы поделиться документом, нажмите кнопку «Настройки Доступа»:

Откроется окно предоставления доступа пользователям и группам. Чтобы дать доступ к документу другому пользователю, нажмите кнопку «Разрешить доступ всем, у кого есть ссылка»:

Затем выберите статус пользователя:

• Редактор может вносить любые изменения.
• Комментатор может только оставлять комментарии к разным элементам таблицы.
• Читатель может просматривать, без редактирования и отправки примечаний.

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

У меня есть готовый документ. Как его импортировать?

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

Необходимый документ можно добавить тремя способами:

1. Перетащить в окно.
2. Указать его на «Google Диске».
3. Загрузить прямо с устройства.

Как удалить таблицу целиком

Мы добавили необходимые данные и теперь можем работать со всем функциями сервиса. Чтобы стереть, нужно выбрать пункт «Удалить»:

Также вы можете открыть «Google Диск», найти необходимый файл и удалить его там:

Интерфейс: разбираемся с меню и функциями

Большая часть работы с таблицами в сервисе происходит при помощи верхнего меню:

Рассмотрим верхнее и второстепенное меню подробнее:

• «Файл». Отсюда можно создать или открыть уже существующую таблицу, импортировать ее, переименовать или отправить на печать. Здесь же можно получить доступ к глобальным настройкам.
• «Изменить». Стандартное меню откуда можно копировать и вставить любые элементы. Редактируйте примечания, строки, ячейки и столбцы.
• «Вид». Доступ к масштабированию страницы, кроме этого можно установить диапазоны, настроить формулы и задать отображение глобального меню.
• «Вставка». Все что касается вставки любых элементов, включая картинки, формы, листы и конечно — столбцы, ячейки и строки.
• «Формат». Здесь можно отформатировать любые данные, например текст. В этом меню настраивается размер, переносы, устанавливается выравнивание.

• «Данные». Управление данными, включая сортировку, удаление дублей, работа с фильтрами. Отсюда же можно быстро удалить лишние пробелы в документе.
• «Инструменты». В меню настраивается защита, происходит управление скриптами, устанавливаются оповещения. Здесь же можно создать многофункциональные формы.
• «Дополнения». Если вам не хватает какой-либо функции, вы можете установить расширения сторонних разработчиков: например, Autocrat, Doctopus, Flubaroo, Form Mule, CoRubrics.
• «Справка». Все справочные материалы и документация по сервису находятся здесь.

Быстрые функции

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

В частности, здесь доступно множество инструментов, позволяющих:

• Отменить совершенное действие.
• Повторить совершенное действие.
• Отправить на печать.
• Копировать форматирование.
• Редактировать форматирование.
• Управлять шрифтами.
• Редактировать границы.
• Выравнивать таблицу.
• Вставлять изображения.
• Добавлять комментарии.
• Фильтровать данные.
• Пользоваться математическими функциями.

Боковая панель

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

Рассмотрим их подробнее.

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

Google Keep. Быстрый доступ к сервису заметок Google:

Задачи. Создавайте единичные задачи или целые списки задач для себя или других участников проекта:

Контакты. Быстрый доступ к вашей контактной книге, привязанной к Google-аккаунту:

Карты. Быстрый доступ к Google Maps, если вам нужно уточнить какую либо информацию (адрес, маршрут) или сохранить место в списках.

Установить дополнение. Быстрый доступ в Google Workspace Marketplace, где можно установить дополнение для Google Sheets:

Строка функций

Здесь указываются формулы – чтобы представить ячейку в виде необходимой вам переменной. Например, вы можете указать формулу РАБДЕНЬ (WORKDAY): она рассчитывает дату через указанное количество рабочих дней, и в ячейке сразу будет нужное вам значение.

Еще примеры функций в финансовой категории:

• НАКОПДОХОД — возвращает начисленный процент по ценной бумаге.
• ПРПЛТ — возвращает сумму процента выплат по кредитованию с фиксированной ставкой.
• ПУО — вычисляет амортизацию активна (за любой период).
• СКИДКА (DISC) — возвращает ставку ценной бумаги исходя из ее цены.

Подробнее о строке функции мы расскажем чуть ниже, в разделе «Как работать с формулами и функциями в «Google Таблицах».

Рабочая книга

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

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

Действия с ячейками

Любой элемент можно отредактировать, удалить или переместить.

Чтобы отредактировать ячейку, кликните по ней правой кнопкой мыши. Появится контекстное меню, которое поможет вырезать / копировать / вставить значение внутрь ячейки:

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

1. Условное форматирование.
2. Настройка проверки данных.
3. Получение ссылки на конкретную ячейку.
4. Определение именований диапазона.
5. Защита диапазона.

Действия со столбцами

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

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

Для примера попробуем вставить столбец слева. Выделяем столбец:

Нажимаем на значок «▼»:

Вставляем столбец слева:

Столбец вставлен успешно:

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

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

Глобальные правила задаются через функцию условного форматирования. Чтобы получить к ней доступ, откройте меню «Формат» и выберите пункт «Условное форматирование»:

Указываем, к какому диапазону применять условие, и выбираем правило форматирования. Доступны следующие сценарии правил:

• Не содержит данных.
• Содержит данные.
• Текст содержит.
• Текст не содержит.
• Текст начинается с.
• Текст заканчивается.
• Текст в точности.
• Дата.
• Дата до.
• Дата после.
• Больше.
• Больше или равно.
• Меньше.
• Меньше или равно.
• Равно.
• Не равно.
• Между.
• Не между.
• Собственная формула.

Мы получили новое правило — обратите внимание на вкладку справа:

Правило можно заново отредактировать. Для этого кликните по иконке сформированного правила. Кроме этого, созданные правила можно перемещать перетаскиванием в этом же окне.

Как задать конкретный формат данных для чисел

«Google Таблицы» поддерживают несколько форматов данных для чисел. Вот несколько из них:

• Дата.
• Текст.
• Время.
• Числа.
• Проценты.
• Финансы.
• Валюта.

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

Для этих же целей можно использовать не только один цвет, но и градиентное заполнение:

Как работать с формулами и функциями

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

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

Сделаем простейший расчет. Допустим, что у нас есть таблица товаров и нам нужно посчитать стоимость:

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

Если синтаксис и значения корректны, то вы сразу увидите результат вычисления:

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

Как расширить функционал

Откройте меню «Расширение», кликните по строке «Дополнения» и выберите пункт «Установить дополнения»:

Все приложения, которые вы увидите в Google Workspace Marketplace, полностью совместимы с «Google Таблицами». Здесь есть решения для самых разных целей:

• Графика.
• Данные.
• Интеграции.
• API.
• Связь с аналитикой.
• Многое другое.

Просто выберите необходимое расширение, кликните по нему и нажмите кнопку «Установить»:

Теперь приложение Copy Down можно запускать как дополнение прямо в «Google Таблицах». Откройте раздел «Расширение» и найдите установленное приложение:

Появится предупреждение о создании новой формы. Его нужно принять, кликнув на кнопку Create me a form:

Активируем тумблер в активное положение:

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

Анализ данных

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

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

Как проверить данные

«Google Таблицы» значительно оптимизируют работу при взаимодействии с большим количеством данных. Проверка данных – одна из таких функций. Чтобы проверить форматирование данных в ячейках, откройте меню «Данные» и выберите пункт «Настроить проверку данных»:

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

• значение из выбранного диапазона;
• значение из списка;
• число,
• текст;
• дату;
• собственную формулу или флажок.

Создадим правило для целого столбца. Нажимаем «Настроить проверку данных» и выбираем необходимый нам столбец:

Устанавливаем тип правила. Для примера создадим правило «дата является допустимой»:

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

Допустим, у нас есть небольшой набор данных. Чтобы вставить диаграмму, сперва выделите необходимые элементы таблицы (с нужными данными) и откройте меню «Вставка»:

После клика по пункту «Диаграмма» произойдет магия и график построится автоматически:

Вы можете выбрать тип диаграммы (доступно более десятка разных вариантов), уточнить диапазон данных и настроить строки, столбцы, заготовки и ярлыки. Если диаграмма сформировалась некорректно, вы можете изменить данные в ней. Для этого во вкладке «Настройки» откройте раздел «Редактор диаграмм»:

Если вы хотите изменить дизайн диаграммы, откройте раздел «Дополнительные» в «Редакторе диаграмм». В нем доступны настройки сетки и деления, легенды, осей диаграммы. Главные визуальные параметры диаграммы вы можете отредактировать в разделе «Стиль диаграммы»:

Мы сделали столбчатый вариант диаграммы:

Я хочу защитить данные, как это сделать?

Делаем правый клик на нужном диапазоне, затем выбираем «Показать другие действия с ячейкой» и в открывшемся меню нажимаем «Защитить диапазон»:

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

Мы успешно защитили данные от редактирования:

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

При попытке указать данные в защищенных ячейках вы увидите предупреждение, что сделать это нельзя:

Как работать с фильтрами в «Google Таблице»

Режимы фильтрации. Часто требуется фильтрация определенных данных в таблице. Вы можете выполнить как элементарную фильтрацию в алфавитном порядке, так и сложную.

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

Выше мы показали режимы фильтрации. Теперь перейдем к простым вариантам.

Создать фильтр. На скриншоте выше показана команда для создания нового фильтра. Например, мы создали фильтр для всех выделенных данных:

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

Мы отфильтровали цифровые значения в порядке возрастания:

Аналогичным образом можем отфильтровать данные в обратном (убывающем) порядке:

Доступна фильтрация и сортировка по цвету заливки или цвету текста:

И конечно, есть фильтрация по условию. Доступны следующие сценарии:

1. Содержит / не содержит данные.
2. Текст содержит.
3. Текст не содержит.
4. Текст начинается с.
5. Текст заканчивается на.
6. Текст в точности.
7. Дата до.
8. Дата после.
9. Дата.
10. Больше.
11. Больше или равно.
12. Меньше.
13. Меньше или равно.
14. Равно.
15. Не равно.
16. Между.
17. Не между.
18. Ваша формула.

Фильтрация по значению отсортирует данные по выбранному вами значению. В нашем случае доступны два значения: 45 и 50. Выбираем всё или что-то одно:

Подробнее о том, как сортировать и фильтровать данные, написано в справке Google Sheets.

Самые популярные вопросы по использованию «Google Таблиц»

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

Как закрепить строку в «Google Таблицах»?

Откройте таблицу и пройдите в меню «Вид», кликните по пункту «Закрепить» и выберите необходимые значения.

Где посмотреть историю изменений и как понять, кто вносил изменения?

Откройте интересующую вас таблицу и кликните по ссылке «Последнее изменение…»:

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

Мы восстановили первоначальную версию таблицы и теперь можем работать в ней.

Можно ли перенести ячейку перетаскиванием?

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

После перетаскивания в пустой ячейке появится необходимое нам значение — 413.

Как добавить комментарий к ячейке?

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

Один из способов добавить комментарий в «Google Таблицах» — нажать кнопку «Показать историю комментариев» на главной панели «Google Таблиц» и выбрать пункт «Добавить комментарий»:

Попадаем в раздел комментариев и нажимаем кнопку «Добавить комментарий». Теперь кликаем по любой ячейке и оставляем необходимый комментарий:

В результате другой пользователь сможет увидеть такой комментарий и ответить на него:

Бонус: горячие клавиши «Google Таблиц»

«Google Таблицы» — по-настоящему удобный и функциональный инструмент, который ничем не уступает Excel или другим редакторам электронных таблиц. Чтобы сделать работу с «Google Таблицами» еще более быстрой и эффективной, мы собрали несколько важных горячих клавиш:

• Выделение столбца: Ctrl + Пробел.
• Выделение строки: Shift + Пробел.
• Замена: Ctrl + Shift + H.
• Показать все формулы: Ctrl + ~.
• Вставить формулу массива: Ctrl + Shift + Ввод.
• Свернуть формулу массива: Ctrl + E.
• Вставить URL: Ctrl + K.
• Вставить строки выше: Ctrl + Alt + Shift + =.
• Вставить строки ниже: Alt + I, затем W.

В «Google Таблицах» работают все горячие клавиши, к которым вы привыкли в Excel и других офисных программах.

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

Что такое Google Таблицы

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

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

Гугл-таблицы помогут в учёбе и работе. С этим инструментом работают студенты, фрилансеры, бухгалтеры, таргетологи, предприниматели и др. В гугл-таблицах удобно планировать финансы, составлять контент-план, вести бухгалтерский учёт, проводить маркетинговый анализ или строить прогнозы.

Как начать работу Google Таблицами

Чтобы начать работать с гугл-таблицей, нужно зайти на страницу онлайн-инструментов от Google, нажать на вкладку «Таблица» , а затем на кнопку «Открыть Google Таблицы».

В сервисах Google можно работать не только с электронными таблицами, но и создавать презентации, проводить опросы и совместно работать над текстовыми документами в программе Google Docs

После того как вы нажмёте на кнопку, система попросит вас авторизоваться. Получить доступ к сервису смогут только те, у кого зарегистрирован электронный ящик в гугл почте.

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

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

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

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

Рабочая область в Google Таблицы

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

При наведении курсора на каждый элемент система подскажет его функцию

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

Изменить название документа можно также во вкладке «Настройки доступа»

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

Такой же выпадающий список появится, если вы нажмёте на одну из пронумерованных ячеек в левой части экрана

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

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

Если вы захотите удалить гугл-таблицу, то зайдите во вкладку «Файл» и выберите пункт «Удалить». Восстановить удалённые данные можно будет в «шапке», нажав на кнопку «последнее изменение».

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

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

Чтобы закрепить строку, нужно нажать на вкладу «Вид» и кнопку «Закрепить», затем из выпадающего списка выбрать тот элемент, который хотите закрепить. После нажатия под строкой появится серая линия

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

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

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

Выберите нужный диапазон ячеек, нажмите правой кнопкой мыши и выберите вариант «Защитить диапазон»

Горячие клавиши. Для удобства работы со строками, ячейками и столбцами, воспользуйтесь специальными сочетаниями клавиш в в гугл-таблицах — «горячими клавишами». Когда вы их запомните и начнёте чаще применять, скорость работы с таблицами значительно повысится. Сервис от гугл поддерживает сотню сочетаний быстрых клавиш.

Перечислим наиболее распространённые:

• Сtrl + пробел — поможет выделить конкретный столбец;
• Shift + прoбел — это сочетание позволит вам выделить определённую строку;
• Сtrl + Enter — так вы сможете заполнить диапазон;
• Сtrl + К — удобный способ, чтобы вставить ссылку;

Эти горячие клавиши подходят только для компьютеров с системой Windows, для MacOS и Linux будут другие сочетания.

Форматы и форматирование в Google Таблицах

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

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

Также вы можете поменять внешний вид страницы, настроив выравнивания, переносы, определив цвет шрифта, интервалы или стиль границ. Если вы хотите, чтобы разные данные в ячейках выглядели по-разному и эти изменения происходили автоматически, воспользуйтесь правилами условного форматирования. Эти правила можно устанавливать для всей области таблицы, одной ячейки или конкретной строки. Настраивают правила во вкладке «Формат», нажав на кнопку «Условное форматирование» справа вы увидите дополнительное поле.

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

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

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

Вы также можете изменить параметры понравившейся вам темы, подобрав другие шрифты и цвета. Для этого нажмите на кнопку «Настройка» в разделе тем.

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

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

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

В сервисе настроить фильтрацию данных двумя способами:

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

Фильтрация и сортировка помогают работать с большим объёмом данных

Также во вкладке «Данные» вы найдёте полезный инструмент под названием «Настроить проверку данных». Он позволит понять на сколько соответствуют данные в ячейке заданным параметрам. Допустим, вы хотите, чтобы в одном диапазоне были только числа. Для проверки вам нужно нажать кнопку «Настроить проверку данных» в появившимся окне найти поле «Правила» и в выпадающем списке выбрать пункт «Числа».

После того, как настроите проверку данных, не забудьте нажать кнопку «Сохранить»

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

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

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

Визуализация данных в Google Таблицах

Сегодня можно найти немало сервисов для визуализации аналитических данных: Power BI, Modus, Tableau и др. Однако это можно сделать и в самих гугл-таблицах, создав столбчатый график или круговую диаграмму. Для этого нужно выделить рабочий диапазон данных, нажать на вкладку «Вставка» и выбрать пункт «Создать диаграмму».

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

Функции в Google Таблицах

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

Чтобы выбрать одну из функций, нажмите на кнопку «Вставка» и выберите пункт «Функция» 

Вставить функцию можно и вручную. Для этого нужно поставить знак «=», затем написать буквами название функции и заковав в скобки отметить диапазон данных, прописав номера ячеек — вот так: =SUM(A2:A7).

Бонусные функции Google Таблиц

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

Google Формы

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

Чтобы интегрировать опросник зайдите во вкладку «Инструменты» и выберите пункт «Создать форму»

Google Analytics

Google Analytics — это система аналитики, которая позволяет собирать и анализировать данные цифровых ресурсов: мобильных приложений, сайтов, блогов. Интеграция Google Analytics с гугл-таблицами реализована через специальное дополнение. Для подключения вам необходимо открыть вкладку «Расширения», выбрать пункт «Дополнения» и нажать на кнопку «Установить дополнения».

Также во вкладке «Расширения» можно найти макросы — это серия записанных действий, которые вы произвели в гугл-таблицах. Записав действия, вы можете активировать макрос, чтобы позже повторить действия, нажав на одну кнопку в меню или применив сочетание клавиш

После того как вы нажали кнопку «Установить дополнения», вам откроется  окно, в котором нужно найти систему Google Analytics.

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

Коротко о главном

Гугл-таблицы — это бесплатный сервис для учёбы и работы, позволяющий быстро обработать большой объём данных, составить отчёты и создать диаграммы и графики. Все данные сохраняются автоматически, к тому же над таблицей могут работать одновременно несколько человек. Интерфейс гугл-таблиц схож с внешним видом Microsoft Excel, поэтому вам будет просто ориентироваться в сервисе.

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

Все права защищены.

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

© Евгений Намоконов, Ренат Шагабутдинов, 2018

© Оформление. ООО «Манн, Иванов и Фербер», 2018

* * *

Введение

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

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

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

Когда в файле работают одновременно 20 человек и он при этом не «тормозит», все данные меняются в реальном времени и не нужно пересылать друг другу файлы с названиями «Бюджет2015_Финал_v2_Иванов» или «Бюджет2015_FINAL_на согласование 11.02.2015» – это очень удобно.

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

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

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

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

Для кого будет полезна эта книга?

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

• Для тех, кто впервые начал работать с аналитикой и таблицами именно в Google Таблицах (уж извините за тавтологию).

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

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

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

Новые советы по Google Таблицам вы сможете найти на нашем канале в Телеграме – мы публикуем несколько кейсов в неделю с иллюстрациями и файлами-примерами:

https://telegram.me/google_sheets

В этой книге мы не стали рассматривать скрипты, позволяющие автоматизировать процессы в Google Таблицах и других приложениях Google Apps. Скрипты – это аналоги макросов в Excel, работающих на основе JavaScript. Для изучения этой темы рекомендуем вам книгу Going GAS издательства O’Reilly.

По любым вопросам пишите:

Ренат: [email protected]

Евгений: [email protected]

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

ЧЕМ ТАБЛИЦЫ ЛУЧШЕ EXCEL

• Совместная работа в режиме онлайн.

• Бесплатный сервис (если вы используете его для себя, а не для бизнеса).

• SQL-запросы прямо в таблицах с помощью функции QUERY.

• Большое количество бесплатных надстроек (дополнений).

• Богатые возможности по поиску и загрузке котировок, данных с сайтов, RSS-фидов, другой информации из Сети.

• Работа с любого компьютера, планшета и (хотя это уже затруднительно) даже с телефона.

• Удобная передача файлов – достаточно открыть доступ и отправить ссылку. Для этого не нужен быстрый интернет и компьютер, достаточно даже смартфона.

• Возможность связать несколько документов: данные будут обновляться автоматически в режиме онлайн (в Excel – только при открытии файла-источника, на который ссылается ваша книга).

ЧЕМ ТАБЛИЦЫ ХУЖЕ EXCEL

• Сводные таблицы и диаграммы с урезанным функционалом.

• Нет расширенного фильтра.

• С большим количеством данных и формул Таблицы работают медленнее.

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

ИМПОРТ ИЗ EXCEL

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

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

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

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

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

ЭКСПОРТ В EXCEL

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

Файл → Скачать как → Microsoft Excel (XLSX)

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

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

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

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

=ЕСЛИОШИБКА(__xludf.DUMMYFUNCTION(«SPLIT(B21,»» «»)»);»Этот»)

Как сделать документ легче и быстрее

• Удаляйте неиспользуемые строки на каждой вкладке (по умолчанию создается 1000 строк – если у вас на вкладке сейчас используется 200, удалите лишние 800, при необходимости просто добавьте нужное количество) и столбцы (аналогично). Можно воспользоваться надстройкой Crop Sheet или сделать это вручную.

• Оптимизируйте количество вкладок (попробуйте объединить в одну несколько вкладок с маленькими таблицами или списками).

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

• Не заливайте строки/столбцы цветом целиком (и вообще старайтесь избегать излишнего форматирования).

• Проверьте, нет ли условного форматирования на (излишне) большом диапазоне ячеек.

• Не ставьте фильтр на все столбцы.

• Очистите примечания, если их много и они не нужны.

• Посмотрите, нет ли проверки данных на большом диапазоне ячеек.

Ренат: У нас в МИФе есть сводный файл со списком всех книг и большим количеством данных по ним, которые грузятся из разных источников. В какой-то момент некоторые коллеги перестали им пользоваться – ноутбуки перегревались, а файл иногда и не открывался:)

После оптимизации по большинству описанных пунктов он стал «летать».

Это работало и со многими другими документами.

В Excel, кстати, с помощью этих же правил бывали случаи уменьшения размера файла в разы, а несколько раз – даже на порядок (последнее, правда, случается только при пересохранении файла из старого формата XLS в новые XLSX, XLSM, XLSB).

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

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

Начало работы

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

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

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

• личное (список дел, бюджет, календарь и т.д.);
• работа (табель учета времени, бюджет компании, расписание смен и т.д.);
• управление проектами (планы проекта, диаграммы контроля сроков и т.д.);
• образование (посещаемость, журнал успеваемости).

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

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

1. Загрузить его на Google Диск, а затем открыть через меню «Файл» в Таблицах.

2. Через меню «Файл» — «Открыть» — «Загрузка» найти нужный файл на компьютере или перетащить вручную. Открыть это окно можно и с помощью горячих клавиш Ctrl+O.

Скачать созданные или измененные таблицы можно через соответствующий раздел во вкладке «Файл». Скачивание возможно в форматах .xlsx или .csv — они поддерживаются Microsoft Excel — или еще в нескольких форматах, для которых будет доступен только просмотр.

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

Интерфейс Google Таблиц 

Меню в Google Таблицах

Начнем изучать функционал Таблиц. В верхней части располагается несколько вкладок основного меню:

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

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

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

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

Формат. Служит для работы с содержимым ячеек (например с текстом), а также для установки выравнивания, размера шрифта, переносов и т.д.

Данные. Здесь можно настраивать фильтрацию, делать сортировку, убирать дублированную информацию, а также приводить документы в порядок, например удаляя лишние пробелы.

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

Расширения. С помощью вкладки можно добавить внешние расширения для более широких действий: Doctopus, Form Mule, CoRubrics и другие.

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

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

Стандартные инструменты Google Таблиц в чем-то похожи на функции из Excel.

Среди наиболее востребованных:

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

Боковое меню

Справа от рабочей области также располагается несколько иконок меню. Разберем их подробнее сверху вниз:

• Календарь. С его помощью можно настроить календарь непосредственно в Таблицах без использования дополнительных сервисов и приложений.
• Google Keep. Предоставляет быстрый доступ к Заметкам. Работает в обе стороны: можно добавить в таблицу изображение из заметок или передать данные из таблицы в заметки.
• Задачи. В этом разделе можно создавать новые задачи для себя или всей команды.
• Контакты. Здесь можно хранить список контактов в любом удобном формате. Обычно список синхронизирован с вашей телефонной книгой.
• Карты. Быстрый доступ к Google Maps — например, для оперативного уточнения информации о расположении какого-либо объекта или адреса.
• Установка дополнений. С помощью последней вкладки можно добавлять новые дополнения, совместимые с Таблицами, через окно Google Workspace Marketplace.

Строка функций

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

Внесение данных

Ячейки. Данные вносятся в любую активную ячейку, как и в Microsoft Excel. Для этого не обязательно делать ее активной двумя кликами — ячейка доступна для изменений уже при установке в ней курсора. Если необходимо добавить или скопировать данные из конкретной ячейки, нужно щелкнуть правой клавишей мыши и выбрать команду из предложенных: «Копировать», «Вставить», «Вырезать» и т.д. Каждая ячейка имеет собственный уникальный адрес по буквенному обозначению столбца и числовому обозначению строки.

Столбцы. Чтобы выделить столбец полностью, нужно кликнуть на название столбца (A, B, C, D и т.д.), а затем с помощью правой клавиши мыши открыть меню с необходимыми действиями. Столбцы можно копировать, скрывать, вырезать, удалять, вставлять, менять в них формат, сортировать данные в любом порядке и выполнять ряд других действий.

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

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

Строки и столбцы можно перемещать по всему листу. Для этого в меню «Правка» нужно выбрать пункт «Переместить» и перенести выбранный элемент по своему усмотрению.

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

История изменений и сохранение

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

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

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

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

Читатель. Можно просматривать документ, но нельзя вносить изменения и оставлять комментарии в примечаниях.

Комментатор. Можно оставлять комментарии по тексту и интересующим данным, но сами данные изменять нельзя.

Редактор. Полный доступ к изменению данных в документе + функция комментирования.

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

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

Таблицу можно удалить как из списка документов на главной странице Google Таблиц, так и при открытом документе. Для этого в меню «Файл» необходимо выбрать команду «Удалить». В результате таблица отправится в Корзину, где будет храниться 30 дней, в течение которых ее можно восстановить. По истечении этого срока она исчезнет окончательно.

Безопасность при работе с данными

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

В появившемся справа окне нужно выбрать, что подлежит защите — весь лист или конкретная группа ячеек. Затем в разделе «Задать разрешения» можно указать пользователей, которые имеют к нему доступ. Если у пользователя есть ограничение на внесение изменений, при попытке правок для него будет показываться предупреждающее сообщение.

Комментарии к ячейкам

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

Форматы и внешний вид данных

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

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

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

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

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

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

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

Работа с данными: фильтрация, сортировка, срезы

Фильтрация

Фильтры устанавливают на всю таблицу, чтобы отключать данные, которые не нужны для работы в данный момент. Это удобный формат при работе с большими массивами информации: вы можете изучать данные только по одному направлению, типу контента или, например, виду товара. Инструментов для установки фильтров два: «Создать фильтр» и «Режим фильтрации».

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

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

Сортировка

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

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

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

Срезы

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

Проверка

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

Настройка выполняется следующим образом.

1. Выбираем диапазон ячеек — обычно это отдельный столбец.

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

3. Нажимаем кнопку «Сохранить».

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

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

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

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

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

Отчет создается с помощью меню «Вставка» — «Сводная таблица». При этом создается новый лист и открывается дополнительно окно редактора. Затем нужно добавить в разделы «Строки», «Значения», «Столбцы» и «Фильтры» данные, которые предстоит проанализировать.

Формулы в Google Таблицах

Главная составляющая Таблиц, как и в случае с Microsoft Excel, — широкие возможности работы с формулами: от простейших математических функций до сложных расчетов и проверок на условия.

Ввод формулы осуществляется так же, как и в Excel. В строке формул или в самой ячейке прописывается знак «=», затем нужная функция (с помощью подсказки можно выбрать подходящую), затем условия, по которым эта функция будет работать. Условия вводятся в скобках.

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

Всего в Таблицах предусмотрено около 400 функций, разбирать каждую из которых нужно отдельно. Если продолжать сравнение с Excel, то некоторых функций от продукта Microsoft в Google Sheets нет. И наоборот: такая функция, как GOOGLETRANSLATE, переводящая с одного языка на другой, есть только в Таблицах.

Популярные формулы:

1. СУММА. Самая простая и понятная формула — отвечает за сложение чисел из нескольких ячеек. Записывается в формате «=СУММ(A1;B1)».

2. СЧЕТ. Показывает количество ячеек в диапазоне с числовыми значениями. Записывается в формате «=СЧЕТ(A1:B5)».

3. ЕСЛИ. Отвечает за логическую проверку данных в ячейках в формате «истина — ложь». Записывается в формате «=ЕСЛИ(логическое выражение; значение, если истина; значение, если ложь».

4. СУММЕСЛИ. Складывает числа в диапазоне в зависимости от заданных условий. Записывается в формате «=СУММЕСЛИ(выбранный диапазон; условие)».

5. СЧЕТЕСЛИ. Подсчитывает количество ячеек в диапазоне, отвечающих определенным условиям. Записывается в формате «=СЧЕТЕСЛИ(выбранный диапазон; условие)».

Построение диаграмм и графиков

Для визуализации данных у Google есть другие сервисы — например, Power BI или Google Data Studio, — но диаграммы и графики можно строить и внутри Таблиц. Это обусловлено необходимостью наглядно показывать результаты исследований, не переключаясь между сервисами только с одной целью. Запустить построение диаграмм можно через меню «Вставка». Далее нужно выбрать раздел «Диаграмма», которая по умолчанию создаст такой тип графика:

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

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

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

Сервисы Google – полноценная экосистема, в которой все взаимосвязано. Поэтому Таблицы можно легко интегрировать в другие инструменты, например Forms или Analytics.

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

Логично, что Таблицы, как аналитический инструмент, тесно связаны с главным аналитическим продуктом от Google. Подключить его можно с помощью меню «Расширения» — «Установить дополнения». В открывшемся окне нужно найти Google Analytics — в списке или через поиск. Нужно дать разрешение аддону на установку.

Чтобы выгрузить данные из Google Analytics в Таблицы, нужно воспользоваться меню «Расширения» — «Google Analytics» — «Create new report».

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

После этого автоматически создается лист с названием «Report Configuration», в котором прописываются параметры отчета.

Сам отчет можно создать с помощью меню «Расширения» — «Google Analytics» — «Run Reports». Программа выгрузит требуемые данные, с которыми можно работать дальше. Более сложный уровень — автоматическая выгрузка данных в имеющуюся таблицу.

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

Никакой настройки для интеграции с Формами не предусмотрено — это по умолчанию есть в Таблицах. Новая форма создается через меню «Инструменты». По клику в меню «Создать форму» открывается соответствующий редактор.

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

Другие дополнения

Если рассмотреть другие возможности интеграции Google Sheets с сервисами Google, можно выделить следующие интересные расширения:

• Template Gallery — набор готовых шаблонов для планов, отчетов, создания меню, графиков и т.д.;
• Sort by Color — возможность сортировки данных по цвету ячейки;
• Power Tools — поиск дубликатов в таблице, удаление двойных пробелов, пустых столбцов и ячеек и ряд других полезных функций;
• Remove Duplicates — поиск ячеек с одинаковыми данными;
• Supermetrics — выгрузка данных из нескольких аналитических сервисов (в т.ч. не только Google Analytics);
• Crop Sheet — удаление пустых строк и столбцов, очистка таблицы от ненужных ячеек.

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

Горячим клавишам в Google Sheets может быть посвящено отдельное большое исследование. Пока остановимся на ключевых возможностях Таблиц и расскажем, как их можно применять. С помощью горячих клавиш можно увеличить скорость работы в Таблицах примерно в 1,5 раза:

• Ctrl + пробел — выделение столбца;
• Shift + пробел — выделение строки;
• Ctrl + Home — возвращение в начало листа;
• Ctrl + Backspace — переход к активной ячейке;
• Ctrl + K — вставка ссылки;
• Home — возвращение в начало строки.

Также в Таблицах работают и некоторые стандартные горячие клавиши из Microsoft Excel, например, Ctrl + C, Ctrl + V и т.д.

Специальные возможности Google Таблиц

Работа в офлайн-режиме

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

1. Нужно установить расширение «Google Документы офлайн».

2. В «Настройках» необходимо установить флажок в меню «Разрешить создание файлов и работу с недавними документами, таблицами и презентациями Google на этом устройстве, даже когда оно не подключено к интернету».

3. Нажать «Готово».

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

Добавление нескольких аккаунтов

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

1. В браузере нужно нажать на фото профиля в правом верхнем углу.

2. Выбрать «Управлять пользователями».

3. Нажать «Добавить пользователя».

4. Ввести логин и пароль от другого аккаунта, нажать «Добавить».

5. Войти в новый аккаунт. Все настройки и закладки будут сохранены автоматически.

6. Переключение между аккаунтами также можно проводить через фото профиля.

Работа с макросами Excel

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

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

1. Открыть через меню «Инструменты» — «Макросы» — «Записать макрос».

2. Выбрать тип ссылки.

3. Выполнить операцию, которую необходимо автоматизировать, нажать «Сохранить».

4. Дать макросу имя, нажать «Сохранить».

5. Чтобы выполнить макрос, нужно запустить его через меню «Инструменты» — «Макросы» — «Название сохраненного макроса».

Несколько советов по работе с макросами

1. Чтобы макрос работал эффективнее, количество действий в нем желательно минимизировать.

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

3. Для макросов лучше создавать свои горячие клавиши. В Таблицах можно создать до десяти таких комбинаций.

4. Лучше минимизировать количество копий макросов. Можно применять макрос, записанный для одной ячейки к диапазону ячеек. Для этого достаточно выделить несколько ячеек и запустить макрос.

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

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

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

Плюсы

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

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

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

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

Минусы

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

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

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

Сложность при работе с крупными массивами. В этом функционал Таблиц несколько уступает классическому Excel. При этом работа по совершенствованию системы ведется.

Ограниченное количество стандартных функций. Внутри Microsoft Excel содержится значительно больше формул и функций.

Евгений Намоконов. Ренат Шагабутдинов
Google Таблицы. Это просто. Функции и приемы

Все права защищены.

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

© Евгений Намоконов, Ренат Шагабутдинов, 2018

© Оформление. ООО «Манн, Иванов и Фербер», 2018

* * *

Введение

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

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

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

Когда в файле работают одновременно 20 человек и он при этом не «тормозит», все данные меняются в реальном времени и не нужно пересылать друг другу файлы с названиями «Бюджет2015_Финал_v2_Иванов» или «Бюджет2015_FINAL_на согласование 11.02.2015» – это очень удобно.

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

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

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

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

Для кого будет полезна эта книга?

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

• Для тех, кто впервые начал работать с аналитикой и таблицами именно в Google Таблицах (уж извините за тавтологию).

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

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

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

Новые советы по Google Таблицам вы сможете найти на нашем канале в Телеграме – мы публикуем несколько кейсов в неделю с иллюстрациями и файлами-примерами:

https://telegram.me/google_sheets

В этой книге мы не стали рассматривать скрипты, позволяющие автоматизировать процессы в Google Таблицах и других приложениях Google Apps. Скрипты – это аналоги макросов в Excel, работающих на основе JavaScript. Для изучения этой темы рекомендуем вам книгу Going GAS издательства O’Reilly.

По любым вопросам пишите:

Ренат: [email protected]

Евгений: [email protected]

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

ЧЕМ ТАБЛИЦЫ ЛУЧШЕ EXCEL

• Совместная работа в режиме онлайн.

• Бесплатный сервис (если вы используете его для себя, а не для бизнеса).

• SQL-запросы прямо в таблицах с помощью функции QUERY.

• Большое количество бесплатных надстроек (дополнений).

• Богатые возможности по поиску и загрузке котировок, данных с сайтов, RSS-фидов, другой информации из Сети.

• Работа с любого компьютера, планшета и (хотя это уже затруднительно) даже с телефона.

• Удобная передача файлов – достаточно открыть доступ и отправить ссылку. Для этого не нужен быстрый интернет и компьютер, достаточно даже смартфона.

• Возможность связать несколько документов: данные будут обновляться автоматически в режиме онлайн (в Excel – только при открытии файла-источника, на который ссылается ваша книга).

ЧЕМ ТАБЛИЦЫ ХУЖЕ EXCEL

• Сводные таблицы и диаграммы с урезанным функционалом.

• Нет расширенного фильтра.

• С большим количеством данных и формул Таблицы работают медленнее.

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

ИМПОРТ ИЗ EXCEL

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

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

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

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

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

ЭКСПОРТ В EXCEL

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

Файл → Скачать как → Microsoft Excel (XLSX)

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

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

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

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

=ЕСЛИОШИБКА(__xludf.DUMMYFUNCTION(«SPLIT(B21,»» «»)»);»Этот»)

Как сделать документ легче и быстрее

• Удаляйте неиспользуемые строки на каждой вкладке (по умолчанию создается 1000 строк – если у вас на вкладке сейчас используется 200, удалите лишние 800, при необходимости просто добавьте нужное количество) и столбцы (аналогично). Можно воспользоваться надстройкой Crop Sheet или сделать это вручную.

• Оптимизируйте количество вкладок (попробуйте объединить в одну несколько вкладок с маленькими таблицами или списками).

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

• Не заливайте строки/столбцы цветом целиком (и вообще старайтесь избегать излишнего форматирования).

• Проверьте, нет ли условного форматирования на (излишне) большом диапазоне ячеек.

• Не ставьте фильтр на все столбцы.

• Очистите примечания, если их много и они не нужны.

• Посмотрите, нет ли проверки данных на большом диапазоне ячеек.

Ренат: У нас в МИФе есть сводный файл со списком всех книг и большим количеством данных по ним, которые грузятся из разных источников. В какой-то момент некоторые коллеги перестали им пользоваться – ноутбуки перегревались, а файл иногда и не открывался:)

После оптимизации по большинству описанных пунктов он стал «летать».

Это работало и со многими другими документами.

В Excel, кстати, с помощью этих же правил бывали случаи уменьшения размера файла в разы, а несколько раз – даже на порядок (последнее, правда, случается только при пересохранении файла из старого формата XLS в новые XLSX, XLSM, XLSB).

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

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

Работа с формулами и диапазонами

НЕСКОЛЬКО БАЗОВЫХ ПРАВИЛ

• Любая формула, как и в Excel, вводится со знака «равно».

• Текст указывается в кавычках, после названий листов ставится восклицательный знак, названия листов берутся в апострофы, если в них есть пробелы (‘Название листа’!A1).

• Аргументы функций разделяются символом (каким именно – зависит от региональных настроек), для России это точка с запятой.

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

РАЗБИРАЕМ НА ПРИМЕРЕ СУММЕСЛИ (SUMIF), КАК ЗАДАТЬ (ВЫБРАТЬ) В ФОРМУЛЕ ДИАПАЗОНЫ И УСЛОВИЯ

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

Возьмем простую формулу СУММЕСЛИ (SUMIF).

По ссылке https://goo.gl/1dIZMI вы найдете Google Документ с примером, на котором можно потренироваться. Для редактирования выберите:

Файл → Создать копию.

Начнем.

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

Формулы всегда начинаются со знака «равно».

Итак, выделяем ячейку В2 и начинаем вводить формулу. Уже после нескольких символов =СУ появляются варианты формул с этим слогом в названии, выбираем мышкой СУММЕСЛИ и кликаем на нее:

Видим вот такое окно (формулу можно писать как в самой ячейке, так и в строке формул – это не принципиально):

Под формулой видим окно справки. В нем цветом подсвечивается тот элемент, который нужно ввести сейчас. У нас это диапазон условия (если справка не открылась, нажмите на? под формулой).

Выбираем лист «Диапазоны» и выделяем диапазон условия – для этого кликаем на его первой ячейке и «протягиваем» до последней (в данном примере это С1:C7). Выделять ячейки можно и в обратном порядке: начать с С7 и протянуть до С1; или можно кликнуть на названии столбца С, и он выберется целиком.

Если вам мешает справка формулы, то закройте ее, нажав на крестик. Если все равно что-то мешает и никак не получается выбрать нужный диапазон, как B1:B7 на скриншоте ниже, – его можно ввести с помощью клавиатуры, прямо в строке формул (не забывайте, что буквы в ссылках Таблиц латинские).

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

(Если нажать F4 еще раз, то зафиксируются только строки, при повторном нажатии – только столбцы.)

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

Кликнем на вкладку Сводный отчет и на ячейку А2. Не будем делать условие абсолютным, так как планируем скопировать формулу на ячейку ниже и нам нужно, чтобы условие с А2 поменялось на А3.

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

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

Формула готова, теперь скопируем ее из ячейки B2 в ячейку В3 (можно через пункты меню Копировать и Вставить, можно кликнуть на В2 и протянуть на ячейку ниже, можно использовать сочетания клавиш Ctrl + C, Ctrl + V):

АБСОЛЮТНЫЕ, ОТНОСИТЕЛЬНЫЕ И СМЕШАННЫЕ ССЫЛКИ. ФУНКЦИЯ ДВССЫЛ (INDIRECT)

Ссылки в Таблицах, как и в Excel, могут быть абсолютными и относительными.

Ровно так же они изменяются с помощью клавиши F4 или путем ввода знака доллара перед номером строки и/или перед номером столбца.

Читать этот подраздел дальше имеет смысл, если вы не работали с абсолютными и относительными ссылками в Excel или хотите повторить этот материал.

Относительные ссылки – это обычные ссылки вида A1, D10, AX127, которые по умолчанию появляются при щелчке на ячейку во время ввода формул. Такие ссылки смещаются вместе с формулой. Они являются относительными, так как ссылаются не на конкретную ячейку, а на ячейку, отстоящую на N строк и M столбцов от той, в которую введены. Так, если вы ввели в ячейку B1 формулу

=A1

то вы ссылаетесь не на A1, а на ячейку слева от ячейки с формулой, или отстоящую от нее на -1 (минус один) столбец и 0 (ноль) строк.

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

=B4

то есть по-прежнему ссылаться на ячейку слева от себя.

Это не всегда подходит для ваших задач.

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

Уже во второй строке формула ссылается не на нужную ячейку, а на пустую ячейку под ней.

Для таких случаев используются абсолютные ссылки. Абсолютная ссылка – это ссылка вида $A$1 или $B$5, которая не изменяется при перемещении формулы:

Ссылку можно сделать абсолютной, нажав F4 при вводе формулы (или щелкнув курсором на ссылку в строке формул). Последовательно нажимая F4, вы будете перебирать все 4 возможные комбинации:

A1 → $A$1 → A$1 → $A1 → A1.

A$1 и $A1 – это смешанные ссылки, в которых закреплена только строка или только столбец. Например, для составления обычной таблицы умножения требуются именно такие ссылки в формуле, ведь нам нужно перемножать числа из первой строки (фиксируем ее) и первого столбца (фиксируем столбец A):

На скриншоте ниже видно, как меняются адреса разных типов, если мы вводим их в столбец А и копируем в столбцы С, D и Е:

Абсолютная ссылка тоже может изменяться. Например, если вы добавите строку в примере выше, ссылка по-прежнему укажет на ячейку с 20 %, но это будет уже не $E$1, а $E$2:

В большинстве случаев такая ситуация приемлема.

Если вы хотите создать по-настоящему абсолютную ссылку, всегда указывающую на A1 даже при изменении строк, воспользуйтесь функцией INDIRECT (ДВССЫЛ). Ее единственный аргумент – адрес ячейки:

=INDIRECT(«A1»)

=ДВССЫЛ(«A1»)

Теперь при вставке строки результат вычисления обнулился, так как формула не стала ссылаться на E2, а осталась на E1:

ТИПЫ ССЫЛОК A1 И R1C1

Стиль ссылок А1. Является классической системой адресации в электронных таблицах; как в MS Excel, так и в Google Таблицах сначала идет имя столбца, потом – номер ячейки.

Стиль ссылок R1C1. В этой системе строки (ROW) и столбцы (COLUMN) обозначаются цифрами. Например, R3C2: 3-я строка и 2-й столбец – ячейка B3.

В Google Таблицах этот стиль используется редко, например в формуле ДВССЫЛ (INDIRECT). С помощью этой формулы мы приводим текстовое наименование адреса ячейки, записанное в стиле A1 или R1C1, в вид настоящей ссылки на ячейку, и формула возвращает содержимое этой ячейки.

Синтаксис ДВССЫЛ (INDIRECT) состоит из двух аргументов: адреса ячейки и стиля адреса ячейки. Если вы хотите использовать в этой формуле стиль R1C1, то поставьте 0 во втором аргументе.

На скриншоте ниже подробнее:

ДИАПАЗОНЫ ВИДА A2:A

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

Если при этом на листе с таблицей нет никаких других данных, кроме заголовков полей (столбцов) и данных под ними (то есть нет нескольких таблиц, расположенных одна под другой), есть смысл указывать в аргументах открытые диапазоны вида A2:A, а не A2:A100. Тогда вам не придется каждый раз менять формулы.

Можно указывать столбцы/строки целиком, если для вас приемлемо включить заголовки в расчет:

A: A – весь столбец A

3:3 – вся строка 3

Итак, вы указали обычный закрытый диапазон – до 12-й строки:

И после добавления данных в 13-ю результат расчета формулы не изменится:

С открытым диапазоном таких проблем не будет:

Почему в примере (да и вообще) лучше использовать C2:C, чем весь столбец C: C? Чтобы не учитывать заголовок.

Он может означать год, и тогда (типичная ошибка) к общей сумме добавится, например, 2016:

ИМЕНОВАННЫЕ ДИАПАЗОНЫ

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

=A7*$E$1

вы будете видеть

=Продажи*Налог

Чтобы задать диапазону имя, выделите его и нажмите на кнопку Именованные диапазоны в разделе меню Данные (или сначала вызовите окно Именованные диапазоны, а потом выделите то, чему будете присваивать имя):

После чего справа в появившемся окне введите название диапазона и нажмите Готово:

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

КАК ПРАВИЛЬНО ПИСАТЬ УСЛОВИЯ В ФОРМУЛАХ И ИСПОЛЬЗОВАТЬ СИМВОЛЬНЫЕ ШАБЛОНЫ

Небольшое пояснение, как писать условия в формулах, чтобы все работало.

• Числовые и текстовые условия, знаки >, <, = нужно писать в кавычках (кстати, не все знают, что сочетание <> обозначает неравенство):

– например, так нашим условием будет Вася

– а так все, кроме Васи

– Если вам нужно объединить несколько элементов в условии формулы, скажем, адрес ячейки и знак >= или формулу и текст, используйте амперсанд (&)

• В условиях некоторых формул (СЧЁТЕСЛИМН (COUNTIF), СУММЕСЛИМН (SUMIFS) и других) можно использовать символьные шаблоны * и?:

– * – любая последовательность любых символов;

– ? – любой один символ.

• Например, Василий* будет означать, что формула отберет Василиев с любыми фамилиями (и без них), а если написать В?силий, то условием будут все В(любая буква)силий в наших данных.

– если вам нужно, чтобы символьные шаблоны * или? формула восприняла как обычный текст, поставьте перед ними тильду ~:

ВЫЧИСЛЕНИЕ ЧАСТИ ФОРМУЛЫ В СТРОКЕ ФОРМУЛ

Разбирать чужие (а иногда и свои, но забытые) сложные формулы – то еще удовольствие. Особенно если они «многоэтажные» и состоят из многих вложенных друг в друга функций.

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

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

Примечание: в Excel это тоже работает – необходимо не только выделить фрагмент формулы, но и нажать F9 после этого. Важно, что в конце нужно нажать Escape, а не Enter, иначе этот фрагмент так и останется числом, а не функцией/диапазоном. В Google Таблицах этого нет.

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

По ссылке – файл с примером. Копируйте на свой диск, изучайте формулу, играйтесь: https://goo.gl/bGGIRR.

Эта формула (далее мы подробно рассмотрим такие конструкции) возвращает название книги с N-ми по счету продажами, где N берется из ячейки D3. То есть, вводя туда число 4, вы задаете вопрос: «Какая книга занимает 4-е место в рейтинге продаж?».

Формула работает так: функция LARGE (НАИБОЛЬШИЙ) находит N-е по величине значение (в примере 310 – 7-я по счету величина). Функция MATCH (ПОИСКПОЗ) находит порядковый номер строки, в которой находится это значение (1-я строка в диапазоне), а функция INDEX (ИНДЕКС) возвращает значение, стоящее в этой строке, но в диапазоне A3:A14 (названия книг).

НЕСКОЛЬКО ПРАВИЛ ОФОРМЛЕНИЯ ТАБЛИЦ И РАБОТЫ С ДАННЫМИ

1. Используйте разделители разрядов: не 12948224, а 12 948 224:

2. В аналитике принято сравнивать данные (отчетности, показатели) с какой-то базой: предыдущим периодом, аналогичным периодом прошлых лет, с данными конкурентов, со средним показателем по отрасли, по региону, с нормативами и т. д.

Сравнение может быть абсолютным (в отделе стало работать на 3 человека больше) и относительным (было 6 сотрудников, а стало 9: прирост на 50 %!).

Ни то ни другое сравнение не показательно само по себе.

Используйте оба.

Приросты считаются так:

Абсолютный прирост = текущий показатель – базисный показатель (прошлый месяц, аналогичный месяц прошлого года и т. д.).

Относительный прирост = текущий показатель / базисный показатель –1.

Итого: смотрим на динамику в рамках года и изменение год к году – в относительных и абсолютных величинах.

3. Управленческая информация должна быть актуальной и «живой». Что это значит? Например, что ее нельзя хранить в форматах PDF или Word, потому что тогда вы не сможете:

• быстро обновить данные;

• поменять одну из переменных и посмотреть на то, как это изменение отразится на итоговых показателях (провести сценарный анализ);

• добавить новые показатели.

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

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

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

Станет ли более взвешенным решение, если вы будете знать о росте показателя на 1,247 % вместо того, чтобы знать о его росте на 1,2 %? Стоит ли более точный расчет того времени и внимания, которое на него потратите вы / аналитик / маркетер / кто-либо другой из ваших коллег?

5. Не забывайте закреплять заголовки таблиц, в которых много данных: