Podpůrný matematický aparát pro řešitele DECOX v tabulkových procesorech

Podprogramy a funkce v doplňku pro Excel

Doc. Dr. Vladimír Homola, Ph.D.

Abstrakt

Článek popisuje instalaci a použití doplňku řešiteli výzkumného záměru MSM 6198910019 v případech, kdy pro uchování a předzpracování dat používají tabulkový procesor Excel. Článek přímo navazuje na obecný popis tvorby doplňků (viz Homola - zde). V popisované podobě jde o doplněk ve verzi Office 2013. Základní ideou popisovaného doplňku je - na rozdíl od jiných komerčně i amatérsky vytvořených doplňků - to, že všude kde je to jen trochu možné, generují programy doplňku nikoliv výsledná data, ale vzorce požadovaná data počítající. To má nesmírný význam právě při vědeckých a výzkumných pracích, kde je tím umožněno použít tabulkového procesoru do jisté míry jako nástroje pro dynamické modelování apod.

Dodání a instalace

Doplněk tvoří jediný soubor, jehož verze je dostupná ke stažení jako DECOX.XLAM - zde. Po stažení je nejjednodušší umístit soubor do kteréhokoliv adresáře a při otevřeném libovolném sešitu nastavit programu Excel cestu k němu pomocí hlavního menu VÝVOJÁŘ položka Doplňky a v následně zobrazeném formuláři stisknutím tlačítka Procházet:

 

 

Obr. 1: Volba doplňků

Po označení v zaškrtávacím poli příslušného doplňku a stisknutí tlačítka OK je doplněk otevírán současně s každým Excelem otevřeným sešitem, ve kterém pak lze využívat nástroje doplňku.

Menu doplňku

Po shora popsané instalaci doplňku je nabídka hlavního menu DOPLŇKY rozšířena o novou položku:

 

Obr. 2: Rozříření menu DOPLŇKY

 

Po aktivaci položky DECOX je rozvinuto pod-menu obsahující nabídku vlastního doplňku:

 

Obr. 3: Nabídka funkcí doplňku

 

Demonstrační data

Pro dokumentaci popisovaných funkcí jsou použity části dat řešeného výzkumného záměru. Uveďme ku příkladu vybrané analýzy na veličinu P (celkový fosfor [mg/kg]) v několika lokalitách pro několik jedinců:

Data jsou umístěna v sešitu na listu lDreviny (viz označení v adresách oblastí v příkladech níže).

Výběr oblasti uživatelem

Ve formulářích jednotlivých funkcí doplňku zadává uživatel nejčastěji oblast s daty určenými ke zpracování. Slouží k tomu ovládací prvek třídy RefEdit. Běžný uživatel Excelu zná jeho vzhled podle obr. 4, ve formulářích doplňku má vzhled podle obr. 5 - liší se tedy pouze vzhledem "sbalovacího" tlačítka v pravé části ovládacího prvku, funkčnost je totožná: je-li takový prvek aktivní, pak po pokusu o označení nějaké oblasti v nějakém listu se celý formulář "sbalí" a adresa oblasti označovaná (vybíraná) uživatelem se průběžně vypisuje do textového pole ovládacího prvku. Po výběru oblasti uživatelem je adresa jím označené oblasti zapsána v textové části ovládacího prvku.

 

Obr. 4: Prvek pro výběr v Excelu		Obr. 5: Prvek pro výběr v Doplňku

Jednotlivé funkce doplňku

Neparametrické statistiky

Tato funkce umístí na zadané místo sešitu základní neparametrické statistiky (kvartily, medián) a pro srovnání také vybrané parametrické statistiky (aritmetický a geometrický průměr a střední hodnotu).

Po volbě Neparametrické statistiky menu DECOX je zobrazen formulář pro zadání dat a volbu umístění výsledku podle obr. 6. Po nastavení kurzoru do každého ze dvou výběrových polí se zobrazí nápověda - na obrázku pro pole zpracovávaných dat:

 

Obr. 6: Parametry úlohy

 

Stisknutím výběrového tlačítka lze označit požadovanou oblast - např. zpracovávaná data:

 

Obr. 7: Výběr dat tažením myší

 

Po vyplnění zobrazuje formulář požadované informace:

 

