Университет "Проф. д-р Асен Златаров" - Бургас             Димитрова, Ж., Р. Димитрова. Система за обучение по статистика и статистически изчисления с Microsoft Excel   

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

Описателните методи служат за класификация и обобщено представяне (организиране) на данните в табличен, графичен и аналитичен вид. Те предоставят възможност да се получат обобщения за данните, тъй като в суров вид данните са твърде разпръснати, за да могат да бъдат обхванати.

Организация на данни

Честотни разпределения и честотни таблици

Създаването на таблици на честотно разпределение (честотни таблици) е една от първите стъпки при организирането на данните от дадена извадка. За целта е необходимо да се намери колко пъти всяка една стойност се среща в множество от данни на извадката. Броят на появяванията на дадена стойност в това множество се нарича абсолютна честота на тази стойност. Обикновено абсолютните честоти се бележат с буквата f. Когато дадена абсолютна честота се раздели на общия брой на данните в извадката, то се получава относителната честота на съответната стойност. Относителните честоти сe бележат с буквата p.

        Множеството от различните стойности на данните от извадката и съответните им абсолютни или относителни честоти се нарича честотно разпределение.
        Честотното разпределение може да се представи чрез таблица (нарича се честотна таблица) или графично чрез диаграма.
        Разпределението на данните от дадена извадка се нарича
емпирично разпределение.
        Друг вид разпределения са теоретичните разпределения. Те са абстрактни модели, получени чрез прилагане на математически методи и се описват с математически средства.

Процедурата за построяването на честотна таблица ще илюстрираме със следната задача:

Задача 1. В математическо състезание участват 17 ученици. Те трябва да решат 5 задачи. Регистрирани са следните данни за броя на решените задачи от всеки участник: 

2,  3,  3,  4,  1,  5,  4,  3,  2,  4,   4,  5,  3,  2,  3,  3,  2

Постройте таблица на честотното разпределение.

Решение. 1. Подреждаме данните във възходящ ред, за да се получи статистически ред:

           1, 2,  3,  3,  3,  3,  4,  4,  4,  4,  4,  4,  5,  5,  5,  5,

2. Чрез броене определяме колко пъти се среща всяка една стойност в статистическия ред, т.е. намираме абсолютните честоти:

1 2,2,2,2 3,3,3,3,3,3 4, 4, 4, 4, 5,5
1 4 6 4 2

3. Създаваме таблица с три колони. В първата колона нанасяме стойностите на изследваната променлива, в случая брой решени задачи: 1, 2, 3, 4, 5. Във втората колона записваме абсолютните честоти - брой ученици, решили съответния брой задачи, след което пресмятаме сбора на честотите. Изчисляваме относителните честоти (като числа или като проценти) и ги сумираме. Нанасяме резултатите в третата колона.

Брой решени задачи Абсолютна честота f Относителна честота p (в %) Относителната честота е полезна, когато трябва да получим отговор на въпроса колко процента от участниците са решили определен брой задачи. Например, 35,3 % от участниците са решили 3 задачи. Чрез сумиране на съответните проценти:   35,3% + 23,5% + 11,8%, можем да установим, че 70,6% от учениците са решили повече от 2 задачи. По-малко от 3 задачи са решили 29,4% от учениците: 5,9% + 23,5%.
1 1 1/17 . 100 = 5,9
2 4 4/17 . 100 = 23,5
3 6 6/17 . 100 = 35,3
4 4 4/17 . 100 =23,5
5 2 2/17 . 100 =11,8
Общо 17 100
Правило: Сборът на абсолютните честоти трябва да бъде равен на общия брой наблюдавани обекти, в случая 17 участника, а сборът на относителните честоти трябва да бъде равен на 100% или на 1.

Построяването на честотното разпределение може да се извърши за данни, измерени както по метрична скала (негрупирани и групирани), така и измерени по номинална и ординална скали.


Създаване на честотни таблици в Excel

Изчислителните процедури за създаване на честотни таблици в Excel се реализират чрез статистическите функции FREQUENCY и COUNTIF и чрез модула Histogram от Data/Data Analysis. Ако  Data Analysis не се съдържа в лентата Data, то е необходимо да бъде активиран Analysis ToolPak, за да се инсталира Data Analysis.

I. Функция FREQUENCY

Функцията FREQUENCY намира приложение за построяване на честотното разпределение на данните, когато те са измерени по метрична скала – интервална или относителна. Функцията се използва както за негрупирани данни (когато извадката съдържа определен брой дискретни стойности), така и за данни, групирани в класове. За да се получи честотно разпределение на данни, измерени по номинална или ординална скала, трябва да са въведени числовите кодове на съответните категории.

Функцията FREQUENCY има два аргумента: Data_array и Bins_array:

Data_array – това е областта с данни. Не се изисква данните да са подредени във възходящ ред.

Bins_array – областта със стойности (класове), за които ще се извърши групиране на данните и изчисляване на честотите. Тези стойности задължително трябва да са подредени във възходящ ред и да са въведени в област от една колона.

Честотни разпределения при негрупирани данни

Прилагането на функцията FREQUENCY за негрупирани данни е илюстрирано въз основа на данните от следващата таблица, съдържаща се в работен лист на Excel. В областта B2:B33 (Data_array) на таблицата се съдържат оценките на обучаваните лица, в областта D2:D6 (Bins_array), са въведени стойностите, за които ще се изчисляват честотите – 2; 3; 4; 5; 6.

Въвеждането на формулите с функцията FREQUENCY се извършва в следната последователност: 

1. Маркира се областта за честотите E2:E6. 

2. Активира се бутонът fx и от групата Statistical се избира функцията FREQUENCY. 

3. В прозореца с аргументите на функцията се въвеждат двете области чрез маркиране с мишката:

4. За да завърши въвеждането на функцията, се натиска клавишната комбинация CTRL+SHIFT+ENTER, тъй като функцията спада към функциите масиви. В областта E2:E6 се появяват честотите. 

След създаването на честотна таблица въз основа на получените абсолютни честоти се изчисляват относителните и кумулативните честоти. За целта е необходимо да се въведат представените по-долу формули в клетка E7 и колони F и G на таблицата:

Въвеждането на формулите става в следната последователност:

           1. Активира се клетката E7 и се щраква два пъти върху бутона AutoSum - така в клетката E7 се появява формулата =SUM(E2:E6).
           2. Активира се клетката F2, въвежда се формулата =E2/E7 и се натиска клавишът F4 преди да се потвърди въвеждането – така  E7 се превръща в абсолютен адрес $E$7.
           3. Формулата от клетката F2 се копира в областта F3:F6 .
           4. Активира се клетката F7 и се щраква два пъти върху бутона AutoSum .
           5. Въвеждат се формулите в клетките G2 и G3, след което формулата от клетката G3 се копира в областта G4:G6.

По този начин се получава следната честотна таблица:


Преобразуването на относителната и кумумлативната честоти в проценти се извършва като първо се маркират съответните клетки (F2:G6), след което се активира бутонът % от лентата Home.

Задача 2. В състезание по биатлон участват 20 спортисти. Данните за броя на точните попадения при стрелбата (4 серии по 5 изстрела) на всеки участник са: 
             15, 18, 13, 2, 19, 18, 17, 16, 20, 16, 20, 19, 18, 17, 19,15, 17, 19, 18, 20. 
Постройте таблица на честотното разпределение на данните в Excel. Колко процента от участниците имат по повече от 17 точни попадения при стрелбата?
Упътване. В данните не се съдържа числото 14, но то трябва да се включи с честота нула в таблицата. 

Задача 3. В един клас е проведено наблюдение за закъсненията на учениците за първия час, като за един месец са наблюдавани 21 закъснения и е регистрирана продължителността в минути за всяко едно закъснение: 
            2, 2, 1, 3, 3, 1, 5, 2, 1, 1, 2, 1, 7, 1, 3, 4, 1, 1, 6, 2, 4. 
Обект на наблюдение са учениците, а променливата, която се изследва, е закъснение в минути. Постройте таблица на честотното разпределение в Excel.

Честотни разпределения при групирани данни

В случаите, в които данните приемат голям брой различни стойности и честотното им разпределение може да не е достатъчно ясно и полезно, числовият интервал на данните се разделя на подинтервали (класове), в които се извършва групиране на данните. При групирани данни във функцията FREQUENCY в аргумента Bins_array се зададават само горните граници на интервалите. Така например, ако класовете, в които ще се групират данните от извадка за височината (в см) на учениците от изследваната група са:

<=155
(155, 160]
(160, 165]
(165, 170]
(170, 175]
(175, 180]
(180, 185]
>185


то вторият аргумент Bins_Array на функцията трябва да съдържа само горните граници на интервалите, така както е показано в областта K3:K9 в следващата таблица.


Функцията ще групира данните в интервали от вида (a, b], където a и b представляват горните граници на два съседни интервала и ще изчисли съответните честоти. Възможно е последният интервал да няма горна граница. В този случай, ако областта със стойности на горните граници на интервалите е K3:K9, то областта за честотите е с една клетка повече – M3:M10. Потребителят е този, който на практика задава границите на интервалите, а функцията групира данните и изчислява честотите. 

Въвеждането на формулите с функцията FREQUENCY за групирани данни, както и изчисляването на относителните и кумулативните честоти се извършва по аналогичен начин на този при негрупирани данни. В случая трябва първо да се маркира областта за резултатите - M3:M10, след което да се извика функцията FREQUENCY, в Bins_Array да се задава K3:K9, а в Data_array - съответната област с данните. По аналогичен начин чрез клавишната комбинация CTRL+SHIFT+ENTER завършава въвеждането на функцията.









Важно е да се обърне внимание на недвусмисленото обозначаване на границите на интервалите, особено когато изследваната величина е непрекъсната. За целта е необходимо потребителят да въведе в отделна колона (в случая колона L) дадените по-долу формули.

Във формулите е използван операторът & за свързване на две стойности като един текст – в случая горните граници на интервалите, зададени с адресите на техните клетки и съответните символни константи. Тези формули позволяват промените в границите на интервалите в колона K динамично да се отразяват в тяхното обозначаване в колона L. Освен формулите от колона L е показано и как ще изглеждат самите резултати. Формулата от клетката L4 се копира в областта L5:L9.

Задача 4. Създайте таблица на честотното разпределение на данните за височината в см на 26 ученици от един клас:

170    154    156    167    160    181    165    164    165    162    176    157    186

155    171    182    174    176    168    177    178    180    161    172    184    166

За групиране на данните в класове използвайте дадените по-горе граници на интервали. Въведете формулите за обозначаване на границите на интервалите и за изчисляване на относителните честоти.

Задача 5.  Дадени са резултатите (брой точки) от тест по математика на 50 ученика.

Създайте формулите за получаване на честотна таблица, подобна на следната:

Задача 6.  Дадени са резултатите (брой точки) от тест за интелигентност на 25 ученика.

68    46    52   92   88    94    63    82    67    41    72    75    84

79    78   76    77    80    66    75    86    60    55    94    61    

Определете числовия интервал на данните. Създайте таблица на честотното разпределение като започнете:

а) от интервалите 40 ÷ 49; 50 ÷ 59, 60 ÷ 69 и т.н.;           б) от интервалите 40 ÷ 44; 45 ÷ 49, 50 ÷ 54 и т.н.

Във файла distr1.xls са дадени формули за решаване на някои от дадените задачи.

II. Функция COUNTIF

При данни, получени въз основа на номинална или ординална скала, функцията FREQUENCY може да се използва след допълнително преобразуване на тези данни в числови стойности, като им се съпоставят съответни числови кодове по някакво правило. Вместо това може да се използва функцията COUNTIF така, както е показано в следващата таблица. Както се вижда от прозореца Function Arguments на функцията COUNTIF, тя има два аргумента: Range(област с данни) и Criteria (критерий). Функцията връща броя на клетките от областта Range, които удовлетволяват критерия, зададен като втори аргумент.

В таблицата в областта B2:B21 се съдържат отговорите на даден въпрос от анкета (а, б, в). В областта D4:D6 са дадени всички възможни отговори на този въпрос. Формулата с функцията COUNTIF се въвежда еднократно в клетка E4,след което се копира в клетките E5:E6. В клетка E4 ще се получи броят на клетките от областта B2:B21, съдържащи отговор а, в клетка E5 ще се получи броят на клетките от областта B2:B21, съдържащи отговор б и т.н.

Въвеждането на формулите с функцията COUNTIF се извършва в следната последователност: 

1. Активира се клетката E4.

2. Активира се бутонът fx и от групата Statistical се избира функцията COUNTIF. 

3. В прозореца с аргументите на функцията  се въвеждат двата аргумента: в  Range чрез маркиране с мишката се задава областта B2:B21, след което веднага се натиска клавишът F4 - адресът се превръща в абсолютен - $B$2:$B$21; в Criteria се задава D4 и с OK завършва въвеждането на функцията.

4. Формулата се копира в клетки E5:E6.

5. Активира се клетката  E7 и се щраква двукратно върху бутона AutoSum .

Така ще се получат следните резултати:

Отговор Честота f
а 10
б 4
в 6
Общо 20


Дефиниране на имена на области в Excel

Тъй като областта с данните от работния лист може да участва в различни формули, то е подходящо в Excel да се дефинира име на тази област с клетки, което да се използва във формулите вместо адресът на областта. Това става по следния начин

1. Маркира се областта с данните - за горната таблица това е областта B2:B21. 

2. В полето за адрес на реда за обработка (Formula Bar) се въвежда името на областта (в случая danni) и се потвърждава с клавиша Enter.

Всеки път, когато е необходимо името на област от работния лист да се въведе в дадена формула или като аргумент на функция, трябва само да се натисне клавишът F3 – чрез него се отваря прозорецът Paste Name, съдържащ списък с имената на всички области в работната книга, след кото се избира съответното име:

Така формулата с функцията COUNTIF в клетката E4 за таблицата по-горе ще бъде: =COUNTIF(danni;D4).

Функцията COUNTIF може да се използва за построяване на честотни таблици за данни, измерени както по метрични скали (интервална и относителна), така и по неметрични скали (номинална и ординална).  При метрични данни тя може да се прилага за негрупирани и за групирани данни. Функцията COUNTIF предоставя по-големи възможности за построяване на честотно разпределение в сравнение с функцията FREQUENCY. Във файла distr1.xls са дадени различни примери за използването на функцията COUNTIF. 

III. Модул Histogram

За построяване на честотното разпределение само на числови данни може да се използва и модулът Histogram. Той предоставя удобен интерфейс на потребителя - не е необходимо да се въвеждат формули и фунции за получаването на честотно разпределение.  Работата с модула Histogram се извършва в следната последователност: 

1.  Чрез Data/Data Analysis се отваря прозорецът Data Analysis и се избира инструментът Histogram

2. В прозореца Histogram се задават: 
                    - областта с данните - в Input Range;
                    - областта с горните граници на интервалите за групиране – в Bin Range;
                    - в Output options се избира къде да се появи честотната таблица – в съшия работен лист, в който са данните (Output Range), в нов работен лист (New Worksheet Play) или в нова работна книга (New Workbook);

- може да се активира полето Chart Output, за да се получи и диаграма на честотното разпределение;

- с OK се затваря диалоговият прозорец - таблицата и диаграмата на честотното разпределение се получават според избраното място в Output options.

Таблицата, която се получава, има следния вид:

Bin Frequency
10 0
15 5
20 6
25 4
30 5
More 0

Допълнително следва да се въведат формули за изчисляване на относителните и кумулативните честоти.