Как добавить вычисляемое поле и показать долю от итога в отчёте

Отчёт по регионам показывает выручку и себестоимость, но маржи в журнале нет, а добавлять столбец в выгрузку из учётной программы неудобно. Справка Microsoft предлагает собственные формулы в вычисляемых полях и вычисляемых объектах как раз для случаев, когда функций сведения и настраиваемых вычислений недостаточно^[1]. Такой столбец решает задачу без правки журнала.

Путь такой: открыть вкладку анализа на ленте, выделить ячейку отчёта, выбрать поле с формулой и ввести её, проверить результат на строке «Общий итог», при ошибке исправить формулу или снять поле флажком и сохранить приём для своих данных. Главная ловушка — формула считает по суммам, а не по строкам журнала.

Ключевые факты: формулы в сводной таблице

• Вычисляемое поле — формула внутри отчёта, которая работает с суммами исходных данных по каждому полю^[1].
• Пример из справки =Продажи*1,2 умножает общую сумму продаж для каждого типа и региона на 1,2^[1].
• Ссылки на ячейки и определённые имена в такой формуле не допускаются, как и формулы массива^[1].
• Вычисляемый объект отличается от поля тем, что работает с отдельными записями и сам попадает в промежуточные и общие итоги^[1].
• Справка Microsoft распространяет правила формул в сводных на Excel 2016, Excel 2019, Excel 2021, Excel 2024, Microsoft 365 и его версию для Mac^[1].

Что такое вычисляемое поле и вычисляемый объект

Вычисляемое поле добавляет в отчёт столбец, посчитанный по формуле из сумм других полей. Справка Microsoft поясняет на примере: =Продажи*1,2 умножает общую сумму продаж для каждого типа и региона на 1,2^[1]. Исходный журнал для примера:

Сводная по регионам с двумя вычисляемыми полями — =Выручка-Себестоимость и =(Выручка-Себестоимость)/Выручка:

Вычисляемое поле «Маржа» здесь — разность сумм выручки и себестоимости по каждому региону. Рентабельность в строке «Общий итог» — честное отношение сумм, а не среднее трёх процентов: формула применяется к уже сложенным значениям.

Вычисляемый объект работает иначе: он добавляет элемент внутри поля строк и считает по отдельным записям — справка приводит формулу =Молоко*115%^[1]. Такой объект автоматически попадает в промежуточные и общие итоги.

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

Как добавить поле с формулой и не ошибиться

Поле с формулой создают на вкладке анализа сводной таблицы, а формулу поля собирают из имён полей, операторов и констант^[1]. Справка «Вычисление значений в сводной таблице» описывает правила формул, но не путь по ленте, поэтому подписи кнопок сверяйте со своей версией.

1. Щёлкните любую ячейку отчёта — на ленте появится вкладка анализа сводной таблицы (PivotTable Analyze в английской справке^[2]).
2. Выберите команду вычисляемого поля среди вычислений этой вкладки.
3. Задайте имя «Маржа» и формулу =Выручка-Себестоимость.
4. Подтвердите и проверьте строку «Общий итог»: 490000 совпадает с разницей итогов.

Имена полей из нескольких слов, с цифрами или символами берут в одинарные кавычки^[1], например ='Сумма, ₽'/'Кол-во'. Ссылки на ячейки и определённые имена не допускаются, функции листа со ссылками и формулы массива тоже не поддерживаются, поэтому ставку наценки пишут прямо числом. Формула не может ссылаться и на итоговые значения отчёта.

Убрать поле из отчёта можно, сняв его флажок в списке полей. После правки журнала обновите сводную клавишами Alt+F5^[2], иначе новое поле посчитает старые суммы.

Добавьте поле «Цена за шт» в сводной по менеджерам в тренажёре: урок проверит формулу с именами в кавычках.

Однако для долей и нарастающих итогов формула не нужна: встроенные команды устроены иначе — они пересчитывают уже готовые суммы.

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

Вычисляемое поле не нужно для долей: команда «Показать значения как» считает их без формулы. Её вызывают щелчком правой кнопкой по полю значений; подпись «Дополнительные вычисления» в русской справке встречается только на иллюстрации^[3].

Чтобы рядом стояли рубли и проценты, перетащите поле в область значений ещё раз — копия получит номер «_2»^[3]. В Excel для Mac части вариантов может не быть: тогда справка советует открыть «Дополнительные параметры».

Даты собирают в месяцы и кварталы без формул командой Group в контекстном меню: в окне группировки задают границы Starting at и Ending at и шаг By^[4]. Поля времени, добавленные в строки, Excel по той же справке связывает и группирует автоматически.

Именно поэтому формулу оставляют для того, чего встроенные команды не умеют, а её границы стоит знать заранее.

Когда формула в сводной не подойдёт

Вычисляемое поле не подойдёт, если расчёт нужен по каждой строке журнала, а не по суммам полей. Пример — выручка как цена, умноженная на количество:

Построчно выручка равна 700, а поле =Цена*Кол-во перемножит суммы и покажет 1800.

• Если нужно произведение по строкам, посчитайте столбец «Сумма» в исходном журнале и сложите его в отчёте.
• Если ставка лежит в ячейке листа, формула со ссылкой на неё не работает: ссылки на ячейки и определённые имена не допускаются^[1].
• При источнике OLAP формулы не поддерживаются, и поле не добавить^[1].
• Когда формула должна опереться на итог отчёта, она не сработает: ссылаться на итоговые значения нельзя.

Правила из этой справки относятся к Excel: для сводных в Google Таблицах действует своя справка Google, и переносить ограничения напрямую не стоит. Создание отчёта, области и обычные итоги описаны в основах сводной таблицы; остальные темы — среди уроков тренажёра по сводным и анализу данных.