Dynamiczny wykres bąbelkowy w Excelu

by Marcin

dynamiczny_wykres_babelkowy

Pisałem już kiedyś o dynamicznych zakresach, które umożliwiają bardzo szybkie i skuteczne dodanie odrobiny życia wykresom Excela. Dzisiaj chciałbym kontunuować ten temat, tym razem  z wykresem bąbelkowym w roli głównej. Bezpośrednią inspiracją była dla mnie jedna z  prezentacji pokazanych na konferencji TED. Spodobało mi się strasznie jak te wszystkie „bąbelki” wędrują po ekranie i postanowiłem przygotować wykres bąbelkowy, który będzie pokazywał ścieżkę od „dawniej” do „teraz”.

Jak przed chwilą wspomniałem wykorzystamy wykres bąbelkowy (bubble chart). Tabela z danymi dla tego typu wykresu oraz sam wykres (przygotowany w oparciu o dane z pokazanej tabeli) wyglądają mniej więcej tak:

Wykres bąbelkowy w Excelu

Wykres bąbelkowy w Excelu

Dla każdego bąbelka potrzebujemy określić jego położenie (X i Y) oraz wielkość. Proste.

Pierwszym krokiem prowadzącym do ożywienia naszego wykresu jest przygotowanie tabeli z danymi źródłowymi. Ponieważ myślenie o wykresie bąbelkowym wykorzystało całą moc mojego „procesora” nie starałem się nawet szukać rzeczywistych danych. Po prostu … zmyśliłem je sobie. Moja tabelka wygląda tak:

Tabelka z danymi dla dynamicznego wykresu bąbelkowego

Tabelka z danymi dla dynamicznego wykresu bąbelkowego

Dane przygotowane są dla lat 1999-2005 oraz dla sześciu krajów.  Jak sam widzisz (po szybkiej analizie tabelki) w każdym przypadku nie jest to nic innego jak tylko pozycja X i Y bąbelka oraz jego wielkość.

Zdecydowałem także, że chciałbym, aby „ogon” z danymi  historycznymi  „ciągnął” się za bierzącymi danymi na „odległość”  czterech lat.  Czyli zobaczysz maksymalnie pięć  bąbelków dla każdego z krajów.

Dynamiczny wykres bąblekowy - założenia

Dynamiczny wykres bąbelkowy - założenia

Oczywiście nic nie stoi na przeszkodzie, aby Wasz „ogon” był dłuższy lub krótszy w zależności od potrzeb.

Po kolejnych latach poruszać  będziemy się przy pomocy paska przewijania (formanty arkusza). Wygląda to mniej więcej tak, jak na rysunku poniżej.

Pasek przewijania służący do sterowania naszym wykresem

Pasek przewijania służący do sterowania naszym wykresem

Pasek przewijania podlinkowany jest do jednej z komórek w skoroszycie.  Nadałem jej nazwę „przesuniecie” (przyznam, że trochę niefortunnie, ponieważ funkcja korzystająca z tej nazwy to PRZESUNIĘCIE, co może nieco „zamydlić” zrozumienie całości).

Cała „magia” rozwiązania to odpowiednie wykorzystanie dynamicznych zakresów sterujących tym jakie dane są akurat wyświetlane na wykresie. Ponieważ zdecydowałem się na maksymalnie 5 bąbelków, jako źródło danych dla wykresu zdefiniowałem 5 serii.

Wykres bąbelkowy - zdefiniowane serie danych

Wykres bąbelkowy - zdefiniowane serie danych

Seria główna („Serie1” na rysunku powyżej. Od razu zwracam uwagę, aby nie sugerować się za bardzo samymi nazwami. Nic nie znaczą), która odpowiada najbardziej aktualnemu bąbelkowi  zdefiniowana jest następująco:

Dynamiczny wykres bąbelkowy - dane serii głównej

Dynamiczny wykres bąbelkowy - dane serii głównej

ToShow_X, ToShow_y oraz ToShow_Bubble to odpowiednio zdefiniowane dynamiczne zakresy. Wszystko po to, aby wraz z wyborem  roku, dane dla odpowiedniego bąbelka automatycznie przesywały się po tabeli z danymi, zawsze wskazując położenie i rozmiar bąbla dla wybranego roku.

