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

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

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

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

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

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

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

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

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

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

Шаг первый: вычислите квартили

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

Excel предоставляет функцию QUARTILE для расчета квартилей. Требуется две части информации: массив и кварт.

= QUARTILE (массив, кварт)

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

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

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

Для расчета 1^улица В квартиле мы можем использовать следующую формулу в ячейке F2.

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

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

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

= КВАРТИЛЬ (В2: 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:

= ИЛИ (В2 $ F $ 6)

Затем мы скопируем это значение в наши ячейки C3-C14. Значение TRUE указывает на выброс, и, как вы можете видеть, у нас есть два в наших данных.

Игнорирование выбросов при расчете среднего значения

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

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

= TRIMMEAN (массив, проценты)

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

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

= TRIMMEAN (B2: B14, 20%)

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