Меню

Как работает стандартное отклонение в Excel



Дисперсия, среднеквадратичное (стандартное) отклонение, коэффициент вариации в Excel

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

Дисперсия

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

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

Формула дисперсии в теории вероятностей имеет вид:

Формула дисперсии в теории вероятностей

То есть дисперсия — это математическое ожидание отклонений от математического ожидания.

На практике при анализе выборок математическое ожидание, как правило, не известно. Поэтому вместо него используют оценку – среднее арифметическое. Расчет дисперсии производят по формуле:

Дисперсия во выборке

s 2 – выборочная дисперсия, рассчитанная по данным наблюдений,

X – отдельные значения,

– среднее арифметическое по выборке.

Стоит отметить, что у такого расчета дисперсии есть недостаток – она получается смещенной, т.е. ее математическое ожидание не равно истинному значению дисперсии. Подробней об этом здесь. Однако при увеличении объема выборки она все-таки приближается к своему теоретическому аналогу, т.е. является асимптотически не смещенной.

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

Расчет дисперсии в Excel

Генеральную и выборочную дисперсии легко рассчитать в Excel. Есть специальные функции: ДИСП.Г и ДИСП.В соответственно.

Функции Excel для расчета дисперсии

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

Свойства дисперсии

Свойство 1. Дисперсия постоянной величины A равна (нулю).

Свойство 2. Если случайную величину умножить на постоянную А, то дисперсия этой случайной величины увеличится в А 2 раз. Другими словами, постоянный множитель можно вынести за знак дисперсии, возведя его в квадрат.

Свойство 3. Если к случайной величине добавить (или отнять) постоянную А, то дисперсия останется неизменной.

Свойство 4. Если случайные величины X и Y независимы, то дисперсия их суммы равна сумме их дисперсий.

Свойство 5. Если случайные величины X и Y независимы, то дисперсия их разницы также равна сумме дисперсий.

Среднеквадратичное (стандартное) отклонение

Если из дисперсии извлечь квадратный корень, получится среднеквадратичное (стандартное) отклонение (сокращенно СКО). Встречается название среднее квадратичное отклонение и сигма (от названия греческой буквы). Общая формула стандартного отклонения в математике следующая:

Среднеквадратичное отклонение

На практике формула стандартного отклонения следующая:

Среднеквадратичное отклонение по генеральной совокупности

Как и с дисперсией, есть и немного другой вариант расчета. Но с ростом выборки разница исчезает.

Расчет cреднеквадратичного (стандартного) отклонения в Excel

Для расчета стандартного отклонения достаточно из дисперсии извлечь квадратный корень. Но в Excel есть и готовые функции: СТАНДОТКЛОН.Г и СТАНДОТКЛОН.В (по генеральной и выборочной совокупности соответственно).

Среднеквадратичное (стандартное) отклонение в Excel

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

Коэффициент вариации

Значение стандартного отклонения зависит от масштаба самих данных, что не позволяет сравнивать вариабельность разных выборках. Чтобы устранить влияние масштаба, необходимо рассчитать коэффициент вариации по формуле:

Формула коэффициента вариации

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

Расчет коэффициента вариации в Excel

Расчет коэффициента вариации в Excel также производится делением стандартного отклонения на среднее арифметическое:

Коэффициент вариации обычно выражается в процентах, поэтому ячейке с формулой можно присвоить процентный формат:

Процентный формат

Коэффициент осцилляции

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

Коэффициент осцилляции в Excel

Коэффициент осцилляции показывает степень размаха вариации относительно средней, что также можно использовать для сравнения различных наборов данных.

Таким образом, в статистическом анализе существует система показателей, отражающих разброс или однородность данных.

Ниже видео о том, как посчитать коэффициент вариации, дисперсию, стандартное (среднеквадратичное) отклонение и другие показатели вариации в Excel.

Источник

Вычисление значений в сводной таблице

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

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

Доступные методы вычислений

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

Функции сведения в полях значений. В сводной таблице в области значений отображаются сводные данные, вычисленные на основе исходных данных. Рассмотрим пример с такими исходными данными:

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

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

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

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

Читайте также:  Отличие киловатт от киловатт∙час

Сумма значений. Функция по умолчанию для числовых данных.

Число значений. Действует аналогично функции СЧЁТЗ. Функция по умолчанию для данных, отличных от числовых.

Количество числовых значений. Действует аналогично функции СЧЁТ.

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

Стандартное отклонение генеральной совокупности, которая содержит все сводимые данные.

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

Дисперсия генеральной совокупности, которая содержит все сводимые данные.

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

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

Значение, введенное в данное поле.

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

% от суммы по столбцу

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

% от суммы по строке

Значение в каждой строке или категории в процентах от итогового значения по этой строке или категории.

Значения в процентах от значения базового элемента в соответствующем базовом поле.

% от суммы по родительской строке

Рассчитывает значения следующим образом:

(значение элемента) / (значение родительского элемента по строкам).

% от суммы по родительскому столбцу

Рассчитывает значения следующим образом:

(значение элемента) / (значение родительского элемента по столбцам).

% от родительской суммы

Рассчитывает значения следующим образом:

(значение элемента) / (значение родительского элемента в выбранном базовом поле).

Значения в виде разности по отношению к значению базового элемента в соответствующем базовом поле.

Значения в виде разности в процентах по отношению к значению базового элемента в соответствующем базовом поле.

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

Значение в виде нарастающего итога для последовательных элементов в базовом поле.

% от суммы с нарастающим итогом в поле

Значение в виде нарастающего итога в процентах для последовательных элементов в базовом поле.

Сортировка от минимального к максимальному

Ранг выбранных значений в определенном поле с учетом того, что наименьшему из них присваивается значение 1, а остальным — значения более высокого ранга соответственно.

Сортировка от максимального к минимальному

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

Рассчитывает значения следующим образом:

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

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

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

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

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

Кроме того, если сервер OLAP предоставляет вычисляемые поля, называемые «вычисляемыми элементами», вы увидите их в списке полей сводной таблицы. Вы также увидите все вычисляемые поля и вычисляемые объекты, созданные с помощью макросов, которые написаны на языке Visual Basic для приложений (VBA) и хранятся в книге, но не сможете их изменить. Если вам нужны дополнительные типы вычислений, обратитесь к администратору базы данных OLAP.

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

Вычисления на основе исходных данных не из базы данных OLAP. В сводных таблицах, основанных на внешних данных других типов или на данных листа Excel, для вычисления полей значений, содержащих числовые данные, используется функция «Сумма», а для вычисления полей данных, содержащих текст, — функция «Количество». Для дальнейшего анализа и обработки своих данных вы можете выбрать другие функции сведения, например «Среднее», «Максимум» или «Минимум». Кроме того, можно создавать собственные формулы, в которых используются элементы отчета или другие данные листа. Для этого нужно создать вычисляемое поле или вычисляемый объект в поле.

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

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

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

Имена полей и элементов. В Excel имена полей и элементов используются для идентификации этих элементов отчета в формулах. В приведенном ниже примере для данных в диапазоне C3:C9 используется имя поля Молоко. Для вычисляемого объекта в поле Тип, оценивающего объем продаж нового продукта на основе данных о продажах молочных продуктов, можно использовать формулу =Молоко * 115%.

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

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

Формулы для вычисляемых объектов оперируют отдельными записями. Например, формула вычисляемого объекта =Молоко * 115% умножает каждое отдельное значение продаж молочных продуктов на 115 %, после чего полученные величины суммируются в области «Значения».

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

Итоги. Формулы не могут ссылаться на итоговые значения (в примере выше — это Сумма за март, Сумма за апрель и Общий итог).

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

Читайте также:  Как самостоятельно определить размер груди

Ссылки на элементы по позиции. Вы можете сослаться на элемент, указав его позицию в отчете (с учетом того, какие элементы фактически отображаются и как они отсортированы в настоящий момент). Тип[1] — это Молоко, а Тип[2] — Морепродукты. Когда позиции элементов изменятся, например, если какие-то из них будут скрыты или снова отображены, такая ссылка, возможно, будет указывать на другой элемент. Скрытые элементы не учитываются в этом индексе.

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

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

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

Например, на этой сводной диаграмме представлены данные о продажах для каждого продавца по регионам:

Чтобы посмотреть, как будут выглядеть объемы продаж, если увеличатся на 10 %, можно создать вычисляемое поле в связанной сводной таблице и воспользоваться формулой =Продажи * 110%.

Результат сразу отображается на сводной диаграмме, как показано на этом рисунке:

Чтобы отобразить отдельный маркер данных для продаж в регионе «Север» за вычетом транспортных расходов, которые составляют 8 %, можно создать в поле «Регион» вычисляемый объект с такой формулой: =Север – (Север * 8%).

