Вычислить выбросы

Использование формулы STDEV в Google Таблицах для расчета стандартного отклонения

Теперь позвольте мне показать вам несколько примеров вычисления стандартного отклонения в Google Таблицах с использованием формулы STDEV.

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

Вы можете использовать следующую формулу:

= СТАНДОТКЛОН (B2: B6)

Ниже приведен результат этой формулы:

Есть еще несколько способов использования формулы STDEV в Google Таблицах.

Вы можете вручную ввести значения в формулу, как показано ниже:

= СТАНДОТКЛОН (67,83,89,95,77)

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

Вы также можете использовать ссылку на отдельную ячейку вместо жесткого кодирования значений в формуле, как показано ниже:

= СТАНДОТКЛОН (B2; B3; B4; B5; B6)

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

Вы также можете комбинировать диапазон и ссылку на ячейку (или значение). Например, вы также можете использовать следующую формулу:

= СТАНДОТКЛОН (B2: B5; B6)

Что такое выбросы?

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

Это редко, или отчетливо, или не подходит в некотором роде.

Выбросы могут иметь много причин, таких как:

• Ошибка измерения или ввода.
• Повреждение данных.
• Истинное наблюдение за выбросами (например, Майкл Джордан в баскетболе).

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

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

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

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

Как использовать функцию выбросов в Excel — Вокруг-Дом — 2021

Table of Contents:

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

кредит: nd3000 / iStock / GettyImages

Расчет межквартильного диапазона

Межквартильный диапазон данных — это диапазон, охватываемый «квадратом» на графике «квадрат и ус», или, точнее, результат вычитания значения для первого квартиля из данных третьего квартиля. Excel содержит встроенную функцию для расчета любого квартиля для ваших данных. Найдите запасную ячейку и введите «= QUARTILE (диапазон данных, число квартилей)» и поместите диапазон ячеек для ваших данных, где указано «диапазон данных», и нужный квартиль, где говорится «квартиль» число.»

Например, если у вас есть данные в ячейках с A2 по A101, и вы хотите получить значение для первого квартиля, введите «= QUARTILE (A2: A101, 1)», чтобы найти это значение. Для первой части аргумента вы можете выделить соответствующие ячейки мышью, но после запятой вам нужно указать желаемое число квартилей. Для третьего квартиля с теми же данными введите «= QUARTILE (A2: A101, 3)», чтобы получить результат.

Используя другую пустую ячейку, вычтите значение первой ячейки квартиля из значения третьей ячейки квартиля. Если первый квартиль находится в ячейке C2, а третий квартиль находится в ячейке D2, введите «= D2-C2», чтобы получить результат. Это межквартильный размах.

Анализ выбросов в Excel

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

Если первое квартильное значение находится в C2, третье квартильное значение находится в ячейке D2, а межквартильный диапазон находится в ячейке E2, вы должны набрать «= C2- (1.5 E2) «найти нижний предел» и = D2 + (1,5 E2) «чтобы найти верхний предел. В общем, вы вводите» = первый квартиль — (1,5 межквартильный диапазон) «найти нижний предел и» = третий квартиль + (1,5 interquartile range) «, чтобы найти верхний предел.

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

