2005

Excel Asistent Magazín PREMIUM 01/2005 ISSN 1801 - 2698 Copyright © 2003 – 2005 Jiří Číhař, Dataspectrum http:// //www.dataspectrum.cz //

mailto:[email protected] Excel Asistent Magazín PREMIUM je rozšířená verze měsíčníku Excel Asistent Magazín. Toto první číslo je určeno k bezplatné distribuci, následující čísla si v případě zájmu můžete objednat prostřednictvím formuláře na webové stránce http://www.dataspectrum.cz

Obsah tohoto magazínu podléhá autorskému zákonu a nelze jej bez předchozího souhlasu autora kopírovat.

Excel Asistent Magazín PREMIUM 01/2005 Součet položek splňující zadaná kriteria. ................................................................................................1 Funkce? Nejlepší přítel uživatele Excelu. ................................................................................................5 Excel a praxe auditora – nevěřme křížovým součtům. ........................................................................12 Zaokrouhlujete? A umíte zaokrouhlit na zadanou hodnotu? .............................................................14 Kontingenční tabulky – vy je ještě nepoužíváte? ..................................................................................16 Excel a vizualizace dat – podmíněné formátování. ...............................................................................18 Excel a finance – vytvoření splátkového kalendáře. .............................................................................22 Funkce pro Excelovské kouzelníky – seznam unikátních hodnot. ......................................................24 Excelentní Excel – nainstalujte si další nástroje ...................................................................................26 Zobrazení funkcí a vzorců.......................................................................................................................35

Příklady ilustrující postupy prezentované v tomto magazínu naleznete v dokumentu Excelu na adrese http://www.dataspectrum.cz/excelmagprem/download/eamp0105x.zip

15.7. 2005 si budete moci z webové stránky http://www.dataspectrum.cz stáhnout Excel Asistent Magazín PREMIUM č. 02/2005.

V tomto čísle naleznete i objednávkový formulář – zabezpečte si budoucích 12 čísel. Každé z nich Vám přinese minimálně o 10 stran informací více než předchozí bezplatné číslo Excel Asistent Magazínu.

V čem mi může magazín Excel Asistent Magazín PREMIUM pomoci? • • • • • • • •

Požadují po Vás nadřízení zpracování dat dle neustále se měnících kriterií? Prezentujete výsledky své práce ve formě tabulek Excelu? Je nezbytné, aby data, která zpracováváte a výpočty na nich založené, byly bezchybné? Potřebujete zvýšit vypovídací schopnost Vašich výstupů? Zpracováváte v Excelu údaje z různých zdrojů odlišné kvality a formátu? Kontrolujete a ověřujete data a výpočty Vašich kolegů? Je pracovní pozice, kterou zastáváte, nebo o kterou se ucházíte, spojena s expertní znalostí Excelu? Hledáte způsob, jak automatizovat rutinní operace v prostředí Excelu?

Pokud jsou Vaše odpovědi na tyto otázky záporné, pak Excel Asistent Magazín PREMIUM nepotřebujete.V opačném případě by Vám však magazín mohl ve Vaší práci výrazně pomoci. NESTAČÍ MI SNAD ÚSPĚŠNÉ ABSOLVOVÁNÍ TÝDENNÍHO KURZU EXCELU, PODNIKOVÉ VZDĚLÁVÁNÍ, PŘÍRUČKA PROGRAMU A PŘEČTENÍ NĚKTERÉ Z MNOHA UČEBNIC PRÁCE S EXCELEM? ODPOVĚĎ JE NA VÁS –

•

• • •

Vzdělávací kurz se obvykle přizpůsobuje nejslabším ( čili začínajícím) uživatelům – a Vy se chcete stát skutečným znalcem, který bude nepostradatelný jak pro své nadřízené, tak i pro své kolegy. Kdy jste se naposledy účastnil podnikového vzdělávání? Před rokem, nebo si již nemůžete ani vzpomenout? Nezměnila se Vaše náplň práce od té doby, nepřibyly Vám nové a složitější úkoly? Nabízí Vám příručka osvědčené postupy a alternativní způsoby řešení? Nehledáte podstatné vědomosti v záplavě informací typu: Stiskněte tlačítko a pak potvrďte stiskem klávesy Enter“? Přečetli jste učebnici od první do poslední stránky? Neobjevily se od data vydání učebnice nové postupy a oblasti využití?

V ČEM MŮŽE EXCEL ASISTENT MAGAZÍN PREMIUM ROZŠÍŘIT A DOPLNIT PŘEDCHÁZEJÍCÍ ZDROJE VZDĚLÁNÍ?

• Reaguje na nové poznatky a publikovaná řešení. Obsah vzniká mimo jiné i monitorováním otázek a odpovědí prezentovaných ve specializovaných diskusních skupinách v několika jazycích ( angličtina, němčina, italština, francouzština, ruština, polština a španělština) a cíleným výběrem příspěvků, které posouvají využití Excelu na kvalitativně vyšší stupeň. • Seznamuje Vás s možnostmi rozšíření funkčnosti Excelu. Přináší popisy doplňků, které lze získat z internetu a často zcela bezplatně používat pro řešení úkolů. • Vytváří zpětnou vazbu publikováním dotazů čtenářů a odpovědí na ně. • Zprostředkovává 10 let zkušeností vývoje řešení v Excelu v prostředí finančních institucí a 3 roky provozu domény www.dataspectrum.cz • Nabízí řešení reálných problémů reálného prostředí z pohledu českého a slovenského uživatele Excelu.

1 2

Součet položek splňující zadaná kritéria Určeno: zkušený uživatel

jednotlivých buněk, k představě vzorce, který pracuje s celou oblastí. Nápověda Excelu popisuje maticové vzorce následujícím způsobem:

Zebrasona Xanthurum Rudé moře

J

ednou z nejčastěji kladených otázek uživatelů je dotaz týkající se způsobu, jak v Excelu vytvořit součet položek na základě splnění podmínky. Tento problém může mít samozřejmě různé varianty, například místo součtu je naším cílem zjistit počet záznamů, které vyhovují podmínce, nebo místo jednoduché podmínky nás zajímá výběr na základě podmínky komplexní s využitím logických operátorů.

Maticový vzorec může provést několik výpočtů a potom vrátit jeden nebo několik výsledků. Maticové vzorce počítají na základě dvou nebo více množin hodnot, neboli maticových argumentů. Každý maticový argument musí obsahovat stejný počet řádků a sloupců. Maticové vzorce vytvoříte stejně jako jiné vzorce. Jediný rozdíl spočívá v tom, že se vzorec zadává stisknutím kláves CTRL+SHIFT+ENTER. Rozsáhlý úvod a mnoho příkladů použití tohoto typů vzorců naleznete v Excel Asistent Magazínu č. 03/2003. Pro úspěšnou práci s maticovými vzorci si pamatovat následující čtyři základní pravidla: 1.

musíme

Každý argument maticového vzorce musí obsahovat stejný počet řádků a sloupců ( tvrzení neříká, že počet řádků = počet sloupců, ale musí platit, že počet řádků prvního argumentu se rovná počtu řádků dalšího argumentu a obdobná rovnost platí i pro počty sloupců) 2. Maticový vzorec zadáváme nikoliv pouhým stisknutím potvrzovací klávesy Enter, ale současným stiskem tří kláves Ctrl+Shift+Enter. Stisk těchto kláves a tedy vytvoření maticového vzorce je indikováno Excelem zobrazením složených závorek, které ohraničují zapsaný vzorec a jsou zobrazeny v řádku vzorců. Řešení pomocí maticových Maticové vzorce 3. Maticový vzorec nelze vytvořit vzorců Excelu představují zadáním složených závorek přímo z klávesnice – současný mocný a užitečný Maticové vzorce Excelu představují velice stisk Ctrl+Shift+Enter je jediný nástroj mocný a užitečný nástroj, který umožňuje regulerní postup, který Excel vytvářet mnohem komplexnější výpočtové akceptuje. (Zkušenější modely než klasické vzorce. Právě použití uživatelé mohou nabýt dojmu, maticových vzorců povyšuje práci s Excelem z rutinních že předchozí tvrzení není pravdivé, protože vědí, postupů na úroveň, kde lze jistě oprávněně hovořit o že lze zadat do vzorce matici, která je ohraničena umění. právě ručně zadanými složenými závorkami. To je Zvládnutí principů maticových vzorců není tak obtížné, pravda, ale tento postup nevytváří maticový jak by se mohlo při pohledu na nějaký komplexní vzorec, vede pouze k zadání matice jako maticový vzorec zdát. Studiem jednodušších, ale plně argumentu funkce. Časem jistě získáme cit pro funkčních maticových vzorců můžeme získat schopnost vnímání rozdílů mezi maticovými vzorci a vzorci nejenom tyto typy vzorců vytvářet, ale především obsahujícími matici jako svůj argument.) uvažovat o excelovských modelech v intencích 4. Nemůžeme vytvořit maticový vzorec pro oblast maticového uspořádání. zahrnující celý sloupec. V podstatě se jedná o přechod mezi vnímáním vzorce jako postupu, který nějakým způsobem mění obsah

Tak jako i pro řešení jiných úloh, Excel nám nabízí více způsobů, jak součet nebo počet na základě výběrového kriteria vytvořit. Postupy nám pochopitelně musí poskytnout shodný výsledek, liší se ale jednak složitostí a především efektivitou. Vykazují totiž různou rychlost odezvy a některé z těchto postupů dokonce mohou být v případě rozsáhlejšího souboru dat s ohledem na dobu zpracování téměř nepoužitelné. Náročnosti na čas zpracování bychom si měli být vědomi už v době návrhu a volit způsob řešení především s ohledem na rozsah úlohy.

2 2

Řešené příklady Pro účely našeho výkladu použijeme databázi obsahující čtyři parametry a 10 záznamů v oblasti A1:D11.

Příklad č. 3

Počet osob ve věku 30 až 40 let s platem 20 000Kč – 30 000Kč =SUMA((B2:B11>30)*(B2:B11<40)*(E2:E11>20000) *(E2:E11<30000)) Příklad č.1

Počet mužů mladších 40 let ( Muž / Žena = „M“,Věk < 40 ) =SUMA((B2:B11<40)*(D2:D11=“M“))

Příklad č. 2

Počet osob ve věku mezi 25 a 45 let s platem přes 30 000Kč =SUMA((B2:B11>25)*(B2:B11<45)*(E2:E11>30000))

Ačkoliv jsme v našich příkladech použili funkci SUMA, která je určena pro vytváření součtů, představuje výsledná hodnota počet záznamů vyhovujících zadané kombinaci parametrů. Je tomu tak proto, že funkce SUMA sčítá výsledky násobení pravdivostních hodnot PRAVDA ( neboli číselná hodnota 1) a NEPRAVDA ( hodnota 0). Z výše uvedeného plyne, že pokud v některém z řádků není splněna i jen jediná podmínka, bude s ohledem na pravidla násobení také i výsledný součin roven nule. Pokud jsou však všechny podmínky pro určitý řádek splněny, je i výsledný součin roven nule ( 1*1*1…=1). A je to právě operace násobení, která konvertuje pravdivostní hodnoty PRAVDA / NEPRAVDA na číselné hodnota 1 a 0. Celý způsob výpočtu si můžeme dokumentovat na prvním příkladě:

3 2

Řešení pomocí funkce SOUČIN.SKALÁRNÍ Dalším způsobem řešení je zapojení poměrně málo využívané funkce SOUČIN.SKALÁRNÍ. Tato funkce násobí odpovídající prvky matic zadaných ve vstupních argumentech a pak tyto součiny sečte. Může se nám na první pohled zdát, že význam této funkce je spíše akademický a ve světě reálných problému ji jen stěží využijeme. Opak je však pravdou – právě svojí schopností akceptovat v argumentech matice a oblasti buněk, provést

na jejich prvcích matematickou operaci a následně vrátit jedinou hodnotu plně vyhovuje konceptu tabulkového procesoru. Tedy konceptu ukládání dat do izolovaných buněk s geometrickým rozložením – principem, kdy oblasti sousedících buněk jsou nositelem logicky souvisejících informací. Lze říci, že tato funkce umožňuje provádět operace jinak dosažitelné pomocí maticových vzorců. Který z postupů je vhodnější? Odpověď není jednoduchá, závisí samozřejmě na mnoha okolnostech, ale obecně lze říci, že oblast použití maticových vzorců je rozsáhlejší, než možnosti funkce SOUČIN.SKALÁRNÍ, na druhou stranu však použití většího počtu maticových vzorců může vést k výraznému zpomalení výpočtů v našem sešitu. Větším počtem nerozumíme pouze počet buněk, do kterých jsme zapsali maticové vzorce, ale samozřejmě i počet buněk oblastí, které maticovým vzorcům zadáváme jako vstupní argumenty. Snížení rychlosti přepočítávání buněk vzniká i při rozsáhlém použití funkce samozřejmě SOUČIN.SKALÁRNÍ, toto zpomalení však není obecně v porovnání s obdobným zpomalením při použití maticových vzorců tak dramatické. Formální, i když jistě významný rozdíl mezi funkcí SOUČIN.SKALÁRNÍ a maticovými vzorci je ve způsobu zadání – SOUČIN.SKALÁRNÍ potvrzujeme pouhým stiskem klávesy Enter, maticové vzorce stiskem kombinace kláves Ctrl+Shift+Enter.

