Как (и зачем) использовать функцию выбросов в Excel

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

Быстрый пример

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

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

Как найти выбросы в ваших данных

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

  1. Вычислите 1-й и 3-й квартили (мы немного поговорим о том, что это такое).
  2. Оцените межквартильный размах (мы также объясним это немного ниже).
  3. Верните верхнюю и нижнюю границы нашего диапазона данных.
  4. Используйте эти границы для определения отдаленных точек данных.

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

Давайте начнем.

Шаг 1. Рассчитайте квартили

Если вы разделите данные на кварталы, каждый из этих наборов называется квартилем. Самые низкие 25% чисел в диапазоне составляют 1-й квартиль, следующие 25% - 2-й квартиль и т. Д. Мы делаем этот шаг в первую очередь, потому что наиболее широко используемое определение выброса - это точка данных, которая более чем на 1,5 интерквартильных диапазона (IQR) ниже 1-го квартиля и на 1,5 межквартильных диапазонов выше 3-го квартиля. Чтобы определить эти значения, мы сначала должны выяснить, каковы квартили.

В Excel есть функция КВАРТИЛЬ для расчета квартилей. Для этого требуются две части информации: массив и кварта.

= КВАРТИЛЬ (массив; кварта)

Массив является диапазон значений , которые вы оцениваете. И кварта это число , которое представляет квартиль вы хотите вернуться (например, 1 для 1 - й квартили, 2 для 2 - го квартили, и так далее).

Примечание. В Excel 2010 Microsoft выпустила функции QUARTILE.INC и QUARTILE.EXC как улучшения функции QUARTILE. QUARTILE более обратно совместима при работе с несколькими версиями Excel.

Вернемся к нашему примеру таблицы.

Чтобы вычислить 1-й квартиль, мы можем использовать следующую формулу в ячейке F2.

= КВАРТИЛЬ (B2: B14,1)

Когда вы вводите формулу, Excel предоставляет список параметров для аргумента кварты.

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

= КВАРТИЛЬ (B2: B14,3)

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

Шаг второй: оцените межквартильный размах

Межквартильный диапазон (или IQR) - это средние 50% значений ваших данных. Он рассчитывается как разница между значением 1-го квартиля и значением 3-го квартиля.

Мы собираемся использовать простую формулу в ячейке F4, которая вычитает 1-й квартиль из 3-го квартиля:

= F3-F2

Теперь мы можем видеть наш межквартильный размах.

Шаг третий: верните нижнюю и верхнюю границы

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

Мы рассчитаем нижний предел в ячейке F5, умножив значение IQR на 1,5, а затем вычтя его из точки данных Q1:

= F2- (1,5 * F4)

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

Чтобы вычислить верхнюю границу в ячейке F6, мы снова умножим IQR на 1,5, но на этот раз добавим его к точке данных Q3:

= F3 + (1,5 * F4)

Шаг четвертый: выявление выбросов

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

Мы воспользуемся функцией ИЛИ, чтобы выполнить этот логический тест и показать значения, соответствующие этим критериям, введя следующую формулу в ячейку C2:

= ИЛИ (B2 $ F $ 6)

Затем мы скопируем это значение в наши ячейки C3-C14. Значение ИСТИНА указывает на выброс, и, как видите, в наших данных их два.

Игнорирование выбросов при вычислении среднего среднего

Использование функции КВАРТИЛЬ позволяет нам рассчитать IQR и работать с наиболее широко используемым определением выброса. Однако при вычислении среднего среднего для диапазона значений и игнорировании выбросов существует более быстрая и простая функция. Этот метод не будет идентифицировать выброс, как раньше, но он позволит нам быть гибкими в выборе того, что мы можем считать своей частью выброса.

Нужная нам функция называется TRIMMEAN, синтаксис для нее вы можете увидеть ниже:

= ОБРЕЗАТЬ (массив; проценты)

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

В нашем примере мы ввели приведенную ниже формулу в ячейку D3, чтобы вычислить среднее значение и исключить 20% выбросов.

= TRIMMEAN (B2: B14; 20%)

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