Завдання 1. Тема «Зведення і групування даних»

Завдання 1 передбачає виконання аналітичного групування,метою якого є встановлення взаємозв'язку між трьома показниками. При цьому показник, який є причиною зміни інших, пов'язаних з ним показників, називається факторним (Х). Ті показники, котрі змінюються під впливом факторного, є результативними (Y). У даному конкретному випадку факторним показником (отже і групувальним) є середньорічна вартість основних виробничих засобів у 2010 р., а результативними – фактична реалізована продукція та середньооблікова кількість працівників у цьому році.

Аналітичне групування виконують за факторним показником, при цьому утворюють рівні інтервали. Величину інтервалу групування визначають за формулою:

, (1)

де Xmax, Xmin – відповідно найбільше та найменше значення групувального показника; m – число груп.

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

Виконання аналітичного групування з використанням MS EXCEL доцільно розпочати з формування масиву первинних даних, для чого необхідно використати порядкові номери заводів, які відібрано випадковим чином (надаються викладачем), і графи 2, 7, 9 таблиці вихідних даних для виконання індивідуальних завдань і сформувати робочу таблицю з вибірковими даними (рис.1).

Наступний етап передбачає знаходження максимального і мінімального значень групувальноого показника, тобто максимального і мінімального значення у стовбці Х (комірки В4:В18). Для цього доцільно скористатися відповідними статистичними функціями «Мастера функцій» MS EXCEL, активувати який можна кліком по кнопці «Вставка функцій» (f_x), розміщену в рядку формул. Після цього на екрані з’являється діалогове вікно «Мастера функций» з переліком видів функцій. Із запропонованого переліку вибираємо категорію «Статистические» і функцію «МАКС» для знаходження максимального значення групувального (факторного) показника Х (рис. 2).

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

Рис. 2. Вікна «Мастера функций» та статистичних функцій

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

Рис. 3. Вікно статистичної функції «МАКС»

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

Рис. 4. Вікно статистичної функції «МИН»

Далі обчислюємо величину інтервалу групування (1), формулу його розрахунку задано в комірці В22 (див. рис. 1). Наступний крок – визначення інтервалів групування для кожної групи: 1-й інтервал 28,60 – 44,40; 2-й інтервал 44,41 – 60,20; 3-й інтервал 60,21 – 76,00; 4-й інтервал 76,01 – 91,80. Результати розрахунків за відповідними формулами наведено в комірках B25:C28 (рис. 5).

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

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

Рис. 6. Вікна сортування даних

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

Рис.7. Розподіл заводів по групах відповідно до інтервалів групування

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

Рис. 8. Вікно функції «СРЗНАЧ»

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

Рис. 9. Розрахунок сумарних і середніх значень показників

Для виконання групування можна також використовувати меню «Данные» команду «Расширенный фильтр» (рис. 10). Для виконання групування введемо позначення показників (комірки B2, C2, D2). Далі відповідно до встановлених інтервалів необхідно задати діапазон умов для кожної групи (рис. 11, комірки G4, J4, K4; M4, N4, P4), при цьому позначення показників повинно точно відповідати позначенню у вихідній таблиці, тому їх краще копіювати.

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

Рис. 10. Вікно звернення до команди «Расширенный фильтр»

Рис. 11. Діалогове вікно команди «Расширенный фильтр»

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

Рис. 12. Результати аналітичного групування за допомогою команди «Расширенный фильтр»

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

Таблиця 1