Řešené příklady Příklad č. 4

Počet osob ve věku mezi 25 a 45 let s platem přes 30 000Kč =SOUČIN.SKALÁRNÍ((B2:B11>25)*(B2:B11<45)*(E2:E11>30000))

Příklad č. 5

Součet výše platů mužů ve věku nad 30 let, kteří nastoupili po datu 1.1.2000 Řešení pomocí maticového vzorce {=SUMA(KDYŽ(B2:B11>30;KDYŽ(C2:C11>DATUM(2000;1;1);KDYŽ(D2:D11=“M“;E2:E11))))} Řešení pomocí funkce SOUČIN.SKALÁRNÍ

4 2

=SOUČIN.SKALÁRNÍ((B2:B11>30)*(C2:C11>DATUM(2000;1;1))*(D2:D11=“M“) *(E2:E11))

Na tomto příkladě je již vidět „přímočarost“ řešení založeného na funkci SOUČIN.SKALÁRNÍ. Není nutné využívat v konstrukci vzorce žádnou další funkci, logika řešení je vyznačena oddělujícími závorkami. Naopak v případě použití funkce SUMA musíme zapojit i funkci KDYŽ, která nám umožní aplikovat omezující kriteria. Přehlednost a srozumitelnost vzorce však již začíná být snížena v důsledku hierarchického vnořování jednotlivých funkcí KDYŽ do sebe. Měli bychom mít na paměti i omezení Excelu, který umožňuje maximálně vnoření 7 funkcí KDYŽ. Může tedy nastat i situace, kdy v důsledku tohoto omezení nebudeme schopni vzorec sestavit. Důležité upozornění na závěr Nadměrné používání maticových operací nebo funkce SOUČIN.SKALÁRNÍ obsahující větší počet kriterií vede k výraznému zpomalení odezvy Excelu. Nejde jen o počet buněk obsahující tyto vzorce, ale i o počet buněk obsažených v oblastech, které jsou funkcím zadány jako jejich vstupní parametry. Pokud by k takovéto nežádoucí situaci mělo dojít, je třeba použít vhodnější nástroje. Těmi jsou databázové funkce, které byly vytvořeny právě pro řešení úloh obsahujících velký počet kriterií. Tyto funkce nalezneme v seznamu zabudovaných funkcí Excelu. (V některém z dalších pokračování Excel Asistent Magazínu se budeme podrobně věnovat použití těchto funkcí – jejich přednostmi a nedostatky. Ukážeme si příklady typů úloh, pro jejichž řešení je jejich použití mimořádně efektivní.) Použití funkce SOUČIN.SKALÁRNÍ není omezeno pouze na úlohy, ve kterých je našim cílem nalezení součtu nebo určení počtu položek, které odpovídají komplexním výběrovým kritériím. Tato funkce patří u uživatelů, které je možné označit za experty v prostředí Excelu, mezi nejpoužívanější. Je totiž v kombinaci s dalšími funkcemi natolik flexibilní, že téměř neexistuje oblast, kde by s její pomocí nebylo možné vytvořit kompaktnější a efektivnější řešení než prostřednictvím ostatních funkcí.

V Excel Asistent Magazínu 02/2005 naleznete podrobný článek věnovaný výhradně funkci SOUČIN.SKALÁRNÍ s několika desítkami řešených příkladů. Věřím, že po jeho přečtení se tato funkce stane jedním z Vašich nejužitečnějších spojenců.

5

Funkce? Nejlepší přítel uživatele Excelu. Určeno: pokročilý uživatel /( ilustrace v tomto článku odpovídají verzi Excel 2000)

Pterois Volitans Indický a Tichý oceán

T

abulkový kalkulátor Excel je vybaven více než 200 vlastních funkcí, což jsou předdefinované vzorce, pomocí kterých můžeme Excel přeměnit z prostého skladníka dat na vysoce výkonného datového kouzelníka.

Funkce pokrývají celou škálu našich potřeb, od funkcí, které pouze jednoduchým způsobem zaměňují hodnoty uložené v buňkách jako například funkce N() nebo T(), až po funkce pochopitelné omezenému okruhu specialistů – například funkce ZTEST(), vracející dvoustrannou hodnotu P, která je výsledkem z-testu. Tváří v tvář celé této záplavě vzorců, o existenci a využití mnohých z nich jsme dosud ani neslyšeli, můžeme získat dojem, že již není co doplnit. Pravdou však je, že v průběhu času se jistě setkáme se situací, kdy zjistíme, že nám přece jen nějaký vzorec, důležitý v oblasti, kterou právě řešíme, chybí. Excel nám i v této situaci podá pomocnou ruku – přesněji řečeno jeho vývojáři, kteří pro nás připravili nástroje umožňující a usnadňující vytváření našich vlastních funkcí a jejich použití v našich tabulkách a sešitech. Vlastní funkce Excelu Vlastní funkce Excelu jsou pro použití v buňkách listu dostupné pomocí dialogového okna (nazývané také „průvodce vložením funkce“) Vložit funkci. Toto dialogové okno můžeme otevřít jedním z následujících postupů: • pomocí Vložit > Funkce • stiskem kombinace kláves Shift+F3 • stiskem tlačítka Vložit funkci, které nalezneme ve standardním panelu nástrojů Dialogové okno slouží k výběru vhodné ( a někdy i zcela nevhodné) funkce.

Funkce jsou v dialogovém okně seskupeny podle smyslu – v jedné skupině nalezneme funkce usnadňující vyhledávání dat, v další jsou zase shromážděny funkce, které nám umožní manipulaci s textem. Pokud si nejsme jisti, ve které skupině se hledaná funkce nalézá, zvolíme kategorii „Vše“, která obsahuje všechny funkce tříděné podle abecedy. Povzbudivé je pro nás, že nemusíme mít žádné znalosti programování, dokonce ani nemusíme znát přesný název funkce. Pokud například chceme vložit do oblasti buněk náhodná čísla a neurčitě víme, že požadovaná funkce se přibližně jmenuje NÁHODNÉČÍSLO, zobrazíme si kategorii „Vše“, aktivujeme seznam funkcí na pravém okraji dialogového okna a opakovaným stiskem klávesy N procházíme názvy funkcí, které tímto písmenem začínají, dokud nenarazíme na název funkce podobající se hledanému názvu.

6

Dokonce nemusíme znát ani přibližný název funkce. Dialogové okno totiž zobrazuje krátký popis funkce, kterou jsme v seznamu aktivovali. Pokud tedy budeme procházet jednotlivé funkce seznamu, můžeme pomocí této stručné nápovědy nalézt funkci splňující naše požadavky. Dříve než zvolíme funkci, která nejlépe odpovídá našim potřebám, musíme však provést ještě jedno rozhodnutí. Určit buňku, která bude obsahovat zápis naší funkce a zároveň i obsahovat výsledek této funkce. Určení buňky pro uložení výsledku je jednoduché – je to vždy buňka, která je aktivní. Jakmile zobrazíme dialogové okno průvodce vložením funkce, Excel zapíše vybranou funkci do buňky, která byla v okamžiku aktivace tohoto okna aktivní. Bude však ještě potřebovat nějaké doplňující informace. Těmi jsou hodnoty vstupních parametrů – tedy argumentů, které funkce potřebuje k výpočtu výsledku. I zde nám Excel nabízí nemalou pomoc. Ke vložení argumentů nám slouží přednastavená vstupní pole spojená opět s nápovědou. Jakmile přesuneme kurzor do některého z těchto polí, Excel zobrazí popis argumentu, jeho význam a dokonce i přípustné hodnoty. Pokud daný argument je nepovinný a nemusíme ho tedy zadávat, jsme na tuto skutečnost upozorněni. Názvy povinných argumentů jsou totiž zapsány tučným písmem, názvy nepovinných argumentů písmem obyčejným.

Některé funkce vyžadují nebo umožňují zadat velké množství argumentů a proto dialogové okno obsahuje svislý posuvník pro snadnější pohyb mezi jednotlivými argumenty.

Existují však i funkce, které žádný argument nepožadují – například již zmíněná funkce vracející náhodné číslo. Důležité je vědět, že hodnoty argumentů nemusíme zadávat přímo, ale můžeme je zadat zprostředkovaně. To znamená, že do vstupního pole argumentů zapíšeme adresy buněk, které obsahují hodnoty použité ve funkci ve významu argumentů.

Funkce vytvořené uživatelem pomocí VBA – čím se liší od vlastních funkcí. Uživatelské funkce jsou funkce, které si můžeme vytvořit sami. Slouží nám k tomu programovací jazyk VBA, který je nám s každou instalací Excelu k dispozici – stačí stisknout kombinaci kláves Alt+F11. Vlastní funkce Excelu nelze modifikovat. Nemůžeme změnit jejich chování, počet argumentů, algoritmus výpočtu. Tyto funkce akceptují pouze argumenty, které očekávají. V opačném případě vrátí chybu. Chybu vrací i v případech, kdy jsme funkci odkázali na buňku s nesprávným typem dat, pokusili se dělit nulou, pokusili se použít hodnoty v nedostupných buňkách a podobně. Chybu poznáme tak, že místo „rozumné“ hodnoty funkce zapíše do buňky některou z hodnot #N/A, #NUM!, #VALUE!, #DIV/0!, #REF, #NAME? Nebo #NULL. Pokud však vytváříme vlastní funkce, můžeme si funkci plně přizpůsobit vlastním potřebám. Můžeme například nastavit počet a typy argumentů, počet a typy výstupních hodnot, ověřovací kriteria, v případ vzniku chyby poskytnout uživateli srozumitelný popis chyby a informaci o možné příčině. Příklad si ukážeme na jedné z nejčastěji používaných funkcí – funkci SUMA, kterou používáme pro výpočet součtu hodnot oblasti buněk. Tato funkce ignoruje jiné než číselné hodnoty. Přesněji řečeno, pokud máme číslo naformátované jako text, zahrne toto číslo do součtu. Pokud ale máme řetězec, který se zobrazí jako číslo, ale funkce ho za číslo nepovažuje, pak tuto hodnotu ze součtu vyloučí a bohužel nás o této jistě nemilé skutečnosti neinformuje. Že takováto skutečnost nemůže nastat? Tak to je značný omyl. Mnohé provozní systémy vytvářejí sestavy, v nichž jsou hodnoty vesměs formátované pouze jako text. Často tyto hodnoty uvozují znakem apostrof ( ‚ ) A to je právě případ, kdy standardní funkce SUMA rozhodne o vyloučení hodnoty z celkového součtu ( přesněji řečeno, nahradí si hodnotu pro potřeby výpočtu číslem 0). Pouhým pohledem na data

7

nejsme schopni problém rozpoznat, protože Excel apostrof nezobrazuje a hodnota se nám tedy bude zobrazovat jako číslo. Vlastní funkce ChytraSuma, která je vytvořena v prostředí VBA, je však schopna nás upozornit na případný výskyt nečíselné hodnoty a místo nesprávného výsledku zobrazit text upozorňující, že oblast obsahuje tento nepřípustný typ. Public Function ChytraSuma(VstupniOblast As Range) ' cíl - funkce před výpočtem součtu testuje, ' zda jsou všechny hodnoty oblasti čísla

Pokud by tomu tak nebylo, nemáme tuto užitečnou vlastnost aktivovánu. Aktivaci povolíme v prostředí editoru pomocí Tools > Options > karta Editor > položka Auto List Members.

Dim a As Range Dim NalezenaChyba As String NalezenaChyba = False For Each a In VstupniOblast If Not Application.IsNumber(a) Then NalezenaChyba = True End If Next ' pokud naleznu ve VstupniOblast buňku ' obsahující jinou hodnotu než číslo ' oznámím to ' pokud jsou všechny hodnoty čísla ' vypočítám součet If NalezenaChyba = False Then ChytraSuma = _ Application.WorksheetFunction.Sum(VstupniOblast) Else ChytraSuma = _ "Nalezena hodnota, která není číslo" End If End Function

Z předchozího příkladu vyplývá jedna velice důležitá informace – při vytváření našich vlastních funkcí můžeme využívat vlastní funkce Excelu. Máme tedy k dispozici více než 200 stavebních kamenů, které můžeme podle libosti skládat, kombinovat, doplňovat o ověřovací kriteria, doplňovat vlastními výpočty a podobně. Rozsah možností je neomezený a záleží pouze na naší představivosti a schopnostech vývojáře. Vlastní funkce Excelu jsou dostupné v prostředí VBA ( editor tohoto jazyka otevřeme pomocí stisku kombinace kláves Alt+F11) jako metody objektu WorksheetFunction, jehož rodičem je objekt Application. VBA editor nám poskytuje značnou pomoc při vytváření našich funkcí. Například tím, že po zapsání klíčového slova WorksheetFunction (rodičovský objekt Application zapisovat nemusíme) a operátoru tečka nabídne seznam dostupných funkcí, ze kterého si můžeme vybrat. Výběr je interaktivní, pokud zadáváme z klávesnice znaky, které editor dokáže interpretovat jako začínající písmena některé z funkcí seznamu, aktivuje v seznamu název této funkce. Důležité je mít na paměti, že seznam je aktivován zapsáním operátoru tečka

Jakmile zvolíme funkci (dvojitým klepnutím na danou položku nebo stiskem klávesy mezerník), Excel nám nabídne seznam argumentů. Chování editoru je však v těchto dvou případech poněkud odlišné: • po dvojitém klepnutí musíme buď vložit mezeru nebo zapsat levou kulatou závorku • po stisku klávesy mezerník Excel automaticky vloží název funkce, za tento název mezeru a zobrazí seznam argumentů

Musíme však akceptovat skutečnost, že VBA editor není polyglot a vždy s námi bude hovořit pouze anglicky. Jestliže se rozhodneme používat ve svých modelech vlastní funkce, měli bychom mít stále na paměti, že neomezená flexibilita jejich vytváření s sebou přináší i jedno velké nebezpečí. Pokud totiž používáme vlastní funkce Excelu, které vkládáme pomocí dialogového okna, je výsledná

8

hodnota vždy uložena do aktivní buňky obsahující funkci. Uživatelé často nejsou schopni rozpoznat, které buňky obsahují reálné hodnoty a které funkce. Nicméně uživatelské prostředí Excelu poskytuje nástroje, kterými lze zobrazit funkce, případně odlišit buňky obsahující funkce od buněk obsahujících konstantní hodnoty. Excel nabízí v nápovědách dostatek informací, které uživatelům našich sešitů pomohou pochopit konstrukci, význam a cíl použitých VLASTNÍCH funkcí. Nástroje a postupy usnadňující identifikaci buněk obsahující funkce jsou vysvětleny v Excel Asistent Magazín PREMIUM 01/2005

Zatím jsme se zabývali případy, kdy vkládáme funkci do buněk sešitu, v němž je funkce zapsána. Obecně je možné v sešitu použít i funkce zapsané v jiném (nutně však otevřeném) sešitu. Pokud však takovou funkci chceme použít, musíme mít na paměti, že dialogové okno její název zobrazuje společně s názvem sešitu. Seznam se však chová poněkud zmatečně, protože funkce řadí dle abecedního pořadí jejich názvu, ale zobrazuje je v této pozici s názvem sešitu, který by ale měl být podle abecedního pořadí umístěn jinde. Toto zvláštní chování mějme na paměti, když se budeme snažit v seznamu nalézt požadovanou funkci.

Pokud však používáme vlastní funkce, uživatelé jsou odkázání pouze na náš popis a vysvětlení. Pokud kód funkce nedoplníme o podrobný popis, budou používat něco, o čem vůbec nevědí, jak funguje. Nejvíce uživatelům pomůžeme vložením komentáře s podrobným popisem do každé buňky obsahující naši vlastní funkci, případně s popisem stručným a odkazem na místo, kde mohou získat podrobnější informace. Umístění uživatelských funkcí do seznamu dialogového okna Vložit funkci Při zápisu funkce v editoru VBA je povinné deklarovat ji klíčovým slovem Function. Tímto výrazem tedy začíná i končí zápis kódu naší funkce. Další dvě možnosti jsou deklarace pomocí • Public Function • Private Function Zápis Function nebo Public Function způsobí, že naše funkce bude dostupná i pomocí dialogového okna Vložit funkci, konkrétně se jedná o kategorii označenou „vlastní“.

Pokud je naším cílem profesionálně vytvářet a používat uživatelské funkce, měli bychom se seznámit s principy použití osobního sešitu maker PERSONAL.XLS a tvorbou doplňků Excelu – dokumenty s příponou .XLA. O těchto tématech budeme podrobně psát v dalších číslech magazínu. Existují i vysoce sofistikované způsoby vytváření a implementace vlastních funkcí – například vytváření dokumentů typu .XLL. O jednom mimořádně užitečném souboru .XLL, který rozšiřuje Excel o desítky dalších funkcí, se můžeme dočíst v Excel Asistent Magazín PREMIUM 01/2005. Není pravdou, že uživatelská funkce musí vracet hodnotu nebo přijímat vstupní argumenty od uživatele. Můžeme například napsat funkci, která po uživateli nebude požadovat žádnou vstupní hodnotu a přesto bude vracet užitečnou informaci. Public Function JulianskyDen() ' vrátí pořadové číslo dnešního dne JulianskyDen = Date – DateValue _ ("12" & "/" & "31" & "/" & Year(Date) - 1) End Function

Pokud zápis kódu funkce zahájíme výrazem Private Function, takto zadaná funkce nebude z dialogového okna dostupná. Můžeme ji však zapsat do buňky listu tak, že její název zapíšeme pomocí klávesnice.

Vstupním argumentem naší funkce je dnešní datum. Funkce nevyžaduje tuto informaci od uživatele, potřebnou hodnotu získá pomocí vestavěné funkce Date() přímo z Excelu.

9

Vložení nápovědy k vlastní funkci V dialogovém okně Vložit funkci se můžeme orientovat pomocí nápovědy stručně popisující naši funkci. Obdobnou nápovědou můžeme doplnit i naše vlastní funkce a tím výrazně zvýšit jejich srozumitelnost pro koncové uživatele. Protože se jedná o funkce vytvářené v editoru VBA, budeme nápovědu zapisovat také v tomto prostředí. Využijeme k tomu položku Properties dostupnou po aktivací okna Object Browser. Postup si znázorníme pomocí obrázků. • •

• • •

Otevřeme v editoru kontextové menu ( klepneme pravým tlačítkem myši kdekoliv uvnitř kódu). V kontextovém menu zvolíme položku Object Browser

V okně Object Browser aktivujeme název funkce, ke které chceme vložit nápovědu… …a opět zobrazíme klepnutím pravým tlačítkem myši kontextové menu V kontextovém menu zvolíme položku Properties

•

Do textového okna Description zapíšeme text naší nápovědy.

•

Zadání potvrdíme stiskem klávesy OK, sešit uložíme.

Dialogové okno Vložit funkci bude nyní obsahovat text naší nápovědy.

10

Není pochyb o tom, že případným uživatelům našich souborů tento popis usnadní orientaci a to zejména tehdy, když jim nabídneme větší množství na míru vyvářených funkcí. Mezi vlastními funkcemi mi jedna chybí a naprogramovat ji zatím nedokážu.

Doplňky stisknutím tlačítka Procházet… Doplněk se jmenuje ANALYS32.XLL. Pokud doplněk nejsme schopni nalézt, je možné, že nebyl s Excelem nainstalován. V tomto případě je třeba instalaci doplnit z CD. Pokud instalace Excelu proběhla standardně, můžeme si povšimnout, že k dispozici máme dva doplňky podobného názvu. Pokud aktivujeme i doplněk Analytické nástroje – VBA ( na pevném disku ho nalezneme pod názvem ATPVBAEN.XLA), budeme moci používat tyto dodatečné funkce i v našich programech. Tedy například využít je při tvorbě našich vlastních funkcí. A to se vyplatí – řečeno s moderátory Naší Vaší Televize.

Možná by pomohlo nainstalování doplňku Analytické nástroje. Doplněk v podstatě obsahuje uživatelské funkce a je ho možné tedy považovat za praktickou ukázku postupů diskutovaných v tomto článku. Samozřejmě se jedná spíše o závěr cesty, jejíž začátek jsme si popsali. Funkce v tomto doplňku jsou totiž napsány v jazyku C, který je z hlediska zpracování mikroprocesorem optimalizován a tak jsou tyto Kde tyto doplňkové funkce nalezneme? Opět v nabídce funkce rychlejší než jejich případné protějšky v VBA. průvodce Vložit funkci. Tento doplněk se instaluje na pracovní stanici v průběhu instalace Excelu. Je třeba jej však ještě Pod kategorií Vlastní je založeno 5 aktivovat. K tomu musíme vynaložit dalších kategorií – určitou námahu, povzbudivé však je, že Analytické nástroje jsou • Inženýrská analýza aktivaci provedem pouze jednou a při jedním z desítek užitečných • Informační analýza dalším spuštění Excelu budou funkce doplňků, kterými můžeme • Matematická analýza doplňku k dispozici automaticky. • Finanční analýza rozšířit pracovní prostředí • Analýza času Dialogové okno s jehož pomocí Excelu můžeme přidávat nebo odebírat doplňky Tyto nové kategorie obsahují několik rozšiřující funkčnost Excelu je dostupné desítek specializovaných funkcí, které jsou ale v mnoha pomocí Nástroje > Doplňky… oborech zcela nepostradatelné. Pokusme se s nimi seznámit – Pokud jsme při instalaci Excelu nahráli na pevný disk i nebude to obtížné. Jsou totiž doplněny podrobnou doplněk Analytické nástroje, měl by se v tomto přehledu nápovědou, která je navíc přeložena do češtiny. zobrazit. Pro jeho aktivaci stačí označit políčko u jeho názvu a volbu potvrdit klávesou OK.

Pokud se nám v seznamu doplněk Analytické nástroje nezobrazí, můžeme se pokusit jej nalézt na pevném disku sami. V mé instalaci Excelu2000 se tento doplněk nachází ve složce C:\ProgramFiles\MicrosoftOffice\Office\Library\Analysis. Složku otevřeme v dialogovém okně dostupném z okna

Vytváření uživatelských funkcí pomocí editoru VBA výrazným způsobem rozšiřuje možnosti využití Excelu v automatizaci činností. Možnost zobrazit tyto funkce ve standardním dialogovém okně určeném pro zadávání vlastních funkcí Excelu znamená, že tyto funkce mohou používat i lidé, kteří nemají s programování ve VBA žádné zkušenosti. Je tedy možné bez investice do dalšího softwarového vybavení připravit takové prostředí Excelu, které v maximální míře usnadní vykonávání rutinních činností a zároveň sníží možnost vzniku chyb.

11

Pokud se naučíme vytvářet uživatelské funkce a distribuovat je ve formě doplňků add-in Excelu, můžeme snadným, časově a finančně nenáročným způsobem přizpůsobit pracovní prostředí našim uživatelům a spolupracovníkům. V případě nutnosti přepracovat některou z funkcí nebo sadu funkcí doplnit o nové, aktualizaci provedeme pouhým rozesláním jediného souboru. V některém z příštích čísel Excel Asistent Magazínu se budeme zabývat tématy souvisejícími s problematikou vytváření uživatelských funkcí Excelu. • • • • • •

Vytvoření vlastních kategorií pro uživatelské funkce. Distribuce vlastních funkcí pomocí doplňků Excelu. Lze doplnit vlastní funkce o složitější nápovědy? Vytváření funkcí s několika vstupními parametry. Vytváření funkcí s nepovinnými parametry. Využití doplňku Analytické nástroje při vytváření vlastních funkcí

12

Excel a praxe auditora - nevěřme křížovým součtům. Určeno: zkušený uživatel

Paracanthurus Hepatus Indický a Tichý oceán

V

mnoha modelech Excelu je mezivýsledkem nebo konečným výsledkem tabulka obsahující kromě součtů jednotlivých řádků a součtů za jednotlivé sloupce i buňku představující celkový součet dat. Tato buňka bývá označována jako „Celkový součet“, „Total“, „Grandtotal“ apod. Tento celkový součet bývá samozřejmě vnímán jako číslo, které dostaneme, pokud sečteme všechny celkové

součty řádků nebo všechny celkové součty sloupců. Bohužel tomu tak často nebývá. V převážné většině případů jsem se při studiu modelů setkával v buňce obsahující celkový součet se vzorci, které buď sčítaly jen výsledky za jednotlivé řádky, nebo pouze výsledky za jednotlivé sloupce. Kontrolní význam tohoto celkového součtu pak byl zcela pochybný. Praxe mi také potvrdila, že převážná většina uživatelů Excelu vzorec celkového součtu vytváří jako souhrn nad ním ležících buněk, tedy jako souhrn součtů za jednotlivé řádky. Zde se nejedná o chybu, ale spíše o preferenci. Znalost této skutečnosti nás pak v případě, kdy je naším úkolem ověřit si platnost součtů, vede k postupu, kdy kontrolujeme součet buněk obsahujících součty jednotlivých sloupců. Optimální však je vytvořit v buňce celkového součtu vzorec, který bude indikovat případnou diferenci mezi součtem sloupcových součtů a součtem řádkových součtů. Tvorba tohoto vzorce není nijak složitá, zvládne ji každý zkušenější uživatel Excelu. Je nutné si však uvědomit, že tento vzorec nevznikne prostým zkopírováním vzorců v buňkách obsahujících jednotlivé součty, což znamená, že musíme vynaložit o trochu více času, než při standardním (a chybném) postupu.

V čem spočívá přínos tohoto vzorce? Pokud dojde k situaci, kdy součtové vzorce jsou nekonzistentní (například vzorec pro součet řádku za měsíc březen neobsahuje příspěvek západní pobočky), buňka obsahující celkový součet bude indikovat chybu.

13

Vzhledem k tomu, že Excel obdobně jako všechny ostatní aplikace pracuje v binární a nikoli desítkové soustavě, může vzniknout „chyba“ způsobená interpretací hodnoty. Nejedná se o skutečnou chybu, ale o důsledek faktu, že mnohá desetinná čísla nelze v binární soustavě zapsat bez zaokrouhlení. Akceptovatelnou míru této nepřesnosti nastavíme ve vzorci parametrem, který má v našem příkladu hodnotu 0,01. Znamená to, že v našem příkladu akceptuji nepřesnost ve výši 1 haléř, což je jistě pro

náš příklad dostačující mez – neříkejme to ale pro jistotu zaměstnancům finančního úřadu. Přínos našeho vzorce není omezena pouze na situace, kdy může dojít k nesprávnému zápisu některého ze vzorců, na kterých závisí celkový součet. Stejně častou frekvenci výskytu vykazují i chyby vzniklé vědomým zaokrouhlováním. Tato chyba je však o to závažnější, že si jí velice často uživatelé nejsou vůbec vědomi, nebo dokonce ani nechápou její vznik a možné důsledky.

Mechanismus vzniku této chyby je následující: 1.

uživatel vytvoří dílčí sloupcové a řádkové sloupce

2.

v dalším kroku uživatel nahradí vzorce aktuálními hodnotami. Důvodem může být snaha zrychlit načítání souboru nebo jen zvyk či snaha zatajit vzorce v modelech, které předává jiným organizačním útvarům. Není vzácností, že tato transformace je neúplná – uživatel nahradí hodnotami vzorce buď jen sloupcových součtů nebo součtů řádkových, v případě rozsáhlejších přehledů může opominout krajní nebo několik krajních buněk. příjemce modelu změní přesnost zobrazení. Například hodnoty zobrazí v tisících nebo zaokrouhlí na jednotky. Tato úprava v kombinaci s předchozí úpravou může vést a často vede k nekonzistentnosti mezi souhrnem řádkových a sloupcových součtů – čili k chybě při stanovení křížového součtu. Dokonce může dojít k odlišnostem v součtech i z důvodů, které vůbec nesouvisí s formátováním dat. Jak již bylo zmíněno, Excel používá binární logiku, která nutně vede k tomu, že některá desetinná čísla nelze zobrazit konečným počtem mocnin čísla 2. Excel tato čísla aproximuje, neboli nahradí, čísly zapsatelnými ve dvojkové soustavě s nastavenou mírou přesnosti. Pokud takovéto číslo vznikne například jako součet některého řádku, dojde k situaci, kdy celkový součet řádkových součtů může být vzhledem k tomu, že obsahuje čísla pouze aproximativní, nutně rozdílný od celkového součtu jednotlivých sloupců. Pokud by však tento rozdíl byl větší než uživatelem nastavená mez přesnosti ve vzorci křížového součtu, Excel indikuje chybu.

3.

4.

Navržený vzorec určený ke stanovení křížového součtu, umožňující nastavení přijatelné diference a tedy stupně přesnosti dle charakteru dat, by měl patřit mezi postupy, které budeme ve svých modelech automaticky používat. Významným způsobem totiž zvýší přesnost a věrohodnost našich dat a výstupů z nich.

V každém z dalších čísel našich magazínů se budeme věnovat tématu bezpečného využívání Excelu a užitečných postupů při auditu souborů Excelu. Upozorním na chyby, kterých se uživatelé dopouštějí a které mohou vést k nesprávným závěrům a ke zkreslení dat. Zároveň se s vámi podělím o zkušenosti získané při ověřování konzistentnosti modelů Excelu, dohledávání chyb a návrzích změn struktury tabulek.

14

Zaokrouhlujete? A umíte zaokrouhlit na zadanou hodnotu? Určeno: zkušený uživatel

Je jí vlastní funkce MROUND, která vrací číslo zaokrouhlené na požadovaný násobek, jak nám prozradí nápověda v průzkumníku funkcí

Premnas Biaculeatus Tichý oceán, Andamanské moře

K

dyž pracujeme s účetními hodnotami, setkáme se často s požadavkem na zaokrouhlení částky k nějaké významné hodnotě. Nejčastěji to může být zaokrouhlení na „padesátník“. Obvyklým postupem je použití výrazu obsahujícího funkci ZAOKROUHLIT – například ve tvaru =ZAOKROUHLIT(A1/0,5;0)*0,5

Ve výrazu je hodnota uložená v buňce A1 vydělena nejdříve číslem 0,5 – které představuje částku, ke které zaokrouhlujeme ( 50 haléřů) – a pak je tato nová hodnota zaokrouhlena na číslo s nulovým počtem desetinných míst. Jako poslední krok vynásobíme zaokrouhlené číslo pomocnou hodnotou 0,5. Na tento postup se můžeme dívat tak, jako bychom původní částku zdvojnásobili ( a to je příjemná představa) a pak na našem novém bohatství provedli matematické zaokrouhlení na částku bez haléřů. Následným násobením číslem 0,5 se pak opět vrátím do reálného světa a nabyté bohatství zahodím. Pokud nemáme v oblibě desetinná čísla, můžeme ve výrazu nahradit pomocnou hodnotu 0,5 číslem 2. Samozřejmě ale musíme zaměnit i operace dělení a násobení – výsledný výraz pak vypadá takto: =ZAOKROUHLIT(A1*2;0)/2 Excel nám však jen výjimečně nabízí pouze jedinou cestu vedoucí k řešení našeho problému. I v tomto případě má ve svém arzenálu funkcí připravenou alternativu.

V našem případě, kdy je cílem zaokrouhlit částku na 50 haléřů, nastavíme druhý parametr funkce MROUND na hodnotu 0,5: =MROUND(A1;0,5)

Funkce MROUND je jednou z mnoha užitečných funkcí doplňkové sady analytických nástrojů Excelu, která se nazývá Analysis ToolPak ( v české verzi Analytické nástroje). Pokud chceme tuto funkci používat, musíme mít doplněk Analytické nástroje nainstalován. Zda tomu tak je, přesvědčíme se v dialogovém okně Doplňky, které lze zobrazit pomocí Nástroje > Doplňky. Podrobnější popis instalace a aktivace tohoto doplňku je k dispozici v článku věnovaném tvorbě vlastních funkcí.

Zaokrouhlování čísel patří mezi nejčastěji prováděné matematické operace nad daty v Excelu. V některém z příštích čísel naleznete podrobný a rozsáhlý článek, ve kterém se budeme věnovat této problematice. Ukážeme si nebezpečí skrytá

15

v postupech, které běžně – často automaticky a bez dohlédnutí možných negativních důsledků – používáme a podíváme se i „za oponu“ těchto postupů.

16

Kontingenční tabulky – vy je ještě nepoužíváte? 1/12 Určeno: zkušený uživatel

o čem hovořím. Na pozici programátorů bývají převážně lidé, kteří mají dostatečné znalosti (a také dostatečné oprávnění) nutné pro vytěžení dat z rozsáhlých databází. Bohužel ale obvykle nemají znalosti nutné pro interpretaci těchto dat. Často dokonce ani nejsou schopni zjistit, zda výsledný soubor dat – výsledek jejich práce – je kompletní či zda neobsahuje data, která jsou v rozporu se zadáním. Zadavatel, kterému jsou tato data určena, je jedinec obvykle vybavený protikladnými vlastnostmi – má znalosti a zkušenosti nutné pro analýzu a interpretaci Centropyge Bicolor dat, nedokáže se k nim však v provozním systému západní část Tichého oceánu dostat. Tato neschopnost nemusí nutně souviset s neznalostí postupů, často je příčinou pouze to, že není vybaven vhodnými nástroji a oprávněními. Ty si pro svoji potřebu ponechávají pouze programátoři. průběhu minulých deseti let mnohé firmy Argumentují podle nálady – někdy je „příčinou“ investovaly značné částky do rozvoje politika dodavatele, někdy vnitrofiremní licenční informačních technologií. Webové aplikace, bezpečnostní směrnice nebo nedostatek financí na intranet, komunikační software, systémy zvyšující Velké firmy samozřejmě dávají školení uživatelů. bezpečnost jsou nasazovány a obměňovány rychleji, prostor i lidem, kteří nedokáží ani data získat, ani je než se je mnozí uživatelé naučí plně ovládat. Často interpretovat – pro ty pak předchozí astronomické sumy jsou utráceny dvě skupiny na úkor svých činností za návrhy podnikových provozních Kontingenční tabulky jsou MIS neboli Manažerský vytvářejí manažerských systémů, Informační Systém. jedním z nejmocnějších a informačních systémů, systémů

V

podpory řízení klientských vztahů zároveň nejméně Seriál dvanácti článků, ve kterých (CRM), datových skladů. používaných nástrojů se vyznám ze svého obdivu ke Management firem si stále v našich počítačích. konceptu kontingenčních tabulek, výrazněji uvědomuje, že bohatství je určen těm, na jejichž straně společností, které řídí, souvisí stojím – uživatelům. Jsem totiž především s daty a informacemi uloženými že tímto nástrojem snímá Excel okovy přesvědčen, v databázích. I když se zpočátku zdá, že toto jejich závislosti na programátorech a správcích bohatství tkví v množství dat, začíná být stále databází. Pomocí kontingenčních tabulek lze zřetelnější, že významnější než množství dat je jejich překonat omezení 65536 záznamů. Právě například konzistence, věrohodnost, bezchybnost a zejména tento údaj bývá předkládán programátory jako možnost tato data dále zpracovat. absolutní důkaz nevhodnosti Excelu pro zpracování rozsáhlých databází. Jde však ale pouze o omezení Vysoce ceněnými jsou v každé firmě ti zaměstnanci, v hlavách argumentujících. Pomocí kontingenčních kteří dokáží z dat vytěžit pomocí filtrací, agregací, tabulek můžeme zpracovávat mnohem rozsáhlejší třídění či drilování informace. Začíná však více a více databáze. Osobně dávám přednost čekání na odezvu vyvstávat problém, spočívající v oddělení schopnosti Excelu při zpracování takovýchto souborů před data zpracovat a data interpretovat. čekáním na odezvu programátora, který dle mého zadání data připravuje. Vyplývá to z mých Komunikační bariéry uvnitř firem zkušeností, že Windows a Excel jsou stabilnějšími systémy než IT útvary velkých podniků. Záměrně píši Ve společnostech, které vzhledem ke své velikosti velkých podniků. U firem menších a středních nebo vzhledem k různorodosti vykonávaných činností obvykle role uživatele/IT nadšence a role oddělují správu dat a jejich interpretaci, vznikají ve programátora/správce databází splývá. Právě tito lidé stále vyšší míře komunikační bariéry. Jste-li v převážné většině doceňují možnosti kontingenčních specialista, který pro vstupy své práce potřebuje data tabulek, s jejichž pomocí mohou během několika z podnikového provozního systému a tato data málo minut analyzovat veškerá data jejich firem. získává přenesením požadavku na programátora, víte,

17

Vím podle své zkušenosti, že zkrocení kontingenčních tabulek vyžaduje nemalé úsilí, ale také vím, že odměna je tomuto úsilí úměrná. Získáte nástroj, s jehož pomocí vytěžíte z nesourodé skupiny dat vztahy, souvislosti, poměrové ukazatele, trendy. Objevíte extremní hodnoty, časové řady, vytvoříte souhrny dle jakýchkoliv kriterií. Drilování – neocenitelný pomocník při analýze dat Naprosto unikátní je schopnost drilovat data – pokud vás nějaký údaj zaujme, kliknete na buňku obsahující toto číslo a Excel založí nový list, do kterého zkopíruje všechny záznamy, které tento údaj vytvářejí. Pokud byste něco takového požadovali v provozním systému velké firmy, absolvujete několik jednání a možná budete nuceni předložit vedení návrh na schválení nové projektové aktivity, za kterou budete následně odpovědni. Pokud chcete data zobrazit názornou formou, kontingenční tabulka je pro vás zobrazí ve formě grafu, který se bude měnit okamžitě se změnou kriterií. Máte kriteria zobrazena v řádcích a myslíte si, že by působilo lépe umístit je do sloupců? Déle než deset vteřin vám tato změna trvat nebude.

Pokud vytrváte, prostudujete pozorně všech dvanáct pokračování a procvičíte si připravené příklady, budete umět kontingenční tabulky vytvářet, využívat jejich schopností, filtrovat a třídit údaje, které vracejí, vkládat do tabulek vlastní vzorce, nebo naopak výstupy z tabulek vkládat jako argument do dalších vzorců, tabulky programovat prostřednictvím VBA, propojovat je s daty podnikových databází. A pokud prostudujete i závěrečné kapitoly tohoto kurzu, naučíte se propojovat kontingenční tabulky s datovými sklady. Excel se totiž od verze XP stal plnokrevným OLAP klientem! A bonbónek na závěr? Co třeba kontingenční tabulka založená na XML souboru? I to je možné a také tomuto tématu se budeme v našich pokračováních věnovat. Je-li vaším cílem zpracovávat data kreativním způsobem, jsou kontingenční tabulky vaše volba.

18

Excel a vizualizace dat – podmíněné formátování. Určeno: zkušený uživatel

tabulkách zapisujeme větším tučným písmem. Ze stejného důvodu významné údaje podtrháváme, buňky, které tyto údaje obsahují, podbarvujeme nebo ohraničujeme různými typy čar. Tento přístup je jistě z hlediska orientace v záplavě údajů přínosný.

Naso Lituratus Indický oceán, Tichý oceán

Z

Kušení uživatelé vědí, že prezentace dat je pro výsledné pochopení velice důležitá. Zároveň lze jistě říci, že prezentace dat je značně subjektivní pojem – jiným způsobem se bude snažit svá data prezentovat marketingový specialista, jiným způsobem vědecký pracovník. Pro každého z nich však Excel nabízí nemalé množství nástrojů a postupů, kterými mohou zvýšit vypovídací schopnost čísel, která chtějí zobrazit.

Excel nám může v tomto ohledu významně ulehčit práci. Obsahuje totiž funkcionalitu nazývanou podmíněné formátování. S jeho pomocí lze zajistit plně automatické zvýrazňování údajů, které jsou průběžně měněny. Podmíněné formátování zdůrazní podle nastavených pravidel buňky obsahující data, která jsou z nějakého důvodu významná. Podrobný úvod do této problematiky naleznete v dokumentech • Excel Asistent Magazín č. 01/2004 Excel Asistent Magazín č. 02/2004 • Případně v článku dostupném na adrese http://www.pcsvet.cz/art/article.php?id=4591.

Pro potřeby tohoto článku z výše uvedeného zdroje cituji ( se svolením autora – dovolil jsem si sám Základním prostředkem, který slouží ke zvýšení sobě): přehlednosti dat, je formátování. Který z následujících zápisů téhož čísla vnímáte s vynaložením menší Podmíněné formátování Podmíněné formátování umožňuje pozornosti? zdůrazní podle nastavených nastavit grafické zvýraznění buněk, • čtyřistapadesáttřimiliony pravidel buňky obsahující které obsahují data vyžadující naši sedmsetšedesátpěttisíc zvýšenou pozornost. Nejčastěji data, která jsou z nějakého • 4,53765E+08 používáme tuto funkci pro nastavení • 453765000 důvodu významná. „alarmu“, který způsobí, že Excel • 453 765 000 automaticky zvýrazní data, jejichž Předpokládám, že se přikloníte ke rozsah. Toto hodnoty jsou mimo očekávaný čtvrté variantě zobrazení. Využívá místně obvyklého zvýraznění může být založeno na změně jednoho způsobu oddělování tisíců. Toto oddělování nemá nebo více následujících atributů: žádný vliv na vlastní hodnotu, má však zásadní vliv • barva, řez, styl, velikost písma na způsob, jakým číslo čteme, vnímáme a • podtržení nebo přeškrtnutí písma uchováváme v paměti. Formátování lze tedy • barva nebo vzorek výplně buňky považovat za jeden ze základních nástrojů vizualizace • typ čáry nebo barva vnějšího ohraničení buňky dat. Vizualizace dat – symboly a barvy Naše vnímání je však založeno nejen na symbolech – čili v případě čísel na symbolech deseti znaků reprezentujících číslice plus znaků mezery pro oddělení tisíců, čárky pro oddělení desetinné části, znaménka minus pro znázornění záporných hodnot. Výrazně vnímáme též velikost a sílu těchto znaků – proto například významné hodnoty v našich

Podmíněné formátování můžeme použít snad ve všech myslitelných úlohách, které pomocí Excelu řešíme: • v seznamu faktur vyznačit dosud neuhrazené • v seznamu denního hlášení o prodeji vyznačit výrazně podprůměrné ( např. červeným písmem) nebo nadprůměrné hodnoty (např. zelenou výplní buňky)

19

• • • • •

v seznamu zaměstnanců vyznačit ty zaměstnance, kteří v následujících 30ti dnech slaví narozeniny v seznamu pohledávek vyznačit různým odstínem výplně buněk pohledávky dle uplynulé doby po lhůtě splatnosti alternativně vybarvit řádky listu – a tím zvýšit přehlednost a čitelnost našich seznamů vyznačit klesající hodnoty časové řady ( např. objem prodeje) upozornit na chybně zadaný vstupní údaj

Mějme však na paměti, že podmíněné formátování je nejefektivnější tehdy, když je použito střídmě. Pokud je každá buňka našeho pracovního listu různě formátována, pak ve výsledku nepoznáme téměř nic. Podmíněné formátování má sloužit k identifikaci VÝJIMEČNÝCH hodnot, a těch by mělo být v našem seznamu pouze několik.

2. v menu Formát klepneme na položku Podmíněné formátování…

3.

v dialogovém okně zvolíme variantu Hodnota buňky, vybereme operátor je menší než a zapíšeme do posledního vstupního pole adresu =B3 ( nesmíme zapomenout na znak rovnítka)

4.

klepneme na tlačítko Formát...,v zobrazeném okně nastavíme červenou barvu písma a potvrdíme stiskem tlačítka OK 5. nastavení potvrdíme stiskem klávesy OK

V následujících příkladech se seznámíme se základními možnostmi využití funkce podmíněného formátování. Příklad 1 – zdůraznění klesajících tržeb

Tento příklad ilustruje způsob, jakým lze upozornit prostřednictvím podmíněného formátování na snížení hodnot ( zde ve významu poklesu tržeb prodejce v porovnání s předcházejícím rokem).

Příklad 2 – nastavení vlastního „automatického formátu“

Příklad ilustruje způsob, jakým můžeme nastavit formátování oblasti dat, které automaticky zobrazí: • ohraničení a tučné písmo v řádku, ve kterém první sloupec obsahuje slovo „Celkem“ • střídavé podbarvení řádků

1.

vybereme oblast buněk C3:D11 tak, aby buňka C3 byla aktivní

20

1.

vybereme oblast buněk A1:E17 tak, aby buňka A1 byla aktivní 2. v menu Formát klepneme na položku Podmíněné formátování…

8.

nastavení potvrdíme stiskem klávesy OK

Excel automaticky změní formátování řádků i v případě, že do oblasti A1:E17 vložíme nové řádky ( případně některé odstraníme). 3. v dialogovém okně zvolíme variantu Vzorec a do vstupního pole pro zadání vzorce zapíšeme =$A1=“Celkem“

4.

5.

klepneme na tlačítko Formát..., na kartě Ohraničení zvolíme typ čáry a ohraničení horního a dolního okraje buňky. Volbu potvrdíme stiskem tlačítka OK

klepneme na tlačítko Přidat>> pro nastavení 2. podmínky 6. v dialogovém okně zvolíme variantu Vzorec a do vstupního pole pro zadání vzorce zapíšeme =MOD(ŘÁDEK($A1);2)=0 7. klepneme na tlačítko Formát..., v zobrazeném okně nastavíme pozadí buňky světle modré a potvrdíme stiskem tlačítka OK

Příklad 3 – využití grafických prvků pro indikaci významné hodnoty

Následující příklad ukazuje způsob, jak založit znázornění dat na grafických prvcích uložených v jednotlivých prvcích znakové sady. Využijeme sadu písmen Symbol. Cílem je znázornit plnění plánu jednotlivými prodejci za časové období tří let. Za vizualizační symbol si zvolíme šipku směřující vzhůru pro případ, kdy prodejce plán splnil a analogicky šipku směřující dolů pro případ, kdy plán splněn nebyl.

21

Při řešení této úlohy postupujeme takto: 1. do buňky B25, která obsahuje porovnání skutečně dosaženého obratu a plánu prodejce Milana za rok 2001, zapíšeme vzorec =KDYŽ(B14>B3;ZNAK(173);KDYŽ(B14=B3; “-„;ZNAK(175))) 2. nastavíme v buňce B25 font písma Symbol – v této znakové sadě odpovídá kód 173 šipce směřující vzhůru a kód 175 šipce směřující dolů. Znak „ –„ zadáme pro indikaci situace, kdy prodejce splnil plán zcela přesně. 3. vzorec a formátování buňky B25 zkopírujeme i do ostatních buněk

4.

pomocí podmíněného formátování nastavíme barevné odlišení - nejdříve vybereme oblast buněk B25: D32 tak, aby buňka B25 byl aktivní. 5. zapíšeme podmínky do okna nastavení podmíněného formátování dle obrázku 6.

nastavení potvrdíme stiskem tlačítka OK

22

Excel a finance – vytvoření splátkového kalendáře. Určeno: zkušený uživatel

Coris Gaimard Indický oceán, Tichý oceán

O

blastí, ve které je Excel nejvíce využíván, je samozřejmě zpracování číselných údajů a zejména zpracování údajů finančních. Sekce „finanční“ průvodce vložením funkce ( viz článek „Funkce? Nejlepší přítel uživatele Excelu.“) patří počtem funkcí mezi nejrozsáhlejší.

Nenechte se bankou vodit za nos – vytvořte si vlastní splátkový kalendář. Každý z nás se občas dostane do situace, kdy přemýšlí o půjčce. Při zvažování, zda si půjčku pořídíme, zvažujeme mnoho okolností. Kolik peněz zaplatím navíc, jak dlouho budu splácet, jakou část z platby bude činit úrok a jakou část splácení jistiny? Pokud chceme znát odpovědi na tyto otázky, být připraveni na jednání v bance případně si překontrolovat návrh banky, Excel nám může poskytnout neocenitelné služby. Dejme tomu, že potřebujeme půjčit 40 000 Kč na dobu 60 měsíců. Banka nám poskytne úvěr s garantovanou úrokovou mírou 12%.

Pokud by nám některá speciální funkce v tomto výčtu chyběla, jistě ji nalezneme v sekci „finanční analýza“, která je součástí doplňku „Analytické nástroje“ – opět viz článek „Funkce? Nejlepší přítel uživatele Excelu.“ V tomto čísle Excel Asistent Magazínu.

Pokud bychom pouze potřebovali určit výši měsíční splátky, použili bychom funkci PLATBA – vypočítá platbu půjčky na základě konstantních plateb a konstantní úrokové sazby . .

Naším cílem je však vytvořit přehledný splátkový kalendář za celé období platby.

23

K řešení této úlohy využijeme další dvě funkce: • PLATBA.ÚROK – Počítá úrokovou platbu z investice založené na pravidelných stálých platbách a konstantní úrokové míře pro dané období. • PLATBA.ZÁKLAD – Vrátí hodnotu základní jistiny v anuitní splátce investice za dané období, vypočtenou na základě pravidelných konstantních splátek a konstantní úrokové sazby. Pomocí funkce PLATBA.ÚROK zjistíme, kolik peněz ze splátky připadá na splácení úroku a pomocí PLATBA.ZÁKLAD získáme informaci, kolik peněz ze splátky je určeno na hrazení jistiny.

Vzhledem k množství funkcí je možné řešení úlohy prostudovat v dokumentu Excelu, který si můžete stáhnou z adresy : http://www.dataspectrum.cz/excelmag/download/ea m0105x.zip

24

Funkce pro Excelovské kouzelníky – seznam unikátních hodnot. Určeno: expert

=POSUN(List1!$A$1;;;POČET2(List1!$A:$A);)

Amphiprion ocellaris Východní část Indického oceánu Vytvoření seznamu unikátních hodnot

S

tojíme před úkolem vytvořit seznam unikátních hodnot ze skupiny, ve které se některé hodnoty mohou opakovat. Tyto hodnoty máme navíc vybrat ze skupiny, do které můžeme hodnoty doplňovat, nebo je naopak odstraňovat. Jinými slovy, ve sloupci A máme uloženy následující hodnoty

Tato část vytváří expandující oblast obsahující hodnoty zapsané ve sloupci A pracovního listu List1

a b 1 b 3 Naším cílem je vytvořit funkci, která vrátí matici hodnot {„a“;“b“;1;3}. Pokud sloupec hodnot doplníme dalšími hodnotami a b 1 b 3 c 1 d a b být výsledná matice hodnot by měla {„a“;“b“;1;3;“c“;“d“}. Hledaný výraz nepatří mezi nejjednodušší, proto si jej rozdělíme na několik spolupracujících částí. Nejdříve budeme definovat pro první část výrazu název MujSeznam:

Dále definujeme výraz pomMatice =SMALL(KDYŽ(POZVYHLEDAT(MujSeznam; MujSeznam;0)=ŘÁDEK(MujSeznam);ŘÁDEK(Mu jSeznam);““);ŘÁDEK(NEPŘÍMÝ.ODKAZ(„1:“& SUMA(N(POZVYHLEDAT(MujSeznam;MujSezn am;0)=ŘÁDEK(MujSeznam))))))-1

25

Tento výraz obsahuje několik důležitých částí – pokusme se jejich funkci přiblížit: KDYŽ(POZVYHLEDAT(MujSeznam;MujSeznam ;0)=ŘÁDEK(MujSeznam);ŘÁDEK(MujSeznam);“ “) vrací matici obsahující pozice unikátních hodnot. Tato matice má stejný rozsah jako matice MujSeznam. Rozdíl mezi těmito dvěmi maticemi je však v tom, že původní opakující se hodnoty jsou nahrazeny prázdným řetězcem. ŘÁDEK(NEPŘÍMÝ.ODKAZ(„1:“&SUMA(N(PO ZVYHLEDAT(MujSeznam;MujSeznam;0)=ŘÁDE K(MujSeznam)))))) nám jako výsledek vrací matici čísel od 1 do n, kde n představuje počet unikátních hodnot vyskytujícíh se v seznamu. Tato hodnota je výsledkem výrazu SUMA(N(POZVYHLEDAT(MujSeznam;MujSezn am;0)=ŘÁDEK(MujSeznam))).

Část výrazu POSUN(MujSeznam;pomMatice;;1) je matice matic obsahujících jediný prvek. Tuto „vnořenou“ matici můžeme převést na matici obvyklého typu použitím funkce T nebo funkce N. V našem výrazu jsou použity funkce obě z důvodu, že náš seznam může obsahovat jak číselné, tak i textové hodnoty. A jak tuto složitou funkci využijeme? Zvolíme oblast buněk B1:B10, zapíšeme vzorec =UnikatniHodnoty a potvrdíme ho stiskem kombinace kláves Ctrl+Shift+Enter ( jedná se o maticový vzorec). V jednotlivých buňkách oblasti budou zapsáni reprezentanti hodnot původní oblasti – každá z hodnot pouze jednou. Pokud je náš maticový vzorec zapsán do oblasti obsahující více buněk než je unikátních hodnot, jsou tyto „nadbytečné“ buňky vyplněny chybovou hodnotou #N/A.

Naším cílem je získat matici, která bude obsahovat čísla reprezentující pozice unikátních hodnot bez prázdných řetězců. Toho dosáhneme použitím funkce SMALL, která má společně se svojí mezi funkcemi doplňkovou funkcí LARGE výsadní postavení. Jejich výlučnost spočívá ve schopnosti vytvářet matice s jinou velikostí než má matice předaná funkci jako vstup v 1. argumentu – za předpokladu, že i 2. argument funkce obsahuje matici. Záhadně může působit snížení výsledné hodnoty o jedničku. Část –1 slouží k adjustaci pozice prvku tak, aby bylo možné hodnotu pozice použít ve výrazech popsaných v dalším textu. Definujme výraz UnikatniHodnoty: =KDYŽ(T(POSUN(MujSeznam;pomMatice;;1))=“ “;N(POSUN(MujSeznam;pomMatice;;1));T(POSU N(MujSeznam;pomMatice;;1)))

Ačkoliv postupy využívané v našem komplexním výrazu jsou funkční, nedoporučuji je používat v situacích, kdy je třeba zpracovat rozsáhlé seznamy ( více než 1000 položek). V takovém případě bude Excel pro dokončení výpočtu potřebovat velmi dlouhou dobu. V případě rozsáhlých seznamů je s ohledem na rychlost odezvy vhodnější používat postup založený na filtru nebo funkce využití Pokročilého UNIQUEVALUES, která je součástí doplňku Morefunc.xll – viz další článek.

26

Excelentní Excel – nainstalujte si další nástroje 1/12 Určeno: expert

Nicméně stále mezi námi žijí i altruisté, kteří nemusí nutně zpeněžit každý výsledek své práce a jsou ochotni nabídnout i zcela mimořádné a obdivuhodné produkty zcela nezištně neznámým lidem.

Pygoplites diacanthus Indický oceán, Tichý oceán

V našem seriálu budu postupně prezentovat některé z těchto rozšiřujících doplňků, které považuji za natolik užitečné, že seznámení s nimi stojí za vynaložený čas a úsilí.

Morefunc.xll

P

řestože schopnosti Excelu jsou i v základní instalaci ohromující a poskytují množství nástrojů umožňující pokročilé zpracování rozsáhlých datových struktur, můžeme na internetu nalézt tisíce doplňků, které usnadňují uživatelům používání tohoto tabulkového kalkulátoru nebo přímo rozšiřují jeho schopnosti do oblastí, které tvůrci nepředpokládali.

Doplněk Morefunc.xll je dle mého názoru zcela mimořádný produkt mezi ostatními freewareovými programy rozšiřujícími schopnosti Excelu. Francouzský mistr Excelu Laurent Longre tento doplněk obsahující v současné době 41 funkcí vytváří, doplňuje a optimalizuje již od roku 1998.

Web obsahuje tisíce doplňků, které jejich autoři vytvořili s cílem rozšířit standardní schopnosti Excelu

Mnohé z těchto doplňků jsou dostupné ve formě freeware a jsou tedy k dispozici bez jakékoliv další finanční investice. Záměrně píšu finanční, protože určitou investici samozřejmě tyto nástroje vyžadují. Jedná se především o investici časovou, spočívající v nutnosti vynaložit určitý čas pro vyhledání těchto rozšiřujících doplňků a v čase vynaloženém na pochopení jeho možností a způsobu ovládání.

Často se jedná o doplňky, které autor nezamýšlel nabídnout k volnému využití případným zájemcům, ale vytvářel je především pro zjednodušení nebo zabezpečení činností, které sám v Excelu vykonává. Tyto doplňky pak sice v prostředí internetu zveřejnil, ale nedoplnil je ani popisem, ani nápovědou. Někdy však investoval svůj čas a energii a vytvořil pro potenciální zájemce podrobný popis případně ucelenou nápovědu. Samozřejmě i programátor musí platit účty, nájem a energii a proto se nelze divit, že za svoji práci žádá zaslouženou odměnu.

Doplněk si můžete stáhnout z domovské stránky autora http://xcell05.free.fr/ zcela bezplatně a zcela bez omezení jej můžete používat ve své práci (přesná adresa pro stažení souboru je http://xcell05.free.fr/downloads/

Morefunc.zip). Neumím si představit pracovní stanici s instalovaným Excelem, kde by alespoň jedna ze zmíněných 41 funkcí neusnadnila uživateli jeho práci. Doplněk je dostupný samozřejmě v mateřském jazyku autora, tedy ve francouzštině, ale je také lokalizován do angličtiny – včetně nápovědy. Čeština bohužel nepatří mezi nejrozšířenější jazyky, proto se vám pokusím některé z funkcí přiblížit v tomto článku na řešených příkladech. V následujícím přehledu naleznete stručný popis jednotlivých funkcí obsažených v doplňku:

ANSI128

Odstraní interpunkci z řetězce.

CHBASE

Konvertuje hodnotu z jedné číselné soustavy do jiné.

COUNTDIFF

Vrátí počet jednoznačných hodnot v oblasti nebo matici (funkce je určena pro dvourozměrnou oblast – buňky jednoho listu nebo prvky dvourozměrné matice).

COUNTIF.3D

Vrátí počet jednoznačných hodnot v trojrozměrné oblasti nebo matici (prohledává oblast zasahující více listů).

DATE.DIFF

Vrátí rozdíl dvojice datumů v určených jednotkách (rok, měsíc, den) – lze ji použít například pro určení věku.

EASTERDATE

Vrátí ve tvaru sériového čísla datum velikonoční neděle zadaného roku.

EVAL

Vyhodnocuje vzorec nebo výraz ve formátu řetězce a vrátí výsledek výpočtu.

FILENAME

Vrátí jméno aktivního sešitu.

FORMULATEXT

Vrátí ve formátu řetězce funkci zapsanou v buňce.

GEOMEAN.EXT

Vrátí geometrický průměr pole nebo oblasti kladných dat.

HSORT

Setřídí obsah oblasti buněk nebo matice (podporuje až 14 klíčů současně) – třídí „horizontálně“, tj. třídí sloupce.

HSORT.IDX

Setřídí index oblasti buněk nebo matice (podporuje až 14 klíčů současně) – třídí „horizontálně“, tj. třídí sloupce.

INDIRECT.EXT

Funguje obdobně jako NEPŘÍMÝ.ODKAZ – umožňuje však pracovat i s buňkami zavřeného sešitu.

INTVECTOR

Vrátí vzestupnou řadu (vektor) celých čísel.

ISO.WEEKNUM

Vrátí číslo týdne pro daný den odpovídající standardu ISO.

LASTROW

Vrátí obsah poslední vyplněné buňky zadaného sloupce nebo oblasti.

MATRIXROUND

upraví matici zaokrouhlených čísel (=> součet procentuálních hodnot = vždy 100%)

MCONCAT

Sloučí hodnoty oblasti buněk nebo matice.

MDETERM.EXT

Vrátí determinant matice, v porovnání s analogickou funkcí Excelu DETERMINANT počítá rychleji a podporuje rozsáhlejší matice.

MINVERSE.EXT

Vrátí inverzní matici, v porovnání s analogickou funkcí Excelu INVERZE počítá rychleji a podporuje rozsáhlejší matice.

MMAX

Vrátí N nejvyšších hodnot oblasti nebo matice.

MMIN

Vrátí N nejnižších hodnot oblasti nebo matice.

MMULT.EXT

Vrátí maticový součin dvou matic, v porovnání s analogickou funkcí Excelu SOUČIN.MATIC počítá rychleji a podporuje rozsáhlejší matice.

MRAND

Vrátí sadu náhodných celých čísel bez opakování.

NBTEXT

Převede hodnotu kladného čísla do slovního vyjádření (podporuje 13 jazyků).

PAGENUM

Vrátí číslo stránky zadané buňky.

RECALL

Vrátí předchozí hodnotu uloženou v buňce zadané odkazem (obsah buňky před posledním přepočtem).

SETV,GETV

Umožní použití dočasných proměnných ve funkcích listu.

SHEETNAME

Vrátí název listu aktivního sešitu.

STDEV.GROUPED

Vrátí odhad směrodatné odchylky základního souboru určený z náhodného výběru. V porovnání s analogickou funkcí Excelu SMODCH.VÝBĚR pracuje tato funkce na datech sdružených do tříd (hodnoty jsou nahrazeny frekvencí).

STDEVP.GROUPED

Vrátí směrodatnou odchylku základního souboru určenou z náhodného výběru. V porovnání s analogickou funkcí Excelu SMODCH pracuje tato funkce na datech sdružených do tříd (hodnoty jsou nahrazeny frekvencí).

THREED

Transponuje 3D oblast do 2D matice – umožňuje maticovým vzorcům a

standardním funkcím Excelu pracovat s 3D oblastmi. UNIQUEVALUES

Vrátí jednoznačné hodnoty oblasti buněk nebo matice.

VAR.GROUPED

Vrátí odhad rozptylu na základě zadaných výběrových hodnot. V porovnání s analogickou funkcí Excelu VAR.VÝBĚR pracuje tato funkce na datech sdružených do tříd (hodnoty jsou nahrazeny frekvencí).

VARP.GROUPED

Vrátí rozptyl hodnot v základním souboru, vypočtený z hodnot pro všechny jednotky tohoto souboru. V porovnání s analogickou funkcí Excelu VAR pracuje tato funkce na datech sdružených do tříd (hodnoty jsou nahrazeny frekvencí).

VSORT

Setřídí obsah oblasti buněk nebo matice (podporuje až 14 klíčů současně) – třídí „vertikálně“, tj. třídí řádky.

VSORT.IDX

Setřídí index oblasti buněk nebo matice (podporuje až 14 klíčů současně) – třídí „vertikálně“, tj. třídí řádky.

WEIGHTED.AVERAGE Vrátí vážený průměr. WORDCOUNT

Vrátí počet slov v textu.

WMID

Vyjme slovo nebo skupinu slov z textu.

Instalace doplňku Instalace doplňku je velice snadná – jde o zcela stejný postup, který použijeme v případě kteréhokoliv jiného excelovského doplňku. 1.

2.

3.

4.

5.

zkomprimovaný soubor morefunc.exe rozbalíme do jakéhokoliv námi určeného (například můžeme zadat adresáře C:/POMUCKY/MOREFUNC/) po rozbalení bude tento adresář obsahovat následující soubory: • Changelog.txt – přehled verzí doplňku • Morefunc.cnt – pomocný soubor nápovědy • Morefunc.hlp – nápověda programu • Morefunc.xll – vlastní aplikace • Readme.txt – vstupní informace V Excelu zobrazíme pomocí Nástroje > Doplňky dialogové okno pro instalaci doplňků (tato volba je dostupná pouze tehdy, když je založen alespoň jeden pracovní sešit) V tomto okně pomocí tlačítka Procházet zvolíme adresář, který obsahuje soubor Morefunc.xll a následně tento soubor vybereme Název souboru se objeví v seznamu dostupných doplňků

6.

Aktivujeme zaškrtávací políčko umístěné před názvem doplňku a volbu potvrdíme pomocí klávesy OK

7.

Pokud nyní otevřeme dialogové okno pro výběr funkce, zjistíme, že obsahuje novou skupinu funkcí s názvem Morefunc

29

Funkce COUNTDIFF Vrátí počet jednoznačných hodnot v oblasti nebo matici (funkce je určena pro dvourozměrnou oblast – buňky jednoho listu nebo prvky dvourozměrné matice).

8.

9.

Nyní máme doplněk nainstalován – dobrá zpráva je, že tento postup už nemusíme příště opakovat. Excel si ukládá seznam doplňků a cesty k nim do registrů Windows a při příštím otevření Excelu je automaticky aktivuje. Pokud bychom se rozhodli, že doplněk nepotřebujeme, je velice snadné jej odstranit. • Otevřeme dialogové okno Doplňky, zrušíme zaškrtnutí a změnu potvrdíme stiskem tlačítka OK. • Složku s doplňkem odstraníme, případně pouze přejmenujeme. • Nyní opět otevřeme dialogové okno Doplňky a znovu aktivujeme zaškrtávací políčko • Excel zobrazí zprávu, která nás upozorní, že doplněk nelze nalézt a zeptá se, zda jej chceme odstranit ze seznamu.

•

Potvrdíme stiskem tlačítka OK naše rozhodnutí doplněk ze seznamu odstranit.

K čemu se mi může tento doplněk hodit?

Syntaxe funkce je =COUNTDIFF(Array,Blanks,Exclude) • Array (oblast nebo matice) : může obsahovat jakýkoliv typ dat (čísla, text, prázdné buňky…). • Blanks (pravdivostní hodnota, nepovinný parametr) : určuje, zda funkce bude pracovat s prázdnými buňkami ( standardní nastavení: NEPRAVDA). • Exclude (hodnota, oblast buněk nebo matice, nepovinný parametr): hodnota nebo hodnoty, které funkce nebude do výsledku zahrnovat . Příklad 1

Sloupec A obsahuje pořadová čísla jednotlivých týdnů. Buňky sloupce B obsahují záznamy, které představují datum, kdy do firmy volal klient a hlásil poruchu. Pokud v jeden den volalo více klientů, je pro každého z nich zapsáno datum do nové buňky. Naším cílem je spočítat počet dní v daném týdnu, kdy alespoň jeden klient hlásil poruchu. Funkci Excelu COUNTIF v tomto případě nelze použít, protože jejím výsledkem by byl počet volajících klientů v daném týdnu.

Řešením je použití funkce COUNTDIFF – viz obrázek:

Záleží samozřejmě na způsobu a typu úloh, pro které Excel používáme. Nelze proto dát jednoznačný návod, kdy funkce doplňku využít a kdy zvolit alternativní řešení (pokud vůbec existuje). Na následujících příkladech si ukážeme možnosti, které nám nabízejí některé z funkcí. Berme však tyto příklady spíše jen jako nastínění typu úloh, které lze pomocí doplňku řešit.

Funkci zapíšeme do buňky E1 jako maticovou ( potvrdíme ji stiskem kláves Ctrl+Shift+Enter) ve tvaru

30

=COUNTDIFF(KDYŽ($A$2:$A$13=D2;$B$2:$ B$13;““))-1

•

a pak ji zkopírujeme i do buněk E2:E3

Odkaz na trojrozměrnou oblast může zahrnovat i externí oblast (‚[MujSešit.xls]List1:List8‘!A1:C30), ale zdrojový soubor musí být otevřený. Velikost oblasti je omezena na 65536 položek, tj. 3D oblast nemůže obsahovat více než 65536 buněk.

Funkci COUNTDIFF stejně jako ostatní funkce doplňku můžeme použít i přímo v našich kódech VBA. Pokud bychom například chtěli zjistit počet jednotlivých týdnů ze sloupce A – vypadal by náš kód takto: Sub PocetJednotlivychTydnu() Unique = Run([COUNTDIFF],Range(„A2:A13“)) End Sub

Funkce COUNTIF.3D Vrátí počet jednoznačných hodnot v trojrozměrné oblasti nebo matici (prohledává oblast zasahující více listů).

Syntaxe funkce je =COUNTIF.3D(3D range,Criteria) • •

3D range : jakákoliv platná trojrozměrná oblast, jako například List1:List8!A1:C30. Criteria (řetezec nebo pole řetězců) : vyhledávací kriterium nebo kritéria.

Odkaz na trojrozměrnou oblast může zahrnovat i externí oblast (‚[MujSešit.xls]List1:List8‘!A1:C30), ale zdrojový soubor musí být otevřený. Velikost oblasti je omezena na 65536 položek, tj. 3D oblast nemůže obsahovat více než 65536 buněk. Pokud je parametr Criteria zadáván ve formě matice, musí být i funkce zapsána ve formě maticového vzorce. Příklad 2

Potřebuji určit počet listů ( listy „leden“ až „prosinec“), které obsahují v určité buňce ( buňka K10) hodnotu větší než 0. Řešením je použít vzorec =COUNTIF.3D(leden:prosinec!K10;“>0“)

3D range : jakákoliv platná trojrozměrná oblast, jako například List1:List8!A1:C30.

THREED(List1:List8!A1:C30) načte obsah každé oblasti (List1!A1:C30 ... List8!A1:C30, tedy 8 oblastí v tomto případě) a vytvoří jediné pole hodnot. Toto pole začíná prvním řádkem první oblasti a končí posledním řádkem poslední oblasti. {=THREED(List1:List3!A1:C3)} vrací například pole 9 řádků x 3 sloupce: [List1!A1] [List1!A2] [List1!A3] [List2!A1] [List2!A2] [List2!A3] [List3!A1] [List3!A2] [List3!A3]

[List1!B1] [List1!B2] [List1!B3] [List2!B1] [List2!B2] [List2!B3] [List3!B1] [List3!B2] [List3!B3]

[List1!C1] [List1!C2] [List1!C3] [List2!C1] [List2!C2] [List2!C3] [List3!C1] [List3!C2] [List3!C3]

V tomto poli jsou pro obsah první oblasti (List1!A1:C3) vyhrazeny první 3 řádky, druhá oblast (List2!A1:C3) je zapsána do řádků 4 – 6 a třetí oblasti patří řádky 7-9. Funkce tedy v podstatě pracuje tak, jako by kopírovala jednotlivé oblasti pod sebe do nového listu . Podstatné však je, že toto pole je dynamické – obsah se mění podle změny obsahu původních oblastí, velikost pole se mění podle toho, zda jsou oblastech přidávány nebo v původních odstraňovány listy mezi hranicemi oblasti ( tedy mezi List1 a List3). Pole, které funkce THREED vrací jako svůj výstup, můžeme použít ve formě vstupního argumentu jiné maticové funkce nebo dokonce i vlastní uživatelské funkce VBA. Příklad 3

Funkce THREED Transponuje 3D oblast do 2D matice – umožňuje maticovým vzorcům a standardním funkcím Excelu pracovat s 3D oblastmi.

Syntaxe funkce je {=THREED(3D range)}

{=SUMA((THREED(List1:List5!A1:A200)>50)* (THREED(List1:List55!B1:B200)=“A“))} vrací počet řádků v oblasti List1:List5!A1:A200, ve kterých je hodnota uložená v buňkách sloupce A větší než 50 a buňky sloupce B obsahují hodnotu „A“. Příklad 4

31

=COUNTDIFF(THREED(List1:List5!A1:B100)) vrací počet unikátních hodnot v oblasti List1:List5!A1:B100 Příklad 5

Listy List2, List3, List4 obsahují ve sloupci A identifikační znak výrobku a sloupec B obsahuje průměr výrobku v milimetrech. Cílem je vytvořit funkci, která na základě vložené identifikační hodnoty dohledá příslušnou hodnotu průměru.

Vzorec zapíšeme ve tvaru =SOUČIN.SKALÁRNÍ(-(THREED(týden1:týden4!A1:A100)=G4);THRE ED(týden1:týden4!B1:B100)) Vzorec vznikne kombinací funkce SVYHLEDAT ( používáme v 2D prostředí – tedy vyhledávání v rámci jediného listu) a funkce THREED, která umožní funkci SVYHLEDAT procházet více listů. =SVYHLEDAT(„G“;THREED(List2:List4!A1: B3);2;0)

• • •

Funkce SVYHLEDAT nalezne hodnotu z 2.sloupce oblastí A1:B3 ve všech třech listech. Parametr 0 určuje, že funkce má vyhledat přesnou hodnotu – hledáme tedy znak „G“) Příklad 6

Do listů pracovního sešitu, které představují jednotlivé týdny, zaznamenávám odpracované hodiny v těchto týdnech jednotlivými zaměstnanci. Tyto seznamy se liší počtem řádků i pořadím, ve kterém jsou zaměstnanci do seznamu zapisováni. Potřebuji sečíst odpracované hodiny jednotlivých zaměstnanců – jak tuto úlohu vyřešit, když například záznam zaměstnance Karla je v seznamu za některý týden na řádku 5, v jiném týdnu na řádku 12 a v dalším týdnu není vůbec založen?

oblast A1:A100 představuje oblast jmen zaměstnanců (pokud některé z řádků vyplněny nejsou, není to hlediska funkčnosti problém) G4 obsahuje jméno zaměstnance, pro kterého hledáme celkový součet odpracovaných hodin Zdvojený unární operátor umístěný mezi funkcemi SOUČIN.SKALÁRNÍ a THREED nesouvisí s použitím funkce THREED, ale spíše s použitím funkce SOUČIN.SKALÁRNÍ. Jeho úkolem je převést logické hodnoty PRAVDA / NEPRAVDA, které jsou výsledkem výrazu (týden1:týden4!A1:A100)=G4 na hodnoty 1 nebo 0, které lze použít jako vstupní parametr funkce SOUČIN.SKALÁRNÍ. .

Funkce VSORT Setřídí obsah oblasti buněk nebo matice (podporuje až 14 klíčů současně) – třídí „vertikálně“, tj. třídí řádky.

Syntaxe funkce je {=VSORT(Array,Key 1,Order 1,Key 2,Order 2,...,Key 14,Order 14)} •

Array: matice hodnot, které chceme setřídit. Může se jednat o odkaz na oblast buněk (A1:F1500), matici vypočtených hodnot (A1:F500+H1:M500) nebo matici konstant ({12,5;4,2;23,4}).

32

•

•

• •

Key 1 (oblast nebo matice, nepovinný parametr): první klíč třídění nebo násobný klíč třídění. Pokud není tento parametr zadán, funkce třídí podle všech sloupců. Order 1 (číslo nebo matice, nepovinný parametr): určuje směr třídění – vzestupné ( hodnota 1) nebo sestupné ( hodnota 0, standardní nastavení). Pokud není zadán parametr Key 1, určuje parametr Order 1 směr třídění v rámci celé matice. Key 2 ... Key 14 (nepovinný parametr): druhý až čtrnáctý třídící klíč. Order 2 ... Order 14 (nepovinný parametr): směr třídění klíče 2 – klíče 14.

Příklad 7

Na dalším obrázku vidíme část databáze s 15 záznamy v 6 sloupcích. Pokud bychom potřebovali záznamy seřadit, Excel nám umožní v jednom kroku setřídit data pouze podle 3 kriterií. Samozřejmě toto omezení neznamená, že v Excelu nemůžeme data třídit podle více než tří kriterií – musíme ale třídění opakovat ve více krocích. Funkce VSORT pro třídění záznamů ve směru sloupců ( a HSORT pro použití ve směru řádků) přednastavené možnosti Excelu výrazně překračují – umožňují třídit až podle 14 kriterií.

Pokud bychom potřebovali setřídit databázi tak, že například 5. sloupec bude tříděn SESTUPNĚ, bude náš vzorec vypadat takto: =VSORT(A1:F15;A1:D15;1;E1:E15;0;F1:F15;1)

Stejný výsledek můžeme získaz i pomocí vzorce obsahujícího maticovou konstantu =VSORT(A1:F15;A1:F15;{1;1;1;1;0;1}) (poznámka: složené závorky uvnitř vzorce zapisujeme pomocí klávesnice) Tento zápis je podle mého názoru velice přehledný, v maticové konstantě můžeme „žonglovat“ s hodnotami 1 a 0 a tím nastavovat směr třídění podle potřeby. Předpokládejme, že tyto záznamy požadujeme vzestupně setřídit podle všech 7 sloupců. • • •

Označíme oblast, která bude obsahovat setříděný seznam, např. H1:M15. Zapíšeme maticový vzorec ve tvaru =VSORT(A1:F15;A1:F15;1) Protože se jedná o maticový vzorec, potvrdíme jej pomocí Ctrl+Shift+Enter

Potřebujeme-li například setřídit sloupce střídavě, to znamená 1. sloupec vzestupně, 2. sloupec sestupně, 3.sloupec vzestupně …, zapíšeme vzorec takto =VSORT(A1:F15;A1:F15;{1;0;1;0;1;0})

33

Vzorec můžeme i dále zjednodušit – pokud se první i druhý parametr funkce neliší ( zde A1:F15), nemusíme jej opakovat, ale prostě jeho druhou instanci vynecháme =VSORT(A1:F15;;{1;0;1;0;1;0})

34

Zaokrouhlení čísla na pevný počet platných číslic Určeno: zkušený uživatel

pomocí Nástroje > Doplňky. Podrobnější popis instalace a aktivace tohoto doplňku je k dispozici v článku věnovaném tvorbě vlastních funkcí.

Námi vytvořená funkce má následující tvar: =MROUND(B2;KDYŽ(HODNOTA(ZPRAVA(B2 /10^(CELÁ.ČÁST(LOGZ(ABS(B2)))B3+1);2))=0,5;2;1)*SIGN(B2)*10^(CELÁ.ČÁST( LOGZ(ABS(B2)))-B3+1)) kde B2 je adresa buňky obsahující původní hodnotu a B3 je adresa buňky s hodnotou určující počet platných číslic výsledku. Pomacanthus semicirculatus Indický oceán

Z

obrazení hodnot a výpočty založené na pevném počtu platných číslic jsou techniky rozšířené především v přírodních vědách. V ekonomii tyto postupy nejsou obvyklé, což ale neznamená, že je nelze používat. Excel však není vybaven žádnou funkcí nebo příkazem, která by uživateli umožňovala čísla Nicméně zaokrouhlovat tímto způsobem. kombinací dostupných funkcí můžeme i bez znalosti programování v VBA tuto funkci vytvořit. Které funkce použijeme? • ABS – vrátí absolutní hodnotu čísla • LOGZ – vrátí logaritmus čísla při zadaném základu • CELÁ.ČÁST – zaokrouhlí číslo dolů na nejbližší celé číslo • SIGN – vrátí znaménko argumentu. Vrátí hodnotu 1, pokud je číslo kladné, hodnotu 0 pro číslo 0 a hodnotu –1, pokud je číslo záporné. • ZPRAVA – vrátí poslední znaky řetězce • HODNOTA – převede textový řetězec představující číslo na číslo • MROUND – vrátí číslo zaokrouhlené na požadovaný násobek Tato poslední funkce je jednou z mnoha užitečných funkcí doplňkové sady analytických nástrojů Excelu, která se nazývá Analysis ToolPak ( v české verzi Analytické nástroje). Pokud chceme tuto funkci používat, musíme mít doplněk Analytické nástroje nainstalován. Zda tomu tak je, přesvědčíme se v dialogovém okně Doplňky, které lze zobrazit

Pokud změníme hodnotu v buňce B3, získáme výsledek s příslušnou přesností:

35

Zobrazení funkcí a vzorců. Určeno: zkušený uživatel

Acanthurus leucocheilos Indický oceán

Toto vydání magazínu se z převážné části věnuje tématu funkcí. Funkce jsou nástrojem, který snad každý uživatel Excelu ve své práci využije. Pokud nastavená paleta vestavěných funkcí nestačí, vždy můžeme Excel doplnit pomocí programu VBA o funkce, které řeší námi zpracovávanou agendu.

Na stejné kartě nalezneme i zaškrtávací políčko „Vzorce“, jehož aktivace změní zobrazení všech buněk obsahujících funkce. Buňky nebudou zobrazovat výsledné hodnoty, zobrazí zapsané funkce přímo v konkrétní buňce. Tato změna zobrazení se vztahuje na aktivní sešit, v ostatních sešitech zůstává platné původní nastavení.

Buňky obecně mohou obsahovat buď funkci nebo konstantu. Samozřejmě mohou být i prázdné, což je zřejmě nejčastější případ, nicméně do buněk nelze mimo zmíněné dva objekty (funkce, konstanta) vkládat nic jiného. Standardní možnosti Excelu Pokud buňka obsahuje funkci, nelze ji na první pohled odlišit od buňky s konstantou. Je to způsobeno standardním chováním Excelu, který zobrazuje v buňce výslednou hodnotu funkce. Pro zobrazení zápisu funkce je určen řádek vzorců, který lze zobrazit nebo naopak skrýt pomocí zaškrtávacího políčka „řádek vzorců“, dostupného pomocí Nástroje > Možnosti > karta Zobrazení.

Pomocí tohoto nastavení můžeme pro potřeby kontroly tisknout listy i se zobrazením jednotlivých funkcí a vzorců. Nevýhodou tohoto přístupu však je to, že změna zobrazení platí pro všechny buňky sešitu – nemůžeme změnit zobrazení pouze v námi zvolené oblasti buněk.

36

Stejné změny zobrazení můžeme dosáhnout i pomocí stisku kombinace kláves Ctrl+` Znak ` nalezneme nad klávesou tabelátoru. Je však dostupný pouze z anglické klávesnice, bude tedy zřejmě nutné nejdříve změnit českou klávesnici na anglickou – tuto změnu obvykle provádíme stiskem kláves Alt+Shift.

6.

Zobrazení funkce zapsané v jednotlivé buňce Vidíme, že možnosti Excelu v zobrazení funkcí jsou do značné míry omezené. Mimořádně užitečnou vlastností by byla schopnost zobrazit • Funkce a vzorce ve vybraných buňkách. • Zobrazit zároveň funkci i výslednou hodnotu. Třebaže Excel toto ve standardním nastavení nedokáže, můžeme si potřebnou funkcionalitu naprogramovat pomocí VBA:

Pokud buňka, na kterou se naše funkce odkazuje, obsahuje konstantu, bude výsledkem tato konstanta. Náš kód můžeme změnit tak, aby v tomto případě funkce nic nezobrazila (přesněji řečeno zobrazila prázdný řetězec)

Function CellFormula(c) As String If c.HasFormula Then CellFormula = c.FormulaLocal Else CellFormula = "" End If End Function

Zobrazení funkce v komentáři buňky 1. 2.

otevřeme editor VBA – stiskneme Alt+F11 založíme nový modul

Další užitečnou možností je zobrazení zápisu funkce v komentáři buňky. Osobně dávám tomuto způsobu přednost, protože mi umožňuje současné zobrazení jak výsledné hodnoty, tak i vzorce na jednom místě. Opět se jedná o proceduru (tentokrát tedy nikoliv funkci) zapsanou pomocí VBA:

3.

zapíšeme kód funkce

1. 2.

otevřeme editor VBA – stiskneme Alt+F11 založíme nový modul

3.

zapíšeme kód procedury

Function CellFormula(c) as String CellFormula = c.FormulaLocal End Function

4. 5.

opět aktivujeme pracovní prostředí Excelu – stiskneme Alt+F11 pokud nyní do některé buňky zapíšeme funkci =CellFormula s odkazem na buňku obsahující vzorec, výsledkem bude text v podobě tohoto vzorce – v následující ukázce jsme do buňky D1 zapsali vzorec =CellFormula(C1)

37

Sub VzorecDoKomentáře() Dim cell As Range On Error Resume Next Selection.ClearComments On Error GoTo 0 For Each cell In Intersect(Selection, ActiveSheet.UsedRange) If cell.Formula <> "" Then cell.AddComment cell.Comment.Visible = False 'potlačí chybu v případě chybně zapsaného vzorce On Error Resume Next cell.Comment.Text Text:=cell.Address(0, 0) & _ " hodnota: " & cell.Value & Chr(10) & _ " formát: " & cell.NumberFormat & Chr(10) & _ " vzorec: " & cell.Formula & Chr(10) & _ " vzorec: " & cell.FormulaLocal On Error GoTo 0 End If Next cell End Sub

4.

5.

Aktivujeme oblast buněk obsahující vzorce

stiskem kláves Alt+F8 otevřeme dialogové okno pro spouštění maker, vybereme v nabídce makro s názvem „VzorecDoKomentáře“ a spustíme jej stiskem tlačítka „Spustit“

Samozřejmě, že makro si můžeme upravit podle svých potřeb – například zápis syntaxe vzorce v angličtině není zřejmě ve většině případů nezbytný. Měli bychom ale mít na paměti, že zápis vzorců v češtině je pro náš kód zejména v případě velkého počtu buněk časově značně náročný – VBA totiž není lokalizován a pracuje pouze s anglickými ekvivalenty funkcí. I když v našem pracovním listě máme funkce zapsané v české verzi, pro potřeby makra jsou převedeny do angličtiny a pak opět překládány do české podoby. Tento postup je z hlediska času značně náročný.

Existuje mnoho dalších postupů verifikace a identifikace, ověřování, vyhodnocování vzorců. Tyto postupy spadají spíše do oblasti auditních technik než do výbavy běžného uživatele. V příštích číslech magazínu se s nimi setkáme – umožňují totiž provádět velice účinnou sebekontrolu a tím k vyšší věrohodnosti našich přispívají excelovských modelů. Závěrem bych chtěl připomenout jednu z nejméně používaných funkcionalit, která umožňuje mimo jiné identifikovat buňky obsahující funkce. Jde o příkaz „Přejít na…“, který je dostupný stiskem klávesy F5, případně pomocí Úpravy > Přejít na… V obou případech se nám zobrazí dialogové okno, ve kterém stiskneme tlačítko Jinak… Tím zobrazíme okno pomocí kterého lze ve vybrané oblasti aktivovat pouze buňky obsahující zadaný obsah ( konstanty, vzorce, podmíněné formátování apod.).

6.

komentáře buněk nyní obsahují informaci o: • výsledné hodnotě • formátu • syntaxi vzorce v angličtině • syntaxi vzorce v češtině Více

informací

naleznete

v souboru:

http://www.dataspectrum.cz/excelmag/download/ea m0203w.zip

Co naleznete v příštím pokračování • • • • •

Podrobné seznámení s funkcí SOUČIN.SKALÁRNÍ Vizualizace dat – prezentujme svá data pomocí grafických objektů Kontingenční tabulky – základní pojmy Excel a praxe auditora – vyhledávání chyb VBA – pomůcka pro navrhování vlastních menu

Excel Asistent Magazín PREMIUM

http://www.dataspectrum.cz/excelmagprem/eamp_main.html

Archív všech dosud publikovaných čísel Excel Asistent Magazínu

http://www.dataspectrum.cz/excelmag/excelmagmain.htm

Škola Excelu – neustále se rozšiřující soubor řešených příkladů

http://www.dataspectrum.cz/pages/learning/learningmain.htm

ExcelAsistent - více než 100 funkcí pro efektivní práci v Excelu

http://www.dataspectrum.cz/pages/software/softwaremain.htm

Máte dotaz týkající se práce v prostředí Microsoft Excel?

mailto:[email protected]

Staňte se členem elektronické konference věnované výhradně Excelu

http://www.pandora.cz/conference/excel

Na webové stránce http://www.dataspectrum.cz naleznete objednávkový formulář – zabezpečte si dalších 12 čísel magazínu Excel Asistent Magazín PREMIUM. Každé z nich Vám přinese minimálně o 10 stran informací více než předchozí bezplatné číslo Excel Asistent Magazínu. Copyright © 2003 - 2005 Jiří Číhař, Dataspectrum Jiří Číhař, Dataspectrum (http:/www.dataspectrum.cz)