Poradenství a kurzy
Perete se s Excelem? Zbavte sešity špíny a vyždímejte z Excelu maximum! Aviváž zdarma :-)
Algoritmy
Plné využití vzorců a nástrojů listu, aplikovaná matematika, VBA, netradiční řešení úloh, optimalizace běhu aplikací...
Programování
VBA a VB.NET, komentovaný a zarovnaný kód, ADO, WSH, WMI, API...
Štábní kultura
Čisté tabulky, infografika, uživatelsky přívětivé prostředí

Prázdné buňky a nulové hodnoty v grafu

„Pepo, jak to mám udělat, aby se v grafu nezobrazovaly nulové hodnoty?“ – „Funkcí KDYŽ si otestuj nulu a použij funkci NEDEF(),“ ozve se kolega od vedle. Má pravdu? Pojďme si probrat možnosti.

První cestou, jak vynechat jakákoliv data v grafu, je jejich skrytí, ať už přímo či filtrem aplikovaným na řádcích.

Skrytí sloupců s daty pro graf - před

Skrytí sloupců s daty pro graf – před


Skrytí sloupců s daty pro graf - po

Skrytí sloupců s daty pro graf – po


Skrytí dat v grafu filtrem

Skrytí dat v grafu filtrem

Pozn. Excel 2013 již umožňuje filtrovat data přímo v objektu grafu.

Prázdné buňky
Chování prázdných buněk v grafu je možné částečně řídit prostřednictvím voleb ukrytých v dialogu Vybrat zdroj dat.

Nastavení chování prázdných buněk v grafu

Nastavení chování prázdných buněk v grafu


Příklady chování prázdných buněk v grafu

Příklady chování prázdných buněk v grafu

I na tomto místě bych rád zdůraznil, že je nutné rozlišovat „nemít nic a mít nulu“, což v praxi znamená nemít založen v bance účet a mít na kontě nulový zůstatek, případně neprovádět měření a naměřit nulovou odchylku. Z toho důvodu doporučuji ponechat výchozí nastavení – zobrazovat prázdné buňky jako mezery. Je to ostatně jediný způsob, jak přerušit spojnici datových bodů. Třetí volba přepínače v dialogu Nastavení skrytých a prázdných buněk pak vykazuje stejné chování, jako funkce NEDEF(). Zaškrtávací políčko v témže dialogu mi přijde protismyslné a zbytečné.

Nulové hodnoty
Popisek tlačítka Skryté a prázdné buňky v dialogu Vybrat zdroj je nepřesný, dokonce až lživý. Propojený dialog nám neumožní nastavit nic, co by se týkalo zpracování nulových hodnot. První, co vás jistě napadne, je zkusit skrývat nuly prostřednictvím volby Soubor / Možnosti / Upřesnit / sekce Zobrazit možnosti pro tento list. Grafů se ale tato volba netýká. Stejně tak vypuštění nulových hodnot v grafu neovlivní trik s vlastním formátem buňky pro skrytí obsahu buňky (lze jej ovšem uplatit na samotné popisky datových bodů, jak je ukázáno dále). Pro graf je podstatné, že fakticky buňka hodnotu obsahuje.

Zobrazení nulových datových bodů a případných popisků se můžete vyhnout tak, že nebudete užívat značky, a na popisky aplikujete vlastní formát buňky s trikem vynechání třetí sekce (kód formátu končí druhým středníkem).

Ukázka spojnice bez značek a skrytí nulových hodnot v popiscích datových bodů

Ukázka spojnice bez značek a skrytí nulových hodnot v popiscích datových bodů

Kód pro formátování si upravte podle potřeby. Pro čísla s jedním desetinným místem může vypadat takto:
0,0;0,0;

Dialog Formát popisků dat na obrázku obsahuje v sekci Typ ještě přidaný kód formátu #““. Jeho funkčnost je podobná předchozímu. Vyzkoušejte.

Grafy se spojnicí nepřeruší křivku bez ohledu na hodnoty v buňkách (veškeré neprázdné buňky, dokonce i takové, jež obsahují text nebo prázdný řetězec), což je „takový nepěkná věc“. Rozumně se chovají asi jen sloupcové a výsečové grafy. U nich Excel nevykresluje sloupce s nulovou hodnotou ani čarou o šířce sloupce a stejně tak se neobjeví ani čára o délce poloměru kruhu u výsečových grafů.

Nulové hodnoty ve sloupcovém a výsečovém grafu

Nulové hodnoty se ve sloupcovém a výsečovém grafu nevykreslují

Funkce NEDEF()
Funkcí NEDEF(), resp. její návratovou hodnotou v buňce docílíme toho, že nebude vykreslen odpovídající datový bod (značka, sloupec, výseč). Tato funkce ale nijak nerozdělí případnou spojnici mezi přilehlými datovými body. To dokáže VÝHRADNĚ prázdná buňka, a není cesty, jak toho docílit vzorcem.

Užití funkce NEDEF()

Užití funkce NEDEF()

Tvoří-li řada nul nebo prázdných buněk souvislou oblast, jíž data končí (typicky nevyplněné či nulové hodnoty pro budoucí měsíce roku), lze s výhodou použít definovaný název a dynamickou oblast tak, aby se v grafu automaticky projevila pouze platná data. Důležité! V odkazu musí definovanému názvu užitému jako zdroj dat pro graf předcházet uvedení názvu rodičovského listu či sešitu, podle toho, jaký obor platnosti jste mu určili.

Dynamická oblast pro nenulové buňky aplikovaná v grafu

Dynamická oblast pro nenulové buňky aplikovaná v grafu

Dynamické oblasti vychází z funkce POSUN() a dále COUNTIF(), případně POČET(). Obě jsou v příkladu definovány názvem s oborem platnosti pro list Dynamická oblast.

rngDataNenuloveBunky: =POSUN(‚Dynamická oblast‘!$C$3;0;0;1;COUNTIF(‚Dynamická oblast‘!$C$3:$N$3;“>0″))
rngDataNeprazdneBunky: =POSUN(‚Dynamická oblast‘!$C$3;0;0;1;POČET(‚Dynamická oblast‘!$C$3:$N$3))

Sešit k vyzkoušení:
graf_nulove_hodnoty.zip

LinkedIn Auto Publish Powered By : XYZScripts.com