Диаграмма будет выглядеть следующим образом:

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

Источник

Как работает стандартное отклонение в Excel

Function STANDOTKLON 1 Как работает стандартное отклонение в Excel

Добрый день!

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

В Excel используются несколько вариантов этой функции отклонения:

Function STANDOTKLON 1 1 Как работает стандартное отклонение в Excel

  • Функция СТАНДОТКЛОНА – вычисляется отклонение по выборке текстовых и логических значений. При этом ложные логические и текстовые значения формула приравнивает к 0, а 1 будут равняться только истинные логические значения;
  • Функция СТАНДОТКЛОН.В – производит оценку стандартного отклонения по выборке, при этом текстовые и логические значения игнорирует;
  • Функция СТАНДОТКЛОН.Г – делает оценку отклонения по некой генеральной совокупности и как в предыдущей функции игнорируются текстовые и логические значения;
  • Функция СТАНДОТКЛОНПА – также вычисляет по генеральной совокупности стандартное отклонение, но с учетом текстовых и логических значений. Равняться 1 будут только истинные логические значения, а ложные логические и текстовые значения будут приравнены к 0.

Математическая теория

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

Что же собственно делает стандартное отклонение? Оно производит оценку среднеквадратического отклонения случайной величины Х относительно её математического ожидания на основе несмещённой оценки её дисперсии. Согласитесь, звучит запутанно, но я думаю учащиеся поймут о чём собственно идет речь!

Function STANDOTKLON 2 Как работает стандартное отклонение в Excel

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

Function STANDOTKLON 3 Как работает стандартное отклонение в Excel

Теперь можно дать определение и стандартному отклонению – это анализ среднеквадратического отклонения случайной величины Х сравнительно её математической перспективы на основе несмещённой оценки её дисперсии. Формула записывается так: Отмечу, что все две оценки предоставляются смещёнными. При общих случаях построить несмещённую оценку не является возможным. Но оценка на основе оценки несмещённой дисперсии будет состоятельной.

Практическое воплощение в Excel

Ну а теперь отойдём от скучной теории и на практике посмотрим, как работает функция СТАНДОТКЛОН. Я не буду рассматривать все вариации функции стандартного отклонения в Excel, достаточно и одной, но в примерах. А для примера рассмотрим, как определяется статистика стабильности продаж.

Для начала посмотрите на орфографию функции, а она как вы видите, очень проста:

Function STANDOTKLON 4 Как работает стандартное отклонение в Excel

  • Число1, число2, … — являют собой генеральную совокупность значений и имеют только числовые значения или же ссылки на них. Формула поддерживает до 255 числовых значений.

Function STANDOTKLON 5 Как работает стандартное отклонение в Excel

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

Для начала нам необходимо посчитать среднее значение по месяцам. Будем использовать для этого функцию СРЗНАЧ и получится формула: =СРЗНАЧ(C4:E4). Function STANDOTKLON 6 Как работает стандартное отклонение в Excel Теперь собственно мы и можем найти стандартное отклонение с помощью функции СТАНДОТКЛОН.Г в значении которой нужно проставить продажи товара каждого периода. Получится формула следующего вида: =СТАНДОТКЛОН.Г(C4;D4;E4). Function STANDOTKLON 7 Как работает стандартное отклонение в Excel Ну вот и сделана половина дел. Следующим шагом мы формируем «Вариацию», это получается делением на среднее значение, стандартного отклонения и результат переводим в проценты. Получаем такую таблицу: Function STANDOTKLON 8 Как работает стандартное отклонение в Excel Ну вот основные расчёты окончены, осталось разобраться как идут продажи стабильно или нет. Возьмем как условие что отклонения в 10% это считается стабильно, от 10 до 25% это небольшие отклонения, а вот всё что выше 25% это уже не стабильно. Для получения результата по условиям воспользуемся логической функцией ЕСЛИ и для получения результата напишем формулу:

=ЕСЛИ(H4 Использование VBA для функции СТАНДОТКЛОН.Г

Читайте также:  Футбол Беларусь Высшая Лига 2021

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

Источник

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

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

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

Возьмем, например, исходные данные, представленные на рис. 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, поэтому производство яблок имеет первостепенную важность для этого штата.

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

16 комментариев для “Вычисления в сводной таблице (в области значений) в Excel 2013”

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

Источник