вычисляемое поле в сводной таблице гугл
Cоветы и руководства по Google Таблицам
Мастер Google Таблиц с полезными советами и руководствами
Как добавить и использовать вычисляемые поля в сводных таблицах Google Таблиц
Если вы работали со сводными таблицами, вы бы знали, что они являются отличным способом обобщения больших наборов данных. Во-первых, потому что они позволяют группировать данные различными способами, а во-вторых, они позволяют использовать ряд обобщающих показателей для анализа ваших данных.
Для повышения универсальности сводные таблицы также имеют функцию «Расчетное поле», которая позволяет дополнительно настраивать результаты с помощью функций и формул.
В этом руководстве мы продемонстрируем на примере, как вы можете использовать вычисляемые поля в сводной таблице для дальнейшего использования ее аналитических возможностей.
Что такое вычисляемые поля в Google Таблицах?
Сводная таблица предоставляет ряд встроенных показателей, которые вы можете использовать для анализа данных.
К ним относятся большинство стандартных сводных показателей, таких как среднее значение, медиана, дисперсия и т. Д. Однако часто необходимо выполнить определенные вычисления, которые могут быть недоступны во встроенных параметрах.
Здесь на помощь приходят вычисляемые поля. Вычисляемые поля позволяют обрабатывать данные для предоставления более настраиваемых результатов в сводной таблице.
Например, что, если вы хотите добавить НДС к отпускным ценам на товары в определенной торговой точке? Конечно, имеет смысл добавить формулу для этого в исходный набор данных.
Однако что, если вы хотите, чтобы это происходило только в сводной таблице, а исходные данные оставить нетронутыми?
Вычисляемые поля позволяют использовать настраиваемые формулы для отображения сводных показателей в сводной таблице.
Как использовать вычисляемые поля в сводных таблицах в Google Таблицах
Допустим, у вас есть следующий набор данных:
Предположим, из приведенного выше набора данных вы хотите создать сводную таблицу, в которой будет отображаться следующее:
Для этого нужно двигаться шаг за шагом. Это означает, что вам необходимо:
Давайте пройдемся по этим шагам один за другим.
Создание сводной таблицы для отображения общей суммы продаж для каждого продукта
Чтобы создать сводную таблицу, в которой будет отображаться общая сумма продаж по продуктам, вам необходимо выполнить следующие шаги:
Здесь отображается общая сумма продаж по каждому продукту, как показано ниже:
А что, если вы также хотите увидеть, что произойдет, если вы добавите 5% НДС к общей сумме продаж каждого продукта?
В категории «Значения», если вы щелкните раскрывающийся список под «Суммировать по», вы заметите, что нет опции для добавления 5%.
Это означает, что вам нужно будет определить собственный расчет самостоятельно. Это легко сделать, добавив вычисляемое поле.
Добавление вычисляемого поля, суммированного с помощью SUM
Теперь вы хотите добавить 5% к общей сумме продаж каждого товара и отобразить ее в новом столбце. Поскольку расчет должен выполняться для общей суммы продаж (SUM значений SellingPrice для каждого элемента), ваше вычисленное поле необходимо суммировать с помощью SUM.
Вот шаги, которые необходимо выполнить, если вы хотите добавить 5% НДС к общей сумме продаж для каждого продукта:
Примечание. Поскольку мы хотели добавить сумму НДС к общему объему продаж для каждого продукта, мы оставили в поле «Суммировать по» значение по умолчанию «СУММ». Если вы щелкните раскрывающийся список под «Суммировать по», вы заметите, что у вас есть только два варианта: «СУММ» и «Пользовательский».
Если вы хотите отобразить минимальное количество проданных единиц для каждого предмета, вам нужно будет использовать отдельные значения «Единицы» из исходного набора данных в вашей настраиваемой формуле вместо СУММ. Мы увидим, как это сделать, в следующем разделе.
Добавление вычисляемого поля, обозначенного как Custom
Теперь мы хотим найти минимальное количество проданных единиц для каждого продукта. Обратите внимание, что мы хотим использовать для каждого продукта отдельные единицы, проданные в определенный день, а не СУММУ проданных единиц. Это означает, что наше вычисляемое поле не может быть суммировано с помощью SUM. Существует еще один вариант «Суммировать по», и это вариант «Пользовательский».
Вот шаги, которые вам нужно выполнить, если вы хотите найти минимальное количество проданных единиц для каждого продукта:
Примечание. Поскольку мы хотели найти минимальное количество проданных единиц для каждого продукта, мы изменили поле «Суммировать по» на «Пользовательский» вместо SUM.
Важные моменты о вычисляемых полях
Вычисляемые поля обеспечивают большую гибкость и универсальность сводных таблиц. Однако у него все же есть определенные ограничения. Поэтому при создании вычисляемых полей важно помнить об определенных моментах.
В этом руководстве мы показали вам несколько простых примеров использования сводных таблиц с вычисляемыми полями.
Как видите, вычисляемые поля помогают сделать ваши сводные таблицы более мощными, поскольку они позволяют настраивать сводки и результаты по своему вкусу. Мы надеемся, что вам понравился этот урок и он оказался полезным.
Как создать сводную Google таблицу
У вас так много данных, что от множества строк и колонок с данными разбегаются глаза? Вы тонете в море цифр и не можете понять, что происходит? Предположим, вы работаете в компании, занимающейся продажей шоколада в нескольких регионах и работающей с множеством покупателей.
Ваш босс поставил вам задачу – вам необходимо на основе данных о продажах определить лучшего покупателя, самый популярный продукт и наиболее выгодный регион сбыта.
Не спешите впадать в панику и вспоминать, как правильно использовать такие традиционные функции как СЧЕТЕСЛИ, СУММЕСЛИ и подобные им. Расслабьтесь, сделайте глубокий вдох. Эта задача весьма просто решается при помощи сводных таблиц Google.
Что такое Сводные таблицы Google Sheets?
Сводная таблица поможет представить ваши данные в удобном и презентабельном виде.
Главное удобство сводных таблиц состоит в том, что поля в них можно интерактивно перемещать, а данные в них можно фильтровать, группировать, сортировать, рассчитывать итоги и средние значения. Вы можете менять строки и столбцы местами, изменять уровни детализации представления. Это позволяет не только менять внешний вид таблицы, но и взглянуть на ваши данные под совсем другим углом.
Важно также и то, что при этом ваши исходные данные не меняются – что бы вы не делали с вашей сводной таблицей. Вы просто выбираете такой способ их отображения, который позволит вам увидеть новые закономерности и связи. Ваши данные в сводной таблице будут разделены на группы, а огромный объем информации будет представлен в понятной и доступной для анализа форме.
Краткое руководство по сводным таблицам в Google таблицах.
Вот как, к примеру, выглядят данные о ваших продажах, хранящиеся в Google таблице.
Откроем таблицу, в которой содержатся наши исходные данные по продажам. Важно, чтобы данные, которые вы будете использовать, были расположены по столбцам. Каждый столбец представляет собой определённый набор данных. И каждый столбец должен иметь заголовок. Кроме того, в ваших исходных данных не должно быть объединённых ячеек.
Выделите мышкой все ваши данные, которые вы будете использовать для создания сводной таблицы.
В нашем примере вы можете просто выделить столбцы A:G.
Перейдите к новому созданному листу со сводной таблицей. Пока на нём нет данных, но справа появилось меню «Редактор отчетов». При помощи этого меню можно добавить поля в «Строки», «Столбцы», «Значения» и «Фильтр».
Теперь просто нажмите на «Добавить поле» и выберите те данные, которые необходимы для строк и столбцов сводной таблицы, а также для её значений.
К примеру, рассчитаем продажи различных видов шоколада в разных городах.
В поле “Значения” можно настроить тип рассчитываемого итога. Это может быть общая сумма, минимальная или максимальная сумма, средняя стоимость, количество значений и другие варианты. Мы используем для примера общую сумму.
Рекомендуем обратить еще внимание на находящееся в самом низу поле “Фильтр”. Оно позволяет ограничить подсчитываемые данные каким-либо условием. К примеру, рассчитаем итоги продаж только за определённую дату.
Сводная таблица позволяет показать и более сложные группировки данных. Для этого в поле “Строки” или “Столбцы” нужно добавить несколько полей. Вот, например, как можно рассчитать продажи и по городам, и по регионам одновременно.
Важное замечание: в отличие от Excel, сводные таблицы в Google обновляются автоматически. Но мы советуем регулярно проверять, ссылается ли сводная таблица на весь диапазон исходных данных.
Как создать сводную таблицу Google из нескольких диапазонов данных.
Часто случается, что необходимые для анализа данные находятся в разных таблицах. А сводную таблицу можно построить только по одному диапазону данных. Данные сразу из нескольких таблиц при построении сводной таблицы использовать нельзя. Каков же выход?
Для того, чтобы использовать несколько листов в одной сводной таблице, их сначала нужно объединить в одну общую таблицу.
Для такого объединения листов существуют различные решения. Но, учитывая простоту и доступность, мы хотели бы предложить вам использовать следующее дополнение к Google таблицам, которое поможет вам.
Итак, наша сводная таблица готова.
Используем сводную таблицу для анализа данных.
Как видите, даже на самом простом уровне сводная таблица уже позволяет ответить на достаточно важные вопросы.
Однако, вернёмся к вопросам, которые интересуют вашего босса: “Кто мой лучший покупатель?”
Действуя старым и проверенным способом, изменяем строки и столбцы нашей таблицы и получаем искомый результат. Не забудьте отсортировать покупателей с использованием поля “Сумма”.
Аналогичным образом дадим ответы и на другие интересные вопросы. Например, какой товар лучше всего продаётся?
Где мои товары лучше всего продаются?
Где какой товар лучше продаётся?
Итак, в течение 5 минут вы получили при помощи Google таблиц ответы на интересующие вас вопросы. Ваш босс доволен!
Обратите внимание на то, что общий объём продаж одинаков во всех наших сводных таблицах. Это потому, что каждая из них просто представляет одни и те же данные разными способами.
Как создать диаграмму в сводной Google таблице.
Ещё более наглядными становятся наши данные при использовании графиков и диаграмм. Добавить диаграмму к сводной таблице можно двумя способами.
Вот что можно получить по умолчанию из данных нашей последней таблицы, где мы анализировали, какой товар где лучше продаётся.
Есть и второй способ создать диаграмму.
В Google таблицах есть дополнительный сервис, которого ранее не было ни в одной из программ работы с таблицами. В правом нижнем углу есть кнопка “Анализ данных”. Нажимая на нее, вы можете получить различные подсказки и предложения по улучшению представления ваших данных, их анализу, в том числе и созданию диаграмм.
Нажмите значок в правом нижнем углу, и вы сможете не только выбрать самую подходящую диаграмму из нескольких предложенных, но и изменить оформление вашей сводной таблицы.
Вернувшись к тем вопросам, которые мы ранее задавали, получаем диаграмму, которая показывает не только объём покупок каждым из покупателей, но и даёт представление о том, какой именно шоколад предпочитает покупатель.
Обратите внимание, что система анализирует ваши данные и даже отмечает отдельные закономерности, на которые вы сами, возможно, не обратили бы и внимания.
Теперь осталось только отредактировать нашу диаграмму, придать ей наиболее наглядный и читаемый вид, оформить подписи осей и данных. Как это делать – об этом мы поговорим в отдельном материале чуть позже.
Как опубликовать сводную Google таблицу или диаграмму в Интернете.
Если мы выберем лист нашего файла Google таблиц, то мы можем представить его либо как веб-страницу, либо как статический документ в формате PDF. Если мы хотим опубликовать только диаграмму, то варианта также два. Либо это будет интерактивная диаграмма, которая будет изменяться вместе с изменением исходных данных, либо можно просто ограничиться картинкой, показывающей как бы снимок диаграммы в определённый момент времени.
Как видите, вы можете опубликовать свою диаграмму двумя способами. Либо вы используете закладку “Ссылка”, и тогда для публикации используется интернет-ссылка. Этой ссылкой вы можете поделиться любым удобным для вас способом (к примеру, опубликовать ее в соцсетях либо отправить по электронной почте). Второй способ – вы получаете код для встраивания вашей диаграммы непосредственно в код вашего интернет-сайта (если таковой имеется), и все желающие смогут её там посмотреть.
Естественно, любой вариант публикации предусматривает только режим просмотра, ни о какой совместной работе над диаграммой или внесении в нее изменений вашими коллегами речь не идет. Для этого используется совместный доступ к файлу, о котором мы подробно рассказывали.
При помощи этого же меню можно отменить публикацию.
Надеюсь, этот небольшой обзор возможностей сводных таблиц Google вдохновит вас на их использование с вашими собственными данными. Поверьте, вы сразу поймёте, как это легко и удобно. Сводные таблицы помогут вам сэкономить силы и время. Важным для вас будет и то, что созданный сегодня отчет можно легко обновить и использовать в будущем с новыми данными.
Советы по эффективной работе с Google таблицами:
Сводные таблицы в Google Spreadsheets
Сводные таблицы в Google Таблицах имеют более скромный функционал по сравнению с Excel, но все же остаются полезным инструментом.
Добавить новую сводную таблицу можно следующим образом:
Рассмотрим пример: из таблицы с книгами получим сводную картину по тематикам. Исходная таблица выглядит так:
Для этого в редакторе отчетов в значения перенесем 2015 и 2016 год:
В строки добавим Тематику и отсортируем по убыванию по сумме 2016-го года:
И получим следующий результат:
Рассчитываемое поле
Рассчитываемое поле в сводной таблице позволяет производить дополнительные вычисления. Это аналог вычисляемого поля в Excel.
Добавить его можно, щелкнув на соответствующую кнопку меню после нажатия на “Добавить поле” в редакторе отчетов сводной таблицы:
После этого задайте новому полю имя и введите формулу для расчета.
В примере мы рассчитаем прирост в 2016 году.
Обратите внимание, что названия полей в формуле указываются в апострофах. Они соответствуют названиям столбцов исходной таблицы, на основе которой вы построили сводную.
Как видите, в сводной, как и в обычных диапазонах, можно менять формат ячеек. У ячеек с вычисляемым полем мы установили процентный формат.
В Google Таблицах, в отличие от Excel, можно напрямую ссылаться на ячейки сводной в формулах, без помощи функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GETPIVOTDATA).
То есть мы могли получить такой же результат и без рассчитываемого поля:
Но это менее предпочтительный вариант. Если вы поменяете структуру сводной, рассчитываемое поле будет работать для всех строк и любой структуры. А формулы придется протягивать вручную.
P.S. Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ в Google Таблицах есть. Какой бы у вас ни был язык формул, ее название нужно вводить на английском: GETPIVOTDATA.
Открытые диапазоны в сводных таблицах
Поэтому в случаях, если нужно работать с открытым диапазоном и данные будут постоянно пополняться, лучше использовать функцию QUERY, а не сводную.
Но! Диапазон вида A:E для сводных работать будет. Так что если исходные данные начинаются с первой же строки листа и будут пополняться, используйте диапазон без номеров строк.
Как использовать сводные таблицы в Таблицах 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? Или есть ли другие инструменты, которые вы используете для анализа данных ваших таблиц? Дайте мне знать в комментариях ниже.