Как сделать описательную статистику в excel

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

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

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

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

Загрузка и активация пакета анализа

1. Откройте вкладку Файл, нажмите кнопку Параметры и выберите категорию Надстройки.
2. В раскрывающемся списке Управление выберите пункт Надстройки Excel и нажмите кнопку Перейти. Если вы используете Excel для Mac, в строке меню откройте вкладку Средства и в раскрывающемся списке выберите пункт Надстройки для Excel.
3. В диалоговом окне Надстройки установите флажок Пакет анализа, а затем нажмите кнопку ОК.
   • Если Пакет анализа отсутствует в списке поля Доступные надстройки, нажмите кнопку Обзор, чтобы выполнить поиск.
   • Если выводится сообщение о том, что пакет анализа не установлен на компьютере, нажмите кнопку Да, чтобы установить его.

Примечание: Чтобы включить Visual Basic для приложений (VBA) для надстройки «Надстройка анализа», вы можете загрузить надстройку VBA так же, как и надстройку «Надстройка анализа». В поле Доступные надстройки выберите «Надстройка анализа — VBA».

Дисперсионный анализ

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

Однофакторный дисперсионный анализ

Этот инструмент выполняет простой анализ дисперсии данных для двух или более выборок. Анализ дает проверку гипотезы о том, что каждая выборка взята из одного и того же распределения вероятности на основе альтернативной гипотезы о том, что для всех выборок распределение вероятности не одно и то же. Если есть только два примера, можно использовать функцию T.ТЕСТ. В более чем двух примерах нет удобного обобщения T.ВМЕСТОэтого можно использовать модель Anova для одного фактора.

Двухфакторный дисперсионный анализ с повторениями

Этот инструмент анализа применяется, если данные можно систематизировать по двум параметрам. Например, в эксперименте по измерению высоты растений последние обрабатывали удобрениями от различных изготовителей (например, A, B, C) и содержали при различной температуре (например, низкой и высокой). Таким образом, для каждой из 6 возможных пар условий , имеется одинаковый набор наблюдений за ростом растений. С помощью этого дисперсионного анализа можно проверить следующие гипотезы:

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

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

Двухфакторный дисперсионный анализ без повторений

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

Корреляция

Функции CORREL и PEARSON вычисляют коэффициент корреляции между двумя переменными измерения, если для каждой переменной наблюдаемы измерения по каждому из N-объектов. (Любые отсутствующие наблюдения по любой теме вызывают игнорирование в анализе.) Средство анализа корреляции особенно удобно использовать, если для каждого субъекта N имеется более двух переменных измерения. Она содержит выходную таблицу — матрицу корреляции, которая показывает значение CORREL (или PEARSON),примененного к каждой из возможных пар переменных измерения.

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

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

Ковариация

Средства корреляции и коварианс могут использоваться в одном и том же параметре, если у вас есть N различных переменных измерения, наблюдаемые для набора людей. Каждый из инструментов корреляции и ковариции дает выходную таблицу — матрицу, которая показывает коэффициент корреляции или коварианс между каждой парой переменных измерения соответственно. Разница заключается в том, что коэффициенты корреляции масштабироваться в зависимости от -1 и +1 включительно. Соответствующие ковариансы не масштабироваться. Коэффициент корреляции и коварианс — это показатели степени, в которой две переменные «различаются».

Инструмент Ковариана вычисляет значение функции КОВАРИАНА. P для каждой пары переменных измерения. (Прямое использование КОВАРИАНА. P вместо ковариана является разумной альтернативой, если есть только две переменные измерения, то есть N=2.) Запись в диагонали выходной таблицы средства Коварица в строке i, столбце i — коварианс i-й переменной измерения. Это только дисперсия по численности населения для этой переменной, вычисляемая функцией ДИСПЕРС.P.

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

Описательная статистика

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

Экспоненциальное сглаживание

Инструмент анализа «Экспоненциальное сглаживание» применяется для предсказания значения на основе прогноза для предыдущего периода, скорректированного с учетом погрешностей в этом прогнозе. При анализе используется константа сглаживания a, величина которой определяет степень влияния на прогнозы погрешностей в предыдущем прогнозе.

Примечание: Для константы сглаживания наиболее подходящими являются значения от 0,2 до 0,3. Эти значения показывают, что ошибка текущего прогноза установлена на уровне от 20 до 30 процентов ошибки предыдущего прогноза. Более высокие значения константы ускоряют отклик, но могут привести к непредсказуемым выбросам. Низкие значения константы могут привести к большим промежуткам между предсказанными значениями.

Двухвыборочный t-тест для дисперсии

Двухвыборочный F-тест применяется для сравнения дисперсий двух генеральных совокупностей.

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

Анализ Фурье

Инструмент «Анализ Фурье» применяется для решения задач в линейных системах и анализа периодических данных на основе метода быстрого преобразования Фурье (БПФ). Этот инструмент поддерживает также обратные преобразования, при этом инвертирование преобразованных данных возвращает исходные данные.

Гистограмма

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

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

Совет: В Excel 2016 теперь можно создавать гистограммы и диаграммы Парето.

Скользящее среднее

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

• N — число предшествующих периодов, входящих в скользящее среднее;
• Aj — фактическое значение в момент времени j;
• Fj — прогнозируемое значение в момент времени j.

Генерация случайных чисел

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

Ранг и персентиль

Средство анализа Ранг и процентиль создает таблицу, которая содержит порядковую и процентную ранг каждого значения в наборе данных. Можно проанализировать относительное положение значений в наборе данных. В этом средстве используются функции РАНГ. EQ и PERCENTRANK. INC. Если вы хотите учитывать связанные значения, используйте РАНГ. Функция EQ, которая обрабатывает связанные значения как имеющие одинаковый ранг или использует РАНГ.Функция AVG, которая возвращает средний ранг связанных значений.

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

В средстве регрессии используется функция LINEST.

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

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

Парный двухвыборочный t-тест для средних

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

Примечание: Одним из результатов теста является совокупная дисперсия (совокупная мера распределения данных вокруг среднего значения), вычисляемая по следующей формуле:

Двухвыборочный t-тест с одинаковыми дисперсиями

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

Двухвыборочный t-тест с различными дисперсиями

Этот инструмент анализа выполняет t-тест для двух образцов учащихся. В этой форме t-test предполагается, что два набора данных были полученными из распределения с неравными дисперсиями. Его называют гетероскестическими t-тестами. Как и в предыдущем примере с равными дисперсиями, этот t-тест можно использовать для определения вероятности того, что эти две выборки взяты из распределения с равными средствами распределения. Этот тест можно использовать, если в двух примерах есть отдельные объекты. Используйте тест Парный, описанный в примере, если существует один набор тем и две выборки представляют измерения по каждой теме до и после обработки.

Для определения тестовой величины t используется следующая формула.

Следующая формула используется для вычисления степеней свободы (df). Так как результат вычисления обычно не является integer, значение df округлится до ближайшего другого, чтобы получить критическое значение из таблицы t. Функция Excel T .Test использует вычисляемую величину df без округлений, так как можно вычислить значение для T.ТЕСТ с неинтегрированной df. Из-за этих разных подходов к определению степеней свободы результаты T.Тест и этот t-тест будут отличаться в случае неравных дисперсий.

Z-тест. Средство анализа «Две выборки для середины» выполняет два примера z-теста для средств со известными дисперсиями. Этот инструмент используется для проверки гипотезы NULL о том, что между двумя значениями численности населения нет различий между односторонними или двухбокльными альтернативными гипотезами. Если дисперсии не известны, функция Z .Вместо этого следует использовать тест.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Описательная статистика в EXCEL

Задача описательной статистики (descriptive statistics) заключается в том, чтобы с использованием математических инструментов свести сотни значений выборки к нескольким итоговым показателям, которые дают представление о выборке .В качестве таких статистических показателей используются: среднее , медиана , мода , дисперсия, стандартное отклонение и др.

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

