вычисляемое поле в эксель
Вычисляемое поле сводной таблицы «Excel». ( формулы в сводной таблице )
По умолчанию сводная таблица отображает поля, которые вычисляют сумму или количество значений из источника данных.
Как правило, этого хватает, чтобы обработать данные и провести простую аналитику полученной информации.
Иногда случается так, что суммы и количества из полей сводной таблицы недостаточно. В таких случаях на помощь приходят вычисляемые поля.
в этих полях можно прописать формулу, которая будет обрабатывать информацию сводной таблицы так, как Вам требуется.
О том, как записать формулу в вычисляемые поля сводной таблицы «Эксель» расскажу далее.
На основании таблицы, в которой изображены продажи товаров за несколько лет,
Таблица на листе в Excel
построена сводная таблица.
Сводная таблица в Эксель
В сводную таблицу нужно добавить вычисляемые ячейки с формулами.
Рассмотрим, как это можно сделать.
Выделяем сводную таблицу кликом левой кнопки мыши.
Перейти во вкладку «Параметры»
Кликнуть по ярлыку «Поля, элементы, наборы»
Вставить вычесляемое поле
Выбрать пункт «Вычисляемое поле»
В открывшемся окне указать имя нового поля.
Имя поля
Вставка формулы в поле сводной таблицы
Если в формуле следует использовать поля сводной таблицы, их можно внести в формулу кнопкой добавить.
Вычисление значений в сводной таблице
В сводных таблицах можно использовать функции сведения в полях значений для объединения значений из исходных данных. Если функции сведения и настраиваемые вычисления не дают желаемых результатов, вы можете создать собственные формулы в вычисляемых полях и вычисляемых объектах. Например, можно добавить вычисляемый объект с формулой расчета комиссионных за продажу, которые могут быть разными в различных регионах. Эти комиссионные будут автоматически включены в промежуточные и общие итоги в сводной таблице.
В сводных таблицах можно рассчитывать данные разными способами. Вы узнаете о доступных методах вычислений, о влиянии типа исходных данных на вычисления и о том, как использовать формулы в сводных таблицах и на сводных диаграммах.
Доступные методы вычислений
Для вычисления значений в сводной таблице можно использовать любые из описанных ниже методов.
Функции сведения в полях значений. В сводной таблице в области значений отображаются сводные данные, вычисленные на основе исходных данных. Рассмотрим пример с такими исходными данными:
Сводная таблица и сводная диаграмма выглядят, как показано на рисунке ниже. Если создать сводную диаграмму на основе данных из сводной таблицы, то значения на диаграмме будут соответствовать вычислениям в связанной сводной таблице.
В сводной таблице поле столбца Месяц содержит элементы Март и Апрель. Поле строки Регион содержит элементы Север, Юг, Восток и Запад. Значение на пересечении столбца Апрель и строки Север — это общая выручка от продаж, определенная по исходным данным, для которых столбец Месяц содержит значение Апрель, а столбец Регион — значение Север.
В сводной диаграмме поле Регион может представлять собой поле категорий, в котором элементы Север, Юг, Восток и Запад отображаются как категории. Поле Месяц поле может быть полем рядов, в котором элементы Март, Апрель и Май отображаются как ряды, представленные в легенде. Поле значений с именем Сумма продаж может содержать маркеры данных, которые представляют общую выручку в каждом регионе за каждый месяц. Например, один маркер данных может представлять (своим положением на вертикальной оси, т. е. оси значений) сумму продаж за месяц Апрель в регионе Север.
Ниже перечислены функции сведения, с помощью которых можно вычислять поля значений. Эти функции доступны для всех типов исходных данных, кроме 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%).
Диаграмма будет выглядеть следующим образом:
Однако вычисляемый объект, созданный в поле «Продавец», будет отображаться как ряд, представленный в легенде, и появится на диаграмме в виде точки данных в каждой категории.
Вычисляемое поле в эксель
Сводные таблицы способны проводить вычисления, гораздо более сложные, чем простые суммирования данных. В данной статье я расскажу, как можно заставить Сводную таблицу «считать».
| Файл | Описание | Размер файла: | Скачивания |
|---|---|---|---|
| Пример | 109 Кб | 3580 |
Допустим, мы имеем такую сводную таблицу:
Нам необходимо посчитать долю каждого менеджера в разрезе месяцев. Для этого в правом окне «Поля сводной таблицы», наводим курсор мышки на поле «Сумма», нажимаем левую клавишу и удерживая клавишу мышки тянем поле «Сумма» в окно «Значения»:
Как включить видео?
Чтобы увидеть видео включите в браузере GIF-Анимацию.
Затем наводим курсор на это поле в окне «Значения» и нажимаем на него левой клавишей мышки, в выпавшем меню выбираем пункт «Параметры полей значений. «:
Как включить видео?
Чтобы увидеть видео включите в браузере GIF-Анимацию.
В открывшемся диалоговом окне «Параметры поля значений», меняем название поля, например, на «Доля»:
Переходим во вкладку «Дополнительные вычисления», в поле со списком (там, где написано «Без вычислений») выбираем пункт «% от суммы по столбцу» и нажимаем кнопку «ОК»:
После чего ваша Сводная таблица должна будет приобрести следующий вид:
Поэкспериментируйте с другими вариантами вычислений.
Еще один пример вычислений, имеем такую Сводную таблицу:
В открывшемся диалоговом окне меняем название поля, например, на «Рентабельность»:
Вычисляемые объекты в Excel и службах Excel
Microsoft Excel 2013 предоставляет разнообразные возможности бизнес-аналитики, которые позволяют создавать функциональные отчеты, системы показателей и панели мониторинга. Новые улучшенные возможности позволяют создавать вычисляемые объекты (например, вычисляемые показатели, элементы и поля). Прочтите эту статью, чтобы узнать о таких объектах и о том, поддерживаются ли они службами Excel.
Вычисляемые объекты в службах Excel
В Excel можно создавать такие вычисляемые объекты: вычисляемые показатели, вычисляемые элементы и вычисляемые поля. Они позволяют задавать и использовать настраиваемые вычисления и наборы элементов, не существующие в базах данных, которые используются для создания отчетов сводной диаграммы и сводной таблицы.
Если книга содержит вычисляемые объекты, вы можете предоставить к ней общий доступ, передав ее в библиотеку SharePoint. В зависимости от настроек среды SharePoint, в окне браузера можно просматривать книги, содержащие вычисляемые объекты, а также работать с ними. Но в отдельных случаях эта возможность не поддерживается.
Если ваша организация использует Office Web Apps сервер вместе с SharePoint Server 2013 (локальным), то для отрисовки книг в окне браузера используется либо службы Excel (SharePoint server 2013), либо Excel Web App (Office Web Apps Server). Это решение может повлиять на то, будут ли книги, которые содержат вычисляемые поля (созданные с помощью Power Pivot для Excel), просматриваться в окне браузера.
В таблице ниже приведены сведения о том, поддерживаются ли вычисляемые элементы в службах Excel (SharePoint Server 2013), Excel Web App (Office Web Apps Server) и Excel в Интернете (в SharePoint).
Службы Excel в локальной версии SharePoint Server 2013
Excel Web App (локальная версия Office Web Apps)
Excel в Интернете (в SharePoint Online )
Да, если используемые источники данных поддерживаются в SharePoint. Ознакомьтесь с использованием внешних данных в книгах в SharePoint.
Да, если используемые источники данных поддерживаются в SharePoint. Ознакомьтесь с использованием внешних данных в книгах в SharePoint.
Возможности Power Pivot, в том числе создание вычисляемых полей и моделей данных, в Office Web Apps Server (локальной версии) не поддерживаются.
Да, если используемые источники данных поддерживаются в SharePoint. Ознакомьтесь с использованием внешних данных в книгах в SharePoint.
Дополнительные сведения можно найти в следующих статьях:
Важно: Если при попытке открыть книгу, содержащую вычисляемые объекты (или неподдерживаемые функции) в окне браузера появляется сообщение о том, что книга не поддерживается, попробуйте открыть ее в Excel 2013.
Что такое вычисляемые показатели?
Вычисляемый показатель — настраиваемое вычисление, которое вы можете создать в Excel при работе с многомерными данными, хранящимися в службах SQL Server Analysis Services. Вычисляемые показатели удобно использовать при задании вычислений, которые могут отсутствовать в базе данных. Примеры настраиваемых вычислений:
показатель торговой квоты, использующий определенную формулу;
процент от общего итога для элементов группы;
валовая прибыль, которая рассчитывается с помощью сложного запроса;
показатель дохода, использующий сумму валового дохода и себестоимости продукции.
При создании вычисляемого показателя необходимо определить запрос многомерных выражений (MDX). Это легко сделать с помощью диалогового окна «Вычисляемый показатель» в Excel, которое позволяет настроить запрос с помощью функций перетаскивания.
Создание вычисляемого показателя в Excel
Создайте отчет сводной таблицы или сводной диаграммы, используя данные из куба служб аналитики.
На вкладке Анализ группы Вычисления выберите Средства OLAP > Вычисляемая мера многомерного выражения. Откроется диалоговое окно Создание вычисляемой меры.
Введите имя вычисляемого показателя в поле Имя.
(Это действие не является обязательным.) Чтобы указать, как вычисляемый показатель должен располагаться в списке полей сводной таблицы или сводной диаграммы, выполните хотя бы одно из указанных ниже действий.
С помощью списка Группа показателей укажите, как вычисляемый показатель будет располагаться в списке Поля сводной таблицы или Поля сводной диаграммы. Если группа показателей не указана, вычисляемый показатель будет отображаться в группе Значения.
В поле Папка введите имя папки для вычисляемого показателя.
Перетащите элемент (например, показатель) с вкладки Поля и элементы на панель MDX.
Перетащите второй элемент с вкладки Поля и элементы на панель MDX.
Повторяйте шаги 5–7, пока не будут добавлены все элементы формулы.
Например, если вы создаете вычисляемую меру с именем «доход», то на панели MDX может быть запрос, похожий на
[Measures].[Product Cost]+[Measures].[Gross Profit]
Нажмите кнопку Проверить MDX, чтобы убедиться, что запрос работает правильно.
Чтобы создать вычисляемый показатель, нажмите кнопку ОК.
Чтобы использовать вычисляемый показатель в отчете, выберите его в списке Поля сводной таблицы или Поля сводной диаграммы. Ее расположение было указано в шаге 4.
Примечание: Так как вычисляемая мера многомерных выражений в Excel используется для создания вычислений для источника данных служб SQL Server Analysis Services, вычисляемое измерение будет ограничено сеансом и используемым подключением к источнику данных. Узнайте больше о том, как создавать вычисляемые элементы с областью действия сеанса.
Что такое вычисляемые элементы?
Вычисляемый элемент — это набор элементов, который вы можете определить в Excel при работе с многомерными данными, хранящимися в службах SQL Server Analysis Services. Вычисляемые элементы удобно использовать для определения наборов элементов, которых еще нет в базе данных. Примеры пользовательских наборов:
территория, состоящая из таких географических единиц, как страны, регионы и штаты;
группа продуктов, учитываемых в квоте торгового представителя;
набор рекламных задач, относящихся к определенной маркетинговой кампании.
По аналогии с вычисляемыми показателями при создании вычисляемого элемента определяется MDX-запрос. Это легко сделать с помощью диалогового окна «Вычисляемый элемент» в Excel, которое позволяет настроить запрос с помощью функций перетаскивания.
Примечание: При работе со сводной таблицей в Excel или использовании средств OLAP для добавления вычисляемого элемента вы не сможете отменить его выбор в раскрывающемся списке полей, если источник данных связан с сервером, на котором работает SQL Server 2008 или более ранней версии. Если источник данных связан с сервером, на котором работает SQL Server 2008 R2 или более поздней версии, можно выбирать вычисляемый элемент и отменять его выбор в раскрывающемся списке фильтра.
Создание вычисляемого элемента в Excel
Создайте отчет сводной таблицы или сводной диаграммы, используя данные из куба служб аналитики.
На вкладке Анализ группы Вычисления выберите Средства OLAP > Вычисляемый элемент многомерного выражения. Откроется диалоговое окно Создание вычисляемого элемента.
Введите имя вычисляемого элемента в поле Имя.
С помощью списка Родительская иерархия укажите, как вычисляемый элемент будет располагаться в списке Поля сводной таблицы или Поля сводной диаграммы.
Обратите внимание на то, что вы выбираете. Его необходимо знать, чтобы использовать вычисляемый элемент в отчете сводной таблицы или сводной диаграммы.
Перетащите элемент (например, иерархию измерения) с вкладки Поля и элементы на панель MDX.
Затем на панель Многомерное выражение добавьте операцию, например «+», «-«, «/», или «*».
Перетащите второй элемент с вкладки Поля и элементы на панель MDX.
Повторяйте действия 5–7 до тех пор, пока не создадите формулу со всеми нужными элементами.
Например, если вы создаете вычисляемый элемент «основные продукты», включающий все категории продуктов (кроме двух), в области многомерных выражений может появиться запрос, подобный
[Product].[Product Categories]-[Product].[Product Categories].[Category].&[4]-[Product].[Product Categories].[Category].&[3]
Нажмите кнопку Проверить MDX, чтобы убедиться, что запрос работает правильно.
Чтобы создать вычисляемый элемент, нажмите кнопку ОК.
Чтобы добавить вычисляемый элемент в отчет сводной таблицы или сводной диаграммы, сделайте следующее:
убедитесь, что для отчета выбран хотя бы один показатель;
в списке Поля сводной таблицы или Поля сводной диаграммы раскройте родительское измерение, указанное в шаге 4;
установите флажок рядом с иерархией измерения, соответствующей той иерархии, которая использовалась для создания вычисляемого элемента. В отчете отображаются сведения для всех элементов измерения в группе, включая созданный вычисляемый элемент.
(Это действие не является обязательным.) Чтобы в отчете отображались только сведения вычисляемого элемента, сделайте следующее:
в списке Поля сводной таблицы или Поля сводной диаграммы наведите указатель мыши на иерархию измерения, содержащую вычисляемый элемент;
когда появится стрелка вниз, щелкните или коснитесь ее, чтобы открыть диалоговое окно Выбор поля;
снимите флажки всех элементов, кроме созданного вычисляемого элемента.
Вычисляемые поля
Вычисляемые поля удобно использовать, когда нужно создать вычисляемый объект в сводной таблице или отчете, в которых используются не многомерные данные, хранящиеся в службах аналитики, а модель данных, созданная с помощью Power Pivot в Excel. Значения в вычисляемых полях могут изменяться в зависимости от контекста. Контекст определяется выбранными строками, столбцами, фильтрами или пользовательской формулой Data Analysis Expressions (DAX), созданной в PowerPivot.
Подобно вычисляемым показателям и элементам вычисляемые поля отображаются в списке «Поля сводной таблицы» (или «Поля сводной диаграммы»), как правило, в группе «Значения». Создать вычисляемое поле можно несколькими способами.
Создание вычисляемого поля в Excel
Подробнее о создании и использовании вычисляемых полей см. в статьях:
Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).