Порядок виконання роботи

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

Стовпчик1		Стовпчик2
Середнє		Середнє
Стандартна помилка		Стандартна помилка
Медіана		Медіана
Мода		Мода
Стандартне відхилення		Стандартне відхилення
Дисперсія вибірки		Дисперсія вибірки
Ексцес		Ексцес
Асиметричність		Асиметричність
Інтервал		Інтервал
Мінімум		Мінімум
Максимум		Максимум
Сума		Сума
Рахунок		Рахунок
Рівень надійності(95,4%)		Рівень надійності(95,4%)

Між термінологією інструменту Описова статистика і термінами, прийнятими у вітчизняній статистиці, є ряд розбіжностей. Узгодження термінології наводиться в табл. 4.1.

Таблиця 4.1

Статистична інтерпретація параметрів Описової статистики

Параметр інструмента «Описова статистика»	Статистичний показник	Помітка
Середнє	Середня арифметична величина ознаки у вибірці, обчислена за несгруппірованних даними
Стандартна помилка	Середня помилка вибірки - середньоквадратичне відхилення вибіркової середньої від математичного очікування генеральної середньої
Медіана	Значення ознаки, що припадає на середину рангового ряду вибіркових даних	Me
Мода	Значення ознаки, що повторюється в вибірці з найбільшою частотою	Mo
Стандартне відхилення	Генеральне середньоквадратичне відхилення, оцінене за вибіркою	σ N
Дисперсія вибірки	Генеральна дисперсія, оцінена по вибірці	σ 2 N
Ексцес	Коефіцієнт ексцесу, що оцінює по вибірці значення ексцесу у генеральній сукупності	Ek N
Асиметричність	Коефіцієнт асиметрії, що оцінює по вибірці величину асиметрії у генеральній сукупності	As N
Інтервал	Розмах варіації в вибірці	R
Мінимум	Мінімальне значення ознаки у вибірці	xmin
Максимум	Максимальне значення ознаки у вибірці	xmax
Сума	Сумарне значення елементів вибірки
Рахунок	Об’єм вибірки	n
Рівень надійності (95,0%)	Гранична помилка вибірки, оцінена за заданим рівнем надійності

Обчислені значення всіх перерахованих вище показників представляються в єдиній результативній таблиці на робочому листі Excel. При цьому показники можуть розраховуватися одразу для кількох рядів даних відповідно до заданим вхідним діапазоном осередків. Так, для вхідного діапазону з двома рядами даних результативна таблиця буде складатися з двох стовпців значень описових параметрів (мал. 6). Саме такий формат має таблиця 3, зарезервована в робочому файлі персональної папки студента для показників, що розраховуються в режимі Описательная статистика.

2. Завдання керуючої інформації в діалоговому вікні інструменту Описова статистика

Рис. 4.2. Вікно «Описова статистика»

Запуск інструменту Описова статистика здійснюється наступною послідовністю дій: Сервіс => Аналіз даних => Описательная статистика => ОК.

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

1. Поле Вхідний інтервал - вводиться посилання на діапазон комірок, що містять значення аналізованого ознаки. В якості вхідного інтервалу може бути вказаний діапазон, який містить ряди значень одразу декількох аналізованих ознак. В такому випадку показники Описової статистики будуть розраховані для кожного ряду і представлені в єдиній таблиці у вигляді окремих стовпців (табл. 4.2).

2. Перемикач Групування: за стовпцями / рядкам - встановлюється в положення по стовпцях або по рядках залежно від того, в якому напрямку розташовуються аналізовані дані у вхідному діапазоні - вертикальному (по стовпцях) або горизонтальному (по рядках).

3. Прапорець Мітки в першому рядку - встановлюється в активний стан, якщо перший рядок у вхідному діапазоні містить заголовки. Якщо заголовки відсутні, полі не активізується. В цьому випадку будуть автоматично створені стандартні назви для даних вихідного діапазону.

4. Поле Вихідний інтервал - вводиться посилання на комірку заголовка першого шпальти вихідний результативною таблиці. Розмір вихідного діапазону комірок визначається автоматично. У разі можливого накладення вихідного діапазону на інші дані на екрані з'явиться відповідне повідомлення.

5. Перемикачі Новий робочий лист і Нова робоча книга - встановлюються в активне положення при необхідності відкриття відповідно нового аркуша або нової книги. У новому аркуші результати аналізу розташовуються починаючи з клітинки А1, в новій книзі - на першому аркуші, починаючи з клітинки А1.

