0
0 Produkty w koszyku

No products in the cart.

Wykres lejkowy w komórkach arkusza — porada #303

Oglądając film z kanału YT Goodly o wykresie lejkowym, który formatuje się warunkowo w zależności od komórki danych, w którą klikniemy stwierdziłem, że też chcę stworzyć taki wykres, ale prościej 😉 Przede wszystkim mój lejek miał się znajdować w komórkach Excela, a nie na wykresie. Pomogły mi informacje, jakie zdobyłem tworząc kurs o wizualizacji i Dashboaradach w Excelu. Uzyskałem taki efekt.

WykresLejkowyWKomórkach 01

Oczywiście powyższy wykres podświetla na czerwono wiersz, w który klikniemy. Jak uzyskać taki efekt? 

Przede wszystkim musimy stworzyć poziomy „lejka” w komórkach. Robimy to za pomocą odpowiedniej formuły i zmiany czcionki.
Zaczniemy od formuły – potrzebujemy powtórzyć pewną ilość razy znak pionowej kreski "|". Robimy to za pomocą funkcji POWT.

Musimy tylko tak dopasować ilość powtórzeń, żeby każdy kolejny poziom lejka miał odpowiednią szerokość, czyli normalizujemy liczbę powtórzeń. Załóżmy, że maksymalnie chcemy powtórzyć 60 razy znak "|" – dla najwyższego poziomu. Każdy kolejny poziom ma mieć proporcjonalnie mniej znaków. Czyli wystarczy, że wartość z aktualnego poziomu podzielimy przez maksymalną wartość ze wszystkich poziomów (domyślnie przez wartość z najwyższego/pierwszego poziomu). Oczywiście musimy jeszcze przemnożyć przez 60:

=POWT("|";B4*60/MAX($B$4:$B$8))

WykresLejkowyWKomórkach 02

Zwróć uwagę, że czcionka formuły wygląda nietypowo. To dlatego, że standardowa czcionka by się tutaj nie sprawdziła – byłyby za duże odstępy pomiędzy poszczególnymi pionowymi liniami. Dlatego w przykładzie wykorzystana jest czcionka Haettenschweiler (możesz m.in. skorzystać też z czcionek: Britannic Bold, Nyala, Playbill, Stencil)

Mamy paski, ale nie przypominają one lejka. Rozwiązanie jest proste – wystarczy wyrównać tekst do środka.

Zanim zaczniemy malować wiersze dołóżmy jeszcze proste formuły, które będą obliczały procentowy spadek do pierwszego i poprzedniego kroku. Są one bardzo podobne:

=1‑B5/$B$4 i =1‑B5/B4

Różnica polega tylko na tym czy w pierwszej formule zablokujemy bezwzględnie odwołanie do komórki B4 czy nie.

WykresLejkowyWKomórkach 03

Teraz możemy zająć się krokami potrzebnymi do tego, żeby nasz mały Dashboard formatował się na czerwono, gdy klikniemy w komórkę danego wiersza. Niestety będziemy potrzebowali skorzystać z kodu VBA. Na szczęście to bardzo prosty fragment kodu, który musimy umieścić w module arkusza, na którym znajduje się nasz „lejek”.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("A1").Value = ActiveCell.Row
End Sub

Powyższy kod VBA uruchamia się za każdym razem, gdy zmienimy zaznaczenie na arkuszu i wstawia do komórki A1 numer wiersza aktywnej komórki.

Może Ci się wydawać, że kod ten nie działa na załączonym pliku, ale wynika to tylko z zastosowanej sztuczki – zmianie formatowania liczbowego komórki A1.

Jeśli zaznaczysz komórkę A1 i naciśniesz skrót Ctrl + 1 zobaczysz, że liczby w komórce zostały sformatowane niestandardowo tak, żeby zawsze pokazywać tekst "Lejek sprzedażowy w komórkach".

WykresLejkowyWKomórkach 04

Bazując na wartości w komórce A1 tworzymy formatowanie warunkowe (na zakresie naszego lejka – A4:E8) korzystające z formuły:

=$A$1=WIERSZ()

WykresLejkowyWKomórkach 05
Formuła jest bardzo prosta sprawdza czy aktualny wiersz komórki jest równy wartości przechowywanej w komórce A1 jeśli tak, to nakłada formatowanie. I już gotowe — główne zadanie zostało zrealizowane – lejek zmienia kolor.

My jednak chcemy dołożyć jeszcze parę dodatkowym informacji na naszym mini Dashboardzie. Chcemy podawać liczbę klientów straconych do pierwszego i poprzedniego kroku. 

Najczęściej podobne informacje podaje się w polach tekstowych – najpierw w jakiejś odległej (niewidocznej na dashboardzie) komórce wykonuje obliczenia, a potem wstawie pole tekstowe i wpisuje się w pasku formuły, że równa się ono komórce z formułą (nie można wpisać formuły bezpośrednio do pola tekstowego). 

W naszym przykładzie robimy ciut inną rzecz – w komórce D2 mamy już jedną z potrzebnych formuł:

=JEŻELI.BŁĄD(INDEKS($B$4:$B$8;A1‑4)-INDEKS($B$4:$B$8;A1‑3);0)

Formuła do komórki B2:

=JEŻELI.BŁĄD(B4-INDEKS($B$4:$B$8;A1‑3);0)

ale dodatkowo pod nią (a niejako w niej) znajduje się pole tekstowe z tekstem. Komórce zmieniliśmy kolor czcionki na czerwoną i wyrównaliśmy tekst do góry, a w polu tekstowym zostawiliśmy domyślny kolor czcionki. 

W tym przykładzie wygląda to w miarę OK, ale korzystanie z komórki ogranicza położenie wyświetlanych wartości, dlatego częściej stosuje się pole tekstowe, które odwołuje się do komórki z odpowiednią formułą/obliczeniem oraz drugiego pola tekstowego, które funkcjonuje jako podpis.

Na koniec jeszcze jedna czynność często wykorzystywana przy Dashboardach – wyłączanie linii siatki (pole wyboru na karcie Widok).

WykresLejkowyWKomórkach 06

Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP

Wykres schodkowy czasu — porada #302

Wykres schodkowy dla dat jest prosty do stworzenia (patrz porada 301). Jednak gdy na wykresie przykładowo chcemy przedstawić stan czujnika w poszczególnych godzinach, to sprawa jest trudniejsza. Przede wszystkim nie będziemy mogli skorzystać z wykresu liniowego, bo nie rozpoznaje on czasu, tak jak rozpoznaje dat. Dlatego będziemy musieli skorzystać z wykresu punktowego.

Porada 302 - Wykres schodkowy czasu 01

Standardowo musimy odpowiednio przygotować dane. Mamy dane źródłowe, które pokazują o której godzinie stan czujnika się zmienił i na jaki – są 3 stany –„OK”, „Czuwa” i „Błąd”.

Porada 302 - Wykres schodkowy czasu 02

Stanów zapisanych jako tekst nie jesteśmy w stanie przedstawić na wykresie, dlatego przypisujemy im wartości liczbowe w tabeli pomocniczej. Ważne, w późniejszym kroku, że jedna z tych wartości jest dodatnia, druga ujemna, a trzecia wynosi zero.

Porada 302 - Wykres schodkowy czasu 03

Za pomocą funkcji WYSZUKAJ.PIONOWO odnajdujemy odpowiednie wartości dla poszczególnych punktów.

=WYSZUKAJ.PIONOWO(B2;Tabela13;2;0)

Porada 302 - Wykres schodkowy czasu 04

Podobnie jak w poradzie 301 przygotowane dane kopiujemy w inne miejsce dwukrotnie pod sobą (ważne, że nasze dane są posortowane od najmniejszych czasów do największych).

Porada 302 - Wykres schodkowy czasu 05

Analogicznie jak wcześniej chcemy usunąć pierwszą komórkę z czasem (pomalowaną na żółto) oraz ostatnie komórki związane z wartościami (2 żółte komórki na powyższym obrazie). Oba usuwania z przesunięciem do góry. Analogicznie jak przy datach spowoduje to, że dla każdej godziny (poza pierwszą) będą dwa stany czujnika przed i po zmianie.

