Как посчитать вариацию в excel. Прогнозируем с Excel: как посчитать коэффициент вариации

Функция СТАНДОТКЛОН.В возвращает значение стандартного отклонения, рассчитанного для определенного диапазона числовых значений.

Функция СТАНДОТКЛ.Г используется для определения стандартного отклонения генеральной совокупности числовых значений и возвращает величину стандартного отклонения с учетом, что переданные значения являются всей генеральной совокупностью, а не выборкой.

Функция СТАНДОТКЛОНА возвращает значение стандартного отклонения для некоторого диапазона чисел, которые являются выборкой, а не всей генеральной совокупностью.

Функция СТАНДОТЛОНПА возвращает значение стандартного отклонения для всей генеральной совокупности, переданной в качестве ее аргументов.

Примеры использования СТАНДОТКЛОН.В, СТАНДОТКЛОН.Г, СТАНДОТКЛОНА и СТАНДОТКЛОНПА

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

Таблица исходных данных:

Вначале рассчитаем среднее количество клиентов, с которыми работали менеджеры ежедневно:

СРЗНАЧ(B2:B11)

Данная функция выполняет расчет среднего арифметического значения для диапазона B2:B11, содержащего данные о количестве клиентов, принимаемых ежедневно первым менеджером. Аналогично рассчитаем среднее количество клиентов за день у второго менеджера. Получим:

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

СТАНДОТКЛОН.В(B2:B11)

B2:B11 – диапазон исследуемых значений. Аналогично определим стандартное отклонение для второго менеджера и получим следующие результаты:

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



Пример использования функции СТАНДОТКЛОНА в Excel

Пример 2. В двух различных группах студентов колледжа проводился экзамен по одной и той же дисциплине. Оценить успеваемость студентов.

Таблица исходных данных:

Определим стандартное отклонение значений для первой группы по формуле:

СТАНДОТКЛОНА(A2:A11)

Аналогичный расчет произведем для второй группы. В результате получим:

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

Пример функции СТАНДОТКЛОН.Г в Excel

Пример 3. Определить эффективность подготовки студентов к экзамену для всех групп университета.

Примечание: в отличие от предыдущего примера, будет анализироваться не выборка (несколько групп), а все число студентов – генеральная совокупность. Студенты, не сдавшие экзамен, не учтены.

Заполним таблицу данных:

Для оценки эффективности будем оперировать двумя показателями: средняя оценка и разброс значений. Для определения среднего арифметического используем функцию:

СРЗНАЧ(B2:B21)

Для определения отклонения введем формулу:

СТАНДОТКЛОН.Г(B2:B21)

В результате получим:

Полученные данные свидетельствует об успеваемости немного ниже среднего (<4), величина разброса характеризует довольно большое количество студентов, получивших 5 и 3 соответственно (учитывая, что анализировались только данные из диапазона от 3 до 5).

Пример функции СТАНДОТКЛОНПА в Excel

Пример 4. Проанализировать успеваемость студентов по результатам сдачи экзамена с учетом тех студентов, которым не удалось сдать этот экзамен.

Таблица данных:

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

СТАНДОТКЛОНПА(B2:B21)

В результате получим:

Высокий разброс значений в последовательности свидетельствует о большом числе не сдавших экзамен студентов.

Особенности использования СТАНДОТКЛОН.В, СТАНДОТКЛОН.Г, СТАНДОТКЛОНА и СТАНДОТКЛОНПА

Функции СТАНДОТКЛОНА И СТАНДОТКЛОНПА имеют идентичную синтаксическую запись типа:

ФУНКЦИЯ (значение1; [значение2];…)

Описание:

• ФУНКЦИЯ – одна из двух рассмотренных выше функций;
• значение1 – обязательный аргумент, характеризующий одно из значений выборки (либо генеральной совокупности);
• [значени2] – необязательный аргумент, характеризующий второе значение исследуемого диапазона.

Примечания:

1. В качестве аргументов функций могут быть переданы имена, числовые значения, массивы, ссылки на диапазоны числовых данных, логические значения и ссылки на них.
2. Обе функции игнорируют пустые значения и текстовые данные, содержащиеся в диапазоне переданных данных.
3. Функции возвращают код ошибки #ЗНАЧ!, если в качестве аргументов были переданы значения ошибок или текстовые данные, которые не могут быть преобразованы в числовые значения.

Функции СТАНДОТКЛОН.В и СТАНДОТКЛОН.Г имеют следующую синтаксическую запись:

ФУНКЦИЯ(число1;[число2];…)

Описание:

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

Примечание: обе функции не включают в процесс вычисления числа, представленные в виде текстовых данных, а также логические значения ИСТИНА и ЛОЖЬ.

Примечания:

1. Стандартное отклонение широко используется в статистических расчетах, когда нахождение среднего значения диапазона величин не дает верное представление о распределении данных. Оно демонстрирует принцип распределения величин относительно среднего значения в конкретной выборке или всей последовательности целиком. В Примере 1 будет наглядно рассмотрено практическое применение данного статистического параметра.
2. Функции СТАНДОТКЛОНА и СТАНДОТКЛОН.В следует использовать для анализа только части генеральной совокупности и производят расчет по первой формуле, а СТАНДОТКЛОН.Г и СТАНДОТКЛОНПА должны принимать на вход данные о всей генеральной совокупности и производят расчет по второй формуле.
3. В Excel содержатся встроенные функции СТАНДОТКЛОН и СТАНДОТКЛОНП, оставленные для совместимости с более старыми версиями Microsoft Office. Они могут быть не включены в более поздние версии программы, поэтому их использование не рекомендуется.
4. Для нахождения стандартного отклонения используются две распространенные формулы: S=√((∑_(i=1)^n▒(x_i-x_ср)^2)/(n-1)) и S=√((∑_(i=1)^n▒(x_i-x_ср)^2)/n), где:

• S – искомое значение стандартного отклонения;
• n – рассматриваемый диапазон значений (выборка);
• x_i – отдельно взятое значение из выборки;
• x_ср – среднее арифметическое значение для рассматриваемого диапазона.

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

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

Формула для расчета довольно проста. Чтобы найти среднеквадратическое отклонение, нужно взять квадратный корень из дисперсии. Так что теперь вы должны спросить: “А что же такое дисперсия?”

Что такое дисперсия

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

Чтобы найти дисперсию последовательно проведите следующие вычисления:

• Определите среднее (простое среднее арифметическое ряда значений).
• Затем от каждого из значений отнимите среднее и возведите полученную разность в квадрат (получили квадрат разности ).
• Следующим шагом будет вычисление среднего арифметического полученных квадратов разностей (Почему именно квадратов вы сможете узнать ниже).

Рассмотрим на примере. Допустим, вы с друзьями решили измерить рост ваших собак (в миллиметрах). В результате измерений вы получили следующие данные измерений роста (в холке): 600 мм, 470 мм, 170 мм, 430 мм и 300 мм.

Вычислим среднее значение, дисперсию и среднеквадратическое отклонение.

Сперва найдём среднее значение . Как вы уже знаете, для этого нужно сложить все измеренные значения и поделить на количество измерений. Ход вычислений:

Среднее мм.

Итак, среднее (среднеарифметическое) составляет 394 мм.

Теперь нужно определить отклонение роста каждой из собак от среднего :

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

Дисперсия мм 2 .

Таким образом, дисперсия составляет 21704 мм 2 .

Как найти среднеквадратическое отклонение

Так как же теперь вычислить среднеквадратическое отклонение, зная дисперсию? Как мы помним, взять из нее квадратный корень. То есть среднеквадратическое отклонение равно:

Мм (округлено до ближайшего целого значения в мм).

Применив данный метод, мы выяснили, что некоторые собаки (например, ротвейлеры) – очень большие собаки. Но есть и очень маленькие собаки (например, таксы, только говорить им этого не стоит).

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

То есть с помощью среднеквадратического отклонения мы получаем “стандартный” метод, который позволяет узнать, какое из значений является нормальным (среднестатистическим), а какое экстраординарно большим или, наоборот, малым.

Что такое стандартное отклонение

Но… все будет немного иначе, если мы будем анализировать выборку данных. В нашем примере мы рассматривали генеральную совокупность. То есть наши 5 собак были единственными в мире собаками, которые нас интересовали.

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

Если есть значений, то:

Все остальные расчеты производятся аналогично, в том числе и определение среднего.

Например, если наших пять собак – только выборка из генеральной совокупности собак (всех собак на планете), мы должны делить на 4, а не на 5, а именно:

Дисперсия выборки = мм 2 .

При этом стандартное отклонение по выборке равно мм (округлено до ближайшего целого значения).

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

Примечание. Почему именно квадраты разностей?

Но почему при вычислении дисперсии мы берём именно квадраты разностей? Допустим при измерении какого-то параметра, вы получили следующий набор значений: 4; 4; -4; -4. Если мы просто сложим абсолютные отклонения от среднего (разности) между собой … отрицательные значения взаимно уничтожатся с положительными:

.

Получается, этот вариант бесполезен. Тогда, может, стоит попробовать абсолютные значения отклонений (то есть модули этих значений)?

На первый взгляд получается неплохо (полученная величина, кстати, называется средним абсолютным отклонением), но не во всех случаях. Попробуем другой пример. Пусть в результате измерения получился следующий набор значений: 7; 1; -6; -2. Тогда среднее абсолютное отклонение равно:

Вот это да! Снова получили результат 4, хотя разности имеют гораздо больший разброс.

А теперь посмотрим, что получится, если возвести разности в квадрат (и взять потом квадратный корень из их суммы).

Для первого примера получится:

.

Для второго примера получится:

Теперь – совсем другое дело! Среднеквадратическое отклонение получается тем большим, чем больший разброс имеют разности … к чему мы и стремились.

Фактически в данном методе использована та же идея, что и при вычислении расстояния между точками, только примененная иным способом.

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

О том, как найти среднеквадратическое отклонение, вам рассказал , Сергей Валерьевич

Стандартное отклонение - классический индикатор изменчивости из описательной статистики.

Стандартное отклонение , среднеквадратичное отклонение, СКО, выборочное стандартное отклонение (англ. standard deviation, STD, STDev) - очень распространенный показатель рассеяния в описательной статистике. Но, т.к. технический анализ сродни статистике, данный показатель можно (и нужно) использовать в техническом анализе для обнаружения степени рассеяния цены анализируемого инструмента во времени. Обозначается греческим символом Сигма «σ».

Спасибо Карлам Гауссу и Пирсону за то, что мы имеем возможность пользоваться стандартным отклонением.

Используя стандартное отклонение в техническом анализе , мы превращаем этот «показатель рассеяния » в «индикатор волатильности «, сохраняя смысл, но меняя термины.

Что представляет собой стандартное отклонение

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

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

Применение стандартного отклонения

Вручную вычислить стандартное отклонение не очень интересно , но полезно для опыта. Стандартное отклонение можно выразить формулой STD=√[(∑(x-x ) 2)/n] , что звучит как корень из суммы квадратов разниц между элементами выборки и средним, деленной на количество элементов в выборке.

Если количество элементов в выборке превышает 30, то знаменатель дроби под корнем принимает значение n-1. Иначе используется n.

Пошагово вычисление стандартного отклонения :

1. вычисляем среднее арифметическое выборки данных
2. отнимаем это среднее от каждого элемента выборки
3. все полученные разницы возводим в квадрат
4. суммируем все полученные квадраты
5. делим полученную сумму на количество элементов в выборке (или на n-1, если n>30)
6. вычисляем квадратный корень из полученного частного (именуемого дисперсией )

Функция стандартное отклонение это уже из разряда высшей математики относящейся к статистики. В Excel существует несколько вариантов использования Функции стандартного отклонения это:

• Функция СТАНДОТКЛОНП.
• Функция СТАНДОТКЛОН.
• Функция СТАНДОТКЛОНПА

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

Предисловие

Давайте посмотрим на формулы сначала математическим языком, а после (ниже по тексту) подробно разберем формулу в Excel и как получившийся результат применяется в анализе статистических данных продаж.

Итак, Стандартное отклонение - это оценка среднеквадратического отклонения случайной величины x относительно её математического ожидания на основе несмещённой оценки её дисперсии)))) Не пугайтесь не понятных слов, потерпите и Вы все поймете!

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

Теперь стандартное отклонение - оценка среднеквадратического отклонения случайной величины x относительно её математического ожидания на основе несмещённой оценки её дисперсии:

Дисперсия;

- i -й элемент выборки;

Объём выборки;

Среднее арифметическое выборки:

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

Правило трёх сигм () - практически все значения нормально распределённой случайной величины лежат в интервале . Более строго - приблизительно с 0,9973 вероятностью значение нормально распределённой случайной величины лежит в указанном интервале (при условии, что величина истинная, а не полученная в результате обработки выборки). Мы же будем использовать округленный интервал 0,1

Если же истинная величина неизвестна, то следует пользоваться не , а s . Таким образом, правило трёх сигм преобразуется в правило трёх s . Именно это правило поможет нам определить стабильность продаж, но об этом чуть позже...

Теперь Функция стандартного отклонения в Excel

Надеюсь я не слишком Вас загрузил математикой? Возможно кому то данная информация потребуется для реферата или еще каких-нибудь целей. Теперь разжуем как эти формулы работают в Excel...

Для определения стабильности продаж нам не потребуется вникать во все варианты функций стандартного отклонения. Мы будем пользоваться всего одной:

Функция СТАНДОТКЛОНП

СТАНДОТКЛОНП (число1 ;число2 ;... )

Число1, число2,.. - от 1 до 30 числовых аргументов, соответствующих генеральной совокупности.

Теперь разберем на примере:

Давайте создадим книгу и импровизированную таблицу. Данный пример в Excel Вы скачаете в конце статьи.

Продолжение следует!!!

И снова здравствуйте. Ну что!? Выдалась свободная минутка. Давайте продолжим?

И так стабильность продаж при помощи Функции СТАНДОТКЛОНП

Для наглядности возьмем несколько импровизированных товаров:

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

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

И так у нас есть продажи, теперь нам нужно рассчитать средние значения продаж по периодам.

Формула среднего значения СРЗНАЧ(данные периода) в моем случае формула выглядит вот так =СРЗНАЧ(C6:E6)

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

Ctrl + Вниз курсор переместиться в коней списка.

Ctrl + Вправо, курсор переместиться в правую часть таблицы. Еще раз вправо и мы попадем на столбец с формулой.

Теперь зажимаем

Ctrl + Shift и нажимаем вверх. Так мы выделим область протягивания формулы.

И комбинация клавиш Ctrl + D протянет функцию там где нам надо.

Запомните эти комбинации, они реально увеличивают Вашу скорость работы в Excel, особенно когда Вы работаете с большими массивами.

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

Прописываем функцию и в значениях функции ставим значения продаж каждого периода. Если у Вас продажи в таблице друг за другом можно использовать диапазон, как у меня в формуле =СТАНДОТКЛОНП(C6:E6) или через точку с запятой перечисляем нужные ячейки =СТАНДОТКЛОНП(C6;D6;E6)

Вот все расчеты и готовы. Но как понять, что продается стабильно, а что нет? Просто проставим условность XYZ где,

Х - это стабильно

Y - с не большими отклонениями

Z - не стабильно

Для этого используем интервалы погрешности. если колебания происходят в пределах 10% будем считать что продажи стабильны.

Если в пределах от 10 до 25 процентов - это будет Y.

И если значения вариации превышает 25% - это не стабильность.

Что бы правильно задать буквы каждому товару, воспользуемся формулой ЕСЛИ подробнее про . В моей таблице данная функция будет выглядеть так:

ЕСЛИ(H6<0,1;"X";ЕСЛИ(H6<0,25;"Y";"Z"))

Соответственно все формулы протягиваем по всем наименованиям.

Постараюсь сразу ответить на вопрос, Почему интервалы 10% и 25%?

На самом деле интервалы могут быть иными, все зависит от конкретной задачи. Я специально показал Вам утрированные значения продаж, где разница видна на "глаз". Очевидно, что товар 1 продается не стабильно, но динамика показывает увеличение продаж. Такой товар оставляем в покое...

А вот товар 2, тут уже дистабилизация на лицо. И наши расчеты показывают Z, что говорит нам о не стабильности продаж. Товар 3 и Товар 5 показывают стабильные показатели, обратите внимание, Вариация в пределах 10%.

Т.е. Товар 5 с показателями 45, 46 и 45 показывает вариацию 1%, что является стабильным числовым рядом.

А вот Товар 2 с показателями 10, 50 и 5 показывают вариацию в 93%, что является НЕ стабильным числовым рядом.

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

В моей таблице не получилось "Y", я думаю для наглядности числового ряда, его нужно добавить. Пририсую Товар 6...

Вот видите, числовой ряд 40, 50 и 30 показывает 20% вариации. Вроде большой погрешности нет, но все же разброс существенный...

И так под итожим:

10,50,5 - Z не стабильность. Вариация более 25%

40,50,30 - Y на этот товар можно обратить внимание, и улучшить его продажи. Вариация меньше 25%, но больше 10%

45,46,45 - X это стабильность, с этим товаром пока ничего делать не надо. Вариация меньше 10%

На этом все! Надеюсь я все понятно изложил, если нет спрашивайте что не понятно. И буду благодарен Вам за каждый комментарий будь то похвала или критика. Так я буду знать, что Вы меня читаете и Вам, что очень ВАЖНО, интересно. И соответственно будут появляться новые уроки.

Добрый день!

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

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

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

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

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

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

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

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

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

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

СТАНДОТКЛОН.Г(_число1_;_число2_; ….), где:

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

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

ЕСЛИ(H4<0,1;"стабильно";ЕСЛИ(H4<0,25;"нормально";"не стабильно"))

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

Для начала выделяете , для которых будете применяться условное форматирование. В панели управления «Главная» выбираете «Условное форматирование» и в выпадающем меню пункт «Правила выделения ячеек» и следующим нажимаете пункт меню «Текст содержит…». Появляется диалоговое окно в которое вы вписываете свои условия.

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

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

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

Function MyStDevP(Arr) Dim x, aCnt&, aSum#, aAver#, tmp# For Each x In Arr aSum = aSum + x "вычисляем сумму элементов массива aCnt = aCnt + 1 "вычисляем кол-во элементов Next x aAver = aSum / aCnt "среднее значение For Each x In Arr tmp = tmp + (x - aAver) ^ 2 "вычисляем сумму квадратов разницы элементов массива и среднего значения Next x MyStDevP = Sqr(tmp / aCnt) "вычисляем СТАНДОТКЛОН.Г() End Function

Function MyStDevP (Arr ) Dim x , aCnt & , aSum #, aAver#, tmp# For Each x In Arr aSum = aSum + x "вычисляем сумму элементов массива