Надстройка Пакет анализа

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

СОВЕТ : Подробнее о других инструментах надстройки Пакет анализа и ее подключении – читайте в статье Надстройка Пакет анализа MS EXCEL .

Выборку разместим на листе Пример в файле примера в диапазоне А6:А55 (50 значений).

Примечание : Для удобства написания формул для диапазона А6:А55 создан Именованный диапазон Выборка.

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

После нажатия кнопки ОК будет выведено другое диалоговое окно,

в котором нужно указать:

• входной интервал (Input Range) – это диапазон ячеек, в котором содержится массив данных. Если в указанный диапазон входит текстовый заголовок набора данных, то нужно поставить галочку в поле Метки в первой строке (Labelsinfirstrow). В этом случае заголовок будет выведен в Выходном интервале. Пустые ячейки будут проигнорированы, поэтому нулевые значения необходимо обязательно указывать в ячейках, а не оставлять их пустыми;
• выходной интервал (Output Range). Здесь укажите адрес верхней левой ячейки диапазона, в который будут выведены статистические показатели;
• Итоговая статистика (SummaryStatistics) . Поставьте галочку напротив этого поля – будут выведены основные показатели выборки: среднее, медиана, мода, стандартное отклонение и др.;
• Также можно поставить галочки напротив полей Уровень надежности (ConfidenceLevelforMean) , К-й наименьший (Kth Largest) и К-й наибольший (Kth Smallest).

В результате будут выведены следующие статистические показатели:

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

Если во входном интервале указать ссылку на несколько столбцов данных, то будет рассчитано соответствующее количество наборов показателей. Такой подход позволяет сравнить несколько наборов данных. При сравнении нескольких наборов данных используйте заголовки (включите их во Входной интервал и установите галочку в поле Метки в первой строке ). Если наборы данных разной длины, то это не проблема — пустые ячейки будут проигнорированы.

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

• Интервал (Range) — разница между максимальным и минимальным значениями;
• Минимум (Minimum) – минимальное значение в диапазоне ячеек, указанном во Входном интервале (см. статью про функцию МИН() );
• Максимум (Maximum)– максимальное значение (см. статью про функцию МАКС() );
• Сумма (Sum) – сумма всех значений (см. статью про функцию СУММ() );
• Счет (Count) – количество значений во Входном интервале (пустые ячейки игнорируются, см. статью про функцию СЧЁТ() );
• Наибольший (Kth Largest) – выводится К-й наибольший. Например, 1-й наибольший – это максимальное значение (см. статью про функцию НАИБОЛЬШИЙ() );
• Наименьший (Kth Smallest) – выводится К-й наименьший. Например, 1-й наименьший – это минимальное значение (см. статью про функцию НАИМЕНЬШИЙ() ).

Ниже даны подробные описания остальных показателей.

Среднее выборки

Среднее (mean, average) или выборочное среднее или среднее выборки (sample average) представляет собой арифметическое среднее всех значений массива. В MS EXCEL для вычисления среднего выборки используется функция СРЗНАЧ() . Выборочное среднее является «хорошей» (несмещенной и эффективной) оценкой математического ожидания случайной величины (подробнее см. статью Среднее и Математическое ожидание в MS EXCEL ).

Медиана выборки

Медиана (Median) – это число, которое является серединой множества чисел (в данном случае выборки): половина чисел множества больше, чем медиана , а половина чисел меньше, чем медиана . Для определения медианы необходимо сначала отсортировать множество чисел . Например, медианой для чисел 2, 3, 3, 4 , 5, 7, 10 будет 4.

Если множество содержит четное количество чисел, то вычисляется среднее для двух чисел, находящихся в середине множества. Например, медианой для чисел 2, 3, 3 , 5 , 7, 10 будет 4, т.к. (3+5)/2.

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

