Jdi na obsah Jdi na menu
 


Není vzorec jako vzorec....

31. 5. 2009

Autor: Ing. Vladimír Hajdovský
Aktualizováno 9.února 2010:  Autor článku přidal přílohu viz. konec článku


Vzorce na pracovních listech tabulkových procesorů jsou základním prostředkem pro vyjádření vztahů mezi jednotlivými buňkami na nich. Vzorec na listu zásadně a vždy určuje hodnotu své hostitelské buňky; nikdy nemůže aktivně změnit hodnotu v jiné buňce. Tato jednosměrnost chování vzorců je na jedné straně významným omezujícím faktorem chování vzorců, na druhé straně jim propůjčuje mimořádnou efektivitu při využití jejich schopností.

 

Pomocné buňky a supervzorce.

 

Vzorce v MS Excelu mohou dosahovat až neskutečných délek. K těmto délkám se často propracujeme tím, že sloučíme dílčí výpočty, prováděné v pomocných buňkách, do výsledného „supervzorce“. Technika pomocných buněk (případně celých pomocných řad) umožňuje rozepsat složitý výpočet do postupných, zpravidla dobře srozumitelných kroků. Výsledný vzorec pak využívá těchto mezivýsledků formou odkazů na pomocné buňky.
Po pravdě řečeno, nikdy jsem moc nepochopil, proč je odbornou veřejností takový postup pokládán za méně kulturní a proč je za kulturnější pokládán postup, kdy jsou do výsledného vzorce místo mezivýsledků dosazeny jejich zdrojové vzorce. O co méně se výsledný supervzorec odkazuje na okolí, o to monstróznějšího a nesrozumitelnějšího tvaru nabývá.
S pojmem supervzorec jsem se poprvé potkal u J.Walkenbacha, který se zdá být jejich velkým zastáncem a propagátorem. Ke cti Excelu musím přiznat, že supervzorce fungují naprosto spolehlivě i při délce mnoha set znaků. Katastrofický scénář však může připravit situace, kdy potřebujeme v supervzorci provést úpravu. Byl jsem nejednou svědkem toho, že praktická nerozluštitelnost a neopravitelnost rafinovaně konstruovaných supervzorců znamenala konec velmi pěkné excelské aplikace v okamžiku, kdy se změnily vnější podmínky pro úlohu. Původní autor už nebyl k dosažení a jeho nástupci nedokázali jím vytvořené supervzorce rozluštit, natož upravit. Až by se chtělo napsat novou murphyovskou poučku: „Cesta k nepostradatelnosti vede přes supervzorce“. Nikoliv Excel, ale člověk v konfrontaci se supervzorci selhává.

 

Porovnání použití supervzorců s použitím pomocných řad.

 

Každý tabulkový procesor je v první řadě určen pro maximálně pohotové vyhodnocování hodnot a vztahů mezi nimi. Pokud slouží jako jednorázový vyhodnocovací prostředek, pokládám pomocné buňky za zcela prioritní pomůcku pro řešení problémů kolem složitějších výpočtů. Jejich využití zpravidla velmi dobře odpovídá analytickému rozkladu problému při cestě za výsledkem. Pro opakované použití (a zejména při přípravě úloh pro třetí strany) se ovšem mohou pomocné sloupce a řádky opticky jevit jako nemístný balast. V tomto případě bych před jejich likvidací pomocí supervzorců dal přednost technice skrytí pomocných řad. Všichni moudří designéři excelských úloh vám potvrdí, že listy, na nichž se nacházejí vzorce, mají být zamčené a otevřené na nich mají zůstat pouze oblasti, určené pro manuální změny dat. Pokud na listu s pomocnými řadami před jeho uzamčením pomocné řady skryjeme, pak se velmi jednoduše „vlk nažere a koza zůstane celá“. Na zamčeném listu se skrytými pomocnými řadami nejenom tyto řady vizuálně neruší, ale uživatel se k nim jednoduše ani nedostane.
Jednou ze základních vlastností každé softwarové aplikace by měla být její snadná udržovatelnost. Praxe ukazuje, že supervzorce jsou jednou z nejhůře udržovatelných komponent spreadsheetové aplikace. Čtvrt století jsem velel softwarovým projekčním týmům a troufnu si říct, že nerozluštitelné kousky softwaru v dílech odcházejících programátorů měly vždy povahu časovaných min, vybuchujících zásadně v těch nejvypjatějších a nejméně vhodných okamžicích. Supervzorce mezi takové miny řadím a na rozdíl od Mr.Spreadsheeta (jak se Johnovi přezdívá) je doporučit nemohu!

 

