Jak stworzyć dynamiczny wykres?
Większość użytkowników Excela potrafi tworzyć proste wykresy z danych w arkuszu. Są to zazwyczaj wykresy robione „na sztywno”, czyli to co zaznaczysz do serii danych, znajdzie się na wykresie i nie można tego zmienić, chyba, że edytujesz zakres serii danych. Jest to dosyć czasochłonne, jeśli musisz nanosić na wykres każdą zmianę danych, np. dodanie informacji o sprzedaży za zeszły tydzień. W takiej sytuacji nie pozostaje nic innego jak stworzenie dynamicznego wykresu. Jest na to kilka sposobów.
SPIS TREŚCI
Dynamiczny wykres z tabeli
Jest to chyba najprostszy sposób na samoaktualizujący się wykres. Załóżmy, że masz już w arkuszu dane, na podstawie których będzie tworzony wykres. Wystarczy, że te dane przekonwertujesz na tabelę.
Rysunek 1. Przykładowa tabela z danymi za kolejne miesiące.
Zaznacz dowolną komórkę w obrębie danych i użyj skrótu CTRL + T. Pojawia się okienko Tworzenie tabeli, które podaje zakres danych. Upewnij się, że wszystkie dane są zaznaczone i zaznaczona jest opcja Moja tabela ma nagłówki. Kliknij Enter.
Rysunek 2. Okno tworzenia automatycznej tabeli w Excelu.
Teraz zaznacz zakres całej tabeli i na karcie Wstawianie wybierz typ wykresu, który ma zostać wstawiony.
Rysunek 3. Wybierz typ wykresu, który Ci odpowiada.
Wykres, który wyświetlił się w arkuszu będzie automatycznie dodawał nowe dane, kiedy uzupełnisz swoją tabelę o nowy wiersz lub kolumnę, czyli nie musisz już przejmować się aktualizacją serii danych, gdy tabela się rozrasta. Zobaczmy to na przykładzie: dodaj nowy miesiąc, czyli lipiec wraz z liczbą w kolumnie obok (po prostu wpisz te wartości). Wtedy tabela rozszerza się o nowy wiersz, a na wykresie pojawia się nowy element (lipiec).
Rysunek 4. Nowy wiersz w tabeli, to nowa kolumna na wykresie.
Wykres przestawny, fragmentatory i oś czasu
Wykres przestawny z fragmentatorami jest bardzo wygodną formą dynamicznego wykresu. Zaznacz swoją tabelę z danymi, a następnie, z karty Wstawianie wybierz polecenie Wykres przestawny.
Tworzenie dynamicznego wykresu przestawnego
Rysunek 5. Wykres przestawny na wstążce Excela.
Pojawi się okno, w którym trzeba zdeklarować zakres danych, który będzie wykorzystywany do wykresu i lokalizację wykresu. Najczęściej nie trzeba nic w nim zmieniać, bo domyślne opcje są prawidłowe, więc naciśnij OK.
Rysunek 6. Okno tworzenia dynamicznego wykresu przestawnego.
Wtedy, w wybranej lokalizacji, pojawi się lista pól tabeli przestawnej. Jest ona zawsze tworzona, gdy wstawiasz wykres przestawny do arkusza.
Więcej informacji o dynamicznych formułach
Sprawdź za darmo calculatic.pl gdzie znajdziesz wiele więcej przykładowych zadań!
Rysunek 7. Lista pól tabeli przestawnej.
Z listy wybierz pola, które mają być przedstawione na wykresie, np. pole miesiąca przeciągnij do obszaru Oś, a pole sprzedaży do obszaru wartości.
Rysunek 8. Pola przeciągnięte do obszaru Oś i Wartości.
Wykres automatycznie wyświetla wybrane przez Ciebie pola. W prawym dolnym rogu wykresu widoczne są znaki + i -. Służą one do zwijania i rozwijania pól, np. możesz pokazać zagregowaną sprzedaż tylko latami lub z podziałem na lata i miesiące. W lewym dolnym rogu widoczne są filtry wykresu. Dzięki nim możesz zawężać wykres do wybranych lat lub miesięcy. Każda zmiana jest od razu widoczna na wykresie.
Rysunek 9. Gotowy wykres przestawny.
Fragmentator dodany do wykresu przestawnego
Wyświetlanie danych może być jeszcze łatwiejsze i szybsze dzięki fragmentatorom. Fragmentator jest to tzw. szybki filtr. Kliknij na wykres, a pojawi się na wstążce Excela karta Analiza. Wybierz z niej polecenie Wstaw fragmentator.
Rysunek 10. Przycisk wstawiający fragmentator do wykresu przestawnego.
Z okna, które się pojawiło wybierz następnie pola, które masz zamiar filtrować. W naszym przykładzie może być to pole miesiąca.
Rysunek 11. Okno fragmentatora.
Teraz klikając na wybrane parametry wykres jeszcze szybciej dostosowuje się do wymagań. We fragmentatorach można zaznaczać pojedyncze pola, jak i kilka pól. Żeby wybrać kilka pól trzeba zaznaczyć opcję wyboru wielokrotnego i przeciągnąć lub klikać na wybrane parametry.
Rysunek 12. Gotowy fragmentator filtruje zarówno tabelę przestawną, jak i wykres przestawny.
Oś czasu na wykres przestawnym pozwala na dynamiczną analizę danych
Kiedy dane zawierają informacje o czasie jakiegoś zdarzenia (są to wartości typu data, a nie np. teksty), które prezentujesz na wykresie, zamiast fragmentatora równie dobrze można użyć Osi czasu. Kliknij na wykres, a pojawi się karta Analiza wykresu przestawnego. Wybierz z niej polecenie Wstaw oś czasu i kolejno wybierz odpowiednie pola daty. Teraz kliknij na strzałkę w oknie osi i wybierz miarę czasu, jaką chcesz filtrować. Przeciągając myszką przez kafelki jednostek czasu można szybko dostosować swój wykres.
Rysunek 13. Oś czasu wykresu przestawnego.
Zakres danych, menadżer nazw i pasek przewijania
Kiedy korzystasz z dynamicznego wykresu opartego na danych tabeli, jak w pierwszym punkcie, a tabela ta ciągle rośnie w dane, dojdzie do momentu, że wykres przestanie być czytelny z racji za dużej ilości prezentowanych informacji. Na szczęście Excel ma sposób i na to. Ilość wyświetlanych danych na wykresie można z powodzeniem dynamicznie zawęzić. W tym przypadku, można pracować na zwykłym zakresie danych (a nie na tabeli automatycznej, którą pokazywałem na początku artykułu). Załóżmy, że jedna kolumna Twoich danych zawiera daty (rok + miesiąc), a druga ilość sprzedaży i chcesz pokazywać na wykresie zawsze 6 miesięcy. Nagłówki danych znajdują się w pierwszym wierszu arkusza, a dane zaczynają się w drugim wierszu.
Rysunek 14. Przykładowa tabela danych.
Konieczne będzie wykorzystanie funkcji PRZESUNIĘCIE, paska przewijania i nazw. Dzięki temu, można zdefiniować mniejszy zakres, który będzie widoczny na wykresie, który możesz dynamicznie zmieniać.
W dowolnej komórce arkusza (np. G1) wpisz liczbę, która będzie określać o ile wartości pasek przewijania przesuwa się przy pojedynczym kliknięciu. W naszym przykładzie na razie niech będzie to 1. Czyli po naciśnięciu strzałki paska przesuwania na widoku wykresu pojawi się jako pierwsza data z jednego wiersza poniżej wiersza z pierwszą datą, jeśliby to było 4 to pojawi się data z czwartego wiersza poniżej wiersza z pierwszą datą. Zaznacz komórkę z tym numerem (G1), na karcie Formuły wybierz polecenie Menadżer nazw, a następnie Nowy.
Rysunek 15. Aby stworzyć nazwę, zaznacz komórkę, przejdź do Formuły, naciśnij Menedżer nazw i naciśnij Nowy.
Nadaj nazwę komórce tej komórce, np. „Suwak”.
Rysunek 16. Nadanie komórce nazwy „Suwak”.
W podobny sposób nadaj nazwę dynamicznemu zakresowi miesięcy za pomocą funkcji PRZESUNIĘCIE. W naszym przykładzie, w obszarze Odwołuje się do, należy użyć funkcji:
=PRZESUNIĘCIE('Szablon (4)'!$A$2;Suwak;0;6;1)
Pasek przewijania dzięki temu będzie brał pod uwagę wartości poczynając od komórki A2, przesunięte o liczbę wierszy określoną w komórce Suwak, przesunięte o 0 kolumn, zwracając zakres składający się z 6 wierszy i 1 kolumny. Zakres ten nazwijmy Miesiące.
Rysunek 17. Nadanie nazwy formule, która dynamicznie będzie wskazywać miesiące do wykresu.
Nazwać należy też dynamiczny zakres sprzedaży w taki sam sposób, używając funkcji:
=PRZESUNIĘCIE('Szablon (4)'!$B$2;Suwak;0;6;1)
Zakres ten nazwijmy Sprzedaż.
Rysunek 18. Tworzenie nazwy kolumny Sprzedaż.
Teraz zaznacz dane w arkuszu i wstaw dowolny typ wykresu z karty Wstawianie. Kliknij na wykres prawym przyciskiem myszki i wybierz Zaznacz dane.
Rysunek 19. Kliknij prawym przyciskiem myszy na wykresie i wybierz Zaznacz dane.
Następnie zaznacz serię danych i edytuj ją.
Rysunek 20. Zaznaczanie i edytowanie serii, aby wykres stał się dynamiczny.
W wartości serii należy wpisać nazwę dynamicznego zakresu sprzedaży poprzedzoną nazwą arkusza. W omawianym przykładzie będzie to:
='Szablon (4)'!Sprzedaż
Rysunek 21. Wartości serii danych.
Teraz wybierz opcję edycji osi poziomej.
Rysunek 22. Przycisk przenoszący do edycji etykiet osi poziomej.
Zakres etykiet osi należy zdefiniować jako dynamiczny zakres dat, czyli używać stworzonej wcześniej nazwy (poprzedzonej nazwą arkusza):
='Szablon (4)'!Miesiące
Rysunek 23. Wskazanie dynamicznego zakresu dat na wykresie.
Po nazwaniu dynamicznych obszarów i stworzeniu wykresu należy stworzyć pasek przewijania, za pomocą którego będzie można sterować widokiem wykresu. Na karcie Deweloper w poleceniu Wstaw i dalej Kontrolki formularza, wybierz ikonę paska przewijania.
Rysunek 24. Wstawianie paska przewijania.
Wstaw ją do arkusza, a następnie naciśnij na tym pasku przewijania prawym przyciskiem myszy. Wybierz Formatuj formant.
Rysunek 25. Kliknij prawym i wybierz Formatuj formant.
Ustaw pożądane właściwości i co najważniejsze, łącze komórki musi się odnosić do komórki nazwanej wcześniej Suwak (G1). Zaakceptuj wpisane właściwości i pozostaje tylko sprawdzić czy wykres działa prawidłowo, przesuwając suwak i patrząc, czy wartości na wykresie dynamicznie się zmieniają.
Rysunek 26. Połączenie formantu z komórką G1, a tym samym z wykresem.