Чтобы завершить тест выбросов в Excel, используйте логическую функцию «ИЛИ», чтобы определить, какие значения в вашем классе данных являются выбросами эффективным способом. Введите «= ИЛИ (ячейка данных> верхний предел, ячейка данных $ F $ 2, A2

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

Вы также можете выделить данные и перейти к «Условному форматированию» в разделе «Стили» на вкладке «Главная», если вы хотите изменить форматирование выбросов. Выберите «Новое правило» и выделите опцию «Использовать формулу, чтобы определить, какие ячейки форматировать». Введите ту же формулу, что и в предыдущем абзаце, а затем нажмите параметр «Формат», чтобы выбрать уникальное форматирование для выбросов.

Использование Trimmean

Функция «Trimmean» — это более простой способ определения выбросов. Введите «= TRIMMEAN (диапазон данных, пропорция к обрезке)», чтобы использовать функцию, с диапазоном ячеек, содержащим данные вместо «диапазон данных» и десятичным процентом, который вы хотите обрезать, где он говорит « пропорция к отделке. » Это удаляет экстремальные значения сверху и снизу, а затем вычисляет среднее значение на основе оставшихся. Таким образом, если вы урежете 10 процентов, то перед вычислением среднего значения будут удалены верхние 5 процентов и нижние 5 процентов.

Если данные идут от A2 до A101, и вы хотите обрезать крайние 5 процентов значений, введите «= TRIMMEAN (A2: A101, 0,05)», чтобы найти скорректированное среднее значение. Вы можете обрезать 15 процентов, написав «= TRIMMEAN (A2: A101, 0.15)».

Выбросы и шумные данные

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

Шум должен быть удален при применении обнаружения выбросов. Это может искажать нормальные объекты и размывать различия между нормальными объектами и выбросами. Это может помочь скрыть выбросы и снизить эффективность обнаружения выбросов. Например, если пользователь рассматривает возможность покупки более дорогого обеда, который он обычно покупал, такое поведение следует рассматривать как «шумовые транзакции», такие как «случайные ошибки» или «дисперсия».

Типы выбросов

В общем, выбросы можно классифицировать на три категории: глобальные выбросы, контекстные (или условные) выбросы и коллективные выбросы.

• Глобальный выброс — объект значительно отклоняется от остальной части набора данных
• Контекстуальные выбросы — объект значительно отклоняется в зависимости от выбранного контекста. Например, 28 ° C является выбросом для московской зимы, но не является выбросом в другом контексте, 28 ° C не является выбросом для московского лета.
• Коллективный выброс — Подмножество объектов данных в совокупности значительно отличается от всего набора данных, даже если отдельные объекты данных могут не быть выбросами. Например, большой набор транзакций одной и той же акции среди небольшой стороны за короткий период может рассматриваться как свидетельство манипулирования рынком.

Коллективный выброс.

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

Методы обнаружения выбросов

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

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

Моделирование выбросов

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

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

В своей книгеАнализ выбросов(партнерская ссылка), Aggarwal предоставляет полезную таксономию методов обнаружения выбросов:

• Анализ экстремальных значений: Определить статистические хвосты базового распределения данных. Например, статистические методы, такие как z-оценки на одномерных данных.
• Вероятностные и статистические модели: Определить маловероятные случаи из вероятностной модели данных. Например, модели гауссовой смеси оптимизированы с использованием максимизации ожидания.
• Линейные модели: Проекционные методы, которые моделируют данные в более низкие измерения с использованием линейных корреляций. Например, анализ основных компонентов и данные с большими остаточными ошибками могут быть выбросами.
• Модели на основе близости: Экземпляры данных, которые изолированы от массы данных, как определено кластером, плотностью или анализом ближайшего соседа.
• Информационно-теоретические модели: Выбросы обнаруживаются как экземпляры данных, которые увеличивают сложность (минимальную длину кода) набора данных.
• Обнаружение крупномасштабных выбросов: Методы, которые ищут подпространства для выбросов, дают разбивку мер на основе расстояния в более высоких измерениях (проклятие размерности).

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

В его предисловии кРуководство по добыче и изучению данных(партнерская ссылка), Ирад Бен-Гал предлагает таксономию моделей выбросов как одномерных или многомерных, так и параметрических и непараметрических. Это полезный способ структурировать методы на основе того, что известно о данных. Например:

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

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

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

Приведу простой пример.

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

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

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

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

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

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

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

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

Выбор расчетной сетки

Методы № 273 определяют зону влияния выбросов по изолинии в 0,05 предельно допустимой максимально разовой концентрации (далее — ПДК).

Извлечение
из Методов № 273

8.9. Для совокупности источников выбросов отдельных предприятий рассчитываются зоны влияния, включающие в себя круги радиусом х₁ равным 10·х_m, проведенные вокруг каждого из основных источников выброса (труб или других источников) предприятия, и участки местности, где рассчитанная по формуле (49) суммарная концентрация ЗВ от всей совокупности источников данного предприятия, включая источники низких и неорганизованных выбросов, превышает 0,05·ПДК_м.р..

Методика № 581 уточняет требования для определения зоны влияния выбросов в части выбора максимальной зоны влияния по наиболее жесткому нормативу качества атмосферного воздуха.

Извлечение
из Методики № 581

26. Зона влияния выбросов конкретного загрязняющего вещества (далее — j-ое загрязняющее вещество) определяется как территория, ограниченная замкнутой линией, вне которой для любой точки местности в течение всего времени выбросов j-го загрязняющего вещества выполняется условие (1):
q_{пр, j} < 0,05 ПДК_j, (1)
где q_{пр, j} — значение приземной концентрации j-го загрязняющего вещества в атмосферном воздухе (C_{пр, j}), создаваемой выбросами стационарных источников рассматриваемого объекта ОНВ, в долях ПДК_j;
ПДКj (мг/м3) — предельно допустимая концентрация (далее — ПДК) рассматриваемого j-го загрязняющего вещества в атмосферном воздухе, соответствующая наиболее жесткому нормативу качества атмосферного воздуха из числа указанных в пункте 14 настоящей методики.

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

Бытует мнение, что для тех загрязняющих веществ, для которых максимальная концентрация при первичных расчетах составила менее 0,05 ПДК, зона влияния не образуется, соответственно, такое загрязняющее вещество в детальных расчетах рассеивания не участвует.

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

Методика разработки (расчета) и установления нормативов допустимых выбросов загрязняющих веществ в атмосферный воздух (далее — Методика № 581) утверждена Приказом Минприроды России от 11.08.2020 № 581.

Методы расчетов рассеивания выбросов вредных (загрязняющих) веществ в атмосферном воздухе (далее — Методы № 273) утверждены Приказом Минприроды России от 06.06.2017 № 273.

Объект, оказывающий негативное воздействие на окружающую среду.

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

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

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

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

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

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

Если вы разделите свои данные на кварталы, каждый из этих наборов называется квартилем. Самые низкие 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 предоставляет список параметров для аргумента кварта.

Чтобы вычислить третий квартиль, мы можем ввести формулу, аналогичную предыдущей, в ячейку 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 указывает на выброс, и, как вы можете видеть, у нас есть два в наших данных.

Начать

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

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

Анализ экстремальных значений

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

• Сосредоточьтесь на одномерных методах
• Визуализируйте данные с помощью диаграмм рассеяния, гистограмм и диаграмм блоков и усов и ищите экстремальные значения
• Предположим распределение (по Гауссу) и ищем значения более 2 или 3 стандартных отклонений от среднего значения или в 1,5 раза от первого или третьего квартиля
• Отфильтруйте кандидата от выбросов из учебного набора данных и оцените производительность ваших моделей

Методы близости

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

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

Методы проекции

Методы проекции относительно просты в применении и быстро выделяют посторонние значения.

• Используйте методы проекции, чтобы обобщить ваши данные в двух измерениях (например, PCA, SOM или отображение Саммона)
• Визуализируйте отображение и идентифицируйте выбросы вручную
• Используйте меры близости от прогнозируемых значений или векторов кодовой книги для определения выбросов
• Отфильтруйте кандидата от выбросов из учебного набора данных и оцените производительность ваших моделей

Методы, устойчивые к выбросам

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

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