Pro PRAVDU se Excel nezlobφ

Jak asi vφte, Excel zvlßdß v²poΦty s Φφsly na patnßct platn²ch cifer. To se m∙₧e zdßt jako naprosto dostateΦnΘ pro kancelß°skΘ u₧itφ Office, pro v∞deckΘ v²poΦty u₧ to nenφ optimßlnφ. Kup°φkladu funkce Fibbonaciho posloupnosti u₧ p°estßvß b²t pou₧itelnß lehce nad Φφslem 70, v Excelu jen s velk²mi obtφ₧emi vyzkouÜφte algoritmus pro poΦφtßnφ cifer Φφsla "pφ", obecn∞ pak v²poΦetnφ technice jako takovΘ dßvß zabrat faktorißl. Jin²mi slovy, rychle rostoucφ posloupnosti Φφsel nejsou pro Excel tφm prav²m. V∞tÜinu z nßs ovÜem tyto skuteΦnosti nemusφ trßpit.

Vzpomφnßte, kdy₧ byla °eΦ o vnit°nφm a vn∞jÜφm formßtu bu≥ky? Vnit°n∞ si Excel poΦφtß se sv²mi patnßcti ciframi. Vizußlnφ tvar Φφsla ovlivnφ formßt bu≥ky. Nezapome≥te, ₧e jeho d∙sledkem m∙₧e b²t chybn∞ zobrazenß hodnota. Jako p°φklad vyzkouÜejte souΦet cifer 1.95 a 1.90 s formßtem bu≥ky pro zobrazenφ jednoho desetinnΘho mφsta. Stejn² formßt dejte i bu≥ce se souΦtem t∞chto hodnot. Excel ukß₧e Φφsla 2.0, 2.0 a 3.9. (Dovolil jsem si zde pou₧φt teΦku mφsto desetinnΘ Φßrky.)

Zaokrouhlovßnφ s pomocφ funkcφ listu je zaokrouhlenφm definitivnφm a je t°eba je zvß₧it. Ka₧d² dalÜφ v²poΦet, kter² se na Φφslo odvolßvß, bude obsahovat i chybu vlivem zaokrouhlenφ. Chyba tak nar∙stß a v²sledek °ekn∞me dvaceti v²poΦt∙ m∙₧e b²t znehodnocen. N∞kdy je ale zaokrouhlenφ dokonce dßno legislativou. Ano, mßm te∩ na mysli ·Φetnφ zaokrouhlovßnφ. Poj∩me se podφvat na fukce slou₧φcφ k zaokrouhlovßnφ.

Jak vidφte, funkce nepracujφ a₧ tak ·pln∞ korektn∞.

Poznßmka: Budete-li Φerpat ze zahraniΦnφ literatury, m∞jte po ruce tabulku funkcφ AN-CZ. Funkci ZAOKR.DOLU toti₧ t°eba odpovφdß v anglickΘ verzi funkce FLOOR, nikoliv ROUNDDOWN, kterß Φesk² p°eklad nemß. Vysv∞tlivku k funkcφm p°edstavuje dalÜφ obrßzek.

Funkce ROUNDUP a ROUNDDOWN zaokrouhlujφ Φφslo na nßsobky v danΘm sm∞ru. Je-li vaÜφm cφlem zφskat nßsobek Φφsla nejbli₧Üφ danΘ hodnot∞, pou₧ijte funkci MROUND (nutno nainstalovat dopln∞k AnalytickΘ nßstroje). Zmφn∞nΘ ·Φetnφ zaokrouhlovßnφ pak m∙₧e vyu₧φvat vzorec =ZAOKR.NAHORU(A3;0,5), tj. zaokrouhlovßnφ na padesßtihalΘ°e sm∞rem nahoru.

Co mi °eknete o danΘm Φφsle? Pokud bych obdr₧el danou otßzku, asi bych si vzpomn∞l na to, ₧e Φφslo m∙₧e b²t lichΘ nebo sudΘ, kladnΘ nebo zßpornΘ, celΘ, desetinnΘ, p°irozenΘ, reßlnΘ, komplexnφ, prvoΦφslo apod. Sm∞sici funkcφ, a¥ u₧ v²poΦetnφch nebo informaΦnφch, vlastnφch i vestav∞n²ch, p°edvßdφ nßsledujφcφ t°i obrßzky (vÜe je samoz°ejm∞ p°ipraveno k odzkouÜenφ v p°φloze).

Funkce STIRLING je aproximacφ faktorißlu (poΦφtaΦov∞ ·nosn∞jÜφ algoritmus v²poΦtu za cenu nep°esnosti), FIB generuje Φleny Fibbonaciho posloupnosti, LUC Φleny Lucasovy posloupnosti. DESROMAN je protip≤lem vestav∞nΘ funkce ROMAN (p°evod °φmsk²ch Φφsel na desφtkovΘ), PRVOCISLO A PRVOCINITELE asi neni treba komentovat. OznaΦenφ lichΘ-sudΘ zjiÜ¥ujφ funkce ISODD a ISEVEN (viz op∞t dopln∞k AnalytickΘ nßstroje). D∞litelnost °eÜφ funkce MOD (funkce vlastnφ algoritm∙m). ARMSTRONG vracφ hodnotu PRAVDA, pokud je Φφslo rovno souΦtu t°etφch mocnin sv²ch cifer, PALINDROM vracφ PRAVDA, je-li Φφselnß posloupnost symetrickß (nap°φklad 175565571). SUMACIFER a POCETCIFER zφskßvß z °et∞zce ΦφselnΘ hodnoty a dßle s nimi poΦφtß, UNIZNAKY pak vracφ hodnotu PRAVDA, pokud °et∞zec obsahuje znaky bez opakovßnφ. N∞kterΘ vlastnφ funkce (FAKT pro v²poΦet faktorißlu, LUD pro zφskßnφ Φφsla "pφ", ...) aΦkoliv v Excelu existujφ, jsou zpracovßny v rßmci praktikovßnφ znßm²ch algoritm∙. Netvrdφm, ₧e jejich zpracovßnφ je ideßlnφ. A jedeme dßl... PokraΦujeme zpracovßnφm pßr∙ Φφsel.

Zde je snad zapot°ebφ jen vysv∞tlenφ dvou funkcφ. KONGRUENCE vracφ PRAVDA, pokud prvnφ i druhΘ Φφslo po d∞lenφ zvolen²m Φφslem vracφ stejn² zbytek. PAROVOST testuje, zda-li se jednß o stejnou skupinu znak∙ °et∞zce, by¥ v jinΘm po°adφ. Je to v podstat∞ test permutace.

Mno₧iny

Moc se nesm∞jte, ono to s t∞mi "hruÜkami a Üvestkami" tak jednoduchΘ nenφ :-) Tento odstavec mß op∞t velmi blφzko k algoritmizaci ·loh a k slibovanΘmu na₧havenφ mozkov²ch zßvit∙. Jisk°iΦkou pro zß₧eh byla skuteΦnost, ₧e funkce KDYÄ umo₧≥uje pouze sedm k°i₧ovatek pro rozhodovßnφ. Otßzka tedy zn∞la, zda-li jde snφ₧it poΦet porovnßvßnφ pro zßkladnφ ·lohy. V²sledek posu∩te sami.

Na zßklad∞ ukßzky lze dosp∞t i k dalÜφ zajφmavΘ skuteΦnosti. ObdΘlnφkovou oblast (t°eba oblast bun∞k v listu) lze popsat pouze dvojφm porovnßnφm (viz Φtvrt² °ßdek, operace <= berte jako jedno porovnßnφ). No a kdy₧ k funkci ABS p°idßte jeÜt∞ SIGN (-1, 0, 1 podle druhu Φφsla zßpornΘ-nula-kladnΘ), dokß₧ete i dalÜφ v∞ci.

┌lohy pracujφcφ s cykly

P°edstavte si, ₧e mßte v bu≥kßch pod sebou napsanΘ m∞sφce od ledna do prosince. Kdy₧ tento seznam vytisknete, vyst°ihnete a slepφte oba konce k sob∞, mßte nekoneΦn² cyklus, tak jak funguje i v praxi, tedy po prosince p°ijde op∞t leden. Jak ale donutit Excel, aby takΘ pova₧oval rozbalen² seznam za cyklus? Jin²mi slovy, jak donutit t°eba vyhledßvacφ funkci aby po prosinci p°eÜla na leden a obrßcen∞? Vyhledßvacφ funkce v∞tÜinou pracujφ s indexy, proto si na chvilku zam∞≥te v duchu m∞sφce za Φφsla od 1 do 12 (i = 1, 2, à 12), co₧ znamenß nauΦit Excel, aby po dvanßctce p°eÜel na jedniΦku. Nazv∞me tento p°φpad pot°ebou resetu cyklu na Φφsle (R), v naÜem p°φkladu resetu na Φφsle 12. Pot°ebujeme tedy kontrolovat p°eteΦenφ prom∞nnΘ i. Jako prvnφ vßs asi napadne vyu₧φt funkce MOD() vracejφcφ zbytek Φφsla po d∞lenφ. Pokud ji u₧ijeme ve tvaru MOD(i, R+1), dosßhneme k²₧enΘho efektu jen zΦßsti. Dojde k resetu na R (v naÜem p°φkladu R = 12), ovÜem nßsledujφcφ cyklus zaΦne nulou a nikoliv jedniΦkou. P°esu≥me pozornost k sesterskΘ funkci QUOTIENT vracejφcφ celou Φßst po d∞lenφ (nutn² dopln∞k AnalytickΘ nßstroje). Experimentovßnφm jsem u kladn²ch Φφsel doÜel ke tvaru

= i-R*(QUOTIENT(i-1;R))

P°i R = 12 bude vrßcenß hodnota tedy v₧dy v rozmezφ 1 a₧ 12, a to nikoliv nßhodn∞. Vlastn∞ dochßzφ k jakΘmusi rozpoΦφtßvßnφ do Üuplφk∙ 1 a₧ 12. Pro pochopenφ, Φφslo 20 na vstupu nßm bude vrßceno jako 8, co₧ je jeden rok (cyklus) a 8 m∞sφc∙ (Üuplφk 8). P°ipustφme-li v²skyt zßporn²ch hodnot, pak funkce m∙₧e mφt nßsledujφcφ tvar:

= SIGN(i)*(ABS(i)-R*(QUOTIENT(ABS(i)-1;R)))

Po Φase jsem doÜel k ekvivalentnφmu vzorci, kter² nevy₧aduje instalovanΘ AnalytickΘ nßstroje:

= i-R*(ROUNDUP(i/R;0)-1)

P°ehled postup∙ p°i prßci s cykly (pou₧iteln² obecn∞ i ve VBA) znßzor≥uje obrßzek nφ₧e.

"╚φsla v mixΘru"

Odbornφk promine, ale tento odstavec jsem tak nazval proto, ₧e jsem necht∞l Φtenß°e u₧ nadpisem d∞sit neoblφbenou Φßstφ matematiky st°ednφ Ükoly. P∙jde toti₧ o kombinace, variace a permutace. P°φkladem snad alespo≥ naznaΦφm, co se skr²vß pod jednotliv²mi pojmy. Cφlem bylo navr₧enφ funkcφ, kterΘ generujφ seskupenφ Φφsel v souladu s t∞mito pojmy.

U₧ slyÜφm Φtenß°e, kte°φ °φkajφ, ₧e pot°ebujφ funkci, kterß generuje hesla ze znak∙, nikoliv Φφsel. Tento Φlßnek ale nesm∞°uje p°φmo k VBA, jen naznaΦuje mo₧nosti. Napovφm pouze tedy, ₧e kup°φkladu zßpisem 4312 je °eΦeno: ╚tvrt² Φlen dej na prvnφ pozici, t°etφ na druhou, prvnφ p°ijde na t°etφ a druh² na poslednφ. Tak₧e hurß se znaky do pole a ... zbytek je na vßs :-) K≤d funkcφ je toti₧ voln∞ p°φstupn²...

K vyhodnocovßnφ pravdivosti Φi nepravdivosti v²rok∙ slou₧φ tzv. Boolovskß algebra. Ta pracuje s dvouhodnotov²mi prom∞nn²mi. Ony dva stavy tvo°φ znßmΘ PRAVDA, NEPRAVDA, v elektrotechnice pak 1 (zapnuto), 0 (vypnuto). ┌plnou tabulku elementßrnφch operacφ dvou prom∞nn²ch ukazuje nßsledujφcφ obrßzek (spodnφ odd∞lenß Φßst je v²sledkem experimentovßnφ a netvo°φ zßklad).

Pracujeme-li s vφce jak dv∞ma vstupy, musφme se pohlΘdnout po teorii. Existujφ v podstat∞ dv∞. Prvnφ je znßmß jako Karnaughovy mapy, se druhou jsem se osobn∞ seznßmil mnohem pozd∞ji a je ·Φinn∞jÜφ. Nese nßzev Quine-McCluskey. Cφlem obou je minimalizovat funkci (a tedy poΦet elektronick²ch souΦßstek) pot°ebnou k °eÜenφ problΘmu. Je vßm tato technika cizφ a zdß se vßm nepot°ebnß? Nemusφ tomu tak b²t. Nßsledujφcφ p°φklad °eÜφ pou₧itφ odv∞trßvacφho za°φzenφ u t°φ pecφ. Zadßnφ je takovΘ, ₧e odv∞trßvßnφ se mß spustit, jsou-li v provozu alespo≥ dv∞ pece.

V tuto chvφli nemßm zpracovan² obecn² algoritmus pro Karnaughovy mapy. Proto je v²sledku dosa₧eno p°evß₧n∞ ruΦn∞. Na internetu naleznete ovÜem °adu samostatn²ch program∙ na toto tΘma (Karnaugh Minimizer - http://www.shuriksoft.com, K-V Diagram-Applet a jinΘ).

V moment∞, kdy jsem se chystal naprogramovat pou₧iteln∞jÜφ metodu Quine-McCluskey, naÜel jsem v²born² dopln∞k italskΘho programßtora ji₧ hotov² a navφc zdarma. Jednß se o Binary Arithmetic Package (zkrßcen∞ BINUM) a najdete jej na strßnkßch http://digilander.libero.it/foxes/binum11.zip.

• ╚φslo "pφ" je v Excelu zastoupeno stejnojmenou funkcφ PI, Eulerovo Φφslo "e" zadßte funkcφ EXP(1), tj. "e" na prvou. Zkratka EXP je ostatn∞ Φastokrßt uvßd∞na i v odbornΘ literatu°e.
• PoΦφtßte-li ve sv²ch vzorcφch se zßporn²mi Φφsly, vklßdejte je do zßvorek. Znßm je toti₧ problΘm s unßrnφm znamΘnkem minus v porovnßnφ se znamΘnkem odΦφtßnφ (viz obrßzek).
  
• Zatφmco funkce USEKNOUT je v∞rnß svΘmu nßzvu, funkce CEL┴.╚┴ST zaokrouhlφ Φφslo na nejbli₧Üφ menÜφ celΘ Φφslo. Nap°φklad =CEL┴.╚┴ST(-9,1) vrßtφ -10. Zaokrouhluje tedy sm∞rem doleva.
• Funkce DOLLARFR a DOLLARDE p°evßdφ desetinnΘ Φφslo na zlomek a naopak.

Pou₧itß literatura:

J. Walkenbach: Excel - vzorce a funkce, Mobil Media, 2001
H. J. Bartsch: MatematickΘ vzorce, Mladß fronta, 1996
M. Kouck²: DiskrΘtnφ matematika, dφl I. a II., TU Liberec, 2003