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

Итоговые данные сводной таблицы и отчета сводной диаграммы

Исходные данные

Значения в области данных соответствуют исходным данным в отчете.

Отчет сводной таблицы состоит из вышеприведенных исходных данных

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

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

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

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

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

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

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

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

Исходные данные OLAP. Для отчетов на основе данных OLAP кубы, сводные значения перед отображениям на экране Microsoft Excel предварительно просчитываются на сервере OLAP. Следовательно изменить метод расчетов невозможно. Невозможно также изменить статистические функции используемые для расчетов области данных, добавить вычисляемые поля или вычисляемые элементы. Если сервер OLAP использует вычисляемые поля, они отобразятся на листе сводной таблицы. Также на экране отобразятся вычисляемые поля и элементы, созданные макросами, написанными в языке Visual Basic для приложений и сохраненными в рабочей книге. Эти поля и элементы изменить будет нельзя. Если требуются дополнительные средства вычислений, свяжитесь с администратором базы данных OLAP.

Другие виды исходных данных. Для отчетов, основанных на внешних данных или данных рабочего листа, Microsoft Excel использует функции: СУММ (для расчета полей, содержащих числовые данные) и СЧЕТ (для расчета полей, содержащих текст). Для дальнейшего анализа и настройки данных можно использовать различные функции, такие как СРЗНАЧ, МАКС или МИН . Можно создавать собственные формулы, использующие элементы отчета или другие рабочие листы. Для этого создайте внутри поля вычисляемые поля или элементы.

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

Синтаксис формул

Формулы нельзя создавать в отчетах основанных исходных данных OLAP.

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

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

Имена в отчетах. Microsoft Excel использует имена для поиска элементов отчета в формулах. Имена составляются из имен поля и элемента. В следующем примере данные в диапазоне C3:C9 названы «Молоко».

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

Примеры. Вычисляемое поле с именем «Прогноз» позволяет спрогнозировать будущие заказы с помощью формулы наподобие следующей:

=Продажи * 1.2

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

=Молоко * 115%

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

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

Итоги. В формулах нельзя ссылаться на итоги (например, «Итоги марта», «Итоги апреля» и «Общие итоги» ).

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

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

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

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

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

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

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

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

=Продажи * 110%

Результат будет отображен на диаграмме следующим образом:

Чтобы увидеть отдельный маркер данных для продаж в северном регионе за вычетом транспортировки, составляющей 8 процентов, можно создать вычисляемый элемент в поле «Регион» с помощь следующей формулы:

=Север – (Север * 8%)

Результат будет выглядеть так:

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

Отображение или скрытие итогов в отчете сводной таблицы

Выполните одно или несколько следующих действий.

Отобразите итоги для отдельных полей

Дважды щелкните мышью поле.
Выполните одно из следующих действий.

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

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

Удалите промежуточные итоги.   Нажмите кнопку Нет в окне Итоги.

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

Совет

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

Отобразите конечные итоги для отчета целиком

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

Отобразите конечные итоги.   Установите один или оба флажка: общая сумма по столбцам и общая сумма по строкам.

Скройте конечные итоги.   Снимите один или оба флажка.

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

Подсчитайте итоги со скрытыми элементами или без них

Щелкните отчет.
На панели инструментов Сводные таблицы нажмите кнопку Учитывать скрытые элементы в итогах .

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

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

Изменение способа подведения итогов в сводной таблице или сводной диаграмме

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

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

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

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

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

Создание собственной формулы в отчете сводной таблице или сводной диаграммы

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

Решите, что требуется создать: вычисляемое поле или вычисляемый объект в поле.

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

Добавьте вычисляемое поле
1. Щелкните отчет.
2. На панели инструментов Сводные таблицы нажмите кнопку Сводная таблица или Сводная диаграмма, далее выберите команду Формулы, а затем команду Вычисляемое поле.
3. В поле Имя введите имя данного поля.
4. Введите формулу в поле Формула.
  
  Чтобы использовать в формуле данные из другого поля, выберите это поле в списке Поля, а затем нажмите кнопку Добавить поле. Например, чтобы вычислить 15% отчислений для каждого значения в поле «Продажи», введите =Продажи*15%.
5. Нажмите кнопку Добавить, а затем — кнопку OK.
Добавьте вычисляемый объект в поле
1. Если объекты в поле сгруппированы, щелкните правой кнопкой мыши каждую группу, укажите в контекстном меню на пункт Группа и структура, а затем выберите команду Разгруппировать.
2. Выберите поле, в которое требуется добавить вычисляемый объект.
3. На панели инструментов Сводные таблицы нажмите кнопку Сводная таблица или Сводная диаграмма, далее выберите команду Формулы, а затем команду Вычисляемый объект.
4. Введите имя вычисляемого элемента в поле с раскрывающимся списком Имя.
5. Введите формулу для объекта в поле Формула.
  
  Чтобы использовать в формуле данные объекта поля, выберите этот объект в списке Элементы, а затем нажмите кнопку Добавить элемент.
6. Нажмите кнопку Добавить, а затем — кнопку OK.
7. Если на шаге 1 объекты были разгруппированы, теперь при необходимости можно сгруппировать их.
Для вычисляемых объектов можно вводить различные формулы в ячейки по очереди.

Инструкции.
Например, если для всех месяцев в вычисляемом объекте «Клубника» содержится формула «Апельсины * 0,25», можно заменить ее формулой «Апельсины *0,5» для июня, июля и августа.
1. Выберите ячейку, в которой требуется изменить формулу.
  
  Чтобы изменить формулу в нескольких ячейках, нажмите клавишу CTRL и, удерживая ее нажатой, выделите остальные ячейки.
2. Измените формулу в строке формул.
При наличии в формуле нескольких вычисляемых объектов можно изменить порядок их вычисления.

Инструкции.
1. Щелкните отчет.
2. На панели инструментов Сводные таблицы выберите Сводная таблица или Сводная диаграмма, далее выберите Формулы, и затем — команду Порядок вычислений.
3. Выберите формулу и нажмите кнопку Вверх или Вниз.
4. Продолжайте выполнять предыдущий шаг, пока выбранная формула не окажется на нужном месте.

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

Совет

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

Изменение формулы сводной таблицы или сводной диаграммы

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

Определите, где находится формула: в вычисляемом поле или в вычисляемом объекте. Если формула находится в вычисляемом объекте, определите, является ли она единственной для этого объекта.

Инструкции
1. Щелкните отчет.
2. На панели инструментов Сводные таблицы нажмите кнопку Сводная таблица, укажите на пункт Формулы, а затем выберите команду вывести формулы.
3. В разделе «Вычисляемое поле» или «Вычисляемый объект» списка формул найдите формулу, которую требуется изменить.
  
  Если вычисляемому объекту соответствует несколько формул, формула, введенная при создании объекта, называется также, как и вычисляемый объект в столбце B. Для дополнительных формул для этого объекта в столбце B содержатся имя вычисляемого объекта и имена связанных объектов.
  
  Предположим, имеется формула для вычисляемого объекта Товар, и другая формула для этого объекта Товар Январь Продажи. В отчете сводной таблицы эта формула находится в ячейке «Продажи» в строке «Товар» и столбце «Январь».
Выполните одно из следующих действий.

Измените формулу в вычисляемом поле
1. Щелкните отчет.
2. На панели инструментов Сводные таблицы нажмите кнопку Сводная таблица или Сводная диаграмма, далее выберите команду Формулы, а затем команду Вычисляемое поле.
3. Выберите вычисляемое поле, для которого необходимо изменить формулу, в поле с раскрывающимся списком Имя.
4. Измените формулу в поле Формула.
5. Нажмите кнопку Изменить, а затем — кнопку OK.
Измените отдельную формулу вычисляемого объекта
1. Выделите поле, содержащее вычисляемый объект.
2. На панели инструментов Сводные таблицы нажмите кнопку Сводная таблица или Сводная диаграмма, далее выберите команду Формулы, а затем команду Вычисляемый объект.
3. Выберите вычисляемый объект в поле с раскрывающимся списком Имя.
4. Измените формулу в поле Формула.
5. Нажмите кнопку Изменить, а затем — кнопку OK.
Измените отдельную формулу для определенных ячеек вычисляемого объекта
Например, если для всех месяцев в вычисляемом объекте «Клубника» содержится формула «Апельсины * 0,25», можно заменить ее формулой «Апельсины *0,5» для июня, июля и августа.
1. Выберите ячейку, в которой требуется изменить формулу.
  
  Чтобы изменить формулу в нескольких ячейках, нажмите клавишу CTRL и, удерживая ее нажатой, выделите остальные ячейки.
2. Измените формулу в строке формул.
При наличии в формуле нескольких вычисляемых объектов можно изменить порядок их вычисления.

Инструкции.
1. Щелкните отчет.
2. На панели инструментов Сводные таблицы выберите Сводная таблица или Сводная диаграмма, далее выберите Формулы, и затем — команду Порядок вычислений.
3. Выберите формулу и нажмите кнопку Вверх или Вниз.
4. Продолжайте выполнять предыдущий шаг, пока выбранная формула не окажется на нужном месте.

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

Удаление формулы сводной таблицы или сводной диаграммы

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

Определите, где находится формула: в вычисляемом поле или в вычисляемом объекте.

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

Удалите вычисляемое поле
1. Щелкните отчет.
2. На панели инструментов Сводные таблицы нажмите кнопку Сводная таблица или Сводная диаграмма, далее выберите команду Формулы, а затем команду Вычисляемое поле.
3. Выберите удаляемое поле в поле с раскрывающемся списком Имя.
4. Нажмите кнопку Удалить.
Удалите вычисляемый объект
1. Выберите объект, который требуется удалить.
2. На панели инструментов Сводные таблицы нажмите кнопку Сводная таблица или Сводная диаграмма, далее выберите команду Формулы, а затем команду Вычисляемый объект.
3. Выберите в поле с раскрывающемся списком Имя объект, который требуется удалить.
4. Нажмите кнопку Удалить.

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

Совет

Если формулу не требуется удалять окончательно, можно скрыть поле или объект. Чтобы скрыть поле, перетащите его с отчета либо откройте поле со списком Данные и снимите его флажок; это поле останется доступным в списке полей. Чтобы скрыть объект, откройте его поле со списком и снимите соответствующий этому объекту флажок.

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

В отчете сводной таблицы найдите ячейку, которую требуется использовать в формуле.
В ячейке формулы введите формулу до места, куда требуется включить данные из отчета.
Щелкните ячейку в отчете. В формулу будет добавлена функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ, получающая данные из отчета. Эта функция будет получать правильные данные и в случае изменения макета отчета.
Завершите ввод формулы и нажмите клавишу ENTER.

Примечание. Если удалить из отчета одно из полей, на которые ссылается формула ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ, формула вернет результат #ССЫЛКА!.

Дополнительные вычисления в полях данных сводной таблицы и сводной диаграммы

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

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

Итоговые функции для анализа данных

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

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