Как добавить вычисляемое поле в сводную таблицу
Сводные таблицы в excel — уже сами по себе мощный инструмент работы с данными. Однако, использования стандартного функционала сводных таблиц может быть недостаточно. Иногда нужно произвести дополнительные вычисления и получить поля, которых нет в исходной таблице данных. Тогда на помощь приходят инструменты Вычисляемое поле и Вычисляемый объект для сводных таблиц Excel.
Как построить сводную таблицу в Excel
В этой статье:
- Что такое вычисляемое поле и для чего оно нужно
- Как создать вычисляемое поле в сводной таблице Excel
- Альтернатива № 1 вычисляемому полю: столбец с расчетом в исходной таблице
- Почему не всегда можно применять расчетный столбец для вычислений в сводной таблице
- Альтернатива № 2 вычисляемому полю: вычисления вне диапазона сводной таблицы
- Что такое вычисляемый объект
- Как создать вычисляемый объект
- Удаление и изменение вычислений в сводных таблицах
- Недостатки использования вычислений в сводных таблицах excel
- Как получить формулы вычислений
Что такое Вычисляемое поле и для чего оно нужно
Вычисляемое поле – это виртуальное поле данных, создаваемое в результате вычислений, основанных на существующих полях сводной таблицы.
Другими словами, это данные, которые возникают в результате расчетов и попадают в готовом виде в сводную таблицу. В исходной таблице они не фиксируются. При этом, если в исходной таблице данных происходят изменения (например, добавились новые строки), вычисляемое поле также пересчитается.
Проще всего понять, как работает вычисляемое поле, на примере.
Имеем таблицу с данными о выручке в торговых точках сети магазинов.
В таблице есть данные о выручке и количестве чеков. Если нужно получить величину среднего чека для каждой торговой точки или для категории торговых точек, нужно выручку разделить на количество чеков. Для этой операции отлично подойдет инструмент Вычисляемое поле.
Для создания вычисляемого поля “Средний чек” используются имеющиеся в таблице поля “Выручка” и “Кол-во чеков”. Однако, поле “Средний чек” будет добавлено только в сводную таблицу, но в исходной таблице его не будет.
У вас может возникнуть резонный вопрос: а зачем морочить голову вычисляемыми полями, когда такой же столбец можно добавить в исходную таблицу? Иногда это действительно так.
Но у такого метода есть ряд ограничений и недостатков. В первую очередь, иногда невозможно или неудобно внести изменения в исходную таблицу. Во-вторых, при следующем обновлении исходной таблицы в нее могут добавиться новые столбцы, и тогда ваши расчеты затрутся.
Чек-лист идеальной сводной таблицы в Excel
Как создать вычисляемое поле в сводной таблице Excel
1) Для начала создадим сводную таблицу, в строки которой добавим категорию торговой точки. В значения — сумму по полю Выручка и сумму по полю Кол-во чеков.
2) Установим курсор на любой ячейке сводной таблицы и перейдем на вкладку Анализ — блок Вычисления — Поля, элементы и наборы — Вычисляемое поле…
3) Зададим имя вычисляемого поля. Оно не должно повторять ни одного наименования поля в исходной таблице.
4) Теперь напишем формулу, по которой вычисляемое поле будет производить расчет. Нам нужно поле Выручка разделить на поле Кол-во чеков.
Для этого в блоке Поля выделим поле Выручка и нажмем кнопку Добавить поле.
Оно появилось в поле Формула.
Теперь нужно написать оператор деления “/” и таким же образом указать поле Кол-во чеков.
В итоге получим такую формулу:
Если вы достаточно внимательны, то заметили, что название поля Выручка указано без кавычек, а название Кол-во чеков заключено в одинарные кавычки. Это связано в тем, что во втором случае (‘Кол-во чеков’) название поля состоит из нескольких слов. Excel автоматически проставляет эти кавычки, поэтому добавлять или убирать их вручную не нужно.
5) Осталось нажать Ок, и новое вычисляемое поле автоматически добавилось в таблицу.
Немного поправим его формат (уберем хвост знаков после запятой), и вот что получилось.
При этом исходная таблица не изменилась, в ней по-прежнему нет поля Средний чек.
Альтернатива № 1 вычисляемому полю: столбец с расчетом в исходной таблице
В нашем случае можно использовать альтернативу вычисляемому полю. В исходной таблице данных добавим столбец Средний чек, в первой ячейке которого пропишем простейшую формулу: ячейку из столбца Выручка разделим на ячейку из столбца Средний чек.
Протянем формулу и заполним столбец (если вы делаете расчет в умной таблице, то формула скопируется автоматически до конца столбца)
Мы получили тот же средний чек, но только в разрезе каждой торговой точки. Если же нужно, как в предыдущем примере, получить средний чек по категории точек, то можно попробовать также сделать сводную таблицу.
И здесь мы подобрались к основной причине, почему такой способ — не всегда альтернатива полноценному вычисляемому полю.
Почему не всегда можно применять расчетный столбец для вычислений в сводной таблице
Теперь на основании этой таблицы создадим сводную. Набор полей такой же, как в предыдущем примере, только в поле Значения добавим еще вновь созданный Средний чек.
Анализ среднего чека в продажах
В столбце Средний чек получилась какая-то ерунда. Это потому, что по умолчанию excel просуммировал значения, нам же нужно получить среднее. Щелкнем по треугольнику возле Сумма по полю Средний чек и выберем Параметры полей значений.
Далее выберем Среднее.
Получили средний чек.
И снова самые внимательные заметят, что он не совпадает с тем Средний чеком, который мы получили при помощи вычисляемого поля. Да и если разделить значение из поля Выручка на Кол-во чеков — получим другие данные.
Делаем вывод, что при расчете среднего из средних значений данные могут получиться некорректными.
Если не углубляться в статистику, причина тому — разный вес каждого среднего.
Медиана, среднее арифметическое и мода — как посчитать в Excel
Альтернатива № 2 вычисляемому полю: вычисления вне диапазона сводной таблицы
Часто пользователи просто производят все необходимые вычисления рядом со сводной таблицей при помощи обычных формул.
Добавим столбец Средний чек рядом со сводной таблицей и в строке формул напишем формулу деления Выручки на Кол-во чеков. Даже форматирование сделаем, как в сводной.
Такой способ иногда оправдан — когда это временная таблица, и посчитать надо быстро. Однако, если это регулярный отчет, который может модифицироваться, то лучше им не пользоваться. Почему?
Представим ситуацию, что появилась новая категория торговой точки. Обновим сводную, и видим такую “красоту”. Итоги съехали, надо переделывать вручную.
А если нужно будет увеличить таблицу в ширину, добавив новую детализацию (например, адрес торговой точки), то и вовсе вычисления затрутся.
Таким образом, делаем вывод, что эта альтернатива рабочая, но только для “одноразовых” вычислений. Никак не для постоянных отчетов.
Что такое вычисляемый объект
Вычисляемый объект — это по сути строка вычисляемая строка данных. В отличие от вычисляемого поля, вычисляемый объект добавляет не столбец, а строку.
Также отличие в том, что вычисляемое поле работает со столбцами, а вычисляемый объект — со строками.
Эта операция похожа на группировку данных, и часто группировкой в сводной таблице ее можно заменить. Но часто группируемые строки не имеют общего признака, как в нашем примере ниже.
Как создать вычисляемый объект
Давайте разделим категории торговых точек на еще более укрупненные категории. В категорию “Большие точки” отнесем категории “Крупная” и “Выше среднего”. В категорию “Маленькие точки” — “Микро” и “Средняя”. Как видите, категории не имеют какого-то общего признака, по которому можно сделать агрегацию (точнее, он есть, но только в нашей голове).
Работать будем с той же сводной таблицей.
Щелкнем на любой ячейке в строке таблицы, которую будем группировать.
Важно: именно в строках, а не в числовых значениях!
Далее вкладка Анализ — блок Вычисления — Поля, элементы и наборы — Вычисляемый объект…
Поле, по которому будет делаться группировка, выделено автоматически. В правой части указаны элементы этого поля — в нашем случае категории точек из сводной таблицы.
Зададим имя объекта “Большие точки” и в поле Формула по аналогии с созданием вычисляемого поля зададим формулу. Использовать будем значения из поля Элементы и кнопку Добавить элемент.
Нажмем Ок, и получим группирующую строку внизу таблицы.
Аналогично сделаем вычисляемый объект для группы “Маленькие точки”.
Также добавим ранее созданное вычисляемое поле Средний чек (для полноты картины).
Внизу таблицы располагаются созданные вычисляемые объекты.
Можете заметить, что общий итог в этом случае посчитан неправильно, потому что он суммирует вычисляемые объекты как отдельную строку. Поэтому нужно либо убрать общие итоги, либо оставить в таблице только вычисляемые объекты.
Удаление и изменение вычислений в сводных таблицах
Давайте для примера удалим вычисляемое поле Средний чек.
Откроем меню Вычисляемое поле.
Далее в выпадающем списке выберем поле, которое нужно удалить, и нажмем кнопку Удалить.
Готово, вычисляемое поле удалено.
Точно так же удаляется вычисляемый объект, только через соответствующий пункт меню.
Точно также можно внести изменения в вычисляемое поле (или объект).
Нужно исправить формулу и нажать Ок, кнопку Удалить не нажимать.
Недостатки использования вычислений в сводных таблицах excel
Автоматизация вычислений при помощи вычисляемых полей или вычисляемых объектов имеет свои недостатки. Учитывайте их.
- Вычисления возможны только с данными из сводной таблицы. В них невозможно использовать данные, находящиеся за ее пределами. Даже данные из исходной таблицы, если они не добавлены в сводную — использовать нельзя.
- Вычисляемые объекты по умолчанию никак не выделяются и выглядят как обычная строка. Следовательно, их легко спутать со строкой, и нужно применять дополнительные методы форматирования, например, условное форматирование.
- Некорректный расчет общих итогов при создании вычисляемых объектов (строк).
Как создать сводную таблицу из нескольких таблиц Excel
Как получить формулы вычислений
Чтобы узнать, какие вычисления производились в сводной таблице, нужно щелкнуть в любой ее ячейке, далее вклдака Анализ — блок Вычисления — Поля, элементы и наборы — Вывести формулы
Формулы откроются на отдельном листе.
Это очень полезный инструмент, особенно, когда сводная таблица имеет большое количество вычислений. Или когда автор таблицы не вы, и нужно разобраться в расчетах.
Таким образом, мы прокачали свои навыки работы со сводными таблицами. Их можно использовать, например, при создании отчетов или интерактивных дашбордов.
Условное форматирование в сводной таблице Excel
Сообщество Excel Analytics | обучение Excel
Канал на Яндекс.Дзен
Вам может быть интересно:
Как добавить и использовать вычисляемые поля в сводных таблицах Google Таблиц
Содержание:
Если вы работали со сводными таблицами, вы бы знали, что они являются отличным способом обобщения больших наборов данных. Во-первых, потому что они позволяют группировать данные различными способами, а во-вторых, они позволяют использовать ряд обобщающих показателей для анализа ваших данных.
Для повышения универсальности сводные таблицы также имеют функцию «Расчетное поле», которая позволяет дополнительно настраивать результаты с помощью функций и формул.
В этом руководстве мы продемонстрируем на примере, как вы можете использовать вычисляемые поля в сводной таблице для дальнейшего использования ее аналитических возможностей.
Что такое вычисляемые поля в Google Таблицах?
Сводная таблица предоставляет ряд встроенных показателей, которые вы можете использовать для анализа данных.
К ним относятся большинство стандартных сводных показателей, таких как среднее значение, медиана, дисперсия и т. Д. Однако часто необходимо выполнить определенные вычисления, которые могут быть недоступны во встроенных параметрах.
Здесь на помощь приходят вычисляемые поля. Вычисляемые поля позволяют обрабатывать данные для предоставления более настраиваемых результатов в сводной таблице.
Например, что, если вы хотите добавить НДС к отпускным ценам на товары в определенной торговой точке? Конечно, имеет смысл добавить формулу для этого в исходный набор данных.
Однако что, если вы хотите, чтобы это происходило только в сводной таблице, а исходные данные оставить нетронутыми?
Вычисляемые поля позволяют использовать настраиваемые формулы для отображения сводных показателей в сводной таблице.
Как использовать вычисляемые поля в сводных таблицах в Google Таблицах
Допустим, у вас есть следующий набор данных:
- Общий объем продаж различных товаров.
- Сумма, полученная после добавления 5% к общей сумме продаж для каждого продукта.
- Минимальное количество проданных единиц для каждого предмета.
Для этого нужно двигаться шаг за шагом. Это означает, что вам необходимо:
- Создайте сводную таблицу, в которой будет отображаться общая сумма продаж для каждого продукта.
- Добавьте вычисляемое поле, в котором будут отображаться результаты настраиваемой формулы после добавления 5% НДС к общей стоимости.
- Добавьте вычисляемое поле, в котором будет отображаться настроенная формула после нахождения минимального количества проданных единиц для каждого продукта.
Давайте пройдемся по этим шагам один за другим.
Создание сводной таблицы для отображения общей суммы продаж для каждого продукта
Чтобы создать сводную таблицу, в которой будет отображаться общая сумма продаж по продуктам, вам необходимо выполнить следующие шаги:
- Щелкните меню «Данные» на ленте меню.
- В появившемся раскрывающемся меню выберите параметр «Сводная таблица».
- Теперь вы должны увидеть окно с вопросом, хотите ли вы вставить сводную таблицу на существующий лист или на новый лист. Выберите тот вариант, который вам больше нравится. Для наглядности всегда лучше создавать его на новом листе.
- Щелкните по кнопке Create.
- Это должно создать вашу сводную таблицу либо на том же листе, либо на новом листе, в зависимости от того, что вы выбрали на шаге 3.
- Ваша сводная таблица на этом этапе должна выглядеть как на снимке экрана, показанном ниже:
- Должна быть сетка, отображающая «Строки», «Столбцы» и «Значения».
- Теперь вы можете начать заполнять сводную таблицу необходимыми данными. В правой части окна вы должны увидеть редактор сводной таблицы. Это поможет вам указать, что должно быть в вашей сводной таблице.
- Теперь мы хотим, чтобы в нашей сводной таблице было два столбца (изначально) — Товар и общая продажная цена. Итак, в категории «Строки» нажмите «Добавить».
- В появившемся раскрывающемся списке выберите элемент. Это добавит каждое уникальное имя элемента в отдельные строки вашей сводной таблицы.
- Затем мы хотим увидеть общую сумму продаж для каждого товара. Итак, в категории «Значения» нажмите «Добавить».
- В появившемся раскрывающемся списке выберите «SalesPrice». Это отобразит сумму всех продажных цен для каждого товара.
Здесь отображается общая сумма продаж по каждому продукту, как показано ниже:
В категории «Значения», если вы щелкните раскрывающийся список под «Суммировать по», вы заметите, что нет опции для добавления 5%.
Добавление вычисляемого поля, суммированного с помощью SUM
Теперь вы хотите добавить 5% к общей сумме продаж каждого товара и отобразить ее в новом столбце. Поскольку расчет должен выполняться для общей суммы продаж (SUM значений SellingPrice для каждого элемента), ваше вычисленное поле необходимо суммировать с помощью SUM.
Вот шаги, которые необходимо выполнить, если вы хотите добавить 5% НДС к общей сумме продаж для каждого продукта:
- Щелкните любую ячейку сводной таблицы.
- В категории «Значения» нажмите «Добавить».
- В появившемся раскрывающемся списке выберите параметр «Расчетное поле».
- Теперь вы увидите новый столбец в сводной таблице с надписью «Расчетное поле».
- Вы можете изменить это имя прямо в сводной таблице. Назовем его «Сумма после НДС».
- Вы также должны увидеть некоторые параметры для вычисляемого поля в редакторе сводной таблицы.
- В поле ввода в разделе Формула вы можете ввести формулу, которая будет использоваться для результатов вычисляемого поля.
- Поскольку вы хотите отобразить сумму, полученную после добавления 5% к общей сумме продаж, введите формулу: = SalesPrice + ((5/100) * SalesPrice). Обратите внимание, что переменная SalesPrice здесь относится к столбцу SalesPrice в исходном наборе данных.
- Теперь это должно отображать результаты нашей настраиваемой формулы в новом созданном вычисляемом поле.
Обратите внимание, что переменная SalesPrice здесь относится к столбцу SalesPrice в исходном наборе данных.Примечание. Поскольку мы хотели добавить сумму НДС к общему объему продаж для каждого продукта, мы оставили в поле «Суммировать по» значение по умолчанию «СУММ». Если вы щелкните раскрывающийся список под «Суммировать по», вы заметите, что у вас есть только два варианта: «СУММ» и «Пользовательский».
Если вы хотите отобразить минимальное количество проданных единиц для каждого предмета, вам нужно будет использовать отдельные значения «Единицы» из исходного набора данных в вашей настраиваемой формуле вместо СУММ. Мы увидим, как это сделать, в следующем разделе.
Добавление вычисляемого поля, обозначенного как Custom
Теперь мы хотим найти минимальное количество проданных единиц для каждого продукта. Обратите внимание, что мы хотим использовать для каждого продукта отдельные единицы, проданные в определенный день, а не СУММУ проданных единиц.
Это означает, что наше вычисляемое поле не может быть суммировано с помощью SUM. Существует еще один вариант «Суммировать по», и это вариант «Пользовательский».
Вот шаги, которые вам нужно выполнить, если вы хотите найти минимальное количество проданных единиц для каждого продукта:
- Щелкните любую ячейку сводной таблицы.
- В категории «Значения» нажмите «Добавить».
- В появившемся раскрывающемся списке выберите параметр «Расчетное поле».
- Теперь вы увидите новый столбец в сводной таблице с надписью «Расчетное поле 2». Вы можете изменить это имя прямо в сводной таблице. Давайте переименуем его в «Минимальное количество проданных единиц».
- Вы также должны увидеть некоторые параметры для вычисляемого поля в редакторе сводной таблицы.
- В поле ввода в разделе Формула вы можете ввести формулу, которая будет использоваться для результатов вычисляемого поля.
- Поскольку вы хотите отобразить минимальное количество проданных единиц, введите формулу: = MIN (единиц). Обратите внимание, что переменная «Единицы» здесь относится к столбцу «Единицы» в исходном наборе данных.
- Щелкните раскрывающийся список под «Суммировать по» и выберите «Пользовательский».
- Теперь это должно отображать результаты нашей настраиваемой формулы в новом созданном вычисляемом поле.
Примечание. Поскольку мы хотели найти минимальное количество проданных единиц для каждого продукта, мы изменили поле «Суммировать по» на «Пользовательский» вместо SUM.
Важные моменты о вычисляемых полях
Вычисляемые поля обеспечивают большую гибкость и универсальность сводных таблиц. Однако у него все же есть определенные ограничения. Поэтому при создании вычисляемых полей важно помнить об определенных моментах.
- Формулы вычисляемых полей относятся только к ячейкам исходного набора данных. Они не могут ссылаться на итоги или промежуточные итоги сводной таблицы.
- Вам необходимо использовать имена полей вашего набора данных в формулах вычисляемых полей. Вы не можете ссылаться на отдельные ячейки с их адресами или именами ячеек.
- Важно убедиться, что вы указали правильное имя переменной для полей в формуле. Если имя вашего поля содержит более одного слова с пробелами между ними, вам необходимо заключить имя переменной в одинарные кавычки при включении его в формулу вычисляемого поля.
В этом руководстве мы показали вам несколько простых примеров использования сводных таблиц с вычисляемыми полями.
Как видите, вычисляемые поля помогают сделать ваши сводные таблицы более мощными, поскольку они позволяют настраивать сводки и результаты по своему вкусу. Мы надеемся, что вам понравился этот урок и он оказался полезным.
Как создать вычисляемые поля в сводной таблице
Сводные таблицы — это самые мощные инструменты в Excel для гибкого анализа большого набора данных. Вы можете быстро и легко создать сложный отчет, чтобы обобщить свои выводы из набора данных. Вы даже можете настроить функциональность своей сводной таблицы с помощью вычисляемого поля.
Что такое вычисляемое поле?
Вычисляемое поле — это встроенная функция сводной таблицы, предназначенная для дальнейшего расширения ее функциональности и выполнения вычислений с вашими данными для получения желаемых результатов путем создания собственной формулы. Вы можете использовать вычисляемые поля, чтобы добавить новое поле в свою сводную таблицу, чтобы выполнять и отображать вычисления на основе значений полей в вашем наборе данных.
Другими словами, используя вычисляемые поля, вы можете легко складывать/вычитать значения двух полей; производить расчеты на основе некоторых условий/критериев в формуле, используя данные поля (полей), чтобы показать результаты во вновь добавленном поле в сводной таблице.
Мы можем выполнять различные вычисления в вычисляемых полях, такие как деление, вычитание, умножение двух или более полей, сумма, деленная на количество полей, количество, среднее, средневзвешенное значение, даже операторы ЕСЛИ для выполнения вычислений на основе критериев.
Использование вычисляемого поля в сводной таблице
В этом уроке мы будем использовать набор данных выставки Art Gallery в качестве примера. Давайте представим, что вы менеджер художественной галереи, который хочет сравнить набор данных оценочной стоимости и цены выпуска (фактическая цена продажи) для каждого предмета в определенной определенной категории.
Используя сводную таблицу, вы можете рассчитать сумму Est. значения и сумма цен выпуска для всех предметов в зависимости от их категорий. В дополнение к этому вы можете увидеть, какую скидку вы предложили для каждой категории в результате Est. Разница в стоимости и цене выпуска. И вы также можете увидеть, сколько прибыли / убытка вы заработали в контексте целевой фиксированной ставки дисконтирования, скажем, 50%.
Для этих других требований вы будете работать с вычисляемыми полями в сводной таблице с именами « Предлагаемая скидка », « Целевая цена выпуска » и « Прибыль/убыток». ”
Здесь вы узнаете, как создавать, изменять и добавлять/вычитать 2 поля в сводной таблице, используя этот набор данных выставки Art Gallery.
Давайте посмотрим на приведенную ниже сводную таблицу, в которой вам нужно выполнить базовые вычисления, чтобы суммировать Цена выпуска 9.0018 и Расчетное время. Значение для каждого типа определенной категории.
Теперь нам нужно добавить или создать вышеупомянутые вычисляемые поля в сводную таблицу. Это использует значения этих полей, как показано на изображении выше, в формуле для выполнения вычислений.
Как добавить/создать вычисляемые поля в сводной таблице Вычисляемые поля добавляются одно за другим в следующих шагах.
- Щелкните любую ячейку в сводной таблице.
- На вкладке Анализ в группе Расчеты щелкните Поля, элементы и наборы.
- Щелкните Вычисляемое поле.
Появится диалоговое окно «Вставить вычисляемое поле».
- Введите Имя вычисляемого поля
- Введите формулу
- Нажмите Добавить.
Примечание : используйте кнопку «Вставить поле», чтобы быстро вставлять поля при вводе формулы. Чтобы удалить вычисляемое поле, выберите это поле и нажмите «Удалить» (в разделе «Добавить»).
- Нажмите ОК. Excel автоматически добавляет вычисляемое поле в область значений сводной таблицы.
Повторите эти шаги, чтобы добавить все вычисляемые поля в соответствии со следующими именами и соответствующими им формулами для выполнения вычислений.
- Предлагается скидка. В этом поле будут использоваться значения следующих полей сводной таблицы в приведенной ниже формуле. (См. изображение)
Формула: = («Оценочная стоимость» — «Цена выпуска») / «Оценочная стоимость». Значение’Excel автоматически добавит это поле в область Значения сводной таблицы, но оно будет отображаться как « Сумма предлагаемой скидки ». Нам нужно изменить формат этого поля на «Процент» и отредактировать его имя, чтобы оно отображалось как « Скидка по предложению » (см. Изображение).
Нам нужно сделать следующее, чтобы внести изменения в Формат и Имя поля в соответствии с требованием.
Чтобы изменить его формат с Сумма значений от до Процент , нам нужно сделать следующее:
Щелкните правой кнопкой мыши имя вычисляемого поля и выберите « Параметры поля значений …» (см.
изображение)Затем нажмите кнопку « Числовой формат », чтобы выбрать Процент в качестве параметра формата, и нажмите OK. (см. изображение)
Теперь удерживайте курсор в этом недавно добавленном вычисляемом поле, измените его имя в строке формул выше и нажмите 9.0017 Введите . (см. изображение)
Следуя этим шагам, мы добавим два других вычисляемых поля ниже.
- Целевая цена выпуска. В этом поле будут использоваться значения следующих полей сводной таблицы в приведенной ниже формуле. Формула: = ‘Расч. Значение’ * (1-50%)
- Прибыль/убыток. В этом поле будут использоваться значения следующих полей сводной таблицы в приведенной ниже формуле.
В этом вычисляемом поле мы будем использовать два поля для сложения/вычитания, чтобы произвести вычисления для этого третьего вычисляемого поля; одно поле сводной таблицы и одно вычисляемое поле, как указано ниже. Формула: = «Цена выпуска» — «Целевая цена выпуска» Вы можете видеть, что все три вычисляемых поля были добавлены в сводную таблицу с использованием формул, позволяющих легко выполнять вычисления с использованием существующих полей.
изображение)
Формула: = «Цена выпуска» — «Целевая цена выпуска» Создав вычисляемые поля, вы можете легко изменить любое из них. Просто выберите имя вычисляемого поля из кнопки раскрывающегося списка в разделе «Имя». Затем отредактируйте или измените формулу и нажмите изменить кнопку. См. ниже фотографии.
Здесь вы можете видеть, что мы отредактировали или изменили формулу, изменив процентное значение с 50% до 60%.
Здесь вы можете видеть, что, изменив формулу в одном вычисляемом поле, все соответствующие вычисления были обновлены в сводной таблице.
Функция вычисляемых полей в сводной таблице — это мощный инструмент для выполнения быстрых вычислений.
Есть много других параметров сводной таблицы, которые вы можете изменить для достижения ваших целей расчета.
Если вы не нашли ответ в этой статье, попробуйте связаться с нашими экспертами по ссылке справа. Через несколько секунд вас свяжут с квалифицированным специалистом по Excel, и он решит вашу проблему на месте в режиме реального времени в чате 1:1.
Как добавить вычисляемые поля в сводные таблицы Excel (2023)
Перейти к содержимому Как добавить вычисляемые поля в сводные таблицы Excel (2023)Обычно вам нужно изменить исходные данные, чтобы добавить поле в сводную таблицу . Но с вычисляемым полем вы можете добавить поле в сводную таблицу без предварительного добавления его в исходные данные.
Это создает «воображаемый» столбец, в котором вы можете использовать свои собственные формулы для получения данных или выполнения вычислений.
Это отлично подходит для ситуаций, когда редактирование исходных данных нецелесообразно.
Давайте углубимся в то, как добавить вычисляемое поле в сводную таблицу 🤿
Загрузите образец книги здесь, если хотите присоединиться к ней.
Содержание
Что такое вычисляемые поля
Вы можете создавать вычисляемые поля, используя формулы на основе существующих полей сводной таблицы.
Приведенный ниже пример поможет вам понять вычисляемые поля сводной таблицы.
В приведенной ниже сводной таблице приведены цели продаж сотрудников и фактические данные о продажах 💰
Теперь вам нужно найти достижение продаж для каждого сотрудника 🧑🏻💻
Что произойдет, если вы рассчитаете процент достижения продаж вне сводной таблицы?
Тогда расчеты могут не обновляться при обновлении сводной таблицы.
Существует простой способ добавить новое вычисляемое поле в сводную таблицу в виде процента достижений.
Вам не нужно идти и добавлять новый расчет к вашим исходным данным.
Это добавление вычисляемого поля в сводную таблицу Excel 🥳
Как добавить вычисляемые поля
Вы можете добавить вычисляемое поле в качестве процента достижений в сводную таблицу, выполнив следующие шаги 🪜
- Выберите любую ячейку в сводную таблицу, чтобы активировать вкладку «Анализ сводной таблицы», и щелкните ее.
- Разверните параметр «Поля, элементы и наборы» в группе «Вычисления».
- Нажмите «Вычисляемое поле…», чтобы открыть диалоговое окно «Вставить вычисляемое поле».
- Введите имя вычисляемого поля в поле имени.
Итак, вы можете ввести «Достижение%» в поле имени.
- Введите формулу вычисляемого поля.
Вы можете выбрать поле и дважды щелкнуть или нажать кнопку «Вставить поле», чтобы добавить поле в поле формулы.
В этом поле формул можно создавать собственные формулы вычисляемых полей.
В этом случае необходимо ввести следующую формулу.
= «Фактические продажи»/ «Цели продаж»
- Нажмите кнопку «Добавить» или кнопку «ОК», чтобы добавить рассчитанное поле в сводную таблицу Excel.
Вы можете видеть, что вычисляемое поле появляется в сводной таблице и в области значений поля полей сводной таблицы.
Вы можете использовать предпочтительное числовое форматирование для вычисляемого поля.
Совет для профессионалов:
Хотите получить все формулы, которые вы использовали для вычисляемых полей, в сводной таблице Excel?
Вы должны выполнить следующие шаги.
- Разверните параметр «Поля, элементы и наборы» на вкладке «Анализ сводной таблицы».
- Выберите «Список формул».
Excel отображает все сведения о вычисляемых полях сводной таблицы Excel на новом листе.
В этом случае вы можете увидеть формулу сводной таблицы, которую вы использовали для вычисляемого поля 🤩
При необходимости вы можете изменить или удалить существующее вычисляемое поле.
- Чтобы изменить,
- Выберите имя поля из списка Вставить имена вычисляемых полей.
- Внесите изменения в поле имени и/или поле формулы.
- Нажмите кнопку «Изменить».
- Для удаления вычисляемых полей;
- Выберите имя поля из списка Вставить имена вычисляемых полей.
- Нажмите кнопку удаления.
- Выберите имя поля из списка Вставить имена вычисляемых полей.
Недостатки вычисляемых полей
Несмотря на то, что вычисляемые поля являются очень полезной функцией, у нее есть некоторые недостатки ☹️
- Вы можете применять вычисляемые поля только в обычных сводных таблицах. Если вы использовали модели данных для сводной таблицы, вычисляемые поля недоступны для вашей сводной таблицы.
- При создании вычисляемых полей нельзя использовать диапазоны имен или ссылки. Из-за этого вы не можете использовать XLOOKUP, VLOOKUP, INDEX и функции, которые используют ссылки для формул вычисляемого поля.
- Может давать неверные промежуточные или общие итоги для вычисляемых полей сводной таблицы. Вы можете вставить новое вычисляемое поле в качестве комиссии, чтобы понять этот момент.
Вы можете использовать следующую формулу для расчета значения комиссии на основе общего объема продаж.
= ЕСЛИ («Достижение%»> 100%, «Факт продаж» * 10%, 0)
Вы получите следующий вывод.
Общая сумма комиссии рассчитывается как фактический общий объем продаж, умноженный на 10%. Он не принял общее количество рассчитанных элементов.
- Вы не можете использовать вычисляемые поля в качестве фильтра или среза отчета.
- Количество текстовых полей нельзя использовать для формулы вычисляемого поля. Если его использовать, то результат будет нулевой.
Вы можете создать вычисляемое поле для приведенной ниже сводной таблицы.
Предположим, вы хотите умножить количество сотрудников в каждой группе на надбавку в размере 1000 долларов, чтобы рассчитать стоимость надбавки.
Для этого можно использовать приведенную ниже формулу.
=Сотрудник *1000
Вывод будет следующим.
Все рассчитанные значения равны нулю 0️⃣
Это связано с тем, что вычисляемое поле не использует количество сотрудников поля сводной таблицы.
Используются поля данных в исходных данных. Эти текстовые поля не имеют числового значения, и значение равно нулю.