6. Прапорець Підсумкова статистика - встановлюється в активний стан, якщо для даних вхідного діапазону необхідно провести розрахунок основних показників, перерахованих в макеті результативною таблиці на рис. 6.

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

Значення рівня надійності виражається у відсотках і задається в поле навпроти прапорця Рівень надійності. Рівень надійності 95,0% (що рівносильно довірчої ймовірності P = 0,95 або ж рівнем значущості α = 0,05) фіксується в поле автоматично.

8. Прапорці К-тий найменший і К-тий найбільший - активізуються, якщо в результативну таблицю необхідно включити рядок відповідно для k-того найменшого (починаючи з мінімуму xmin) і k-того найбільшого (починаючи з максимуму xmax) значень елементів у вибірці. В цьому випадку в поле, розташованому навпроти кожного прапорця, вводиться число k. При k = 1 вихідні рядки міститимуть відповідно xmin і xmax.

3. Розрахунок граничної помилки вибірки

Розрахунок граничної помилки вибірки здійснюється в режимі Описательная статистика. Значення для рівня надійності 95,0% автоматично виводиться в результативній таблиці. Для розрахунку при рівнях надійності 99,7% і 68,3% необхідно двічі звернутися до інструменту Описова статистика і в діалоговому вікні активізувати поля Вхідний інтервал, Групування, Вихідний інтервал, Рівень надійності, внісши в них відповідні значення. Оскільки прапорець Підсумкова статистика при цьому не активізується, дублювання обчислень описових показників не відбувається.

Для граничних помилок вибірки при рівнях надійності 68,3% і 99,7% в робочому файлі персональної папки студента зарезервовані таблиці 4а та 4б відповідно, мають наступний формат:

Таблиця 4

Гранична помилка вибірки

Стовпчик1		Стовпчик2
Рівень надійності ( …%)		Рівень надійності ( …%)

4.Використання статистичних функцій інструменту Майстер функцій

Крім показників, що визначаються в режимі Описательная статистика, для аналізу статистичних властивостей досліджуваної сукупності необхідні додатково наступні вибіркові показники (які або взагалі не обчислюються інструментом Описательная статистика, або обчислюються для генеральної сукупності):

• вибіркове середньоквадратичне відхилення σ_n;

• вибіркова дисперсія σ²_n;

• вибіркове середнє лінійне відхилення ;

• коефіцієнт варіації ознаки у вибірці ;

• коефіцієнт асиметрії Пірсона Аs _п.

Для цих п'яти показників в робочому файлі персональної папки студента зарезервована таблиця 4.3 такого формату:

Таблиця 4.3

Вибіркові показники сукупності

Перша ознака		Друга ознака
Стандартне відхиленняσn		Стандартне відхиленняσn
Дисперсія σ2n		Дисперсія σ2n
Середнє лінійне відхилення		Середнє лінійне відхилення
Коефіцієнт варіації		Коефіцієнт варіації
Коефіцієнт асиметрії Asп		Коефіцієнт асиметрії Asп

Для обчислення показників σ_n, σ²_n і використовуються відповідні статистичні функції інструменту Майстер функцій:

1. СТАНДОТКЛОНП (Діапазон осередків) - оцінює середньоквадратичне відхилення σ в припущенні, що вихідні дані представляють всю сукупність (не є вибіркою з генеральної сукупності).

2. ДІСПР (Діапазон осередків) - оцінює дисперсію σ² в припущенні, що вихідні дані представляють всю сукупність.

3. СРОТКЛ (Діапазон осередків) - розраховує для вибіркових даних середнє лінійне відхилення .

Технологія розрахунку показників в режимі Майстер функцій:

1. Викликати Майстер функцій, натиснувши кнопку на панелі інструментів.

2. В Майстрі функцій серед пропонованих категорій функцій вибрати категорію Статистичні, а потім - необхідну функцію (СТАНДОТКЛОНП, ДІСПР або СРОТКЛ).

3. У діалоговому вікні в полі Число 1 вказати діапазон комірок, що містять значення аналізованого ознаки.

Для розрахунку коефіцієнта варіації за формулою (5) необхідно використовувати середнє арифметичне значення з результативною таблиці Описової статистики і вибіркове середньоквадратичне відхилення σn з таблиці вибіркових показників.

