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.