Dynamiczne zakresy zdefiniowałem następująco.

ToShow_X

=PRZESUNIĘCIE(Sheet1!$D$7;Przesuniecie*3;0;1;7)

ToShow_Y

=PRZESUNIĘCIE(Sheet1!$D$7;Przesuniecie*3+1;0;1;7)

ToShow_Bubble

=PRZESUNIĘCIE(Sheet1!$D$7;Przesuniecie*3+2;0;1;7)

„Przesuniecie” to wspomniana wcześniej, zdefiniowana nazwa odwołująca się do komórki połączonej z naszym paskiem przewijania. Komórka D7 to początek naszej tabelki z danymi. Dla jasności załączam obrazek.

Pierwsza komórka tabeli z danymi służy jako punkt odniesienia zdefiniowanych pzrezze mnie dynamicznych zakresów

Pierwsza komórka tabeli z danymi służy jako punkt odniesienia zdefiniowanych pzrezze mnie dynamicznych zakresów

Kolejne serie zdefiniowane są bardzo podobny do siebie sposób:

Dynamiczny wykres bąbelkowi - definiowanie zakresów

Dynamiczny wykres bąbelkowi - definiowanie serii dla "ogona"

Wszystkie zakresy dynamiczne, do których odwołują sie kolejne serie mają także tę samą strukturę. Przyjrzyjmy się zakresowi dla serii 2 (pierwszego bąbelka z naszego „ogona”)

ToFollow_X1

=JEŻELI(Przesuniecie<1;Sheet1!$D$2:$I$2;PRZESUNIĘCIE(Sheet1!$D$7;(Przesuniecie-1)*3;0;1;7)) [/vb] Tak jak poprzednio „Przesuniecie” to zdefiniowana nazwa odwołująca się do komórki połączonej z naszym paskiem przewijania. Zakres $D$2:$I$2 ma z kolei na celu „oszukanie” wykresu i niepokazywanie bąbelków, które akurat nie powinny być widoczne. W moim skoroszycie zakres ten wygląda następująco:

Wykres bąbelkowy - dodatkowy zakres

Wykres bąbelkowy - dodatkowy zakres

ToFollow_X1

=JEŻELI(Przesuniecie<1;Sheet1!$D$3:$I$3;PRZESUNIĘCIE(Sheet1!$D$7;(Przesuniecie-1)*3+1;0;1;7)) [/vb] ToFollow_Bubble1 [vb] =JEŻELI(Przesuniecie<1;Sheet1!$D$2:$I$2;PRZESUNIĘCIE(Sheet1!$D$7;(Przesuniecie-1)*3+2;0;1;7)) [/vb] Dla wszystkich pozostałych zakresów struktura jest ta sama, zmieniają się tylko dwa elementy pokazane na rysunku poniżej: [caption id="attachment_370" align="aligncenter" width="550" caption="Sposób definiowania zakresów dynamicznych dla wykresu bąbelkowego"]Sposób definiowania zakresów dynamicznych dla wykresu bąbelkowego[/caption]

W zasadzie to tyle. Odpowiednie przygotowanie zakresów dynamicznych i podpięcie ich pod serie wykresu bąbelkowego zdaje się rozwiązywać problem. Na koniec ustawiłem tylko kolory dla kolejnych serii (nie jest idealnie, ale nie miałem za wiele czasu na poszukiwania idealnych kompozycji kolorystycznych), wypiłem herbatę i ….. napisałem ten wpis 🙂

Na koniec, jak zwykle, przykładowy plik do ściągnięcia. Tym razem przygotowałem go w Excelu 2007. Dla wszystkich posiadaczy Excela 2003 w paczce znajduje się także wersja wyeksportowana do tego właśnie formatu. Powinno działac bezproblemowo, ale prawie na pewno kolory staną sie „dzikie”.

Marcin

Download

{ 1 comment… read it below or add one }

stan1

Dobre

Leave a Comment

Previous post:

Next post: