Použití tabulkového procesoru pro dynamické modelování v geovědách

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

Abstrakt

Jak při výuce, tak také v odborné praxi je často zapotřebí zkoumat chování nějaké veličiny v závislosti na chování veličin jiných. Většinou je chování zkoumané veličinu popsáno funkčními vztahy a stav oněch jiných veličin parametry těchto funkčních vztahů. Největší názornost vnese do procesu modelování dynamická změna parametrů doprovázená grafickou informací. Článek popisuje použití tabulkového procesoru, konkrétně Excelu, právě k takovým účelům. Vysvětluje jednak méně známé vlastnosti často používaných konstrukcí, jednak méně známé objekty a vlastnosti, to vše v netradičním kontextu dynamického modelování. Jako modelový příklad používá geofyzikální metodu zjišťování složek magnetického pole mocné desky známou jako přímá úloha magnetometrie.

Úvod

Poměrně zdařilý tabulkový procesor Excel dodávaný firmou Microsoft v rámci MS Office obsahuje některé možnosti, které nejsou obecně známy, a přitom jsou velmi silným nástrojem pro specifické aplikace. Tento článek z nich diskutuje XY graf, použití ovládacích prvků, a některé aspekty pojmenovaných oblastí.

Článek je určen těm, kteří svá data zpracovávají v prostředí tabulkových procesorů, protože zpracování v prostředí jiných - např. databázových - systémů jim z nějakých důvodů nevyhovuje. Mnozí takoví uživatelé, odborníci z oblasti geověd zvlášť (možná s výjimkou geoinformatiky) však mají vrozenou - nebo snad vypěstovanou? - nechuť k tomu, čemu se říká programování. Proto se tento článek zaměřuje na takové postupy, které programování nevyžadují, přestože nějakých pár příkazů by mnohdy řešení zjednodušilo, zjasnilo.

Jako modelový příklad je pro dané téma zvoleno vyšetřování magnetického pole geologického tělesa tvaru mocné svislé desky, tedy příklad z oblasti užité geofyziky. Aplikace z jiných oblastí jsou pak analogií poznatků a postupů uvedených v článku.

Teorie k modelovému příkladu

K modelovému příkladu je zapotřebí uvést nezbytnou teorii. Jde sice o specializované téma, bylo však vybráno pro jednoduchou abstrahovatelnost od problému magnetického pole a jednoduché povýšení na obecnou úroveň.

Přímá úloha magnetometrie

Přímou úlohou magnetometrie se (podle [1]) rozumí nalezení magnetického potenciálu dle daného tvaru, rozměru a magnetizace horninového tělesa. Ačkoliv jsou tvary právě horninových těles složité a různorodé, mohou být mnohé z nich v prvním přiblížení aproximovány pravidelným geometrickým tvarem. Za druhé: skutečné horninové těleso bývá začasté magneticky značně nehomogenní. Ale stejně tak často bývá střední hodnota magnetizace ve větších dílčích objemech tělesa blízká a proto - měřeno v dostatečných vzdálenostech - se těleso bude jevit jako přibližně magneticky homogenní.

Zkoumejme tedy horninové těleso idealizované do mocné desky - tak je označován velmi dlouhý pravoúhlý hranol o mocnosti m. Tak také mohou být zjednodušeny mnohé vrstvy. Označme h₁ vzdálenost svrchní a h₂ vzdálenost spodní vrstevní plochy od povrchu a zkoumejme vertikální (Z) a horizontální (H) složku magnetického pole takového tělesa v nějakém bodě P srovnávací roviny, zde tedy povrchu Země. Vlastní magnetizace tělesa nechť je M. Situaci znázorňuje obrázek 1 - svislý řez, v němž pro zjednodušení pozdějších vzorců je použito označení m = 2b.
 

Obr. 1: Označení v úloze magnetometrie

Vynechme detaily matematického odvození. Plyne z něj, že při označení

p₁ = (x + b) / h₁

p₂ = (x - b) / h₁

p₃ = (x + b) / h₂

p₄ = (x - b) / h₂

a dále

s₁ = 1 + p₁²

s₂ = 1 + p₂²

s₃ = 1 + p₃²

s₄ = 1 + p₄²

je

Z = 2 . c . M . (arctg p₁ - arctg p₂ - arctg p₃ + arctg p₄)

H = c . M . (ln (s₃ / s₄) - ln (s₁ / s₂))

kde c = 10^-7 [H.m^-1], jsou-li hodnoty uváděny v příslušných jednotkách: x [m], b [m], M [A.m^-1], h_i [m].

