Вычисления в сводной таблице подчиняются правилам по умолчанию. При формировании сводной таблицы данные в поле значений обрабатывают данные источника следующим образом:
- для числовых значений используется функция СУММ;
- для текстовых – функция СЧЁТ.
Возьмем, например, исходные данные, представленные на рис. 1. Если включить в область значений сводной таблицы поля Заказчик и Доход, то по первому полю посчитается число заказчиков, а по второму – сумма дохода (рис. 2).
Рис. 1. Исходные данные, используемые во всех примерах заметки
Скачать заметку в формате Word или pdf, примеры в формате Excel
Рис. 2. В сводной таблице для числовых полей в области значений по умолчанию находится сумма, для остальных типов данных – количество
В Excel 2013 значительно расширены возможности вычислений в полях, относящихся к области значений. [1] Чтобы увидеть все доступные опции, откройте окно Параметры поля значений (рис. 3). Для этого, например, кликните правой кнопкой мыши на любой ячейки из области значений (на рис. 2 это – область В3:С8), и выберите пункт меню Параметры полей значений… [2] В Excel 2013 на вкладке Операция доступны 11 функций вычислений (на рис. 3а обведены 6 функций, видимых на экране), на вкладке Дополнительные вычисления – еще 15 (рис. 3б). Для сравнения, в Excel 2007 таких функций было только 8.
Рис. 3. Окно Параметры поля значений: (а) вкладка Операция, (б) вкладка Дополнительные вычисления
Основные функции
Проиллюстрируем, как работают 11 основных функций. Для этого создадим сводную таблицу, в которую 11 раз в область значений перетащим поле Доход, и последовательно настроим функции вычисления (рис. 4; я создал две таблицы, поскольку слишком широкое изображение будет плохо читаемым). Для настройки функции кликните на одной из ячеек настраиваемого столбца правой кнопкой мыши, выберите пункт меню Параметры полей значений… и отметьте соответствующую функцию на вкладке Операция. Перейдите в поле Пользовательское имя (см. рис. 3), и введите имя, соответствующее функции, например, Сумма, Среднее и др. На рис 4 видно, что в поле Произведение некоторые значения так велики, что даже превышают возможности Excel отражать такие числа.
Рис. 4. Основные функции вычислений в сводной таблице
Некоторые функции требуют пояснения. Смещенное отклонение – оно же среднеквадратичное отклонение выборки. Несмещенное отклонение – стандартное отклонение в генеральной совокупности. Аналогично и два вида дисперсии. Видно, что смещенные значения немного больше несмещенных. Более подробно о сути различий этих двух оценок см., например, Выборочная дисперсия.
Дополнительные вычисления
Дополнительные вычисления покрывают самые разнообразные требования, которые могут возникнуть при анализе данных. В зависимости от выбранной функции (область 1 на рис. 5) дополнительная настройка может не потребоваться (рис. 5а), или может потребоваться выбрать только базовое поле (рис. 5б) или, и базовое поле, и базовый элемент (рис. 5в).
Рис. 5. В зависимости от выбранной функции (область 1): (а) дополнительная настройка не требуется, (б) требуется выбор базового поля, (в) требуется выбор базового поля и базового элемента
Помните, что дополнительные вычисления накладываются на основные. Например, если в качестве основной функции выбрана Сумма (столбцы Cи Dна рис. 6), то дополнительное вычисление % от общей суммы покажет долю каждого элемента (в столбце D) от итоговой суммы 6 707 812. Если же качестве основной функции выбран Максимум, то дополнительное вычисление % от общей суммы покажет долю каждого элемента (в столбце F) от максимума 25 350.
Рис. 6. Совместное влияние на вычисления основной и дополнительной функций
Работа дополнительных функций «% от общей суммы», «% от суммы по столбцу» и «% от суммы по строке» показана на рис. 7. Эти функции не требуют дополнительных настроек.
Рис. 7. Дополнительные функции: (а) % от общей суммы, (б) % от суммы по столбцу, (в) % от суммы по строке
Несколько более сложной для понимания является дополнительная функция «доля». Чтобы создать сводную таблицу, изображенную на рис. 8:
- Дважды перетащите в область значений поле Доход
- Отсортируйте строки по полю Доход по убыванию
- Установите параметры поля значений для столбца С, как показано на рисунке.
Рис. 8. Дополнительная функция Доля
Видно, что значения в столбце Доля показывают процент от продаж в Нью-Йорке.
Начиная с версии 2010 в Excel появились дополнительные функции Сортировки. На рис. 9а показана сортировка от минимального значения (которому присваивается ранг 1) до максимального. На рис. 9б добавлено поле Регион, а сортировка осуществляется от максимального значения (ранг 1) к минимальному. Видно, что сортировка выполняется в каждом регионе отдельно.
Рис. 9. Дополнительная функция Сортировка: (а) сортировка от минимального к максимальному; (б) сортировка от максимальному к минимальному
Вычисление суммы с нарастающим итогом обычно выполняется для таблиц, в которых месяцы расположены по строкам (рис. 10а), либо, когда нужно показать, что первые Nклиентов дают N% дохода/прибыли (рис. 10б). Видно, что первые 9 клиентов обеспечивают 80% продаж.
Рис. 10. Дополнительная функция: (а) С нарастающим итогом в поле; (б) % от суммы с нарастающим итогом в поле
Дополнительная функция Приведенное отличие похожа на функцию Доля (см. рис. 8). Например, на рис. 9 в ячейке С4 функция Приведенное отличие показывает на сколько процентов доход в Детройте меньше, чем доход в Нью-Йорке.
Рис. 11. Дополнительная функция Приведенное отличие
Дополнительная функция «% от суммы по родительской строке» показывает долю текущего элемента в промежуточных итогах. Например, в ячейке Е13 (рис. 12) значение 2,75% показывает долю дохода в Чикаго ($ 184 425) от общего по стране ($6 707 812). В ячейке D9 значение 78,84% показывает долю Детройта ($1 372 957) от общего по региону Средний Запад ($1 741 424).
Рис. 12. Дополнительная функция % от суммы по родительской строке
Самой загадочной является дополнительная функция Индекс (рис. 13). Обратите внимание, индекс персиков в Джорджии 2,55, а в Калифорнии – 0,5. Если в следующем году урожай персиков пострадает, это нанесет сильный удар по фермерам Джорджии и лишь слегка затронет фермеров Калифорнии.
Рис. 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.
Добрый день.
Просьба подсказать, например, на листе «Рис. 5» в списке полей сводной таблицы есть поля Месяцы и Годы. При этом в, в источнике данных на листе «Рис. 1» такие столбцы отсутствуют. Помогите разобраться как это было сделано.
Владимир, см. заметку Группировка данных сводной таблицы в Excel 2013
Премного благодарен! Все стало понятно.
Добрый день. У Вас неправильная ссылка на фразе «Выборочная дисперсия» . Правильный адрес 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.
Спасибо, получилось.
Добрый день, спасибо еще раз за помощь! Могу ли я еще обратиться за помощью по моему файлу (! не могу найти формулу и считаю вручную!), возможно ли написать формулу, которая будет высчитывать первую сделку месяца: например, клиент первый раз грузился в апреле, в апреле формула проверяет, были ли ранее отгрузки по этому клиенту, если нет….=первая сделка. Конечная задача: вывести отчет по новым клиентам, которые впервые отгрузились (за период-месяц)
Заранее спасибо огромное!
Инна, если это допустимо, создайте два дополнительных столбца. В одном сцепите год, месяц, имя клиента, а во втором — примените формулу, как выше.
Добрый день. Можно ли из поля в сводной с мин значение вычесть именно мин значение а не сумму
Ксения, не понял вопрос(( Пришлите файл с подробным описанием задачи на s_bag@mail.ru
Добрый день! А если есть два столбца и надо межу ними абсолютное отклонение вывести, как это сделать?
Эл, если я правильно понял вопрос, вам нужно создать вычисляемое поле в сводной таблице. Почитайте Вычисляемые поля и вычисляемые элементы в Excel 2013.
Сергей Викторович, добрый день!
Помогите, пожалуйста, решить проблему в Excel.
Есть два листа: приход и расход, итоги которых формируются на отдельный лист в сводную таблицу. Я смог настроить так, чтобы при изменении даты, менялось сальдо по банковскому счету на каждый день. Но если выбрать в фильтре несколько дней или весь месяц, то остаток по банковскому на первый день выбранного периода у меня не получается вывести автоматически.
В идеале хотелось бы, чтобы остаток по счету считался автоматически исходя из прихода и расхода. А пользователь отчета мог бы просто выбирать в фильтре даты или определенный период и видеть остаток на первый день выбранного периода, обороты за период в разрезе статей затрат/доходов и контрагентов и остаток на конец заданного периода.
Рабочий файл выслал на Вашу почту s_bag@mail.ru.
Заранее благодарю Вас за помощь.