Как отыскать выбросы в Excel (и как с ними совладать)

Как отыскать выбросы в Excel (и как с ними совладать)

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

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

Что такое выбросы и почему их принципиально отыскать?

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

Приведу обычной пример.

Допустим, 30 человек движутся на автобусе из пт предназначения А в пункт предназначения Б. Все люди относятся к одной весовой группе и группе доходов. Для целей этого управления давайте разглядим, что средний вес составляет 220 фунтов, а средний годичный доход — 70 000 баксов.

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

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

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

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

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

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

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

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

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

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

Разрешите мне показать для вас пример.

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

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

• Изберите заголовок столбца, который вы желаете отсортировать (в этом примере ячейка B1).
• Перейдите на вкладку «Основная»
• В группе «Редактирование» щелкните значок «Сортировка и фильтр».
• Щелкните Custom Sort (Пользовательская сортировка).
• В диалоговом окне «Сортировка» изберите «Длительность» в раскрывающемся перечне «Сортировка по» и «От большего к меньшему» в раскрывающемся перечне «Порядок».
• Нажмите ОК

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

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

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

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

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

В статистике квартиль составляет четверть набора данных. К примеру, если у вас есть 12 точек данных, то 1-ый квартиль будет 3-мя нижними точками данных, 2-ой квартиль будет последующими 3-мя точками данных и так дальше.

Ниже приведен набор данных, по которому я желаю отыскать выбросы. Для этого мне необходимо будет вычислить 1-й и 3-й квартили, а потом с его помощью вычислить верхний и нижний предел.

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

и вот тот, который вычисляет 3-ий квартиль в ячейке E3:

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

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

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

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

который в нашем примере становится:

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

который в нашем примере становится:

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

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

Я употреблял приведенную ниже формулу ИЛИ, чтоб получить ИСТИНА для тех значений, которые являются выбросами.

Сейчас вы сможете фильтровать столбец Outlier и показывать лишь те записи, для которых значение TRUE.

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

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

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

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

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

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

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

Буквально так же 2-ое по величине значение будет равно

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

Буквально так же, если для вас необходимы 5 меньших значений, используйте последующую формулу:

либо последующее, если у вас нет динамических массивов:

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

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

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

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

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

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

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

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

Восстановить выбросы (отрегулировать значение)

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

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

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

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

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

Соответствие нескольким аспектам Excel

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

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

Как соответствовать нескольким аспектам Excel? (Примеры)

Пример # 1 — Логические функции в Excel

Обычно мы используем три логические функции в Excel для выполнения вычислений нескольких критериев, и эти три функции являются условиями ЕСЛИ, И и ИЛИ.

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

Поглядите на формулу ниже; мы проверили, составляет ли значение ячейки A1> 20 либо нет, значение ячейки B1> 20 либо нет, значение ячейки C1> 20 либо нет.

В приведенной выше функции все значения ячеек> 20, потому итог ИСТИНА. Сейчас я изменю одно из значений ячейки на наименее 20.

Значение ячейки B1 изменено на 18, что меньше 20, потому конечный итог — ЛОЖЬ, даже если значения 2-ух остальных ячеек> 20.

Сейчас для такого же числа я применим функцию ИЛИ в excel.

Для функции ИЛИ хоть какой аспект должен удовлетворять, чтоб получить итог ИСТИНА; это не похоже на функцию И, где нам необходимы все условия, чтоб получить ИСТИННЫЙ итог.

Пример # 2 — Несколько логических функций в Excel

Сейчас мы увидим, как делать несколько логических функций для соответствия нескольким условиям.

• Поглядите на данные ниже в Excel.

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

Если срок службы> 1,5 года и если «Dept.» это «Реализации» либо «Поддержка», тогда приз составляет 8000 баксов США (Соединённые Штаты Америки — государство в Северной Америке); в неприятном случае приз составит 4000 баксов.

Итак, чтоб получить один итог, нам необходимо соответствовать нескольким аспектам в Excel. В этом случае нам необходимо проверить, составляет ли год обслуживания 1,5 года, также проверить, является ли отдел «продажами» либо «поддержкой», чтоб получить сумму приза.

• 1-ое открытое состояние в Excel.

• 1-ое логическое условие для проверки является неотклонимым, независимо от того, составляет ли год службы наиболее 1,5 лет либо нет. Потому для получения неотклонимого ИСТИННОГО результата нам необходимо употреблять функцию И.

Вышеупомянутый логический тест возвратит ИСТИНА, лишь если год службы> 1,5; по другому в итоге мы получим ЛОЖЬ. Снутри данной функции AND нам необходимо проверить очередной аспект, другими словами, является ли отдел «Продажами» либо «Поддержкой», чтоб получить сумму приза.

• В этом случае, если хоть какой отдел соответствует сиим аспектам, нам нужен ИСТИННЫЙ итог, потому нам необходимо употреблять функцию ИЛИ.

Как вы сможете созидать выше, мы употребляли функции И и ИЛИ для соответствия единым аспектам. Функция И инспектирует, что год обслуживания превосходит 1,5 года, а функция ИЛИ инспектирует, является ли отдел продажей либо службой саппорта. Таковым образом, если Год обслуживания равен 1,5, а отдел — или Реализации, или Поддержка, логический итог теста будет ИСТИНА либо ЛОЖЬ.

• Сейчас, если логический тест ИСТИНА, то нам нужна сумма приза в размере 8000 баксов. Итак, для Value, если аргумент True, нам необходимо указать # 8000.

• Если логический тест — ЛОЖЬ, то нам нужна сумма приза в размере 4000 баксов.

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

Сейчас поглядим на 4 th строчка; в этом случае Год службы равен 2,4, а отдел — «Поддержка», потому приз добивается 8000 баксов. Таковым образом, мы можем сравнить несколько критериев в Excel.