Parametry a proměnné úlohy

Ze shora uvedených vztahů je zřejmé, že parametry úlohy jsou:

• h₁ a h₂ vymezující polohu svrchní a spodní vrstevní plochy vzhledem k povrchu

• mocnost resp. její polovina b

• vlastní magnetizace M tělesa

Proměnnou v úloze je poloha x bodu P srovnávací roviny.

Zkoumané vztahy a abstrakce od modelového tématu

Úloha magnetometrie tedy vyšetřuje vztahy tvaru

Z = Z (x; {h₁, h₂, b, M})

H = H (x; {h₁, h₂, b, M})

Je zřejmé, že - abstrahujeme-li od konkrétní problematiky magnetometrie - jde o vyšetřování obecného funkčního vztahu

y = f ( x; { p_i })

kde p_i jsou parametry funkčního vztahu, x je nezávisle proměnná a y závisle proměnná.

Parametry a proměnné v tabulkovém procesoru

Proměnné ve funkčním vztahu

Vysvětleme věc na úplně nejjedošší parabole. Závisle a nezávisle proměnná tvoří klasickou dvojici hodnot, pro kterou byl vlastně tabulkový procesor původně stvořen. Konkrétní hodnota x na jedné straně vstupuje adresou buňky, ve které je umístěna, do výrazu vypočítávajícího hodnotu závisle proměnné y na straně druhé. Nejčastěji pak tyto dvojice tvoří tabulku typu té, která je na obr. 2.
 

Obr. 2: x a y ve funkčním vztahu

V tabulce i samotné hodnoty nezávisle proměnné x mohou být vypočítávány. Pro modelování je však nevýhodné, že tabulkové procesory sice velmi dynamicky pracují se vzorci, ale neumožňují stejně dynamicky pracovat s množstvím dat - např. počet hodnot x v předchozím obrázku (tedy 5) nelze měnit jen změnou obsahu nějaké jiné buňky; musí se použít buď "ruční" změna nebo programování.

Parametry ve funkčním vztahu

Hodnota 3 ve vztahu y = 3 . x² je parametrem. Na jeho hodnotě záleží, jak bude funkce "vypadat". Parametr konkrétní funkce má při řešení konkrétního problému konkrétní význam - např. parametr a ve funkčním vztahu y = a . x² může být polovinou tíhového zrychlení, je-li konkrétním problémem volný pád a konkrétní funkcí dráha v závislosti na čase. Je zřejmé, že právě hodnoty parametrů funkcí popisujících nějaký jev jsou častým cílem vědeckého bádání.

Parametrizovat funkční vztah v tabulkovém procesoru (přesněji ve vzorcích v něm) je jednoduché a logické: do vzorce se zadá nikoliv hodnota parametru, ale adresa buňky, ve které se tato hodnota nachází. Je-li však vzorec použit pro výpočet funkční hodnoty opakovaně (což při tabelaci funkce je samozřejmé), je zapotřebí při kopírování vzorce zabránit případné automatické úpravě adresy buňky s parametrem. K tomu slouží známé absolutní adresování (Excel k němu používá znak dolar - $).

Pro mnohé uživatele je však absolutní adresace něčím naprosto tajuplným, nehledě k tomu, že vzorce složené jen z adres jsou nepřehledné. Proto lze jen doporučit používání mechanismu pojmenování buněk. Nejjednodušeji přes pole názvů se vybrané buňce přidělí jméno, název, kterým je identifikátor a který vhodně volenou mnemonikou nebo shodou s matematickým zápisem funkce jistě lépe informuje o tvaru funkce a zpřehledňuje její zápis. Jméno, název buňky pak navíc reprezentuje právě absolutní adresu.

Použití (navíc pojmenovaného) parametru v parametrizovaném funkčním vztahu ukazuje obrázek 3.
 

Obr. 3: x a y v parametrizovaném funkčním vztahu

Dynamický model v Excelu

Už zavedením samostatného parametru podle předchozího odstavce bylo docíleno jistého stadia dynamického modelu. Změnou hodnoty jediné buňky dojde k dynamické změně hodnot v mnoha dalších, modeluje se tedy chování funkce pro různé hodnoty parametru. Je-li navíc vytvořen graf, který zobrazuje funkční hodnoty, pak změnou jediné hodnoty parametru se změní tabulka funkčních hodnot a naopak změnou tabulky funkčních hodnot se změní i graf.

Má to však jednu vadu: hodnotu parametru musí uživatel vždy znovu "ručně" vepsat do příslušné buňky. Teprve po zadání nové hodnoty se graf překreslí, aktualizuje. A takové "poskakování" grafu má daleko k tomu, co si lze pod pojmem dynamický model představit. Otázka tedy zní: nelze pro účely dynamického modelování použít nějaký nástroj, který plynule mění hodnoty konkrétní buňky? Odpověď zní - jak jinak - existuje! Alespoň v Excelu lze totiž použít jednak všechny ovládací prvky registrované ve Windowsech, jednak tzv. formulářové ovládací prvky..

Zřejmě nejjednodušší je použití prvku známého jako scroll bar (česky překládáno nejčastěji jako posuvník). Jeho jezdcem uživatel pohybuje od jedné krajní polohy do druhé. Krajním hodnotám odpovídají číselná datová pole posuvníku min a max. Datové pole value posuvníku je nositelem číselné hodnoty odvozené z relativní polohy jezdce mezi oběma krajními polohami. A právě datové pole value lze svázat s některou buňkou sešitu Excelu: změnou polohy jezdce se změní hodnota buňky a změnou hodnoty buňky se změní poloha jezdce.
 

Obr. 4: Parametr svázaný s posuvníkem v poloze Min				Obr. 6: Dynamické modelování průběhu funkce
Obr. 5: Parametr svázaný s posuvníkem v poloze Max

Na obrázcích 4 až 6 je uvedený mechanismus demonstrován. Datové pole posuvníku je svázáno s buňkou D2, která je pojmenována a a je parametrem funkce tabelované vzorci ve sloupci y (viz obr. 3). Datové pole Min posuvníku je nastaveno na 1, tedy při dotažení jezdce do levé krajní polohy nabude datové pole Value hodnoty 1 a tedy i buňka D2=a nabude hodnoty 1. Datové pole Max posuvníku je nastaveno na 10, tedy při dotažení jezdce do pravé krajní polohy nabude datové pole Value hodnoty 10 a tedy i buňka D2=a nabude hodnoty 10. Protože však při tažení jezdce z jedné do druhé krajní polohy dochází průběžně k události Scroll a při ní posuvník mění hodnotu datového pole Value svázaného s buňkou D2=a, mění se průběžně při tažení jezdce i tabulka funkčních hodnot a následně i graf, jak to předvádí obrázek 6.

Modelování magnetického pole

Postup nastíněný v předchozí kapitole nyní uveďme detailně na přímé úloze magnetometrie, jejíž teoretická část je uvedena v úvodu.

Parametry úlohy

Metodicky je vhodné umístit parametry úlohy s doprovodným textem do záhlaví listu. Prakticky je vhodné pojmenovat je ještě předtím, než se zapíše první vzorec, který je používá - pak totiž při konstrukci vzorce "ukazováním" se do vzorce vloží už jméno buňky, nikoliv její adresa. Postup pojmenování buňky je asi nejjednodušší provádět podle Přílohy 1.

Jak plyne z teoretické části, parametry jsou především hodnoty určující geometrii úlohy, tedy hloubky svrchní a spodní plochy a mocnost desky. Dále je parametrem vlastní magnetizace desky. Pro pohodlný zápis vzorců parametry pojmenujme následujícími identifikátory:
 

Fragment listu s parametry může po příslušném formátování vypadat např. takto:
 

Obr. 7: Parametry úlohy

Dynamická změna parametrů

Pro dynamickou změnu parametrů použijme shora popsaného posuvníku (přesněji: pro čtyři parametry čtyři posuvníky). V kontextu modelové úlohy a jí podobných je metodicky vhodné umístit posuvník poblíž buňky, jejíž obsah má měnit (s níž má být svázán) - viz však diskusi níže. Podle grafického uspořádání údajů na listu je možno zvolit posuvník svislý nebo vodorovný; v našem případě čtyř pod sebou umístěných údajů zvolíme čtyři vodorovné posuvníky.

Vložení a nastavení posuvníku je podrobně popsáno v Příloze 2. Na tomto místě především ukažme možnou grafickou úpravu části listu s posuvníky:
 

Obr. 8: Parametry nastavované posuvníky

Dále však diskutujme situaci, která nastala u magnetizace a která plyne v případě použití tabulkového procesoru Excel z tohoto faktu (mírná chybka na straně fy. Microsoft):

Datové pole Value, ve kterém si posuvník uchovává hodnotu z intervalu <Min, Max> odpovídající poloze jezdce, je typu "Celé číslo se znaménkem na 2 bytech". Při konverzi této hodnoty do svázané buňky (LinkedCell) však někdo něco opomenul a výsledek konverze je typu "Celé číslo bez znaménka na 2 bytech". Důsledek je toto: Nastavením Min na zápornou hodnotu se poloha jezdce odpovídající hodnotě -1 převede do svázané buňky jako 65535, -2 jako 65534 atd. Datové pole Value má přitom nastavenou správnou hodnotu.

Posuvník je tedy v Excelu schopen do svázané buňky dodávat reálně použitelné celočíselné hodnoty nanejvýš z intervalu < 0, 32 767 >. Obecně rozpětí <Min,Max> řídí počet hodnot (= Max - Min + 1), které je posuvník schopen dodat - jak "hrubě" nebo "jemně" lze použít.

Je však zřejmé, že ve vědeckých aplikacích je zapotřebí získávat neceločíselné hodnoty z libovolného intervalu <A,B>. Označme Min a Max mezní hodnoty ve stejně pojmenovaných datových polích posuvníku, označme A a B meze intervalu, který je požadován, označme V hodnotu dodanou posuvníkem. Je

V Î <Min, Max>

V - Min Î <0, Max - Min>

(V - Min) / (Max - Min)Î <0, 1>

(V - Min) / (Max - Min) * (B - A) Î <0, B - A>

A + (V - Min) / (Max - Min) * (B - A) Î <A, B>

Označíme-li P výraz

P = A + (V - Min) / (Max - Min) * (B - A)

je pak pro hodnotu V z intervalu <Min,Max> hodnota P právě z intervalu <A,B>.

Tedy zpět k magnetizaci. Tu modelujme např. z intervalu <10^-2, 1>. Nelze to posuvníkem přímo, proto použijeme předchozího vztahu. Zvolme modelování magnetizace např. 101 různými hodnotami. Tomu odpovídá interval posuvníku např. <Min=0, Max=100>. Požadovaný interval magnetizace je <A=10^-2, B=1>. Je tedy podle předchozího vztahu

P = 10^-2 + (V - 0) / (100 - 0) * (1 - 10^-2)
P = 0.01 + 0.0099 * V
 

Umístíme-li tedy magnetizaci do buňky F13 (viz shora), musí v ní být vzorec

= 0.01 + 0.0099 * Xi

kde teprve Xi je adresa buňky (LinkedCell) svázané přímo s posuvníkem. Ta může být kdekoliv, slouží jako mezičlánek pro nastavení parametru magnetizace. Pro řešení vlastního problému je irelevantní a vůbec není zapotřebí, aby ji uživatel viděl. Nejjednodušší tedy je, umístí-li se pod posuvník - např. Xi = G13 nebo H13 ... až L13 (viz obr. 8).

Proměnné v úloze

Nezávisle proměnnou (x) je vzdálenost místa P od svislé osy desky. Závisle proměnnou je vodorovná (H) resp. svislá (Z) složka magnetického pole desky v místě P (viz obr. 1). Zkoumáme-li závislost Z=Z(x) resp. H=H(x), volíme x z nějakého vhodného intervalu - a protože jde o počítačové modelování, s nějakým vhodným krokem resp. vhodným počtem rozdělení intervalu. V tom případě jde o běžnou tabelaci, která je jedním ze základních nástrojů tabulkových procesorů. Příklad na obr. 9 ukazuje tabelaci v intervalu <-80, 80> s dělením na 51 kroků (po 3.2 metru).
 

Obr. 9: Tabelace funkcí Z=Z(x) a H=H(x) s pomocnými mezivýpočty

Parametry úlohy se vyskytují jednak ve vzorcích pro výpočet p_i a s_i, jednak v samotných Z a H. Parametry jsou dynamicky měněny posuvníky (viz shora), tedy i funkční hodnoty Z a H se dynamicky mění při změně vyvolané posuvníkem. Jsou-li navíc Z a H řadami v XY grafu, mění se dynamicky i čára grafu. Obrázek 10 ukazuje možný graf včetně znázornění průřezu desky (i ten lze dynamicky měnit).
 

Obr. 10: Graf modelu magnetického pole desky

Praktická realizace

Zájemci mohou zde získat sešit Excelu s praktickou realizací dynamického modelu magnetického pole. Sešit je nejlépe stáhnout na lokální počítač zájemce a otevírat až tam.

Závěr

Článek se pokusil ukázat jednoduché vytvoření dynamického modelu běžnými prostředky tabulkového procesoru (zde Excelu). Nebylo použito programování ani vlastní funkce. Výsledný model je pro výuku i pro vlastní odbornou práci názorný, efektní, variabilní. Obdobného principu lze použít i pro jiné úlohy obdobného charakteru.

Použití vlastních funkcí by výsledné řešení velmi zobecnilo. Tvorba analogických úloh by pak byla daleko jednodušší. Použití programování by pak mohlo úlohu např. více "zefektnit": graf by se mohl "hýbat sám" apod.

V obou případech by však takový sešit podléhal nastavení zabezpečení tabulkového procesoru uživatele. Z nich vysoké zabezpečení vůbec nepovoluje používání programových jednotek, a to bez upozornění. Běžný uživatel by pak mohl být zmaten, protože deklarovaná dynamika by se nekonala. I při středním zabezpečení (tj. povolení použití programových jednotek uživatelem na základě dotazu) je zde vysoká pravděpodobnost, že na výhružný dotaz na povolení tzv. maker se běžný uživatel zalekne, odpoví Nepoužívat a výsledek je stejný jako v předchozím případě.

Literatura

[1] Mareš, S. a kol.: Úvod do užité geofyziky. Praha, SNTL Alfa, 1979.

Přílohy

Příloha 1: Pojmenování buňky

Zřejmě nejrychlejší postup při pojmenování buňky ukažme na příkladu, kdy buňku D2 pojmenujeme (=přidělíme název) hloubka1. Ukázka zobrazuje sice prostředí z Office 2003, v nojvějších verzích se však pracuje s totožnou lokalitou a funkčně se neliší. Ve třech krocích se pak musí:
 

1. Pojmenovávanou buňku učinit aktivní, vybranou buňkou;  v poli názvů musí být její adresa - zde D2:

2. Kliknout do textu názvu - zde D2 - v poli názvů (nikoliv na jeho rozvíjecí tlačítko!); text se označí, zarovná na levý okraj a za ním bude blikat textový kurzor - neklamné znamení, že je připraven k editaci:

3. Přepsat původní označenou adresu (zde D2) požadovaným pojmenováním buňky (zde hloubka1) a stisknout klávesu ENTER (!):

Příloha 2: Vložení a nastavení posuvníku

Posuvník jako každý ovládací prvek je dostupný ve dvou režimech: režim návrhu (ve kterém se vytváří i upravuje) a režim použití.

Režim návrhu se do verze Office 2003 zapíná a vypíná z panelu nástrojů pro ovládací prvky. Tento panel je dostupný z hlavního menu Zobrazit, položka Ovládací prvky podle následujícího schematu:
 

	Menu Zobrazit / Ovládací prvky
	Panel Ovládací prvky

Tlačítko se symbolem trojúhelníku (v prvním řádku panelu nástrojů vlevo) přepíná režimy návrhu (stisknuto) a použití (uvolněno). Dále je v panelu nástrojů označen kroužkem prvek Posuvník (anglicky Scroll Bar). Stisknutím a opsáním obdélníku kdekoliv v listě dojde k vložení posuvníku opsané velikosti do listu. Posuvník zůstane v režimu návrhu, označen bílými čtvercovými úchytkami pro změnu velikosti:
 

Dalším krokem je nastavení vlastností posuvníku. Tlačítko se symbolem ruky držící list papíru (v prvním řádku panelu nástrojů vpravo) zobrazuje nebo skrývá formulář s vlastnostmi (anglicky Properties) aktivního objektu. Pro právě vložený posuvník je formulář vlastností tohoto tvaru:
 

Vlastnosti = datová pole - stojící za pozornost pro posuvník - jsou označena zakroužkováním. Jde o tato datová pole, které je třeba nastavit podle konkrétní aplikace:

• 1. Min a Max: Nastaví se hodnota, kterou má nabýt datové pole Value pro obě krajní polohy jezdce.

• 2. Linked Cell: Nastaví se adresa buňky (např. tvarem D2 nebo jménem hloubka1), která má být svázána s datovým pole Value.

Ve verzi Office 2007 a vyšší je přístup k výběru ovládacího prvku zajištěn z hlavního menu VÝVOJÁŘ, karta Ovládací prvky (viz následující schema). Ve zbytku je postup stejný jako v předchozím textu s tím, že tlačítko vypínání a zapínání režimu návrhu je rovněž na kartě Ovládací prvky spolu s tlačítkem Vlastnosti (dříve Properties, ovšem otevřená formulář vlastností je stejně jako dříve v angličtině).
 

Rev. 6  / 2016