Základní role a postavení názvů.

 

Tentýž John Walkenbach, který přisuzuje supervzorcům vysoký význam, se ještě s větší vahou věnuje ve svých manuálech názvům. Už to, že se těmto objektům běžně říká „pojmenované oblasti“, svědčí o jejich zúženém chápání mezi excelskou komunitou. Fakt, že se názvů používá zejména k pojmenování statických oblastí buněk, ještě neznamená, že je to jediné možné využití této struktury. Ocituji zcela zásadní slova zmíněného autora:
„Když vytvoříte NÁZEV pro buňku nebo oblast buněk Excelu, vytváříte vlastně POJMENOVANÝ VZOREC – vzorec, který není umístěn v buňce, ale v paměti Excelu.“
Uvedená definice má pro roli názvu v Excelu vyšší filozofickou i praktickou hodnotu, než by se na první pohled zdálo. Podstatné je zejména to, že název definuje v první řadě vzorec, přičemž jeho přímé přiřazení určité oblasti buněk je jenom nejběžnější (nikoliv však jedinou) možností využití názvu. V tomto okamžiku jsem vlastně překročil neviditelnou čáru, kdy přestávám vidět název optikou vybrané oblasti (tj. jako pojmenovanou oblast) a začínám ho chápat jako samostatnou datovou strukturu v rámci Excelu. Tento skrytý potenciál názvů jsem dlouho jen pasivně registroval, aniž bych si uvědomoval možnosti z toho plynoucí.

I když zatím setrváme u myšlenky, že název nepředstavuje víc než vzorec s odkazem na oblast buněk, nemusí jít nutně o staticky definovanou oblast pomocí absolutních mezí, jako je např. název Výřez, definovaný jako

=List1!$C$3:$E$10

Máme-li například ve sloupci A postupně vznikající seznam hodnot s hlavičkou v buňce A1, můžeme snadno vytvořit název se vzorcem, který bude dynamicky reagovat na změnu rozsahu našeho seznamu. Název Seznam může obsahovat následující odkaz:

=List1!$A$2:$A$&POČET2(A:A)

Měnící se počet použitých buněk ve sloupci A bude sám dynamicky měnit velikost té oblasti, na kterou se odkazuje název Seznam.
Před malou chvílí jsem napsal, že název představuje pojmenovaný vzorec, nikoliv jen odkaz na oblast buněk. Znamená to tedy, že v názvu může být umístěna i hodnota (jako výsledek vyhodnocení vzorce)? Zkusme to a do názvu PočetNoh vložme „vzorec“

=4

Do libovolné buňky zadejme další vzorec =6*PočetNoh. A opravdu, v naší buňce vznikne hodnota 24. Poprvé jsme svědky toho, že název není pojmenovanou oblastí, ale standardním vzorcem. V experimentech můžeme pokročit dál. Definujme název Den pomocí vzorce

=DNES()+ŘÁDEK()-1

Vložme nyní do buňky A1 vzorec =Den a tento vzorec roztáhněme na několik dalších buněk ve sloupci. Zjistíme, že název Den poskytuje proměnnou hodnotu v závislosti na aktuálním kalendářním datu a číslu řádku, kde je název použit. To už má k pojmenované oblasti opravdu hodně daleko.

Vzorec v názvu je plnohodnotnou podobou vzorce a může obsahovat i volání jiných názvů!

 

Využití názvů pro tvorbu čitelných vzorců.

 

 

Položme si otázku, co ze vzorce dělá supervzorec a co je současně s tím zdrojem jeho špatné čitelnosti a ještě horší upravitelnosti. Supervzorec je zpravidla sám o sobě jednoduchým výrazem nebo jednoduchou funkcí, složenou ovšem z extrémně složitých argumentů. Mnohočetné opakování stejných základních konstrukcí a mnohoúrovňové vnořování různých funkcí jedné do druhé je důsledkem toho, že v supervzorcích je zavrženo využití jakýchkoliv mezivýsledků. Potřebujeme-li shodný mezivýsledek v supervzorci pětkrát po sobě, musíme ho tam také pětkrát po sobě znovu od samého základu rozepsat a také spočítat. To je daň za to, že jsme si zakázali využívání pomocných buněk v rámci boje za kulturní datový list.
Snad není pochyb, kam směřuji. Prostor pro mezivýsledky nám mohou v rozsáhlém měřítku zajistit názvy. Při dostatečné vynalézavosti začneme zjišťovat, že v míře daleko vyšší, než se nám původně zdálo. Např. v souboji s absolutním adresováním, které je ve vzorcích pro názvy povinné, téměř vždy prokličkujeme pomocí funkcí ODKAZ a NEPŘÍMÝ.ODKAZ a z původně statického vzorce tak můžeme vytvořit vzorec s pružným chováním.
Jak tedy vypadá koncepce tvorby čitelných vzorců pomocí názvů? Zpravidla začneme úplně stejně jako při použití pomocných buněk. Místo do nich však začneme pomocné vzorce umisťovat do názvů. V dalších vzorcích se pak místo na pomocné buňky budeme odvolávat na odpovídající názvy a takto vzniklé vzorce budeme umisťovat do dalších názvů. Do cílové buňky tak dospěje místo strašidelného supervzorce zpravidla stručná a výborně čitelná formulka. Názvy totiž mohou nést proti strohým odkazům na buňky také textovou informaci o svém obsahu.
Tvorbu čitelných vzorců jako plnohodnotnou variantu k supervzorcům pokládám za směr, kterým by se měla ubírat technika užívání složitých vzorců v rámci MS Excel. Od okamžiku, kdy jsem poprvé nahlédl do tohoto zákoutí Excelu, si kladu otázku, proč se tato technika lavinovitě nerozšířila do vývojářské praxe. Určitě by přispěla k lepší pochopitelnosti a snadnější údržbě excelských aplikací, postavených na složitých vzorcích.

 

Ilustrativní příklad.

 

Na závěr bych rád okomentoval velmi názorný příklad, porovnávající použití supervzorců s technikou psaní čitelných vzorců. Příklad byl prezentován na Pandoře s původním supervzorcovým řešením. Následně jsem pro něj zpracoval řešení technikou čitelných vzorců, při kterém jsem pro sebe objevil krásu a půvab tohoto přístupu k výstavbě složitých vzorců.
Existuje dvourozměrná tabulka roky / produkty s vyplněnými hodnotami, v níž si můžeme pomocí dialogu vybrat časové rozpětí pro konkrétní produkt. Pro takto provedený výběr se automaticky spočítají jeho statistické charakteristiky. Výběr se provádí s využitím ověřovacích seznamů, výpočet charakteristik pomocí vzorců, vázaných na daný výběr.
Technika přímé tvorby finálních vzorců pro statistiky vede na vznik supervzorců. Jako příklad vzorce této povahy uvádím výpočet průměrné hodnoty za vybranou oblast:

=PRŮMĚR(NEPŘÍMÝ.ODKAZ(ODKAZ(POZVYHLEDAT($D$13;$A$1:$A$8;0); POZVYHLEDAT($A$13;$A$1:$H$1;);1)):NEPŘÍMÝ.ODKAZ(ODKAZ( POZVYHLEDAT($D$13;$A$1:$A$8;0);POZVYHLEDAT($B$13;$A$1:$H$1;); 1)))

Uvedený vzorec, i když stále ještě není extrémně rozsáhlý, nese typické známky supervzorců, jak jsem je zmínil dříve: jak vícenásobné opakování stejných argumentů, tak i významné vnořování funkcí. Vzorec sám je přitom velmi jednoduchý a tvoří ho jediná funkce =PRŮMĚR(oblast). Problémem je velmi složitá definice argumentu oblast. Vzorce =SUMA(oblast), =MAX(oblast) a = MIN(oblast) jsou stejně rozsáhlé supervzorce (kvůli opakované definici téže oblasti).
Nyní se naopak pokusíme s aktivním využitím názvů postupně vybudovat stavební kameny pro sestavení finálních čitelných vzorců. Nejdřív vytvoříme pojmenované vzorce pro zdrojovou tabulku Tabule, seznam produktů Zboží a pro seznam období Roky:

Tabule:   =List1!$A$1:$H$8
Zboží:      =List1!$A$1:$A$8
Roky:       =List1!$A$1:$H$1

S pomocí těchto vzorců připravíme pojmenované vzorce pro vybraný časový interval Od a Do a dále pojmenovaný vzorec pro vybraný produkt Druh:

Od:         =POZVYHLEDAT(List1!$A$13;Roky;0)
Do:         =POZVYHLEDAT(List1!$B$13;Roky;0)
Druh:     =POZVYHLEDAT(List1!$D$13;Zboží;0)

Tím jsme si připravili půdu k vytvoření pojmenovaného vzorce pro vybranou oblast Blok:

Blok:      =INDEX(Tabule;Druh;Od):INDEX(Tabule;Druh;Do)

Za povšimnutí stojí, jak jednotlivé pojmenované vzorce vstupují do vyšších vzorců, aniž by byla nutná jejich vícenásobná definice. Využití pojmenovaného vzorce Blok se projeví až na listu, kde plně zastoupí výše naznačené čtyři supervzorce:

=SUMA(Blok)
=PRŮMĚR(Blok)
=MIN(Blok)
=MAX(Blok)

Domnívám se, že porovnání obou technik z hlediska přehlednosti, srozumitelnosti a upravitelnosti není nutné komentovat. Pouze zdůrazním, že funkčně jde o dva naprosto ekvivalentní postupy.

Příloha    PodOblast.xls

Aktualizováno 9.února 2010:  Autor článku přidal přílohu.

Celý následující text pojednává o příspěvku na konferenci pandora.cz a následné diskuzi o tvorbě megavzorců.

Na prvním listu sešitu je podrobný rozklad výchozí školní úlohy. Ten začíná rozkladem do dílčích vzorců pro můj pokus o tvorbu megavzorců. Vzhledem k tomu, že jsem použil méně vhodného rozkladu do pomocných vzorců než Vašek (jsem zjevně horší vzorečkář než on), dokonce nelze z mých dílčích vzorců sestavit požadované finální megavzorce, jak se to podařilo Vaškovi. Excel totiž nezvládá složitost "mých" megavzorců z hlediska požadované hloubky vnoření funkcí (viz mé poznámky v předchozích vláknech).

V podstatě shodný rozklad do dílčích vzorců, na kterém ztroskotal můj pokus o vytvoření megavzorců, je následně použit pro tvorbu řešení na bázi definovaných názvů. Fakt, že moje dílčí vzorce, které neuspěly v případě megavzorců, skvěle posloužily definovaným názvům, je významným argumentem ve prospěch druhé metody. Ve výsledku je několik tisíc znaků, které by teoreticky spotřebovaly moje megavzorce, nahrazeno doslova několika desítkami znaků, potřebnými pro konstrukci definovaných názvů. Na listu s řešením jsem se pokusil tuto skutečnost zdokumentovat co nejnázorněji.

