Групування з використанням засобів Місrоsоft Ехсеl

В MS Excel для виконання аналітичного групування можуть використовуватися спеціальні інструменти:

а) вбудовані спеціальні статистичні функції «Мастера функций»;

б) засоби програмної надбудови «Пакет анализа»;

і

в) створення власних формул розрахунків статистичних показників характеристик.

Розглянемо приклад виконання аналітичного групування підприємств зв'язку за середньорічною вартістю основних фондів засобами MS Excel.

Групування доцільно розпочати з формування масиву первинних даних (рис.3.1). При цьому групувальною ознакою має бути так званий факторний показник – середньорічна вартість основних фондів, за яким буде утворено п’ять груп з рівними інтервалами.

Рис. 3.1. Вихідні дані для виконання аналітичного групування

Перший етап передбачає знаходження максимального і мінімального значення групувальної ознаки, тобто максимального і мінімального значення у стовбці В (комірки В4:В23), для чого доцільно скористатися відповідними статистичними функціями Мастера функций Excel, що активується кліком по кнопці «Вставка функції» яку розміщено у рядку формул. Після цього на екрані з’являється діалогове вікно «Мастера функций» з переліком видів функцій:

Із запропонованого переліку вибираємо категорію функції «Статистические» та функцію «МАКС» для знаходження максимального значення групувальної ознаки:

Після появи діалогового вікна функції «МАКС» встановлюємо курсор у полі «Число1» та відмічаємо діапазон даних, для яких робимо розрахунок, у даному випадку це комірки В4:В23. В активованій раніше комірці з’явиться число 20 (на рис 3.1 це комірка В27).

Аналогічно знаходимо мінімальне значення групувальної ознаки з використанням функції «МИН» (результат 5,2 занесено у комірку В28).

Далі визначаємо величину інтервалу групування шляхом створення власної формули. Формулу розрахунку наведено в комірці В29 рис 3.1. Встановлюємо інтервал групування для кожної групи:

1-й інтервал 5,2 – (5,2 + 2,96 = 8,16);

2-й інтервал 8,17 – (8,16 + 2,96 = 11,12);

3-й інтервал 11,13 – (11,12 + 2,96 =14,08);

4-й інтервал 14,09 – (14,08 + 2,96 = 17,04);

5-й інтервал 17,05 – (17,04 + 2,96 =20,0).

Результати розрахунків за формулами наведено в комірках В31:С35 (див. рис. 3.2).

Рис.3.2. Результати розрахунків інтервалів групування

Для формування груп у відповідності з встановленими інтервалами можна спочатку розташувати (сортувати) значення показників рис.3.2 у порядку зростання факторного показника з використанням меню «Данные» і команди «Сортировка».

Після появи діалогового вікна команди «Сортировка» у полі «Сортировать по» обираємо «Столбец В» та вказуємо порядок сортування «по возрастанию». Після натискання кнопки ОК у таблиці дані будуть розташовані у порядку зростання факторного показника. Далі можна візуально визначити діапазони значень факторного показника, які відповідають визначеним інтервалам та відділити їх один від одного двома рядками, в яких будуть розміщені сумарні та середні значення по кожній групі:

Рис. 3.3. Візуальне визначення діапазонів значень факторного показника

Сумарне значення факторного показника у першій групі можна розрахувати з використанням кнопки «Автосумма» ∑. Для розрахунку сумарного значення необхідно виділити діапазон даних, які необхідно додавати, натиснути кнопку ∑, після цього в наступній комірці з’явиться результат. Для наступних груп суму можна визначити шляхом копіювання формули. Середнє значення можна обчислити з використанням функції «СРЗНАЧ».

У поле «Число 1» вводиться діапазон значень, для яких визначається середня величина, в даному випадку для першої групи за факторним показником це діапазон В4:В7. У наступних групах середнє значення можна розрахувати шляхом копіювання формули. Покажемо результати обчислень у робочій таблиці (рис. 3.4).

Рис. 3.4. Результати обчислень сумарних та середніх

значень показників

Для виконання групування можна також використовувати меню «Данные» команду «Расширенный фильтр» (рис. 3.5):

Рис. 3.5. Групування даних за допомогою розширеного фільтру

Для виконання групування введемо позначення показників (рядок 4 таблиці з вихідними даними). Далі у відповідності зі встановленими інтервалами необхідно задати діапазон умов для кожної групи (комірки A29, A30; D29, D30; E29, E30; G29, G30; H29, H30; J29, J30; K29, K30; M29, M30), при цьому позначення групувальної ознаки повинно точно відповідати позначенню у вихідній таблиці, тому його краще копіювати.

Після введення діапазону умов необхідно активувати команду «Расширенный фильтр» у меню «Данные» (рис.3.6). На екрані з’явиться діалогове вікно, в перше поле «Исходный диапазон» вводиться діапазон даних з позначеннями показників, але без сумарного значення, в друге поле «Диапазон условий» вводиться діапазон умов для першої групи, включаючи позначення групувального показника, в трете поле «Поместить результат в диапазоне» вводиться діапазон комірок, у яких будуть розміщені результати (за умови вибору «Скопировать результат в другое место»).

