Informatika · Matematika · Analýza dát

Štatistika v Exceli
— 3 Vyučovacie hodiny

Interaktívna zbierka úloh od popisnej štatistiky po analytické nástroje, s vizuálnym zobrazením a kľúčom riešení.

Prejsť na obsah ↓
scroll

1. vyučovacia hodina

Popisná štatistika
v Exceli

Stredné hodnoty, miery variability, kvartily, histogram – so základnými funkciami.

Pojmovník — 1. hodina
Stredná hodnota
Priemer x̄
Súčet všetkých hodnôt vydelený ich počtom. Udáva „ťažisko" súboru údajov. Je citlivý na extrémne (odľahlé) hodnoty.
x̄ = (x₁+x₂+…+xₙ) / n
Stredná hodnota
Medián Me
Stredná hodnota usporiadaného súboru. Pri párnom počte hodnôt je to priemer dvoch stredných. Nie je ovplyvnený odľahlými hodnotami.
Me = x₍ₙ₊₁₎/₂ (pre nepárne n)
Stredná hodnota
Modus Mo
Hodnota, ktorá sa v súbore vyskytuje najčastejšie. Súbor môže mať viac modusov, alebo žiadny – keď sa každá hodnota vyskytuje rovnako často.
=MODE(rozsah) [=MODUS v SK]
Variabilita
Rozptyl (variačné rozpätie) R
Rozdiel medzi maximálnou a minimálnou hodnotou. Jednoduchý ukazovateľ, no citlivý na odľahlé hodnoty – zohľadňuje iba dve krajné hodnoty.
R = x_max − x_min
Variabilita
Smerodajná odchýlka s
Odmocnina z priemeru štvorcov odchýlok od priemeru. Udáva, o koľko sa hodnoty v priemere odchyľujú od strednej hodnoty.
s = √[Σ(xᵢ−x̄)²/(n−1)]
Variabilita
Variačný koeficient CV
Podiel smerodajnej odchýlky a priemeru vyjadrený v percentách. Umožňuje porovnávať variabilitu súborov s rôznymi jednotkami alebo rozsahmi.
CV = (s / x̄) × 100%
Kvantily
Kvartily Q1, Q2, Q3
Hodnoty rozdeľujúce usporiadaný súbor na štyri rovnaké časti. Q1 je dolná hranica, Q2 je medián, Q3 je horná hranica. Základ škatuľového grafu.
=QUARTILE(rozsah; 1|2|3)
Variabilita
IQR Q3−Q1
Medzikvartilové rozpätie: rozsah stredných 50 % hodnôt. Robustný ukazovateľ variability – nie je ovplyvnený odľahlými hodnotami. Hranica odľahlých: Q1−1,5×IQR a Q3+1,5×IQR.
IQR = Q3 − Q1
—
Priemer (€)
—
Medián (€)
—
Smer. odchýlka
—
IQR (€)

Údaje — mesačné vreckové žiakov (€)

ŽiakŽ1Ž2Ž3Ž4Ž5Ž6Ž7Ž8Ž9Ž10
€45603075509040558025

Excel: zadajte údaje do buniek B2:K2.

Rozloženie vreckového

Škatuľový diagram (simulovaný box plot)

1

Stredné hodnoty

Vypočítajte priemer, medián a modus! Porovnajte ich – prečo sa môžu líšiť?

=AVERAGE(B2:K2) =MEDIAN(B2:K2) =MODE(B2:K2)
Riešenie
AVERAGE → 55 € MEDIAN → 52,5 € MODE → #N/A (žiadne opakovanie)

Priemer a medián sú blízko seba → takmer symetrické rozdelenie. Jedna extrémna hodnota (napr. 500 €) by skreslila priemer nahor, medián takmer nie.

2

Miery variability

Vypočítajte variačné rozpätie, smerodajnú odchýlku a variačný koeficient!

=MAX(B2:K2)-MIN(B2:K2) =STDEV(B2:K2) =STDEV/AVERAGE×100%
Riešenie
Variačné rozpätie → 65 Smer. odchýlka → 21,4 Variačný koeficient → 38,9 %

38,9 % = stredná variabilita. Pravidlo: <30 % nízka, 30–60 % stredná, >60 % vysoká variabilita.

3

Kvartily

Vypočítajte Q1 a Q3! Aké je medzikvartilové rozpätie? Existujú odľahlé hodnoty?

=QUARTILE(B2:K2;1) =QUARTILE(B2:K2;3) IQR = Q3 − Q1
Riešenie
Q1 → 38,75 € Q3 → 71,25 € IQR → 32,5 | Žiadne odľahlé hodnoty

Hranice odľahlých hodnôt: Q1−1,5×IQR = −9,9 (žiadna hodnota pod) a Q3+1,5×IQR = 120 (žiadna nad). Súbor je čistý.

4

Histogram funkciou COUNTIF

Vytvorte 4 triedy a spočítajte žiakov v každej triede pomocou COUNTIF!

=COUNTIF(B2:K2;"<=30") =COUNTIFS(B2:K2;">30";...;"<=60")
Riešenie
0–30 € → 2 žiaci 30–60 € → 4 žiaci 60–90 € → 4 žiaci

Rozdelenie je takmer rovnomerné. Označte oba stĺpce → Vložiť → Stĺpcový graf pre vizualizáciu.

2. vyučovacia hodina

Analýza dát &
podmienené funkcie

AVERAGEIF, COUNTIFS, CORREL – analýza triednej knihy.

Pojmovník — 2. hodina
Podmienená funkcia
AVERAGEIF priemer s podmienkou
Počíta priemer len pre tie prvky, ktoré spĺňajú zadanú podmienku. Napr. priemer len dievčat alebo len žiakov s jednotkou.
=AVERAGEIF(rozsah_podm; podm; rozsah_priem)
Podmienená funkcia
COUNTIFS počet s viacerými podmienkami
Spočíta bunky, ktoré súčasne spĺňajú viacero podmienok. Napríklad: jednotka z matematiky A jednotka z biológie.
=COUNTIFS(rozs1; podm1; rozs2; podm2)
Logická funkcia
IF(AND(…)) podmienka + a zároveň
Zložený podmienený výraz: AND overuje splnenie viacerých podmienok naraz, IF na základe toho vracia jednu z dvoch hodnôt.
=IF(AND(podm1; podm2); "áno"; "nie")
Analýza závislosti
Korelácia r
Miera sily a smeru lineárnej závislosti medzi dvoma premennými. Hodnota od −1 do +1: −1 = dokonalá negatívna, 0 = žiadna závislosť, +1 = dokonalá pozitívna.
=CORREL(rozsah1; rozsah2)
Rozdelenie
Relatívna početnosť fᵢ
Udáva, koľko percent zo všetkých pozorovaní pripadá na danú hodnotu alebo triedu. Ich súčet je vždy 100 %.
fᵢ = nᵢ / N × 100%
Rozdelenie
Kumulatívna početnosť Fᵢ
Podiel všetkých pozorovaní do danej hodnoty (vrátane). Udáva, koľko percent hodnôt je menších alebo rovných určitej hodnote.
Fᵢ = f₁ + f₂ + … + fᵢ
Smer závislosti
Pozitívna korelácia r > 0
Keď jedna premenná rastie, rastie aj druhá. Napr.: viac štúdia → lepšia známka. Čím bližšie je r k +1, tým tesnejšia závislosť.
r ∈ (0; +1]
Smer závislosti
Negatívna korelácia r < 0
Keď jedna premenná rastie, druhá klesá. Napr.: viac absencií → horšia známka. Naša hodnota −0,89 naznačuje silnú negatívnu závislosť.
r ∈ [−1; 0)
4,4
Priemer dievčat (mat.)
3,0
Priemer chlapcov (mat.)
−0,89
Korelácia
1
Jednotka z oboch predm.

Absencie ↔ Známka z matematiky — korelácia: −0,89

−1 (silná)
+1 (silná)

Hodnota −0,89: silná negatívna závislosť. Čím viac dní žiak chýba, tým horšia je jeho známka.

Známka z matematiky — dievčatá vs. chlapci

Absencie vs. Známka z matematiky (scatter)

Údaje z triednej knihy

ŽiakPohlavieMat.Bio.AbsencieJednotka z oboch?
AnnaD122—
BélaCh338—
CsillaD211—
DávidCh4312—
EszterD110✓ Jednotka!
FeriCh225—
GrétaD323—
HajniD130—

Pohlavie: D = dievča, Ch = chlapec. Slovenské známkovanie: 1 = výborný, 5 = nedostatočný.

1

Podmienené priemery podľa pohlavia

Vypočítajte priemerné hodnotenie z matematiky pre dievčatá a chlapcov pomocou AVERAGEIF!

=AVERAGEIF(B:B;"D";C:C) =AVERAGEIF(B:B;"Ch";C:C)
Riešenie
Dievčatá → 1,6 Chlapci → 3,0

Rozdiel je 1,4 stupňa – no vzorka má len 8 žiakov! Výsledok je iba popisný, nie je možné ho zovšeobecniť.

2

Zložená podmienka — výborní žiaci

Koľko žiakov dostalo jednotku Z OBOCH predmetov? Označte ich pomocou IF(AND…)!

=COUNTIFS(C:C;1;D:D;1) =IF(AND(C2=1;D2=1);"Výborný";"")
Riešenie
COUNTIFS → 1 Eszter → Výborná z oboch predmetov

Vzorec IF(AND(...)) skopírujte nadol pre všetkých žiakov. Podmienku splnila len Eszter (0 absencií, 1–1).

3

Korelácia: absencie vs. známka

Existuje závislosť medzi počtom absencií a hodnotením z matematiky?

=CORREL(E2:E9;C2:C9)
Riešenie
CORREL → +0,89 (v SK stupnici: viac abs. → horšia zn.)

Silná pozitívna závislosť medzi absenviami a číselnými hodnotami. Interpretácia: 0 = žiadna závislosť, ±1 = dokonalá závislosť. Hodnota 0,89 naznačuje veľmi tesný vzťah.

4

Relatívna a kumulatívna početnosť

Zostavte tabuľku početností pre hodnotenia z matematiky (1–5)!

=COUNTIF(C2:C9;1) =počet/COUNTA(C2:C9)
Riešenie
Zn. 1: 3 → 37,5 % → kumulat.: 37,5 % Zn. 2: 2 → 25,0 % → kumulat.: 62,5 % Zn. 3: 2 → 25,0 % → kumulat.: 87,5 % Zn. 4: 1 → 12,5 % → kumulat.: 100 %

62,5 % žiakov dostalo dvojku alebo lepšie. Najviac žiakov (37,5 %) bolo hodnotených jednotkou.

3. vyučovacia hodina

Analysis ToolPak —
Analytické nástroje

Popisná štatistika nástrojom, histogram, regresia, párový t-test.

Pojmovník — 3. hodina
Tvar rozdelenia
Šikmosť g₁
Udáva, nakoľko je rozdelenie symetrické. g₁ = 0: symetrické; g₁ > 0: pravostranné (pozitívna šikmosť); g₁ < 0: ľavostranné (negatívna šikmosť).
Excel: Popisná štatistika → Šikmosť (Skewness)
Tvar rozdelenia
Špicatosť g₂
Meria „špicat" alebo „plochosť" rozdelenia v porovnaní s normálnym. g₂ > 0: špicatejšie (leptokurtické); g₂ < 0: plochejšie (platykurtické).
Excel: Popisná štatistika → Špicatosť (Kurtosis)
Regresia
R² (koeficient determinácie)
Udáva, akú časť variability Y vysvetľuje nezávislá premenná X. R² = 0,94 → 94 % variability je vysvetlených modelom. Hodnota blízka 1 = dobré prispôsobenie.
R² ∈ [0; 1] → 0 % … 100 %
Regresia
Regresná priamka ŷ
Priamka opisujúca lineárnu závislosť medzi X a Y. Smernica (b) udáva, o koľko sa zmení Y pri jednotkovom náraste X.
ŷ = b·x + a (smernica × x + priesečník)
Štatistický test
Nulová hypotéza H₀
Základný predpoklad, ktorý testujeme – zvyčajne „nie je rozdiel" alebo „nie je účinok". Ak je p-hodnota malá (p < α), H₀ zamietame.
H₀: μ_pred = μ_po
Štatistický test
p-hodnota p
Pravdepodobnosť, že pozorovaný rozdiel by nastal náhodne, ak by H₀ platila. Ak p < 0,05 (hladina významnosti), výsledok je štatisticky významný.
p < α (0,05) → H₀ zamietnutá
Štatistický test
Párový t-test t
Slúži na porovnanie výsledkov tých istých osôb meraných v dvoch rôznych časoch alebo podmienkach. Používa sa pri testoch pred–po (pretest–posttest).
t = d̄ / (s_d / √n) kde d = rozdiely párov
Histogram
Rozsah tried (Bin range)
Rozsah buniek obsahujúcich horné hranice tried histogramu. Určuje, ako Excel zoskupuje údaje pri tvorbe histogramu pomocou nástroja Analysis ToolPak.
Analýza dát → Histogram → pole Bin Range
⚙ Príprava

Aktivácia Analysis ToolPak

1

Súbor → Možnosti

Otvorte nastavenia programu Excel.

2

Záložka Doplnky

Spravovať: Doplnky programu Excel → tlačidlo Prejsť.

3

Analysis ToolPak ✓

Zaškrtnite políčko Analysis ToolPak (Analytické nástroje).

4

Záložka Údaje

Zobrazí sa tlačidlo Analýza dát na páse nástrojov.

Údaje — výsledky pretest a posttest (zo 100)

ŽiakPretestPosttestZlepšenie (+)
15268+16
26174+13
34563+18
47380+7
55871+13
64055+15
76679+13
85572+17

Vzorec pre stĺpec Zlepšenie: =C2-B2 — skopírujte nadol D2:D9.

57,5
Priemer pretest
70,3
Priemer posttest
0,94
R² (regresia)
p<0,001
t-test

Pretest vs. Posttest — porovnanie

Zlepšenie podľa žiakov

📈 Výsledok regresie

Jednoduchá lineárna regresia — pretest → posttest

Regresná rovnica:

Ŷ = 0,87 · X + 20,1

kde X = pretest, Ŷ = odhadovaný posttest

Ukazovatele kvality:

R² (vysvetlená variancia)94 %
Korelácia (r)97 %
Interpretácia: Pretest vysvetľuje 94 % variability posttestu. Ak žiak dosiahol o 1 bod viac v preteste, v postteste je to o 0,87 bodu viac.
⚙ ToolPak
1

Popisná štatistika nástrojom

Spustite nástroj Popisná štatistika pre posttest! Aktivujte možnosť Súhrnná štatistika.

Kroky + Výsledok

Analýza dát → Popisná štatistika → C1:C9 → Súhrnná štatistika ✓

Priemer: 70,25 | Smer. odch.: 8,04 Min: 55 | Max: 80 Šikmosť: −0,21 (takmer symetrické) Špicatosť: −1,08 (plochejšie)

Šikmosť ≈ 0 → rozdelenie blízke normálnemu. Záporná špicatosť → plochejšie ako normálne (platykurtické).

⚙ ToolPak
2

Histogram s rozsahom tried

Vytvorte rozsah tried (40, 55, 70, 85, 100) a spustite nástroj Histogram!

Kroky

H1:H5 → zadať hodnoty → Analýza dát → Histogram → Vstup: C2:C9, Bin Range: H1:H5, Graf ✓

40–55: 1 žiak 55–70: 2 žiaci 70–85: 5 žiakov

Najviac žiakov sa nachádza v triede 70–85 bodov. To naznačuje ľavostranné (negatívne) šikmé rozdelenie.

⚙ ToolPak
3

Regresná analýza

Ako pretest predpovedá výsledok posttestu? Spustite jednoduchú lineárnu regresiu.

Kroky

Analýza dát → Regresia → Y: C1:C9, X: B1:B9, Reziduály ✓

R² ≈ 0,94 Smernica ≈ 0,87 Priesečník ≈ 20,1

p < 0,05 → štatisticky významná závislosť. Pretest vysvetľuje 94 % posttestu. Slabší žiaci sa relatívne viac zlepšili.

⚙ ToolPak
4

Párový t-test

Overte štatisticky: je posttest štatisticky lepší ako pretest? (α = 0,05)

Kroky + Záver

Analýza dát → t-test: Párový dvojvýberový → Premenná 1: B2:B9, Premenná 2: C2:C9, Alfa: 0,05

t-stat ≈ −11,2 p (jednostranné) ≈ 0,000006 p < 0,05 → H₀ zamietnutá!

Nulovú hypotézu (žiadne zlepšenie) zamietame. Pedagogická intervencia štatisticky preukázateľne zlepšila výsledky — jednoduchá experimentálna pedagogická evaluácia!