Федеральное агентство по образованию

Государственное образовательное учреждение

высшего профессионального образования

Уфимский государственный авиационный технический университет

СТАТИСТИЧЕСКИЙ АНАЛИЗ

СРЕДСТВАМИ ПАКЕТА MS EXCEL

Лабораторный практикум

по дисциплине «Статистика»

Уфа 2008 Федеральное агентство по образованию Государственное образовательное учреждение высшего профессионального образования Уфимский государственный авиационный технический университет Кафедра автоматизированных систем управления

СТАТИСТИЧЕСКИЙ АНАЛИЗ

СРЕДСТВАМИ ПАКЕТА MS EXCEL

Лабораторный практикум по дисциплине «Статистика»

Уфа Составители: В.Ю.Арьков, А.В.Жевак, А.И.Абдулнагимов УДК 311: 004.45 (07) ББК 60.6: 32.973-018.2 (Я7) Статистический анализ средствами пакета MS Excel:

Лабораторный практикум по дисциплине «Статистика» / Уфимск.

гос. авиац. техн. ун-т; Сост.: В.Ю. Арьков, А.В. Жевак, А.И. Абдулнагимов. – Уфа, 2008. – 51 с.

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

Предназначены для студентов 2 курса специальности «Прикладная информатика в экономике».

Табл. 6. Ил.35. Библиогр.: 6 назв.

Рецензенты: канд. техн. наук, Л.М. Бакусов канд. техн. наук, Р.В. Насыров Уфимский государственный авиационный технический университет,

СОДЕРЖАНИЕ

ВВЕДЕНИЕ

ЛАБОРАТОРНАЯ РАБОТА №

СВОДКА И ГРУППИРОВКА ДАННЫХ

1. ЦЕЛЬ РАБОТЫ И ЗАДАЧИ

2. ТЕОРЕТИЧЕСКИЕ СВЕДЕНИЯ

3. КРАТКОЕ ОПИСАНИЕ ПРОГРАММНОГО КОМПЛЕКСА......... 4. МЕТОДИКА ВЫПОЛНЕНИЯ РАБОТЫ

4.1. Упражнение 1. Генерация исходных данных

4.2. Упражнение 2. Вычисление показателей вариации................. 4.3. Упражнение 3. Группировка с помощью статистической надстройки

4.4. Упражнение 4. Группировка с помощью формул

4.5. Упражнение 5. Построение графиков

4.6. Упражнение 6. Сравнение фактического распределения с теоретическим

Требования к содержанию и оформлению отчета

Порядок выполнения работы

Контрольные вопросы

Критерии результативности выполнения работы

ЛАБОРАТОРНАЯ РАБОТА №

КОРРЕЛЯЦИЯ И РЕГРЕССИЯ

1. ЦЕЛЬ РАБОТЫ И ЗАДАЧИ

2. ТЕОРЕТИЧЕСКИЕ СВЕДЕНИЯ

3. МЕТОДИКА ВЫПОЛНЕНИЯ РАБОТЫ

3.1. Упражнение 1. Генерация исходных данных

3.2. Упражнение 2. Корреляционный анализ

3.3. Упражнение 3. Регрессионный анализ

3.4. Упражнение 4. Условное среднее

3.5. Упражнение 5. Анализ качества модели связи

Требования к содержанию и оформлению отчета

Порядок выполнения работы

Контрольные вопросы

Критерии результативности выполнения работы

ЛАБОРАТОРНАЯ РАБОТА №

РЯДЫ ДИНАМИКИ

1. ЦЕЛЬ РАБОТЫ И ЗАДАЧИ

2. ТЕОРЕТИЧЕСКИЕ СВЕДЕНИЯ

3. МЕТОДИКА ВЫПОЛНЕНИЯ РАБОТЫ

3.1 Упражнение 1. Компоненты динамики

3.2. Упражнение 2. Загрузка исторических данных

3.3. Упражнение 3. Анализ тренда

3.4. Упражнение 4. Скользящая средняя простая

3.5 Упражнение 5. Скользящая средняя взвешенная

3.6. Упражнение 6. Экспоненциально взвешенная скользящая средняя

3.7. Упражнение 7. Биржевые графики

Требования к содержанию и оформлению отчета

Порядок выполнения работы

Контрольные вопросы

Критерии результативности выполнения работы

СПИСОК ЛИТЕРАТУРЫ

ВВЕДЕНИЕ

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

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

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

Лабораторные работы выполняются с помощью модуля Анализ данных, а также с использованием встроенных функций MS Excel.

В тексте методических указаний названия файлов, программ, комбинаций клавиш и пунктов меню даются шрифтом Courier, чтобы отличать их от основного текста, который набран гарнитурой Times. Например, запись [Сервис Надстройки] означает, что необходимо подвести курсор к пункту меню Сервис и щелкнуть левой кнопкой мыши, затем подвести курсор к пункту Надстройки выпадающего меню и щелкнуть левой кнопкой мыши. Выражение [Shift+F12] означает, что необходимо нажать на клавишу Shift на клавиатуре и затем, не отпуская ее, нажать функциональную клавишу F12, после чего отпустить обе клавиши.

ЛАБОРАТОРНАЯ РАБОТА №

СВОДКА И ГРУППИРОВКА ДАННЫХ

1. ЦЕЛЬ РАБОТЫ И ЗАДАЧИ

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

Задачами работы является:

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

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

приобретение навыков построения статистических графиков.

2. ТЕОРЕТИЧЕСКИЕ СВЕДЕНИЯ

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

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

Можно использовать следующие правила группирования:

границы интервалов должны быть круглыми числами (например, 10-20, 70-80, 120-150 и т.п.);

интервалы должны иметь одинаковую ширину (например: 160). При этом ширина первого и последнего интервалов принимается равной остальным;

рекомендуемое количество интервалов должно быть на порядок меньше объема выборки;

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

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

ni – частота (количество элементов выборки, попадающих в данный интервал);

ni % – относительная частота, частость (доля числа элементов в данном интервале от объема выборки);

Ki % – накопленная частота (сумма частостей текущего интервала и всех предыдущих).

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

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

К основным показателям вариации относятся: размах вариации, объем выборки, медиана, мода, среднее, дисперсия и т.д. (см.

табл.1.1).

Любая случайная величина имеет функцию распределения зависимость плотности вероятности от значения случайной величины. Для нормального распределения (распределения Гаусса) функция распределения имеет следующий вид:

где µ – математическое ожидание, – стандартное отклонение.

Стандартным нормальным распределением называется нормальное распределение с математическим ожиданием 0 и стандартным отклонением 1.

Объём выборки Среднее отклонение

СТАНДОТКЛОНП

СТАНДОТКЛОН

Коэффициент Линейный коэффициент График функции плотности распределения вероятностей и интегральной функции распределения представлен на рис. 1.1. и 1.2.

Рис. 1.1. График функции плотности распределения Рис. 1.2. Интегральная функция распределения Непрерывная случайная величина имеет равномерное распределение на отрезке [a, b], если на этом отрезке плотность распределения случайной величины постоянна, а вне его равна нулю.

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

Плотность равномерного распределения представлена на рис. 1.3.

Рис.1.3. Плотность равномерного распределения Функция нормального распределения F(x) на отрезке [a, b] равна

3. КРАТКОЕ ОПИСАНИЕ ПРОГРАММНОГО

КОМПЛЕКСА

Лабораторные работы выполняются в пакете Microsoft Excel, который представляет собой электронную таблицу.

Документ Excel имеет расширение *.xls и называется рабочей книгой. Рабочая книга состоит из листов. Переключаться между листами можно, используя закладки (ярлычки) в нижней части окна «Лист 1» и т.д.

Каждый лист представляет собой таблицу. Таблица состоит из столбцов и строк. Количество столбцов в листе – 256, строк – 65536.

Столбцы обозначаются буквами латинского алфавита (в обычном режиме) от A до Z, затем идет AA-AZ, BB-BZ и т.д. Строки обозначаются обычными арабскими числами.

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

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

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

Формулы начинаются со знака равенства (=), например, =СУММ(B2:B151)/ Статистические функции вставляют в формулы, выбрав в верхнем меню [Вставка Функция Категория Статистические]. Справочные материалы по этим функциям можно получить в справочном руководстве Microsoft Excel ([Справка Справка Microsoft Excel] или F1). Пример:

вычисления среднего значения:

=СРЗНАЧ(B2:B151)

4. МЕТОДИКА ВЫПОЛНЕНИЯ РАБОТЫ

