Informatika · Matematika · Adatelemzés

Statisztika Excelben
— 3 Tanítási Óra

Interaktív feladatgyűjtemény leíró statisztikától az analitikus eszközökig, vizuális megjelenítéssel és megoldáskulccsal.

Ugrás a tartalomhoz ↓
scroll

1. tanítási óra

Leíró statisztika
Excelben

Középértékek, szórásmérők, kvartilisek, hisztogram – alapfüggvényekkel.

Fogalomtár — 1. óra
Középérték
Átlag x̄
Az adatok összegét elosztjuk az adatok számával. Megmutatja az adathalmaz „súlypontját". Érzékeny a kiugró értékekre.
x̄ = (x₁+x₂+…+xₙ) / n
Középérték
Medián Me
A sorba rendezett adatsor középső eleme. Ha páros számú adat van, a két középső átlaga. Nem érzékeny a kiugró értékekre.
Me = x₍ₙ₊₁₎/₂ (páratlan n esetén)
Középérték
Módusz Mo
A legtöbbször előforduló érték az adatsorban. Egy adatsornak lehet több módusza, vagy egyáltalán nem létezik, ha minden érték egyszer szerepel.
=MÓDUSZ(tartomány)
Szórásmérő
Terjedelem R
A legnagyobb és legkisebb érték különbsége. Egyszerű, de érzékeny a kiugró értékekre, mert csak a két szélső adatot veszi figyelembe.
R = x_max − x_min
Szórásmérő
Szórás s
Az átlagtól való eltérések négyzetének átlaga alól vont gyök. Megmutatja, átlagosan mennyire szóródnak az adatok az átlag körül.
s = √[Σ(xᵢ−x̄)²/(n−1)]
Szórásmérő
Var. együttható CV
A szórás és az átlag hányadosa, százalékban kifejezve. Lehetővé teszi különböző léptékű adatsorok szóródásának összehasonlítását.
CV = (s / x̄) × 100%
Kvantilis
Kvartilis Q1, Q2, Q3
Az adatsor negyedeit elválasztó értékek. Q1 az alsó negyed határa, Q2 a medián, Q3 a felső negyed határa. A dobozdiagram alapja.
=KVARTILIS(tartomány; 1|2|3)
Szórásmérő
IQR Q3−Q1
Interkvartilis terjedelem: a középső 50% terjedelme. Robusztus szórásmutató, nem befolyásolják a kiugró értékek. Outlier-határ: Q1−1,5×IQR és Q3+1,5×IQR.
IQR = Q3 − Q1
—
Átlag (€)
—
Medián (€)
—
Szórás
—
IQR (€)

Adatok — tanulók havi zsebpénze (€)

TanulóA1A2A3A4A5A6A7A8A9A10
€45603075509040558025

Excel: írja be az adatokat a B2:K2 cellákba.

Zsebpénz eloszlása

Dobozdiagram (box plot szimulált)

1

Középértékek

Számítsa ki az átlagot, mediánt és móduszt! Hasonlítsa össze – miért különbözhetnek?

=ÁTLAG(B2:K2) =MEDIÁN(B2:K2) =MÓDUSZ(B2:K2)
Megoldás
ÁTLAG → 55 € MEDIÁN → 52,5 € MÓDUSZ → #HIÁNYZIK (nincs ismétlés)

Az átlag és medián közel vannak → közel szimmetrikus eloszlás. Egyetlen kiugró adat (pl. 500€) az átlagot felfelé torzítaná, a mediánt szinte nem.

2

Szórásmérők

Számítsa ki a terjedelmet, szórást és relatív szórást (variációs együttható)!

=MAX(B2:K2)-MIN(B2:K2) =SZÓRÁS(B2:K2) =SZÓRÁS/ÁTLAG×100%
Megoldás
Terjedelem → 65 Szórás → 21,4 Var. együttható → 38,9%

38,9% közepes szóródás. Szabály: <30% alacsony, 30–60% közepes, >60% erős szóródás.

3

Kvartilisek

Számítsa ki Q1-et és Q3-at! Mekkora az interkvartilis tartomány? Van-e kiugró adat?

