Chcemy stworzyć w Excelu wykres analogiczny jak mamy przygotowany w szablonach funkcjonalności. Przypomina on wykres koła życia, ale opiera się o doświadczenie firmy EI i nosi nazwę SPOCO Puls.
W poradzie 118 opisywałem jak można szybko stworzyć koło życia za pomocą wykresu radarowego, ale trochę więcej doświadczenia i chce się więcej, bardziej precyzyjnie i ładniej 😉
Głowiąc się nad problemem uzyskania w Excelu wykresu SPOCO Pulsu udało mi się zbliżone rezultaty osiągnąć za pomocą dwóch rodzajów wykresów:
- pierścieniowego
— sunburst (Excel 2016)
W tym wpisie omówimy pomysł korzystający z wykresu pierścieniowego. Jest on bardziej mozolny przy tworzeniu, ale jest bardziej zbliżony do efektu, który chcemy osiągnąć i umożliwia większą kontrolę nad tym co znajduje się na wykresie.
Wiemy już, że będziemy korzystali z wykresu pierścieniowego. Teraz potrzebujemy przygotować odpowiednie dane. W przykładzie omówimy uproszczony wykres, żebyśmy poznali zasadę tworzenia i nie musieli się zbytnio zmęczyć przy jego tworzeniu. Dlatego będziemy mieli tylko 4 główne serie punktowane (społeczność, oferta, ludzie, organizacja) od 0 do 5 punktów. Oprócz głównych serii potrzebujemy jeszcze serii dopełniających do maksymalnej wartości punktowej, czyli w sumie musi być to 8 serii.
Ważna jest zasada na jakiej są przypisywane wartości – w kolumnie A mamy ilośćpunktów uzyskaną w danej dziedzinie, a z prawej strony odpowiednio wypełnione wiersze jedynkami i zerami. Odpowiednio dla głównych serii wpisujemy 1 do kolumn z punktacją równą lub mniejszą od osiągniętą, a powyżej wpisujemy zera.
Przykładowo na obrazie powyżej dla społeczności wpisaliśmy 3 punkty, czyli w kolumnach C, D, E (1,2,3) wpisujemy jedynki, a dla kolumn F i G (4 i 5) wpisujemy zera. Dla serii dopełniających odwrotnie, czyli w kolumnach C, D, E wpisujemy zero, a F i G jeden. Zapewniamy to sobie dzięki odpowiednim formułą– dla serii głównej:
=JEŻELI($A2>=C$1;1;0)
a dla serii pomocniczej:
=JEŻELI($A3 mniejsze niż C$1;1;0)
Gdy mamy już przygotowane dane możemy je zaznaczyć (zakres B2:G9) i wstawić wykres pierścieniowy (pracujemy na Excelu 2016, ale we wcześniejszych wersjach poszczególne kroki są analogiczne).
Domyślnie wykres pierścieniowy ma zbyt dużą dziurę w środku i musimy to zmienić. Klikamy w dowolną serię danych i naciskamy Ctrl + 1, żeby przejść do właściwości wykresu i w opcjach osi ustawić rozmiar otworu pierścienia na np.: 15% (z założenia chcemy mieć możliwość wstawianie w ten otwór rysunku, jeśli tego nie potrzebujesz śmiało możesz ustawić 0%).
Widzimy, że kolorowane są zarówno serie główne jak i dopełniające, a nam zależy, żeby serie dopełniające były przezroczyste, a główne wypełnione wybranym przez nas kolorem. Oba typy serii mają mieć obramowanie (ciemne, żeby było widoczne). To jest kłopotliwe ponieważ seria na wykresie jest rozpoznawana jako pierścień (dane w kolumnie), a nie jak interpretowaliśmy wcześniej (jako wiersz). Z tego powodu identyczne kolorowanie poszczególnych kategorii (tak będziemy teraz odwoływać się do danych w wierszu) jest mozolne.
Zaczniemy od tego, że wszystkim serią zmienimy obramowanie na ciemne i kolor wypełnienia na brak wypełnienia.
Zacznijmy od najbardziej zewnętrznego pierścienia – zaznaczmy go klikając w niego raz, a następnie zmieniają kolor krawędzi na dowolny ciemny (np.: ciemny szary lub automatyczny).
Następnie klikamy na kolejny pierścień i naciskamy klawisz F4 – spowoduje to powtórzenie przez Excela ostatnio wykonanej czynności, czyli zmiany koloru obramowania. Tak robimy dla wszystkim pierścieni. Prawdopodobnie będzie trzeba zmienić grubość kontury, czego nie możemy wykonać jednocześnie ze zmianą koloru konturu, dlatego robimy to w kolejnej sekcji kroków (też wspierając się klawiszem F4, żebyśmy nie musieli rozwijać za każdym razem poleceń).
Analogicznie zmieniamy też kolor wypełnienia na brak wypełnienia.
Teraz nasz wykres jest brzydki/niefunkcjonalny, bo zawsze jest pusty niezależnie od wpisanych wartości, bo zmiana koloru obramowania i wypełnienia serii (pierścieni) zadziałała na wszystkie punkty.
Musimy zmienić teraz poszczególnych punktów dla kategorii głównych, żeby wykres wyglądał tak jak chcemy. Zanim do tego przejdziemy zmieniamy wartości przydzielonych punktów na maksymalną założoną (w tym przykładzie 5). Teraz klikamy na serię (pierścień) raz i ponownie na konkretny punkt w nim ponownie. Powinien zaznaczyć się tylko ten punkt, czyli w danej serii konkretna kategoria główna (bo dopełniające są niewidoczne przez zera). Możemy się tego upewnić zaglądając na kartę Formatowanie Narzędzi Wykresów.
Dalej pomagamy sobie klawiszem F4, tylko musimy uważać, żeby mieć zaznaczony tylko pojedynczy punkt, bo jak pomalujemy całą serię (pierścień) za pomocą klawisza F4, to nawet po cofnięciu będzie zawsze zamalowywał serię (pierścień), nawet jeśli zaznaczymy tylko pojedynczy punkt.
Jeśli udało nam się nadać odpowiedni kolor wszystkim punktom kategorii głównych, to możemy zmienić wartości im przypisanym i zobaczymy, że wykres prezentuje się dużo lepiej – widzi, że zapełnia się mniej lub bardziej od wpisanych wartości.
Widzimy kolory przypisane poszczególnym kategoriom w legendzie, w której nie potrzebujemy widzieć kategorii dopełniających. Możemy je łatwo usunąć klikając na konkretną kategorię dwa razy. Pierwsze kliknięcie zaznaczy całą legendę, a drugi już konkretną kategorię. Niepotrzebne wpisy w legendzie kasujemy przyciskiem Delete.
Nasz wykres nabiera kolorów, ale przydałaby się na nim jeszcze informacja na temat ilości punktów przypisanych poszczególnym pierścieniom. Można kombinować z etykietami danych, ale one wyświetlają się na środku punktu (fragmentu pierścienia kategorii) co nie odpowiada wyglądowi naszego wzorca, dlatego wykorzystamy inne rozwiązania, a mianowicie wkleimy do wykresu odpowiedni obrazek z liczbami.
Zrobimy tylko przykład dla numerowania w prawo. Czyli potrzebujemy serii danych w komórkach od 1 do 5. Przy okazji odznaczmy w karcie Widok pole wyboru Linie siatki, żeby nie pokazywały się przy kopiowaniu zakresu do obrazu.
Teraz kopiujemy komórki z naszą serią i wklejamy je jako obraz (ewentualnie jako obraz połączony – od updatu Excela na początku sierpnia 2017 roku można już wklejać obrazy połączone jako część wykresu, wcześniej wklejały się jako statyczne obrazy, ominięcie tego polegało na zgrupowaniu wykresu i dynamicznego obrazu, ale wygodniej jest mieć obraz jako część wykresu niż jako grupę)
Po wklejeniu tego obrazu kopiujemy go, zaznaczamy wykres i wklejamy skopiowany obraz do wykresu. Od teraz obraz jest częścią wykresu, czyli będzie się przesuwał z wykresem, a nawet domyślnie zmieniał rozmiar (chociaż tu ciężko zachować proporcje do obszaru wykresu i samego wykresu, żebyśmy nie musieli robić korekty obrazu po zmianie rozmiaru wykresu).
Teraz wstawiliśmy punktację na wykresie tak jak chcieliśmy. Kolejne serie punktów do wykresu możemy dodać kopiując obrazek i obracając go, albo jeśli nie pasować nam będzie orientacja liczb, kopiować inne zakresy komórek np.: pionowe.
Na koniec jeszcze standardowa sztuczka z połączeniem tytułu wykresu z wartością z komórki – zaznaczamy tytuł wykresu naciskamy klawisz równa się, następnie klikamy komórkę z tekstem, który chcemy mieć w tytule wykresu i klikamy Enter.
Nasz docelowy wykres SPOCO® Pulsu ma więcej kategorii, punktów i obrazów (oraz wordartów podpisujących kategorie zamiast legendy) w sobie, ale to po prostu więcej tej samej pracy by uzyskać efekt –wow–„To mogę stworzyć taki wykres w Excelu i zmienia się dynamicznie w zależności od wartości jakie wpiszę?” TAK!
Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP