Вычисляемое поле сводная таблица: Вычисление значений в сводной таблице

Вычисление значений в сводной таблице

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

Доступные методы вычислений

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

• Функции сведения в полях значений.    В сводной таблице в области значений отображаются сводные данные, вычисленные на основе исходных данных. Рассмотрим пример с такими исходными данными:

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

• В сводной таблице поле столбца Месяц содержит элементы Март и Апрель. Поле строки Регион содержит элементы Север, Юг, Восток и Запад. Значение на пересечении столбца Апрель и строки Север — это общая выручка от продаж, определенная по исходным данным, для которых столбец

  Месяц содержит значение Апрель, а столбец Регион — значение Север.

• В сводной диаграмме поле Регион может представлять собой поле категорий, в котором элементы Север, Юг, Восток и Запад отображаются как категории. Поле Месяц поле может быть полем рядов, в котором элементы Март, Апрель и Май отображаются как ряды, представленные в легенде. Поле значений с именем Сумма продаж может содержать маркеры данных, которые представляют общую выручку в каждом регионе за каждый месяц. Например, один маркер данных может представлять (своим положением на вертикальной оси, т. е. оси значений) сумму продаж за месяц Апрель в регионе Север.

• Ниже перечислены функции сведения, с помощью которых можно вычислять поля значений. Эти функции доступны для всех типов исходных данных, кроме OLAP.

Функция	Сведение данных
Сумма	Сумма значений. Функция по умолчанию для числовых данных.
Количество	Число значений. Действует аналогично функции СЧЁТЗ. Функция по умолчанию для данных, отличных от числовых.
Среднее	Среднее арифметическое.
Максимум	Наибольшее значение.
Минимум	Наименьшее значение.
Произведение	Произведение значений.
Количество чисел	Количество числовых значений. Действует аналогично функции СЧЁТ.
Стандартное отклонение	Оценка стандартного отклонения генеральной совокупности, где выборка является подмножеством всей генеральной совокупности.
Несмещенное отклонение	Стандартное отклонение генеральной совокупности, которая содержит все сводимые данные.
Дисперсия	Оценка дисперсии генеральной совокупности, где выборка является подмножеством всей генеральной совокупности.
Несмещенная дисперсия	Дисперсия генеральной совокупности, которая содержит все сводимые данные.

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

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

Функция	Результат
Без вычислений	Значение, введенное в данное поле.
% от общей суммы	Значения в процентах от общей суммы всех значений или точек данных в отчете.
% от суммы по столбцу	Все значения в каждом столбце или ряду в процентах от итогового значения по этому столбцу или ряду.
% от суммы по строке	Значение в каждой строке или категории в процентах от итогового значения по этой строке или категории.
Доля	Значения в процентах от значения базового элемента в соответствующем базовом поле.
% от суммы по родительской строке	Рассчитывает значения следующим образом: (значение элемента) / (значение родительского элемента по строкам).
% от суммы по родительскому столбцу	Рассчитывает значения следующим образом: (значение элемента) / (значение родительского элемента по столбцам).
% от родительской суммы	Рассчитывает значения следующим образом: (значение элемента) / (значение родительского элемента в выбранном базовом поле).
Отличие	Значения в виде разности по отношению к значению базового элемента в соответствующем базовом поле.
Приведенное отличие	Значения в виде разности в процентах по отношению к значению базового элемента в соответствующем базовом поле.
С нарастающим итогом в поле	Значение в виде нарастающего итога для последовательных элементов в базовом поле.
% от суммы с нарастающим итогом в поле	Значение в виде нарастающего итога в процентах для последовательных элементов в базовом поле.
Сортировка от минимального к максимальному	Ранг выбранных значений в определенном поле с учетом того, что наименьшему из них присваивается значение 1, а остальным — значения более высокого ранга соответственно.
Сортировка от максимального к минимальному	Ранг выбранных значений в определенном поле с учетом того, что наибольшему значению в поле присваивается значение 1, а каждому меньшему значению — более высокий ранг.
Индекс	Рассчитывает значения следующим образом: ((значение в ячейке) x (общий итог)) / ((итог строки) x (итог столбца)).

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

Влияние типа источника данных на вычисления

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

• Вычисления на основе исходных данных OLAP.    При создании сводных таблиц на основе кубов OLAP сводные значения вычисляются на сервере OLAP еще до отображения результатов в Excel. В сводной таблице невозможно изменить способ вычисления этих значений. Например, вы не сможете выбрать другую функцию сведения для вычисления полей данных или промежуточных итогов и добавить вычисляемые поля или вычисляемые объекты.

  Кроме того, если сервер OLAP предоставляет вычисляемые поля, называемые «вычисляемыми элементами», вы увидите их в списке полей сводной таблицы. Вы также увидите все вычисляемые поля и вычисляемые объекты, созданные с помощью макросов, которые написаны на языке Visual Basic для приложений (VBA) и хранятся в книге, но не сможете их изменить. Если вам нужны дополнительные типы вычислений, обратитесь к администратору базы данных OLAP.

  Если исходные данные получены из базы данных OLAP, то при вычислении промежуточных и общих итогов можно включить или исключить значения для скрытых элементов.

• Вычисления на основе исходных данных не из базы данных OLAP.    В сводных таблицах, основанных на внешних данных других типов или на данных листа Excel, для вычисления полей значений, содержащих числовые данные, используется функция «Сумма», а для вычисления полей данных, содержащих текст, — функция «Количество». Для дальнейшего анализа и обработки своих данных вы можете выбрать другие функции сведения, например «Среднее», «Максимум» или «Минимум». Кроме того, можно создавать собственные формулы, в которых используются элементы отчета или другие данные листа. Для этого нужно создать вычисляемое поле или вычисляемый объект в поле.

Использование формул в сводных таблицах

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

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

• Имена полей и элементов.    В Excel имена полей и элементов используются для идентификации этих элементов отчета в формулах. В приведенном ниже примере для данных в диапазоне C3:C9 используется имя поля Молоко. Для вычисляемого объекта в поле Тип, оценивающего объем продаж нового продукта на основе данных о продажах молочных продуктов, можно использовать формулу =Молоко * 115%.

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

• Формулы работают с итоговыми суммами, а не с отдельными записями.    Формула для вычисляемого поля оперирует суммой исходных данных для каждого используемого поля. Например, формула вычисляемого поля =Продажи * 1,2 умножает сумму продаж для каждого типа и региона на 1,2, а не умножает каждое отдельное значение продаж на 1,2 с последующим суммированием полученных величин.

  Формулы для вычисляемых объектов оперируют отдельными записями. Например, формула вычисляемого объекта =Молоко * 115% умножает каждое отдельное значение продаж молочных продуктов на 115 %, после чего полученные величины суммируются в области «Значения».

• Пробелы, цифры и символы в именах.    В имени, которое содержит два или несколько полей, их порядок не имеет значения. В примере выше ячейки C6:D6 могут называться ‘Апрель Север’ или ‘Север Апрель’. Имена, которые состоят из нескольких слов либо содержат цифры или символы, нужно заключать в одинарные кавычки.

• Итоги.    Формулы не могут ссылаться на итоговые значения (в примере выше — это Сумма за март, Сумма за апрель и Общий итог).

• Имена полей в ссылках на элементы.    Вы можете включить имя поля в ссылку на элемент. Имя элемента должно быть заключено в квадратные скобки, например: Регион[Север]. Используйте этот формат, чтобы избежать ошибок #ИМЯ?, которые возникают, если два элемента в двух разных полях отчета имеют одинаковые имена. Например, если в отчете есть два элемента с именем «Мясо» в полях «Тип» и «Категория», можно избежать появления ошибок #ИМЯ?, ссылаясь на эти элементы следующим образом: Тип[Мясо] и Категория[Мясо].

• Ссылки на элементы по позиции.    Вы можете сослаться на элемент, указав его позицию в отчете (с учетом того, какие элементы фактически отображаются и как они отсортированы в настоящий момент). Тип[1] — это Молоко, а Тип[2] — Морепродукты. Когда позиции элементов изменятся, например, если какие-то из них будут скрыты или снова отображены, такая ссылка, возможно, будет указывать на другой элемент. Скрытые элементы не учитываются в этом индексе.

  Для ссылки на элементы можно использовать относительные позиции. Они определяются относительно вычисляемого объекта, содержащего формулу. Если текущим регионом является Юг, то Регион[-1] — это Север. Если текущим регионом является Север, то Регион[+1] — это Юг. Например, для вычисляемого объекта можно использовать формулу =Регион[-1] * 3%. Например, для вычисляемого объекта можно использовать формулу =Регион[-1] * 3%. Если позиция, которую вы указали, находится перед первым или после последнего элемента в поле, формула возвращает ошибку #ССЫЛКА!.

