Что такое Сводные таблицы Google Sheets?

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

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

Не спешите впадать в панику и вспоминать, как правильно использовать такие традиционные функции как СЧЕТЕСЛИ, СУММЕСЛИ и подобные им. Расслабьтесь, сделайте глубокий вдох. Эта задача весьма просто решается при помощи сводных таблиц Google.

Сводная таблица поможет представить ваши данные в удобном и презентабельном виде.

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

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

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

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

данные о продажах

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

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

В нашем примере вы можете просто выделить столбцы A:G.

создать сводную таблицу

Затем в меню выбираем Данные -> Сводная таблица. Открывается новый лист, на котором мы будем создавать и редактировать сводную Google таблицу. Вам необходимо настроить содержание и внешний вид вашей таблицы.

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

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

добавить поле в сводную таблицу

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

добавляем строки и столбцы в сводную таблицу

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

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

фильтр в сводной таблице

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

более сложные вычисления в сводной таблице

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

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

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

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

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

Итак, наша сводная таблица готова.

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

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

Однако, вернёмся к вопросам, которые интересуют вашего босса: "Кто мой лучший покупатель?"

pivot table

Действуя старым и проверенным способом, изменяем строки и столбцы нашей таблицы и получаем искомый результат. Не забудьте отсортировать покупателей с использованием поля "Сумма".

Аналогичным образом дадим ответы и на другие интересные вопросы. Например, какой товар лучше всего продаётся?

сводная таблица по товарам

Где мои товары лучше всего продаются?

сводная таблица по регионам

Где какой товар лучше продаётся?

сводная таблица по продажам

Итак, в течение 5 минут вы получили при помощи Google таблиц ответы на интересующие вас вопросы. Ваш босс доволен!

Обратите внимание на то, что общий объём продаж одинаков во всех наших сводных таблицах. Это потому, что каждая из них просто представляет одни и те же данные разными способами.

Как создать диаграмму в сводной Google таблице.

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

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

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

диаграмма в сводной таблице

Есть и второй способ создать диаграмму.

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

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

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

анализ данных сводной таблицы Google

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

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

Как опубликовать сводную Google таблицу или диаграмму в Интернете.

Вашу диаграмму вы можете опубликовать в интернете. Для этого в главном меню нажмите Файл -> Опубликовать в Интернете. Затем выберите те объекты, которые вы хотите опубликовать. В нашем случае мы  можем опубликовать либо весь файл, либо отдельный лист, либо только диаграмму. Если мы выберем лист нашего файла Google таблиц, то мы можем представить его либо как веб-страницу, либо как статический документ в формате PDF. Если мы хотим опубликовать только диаграмму, то варианта также два. Либо это будет интерактивная диаграмма, которая будет изменяться вместе с изменением исходных данных, либо можно просто ограничиться картинкой, показывающей как бы снимок диаграммы в определённый момент времени.

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

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

При помощи этого же меню можно отменить публикацию.

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