Увага! В даному випадку результат виводиться як формула масиву, для цього натискають клавішу F2, потім одночасно Ctrl+Shift+Enter.

Рис. 3.6. Процес групування даних за допомогою розширеного фільтру

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

Для розрахунку кількості одиниць у групі можна скористатися функцією «СЧЕТ», для підрахунку сум та середніх – відповідно функціями «СУММ» та «СРЗНАЧ» (див. рис. 3.7).

Рис. 3.7. Результати аналітичного групування

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

Таблиця 3.7 – Залежність між вартістю основних виробничих засобів, реалізованою продукцією та чисельністю робітників

Групи підприємств за середньорічною вартістю основних фондів, ум.гр.од.	Кіль-кість підприємств	Середньорічна вартість основних фондів, ум.гр.од.	Середньоспискова чисельність робітників, чол.	Кількість наданих послуг, ум.гр.од.
разом	в середньому	разом	в середньому	разом	в середньому
5,20 – 8,16		26,0	6,5	214,0	53,5	36,3	9,1
8,17 – 11,12		105,2	9,6	681,0	61,9	143,7	13,1
11,13 – 14,08		12,1	12,1	79,0	79,0	20,3	20,3
14,09 – 17,04		30,6	15,3	173,0	86,5	37,6	18,8
17,05 – 20,00		38,4	19,2	116,0	58,0	59,7	29,9
По сукупності		212,3	10,62	1263,0	63,2	297,6	14,88

Проаналізувавши середні значення показників по групах, можна зробити висновок про те, що із збільшенням вартості основних виробничих засобів зростає наданих послуг та чисельність робітників. Але слід відзначити, що заводи розподілилися по групах нерівномірно, що зменшує достовірність висновків.

Для виконання структурного групування, яке передбачає підрахунок кількості одиниць у кожній групі, тобто побудову ряду розподілу, можна використати «Мастер функций» Місrоcоft Ехсеl, зокрема функцію «Частота», яка дає можливість обчислити кількість значень ознаки у заданому інтервалі. Для цього обираємо категорію «Статистические» та функцію «Частота»:

Наприклад, маємо дані про середньоспискову чисельність працівників 20 підприємств. Необхідно виконати структурне групування, утворивши чотири групи з рівними інтервалами. Вихідні дані занесемо у відповідні комірки таблиці (комірки А1:В21). Далі за допомогою функцій «МАКС» і «МИН» знайдемо максимальне (комірка В23) і мінімальне (комірка В24) значення групувальної ознаки, обчислимо величину інтервалу групування (комірка В26) та встановимо нижню і верхню межу кожного інтервалу (комірки В29:С32). Для виведення частот активуємо чотири комірки (F29:F32). Після появи діалогового вікна функції «Частота» у поле «Массив данных» введемо діапазон значень групувальної ознаки, у поле «Массив интервалов» – діапазон верхніх меж перших двох інтервалів (завжди крім останньої) (рис. 3.8).

Увага! В даному випадку результат виводиться як формула масиву, для цього натискають клавішу F2, потім одночасно Ctrl+Shift+Enter.

Рис. 3.8. Процес структурного групування

Таким чином, кількість одиниць у кожному інтервалі (частоти) будуть розміщені у комірках F29:F32 (рис. 3.9).

Рис.3.9. Результат структурного групування

Питання і завдання для самоконтролю

3.1. Що ви розумієте під поняттям "зведення" у цілому? Що таке просте i складне зведення?

3.2. Що слід розуміти під централізованим і децентралізованим зведенням? Наведіть приклади.

3.3. Які види помилок статистичного спостереження ви знаєте: назвіть i дайте характеристику?

3.4. До якого етапу статистичного дослідження слід відносити статистичне зведення i групування?

3.5. У чому полягає сутність групувань у статистиці, яка їхня роль у статистичному аналізі?

3.6. Які задачі розв’язуються за допомогою методу групування?

3.7. Які існують види групувань за організацією?

3.8. Які існують види групувань за технікою виконання?

3.9. Які є види групувань за характером завдань?

3.10. Які є види групувань за кількістю ознак групування?

3.11. Що таке групувальна ознака або основа групування? Назвіть основні правила вибору групувальної ознаки.

3.12. Як можна визначити кількість груп для здійснення групування даних?

3.13. Що таке інтервал групування? Вкажіть основні види інтервалів групувань.

3.14. Як визначити розмір рівновеликого інтервалу? Наведіть формулу та покажіть, як нею користуватися.

3.15. Як побудувати інтервали групування?

3.16. Яким чином утворюють відкриті інтервали?

3.17. Як визначити величину інтервалу при групуванні з різними інтервалами?

3.18. Які групування називаються типологічними? Наведіть приклад і побудуйте макет типологічного групування.

3.19. Що таке структурне групування? Наведіть приклад і побудуйте макет структурного групування.

3.20. Що слід розуміти під аналітичним групуванням? Наведіть приклад і побудуйте макет аналітичного групування.

3.21. Що розуміють під простим і комбінаційним групуваннями? Наведіть приклади та побудуйте макети простого й комбінованого групувань.

3.22. Що таке багатовимірне групування?

3.23. У чому полягає сутність вторинного групування? З якою метою його застосовують?

Література [3; 5; 7; 8; 15–24; 26].