v ch tabulek. Z tohoto pohledu se zd b t jednoduch z m na na prvn pohled bezpe n m zp sobem utajen obsahu zpr vy. Realita je ov em jin. Na skute nost,

INFORMATIKA Po ta ov bezpe nost ve vuce informatiky

(3. st { lutn jednoduch zmny, frekvenn analza)

;

MICHAL MUS LEK { TPN HUBLOVSK P rodovdeck fakulta Univerzity Hradec Krlov

V minul ch lncch naeho serilu 7], 8] jsme se nauili ifrovat otev en text na ifrov a naopak deifrovat ifrov text zpt na otev en . Deifrovat znamen p evdt ifrov text na otev en se znalost kryptograck metody a pouitho kle. Pokud se jedn o jednoduchou zmnu, znamen to, e znme p slunou p evodovou tabulku. Zcela jinou innost je lutn, kdy ifrov text na otev en nep evd p jemce, ktermu byla zprva urena, ale t et osoba, kter chce odhalit tajemstv ifrovho textu, urit pouitou kryptograckou metodu a kl. Pokud je tento lovk vybaven pot ebn mi znalostmi, oznaujeme ho jako kryptoanalytika. Ve 2. sti naeho serilu jsme odvodili, e vech mon ch jednoduch ch zmn v rmci znak mezinrodn abecedy (26 psmen) je 26! = 49 1021 . Pokud vezmeme jen zmny, kter jsou inverzn samy k sob, bude jich mnohokrt mn, ale i tak zbude 7 9 1012 (7,9 bilin) rzn ch p evodo554

Matematika - fyzika - informatika 20 2010/2011

v ch tabulek. Z tohoto pohledu se zd b t jednoduch zmna na prvn pohled bezpen m zpsobem utajen obsahu zprvy. Realita je ovem jin. Na skutenost, e jednoduchou zmnu lze pomrn snadno lutit, p ili jako prvn patrn arabt uenci v 9. stolet naeho letopotu. Simon Singh ve sv knize 5] odkazuje na p ekvapiv objev, k nmu dolo v Slajmanov osmanskm archivu v Istambulu v roce 1987. Byl zde nalezen Rukopis o de ifrovn kryptograckch zprv z pera al-Kindho, arabskho losofa, lka e, astronoma, matematika, hudebnka a jazykovdce. Al-Kind ve dvou strun ch odstavcch svho rukopisu popsal princip frekvenn anal zy, kter je jednm z nejdleitjch nstroj klasick kryptoanal zy. Pomoc n nejprve rozhodneme, zda je ifrov text vbec zaifrovn pomoc jednoduch zmny, nebo jinou kryptograckou metodou. V p pad, e se jedn o jednoduchou zmnu, urme pomoc frekvenn anal zy, bu p esn, nebo alespo p iblin s nutnost !doladn" pouitm jin ch metod, p evodovou tabulku pouitou u dan ifrov zprvy. Je pravdpodobn, e ke vzniku frekvenn anal zy p isplo studium kornu, kter bylo tak dkladn, e zkoumalo nejen etnost v skytu jednotliv ch slov v s#rch, ale dokonce etnost jednotliv ch psmen. V arabtin se velmi asto vyskytuj psmena a a l, jist i proto, e spolu tvo urit len al-, zatmco etnost psmene j je asi desetkrt ni. Frekvence, neboli etnost v skytu jednotliv ch psmen abecedy je ovem v kadm jazyce jin. Frekvenn anal zou ifrovho textu, jemu odpovd otev en text v anglitin se zab vaj Simon Singh 5] a Vlastimil Klma 2]. Komplikovan by pro ns byla anal za jazyk, kter pouvaj jinou sadu znak, nap . rutina s azbukou. Ale ani jazyky, kter pouvaj latinku to nemaj #pln jednoduch. Nap . pro italtinu jsou typick zdvojen souhlsky, zdvojuj se ale jen nkter z nich, take p i prostm pouit p evodov tabulky by je kryptoanalytik snadno odhalil (vidl by v ifrovm textu dvojit znaky a k nim by p i azoval p slun souhlsky). Proto se p ed vlastnm ifrovnm italsk otev en text zpravidla uprav tak, e se vechny zdvojen souhlsky nahrad jednoduch mi a pak se teprve provede ifrovn. V eskm jazyce je zase zvykem odstranit vekerou diakritiku (rky a hky) a teprve potom ifrovat. Psmeno ch, kter je pozstatkem sp ekovho pravopisu, se zpravidla ifruje jako dv samostatn psmena c a h. Tak se z otev enho eskho textu stane text bez diakritiky, kter pouv 26 psmen mezinrodn abecedy, stejn jako anglitina. Jak je to s frekvenc jednotliv ch hlsek v eskm textu zjistme, kdy vezmeme njak Matematika - fyzika - informatika 20 2010/2011

555

del text a spotme etnost jednotliv ch psmen. Diakritiku si p itom tak odmyslme. Tak nap klad prvn lnek naeho serilu sestval celkem z 11 444 psmen. Relativn etnost jednotliv ch psmen, vyjd en v procentech byla:

A 9,0 N 6,3

B 1,5

O 8,3

C 3,6 P 3,8

D 3,1 Q 0,0

E F G H I J K L M 10,4 0,6 0,6 2,1 7,5 2,2 3,6 3,3 3,8 R 4,8

S 5,4

T 5,6

U 3,9

V 4,0

W 0,1

X 0,3

Y 2,8

Z 3,3

Mnohem dkladnj a p esnj je statistika frekvenc jednotliv ch psmen zskan anal zou text sestvajcch z 5 396 882 psmen Centrem zpracovn p irozenho jazyka Fakulty informatiky Masarykovy univerzity v Brn 3]:

A 8,8 N 6,7

B 1,7

O 8,3

C 3,6 P 3,5

D 3,6 Q 0,0

E F G H I J K L M 10,5 0,4 0,3 2,3 7,7 2,0 3,8 4,1 3,3 R 5,2

S 5,4

T 5,6

U 3,8

V 4,4

W 0,1

X 0,1

Y 2,7

Z 3,1

Porovnnm obou tabulek frekvenc vidme, e p esn hodnoty relativn etnost psmen se sice trochu li, ale po ad nejetnjch psmen zstv stejn. V obou p padech jsou pti nejastji pouit mi psmeny E, A, O, I a N. Frekvenn anal za nm pome v lutn tm vc, m je ifrov text del a m vc je ist esk (bez p msi cizch slov). Jak postup lutn meme doporuit? Provete frekvenn anal zu textu. Pokud mte text k dispozici v elektronick podob, vyuijete s v hodou pomcku na webu 4]. Psmena s nejvy etnost jsou pravdpodobn E, A a O tedy samohlsky. Dal dv pravdpodobn budou I, N a s urit m odstupem by pak mla nsledovat dal psmena. 'm je text krat, tm je pravdpodobnj, e dojde k zmnm po ad psmen oproti v e uvedenmu. Proto si pomhme dalmi jazykov mi znalostmi. Souhlsky a samohlsky se v eskm textu pomrn 556


pravideln st daj. Skupina nkolika po sob jdoucch samohlsek je p itom mnohem vzcnj ne skupina po sob jdoucch souhlsek. Krom frekvence jednotliv ch psmen se meme zab vat frekvenc dvojic nebo trojic po sob jdoucch psmen, kter m kme bigramy, resp. trigramy. Pokud budeme zkoumat vechny bigramy, je v eskm textu nejetnjch 3] tchto patnct: ST, NI, PO, OV, CH, RO, EN, NA, JE, PR, TE, LE, KO, NE, OD. Jinou monost je sledovat pouze souhlskov bigramy ve chvli, kdy se nm poda odliit souhlsky od samohlsek. Pavel Vondruka 6] jich uvd est: ST, PR, CH, SK, DN a TR. Navc ovem uvd tak typick vlastnosti (zvltnosti) pro t i nejastj: ST

Psmena S a T maj p iblin stejnou frekvenci. Existuje i opan bigram TS. Je soust mnostv souhlskovch trigram (STR, STN, STL, STV,

PR

:::

)

Psmeno P m p iblin polovin frekvenci ne psmeno R. Obrcen bigram RP se tm nevyskytuje (vjimka: chrpa). Vtinou jej nelze roz it na souhlskov trigram doprava (vjimka: PRV), ale asto jej lze roz it smrem vlevo (SPR, ZPR, ) :::

CH

Psmeno H m jen o nco men frekvenci ne psmeno C. asto je na konci slov spolu s Y, I, A, E (YCH, ICH, ACH, ECH). Vtinou plat: je-li p ed CH souhlska, nsleduje po CH samohlska a naopak (OBCHOD, NECHTL, ) :::

Nejastjmi trigramy jsou PRO, OVA, OST, STA, PRI, PRE, TER, ENI, POD, KTE, PRA, EHO, STI, RED, KON. Nejetnjm souhlskov m trigramem je STR. Krom frekvence psmen a zkoumn bigram a trigram nm p i lutn me pomoci odhad nkter ch slov otev enho textu, zejmna kdy od zatku tume, nebo bhem lutn zaneme tuit, jakmu tmatu se ifrov text vnuje. Pokud bychom p i frekvenn anal ze zjistili, e nejen v otev enm, ale i v ifrovm textu je nejetnjm psmenem E, dalmi A, I, O atd. Matematika - fyzika - informatika 20 2010/2011

557

a v textu se podez ele asto vyskytuj i skupiny po sob jdoucch souhlsek, pak se vbec nejedn o substitun ifru, ale o zaifrovn vznikl zmnou po ad psmen tvo cch zprvu, tedy o tzv. transpozici. Kdyby vechny znaky ifrovho textu mly podobnou frekvenci a nevyskytla by se v razn maxima a minima, jednalo by se nejsp o polyalfabetickou ifru. V tchto p padech bychom museli zvolit jin postupy lutn ne v e uveden. Nakonec si ukame cel postup lutn ifrovho textu zaifrovanho pomoc jednoduch zmny na p kladu. Mme rozlutit ifrov text: HCNON SATBH YTRWL TWSAT ZVHGW VSATC HTRGA ZNENR LCANI SHTRN ESNSA ASOEX GENRS

WTBTO MZXKM COMXS INYLZ ZGJLH OXNEA NXKSO OTATX ALZVG SJSVN SXSZV KSESJ HTVSE

XKNHA OXTYM YSCMI VOHMC NHTEO TVSIL XTZVR KMISO ITYZG ZGBMN SXMES LATJL NYIGW

TRWLC RCGOY TSENC TYGGX MASGW OYMIO WLCOT NICGO VATWX CGOYT ANXKS YSXKM GXSWY

OTATB TZVET RGBNZ SYMEM SOEXS MXKSE ANWZV YTZVE SESIH ZVEGB WGREM YMOAN TBTVS

HTEOM AMYMW MBOSG ZRLZV ZVLJA SWSZT ATXKS GBTES ONRMJ MATHL HAMNK IMOET MAETY

OLIXN OEHNN ESHYN JNWSR TCWNH YNHNC ATBNR VYTOT NWNET OYTWR TZTXE ZVEME TREGN

EMTWS YMWOR OEATY SAZTG LRSAN ATCTA SGGOY HXKHA YGBTO LJNWN MHAMW XSOGC YAMZV

OENHN TVSCM MWTGX ANJSC YMHMZ TBHMZ TZVEM MKNWT XKNHA AMNYT GZVSH SHNEV XYSWG

ETIRW HSENN NWATI TAORL WSJKT BTZYS YSGHT ELRNH TCTZY OANVN AMZMA SWASE

Frekvenn anal zou zjistme, e zprva m 645 znak. Z nich se nejastji vyskytuje znak T, kter je ve zprv 67krt, tedy m relativn etnost 10,4 %. Dalmi v po ad jsou znaky S, N, M a A s relativnmi etnostmi 8,8 %, 8,1 %, 6,5 % a 6,5 %. Zkusme tyto znaky nahradit podle d ve uveden ch tabulek po ad psmeny E, A, O, I a N a zb vajc znaky zatm nahradme pomlkou. Vidme, e st dn samohlsek se souhlskami odpovd, take zkusme vytipovat dal znaky pro samohlsky (U a Y) a ozname je hvzdikou a k kem. --O-O ANE--E--+ E-ANE ---*-ANE-

558

-E-EI---I --I-A -O-+-*-+--O-N

--O-N --E-I -A-I---IO-E-E-A-+

E--+--*-EA-OE-**INA*--I--

-ENE--E--I -+--O -IE-A --O-O -E--E---E NI-I- ---OO -I--- E-A-I -A-OO -*-O- I--A* -A--O --NE- I-E*- O-NEA-I-I --+-- -O-A- AN-E* NO-A- EN--+ A---A --+-N E--O- +-ANO -I-I- -A--E I--A- A-A-E -O-O- NE-EN E--I- -E--A


-E-*N -O-O+-NOA-E-O -AOAN NA--*-O-A

O--A-ENEN+--* A-A-O A-A--A-A-E-A-

-E---I-A-E--* -*-IO A-I-A +NE-+ O--*-

-+--E O--*-NE--*--E NO--A -A--I *-A--

NO---E--A-A----*-*--I -I-NO E-E-A

NE--A *-E-A -O-IINE-+ -NIO-I--E IN-E-

NE-O--E-E O-O-E --E-E-E----IE--*O

A**-----N -*-E+-O-O I-NI-A-*-NI--

E---I I-O-E --O-N NIO-E *--AA-O---A-*

-A*-E -+-OE-E--NO-O NI-IN A-NA-

Nyn se p eorientujeme ze samohlsek na souhlsky, p esnji eeno na souhlskov bigramy. Protoe ns nyn nezajmaj samohlsky, nahradme je v zaifrovanm textu tekami a budeme si vmat skupin nkolika po sob jdoucch souhlsek. HC.O. .A.BH Y.RW. .W.A. ZVH.W V.A.C H.R.A Z.E.R .CA.I .H.R. E...A A.OEX .E.R.

W.B.O .ZXK. CO.X. I.Y.Z Z.J.H OX.EA .XK.O O.A.X A.ZV. .J.V. .X.ZV K.E.J H.V.E

XK.HA OX.Y. Y.C.I VOH.C .H.EO .V.I. X.ZVR K.I.O I.YZ. Z.B.. .X.E. .A.J. .YI.W

.RW.C RC.OY ..E.C .Y..X .A..W OY.IO W.CO. .IC.O VA.WX C.OY. A.XK. Y.XK. .X.WY

O.A.B .ZVE. R.B.Z .Y.E. .OEX. .XK.E A.WZV Y.ZVE .E.IH ZVE.B W.RE. Y.OA. .B.V.

H.EO. A.Y.W .BO.. ZR.ZV ZV.JA .W.Z. A.XK. .B.E. O.R.J ...H. HA..K I.OE. .AE.Y

O.IX. OEH.. E.HY. J.W.R .CW.H Y.H.C A.B.R VY.O. .W.E. OY.WR .Z.XE ZVE.E .RE..

E..W. Y.WOR OEA.Y .AZ.. .R.A. A.C.A ...OY HXKHA Y.B.O .J.W. .HA.W X.O.C YA.ZV

OE.H. .V.C. .W..X A.J.C Y.H.Z .BH.Z .ZVE. .K.W. XK.HA A..Y. .ZV.H .H.EV XY.W.

E.IRW H.E.. .WA.I .AOR. W.JK. B.ZY. Y..H. E.R.H .C.ZY OA.V. A.Z.A .WA.E

Projdeme-li uveden text, nap . pomoc funkce !Najt" textovho editoru (klvesov zkratka Ctrl + F), zjistme e obsahuje bigram ZV 15krt, p iem frekvence znaku Z 4,7 % je o nco vt ne frekvence znaku V 3,9 %. Tento bigram je navc zpravidla obklopen z jedn strany samohlskou a z druh strany souhlskou. Proto usuzujeme, e jde v otev enm textu o CH. Druh m nejetnjm bigramem je XK, kter je zde 11krt, p itom text neobsahuje opan bigram KX. Proto usuzujeme, e jde v otev enm textu o bigram PR. T etm v po ad je bigram OE, kter najdeme 6x, ale tak je zde 2x opan bigram EO. Oba znaky maj zhruba stejnou frekvenci. Proto usuzujeme, e jde v otev enm textu o bigram ST. Dalm bigramem je HA, o kterm j vme, e znak A p edstavuje v otev eMatematika - fyzika - informatika 20 2010/2011

559

nm textu N, a protoe se mezi esti nejastjmi esk mi souhlskov mi bigramy vyskytuje DN a frekvence znak tomu tak odpovdaj, usuzujeme, e se jedn prv o DN. Nyn u znme st p evodov tabulky: A B C E G H I J K L M N O R S T V W X Y Z N { { T { D { { R { I O S { A E H { P { C

Prvn dek tabulky neobsahuje znak D, F, P, Q a U, protoe se v ifrovm textu nevyskytuj. Ve druhm dku jsou odpovdajc psmena otev enho textu, kter znme. Zatm neznm jsou nahrazena pomlkou. Po proveden tto zmny zskme jet stle obtn iteln text, ze kterho vystupuje na zatku sedmho dku skupina DE{N OPRAS PECH, co by mohlo znamenat NA PROSP+CH, a na zatku jedenctho dku je skupina TAOAN APACH API, co by mohlo znamenat TO A ONO POCHOP,. Oba p evody ovem vyaduj vzjemnou v mnu psmen A a O ve druhm dku tabulky. To znamen, e v danm otev enm textu m psmeno O vy etnost ne psmeno A. Podobnou #vahou opravme jet jeden chybn odhad. Souhlskov bigram HA nenahrazuje v otev enm textu DN, n br VN. Po #pravch dostaneme p evodovou tabulku: A B C E G H I J K L M N O R S T V W X Y Z N { { T { V { { R { I A S { O E H { P { C

Kdy si tabulku pozorn prohldneme, vidme, e zobrazen, kter p edstavuje na mnoin psmen abecedy, je samo k sob inverzn. Tedy: kdy A nahrazuje N, pak N nahrazuje A, kdy msto E peme T, pak msto T peme E atd. Doplnme-li tabulku pouitm tohoto pravidla, budeme mt k dispozici dal informace: A B C E G H I J K L M N O P R S T V W X Y Z N J Z T { V M { R { I A S X K O E H D P { C

Pomoc tto posledn tabulky zskme sice jet ne#pln , ale ji pomrn dob e iteln p evod do otev enho textu: 560


VZASA ONE-V -EK-E-ONE CHV-HONEZ VEK-N CATAK -ZNAM OVEKA TOAON NOSTP -TAKO

-E-ES ICPRI ZSIPO MA--C C---V SPATN APROS SENEP N-CHO-OHA OPOCH ROTOVEHOT

PRAVN SPE-I -OZIM HSVIZ AVETS EHOMPECHK RIMOS ME-CC--IA OPITO -NE-A-M--

EK--Z KZ-SEOTAZ E---P INO-S-IMS --ZSE AMZ-S HNE-P Z-S-E NAPRO -OPRI -PO--

SENEECHTE K--AC O-ITI OSTPO IPROT NA-CH -ECHT OTOMV CHT---KTI -ISNA E-EHO

VETSI NI-II-SOCK-CH CH--N O-OCE NEPRO --ETO SAKIINEVVNIAR MISTE INTE-

S-MPA STVAA TOV-A -A-OK EZ-AV -AVAZ NE-AK H-ESE A-ATE S-E-K ECEPT CHTIT EKT-A

TIE-O -I-SK STNEONCE-KONA NEZEN O--SVPRVN ---ES --A-A IVNIPOS-Z -NICH

STAVA EHOZI I-E-P NA-OZ -IVIC E-VIC ECHTI IRA-E PRAVN NIA-E -CHOV OVATH P-O--

TEMKVOTAA A-NEM ENSK-O-RE -EC-O -O-VE T-KAV EZECSNAHA NICIN O-NOT

Nyn zb v odhadnout zb vajc psmena otev enho textu, doplnit p evodovou tabulku, dokonit p evod celho ifrovho textu na otev en , zruit lenn do ptimstn ch skupin a nahradit jej lennm do slov a doplnit diakritiku (rky a hky). Tyto posledn kroky ji nejsou nijak obtn, proto ponechvme ten i, aby sm dokonil lutn danho ifrovho textu. Odmnou mu budou moudr slova Alberta Einsteina 1]. Literatura 1] Einstein, A.: Jak vidm svt. Praha: Nakladatelstv Lidov noviny, s. r. o. 1993. 2] Klma V.: Utajen komunikace { 2. dl. CHIP: magazn informanch technologi, erven 1994, ro. 4, . 6. 3] Kolektiv: Frekvence psmen, bigram, trigram, dlka slov online]. Centrum zpracovn p irozenho jazyka Fakulty informatiky Masarykovy univerzity, Brno. cit. 2010-05-05] Dostupn z: http://nlp. .muni.cz/cs/Frekvence pismen bigramu trigramu delka slov 4] Muslek M.: !ifry a k"dy online]. 2010 cit. 2010-05-05] Dostupn z: http://www.musilek.eu/michal/sifry.html?menu=mat 5] Singh S.: Kniha k"d a ifer. 2. vyd. Praha: Argo a Doko n, 2009. 6] Vondru ka P.: Kryptologie, ifrovn a tajn psma. 1. vyd. Praha: Albatros, 2006. Autorkou %vodn ilustrace je Mgr. Jaroslava ermkov. <

<


>

>

561

Strukturovan programovn ve VBA pro Excel TPN HUBLOVSK P rodovdeck fakulta Univerzity Hradec Krlov

Aplikace MS Excel lze s v hodou vyuvat jako nstroje, kter umouje propojit tradin metodu v uky s monostmi, kter nabzej informan technologie, viz tak nap . 1] a 2]. 'lnek odr zkuenosti z v uky programovn na st edn a vysok kole a zab v se monostmi vyuit MS Excel ve v uce algoritmizace a programovn na st edn kole.

MS O ce jako programovac prosted

Vechny MS O-ce produkty jako je Excel, Access, Word, PowerPoint, Outlook a dal, maj implementovn programovac jazyk Visual Basic for Application (VBA). Jedn se o objektov orientovan programovac jazyk, jeho zkladem je Visual Basic 3]. VBA neobsahuje takov mnostv objekt, jako vlastn Visual Basic, um vak vyuvat a manipulovat s objekty !sv" mate sk aplikace (bu kami, v bry bunk, listy atd. u Excelu, odstavci, slovnm a formtovnm atd. u Wordu apod.), co lze povaovat za jeho hlavn v hodu, p edevm ve v uce programovn. Kad uivatel MS O-ce sady m k dispozici programovac jazyk, kter si me dle libosti vyzkouet a vytvo it svoji !malou uitenou aplikaci", kter se me stt zkladem pro jeho dal zjem o programovn. Zklady algoritmizace zanme km a studentm vysvtlovat na tvorb algoritm pro een jednoduch ch matematick ch #loh. Vzhledem k tomu, e MS Excel je aplikace, kter sama o sob umo uje provdt matematick #kony, jedn se o tabulkov kalkultor, i z hlediska programovn proto povauji za nejvhodnj zat s integrovan m programovacm jazykem Visual Basic pro Excel. Jak uvidme v dalm textu, tento programovac jazyk, p estoe se jedn o objektov orientovan jazyk, umon zpracovat zkladn algoritmick #lohy bez toho, aby bylo nutn se ji v zatcch v uky zmi ovat o objektech, vlastnostech a metodch objekt a o udlostmi zenm programovn. Naopak meme se s v hodou p i v uce dret pln strukturovanho, sekvennho p stupu, co je zejmna vhodn v potench fzch v uky algoritmizace a programovn. 562


Vvojov prosted VBA D ve ne ukeme p kazy na konkrtnm p klad v jazyce VBA, je t eba si strun popsat v vojov prost ed. Jak ji bylo eeno, VBA je soust kad MS O-ce aplikace. V vojov prost ed je dostupn v Excelu 2007 z karty !V voj ". Tato karta nen standardn zobrazena. Kliknutm na Tlatko O-ce (kruhov ikona v levm hornm rohu aplikace Excel) a pot na tlatko Monosti aplikace Excel otev eme dialogov okno Monosti aplikace Excel. Zde zakrtneme polko !Zobrazit na psu karet V voj " (obr. 1).

; ; Obr. 1 Nastaven karty &Vvoj '

Aby bylo mon toto prost ed vyuvat, je t eba dle nastavit zabezpeen Maker na nejni #rove . V Excelu 2007 se toto nastaven provd na kart V voj { Zabezpeen Maker, kde v nastaven maker zakrtneme polko !Povolit vechna makra". V vojov prost ed lze otev t dvma zpsoby { bu na kart V voj klikneme na ikonu !Visual Basic" nebo pouijeme klvesovou zkratku Alt-F11. Otev e se nov okno. V lev sti jsou dv podokna { Project (zobrazuje seznam aktuln otev en ch projekt { seit, list, modul, pop . formul ) a Properties (zobrazuje jejich vlastnosti). V zatcch programovn meme !Properties Window" k kem zav t, pouv se hlavn p i tvorb vlastnch formul . Kd jazyka VBA se pe a je uloen do tzv. modul. Modul vlome pomoc nabdky !Insert { Module". Objev se bl plocha, na kterou ji peme svj kd jazyka VBA (obr. 2). Matematika - fyzika - informatika 20 2010/2011

563

;; Obr. 2 Vvojov prost ed

Kad program m zatek a konec. VBA umo uje vloit zatek a konec opt pomoc nabdky !Insert { Procedure". Do dialogovho okna zadme Nzev programu a stiskneme OK. Na bl ploe se objev Public Sub Nzev(), co zna zatek naeho programu a uzavrac dek End Sub jako konec programu. Nzev programu mus b t jednoznan , bez mezer. Na jedn pracovn ploe meme mt napsno vce program, p ehlednj je vak dal program pst do novho modulu.

Program zapsan ve VBA { rekurentn vpoet odmocniny V nsledujcm textu ukeme, jak VBA meme vhodn vyut pro v uku zklad algoritmizace a programovn. Dle vlastn zkuenosti doporuuji v uku programovn v poten fzi a v uku algoritmizace vzjemn prolnat, co znamen programovat dle zsad strukturovanho programovn, kdy algoritmus een #lohy zapsan pomoc v vojovho diagramu p episujeme pomoc p kaz programovacho jazyka. Na konkrtnm p kladu v potu druh odmocniny sla N (X = N ) pomoc rekurentnho vztahu ukeme zkladn programovac prvky a syntaxi jazyka VBA. Rekurentn vztah pro v poet odmocniny je odvozen na zklad Newtonovy metody seen pro een rovnice X 2 A = 0 nap klad v 4] a m tvar N 1 X +1 = 2 X + X p

;

i

i

i

kde X0 je poten odhad a X (i = 1 2 3 : : : ) jsou leny posloupnosti iterac, kter se postupn p ibliuj k v sledn hodnot. V poet bude i

564


ukonen, pokud doshneme uivatelem zadan procentuln p esnosti v potu E , co lze vyjd it podmnkou

X

j

i ;

X +1 < X E=100: i

j

i

Z hlediska algoritmizace to znamen, e rekurentn v poet se opakuje tak dlouho, dokud nen splnna v e uveden podmnka p esnosti. Pro ukonen v potu v algoritmu pouijeme cyklus s podmnkou na konci. V vojov diagram algoritmu je na obrzku 3 { viz nap . 5]. Algoritmus je sloen z p kaz vstupu, podmnnho p kazu, p kazu cyklu s podmnkou na konci a p kaz v stupu. (P epis v vojovho diagramu pomoc p kaz jazyka VBA a jejich specika probereme v dalm textu.)

;

Obr. 3 Vpoet odmocniny { vvojov diagram Public Sub Odmocnina() N = InputBox("Zadej N:") X = InputBox("Zadej Xo:", , 1) E = InputBox("Zadej E:", , 0.001) Cells(2, 1) = N If N > 0 Then I = 0


565

Do Xo = X X = (N / Xo + Xo) / 2 I = I + 1 Cells(I+1, 2) = Xo Loop Until (Abs(X - Xo) < X *(E /100)) V = X ElseIf N = 0 Then V = 0 Else V = "neex" End If Cells(I + 2, 2) = V MsgBox ("X = " & V) End Sub

Vstup dat Vstupn hodnoty N , X , E natme pomoc p kazu InputBox. P kazov dek v programu X = InputBox("Zadej Xo:", , 1) otev e jednoduch dialogov okno s hlenm, kter vyaduje zadn dan hodnoty promnn, a ek na okamitou akci uivatele (obr. 4). S v hodou meme pout jednoho z parametr InputBox (1 p ed posledn zvorkou), kter umo uje nastavit poten hodnotu promnn. Vstup hodnot lze realizovat i z jednotliv ch bunk Excelu p kazem X = Cells(R,S), kter do promnn X nate hodnotu z bu ky na dku R a ve sloupci S.

;

Obr. 4 Vstup hodnot pomoc p kazu InputBox

Podmnn pkaz Po p eten vstupnch hodnot nsleduje vcensobn podmnn p kaz If - Then - ElseIf - Else - End If, kter vtv program podle

566


hodnoty zadanho N . Ve vtvi pro N > 0 provdme rekurentn v poet odmocniny, ve vtvi pro N = 0 dochz s ohledem na zaokrouhlovac chyby po nkolika krocch k dlen 0, proto v sledek do promnn V = 0 p i adme okamit a pro N < 0 do v sledn promnn V vlome etzec V = !neex". V jazyce VBA jsou sloen p kazy, tedy i podmnn p kaz, ukoneny tzv. uzavracm dkem (End If), kter tyto p kazy ukon. (Poznmka: U jazyka Pascal sloen p kaz peme mezi begin end). Rekurentn vpoet odmocniny V p pad, e zadan hodnota N je vt ne 0, provede se rekurentn v poet odmocniny. Tento v poet realizujeme pomoc cyklu s podmnkou na konci. VBA umo uje cyklus s podmnkou na konci ukonit, pokud podmnka je splnna, ale tak pokud splnna nen, viz tab. 1. (Poznmka: Jazyk Pascal umo uje u cyklu s podmnkou na konci ukonit cyklus pouze tehdy, kdy podmnka je splnna.). V naem p pad se pouil cyklus typu until, kter kon, pokud podmnka splnna je, tedy pokud |X -Xo|<X*E/100, kde X zna nov spotenou a X0 p edelou hodnotu odmocniny, E je p esnost v procentech. (.pln p ehled syntaxe cykl s podmnkou jazyka VBA je v tab. 1). Tab. 1 P ehled cykl s podmnkou Cykly s podmnkou na konci Typ until

Do

P kazy cyklu Loop Until Podmnka Cykly s podmnkou na zatku Typ Until Do Until Podmnka P kazy cyklu

Loop

Typ while

Do

P kazy cyklu

Loop While Podmnka Typ While

Do While Podmnka P kazy cyklu

Loop

Krom cykl s podmnkou umo uje VBA i cykly zen promnnou { cykly typu For. Syntaxe tohoto p kazu je: For I = P To N Step S Pkazy cyklu Next I


567

Zde I je dc promnn cyklu, kter nab v hodnot v mezch od P do N s krokem S . Cyklus, jak je to ve VBA standardem, je ukonen koncov m

dkem Next.

Vstupy hodnot Konenou vypotenou hodnotu odmocniny zobrazme p kazem v stupu MsgBox ("X = " & V). Tento p kaz vype obsah promnn V i s dopl kov m textem (X = : : : ) { viz obr. 5.

; Obr. 5 Vpis hodnot

V p pad zadn N < 0 se promnn V napln informanm textem !neex", co zna, e odmocninu nelze vypotat. Je tedy z ejm, e do jedn promnn umo uje VBA zapisovat jak selnou hodnotu, tak hodnotu typu text. Z hlediska deklarac tento programovac jazyk nen striktn. Pokud nen na zatku programu promnn deklarovna, m standardn p i azen typ Variant, kter umo uje do tto promnn nast jakkoliv slo (cel, reln), text a vechny dal typy, kter VBA p ipout. Zmna typu promnn (textu na slo apod.) probh !automaticky", kompiltor ve vtin p pad hodnot p i ad sprvn typ. Pro lep pochopen typu Variant si lze p edstavit, e promnn tohoto typu ve VBA odpovd v Excelu typu bu ky Obecn . V naem p kladu program vypisuje (pro informaci) do bunk sloupce B prbn hodnoty X . Tyto v stupy provd p kaz Cells(I+1, 2) = Xo, p iem I uruje index dku a slo 2 uruje index sloupce { viz obr. 5. 568


Odladn programu a jeho sputn

Program se spout klvesou F5 nebo zelenou ipkou v nabdce. Velice uitenou funkc, zejmna pro ladn, je monost tzv. krokovn programu. Program krokujeme stiskem klvesy F8 a kad m dalm stiskem tto klvesy se posuneme na dal dek programu (aktuln provdn dek je oznaen lut). Zastavit program v prbhu ladn meme tak pomoc stop znaky umstn vlevo vedle dku, na kterm chceme program pozastavit. Pokraovat dl umon stisk klvesy F8 nebo F5. VBA v reimu krokovn umo uje zjistit aktuln hodnotu promnn { sta umstit kurzor na tuto promnnou a hodnota se objev v malm rmeku { viz obr. 6.

;;

; Obr. 6 Krokovn programu ve VBA

Program napsan ve VBA se neukld samostatn, ale vdy spolen se seitem Excelu v rmci nho je vytvo en. Ve verzi Excelu 2007 je t eba aplikaci uloit jako Seit aplikace Excel s podporou maker (*.xlsm).

Z vr

'lnek p edstavuje Visual Basic for Application (VBA) jako programovac jazyk vhodn pro v uku zklad algoritmizace a programovn. P estoe VBA pat mezi modern objektov orientovan a udlostmi zen programovac jazyky, lze ho vyut i v pln sekvennm strukturovanm Matematika - fyzika - informatika 20 2010/2011

569

programovn ani by bylo nutn zab vat se otzkami objekt a udlostmi. Tento p stup je v zkladech v uky programovn nezastupiteln . Dal v hodou tohoto programovacho jazyka je to, e se jedn o jazyk implementovan do vech MS O-ce produkt, je v tchto aplikacch dostupn a pln vyuiteln . Navc si s vyuitm tohoto jazyka meme p i vyuvn tchto aplikac usnadnit prci, pokud si naprogramujeme nstroj, kter za ns vykonv uritou astokrt se opakujc stejnou innost. Literatura 1] Pra k, P. { Pra kov , B.: Excel a rekurentn zadan posloupnosti ve nann matematice. MFI, r. 18 (2008/9), . 1, s. 42{51. 2] Hub lovsk , .: Vyuit aplikace MS Excel pro vpoet tit soustavy kvdr. MFI, r. 19 (2009/10), . 9, s. 551 { 558. 3] Walkenbach, J.: Microsoft Excel, Programovn ve VBA. Computer Press, Brno, 2004. 4] Redakce MFI: Odmocnina. MFI, r. 18 (2008/9), . 1, s. 52 { 53. 5] Taufer, I. { Kotyl, J. { Hrubina, K.: Algoritmy a algoritmizace { vvojov diagramy. Univerzita Pardubice, 2009.

ZKU ENOSTI Kam chod ci a studenti v Toulouse na exkurze Nkter msta se pyn akvaparky i rznmi pou(ovmi atrakcemi s nep li hodnotnou zbavou a stvaj se p itom { bohuel { i clem kolnch vlet. Se skupinou p rodovdn vzdlanch uitel z nkolika zem EU jsem ml monost ve francouzskm mst Toulouse poznat podstatn hodnotnj zbavu. Velmi p italivou a hojn navtvovanou lokalitou je zde astronomicky a technicky zam en vzdlvac centrum. Rozlehl arel s npisem Cit de l'Espace nad vchodem je zaplnn expozicemi, kter nvtvnkovi p ibliuj

570

ve, co souvis se vstupem lovka do kosmu (obr. 1). Je to obdoba proslulho Space & Rocket Center v americkm Huntsvillu. Zatmco Amerian jsou velmi pyn na vlastn podl dobvan vesmru (v tamnm Muzeu asu jde tm o ryze americk obsah expozice), Francouzi jsou zde objektivnj. Je pravdou, e prvn druici, pejska i lovka poslali do kosmu Rusov, a prv tmto udlostem je vnovna

; ; Obr. 1


v ch tabulek. Z tohoto pohledu se zd b t jednoduch z m na na prvn pohled bezpe n m zp sobem utajen obsahu zpr vy. Realita je ov em jin. Na skute nost,

Recommend Documents