Idealny proces raportowy w programie Excel obejmuje ścieżkę Power Query > Power Pivot > Tabela przestawna. Niestety możliwości graficzne tabel i wykresów przestawnych są dość ograniczone, bo nie możemy na nich wstawiać ani wykresów punktowych, ani żadnego z nowych wykresów w programie Excel 2016 lub 365. Jak sobie z tym poradzić?
Wszystkie nowe wykresy (Histogram, Pareto, Skrzynka-wąsy, Mapa drzewa, Kaskadowy, Lejkowy) nie mogą być wstawione w tabeli przestawnej. A co jeśli nasze dane są w tabeli przestawnej? W poniższym artykule wykorzystamy kilka trików, które umożliwią wykorzystanie tych wykresów. Podobne sztuczki były już wykorzystywane w artykułach na temat wykresu punktowego w tabeli przestawnej oraz fragmentatorów.
Przykład będzie bazował na wykresie typu Mapa drzewa (ang. treemap). Jest to wykres służący do przedstawiania dużych zbiorów danych, które zostały uporządkowane hierarchicznie lub zostały skategoryzowane. Więcej o mapie drzewa, zasadach jej tworzenia i wykorzystywania można znaleźć w artykule Mapa drzewa. Mapę drzewa możemy znaleźć na liście wykresów dostępnych w Excelu 2016.
Przygotowanie danych w programie Excel
Wykres przestawny w formie mapy drzewa zostanie przygotowany na podstawie poniższych danych źródłowych: szczegółowe dane dotyczące zakupu poszczególnych produktów.
Utwórzmy z danych tabelę przestawną: jako Wartości pokażmy sumę ze Sprzedaży, jako Wiersze – Podkategorie produkt i Nazwy produktu.
Budowa dynamicznych nazw zdefiniowanych jako źródło wykresu
Spróbujemy teraz na podstawie danych z tabeli przestawnej stworzyć wykres w postaci mapy drzewa – wykres niestety nie jest dostępny.
W tym ćwiczeniu wykorzystam podejście z dynamicznymi nazwami zdefiniowanymi. Stwórzmy na podstawie tabeli przestawnej nazwy zdefiniowane (Ctrl+F3).
Pierwsza z nazw odnosi się do wartości Sprzedaży. Zastosowanie w odwołaniu formuły Przesunięcie umożliwi aktualizowanie przeszłego wykresu wraz ze zmianą zakresu tabeli przestawnej.
Kolejna nazwa zdefiniowana odwołuje się do Kategorii i także stosujemy formułę Przesunięcie.
W Menedżerze nazw możemy podejrzeć utworzone zakresy.
Budowa mapy drzewa na tabeli przestawnej Excel
Kolejnym krokiem będzie wstawienie Mapy drzewa (na razie bez wskazania żadnych serii danych).
Dopiero teraz wskażmy źródło danych.
Dodajmy serie dane w sposób jak poniżej – jako wartość serii wskażmy zdefiniowaną wcześniej nazwę „dane” (plik Excel nowi nazwę raport, stąd taka nazwa wartości serii).
Natomiast etykiety będą się odwoływać do nazwy zdefiniowanej „Kategorie”.
Wskazane źródła danych wyglądają jak poniżej:
Mapa drzewa na podstawie powyższych serii wygląda jak poniżej.
Dodajmy jeszcze etykiety.
Ostatnim krokiem będzie wstawienie filtrów: osi czasu oraz fragmentatorów. Opcje te są dostępne w wersji Excel 2016 w zakładce Wstawianie. Możemy sprawdzić, że Mapa drzewa reaguje na zamiany na osi oraz we fragmentatorach – jak widać poniżej działa świetnie! Powyższe czynności pomogły stworzyć Mapę drzewa, która jest interaktywna.
Szablon wykresu Excel do pobrania
Wszystkie szczegóły dotyczące Mapy drzewa oraz powyższych kroków są dostępne w załączonym szablonie Excel z mapą drzewa na wykresie przestawnym.
Robię według Twoich instrukcji podobną tabelę, ale brakuje mi w opisie więcej szczegółów.
Na końcu nazwy pokazują mi się tylko z kolumny A. Całość nie grupuje się tak jak u Ciebie kolorami. Co mogło pójść źle?
Seria danych Kategorie ma w formule ostatni argument o wartości 2, który decyduje o szerokości 2 kolumn. Jeśli to nie pomoże, podeślij plik – zerknę do środka 🙂
Witam,
Mam dokładnie tą samą sytuację jaką opisała Agata, przy czym sprawdziłem formułę definiującą kategorię, mam wartość 2 i zaznacza się odpowiedni zakres. Mimo wszystko zwracana jest tylko pierwsza kolumna. Próbowałem już prawie wszystkiego i nic nie pomaga, próbuję dalej:)
Może kwestia Excel 2016 vs 365? Podesłanie pliku pomogłoby znaleźć przyczynę – zapraszam.
Bartku, dziękuję za odpowiedź. Powiem tak, pracowałem nad powtórzeniem Twoich kroków w moim środowisku Excela przez kilka godzin ale ostatecznie nie udało mi się osiągnąć celu. W jednym przypadku jednak dostałem to o co chodzi ale na danych testowych. Później nie udało mi się powtórzyć tego samego dla danych produkcyjnych. Przygotuję plik i prześlę go na Twój adres mailowy. Będę bardzo wdzięczny gdybyś mógł na to spojrzeć.
P.S.
Coś z tą wersją Excela może jednak być nie tak. Pracuję na 32 bitowej wersji OFFICE 2016 dla Użytkowników Domowych i Małych Firm i nie mam np. funkcji POŁĄCZ.TEKSTY, która przecież jest w wersji 2016
Czekam na maila. POŁĄCZ.TEKSTY doszła do Excela 365, w 2016 jej nie ma.
Czy został już rozwiązany problem z grupowaniem? Mam ten sam problem, wersja excela 365.
Dziwna sprawa. U mnie działa i pobrany plik też działa. Czy w załączonym pliku wykres przestawny działa? Czy w Twoim pliku tabela przestawna ma ustawiony układ na Tabelaryczny z powtórzonymi wszystkimi elementami?
Dzień Dobry,
Co może być powodem tego, że za każdym razem gdy zamknę plik i otwieram go na nowo, jestem zmuszony zaznaczać dane od początku?
Po otworzeniu pliku wykres przedstawia tylko pierwszą wartość, więc podmieniam zaznaczenie, następnie ustawiam je na nowo i wszystko działa.. do momentu ponownego otworzenia pliku :/
Excel wersja 2016
Problemem może być właśnie Excel 2016. Proszę podesłać plik, abym mógł sprawdzić, czy w 365 działa prawidłowo.
Mam problem z wykresem kaskadowym. Mimo zmiany zakresu danych w zdefiniowanej nazwie wykres „nie widzi” zmian i cały czas pokazuje pierwotny zakres. Zna ktoś rozwiązanie tego problemu?
Potwierdzam, że ostatnie aktualizacje Excel 365 sprawiły, że opisany sposób nie działa.