Очевидно, что средняя зарплата (71 тыс. руб.) не отражает тот факт, что 86% сотрудников получает не более 30 тыс. руб. (т.е. 86% сотрудников получает зарплату в более, чем в 2 раза меньше средней!). В то же время медиана (15 тыс. руб.) показывает, что как минимум у 50% сотрудников зарплата меньше или равна 15 тыс. руб.

Для определения медианы в MS EXCEL существует одноименная функция МЕДИАНА() , английский вариант — MEDIAN().

Медиану также можно вычислить с помощью формул

Подробнее о медиане см. специальную статью Медиана в MS EXCEL .

СОВЕТ : Подробнее про квартили см. статью, про перцентили (процентили) см. статью.

Мода выборки

Мода (Mode) – это наиболее часто встречающееся (повторяющееся) значение в выборке . Например, в массиве (1; 1; 2 ; 2 ; 2 ; 3; 4; 5) число 2 встречается чаще всего – 3 раза. Значит, число 2 – это мода . Для вычисления моды используется функция МОДА() , английский вариант MODE().

Примечание : Если в массиве нет повторяющихся значений, то функция вернет значение ошибки #Н/Д. Это свойство использовано в статье Есть ли повторы в списке?

Начиная с MS EXCEL 2010 вместо функции МОДА() рекомендуется использовать функцию МОДА.ОДН() , которая является ее полным аналогом. Кроме того, в MS EXCEL 2010 появилась новая функция МОДА.НСК() , которая возвращает несколько наиболее часто повторяющихся значений (если количество их повторов совпадает). НСК – это сокращение от слова НеСКолько.

Например, в массиве (1; 1; 2 ; 2 ; 2 ; 3; 4 ; 4 ; 4 ; 5) числа 2 и 4 встречаются наиболее часто – по 3 раза. Значит, оба числа являются модами . Функции МОДА.ОДН() и МОДА() вернут значение 2, т.к. 2 встречается первым, среди наиболее повторяющихся значений (см. файл примера , лист Мода ).

Чтобы исправить эту несправедливость и была введена функция МОДА.НСК() , которая выводит все моды . Для этого ее нужно ввести как формулу массива .

Как видно из картинки выше, функция МОДА.НСК() вернула все три моды из массива чисел в диапазоне A2:A11 : 1; 3 и 7. Для этого, выделите диапазон C6:C9 , в Строку формул введите формулу =МОДА.НСК(A2:A11) и нажмите CTRL+SHIFT+ENTER . Диапазон C 6: C 9 охватывает 4 ячейки, т.е. количество выделяемых ячеек должно быть больше или равно количеству мод . Если ячеек больше чем м о д, то избыточные ячейки будут заполнены значениями ошибки #Н/Д. Если мода только одна, то все выделенные ячейки будут заполнены значением этой моды .

Теперь вспомним, что мы определили моду для выборки, т.е. для конечного множества значений, взятых из генеральной совокупности . Для непрерывных случайных величин вполне может оказаться, что выборка состоит из массива на подобие этого (0,935; 1,211; 2,430; 3,668; 3,874; …), в котором может не оказаться повторов и функция МОДА() вернет ошибку.

Даже в нашем массиве с модой , которая была определена с помощью надстройки Пакет анализа , творится, что-то не то. Действительно, модой нашего массива значений является число 477, т.к. оно встречается 2 раза, остальные значения не повторяются. Но, если мы посмотрим на гистограмму распределения , построенную для нашего массива, то увидим, что 477 не принадлежит интервалу наиболее часто встречающихся значений (от 150 до 250).

Проблема в том, что мы определили моду как наиболее часто встречающееся значение, а не как наиболее вероятное. Поэтому, моду в учебниках статистики часто определяют не для выборки (массива), а для функции распределения. Например, для логнормального распределения мода (наиболее вероятное значение непрерывной случайной величины х), вычисляется как exp ( m — s 2 ) , где m и s параметры этого распределения.

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

