Как посчитать выбросы в excel

Формула выбросов Как рассчитать выбросы (шаблон Excel)

В статистике выбросы — это две крайние отдаленные необычные точки в данных наборах данных. Чрезвычайно высокое значение и чрезвычайно низкие значения являются выбросными значениями набора данных. Это очень полезно при обнаружении любых ошибок или ошибок, которые произошли. Как следует из названия, выбросы — это значения, которые лежат снаружи от остальных значений в наборе данных. Например, рассмотрим студентов-инженеров и представим, что в их классе есть гномы. Таким образом, дварфы — это люди с очень низким ростом по сравнению с другими людьми с нормальным ростом. Так что это значение выброса в этом классе. Значения выбросов могут быть рассчитаны с использованием метода Тьюки.

Формула для Выбросов —

Lower Outlier = Q1 – (1.5 * IQR)
Higher Outlier= Q3 + (1.5 * IQR)

Примеры формулы выбросов (с шаблоном Excel)

Давайте рассмотрим пример, чтобы лучше понять расчет формулы Outliers.

Вы можете скачать этот шаблон выбросов здесь — шаблон выбросов

Формула выбросов — пример № 1

Рассмотрим следующий набор данных и рассчитать выбросы для набора данных.

Набор данных = 5, 2, 7, 98, 309, 45, 34, 6, 56, 89, 23

Восходящий порядок набора данных:

Медиана набора данных в восходящем порядке рассчитывается как:

В этом наборе данных общее количество данных равно 11. Таким образом, n = 11. Медиана = 11 + 1/2 = 12/2 = 6. Следовательно, значение, которое находится на 6- й позиции в этом наборе данных, является медианой.

Итак, медиана = 34.

Разделите набор данных на 2 половины, используя медиану.

Медиана данных нижней и верхней половины рассчитывается как:

• В нижней половине 2, 5, 6, 7, 23, если мы найдем медиану, например, как мы нашли на шаге 2, медиана будет равна 6. Так что Q1 = 6.
• В верхней половине 45, 56, 89, 98, 309, если мы найдем медиану, например, как мы нашли на шаге 2, медиана будет равна 89. Таким образом, Q3 = 89.

IQR рассчитывается по формуле, приведенной ниже

IQR = Q3 — Q1

• IQR = 89 -6
• IQR = 83

Нижний выброс рассчитывается по формуле, приведенной ниже

Нижний выброс = Q1 — (1, 5 * IQR)

• Нижний выброс = 6 — (1, 5 * 83)
• Нижний выброс = -118, 5

Высший выброс рассчитывается по формуле, приведенной ниже

Выше выброс = Q3 + (1, 5 * IQR)

• Выше выброс = 89 + (1, 5 * 83)
• Высший выброс = 213, 5

Теперь извлеките эти значения из набора данных -118, 5, 2, 5, 6, 7, 23, 34, 45, 56, 89, 98, 213, 5, 309. Значения, которые падают ниже в нижней стороне и выше в верхней стороне являются выбросом значения. Для этого набора данных 309 является выбросом.

Формула выбросов — пример № 2

Рассмотрим следующий набор данных и рассчитать выбросы для набора данных.

Набор данных = 45, 21, 34, 90, 109.

Восходящий порядок набора данных:

Медиана набора данных в восходящем порядке рассчитывается как:

В этом наборе данных общее количество данных равно 5. Таким образом, n = 5. Медиана = 5 + 1/2 = 6/2 = 3. Следовательно, значение, которое находится на 3-й позиции в этом наборе данных, является медианой.

Итак, медиана = 45.

Разделите набор данных на 2 половины, используя медиану.

Медиана данных нижней и верхней половины рассчитывается как:

• Q1 = 27, 5
• Q3 = 89

IQR рассчитывается по формуле, приведенной ниже

IQR = Q3 — Q1

• IQR = 99, 5 — 27, 5
• IQR = 72

Нижний выброс рассчитывается по формуле, приведенной ниже

Нижний выброс = Q1 — (1, 5 * IQR)

• Нижний выброс = 27, 5 — (1, 5 * 72)
• Нижний выброс = -80, 5

Высший выброс рассчитывается по формуле, приведенной ниже

Выше выброс = Q3 + (1, 5 * IQR)

• Выше выброс = 99, 5 + (1, 5 * 72)
• Высший выброс = 207, 5

объяснение

Шаг 1: Расположите все значения в данном наборе данных в порядке возрастания.

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

Медиана = (n + 1) / 2

Где n — общее количество данных, доступных в наборе данных.

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

