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

Рубрика: 7. Полезняшки Excel

Сводные таблицы предоставляют широкие возможности обработки данных. О некоторых возможностях пользователи иногда даже не подозревают. 🙂 Рассмотрим следующий пример [1].

Исходные данные представляют собой отчет об отгрузках (в штуках) за первые пять месяцев 2011 г.:

Рис. 1. Исходные данные (источник). Отчет об отгрузках.

Скачать заметку в формате Word, примеры в формате Excel

При формировании сводной таблицы по умолчанию данные в поле значений обрабатывают данные источника следующим образом (рис. 2):

  • для числовых значений используется функция СУММ;
  • для текстовых – функция СЧЁТ.

Видно (см. рис. 2), что для поля исходных данных «Отгружено, шт.» в сводной таблице применяется суммирование, а для поля исходных данных «Месяц» – подсчет числа ячеек с данными. При этом если используется функция суммирования, можно создать настраиваемое вычисление. В настоящей статье речь именно об этих возможностях.

Рис. 2. Суммирование или подсчет количества ячеек в зависимости от типа исходных данных

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

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

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

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

Чтобы получить такой отчет, на вкладке Дополнительные вычисления выберите опцию Отличие. Поскольку нужно сравнить один месяц с другим, в списке Поле выберите Месяц. В области Элемент содержится несколько значений. Если вы хотите сравнить каждый месяц с предыдущим, выберите опцию (назад). Если вы хотите сравнить данные с каким-то конкретным месяцем, то выберете этот месяц из списка (рис. 3). Видно, что в колонке Е показывается относительное изменение объема продаж в штуках… относительно предыдущего месяца.

Рис. 3. Отличие

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

Рис. 4. Доля

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

Рис. 5. Приведенное отличие

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

Как строить такие диаграммы можно почитать в заметке «Как добавить линию на график».

4. С нарастающим итогом в поле. Отображает значения в виде нарастающего итога для последовательных элементов. Может быть использовано для АВС-анализа (он же анализ Парето)

Рис. 6. С нарастающим итогом в поле.

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

Указывает процентное соотношение в каждой строке и отдельно для каждой строки. Этот тип отчетов часть применяется при анализе сезонности в деятельности компании. Видно (см. рис. 7), что по артикулу 1002 в январе было продано 11,84% от суммарных продаж артикула 1002 за пять месяцев.

Рис. 7. Доля от суммы по строке.

6. Доля от суммы по столбцу. Отображает значения в каждом столбце в процентах от итогового значения по этому столбцу.

Этот тип отчетов может применяться при анализе вклада отдельных направлений (артикулов) в деятельность компании. Видно (см. рис. 8), что наибольший вклад в 5-месячный объем продаж дает артикул 1027 – 4,92% (после установки «доля от суммы по столбцу» я отсортировал артикулы по убывающей, чтобы наиболее «жирные» артикулы расположились вверху сводной таблицы).

Рис. 8. Доля от суммы по столбцу.

7. Доля от общей суммы. Отображает значения в процентах  от общей суммы всех значений в отчете.

Для удобства восприятия (см. рис. 9) я ограничил число артикулов десятью самыми «жирными» – дающими наибольший вклад в 5-месячные продажи. Для этого я использовал фильтр по названию строк – «Первые 10…». Например, в феврале у артикула 1027 значение 4,66%. Что означает это число? Если сложить продажи за пять месяцев указанных 10 артикулов, то 4,66% – вклад в полученную сумму артикула 1027 в феврале.

Рис. 9. Доля от общей суммы

8. Индекс. Отображает относительную важность элемента.

Напомню, что значение индекса вычисляется по формуле:

(Значение в ячейке x Общий итог) / (Итог строки x Итог столбца)

К сожалению, физический смысл этой формуле мне не вполне понятен, поэтому в своей практике я ни разу не использовал такое представление данных… 🙂 В нашем примере (см. рис. 10) наибольший индекс получился у артикула 1073 в январе. Почему это поле в соответствии с индексом, получило столь выдающееся значение, мне не понятно…

Рис. 10. Индекс.

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


[1] Материал написан совместно с Ольгой Кошелевой


Прокомментировать