Описательная статистика ошибка при настройке входной или выходной ссылки

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

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

Эта функция можем высчитывать большинство критериев, среди которых:

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

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

Для этого нажмем на вкладку «Файл» — «Параметры».

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

В новом окне ставим галочку напротив «Пакет анализа» и применяем операцию.

Весь функционал «Пакета анализа» был добавлен в рабочую область и появился во вкладке «Данные». Приступим непосредственно к «Описательной статистике» и попробуем на практике данный инструмент.

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

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

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

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

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

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

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

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

Рассмотрим работу данного инструмента на примере задачи 4.2.

Переходим во вкладку «Данные» и выполняем щелчок по кнопке «Анализ данных», которая размещена на ленте в блоке инструментов «Анализ». Открывается список инструментов, представленных в Пакете анализа. Ищем наименование «Описательная статистика», выделяем его и щелкаем по кнопке «OK» (рис. 4.1).

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

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

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

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

• Выходной интервал;
• Новый рабочий лист;
• Новая рабочая книга.

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

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

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

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

Среди множества показателей Описательной статистики есть те, которые нас интересуют, они выделены цветом (рис. 4.3).

ВОПРОСЫ И УПРАЖНЕНИЯ

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

2. Что характеризует выборочная дисперсия.

3. Вычислите для множества: 22, 15, 16, 21, 24, 24, 27, 28, 30, 30, 31, 31, 31, 34, 36 размах, дисперсию, стандартное отклонение.

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

5. Выборочные дисперсии результатов контрольной работы в классе 7«А» и 7«Б» соответственно равны 0,44 и 1,38. Какой вывод можно сделать при сравнении результатов контрольной работы в двух классах?

6. Дисперсия каждой из групп A и В равна 5. Будет ли дисперсия 10 значений, полученных путем объединения групп, меньше, больше или равна 5?

Группа А: 13, 11, 10, 9, 7

Группа В: 28, 26, 25, 24, 22

Лабораторная работа №2

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

Этапы обработки данных:

1. Занести данные в таблицу Excel (две выборки).

2. Упорядочить данные (по возрастанию) в каждой выборке.

3. Рассчитать моду, медиану и среднее.

4. Посчитать дисперсию, стандартное отклонение.

5. Посчитать коэффициент вариации.

6. Сделать сравнительный анализ, полученных результатов.

Задания для вариантов 1 – 5

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

Дать сравнительную характеристику степени выраженности этого свойства в данных группах.

Вариант 1.

Вариант 2

Вариант 3.

Задания для вариантов 6 – 10

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

Задания для вариантов 11 – 15

Для проверки эффективности новой развивающей программы были созданы две группы детей шестилетнего возраста. На первом этапе дети обеих групп были протестированы по методике Керна-Йерасика (школьная зрелость). Результаты тестирования по невербальной шкале занесены в таблицу. Сделать сравнительный анализ школьной зрелости детей этих групп.

Задания для вариантов 16 – 20

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

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

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

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

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

Откройте вкладку Файл, нажмите кнопку Параметры и выберите категорию Надстройки.

Если вы используете Excel 2007, нажмите Microsoft Office кнопку и выберите «Параметры Excel»

В раскрывающемся списке Управление выберите пункт Надстройки Excel и нажмите кнопку Перейти.

Если вы используете Excel для Mac, в строке меню откройте вкладку Средства и в раскрывающемся списке выберите пункт Надстройки для Excel.

В диалоговом окне Надстройки установите флажок Пакет анализа, а затем нажмите кнопку ОК.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

С помощью этого инструмента вычисляется значение f F-статистики (или F-коэффициент). Значение f, близкое к 1, показывает, что дисперсии генеральной совокупности равны. В таблице результатов, если f 1, «P(F

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

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

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

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

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

N — число предшествующих периодов, входящих в скользящее среднее;

A j — фактическое значение в момент времени j;

F j — прогнозируемое значение в момент времени j.

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

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

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

Инструмент «Регрессия» использует функцию LINEST.

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

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

Для всех трех средств, перечисленных ниже, значение t вычисляется и отображается как «t-статистика» в выводимой таблице. В зависимости от данных это значение t может быть отрицательным или неотрицательным. Если предположить, что средние генеральной совокупности равны, при t =0 «P(T Парный двухвыборочный t-тест для средних

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

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

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

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

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

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

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

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

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

При использовании этого инструмента следует внимательно просматривать результат. «P(Z = ABS(z)), вероятность z-значения, удаленного от 0 в том же направлении, что и наблюдаемое z-значение при одинаковых средних значениях генеральной совокупности. «P(Z = ABS(z) или Z

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

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

ОПИСАТЕЛЬНАЯ СТАТИСТИКА

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

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

Рассмотрим работу с описательной статистикой на примере.

Пример 4.1. Имеются некоторые данные о стоимости новогодних туров (рис. 4.2). Каждый из столбцов можно рассматривать как отдельный признак или переменную. Требуется провести анализ данных о продолжительности туров.

Таблица исходных данных

Исходные данные содержат несколько переменных, характеризующих тур. «Название фирмы», «Страна», «Транспорт» — качественные переменные, которые относятся к номинальной шкале. «Отель» —качественная переменная, которую можно отнести к порядковой шкале, так как количество звездочек отражает уровень обслуживания в отеле. «Количество дней» и «Стоимость» —количественные данные, которые относятся к метрической шкале.

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

Для перехода к описательной статистике выполните: «Данные» —» «Анализ» —> «Анализ данных» —» «Описательная статистика» -> «Ок». В открывшемся диалоговом окне «Описательная статистика» (рис. 4.3) укажите «Входной интервал», диапазон В2:Б16, выберите «Труп-

Диалоговое окно «Описательной статистики»

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

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

«Описательная статистика» вычисляет 16 значений, из них 13 относятся к «Итоговой статистике», еще три определяют доверительный интервал и два выборочных значения.

Отметим главное — «Описательная статистка» надстройки «Анализ данных» предназначена для вычислений статистических характеристик, или статистик, одномерной выборки или нескольких выборок.

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

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

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

Таким образом, практически все расчеты «Описательной статистики» дублируются «Статистическими» функциями. Остальные характеристики можно посчитать, используя формулы. Для того чтобы на рабочем листе Excel отобразились не результаты, а формулы, следует выполнить: «Формулы» -» «Зависимости формул» -» «Показать формулы».

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

«Итоговая статистика» и «Статистические функции»

не изменяются. Чтобы обновить результаты, потребуется вызывать «Анализ данных» снова.

Числовые характеристики «Итоговой статистики» описывают средние, вариацию и форму распределения, всего 13 параметров:

• • среднее, или выборочное среднее, вычисляется как среднее арифметическое наблюдаемых значений выборки;
• • медиана определяется как значение, находящееся в середине распределения, полученного из исходного путем упорядочивания по возрастанию;
• • мода равна наиболее часто встречающемуся значению. Кроме того, выделяют две величины, характеризующие изменчивость, или разброс, значений распределения относительно среднего:
  • 1) дисперсию выборки, или выборочную дисперсию, равную сумме квадратов отклонений каждого значения от среднего, деленной на (А — 1), где N — число значений в распределении, или объем выборки;

2) стандартное отклонение, или выборочное среднеквадратическое отклонение, равное квадратному корню из выборочной дисперсии.

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

• • минимум равен наименьшему из выборочных значений;
• • максимум равен наибольшему из выборочных значений;
• • интервал составляет разность между максимумом и

минимумом, этот параметр называют также размахом.

Если набор данных рассматривается как множество

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

Для отражения близости формы распределения к нормальному виду существует две основные характеристики:

• 1) эксцесс, или выборочный коэффициент эксцесса, который является мерой «сглаженности» распределения;
• 2) асимметричность, или выборочный коэффициент асимметрии, показывает, в какую сторону относительно среднего сдвинуто большинство значений выборки.

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

При необходимости можно вычислить три дополнительные характеристики (рис. 4.5). Результаты расчетов этих характеристик приведены на рисунке 4.6.

«К-й наибольший» выдает К-е выборочное значение, если бы выборка была отсортирована по убыванию. В рассматриваемом примере сортировка по убыванию имеет вид 14,12,12,12, 11, Юит. д., третье значение равно 12. «К-й наименьший» выдает К-е выборочное значение, если бы выборка была отсортирована по возрастанию, это значение равно 5.

Задав «Уровень надежности», например 95%, получим значение для построения доверительного интервала для

Описательная статистика, дополнительные параметры

Результаты расчетов дополнительных параметров

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

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

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

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

Построение доверительных интервалов для среднего. Описательная статистика в Excel

2015-03-22
2255

ПРАКТИЧЕСКОЕ ЗАНЯТИЕ 3

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

Вычисление границ доверительных интервалов в Excel

Использование инструмента Пакета анализа Описательная статистика.

Построение доверительных интервалов для среднего.

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

Для установки раздела Анализ данных в пакете Excel сделайте следующее:

— в меню Сервис выберите команду Надстройки;

— в появившемся списке установите флажок Пакет анализа.

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

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

— указать курсором мыши на пункт меню Сервис и щелкнуть левой кнопкой мыши;

— в раскрывающемся списке выбрать команду Анализ данных (если команда Анализ данных отсутствует в меню Сервис, то необходимо установить в Excel пакет анализа данных);

— выбрать необходимую строку в появившемся списке Инструменты анализа;

— ввести входной и выходной диапазоны и выбрать необходимые параметры.

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

— выполнить команду Сервис > Анализ данных;

— в появившемся списке Инструменты анализа выбрать строку Описательная статистика и нажать кнопку ОК (рис. 1);

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

Рис. 1. Окно выбора метода обработки данных

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

— в разделе Группировка переключатель установить в положение по столбцам; о установить флажок в поле Итоговая статистика;

— нажать кнопку ОК.

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

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

Необходимо определить основные статистические характеристики в группах данных.

1. Для использования инструментов анализа исследуемые данные следует представить в виде таблицы, где столбцами являются соответствующие показатели. Значения зарплат сотрудников администрации введите в диапазон А1:А5, обслуживающего персонала— в диапазон В1:В8 и т. д. В результате получится таблица, представленная на рис. 2.

Рис. 2. Таблица из примера

2. Далее необходимо провести элементарную статистическую обработку. Для этого, указав курсором мыши на пункт меню Сервис, выберите команду Анализ данных. Затем в появившемся списке Инструменты анализа выберите строку Описательная статистика.

Рис. 3. Пример заполнения диалогового окна Описательная статистика

3. В появившемся диалоговом окне (рис. 3) в рабочем поле Входной интервал укажите входной диапазон —А1:С8. Активировав переключателем рабочее поле Выходной интервал, укажите выходной диапазон — ячейку А9. В разделе Группировка переключатель установите в положение по столбцам. Установите флажок в поле Итоговая статистика и нажмите кнопку ОК. В результате анализа (рис. 4) в указанном выходном диапазоне для каждого столбца данных получим соответствующие результаты.

Рис. 4. Результаты работы инструмента Описательная статистика.

1. Найдите наиболее популярный туристический маршрут из четырех реализуемых фирмой (моду), если за неделю последовательно были реализованы следующие маршруты (приводятся номера маршрутов): 1, 3, 3, 2, 1, 1, 4, 4, 2, 4, 1, 3, 2, 4, 1, 4, 4, 3, 1, 2, 3, 4, 1, 1, 3.

2. В рабочей зоне производились замеры концентрации вредного вещества. Получен ряд значений (в мг/м3): 12, 16, 15, 14, 10, 20, 16, 14, 18, 14, 15, 17, 23, 16. Необходимо определить основные выборочные характеристики.

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

Задача

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

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

выборки

к нескольким итоговым показателям, которые дают представление о

выборке

.В качестве таких статистических показателей используются:

среднее

,

медиана

,

мода

,

дисперсия, стандартное отклонение

и др.

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

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

, из которого была взята

выборка

. Например, если у нас есть

выборка

значений толщины трубы, которая изготавливается на определенном оборудовании, то на основании анализа этой

выборки

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

Содержание статьи:

• Надстройка Пакет анализа;
• Среднее выборки

  ;

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

  ;

• Мода выборки

  ;

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

  ;

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

  ;

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

  ;

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

  ;

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

  ;

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

  ;

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

  .

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

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

выборок

, используем

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

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

СОВЕТ

: Подробнее о других инструментах надстройки

Пакет анализа

и ее подключении – читайте в статье

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

.

Выборку

разместим на

листе

Пример

в файле примера

в диапазоне

А6:А55

(50 значений).

Примечание

: Для удобства написания формул для диапазона

А6:А55

создан

Именованный диапазон

Выборка.

В диалоговом окне

Анализ данных

выберите инструмент

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

.

После нажатия кнопки

ОК

будет выведено другое диалоговое окно,

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

• 
  входной интервал
  
  (Input Range) – это диапазон ячеек, в котором содержится массив данных. Если в указанный диапазон входит текстовый заголовок набора данных, то нужно поставить галочку в поле
  
  Метки в первой строке (
  
  Labels
  
  in
  
  first
  
  row
  
  ).
  
  В этом случае заголовок будет выведен в
  
  Выходном интервале.
  
  Пустые ячейки будут проигнорированы, поэтому нулевые значения необходимо обязательно указывать в ячейках, а не оставлять их пустыми;
• 
  выходной интервал
  
  (Output Range). Здесь укажите адрес верхней левой ячейки диапазона, в который будут выведены статистические показатели;
• 
  Итоговая статистика (
  
  Summary
  
  Statistics
  
  )
  
  . Поставьте галочку напротив этого поля – будут выведены основные показатели выборки:
  
  среднее, медиана, мода, стандартное отклонение
  
  и др.;
• Также можно поставить галочки напротив полей
  
  Уровень надежности (
  
  Confidence
  
  Level
  
  for
  
  Mean
  
  )
  
  ,
  
  К-й наименьший
  
  (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().

Медиану

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

=КВАРТИЛЬ.ВКЛ(Выборка;2) =ПРОЦЕНТИЛЬ.ВКЛ(Выборка;0,5).

Подробнее о

медиане

см. специальную статью

Медиана в 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

²

)

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

среднему значению

.

Чем больше величина

дисперсии

, тем больше разброс значений в массиве относительно

среднего

.

Размерность

дисперсии

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

дисперсии

будет кг

²

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

дисперсии – стандартное отклонение

.

Подробнее о

дисперсии

см. статью

Дисперсия и стандартное отклонение в MS EXCEL

.

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

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

(Standard Deviation), как и

дисперсия

, — это мера того, насколько широко разбросаны значения в выборке

относительно их среднего

.

По определению,

стандартное отклонение

равно квадратному корню из

дисперсии

:

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

не учитывает величину значений в

выборке

, а только степень рассеивания значений вокруг их

среднего

. Чтобы проиллюстрировать это приведем пример.

Вычислим стандартное отклонение для 2-х

выборок

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

выборок

существенно отличается.

В MS EXCEL 2007 и более ранних версиях для вычисления

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

используется функция

СТАНДОТКЛОН()

. С версии MS EXCEL 2010 рекомендуется использовать ее аналог

СТАНДОТКЛОН.В()

.

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

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

файл примера

):

=КОРЕНЬ(КВАДРОТКЛ(Выборка)/(СЧЁТ(Выборка)-1)) =КОРЕНЬ((СУММКВ(Выборка)-СЧЁТ(Выборка)*СРЗНАЧ(Выборка)^2)/(СЧЁТ(Выборка)-1))

Подробнее о

стандартном отклонении

см. статью

Дисперсия и стандартное отклонение в MS EXCEL

.

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

В

Пакете анализа

под термином

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

имеется ввиду

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

(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*σ

⁴

, следовательно,

Эксцесс

равен 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
данные
таблицы 8.54.

Таблица
8.54

Распределение
группы студентов по размеру оплаты за
образовательные услуги в регионе

1.
В меню выбираем:
Сервис
Анализ данныхОписательная
статистикаОК.
Появляется окно «Описательная
статистика»
(рис. 8.19,

8.20).

Рис.
8.19. Окно «Анализ данных»

Рис.
8.20. Окно «Описательная статистика»

2.
В данном окне выбираем команды: Входной
интервал –
диапазон ячеек со значениями Средняя
стоимость образовательных услуг и
Количество респондентов (В3:С9); Группировка
– по столбцам; Итоговая
статистика
– активировать, Уровень
надежности
– активизировать; Уровень
надежности –
95%; Выходной
интервал –
А12; ОК (рис.
8.21).

Рис.
8.21. Окно «Описательная статистка» с
необходимыми командами.

При
появлении окна с сообщением «Выходной
интервал накладывается на имеющиеся
данные»
— ОК.

В
результате указанных действий Microsoft
Excel
осуществляет вывод таблицы описательных
статистик (табл. 8.55).

Таблица
8.55

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

Интерпретация
терминов таблицы 8.55 следующая: Среднее
– средняя арифметическая величина
признака в выборке, вычисленная по
несгруппированным данным; Стандартная
ошибка –
средняя ошибка выборки – среднее
квадратическое отклонение выборочной
средней от математического ожидания
генеральной средней; Медиана
– значение
признака, приходящееся на середину
ранжированного ряда выборочных данных;
Мода
– значение признака, повторяющееся в
выборке с наибольшей частотой; Стандартное
отклонение
– генеральное среднее квадратическое
отклонение, оцененное по выборке;
Дисперсия
выборки –
генеральная дисперсия, оцененная по
выборке; Эксцесс
– коэффициент
эксцесса, оценивающий по выборке значение
эксцесса в генеральной совокупности;
Ассиметричность
– коэффициент ассиметрии, оценивающий
по выборке величину ассиметрии в
генеральной совокупности; Интервал
– размах вариации в выборке; Минимум
— минимальное значение признака в
выборке; Максимум
– максимальное значение
признака в выборке; Сумма – суммарное
значение элементов выборки; Счет
– объем выборки; Уровень
надежности (95,4%)
– предельная ошибка выборки, оцененная
с заданным уровнем надежности.

Метод
2. Расчет
предельной ошибки выборки при Р=0,997

1.
В меню выбираем:
Сервис
Анализ данныхОписательная
статистикаОК.
Появляется окно «Описательная
статистика».

2.
В данном окне выбираем команды: Входной
интервал –
диапазон ячеек со значениями Средняя
стоимость образовательных услуг и
Количество респондентов (В3:С9); Итоговая
статистика
– снять флажок, Уровень
надежности
– активизировать; Уровень
надежности –
99,7%; Выходной
интервал –
А29; ОК (рис.
8.22).

Рис.
8.22. Окно «Описательная статистка» с
необходимыми командами

При
появлении окна с сообщением «Выходной
интервал накладывается на имеющиеся
данные»
— ОК.

Таблица
8.56

Предельная ошибка
выборки

Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]

• #
• #
• #
• #
• #
• #
• #
• #
• #
• #
• #

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

Расчет средней арифметической ошибки

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

Расчет стандартной ошибки при помощи встроенных функций

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

1. Для расчетов будем использовать таблицу с выборкой чисел. Кликаем на любой пустой ячейке на листе, где будет отображаться результат. Затем нажимаем кнопку «Вставить функцию.

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

1. В окне «Аргументы функции» кликаем в первом поле «Число 1», затем выполняем выделение мышью диапазона ячеек со значениями таблицы и нажимаем кнопку «ОК».

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

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

1. Далее открывается окно, в котором необходимо заполнить поле с числом. Для этого переходим в поле «Имя», где спускаемся к пункту «Счет». Если его нет, ищите в дополнительных функциях.

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

Решение задачи с помощью опции «Описательная статистика»

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

1. Переходим во вкладку «Файл», где перемещаемся в пункт «Параметры». Далее нажимаем на запись «Надстройки».

1. Открывается окошко, в нем в графе «Управление» должно быть прописано «Надстройки Excel», затем рядом нажимаем кнопку «Параметры».

1. В появившемся окне находим «Пакет анализа» и нажимаем кнопку «ОК».

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

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

1. Открывается новый мастер значений. Здесь нужно вводить данные предельно внимательно. В поле «Входной интервал» вносим адрес диапазона ячеек с выборкой. Затем указываем параметр «Группирование» «По столбцам». Затем выбираем место для «выходного интервала», его должно быть столько же, сколько и «входного». Ставим галочку напротив «Итоговая статистика» и нажимаем кнопку «ОК».

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

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

Расчет ошибки средней арифметической

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

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

Способ 1: расчет с помощью комбинации функций

Прежде всего, давайте составим алгоритм действий на конкретном примере по расчету ошибки средней арифметической, используя для этих целей комбинацию функций. Для выполнения задачи нам понадобятся операторы СТАНДОТКЛОН.В, КОРЕНЬ и СЧЁТ.

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

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



2. Открывается Мастер функций. Производим перемещение в блок «Статистические». В представленном перечне наименований выбираем название «СТАНДОТКЛОН.В».



3. Запускается окно аргументов вышеуказанного оператора. СТАНДОТКЛОН.В предназначен для оценивания стандартного отклонения при выборке. Данный оператор имеет следующий синтаксис:

   =СТАНДОТКЛОН.В(число1;число2;…)

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

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



4. В ячейку на листе выводится результат расчета оператора СТАНДОТКЛОН.В. Но это ещё не ошибка средней арифметической. Для того, чтобы получить искомое значение, нужно стандартное отклонение разделить на квадратный корень от количества элементов выборки. Для того, чтобы продолжить вычисления, выделяем ячейку, содержащую функцию СТАНДОТКЛОН.В. После этого устанавливаем курсор в строку формул и дописываем после уже существующего выражения знак деления (/). Вслед за этим клацаем по пиктограмме перевернутого вниз углом треугольника, которая располагается слева от строки формул. Открывается список недавно использованных функций. Если вы в нем найдете наименование оператора «КОРЕНЬ», то переходите по данному наименованию. В обратном случае жмите по пункту «Другие функции…».



5. Снова происходит запуск Мастера функций. На этот раз нам следует посетить категорию «Математические». В представленном перечне выделяем название «КОРЕНЬ» и жмем на кнопку «OK».



6. Открывается окно аргументов функции КОРЕНЬ. Единственной задачей данного оператора является вычисление квадратного корня из заданного числа. Его синтаксис предельно простой:

   =КОРЕНЬ(число)

   

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

   Устанавливаем курсор в поле «Число» и кликаем по знакомому нам треугольнику, который вызывает список последних использованных функций. Ищем в нем наименование «СЧЁТ». Если находим, то кликаем по нему. В обратном случае, опять же, переходим по наименованию «Другие функции…».



7. В раскрывшемся окне Мастера функций производим перемещение в группу «Статистические». Там выделяем наименование «СЧЁТ» и выполняем клик по кнопке «OK».



8. Запускается окно аргументов функции СЧЁТ. Указанный оператор предназначен для вычисления количества ячеек, которые заполнены числовыми значениями. В нашем случае он будет подсчитывать количество элементов выборки и сообщать результат «материнскому» оператору КОРЕНЬ. Синтаксис функции следующий:

   =СЧЁТ(значение1;значение2;…)

   В качестве аргументов «Значение», которых может насчитываться до 255 штук, выступают ссылки на диапазоны ячеек. Ставим курсор в поле «Значение1», зажимаем левую кнопку мыши и выделяем весь диапазон выборки. После того, как его координаты отобразились в поле, жмем на кнопку «OK».



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

   =СТАНДОТКЛОН.В(B2:B13)/КОРЕНЬ(СЧЁТ(B2:B13))

   Результат вычисления ошибки средней арифметической составил 0,505793. Запомним это число и сравним с тем, которое получим при решении поставленной задачи следующим способом.

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

=СТАНДОТКЛОН.В(B2:B13)/КОРЕНЬ(СЧЁТ(B2:B13)-1)

Урок: Статистические функции в Экселе

Способ 2: применение инструмента «Описательная статистика»

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

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

1. После того, как открыт документ с выборкой, переходим во вкладку «Файл».



2. Далее, воспользовавшись левым вертикальным меню, перемещаемся через его пункт в раздел «Параметры».



3. Запускается окно параметров Эксель. В левой части данного окна размещено меню, через которое перемещаемся в подраздел «Надстройки».



4. В самой нижней части появившегося окна расположено поле «Управление». Выставляем в нем параметр «Надстройки Excel» и жмем на кнопку «Перейти…» справа от него.



5. Запускается окно надстроек с перечнем доступных скриптов. Отмечаем галочкой наименование «Пакет анализа» и щелкаем по кнопке «OK» в правой части окошка.



6. После выполнения последнего действия на ленте появится новая группа инструментов, которая имеет наименование «Анализ». Чтобы перейти к ней, щелкаем по названию вкладки «Данные».



7. После перехода жмем на кнопку «Анализ данных» в блоке инструментов «Анализ», который расположен в самом конце ленты.



8. Запускается окошко выбора инструмента анализа. Выделяем наименование «Описательная статистика» и жмем на кнопку «OK» справа.



9. Запускается окно настроек инструмента комплексного статистического анализа «Описательная статистика».

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

   В блоке «Группирование» оставляем настройки по умолчанию. То есть, переключатель должен стоять около пункта «По столбцам». Если это не так, то его следует переставить.

   Галочку «Метки в первой строке» можно не устанавливать. Для решения нашего вопроса это не важно.

   Далее переходим к блоку настроек «Параметры вывода». Здесь следует указать, куда именно будет выводиться результат расчета инструмента «Описательная статистика»:

   • На новый лист;
   • В новую книгу (другой файл);
   • В указанный диапазон текущего листа.

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

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

   • Итоговая статистика;
   • К-ый наибольший;
   • К-ый наименьший;
   • Уровень надежности.

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

   После того, как все настройки в окне «Описательная статистика» установлены, щелкаем по кнопке «OK» в его правой части.



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

Урок: Описательная статистика в Экселе

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