Как посчитать размах в excel

Инструменты Excel для вычисления числовых характеристик выборки

Процедура «Описательные статистики » пакета «Анализ данных.

В процедуре автоматически вычисляются следующие числовые характеристики выборки:

• среднее – ;
• стандартная ошибка среднего – ;
• медиана – решение уравнения , где – эмпирическая функция распределения;
• мода – чаще всего встречающееся в выборке значение;
• выборочная дисперсия – ;
• стандартное отклонение – ;
• эксцесс – ;
• коэффициент асимметрии – ;
• размах варьирования – ;
• наибольшее значение – ;
• наименьшее значение – ;
• объём выборки – .

Для того чтобы выполнить вычисления, вводим в поле «Водной интервал» адреса ячеек, в которых записаны выборочные значения;

помечаем «Выходной интервал» и вводим в поле адрес первой ячейки, начиная с которой в листе Excel будет отображён резгультат; помечаем «Итоговая статистика»:

Результаты вычислений процедуры представлены в виде таблицы:

Межквартильный диапазон в Excel с примером формулы КВАРТИЛЬ

В Excel функция КВАРТИЛЬ используется для разделения данных на равные доли. Также еще часто используют эту функцию для поиска отстающих показателей, то есть существенно отличающихся от остальных значений в исходных данных.

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

Ниже на рисунке представлен другой список работников с показателями производственных браков на 1000 шт. выпущенной продукции. Допустим нам необходимо узнать, какие работники делают большое и малое количество браков, существенно выходящее за пределы допустимой нормы (отстающие и превышающие ее — так называемые выборсы от медианы), чтобы потом проанализировать их. С целью поиска аномальных отклонений от показателей нормы в данном примере будет использован метод расширенного межквартильного диапазона. Межквартильный диапазон – это просто данные лежащие в среднем диапазоне, который охватывает 50% всего объема данных (находящийся между 75% и 25%). Определение «расширенный» значит, что средний диапазон данных может быть расширен с учетом определенного коэффициента, определяющего его границы. Все значения, лежащие вне границ, воспринимаются как показатели выборсы:

$F$7;»Высоко»;»»))’ >

Для определения значения в среднем диапазоне между 75% и 25% следует воспользоваться функцией КВАРТИЛЬ.ИСКЛ вместе с указанными аргументами 3 и 1 – соответственно. Межквартильным диапазоном является разницей между этими значениями.

В случае нерасширенного межквартильного диапазона с целью определения его нижней границы достаточно всего лишь вычитать значение диапазона от 25%. А для верхней границы, нужно добавить его до 75%. Результатом применения данного метода могло бы получиться слишком большое число для найденных показателей выбросов. Умножая межквартильный диапазон на расширяющий коэффициент (в данном примере равен 1,5) расширяются границы. Таким образом, можно выбрать только особенно экстремальные значения.

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

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

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

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

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

После определения границ используйте формулу со вложенными функциями ЕСЛИ с целью проверки: является ли данное значение большим чем верхнее или ниже от нижнего граничного значения. В случае значительных отклонений показателей (выбросов) от нормы формула со вложенными функциями ЕСЛИ возвращает слово «Выше» или «Ниже», а в случае значения лежащего внутри границ формула возвращает пустую строку («»).

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

Среднее арифметическое, размах и мода

При изучении учебной нагрузки учащихся выделили группу из 12 семиклассников. Их попросили отметить в определенный день время (в минутах), затраченное на выполнение домашнего задания по алгебре. Получили такие данные:

23, 18, 25, 20, 25, 25, 32, 37, 34, 26, 34, 25.

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

Для этого указанные числа надо сложить и сумму разделить на 12:

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

Средним арифметическим ряда чисел называется частное от деления суммы этих чисел на число слагаемых.

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

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

В рассмотренном примере мы нашли, что в среднем учащиеся затратили на выполнение домашнего задания по алгебре по 27 минут. Однако анализ приведенного ряда данных показывает, что время, затраченное некоторыми учащимися, существенно отличается от 27 минут, т.е. от среднего арифметического. Наибольший расход равен 37, а наименьший – 18 минутам. Разность между наибольшим и наименьшим расходом времени составляет 19 минут. В этом случае говорят, что размах ряда равен 19.

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

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

При анализе сведений о времени, затраченном семиклассниками на выполнение домашнего задания по алгебре, нас могут интересовать не только среднее арифметическое и размах полученного ряда данных, но и другие показатели. Интересно, например, знать, какой расход времени является типичным для выделенной группы учащихся, то есть какое число встречается в ряду данных чаще всего. Нетрудно заметить, что таким числом является число 25. говорят, что число 25 – мода рассматриваемого ряда.

Модой ряда чисел называется число, чаще других встречающееся в данном ряду.

Ряд чисел может иметь более одной моды или не иметь моды совсем.

Например, в ряду чисел

47, 46, 50, 52, 47, 52, 49, 45, 43, 53

две моды – это числа 47 и 52,

а в ряду чисел 69, 68, 66, 80, 67, 65, 71, 74, 63, 73, 72 моды нет.

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

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

36, 36, 36, 36, 37, 37, 36, 37, 38, 36, 36, 36, 39, 39, 37, 39, 38, 38, 36, 39, 36.

Найдем для него среднее арифметическое, размах и моду. Для этого удобно предварительно составить из полученных данных упорядоченный ряд чисел, т.е. такой ряд, в котором каждое последующее число не меньше (или не больше) предыдущего. Получим: 35, 35, 36, 36, 36, 36, 36, 36, 36, 36, 37, 37, 37, 37, 38, 38, 38, 39, 39, 39, 39.

Вычислим среднее арифметическое:

Размах ряда равен 39-35=4. Мода данного ряда равна 36, так как число 36 чаще всего встречается в этом ряду.

Итак, средняя выработка рабочих за смену составляет примерно 37 деталей; различие в выработке рабочих не превосходит 4 деталей; типичной является выработка, равная 36 деталям.

Заметим, что среднее арифметическое ряда чисел может не совпадать ни с одним из этих чисел, а мода, если она существует, обязательно совпадает с двумя или более числами ряда. Кроме того, в отличие от среднего арифметического, понятие «мода» относится не только к числовым данным. Например, проведя опрос учащихся, можно получить ряд данных, показывающий, каким видом спорта они предпочитают заниматься, какую из развлекательных телевизионных программ они считают наиболее интересной. Модой будут служить те ответы, которые встретятся чаще всего. Этим и объясняется само название «мода».

1. Найдите среднее арифметическое и размах ряда чисел:

Расчет показателей вариации в Excel

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

Проведение любого статанализа немыслимо без расчетов. И сегодня в рамках рубрики «Работаем в Excel» мы научимся рассчитывать показатели вариации. Теоретическая основа была рассмотрена ранее в ряде статей о вариации данных. Кстати, на этом указанная тема не закончилась, к выпуску планируются новые статьи – следите за рекламой! Однако сухая теория без инструментов реализации – вещь не сильно полезная. Поэтому по мере появления теоретических выкладок, я стараюсь не отставать с заметками о соответствующих расчетах в программе Excel.

Сегодняшняя публикация будет посвящена расчету в Excel следующих показателей вариации:

— максимальное и минимальное значение

— среднее линейное отклонение

— дисперсия (по генеральной совокупности и по выборке)

— среднее квадратическое отклонение (по генеральной совокупности и по выборке)

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

Максимальное и минимальное значение

Начнем с формул максимума и минимума. Что такое максимальное и минимальное значение, уверен, знают почти все. Максимум – самое большое значение из анализируемого набора данных, минимум – самое маленькое (может быть и отрицательным числом). Это крайние значения в совокупности данных, обозначающие границы их вариации. Примеры реального использования каждый может придумать сам – их полно. Это и минимальные/максимальные цены на что-нибудь, и выбор наилучшего или наихудшего решения задачи, и всего, чего угодно. Минимум и максимум – весьма информативные показатели. Давайте теперь их рассчитаем в Excel.

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

Выбираем нужную формулу, в следующем окошке указываем диапазон данных (в котором ищется максимальное или минимальное значение) и жмем «ОК».

Функции МАКС и МИН достаточно часто используются, поэтому разработчики Экселя предусмотрительно добавили соответствующие кнопки в ленту. Они находятся там же, где суммаи среднее значение – в разворачивающемся списке.

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

Среднее линейное отклонение

Среднее линейное отклонение, напоминаю, представляет собой среднее из абсолютных (по модулю) отклонений от средней арифметической в анализируемой совокупности данных. Математическая формула имеет вид:

a – среднее линейное отклонение,

x – анализируемый показатель, с черточкой сверху – среднее значение показателя,

n – количество значений в анализируемой совокупности данных.

В Excel эта функция называется СРОТКЛ.

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

Дисперсия

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

x – анализируемый показатель, с черточкой сверху – среднее значение показателя,

n – количество значений в анализируемой совокупности данных.

Excel также предлагает готовую функцию для расчета генеральной дисперсии ДИСП.Г.

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

Математическая формула выборочной дисперсии имеет вид:

в Excel выборочная дисперсия рассчитывает через функцию ДИСП.В.

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

Среднее квадратическое отклонение

Среднеквадратическое отклонение по генеральной совокупности – это корень из генеральной дисперсии.

Выборочное среднеквадратическое отклонение – это корень из выборочной дисперсии.

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

— Среднеквадратическое отклонение по генеральной совокупности СТАНДОТКЛОН.Г

— Среднеквадратическое отклонение по выборке СТАНДОТКЛОН.В.

С названием этого показателя может возникнуть путаница, т.к. часто можно встретить синоним «стандартное отклонение». Пугаться не нужно – смысл тот же.

Далее, как обычно, указываем нужный диапазон и нажимаем на «ОК». Среднее квадратическое отклонение имеет те же единицы измерения, что и анализируемый показатель, поэтому является сопоставимым с исходными данными. Об этом ниже.

Коэффициент вариации

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

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

В скобках должен быть указан диапазон данных. При необходимости используется среднее квадратическое отклонение по выборке (СТАНДОТКЛОН.В).

Коэффициент вариации обычно выражается в процентах, поэтому ячейку с формулой можно обрамить процентным форматом. Нужная кнопка находится на ленте на закладке «Главная»:

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

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

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

Легкой работы в Excel и до встречи на блоге statanaliz.info.