Для розрахунку коефіцієнта асиметрії Пірсона Аs_п по формулі (11) необхідно використовувати середнє арифметичне значення і моду Мо з результативною таблиці

Описової статистики, а також вибіркове середньоквадратичне відхилення σ_n з таблиці вибіркових показників.

4. Розташування даних на робочому аркуші Excel

Вихідні дані і їх описові статистичні характеристики розташовуються в таблицях робочого файлу персональної папки студента на відповідно до табл.3-М. При цьому враховується наступна особливість автоматичної побудови вихідних таблиць інструментом Описательная статистика.

Інструменти Пакета аналізу будують вихідні таблиці зі стандартними заголовками стовпців (наприклад, таблиця описових статистик на мал.6 має стандартні заголовки Стовпчик1, Стовпчик2). При цьому розміщення вихідних таблиць на робочому аркуші здійснюється починаючи з клітинки заголовка першого шпальти (у прикладі це осередок з заголовком Стовпчик1). Саме адресу цієї комірки і повинен задаватися в поле Вихідний інтервал діалогового вікна при формуванні вихідних таблиць.

Таблиця 4.3

Розташування даних на робочому аркуші Excel

Вихідні дані і їх статистичні характеристики	Ознака Середньорічна вартість основних виробничих фондів, млн. грн.	Ознака Випуск продукції, млн. грн.
Вихідні дані після видалення «аномальних» значень	B4:B33	С4:C33
Описові статистики	A46	C46
Гранична помилка вибірки при P=0,683	А67	С67
Гранична помилка вибірки при P=0,997	А75	С75
Середнє квадратичне відхилення вибірки σn	B83	D83
Дисперсія вибірки σ2n	B84	D84
Середнє лінійне відхилення	B85	D85
Коефіцієнт варіації Vσ	B86	D86
Коефіцієнт асиметрії Аsn	B87	D87

Виконання завдання включає три етапи:

1.Розрахунок описових параметрів вибіркової і генеральної сукупностей з використанням інструменту Описова статистика.

2.Оцінка граничних помилок вибірки для різних рівнів надійності в режимі Описова статистика.

3.Розрахунок описових параметрів вибіркової сукупності з використанням інструменту Майстер функцій.

Етап 1. Розрахунок описових параметрів вибіркової і генеральної сукупностей з використанням інструменту Описова статистика

Алгоритм 1.1. Розрахунок описових статистик

1.Сервіс => Аналіз даних => Описова статистика => OK;

2.Вхідний інтервал <= діапазон комірок таблиці, виділений згідно табл. 3-М для значень ознак Вартість основних фондів і Випуск продукції;

3.Групування => за стовпцями;

4.Кінцева статистика - Активізувати;

5.Рівень надійності - Активізувати;

6.Рівень надійності <= 95,4;

7.Вихідний інтервал <= адреса комірки заголовка першої шпальти табл.3 (див. Табл. 3-М);

8.OK;

9.При появі вікна з повідомленням "Вихідний інтервал накладається на наявні дані" => ОК.

У результаті зазначених дій Excel здійснює виведення таблиці описових статистик в заданий діапазон робочого файлу.

Етап 2. Оцінка граничних помилок вибірки для різних рівнів надійності в режимі

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

Алгоритм 2.1. Розрахунок граничної помилки вибірки при P = 0,683

1. Сервіс => Аналіз даних => Описова статистика => OK;

2. Вхідний інтервал <= діапазон комірок таблиці, виділений згідно табл. 3-М для значень ознак Вартість основних фондів і Випуск продукції;

3. Підсумкова статистика - Зняти прапорець;

4. Рівень надійності - Активізувати;

5. Рівень надійності <= 68,3;

6. Вихідний інтервал <= адреса комірки, виділеної згідно табл. 3-М для граничної помилки вибірки при P = 0,683; 7. OK;

8. При появі вікна з повідомленням "Вихідний інтервал накладається на наявні дані" => ОК.

Алгоритм 2.2. Розрахунок граничної помилки вибірки при P = 0,997

1. Сервіс => Аналіз даних => Описательная статистика => OK;

2. Вхідний інтервал <= діапазон комірок таблиці, виділений згідно табл. 3-М для значень ознак Вартість основних фондів і Випуск продукції після видалення аномальних значень;

3. Підсумкова статистика - Зняти прапорець;

4. Рівень надійності - Активізувати;

5. Рівень надійності <= 99,7;

6. Вихідний інтервал <= адреса комірки для граничної помилки вибірки при P = 0,997;

7. OK;

8. При появі вікна з повідомленням "Вихідний інтервал накладається на наявні дані" => ОК.

В результаті роботи алгоритмів 2.1 і 2.2 Excel виводить у відповідні комірки робочого файлу значення граничних помилок вибірки при P = 0,683 і P = 0,997.

Етап 3. Розрахунок описових параметрів вибіркової сукупності з використанням інструменту Майстер функцій

Алгоритм 3.1. Розрахунок вибіркового стандартного відхилення σ_n для ознаки Середньорічна вартість основних виробничих фондів

1. Встановити курсор в комірку для середнього квадратичного відхилення першого ознаки;

2. Вставка => Функція;

3. Статистичні => СТАНДОТКЛОНП => ОК;

4. Число 1 <= діапазон комірок, що містять значення першого ознаки.

Алгоритм 3.2. Розрахунок вибіркового стандартного відхилення σ_n для ознаки Випуск продукції. Встановити курсор в комірку для середнього квадратичного відхилення другої ознаки;

2. Вставка => Функція;

3. Статистичні => СТАНДОТКЛОНП => ОК;

4. Число 1 <= діапазон комірок, що містять значення другої ознаки.

Алгоритм 3.3. Розрахунок вибіркової дисперсії σ²_n для ознаки Середньорічна вартість основних виробничих фондів

1. Встановити курсор в комірку, виділену згідно табл.3-М для вибіркової дисперсії першого ознаки;

2. Вставка => Функція;

3. Статистичні => ДІСПР => ОК;

4. Число1 <= діапазон комірок табл.1, що містить значення першої ознаки.

Алгоритм 3.4. Розрахунок вибіркової дисперсії σ²_n за ознакою Випуск продукції

1. Встановити курсор в комірку, виділену згідно табл.3-М для вибіркової дисперсії другої ознаки;

2. Вставка => Функція;

3. Статистичні => ДІСПР => ОК;

4. Число1 <= діапазон комірок табл.1, що містять значення другої ознаки.

Алгоритм 3.5. Розрахунок вибіркового середнього лінійного відхилення за ознакою Середньорічна вартість основних виробничих фондів

1. Встановити курсор в комірку, виділену згідно табл.3-М для середнього лінійного відхилення першого ознаки;

2. Вставка => Функція;

3. Статистичні => СРОТКЛ => ОК;

4. Число1 <= діапазон комірок табл.1, що містять значення першого ознаки.

Алгоритм 3.6. Розрахунок вибіркового середнього лінійного відхилення за ознакою Випуск продукції

1. Встановити курсор в комірку, виділену згідно табл.3-М для середнього лінійного відхилення другої ознаки;

2. Вставка => Функція;

3. Статистичні => СРОТКЛ => ОК;

4. Число1 <= діапазон комірок табл.1, що містять значення другої ознаки.

Алгоритм 3.7. Розрахунок коефіцієнта варіації за ознакою Середньорічна вартість основних виробничих фондів

1. Встановити курсор в комірку, виділену згідно табл.3-М для коефіцієнта варіації першого ознаки;

2. В активізовану комірку ввести формулу = B83 / B48 * 100.

Алгоритм 3.8. Розрахунок коефіцієнта варіації за ознакою Випуск продукції

1. Встановити курсор в комірку, виділену згідно табл.3-М для коефіцієнта варіації другої ознаки;

2. В активізовану комірку ввести формулу = D83 / D48 * 100.

Алгоритм 3.9. Розрахунок вибіркового коефіцієнта асиметрії Пірсона As_п за ознакою Середньорічна вартість основних виробничих фондів

1. Встановити курсор в комірку, виділену згідно табл.3-М для коефіцієнта асиметрії першого ознаки;

2. В активізовану комірку ввести формулу = (B48-B51) / B83.

Алгоритм 3.10. Розрахунок вибіркового коефіцієнта асиметрії Пірсона As_п за ознакою Випуск продукції

1. Встановити курсор в комірку, виділену згідно табл.3 для коефіцієнта асиметрії другої ознаки;

2. В активізовану комірку ввести формулу = (D48-D51) / D83

В результаті роботи алгоритмів 3.1 - 3.10 Excel здійснює висновок значень вибіркових параметрів σ_n, σ²_n, , і Аs_n у відповідні комірки робочого листа