Розрахунок статистичних параметрів засобами табличного процесора MS Excel

Масиви правових даних характеризуються середніми значеннями вели­чин різного виду, варіацією ряду, моментами і формою розподілу даних.

Для статистичного оцінювання даних використовують такі усереднені показники:

- середнє арифметичне;

- середнє квадратичне;

- середнє геометричне;

- середнє гармонічне;

- середнє кубічне.

Для оцінювання розсіювання (відхилення) даних відносно середнього значення користуються такими показниками:

- дисперсією;

- середньоквадратичним відхиленням – квадратним коренем із дисперсії (чим менше значення величини відносно її середнього значення, тим більш рівнемір ним є розподіл ряду даних);

- середнім модулем відхилень;

- ексцесом (крутістю ряду або щільністю розподілу ймовірності). Для нормального розподілу ексцес дорівнює нулю, для крутих кривих розподілу він додатний, для плоских – від’ємний порівняно з нор­маль­ною щільністю розподілу кривих;

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

- максимумом;

- мінімумом;

- найбільшим К-м (К – порядок значення, меншого за максимум);

- найменшим К-м (К – порядок значення, більшого за мінімум);

- інтервалом (максимум – мінімум);

- модою (значенням, що найчастіше зустрічається у ряду даних);

- медіаною (значенням, розміщеним посередині ря­ду даних);

- квартилями розподілу (підмножинами даних з од­наковим числом елементів);

- довірчим інтервалом тощо.

Крім спеціальних статистичних функцій, для статистичного оцінювання даних можна використо­вувати спеціальний інструмент Описательная ста­ти­стика із засобу Анализ данных Excel.

Завантаження Анализа данных здійснюється з меню Сервис. У резуль­таті на екрані монітора з’явиться діалогове вікно, в яко­му потрібно активі­зу­вати опцію Анализ данных (рис. 6.1.)

Рис. 6.1. Вікно аналізу даних

Анализ данных включає великий набір інструментів для проведення статистичного, кореляційно-регресійного, дисперсного та інших видів ана­лі­зу. Подальший виклик інструментів Анализ данных можна здійсню­вати за командоюАнализ данных... із менюСервис.

Вхідні дані, за якими проводиться їх статистичний аналіз із вико­ри­ста­нням інструменту Описательная статистика, мають відповідати таким вимогам:

- на аркуші вхідного діапазону даних не повинно бу­ти об’єднаних клі­тинок;

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

Після подачі команди Анализ данных.../Описательная статистика з меню Сервис на екрані монітора з’явиться діалогове вікно, в якому потрібно вказати такі параметри:

q вхідний діапазон – посилання на клітинки, що містять аналізовані дані. Посилання має складатися як мінімум із двох суміжних діапазонів даних, оформлених у вигляді стовпців або рядків;

q групування – перемикач Группирование задає положення По столб­цам або По строкам залежно від розташування даних у вхідному діапазоні;

q мітки в першому рядку (стовпці) – перемикач задає положення назв показників Метки в первой строке,якщо перший рядок у вхідному діапазоні назв містить стовпці, або Метки в первом столбце, якщо назви ряд­ків знаходяться у першому стовпці вхідного діапазону. Якщо вхідний діапазон не містить міток, то заголовки у вихідному діапазоні створюватимуться автоматично (Строка1\Столбец1);

q вихідний діапазон – посилання на ліву верхню клітинку вихідного діапазону;

q новий листок – перемикач активізують, щоб ство­рити новий листок у книзі та помістити результати ана­лізу, починаючи з клітини А1. Якщо потрібно, то мож­на ввести ім’я нового листка в поле, розташоване нав­проти відповідного положення перемикача;

q нову книгу – перемикач активізують, щоб створи­ти нову книгу та по­мі­сти­ти результати аналізу в клітин­ку А1 на першому листку в цій книзі;

q підсумкову статистику – прапорець активізують, якщо у вихідному діапазоні потрібно одержати додатко­ві статистичні оцінки даних – середнє, стандартну по­милку (середнього), медіану, моду, стандартне відхи­лення, дисперсію вибірки, ексцес, асиметричність, ін­тервал, мінімум, максимум, суму;

q К-й найбільший – прапорець активізують, якщо у вихідну таблицю треба включити рядок К-го найбіль­шого значення для кожного діапа­зону даних;

q К-й найменший – прапорець активізують, якщо у вихідну таблицю по­трібно включити рядок К-го най­меншого значення для кожного діапазону даних;

q рівень надійності – прапорець активізують, якщо у вихідну таблицю треба включити рядок для рівня на­дійності. Наприклад, значенню 95 % відповідає рівень надійності середнього зі значущістю 0,05.

Розглянутий інструмент аналізу виводить два стовпці результатів для кож­ного показника даних. Лівий стов­пець містить назви статистичних оці­нок, а правий – ста­тистичні оцінки. Відповідно над першим стовпцем роз­міщується назва показника, якщо було виділено рядок чи стовпець назв та активізовано перемикач Метки. Діа­пазон із двох стовпців буде виведений для кожного стовп­ця або для кожного рядка вхідного діапазону показників залежно від положення перемикача Группирование.

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

- перемістити назви показників у першому рядку із першого (третього, п’ятого і т. д.) стовпця у другий (чет­вертий, шостий і т. д.);

- вилучити дублюючі стовпці назв статистичних оці­нок (третій, п’ятий і т. д.);

- розширити перший стовпець назв статистичних оцінок;

- задати для першого рядка назв показників формат розміщення тексту в кілька рядків та підібрати потріб­ну ширину стовпців.

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

Інструмент Описательная статистика досить зруч­ний для здобуття 16 статистичних оцінок показників. Однак вихідна таблиця статистичного ана­лізу не є дина­мічною, оскільки включає лише результати обчислень (зна­че­ння) і не зберігає зв’язку з таблицею вхідних да­них. Якщо значення показ­ників у вхідній таблиці змінюватимуться, то кожний раз розрахунки треба вико­нувати заново.

Побудова таблиці статистичного аналізу за допомо­гою статистичних функ­цій займає набагато більше часу (кожну функцію необхідно вводити окремо). Проте вона дасть досить великий виграш у майбутньому при зміні вхідних даних.

Трендові моделі

Значення кримінологічних показників, які мають випадковий характер, можна використовувати для побудови часових рядів – емпіричної послі­дов­ності да­них, здобутих у певні моменти часу. Кожний такий ряд хара­кте­ри­зується деякою тенденцією розвитку процесу в часі, яка називається трен­дом. Трендові моделі часо­вих (динамічних) рядів забезпечують видачу про­гно­зів на коротко- та середньостроковий періоди при виконан­ні низки умов:

- період часу, за який досліджується прогнозований процес, має бути достатнім для виявлення закономір­ностей;

- трендова модель в аналізований період має розви­ватись еволюційно;

- процес, що описується часовим рядом даних, пови­нен мати певну інер­ційність, тобто для великих змін у поведінці процесу потрібний значний час;

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

На практиці найпоширенішими методами статис­тичного дослідження тренда є:

- збільшення інтервалів для визначення тренда в ча­сових рядах даних, що коливаються;

- метод ковзних середніх значень із заданим періо­дом т;

- метод аналітичного вирівнювання у вигляді функ­ції тренда, яка зале­жить від часу.

Лінія тренда широко застосовується для розв’язан­ня задач прогнозування за допомогою методів регресійного аналізу. Підбір функції тренда здій­сню­ється мето­дом найменших квадратів. Для оцінювання точності моделі вико­ри­стовують коефіцієнт детермінації, побу­дований на основі оцінок дисперсії емпіричних даних та значень трендової моделі.

Трендова модель показує тенденцію розвитку проце­су, якщо коефіцієнт детермінації прямує до 1.

Явища, що спостерігаються у часі, можуть розвива­тися так:

- рівномірно при сталому абсолютному прирості чер­гового рівня часо­во­го ряду даних за лінійним законом: у = a₀ + a₁t, де a₀ – стала; a₁ – коефіцієнт регресії, що визначає швидкість і напрямок (< 0 – спадання; > 0 – зростан­ня) розвитку;

- рівноприскорено при сталому в часі збільшенні (зниженні) темпу приросту рівнів за законом (парабола другого порядку):

у = a₀ + a₁ t+ a₂ t², де a₂ – коефіцієнт, що характеризує сталу зміну швид­кості (темпу) розвитку (a₂ > 0 – прискорення розвитку, a₂ < 0 – його сповільнення);

^- із змінним прискоренням (сповільненням) при змінному в часі збіль­шенні (зменшенні) розвитку за за­коном (парабола третього – шостого порядків): у = a₀ + a₁ t+ a₂ t²₊ a₃ t³_....+ a₆ t⁶ ;

- зі сповільненням зростання в кінці періоду, коли приріст у кінцевих зна­ченнях ряду даних прямує до ну­ля за законом (логарифмічна функ­ція): у = lnt + a₀;

- зі зростанням за експоненціальним законом: y = а₀ e ^a1t, де t – випад­ковий час появи чергової події;

- зі сталим відносним приростом за законом степене­вої функції (гіпер­бола): у = а₀t ^a1, у = a₀ + a₁/t.

Microsoft Excel будує трендові моделі графічним способом на основі дво­вимірних діаграм – лінійних, гра­фіків, гістограм, точкових, що відображають динамічні зміни.

Послідовність виконання цієї процедури така:

- будують діаграму;

- діаграму переводять у режим редагування;

- виділяють ряд на діаграмі для побудови лінії тренда;

- подають команду Добавить линию тренда... з меню Диаграмма або за допомогою контекстного меню.

У результаті на екрані монітора з’являється діалого­ве вікно, у першому розділі якого можна визначити тип лінії тренда (лінійний, логарифмічний, поліномний, степеневий, експоненціальний, ковзних середніх зна­чень), а у другому задати її параметри:

- ім’я (автоматично з назвою трендової моделі або ввести у текстове поле);

- кількість періодів прогнозування наперед (прово­диться на 0,5; 1; 1,5 і т. д. періоди, точний прогноз мо­же здійснюватись тільки на невеликий період, особливо якщо масив фактичних даних невеликий);

- кількість періодів прогнозування назад;

- Y-перетин – точку, в якій лінія тренда має перетинати вісь Y;

- R ² – виведення коефіцієнта детермінації, а також відобразити рівняння лінії тренда на діаграмі.

Спочатку обирають трендову модель і задають її параметри (рівняння, R²). Далі підбирають тип тренда (R²®1), двічі клац­нувши лівою кла­вішею миші по лінії тренда, та перевіряють іншу трендову модель (рис. 6.2).

Рис. 6.2. Прогнозування обсягу квартирних крадіжок за допомогою лінії тренда

Щодо даних найкращою є логарифмічна модель. На діаграмі можна спо­сте­ріга­ти незначну тенденцію зростання обсягу квартирних крадіжок у часі.