Создание вычисляемог столбца в Power Pivot
В Power Pivot можно добавлять новые данные в таблицу путем создания вычисляемого столбца. Новый столбец можно использовать в сводных таблицах, сводных диаграммах и отчетах так же, как и любой другой столбец. Для создания вычисляемых столбцов требуется ввести формулу DAX. Например, вы можете создать столбец Total Pay в таблице Payroll путем ввода формулы =[Earnings] + [Bonus] . Она добавляет значения из столбца Earnings к значениям из столбца Bonus в той же таблице для каждой строки. Дополнительные сведения см. в статье Вычисляемые столбцы в Power Pivot.
- Прокрутите таблицу, в которой нужно добавить новый столбец, вправо и выберите крайний правый столбец.
- В строке формул введите допустимую формулу DAX и нажмите клавишу ВВОД.
- Щелкните правой кнопкой мыши заголовок столбца, выберите Переименовать и введите новое имя.
Примечание: В отличие от вычисляемых полей, нельзя указать имя в самой формуле.
Вычисляемое поле в Сводных таблицах в MS Excel
В качестве исходной таблицы возьмем таблицу продаж товара по месяцам. В этой таблице также содержится план продаж. Подробности можно посмотреть в файле примера .
Нашей задачей будет:
- вычислить % выполнения плана
- представить полученные данные по годам для каждого месяца (каждый год — отдельный столбец)
В итоге у нас должна получиться вот такая сводная таблица.
Исходная таблица
Исходную таблицу подготовим в специальном формате таблиц MS EXCEL (см. статью Таблицы в формате EXCEL 2007 ).
На основе даты продажи в столбце А, в таблице рассчитываются 2 столбца: Номер месяца =МЕСЯЦ() и Год =ГОД() . Для форматирования ячеек столбца А в виде окт11 использован пользовательский формат Даты [$-419]МММГГ;@.
Столбец План представляет собой линейный тренд (это не важно для целей данной статьи), столбец Продано — фактический объем продаж.
Сводная таблица
Для создания сводной таблицы выделите любую ее ячейку и в меню Вставка/ Таблицы нажмите кнопку Сводная таблица. В результате появится диалоговое окно.
Нажав ОК, сводная таблица автоматически создастся на новом листе.
В окне Список полей будут отражены названия всех столбцов исходной таблицы. Таким образом, поле — это просто столбец. Вычисляемое поле — это, по сути, вычисляемый столбец.
Перед тем как создать Вычисляемое поле перетащите поле Номер месяца в Названия строк.
Создаем вычисляемое поле
Для решения задачи нам потребуется вычислить % выполнения плана по формуле =’Продано, руб.’/’План, руб.’
Это можно сделать непосредственно в Сводной таблице , создав Вычисляемое поле ПроцентВыполнения.
Для этого выделите ячейку в Сводной таблице, в появившемся меню Работа со сводными таблицами выберите Параметры/ Вычисления/ Поля, элементы и наборы/ Вычисляемое поле :
Появится диалоговое окно:
Интерфейс этого окна не относится к интуитивно понятным вещам, поэтому требует дополнительного пояснения:
- Вместо Поле1 введите название Вычисляемого поля, например, ПроцентВыполнения
- В списке полей выделите поле Продано, руб. и нажмите кнопку Добавить поле или дважды кликните на него. Название поля будет введено в поле Формула
- Введите символ деления / в поле Формула
- В списке полей выделите поле План, руб. и нажмите кнопку Добавить поле
- Нажмите ОК
После проведенных манипуляций в списке поле Сводной таблицы появится еще одно поле. Завершите формирование Сводной таблицы как показано на рисунке ниже, разместив Вычисляемое поле в область Значения.
После несложного форматирования Сводная таблица приобретет законченный вид (необходимо убрать ошибку #ДЕЛ/0!, изменить названия столбцов и изменить формат ячеек на процентный ).
Обратите внимание, что Сводная таблица содержит Общий итог как по столбцам, так и по строкам.
Теперь разберемся, что Вычисляемое поле нам насчитало.
Вычисляемое поле. Алгоритм расчета
Для каждого месяца у нас есть только одно значение фактических продаж (столбец Продажи) и плана. Вычисляемое поле ПроцентВыполнения возвращает значение равное их отношению. Например, для января 2012 года — это 50,19% (продано было 36992,22, а план был 73697,76). 36992,22/73697,76=0,5019 (см. строку 10 на листе Исходная таблица).
Теперь проверим итоги по месяцам. За январь итоговым значением является 93,00%. Как это значение получилось?
Сначала программа вычислила СУММУ продаж за январь по всем годам, затем, вычислила СУММУ всех плановых значений. Разделив одно на другое, было получено 93,00%. В этом можно убедиться проделав вычисления самостоятельно (см. строку 10 на листе Сводная таблица, столбцы H:J).
В этом состоит одно из ограничений Вычисляемого поля — итоговые значения вычисляются только на основании суммирования.
Аналогично расчет ведется и для итогов по столбцам: находится сумма продаж и плана по годам, затем вычисляется их отношение.
Если бы для каждого месяца в исходной таблице было бы несколько сумм продаж и плановых значений, то расчет был бы аналогичен подсчету итоговых значений.
Чтобы обойти данное ограничение и вычислить, например, средний % выполнения плана для всех январских месяцев, придется отказаться от Вычисляемого поля. Создайте в исходной таблице новый столбец — отношение продажи к плану для каждого месяца (см. лист Исходная таблица2). Затем, создайте на ее основе другую сводную таблицу. В окне параметров полей значений установите Среднее.
В итоговом столбце теперь будет отображаться средний процент выполнения плана.
Изменяем и удаляем Вычисляемое поле
Вызовите тоже диалоговое окно, которое мы использовали для создания Вычисляемого поля. В выпадающем списке выберите нужное поле. Появится его формула, которую можно отредактировать, также как и название этого Вычисляемого поля.
Там же можно удалить это поле.
Еще одно ограничение
Еще одно ограничение Вычисляемого поля проявляется при попытке использовать его в качестве названия Строк или Столбцов Сводной таблицы. Этого сделать нельзя. Покажем это на нашем примере.
Изначально в исходной таблице номер месяца и года вычислялись в отдельных столбцах. Попробуем сделать эти вычисления в Вычисляемом поле.
Создать само Вычисляемое поле для номера месяца — не проблема:
Однако, перенести его в качестве строк сводной таблицы не получается.
Вычисление значений в сводной таблице
В сводных таблицах можно использовать функции сведения в полях значений для объединения значений из исходных данных. Если функции сведения и настраиваемые вычисления не дают желаемых результатов, вы можете создать собственные формулы в вычисляемых полях и вычисляемых объектах. Например, можно добавить вычисляемый объект с формулой расчета комиссионных за продажу, которые могут быть разными в различных регионах. Эти комиссионные будут автоматически включены в промежуточные и общие итоги в сводной таблице.
Другой способ вычислений — использовать меры в Power Pivot, которые вы создаете с помощью формулы выражений анализа данных (DAX). Дополнительные сведения см. в разделе Создание меры в Power Pivot.
В сводных таблицах можно рассчитывать данные разными способами. Вы узнаете о доступных методах вычислений, о влиянии типа исходных данных на вычисления и о том, как использовать формулы в сводных таблицах и на сводных диаграммах.
Доступные методы вычислений
Для вычисления значений в сводной таблице можно использовать любые из описанных ниже методов.
-
Функции сведения в полях значений. В сводной таблице в области значений отображаются сводные данные, вычисленные на основе исходных данных. Рассмотрим пример с такими исходными данными:
| Функция | Сведение данных |
|---|---|
| Сумма | Сумма значений. Функция по умолчанию для числовых данных. |
| Количество | Число значений. Действует аналогично функции СЧЁТЗ. Функция по умолчанию для данных, отличных от числовых. |
| Среднее | Среднее арифметическое. |
| Максимум | Наибольшее значение. |
| Минимум | Наименьшее значение. |
| Произведение | Произведение значений. |
| Количество чисел | Количество числовых значений. Действует аналогично функции СЧЁТ. |
| Стандартное отклонение | Оценка стандартного отклонения генеральной совокупности, где выборка является подмножеством всей генеральной совокупности. |
| СТАНДОТКЛОНП | Стандартное отклонение генеральной совокупности, которая содержит все сводимые данные. |
| ДИСП | Оценка дисперсии генеральной совокупности, где выборка является подмножеством всей генеральной совокупности. |
| Несмещенная дисперсия | Дисперсия генеральной совокупности, которая содержит все сводимые данные. |
| Функция | Результат |
|---|---|
| Без вычислений | Значение, введенное в данное поле. |
| % от общей суммы | Значения в процентах от общей суммы всех значений или точек данных в отчете. |
| % от суммы по столбцу | Все значения в каждом столбце или ряду в процентах от итогового значения по этому столбцу или ряду. |
| % от суммы по строке | Значение в каждой строке или категории в процентах от итогового значения по этой строке или категории. |
| Доля | Значения в процентах от значения базового элемента в соответствующем базовом поле. |
| % от суммы по родительской строке | Рассчитывает значения следующим образом: (значение элемента) / (значение родительского элемента по строкам). |
| % от суммы по родительскому столбцу | Рассчитывает значения следующим образом: (значение элемента) / (значение родительского элемента по столбцам). |
| % от родительской суммы | Рассчитывает значения следующим образом: (значение элемента) / (значение родительского элемента в выбранном базовом поле). |
| Отличие | Значения в виде разности по отношению к значению базового элемента в соответствующем базовом поле. |
| Приведенное отличие | Значения в виде разности в процентах по отношению к значению базового элемента в соответствующем базовом поле. |
| С нарастающим итогом в поле | Значение в виде нарастающего итога для последовательных элементов в базовом поле. |
| % от суммы с нарастающим итогом в поле | Значение в виде нарастающего итога в процентах для последовательных элементов в базовом поле. |
| Сортировка от минимального к максимальному | Ранг выбранных значений в определенном поле с учетом того, что наименьшему из них присваивается значение 1, а остальным — значения более высокого ранга соответственно. |
| Сортировка от максимального к минимальному | Ранг выбранных значений в определенном поле с учетом того, что наибольшему значению в поле присваивается значение 1, а каждому меньшему значению — более высокий ранг. |
| Индекс | Рассчитывает значения следующим образом: ((значение в ячейке) x (общий итог)) / ((итог строки) x (итог столбца)). |
- Формулы. Если функции сведения и настраиваемые вычисления не дают желаемых результатов, вы можете создать собственные формулы в вычисляемых полях и вычисляемых объектах. Например, можно добавить вычисляемый объект с формулой расчета комиссионных за продажу, которые могут быть разными в различных регионах. Эти комиссионные будут автоматически включены в промежуточные и общие итоги в отчете.
Влияние типа источника данных на вычисления
Доступность вычислений и параметров в отчете зависит от того, получены ли исходные данные из базы данных OLAP.
- Вычисления на основе исходных данных OLAP. При создании сводных таблиц на основе кубов OLAP сводные значения вычисляются на сервере OLAP еще до отображения результатов в Excel. В сводной таблице невозможно изменить способ вычисления этих значений. Например, вы не сможете выбрать другую функцию сведения для вычисления полей данных или промежуточных итогов и добавить вычисляемые поля или вычисляемые объекты. Кроме того, если сервер OLAP предоставляет вычисляемые поля, называемые «вычисляемыми элементами», вы увидите их в списке полей сводной таблицы. Вы также увидите все вычисляемые поля и вычисляемые объекты, созданные с помощью макросов, которые написаны на языке Visual Basic для приложений (VBA) и хранятся в книге, но не сможете их изменить. Если вам нужны дополнительные типы вычислений, обратитесь к администратору базы данных OLAP. Если исходные данные получены из базы данных OLAP, то при вычислении промежуточных и общих итогов можно включить или исключить значения для скрытых элементов.
- Вычисления на основе исходных данных не из базы данных OLAP. В сводных таблицах, основанных на внешних данных других типов или на данных листа Excel, для вычисления полей значений, содержащих числовые данные, используется функция «Сумма», а для вычисления полей данных, содержащих текст, — функция «Количество». Для дальнейшего анализа и обработки своих данных вы можете выбрать другие функции сведения, например «Среднее», «Максимум» или «Минимум». Кроме того, можно создавать собственные формулы, в которых используются элементы отчета или другие данные листа. Для этого нужно создать вычисляемое поле или вычисляемый объект в поле.
Использование формул в сводных таблицах
Формулы можно создавать только в отчетах, которые основаны на исходных данных, полученных не из источника данных OLAP. В отчетах, основанных на базе данных OLAP, формулы не поддерживаются. При использовании формул в сводных таблицах нужно учитывать описанные ниже правила синтаксиса и поведения формул.
- Элементы формулы сводной таблицы. В формулах, которые создаются для вычисляемых полей и вычисляемых объектов, можно использовать операторы и выражения, как и в других формулах на листе. Также можно использовать константы и ссылаться на данные из отчета, но не допускается использование ссылок на ячейки и определенных имен. Невозможно использовать функции листа, для которых нужны аргументы в виде ссылок на ячейки или определенных имен, а также формулы массива.
- Имена полей и элементов. В Excel имена полей и элементов используются для идентификации этих элементов отчета в формулах. В приведенном ниже примере для данных в диапазоне C3:C9 используется имя поля Молоко. Для вычисляемого объекта в поле Тип, оценивающего объем продаж нового продукта на основе данных о продажах молочных продуктов, можно использовать формулу =Молоко * 115%.
Примечание: На сводной диаграмме имена полей отображаются в списке полей сводной таблицы, а имена элементов можно просмотреть в каждом раскрывающемся списке полей. Не следует путать эти имена с теми, которые отображаются в подсказках к диаграммам и соответствуют именам рядов и точек данных.
Использование формул в сводных диаграммах
Чтобы использовать формулы в сводной диаграмме, их нужно создать в связанной сводной таблице. Там вы увидите отдельные значения, из которых состоят данные, а затем сможете посмотреть на результаты в графическом представлении на сводной диаграмме.
Например, на этой сводной диаграмме представлены данные о продажах для каждого продавца по регионам:
Чтобы посмотреть, как будут выглядеть объемы продаж, если увеличатся на 10 %, можно создать вычисляемое поле в связанной сводной таблице и воспользоваться формулой =Продажи * 110%.
Результат сразу отображается на сводной диаграмме, как показано на этом рисунке:
Чтобы отобразить отдельный маркер данных для продаж в регионе «Север» за вычетом транспортных расходов, которые составляют 8 %, можно создать в поле «Регион» вычисляемый объект с такой формулой: =Север – (Север * 8%).
Диаграмма будет выглядеть следующим образом:
Однако вычисляемый объект, созданный в поле «Продавец», будет отображаться как ряд, представленный в легенде, и появится на диаграмме в виде точки данных в каждой категории.
Создание формул в сводной таблице
Важно: Создать формулу в сводной таблице, подключенной к источнику данных OLAP, невозможно.
Прежде всего определитесь, что нужно создать в поле: вычисляемое поле или вычисляемый объект. Если вы хотите использовать в формуле данные из другого поля, создайте вычисляемое поле. Если нужно использовать в формуле данные из одного или нескольких конкретных элементов в определенном поле, создайте вычисляемый объект.
В случае вычисляемых объектов в отдельных ячейках можно вводить разные формулы. Например, если вычисляемый объект с именем ЯблокиОбласть содержит формулу =Яблоки * 0,25 для всех месяцев, то для июля, августа и сентября ее можно заменить формулой =Яблоки * 0,5.
Если есть несколько вычисляемых объектов или формул, можно настроить порядок вычислений.
Добавление вычисляемого поля
- Щелкните сводную таблицу. Отобразится вкладка «Работа со сводными таблицами» с дополнительными вкладками Анализ и Конструктор.
- На вкладке Анализ в группе Вычисления выберите команду Поля, элементы и наборы, а затем — пункт Вычисляемое поле.
Добавление вычисляемого объекта в поле
- Щелкните сводную таблицу. Отобразится вкладка «Работа со сводными таблицами» с дополнительными вкладками Анализ и Конструктор.
- Если элементы в поле сгруппированы, на вкладке Анализ в группе Группировать выберите команду Разгруппировать.
Ввод разных формул для вычисляемых объектов в отдельных ячейках
- Щелкните ячейку, для которой нужно изменить формулу. Чтобы изменить формулу для нескольких ячеек, нажмите клавишу CTRL и, удерживая ее, выделите остальные ячейки.
- В строке формул внесите изменения в формулу.
Изменение порядка вычислений для нескольких вычисляемых объектов или формул
- Щелкните сводную таблицу. Отобразится вкладка «Работа со сводными таблицами» с дополнительными вкладками Анализ и Конструктор.
- На вкладке Анализ в группе Вычисления выберите команду Поля, элементы и наборы, а затем — пункт Порядок вычислений.
Просмотр всех формул, используемых в сводной таблице
Вы можете отобразить список всех формул, которые используются в текущей сводной таблице.
- Щелкните сводную таблицу. Отобразится вкладка «Работа со сводными таблицами» с дополнительными вкладками Анализ и Конструктор.
- На вкладке Анализ в группе Вычисления выберите команду Поля, элементы и наборы, а затем — пункт Вывести формулы.
Редактирование формулы в сводной таблице
Перед тем как редактировать формулу, определите, к чему она относится: к вычисляемому полю или вычисляемому объекту. Если формула находится в вычисляемом объекте, также определите, является ли она единственной для него.
В случае вычисляемых объектов можно изменять отдельные формулы для конкретных ячеек одного объекта. Например, если вычисляемый объект с именем ЯблокиРасчет содержит формулу =Яблоки * 0,25 для всех месяцев, то для июля, августа и сентября ее можно заменить формулой =Яблоки * 0,5.
Определение принадлежности формулы к вычисляемому полю или вычисляемому объекту
- Щелкните сводную таблицу. Отобразится вкладка «Работа со сводными таблицами» с дополнительными вкладками Анализ и Конструктор.
- На вкладке Анализ в группе Вычисления выберите команду Поля, элементы и наборы, а затем — пункт Вывести формулы.
Редактирование формулы вычисляемого поля
- Щелкните сводную таблицу. Отобразится вкладка «Работа со сводными таблицами» с дополнительными вкладками Анализ и Конструктор.
- На вкладке Анализ в группе Вычисления выберите команду Поля, элементы и наборы, а затем — пункт Вычисляемое поле.
Редактирование одной формулы для вычисляемого объекта
- Щелкните поле, содержащее вычисляемый объект.
- На вкладке Анализ в группе Вычисления выберите команду Поля, элементы и наборы, а затем — пункт Вычисляемый объект.
Редактирование отдельной формулы для конкретной ячейки вычисляемого объекта
- Щелкните ячейку, для которой нужно изменить формулу. Чтобы изменить формулу для нескольких ячеек, нажмите клавишу CTRL и, удерживая ее, выделите остальные ячейки.
- В строке формул внесите изменения в формулу.
Совет: Если есть несколько вычисляемых объектов или формул, можно настроить порядок вычислений. Дополнительные сведения см. в разделе Изменение порядка вычислений для нескольких вычисляемых объектов или формул.
Удаление формулы из сводной таблицы
Примечание: Если удалить формулу из сводной таблицы, восстановить ее будет невозможно. Если вы не хотите удалять формулу безвозвратно, то можете просто скрыть поле или элемент, перетащив его за пределы сводной таблицы.
- Определите, к чему относится формула: к вычисляемому полю или вычисляемому объекту. Вычисляемые поля отображаются в списке полей сводной таблицы. Вычисляемые объекты отображаются в виде элементов в других полях.
- Выполните одно из указанных ниже действий.
- Если нужно удалить вычисляемое поле, щелкните в любом месте сводной таблицы.
- Если нужно удалить вычисляемый объект, в сводной таблице щелкните поле, которое его содержит. Отобразится вкладка «Работа со сводными таблицами» с дополнительными вкладками Анализ и Конструктор.
- На вкладке Анализ в группе Вычисления выберите команду Поля, элементы и наборы, а затем — пункт Вычисляемое поле или Вычисляемый объект.
Для сведения данных в сводной таблице в Excel в Интернете можно использовать такие функции, как СУММ, СЧЁТ и СРЗНАЧ. По умолчанию для чисел в полях значений используется функция СУММ. Вы можете просматривать и редактировать сводную таблицу на основе источника данных OLAP, но не можете создать ее в Excel для Интернета.
Ниже описано, как выбрать другие функции сведения данных.
- Щелкните в любом месте сводной таблицы, а затем выберите Сводная таблица >Список полей. Вы также можете щелкнуть сводную таблицу правой кнопкой мыши и выбрать Показать список полей.
- В списке Поля сводной таблицы в группе Значения щелкните стрелку рядом с полем значений.
Примечание: Функции сведения недоступны в сводных таблицах на базе источников данных OLAP.
| Функция сведения данных | Вычисляемое значение |
|---|---|
| Сумма | Сумма значений. Используется по умолчанию для полей с числовыми значениями. |
| СЧЁТ | Количество заполненных полей. Функция сведения данных СЧЁТ работает так же, как СЧЁТЗ. СЧЁТ по умолчанию используется для пустых полей и полей с нечисловыми значениями. |
| Среднее | Среднее арифметическое. |
| Максимум | Наибольшее значение. |
| Минимум | Наименьшее значение. |
| Произведение | Произведение значений. |
| Количество чисел | Количество значений, содержащих числа (отличается от функции СЧЁТ, в которой учитываются заполненные поля). |
| Смещенное отклонение | Оценка стандартного отклонения генеральной совокупности, где выборка является подмножеством всей генеральной совокупности. |
| СТАНДОТКЛОНП | Стандартное отклонение генеральной совокупности, которая содержит все сводимые данные. |
| ДИСП | Оценка дисперсии генеральной совокупности, где выборка является подмножеством всей генеральной совокупности. |
| Несмещенная дисперсия | Дисперсия генеральной совокупности, которая содержит все сводимые данные. |
Сводная таблица на iPad постепенно развертывается для пользователей, использующих Excel на iPad версии 2.80.1203.0 и выше.
Чтобы суммировать значения в сводной таблице в Excel для iPad, можно использовать функции сводки, такие как Sum, Count и Average. По умолчанию для чисел в полях значений используется функция СУММ. Вы можете просматривать и изменять сводную таблицу на основе источника данных OLAP, но не можете создать ее в Excel для iPad.
Ниже описано, как выбрать другие функции сведения данных.
1. Коснитесь в любом месте сводной таблицы, чтобы отобразить ее на вкладке Сводная таблица , проведите пальцем влево и выберите Список полей , чтобы отобразить список полей.
2. В списке Поля сводной таблицы в разделе Значения коснитесь многоточия рядом с полем значения.
3. Выберите Параметры поля.
4. Проверьте нужную функцию сводки.
Примечание: Функции сведения недоступны в сводных таблицах на базе источников данных OLAP.
Функция сведения данных
Вычисляемое значение
Сумма значений. Используется по умолчанию для полей с числовыми значениями.
Количество заполненных полей. Функция сведения данных СЧЁТ работает так же, как СЧЁТЗ. СЧЁТ по умолчанию используется для пустых полей и полей с нечисловыми значениями.
Количество значений, содержащих числа (отличается от функции СЧЁТ, в которой учитываются заполненные поля).
Оценка стандартного отклонения генеральной совокупности, где выборка является подмножеством всей генеральной совокупности.
Стандартное отклонение генеральной совокупности, которая содержит все сводимые данные.
Оценка дисперсии генеральной совокупности, где выборка является подмножеством всей генеральной совокупности.
Дисперсия генеральной совокупности, которая содержит все сводимые данные.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
Как в сводной таблице добавить столбец с формулой
Argument ‘Topic id’ is null or empty
Сейчас на форуме
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
| ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |