Statistické zpracování výsledků úspěšnosti s Excelem
Odborný článek
Digitalizace ve vzdělávání
Statistické zpracování výsledků úspěšnosti s Excelem
Aktivita je určena pro práci žáků s tabulkovým procesorem MS Excel v rámci statistického vyhodnocení a následného porovnání úspěšnosti žáků v testu. Předmětem srovnání jsou fiktivní výsledky stejné písemné práce zadané ve čtyřech paralelních třídách. Soubor je zadán rozdělením četností známek v daných třídách.
Cíl výuky
Žák/žákyně
aktivně využívá funkci SUMA (Součet), ODMOCNINA (případně SMODCH, VAR)
vzorec zadaný v první buňce zkopíruje do ostatních buněk tabulky
správně posoudí efektivitu využití digitálních nástrojů a paměťového výpočtu
na základě znalosti způsobu výpočtu vybrané statistické charakteristiky vytvoří novou funkci s relativními a absolutními odkazy na data v tabulce
v případě potřeby zobrazí zadaný vzorec, zkontroluje, doplní nebo opraví syntaxi
nastaví zobrazení výsledků číselných dat se dvěma desetinnými místy
průběžně porovnává vypočtené hodnoty se svým odhadem
interpretuje závěry vyplývající ze zjištěných číselných hodnot statistických ukazatelů z pohledu porovnání úspěšnosti napříč třídami
znalost statistických charakteristik polohy (aritmetický průměr, modus, medián), způsobů jejich výpočtu a porozumění jejich významu
znalost statistických ukazatelů variability (rozptyl, směrodatná odchylka, variační koeficient), způsobů jejich výpočtu a porozumění jejich významu
schopnost interpretovat porovnání výsledných hodnot
Digitální
stažení souboru z úložiště
základní ovládání programu MS Excel v oblasti výpočtů (vytvořit vzorec a vložit funkci)
dovednost zapsat druhou mocninu základu (např. pomocí operátoru „^“, případně užitím funkce POWER)
rozlišení absolutního a relativního odkazu buňky
pojmenování souboru a uložení ve formátu Sešit aplikace Excel na určené místo
Přínos využití digitálních technologií
efektivní zpracování statistických ukazatelů číselných dat
Metodická poznámka
Známá data (zadání úlohy) lze žákům zadat v podobě naformátované tabulky, není to však nutná podmínka. Soubor je zadán rozdělením četností známek v jednotlivých třídách. Naším záměrem je zpracovat tato data pokud možno bez nutnosti výpisu hodnot jednotlivých znaků. Chceme také zjistit, jak si žáci poradí se zápisem syntaxe vzorců a zda v nich aplikují absolutní odkazy.
Vyučující uloží tabulku v Excelu se zadáním pro žáky do společného úložiště. Odtud si žáci soubor stáhnou.
Plnění úkolu provádíme v několika etapách. Mají-li žáci už nějaké zkušenosti s využitím programu MS Excel, mohou pracovat více samostatně a výsledky společně průběžně kontrolujeme.
Na začátku se zaměříme na zjištění charakteristik polohy. Nejdříve diskutujeme se žáky, jakým způsobem jsou data zadána (tabulka absolutních četností známek v jednotlivých třídách) a jak efektivně určit z těchto dat aritmetický průměr (např. využití funkcí Součet – SUMA, Průměr - AVERAGE), modus a medián (tyto dvě charakteristiky je pro náš případ rychlejší stanovit bez užití digitálních nástrojů). Společně se domluvíme na pořadí určení jednotlivých charakteristik, kterými nadepíšeme další sloupce/řádky tabulky (viz ukázka řešení). Při definování vzorce pro výpočet průměrné známky v každé třídě je vhodné využít tzv. absolutní odkaz na buňku s hodnotou známky (jedná se o konstanty, jejichž hodnoty zůstávají neměnné). Pokud ve vzorci ponecháme relativní odkaz na tyto buňky, správný výpočet se objeví pouze v první definované buňce. Absolutní odkaz vytvoříme tak, že po zadání adresy buňky s hodnotou známky stiskneme klávesu F4. Ta zajistí vložení znaku dolaru před označení sloupce a čísla řádku. Po zkopírování vzorce (s absolutními odkazy) dvojklikem na čtvereček v pravém dolním rohu buňky nebo jeho přetažením máme zaručený správný výpočet průměrné známky i v ostatních třídách.
Formát čísla nastavíme na dvě desetinná místa (karta Domů - nabídka Formát čísla - Číslo).
Nejobtížnějším úkolem je bezchybný zápis vzorce pro výpočet rozptylu. Doporučujeme nejdříve společně odvodit a poté zapsat podobu výpočtu pro data z první uvedené třídy na tabuli:
Následně je důležité, aby žáci sledovali, v jaké buňce se která hodnota vyskytuje. Pak pomocí kliknutí na buňku tvoří vzorec s odkazy právě do označené buňky. Opět je vhodné uvědomit si, které hodnoty zůstávají ve vzorci vždy stejné (hodnoty známek a hodnota aritmetického průměru), a využít absolutní odkazy na buňky s těmito čísly, obrázek 1 (pro zvětšení vždy na obrázek klikněte).
Obrázek 1 - Ukázka zobrazení zápisu ve vstupním řádku s využitím klávesy F4
V případě, že se jim napoprvé nepodaří syntaxi vzorce zapsat správně, rozklikneme buňku ve které očekáváme výsledek a zobrazí se kompletní definovaný vzorec. Díky barevnému rozlišení (obrázek 2) se v něm můžeme snadno zorientovat a případnou chybu opravit. Pokud je syntaxe nesprávná, program obvykle problematické místo i označí.
Obrázek 2 - Ukázka zobrazení syntaxe po rozkliknutí buňky
Další možností, jak stanovit rozptyl, je přidat sloupce s pomocnými výpočty, například výpočet absolutní hodnoty rozdílu známky od aritmetického průměru, výpočet druhé mocniny předchozího rozdílu a nakonec výpočet rozptylu s využitím odkazů na předchozí výpočty.
Třetí způsob zjištění hodnoty rozptylu je využití v programu definované funkce VAR s označením rozsahu buněk s vypsanými daty. Výběr metody výpočtu rozptylu necháváme na vyučujícím.
K výpočtu směrodatné odchylky využijeme matematickou funkci ODMOCNINA, v níž bude argumentem odkaz na buňku s hodnotou rozptylu. Jestliže jsme již dříve z tabulky četností vypsali seznam všech dat, můžeme použít definovanou statistickou funkci SMODCH (opět s označením buněk s daty).
Poslední charakteristikou je variační koeficient vyjádřený v procentech. Definujeme funkci podílu příslušné směrodatné odchylky a průměru vynásobeného stem.
Provedeme společnou kontrolu výsledků zjištěných hodnot ukazatelů variability.
Poslední část úlohy je zaměřena na schopnost žáků interpretovat zjištěné hodnoty v souvislosti s porozuměním významu statistických ukazatelů. Je třeba poskytnout žákům dostatečný prostor pro slovní vyjádření. Z tohoto důvodu můžeme přesunout diskuzi na začátek následující hodiny, v níž stačí, aby byly výsledky zpracování žákům vytištěny nebo promítnuty. Máme-li možnost také v této vyučovací hodině pracovat s digitální technikou, lze žákům navíc ukázat například změny charakteristik při změně hodnoty znaku, popřípadě další definované statistické funkce programu.
Doporučujeme, aby si vyučující předem od kolegy informatika zjistil, s jakým odstupem žáci uvažované třídy naposledy pracovali v MS Excel (setkali jsme se i s prodlevou 2 let!) - a zda při tvorbě vzorců využívali absolutní odkaz na buňku. V ideálním případě se matematik s informatikem domluví a naplánuje výuku statistiky v návaznosti na výuku MS Excel v informatice.
Popis vzdělávací aktivity
Úloha - Porovnání výsledků písemné práce v paralelních třídách
Na konci druhého ročníku SOŠ byla v paralelních třídách zadána stejná písemná práce z matematiky. Výsledky jsou zaznamenány v tabulce 1.
známka
třída
1
2
3
4
5
2. A
4
11
7
4
1
2. B
3
8
10
6
0
2. C
4
11
11
0
2
2. D
6
10
8
2
3
Úkoly
Určete celkový počet žáků v každé třídě.
Vypočtěte průměrné známky v každé třídě. Nastavte formát čísla na dvě desetinná místa.
Vypočtěte průměrnou známku všech tříd.
Doplňte charakteristiky modus a medián.
Vytvořte vzorec pro výpočet rozptylu z dat v tabulce.
Vytvořte vzorec pro výpočet směrodatné odchylky z dat rozptylu.
Vytvořte vzorec pro výpočet variačního koeficientu z dat směrodatné odchylky a průměru.
Porovnejte všechny třídy na základě zjištěných hodnot charakteristiky a variability.
Řešení
známka
třída
1
2
3
4
5
celkový počet
průměrná známka
modus
medián
rozptyl
směrodatná odchylka
variační koeficient [%]
2. A
4
11
7
4
1
27
2,52
2
2
1,06
1,03
40,97
2. B
3
8
10
6
0
27
2,70
2
3
0,88
0,94
34,60
2. C
4
11
11
0
2
28
2,46
2 a 3
2
0,96
0,98
39,82
2. D
6
10
8
2
3
29
2,52
2
2
1,42
1,19
47,37
CELKEM
17
40
36
12
6
111
2,55
2
2
---
---
---
Nejlepší průměr v ročníku má třída 2.C a je zřejmé, že by mohl být výrazně lepší, nebýt dvou žáků se známkou 5. V porovnání s ostatními známkami ve třídě jsou tyto extrémní. Bez nich by žáci ve třídě vykazovali srovnatelné velmi dobré výsledky. Třída 2.C jako jediná celkově dosáhla nadprůměrné hodnoty známky.
Stejného průměru dosáhli shodně žáci tříd 2.A a 2.D. Hodnoty rozptylu, směrodatné odchylky i variačního koeficientu ukazují, že ve třídě 2.A dosahují žáci srovnatelnějších výsledků než ve 2.D, kde je více výborných žáků, ale také více žáků s pětkou.
Jako nejvíce homogenní skupina se ukazuje třída 2.B, ve které jsou hodnoty charakteristik variability nejmenší. Z tabulky také vidíme, že rozpětí obdržených známek je 1 - 4. Žádný žák této třídy nebyl hodnocen známkou nedostatečnou. Ovšem průměrná známka je v této třídě nejhorší, od ostatních se výrazně liší a dosahuje podprůměrné hodnoty v porovnání s paralelními třídami.
Reflexe
Aktivitu jsme vyzkoušeli se žáky, kteří, jak se v průběhu hodiny projevilo, neměli příliš praktických zkušeností s využíváním tabulkového procesoru MS Excel. Chtěli jsme ověřit, do jaké míry si poradí s využitím a tvorbou vzorců v MS Excel, zda umí použít absolutní odkaz. V tabulce se žáci správně orientovali, dobře zvládli i využití funkce SUMA a výpočet aritmetického průměru. Všichni se také shodli na „ručním“ stanovení charakteristik modus a medián v našem případě jako efektivnějším způsobu v porovnání s užitím specializované funkce programu. Žáci však obtížně chápali pojmy absolutní a relativní odkaz, vzorec pro rozptyl už jsme do tabulky v MS Excel v jedné hodině zapsat nestihli. V následující hodině matematiky jsme ručně dopočítali zbývající charakteristiky variability a formou společné diskuze jsme provedli porovnání všech tříd.
Jak se ukázalo, výuka popisné statistiky by si zasloužila podpořit souvislým využíváním tabulkového procesoru, aby si žáci dostatečně osvojili efektivní nástroje digitálních technologií při práci s množstvím reálných dat a mohli se více zaměřit na jejich analýzu a vyhodnocení.
