Как найти отклонение в excel
Argument ‘Topic id’ is null or empty
Сейчас на форуме
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
| ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |
Как посчитать процент отклонения в Excel по двум формулам
Понятие процент отклонения подразумевает разницу между двумя числовыми значениями в процентах. Приведем конкретный пример: допустим одного дня с оптового склада было продано 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 Formula Examples
- Создать таблицу
- Форматирование
- Функции Excel
- Формулы и диапазоны
- Фильтр и сортировка
- Диаграммы и графики
- Сводные таблицы
- Печать документов
- Базы данных и XML
- Возможности Excel
- Настройки параметры
- Уроки Excel
- Макросы VBA
- Скачать примеры
Как найти отклонение в excel
Argument ‘Topic id’ is null or empty
Сейчас на форуме
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
| ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |
Как найти отклонение в excel
contact@deynekina.ru
+7-916-571-91-94
DEYNEKINA HR&BA
Как анализировать результаты тестов
с помощью стандартного отклонения?
Для работы с аналитикой необходимо познакомиться с таким показателем, как стандартное отклонение. В любом наборе числовых данных этот показатель можно посчитать с помощью всего одной функции в Excel. Но для чего он нужен?
Давайте с вами продолжим разбираться со статистическими показателями.
Мы уже разобрали в других статьях особенности применения среднего и медианы, разбирали расчет премии с помощью средневзвешенного показателя, учились, как считать стаж работы с помощью показателя моды и убирали выбросы из данных с помощью квартилей , а сегодня поговорим о стандартном отклонении.
Давайте разберем этот показатель на примере результатов теста на знание Excel, который есть на нашем сайте. Если вы еще не проходили его, просим заглянуть.
Итак, тест состоит из 13 вопросов. Сейчас тест прошли 130 человек. В итоге я делаю такую гистограмму, чтобы посмотреть распределение количества правильных ответов по количеству ответивших.
То есть 8 человек дали 1 верный ответ из 13, 10 человек – 2 верных ответа из 13 и тд.
При этом среднее значение в этом наборе данных равно 6,9, медиана – 7, мода – 7. То есть наши три показателя почти совпадают. Надеюсь, вы помните, чем эти три показателя отличаются друг от друга?
Если нет, ссылки на статьи я дала в первом абзаце этой статьи.
Так что же такое стандартное отклонение? Это показатель, который описывает, насколько значения данных отличаются от среднего значения. То есть среднее расстояние всех данных от среднего значения.
В нашем примере стандартное отклонение показывает, насколько баллы теста по всем ответившим
отличаются от среднего значения, равного 6,9 баллам.
Стандартное отклонение рассчитывается с помощью функции Excel =СТАНДОТКЛОН.В(диапазон) для выборки или =СТАНДОТКЛОН.Г(диапазон) для генеральной совокупности.
В примере мы работаем с выборкой, так как тест на знание Excel прошли лишь 130 человек, а не все HR-специалисты России и СНГ.
Давайте разберем правила расчета стандартного отклонения на примере:
1. Возьмем выборку из 10 участников нашего теста
2. Посчитаем средний балл для выборки участников – 7,1 балл
3. Вычтем из каждого ответа среднее значение
4. Возведем в квадрат получившиеся значения, чтобы избавиться от отрицательных значений
5. Сложим получившиеся значения – 106,9
6. Разделим получившуюся сумму на количество участников выборки, то есть на 10 участников – 10,69
7. Вычтем квадратный корень из получившегося значения – 3,27.
Значение 3,27 и есть наше стандартное отклонение по результату теста от среднего балла по выборке из 10 человек.
Обратите внимание, что сначала мы возводим в квадрат, а потом вычитаем квадратный корень. Почему?
Если не возводить в квадрат, то сумма значений после этапа вычитания среднего значения из баллов участников всегда будет равна 0. И стандартное отклонение посчитать не получится. Чтобы это обойти, мы делаем взаимоисключающие действия: сначала возводим в квадрат, потом вычитаем квадратный корень. Таким образом мы избавляемся от отрицательных значений.
Конечно, я показала этапы расчета для того, чтобы вы поняли, как рассчитывается этот показатель.
На практике мы считаем стандартное отклонение с помощью одной из функций Excel
=СТАНДОТКЛОН.В(диапазон) или =СТАНДОТКЛОН.Г(диапазон). В нашем случае мы используем =СТАНДОТКЛОН.В(диапазон), так как работаем с выборкой.
Итак, вернемся к общим результатам нашего теста по 130 участникам.
Мы можем описать результаты с помощью следующих данных: среднее значение – 6,9 баллов, стандартное отклонение – 3,3 балла.
В среднем на 3,3 балла отклоняются значения от среднего результата теста. То есть от 3,6 баллов до 10,2 баллов. Это довольно широкий диапазон распределения баллов участников теста.
Стандартное отклонение помогает сравнивать результаты нескольких групп.
Например, в компании провели аттестацию рабочих.
Давайте сравним результаты аттестации цеха 1 и 2.
Если бы мы ориентировались только на среднее значение, мы бы не увидели разницу, так как среднее значение в обоих цехах равно 80 баллам. Но мы видим, что стандартное отклонение отличается: в первом цехе равно 10, во втором – 5. То есть разброс баллов по аттестации рабочих в первом цехе сильнее, чем во втором цехе.
Предположим, у нас есть проходной балл аттестации – 75. Давайте посмотрим, в каком цехе больше рабочих, набравших проходной балл и прошедших аттестацию?
В первом цехе средний балл – 80, но стандартное отклонение – 10, значит, в среднем результаты аттестации находятся в диапазоне от 70 до 90 баллов.
Во втором цехе стандартное отклонение – 5, в среднем результаты аттестации находятся в диапазоне
от 75 до 85 баллов.
Значит, в первом цехе больше сотрудников, которые не набрали проходной балл в 75 баллов, чем во втором.
Давайте глянем на картинку.
Конечно, я показала гипотетическую ситуацию, когда средний балл аттестации в двух цехах одинаковый. Если он будет разным, нужно будет стандартизировать значения. Но это уже другая история. Наша задача сегодня была – разобраться в показателе «стандартное отклонение».
Итак, что позволяет сделать стандартное отклонение и почему я рекомендую его считать:
1. Оценить разброс данных в выборке или генеральной совокупности относительно среднего значения
2. Сравнить результаты расчетов в двух выборках.
Все эти показатели и применение их на практике мы разбираем на наших онлайн-курсах. Если вы хотите повысить квалификацию в аналитике, приглашаем в нашу онлайн-школу HR-аналитики .
© 2020 Все права защищены
ИП Дейнекина Галина Игоревна
ИНН 231408484160
ОГРНИП 318505300003952