Trzy pierwsze wykresy, które zostały ujęte w dodatku Sparklines for Excel w grupie Composition, da się utworzyć w Excelu w tradycyjny sposób, który został już na blogu opisany. Dodatek umożliwia jednak ich budowę w znacznie prostszy sposób – wewnątrz komórek, co znacznie ułatwia konstrukcję dashboardów, które odbiorca będzie otwierał w Excelu. Sprawdźmy zatem, jak tworzy się wykres Pareto, kaskadowy i słupkowy skumulowany.
Wykres Pareto w dodatku Sparklines for Excel
Wykres Pareto służy do przedstawienia rankingu jakiejś grupy elementów i pozwala oddzielić te, które są istotne od tych, które niosą za sobą mniejszą wartość. Wykres Pareto został zaliczony do grona 7 podstawowych narzędzi jakości. Użycie dodatku SfE pozwala zautomatyzować jego tworzenie.
W odróżnieniu od typowych sparklines, wykres Pareto ciężko byłoby zmieścić w jednej komórce. Dlatego też SfE automatycznie rysuje go w serii komórek, w zależności od tego, jak zorganizowane są dane. Formuła natomiast zlokalizowana jest w jednej komórce. Zacznijmy od przygotowania danych do wykresu. Narysujmy w tym celu prostą tabelę, przesortowaną malejąco:
Przejdźmy do SfE. Jedynym wymaganym parametrem przy rysowaniu tego wykresu jest zakres danych, które mają być przedstawione. Ustawmy się teraz w kolejnej kolumnie, obok pierwszej komórki zawierającej dane:
Klikamy na przycisk Pareto ze wstążki Sparklines:
Następnie zaznaczamy zakres z danymi, czyli w naszym przypadku C5:C9.
Oto efekt, czyli wykres w wersji podstawowej:
Tak wygląda wykres z kolumnami w poziomie. Jeśli chcemy, aby SfE narysował kolumny pionowo, wystarczy zmienić orientację danych – dodatek reaguje automatycznie na kształt zakresu danych. W tym przypadku formułę umieszczamy w komórce po lewej stronie:
Wróćmy jednak do wykresu z poziomymi kolumnami. Dodatek pozwala nam na zaprojektowanie kolorów dla poszczególnych kolumn. W tym celu, w osobnym zakresie komórek, odpowiadającym rozmiarem zakresowi komórek z danymi, wpisujemy kody kolorów z tabeli dostępnej na stronie projektu. Adres zakresu wpisujemy do pola ColorRange z okna dialogowego funkcji:
Efekt takiego zabiegu prezentuje się następująco:
Kolumnę z kodami kolorów, jako kolumnę techniczną, możemy oczywiście przenieść w inne miejsce arkusza – SfE® automatycznie zaktualizuje jej adres. Dodatkowe opcje, jakie oferuje nam SfE® do tego wykresu, to dodanie linii targetu – możemy umieścić na wykresie dwie takie linie. Aby to zrobić, korzystamy z pól Target i Target2, wpisując tam wartość pomiędzy 0% i 100%, powiedzmy 50% i 80%:
Szybko możemy więc określić, które błędy stanowią 80% wszystkich – dwa pierwsze. Kolejna opcja, to wyróżnienie którejś pozycji kolorem czerwonym. Służy do tego pole HighlightPosition, do którego wpisujemy odpowiedni numer kolumny, liczony od góry. Jeśli chcemy dodatkowo uszczegółowić nasz wykres, możemy dodać do poszczególnych kolumn etykiety – wpisując w pole Label wartość 1 (lub True). Tak wygląda wykres z wyróżnioną pierwszą kolumną oraz etykietami:
Wykres kaskadowy w dodatku Sparklines for Excel
Kolejnym wykresem dostępnym w dodatku SfE jest wykres kaskadowy, po angielsku zwany cascade lub waterfall. Służy on do podsumowania elementów składających się na wzrost lub spadek jakiejś wartości. O tym, jak wykres kaskadowy skonstruować za pomocą Excela bez dodatku SfE, pisałem w tym artykule. Spróbujmy przygotować taki wykres za pomocą SfE.
Zacznijmy od zebrania danych. Niech będzie to sprzedaż w 2013 i 2014 roku, dla spółki oferującej trzy marki:
Aby stworzyć wykres kaskadowy, musimy teraz te dane przearanżować – interesują nas składowe zmiany sprzedaży w roku 2014:
Podobnie, jak w przypadku wykresu Pareto, wykres kaskadowy nie jest tutaj typowym sparkline – nie zmieści się w jednej komórce – formułę umieszczamy przy najwyższej komórce z danymi, a wykres zostanie narysowany w tylu komórkach, ile mamy wierszy z danymi. Przejdźmy więc teraz do narysowania wykresu – w tym celu, stojąc w komórce, która wyznaczy początek wykresu, na wstążce Sparklines odnajdujemy i klikamy przycisk Cascade:
W oknie dialogowym uzupełniamy odpowiednio pola PointsRange (jedyny element obowiązkowy w tym wykresie) i dodatkowo LabelRange, wpisując tam zakresy z danymi i z etykietami poszczególnych kolumn:
Gotowy wykres wygląda tak:
Kolejne opcje dostępne w tym wykresie, to określenie wartości minimalnej i maksymalnej (pola Minimum i Maximum w oknie dialogowym). Przydają się wtedy, kiedy rysujemy serię wykresów obok siebie i chcemy, aby miały one wspólny punkt odniesienia.
Możemy też zmienić domyślne kolory wykresu. Jak widać na przykładzie, wartości dodatnie są rysowane na zielono, a ujemne – na czerwono. Widać też, że wartości wyjściowa i końcowa są w bardziej intensywnym kolorze, niż wartości pośrednie. Na szczęście nie musimy układać schematu kolorystycznego sami – wystarczy, że podamy dwa główne kolory, a SfE® sam dopasuje natężenie barwy do odpowiednich kolumn. Kolory określamy jak we wszystkich wykresach SfE® – za pomocą tabeli. Po wybraniu odpowiedniej barwy, wpisujemy kody w pola ColorPositive i ColorNegative. Niestety, ale w opcjach tego wykresu zabrakło możliwości sterowania wielkością czcionki do etykiet poszczególnych kolumn – domyślna czcionka jest bardzo mała.
Wykres słupkowy skumulowany
Kolejnym wykresem z tej serii jest wykres skumulowany (stacked chart). Pozwala on stworzyć słupek podzielony na segmenty różnej wielkości. Taki wykres można też stworzyć za pomocą zwykłego wykresu (słupkowy skumulowany 100%), ale jako sparklines próżno go szukać w opcjach Excela. Zobaczmy, jak to działa.
Przygotujmy najpierw dane do naszego wykresu – niech będzie to kwartalna sprzedaż kilku marek spółki X:
Następnie, w kolejnej kolumnie umieśćmy formułę SfE®. W tym celu, ustawiamy się w pierwszym wierszu tej kolumny i na wstążce Sparklines klikamy przycisk Stacked:
W oknie dialogowym wykresu, w polu Points, wpisujemy zakres danych, jakie ma pokazać pierwszy pasek danych, czyli w naszym wypadku dane z pierwszego kwartału:
Efektem jest gotowy pasek:
Wystarczy teraz przeciągnąć formułę do pozostałych komórek:
Wykres jest w zasadzie gotowy. Sprawdźmy teraz, jak możemy go urozmaicić. Po pierwsze, podobnie jak w wykresie Pareto, możemy zaprojektować kolor każdego z segmentów. Domyślnie, jak widać na powyższym obrazku, są to odcienie szarości, które świetnie się sprawdzą przy wersji do druku. Pamiętając o zasadach stosowania kolorów, o których pisaliśmy już w tym artykule, możemy dobrać inny schemat.
Kolory zmieniamy analogicznie, jak w wykresie Pareto, czyli wpisując do pola ColorRange adres zakresu komórek, w których umieściliśmy kody kolorów dla każdej z marek. Pamiętajmy o zablokowaniu zakresu, żeby móc skopiować formułę dla wszystkich pasków danych:
Nasz wykres zmienił kolory na odcienie zielonego. Alternatywnym rozwiązaniem jest użycie pola Color, które znajdziemy w oknie dialogowym kilka wierszy niżej. SfE® sam dopasuje nam schemat kolorów do jednego koloru bazowego, którego kod należy wpisać w te pole.
Kolejna opcja, z której możemy skorzystać, pozwala nam dodać do wykresu etykiety. W tym celu, w pole LabelRange wpisujemy zakres komórek, w których znajdują się nazwy naszych marek:
Jak widać, ta opcja nie jest niestety doskonała, bo jeśli segment jest zbyt krótki, tekst zostaje ucięty. Możemy co prawda sterować wielkością tekstu za pomocą pola TextSize – ale ciężko jest dopasować jeden rozmiar do wszystkich segmentów na raz. Dostępne jest też pole TextOrientation, które pozwala zmienić orientację tekstu na pionową. W tym celu, wpisujemy w to pole „true” bądź 1.
Następna opcja modyfikacji wykresu to ustalenie maksymalnej wartości osi poziomej. Przydaje się, jeśli chcemy mieć wspólną oś, a tym samym wspólny punkt odniesienia, dla serii pasków. Jeśli nie skorzystamy z tej opcji, każdy pasek, pomimo tej samej szerokości, będzie w rzeczywistości reprezentował inną wartość – patrząc na całość, będziemy mogli porównać jedynie rozkład procentowy poszczególnych marek.
Aby uspójnić osie, musimy ustalić, który kwartał miał w sumie największą sprzedaż, a następnie wpisać tę wartość w pole Maximum dla każdego z pasków:
SfE daje nam także możliwość określenia czterech linii targetu – czerwonej, zielonej, niebieskiej i żółtej. Służą do tego pola TargetRed, TargetGreen, TargetBlue i TargetYellow, w które należy wpisać pożądane wartości. Oto efekt, który osiągniemy, jeśli ustalimy targety co 1 000 000:
Możemy zwiększyć grubość linii targetu za pomocą pola TargetWeight. Ostatnie dostępne pole z okna dialogowego, HighlightPosition, pozwala nam wyróżnić czerwonym kolorem dowolny segment – wystarczy wpisać tutaj jego numer.
Plik do pobrania
Tu możesz pobrać plik Excel z wykresem Pareto, kaskadowym i słupkowym skumulowanym w SfE.