Для того, чтобы получить оценку моды распределения, из генеральной совокупности которого взята выборка , можно, например, построить гистограмму . Оценкой для моды может служить интервал наиболее часто встречающихся значений (самого высокого столбца). Как было сказано выше, в нашем случае это интервал от 150 до 250.

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

Например, в рассмотренном примере о распределении заработных плат (см. раздел статьи выше, о Медиане), модой является число 15 (17 значений из 51, т.е. 33%). В этом случае функция МОДА() дает хорошую оценку «наиболее вероятного» значения зарплаты.

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

О вычислении моды для распределения непрерывной случайной величины читайте статью Мода в MS EXCEL .

Мода и среднее значение

Не смотря на то, что мода – это наиболее вероятное значение случайной величины (вероятность выбрать это значение из Генеральной совокупности максимальна), не следует ожидать, что среднее значение обязательно будет близко к моде .

Примечание : Мода и среднее симметричных распределений совпадает (имеется ввиду симметричность плотности распределения ).

Представим, что мы бросаем некий «неправильный» кубик, у которого на гранях имеются значения (1; 2; 3; 4; 6; 6), т.е. значения 5 нет, а есть вторая 6. Модой является 6, а среднее значение – 3,6666.

Другой пример. Для Логнормального распределения LnN(0;1) мода равна =EXP(m-s2)= EXP(0-1*1)=0,368, а среднее значение 1,649.

Дисперсия выборки

Дисперсия выборки или выборочная дисперсия ( sample variance ) характеризует разброс значений в массиве, отклонение от среднего .

Из формулы №1 видно, что дисперсия выборки это сумма квадратов отклонений каждого значения в массиве от среднего , деленная на размер выборки минус 1.

В MS EXCEL 2007 и более ранних версиях для вычисления дисперсии выборки используется функция ДИСП() . С версии MS EXCEL 2010 рекомендуется использовать ее аналог — функцию ДИСП.В() .

Дисперсию можно также вычислить непосредственно по нижеуказанным формулам (см. файл примера ): =КВАДРОТКЛ(Выборка)/(СЧЁТ(Выборка)-1) =(СУММКВ(Выборка)-СЧЁТ(Выборка)*СРЗНАЧ(Выборка)^2)/ (СЧЁТ(Выборка)-1) – обычная формула =СУММ((Выборка -СРЗНАЧ(Выборка))^2)/ (СЧЁТ(Выборка)-1) – формула массива

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

Чем больше величина дисперсии , тем больше разброс значений в массиве относительно среднего .

Размерность дисперсии соответствует квадрату единицы измерения исходных значений. Например, если значения в выборке представляют собой измерения веса детали (в кг), то размерность дисперсии будет кг 2 . Это бывает сложно интерпретировать, поэтому для характеристики разброса значений чаще используют величину равную квадратному корню из дисперсии – стандартное отклонение .

Стандартное отклонение выборки

Стандартное отклонение выборки (Standard Deviation), как и дисперсия , — это мера того, насколько широко разбросаны значения в выборке относительно их среднего .

По определению, стандартное отклонение равно квадратному корню из дисперсии :

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

Вычислим стандартное отклонение для 2-х выборок : (1; 5; 9) и (1001; 1005; 1009). В обоих случаях, s=4. Очевидно, что отношение величины стандартного отклонения к значениям массива у выборок существенно отличается.

В MS EXCEL 2007 и более ранних версиях для вычисления Стандартного отклонения выборки используется функция СТАНДОТКЛОН() . С версии MS EXCEL 2010 рекомендуется использовать ее аналог СТАНДОТКЛОН.В() .

Стандартное отклонение можно также вычислить непосредственно по нижеуказанным формулам (см. файл примера ): =КОРЕНЬ(КВАДРОТКЛ(Выборка)/(СЧЁТ(Выборка)-1)) =КОРЕНЬ((СУММКВ(Выборка)-СЧЁТ(Выборка)*СРЗНАЧ(Выборка)^2)/(СЧЁТ(Выборка)-1))

