Обзор встроенных средств статистического анализа данных

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

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

1) генерация случайных чисел;

2) выборка;

3) гистограмма;

4) описательная статистика;

5) ранг и персентиль;

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

7) двухвыборочный t –тест для средних с одинаковыми дисперсиями;

8) двухвыборочный t –тест для средних с различными дисперсиями;

9) парный двухвыборочный t –тест для средних;

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

11) ковариация;

12) корреляция;

13) регрессия;

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

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

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

17) скользящее среднее;

18) экспоненциальное сглаживание;

19) анализ Фурье.

Для доступа к процедурам Пакета анализа необходимо в меню Сервис щёлкнуть указателем мыши на строке Анализ данных. Откроется диалоговое окно с соответствующим названием, в котором перечислены процедуры статистического анализа.

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

К числу общих для большинства процедур элементов управления относятся:

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

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

• флажок Метки. Флажок устанавливается в тех случаях, когда первая строка (первый столбец) входного диапазона содержит заголовки. если такие заголовки отсутствуют, флажок Метки не устанавливают. При этом Excel автоматически создаёт и выводит на экран стандартные названия для данных выходного диапазона (Столбец 1, Столбец 2, … или Строка 1, Строка 2, …);

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

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

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

При выборе Новая рабочая книга открывается новая рабочая книга. На первый лист этой книги, начиная с ячейки А1, выводится таблица результатов решения.

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

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

Для того чтобы вставить функцию на рабочий лист:

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

2. В меню Вставка выделите команду Функция (можно щёлкнуть на кнопке Функция ( )на панели инструментов Стандартная или нажать клавиши Shift + F3).

Откроется диалоговое окно Мастер функций– шаг 1 из 2 с перечнем категорий и имён встроенных функций.

3. В перечне категорий выделите категорию Статистические, в списке функций– имя этой функции.

4. В нижней части диалогового окна Мастер функций появится краткое описание вводимой функции. С помощью этого описания можно ещё раз убедится в правильности (или ошибочности) выбора функции. Если этой информации недостаточно, надо щёлкнуть на кнопке Справка в левой нижней части диалогового окна или нажать клавишу F 1.

5. Щёлкните на кнопке ОК диалогового окна Мастер функций.

6. Под строкой формул появится панель функции с полями ввода аргументов.

7. Введите значения аргументов в поля ввода функции.

8. Завершите ввод функции щелчком на кнопке ОК на панели формул.

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

Когда в качестве кого-либо аргумента встроенной статистической функции введён текст, функция выдаёт сообщение об ошибке # ЗНАЧ!. Если в качестве аргумента, который по определению должен быть целым числом, введено число не целое, Excel использует в качестве аргумента целую часть этого числа.

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

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

Указанные статистические процедуры и функции позволяют решать следующие задачи статистического анализа:

• выполнять различные вычисления, связанные с основными одномерными распределениями вероятностей;

• генерировать случайные числа;

• производить первичную обработку статистических данных;

• анализировать данные с помощью порядковых статистик;

• проверять некоторые статистические гипотезы;

• выполнять дисперсионный анализ;

• производить корреляционный и регрессионный анализ;

• выполнять анализ динамических (временных) рядов;

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