Шаг 4: Найти верхнее значение Quartile Q3 из набора данных. Это в точности как вышеописанный шаг. Вместо нижней половины мы должны следовать той же процедуре, что и верхняя половина набора значений.

Шаг 5: Найти значение IQR межквартильного диапазона. Чтобы найти значение Deduct Q1 из Q3.

IQR = Q3-Q1

Шаг 6: Найдите значение Внутреннего Экстрима. Конец, который выходит за пределы нижней стороны, который также можно назвать второстепенным выбросом. Умножьте значение IQR на 1, 5 и вычтите это значение из Q1, чтобы получить экстремум внутреннего нижнего уровня.

Нижний выброс = Q1 — (1, 5 * IQR)

Шаг 7: Найти значение экстремального экстремума. Конец, который выходит за пределы более высокой стороны, которую также можно назвать основным выбросом. Умножьте значение IQR на 1, 5 и суммируйте это значение с Q3, чтобы получить экстремум Outer Higher.

Выше выброс = Q3 + (1, 5 * IQR)

Шаг 8: Значения, которые выходят за пределы этих внутренних и внешних крайностей, являются значениями выбросов для данного набора данных.

Актуальность и использование формулы выбросов

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

Рекомендуемые статьи

Это было руководство к формуле выбросов. Здесь мы обсуждаем, как рассчитать выбросы вместе с практическими примерами и загружаемым шаблоном Excel. Вы также можете посмотреть следующие статьи, чтобы узнать больше —

Как легко найти выбросы в Excel

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

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

Метод 1: используйте межквартильный диапазон

Межквартильный размах (IQR) — это разница между 75-м процентилем (Q3) и 25-м процентилем (Q1) в наборе данных. Он измеряет разброс средних 50% значений.

Мы можем определить наблюдение как выброс, если оно в 1,5 раза превышает межквартильный размах, превышающий третий квартиль (Q3), или в 1,5 раза превышает межквартильный размах меньше, чем первый квартиль (Q1).

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

Затем мы можем использовать формулу, упомянутую выше, чтобы присвоить «1» любому значению, которое является выбросом в наборе данных:

Мы видим, что только одно значение — 164 — оказывается выбросом в этом наборе данных.

Способ 2: использовать z-показатели

Z-оценка показывает, сколько стандартных отклонений данного значения от среднего. Мы используем следующую формулу для расчета z-показателя:

z = (X — μ) / σ

• X — это одно необработанное значение данных.
• μ — среднее значение населения
• σ — стандартное отклонение населения

Мы можем определить наблюдение как выброс, если его z-оценка меньше -3 или больше 3.

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

Затем мы можем использовать среднее значение и стандартное отклонение, чтобы найти z-оценку для каждого отдельного значения в наборе данных:

Затем мы можем присвоить «1» любому значению, которое имеет z-оценку меньше -3 или больше 3:

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

Примечание. Иногда вместо 3 используется z-показатель 2,5. В этом случае отдельное значение 164 будет считаться выбросом, поскольку его z-показатель больше 2,5. При использовании метода z-показателя руководствуйтесь своим здравым смыслом, какое значение z-показателя вы считаете выбросом.

Как обращаться с выбросами

Если в ваших данных присутствует выброс, у вас есть несколько вариантов:

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

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

2. Удалите выброс.

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

3. Присвойте новое значение выбросу .

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

Выбросы в Excel: Определение, советы и как их рассчитать

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

Что такое пропуски в Excel?

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

Как вычислить выбросы в Excel

Рассмотрим эти шаги для расчета отклоняющихся значений в Excel:

1. Просмотр введенных данных

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

2. Отсортируйте значения данных

Выберите диапазон вашего набора данных, нажав на первую ячейку и перетащив рамку в правом нижнем углу до последней ячейки. На верхней функциональной ленте Excel щелкните по кнопке Главная перейдите на вкладку Сортировать & Фильтр выберите инструмент Пользовательская сортировка опция. Под Заказать раскрывающееся меню категории, выберите порядок набора данных из От наименьшего к наибольшему нажмите кнопку OK для реализации ваших изменений.

3. Проанализируйте свои значения

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

4. Определите квартили ваших данных

