Как обновить сводную таблицу в гугл таблице

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

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

Добавить новую сводную таблицу можно следующим образом:

1. Выделить исходные данные
2. Нажать на кнопку “Сводная таблица” в меню “Данные”:

После нажатия на выделенную кнопку сразу будет создан новый лист, в котором и будет сводная — и редактор отчетов справа (то, что называется списком полей в Excel):

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

Для этого в редакторе отчетов в значения перенесем 2015 и 2016 год:

В строки добавим Тематику и отсортируем по убыванию по сумме 2016-го года:

И получим следующий результат:

Рассчитываемое поле

Рассчитываемое поле в сводной таблице позволяет производить дополнительные вычисления. Это аналог вычисляемого поля в Excel.

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

После этого задайте новому полю имя и введите формулу для расчета.

В примере мы рассчитаем прирост в 2016 году.

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

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

В Google Таблицах, в отличие от Excel, можно напрямую ссылаться на ячейки сводной в формулах, без помощи функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GETPIVOTDATA).

То есть мы могли получить такой же результат и без рассчитываемого поля:

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

P.S. Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ в Google Таблицах есть. Какой бы у вас ни был язык формул, ее название нужно вводить на английском: GETPIVOTDATA.

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

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

Даже если вы укажете диапазон A2:E, он автоматически изменится на A2:E1000, где тысяча — номер последней строки в документе. Соответственно, если после этого в 1001 строке и ниже появятся данные, они не попадут в сводную.

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

Но! Диапазон вида A:E для сводных работать будет. Так что если исходные данные начинаются с первой же строки листа и будут пополняться, используйте диапазон без номеров строк.

Как создавать, редактировать и обновлять сводные таблицы в Google Sheets

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

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

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

Программы для Windows, мобильные приложения, игры — ВСЁ БЕСПЛАТНО, в нашем закрытом телеграмм канале — Подписывайтесь:)

Программы для Windows, мобильные приложения, игры — ВСЁ БЕСПЛАТНО, в нашем закрытом телеграмм канале — Подписывайтесь:)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Советы по эффективной работе с Google таблицами:

Как построить диаграмму в Google таблице — Почему для нас так важны графики и диаграммы, в том числе и в Google таблицах? Объясняется все довольно просто. Производя анализ данных, мы всегда рассматриваем какие-то числа. Однако, для понимания…
Учимся использовать условное форматирование в Google таблицах — Как сделать так, чтобы в зависимости от содержания таблицы изменялось её оформление? И при этом чтобы это происходило автоматически? В этом нам поможет условное форматирование Google таблицы. Мы научимся правильно…
2 простых способа создать раскрывающийся список в Google таблице — Возможно, многие из вас ещё не сталкивались с использованием раскрывающихся (выпадающих) списков и чекбоксов в Google таблицах. Сейчас мы с вами рассмотрим, насколько они могут быть полезны в работе с…
Сортировка данных в Google таблицах — Сортировка данных – это полезная и довольно часто используемая операция при работе с данными в Google таблицах. Ведь когда вы работаете с большими объемами информации в таблице, то организация данных…
Столбцы в Google таблице – приёмы работы и редактирования — Рассмотрим, какие действия можно производить со столбцами Google таблицы: добавление и удаление, фиксация, перемещение, скрытие, изменение ширины, разделение на несколько столбцов различными способами. Также мы научимся менять столбцы и строки…
Как разделить столбец? Поможет дополнение к Google таблицам “Power Tools” — Ранее мы уже рассмотрели, какие основные операции можно производить со столбцами Google таблицы: добавление и удаление, фиксация, перемещение, скрытие, изменение ширины. Рассмотрим более сложные действия: как разделить столбец Google таблицы на…

Как создавать, редактировать и обновлять сводные таблицы в Google Sheets

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

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

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

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

Создание сводных таблиц в Google Sheets

1. Выделите все данные (включая заголовки), выбрав угловую ячейку или нажав Ctrl + A.
2. Выберите Данные, а затем Сводные таблицы из меню.
3. Google Sheets создаст новый лист с пустой сеткой.
4. Выберите строки, столбцы, значения и фильтры для использования в правом меню. Обратите внимание, что Sheets предлагает готовый стол для нас; на самом деле, это именно то, что мы хотим! Выберите «Средняя цена продажи для каждого района».

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

Что если мы хотим изменить нашу сводную таблицу?

Редактировать сводные таблицы в Документах Google

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

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

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

Обновить сводную таблицу в Google Sheets

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

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

1. Откройте Google Sheet с помощью сводной таблицы.
2. Выберите заголовок столбца, чтобы меню редактора отчетов отображалось справа.
3. Проверьте поле фильтра для каждого столбца, чтобы убедиться, что их нет. Вы должны увидеть «Добавить поле» под заголовком «Фильтр», если фильтры не запущены.

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

Добавить диаграмму в сводную таблицу в Google Sheets

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

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

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

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