Statistické zpracování výsledků úspěšnosti s Excelem

Základní informace

• Stupeň vzdělávání: gymnázium, střední škola
• Věková skupina: 15 - 19 let
• Vzdělávací obor: matematika a její aplikace
• Tematický okruh: statistika, práce s daty
• Časová dotace:
  • Výuka: 1 - 2 vyučovací hodiny
  • Příprava: 10 minut (vytvoření tabulky v MS Excel a vložení souboru s daty do společného úložiště)

K čemu aktivita směřuje

k počítačovému zpracování ukazatelů charakteristik polohy a variability známek a následnému slovnímu porovnání jednotlivých tříd

Pomůcky, hardware, software

Učitel/ka: PC s připojením na dataprojektor, MS Excel

Žák/yně: PC, MS Excel

Zdroje

https://www.youtube.com/watch?v=wi8DTLiBFEQ

https://www.managementnews.cz/manazer/rizeni-firmy-id-147972/relativni-a-absolutni-odkazy-v-microsoft-excel-id-2793950

Potřebné vstupní znalosti a dovednosti

Oborové

• 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:

\[ (4\cdot(1-2{,}52)^2+11\cdot(2-2{,}52)^2+7\cdot(3-2{,}52)^2+4\cdot(4-2{,}52)^2+1\cdot(5-2{,}52)^2):27) \]

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).

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čí.

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.

Úkoly

1. Určete celkový počet žáků v každé třídě.
2. Vypočtěte průměrné známky v každé třídě. Nastavte formát čísla na dvě desetinná místa.
3. Vypočtěte průměrnou známku všech tříd.
4. Doplňte charakteristiky modus a medián.
5. Vytvořte vzorec pro výpočet rozptylu z dat v tabulce.
6. Vytvořte vzorec pro výpočet směrodatné odchylky z dat rozptylu.
7. Vytvořte vzorec pro výpočet variačního koeficientu z dat směrodatné odchylky a průměru.
8. Porovnejte všechny třídy na základě zjištěných hodnot charakteristiky a variability.

Řešení

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.