Стандартная ошибка

В Пакете анализа под термином стандартная ошибка имеется ввиду Стандартная ошибка среднего (Standard Error of the Mean, SEM). Стандартная ошибка среднего — это оценка стандартного отклонения распределения выборочного среднего .

Примечание : Чтобы разобраться с понятием Стандартная ошибка среднего необходимо прочитать о выборочном распределении (см. статью Статистики, их выборочные распределения и точечные оценки параметров распределений в MS EXCEL ) и статью про Центральную предельную теорему .

Стандартное отклонение распределения выборочного среднего вычисляется по формуле σ/√n, где n — объём выборки, σ — стандартное отклонение исходного распределения, из которого взята выборка . Т.к. обычно стандартное отклонение исходного распределения неизвестно, то в расчетах вместо σ используют ее оценку s — стандартное отклонение выборки . А соответствующая величина s/√n имеет специальное название — Стандартная ошибка среднего. Именно эта величина вычисляется в Пакете анализа.

В MS EXCEL стандартную ошибку среднего можно также вычислить по формуле =СТАНДОТКЛОН.В(Выборка)/ КОРЕНЬ(СЧЁТ(Выборка))

Асимметричность

Асимметричность или коэффициент асимметрии (skewness) характеризует степень несимметричности распределения ( плотности распределения ) относительно его среднего .

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

Примечание : Асимметрия выборки может отличаться расчетного значения асимметрии теоретического распределения. Например, Нормальное распределение является симметричным распределением ( плотность его распределения симметрична относительно среднего ) и, поэтому имеет асимметрию равную 0. Понятно, что при этом значения в выборке из соответствующей генеральной совокупности не обязательно должны располагаться совершенно симметрично относительно среднего . Поэтому, асимметрия выборки , являющейся оценкой асимметрии распределения , может отличаться от 0.

Функция СКОС() , английский вариант SKEW(), возвращает коэффициент асимметрии выборки , являющейся оценкой асимметрии соответствующего распределения, и определяется следующим образом:

где n – размер выборки , s – стандартное отклонение выборки .

В файле примера на листе СКОС приведен расчет коэффициента асимметрии на примере случайной выборки из распределения Вейбулла , которое имеет значительную положительную асимметрию при параметрах распределения W(1,5; 1).

Эксцесс выборки

Эксцесс показывает относительный вес «хвостов» распределения относительно его центральной части.

Для того чтобы определить, что относится к хвостам распределения, а что к его центральной части, можно использовать границы μ +/- σ .

Примечание : Не смотря на старания профессиональных статистиков, в литературе еще попадается определение Эксцесса как меры «остроконечности» (peakedness) или сглаженности распределения. Но, на самом деле, значение Эксцесса ничего не говорит о форме пика распределения.

Согласно определения, Эксцесс равен четвертому стандартизированному моменту:

Для нормального распределения четвертый момент равен 3*σ 4 , следовательно, Эксцесс равен 3. Многие компьютерные программы используют для расчетов не сам Эксцесс , а так называемый Kurtosis excess, который меньше на 3. Т.е. для нормального распределения Kurtosis excess равен 0. Необходимо быть внимательным, т.к. часто не очевидно, какая формула лежит в основе расчетов.

Примечание : Еще большую путаницу вносит перевод этих терминов на русский язык. Термин Kurtosis происходит от греческого слова «изогнутый», «имеющий арку». Так сложилось, что на русский язык оба термина Kurtosis и Kurtosis excess переводятся как Эксцесс (от англ. excess — «излишек»). Например, функция MS EXCEL ЭКСЦЕСС() на самом деле вычисляет Kurtosis excess.

Функция ЭКСЦЕСС() , английский вариант KURT(), вычисляет на основе значений выборки несмещенную оценку эксцесса распределения случайной величины и определяется следующим образом:

Как видно из формулы MS EXCEL использует именно Kurtosis excess, т.е. для выборки из нормального распределения формула вернет близкое к 0 значение.

