Laboratornye_raboty_semestr2_modul2

Информатика и кт

Специальность Международная экономика, финансы и кредит, учет и аудит

Семестр 2 Модуль 2

2

Лабораторная работа №18

Статистика в MS Excel

Надбудова Аналіз даних

Якщо вбудованих статистичних функцій недостатньо, можна звернутися до

Пакета аналізу.

Щоб одержати доступ до інструментів Пакета анализа необхідно:

∙виконати команду Сервис/Анализ данных;

∙для використання інструмента аналізу, вибрати його ім’я в списку і натиснути кнопку ОК;

∙заповнити діалогове вікно , що відкрилося , (у більшості випадків це означає завдання вхідного діапазону з даними, що ви збираєтеся аналізувати, указівка верхньої лівої комірки вихідного діапазону, у який повинні бути приміщені результати, і вибір потрібних параметрів. Группирование: установити перемикач у положення По столбцам або По строкам у залежності від розташування даних у вхідному діапазоні. Установити перемикач у положення Метки в первой строке, якщо перший рядок у вхідному діапазоні містить назви стовпців або установити перемикач у положення Мітки в первом столбце, якщо назви рядків знаходяться в першому стовпці вхідного діапазону. Якщо вхідної діапазон не містить міток, то необхідні заголовки у вихідному діапазоні будуть

створені автоматично).

Якщо надбудова Анализ данных відсутня, то її можна підключити за допомогою команди Сервис/Надстройки/Пакет анализа VBA (Analysis ToolPak VBA).

До інструментів Пакета анализа, наприклад, відносяться Описательная статистика, Корреляция, Регрессия.

Описательная статистика

Інструмент Описательная статистика пропонує таблицю основних статистичних характеристик для одного або декількох вхідних значень (рис. 1):

Выходной интервал цього інструмента містить такі статистичні характеристики: середнє, стандартна помилка, медіана, мода, стандартне відхилення, дисперсія, коефіцієнт ексцесу, коефіцієнт асиметрії, інтервал (розмах), мінімальне значення, максимальне значення, сума, число значень, k-е найбільше і найменше значення (для будь-якого заданого значення k) і рівень значимості для середнього. Установити прапорець Итоговая статистика, якщо потрібний повний список характеристик, у противному випадку відзначити конкретні характеристики, що повинні бути присутнім у вихідній таблиці. Більшість з отриманих характеристик, отриманих за допомогою пакета аналізу Описательная статистика можна одержати за допомогою убудованих статистичних формул.

3

Рис. 1. Діалогове вікно Описательная статистика

Розглянемо приклад статистичної обробки даних, що містяться в таблиці

(рис. 2).

Рис. 2 Вхідні дані для проведення статистичної обробки даних

Знайдемо основні статистичні параметри, використовуючи інструмент

Анализ данных/Описательная статистика (рис. 3).

Рис. 3 Задання параметрів Описательной статистики для приклада

У результаті використання цього інструмента (рис. 4) буде отримана таблиця (зліва), яку необхідно перетворити. Для цього видалені непотрібні рядки і стовпці (справа):

4

Рис. 4 Результат використання інструмента Описательная статистика Корреляция

Корреляция використовується для кількісної оцінки взаємозв’язку двох наборів даних. Кореляційний аналіз дає можливість установити, чи асоційовані набори даних по розміру, тобто, великі значення з одного набору даних зв’язані з великими значеннями іншого набору (позитивна кореляція), або, навпаки, малі значення одного набору зв’язані з великими значеннями іншого (негативна кореляція), або дані двох діапазонів ніяк не зв’язана (кореляція близька до нуля). У діалоговому вікні Корреляция (рис. 5) указується Входной интервал – посилання на діапазон, що містить аналізовані дані. Посилання повинне перебувати як мінімум із двох суміжних діапазонів даних, організованих у виді стовпців або рядків. Іньші параметри в цьому діалоговому вікні задаються таким же чином, як в вікні Описательная статистика.

Рис. 5 Діалогове вікно Корреляция

Використовуючи інструмент Анализ данных/Корреляция знайдемо коефіцієнт кореляції між наступними параметрами: Мужчины і Женщины (рис. 2).

У діалоговому вікні Корреляция задаються параметри (рис. 6).

5

Рис. 6 Завдання параметрів кореляції

У результаті виконання цього інструмента утворюється кореляційна таблиця

(рис. 7).

Рис. 7 Результат застосування інструмента Кореляция

У кореляційній таблиці значення рівне 0,486815, спостерігається між параметрами Мужчины і Женщины (цей зв’язок не можна назвати сильним).

Регрессия

Регрессия використовується для добору графіка лінії регресії. Параметри діалогового вікна Регрессия (рис. 8):

Входной интервал Y – посилання на діапазон аналізованих залежних даних (діапазон повинний перебувати з одного стовпця). Входной интервал X – посилання на діапазон незалежних даних, підметів аналізу. Уровень надежности

– установити прапорець, щоб включити у вихідний діапазон додатковий рівень. У відповідне поле ввести рівень надійності, що буде використаний додатково до рівня 95%, застосовуваному по умовчанню. Константа-ноль – установити прапорець, щоб лінія регресії пройшла через початок координат. Остатки – установити прапорець, щоб уключити залишки у вихідний діапазон. Стандартизированные остатки – установити прапорець, щоб уключити стандартизовані залишки у вихідний діапазон. График остатков – установити прапорець, щоб побудувати діаграму залишків для кожної незалежної перемінної. График подбора – установити прапорець, щоб побудувати діаграми для даних, що спостерігаються передвіщаються значень для кожної незалежної перемінної. График нормальной вероятности – установити прапорець, щоб побудувати діаграму нормальної імовірності.

6

Рис. 8 Діалогове вікно Регрессия

Використовуючи інструмент Анализ данных/Регрессия проведемо регресійний аналіз для параметрів Мужчины і Женщины (рис. 2).

У діалоговому вікні Регрессия задаються параметри (рис. 9).

Рис. 9 Завдання параметрів регресії

У результаті застосування цього інструмента отримана група таблиць (рис.

10).

7

Рис. 10 Результат застосування інструмента Регрессия Трендові моделі

Лінії тренда звичайно використовуються в задачах прогнозування. Такі задачі вирішують за допомогою методів регресійного аналізу. За допомогою регресійного аналізу можна продовжити лінію тренда вперед або назад, екстраполювати її за межі, у яких дані уже відомі, і показати тенденцію їхньої зміни. Можна також побудувати лінію ковзного середнього, що згладжує випадкові дані, виразно демонструє модель і простежує тенденцію зміни даних.

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

(рис. 11).

Рис. 11 Побудова кореляційного поля

У контекстному меню будь-якої точки діаграми вибрати команду Добавить линию тренда…(рис. 12).

8

рис. 12 Добавление линии тренда

З’явиться діалогове вікно Линия тренда, у який у вкладинці Тип указується тип лінії тренда (Линейная). На вкладинці Параметры виставляється прапорець

Показывать уравнение на диаграмме (рис. 13).

рис. 13 Діалогове вікно Линия тренда вкладки Тип та Параметры

У результаті отриманий графік лінії лінійного тренда (рис. 14).

	Корреляционное поле между параметрами
		Мужчины и Среднее
Среднее	10	y = 0,6623x + 2,1591
	5
	0
	0	5	10	15
		Мужчины

рис. 14 Результат побудови трендової моделі

Усі типи ліній тренду використовуються для апроксимації даних по методу найменших квадратів відповідно до рівнянь.

Лінійна. y = kx + b

9

де k – кут нахилу і b – координата перетинання осі абсцис. Поліноміальна

y = c0 + c1 x + c2 x 2 + Kc6 x6

де c0 Kc6 – константи.

Логарифмічна. y = c ln x + b

де c і b – константи, ln – функція натурального логарифма. Експоненціальна

y = cebx

де c і b – константи, e основа натурального логарифма. Показова

y = cxb

де c і b – константи.

Індивідуальні варіанти лабораторної роботи Вариант 1

1.Используя встроенные статистические функции MS Excel и Анализ данных, вычислить по предлагаемым данным среднее арифметическое, дисперсию, стандартное отклонение, моду, медиану для следующих параметров: производительность труда и коэффициент механизации.

2.Используя встроенную статистическую функцию MS Excel и инструмент Корреляция Анализа данных вычислить коэффициент корреляции (тесноту связи между теми же параметрами).

3.Используя функции НАКЛОН и ОТРЕЗОК (получение уравнения линейной регрессии), а также функцию ПРЕДСКАЗ (получение теоретических значений по линии регрессии) исследовать зависимость производительности труда (y) от уровня механизации работ (x) по данным 14 промышленных предприятий; построить корреляционное поле и график теоретической линии регрессии (на основании данных, полученных по функции ПРЕДСКАЗ). Для проверки параметров теоретической линии регрессии построить линию тренда (на

10

другом графике) с указанием уравнения регрессии. Определить значение производительности труда при коэффициенте механизации, равном 80%.

Предприятие	Коэффициент	Производительность
	механизации труда (%)	труда (т/ч)
1	32	20
2	30	24
3	36	28
4	40	30
5	41	31
6	47	33
7	56	34
8	54	37
9	60	38
10	55	40
11	61	41
12	67	43
13	69	45
14	76	48

Вариант 2

1.Используя встроенные статистические функции MS Excel и Анализ данных, вычислить по предлагаемым данным среднее арифметическое, дисперсию, стандартное отклонение, моду, медиану для следующих параметров: розничная цена на принтеры и скорость печати графики.

2.Используя встроенную статистическую функцию MS Excel и инструмент Корреляция Анализа данных вычислить коэффициент корреляции (тесноту связи между теми же параметрами).

3.Используя функции НАКЛОН и ОТРЕЗОК (получение уравнения линейной регрессии), а также функцию ПРЕДСКАЗ (получение теоретических значений по линии регрессии) исследовать зависимость розничной цены (у) от скорости печати графики (х); построить корреляционное поле и график теоретической линии регрессии (на основании данных, полученных по функции ПРЕДСКАЗ). Для проверки параметров теоретической линии регрессии построить линию тренда (на другом графике) с указанием уравнения регрессии. Определить