Контрольная работа

Задание 1. Провести анализ данных в рамках описательной статистики c использованием средств Вставка функций и Мастер диаграмм MS Excel.

1. Запустите MS Excel и сохраните созданную при запуске книгу под именем Контрольная работа_ФИО.

2. Переименуйте ярлык рабочего листа Лист 1 в ярлык Описательная статистика.

3. Используя инструмент Генерация случайных чисел пакета Анализ данных, сформируйте массив исходных данных в соответствии с выданным преподавателем индивидуальным заданием и оформите таблицу по Образцу 1 (рис. 1).

4. Аналогично введите и оформите заголовок к таблице со статистикой, заголовки строк статистической таблицы. Выполните расчеты указанных в статистической таблице параметров, вставляя при помощи средства  Вставка функции расчетные формулы, как показано на Образце 1 (см. рис.1).

5. Сформируйте таблицу частот исследуемой величины, выполнив группировку данных и расчеты в соответствии с Образцом 2 (рис. 2.) непосредственным вводом формул и при помощи средства  Вставка функции:

Рис. 1. Образец 1

–  введите заголовки строк и столбцов по образцу;

– вставьте формулу для вычисления минимального числа интервалов группирования по эмпирическому соотношению k £ 5×lg N при помощи средства Вставка функции;

Рис. 2. Образец 2

– вставьте формулу для расчета ширины интервала группирования методом непосредственного ввода;

– аналогично вставьте формулы для вычисления правых границ интервалов, как показано на Образце 2;

– вставьте формулу для расчета частот с применением функции массивов: выделите диапазон ячеек частот / используйте инструмент Вставка функции  / найдите и выберите функцию ЧАСТОТА из категории Статистические / нажмите кнопку ОК / в окне вставки функции справа от поля Массив_данных нажмите кнопку сворачивания  / выделите мышью диапазон ячеек исходных данных / нажмите кнопку разворачивания  / справа от поля Двоичный_массив нажмите кнопку сворачивания / выделите мышью диапазон ячеек интервалов / нажмите кнопку разворачивания  / нажмите одновременно клавиши Ctrl+Shift+Enter для фиксации функции массива.

6. Постройте гистограмму для исследуемой величины с применением мастера диаграмм: выделите диапазон ячеек с таблицей частот / используйте инструмент Мастер диаграмм / на вкладке Стандартные в поле Тип выберите вариант Гистограмма  и нажмите кнопку Далее / в окне …источник данных диаграммы на вкладке Диапазон данных включите переключатель в столбцах / на вкладке Ряд нажмите кнопку сворачивания  справа от поля Подписи оси Х / выделите диапазон ячеек интервалов / нажмите кнопку разворачивания  / в поле Ряд выберите Интервалы… / нажмите кнопку Удалить / в поле Ряд выберите Накопленные частоты… / нажмите кнопку Удалить / нажмите кнопку Далее> / в окне …параметры диаграммы на вкладке Заголовки напечатайте в полях Название диаграммы текст Гистограмма 1, Ось Х (категорий) – текст Интервалы / на вкладке Линии сетки установите флажки основные линии в разделах Ось Х и Ось Y / нажмите кнопку Далее> / в окне …размещение диаграммы включите переключатель имеющемся / нажмите кнопку Готово.

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

Задание 2. Провести аппроксимацию и сглаживание построенных в Задании 1 гистограмм при помощи построения линий тренда основных типов – линейного, логарифмического, полиномиального, степенного, экспоненциального и линейной фильтрации.

1. Перейдите на следующий рабочий лист и переименуйте его ярлык в Тренд 1.
2. Выделите и скопируйте  в буфер гистограмму Гистограмма 1. Вставьте график из буфера в начало рабочего листа Тренд 1.
3. Добавьте линейный тренд для ряда Частоты на гистограмму: выделите Гистограмму 1 / щелкните правой кнопкой мыши по одному из столбиков ряда Частоты / выберите пункт Добавить линию тренда контекстного меню / в окне Линия тренда на вкладке Тип выберите образец Линейная / нажмите кнопку ОК.
4. Скопируйте Гистограмму 1 с линейным трендом на рабочем листе Тренд 1 в позицию под нижней границей рамки уже имеющейся диаграммы  с линейным трендом, измените тип линии тренда на логарифмический.
5. Аналогично постройте версии Гистограммы 1 с остальными типами линий тренда (полиномиальная степени 2, степенная, экспоненциальная и линейная фильтрация), сравните варианты и выберите два наиболее соответствующих данной эмпирической гистограмме.
6. Проанализируйте поведение полиномиального тренда при изменении степени полинома:

- выделите гистограмму с полиномиальным трендом и вставьте две ее копии на новый рабочий лист, предварительно переименованный в Полиномиальный тренд_1;

- при помощи контекстного меню полиномиального тренда на второй копии гистограммы откройте диалоговое окно Формат линии тренда / на вкладке Тип для образца Полиномиальная в поле со списком Степень установите значение 3 (вместо предыдущего 2) / на вкладке Параметры установите флажки показывать уравнение на диаграмме, поместить на диаграмму величину достоверности аппроксимации / нажмите кнопку ОК;

- сформируйте аналогичные версии для значений степеней полиномиальной аппроксимации 4 и 5;

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

Задание 3. Провести проверку гипотезы о значимости полученных оценок среднего и дисперсии выборки.

Проверка гипотез о значимости полученных оценок параметров распределений основана на построении доверительных интервалов для оценок с заданным уровнем значимости a (обычно 0,05). Нулевой гипотезой считается тот факт, что истинное значение параметра попадает в построенный доверительный интервал. Доверительный интервал для среднего M определяется как M − T{a, m}S/, M + T{a, m}S/, где T{a, m} – табличное значение распределения Стьюдента с m = N−1 степенями свободы и уровнем значимости a. Доверительный интервал для среднеквадратичного отклонения S с заданным уровнем значимости a: S·, S·, где XI{1-α/2, m } и XI{α/2, m }– табличные значения распределения χ² с m = N−1 степенями свободы для доверительных вероятностей 1−a/2 и a/2.

1. Перейдите на свободный рабочий лист (при отсутствии такового вставьте новый и перетащите его ярлык в конец книги) и переименуйте ярлык рабочего листа в Доверительные интервалы.
2. Скопируйте в начало листа таблицы с исходными данными и  со статистикой с листа Описательная статистика.
3. При помощи средства Вставка функций постройте по расчетным значениям среднего и дисперсии доверительные интервалы для среднего и среднеквадратичного отклонения, следуя примерному Образцу 3 (рис. 3).
4. Проанализируйте полученные результаты и сделайте выводы.

Рис. 3. Образец 3

Задание 4. Провести проверку гипотезы о принадлежности исходной совокупности данных к нормальному распределению при помощи эмпирического теста на нормальность c использованием средства Вставка функций MS Excel.

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

– 99,7 % отклонений от среднего меньше 3S;

– 68,3 % отклонений меньше S;

– 50 % отклонений меньше 0,625S.

В случае невыполнения хотя бы одного из условий эмпирического теста необходима дополнительная проверка исходной гипотезы о нормальности при помощи, например, критерия согласия χ². При выполнении всех трех условий гипотеза о нормальном законе распределения исходных данных принимается.

1. Перейдите на свободный рабочий лист (при отсутствии такового вставьте новый и перетащите его ярлык в конец книги) и переименуйте ярлык рабочего листа в Тест_норм.
2. Скопируйте массив исходных данных с рабочего листа Описательная статистика в начало листа Тест_норм и оформите таблицу в соответствии с  образцом 4 (рис 4).
3. Используя средство Вставка функции, рассчитайте среднее значение и среднеквадратичное отклонение в соответствии с образцом 4 (см. рис 4).
4. Рассчитайте массив отклонений выборочных значений от среднего:

- вставьте в  ячейку А15 формулу в соответствии с образцом 4;

- используя маркер автозаполнения ячейки А15, растяните формулу в необходимое количество ячеек по горизонтали и вертикали.

Рис. 4. Образец 4

5. Сформируйте таблицу проверки условий эмпирического теста на нормальность и вставьте расчетные формулы согласно рис. 5.

Рис. 5. Образец 5

6. Проанализируйте полученные результаты и сделайте выводы.

Задание 5. Провести проверку гипотезы о  принадлежности исходной совокупности данных к нормальному распределению при помощи критерия согласия Пирсона (χ²) c использованием средств Вставка функций.

Тест χ² проверяет нулевую гипотезу о принадлежности выборки к конкретному типу распределения (например нормальному). При расчете критерия следует соблюдать следующие условия: число интервалов группирования должно быть больше 5, теоретическая частота попадания в интервал должна быть не менее 5. Если теоретическая частота меньше 5, данный интервал следует объединить с соседним.

1. Перейдите на свободный рабочий лист (при отсутствии такового вставьте новый и перетащите его ярлык в конец книги) и переименуйте ярлык рабочего листа в Тест_ хи-квадрат.
2.  В ячейке А1 введите заголовок столбца данных х (норм.).
3. Скопируйте исходные данные с листа Описательная статистика на лист Тест_хи-квадрат в позицию начиная с ячейки А2.
4. Реорганизуйте скопированный массив данных на листе Тест_хи-квадрат при помощи приемов перемещения диапазонов ячеек так, чтобы данные размещались в одном столбце А.
5. При помощи средства Сервис / Анализ данных рассчитайте по исходным данным на листе Тест_хи-квадрат описательную статистику и постройте таблицу частот и гистограмму, следуя примерному Образцу 6 (рис. 6).

Рис. 6. Образец 6

Рис.7. Образец 7

6. Используя построенную таблицу частот и рассчитанные среднее значение и среднеквадратичное отклонение, а также стандартные встроенные функции, сформируйте таблицу для расчета статистики χ² по Образцу 7 (рис. 7, ячейки С18:Н34). Обратите внимание на ввод максимального значения варианты вместо текста Еще в исходной таблице частот (ячейка С29) и расчетных формул теоретических частот для самого нижнего и самого верхнего интервала группирования. Эти действия необходимы для корректного вычисления теоретических частот. Обратите внимание также на то, что в ячейках Е20 – Е28 расчетные формулы показаны не в полном объеме. В столбцах скорректированных теоретических и эмпирических частот выполнено объединение тех карманов, где значение теоретических частот менее 5. Это условие правильного применения критерия. Расчетная формула для числа степеней свободы распределения χ² определяется разностью числа карманов (с учетом их объединения) и числа 3.
7. Проанализируйте полученные результаты и сделайте выводы.