Как найти выбросы в Excel (и как с ними справиться)

При работе с данными в Excel у вас часто возникают проблемы с обработкой выбросов в наборе данных.

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

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

Что такое выбросы и почему их важно найти?

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

Простой пример

Допустим, 30 человек едут на автобусе из пункта A в пункт B. Все люди относятся к одной весовой группе и группе доходов: средний вес — 220 фунтов, средний годовой доход — 70 000 долларов.

Где-то посередине маршрута автобус останавливается, и в него садится Билл Гейтс.

Как вы думаете, как это повлияет на средний вес и средний доход людей в автобусе?

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

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

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

Теперь давайте рассмотрим несколько способов найти выбросы в Excel.

Найдите выбросы путём сортировки данных

Быстрый способ: с небольшими наборами данных быстрый способ определить выбросы — просто отсортировать данные и вручную просмотреть некоторые значения в верхней части отсортированных данных.

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

Ниже у меня есть набор данных, в котором указана продолжительность звонков (в секундах) для 15 звонков в службу поддержки.

Ниже приведены шаги по сортировке этих данных, чтобы мы могли идентифицировать выбросы в наборе данных:

Выберите заголовок столбца, который вы хотите отсортировать (в этом примере ячейка B1).

Перейдите на вкладку «Главная».

В группе «Редактирование» щёлкните значок «Сортировка и фильтр».

Щёлкните «Пользовательская сортировка».

В диалоговом окне «Сортировка» выберите «Продолжительность» в раскрывающемся списке «Сортировка по» и «От наибольшего к наименьшему» в списке «Порядок».

Нажмите ОК.

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

В нашем примере видно, что первые два значения намного выше остальных значений (а два нижних намного ниже).

Примечание: Этот метод хорошо работает с небольшими наборами данных, где вы можете вручную сканировать данные. Это не научный метод, но он хорошо работает.

Поиск выбросов с помощью квартильных функций

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

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

Ниже приведена формула для вычисления первого квартиля в ячейке E2:

=КВАРТИЛЬ.ВКЛ($B$2:$B$15;1)

А вот формула для вычисления третьего квартиля в ячейке E3:

=КВАРТИЛЬ.ВКЛ($B$2:$B$15;3)

Теперь можно использовать два вышеупомянутых значения, чтобы получить межквартильный размах (50% данных в пределах 1-го и 3-го квартилей):

=F3-F2

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

Формула для расчёта нижнего предела:

=Квартиль1 - 1,5*(Межквартильный диапазон), то есть =F2-1,5*F4

И формула для расчёта верхнего предела:

=Квартиль3 + 1,5*(Межквартильный диапазон), то есть =F3+1,5*F4

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

Быстрый способ сделать это — проверить каждое значение и вернуть ИСТИНА или ЛОЖЬ в новом столбце с помощью формулы ИЛИ:

=ИЛИ(B2<$F$5;B2>$F$6)

Теперь вы можете отфильтровать столбец «Выброс» и отображать только те записи, для которых значение ИСТИНА. Кроме того, вы также можете использовать условное форматирование, чтобы выделить все ячейки со значением ИСТИНА.

💡 Ограничение метода: Хотя это более распространённый метод поиска выбросов в статистике, он немного непригоден для реальных сценариев. Например, если нижний предел, рассчитанный по формуле, отрицательный, а набор данных может быть только положительным, метод поможет найти выбросы лишь в одном направлении (высокие значения).

Поиск выбросов с помощью функций НАИБОЛЬШИЙ / НАИМЕНЬШИЙ

Если вы работаете с большим количеством данных (значения в нескольких столбцах), вы можете извлечь 5 или 7 наибольших и наименьших значений и посмотреть, есть ли среди них выбросы.

Если есть какие-либо выбросы, вы сможете их идентифицировать, не просматривая все данные в обоих направлениях.

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

=НАИБОЛЬШИЙ($B$2:$B$16;1)

Если вы не используете Microsoft 365 с динамическими массивами, можно использовать формулу, которая сразу даёт пять наибольших значений:

=НАИБОЛЬШИЙ($B$2:$B$16;СТРОКА($1:5))

Точно так же, если вам нужны 5 наименьших значений, используйте:

=НАИМЕНЬШИЙ($B$2:$B$16;СТРОКА($1:5))

Когда у вас есть эти значения, очень легко обнаружить любые выбросы в наборе данных. Хотя я решил извлечь 5 наибольших и наименьших значений, вы можете выбрать 7 или 10 в зависимости от размера вашего набора данных.

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

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

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

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

Удалить выбросы

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

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

Нормализовать выбросы (скорректировать значение)

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

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

Например, если вы анализируете маржу чистой прибыли компаний, где большинство компаний находится в пределах от -10% до 30%, а есть несколько значений выше 100%, можно просто заменить эти выбросы на 30% или 35%.

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

Надеюсь, вы нашли этот урок полезным.

Хотите освоить Excel глубже?

Больше полезных формул, приёмов и готовых решений — на Vip Excel