Na druhém listu přiloženého sešitu slovně popisuji použitý přístup k řešení v podobě rozepsaného postupu tvorby výsledného produktu. V zájmu přiblížení obecně zavedené metodě pomocných sloupců jsem svůj postup maximálně přizpůsobil tomuto schématu řešení. Jistě lze moji "kuchařku" dále zobecnit a snad i zjednodušit. Tvrdím ale, že už v popsané podobě tento text představuje obecný návod pro velmi úsporné řešení mnohých složitých výpočtů na excelském listu. Ve spojení s možností skrytí definovaných názvů se vzorci takový postup pokládám za vysoce efektivní i dostatečně bezpečný způsob řešení pro velmi širokou škálu úloh datové transformace v aplikacích Excelu. Pro mnohé může být významným bonusem tohoto postupu také fakt, že se daný postup plně obejde bez projektu VBA (tedy bez maker), i když se mnohými svými vnějšími znaky takovému řešení formálně podobá.


Příloha    Megavzorce Versus Definovane Nazvy2.xls
Ing. Vladimír Hajdovský

 

 

Komentáře

Přidat komentář

Přehled komentářů

Nojono

(Stanislav Roth, 7. 7. 2013 10:41)

John toho napsal velice mnoho. John má za sebou armádu lidí kterým pomohl stát se přinejmenším dost šikovným uživatelem excelu. Supervzorce, jsou opravdu těžko přehledné a nadělají starosti tomu kdo je má rozluštit. Nicméně při trošce znalostí získaných z jeho knih (nejen) a inteligence lze každý rozluštit. Praxe je taková, že velice dlouhé a komplikované vzorce se vyskytují mnohem řídčeji než by se při čtení vašeho příspěvku mohlo zdát. Už samotná omezení Excelu nedovolí psát vzorce nekonečně dlouhé.
Musím ovšem souhlasit, že mezivýpočty jsou hezké, ale při dlouhých konstrukcích (množství buněk se vzorci pro mezivýpočet), při skrývání sloupců (řádků) a při zamykání listů nastvá luštitelům jiný problém a to vůbec najít kde se co nalézá, s čím vzorec počítá (i přesto, že odkazuje na konkrétní buňku - např. na jiném listě) a s čím ne a ve finále zjistíte, že ten supervzorec je úplně to nejjednodušší i když hodně pokročilé a tudíž i náročné na nastudování.

Spravne to ma byt asi takto

(Premek, 26. 6. 2010 22:15)

="List1!$A$2:$A$" & Pocet2(List1!A:A)
Tento vzorec Vam nasledne vraci TEXT!!! napr. pokud se Vas pojmenovany vzorec jmenuje Test a do bunky B1 jste napsal =Test uvidite napr. vysledek List1!A1:A8
Ted je treba Excelu rict, ze tento text ma brat jako vzorec a na to se pouzije funkce Neprimy.odkaz(s hackami a carkami), takze napr. funkce Suma muze vypadat takto
=Suma(Neprimy.odkaz(Test))

Názvy a vzorce

(Miky, 22. 6. 2010 21:26)

Název Seznam může obsahovat následující odkaz:
=List1!$A$2:$A$&POČET2(A:A).
Při použití mi hlásí chybu v části "$A$".Použiji-li místo "&POČET2(A:A)" číslo, je to OK,ale ztrácí dynamiku. Vzorec =SUMA(Seznam)tak není funkční. Poradí někdo?
Díky

Názvy a vzorce

(Dan, 13. 7. 2009 11:04)

Začíná mi to být o trochu jasnější.
Velmi inspirující.

Názvy a vzorce

(Jiří Porkát, 10. 7. 2009 14:10)

Vladimíre, právě jsem řešil úlohu "věčného" pracovního kalendáře a bojoval jsem s názvy.
Myslím, že jste mi otevřel oči.
Děkuji vám a přeji pěkný den
JiP

 

 

 

Z DALŠÍCH WEBŮ

REKLAMA