=KVARTILIS(B2:K2;1) =KVARTILIS(B2:K2;3) IQR = Q3 − Q1
Megoldás
Q1 → 38,75 € Q3 → 71,25 € IQR → 32,5 | Nincs outlier

Outlier-határok: Q1−1,5×IQR = −9,9 (nincs alatta adat) és Q3+1,5×IQR = 120 (nincs felette). Az adatsor tiszta.

4

Hisztogram DARABTELI-vel

Hozzon létre 4 osztályt és számolja meg a tanulók számát osztályonként!

=DARABTELI(B2:K2;"<=30") =DARABTELITÖBB(B2:K2;">30";...;"<=60")
Megoldás
0–30 € → 2 tanuló 30–60 € → 4 tanuló 60–90 € → 4 tanuló

Az eloszlás közel egyenletes. Jelölje ki a két oszlopot → Beszúrás → Oszlopdiagram a vizualizációhoz.

2. tanítási óra

Adatelemzés &
feltételes függvények

ÁTLAGHA, DARABTELITÖBB, KORREL – osztálynapló elemzése.

Fogalomtár — 2. óra
Feltételes függvény
ÁTLAGHA AVERAGEIF
Átlagot számít, de csak azokra az elemekre, amelyek teljesítik a megadott feltételt. Pl. csak a lányok átlagát, vagy csak az ötöst kapottak átlagát.
=ÁTLAGHA(felt_tart; felt; átlag_tart)
Feltételes függvény
DARABTELITÖBB COUNTIFS
Megszámolja azokat a cellákat, amelyek egyszerre több feltételnek is megfelelnek. Például: ötös matematikából ÉS ötös biológiából.
=DARABTELITÖBB(tart1; felt1; tart2; felt2)
Logikai függvény
HA(ÉS(…)) IF(AND)
Összetett feltételes kifejezés: az ÉS függvény egyszerre több feltétel teljesülését vizsgálja, a HA pedig ennek alapján ad vissza két különböző értéket.
=HA(ÉS(felt1; felt2); "igen"; "nem")
Összefüggés-vizsgálat
Korreláció r
Két változó közötti lineáris összefüggés erősségét és irányát mérő mutató. Értéke −1 és +1 között van: −1 = tökéletes negatív, 0 = nincs összefüggés, +1 = tökéletes pozitív.
=KORREL(tart1; tart2)
Eloszlás
Relatív gyakoriság fᵢ
Megmutatja, hogy az összes megfigyelés hány százaléka esik egy adott értékre vagy osztályba. Összegük mindig 100%.
fᵢ = nᵢ / N × 100%
Eloszlás
Halmozott gyakoriság Fᵢ
Az adott értékig (bezárólag) előforduló összes megfigyelés aránya. Megmutatja, hogy az adatok hány százaléka kisebb vagy egyenlő egy adott értéknél.
Fᵢ = f₁ + f₂ + … + fᵢ
Összefüggés iránya
Pozitív korreláció r > 0
Ha az egyik változó nő, a másik is nő. Például: több tanulás → jobb jegy. Minél közelebb van r az +1-hez, annál szorosabb az összefüggés.
r ∈ (0; +1]
Összefüggés iránya
Negatív korreláció r < 0
Ha az egyik változó nő, a másik csökken. Például: több hiányzás → rosszabb jegy. Az értékünk −0,89, ami erős negatív összefüggést jelez.
r ∈ [−1; 0)
4,4
Lányok mat. átlag
3,0
Fiúk mat. átlag
−0,89
Korreláció
1
Jeles mindkettőből

Hiányzás ↔ Matematikajegy korreláció: −0,89

−1 (erős)
+1 (erős)

Az értéke −0,89: erős negatív összefüggés. Minél több napot hiányzik a tanuló, annál rosszabb a jegye.

Matematikajegy — lány vs. fiú

Hiányzás vs. Matematikajegy (scatter)

Osztálynapló adatok

TanulóNemMat.Bio.HiányzásJeles mindkettőből?
AnnaL542—
BélaF338—
CsillaL451—
DávidF2312—
EszterL550✓ Jeles!
FeriF445—
GrétaL343—
HajniL530—
1

Feltételes átlagok nemek szerint

Számítsa ki a lányok és fiúk matematika-átlagát ÁTLAGHA-val!

=ÁTLAGHA(B:B;"L";C:C) =ÁTLAGHA(B:B;"F";C:C)
Megoldás
Lányok → 4,4 Fiúk → 3,0

A különbség 1,4 jegynyi – de csak 8 fős minta! Nem általánosítható, csupán leíró jellegű megfigyelés.

2

Összetett feltétel — jeles tanulók

Hány tanuló kapott ötöst MINDKÉT tantárgyból? Jelöljük meg a HA(ÉS…) képlettel!

=DARABTELITÖBB(C:C;5;D:D;5) =HA(ÉS(C2=5;D2=5);"Jeles";"")
Megoldás
DARABTELITÖBB → 1 Eszter → Jeles mindkettőből

A HA(ÉS(...)) képletet húzzuk le az egész oszlopra. Csak Eszter teljesítette a feltételt (0 hiányzás, 5-5).

3

Korreláció: hiányzás vs. jegy

Van-e összefüggés a hiányzások száma és a matematikajegy között?

=KORREL(E2:E9;C2:C9)
Megoldás
KORREL → −0,89

Erős negatív korreláció. Értelmezés: 0 = nincs összefüggés, ±1 = tökéletes összefüggés. −0,89 nagyon erős kapcsolatot jelez.

4

Relatív és halmozott gyakoriság

Készítsen gyakorisági táblázatot a matematikajegyekre (2–5)!

=DARABTELI(C2:C9;2) =szám/DARAB2(C2:C9)
Megoldás
2-es: 1 → 12,5% → halmozott: 12,5% 3-as: 2 → 25% → halmozott: 37,5% 4-es: 2 → 25% → halmozott: 62,5% 5-ös: 3 → 37,5% → halmozott: 100%

A tanulók 62,5%-a 4-es vagy annál rosszabb jegyet kapott. A legtöbb tanuló (37,5%) jelesre teljesített.

3. tanítási óra

Analysis ToolPak —
Analitikus eszközök

Leíró statisztika eszközzel, hisztogram, regresszió, páros t-próba.

Fogalomtár — 3. óra
Eloszlás alakja
Ferdeség g₁
Megmutatja, mennyire szimmetrikus az eloszlás. Ha g₁ = 0: szimmetrikus; g₁ > 0: jobbra nyúló (pozitív ferdeség); g₁ < 0: balra nyúló (negatív ferdeség).
Excel: Leíró stat → Ferdeség (Skewness)
Eloszlás alakja
Csúcsosság g₂
Az eloszlás „csúcsos" vagy „lapos" jellegét méri a normáleloszláshoz képest. g₂ > 0: csúcsosabb (leptokurtikus); g₂ < 0: laposabb (platykurtikus) a normálisnál.
Excel: Leíró stat → Csúcsosság (Kurtosis)
Regresszió
R² (determinációs együttható)
Megmutatja, hogy a független változó (X) az Y változó varianciájának hány százalékát magyarázza meg. R² = 0,94 → a variancia 94%-a magyarázott.
R² ∈ [0; 1] → 0% … 100%
Regresszió
Regressziós egyenes ŷ
Az X és Y közötti lineáris összefüggést leíró egyenes. A meredekség (b) azt mutatja, hogy X egységnyi növekedésekor várhatóan mennyit változik Y.
ŷ = b·x + a (meredekség × x + teng.metszet)
Statisztikai próba
Nullhipotézis H₀
Az az alapfeltevés, amelyet tesztelünk – általában „nincs különbség" vagy „nincs hatás". Ha a p-érték kicsi (p < α), elutasítjuk H₀-t.
H₀: μ_előtest = μ_utótest
Statisztikai próba
p-érték p
Annak valószínűsége, hogy a megfigyelt különbség véletlenszerűen adódna, ha H₀ igaz lenne. Ha p < 0,05 (szignifikanciaszint), az eredmény statisztikailag szignifikáns.
p < α (0,05) → H₀ elutasítva
Statisztikai próba
Páros t-próba t
Ugyanazon személyek két különböző időpontban vagy feltétel mellett mért eredményeinek összehasonlítására szolgál. Elő–utótest vizsgálatoknál alkalmazzuk.
t = d̄ / (s_d / √n) ahol d = különbségek
Hisztogram
Raktártartomány bins
A hisztogram osztályainak felső határait tartalmazó cellatartomány. Meghatározza, hogyan csoportosítja Excel az adatokat a hisztogram elkészítésekor.
Adatelemzés → Hisztogram → Raktár mező
⚙ Előkészítés

Analysis ToolPak bekapcsolása

1

Fájl → Beállítások

Nyissa meg az Excel beállításait.

2

Bővítmények fül

Kezelés: Excel-bővítmények → Ugrás gomb.

3

Elemzőeszközök ✓

Jelölje be az Elemzőeszközök (Analysis ToolPak) jelölőnégyzetet.

4

Adatok fül

Megjelenik az Adatelemzés gomb a szalagban.

Adatok — előtest és utótest eredmények (100-ból)

TanulóElőtestUtótestFejlődés (+)
15268+16
26174+13
34563+18
47380+7
55871+13
64055+15
76679+13
85572+17

Fejlődés képlete: =C2-B2 — húzzuk le D2:D9-ig.

57,5
Előtest átlag
70,3
Utótest átlag
0,94
R² (regresszió)
p<0,001
t-próba

Előtest vs. Utótest — összehasonlítás

Fejlődés mértéke tanulónként

📈 Regresszió eredménye

Egyváltozós regresszió — előtest → utótest

Regressziós egyenlet:

Ŷ = 0,87 · X + 20,1

ahol X = előtest, Ŷ = becsült utótest

Illeszkedési mutatók:

R² (magyarázó erő)94%
Korreláció (r)97%
Értelmezés: Az előtest eredménye 94%-ban magyarázza meg az utótest eredményét. Ha egy tanuló 1 ponttal jobban írt előtesztet, várhatóan 0,87 ponttal jobban ír utótesztet is.
⚙ ToolPak
1

Leíró statisztika eszközzel

Futtassa a Leíró statisztika eszközt az utótest adataira! Aktiválja az Összefoglalás statisztika opciót.

Lépések + Eredmény

Adatelemzés → Leíró statisztika → C1:C9 → Összefoglalás ✓

Átlag: 70,25 | Szórás: 8,04 Min: 55 | Max: 80 Ferdeség: −0,21 (közel szimm.) Csúcsosság: −1,08 (lapos)

Ferdeség ≈ 0 → normálishoz közeli eloszlás. Negatív csúcsosság → a normálisnál lapítottabb (platykurtikus).

⚙ ToolPak
2

Hisztogram raktártartománnyal

Hozzon létre raktártartományt (40, 55, 70, 85, 100), majd futtassa a Hisztogram eszközt!

Lépések

H1:H5 → értékek beírása → Adatelemzés → Hisztogram → Bevitel: C2:C9, Raktár: H1:H5, Diagram ✓

40–55: 1 tanuló 55–70: 2 tanuló 70–85: 5 tanuló

A legtöbb tanuló 70–85 pont közé esett. Ez negatívan ferde eloszlást jelez (a zöme a felső tartományban van).

⚙ ToolPak
3

Regresszióelemzés

Az előtest hogyan jósolja meg az utótest eredményét? Egyváltozós regresszió futtatása.

Lépések

Adatelemzés → Regresszió → Y: C1:C9, X: B1:B9, Maradékok ✓

R² ≈ 0,94 Meredekség ≈ 0,87 Tengelymetszet ≈ 20,1

p < 0,05 → szignifikáns összefüggés. Az előtest 94%-ban magyarázza az utótestet. Gyengébb diákok relatíve sokat fejlődtek.

⚙ ToolPak
4

Páros t-próba

Igazolja statisztikailag: az utótest szignifikánsan jobb-e az előtestnél? (α = 0,05)

Lépések + Következtetés

Adatelemzés → t-próba: Páros két mintás → 1. változó: B2:B9, 2.: C2:C9, Alpha: 0,05

t-stat ≈ −11,2 p (egyoldalas) ≈ 0,000006 p < 0,05 → H₀ elutasítva!

A nullhipotézist (nincs fejlődés) elutasítjuk. Az oktatási beavatkozás statisztikailag igazolhatóan javított az eredményeken — egyszerű kísérleti pedagógiai értékelés!