Вычисления в сводной таблице (в области значений) в Excel 2013

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

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

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

Возьмем, например, исходные данные, представленные на рис. 1. Если включить в область значений сводной таблицы поля Заказчик и Доход, то по первому полю посчитается число заказчиков, а по второму – сумма дохода (рис. 2).

Рис. 1. Исходные данные, используемые во всех примерах заметки

Рис. 1. Исходные данные, используемые во всех примерах заметки

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

Рис. 2. Числовые поля суммируются, для остальных типов данных ищется количество вхождений

Рис. 2. В сводной таблице для числовых полей в области значений по умолчанию находится сумма, для остальных типов данных – количество

В Excel 2013 значительно расширены возможности вычислений в полях, относящихся к области значений. [1] Чтобы увидеть все доступные опции, откройте окно Параметры поля значений (рис. 3). Для этого, например, кликните правой кнопкой мыши на любой ячейки из области значений (на рис. 2 это – область В3:С8), и выберите пункт меню Параметры полей значений… [2] В Excel 2013 на вкладке Операция доступны 11 функций вычислений (на рис. 3а обведены 6 функций, видимых на экране), на вкладке Дополнительные вычисления – еще 15 (рис. 3б). Для сравнения, в Excel 2007 таких функций было только 8.

Рис. 3. Окно Параметры поля значений

Рис. 3. Окно Параметры поля значений: (а) вкладка Операция, (б) вкладка Дополнительные вычисления

Основные функции

Проиллюстрируем, как работают 11 основных функций. Для этого создадим сводную таблицу, в которую 11 раз в область значений перетащим поле Доход, и последовательно настроим функции вычисления (рис. 4; я создал две таблицы, поскольку слишком широкое изображение будет плохо читаемым). Для настройки функции кликните на одной из ячеек настраиваемого столбца правой кнопкой мыши, выберите пункт меню Параметры полей значений… и отметьте соответствующую функцию на вкладке Операция. Перейдите в поле Пользовательское имя (см. рис. 3), и введите имя, соответствующее функции, например, Сумма, Среднее и др. На рис 4 видно, что в поле Произведение некоторые значения так велики, что даже превышают возможности Excel отражать такие числа.

Рис. 4. Основные функции вычислений в сводной таблице

Рис. 4. Основные функции вычислений в сводной таблице

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

Дополнительные вычисления

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

Рис. 5. Дополнительные настройки в зависимости от выбранной функции

Рис. 5. В зависимости от выбранной функции (область 1): (а) дополнительная настройка не требуется, (б) требуется выбор базового поля, (в) требуется выбор базового поля и базового элемента

Помните, что дополнительные вычисления накладываются на основные. Например, если в качестве основной функции выбрана Сумма (столбцы Cи Dна рис. 6), то дополнительное вычисление % от общей суммы покажет долю каждого элемента (в столбце D) от итоговой суммы 6 707 812. Если же качестве основной функции выбран Максимум, то дополнительное вычисление % от общей суммы покажет долю каждого элемента (в столбце F) от максимума 25 350.

Рис. 6. Совместное влияние на вычисления основной и дополнительной функций

Рис. 6. Совместное влияние на вычисления основной и дополнительной функций

Работа дополнительных функций «% от общей суммы», «% от суммы по столбцу» и «% от суммы по строке» показана на рис. 7. Эти функции не требуют дополнительных настроек.

Рис. 7. Некоторые дополнительные функции

Рис. 7. Дополнительные функции: (а) % от общей суммы, (б) % от суммы по столбцу, (в) % от суммы по строке

Несколько более сложной для понимания является дополнительная функция «доля». Чтобы создать сводную таблицу, изображенную на рис. 8:

  1. Дважды перетащите в область значений поле Доход
  2. Отсортируйте строки по полю Доход по убыванию
  3. Установите параметры поля значений для столбца С, как показано на рисунке.

Рис. 8. Дополнительная функция Доля

Рис. 8. Дополнительная функция Доля

Видно, что значения в столбце Доля показывают процент от продаж в Нью-Йорке.

Начиная с версии 2010 в Excel появились дополнительные функции Сортировки. На рис. 9а показана сортировка от минимального значения (которому присваивается ранг 1) до максимального. На рис. 9б добавлено поле Регион, а сортировка осуществляется от максимального значения (ранг 1) к минимальному. Видно, что сортировка выполняется в каждом регионе отдельно.

Рис. 9. Дополнительная функция Сортировка

Рис. 9. Дополнительная функция Сортировка: (а) сортировка от минимального к максимальному; (б) сортировка от максимальному к минимальному

Вычисление суммы с нарастающим итогом обычно выполняется для таблиц, в которых месяцы расположены по строкам (рис. 10а), либо, когда нужно показать, что первые Nклиентов дают N% дохода/прибыли (рис. 10б). Видно, что первые 9 клиентов обеспечивают 80% продаж.

Рис. 10. Дополнительная функция С нарастающим итогом в поле

Рис. 10. Дополнительная функция: (а) С нарастающим итогом в поле; (б) % от суммы с нарастающим итогом в поле

Дополнительная функция Приведенное отличие похожа на функцию Доля (см. рис. 8). Например, на рис. 9 в ячейке С4 функция Приведенное отличие показывает на сколько процентов доход в Детройте меньше, чем доход в Нью-Йорке.

Рис. 11. Дополнительная функция Приведенное отличие

Рис. 11. Дополнительная функция Приведенное отличие

Дополнительная функция «% от суммы по родительской строке» показывает долю текущего элемента в промежуточных итогах. Например, в ячейке Е13 (рис. 12) значение 2,75% показывает долю дохода в Чикаго ($ 184 425) от общего по стране ($6 707 812). В ячейке D9 значение 78,84% показывает долю Детройта ($1 372 957) от общего по региону Средний Запад ($1 741 424).

Рис. 12. Дополнительная функция процент от суммы по родительской строке

Рис. 12. Дополнительная функция % от суммы по родительской строке

Самой загадочной является дополнительная функция Индекс (рис. 13). Обратите внимание, индекс персиков в Джорджии 2,55, а в Калифорнии – 0,5. Если в следующем году урожай персиков пострадает, это нанесет сильный удар по фермерам Джорджии и лишь слегка затронет фермеров Калифорнии.

Рис. 13. Дополнительная функция Индекс

Рис. 13. Дополнительная функция Индекс

Чтобы понять, что в Excel подразумевается под индексом, проведите следующие вычисления. Сначала разделите продажи персиков в Джорджии (180) на продажи фруктов в Джорджии (210). Получите относительную стоимость персиков в Джорджии = 0,86. Далее разделите общие продажи персиков (285) на общие продажи всех фруктов (847). Получите Относительную долю продаж персиков = 0,34. Индекс равен отношению первого частного (0,86) ко второму (0,34). Индекс = 2,55.

Например, в Огайо индекс яблок = 4,91, поэтому производство яблок имеет первостепенную важность для этого штата.

[1] Опции доступные в Excel2007 я ранее описал в заметке Изменение настраиваемого вычисления для поля в отчете сводных таблиц

[2] Заметка написана на основе книги Билл Джелен, Майкл Александер. Сводные таблицы в Microsoft Excel 2013. Глава 3.

Комментарии: 12 комментариев

Добрый день.
Просьба подсказать, например, на листе «Рис. 5» в списке полей сводной таблицы есть поля Месяцы и Годы. При этом в, в источнике данных на листе «Рис. 1» такие столбцы отсутствуют. Помогите разобраться как это было сделано.

Премного благодарен! Все стало понятно.

Добрый день. У Вас неправильная ссылка на фразе «Выборочная дисперсия» . Правильный адрес http://statanaliz.info/metody/opisanie-dannyx/15-vyborochnaya-dispersiya

Дмитрий, спасибо. Поправил.

Добрый день, подскажите пжл, есть ли возможность в сводной таблице вывести сумму-счетчик (аналогично функции екселя), которая бы считала количество значений за период. например в сводной по месяцам есть отгрузки: в апреле и июне, т.е. 2 отгрузки. Как вывести такое суммирование?
Заранее благодарю

Инна, такой счетчик описан в самом начале заметки.

это не мой случай, при использовании описанной в начале функции, я получаю результат : количество выписанных позиций или количество отгруженных шт. прилагаю свою таблице, если есть возможность-подскажите пжл, не нашла ответ во всем инете.. https://cloud.mail.ru/public/6Wmx/YTkQmoMds

Инна, если я правильно понял, вам нужно посчитать количество реализаций по периодам. Можно, например, так: вставьте столбец между G и H, и воспользуйтесь формулой =ЕСЛИ(СЧЁТЕСЛИ(G$1:G2;G2)=1;1;0). Таким образом, 1 будет соответствовать уникальным реализациям. И вам останется в сводной таблице посчитать количество строк с фильтром 1.

Спасибо, получилось.

Добрый день, спасибо еще раз за помощь! Могу ли я еще обратиться за помощью по моему файлу (! не могу найти формулу и считаю вручную!), возможно ли написать формулу, которая будет высчитывать первую сделку месяца: например, клиент первый раз грузился в апреле, в апреле формула проверяет, были ли ранее отгрузки по этому клиенту, если нет….=первая сделка. Конечная задача: вывести отчет по новым клиентам, которые впервые отгрузились (за период-месяц)
Заранее спасибо огромное!

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


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