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

Вычисляемые объекты в 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 в SharePoint с Excel Web App

• Бизнес-аналитика в Excel и службах Excel (SharePoint Server) (это относится к локальным средам)

• Возможности бизнес-аналитики в Power BI для Microsoft 365, Excel и SharePoint (это относится к Microsoft 365 ным средам)

Важно: Если при попытке открыть книгу, содержащую вычисляемые объекты (или неподдерживаемые функции) в окне браузера появляется сообщение о том, что книга не поддерживается, попробуйте открыть ее в Excel 2013.

К началу страницы

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

Вычисляемый показатель — настраиваемое вычисление, которое вы можете создать в Excel при работе с многомерными данными, хранящимися в службах SQL Server Analysis Services. Вычисляемые показатели удобно использовать при задании вычислений, которые могут отсутствовать в базе данных. Примеры настраиваемых вычислений:

• показатель торговой квоты, использующий определенную формулу;

• процент от общего итога для элементов группы;

• валовая прибыль, которая рассчитывается с помощью сложного запроса;

• org/ListItem»>

  показатель дохода, использующий сумму валового дохода и себестоимости продукции.

При создании вычисляемого показателя необходимо определить запрос многомерных выражений (MDX). Это легко сделать с помощью диалогового окна «Вычисляемый показатель» в Excel, которое позволяет настроить запрос с помощью функций перетаскивания.

Создание вычисляемого показателя в Excel

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

2. На вкладке Анализ группы Вычисления выберите Средства OLAP > Вычисляемая мера многомерного выражения

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

3. Введите имя вычисляемого показателя в поле Имя.

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

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

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

   • В поле Папка введите имя папки для вычисляемого показателя.

5. Перетащите элемент (например, показатель) с вкладки Поля и элементы на панель MDX.

6. Добавьте оператор, например +, -, / или *, после элемента на панели MDX

   .

7. Перетащите второй элемент с вкладки Поля и элементы на панель MDX.

8. org/ListItem»>

   Повторяйте шаги 5–7, пока не будут добавлены все элементы формулы.
   Например, если вы создаете вычисляемую меру с именем «доход», то на панели MDX может быть запрос, похожий на
   [Measures].[Product Cost]+[Measures].[Gross Profit]

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

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

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

Примечание: Так как вычисляемая мера многомерных выражений в Excel используется для создания вычислений для источника данных служб SQL Server Analysis Services, вычисляемое измерение будет ограничено сеансом и используемым подключением к источнику данных. Узнайте больше о том, как создавать вычисляемые элементы с областью действия сеанса.

К началу страницы

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

Вычисляемый элемент — это набор элементов, который вы можете определить в Excel при работе с многомерными данными, хранящимися в службах SQL Server Analysis Services. Вычисляемые элементы удобно использовать для определения наборов элементов, которых еще нет в базе данных. Примеры пользовательских наборов:

• org/ListItem»>

  территория, состоящая из таких географических единиц, как страны, регионы и штаты;

• группа продуктов, учитываемых в квоте торгового представителя;

• набор рекламных задач, относящихся к определенной маркетинговой кампании.

По аналогии с вычисляемыми показателями при создании вычисляемого элемента определяется MDX-запрос. Это легко сделать с помощью диалогового окна «Вычисляемый элемент» в Excel, которое позволяет настроить запрос с помощью функций перетаскивания.

Примечание: При работе со сводной таблицей в Excel или использовании средств OLAP для добавления вычисляемого элемента вы не сможете отменить его выбор в раскрывающемся списке полей, если источник данных связан с сервером, на котором работает SQL Server 2008 или более ранней версии. Если источник данных связан с сервером, на котором работает SQL Server 2008 R2 или более поздней версии, можно выбирать вычисляемый элемент и отменять его выбор в раскрывающемся списке фильтра.

Создание вычисляемого элемента в Excel

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

2. На вкладке Анализ группы Вычисления выберите Средства OLAP > Вычисляемый элемент многомерного выражения. Откроется диалоговое окно Создание вычисляемого элемента.

3. org/ListItem»>

   Введите имя вычисляемого элемента в поле Имя.

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

5. Перетащите элемент (например, иерархию измерения) с вкладки Поля и элементы на панель MDX.

6. Затем на панель Многомерное выражение добавьте операцию, например «+», «-«, «/», или «*».

7. Перетащите второй элемент с вкладки Поля и элементы на панель MDX.

8. Повторяйте действия 5–7 до тех пор, пока не создадите формулу со всеми нужными элементами.
   Например, если вы создаете вычисляемый элемент «основные продукты», включающий все категории продуктов (кроме двух), в области многомерных выражений может появиться запрос, подобный
   [Product].[Product Categories]-[Product].[Product Categories].[Category].&[4]-[Product].[Product Categories].[Category].&[3]

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

10. Чтобы создать вычисляемый элемент, нажмите кнопку ОК.

11. Чтобы добавить вычисляемый элемент в отчет сводной таблицы или сводной диаграммы, сделайте следующее:

    1. убедитесь, что для отчета выбран хотя бы один показатель;

    2. в списке Поля сводной таблицы или Поля сводной диаграммы раскройте родительское измерение, указанное в шаге 4;

    3. org/ListItem»>

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

12. (Это действие не является обязательным.) Чтобы в отчете отображались только сведения вычисляемого элемента, сделайте следующее:

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

    2. org/ListItem»>

       когда появится стрелка вниз, щелкните или коснитесь ее, чтобы открыть диалоговое окно Выбор поля;

    3. снимите флажки всех элементов, кроме созданного вычисляемого элемента.

К началу страницы

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

Вычисляемые поля удобно использовать, когда нужно создать вычисляемый объект в сводной таблице или отчете, в которых используются не многомерные данные, хранящиеся в службах аналитики, а модель данных, созданная с помощью Power Pivot в Excel. Значения в вычисляемых полях могут изменяться в зависимости от контекста. Контекст определяется выбранными строками, столбцами, фильтрами или пользовательской формулой Data Analysis Expressions (DAX), созданной в PowerPivot.

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

Создание вычисляемого поля в Excel

Подробнее о создании и использовании вычисляемых полей см. в статьях:

• Вычисляемые поля в PowerPivot

• Создание вычисляемого поля в Power Pivot

К началу страницы

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Формула сводной таблицы в Excel

Формула сводной таблицы в Excel (оглавление)

• Формула сводной таблицы в Excel
• Пользовательское поле для расчета суммы прибыли
• Расширенная формула в вычисляемом поле

В Excel мы можем добавлять и изменять формулы, доступные в вычисляемых полях по умолчанию, после создания сводной таблицы. Чтобы увидеть и обновить формулу сводной таблицы, создайте сводную таблицу с соответствующими полями, которые мы хотим сохранить. Выбрав или наведя на него курсор, выберите «Вычисляемые поля» в раскрывающемся списке «Поля, элементы и наборы» на ленте меню «Анализ». Там мы сможем увидеть все поля, используемые в сводной таблице, а также раздел «Имя» и «Формула».

Пользовательское поле для расчета суммы прибыли

Вы можете скачать этот шаблон Excel с формулой сводной таблицы здесь – Шаблон Excel с формулой сводной таблицы

Это наиболее часто используемое вычисляемое поле в сводной таблице. Пожалуйста, взгляните на приведенные ниже данные; У меня есть столбец «Название страны», «Наименование продукта», «Продано единиц», «Цена за единицу», «Валовые продажи», «Себестоимость проданных товаров», «Дата» и «Год».

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

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

Шаг 1: Выберите ячейку в сводной таблице. Перейдите на вкладку «Анализ» на ленте и выберите «Поля, элементы и наборы». Под этим выберите Вычисляемое поле.

Шаг 2: В приведенном ниже диалоговом окне дайте имя новому вычисляемому полю.

Шаг 3: В разделе «Формула» примените формулу, чтобы найти прибыль. Формула для определения прибыли – это валовой объем продаж – себестоимость.

Зайдите в строку формул > выберите «Валовые продажи» в поле ниже и дважды щелкните его, чтобы он появился в строке формул.

Теперь введите символ минус (-) и выберите COGS > Двойной щелчок.

Шаг 4: Нажмите ДОБАВИТЬ и ОК, чтобы завершить формулу.

Шаг 5: Теперь у нас есть столбец ОБЩАЯ ПРИБЫЛЬ в сводной таблице.

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

Шаг 6: Теперь нам нужно рассчитать процент прибыли. Формула для расчета процента прибыли: общая прибыль / валовые продажи.

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

Шаг 7: Теперь мы должны увидеть вновь вставленное вычисляемое поле Total Profit в списке Fields. Вставьте это поле в формулу.

Шаг 8: Введите символ разделителя (/) и вставьте поле «Валовые продажи».

Шаг 9: Назовите это вычисляемое поле как Процент прибыли.

Шаг 10: Нажмите ДОБАВИТЬ и ОК, чтобы завершить формулу. У нас есть процент прибыли в качестве нового столбца.

Усовершенствованная формула в вычисляемом поле

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

Если % прибыли >15%, поощрение должно составлять 6% от общей прибыли.

Если % прибыли >10%, поощрение должно составлять 5% от общей прибыли.

Если % прибыли <10%, поощрение должно составлять 3% от общей прибыли.

Шаг 1: Перейдите к вычисляемому полю и откройте диалоговое окно ниже. Дайте имя как сумма поощрения.

Шаг 2: Теперь я буду использовать условие ЕСЛИ для расчета суммы поощрения. Примените приведенные ниже формулы, как показано на изображении.

= ЕСЛИ (‘ProfitPercentage’>15%, ‘TotalProft’*6%, IF(‘ProfitPercentage’>10%, ‘Total Profit’*5%, ‘Total Profit’ *3%))

Шаг 3: Нажмите ДОБАВИТЬ и ОК для завершения. Теперь у нас есть столбец «Сумма поощрения».

Ограничение вычисляемого поля

Мы видели чудо вычисляемых полей, но оно также имеет некоторые ограничения. Теперь взгляните на изображение ниже; если я хочу увидеть разбивку суммы поощрения в зависимости от продукта, у нас будет неправильная ПРОМЕЖУТОЧНАЯ ИТОГО И ОБЩАЯ ИТОГОЯ СУММЫ ПООЩРЕНИЯ.

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

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

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

Выберите «Анализ» > «Поля, элементы и наборы» — > «Список формул».

Это даст вам сводку всех формул на новом листе.

Что нужно помнить о формуле сводной таблицы в Excel

• Мы можем удалять, изменять все вычисляемые поля.
• Мы не можем использовать такие формулы, как ВПР, СУММЕСЛИ и многие другие диапазоны, включающие формулы в вычисляемых полях, т. е. все формулы, требующие диапазона, не могут быть использованы.

Рекомендуемые статьи

Это руководство по формуле сводной таблицы в Excel. Здесь мы обсудили шаги по использованию формулы сводной таблицы в Excel, а также примеры и загружаемый шаблон Excel. Вы также можете посмотреть на эти полезные функции в Excel –

1. Сводная таблица в Excel
2. Excel Удалить сводную таблицу
3. Сводная таблица VBA
4. Обновление сводной таблицы VBA

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

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

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

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

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

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

Вы можете рассчитать Себестоимость проданных товаров и Валовая прибыль , применив следующие формулы;

Себестоимость проданных товаров = Сумма продаж * 60%

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

Валовая прибыль = Сумма продаж – Стоимость проданных товаров

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

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

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

Стоимость проданных товаров Вычисляемое поле

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

Формула = «Сумма продаж» * 60%

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

Расчетное поле валовой прибыли

Это вычисляемое поле использует следующие поля в приведенной ниже формуле;

Формула = «Сумма продаж» — «Себестоимость проданных товаров»

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

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

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

1. Нажмите раскрывающийся список Имя , выберите Вычисляемое поле имя, которое вы хотите изменить
2. Изменить формулу
3. Нажмите кнопку Изменить
4. Нажмите кнопку OK

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

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

Вы можете удалить вычисляемое поле из сводной таблицы, выполнив следующие действия на  Вставить вычисляемое поле 9диалоговое окно 0006;

1. Нажмите раскрывающийся список  Имя , выберите имя вычисляемого поля, которое вы хотите удалить
2. Нажмите кнопку Удалить  
3. Нажмите OK кнопку

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

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