Если задано менее четырех точек данных, то функция ЭКСЦЕСС() возвращает значение ошибки #ДЕЛ/0!

Вернемся к распределениям случайной величины . Эксцесс (Kurtosis excess) для нормального распределения всегда равен 0, т.е. не зависит от параметров распределения μ и σ. Для большинства других распределений Эксцесс зависит от параметров распределения: см., например, распределение Вейбулла или распределение Пуассона , для котрого Эксцесс = 1/λ.

Уровень надежности

Уровень надежности — означает вероятность того, что доверительный интервал содержит истинное значение оцениваемого параметра распределения.

Вместо термина Уровень надежности часто используется термин Уровень доверия . Про Уровень надежности (Confidence Level for Mean) читайте статью Уровень значимости и уровень надежности в MS EXCEL .

Задав значение Уровня надежности в окне надстройки Пакет анализа , MS EXCEL вычислит половину ширины доверительного интервала для оценки среднего (дисперсия неизвестна) .

Тот же результат можно получить по формуле (см. файл примера ): =ДОВЕРИТ.СТЬЮДЕНТ(1-0,95;s;n) s — стандартное отклонение выборки , n – объем выборки .

Применение описательной статистики в Microsoft Excel

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

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

Использование описательной статистики

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

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

• Медиана;
• Мода;
• Дисперсия;
• Среднее;
• Стандартное отклонение;
• Стандартная ошибка;
• Асимметричность и др.

Рассмотрим, как работает данный инструмент на примере Excel 2010, хотя данный алгоритм применим также в Excel 2007 и в более поздних версиях данной программы.

Подключение «Пакета анализа»

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

1. Переходим во вкладку «Файл». Далее производим перемещение в пункт «Параметры».
2. В активировавшемся окне параметров перемещаемся в подраздел «Надстройки». В самой нижней части окна находится поле «Управление». Нужно в нем переставить переключатель в позицию «Надстройки Excel», если он находится в другом положении. Вслед за этим жмем на кнопку «Перейти…».
3. Запускается окно стандартных надстроек Excel. Около наименования «Пакет анализа» ставим флажок. Затем жмем на кнопку «OK».

После вышеуказанных действий надстройка Пакет анализа будет активирована и станет доступной во вкладке «Данные» Эксель. Теперь мы сможем использовать на практике инструменты описательной статистики.

Применение инструмента «Описательная статистика»

Теперь посмотрим, как инструмент описательная статистика можно применить на практике. Для этих целей используем готовую таблицу.

1. Переходим во вкладку «Данные» и выполняем щелчок по кнопке «Анализ данных», которая размещена на ленте в блоке инструментов «Анализ».
2. Открывается список инструментов, представленных в Пакете анализа. Ищем наименование «Описательная статистика», выделяем его и щелкаем по кнопке «OK».
3. После выполнения данных действий непосредственно запускается окно «Описательная статистика». В поле «Входной интервал» указываем адрес диапазона, который будет подвергаться обработке этим инструментом. Причем указываем его вместе с шапкой таблицы. Для того, чтобы внести нужные нам координаты, устанавливаем курсор в указанное поле. Затем, зажав левую кнопку мыши, выделяем на листе соответствующую табличную область. Как видим, её координаты тут же отобразятся в поле. Так как мы захватили данные вместе с шапкой, то около параметра «Метки в первой строке» следует установить флажок. Тут же выбираем тип группирования, переставив переключатель в позицию «По столбцам» или «По строкам». В нашем случае подходит вариант «По столбцам», но в других случаях, возможно, придется выставить переключатель иначе. Выше мы говорили исключительно о входных данных. Теперь переходим к разбору настроек параметров вывода, которые расположены в этом же окне формирования описательной статистики. Прежде всего, нам нужно определиться, куда именно будут выводиться обработанные данные:
   • Выходной интервал;
   • Новый рабочий лист;
   • Новая рабочая книга.

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

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

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

После того, как все указанные данные внесены, жмем на кнопку «OK».

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

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

• Асимметричность;
• Интервал;
• Минимум;
• Стандартное отклонение;
• Дисперсия выборки;
• Максимум;
• Сумма;
• Эксцесс;
• Среднее;
• Стандартная ошибка;
• Медиана;
• Мода;
• Счет.

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

Как видим, с помощью инструмента «Описательная статистика» можно сразу получить результат по целому ряду критериев, которые в ином случае рассчитывались с применением отдельно предназначенной для каждого расчета функцией, что заняло бы значительное время у пользователя. А так, все эти расчеты можно получить практически в один клик, использовав соответствующий инструмент — Пакета анализа.

31. Пакет анализа ms Excel. Описательная статистика. Гистограммы.

Microsoft Excel предоставляет широкие возможности для анализа статистических данных. При решении простых задач всегда под руками встроенные функции, такие как СРЗНАЧ(), МЕДИАНА() и МОДА(). Если же их оказывается недостаточно, следует обратиться к Пакету анализа. Пакет анализа является дополнением и содержит набор функций и инструментов, расширяющих встроенные аналитические возможности Excel. Пакет анализа позволяет строить гистограммы, делать случайные или периодические выборки данных и находить их статистические характеристики, генерировать неравномерно распределенные случайные числа, проводить регрессионный анализ, выполнять преобразование Фурье и т.д. В статистике совокупность результатов измерений называют распределением. Microsoft Excel дает возможность анализировать распределения, используя встроенные статистические функции, функции анализа выборок и генеральной совокупности, а также инструменты Описательная статистика, Гистограмма, Ранг и Персентиль.

2. Основные встроенные статистические функции

Функция СРЗНАЧ() вычисляет среднее арифметическое или просто среднее для последовательности чисел: суммируются числовые значения в интервале ячеек и результат делится на количество этих значений. Эта функция игнорирует пустые, логические и текстовые ячейки. Функция МЕДИАНА() вычисляет медиану множества чисел. Медиана – это число, являющееся серединой множества: количества чисел, меньшие и большие медианы, равны. Если количество чисел или ячеек четное, то результатом будет среднее двух чисел в середине множества. Функция МОДА() возвращает наиболее часто встречающееся значение во множестве чисел. Функция МАКС() возвращает наибольшее значение среди заданных чисел. Функция МИН() возвращает минимальное значение среди заданных чисел. Функция СУММПРОИЗВ() возвращает сумму произведений соответствующих членов двух и более массивов-аргументов (но не более 30 аргументов). Встречающиеся в аргументах нечисловые значения интерпретируются нулями. Функция СУММКВ() возвращает сумму квадратов аргументов.

3. Анализ выборок и совокупности

Дисперсия и стандартное отклонение – это статистические характеристики распределения наборов или генеральной совокупности данных. Стандартное отклонение определяется как квадратный корень из дисперсии. Как правила, около 68% данных генеральной совокупности с нормальным распределением находится в пределах одного стандартного отклонения и около 95% — в пределах двух стандартных отклонений. При большой величине стандартного отклонения данные широко разбросаны относительно среднего значения, а при маленькой – они группируются близко к среднему значению. Четыре статистические функции ДИСП(), ДИСПР(), СТАНДОТКЛОН() и СТАНДОТКЛОНП() – предназначены для вычисления дисперсии и стандартного отклонения чисел в интервале ячеек. Перед тем как вычислять дисперсию и стандартное отклонение набора данных, нужно определить, представляют ли эти данные генеральную совокупность или выборку из генеральной совокупности. В случае выборки из генеральной совокупности следует использовать функции ДИСП() и СТАНДОТКЛОН(), а в случае генеральной совокупности – функции ДИСПР() и СТАНДОТЛОНП(). Функция СУММСУММКВ() вычисляет сумму сумм квадратов соответствующих элементов в массивах. Функция СУММКВРАЗН() вычисляет сумму квадратов разности соответствующих элементов в массивах.