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

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

Межквартильный диапазон данных - это диапазон, охватываемый "рамкой" на графике "ящик и усы" или более именно результат вычитания значения первого квартиля данных из значения третьего квартиля. квартиль. Excel содержит встроенную функцию для расчета любого квартиля для ваших данных. Найдите свободную ячейку и введите «= КВАРТИЛЬ ([диапазон данных], [номер квартиля])» и введите диапазон ячеек. для ваших данных, где указано «[диапазон данных]», и желаемый квартиль, где указано «[квартиль номер]."

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

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

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

Если значение первого квартиля находится в C2, значение третьего квартиля находится в ячейке D2, а межквартильный диапазон равен в ячейке E2 введите «= C2- (1,5 * E2)», чтобы найти нижний предел, и «= D2 + (1,5 * E2)», чтобы найти верхний предел. предел. Как правило, вы вводите «= [первый квартиль] - (1,5 * [межквартильный размах])», чтобы найти нижний предел, и «= [третий квартиль] + (1,5 * [межквартильный диапазон])», чтобы найти верхний предел.

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

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

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

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

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

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