Реферат: Статистика на Excel

Введение

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

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

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

Инструменты пакетаанализа в MicrosoftExcelДисперсионный анализ

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

Однофакторный дисперсионный анализ — Однофакторныйдисперсионный анализ используется для проверки гипотезы о сходстве среднихзначений двух или более выборок, принадлежащих одной и той же генеральнойсовокупности. Этот метод распространяется также на тесты для двух средних (ккоторым относится, например, 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 интерпретирует вводимые данные  либо  как текст  (выравнивается по  левому  краю),  либо  как  числовое значение (выравнивается по правомукраю).  Для  ввода  формулы необходимо  ввести  алгебраическое выражение, которому должен предшествовать знак равенства (=).

Предположим, что в ячейке А1 таблицы находится число 100, а в ячейке В1 — число 20.  Чтобы  разделить  первое  число  на второе и результат поместить вячейку С1,  в ячейку С1 следует ввести соответствующую формулу (=А1/В1) инажать [Enter].

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

Видыстатистических функций в MicrosoftExcel 2000:

FРАСП  ВозвращаетF-распределение вероятности

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

FРАСПОБР  Возвращаетобратное значение для F-распределения вероятности

ZТЕСТ  Возвращаетдвустороннее P-значение z-теста

БЕТАОБР  Возвращаетобратную функцию к интегральной функции плотности бета-вероятности

БЕТАРАСП  Возвращаетинтегральную функцию плотности бета-вероятности

БИНОМРАСП  Возвращаетотдельное значение биномиального распределения

ВЕЙБУЛЛ  Возвращаетраспределение Вейбулла

ВЕРОЯТНОСТЬ  Возвращаетвероятность того, что значение из днапазона находится внутри заданных пределов

ГАММАНЛОГ  Возвращаетнатуральный логарифм гамма функции, Γ(x)

ГАММАОБР  Возвращаетобратное гамма-распределение

ГАММАРАСП  Возвращаетгамма-распределение

ГИПЕРГЕОМЕТ  Возвращаетгипергеометрическое распределение

ДИСП  Оцениваетдисперсию по выборке

ДИСПА  Оцениваетдисперсию по выборке, включая числа, текст и логические значения

ДИСПР  Вычисляетдисперсию для генеральной совокупности

ДИСПРА  Вычисляетдисперсию для генеральной совокупности, включая числа, текст и логическиезначения

ДОВЕРИТ  Возвращаетдоверительный интервал для среднего значения по генеральной совокупности

КВАДРОТКЛ  Возвращаетсумму квадратов отклонений

КВАРТИЛЬ  Возвращаетквартиль множества данных

КВПИРСОН  Возвращаетквадрат коэффициента корреляции Пирсона

КОВАР  Возвращаетковариацию, то есть среднее произведений отклонений для каждой пары точек

КОРРЕЛ  Возвращаеткоэффициент корреляции между двумя множествами данных

КРИТБИНОМ  Возвращаетнаименьшее значение, для которого биномиальная функция распределения меньше илиравна заданному значению

ЛГРФПРИБЛ  Возвращаетпараметры экспоненциального тренда

ЛИНЕЙН  Возвращаетпараметры линейного тренда

ЛОГНОРМОБР  Возвращаетобратное логарифмическое нормальное распределение

ЛОГНОРМРАСП  Возвращаетинтегральное логарифмическое нормальное распределение

МАКС  Возвращаетмаксимальное значение из списка аргументов

МАКСА  Возвращаетмаксимальное значение из списка аргументов, включая числа, текст и логическиезначения

МЕДИАНА  Возвращаетмедиану заданных чисел

МИН  Возвращаетминимальное значение из списка аргументов

МИНА  Возвращаетминимальное значение из списка аргументов, включая числа, текст и логическиезначения

МОДА  Возвращаетзначение моды множества данных

НАИБОЛЬШИЙ  Возвращаетk-ое наибольшее значение из множества данных

НАИМЕНЬШИЙ  Возвращаетk-ое наименьшее значение в множестве данных

НАКЛОН  Возвращаетнаклон линии линейной регрессии

НОРМАЛИЗАЦИЯ  Возвращаетнормализованное значение

НОРМОБР  Возвращаетобратное нормальное распределение

НОРМРАСП  Возвращаетнормальную функцию распределения

НОРМСТОБР  Возвращаетобратное значение стандартного нормального распределения

НОРМСТРАСП  Возвращаетстандартное нормальное интегральное распределение

ОТРБИНОМРАСП  Возвращаетотрицательное биномиальное распределение

ОТРЕЗОК  Возвращаетотрезок, отсекаемый на оси линией линейной регрессии

ПЕРЕСТ  Возвращаетколичество перестановок для заданного числа объектов

ПЕРСЕНТИЛЬ  Возвращаетk-ую персентиль для значений из интервала

ПИРСОН  Возвращаеткоэффициент корреляции Пирсона

ПРЕДСКАЗ  Возвращаетзначение линейного тренда

ПРОЦЕНТРАНГ  Возвращаетпроцентную норму значения в множестве данных

ПУАССОН  Возвращаетраспределение Пуассона

РАНГ  Возвращаетранг числа в списке чисел

РОСТ  Возвращаетзначения в соответствии с экспоненциальным трендом

СКОС  Возвращаетасимметрию распределения

СРГАРМ  Возвращаетсреднее гармоническое

СРГЕОМ  Возвращаетсреднее геометрическое

СРЗНАЧ  Возвращаетсреднее арифметическое аргументов

СРЗНАЧА  Возвращаетсреднее арифметическое аргументов, включая числа, текст и логические значения.

СРОТКЛ  Возвращаетсреднее абсолютных значений отклонений точек данных от среднего

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

СТАНДОТКЛОНА  Оцениваетстандартное отклонение по выборке, включая числа, текст и логические значения

СТАНДОТКЛОНП  Вычисляетстандартное отклонение по генеральной совокупности

СТАНДОТКЛОНПА  Вычисляетстандартное отклонение по генеральной совокупности, включая числа, текст илогические значения

СТОШYX  Возвращаетстандартную ошибку предсказанных значений y для каждого значения x в регрессии

СТЬЮДРАСП  Возвращаетt-распределение Стьюдента

СТЬЮДРАСПОБР  Возвращаетобратное t-распределение Стьюдента

СЧЁТ  Подсчитываетколичество чисел в списке аргументов

СЧЁТЗ  Подсчитываетколичество значений в списке аргументов

ТЕНДЕНЦИЯ  Возвращаетзначения в соответствии с линейным трендом

ТТЕСТ  Возвращаетвероятность, соответствующую критерию Стьюдента

УРЕЗСРЕДНЕЕ  Возвращаетсреднее внутренности множества данных

ФИШЕР  Возвращаетпреобразование Фишера

ФИШЕРОБР  Возвращаетобратное преобразование Фишера

ФТЕСТ  Возвращаетрезультат F-теста

ХИ2ОБР  Возвращаетобратное значение односторонней вероятности распределения хи-квадрат

ХИ2РАСП  Возвращаетодностороннюю вероятность распределения хи-квадрат

ХИ2ТЕСТ  Возвращаеттест на независимость

ЧАСТОТА  Возвращаетраспределение частот в виде вертикального массива

ЭКСПРАСП  Возвращаетэкспоненциальное распределение

ЭКСЦЕСС  Возвращаетэксцесс множества данных

СПИСОК ИСПОЛЬЗОВАННОЙ ЛИТЕРАТУРЫ

 

1.   ПетрикДж. Бернс, Элисон Берроуз  «Секреты Excel 97.» –М.: Веста, 1999 -753с.

2.   ФигурновВиктор Эдмундович «IBM PC для пользователя» – М.: ИНФРА, 1998-

680с.

3.   А.Гончаров «Microsoft Excel 7.0 в примерах» — С.-П.: Питер, 1996

4.   Андрей Пробитюк «Excel 7.0 для Windows 95 в бюро» – К.:BHV, 1996