Лабораторная работа №19 «Статистический анализ в Excel» |
||||||||||||
|
1. Цель и задачи лабораторной работы. Приобретение навыков проведения статистического анализа в MS Excel. 2. Теоретические сведения. Назначение и возможности пакета анализа В состав Microsoft Excel входит пакет анализа, который позволяет осуществлять статистическую обработку данных в таблицах. В состав этого пакета входят разнообразные статистические методы. Способы применения их всех аналогичны, поэтому мы рассмотрим лишь некоторые из них: экспоненциальное сглаживание, корреляцию, скользящее среднее, регрессию. Корреляция используется для количественной оценки взаимосвязи двух наборов данных, представленных в безразмерном виде. Корреляционный анализ дает возможность установить ассоциированы ли наборы данных по величине, то есть, большие значения из одного набора данных связаны с большими значениями другого набора (положительная корреляция), или, наоборот, малые значения одного набора связаны с большими значениями другого (отрицательная корреляция), или данные двух диапазонов никак не связаны (корреляция близка к нулю). Скользящее среднее используется для расчета значений в прогнозируемом периоде на основе среднего значения переменной для указанного числа предшествующих периодов. Процедура может использоваться для прогноза сбыта, инвентаризации и других процессов. Мы спрогнозируем курс доллара США на основе данных за июль 1999 года. Экспоненциальное сглаживание предназначается для предсказания значения на основе прогноза для предыдущего периода, скорректированного с учетом погрешностей в этом прогнозе. Использует константу сглаживания, по величине которой определяет, насколько сильно влияют на прогнозы погрешности в предыдущем прогнозе. Для константы сглаживания наиболее подходящими являются значения от 0,2 до 0,3. Эти значения показывают, что ошибка текущего прогноза установлена на уровне от 20 до 30 процентов ошибки предыдущего прогноза. Более высокие значения константы ускоряют отклик, но могут привести к непредсказуемым выбросам. Низкие значения константы могут привести к сдвигу аргумента для предсказанных значений. Линейный регрессионный анализ заключается в подборе графика для набора наблюдений с помощью метода наименьших квадратов. Регрессия используется для анализа воздействия на отдельную зависимую переменную значений одной или более независимых переменных. Мы рассмотрим, как влиял на курс ЕВРО по отношению к рублю курс доллара США в июле 1999 года. Установка пакета анализа Если в Microsoft Excel в меню Сервис отсутствует команда Анализ данных, то необходимо установить статистический пакет анализа данных. Чтобы установить пакет анализа данных
Вызов пакета анализа Чтобы запустить пакет анализа:
Корреляция При выборе строки Корреляция в диалоговом запросе Анализ данных появляется следующее окно.
Входной интервал. Введите ссылку на ячейки, содержащие анализируемые данные. Ссылка должна состоять как минимум из двух смежных диапазонов данных, организованных в виде столбцов или строк. (Для этого нужно мышью щелкнуть по кнопке
Группирование. Установите переключатель в положение По столбцам или По строкам в зависимости от расположения данных во входном диапазоне. Метки в первой строке/Метки в первом столбце. Установите переключатель в положение Метки в первой строке, если первая строка во входном диапазоне содержит названия столбцов. Установите переключатель в положение Метки в первом столбце, если названия строк находятся в первом столбце входного диапазона. Если входной диапазон не содержит меток, то необходимые заголовки в выходном диапазоне будут созданы автоматически. (В других видах анализа этот флажок выполняет аналогичную функцию). Выходной интервал. Введите ссылку на левую верхнюю ячейку выходного диапазона. Поскольку коэффициент корреляции двух наборов данных не зависит от последовательности их обработки, то выходная область занимает только половину предназначенного для нее места. Ячейки выходного диапазона, имеющие совпадающие координаты строк и столбцов, содержат значение 1, так как каждая строка или столбец во входном диапазоне полностью коррелирует с самим собой. Новый лист. Установите переключатель, чтобы открыть новый лист в книге и вставить результаты анализа, начиная с ячейки A1. Если в этом есть необходимость, введите имя нового листа в поле, расположенном напротив соответствующего положения переключателя. Новая книга. Установите переключатель, чтобы открыть новую книгу и вставить результаты анализа в ячейку A1 на первом листе в этой книге. В результате программа сформирует таблицу с коэффициентами корреляции между выбранными совокупностями.
Скользящее среднее При выборе строки Скользящее среднее в диалоговом запросе Анализ данных появляется следующее окно.
Входной интервал. Введите ссылку на диапазон исследуемых данных. Входной диапазон должен состоять из одного столбца или одной строки, содержащих не менее четырех ячеек с данными. Метки в первой строке. То же, что и в окне ввода данных для корреляции. Интервал. Введите число значений, необходимое для расчета скользящего среднего. Значение по умолчанию равно 3. Выходной интервал. Введите ссылку на левую верхнюю ячейку выходного диапазона. Если установлен флажок Стандартные погрешности, то выводится состоящий из двух столбцов диапазон, содержащий значения стандартных погрешностей в правом столбце. Если предшествующих данных недостаточно для построения прогноза, Microsoft Excel возвратит ошибочное значение #Н/Д. Выходной диапазон и исходные данные должны находиться на одном листе. По этой причине параметры Новый лист и Новая книга недоступны. Вывод графика. Установите флажок для автоматического создания встроенной диаграммы на листе, содержащем выходной диапазон. Стандартные погрешности. Установите флажок, чтобы включить в выходной диапазон столбец, содержащий стандартные погрешности. Снимите флажок для вывода одного столбца, не содержащего значения стандартных погрешностей. В результате будут рассчитаны средние значения, на основании которых можно сделать прогноз роста курса доллара США, а также стандартные погрешности расчета (в столбце G), будет выведен график.
Экспоненциальное сглаживание При выборе строки Экспоненциальное сглаживание в диалоговом запросе Анализ данных появляется следующее окно.
Входной интервал. Введите ссылку на ячейки, содержащие анализируемые данные. Входной диапазон должен состоять из одного столбца или одной строки, содержащих данные как минимум в четырех ячейках. Фактор затухания. Введите фактор затухания, который будет использоваться в качестве константы экспоненциального сглаживания. Фактором затухания называется корректировочный фактор, минимизирующий нестабильность данных генеральной совокупности. Значение фактора по умолчанию равно 0,3. Метки. Установите флажок, если первая строка или первый столбец входного интервала содержит заголовки. Снимите флажок, если заголовки отсутствуют; в этом случае подходящие названия для данных выходного диапазона будут созданы автоматически. Выходной интервал. Введите ссылку на левую верхнюю ячейку выходного диапазона. Если установлен флажок Стандартные погрешности, то выходной диапазон состоит из двух столбцов, и значения стандартных погрешностей содержатся в правом столбце. Если недостаточно исходных значений для построения прогноза или для вычисления стандартной ошибки, Microsoft Excel возвратит ошибочное значение #Н/Д. Выходной диапазон и данные входного диапазона должны быть расположены на одном листе. По этой причине параметры Новый лист и Новая книга недоступны. Вывод графика. Установите флажок, чтобы построить встроенную диаграмму для фактических и прогнозируемых значений. Стандартные погрешности. Установите флажок, чтобы включить в выходной диапазон столбец стандартных погрешностей. Снимите флажок, чтобы получить выходной диапазон в виде одного столбца без значений стандартных погрешностей. В результате будут рассчитаны значения, на основании которых можно сделать прогноз роста курса доллара США, а также стандартные погрешности расчета (в столбце G), будет выведен график.
Линейный регрессионный анализ Для проведения регрессионного анализа необходимо выбрать строку Регрессия в диалоговом запросе Анализ данных. На экране появится запрос Регрессия со следующими полями.
Входной интервал Y. Введите ссылку на диапазон зависимых данных. Диапазон должен состоять из одного столбца. Входной интервал X. Введите ссылку на диапазон независимых данных. Microsoft Excel располагает независимые переменные этого диапазона слева направо в порядке возрастания. Максимальное число входных диапазонов равно 16.
Метки. Установите флажок, если первая строка или первый столбец входного интервала содержит заголовки. Снимите флажок, если заголовки отсутствуют; в этом случае подходящие названия для данных выходного диапазона будут созданы автоматически. Уровень надежности. Установите флажок, чтобы включить в выходной диапазон дополнительный уровень. В соответствующее поле введите уровень надежности, который будет использован дополнительно к уровню 95%, применяемому по умолчанию. Константа – ноль. Установите флажок, чтобы линия регрессии прошла через начало координат. Выходной интервал. Введите ссылку на левую верхнюю ячейку выходного диапазона. Отведите, по крайней мере, семь столбцов для итогового диапазона, который будет включать в себя: результаты дисперсионного анализа, коэффициенты регрессии, стандартную погрешность вычисления Y, среднеквадратичные отклонения, число наблюдений, стандартные погрешности для коэффициентов. Новый лист. Установите переключатель, чтобы открыть новый лист в книге и вставить результаты анализа, начиная с ячейки A1. Если в этом есть необходимость, введите имя нового листа в поле, расположенном напротив соответствующего положения переключателя. Новая книга. Установите переключатель, чтобы открыть новую книгу и вставить результаты анализа в ячейку A1 на первом листе в этой книге. Остатки. Установите флажок, чтобы включить остатки в выходной диапазон. Стандартизированные остатки. Установите флажок, чтобы включить стандартизированные остатки в выходной диапазон. График остатков. Установите флажок, чтобы построить диаграмму остатков для каждой независимой переменной. График подбора. Установите флажок, чтобы построить диаграммы наблюдаемых и предсказанных значений для каждой независимой переменной. График нормальной вероятности. Установите флажок, чтобы построить диаграмму нормальной вероятности. При анализе влияния курса доллара США (Х) на курс ЕВРО (Y) получим:
3.Оборудование для лабораторной работы. Персональный IBM PC — совместимый компьютер, подключенный в одноранговую локальную вычислительную сеть под управлением Windows XP. 4. Порядок выполнения работы.
|
||||||||||||
©2008-2020, Интернет-институт ТулГУ |
в правом конце строки, установить мышь в верхний правый угол диапазона анализируемых данных и, удерживая нажатой левую кнопку мыши, отбуксировать мышь в левый нижний угол диапазона, нажать клавишу Enter).
