Tabela przestawna jest najszybszym sposobem pracy z uporządkowanymi danymi w Excelu, wykresy to najszybszy sposób ich analizy, zaś wykres punktowy to najbardziej uniwersalny z nich. Jeśli połączyć ze sobą te wszystkie elementy, otrzymujemy wybuchową mieszankę, która pozwoli szybko i interaktywnie analizować dane. Problem w tym, że na tabeli przestawnej nie da się wstawić wykresu punktowego…
Wykres punktowy a tabela przestawna
Chcąc wstawić wykres na tabeli przestawnej, mamy do wyboru ograniczony wachlarz wykresów – nie możemy skorzystać z punktowego, bąbelkowego i giełdowego. Mniejsza o te dwa ostatnie, ale wykres punktowy, naprawdę?! Musi być jakiś sposób…
Rozwiązaniem jest wstawienie zwykłego wykresu i oparcie go o dane, w których wyświetlana jest tabela przestawna.
Przygotowanie danych do wykresu punktowego na tabeli przestawnej
W omawianym przykładzie chcę przeanalizować relację sprzedaży do zysku dla wszystkich pojedynczych klientów w podziale na kategorię produktu. Jeden klient może wystąpić na obrazie końcowym nawet 3 razy, bo kupił w 3 kategoriach produktu. Przygotowuję tabelę przestawną, w której pojawiają się pola Suma z Sprzedaż i Suma z Zysk w podziale na pole Kategoria.
Następnie tworzę nazwy zdefiniowane (Ctrl+F3), które będą odnosić się do każdej kolumny z danymi. W tym przykładzie zakresy danych są statyczne, ale nic nie stoi na przeszkodzie, aby w bardziej zaawansowanych przypadkach posługiwać się magiczną formułą, która sama się rozszerza.
W ten sposób tworzę zakresy danych osi x dla każdej serii oraz oddzielnie dla osi y.
Utworzenie wykresu punktowego na tabeli przestawnej w Excelu
Mając przygotowane nazwy zdefiniowane, stajemy w dowolnym miejscu poza tabelą przestawną i wstawiamy czysty wykresu punktowy. Następnie definiujemy pojedynczo każdą serię, poprzedzając nazwę definiowaną nazwą arkusza.
Tak powstanie wykres, który po odpowiednim sformatowaniu (lekkie obramowanie punktów i przezroczystość) i dodaniu etykiet oraz fragmentatorów (była o nich niedawno mowa w serii o dashboardzie analitycznym) wygląda następująco.
Jak dodać selektywne etykiety do wykresu punktowego?
Do wykresu dodałem jeszcze etykiety dla X najniższych i X najwyższych wartości Zysk w każdej kategorii. W jaki sposób?
Najpierw przygotowałem selektywne etykiety, zgodnie z poniższym rysunkiem.
Następnie utworzyłem dla nich nazwy zdefiniowane:
Nazwy trafiły do opcji Etykiety danych > Wartość z komórek dla wersji Excel >2013).
O opcji tej pisałem w artykule o nowościach w programie Excel 2013 – można dodawać dowolne etykiety do każdego wykresu. Najczęściej wykorzystuję tę opcję właśnie dla wykresu punktowego. Jeśli jesteś użytkownikiem wcześniejszej wersji Excela, możesz skorzystać z dodatku XY Chart Labeler.
Na koniec dodałem za pomocą karty Deweloper możliwość sterowania moim polem TOP/BOTTOM. Efekt końcowy działa następująco:
Plik Excel do pobrania
Tu możesz pobrać szablon Excel z wykresem punktowym na tabeli przestawnej.
PS
Jeśli możesz odłączyć się od tabeli przestawnej, szybciej utworzysz wykres. Wystarczy wkleić dane z tabeli przestawnej do nowego arkusza, zaznaczyć tylko kolumny z danymi i wstawić wykres punktowy:
PS 2
Ładowanie etykiet dla wykresu punktowego trwa bardzo długo, jeśli przesadzimy z ich ilością (np. pokażemy etykiety dla wszystkich punktów) i może zawiesić Excela.
