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

ТРЕБОВАНИЯ К СОДЕРЖАНИЮ И ОФОРМЛЕНИЮ ОТЧЕТА

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

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

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

Упражнение 3. Группировка с помощью статистической надстройки

Упражнение 2. Вычисление показателей вариации

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

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

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

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

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

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

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

номера строки. Адрес ячейки используется для работы с данными (ячейками) и формулами.

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

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

=A1 * $B$2 =СУММ(B2:B151)/150

Статистические функции вставляют в формулы, выбрав в

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

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

При выполнении работы также используется статистическая надстройка Microsoft Excel. Чтобы активировать надстройку в новой версии,необходимо выполнить следующие действия:

1. Нажмите кнопку Microsoft Office , а затем щелкните Параметры Excel.

2. Выберите команду Надстройки и в окне Управление выберите пункт Надстройки Excel.

3. Нажмите кнопку Перейти.

4. В окне Доступные надстройки установите флажок Пакет анализа, а затем нажмите кнопку ОК.

Совет. Если Пакет анализа отсутствует в списке поля Доступные надстройки, то для проведения поиска нажмите кнопку Обзор.

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

5. После загрузки пакета анализа в группе Анализ на вкладке становится доступной команда [Данные → Анализ данных].

В старой версии активировать надстройку можно командой [Сервис → Надстройки] в меню и поставить галочку напротив пункта [Пакет анализа] (см. рис. 1.4).

Рис. 1.4. Включение статистической надстройки MS Excel

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

Исходные данные представляют собой выборку { x ₁, x ₂, …, x_n }, сгенерированную по одному из законов распределения в зависимости от варианта задания (табл. 1.2).

Для генерации исходных данных используется функция [Генерация случайных чисел] статистической надстройки Microsoft Excel (см. рис. 1.5).

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

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

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

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

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

Рис. 1.7. Выбор параметров генератора равномерного распределения

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

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

Рис. 1.8. Генерация случайных чисел и описательная статистика

Для вычисления показателей вариации применяется функция Описательная статистика статистической надстройки MicrosoftExcel [ Данные → Анализ данных→Описательная статистика] (см. рис. 1.9). В диалоговом окне нужно выбрать Входной

интервал, Метки в первой строке, Выходной интервал и Итоговая статистика. Вычислите показатели вариации,указанные в табл. 1.1.

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

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

− выборка считается однородной, если коэффициент вариации V _σ≤30%;

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

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

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

Рис. 1.10. Настройка функции Гистограмма

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

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

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

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

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

− Выходной интервал – расположение результатовгруппировки на листе;

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

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

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

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

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

Рис. 1.12. Настройка результатов группировки

Таблицу необходимо дополнить следующими столбцами:

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

− Частость n_i (%);

− Кумулята K_i (%).

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

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

=100*E3/$E$8.

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

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

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

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

СЧЁТЕСЛИ(B:B;"<="&E6)-СЧЁТЕСЛИ(B:B;"<="&D6). Первая часть приведённой формулы вычисляет количество

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

Рис. 1.13. Группировка данных с использованием формул

По рассматриваемой таблице строится гистограмма (рис. 1.14). для этого выбираем в меню в новой версии [Вставка → Гистограмма], в старой версии [Вставка → Диаграмма → Гистограмма → Обычная гистограмма].

В новой версии вызываем правой кнопкой мыши контекстное меню далее Выбрать данные→Добавить

Нажимаем кнопку Значения и указываем диапазон значений частот. Нажимаем ОК. Далее в правой части окна Выбор источника данных (Подписи горизонтальной оси (категории)) нажимает кнопку Изменить и указываем диапазон значений интервалов → ОК.

В старой версии переходим назакладку Ряд и нажимаем кнопку Добавить. Нажимаем кнопку Значения и указываем диапазон значений частот. Нажимаем кнопку Подписи оси Х и указываем диапазон меток для оси Х. Нажимаемкнопки Далее → Готово. В качестве меток можно указать середины интервалов группировки.

Рис. 1.14. Гистограмма по результатам группировки

Чтобы добавить полигон на график гистограммы, необходимо добавить новый ряд данных. Для этого нужно щелкнуть правой кнопкой мыши по области графика и в контекстном меню выбрать в новой версии [Выбрать данные→Добавить], в старой версии [Исходные данные → Ряд] (рис. 1.15).

Рис. 1.15. Добавление данных для построения полигона для старой версии

Нажимаем кнопки Добавить → Значения и указываем тот же диапазон ячеек, что и для гистограммы. При работе с рядами данных в окне Имя можно ввести комментарий, поясняющий содержание набора данных для графика.

Далее получим два одинаковых графика гистограммы на одном поле, чтобы получить полигон, нужно в новой версии выделить второй созданный график, вызвать контекстное меню → Изменить тип диаграммы → График с маркерами.

Кумулята строится на отдельном графике. В новой версии [Вставка →Точечная → Точечная с прямыми отрезками и маркерами]. В старой версии выбираем в меню [Вставка → Диаграмма → Точечная диаграмма → Точечная диаграмма, на которой значения соединены отрезками]. Добавляем ряд значений, указав диапазоны по оси X ипо оси Y.

Кумулята или кумулятивная кривая в отличие от полигона строится по накопленным частотам или частостям. При этом на оси абсцисс помещают значения признака, а на оси ординат — накопленные частоты или частости.

Пример кумуляты показан на рис. 1.16. На рис. 1.17 можно видеть окончательные вариант графика гистограммы и полигона.

Все графики должны быть оформлены надлежащим образом. Например, минимальное и максимальное значения по оси Y кумуляты должны быть 0 и 100, соответственно (правый клик по оси Y, [Формат оси]); линия кумуляты должна начинаться от x = 0 (правый клик по оси X, [Формат оси], убрать галочку в старой версии [Пересечение с осью Y (значений) в максимальном значении]. Подписи по осям должны бытьинформативными.

В старой версии положение оси Y устанавливается с помощью пункта [Ось Y (значений) пересекает в значении..].

Рис. 1.16. Пример кумуляты

Рис. 1.17. Пример гистограммы и полигона

с теоретическим

Исходная выборка генерируется по стандартному закону распределения с параметрами в соответствии с вариантом задания (см. разд. 4.1). Чтобы сравнить фактическое и теоретическое распределения, необходимо построить их графики. Для работы с теоретическими распределениями используются готовые статистические функции, например, НОРМРАСП и НОРМОБР. Описание статистических функций необходимо самостоятельно изучить, вызвав Справку.

На рис. 1.18 показан пример графика теоретического и фактического распределений.

Чтобы вычислить значение теоретической вероятности попадания случайной величины в интервал [x₁, x ₂), необходимо найти разность вероятности попадания в интервалы [0, x2) и [0, x1) (см. рис. 1.2). Например, вероятность попадания случайной величины в интервал [100, 110) для нормального закона распределения со средним 100 и стандартным отклонением 20,

равна:

=(НОРМРАСП(110;100;20;ИСТИНА)-

НОРМРАСП(100;100;20;ИСТИНА))*100%.

В случае равномерного распределения с нижней границей 50 и верхней 150, может быть использована следующая формула:

=(110–50)/(150–50)–(100–50)/(150–50) =(110–100)/(150–50)*100%.

Рис. 1.18. Эмпирическое (столбики) и теоретическоераспределение (линия)

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

Статистические таблицы должны содержать все элементы, необходимые для анализа материала: заголовки, названия, единицы измерения, диапазоны измерения. Основной заголовок должен отвечать на три вопроса: «Что? Где? Когда?», т.е. содержать информацию об объекте исследования с привязкой к времени и месту.

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

Отчёт заканчивается выводами относительно изучаемой выборки. В выводах не пересказывают этапы проведённых работ, а кратко излагают результаты. Например:

− какие данные проанализированы;

− есть ли явные ошибки и несоответствия в данных;

− какие закономерности в данных обнаружены;

− какими графиками и показателями это подтверждается.

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

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

1. Ознакомьтесь с описанием следующих функций:

СТАНДОКЛ, СТАНДОТКЛП, НОРМРАСП.

2. Сгенерируйте исходные данные.

3. Вычислите основные статистические показатели тремя способами:

− с помощью формул;

− с помощью статистических функций;

− с помощью статистической надстройки.

4. Сравните результаты расчетов.

5. Сделайте вывод об однородности выборки.

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

7. Проведите группировку данных двумя способами: − с помощью стандартных функций Excel;

− с помощью статистической надстройки.

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

9. Постройте гистограмму, полигон и кумуляту двумя пособами:

− с помощью статистической надстройки;

− «вручную».

10. По виду гистограммы определите модальный интервал.

11. По графику кумуляты найдите 80%-й квантиль.

12. Постройте гистограммы теоретического и эмпирического распределений на одном графике.

13. Сделайте вывод о близости эмпирического распределения к теоретическому.

14. Оформите отчёт в соответствии с требованиями.

Дата добавления: 2014-12-23