Сводные таблицы предоставляют широкие возможности обработки данных. О некоторых возможностях пользователи иногда даже не подозревают. 🙂 Рассмотрим следующий пример [1].
Исходные данные представляют собой отчет об отгрузках (в штуках) за первые пять месяцев 2011 г.:
Рис. 1. Исходные данные (источник). Отчет об отгрузках.
Скачать заметку в формате Word, примеры в формате Excel
При формировании сводной таблицы по умолчанию данные в поле значений обрабатывают данные источника следующим образом (рис. 2):
- для числовых значений используется функция СУММ;
- для текстовых – функция СЧЁТ.
Видно (см. рис. 2), что для поля исходных данных «Отгружено, шт.» в сводной таблице применяется суммирование, а для поля исходных данных «Месяц» – подсчет числа ячеек с данными. При этом если используется функция суммирования, можно создать настраиваемое вычисление. В настоящей статье речь именно об этих возможностях.
Рис. 2. Суммирование или подсчет количества ячеек в зависимости от типа исходных данных
Для настройки вычислений в области значений выделите поле, для которого требуется изменить итоговую функцию отчета сводной таблицы. На вкладке Параметры в группе Активное поле нажмите кнопку Параметры поля (или воспользуйтесь контекстным меню, которое вызывается правой кнопкой мыши). Перейдите на вкладку «Дополнительные вычисления».
Доступны следующие варианты настройки вычислений сводной таблицы:
Функция | Результат |
Нет | Выключение настраиваемого вычисления |
Отличие | Отображение значения в виде разницы по отношению к значению элемента в поле |
Доля | Отображение доли в процентах от значения элемента в поле |
Приведенное отличие | Отображение значения в виде разницы в процентах по отношению к значению элемента в поле |
С нарастающим итогом в поле | Отображение значений в виде нарастающего итога для последовательных элементов (Элемент. Подкатегория поля в отчетах сводной таблицы и сводной диаграммы. Например, поле «Месяц» будет включать такие элементы как «Январь», «Февраль» и т. п.) в поле |
Доля от суммы по строке | Отображение значений в каждой строке или категории в процентах от итогового значения по этой строке или категории |
Доля от суммы по столбцу | Отображение всех значений в каждом столбце или ряду в процентах от итогового значения по этому столбцу или ряду |
Доля от общей суммы | Отображение значений в процентах от общей суммы значений или элементов данных в отчете |
Индекс | Производит вычисления следующим образом: (Значение в ячейке x Общий итог) / (Итог строки x Итог столбца) |
- Отличие. Отображает значения в виде разницы по отношению к значению выбранного элемента в поле.
Компании часто сравнивают результаты своей деятельности за текущий месяц с результатами, полученными в предыдущем месяце.
Чтобы получить такой отчет, на вкладке Дополнительные вычисления выберите опцию Отличие. Поскольку нужно сравнить один месяц с другим, в списке Поле выберите Месяц. В области Элемент содержится несколько значений. Если вы хотите сравнить каждый месяц с предыдущим, выберите опцию (назад). Если вы хотите сравнить данные с каким-то конкретным месяцем, то выберете этот месяц из списка (рис. 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. Индекс.
Настраиваемое вычисление создают дополнительные возможности для представления данных непосредственно в самих сводных таблицах.