Různé barvy v grafu pro skutečnost a plán (odhad budoucí hodnoty) si přejí především finanční analytici a plánovači. Inspirací pro dnešních pár řádků mi byl článek Jiřího Číhaře – Odlišení skutečných a odhadovaných hodnot.
Předpokládejme, že máme srovnatelná data skutečnosti ze stejného období loňského roku (celý rok 2015) a mix skutečných a plánovaných dat pro rok letošní (skutečnost od začátku roku 2016 plus plán do konce roku). V tuto chvíli nepotřebujeme vědět, co představuje verze plánu v databázi (OLAP), že jich může být několik, a ani nebudeme řešit, jak plánovat (např. přepis odhadu budoucí hodnoty získané s pomocí FORECAST reálnou hodnotou).
Autor výše uvedeného článku naznačuje, že existuje několik způsobů, jak barevně odlišit data skutečnosti a plánu (odhadu). Mě napadají jen dva – rozdělení datové řady na (přinejmenším) dvě a nebo zpracování makrem. Předně budeme muset znát hranici, které odděluje skutečná data od plánu. Pokud se hodnoty vyskytují ve stejném sloupci, lze předpokládat kombinaci čísla-čísla, čísla-vzorce, vzorce-vzorce. Každopádně pokud nebude dána hranice explicitně, horko těžko ji budeme z jednoho namixovaného sloupce dolovat (nestačí nám ani funkce ISFORMULA v Excelu 2013).
Na základě známé hranice si rozdělíme data skutečnosti od plánu pro rok 2016. Prázdná místa doplníme funkcí NEDEF a zkusíme vytvořit skupinový sloupcový graf. Zjistíme, že jeho vykreslení neproběhne úplně podle představ, a že je dobré si rozdělit i data skutečnosti pro rok 2015. Podívejte se na obrázek.
Všimněte si, že dvojice dat (skutečnost 2015 / skutečnost 2016) a (skutečnost 2015 / plán 2016) v grafu jsou v rámci kategorií zarovnány odlišně nalevo a napravo od hranice (červen / červenec). Je to proto, že Excel ve skutečnosti pracuje se čtyřmi datovými řadami v každé kategorii a na místě NEDEF ponechává prostor „co kdyby náhodou“. Kdybychom nedělili řadu pro skutečnost 2015, dopadlo by vykreslení napravo od hranice hůře (plán 2016 by byl odskočený od skutečnosti 2015) a nepomohly by tomu ani hrátky s nastaveními v dialogu Formát datového bodu / Možnosti datové řady.
Pozn. Z legendy jsem odstranil popisek pro druhou část skutečnosti 2015 (sloupec G) a oběma částem skutečnosti 2015 byla přiřazena stejná šedá barva. Řada pro plán 2016 má světlejší odstín zelené oproti skutečnosti 2016. Příklad byl okořeněn podmíněným formátem ve sloupci D.
Ve finále bychom grafem překryli pomocnou oblast F5:I17 a skryli pomocný výpočet v buňce C3 (hranice skutečnost / plán).
Jak je vidět, skupinový sloupcový graf nám situaci zrovna neulehčil. Proto je vhodné grafy kombinovat.
Kupodivu není dobrý nápad mít stejný typ grafu pro skutečnosti 2015 a 2016 a jiným typem zobrazovat plán 2016. Pro řadu skutečnosti 2015 (zde stačí jedna jediná řada čerpající ze sloupce C) jsem zvolil spojnicový graf a pro oba typy dat roku 2016 ponechal skupinový sloupcový graf. U sloupcových grafů jsem přitom nastavil v dialogu Formát datové řady 100% překrytí (dojde k zarovnání na střed kategorie).
Typy grafů můžeme i prohodit. Pro skutečnost 2015 zvolíme graf sloupcový a pro skutečnost a plán 2016 spojnicový.
Je tu drobný rozdíl oproti předchozímu. Tabulky pro data 2016 se budou potkávat v hraniční hodnotě (ve vzorci pro plán 2016 přidáme v podmínce symbol rovná se). To proto, aby spojnice mezi daty skutečnosti a plánu 2016 nebyla přerušená. Řada skutečnosti 2016 by měla překrývat data plánu 2016 (kvůli hraniční značce).
Technika pomocných řad s funkcí NEDEF je hojně využívaná. Lze ji aplikovat kupříkladu v grafech, kde chceme zvýraznit minimum a maximum odlišnou barvou (datový bod či celý sloupec překryje původní).
Tipy k prostudování
Jon Peltier – Conditional XY Charts Without VBA
Alesandra Blakeston – Create Charts with conditional formatting
Různé barvy pro skutečnost a plán řízené makrem
V případě makra se nám hodí událost Change daného listu a kód aplikovaný na graf se dvěma sadami původních hodnot (viz sloupce C a D).
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 | Private Sub Worksheet_Change(ByVal Target As Range) Dim i As Integer Dim intHranice As Integer Dim rngSledovanaBunka As Range Dim objPoint As Point 'přiřazení sledované buňky do objektové proměnné Set rngSledovanaBunka = Range("B3") 'nastala změna ve sledované buňce? If Union(Target, rngSledovanaBunka).Address = rngSledovanaBunka.Address Then 'načtení hranice intHranice = Range("C3") Application.ScreenUpdating = False 'přes všechny měsíce (datové body) For i = 1 To 12 'převzetí datového bodu druhé řadu grafu Set objPoint = ActiveSheet.ChartObjects("Graf VBA").Chart.SeriesCollection(2).Points(i) If i < = intHranice Then 'formát pro skutečnost With objPoint.Format.Fill .Visible = msoTrue .ForeColor.ObjectThemeColor = msoThemeColorAccent3 .ForeColor.TintAndShade = 0 .ForeColor.Brightness = 0 .Transparency = 0 .Solid End With Else 'formát pro plán With objPoint.Format.Fill .Visible = msoTrue .ForeColor.ObjectThemeColor = msoThemeColorAccent3 .ForeColor.TintAndShade = 0 .ForeColor.Brightness = 0.6000000238 .Transparency = 0 .Solid End With End If Next i Application.ScreenUpdating = True End If End Sub |
Příloha:
graf_ruzne_barvy.zip