Понятие процент отклонения подразумевает разницу между двумя числовыми значениями в процентах. Приведем конкретный пример: допустим одного дня с оптового склада было продано 120 штук планшетов, а на следующий день – 150 штук. Разница в объемах продаж – очевидна, на 30 штук больше продано планшетов в следующий день. При вычитании от 150-ти числа 120 получаем отклонение, которое равно числу +30. Возникает вопрос: чем же является процентное отклонение?
Как посчитать отклонение в процентах в Excel
Процент отклонения вычисляется через вычитание старого значения от нового значения, а далее деление результата на старое значение. Результат вычисления этой формулы в Excel должен отображаться в процентном формате ячейки. В данном примере формула вычисления выглядит следующим образом (150-120)/120=25%. Формулу легко проверить 120+25%=150.
Обратите внимание! Если мы старое и новое число поменяем местами, то у нас получиться уже формула для вычисления наценки .
Ниже на рисунке представлен пример, как выше описанное вычисление представить в виде формулы Excel. Формула в ячейке D2 вычисляет процент отклонения между значениями продаж для текущего и прошлого года: =(C2-B2)/B2
Важно обратит внимание в данной формуле на наличие скобок. По умолчанию в Excel операция деления всегда имеет высший приоритет по отношению к операции вычитания. Поэтому если мы не поставим скобки, тогда сначала будет разделено значение, а потом из него вычитается другое значение. Такое вычисление (без наличия скобок) будет ошибочным. Закрытие первой части вычислений в формуле скобками автоматически повышает приоритет операции вычитания выше по отношению к операции деления.
Правильно со скобками введите формулу в ячейку D2, а далее просто скопируйте ее в остальные пустые ячейки диапазона D2:D5. Чтобы скопировать формулу самым быстрым способом, достаточно подвести курсор мышки к маркеру курсора клавиатуры (к нижнему правому углу) так, чтобы курсор мышки изменился со стрелочки на черный крестик. После чего просто сделайте двойной щелчок левой кнопкой мышки и Excel сам автоматически заполнит пустые ячейки формулой при этом сам определит диапазон D2:D5, который нужно заполнить до ячейки D5 и не более. Это очень удобный лайфхак в Excel.
Альтернативная формула для вычисления процента отклонения в Excel
В альтернативной формуле, вычисляющей относительное отклонение значений продаж с текущего года сразу делиться на значения продаж прошлого года, а только потом от результата отнимается единица: =C2/B2-1.
Как видно на рисунке результат вычисления альтернативной формулы такой же, как и в предыдущей, а значит правильный. Но альтернативную формулу легче записать, хот и возможно для кого-то сложнее прочитать так чтобы понять принцип ее действия. Или сложнее понять, какое значение выдает в результате вычисления данная формула если он не подписан.
Единственный недостаток данной альтернативной формулы – это отсутствие возможности рассчитать процентное отклонение при отрицательных числах в числителе или в заменителе. Даже если мы будем использовать в формуле функцию ABS, то формула будет возвращать ошибочный результат при отрицательном числе в заменителе.
Так как в Excel по умолчанию приоритет операции деления выше операции вычитания в данной формуле нет необходимости применять скобки.
Для того чтобы найти среднее значение в Excel (при том неважно числовое, текстовое, процентное или другое значение) существует много функций. И каждая из них обладает своими особенностями и преимуществами. Ведь в данной задаче могут быть поставлены определенные условия.
Например, средние значения ряда чисел в Excel считают с помощью статистических функций. Можно также вручную ввести собственную формулу. Рассмотрим различные варианты.
Как найти среднее арифметическое чисел?
Чтобы найти среднее арифметическое, необходимо сложить все числа в наборе и разделить сумму на количество. Например, оценки школьника по информатике: 3, 4, 3, 5, 5. Что выходит за четверть: 4. Мы нашли среднее арифметическое по формуле: =(3+4+3+5+5)/5.
Как это быстро сделать с помощью функций Excel? Возьмем для примера ряд случайных чисел в строке:
Или: сделаем активной ячейку и просто вручную впишем формулу: =СРЗНАЧ(A1:A8).
Теперь посмотрим, что еще умеет функция СРЗНАЧ.
Найдем среднее арифметическое двух первых и трех последних чисел. Формула: =СРЗНАЧ(A1:B1;F1:H1). Результат:
Среднее значение по условию
Условием для нахождения среднего арифметического может быть числовой критерий или текстовый. Будем использовать функцию: =СРЗНАЧЕСЛИ().
Найти среднее арифметическое чисел, которые больше или равны 10.
Функция: =СРЗНАЧЕСЛИ(A1:A8;">=10")
Результат использования функции СРЗНАЧЕСЛИ по условию ">=10":
Третий аргумент – «Диапазон усреднения» - опущен. Во-первых, он не обязателен. Во-вторых, анализируемый программой диапазон содержит ТОЛЬКО числовые значения. В ячейках, указанных в первом аргументе, и будет производиться поиск по прописанному во втором аргументе условию.
Внимание! Критерий поиска можно указать в ячейке. А в формуле сделать на нее ссылку.
Найдем среднее значение чисел по текстовому критерию. Например, средние продажи товара «столы».
Функция будет выглядеть так: =СРЗНАЧЕСЛИ($A$2:$A$12;A7;$B$2:$B$12). Диапазон – столбец с наименованиями товаров. Критерий поиска – ссылка на ячейку со словом «столы» (можно вместо ссылки A7 вставить само слово "столы"). Диапазон усреднения – те ячейки, из которых будут браться данные для расчета среднего значения.
В результате вычисления функции получаем следующее значение:
Внимание! Для текстового критерия (условия) диапазон усреднения указывать обязательно.
Как посчитать средневзвешенную цену в Excel?
Как мы узнали средневзвешенную цену?
Формула: =СУММПРОИЗВ(C2:C12;B2:B12)/СУММ(C2:C12).
С помощью формулы СУММПРОИЗВ мы узнаем общую выручку после реализации всего количества товара. А функция СУММ - сумирует количесвто товара. Поделив общую выручку от реализации товара на общее количество единиц товара, мы нашли средневзвешенную цену. Этот показатель учитывает «вес» каждой цены. Ее долю в общей массе значений.
Среднее квадратическое отклонение: формула в Excel
Различают среднеквадратическое отклонение по генеральной совокупности и по выборке. В первом случае это корень из генеральной дисперсии. Во втором – из выборочной дисперсии.
Для расчета этого статистического показателя составляется формула дисперсии. Из нее извлекается корень. Но в Excel существует готовая функция для нахождения среднеквадратического отклонения.
Среднеквадратическое отклонение имеет привязку к масштабу исходных данных. Для образного представления о вариации анализируемого диапазона этого недостаточно. Чтобы получить относительный уровень разброса данных, рассчитывается коэффициент вариации:
среднеквадратическое отклонение / среднее арифметическое значение
Формула в Excel выглядит следующим образом:
СТАНДОТКЛОНП (диапазон значений) / СРЗНАЧ (диапазон значений).
Коэффициент вариации считается в процентах. Поэтому в ячейке устанавливаем процентный формат.
Стандартное отклонение является одним из тех статистических терминов в корпоративном мире, которое позволяет поднять авторитет людей, сумевших удачно ввернуть его в ходе беседы или презентации, и оставляет смутное недопонимание тех, кто не знает, что это такое, но стесняется спросить. На самом деле большинство менеджеров не понимают концепцию стандартного отклонения и, если вы один из них, вам пора перестать жить во лжи. В сегодняшней статье я расскажу вам, как эта недооцененная статистическая мера позволит лучше понять данные, с которыми вы работаете.
Что измеряет стандартное отклонение?
Представьте, что вы владелец двух магазинов. И чтобы избежать потерь, важно, чтобы был четкий контроль остатков на складе. В попытке выяснить, кто из менеджеров лучше управляет запасами, вы решили проанализировать стоки последних шести недель. Средняя недельная стоимость стока обоих магазинов примерно одинакова и составляет около 32 условных единиц. На первый взгляд среднее значение стока показывает, что оба менеджера работают одинаково.
Но если внимательнее изучить деятельность второго магазина, можно убедится, что хотя среднее значение корректно, вариабельность стока очень высокая (от 10 до 58 у.е.). Таким образом, можно сделать вывод, что среднее значение не всегда правильно оценивает данные. Вот где на выручку приходит стандартное отклонение.
Стандартное отклонение показывает, как распределены значения относительно среднего в нашей . Другими словами, можно понять на сколько велик разброс величины стока от недели к неделе.
В нашем примере, мы воспользовались функцией Excel СТАНДОТКЛОН, чтобы рассчитать показатель стандартного отклонения вместе со средним.
В случае с первым менеджером, стандартное отклонение составило 2. Это говорит нам о том, что каждое значение в выборке в среднем откланяется на 2 от среднего значения. Хорошо ли это? Давайте рассмотрим вопрос под другим углом – стандартное отклонение равное 0, говорит нам о том, что каждое значение в выборке равно его среднему значению (в нашем случае, 32,2). Так, стандартное отклонение 2 ненамного отличается от 0, и указывает на то, что большинство значений находятся рядом со средним значением. Чем ближе стандартное отклонение к 0, тем надежнее среднее. Более того, стандартное отклонение близкое к 0, говорит о маленькой вариабельности данных. То есть, величина стока со стандартным отклонением 2, указывает на невероятную последовательность первого менеджера.
В случае со вторым магазином, стандартное отклонение составило 18,9. То есть стоимость стока в среднем отклоняется на величину 18,9 от среднего значения от недели к неделе. Сумасшедший разброс! Чем дальше стандартное отклонение от 0, тем менее точно среднее значение. В нашем случае, цифра 18,9 указывает на то, что среднему значению (32,8 у.е. в неделю) просто нельзя доверять. Оно также говорит нам о том, что еженедельная величина стока обладает большой вариабельностью.
Такова концепция стандартного отклонения в двух словах. Хотя оно не дает представление о других важных статистических измерениях (Мода, Медиана…), фактически стандартное отклонение играет решающую роль в большинстве статистических расчетов. Понимание принципов стандартного отклонения прольет свет на суть многих процессов вашей деятельности.
Как рассчитать стандартное отклонение?
Итак, теперь мы знаем, о чем говорит цифра стандартного отклонения. Давайте разберемся, как она считается.
Рассмотрим набор данных от 10 до 70 с шагом 10. Как видите, я уже рассчитал для них значение стандартного отклонения с помощью функции СТАНДОТКЛОН в ячейке H2 (оранжевым).
Ниже описаны шаги, которые предпринимает Excel, чтобы прийти к цифре 21,6.
Обратите внимание, что все расчеты визуализированы, для лучшего понимания. На самом деле в Excel расчет происходит мгновенно, оставляя все шаги за кулисами.
Для начала Excel находит среднее значение выборки. В нашем случае, среднее получилось равным 40, которое на следующем шаге отнимают от каждого значения выборки. Каждую полученную разницу возводят в квадрат и суммируют. У нас получилась сумма равная 2800, которую необходимо разделить на количество элементов выборки минус 1. Так как у нас 7 элементов, получается необходимо 2800 разделить на 6. Из полученного результата находим квадратный корень, это цифра будет стандартным отклонением.
Для тех, кому не совсем ясен принцип расчета стандартного отклонения с помощью визуализации, привожу математическую интерпретацию нахождения данного значения.
Функции расчета стандартного отклонения в Excel
В Excel присутствует несколько разновидностей формул стандартного отклонения. Вам достаточно набрать =СТАНДОТКЛОН и вы сами в этом убедитесь.
Стоит отметить, что функции СТАНДОТКЛОН.В и СТАНДОТКЛОН.Г (первая и вторая функция в списке) дублируют функции СТАНДОТКЛОН и СТАНДОТКЛОНП (пятая и шестая функция в списке), соответственно, которые были оставлены для совместимости с более ранними версиями Excel.
Вообще разница в окончаниях.В и.Г функций указывают на принцип расчета стандартного отклонения выборки или генеральной совокупности. Разницу между двумя этими массивами я уже объяснял в предыдущей .
Особенностью функций СТАНДОТКЛОНА и СТАНДОТКЛОНПА (третья и четвертая функция в списке), является то, что при расчете стандартного отклонения массива в расчет принимаются логические и текстовые значения. Текстовые и истинные логические значения равняются 1, а ложные логические значения равняются 0. Мне трудно представить ситуацию, когда бы мне могли понадобится эти две функции, поэтому, думаю, что их можно игнорировать.
Функция стандартное отклонение это уже из разряда высшей математики относящейся к статистики. В 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%
На этом все! Надеюсь я все понятно изложил, если нет спрашивайте что не понятно. И буду благодарен Вам за каждый комментарий будь то похвала или критика. Так я буду знать, что Вы меня читаете и Вам, что очень ВАЖНО, интересно. И соответственно будут появляться новые уроки.
Андрей Липов
Если говорить простым языком, то стандартное отклонение показывает насколько сильно цена инструмента колбасится во времени. То есть чем больше этот показатель, тем сильнее волатильность или изменчивость ряда значений.
Стандартное отклонение можно и нужно использовать для анализа наборов значений, так как два набора с, казалось бы, одинаковым средним могут оказаться совершенно разными по разбросу величин.
Пример
Возьмем два ряда чисел.
a) 1,2,3,4,5,6,7,8,9 . Среднее - 5. Ст. отклонение = 2,7386
б) 20,1,7,1,15,-1,-20,4,18,5 . Среднее - 5. Ст. отклонение = 12,2066
Если не держать перед глазами всего ряда чисел, то по показателю стандартного отклонения видно, что в случае «б» величины гораздо сильнее разбрасываются вокруг своего среднего значения.
Грубо говоря, в ряде «б» значение равно 5 плюс-минус 12 (в среднем) - не точно, но раскрывает смысл.
Как посчитать стандартное отклонение
Для расчета стандартного отклонения можно использовать формулу, позаимствованную из расчета стандартного отклонения доходности ПИФов :
Здесь N - количество величин,
ДОХсредн - среднее всех величин,
ДОХпериода - величина N.
В экселе соответствующая функция называется СТАНДОТКЛОН (или STDEV в английской версии программы).
Пошаговая инструкция такова:
- Рассчитайте среднее значение для ряда чисел.
- Для каждого значения определите разницу между средним и этим значением.
- Вычислите сумму квадратов этих разниц.
- Разделите получившуюся сумму на количество чисел в ряде.
- Возьмите квадратный корень от получившегося в прошлом пункте числа.
Вашим друзьям будет полезна эта информация. Поделитесь с ними!