Obr. 8: Vyplněný formulář

 

Stisknutím tlačítka Hotovo se do oblasti určené pro výsledek umístí statistiky dat ze zadané oblasti:

 

 

Obr. 9: Výsledné statistiky

 

Krabicový graf  (boxplot, box-and-whisker plot)

Tato funkce umístí na zadané místo sešitu základní neparametrické statistiky (kvartily, medián) a pro srovnání také vybrané parametrické statistiky (aritmetický a geometrický průměr a střední hodnotu) jedné veličiny, a k nim vloží krabicový graf orientovaný vodorovně nebo svisle. V tomto grafu jsou navíc vyznačeny polohy všech tří parametrických statistik.

Po volbě Krabicový graf menu DECOX je zobrazen formulář pro zadání dat, volbu umístění výsledku a požadovaný titulek grafu podle obr. 10. Stisknutím výběrových tlačítek lze označit požadované oblasti, titulek se do textového pole zadá z klávesnice (obr. 11):

 

Obr. 10: Parametry úlohy

Obr. 11: Vyplněný formulář

 

Stisknutím tlačítka Hotovo se do oblasti určené pro výsledek umístí statistiky dat ze zadané oblasti spolu s krabicovým grafem:

 

Obr. 12: Výsledné statistiky a vodorovný graf

 

případně pro verzi se svislým grafem

 

Obr. 13: Výsledné statistiky a svislý graf

 

Jak bylo zdůrazněno v úvodu článku, generované hodnoty i grafy jsou nikoliv statické, ale přes vzorce vázány na zdrojová data, lze je tedy (např. graf) upravovat běžnými nástroji Excelu. To se týká zejména polohy a rozměru grafu. Jinak také řečeno, při změně zdrojových dat se graf ihned překreslí.

V grafu jsou také znázorněny polohy aritmetického a geometrického průměru a střední hodnoty třemi barevnými úsečkami. Podržením myši nad některou z nich se zobrazí informace (viz obr. 14) o datové řadě, přičemž názvy "qGp", "qSt" a "qPr" jsou po řadě přiřazeny geometrickému průměru, střední hodnotě a aritmetickému průměru.

 

Obr. 14: Informace u datové řadě

 

Krabicové grafy  (boxplots, box-and-whisker plots)

Tato funkce umístí na zadané místo sešitu základní neparametrické statistiky (kvartily, medián) a pro srovnání také vybrané parametrické statistiky (aritmetický a geometrický průměr a střední hodnotu) několika veličin, a k nim vloží krabicové grafy orientované vodorovně nebo svisle. V těchto grafech jsou navíc vyznačeny polohy všech tří parametrických statistik.

Po volbě Krabicové grafy menu DECOX je zobrazen formulář pro zadání dat, volbu umístění výsledku a požadovaný titulek grafů podle obr. 15. Stisknutím výběrových tlačítek lze označit požadované oblasti, titulek se do textového pole zadá z klávesnice nebo lze převzít z buňky sešitu (obr. 16):

 

Obr. 15: Parametry úlohy

Obr. 16: Vyplněný formulář

 

Zaškrtávací pole Data obsahují nadpisy je použito ve zřejmém významu. Zaškrtávací pole Automatická velikost písma se vztahuje k pozdější "ruční" úpravě velikosti grafu - viz níže.

Stisknutím tlačítka Hotovo se do oblasti určené pro výsledek umístí statistiky dat ze zadané oblasti spolu s krabicovými grafy:

 

Obr. 17: Výsledné statistiky a vodorovné grafy

 

případně pro verzi se svislými grafy

 

Obr. 18: Výsledné statistiky a svislé grafy

 

Jak bylo zdůrazněno v úvodu článku, generované hodnoty i grafy jsou nikoliv statické, ale přes vzorce vázány na zdrojová data, lze je tedy (např. graf) upravovat běžnými nástroji Excelu. To se týká zejména polohy a rozměru grafu. Jinak také řečeno, při změně zdrojových dat se graf ihned překreslí. Při změně velikosti grafu, zvláště razantním zmenšení, se však může zmenšit čitelnost doprovodných textů. Vlastnost automatické proporcionální změny velikosti písma lze ovlivnit zaškrtnutím nebo odškrtnutím pole Automatická velikost písma ... v úvodním formuláři funkce (obr. 15 a 16): není-li zaškrtnuto, písmo nemění při změně velikosti grafu velikost.

V grafu jsou také znázorněny polohy aritmetického a geometrického průměru a střední hodnoty třemi barevnými úsečkami. Podržením myši nad některou z nich se zobrazí informace (viz obr. 14) o datové řadě, přičemž názvy "qGp", "qSt" a "qPr" jsou po řadě přiřazeny geometrickému průměru, střední hodnotě a aritmetickému průměru.

Koeficienty korelace

Tato funkce umístí na zadané místo sešitu koeficienty korelace mezi dvojicemi sledovaných veličin.

Po volbě Koeficienty korelace menu DECOX je zobrazen formulář pro zadání dat a volbu umístění výsledku podle obr. 19. Stisknutím výběrových tlačítek lze označit požadované oblasti, titulek se do textového pole zadá z klávesnice nebo lze převzít z buňky sešitu (obr. 20):

 

 

Obr. 19: Parametry úlohy

Obr. 20: Vyplněný formulář

 

Stisknutím tlačítka Hotovo se do oblasti určené pro výsledek umístí koeficienty korelace:

 

Obr. 21: Výsledné koeficienty korelace

 

Jak bylo zdůrazněno v úvodu článku, generované výsledné hodnoty jsou nikoliv statické, ale přes vzorce vázány na zdrojová data. Jinak také řečeno, při změně zdrojových dat se koeficienty ihned přepočtou. V buňkách výsledných koeficientů je umístěn vzorec volající funkci CORREL Excelu, z toho plyne způsob výpočtu i chování funkce. Platí např.: Jestliže matice dat nebo odkaz obsahuje text, logické hodnoty nebo prázdné buňky, jsou tyto hodnoty ignorovány; buňky s nulovou hodnotou jsou však započítávány.

Co do matematické podstaty je koeficient korelace definovaný obvyklým způsobem - jako podíl kovariance dvou veličin dělený součinem jejich směrodatných odchylek.

Buňky s koeficienty korelace nejsou nijak formátovány; je ponecháno na uživateli, aby zvolil nejvhodnější formát pro svůj daný účel. To se týká nejen např. počtu desetinných míst, ale i např. podmíněného formátování s ohledem na významnost koeficientu korelace apod.

Kritická hodnota

Tato funkce umístí na zadané místo sešitu kritickou hodnotu Studentova rozložení s (n-2) stupni volnosti na hladině významnosti (p%)/100. Spolu s hodnotou dále popisované funkce - testovacím kriteriem hypotézy o lineární nezávislosti dvou veličin - slouží k posouzení, zda koeficient korelace mezi dvěma veličinami vypovídá o jejich možné lineární závislosti.

Po volbě Kritická hodnota menu DECOX je zobrazen formulář pro zadání počtu dat, hladiny významnosti a volbu umístění výsledku podle obr. 22. Stisknutím výběrových tlačítek lze označit požadované oblasti s hodnotami (obr. 23):

 

 

Obr. 22: Parametry úlohy

Obr. 23: Vyplněný formulář

 

Stisknutím tlačítka Hotovo se do oblasti určené pro výsledek umístí kritická hodnota:

 

Obr. 24: Výsledná kritická hodnota

 

Jak bylo zdůrazněno v úvodu článku, generované výsledné hodnoty jsou pokud možno nikoliv statické, ale přes vzorce vázány na zdrojová data. Jinak také řečeno, při změně zdrojových dat se vzorce ihned přepočtou. V buňce výsledné kritické hodnoty je umístěn vzorec volající funkci TINV Excelu, z toho plyne způsob výpočtu i chování funkce. Výsledkem je hodnota t distribuční funkce Studentova t-rozdělení jako funkce pravděpodobnosti (=hladina významnosti/100) a stupňů volnosti (=počet vzorků dat-2).

Buňka s kritickou hodnotou není nijak formátována; je ponecháno na uživateli, aby zvolil nejvhodnější formát pro svůj daný účel.

Testovací kriterium koeficientu korelace

Tato funkce umístí na zadané místo sešitu testovací kritérium lineární nezávislosti dvou veličin jako funkci koeficientu korelace a počtu vzorků dat. Spolu s hodnotou výše popisované funkce - kritickou hodnotou hypotézy o lineární nezávislosti dvou veličin - slouží k posouzení, zda koeficient korelace mezi dvěma veličinami vypovídá o jejich možné lineární závislosti.

Po volbě Testovací kriterium koeficientu korelace menu DECOX je zobrazen formulář pro zadání počtu dat, hladiny významnosti a volbu umístění výsledku podle obr. 25. Stisknutím výběrových tlačítek lze označit požadované oblasti s hodnotami (obr. 26):

 

 

Obr. 25: Parametry úlohy

Obr. 26: Vyplněný formulář

 

Stisknutím tlačítka Hotovo se do oblasti určené pro výsledek umístí hodnota testovacího kriteria:

 

Obr. 27: Výsledná hodnota testovacího kriteria

 

Jak bylo zdůrazněno v úvodu článku, generované výsledné hodnoty jsou pokud možno nikoliv statické, ale přes vzorce vázány na zdrojová data. Jinak také řečeno, při změně zdrojových dat se vzorce ihned přepočtou. V buňce výsledné hodnoty testovacího kriteria je umístěn vzorec - viz řádek vzorců v obr. 27. Odpovídá běžně používané definici

kde t je hodnota testovacího kritéria, R je koeficient korelace a n je počet vzorků dat. Buňka s hodnotou testovacího kriteria není nijak formátována; je ponecháno na uživateli, aby zvolil nejvhodnější formát pro svůj daný účel.

Významnost koeficientu korelace pro lineární nezávislost

Tato funkce umístí na zadané místo sešitu posouzení, zda koeficient korelace mezi dvěma veličinami vypovídá o jejich možné lineární závislosti.

Po volbě Významnost koeficientu korelace menu DECOX je zobrazen formulář pro zadání posuzovaného koeficientu korelace, počtu dat, hladiny významnosti a volbu umístění výsledku podle obr. 28. Stisknutím výběrových tlačítek lze označit požadované oblasti s hodnotami (obr. 29):

 

 

Obr. 28: Parametry úlohy

Obr. 29: Vyplněný formulář

 

Stisknutím tlačítka Hotovo se do oblasti určené pro výsledek umístí výsledek posouzení:

 

Obr. 30: Výsledná hodnota posouzení lineární závislosti

 

Jak bylo zdůrazněno v úvodu článku, generované výsledné hodnoty jsou pokud možno nikoliv statické, ale přes vzorce vázány na zdrojová data. Jinak také řečeno, při změně zdrojových dat se vzorce ihned přepočtou. V buňce výsledné hodnoty testovacího kriteria je umístěn vzorec kombinující funkce popsané ve dvou předchozích odstavcích - viz také řádek vzorců v obr. 30.

Buňka s výsledkem posouzení obsahuje logickou hodnotu PRAVDA nebo NEPRAVDA. Přísně statisticky vzato, hodnota NEPRAVDA znamená, že testovací kritérium je v oboru kritických hodnot pro nulovou hypotézu, kterou je hypotéza "totální nezávislosti" - korelační koeficient dvojrozměrného základního souboru je roven nule. Jinak řečeno, hodnota PRAVDA znamená, že testovací kritérium koeficientu korelace svědčí pro přijetí nulové hypotézy, tedy o lineární nezávislosti. V takovém případě však precizní statistický závěr zní: není nutno zamítnout hypotézu o lineární nezávislosti. Tento výrok, obsahující de facto tři negace, je však pro ne-statistického odborníka dosti krkolomný, proto je v doplňku použita formulace poněkud vágnější, ale lépe pochopitelná.

Závěr

Výsledky

Článek popsal použití doplňku (Add In) použitelného programem Excel v jeho sešitech, a to se zaměřením na problematiku statistického vyhodnocení dat přímo souvisejících s výzkumným záměrem Procesy snižování emisí COx.

Poděkování

Autor děkuje poskytovateli výzkumného záměru MSM 6198910019 za přidělení a podporu projektu PROCESY SNIŽOVÁNÍ EMISÍ CO₂ - deCO_x PROCESY.

 

 

 

Rev. 6 / 2016