гугл таблицы сводная таблица рассчитываемое поле
Создание сводной таблицы в Google Sheets
Для того, чтобы свести в один понятный формат большую таблицу со множеством данных можно воспользоваться выводом формулами данных в отдельный диапазон, либо создать простую сводную таблицу в Google Sheets.
Как в Google таблицах создать новую сводную таблицу?
У меня есть реальный пример массива данных расхода на продукты за 2021 год и мне интересно свести все в единую среду через создание простой сводной таблицы.
Нужно выделить всю область, которую мы хотим поместить в таблицу, вместе с заголовками столбцов.
Чтобы сводная таблица была полноценной и полезной, данные в массиве этой таблицы должны быть одного типа и записаны в подготовленных столбцах: дата в столбце для даты; количество товаров (к примеру); цена и т.д. По возможности не используйте объединение ячеек в таблице с данными.
В меню с инструментами Google таблицы кликайте на «Данные» и в модальном меню кликаете на «Создать сводную таблицу».
Появится всплывающее окно с информацией по диапазону с данными, которые мы помещаем в сводную таблицу и возможность разместить эту таблицу на текущем (активном листе) или в новом (создается автоматически).
У вас появится новая сводная таблица с настройками в правой части. В появившимся меню есть возможность выбора отображения данных по рекомендациям системы или по своим собственным предпочтениями настроек вида подачи данных.
В конце можно применить условное форматирование к строкам и столбцам таблицы, выставив границы рамок и т.д.
Попробуйте создать свою простую сводную таблицу в Google таблицах и поделитесь своим результатом в комментариях.
Сводные таблицы в Google Spreadsheets
Сводные таблицы в Google Таблицах имеют более скромный функционал по сравнению с Excel, но все же остаются полезным инструментом.
Добавить новую сводную таблицу можно следующим образом:
Рассмотрим пример: из таблицы с книгами получим сводную картину по тематикам. Исходная таблица выглядит так:
Для этого в редакторе отчетов в значения перенесем 2015 и 2016 год:
В строки добавим Тематику и отсортируем по убыванию по сумме 2016-го года:
И получим следующий результат:
Рассчитываемое поле
Рассчитываемое поле в сводной таблице позволяет производить дополнительные вычисления. Это аналог вычисляемого поля в Excel.
Добавить его можно, щелкнув на соответствующую кнопку меню после нажатия на “Добавить поле” в редакторе отчетов сводной таблицы:
После этого задайте новому полю имя и введите формулу для расчета.
В примере мы рассчитаем прирост в 2016 году.
Обратите внимание, что названия полей в формуле указываются в апострофах. Они соответствуют названиям столбцов исходной таблицы, на основе которой вы построили сводную.
Как видите, в сводной, как и в обычных диапазонах, можно менять формат ячеек. У ячеек с вычисляемым полем мы установили процентный формат.
В Google Таблицах, в отличие от Excel, можно напрямую ссылаться на ячейки сводной в формулах, без помощи функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GETPIVOTDATA).
То есть мы могли получить такой же результат и без рассчитываемого поля:
Но это менее предпочтительный вариант. Если вы поменяете структуру сводной, рассчитываемое поле будет работать для всех строк и любой структуры. А формулы придется протягивать вручную.
P.S. Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ в Google Таблицах есть. Какой бы у вас ни был язык формул, ее название нужно вводить на английском: GETPIVOTDATA.
Открытые диапазоны в сводных таблицах
Поэтому в случаях, если нужно работать с открытым диапазоном и данные будут постоянно пополняться, лучше использовать функцию QUERY, а не сводную.
Но! Диапазон вида A:E для сводных работать будет. Так что если исходные данные начинаются с первой же строки листа и будут пополняться, используйте диапазон без номеров строк.
Как создать сводную Google таблицу
У вас так много данных, что от множества строк и колонок с данными разбегаются глаза? Вы тонете в море цифр и не можете понять, что происходит? Предположим, вы работаете в компании, занимающейся продажей шоколада в нескольких регионах и работающей с множеством покупателей.
Ваш босс поставил вам задачу – вам необходимо на основе данных о продажах определить лучшего покупателя, самый популярный продукт и наиболее выгодный регион сбыта.
Не спешите впадать в панику и вспоминать, как правильно использовать такие традиционные функции как СЧЕТЕСЛИ, СУММЕСЛИ и подобные им. Расслабьтесь, сделайте глубокий вдох. Эта задача весьма просто решается при помощи сводных таблиц Google.
Что такое Сводные таблицы Google Sheets?
Сводная таблица поможет представить ваши данные в удобном и презентабельном виде.
Главное удобство сводных таблиц состоит в том, что поля в них можно интерактивно перемещать, а данные в них можно фильтровать, группировать, сортировать, рассчитывать итоги и средние значения. Вы можете менять строки и столбцы местами, изменять уровни детализации представления. Это позволяет не только менять внешний вид таблицы, но и взглянуть на ваши данные под совсем другим углом.
Важно также и то, что при этом ваши исходные данные не меняются – что бы вы не делали с вашей сводной таблицей. Вы просто выбираете такой способ их отображения, который позволит вам увидеть новые закономерности и связи. Ваши данные в сводной таблице будут разделены на группы, а огромный объем информации будет представлен в понятной и доступной для анализа форме.
Краткое руководство по сводным таблицам в Google таблицах.
Вот как, к примеру, выглядят данные о ваших продажах, хранящиеся в Google таблице.
Откроем таблицу, в которой содержатся наши исходные данные по продажам. Важно, чтобы данные, которые вы будете использовать, были расположены по столбцам. Каждый столбец представляет собой определённый набор данных. И каждый столбец должен иметь заголовок. Кроме того, в ваших исходных данных не должно быть объединённых ячеек.
Выделите мышкой все ваши данные, которые вы будете использовать для создания сводной таблицы.
В нашем примере вы можете просто выделить столбцы A:G.
Перейдите к новому созданному листу со сводной таблицей. Пока на нём нет данных, но справа появилось меню «Редактор отчетов». При помощи этого меню можно добавить поля в «Строки», «Столбцы», «Значения» и «Фильтр».
Теперь просто нажмите на «Добавить поле» и выберите те данные, которые необходимы для строк и столбцов сводной таблицы, а также для её значений.
К примеру, рассчитаем продажи различных видов шоколада в разных городах.
В поле “Значения” можно настроить тип рассчитываемого итога. Это может быть общая сумма, минимальная или максимальная сумма, средняя стоимость, количество значений и другие варианты. Мы используем для примера общую сумму.
Рекомендуем обратить еще внимание на находящееся в самом низу поле “Фильтр”. Оно позволяет ограничить подсчитываемые данные каким-либо условием. К примеру, рассчитаем итоги продаж только за определённую дату.
Сводная таблица позволяет показать и более сложные группировки данных. Для этого в поле “Строки” или “Столбцы” нужно добавить несколько полей. Вот, например, как можно рассчитать продажи и по городам, и по регионам одновременно.
Важное замечание: в отличие от Excel, сводные таблицы в Google обновляются автоматически. Но мы советуем регулярно проверять, ссылается ли сводная таблица на весь диапазон исходных данных.
Как создать сводную таблицу Google из нескольких диапазонов данных.
Часто случается, что необходимые для анализа данные находятся в разных таблицах. А сводную таблицу можно построить только по одному диапазону данных. Данные сразу из нескольких таблиц при построении сводной таблицы использовать нельзя. Каков же выход?
Для того, чтобы использовать несколько листов в одной сводной таблице, их сначала нужно объединить в одну общую таблицу.
Для такого объединения листов существуют различные решения. Но, учитывая простоту и доступность, мы хотели бы предложить вам использовать следующее дополнение к Google таблицам, которое поможет вам.
Итак, наша сводная таблица готова.
Используем сводную таблицу для анализа данных.
Как видите, даже на самом простом уровне сводная таблица уже позволяет ответить на достаточно важные вопросы.
Однако, вернёмся к вопросам, которые интересуют вашего босса: “Кто мой лучший покупатель?”
Действуя старым и проверенным способом, изменяем строки и столбцы нашей таблицы и получаем искомый результат. Не забудьте отсортировать покупателей с использованием поля “Сумма”.
Аналогичным образом дадим ответы и на другие интересные вопросы. Например, какой товар лучше всего продаётся?
Где мои товары лучше всего продаются?
Где какой товар лучше продаётся?
Итак, в течение 5 минут вы получили при помощи Google таблиц ответы на интересующие вас вопросы. Ваш босс доволен!
Обратите внимание на то, что общий объём продаж одинаков во всех наших сводных таблицах. Это потому, что каждая из них просто представляет одни и те же данные разными способами.
Как создать диаграмму в сводной Google таблице.
Ещё более наглядными становятся наши данные при использовании графиков и диаграмм. Добавить диаграмму к сводной таблице можно двумя способами.
Вот что можно получить по умолчанию из данных нашей последней таблицы, где мы анализировали, какой товар где лучше продаётся.
Есть и второй способ создать диаграмму.
В Google таблицах есть дополнительный сервис, которого ранее не было ни в одной из программ работы с таблицами. В правом нижнем углу есть кнопка “Анализ данных”. Нажимая на нее, вы можете получить различные подсказки и предложения по улучшению представления ваших данных, их анализу, в том числе и созданию диаграмм.
Нажмите значок в правом нижнем углу, и вы сможете не только выбрать самую подходящую диаграмму из нескольких предложенных, но и изменить оформление вашей сводной таблицы.
Вернувшись к тем вопросам, которые мы ранее задавали, получаем диаграмму, которая показывает не только объём покупок каждым из покупателей, но и даёт представление о том, какой именно шоколад предпочитает покупатель.
Обратите внимание, что система анализирует ваши данные и даже отмечает отдельные закономерности, на которые вы сами, возможно, не обратили бы и внимания.
Теперь осталось только отредактировать нашу диаграмму, придать ей наиболее наглядный и читаемый вид, оформить подписи осей и данных. Как это делать – об этом мы поговорим в отдельном материале чуть позже.
Как опубликовать сводную Google таблицу или диаграмму в Интернете.
Если мы выберем лист нашего файла Google таблиц, то мы можем представить его либо как веб-страницу, либо как статический документ в формате PDF. Если мы хотим опубликовать только диаграмму, то варианта также два. Либо это будет интерактивная диаграмма, которая будет изменяться вместе с изменением исходных данных, либо можно просто ограничиться картинкой, показывающей как бы снимок диаграммы в определённый момент времени.
Как видите, вы можете опубликовать свою диаграмму двумя способами. Либо вы используете закладку “Ссылка”, и тогда для публикации используется интернет-ссылка. Этой ссылкой вы можете поделиться любым удобным для вас способом (к примеру, опубликовать ее в соцсетях либо отправить по электронной почте). Второй способ – вы получаете код для встраивания вашей диаграммы непосредственно в код вашего интернет-сайта (если таковой имеется), и все желающие смогут её там посмотреть.
Естественно, любой вариант публикации предусматривает только режим просмотра, ни о какой совместной работе над диаграммой или внесении в нее изменений вашими коллегами речь не идет. Для этого используется совместный доступ к файлу, о котором мы подробно рассказывали.
При помощи этого же меню можно отменить публикацию.
Надеюсь, этот небольшой обзор возможностей сводных таблиц Google вдохновит вас на их использование с вашими собственными данными. Поверьте, вы сразу поймёте, как это легко и удобно. Сводные таблицы помогут вам сэкономить силы и время. Важным для вас будет и то, что созданный сегодня отчет можно легко обновить и использовать в будущем с новыми данными.
Советы по эффективной работе с Google таблицами:
Как использовать сводные таблицы в Таблицах Google (полное руководство)
Russian (Pусский) translation by Ellen Nelson (you can also view the original English article)
Сводные таблицы это излюбленная функция уверенных пользователей Excel. Конечно, Excel тяжеловесный чемпион среди электронных таблиц. Но Google Sheets бесплатный web-инструмент, который идеален для совместной работы и обладает множеством собственных возможностей.
Электронная таблица это просто набор строк и столбцов. При пересечении строк и столбцов образуются ячейки. Это то куда вы записываете данные, используя формулы. С ростом таблицы и появлением всё больше и больше записей, вам понадобится инструменты для понимания этих данных.
Сводные таблицы работают с большим набором данных и суммирует их с помощью инструмента перетаскивания.
Это включает в себя возможность создания сводных таблиц, которые помогут вам разобраться в ваших таблицах и найти в них смысл. С помощью сводной таблицы вы можете создавать отчеты, которые помогут вам лучше понять исходные данные.
Построение сводной таблицы в Google Sheets проще чем вы думаете. В этом уроке, я научу вас как использовать Google Sheets для создания мощных сводных таблиц, которые кромсает и формирует ваши данные прямо в веб-браузере.
Как быстро использовать сводные таблицы в Google Sheets (смотри и учись)
В видеоуроке ниже, я помогу вам начать работать с Google Sheets, чтобы вы смогли проанализировать ваши данные. К концу видео, вы узнаете как использовать сводные таблицы по версии Google, для обработки больших объемов данных и их понимания.
Продолжайте читать иллюстрированное, пошаговое руководство по использованию сводных таблиц в Google Sheets. Читайте и следуйте:
Почему сводные таблицы?
Проблема не в нехватке данных. Проблема в том, что у всех у нас слишком много данных, в которых мы должны найти осмысленные способы взглянуть на набор данных.
Сводные таблицы это лишь инструмент, который мы можем использовать для анализа наших данных. Они помогают нам брать большой набор данных и создавать отчеты, которые отвечают на наши вопросы.
Просмотрите на снимок экрана ниже, чтобы увидеть мощь сводных таблиц. Все они связаны с одним и тем же набором данных, но имеют разные представления итоговых данных. Я собрал свою внештатную работу, показав счета моих клиентов, топ-проекты и клиентские счета за год.
Мы используем одни и те же исходные данные, но каждая из этих сводных таблиц, помогает нам увидеть наши данные по-другому; мы можем видеть итоговые суммы проекта слева направо по клиентам, типу проекта и году.
Сводные таблицы берут ваши исходные данные и помогают вам лучше понять их, группируя и подводя итог ваших данных. Давайте узнаем, как использовать сводные таблицы в Google Sheets.
1. Загружаем пример данных
К этому уроку я предлагаю пример набора данных, на которых вы можете учиться, повторяя то, что я делаю. Я создал липовые данные «счетов клиентов» или суммы, на которые мы выставили счета нашим клиентам за фиктивную нештатную работу.
Данные в формате CSV (значения разделены запятой) или необработанные данные, которые вы импортируете в таблицы. Загрузите и распакуйте этот файл, найдите там файл «sample-data.csv«.
Вот как импортировать данные в Таблицы Google:
Шаг 1. Создайте новую таблицу
Заходим на Google Drive и начинаем создание нового файла таблицы. Импортирование лучше начинать с пустого листа.
Шаг 2. Импорт данных в Google Sheets
Найдите пункт меню Файл > Импорт. Укажите на файл sample-data.csv для его импорта.
Шаг 3. Выбираем параметры
Я оставлю опции по умолчанию. Я предпочитаю Создать таблицу для создания отдельного файла и автоматическое обнаружение разделителей. Нажмите Импортировать для завершения.
Когда импортирование будет завершено, то вы увидите сверху ссылку на ваши данные, нажав на которые, вы увидите загруженные данные в таблицу Google.
2. Вставка сводной таблицы в Google Таблицы
Теперь у нас есть необработанные данные. Для продолжения выделим столбцы, в которых хранятся данные, затем перейдем Данные > Сводная таблица, для вставки сводной таблицы.
Выделите столбцы с данными, затем перейдите в Данные > Сводная таблица.
Изучаем редактор отчетов
Теперь мы готовы начать обработку наших данных. Это означает, что мы будем использовать Редактор отчётов Таблиц Google, для построения различных представлений наших данных, чтобы они лучше читались.
Для сводных таблиц берутся большие таблицы исходных данных и размещаются в отчёте так, что вы можете их понимать. Редактор отчётов это инструмент таблиц для построения вашей сводной таблицы и лучшего понимания ваших данных.
Давайте познакомимся с Редактором отчётов. В Таблицах Google есть четыре опции на левой стороне, для размещения данных в сводной таблице в: Строки, Столбцы, Значения, и Фильтр.
Редактор отчётов позволяет вам создавать отчёты и представления данных, таким образом, каким вы хотите их видеть.
Если мы нажмём Добавить поле в любом варианте, вы увидите названия столбцов ваших исходных данных. Нажмите на название столбца, чтобы добавить данные в заданном формате.
В этом и состоит магия сводных таблиц. Вы можете взять любые столбцы своих исходных данных и построить отчёт, размещая поля исходных данных в сводной таблице.
Например, если я нажму Добавить поле рядом со Строки, а затем нажму Client, вы увидите следующее:
Сводная таблица показывает каждого из моих клиентов на отдельной строке. Сводная таблица берёт исходные данные и отображает их в отчёте. Мы можем продолжить и добавить, выставленные счета для каждого клиента, в редактор отчётов.
Вот, что делает каждая из четырёх опций в редакторе отчётов, когда вы добавляете в него поле:
Посмотрите на скриншот ниже, чтобы увидеть полный отчёт, который я сделал с помощью таблиц Google. Я указал клиентов в строки, типы проектов в столбцы. Я также разместил выставленные счёта в полях значения, чтобы увидеть количество комбинации.
Потратьте несколько минут, чтобы рассмотреть редактор отчётов, вписывая различные столбцы данных, в разные области редактора отчётов.
3. Анализ данных с помощью сводных таблиц
Всё настроено, пришло время использовать сводные таблицы, чтобы получше разобраться в нашем наборе данных. Давайте по-другому взглянем в нашей исходной таблице:
В каждой строке есть несколько ключевых данных о проекте, который мы сделали для клиента:
Поскольку каждая строка представляет собой комбинацию клиент-проект, трудно обобщить наши данные.
Сводная таблица может помочь нам быстро ответить на вопросы по этими данными:
Давайте посмотрим на то, как построить такие сводные таблицы ниже:
Какому клиенту мы выставили больше всего счетов?
Вся идея сводных таблиц заключается в том, что мы можем использовать их для понимания наших таблиц. У нас, конечно, нет времени, чтобы вручную вычислить эти вещи, поэтому сводные таблицы — дадут нам ответ.
Для нашего первого примера, давайте попробуем найти, сколько мы выставили счет каждому клиенту. Нам нужно начать с выборки клиента, а затем отобразить суммы для каждого клиента.
Давайте разместим клиентов (client) по строкам, а amount billed в поле значения:
Это простой пример, но он, как раз то, что доктор прописал. В исходных данных было бы сложно подвести подитог по клиенту, а сводная таблица делает это на одном дыхании.
Какой тип проекта имеет самую высокую почасовую ставку?
На этот раз, давайте заменим клиентов на тип проекта. Так как мы хотим увидеть почасовую ставку за проект, давайте уберём поля, добавленные в первом примере. Вы можете сделать это, нажав на X для каждого поля в редакторе отчёта.
Теперь, давайте разместим project type по строкам, а hourly rate в раздел значений нашего отчёта:
Однако мы не хотим видеть сумму почасовых ставок. Мы предпочли бы видеть средние почасовые ставки.
Нажмите на стрелочку рядом с Суммировать по и выберите AVERAGE. Это изменяет способ отображения сумм; теперь, мы видим среднюю часовую ставку для клиента.
Этот пример является отличной иллюстрацией того, что вы можете просматривать суммы по-разному. Попробуйте другие значения в этом выпадающем списке, например count, max, min и другие.
Выставленные счёта по годам
Теперь давайте разложим наши данные и разделим их по годам.
На этот раз по вашему выбору: вы можете поместить либо клиента, либо тип проекта в виде строк в сводной таблице. В любом случае, забросьте Year в раздел столбцов. Это разбивает счета для каждого клиента по годам и помещает каждого клиента в свою собственную строку.
Все эти сводные таблицы привязаны к одному и тому же источнику данных. Построение этих отчетов вообще не заменяет или изменяет исходные данные. Это даёт нам возможность лучше анализировать и просматривать наши данные.
Узнайте больше о Таблицах Google
Таблицы Google являются основным продуктом, который я применяю для своей внештатной работы. Я использую его для организации творческой работы, планирования проектов и сверки дохода, который я ожидаю получить с клиентов.
Лучше всего в Таблицах это то, что это особый гибрид между традиционным приложением таблиц и подключенным веб-инструментом. У вас есть приложение мощь приложения таблиц для внесения ваших данных и использования формул. А также у вас инструмент, который подключается к веб-службам и может легко получать данные.
Вот ещë уроки, которые помогут вам увидеть невероятный потенциал, который предлагают Таблицы. Посмотрите, хотя бы, один из них, чтобы узнать больше:
Как вы используете сводные таблицы в Таблицах Google? Или есть ли другие инструменты, которые вы используете для анализа данных ваших таблиц? Дайте мне знать в комментариях ниже.