Po usunięciu komórek musimy dane posortować ponownie rosnąco po czasie i dopiero możemy wstawić wykres punktowy z liniami i znacznikami, żeby wyglądał jak wykres schodkowy. Zaznaczamy do niego kolumnę z czasem i wartościami (stanu nie zaznaczamy).

Porada 302 - Wykres schodkowy czasu 06

Ten wykres musimy mocniej sformatować, żeby lepiej się prezentował. Przede wszystkim zaczynamy monitorować stan czujnika dopiero po godzinie 6:00, a kończymy około 21:00, więc musimy przypisać odpowiednie wartości minimum i maksimum dla osi. Musimy tutaj pamiętać, że Excel przechowuje czas jako liczbę dziesiętną (część dnia, która minęła) i w opcjach osi nie możemy wpisać 6:00, bo Excel tego nie zrozumie, tylko odpowiadającą tej godzinie liczbę 0,25 (=6/24). Analogicznie 21:00 to 0,875.
Pozostaje w analogiczny sposób dopasować jednostki główne na 0,125 (3 godziny).

Porada 302 - Wykres schodkowy czasu 07

Musimy również dopasować jednostki na osi y, bo nasze maksimum to 1, minimum ‑1, a przeskok (jednostka główka to 1). Przy okazji możemy usunąć linie siatki (uważam, że utrudniają odczyt wykresu) i trochę zmniejszyć wysokość wykresu.

Porada 302 - Wykres schodkowy czasu 08

Wykres schodkowy dla czasu jest już gotowy, ale do pełni szczęścia brakuje nam, żeby na osi y zamiast wartości 1, 0 i ‑1 na wykresie były zapisane odpowiednie stany licznika. Możemy uzyskać taki efekt dzięki niestandardowemu formatowaniu liczbowemu.
Zaznaczamy kolumnę z wartościami i naciskamy Ctrl + 1, żeby otworzyć okno Formatowania komórek. Na zakładce liczby, przechodzimy do kategorii niestandardowej i wpisujemy taki kod formatowania liczbowego.
"OK";"Błąd";"Czuwa"

Porada 302 - Wykres schodkowy czasu 09

Zatwierdzamy formatowanie liczbowe przyciskiem OK i teraz zamiast liczb Excel wyświetla w komórkach tekst. Wystarczy jedna zwrócić uwagę na sposób wyrównania tego tekstu (do prawej), żeby wiedzieć, że Excel traktuj go jako liczb, bo faktycznie w tej komórce jest przechowywana liczba (wynik funkcji WYSZUKAJ.PIONOWO).

Porada 302 - Wykres schodkowy czasu 10

Trzeba powiedzieć kilka słów o niestandardowym formatowaniu liczbowym – przede wszystkim jest dzielone na sekcje za pomocąśredników. Pierwsza sekcja oznacza jak mają być formatowane liczby dodatnie. W naszym przykładzie wpisaliśmy tekst „OK”, czyli Excel zamiast każdej liczby dodatniej (w tym przykładzie plus jedynka) będzie wyświetlał słowo OK. druga sekcja odpowiada za liczby ujemne. Tutaj chcemy wyświetlać słowo „Błąd” (w tym przykładzie minus jedynka). Trzecia sekcja to sposób formatowania zera – w tym przykładzie słowo „Czuwa”. Jest jeszcze czwarta sekcja formatująca tekst, ale tutaj jest nam niepotrzebna.

W zależności od Twojej wersji Excela, wykres może zrozumieć zmianę formatowania danych (komórek) źródłowych lub nie. Jeśli zrozumiał, to na wykresie na osi y zamiast wartości liczbowych będą wyświetlały się słowa. Jeśli Excel nie zrozumiał tej zmiany musisz wstawić/dodać ją ręcznie, czyli zaznaczamy oś y, naciskamy Ctrl + 1 i odszukujmy sekcję formatowania liczb. Tam wpisujemy nasz niestandardowy kod formatowania liczbowego i klikamy przycisk Dodaj.

Porada 302 - Wykres schodkowy czasu 11

Na koniec można jeszcze wspomnieć, że stworzony przez nas wykres nadaje się to przedstawienia stanu jednego czujnika, jeśli dołożymy dane drugiego czujnika to wykres przestanie być czytelny. Dlatego jeśli potrzebujemy monitorować więcej czujników, to żeby wynik był czytelny każdy czujnik przedstawiamy na innym wykresie. A później ustawiamy je równo pod sobą.
Porada 302 - Wykres schodkowy czasu 12

Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP

Wykres schodkowy daty — porada #301

Dziś chcemy stworzyć w Excelu wykres schodkowy, czyli taki, który obrazuj, w którym momencie (w jaki dniu/dacie) nastąpiła zmiana parametru.

Porada 301 - Wykres schodkowy daty 01

Omówimy go na prostym przykładzie stanu konta – mamy przykładowy zbiór danych, który ilustruje ile wynosiłstan konta w danym dniu, czyli 2017-04-04 stan konta wynosił 540zł i nie zmienił się do 2017-04-10, kiedy to zmienił się na 1630zł itd.

Porada 301 - Wykres schodkowy daty 02

Jeśli od razu zamieścilibyśmy nasze dane na wykresie liniowym to prezentowałyby sięźle, ponieważ wyglądałoby, że stan konta zmienia się z dnia na dzień, a faktycznie tak nie jest.

Porada 301 - Wykres schodkowy daty 03

Czyli zamiast wznoszącej się linii przez pierwsze 6 dni powinna być pionowa linia skoku dopiero dnia 2017-04-10.
Jak możemy zmienić nasze dane, żeby dobrze się prezentowały? Przede wszystkim ważne jest, żeby były posortowane od najstarszej daty do najnowszej. Następnie skopiujemy je sobie obok, żeby zachować oryginał. Kolejnym krokiem będzie ponowne skopiowanie danych, ale już bez nagłówków, tuż pod naszej tabeli.

Porada 301 - Wykres schodkowy daty 04

Dodatkowo od razu wypełniliśmy dwie komórki na żółto, bo będziemy chcieli je usunąć z przesunięciem w górę. 

Porada 301 - Wykres schodkowy daty 05
Dlaczego je usuwamy? Bo potrzebujemy oryginalnej serii danych razem z serią, która jest przesunięta o 1 punkt.
Czyli, po przesunięciu serii/usunięciu komórek, mamy zaczynając od pierwszej daty parę:

2017-04-04 540 zł

Chociaż ta para nie jest już w pierwszym wierszu danych, ale ważne że kolejnym datom przypisane są dwie wartości:

2017-04-10 540 zł
2017-04-10 1 630 zł

Jedna z poprzedniej daty i druga – zmieniony stan na dany dzień.

Porada 301 - Wykres schodkowy daty 06

To już wszystkie zmiany jakich potrzebujemy – zaznaczamy zakres danych i wstawiamy wykres liniowy ze znacznikami (żebyśmy widzieli położenie punktów danych).
Porada 301 - Wykres schodkowy daty 07

Pozostaje nam co najwyżej dopracowanie szczegółów, jak zmiana kolorów, tytułu, wartości maksymalnych itp.
Uwaga! Czasami Excel może nie rozpoznać, że ma do czynienia z datami na osi x i musimy to sami zmieniać, czyli klikamy w oś x, naciskami Ctrl + 1 i we właściwościach osi zmieniamy ją na oś daty.

Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP

Wykres sunburst jako 'koło życia' (SPOCO Puls) — porada #300

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.

Porada 300 - Wykres sunburst jako 'koło życia' (SPOCO Puls) (1)

W poprzedniej poradzie omawiałem jak można analogiczny wykres stworzyć za pomocą wykresu pierścieniowego. W tym wpisie omówimy sposób wykorzystujący wykres typu SunBurst (od Excela 2016).

Jak już było wspomniane w poprzednim wpisie nad wykresem typu SunBurst mamy mniejszą kontrolę, ale tworzy się go zdecydowanie szybciej.

Zaczniemy od wyjaśnienia czym w ogóle jest wykres SunBurst. Razem wykresem mapy drzewa należy do wykresów schematów hierarchii i właśnie do pokazywania hierarchii pomiędzy wartościami w poszczególnych kategoriach i podkategoriach nadaje się najbardziej.

Stworzymy sobie szybko wykres SunBurst na podstawie danych z przykładu Microsoftu opisującego ten wykres.

Porada 300 - Wykres sunburst jako 'koło życia' (SPOCO Puls) (2)

Widać, że mamy 3 poziomy hierarchii w przykładowych danych. Pierwszy to Kategoria, później jest podkategoria, a na końcu jest podział tematyczny. 

Przyglądając się danym można zauważyć, że nie wszystkie podkategorie mają przypisany temat. Nie jest to konieczne do prawidłowego stworzenia wykresu SunBurst i nawet pod poziomem kategorii (najwyższym) nie musimy uzupełniać hierarchii jeśli jej nie ma. Excel odpowiednio dopasuje wykres.

W zrozumieniu hierarchii powyższych danych ułatwiają puste pole w miejscach, gdy wartość w poziomie hierarchii się powtarza, albo dany poziom hierarchii nie istnieje.

W kolumnie Przychód znajduje się wartość przychodu związana z danym poziomem hierarchii (nie zawsze oznacza to najniższy poziom).

Ponieważ dane zostały zebrane w formie tabeli wystarczy zaznaczyć dowolną komórkę, a następnie wstawić wykres SunBurst (w polskim Excelu nazywa się pierścieniowy, ale działa całkiem inaczej).

Porada 300 - Wykres sunburst jako 'koło życia' (SPOCO Puls) (3)

Na podstawie przygotowanej hierarchii wykres SunBurst tworzy się błyskawicznie. Trzeba zaznaczyć, że hierarchie są uporządkowane malejąco od tych, które na najwyższym poziomie zgromadziły największy przychód, czyli w tym przykładzie jest to kategoria — Children's Books. Gdyby wartości były równe to wykres SunBurst sortowałby się w odwrotnej kolejności niż są wpisane hierarchie.

Porada 300 - Wykres sunburst jako 'koło życia' (SPOCO Puls) (4)

Poznaliśmy zasadę działania wykresu SunBurst, ale jak za jego pomocą stworzyć koło życia lub SPOCO Puls? Przede wszystkim to kwestia przygotowania danych. Wystarczą nam 4 serie plus domyślne 5 poziomów dla naszego przykładu. Przykładowo jeśli serii społeczność przypiszemy poziom 3, to chcemy, żeby wypełniła się do tego poziomu, czyli ma być wartość w kolumnie C, D i E odpowiednio 1,2,3, a kolumny F i G powinny być puste. Dopiero kolumna H zawiera wartości przypisane do danego poziomu hierarchii. Żeby zachować kolejność z danych na wykresie zaczynamy od wartości 1000 i zmniejszamy ją systematycznie o 1. Taka różnica nie powinna być widoczna na wykresie przy dużych liczbach i wszystkie serie powinny być tych samych rozmiarów.
Formuła wykorzystana do wygenerowania punktów serii:

=JEŻELI($A2>=C$1;C$1;"")

Porada 300 - Wykres sunburst jako 'koło życia' (SPOCO Puls) (5)

Gdy mamy gotowe dane zaznaczamy zakres B2:H5 i z karty Wstawianie wstawiamy wykres SunBurst (Systemy hierarchii -> wykres pierścieniowy). Do tego wykresu dokładamy legendę i etykiety (z zielonego plusa obok wykresu, gdy jest zaznaczony wykres) i nasz wykres wygląda w dużej mierze jak koło życia.

Porada 300 - Wykres sunburst jako 'koło życia' (SPOCO Puls) (6)

Przy tym typie wykresu łatwo zmienia się kolor dla serii (promienia) wystarczy kliknąć na niego dwa razy i wybrać dowolny kolor wypełnienia, obramowania itp.

Porada 300 - Wykres sunburst jako 'koło życia' (SPOCO Puls) (7)

Trzecie kliknięcie spowoduje zaznaczenie serii od najniższego poziomu (najbardziej z brzegu okręgu) do konkretnego punktu z serii (promienia), w który kliknęliśmy.

Po zmianie kolorów wypełnień, obramowań i ewentualnie etykiet uzyskujemy gotowy wykres, na którymwidać nasze numery poszczególnych poziomów zaczynając od nazw serii, które pojawiają się jeśli jest wystarczająco dużo miejsce na ich wyświetlenie w przepisanym im punkcie serii.

Porada 300 - Wykres sunburst jako 'koło życia' (SPOCO Puls) (8)

Mamy jednak problem jeśli wykres ten chcemy zbliżyć do szablonu SPOCO Puls, poniewż wykres SunBurst jest nietypowym wykresem, który został dodany do Excela 2016 i nie ma wielu funkcjonalności, które mają‘klasyczne’ wykresy np.: nie możemy jego tytułu połączyć z komórką arkusza. Nie da się też wkleić w niego obrazu, taki obraz trzeba by grupować, a takie rozwiązanie jest mniej wygodne.

Dlatego tworząc wykres SPOCO pulsu ograniczamy się raczej tylko do legendy, której kolory mogą ciut się różnic od kolorów na wykresie w zależności, od którego schemat wykresu wybraliśmy.

Porada 300 - Wykres sunburst jako 'koło życia' (SPOCO Puls) (9)

Istotne jest też, że wykres SunBurst jako maksymalną wartość prezentowaną uznaje najwyższy punkt w seriach jakie ma, czyli jeśli zmienimy punkty przydzielone serii ludzie z 5 na 4, to wykres będzie prezentował się odpowiednio inaczej, bo żadna seria nie ma poziomu ‘5’.

Porada 300 - Wykres sunburst jako 'koło życia' (SPOCO Puls) (10)

W tym przykładzie potrzebowaliśmy 5 poziomów, a ze względu na przygotowanie danych uzyskaliśmy 6 (nazwy serii i numery od 1 do 5). Jeśli bardzo by Ci zależało, żeby pominąć poziom nazw serii, to byłoby to możliwe tylko wtedy, gdyby kolejne serie na najwyższym poziomie miały naprzemiennie inne nazwy. 

Ponieważ zależy nam, żeby te nazwy wyglądały jako jedynki, to pierwsza seria mogła by być„1” (samą jedynką), a druga „1 „ (jedynką ze spacją), trzecia znów tylko „1” itd. Tylko, że pojawia się problem, gdy przypiszemy 0 punktów, któreś z serii. Jeśli wszystkie etykiety poziomów hierarchii będą pustymi komórkami Excel zacznie przykładowo pokazywać 3 promienie zamiast 4, więc osobiście odradzam takie kombinowanie i pozostanie z rozwiązaniem, gdzie poziomem najwyższym (najgłębiej w okręgu) są nazwy serii. Te punkty będą zawsze widoczne na wykresie ponieważ są wpisane na stałe.

Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP

Wykres pierścieniowy jako 'koło życia' (SPOCO Puls) — porada #299

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.

Porada 299 - Wykres pierścieniowy jako 'koło życia' (SPOCO Puls) 01

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.

Porada 299 - Wykres pierścieniowy jako 'koło życia' (SPOCO Puls) 02

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).

Porada 299 - Wykres pierścieniowy jako 'koło życia' (SPOCO Puls) 03

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%).

Porada 299 - Wykres pierścieniowy jako 'koło życia' (SPOCO Puls) 04

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).

Porada 299 - Wykres pierścieniowy jako 'koło życia' (SPOCO Puls) 05

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.

Porada 299 - Wykres pierścieniowy jako 'koło życia' (SPOCO Puls) 06

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.

Porada 299 - Wykres pierścieniowy jako 'koło życia' (SPOCO Puls) 07

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.

Porada 299 - Wykres pierścieniowy jako 'koło życia' (SPOCO Puls) 08

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.

Porada 299 - Wykres pierścieniowy jako 'koło życia' (SPOCO Puls) 09

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.

Porada 299 - Wykres pierścieniowy jako 'koło życia' (SPOCO Puls) 10

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ę)

Porada 299 - Wykres pierścieniowy jako 'koło życia' (SPOCO Puls) 11

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).

Porada 299 - Wykres pierścieniowy jako 'koło życia' (SPOCO Puls) 12

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!

Porada 299 - Wykres pierścieniowy jako 'koło życia' (SPOCO Puls) 13

Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP