Как добавить и использовать вычисляемые поля в сводных таблицах Google Таблиц

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

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

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

Что такое вычисляемые поля в Google Таблицах?

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

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

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

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

Однако что, если вы хотите, чтобы это происходило только в сводной таблице, а исходные данные оставить нетронутыми?

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

Как использовать вычисляемые поля в сводных таблицах в Google Таблицах

Допустим, у вас есть следующий набор данных:

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

Для этого нужно двигаться шаг за шагом. Это означает, что вам необходимо:

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

Давайте пройдемся по этим шагам один за другим.

Создание сводной таблицы для отображения общей суммы продаж для каждого продукта

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

• Щелкните меню «Данные» на ленте меню.
• В появившемся раскрывающемся меню выберите параметр «Сводная таблица».
• Теперь вы должны увидеть окно с вопросом, хотите ли вы вставить сводную таблицу на существующий лист или на новый лист. Выберите тот вариант, который вам больше нравится. Для наглядности всегда лучше создавать его на новом листе.
• Щелкните по кнопке Create.
• Это должно создать вашу сводную таблицу либо на том же листе, либо на новом листе, в зависимости от того, что вы выбрали на шаге 3.
• Ваша сводная таблица на этом этапе должна выглядеть как на снимке экрана, показанном ниже:
• Должна быть сетка, отображающая «Строки», «Столбцы» и «Значения».
• Теперь вы можете начать заполнять сводную таблицу необходимыми данными. В правой части окна вы должны увидеть редактор сводной таблицы. Это поможет вам указать, что должно быть в вашей сводной таблице.
• Теперь мы хотим, чтобы в нашей сводной таблице было два столбца (изначально) — Товар и общая продажная цена. Итак, в категории «Строки» нажмите «Добавить».
• В появившемся раскрывающемся списке выберите элемент. Это добавит каждое уникальное имя элемента в отдельные строки вашей сводной таблицы.
• Затем мы хотим увидеть общую сумму продаж для каждого товара. Итак, в категории «Значения» нажмите «Добавить».
• В появившемся раскрывающемся списке выберите «SalesPrice». Это отобразит сумму всех продажных цен для каждого товара.

Здесь отображается общая сумма продаж по каждому продукту, как показано ниже:

В категории «Значения», если вы щелкните раскрывающийся список под «Суммировать по», вы заметите, что нет опции для добавления 5%.

Добавление вычисляемого поля, суммированного с помощью SUM

Теперь вы хотите добавить 5% к общей сумме продаж каждого товара и отобразить ее в новом столбце. Поскольку расчет должен выполняться для общей суммы продаж (SUM значений SellingPrice для каждого элемента), ваше вычисленное поле необходимо суммировать с помощью SUM.

Вот шаги, которые необходимо выполнить, если вы хотите добавить 5% НДС к общей сумме продаж для каждого продукта:

• Щелкните любую ячейку сводной таблицы.
• В категории «Значения» нажмите «Добавить».
• В появившемся раскрывающемся списке выберите параметр «Расчетное поле».
• Теперь вы увидите новый столбец в сводной таблице с надписью «Расчетное поле».
• Вы можете изменить это имя прямо в сводной таблице. Назовем его «Сумма после НДС».
• Вы также должны увидеть некоторые параметры для вычисляемого поля в редакторе сводной таблицы.
• В поле ввода в разделе Формула вы можете ввести формулу, которая будет использоваться для результатов вычисляемого поля.
• Поскольку вы хотите отобразить сумму, полученную после добавления 5% к общей сумме продаж, введите формулу: = SalesPrice + ((5/100) * SalesPrice). Обратите внимание, что переменная SalesPrice здесь относится к столбцу SalesPrice в исходном наборе данных.
• Теперь это должно отображать результаты нашей настраиваемой формулы в новом созданном вычисляемом поле.

Примечание. Поскольку мы хотели добавить сумму НДС к общему объему продаж для каждого продукта, мы оставили в поле «Суммировать по» значение по умолчанию «СУММ». Если вы щелкните раскрывающийся список под «Суммировать по», вы заметите, что у вас есть только два варианта: «СУММ» и «Пользовательский».

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

Добавление вычисляемого поля, обозначенного как Custom

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

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

• Щелкните любую ячейку сводной таблицы.
• В категории «Значения» нажмите «Добавить».
• В появившемся раскрывающемся списке выберите параметр «Расчетное поле».
• Теперь вы увидите новый столбец в сводной таблице с надписью «Расчетное поле 2». Вы можете изменить это имя прямо в сводной таблице. Давайте переименуем его в «Минимальное количество проданных единиц».
• Вы также должны увидеть некоторые параметры для вычисляемого поля в редакторе сводной таблицы.
• В поле ввода в разделе Формула вы можете ввести формулу, которая будет использоваться для результатов вычисляемого поля.
• Поскольку вы хотите отобразить минимальное количество проданных единиц, введите формулу: = MIN (единиц). Обратите внимание, что переменная «Единицы» здесь относится к столбцу «Единицы» в исходном наборе данных.
• Щелкните раскрывающийся список под «Суммировать по» и выберите «Пользовательский».
• Теперь это должно отображать результаты нашей настраиваемой формулы в новом созданном вычисляемом поле.

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

Важные моменты о вычисляемых полях

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

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

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

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