Как определить выбросы в выборке excel

Foodband

Удаление точек выбросов из графика

Пример работы программы

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

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

Для быстрого удаления этих точек и была написана данная программа.

Интерфейс ее прост и понятен:

1. Указываете номера столбцов с данными X и Y (начиная счет со столбца A).

2. Вводите количество наблюдений или нажимаете кнопку заполнить автоматически.

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

3. Ниже указывается тип зависимости, которую Вы хотите получить (Экспоненциальная, Логарифмическая или Линейная).

4. Задаются две пары точек, лежащие на искомом графике.

5. Указывается коэффициент корреляции (значение меньшее 1, указывается через запятую).

6. Нажимается кнопка расчет. Для удаления точек-выбрасов.

7. Кнопка отмена возвращает удаленные точки на график.

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

Foodband

Установка

  • Убедитесь, что в Вашей программе Excel включены макросы. Как это сделать описано в этой статье.
  • Для однократного запуска программы просто откройте скаченный файл надстройки «Точки выбросов».
  • Как установить автоматический запуск надстройки при загрузке Excel рассказывается тут.

После установки надстройки на ленте меню появится новая вкладка «Выбросы», на которой расположена кнопка запуска программы «Удаление выбросов».

Изменение функционала

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

Как использовать функцию выбросов в 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)".

Foodband

Ссылка на основную публикацию