Zainspirowany wykresami Sankeya, znalezionymi na stronie Międzynarodowej Agencji Energii (IEA), zacząłem się zastanawiać, czy da się taki wykres stworzyć w Excelu. Przykład ze strony IEA pokazuje konsumpcję energii w podziale na jej źródła, natomiast wykres nadaje się wszędzie tam, gdzie chcemy pokazać bilans lub przepływy. Jego stworzenie w Excelu jest trudne, dlatego radzę poszukać innych narzędzi, np. Power BI.
Skąd wziął się wykres Sankeya?
Diagram zawdzięcza swoją nazwę Matthew H. Sankeyowi, który był inżynierem pracującym nad sprawnością silników parowych. Jego wykresy strumieniowe zostały opublikowane w 1898 r., choć podobne wizualizacje powstawały też wcześniej (m.in. na słynnym wykresie inwazji Napoleona na Rosję autorstwa Charlesa J. Minarda z 1869 r.).
Źródło: domena publiczna
Wykres ten tworzą linie, których kierunek oddaje kierunek przepływu, a szerokość linii świadczy o wielkości tego przepływu (im grubsza linia, tym większa wielkość strumienia). Jeżeli chcemy zawrzeć jeszcze więcej informacji na naszym diagramie, możemy też nadać różne kolory poszczególnym liniom, tak aby oddawały one różne kategorie lub żeby pokazać zmiany z jednego stanu procesu w drugi.
Podstawowa wersja Excela nie ma wbudowanego gotowego wykresu Sankeya, dlatego też trzeba go zbudować krok po kroku, bądź też pobrać dodatek E2D3. Oba sposoby zostaną zaprezentowane w niniejszym wpisie.
Jak przygotować wykres Sankeya w programie Excel? – Sposób 1 – Wykres warstwowy
W niniejszym przykładzie naszymi danymi będzie dwuwymiarowa tabela, w której wiersze (wpływy pieniężne do dwuosobowego gospodarstwa domowego) będą punktem początkowym naszego diagramu, a kolumny (rozkład wydatków w budżecie domowym) punktem końcowym. Wartości znajdujące się na ich przecięciu to natężenie przepływy (jego wielkość).
Następnie musimy określić odstęp, który będzie oddzielał nam każdą z kategorii na finalnym wykresie (pusta przestrzeń między poszczególnymi liniami wykresu), w naszym przykładzie będzie to wartość równa 500.
Na koniec na podstawie danych wejściowych trzeba będzie wykonać obliczenia pośrednie, które zostały przedstawione w czterech oddzielnych tabelach.
Krok 1: Przygotuj dane
Z danych źródłowych tworzymy tabelę z wierszem dla każdej możliwej kombinacji wierszy i kolumn danych wyjściowych. Każda kategoria wierszy jest oddzielona dodatkowym wierszem, który tworzy puste miejsca, które będą widoczne na wykresie (oddzielając nam linie od siebie).
Początek naszej tabeli będzie wyglądał następująco:
Dane z kolumny wartość pochodzą z tabeli z danymi. Natomiast kolumna Końcowa pozycja określa kolejność wierszy na końcu diagramu Sankeya, gdzie wartość 1 będzie kończyć się na samej górze wykresu, wartość 2 będzie zajmować drugą pozycję od góry itp.
Finalny wykres Sankeya będzie stworzony na bazie wykresu 100% skumulowany wykres warstwowy, dlatego też aby to było możliwe, musimy rozszerzyć naszą tabelę o kilka dodatkowych punktów danych, określających:
- Przestrzeń nad zacienioną linią Sankeya
- Wartość zacieniowanej linii Sankey
- Przestrzeń pod zacienioną linią Sankeya
Do lewej strony wcześniejszej tabeli dodajemy po cztery kolumny: Above Start, Above Mid 1, Above Mid 2, Above End, dla każdej z trzech sekcji – łącznie 12 nowych kolumn.
Poniżej znajduje się sposób kalkulacji każdej z nowo powstałej kolumny:
- Above Start – określa ilość miejsca wymaganego nad linią Sankeya w punkcie początkowym.
= SUMA(Tabela4[[#Nagłówki];[Wartość]]:[@Wartość])-[@Wartość]
- Above Mid 1 – wyrównuje kolumnę „Above Start”
=[@[Above Start]]
- Above Mid 2 – wyrównuje kolumnę „Above End”
=[@[Above End]]
- Above End – określa ilość miejsca wymaganego nad linią Sankeya w punkcie końcowym
=SUMA([Wartość])-SUMA.WARUNKÓW([Wartość];[Końcowa pozycja];”>=”&[@[Końcowa pozycja]])
- Value Start, Value Mid 1, Value Mid 2, Value End – wartość zacienionej linii Sankeya nigdy się nie zmienia, dlatego ustawiamy wartość wszystkich 4 kolumn tak aby była równa kolumnie Wartość
=[@Wartość]
- Below Start, Below Mid 1, Below Mid 2, Below End – Kolumny obliczają ilość wymaganego miejsca pod zacienioną linią Sankeya. Każde obliczenie jest konstruowane w ten sam sposób. Pobiera SUMA([Wartość]), a następnie usuwa wartość z równoważnych kolumn Start, Mid 1, Mid 2 lub End.
Below Start:
= SUMA([Wartość])-[@[Above Start]]-[@[Value Start]]
Below Mid 1:
= SUMA([Wartość])-[@[Above Mid 1]]-[@[Value Mid 1]]
Below Mid 2:
= SUMA([Wartość])-[@[Above Mid 2]]-[@[Value Mid 2]]
Below End:
= SUMA([Wartość])-[@[Above End]]-[@[Value End]]
Krok 2: Stwórz tabele dla wartości początkowych i końcowych
Gdy już skończymy tworzyć tabelę wyznaczającą relację usytuowania punktów między sobą, musimy jeszcze stworzyć dwie mniejsze tabele określające wartości początkowe (lewa strona wykresu) oraz wartości końcowe (prawa strona wykresu).
Tabela z wartościami początkowymi, w której zawarte są wszystkie kategorie z wierszy danych źródłowych rozdzielonych wierszem ‘Puste’:
Tabela z wartościami końcowymi, w której zawarte są wszystkie kategorie z kolumn danych źródłowych rozdzielonych wierszem ‘Puste’:
Krok 3: Utwórz tabelę odstępów
Ostatnią częścią obliczeń pośrednich jest zakres o nazwie Odstępy. Jest używana jako pozioma oś kategorii dla wykresu. Określa, w którym momencie nachylenie wykresu zaczyna się i kończy.
Gdy wszystkie obliczenia pośrednie są gotowe, można rozpocząć tworzenie wykresu.
Krok 4: Stwórz wykres warstwowy
Nasz ostateczny wykres Sankeya będzie stworzony na podstawie osobnych 100% skumulowanych wykresów warstwowych z 3 seriami danych, stworzonych dla każdego wiersza tabeli określającej relacje między punktami (tabela z kroku 1).
Na początku warto stworzyć pusty wykres 100% skumulowany warstwowo, ponieważ w dalszej kolejności łatwiej będzie definiować źródło danych, jak i też formatować sam wykres.
Gdy mamy pusty obszar wykresu, klikamy na niego i wybieramy Zaznacz dane. Następnie definiujemy 3 serie danych, gdzie Seria1 to są wartości z sekcji Przestrzeń nad zacienioną linią Sankeya dla danej kategorii, Seria2 odpowiada sekcji Wartość zacieniowanej linii, a Seria3 Przestrzeni pod zacienioną linią. Dodatkowo edytujemy również etykiety osi poziomej, zaznaczając dane z tabeli Odstęp.
Po wprowadzeniu danych otrzymamy taki wykres:
Przechodzimy do jego formatowania, po pierwsze klikamy na dolną oś i wybieramy opcję formatuj oś. W oknie, które pokaże nam się po prawej stronie wybieramy w Opcjach osi – Oś daty, przez co zagęszczą nam się punkty. Następnie możemy usunąć wyświetlanie tej osi na wykresie.
W kolejnym kroku należy sformatować oś pionową i z Opcji osi zaznaczyć kwadracik – Wartości w kolejności odwrotnej, co pozwoli odwrócić nam cały wykres. Na koniec usuwamy oś pionową z wykresu, który po zmianach będzie wyglądać tak:
Teraz klikamy kolejno na niebieski i szary obszar wykresu przechodząc później do formatowania i zaznaczając brak wypełnienia – zależy nam aby został tylko środkowy pasek. Następnie zaznaczamy pomarańczowy obszar, z sekcji wypełnienie wybieramy wypełnienie pełne, możemy zmienić kolor i ustawiamy przeźroczystość na poziomie 50%.
Na koniec warto również usunąć linie obramowania, jak i też poziome linie pomocnicze, które usuniemy zaznaczając wykres i przechodząc do zakładki Formatowanie na główmy pasku narzędzi, po czym w lewym rogu klikamy na rozwijaną listę i wybieramy Oś pionowa – główne linie siatki. Zostaną w ten sposób zaznaczone wszystkie linie pomocnicze i możemy je usunąć, klikając klawisz Delete i otrzymując finalnie taki wykres:
Na koniec trzeba jeszcze sformatować wykres tak, aby był transparentny. W tym celu zaznaczamy go, przechodzimy do zakładki Formatowanie i wybieramy kolejno Obszar kreślenia i Obszar wykresu z listy rozwijanej i ustawiamy je jako brak wypełnienia.
Takie działanie trzeba powtórzyć dla każdego wiersza oddzielnie, a gdy będziemy już mieli wszystkie wykresy, należy je na siebie nałożyć.
Większa część pracy już za nami – brakuje jednakże etykiet kategorii, które definiowały by nam początek przepływu oraz jego koniec.
W tym celu należy stworzyć jeszcze dwa ostatnie wykresy w oparciu o tabele określające wartości startowe i końcowe diagramu. Na ich podstawie tworzymy wykres 100% skumulowany kolumnowy.
Następnie w zakładce Projekt wykresu klikamy Przełącz wiersz/kolumną, a później formatujemy oś pionową i odwracamy ją (Formatowanie osi – Wartość w kolejności odwrotnej).
Obszary odpowiadające pustym wierszą należy ustawić im brak wypełnienia, a pozostałym obszarom dodajemy etykiety, można też zmienić im kolory.
Na sam koniec nanosimy oba ostatnie wykresy na wcześniej stworzone przepływy i wykres jest gotowy.
Jak przygotować wykres Sankeya w programie Excel? – Sposób 2 – Dodatek E2D3
Krok 1: Pobierz dodatek E2D3
Do naszej podstawowej wersji Excela możemy pobrać dodatek E2D3, wchodząc w zakładkę Wstawianie > Pobierz dodatki.
Następnie w oknie wyszukiwania wpisujemy E2D3, później klikamy Dodaj przy wyszukanym dodatku.
Po dodaniu E2D3 klikamy w Moje dodatki i wybieramy go z listy dostępnych dodatków.
Krok 2: Wstaw wykres Sankeya
W arkuszu pojawi nam się okno dialogowe z różnymi rodzajami wizualizacji, jakie znajdują się w ramach E2D3. Wśród nich odnajdujemy wykres Sankeya, najeżdżamy na niego myszką i klikami Visualize. Nie należy przerażać się japońską czcionką, która się pojawi po prostu większość przykładów wgranych do tego dodatku została stworzona w języku japońskim, ale wszystko możemy dostosować pod siebie.
Na początku arkusza pojawią nam się trzy tabele z danymi, a na środku będzie znajdowała się docelowa wizualizacja.
Krok 3: Wstaw swoje dane
Ostatnim krokiem będzie zamienienie danych, które są już wpisane na nasze własne.
Pierwszy wiersz odpowiada tytułowi wykresu, zaś następny to są nazwy poszczególnych kolumn, na wykresie są wpisane nad czarną kreską na prawo i lewo od wykresu. Od trzeciego wiersza zaczynają się już dane (po wpisaniu naszych możemy usunąć nadmiarową ilość wierszy).
Finalnie nasz wykres będzie prezentował się następująco:
Na koniec 2 uwagi:
- Wykres nie będzie działał u osoby, która nie posiada tego dodatku
- Po ostatniej aktualizacji Office 365 wszystkie dodatki nie chcą się u mnie otworzyć, żądając stale logowania. Miejmy nadzieję, że wkrótce to naprawią.
Pobierz szablon wykresu Sankeya w Excelu
Tu możesz pobrać szablon wykresu Sankeya w Excelu i wstawić swoje dane.
-1024x269.png)
