Формула процентного отклонения в Excel
Обновлено: 05.07.2024
Процентное отклонение измеряет степень, в которой отдельные точки данных в статистике отклоняются от среднего измерения этой статистики. Чтобы рассчитать процентное отклонение, сначала определите среднее значение данных и среднее отклонение точек данных от этого среднего значения.
Вычислить среднее
Рассчитайте среднее значение точек данных. Для этого сложите значения всех точек данных, а затем разделите на количество точек данных. Скажем, у вас есть четыре дыни весом 2 фунта, 5 фунтов, 6 фунтов и 7 фунтов. Найдите сумму: 2 + 5 + 6 + 7 = 20, затем разделите на четыре, поскольку точек данных четыре: 20 / 4 = 5. Таким образом, ваш картофель имеет средний вес 5 фунтов.
Рассчитать среднее отклонение
Узнав среднее значение данных, рассчитайте среднее отклонение. Среднее отклонение измеряет среднее расстояние точек данных от среднего значения.
Сначала вычислите расстояние каждой точки данных от среднего значения: расстояние D до точки данных равно абсолютному значению значения точки данных d минус среднее значение m: D = |d - m | Абсолютное значение, представленное символом | |, означает, что если результатом вычитания является отрицательное число, преобразовать его в положительное число. Например, 2-фунтовая дыня имеет отклонение 3, так как 2 минус среднее, 5, равно -3, а абсолютное значение -3 равно 3. Используя эту формулу, вы можете найти, что отклонение 6- дыня весом 1 фунт равна 1, а дыня весом 7 фунтов – 2. Отклонение дыни весом 5 фунтов равно нулю, поскольку ее вес равен среднему значению.
Как только вы узнаете отклонения всех точек данных, найдите их среднее значение, сложив их и разделив на количество точек данных. Отклонения равны 3, 2, 1 и нулю, что в сумме равно 6. Если вы разделите 6 на количество точек данных, 4, вы получите среднее отклонение 1,5.
Процентное отклонение от среднего и среднего
Среднее значение и среднее отклонение используются для определения процентного отклонения. Разделите среднее отклонение на среднее, а затем умножьте на 100. Полученное число покажет средний процент отклонения точки данных от среднего. Ваши дыни имеют средний вес 5 фунтов и среднее отклонение 1,5 фунта, поэтому:
отклонение в процентах = 1,5 / 5 x 100 = 30 процентов
Таким образом, в среднем ваши точки данных отстоят от вашего среднего значения на 30 процентов от среднего значения.
Процентное отклонение от известного стандарта
Процентное отклонение также может означать, насколько среднее значение набора данных отличается от известного или теоретического значения. Это может быть полезно, например, при сравнении данных, полученных в результате лабораторного эксперимента, с известным весом или плотностью вещества. Чтобы найти этот тип процентного отклонения, вычтите известное значение из среднего, разделите результат на известное значение и умножьте на 100.
Предположим, вы провели эксперимент по определению плотности алюминия и получили среднюю плотность 2500 кг на квадратный метр. Известная плотность алюминия составляет 2700 кг на квадратный метр, поэтому вы можете использовать эти два числа, чтобы рассчитать, насколько ваше экспериментальное среднее значение отличается от известного среднего. Вычтите 2700 из 2500, разделите результат на 2700, а затем умножьте на 100:
Отклонение в процентах = (2500–2700) / 2700 x 100 = -200 / 2700 x 100 = -7,41 процента
Знак "минус" в ответе означает, что среднее значение ниже ожидаемого. Если процентное отклонение положительное, это означает, что среднее значение выше ожидаемого. Таким образом, ваша средняя плотность на 7,41% ниже известной плотности.
В этом учебном пособии по Microsoft Excel представлены формулы как для отклонения (фактическое-ожидаемое), так и в пределах допуска в процентах. Если отклонение не рассчитано на вашем листе, правильная формула приведена в разделе «Как найти в пределах допуска для процентов».
1. Расчет процентного отклонения
Сначала мы выясняем, насколько наши машины далеки от требуемого или ожидаемого значения. Машины должны калиброваться на 500, но могут иметь допуск ±2%. Фактические уровни вводятся в столбце B, а процентное отклонение для каждой машины рассчитывается в столбце C.
Формула в ячейке C4: =ABS(B4−500)/500 . Мы помещаем математическое выражение B4−500 внутрь функции ABS, чтобы избежать отрицательного ответа. Функция ABS находит абсолютное значение или положительную версию числа (например, абсолютное значение 8 и −8 равно 8).
2. Ожидаемое значение, хранящееся в ячейке листа
Если номинальная стоимость 500 изменится, нам придется переделать наши формулы! Поэтому часто рекомендуется хранить константы в ячейках рабочего листа, чтобы их можно было легко изменить. Ячейка C2 на листе ниже содержит ожидаемое значение 500.
Формула в ячейке C4 теперь выглядит следующим образом: =ABS(B4−$C$2)/$C$2 . Мы ссылаемся на ячейку C2 по ее абсолютной ссылке на ячейку, $C$2 , поэтому Excel не меняет ее, когда мы копируем формулу.
Большинство электронных таблиц состоят из нескольких строк. Первая формула записывается, а затем реплицируется с помощью копирования и вставки или функции «Обработчик заполнения» в Excel. Во время этого процесса Excel по умолчанию меняет для нас ссылки на ячейки. Когда мы не хотим, чтобы ссылка менялась, мы используем абсолютную ссылку на ячейку: идентификатор строки и столбца, которому предшествует знак доллара.
3. Расчет в процентах допуска
Теперь, когда мы рассчитали отклонение, давайте рассмотрим формулу, чтобы указать, находится ли машина в пределах допуска или за его пределами. Помните, что если на вашем листе нет отклонений, вам нужно использовать формулу, описанную в разделе «Как найти процент допуска».
Формула в ячейке D4: =ЕСЛИ(C4, "Да", "Нет"). Тестовая часть функции ЕСЛИ проверяет отклонение в ячейке C4. Если оно меньше или равно 0,02, Excel пишет в ячейке Да. Если нет, Excel отображает Нет .
Обратите внимание на 0,02 в формуле. В математике 2% равняется 0,02. Столбец C был отформатирован для отображения процентов, но мы показываем его фактические значения с точностью до 4 знаков после запятой в столбце E. (PS: на самом деле мы ввели 0,02 на листе, а Excel добавил ноль слева от десятичной точки.)
Если вы не знаете, как работает функция ЕСЛИ, посмотрите на изображение ниже:
Формула немного усложняется, если отклонение не рассчитывается на листе. Мы рассмотрим этот сценарий в соответствующем руководстве Как найти в пределах допуска с помощью процентов.
В этом руководстве подробно рассказывается, как рассчитать относительное стандартное отклонение (%RSD) с помощью Excel, затем рассматривается рабочий пример и, наконец, подробно описываются ограничения расчета. Относительное стандартное отклонение в процентах является широко используемым статистическим инструментом, но, как ни странно, ни в одной из версий Microsoft Excel нет автоматизированной функции.
Относительное стандартное отклонение в Excel 2003, 2007 и 2010
%RSD — это мощный инструмент для статистического анализа вариаций в наборах данных, но специальная функция недоступна в Excel 2003, 2007 и даже 2010. Для расчета %RSD в Microsoft Excel необходимо использовать короткую формулу:< /p>
= (СТАНДОТКЛОН(диапазон данных) / СРЗНАЧ(диапазон данных))*100
Приведенный выше пример %RSD был введен в строку формул Excel и будет вычислять относительное стандартное отклонение в процентах для набора данных из 5 значений от E6 до E11. В ячейке, где записана формула, будет указано значение от 0 до 100. Результат выражается в процентах, где низкое число (<2,5%) указывает на небольшой разброс значений, а высокое значение указывает на значительный разброс результатов.
Почему %RSD так важен?
%RSD (относительное стандартное отклонение) — это статистическое измерение, описывающее разброс данных по отношению к среднему, а результат выражается в процентах. Функция %RSD популярна среди нестатистиков, поскольку интерпретация основана на процентном результате, а не на каком-то абстрактном значении. %RSD в основном используется в аналитической химии и обычно используется для оценки изменчивости наборов данных.
%RSD Рабочий пример Excel
Для расчета %RSD в Excel требуются две функции; Среднее значение и STDEV. Функция СРЗНАЧ [=среднее(население)] вычисляет среднее значение набора данных, а СТАНДОТКЛОН [=СТАНДОТКЛОН(население)] вычисляет стандартное отклонение (СО) данных. Завершающим этапом расчета является выражение результата в процентах, что и делает *100. Следующий пример %RSD основан на наборе данных из 5 значений. На изображении слева показан полный расчет, а на изображении справа — числовые значения.
Короткий вариант для левой таблицы состоит в том, чтобы объединить вычисление среднего значения и стандартного отклонения в одну функцию, и это будет выглядеть следующим образом:
Чтобы рассчитать отклонение в процентах, вычтите исходное (базовое) число из нового числа, а затем разделите полученный результат на исходное. В показанном примере формула в E5, скопированная вниз, выглядит следующим образом:
Результаты в столбце E представляют собой десятичные значения с примененным форматом процентного числа. Эту же формулу можно использовать для расчета таких вещей, как разница между этим и прошлым годом, разница между бюджетом и фактическими значениями и т. д.
В этом примере цель состоит в том, чтобы вычислить отклонение между прогнозом (столбец C) и фактическим значением (столбец D) в процентах. Например, при прогнозируемом значении 100 000 и фактическом значении 112 000 мы хотим вернуть отклонение 12%.
Концепция дисперсии требует базового значения и "нового" значения. Базовое значение вычитается из нового значения, и результат делится на базовое значение. Общая формула, где «x» — дисперсия, выглядит следующим образом:
После преобразования в формулу Excel со ссылками на ячейки формула в E5, скопированная вниз, выглядит следующим образом:
При копировании формулы она возвращает десятичное число для каждого элемента в списке. Когда эти числа отформатированы в формате процентного числа, они отображаются в процентах.
Форматирование процентов в Excel
В математике процент – это число, выраженное в виде доли от 100. Например, 25% читается как "двадцать пять процентов" и эквивалентно 25/100 или 0,25. Соответственно, значения в столбце E являются десятичными значениями с примененным числовым форматом в процентах. Чтобы преобразовать эти значения в целое число, например 12, умножьте его на 100:
Отрицательные числа
Если у вас есть отрицательное значение для исходного числа, приведенная выше формула не будет работать, и ее можно изменить, добавив функцию ABS:
ABS означает "абсолютное значение" и преобразует отрицательные значения в положительные значения. В этом случае функция ABS гарантирует, что исходное значение будет положительным при расчете дисперсии.
Примечание. Имейте в виду, что отрицательные значения результатов могут вводить в заблуждение, как объяснил Джон Акампора в своей подробной статье на эту тему.
Читайте также: