Статистика на Excel

Введение

Пакет анализа. В состав Microsoft Excel входит набор средств анализа

данных (так называемый пакет анализа), предназначенный для решения сложных

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

этих инструментов следует указать входные данные и выбрать параметры;

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

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

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

Доступные средства. Чтобы просмотреть список доступных инструментов

анализа, выберите команду Анализ данных в меню Сервис. Если команда Анализ

данных в меню Сервис отсутствует — необходима установка пакета анализа.

Необходимые знания. Для успешного применения процедур анализа необходимы

начальные знания в области статистических и инженерных расчетов, для

которых эти инструменты были разработаны.

Инструменты пакета анализа в Microsoft Excel

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

Пакет анализа включает в себя три средства дисперсионного анализа. Выбор

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

исследуемой совокупности данных.

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

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

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

метод распространяется также на тесты для двух средних (к которым

относится, например, t-критерий).

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

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

выборку для каждой группы данных.

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

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

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

или нескольких выборок одинаковы (выборки принадлежат одной и той же

генеральной совокупности). Этот метод распространяется также на тесты для

двух средних, такие как t-критерий.

Корреляционный анализ

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

представленных в безразмерном виде. Коэффициент корреляции выборки

представляет собой ковариацию двух наборов данных, деленную на произведение

их стандартных отклонений.

Корреляционный анализ дает возможность установить, ассоциированы ли наборы

данных по величине, то есть, большие значения из одного набора данных

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

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

другого (отрицательная корреляция), или данные двух диапазонов никак не

связаны (корреляция близка к нулю).

Ковариационный анализ

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

от относительных средних. Ковариация является мерой связи между двумя

диапазонами данных.

Ковариационный анализ дает возможность установить, ассоциированы ли наборы

данных по величине, то есть, большие значения из одного набора данных

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

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

другого (отрицательная ковариация), или данные двух диапазонов никак не

связаны (ковариация близка к нулю).

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

Это средство анализа служит для создания одномерного статистического

отчета, содержащего информацию о центральной тенденции и изменчивости

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

диалогового окна

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

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

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

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

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

прогнозе.

Анализ Фурье

Предназначается для решения задач в линейных системах и анализа

периодических данных, используя метод быстрого преобразования Фурье (БПФ).

Эта процедура поддерживает также обратные преобразования, при этом,

инвертирование преобразованных данных возвращает исходные данные.

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

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

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

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

Гистограмма

Используется для вычисления выборочных и интегральных частот попадания

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

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

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

состоит из границ шкалы оценок и количеств студентов, уровень успеваемости

которых находится между самой нижней границей и текущей границей. Наиболее

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

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

Используется для расчета значений в прогнозируемом периоде на основе

среднего значения переменной для указанного числа предшествующих периодов.

Каждое прогнозируемое значение основано на формуле:

где

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

. Aj фактическое значение в момент времени j

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

Скользящее среднее, в отличие от простого среднего для всей выборки,

содержит сведения о тенденциях изменения данных. Процедура может

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

Проведение t-теста

Пакет анализа включает в себя три средства анализа среднего для

совокупностей различных типов:

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

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

выборок. Эта форма t-теста предполагает совпадение дисперсий генеральных

совокупностей и обычно называется гомоскедастическим t-тестом.

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

Стьюдента используется для проверки гипотезы о равенстве средних для двух

выборок данных из разных генеральных совокупностей. Эта форма t-теста

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

называется гетероскедастическим t-тестом. Если тестируется одна и та же

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

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

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

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

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

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

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

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

Используется для заполнения диапазона случайными числами, извлеченными из

одного или нескольких распределений. С помощью данной процедуры можно

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

распределению вероятностей. Например, можно использовать нормальное

распределение для моделирования совокупности данных по росту индивидуумов,

или использовать распределение Бернулли для двух вероятных исходов, чтобы

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

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

Используется для вывода таблицы, содержащей порядковый и процентный ранги

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

для анализа относительного взаиморасположения данных в наборе.

Регрессия

Линейный регрессионный анализ заключается в подборе графика для набора

наблюдений с помощью метода наименьших квадратов. Регрессия используется

для анализа воздействия на отдельную зависимую переменную значений одной

или более независимых переменных. Например, на спортивные качества атлета

влияют несколько факторов, включая возраст, рост и вес. Регрессия

пропорционально распределяет меру качества по этим трем факторам на основе

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

быть использованы для предсказания качеств нового, непроверенного атлета.

Выборка

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

как генеральную совокупность. Если совокупность слишком велика для

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

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

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

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

создание выборки с периодом 4 разместит в выходном диапазоне значения

продаж из одного и того же квартала.

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

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

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

Например, этот тест может использоваться для определения различия между

характеристиками двух моделей автомобилей.

Статистические функции

Возможность использования формул и функций является одним из важнейших

свойств программы обработки электронных таблиц. Это, в частности,

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

Текст формулы, которая вводится в ячейку таблицы, должен начинаться

со знака равенства (=), чтобы программа Excel могла отличить формулу от

текста. После знака равенства в ячейку записывается математическое

выражение, содержащее аргументы, арифметические операции и функции.

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

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

следующие символы: + (сложение); - (вычитание); * (умножение); / (деление).

Формула может содержать ссылки на ячейки, которые расположены

на другом рабочем листе или даже в таблице другого файла. Однажды

введенная формула может быть в любое время модифицирована.

Встроенный Менеджер формул помогает пользователю найти ошибку или

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

Кроме этого, программа Excel позволяет работать со сложными

формулами, содержащими несколько операций. Для наглядности можно

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

не результат вычисления формулы, а собственно формулу.

Программа Excel интерпретирует вводимые данные либо как текст

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

(выравнивается по правому краю). Для ввода формулы необходимо ввести

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