Чтобы вычислить выбросы в наборе данных, рассчитайте квартили, используя автоматизированную формулу квартилей Excel, начинающуюся со слова = КВАРТИЛЬ( в пустой ячейке. После левой круглой скобки укажите первую и последнюю ячейки в диапазоне данных, разделенные двоеточием и запятой, а также квартиль, который вы хотите определить. Ваша формула может выглядеть следующим образом =КВАРТАЛ(A5:A50, 1) или = КВАРТИЛЬ(B2:B200, 3).

5. Определите интерквартильный размах

Интерквартильный размах представляет собой ожидаемый средний диапазон вашего набора данных, не содержащий отклоняющихся значений. Вы можете рассчитать интерквартильный размах путем вычитания первого квартиля из третьего квартиля. В пустой ячейке укажите ячейку с формулой вашего третьего квартиля, знак минус и ячейку с формулой вашего первого квартиля, чтобы ввести что-то вроде C2-C1 и нажмите Enter, чтобы Excel рассчитал его.

6. Вычислите верхнюю и нижнюю границы

Определение верхней и нижней границ вашего набора данных позволяет вам определить значения, большие или меньшие каждого из них, соответственно, чтобы найти выбросы. Чтобы найти верхнюю границу диапазона данных, умножьте интерквартильный размах на 1.5 и добавьте его к значению третьего квартиля, чтобы создать формулу следующего вида =C2+(1.5*C3). Чтобы найти нижнюю границу диапазона данных, умножьте интерквартильный размах на 1.5 и вычтите его из значения первого квартиля, чтобы создать формулу следующего вида =C1-(1.5*C3).

7. Удаление выбросов

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

Советы по расчету выбросов в Excel

Вот несколько советов, которые помогут вам рассчитать отклонения в Excel:

Корректировка значений выбросов

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

Посмотрите на визуализации данных

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

Сократите диапазон данных

Вы можете использовать функцию автоматической обрезки диапазона данных Excel, чтобы удалить заданный процент значений из самой высокой и самой низкой частей вашего набора данных. Чтобы воспользоваться этой функцией, введите =TRIMMEAN( в пустую ячейку для начала формулы. После левой скобки укажите первую и последнюю ячейки в вашем диапазоне, разделенные двоеточием, затем процент, который вы хотите обрезать, и правую скобку, чтобы создать формулу, подобную следующей =TRIMMEAN(A5:A50, 0.25).

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

Формула расчета статистических выбросов с выборкой в Excel

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

Как определить статистические выбросы и сделать выборку для их удаления в Excel

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

Первым шагом в поиске значений выбросов статистики является определение статистического центра диапазона данных. С этой целью необходимо сначала определить границы первого и третьего квартала. Определение границ квартала – значит разделение данных на 4 равные группы, которые содержат по 25% данных каждая. Группа, содержащая 25% наибольших значений, называется первым квартилем.

Границы квартилей в Excel можно легко определить с помощью простой функции КВАРТИЛЬ. Данная функция имеет 2 аргумента: диапазон данных и номер для получения желаемого квартиля.

В примере показанному на рисунке ниже значения в ячейках E1 и E2 содержат показатели первого и третьего квартиля данных в диапазоне ячеек B2:B19:

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

В этом месте возникает вопрос, как сильно данное значение может отличаться от среднего значения 50% данных и оставаться все еще в пределах нормы? Статистические аналитики соглашаются с тем, что для определения нижней и верхней границы диапазона данных можно смело использовать коэффициент расширения 1,5 умножив на значение межквартильного диапазона. То есть:

1. Нижняя граница диапазона данных равна: значение первого квартиля – межкваритльный диапазон * 1,5.
2. Верхняя граница диапазона данных равна: значение третьего квартиля + расширенных диапазон * 1,5.

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

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

Выборка статистических выбросов с помощью квартилей в Excel

Чтобы создать правило для условного форматирования по выше описанным инструкциям, сделайте следующее:

1. Выделите целевой диапазон ячеек (в данном примере B2:B19) и выберите инструмент «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило». Появится окно «Создание правила форматирования ячеек», как показано ниже на рисунке:
2. Из списка в верхней части окна выберите опцию «Использовать формулу для определения форматируемых ячеек». Данная опция служит для анализа значений в ячейках выделенного диапазона, с помощью определенной формулы с логическим выражением. Если в результате вычислений формулой, по какому-то из значений будет возвращено логическое значение ИСТИНА, тогда в этой ячейке будет применятся условное форматирование.
3. В полю для введения формулы введите логическое выражение представленное на данном шаге. Обратите внимание на то, что в формуле используется относительная ссылка на целевую ячейку B2. А ссылки на верхнюю и нижнюю границу в ячейках $E$5 и $E$6 являются абсолютными. Два логических выражения помещены внутрь логической функции ИЛИ в качестве аргументов. Если значение целевой ячейки будет больше, чем верхняя граница или же меньше чем нижняя граница, тогда формула возвращает значение ИСТИНА и автоматически применяется условное форматирование.

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