Использование формул в сводных диаграммах

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

Например, на этой сводной диаграмме представлены данные о продажах для каждого продавца по регионам:

Чтобы посмотреть, как будут выглядеть объемы продаж, если увеличатся на 10 %, можно создать вычисляемое поле в связанной сводной таблице и воспользоваться формулой =Продажи * 110%.

Результат сразу отображается на сводной диаграмме, как показано на этом рисунке:

Чтобы отобразить отдельный маркер данных для продаж в регионе «Север» за вычетом транспортных расходов, которые составляют 8 %, можно создать в поле «Регион» вычисляемый объект с такой формулой: =Север – (Север * 8%).

Диаграмма будет выглядеть следующим образом:

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

Настройка вычислений в сводных таблицах

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

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

Другие функции расчета вместо банальной суммы

Если щелкнуть правой кнопкой мыши по расчетному полю в области данных и выбрать из контекстного меню команду Параметры поля (Field Settings) или в версии Excel 2007 – Параметры полей значений (Value Field Settings), то откроется очень полезное окно, используя которое можно задать кучу интересных настроек:

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

По умолчанию, для числовых данных Excel всегда автоматически выбирает суммирование (Sum), а для нечисловых (даже если из тысячи ячеек с числами попадется хотя бы одна пустая или с текстом или с числом в текстовом формате) – функцию подсчета количества значений (Count).

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

 …а потом задавайте разные функции для каждого из полей, щелкая по очереди по ним мышью и выбирая команду Параметры поля (Field settings), чтобы в итоге получить желаемое:

Долевые проценты

Если в этом же окне Параметры поля нажать кнопку Дополнительно (Options) или перейти на вкладку Дополнительные вычисления (в Excel 2007-2010), то станет доступен выпадающий список Дополнительные вычисления (Show data as):

В этом списке, например, можно выбрать варианты Доля от суммы по строке (% of row), Доля от суммы по столбцу (% of column) или Доля от общей суммы (% of total), чтобы автоматически подсчитать проценты для каждого товара или города. Вот так, например, будет выглядеть наша сводная таблица с включенной функцией Доля от суммы по столбцу:

Динамика продаж

Если в выпадающем списке Дополнительные вычисления (Show data as) выбрать вариант Отличие (Difference), а в нижних окнах Поле (Base field) и Элемент (Base item) выбрать Месяц и Назад (в родной англоязычной версии вместо этого странного слова было более понятное Previous, т.е. предыдущий):

…то получим сводную таблицу, в которой показаны отличия продаж каждого следующего месяца от предыдущего, т.е. – динамика продаж:

А если заменить Отличие (Difference) на Приведенное отличие (% of difference) и добавить условное форматирование для выделения отрицательных значений красным цветом — то получим то же самое, но не в рублях, а в процентах:

P.S.

В Microsoft Excel 2010 все вышеперечисленные настройки вычислений можно проделать еще проще — щелкнув правой кнопкой мыши по любому полю и выбрав в контекстном меню команды Итоги по (Summarize Values By):

… и Дополнительные вычисления (Show Data as):

Также в версии Excel 2010 к этому набору добавились несколько новых функций:

Ссылки по теме

 

Изменить вычисляемое поле в сводной таблице

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

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

В примере вычисляемым поле является поле с валовым доходом с НДС.

Предположим, нам необходимо изменить расчет в поле и перевести в валовый доход БЕЗ НДС.

Для упрощения, определим ставку НДС=18% для всех групп товаров в таблице.

Для этого:

 1. Кликнем на любом элементе сводной таблицы и в группе меню «Параметры» на ленте.

2. Кликнем на «Формулы» (или «Поля, элементы, наборы») и выберем «Вычисляемое поле».

3. В открывшемся окне, необходимо нажать на выпадающий список справа от поля «Имя». По умолчанию, имена вычисляемых полей сводной таблицы в данном модуле не отображаются.

4. Выберем поле, по которому необходимо изменить расчет и откорректируем формулу в поле «Формула».
В примере мы переведем Валовый доход в значения без НДС.

Вместо

=’Т/О в розничных ценах с НДС’ -‘Т/О в ценах закупки с НДС’

укажем

=(‘Т/О в розничных ценах с НДС’ -‘Т/О в ценах закупки с НДС’)/1,18

 

5. Переименуем поле и нажмем кнопку «Изменить». Закроем модуль.

Итогом будет пересчитанный, уже по новой формуле, столбец.

Для удаления поля, необходимо выбрать имя поля в модуле и нажать «Удалить».

Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже:
(для перевода по карте нажмите на VISA и далее «перевести»)

Создание вычисляемого поля сводной таблицы Excel 2010 — Сводные таблицы Excel 2010

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

Теперь, когда в нашем распоряжении имеется сводная таблица, наступило время для создания, вычисляемого поля. Чтобы создать вычисляемое поле, активизируйте диалоговое окно Вставка вычисляемого поля (Insert Calculated Field).

Рис. 5.6. Создайте сводную таблицу, в которую будет добавляться вычисляемое поле

Чтобы открыть его, на контекстной вкладке ленты Параметры (Options), выбранной в группе контекстных вкладок Работа со сводными таблицами (PivotTable Tools), перейдите в группу Вычисления (Calculations), щелкните на кнопке Поля, элементы и наборы (Fields, Items & Sets) и выберите в раскрывающемся меню команду Вычисляемое поле (Calculated Field), как показано на рис. 5.7.

Рис. 5.7. Создание вычисляемого поля начинается с этого раскрывающегося меню

На экране появится диалоговое окно Вставка вычисляемого поля (Insert Calculation Field), как показано на рис. 5.8.

Рис. 5.8. Диалоговое окно Вставка вычисляемого поля поможет в создании вычисляемого поля для сводной таблицы

В верхней части диалогового окна имеются два текстовых поля: Имя (Name) и Формула (Formula). В этих полях следует задать имя вычисляемого поля и создать формулу, указав необходимые поля данных и математические операторы. На рис. 5.9 показано, что вычисляемому полю присвоено описательное имя Средняя выручка за час. Это имя должно точно характеризовать тип выполняемой математической операции.

Рис. 5.9. Выбор для вычисляемого поля описательного имени

Далее перейдите к списку Поля (Fields) и дважды щелкните на поле Объем продаж. Введите символ косой черты /, чтобы указать программе Excel, что вы будете делить значение поля Объем продаж на другой элемент.

По умолчанию текстовое поле Формула диалогового окна Вставка вычисляемого поля содержит выражение = 0. Перед вводом собственной формулы следует удалить нуль.

С этого момента диалоговое окно должно выглядеть так, как показано на рис. 5.10.

Рис. 5.10. Начните ввод формулы со значения = ‘Объем продаж’/

Закончите ввод формулы, дважды щелкнув на поле Период продаж (в часах). Готовая формула показана на рис. 5.11.

Рис 5.11. Полная формула = ‘Объем продаж’/’Период продаж (в часах)’ отображает требуемое вычисляемое поле

Щелкните на кнопке Добавить (Add), а затем — на кнопке ОК для активизации нового вычисляемого поля. Как видно на рис. 5.12, в результате вы получите вычисляемое поле внутри сводной таблицы.

Рис. 5.12. Вы успешно добавили вычисляемое поле в сводную таблицу. Теперь можно изменять настройки этого нового поля точно так же, как и параметры любого другого поля (например, имя поля, числовой формат или цвет)

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

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

Означает ли это, что вы добавили столбец в источник данных? Нет. Вычисляемые поля похожи на элементы Промежуточная сумма (Subtotal) и Общая сумма (Grand Total) сводной таблицы, задаваемые по умолчанию, так как все они являются математическими функциями, которые выполняют перерасчет данных при изменении или обновлении сводной таблицы. Вычисляемые поля просто имитируют строго заданные поля в источнике данных. Можете перетаскивать их, изменять настройки полей, а также использовать вместе с другими вычисляемыми полями.

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

Как проводить вычисления в сводной таблице?

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

Файлы для скачивания:

Файл	Описание	Размер файла:	Скачивания
Пример		109 Кб	3394

Допустим, мы имеем такую сводную таблицу:

Нам необходимо посчитать долю каждого менеджера в разрезе месяцев. Для этого в правом окне «Поля сводной таблицы», наводим курсор мышки на поле «Сумма», нажимаем левую клавишу и удерживая клавишу мышки тянем поле «Сумма» в окно «Значения»:

Как включить видео?

Чтобы увидеть видео включите в браузере GIF-Анимацию.

Затем наводим курсор на это поле в окне «Значения» и нажимаем на него левой клавишей мышки, в выпавшем меню выбираем пункт «Параметры полей значений…»:

Как включить видео?

Чтобы увидеть видео включите в браузере GIF-Анимацию.

В открывшемся диалоговом окне «Параметры поля значений», меняем название поля, например, на «Доля»:

Переходим во вкладку «Дополнительные вычисления», в поле со списком (там, где написано «Без вычислений») выбираем пункт «% от суммы по столбцу» и нажимаем кнопку «ОК»:

После чего ваша Сводная таблица должна будет приобрести следующий вид:

Поэкспериментируйте с другими вариантами вычислений.

Еще один пример вычислений, имеем такую Сводную таблицу:

В одном столбце у нас «Сумма продаж», в другой «Прибыль», нам необходимо получить рентабельность каждой категории товаров. Для этого устанавливаем курсор на сводную таблицу, в главном меню в разделе «Работа со сводными таблицами», во вкладке «Анализ» (в 2007 и 2010 «Параметры»), в разделе «Вычисления», нажимаем кнопку «Поля, элементы и наборы», в выпавшем списке выбираем пункт «Вычисляемое поле…» (в 2007 «Сервис» -> «Формулы»):

В открывшемся диалоговом окне меняем название поля, например, на «Рентабельность»:

В окне «Поля:» встаем на поле «Прибыль» и нажимаем кнопку «Добавить поле» так чтобы оно появилось в поле «Формула:», затем нажимаем на клавиатуре «/» — Знак деления, встаем на поле «Сумма» и снова нажимаем кнопку «Добавить поле». В итоге всех манипуляций в поле «Формула:» у вас должно получиться «=Прибыль/Сумма»:

Как включить видео?

Чтобы увидеть видео включите в браузере GIF-Анимацию.

Если все получилось, смело, нажимаем кнопку «ОК»:

В итоге ваша Сводная таблица должна будет принять следующий вид:

Присвоим столбцу «Рентабельность» формат «%», уберем из заголовка лишние слова и получим такую красоту:

В чем, собственно говоря, прелесть таких вычислений? В том, что если вы теперь добавите поле «SKU» в «СТРОКИ», то вы получите «Рентабельность» по каждому SKU, не вбивая никаких дополнительных формул:

Как включить видео?

Чтобы увидеть видео включите в браузере GIF-Анимацию.

Добавить комментарий

3.3 «Управление сводными таблицами: группировка, вычисляемые поля» — Агрегирование, сортировка и фильтрация данных

[МУЗЫКА] [МУЗЫКА] [МУЗЫКА] Сейчас рассмотрим такие красивые возможности сводных таблиц, как группировка записей по определенным признакам и создание вычисляемых полей. Эти возможности будем рассматривать на примере таблицы, которая была у нас представлена в начале курса. Здесь тоже фамилии менеджеров, названия клиентов, и самое главное — у нас здесь есть столбец с датами. Классической, чрезвычайно актуальной задачей является по таким таблицам получение, например, итоговых значений, например, итоговых сумм сделок по месяцам — такой самый распространенный вариант. Как можно сделать это с помощью сводных таблиц? Вот этой возможностью очень редко кто пользуется, она абсолютно красива, удобна и проста. Итак, запускаем сводную таблицу. Точно так же можно дать ей умное имя, как мы только что это делали, но для нашего примера это абсолютно не важно, и запускаем инструмент «Сводные таблицы». Вставка, сводная таблица, и у нас нет месяцев, у нас есть даты. Давайте нажмем ОК. И в получившемся макете сводной таблицы дату ставим в строчки, а сумму ставим в то поле, где у нас есть возможность управлять агрегирующей функцией. Я укрупню размер. Это, конечно, не отчет по месяцам, и кстати говоря, достаточно интересно обсудить интерпретацию полученного отчета. Интерпретация этого отчета следующая: это итоговая сумма сделок, совершенных в компании разными менеджерами с разными клиентами. То есть вся сумма сделок, которая была обозначена 10-м января 2017 года. Нам-то нужны месяцы, задача была — получить итоговые суммы для каждого месяца. Правая кнопка на любой дате. В контекстном меню появляется команда «Группировать». Выбираем «Группировать», и здесь у нас есть возможность обозначить шаг — месяц. Нажимаем ОК. Совершенно удивительная, красивая возможность сводных таблиц — группировать даты с подключением календаря и получить итоговые значения по январю, февралю и марту. Красивая возможность, очень советую ею пользоваться. Дальше. А какие еще группировки возможны в сводных таблицах? Во-первых, прежде всего очень полезны группировки текстовых значений. Если сделать, предположим, сводную таблицу по каждому клиенту и получить итоговые суммы сделок, — сейчас увеличим размер — то мы получим вот такой аналитический отчет. Предположим, у нас первый и второй клиент объединились, и у них создалась какая-то единая компания. Совершенно несложные действия, выделяем оба названия, правая кнопка мыши — по-прежнему «Группировать». Мы видим, что у нас появилась условное имя «Группа 1». Это имя можно изменить, например, назовем «Единая компания». Вот сейчас у нас получился вот такой результат. Детализация по вошедшим в эту компанию теперь уже филиалам, или единая строчка, которая дает нам итоговый результат по компании, которая сложилась из слияния двух других компаний. Это пример группировки текстовых значений. Группировка числовых значений будет рассмотрена во втором курсе. Она немного более сложна, с точки зрения постановки задачи, и будет объяснена и рассказана среди более продвинутых инструментов аналитика. Типичной проблемой при создании аналитических отчетов является создание или получение каких-то вычисляемых значений, или, с точки зрения терминологии сводных таблиц, это создание вычисляемых полей. Вернемся к таблице, в которой у нас есть информация не только по выручке, но и информация по плану, который был поставлен каждому менеджеру в каждом месяце в каждом районе, и создадим, например, вот такую таблицу. Ставка, сводная таблица. Мы для каждого товара попросим посчитать итоговое значение по выручке и итоговое значение по плану. Увеличим размер таблицы, и естественно, хочется понять, кто выполнил план, на сколько выполнил план процентов, например, или на сколько перевыполнили или недовыполнил план. То есть мне нужно сравнить значения двух столбцов. Как это делается? На этот раз контекстное меню нам не поможет. На закладке «Анализ» — эта закладка активизируется тогда, когда у нас курсор стоит в любой клетке сводной таблицы, — мы можем использовать команду «Поля, элементы и наборы». И здесь есть команда «Вычисляемое поле». Давайте сейчас посчитаем, на сколько процентов выполнен план по каждому товару. Вычисляемое поле. И на сколько процентов — соответственно, мне нужно выручку… Двойным щелчком я выбираю выручку. Разделить на план. Нажимаю ОК. Цифры, трудные для восприятия, но когда мы разделили выручку на план, вообще-то, должны были появиться процентные значения, давайте выделяем этот столбец и назначаем этим значениям процентный формат. Почему у нас получилось такое большое значение? Потому что в бакалее мы приписывали два нуля, для того чтобы обсудить проблемы обновления. Давайте сейчас заодно, кстати говоря, еще раз вспомним, как обновляются данные. Уберем эти два нолика, хотя у нас уже и так правильный аналитический отчет получился, но более корректные результаты будут вот такие. Мы видим, на сколько процентов выполнен план по продажам каждого вида товаров. Это первый пример с вычисляемым полем. И давайте еще один пример приведем: насколько перевыполнен или недовыполнен план по каждому товару в абсолютных значениях. Кстати говоря, заодно можно проиллюстрировать, как убирается поле, которое вам на данный момент не нужно или просто ошибочно сделали. Я могу просто убрать это поле или убрать просто галку около названия «Поле 1». Итак, Анализ; Поля, элементы и наборы; Вычисляемое поле. И формируем формулу: выручка минус план. У нас получилось новое вычисляемое поле, которое нам совершенно наглядно показывает, что по бакалее сейчас план точно выполнен, по продаже груш перевыполнен, а например, по напиткам он недовыполнен. Инструменты «Группировка» и «Вычисляемые поля» очень удобны и комфортны в работе и решают колоссальное количество проблем, которые встречаются на практике. На них обязательно надо обратить внимание и узнавать их в тех задачах, которые стоят перед пользователем. И еще одна очень несложная возможность сводных таблиц, которая полезна для тех пользователей, которые вынуждены информацию, предположим, по разным районам для этой таблицы распечатывать отдельно или представлять в разные отделы по отдельности. Я упрощу вид сводной таблицы, у меня останутся только названия товаров и итоговая выручка, и помещу район в фильтры отчетов. Далеко достаточно спрятана следующая возможность: на закладке «Анализ» в меню «Сводная таблица» есть команда «Параметры» и команда «Отобразить страницы фильтра отчета». В предыдущих версиях Excel, а мы рассматривали 2013 версию, эта команда была ближе для пользователя. Отобразить страницы фильтра отчета. Нас спрашивают, хочу ли я показать все страницы фильтра отчета из района. Я соглашаюсь, потому что это и было объявлено как цель следующего показа. У нас получилось разнести информацию по продажам разных товаров по отдельным листам с названиями районов. Это абсолютно полезная возможность, которая очень часто встречается на практике. И не используя эту возможность, вы будете вынуждены вручную копировать данные по районам на разные листы.

Работа со сводными таблицами в Excel на примерах

Пользователи создают сводные таблицы для анализа, суммирования и представления большого объема данных. Такой инструмент Excel позволяет произвести фильтрацию и группировку информации, изобразить ее в различных разрезах (подготовить отчет).

Исходный материал – таблица с несколькими десятками и сотнями строк, несколько таблиц в одной книге, несколько файлов. Напомним порядок создания: «Вставка» – «Таблицы» – «Сводная таблица».

А в данной статье мы рассмотрим, как работать со сводными таблицами в Excel.

Как сделать сводную таблицу из нескольких файлов

Первый этап – выгрузить информацию в программу Excel и привести ее в соответствие с таблицами Excel. Если наши данные находятся в Worde, мы переносим их в Excel и делаем таблицу по всем правилам Excel (даем заголовки столбцам, убираем пустые строки и т.п.).

Дальнейшая работа по созданию сводной таблицы из нескольких файлов будет зависеть от типа данных. Если информация однотипная (табличек несколько, но заголовки одинаковые), то Мастер сводных таблиц – в помощь.

Мы просто создаем сводный отчет на основе данных в нескольких диапазонах консолидации.

Гораздо сложнее сделать сводную таблицу на основе разных по структуре исходных таблиц. Например, таких:

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

Мастер сводных таблиц при таких исходных параметрах выдаст ошибку. Так как нарушено одно из главных условий консолидации – одинаковые названия столбцов.

Но два заголовка в этих таблицах идентичны. Поэтому мы можем объединить данные, а потом создать сводный отчет.

1. В ячейке-мишени (там, куда будет переноситься таблица) ставим курсор. Пишем = — переходим на лист с переносимыми данными – выделяем первую ячейку столбца, который копируем. Ввод. «Размножаем» формулу, протягивая вниз за правый нижний угол ячейки.
2. По такому же принципу переносим другие данные. В результате из двух таблиц получаем одну общую.
3. Теперь создадим сводный отчет. Вставка – сводная таблица – указываем диапазон и место – ОК.

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

Покажем, к примеру, количество проданного товара.

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



Детализация информации в сводных таблицах

Из отчета (см.выше) мы видим, что продано ВСЕГО 30 видеокарт. Чтобы узнать, какие данные были использованы для получения этого значения, щелкаем два раза мышкой по цифре «30». Получаем детальный отчет:

Как обновить данные в сводной таблице Excel?

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

Обновление данных:

Курсор должен стоять в любой ячейке сводного отчета.

Либо:

Правая кнопка мыши – обновить.

Чтобы настроить автоматическое обновление сводной таблицы при изменении данных, делаем по инструкции:

1. Курсор стоит в любом месте отчета. Работа со сводными таблицами – Параметры – Сводная таблица.
2. Параметры.
3. В открывшемся диалоге – Данные – Обновить при открытии файла – ОК.

Изменение структуры отчета

Добавим в сводную таблицу новые поля:

1. На листе с исходными данными вставляем столбец «Продажи». Здесь мы отразим, какую выручку получит магазин от реализации товара. Воспользуемся формулой – цена за 1 * количество проданных единиц.
2. Переходим на лист с отчетом. Работа со сводными таблицами – параметры – изменить источник данных. Расширяем диапазон информации, которая должна войти в сводную таблицу.

Если бы мы добавили столбцы внутри исходной таблицы, достаточно было обновить сводную таблицу.

После изменения диапазона в сводке появилось поле «Продажи».

Как добавить в сводную таблицу вычисляемое поле?

Иногда пользователю недостаточно данных, содержащихся в сводной таблице. Менять исходную информацию не имеет смысла. В таких ситуациях лучше добавить вычисляемое (пользовательское) поле.

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

Инструкция по добавлению пользовательского поля:

1. Определяемся, какие функции будет выполнять виртуальный столбец. На какие данные сводной таблицы вычисляемое поле должно ссылаться. Допустим, нам нужны остатки по группам товаров.
2. Работа со сводными таблицами – Параметры – Формулы – Вычисляемое поле.
3. В открывшемся меню вводим название поля. Ставим курсор в строку «Формула». Инструмент «Вычисляемое поле» не реагирует на диапазоны. Поэтому выделять ячейки в сводной таблице не имеет смысла. Из предполагаемого списка выбираем категории, которые нужны в расчете. Выбрали – «Добавить поле». Дописываем формулу нужными арифметическими действиями.
4. Жмем ОК. Появились Остатки.

Группировка данных в сводном отчете

Для примера посчитаем расходы на товар в разные годы. Сколько было затрачено средств в 2012, 2013, 2014 и 2015. Группировка по дате в сводной таблице Excel выполняется следующим образом. Для примера сделаем простую сводную по дате поставки и сумме.

Щелкаем правой кнопкой мыши по любой дате. Выбираем команду «Группировать».

В открывшемся диалоге задаем параметры группировки. Начальная и конечная дата диапазона выводятся автоматически. Выбираем шаг – «Годы».

Получаем суммы заказов по годам.

Скачать пример работы

По такой же схеме можно группировать данные в сводной таблице по другим параметрам.

Вычисляемое поле сводной таблицы Excel или вычисляемый элемент

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

Введение

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

Ключевое отличие от между ними заключается в том, что:

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

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

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

О формулах сводной таблицы

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

Однако есть несколько общих ограничений на использование формул:

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

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

О вычисляемых элементах

Вот ключевые особенности вычисляемых элементов сводной таблицы:

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

Нажмите здесь, чтобы узнать, как настроить Calculated Элементы сводных таблиц

Расчетный элемент Предупреждение

Если вы создаете вычисляемый элемент в поле, следующие ограничения будет помещено в это сводное поле:

• Вы НЕ сможете переместить поле в область фильтров отчета
• Вы НЕ сможете добавить несколько копий поля в Значения. область.

О вычисляемых полях

Вот ключевые особенности вычисляемых полей сводной таблицы

• Использование вычисляемых полей для выполнения вычислений в других полях в сводной таблице.
• Для вычисляемых полей отдельные суммы в других полях суммируются, а затем производится расчет общей суммы.
• Сумма — единственная функция, доступная для вычисляемого поля.
• Вычисляемое поле становится новым полем в сводной таблице, а его при расчете можно использовать сумму других полей.
• Вычисляемые поля отображаются вместе с другими полями значений в сводной таблице. Таблица. Как и другим полям значений, имени вычисляемого поля может предшествовать по Сумме.
• Вычисляемые поля появятся в списке полей сводной таблицы.

Нажмите здесь, чтобы узнать, как настроить Calculated Поля в сводных таблицах

Пример вычисляемого элемента

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

В приведенном ниже примере поле «Статус заказа» содержит четыре элемента: Задержанный, отмененный, ожидающий и отправленный.

В поле «Статус заказа» вы можете создать вычисляемый элемент с именем «Продано», который суммирует заказы со статусом «Отгружено», «Ожидание» или «Задержанный», но не включает отмененные заказы.

На приведенном ниже снимке экрана показан рассчитанный элемент «Продано», а элементы «Задержанный», «Ожидающий» и «Отправленный» скрыты.

Пример вычисляемого поля

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

Формула вычисляемого поля проверяет значение в поле «Единицы» и рассчитывает сумму бонуса на основе 3% от поля «Итого».

= ЕСЛИ (Единицы> 100, Итого * 3%, 0)

Вот результат, где бонус отображается в соответствующих строках.

ПРИМЕЧАНИЕ : Вычисляемое поле не может проверять текстовое значение метки, поэтому используйте фильтрацию или вычисляемые элементы, чтобы отображать только применимые элементы.

Список всех формул сводной таблицы

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

ПРИМЕЧАНИЕ : В списке показаны все формулы в кэше сводной таблицы выбранной сводной таблицы, даже если эти формулы в настоящее время не отображаются в сводной таблице.

Список формул

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

1. Выберите любую ячейку в сводной таблице.
2. На ленте на вкладке Работа со сводными таблицами щелкните вкладку Анализ.
3. В группе «Расчеты» щелкните «Поля, элементы и наборы»
4. Щелкните «Список формул».

Список формул

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

Вычисляемые поля (если есть) перечислены первыми, а затем вычисляемые элементы (если есть). Также отображается порядок решения с примечанием о том, как он работает и как его изменить.

Загрузить образец файла

Щелкните ссылку, чтобы загрузить образец файла, который использовался в вычисляемом поле и вычисляемом Пример товара. Заархивированный файл имеет формат xlsx и не содержит макросы.

Дополнительные руководства

Вычисляемое поле — счетчик

Расчетные позиции

Промежуточные итоги

Сводные функции

Очистить старые элементы сводной таблицы

Расчет значений в сводной таблице

Сводные таблицы

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

Доступные методы расчета

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

• Создает следующие сводные таблицы и сводные диаграммы.Если вы создаете сводную диаграмму на основе данных в сводной таблице, значения в этой сводной диаграмме отражают вычисления в связанном отчете сводной таблицы.

• В сводной таблице поле столбца Месяц содержит элементы марта и апреля .Поле строки Region содержит элементы North , South , East и West . Значение на пересечении столбца Апрель и строки Север представляет собой общий доход от продаж записей в исходных данных, которые имеют значения Месяц апреля и Регион Север .

• В сводной диаграмме поле Region может быть полем категории, которое показывает North , South , East и West как категории.Поле Месяц может быть полем ряда, которое показывает элементы марта , апреля и мая в виде серий, представленных в легенде. Поле значений с именем Сумма продаж может содержать маркеры данных, которые представляют общий доход в каждом регионе за каждый месяц. Например, один маркер данных будет представлять своим положением на вертикальной оси (значения) общие продажи за апреля года в регионе Север, год.

• Для вычисления полей значений доступны следующие сводные функции для всех типов исходных данных, кроме исходных данных оперативной аналитической обработки (OLAP).

Функция	Суммирует
Сумма	Сумма значений.Это функция по умолчанию для числовых данных.
Количество	Количество значений данных. Функция итога счетчика работает так же, как функция COUNTA. Счетчик — это функция по умолчанию для данных, отличных от чисел.
Среднее значение	Среднее значение.
Макс	Наибольшее значение.
мин.	Наименьшее значение.
Товар	Произведение ценностей.
Счетчик	Количество значений данных, которые являются числами. Итоговая функция Count Nums работает так же, как функция COUNT.
StDev	Оценка стандартного отклонения генеральной совокупности, где выборка является подмножеством всей генеральной совокупности.
StDevp	Стандартное отклонение генеральной совокупности, где совокупность — это все данные, которые необходимо суммировать.
Вар	Оценка дисперсии генеральной совокупности, где выборка является подмножеством всей генеральной совокупности.
Варп	Дисперсия генеральной совокупности, где совокупность — это все данные, которые необходимо суммировать.

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

  Для пользовательских вычислений в полях значений доступны следующие функции.

Функция	Результат
Без расчета	Отображает значение, введенное в поле.
% от общей суммы	Отображает значения как процент от общей суммы всех значений или точек данных в отчете.
% от общей суммы столбца	Отображает все значения в каждом столбце или серии в процентах от общего количества для столбца или серии.
% от суммы строки	Отображает значение в каждой строке или категории в процентах от итоговой суммы для строки или категории.
% из	Отображает значения в процентах от значения Базового элемента в поле Базовый .
% от суммы в родительском ряду	Рассчитывает значения следующим образом: (значение для элемента) / (значение для родительского элемента в строках)
% от суммы по родительскому столбцу	Рассчитывает значения следующим образом: (значение для элемента) / (значение для родительского элемента в столбцах)
% от общей суммы материнской	Рассчитывает значения следующим образом: (значение для элемента) / (значение для родительского элемента для выбранного Базовое поле )
Отличие от	Отображает значения как разницу со значением Базового элемента в поле Базовый .
% разница от	Отображает значения в виде процентной разницы от значения Базового элемента в поле Базовый .
Промежуточный итог в	Отображает значение для последовательных элементов в поле Base как промежуточный итог.
% Промежуточная сумма в	Вычисляет значение для последовательных элементов в поле Base , которые отображаются как промежуточная сумма в процентах.
Ранг от наименьшего к наибольшему	Отображает ранг выбранных значений в определенном поле, перечисляя наименьший элемент в поле как 1, и каждое большее значение будет иметь более высокое значение ранга.
По рангу от наибольшего до наименьшего	Отображает ранг выбранных значений в определенном поле, перечисляя самый большой элемент в поле как 1, и каждое меньшее значение будет иметь более высокое значение ранга.
Индекс	Рассчитывает значения следующим образом: ((значение в ячейке) x (Общая сумма общих итогов)) / ((Общая сумма по строке) x (Общая сумма по столбцу))

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

Как тип исходных данных влияет на вычисления

Вычисления и параметры, доступные в отчете, зависят от того, получены ли исходные данные из базы данных OLAP или из источника данных, отличного от OLAP.

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

  Кроме того, если сервер OLAP предоставляет вычисляемые поля, известные как вычисляемые элементы, вы увидите эти поля в списке полей сводной таблицы. Вы также увидите все вычисляемые поля и вычисляемые элементы, созданные макросами, написанными на Visual Basic для приложений (VBA) и сохраненными в вашей книге, но вы не сможете изменить эти поля или элементы. Если вам нужны дополнительные типы вычислений, обратитесь к администратору базы данных OLAP.

  Для исходных данных OLAP вы можете включать или исключать значения для скрытых элементов при вычислении промежуточных и общих итогов.

• Вычисления на основе исходных данных, отличных от OLAP В сводных таблицах, основанных на других типах внешних данных или на данных листа, Excel использует функцию суммирования суммы для вычисления полей значений, содержащих числовые данные, и функцию сводки подсчета для расчета полей данных которые содержат текст. Вы можете выбрать другую функцию сводки, такую ​​как Среднее, Максимальное или Минимальное, для дальнейшего анализа и настройки ваших данных.Вы также можете создавать свои собственные формулы, которые используют элементы отчета или другие данные рабочего листа, создав вычисляемое поле или вычисляемый элемент внутри поля.

Использование формул в сводных таблицах

Формулы можно создавать только в отчетах, основанных на исходных данных, отличных от OLAP. Вы не можете использовать формулы в отчетах, основанных на базе данных OLAP. При использовании формул в сводных таблицах следует знать о следующих правилах синтаксиса формул и их поведении:

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

• Имена полей и элементов Excel использует имена полей и элементов для идентификации этих элементов отчета в ваших формулах. В следующем примере данные в диапазоне C3: C9 используют имя поля Dairy .Вычисляемый элемент в поле Тип , который оценивает продажи нового продукта на основе продаж молочных продуктов, может использовать такую ​​формулу, как = Молочные продукты * 115% .

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

• Формулы работают с итогами, а не с отдельными записями. Формулы для вычисляемых полей работают с суммой базовых данных для любых полей в формуле. Например, формула вычисляемого поля = Продажи * 1,2 умножает сумму продаж для каждого типа и региона на 1,2; он не умножает каждую отдельную продажу на 1,2, а затем суммирует умноженные суммы.

  Формулы для вычисляемых элементов работают с отдельными записями. Например, формула вычисленного товара = Молочные продукты * 115% умножает каждую отдельную продажу Молочных продуктов на 115%, после чего умноженные суммы суммируются в области «Значения».

• Пробелы, числа и символы в именах В имени, которое включает более одного поля, поля могут располагаться в любом порядке.В приведенном выше примере ячейки C6: D6 могут быть «Северный апрель» или «Северный апрель» . Используйте одинарные кавычки вокруг имен, состоящих из более чем одного слова или содержащих числа или символы.

• Итоги Формулы не могут ссылаться на итоги (например, Итого за март , Итого за апрель и Итого за в данном примере).

• Имена полей в ссылках на элемент Вы можете включить имя поля в ссылку на элемент.Название элемента должно быть заключено в квадратные скобки — например, Регион [Север] . Используйте этот формат, чтобы избежать #NAME? ошибки, когда два элемента в двух разных полях отчета имеют одинаковое имя. Например, если в отчете есть элемент с именем «Мясо» в поле «Тип» и другой элемент с именем «Мясо» в поле «Категория», вы можете запретить #NAME? ошибки, указав элементы как Тип [Мясо] и Категория [Мясо] .

• Обращение к элементам по позиции Вы можете ссылаться на элемент по его положению в отчете, как отсортированный и отображаемый в данный момент. Тип [1] — Молочные продукты и Тип [2] — Морепродукты . Элемент, упомянутый таким образом, может изменяться всякий раз, когда меняются положения элементов или различные элементы отображаются или скрываются. Скрытые предметы не учитываются в этом индексе.

  Для ссылки на элементы можно использовать относительные позиции. Позиции определяются относительно вычисляемого элемента, содержащего формулу. Если Юг — текущий регион, Регион [-1] — Север ; если Северный — текущий регион, Регион [+1] — Южный .Например, вычисляемый элемент может использовать формулу = Регион [-1] * 3% . Если указанная вами позиция находится перед первым элементом или после последнего элемента в поле, формула дает # ССЫЛКА! ошибка.

Использование формул в сводных диаграммах

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

Например, следующая сводная диаграмма показывает продажи для каждого продавца по региону:

Чтобы увидеть, как будут выглядеть продажи, если они увеличатся на 10 процентов, вы можете создать вычисляемое поле в связанной сводной таблице, в котором используется такая формула, как = Продажи * 110% .

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

Чтобы увидеть отдельный маркер данных для продаж в Северном регионе за вычетом транспортных расходов в размере 8 процентов, вы можете создать вычисляемый элемент в поле «Регион» с такой формулой, как = Север — (Север * 8%) .

Итоговая диаграмма будет выглядеть так:

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

Вычисляемое поле / элемент в Excel — Easy Excel Tutorial

Расчетное поле | Вычисляемый элемент

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

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

Расчетное поле

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

1. Щелкните любую ячейку в сводной таблице.

2. На вкладке «Анализ» в группе «Вычисления» щелкните «Поля, элементы и наборы».

3.Щелкните Расчетное поле.

Откроется диалоговое окно «Вставить вычисляемое поле».

4. Введите налог для имени.

5. Введите формулу = ЕСЛИ (Сумма> 100000, 3% * Сумма, 0)

6. Щелкните Добавить.

Примечание. Используйте кнопку «Вставить поле», чтобы быстро вставлять поля при вводе формулы. Чтобы удалить вычисляемое поле, выберите поле и нажмите «Удалить» (в разделе «Добавить»).

7. Щелкните OK.

Excel автоматически добавляет поле «Налог» в область «Значения».

Результат:

Расчетный элемент

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

1. Щелкните любую страну в сводной таблице.

2. На вкладке «Анализ» в группе «Вычисления» щелкните «Поля, элементы и наборы».

3. Щелкните Рассчитываемый элемент.

Откроется диалоговое окно «Вставить вычисляемый элемент».

4. Введите «Океания» в поле «Имя».

5. Введите формулу = 3% * (Австралия + Новая Зеландия)

6. Щелкните Добавить.

Примечание. Используйте кнопку «Вставить элемент», чтобы быстро вставлять элементы при вводе формулы.Чтобы удалить вычисленный элемент, выберите его и нажмите «Удалить» (в разделе «Добавить»).

7. Повторите шаги с 4 по 6 для Северной Америки (Канада и США) и Европы (Франция, Германия и Великобритания) со ставкой налога 4% и 5% соответственно.

8. Щелкните OK.

Результат:

Примечание: мы создали две группы (Продажи и Налоги).

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

Часто после создания сводной таблицы возникает необходимость расширить анализ и включить в него дополнительные данные / вычисления.

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

Загрузите набор данных и следуйте инструкциям.

Что такое вычисляемое поле сводной таблицы?

Начнем с базового примера сводной таблицы.

Предположим, у вас есть набор данных розничных продавцов и вы создали сводную таблицу, как показано ниже:

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

Теперь, что, если вы также хотите знать, какова была норма прибыли этих розничных продавцов (где норма прибыли равна «Прибыль», деленная на «Продажи»).

Есть несколько способов сделать это:

1. Вернитесь к исходному набору данных и добавьте эту новую точку данных. Таким образом, вы можете вставить новый столбец в исходные данные и рассчитать по нему размер прибыли. Как только вы это сделаете, вам необходимо обновить исходные данные сводной таблицы, чтобы этот новый столбец стал ее частью.
   • Хотя этот метод возможен, вам придется вручную вернуться к набору данных и произвести вычисления.Например, вам может потребоваться добавить еще один столбец для расчета средней продажи на единицу (Продажи / Количество). Вам снова придется добавить этот столбец к исходным данным, а затем обновить сводную таблицу.
   • Этот метод также увеличивает вашу сводную таблицу при добавлении в нее новых данных.
2. Добавьте вычисления вне сводной таблицы. Это может быть вариантом, если структура вашей сводной таблицы вряд ли изменится. Но если вы измените сводную таблицу, расчет может не обновиться соответствующим образом и может дать неверные результаты или ошибки.Как показано ниже, я рассчитал маржу прибыли, когда в ряду были розничные торговцы. Но когда я сменил с клиентов на регионы, формула выдала ошибку.
3. Использование вычисляемого поля сводной таблицы. Это наиболее эффективный способ использовать существующие данные сводной таблицы и вычислить желаемую метрику. Рассматривайте вычисляемое поле как виртуальный столбец, который вы добавили, используя существующие столбцы из сводной таблицы. Использование вычисляемого поля сводной таблицы дает множество преимуществ (как мы увидим через минуту):
   • Вам не нужно обрабатывать формулы или обновлять исходные данные.
   • Он масштабируемый, так как он автоматически учитывает любые новые данные, которые вы можете добавить в свою сводную таблицу. После добавления поля вычисления вы можете использовать его как любое другое поле в сводной таблице.
   • Легко обновлять и управлять. Например, если показатели меняются или вам нужно изменить расчет, вы можете легко сделать это из самой сводной таблицы.

Добавление вычисляемого поля в сводную таблицу

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

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

Вот шаги, чтобы добавить вычисляемое поле сводной таблицы:

• Выберите любую ячейку в сводной таблице.
• Перейдите в Инструменты сводной таблицы -> Анализ -> Вычисления -> Поля, элементы и наборы.
• В раскрывающемся списке выберите «Вычисляемое поле».
• В диалоговом окне «Вставить вычисляемый файл»:
  • Дайте ему имя, введя его в поле «Имя».
  • В поле «Формула» создайте формулу для вычисляемого поля. Обратите внимание, что вы можете выбрать одно из названий полей, перечисленных под ним. В данном случае формула: «= Прибыль / Продажи». Вы можете ввести имена полей вручную или дважды щелкнуть имя поля, указанное в поле «Поля».
• Нажмите «Добавить» и закройте диалоговое окно.

Как только вы добавите вычисляемое поле, оно появится как одно из полей в списке полей сводной таблицы.

Теперь вы можете использовать это вычисляемое поле как любое другое поле сводной таблицы (обратите внимание, что вы не можете использовать вычисляемое поле сводной таблицы в качестве фильтра или среза отчета).

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

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

В приведенном выше примере я использовал простую формулу (= Прибыль / Продажи) для вставки вычисляемого поля. Однако вы также можете использовать некоторые сложные формулы.

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

• НЕЛЬЗЯ использовать ссылки или именованные диапазоны при создании вычисляемого поля сводной таблицы. Это исключило бы множество формул, таких как ВПР, ИНДЕКС, СМЕЩЕНИЕ и т. Д. Однако вы можете использовать формулы, которые могут работать без ссылок (например, SUM, IF, COUNT и т. Д.).
• В формуле можно использовать константу. Например, если вы хотите узнать прогнозируемые продажи, при которых прогнозируется рост на 10%, вы можете использовать формулу = Продажи * 1.1 (где 1,1 — константа).
• Порядок приоритета соблюдается в формуле, составляющей вычисляемое поле. Рекомендуется использовать круглые скобки, чтобы не запоминать порядок приоритета.

Теперь давайте посмотрим на пример использования расширенной формулы для создания вычисляемого поля.

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

Для прогнозируемой стоимости необходимо использовать увеличение продаж на 5% для крупных розничных продавцов (продажи выше 3 миллионов) и увеличение продаж на 10% для малых и средних предприятий розничной торговли (продажи ниже 3 миллионов).

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

Вот как это сделать:

• Выберите любую ячейку в сводной таблице.
• Перейдите в Инструменты сводной таблицы -> Анализ -> Вычисления -> Поля, элементы и наборы.
• В раскрывающемся списке выберите «Вычисляемое поле».
• В диалоговом окне «Вставить вычисляемый файл»:
  • Дайте ему имя, введя его в поле «Имя».
  • В поле «Формула» используйте следующую формулу: = ЕСЛИ (Регион = «Юг», Продажи * 1,05, Продажи * 1,1)
• Нажмите «Добавить» и закройте диалоговое окно.

Это добавляет новый столбец в сводную таблицу со значением прогноза продаж.

Щелкните здесь, чтобы загрузить набор данных.

Проблема с вычисляемыми полями сводной таблицы

Вычисляемое поле

— это потрясающая функция, которая действительно увеличивает ценность вашей сводной таблицы с помощью вычислений полей, сохраняя при этом все масштабируемость и управляемость.

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

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

Обратите внимание, что промежуточные и общие итоги неверны.

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

Итак, для South Total, в то время как значение должно быть 22 824 000, South Total ошибочно сообщает его как 22 287 000.Это происходит, поскольку для получения значения используется формула 21,225,800 * 1,05.

К сожалению, исправить это невозможно.

Лучший способ справиться с этим — удалить промежуточные и общие итоги из сводной таблицы.

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

Как изменить или удалить вычисляемое поле сводной таблицы?

После создания вычисляемого поля сводной таблицы вы можете изменить формулу или удалить ее, выполнив следующие действия:

• Выберите любую ячейку в сводной таблице.
• Перейдите в Инструменты сводной таблицы -> Анализ -> Вычисления -> Поля, элементы и наборы.
• В раскрывающемся списке выберите Расчетное поле.
• В поле «Имя» щелкните стрелку раскрывающегося списка (маленькая стрелка вниз в конце поля).
• Из списка выберите вычисляемое поле, которое вы хотите удалить или изменить.
• Измените формулу, если вы хотите ее изменить, или нажмите «Удалить», если вы хотите ее удалить.

Как получить список всех формул вычисляемого поля?

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

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

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

• Выберите любую ячейку в сводной таблице.
• Перейдите в Инструменты сводной таблицы -> Анализ -> Поля, элементы и наборы -> Формулы списка.

Как только вы нажмете на «Формулы списка», Excel автоматически вставит новый рабочий лист, содержащий сведения обо всех вычисленных полях / элементах, которые вы использовали в сводной таблице.

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

Вы также можете найти следующие полезные руководства по сводным таблицам:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1. Щелкните меню Data на ленте меню.
2. Выберите опцию Pivot Table в раскрывающемся меню.
3. Теперь вы должны увидеть окно с вопросом, хотите ли вы вставить сводную таблицу на существующий лист или на новый лист.Выберите тот вариант, который вам больше нравится. Для наглядности всегда лучше создавать его на новом листе.
4. Нажмите кнопку Создать .
5. Это должно создать вашу сводную таблицу либо на том же листе, либо на новом листе, в зависимости от того, что вы выбрали на шаге 3.
6. Ваша сводная таблица на этом этапе должна выглядеть как на снимке экрана, показанном ниже:
7. Должна быть сетка с отображением «Строки» , «Столбцы», и «Значения» .
8. Теперь вы можете начать заполнение сводной таблицы необходимыми данными. В правой части окна вы должны увидеть редактор сводной таблицы . Это поможет вам указать, что должно быть в вашей сводной таблице.
9. Теперь мы хотим, чтобы в нашей сводной таблице было два столбца (изначально) — элемент и общая продажная цена. Итак, в категории «Строки» нажмите «Добавить» .
10. В появившемся раскрывающемся списке выберите Элемент .Это добавит каждое уникальное имя элемента в отдельные строки вашей сводной таблицы.
11. Затем мы хотим увидеть общую сумму продаж по каждому товару. Итак, в категории «Значения» нажмите «Добавить» .
12. В появившемся раскрывающемся списке выберите «SalesPrice». Это отобразит сумму всех продажных цен для каждого товара.

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

А что, если вы также хотите посмотреть, что произойдет, если вы добавите 5% НДС к общей сумме продаж каждого продукта?

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

Это означает, что вам нужно будет определить собственный расчет самостоятельно. Это легко сделать, добавив расчетное поле d.

Добавление вычисляемого поля, суммированного суммой

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Другие таблицы Google, которые могут вам понравиться:

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

Сводные таблицы — один из самых полезных инструментов в Excel.Фактически, это первое, что вы найдете на вкладке «Вставка» в Excel.

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

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

Скачать файл расчетной практики в сводных таблицах

Воспользуйтесь этим бесплатным файлом Excel с вычисляемыми полями, чтобы попрактиковаться вместе с учебником.

Что такое вычисляемое поле?

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

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

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

Простое вычисляемое поле

Так как же создать вычисляемое поле в сводных таблицах? Что ж, давайте возьмем эту сводную таблицу с различными хлебобулочными изделиями, их ценами за единицу и их общими суммами.

На этом этапе выделите сводную таблицу, которая должна создать новую небольшую вкладку вверху с надписью «сводная таблица» с двумя вкладками под ней. Вам нужно нажать Analyze , а затем Field, Items & Sets .Затем, наконец, выберите Расчетное поле .

Как вы увидите, откроется новое окно для создания вычисляемого поля.

В этом примере мы рассчитаем процент налога на цену каждого товара, который составит 14%.

Для этого заполните поле Имя с помощью Food Tax . Затем нам нужно создать фактическое вычисление, которое войдет в область Formula .

Для этого мы будем использовать столбец Сумма общей цены , который вы увидите как «Общая цена» в разделе полей ниже.Просто дважды щелкните по нему, чтобы поместить его в качестве столбца, который мы хотим добавить, а затем добавьте к нему «* 0,14», что означает умножение на 0,14 или 14%.

Когда вы закончите, нажмите Добавить , чтобы добавить его в список полей, а также в саму сводную таблицу после того, как вы нажмете Ok .

И здесь вы видите, какой налог будет для каждой позиции в соответствующей строке.

Комплексное вычисляемое поле

Сложные вычисляемые поля точно такие же, как простые вычисляемые поля, с основным отличием в том, что формула, которую вы вставляете, может быть более сложной.Например, вместо того, чтобы рассчитывать только 14% налог на продукты питания в приведенном выше примере, вы также можете добавить к этому налогу с продаж.

Это, безусловно, может быть полезно для мест, где могут быть разные вычисления по всем направлениям, поэтому возможность добавить немного больше сложности к формуле — это здорово.

Как изменить вычисляемое поле

Как удалить вычисляемое поле

Если вы хотите временно удалить вычисляемое поле, просто щелкните поле правой кнопкой мыши и выберите параметр «Удалить», который в данном случае является «Удалить сумму налога на питание».

С другой стороны, если вы хотите удалить поле навсегда, вернитесь в поле Вставить вычисляемое поле , перейдя в Анализировать > Поле, элементы и наборы .

Оказавшись там, в области Имя вы найдете раскрывающийся список вычисляемых полей. Выберите тот, который вам не нужен, и нажмите Удалить .

Как получить список формул вычисляемых полей

Получить список вычисляемых формул в Excel относительно просто.Просто выделите любое поле в сводной таблице, перейдите на верхнюю ленту, где написано Analyze , а затем перейдите к Fields, Items & Sets и нажмите List Formulas .

После этого откроется новый лист со всеми формулами, использованными в вычисляемом поле.

Задачи с вычисляемыми полями

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

Различия между версиями Office

За исключением нескольких вариантов дизайна пользовательского интерфейса, общий макет и названия всех шагов в разных версиях Office одинаковы. Это замечательно, если вы переходите со старой версии на новую.

Когда не использовать сводную таблицу

Знаете ли вы, что в Excel есть ограничение на 1 048 576 строк данных? Большинство пользователей даже близко не приблизятся к этой цифре, но такие случаи бывают. Если ваша работа требует, чтобы вы управляли гигантской таблицей, в которой, например, два миллиона строк, сводные таблицы не справятся с этой задачей сами по себе.

Таким образом, в 2010 году Excel представила надстройку под названием PowerPivot. Это позволяет не только превышать этот предел, но и создавать более эффективные книги, чем те, которые создаются обычными сводными таблицами.

Однако PowerPivot — это другая тема. Если вам интересно, ознакомьтесь с нашим руководством по использованию, установке и настройке PowerPivot.

Заключительные слова

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

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

Зарегистрируйтесь сегодня, чтобы бесплатно опробовать нашу полную библиотеку курсов по Excel и многое другое.

Повысьте свои навыки Excel

Станьте сертифицированным ниндзя Excel с небольшими курсами GoSkills

Начать бесплатную пробную версию

Алекс Уильямс

Алекс Уильямс — опытный разработчик полного цикла и владелец Hosting Data UK.После окончания Лондонского университета по специальности ИТ Алекс почти 10 лет работал разработчиком, ведя различные проекты для клиентов со всего мира. Недавно Алекс стал независимым ИТ-консультантом и завел собственный блог. В свободное время Алекс любит играть в футбол со своими сыновьями и путешествовать по миру с семьей.

Все о вычисляемом поле в сводной таблице в Google Таблицах

Узнайте все о вычисляемом поле в сводной таблице в Google Таблицах.Что такое вычисляемое поле в сводной таблице Google Sheets и как его создать? Я объясню вам эту функцию Pivot в этом руководстве с примерами и скриншотами.

Сводная таблица в Google Таблицах, после последних обновлений, теперь является мощным инструментом для группировки и суммирования большого набора данных.

Сводная таблица позволяет пользователям создавать потрясающие отчеты в Google Таблицах без использования собственных формул.

Сводная таблица

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

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

Аналогично: Месяц, квартал, год Разумная группировка в сводной таблице в Google Таблицах

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

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

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

Если вы используете настраиваемую формулу, будет дополнительный столбец с выходными данными этой формулы, и этот столбец будет называться Вычисляемое поле в сводной таблице в Google Таблицах.

Обязательно к прочтению: Как сгруппировать данные по месяцам и годам в Google Таблицах (формула запроса)

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

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

Как правило, я начинаю с набора данных для этого примера. Это очень простой пример, который поможет вам понять, как создать вычисляемое поле в сводной таблице в Google Таблицах.

Примеры данных сводной таблицы 1:

изображение 1

Примечание: В столбце E есть опечатки. Цены в E4 и E5 составляют 4,50 доллара США, а E6, E7 и E8 — 3 доллара США.00.

Основное назначение сводной таблицы — группировка значений в столбцы.

В этом примере данных я могу сгруппировать столбец A (по месяцам, месяцам и годам, по кварталам, по кварталам и годам или по годам), столбец B (по названию материала) или столбец C (по площади Мудрый).

Например, если я группирую столбец B, в группе будет два элемента — Гравий и Песок. Я группирую столбец B в приведенном ниже примере.

Чтобы сгруппировать любой столбец в сводной таблице, лучшим решением является «ДОБАВИТЬ» соответствующее поле в разделе «Строка» в сводном редакторе.См. Этот пример.

Шаги по созданию базовой сводной таблицы

Сначала выберите диапазон данных A3: E8. Затем щелкните «Сводная таблица» в меню «Данные».

Выберите, хотите ли вы, чтобы отчет был на том же листе или на новом листе. Я выбираю «Новый лист».

Нажмите «Создать». На новой вкладке листа Google Sheets вставит скелет сводной таблицы с боковой панелью под названием «Редактор сводной таблицы».

Как я уже упоминал выше, я группирую столбец B в моем наборе данных выборки, т.е.е. поле «название материала».

Итак, щелкните «ДОБАВИТЬ» напротив «Строки» в «Редакторе сводной таблицы» и выберите «Имя материала». Элементы в поле «название материала», то есть гравий и песок, будут сгруппированы.

изображение 2

Теперь просто обратитесь к нашим образцам данных. Там вы можете увидеть, что общее «количество единиц» гравия составляет 200, а песка — 150.

Есть сомнения? Тогда посмотрите изображение ниже.

изображение 3

Примечание: В данные внесены исправления.Правильные значения в столбце E см. На рисунке 5 ниже.

Я хочу, чтобы эта сумма в сводном отчете сравнивалась с сгруппированными названиями материалов. Как? На шаге ниже я подробно описываю это.

В сводном редакторе щелкните «ДОБАВИТЬ» напротив «Значения» и выберите «количество единиц».

В разделе «Суммировать по» выберите СУММ. Это будет суммировать «количество единиц» по пунктам. Чтобы понять этот шаг и увидеть результат, см. Снимок экрана ниже.

изображение 4

Мы только что создали отчет сводной таблицы без настраиваемого вычисляемого поля.Теперь вы на один шаг ближе к созданию вычисляемого поля в сводной таблице в Google Таблицах.

На следующем шаге я собираюсь создать вычисляемое поле в отчете сводной таблицы Google Sheets.

Шаги по созданию вычисляемых полей в сводной таблице

В приведенном выше сводном отчете я сгруппировал «название материала». Итак, теперь у меня есть уникальные названия материалов и их СУММА «количества единиц».

Теперь мне нужна «цена за единицу».Не СУММА «цены за единицу». Я имею в виду, я хочу получить результат, как показано ниже.

Гравий	200	$ 4,50
Песок	150	$ 3,00

изображение 5

Мы можем СУММИРОВАТЬ «количество единиц», а не «цену за единицу». Мы хотим, чтобы значение 4,50 доллара США не было суммированным значением 9,00 долларов США по отношению к Gravel в сводном отчете.

Если вы добавите «цену за единицу», как показано ниже, в «Сводном редакторе», она, конечно, будет СУММИРОВАТЬ.

изображение 6

Потому что у нас нет другого выбора, кроме выбора любой функции «Суммировать по» (см. Изображение выше) в этом методе.

Таким образом, единственный способ — использовать вычисляемое поле в сводной таблице. Да! Здесь я собираюсь создать вычисляемое поле в сводной таблице Google Sheets.

Расчетное поле 1 (Пример 1)

Чтобы создать вычисляемое поле в сводной таблице, щелкните «ДОБАВИТЬ» напротив «Значения» и выберите «Расчетное поле»

изображение 7

Введите формулу, как показано ниже.Это ярлык поля, и он должен быть заключен в одинарные кавычки. Также выберите «Пользовательский» в разделе «Суммировать по». Это очень важно.

  = "цена за единицу"  

изображение 8

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

И результат будет таким, как показано ниже.

изображение 9

В сводной таблице появился новый столбец с названием «Расчетное поле». Под ним вы можете увидеть «цену за единицу», а не сумму.

Переименование вычисляемых полей

Вы знаете, как переименовать вычисляемое поле?

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

Теперь у меня есть общее количество единиц материала Gravel and Sand и его цена за единицу . Теперь я могу перемножить их, чтобы получить общую сумму вроде:

Гравий: 200 х 4.5 = 900

Песок: 150 x 3 = 450

См. Эту формулу ниже.

Расчетное поле 2 (Пример 1)

Я добавляю для этой цели еще одно вычисляемое поле в сводную таблицу. Процедура такая же, как и выше. Я повторю.

Щелкните «ДОБАВИТЬ» напротив «Значения» и введите приведенную ниже формулу в поле формулы. Снова не забудьте выбрать «Пользовательский» в разделе «Суммировать по».

  = сумма («количество единиц») * «цена за единицу»  

Примечание:

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

Смотрите результат!

изображение 10

Примечание: Прочтите строку общего итога как 350 x 4,5 = 1575 или лучше отключите ее.

Здесь вы могли заметить одну вещь. Я переименовал здесь вычисленные имена полей, как описано ранее.

Вместо того, чтобы просто показывать «Вычисляемое поле 1», «Вычисляемое поле 2», я просто переименовал его в содержательные тексты.Это все.

Я объяснил вам, как добавлять вычисляемые поля в отчет сводной таблицы Google Таблиц.

Дополнительные формулы в вычисляемом поле

Я просто хочу показать вам еще один пример. Чтобы вы могли полностью понять использование вычисляемого поля в сводной таблице в Google Таблицах.

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

Пример данных сводной таблицы 2 :

изображение 11

Вот и есть два материала. Но на этот раз для каждого товара разная «цена за единицу», потому что «площадь» разная.

Например, у материала Гравий здесь две разные цены. Итак, здесь, если вы используете приведенный выше первый пример формулы вычисляемого поля 1, цена за гравий будет 4,50 доллара США.

В любом случае это неверно, так как существует две нормы для материала Гравий. Идеальное решение — использовать среднюю цену.

Почему первая формула настраиваемого вычисляемого поля 1 возвращает 4,50 для гравия и 3 для песка как «цену за единицу»?

Потому что формула настраиваемого вычисляемого поля, используемая там (в первом примере), будет учитывать только «цену за единицу» первых экземпляров материалов.

Если товар имеет разные цены за единицу в каждой строке, вы должны использовать только формулу, которая может возвращать среднее значение «цены за единицу». Например;

Для материала Гравий:

(4.50 + 4,00) / 2 = 4,25

Для материала Песок:

(3,00 + 2,50 + 2,50) / 3 = 2,67

Подходящая формула для вычисляемого поля 1 настраиваемой сводной таблицы:

Расчетное поле 1 (Пример 2)

  = средняя («цена за единицу»)  

Расчетное поле 2 (Пример 2)

Формула для вычисляемого поля 2 здесь:

  = сумма («количество единиц») * средняя («цена за единицу»)  

Пожалуйста, обратитесь к этому изображению.

изображение 12

Если вы не хотите использовать среднюю цену за единицу, воспользуйтесь пользовательскими формулами из примера 1. Но сгруппируйте и «название материала», и «область» (добавьте эти два поля под СТРОКИ в редакторе сводной таблицы). Подробнее см. «Пример 3» в моем листе с примерами ниже.

Надеюсь, вы научились вставлять вычисляемые файлы в сводную таблицу Google Таблиц. Спасибо за пребывание. Наслаждаться!

Пример сводной таблицы 1520

Ресурсы:

.