„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.
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.
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).
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ů.
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.
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é 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