4.1. Упражнение 1. Генерация исходных данных При выполнении работы также используется статистическая надстройка Microsoft Excel. Чтобы активировать надстройку, необходимо выбрать [Сервис Надстройки] в меню и поставить галочку напротив пункта [Пакет анализа] (см. рис.

1.4). После этого станет доступным пункт меню [Сервис Анализ данных].

Исходные данные представляют собой выборку {x1, x2, …, xn}, сгенерированную по одному из законов распределения в зависимости от варианта задания (табл. 1.2). Для генерации исходных данных используется функция [Генерация случайных чисел] статистической надстройки Microsoft Excel (см. рис. 1.5). После вызова функции, статистическая надстройка предложит указать параметры генерации выборки, как показано на рис. 1.6, 1.7.

Число переменных устанавливается равным 1, объём выборки n (Число случайных чисел). Параметры распределения задаются согласно варианту задания (см. табл. 1.2). При генерировании равномерного распределения параметрами являются минимальное и максимальное значения диапазона (min и max), которые вводятся в окне [Параметры Между]. Для нормального распределения указывают среднее значение µ и стандартное отклонение.

Рис. 1.4. Включение статистической надстройки MS Excel Рис. 1.5. Вызов функции «Генерация случайных чисел»

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

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

Рис. 1.6. Выбор параметров генератора нормального распределения Сгенерированные случайные числа являются вещественными, их необходимо округлить до целых и поместить во второй столбец таблицы. Для этого можно воспользоваться математической функцией ОКРУГЛ, имеющей 2 аргумента: округляемое число и количество десятичных разрядов, до которого его нужно округлить.

Число разрядов равно 0 в случае округления до целого.

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

Рис. 1.7. Выбор параметров генератора равномерного распределения Рис. 1.8. Генерация случайных чисел и описательная статистика 4.2. Упражнение 2. Вычисление показателей вариации Для вычисления показателей вариации применяется функция Описательная статистика статистической надстройки Microsoft Excel (см. рис. 1.9). В диалоговом окне нужно выбрать Входной интервал, Метки в первой строке, Выходной интервал и Итоговая статистика.

указанные в табл. 1.1.

Рис. 1.9. Использование функции «Описательная статистика»

При анализе показателей вариации можно использовать следующие правила:

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

Критические значения А и Е вычисляют по оценкам дисперсий:

Критерий согласия:

4.3. Упражнение 3. Группировка с помощью статистической надстройки Группировка данных проводится двумя способами: с помощью стандартных функций Excel и статистической надстройки.

Вначале следует создать таблицу нижних границ интервалов группирования (рис. 1.10).

Рис. 1.10. Настройка функции Гистограмма Для группировки данных с помощью статистической надстройки выбираем меню [Сервис Анализ данных Гистограмма]. Указываем следующие параметры:

Входные данные:

Входной интервал – выборка исходных данных;

Интервал карманов – нижние границы интервалов группирования.

Параметры вывода:

группировки на листе;

Вывод графика – построение гистограммы.

Интегральный процент – вычисление накопленных частостей.

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

Рис. 1.11. Результат вызова функции «Гистограмма»

Рис. 1.12. Настройка результатов группировки Таблицу необходимо дополнить следующими столбцами:

Интервал – подписи для столбцов гистограммы в виде текстовых меток, описывающих границы интервалов группировки;

Частость ni (%);

Кумулята Ki (%).

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

Для вычисления недостающих характеристик интервалов следует использовать формулы. Например, для вычисления накопленной частости для интервала 90..100 (ячейка G3) используется формула =G2+F3. Вычисление относительной частоты для интервала 80..90 (ячейка F3) выполняется с помощью формулы:

=100*E3/$E$8.

После вычислений следует убедиться в отсутствии грубых ошибок. Например, накопленная частость должна быть равна 100%.

Расположение столбиков гистограммы должно соответствовать границам интервалов группирования данных. Для настройки графика щелкните по столбику гистограммы курсором и нажмите правую кнопку мыши. Выберите [Формат рядов данных Параметры] и установите нулевое значение параметров Перекрытие и Ширина зазора.

4.4. Упражнение 4. Группировка с помощью формул Пример группировки с помощью формул приводится на рис. 1.13.

Подсчет частоты попадания в интервал значений определяется как разность количества значений меньше верхней границы и меньше нижней границы интервала. Например, частота для первого интервала (ячейка G6) рассчитана с помощью функции СЧЁТЕСЛИ: