Jak utworzyć dynamiczny zakres wykresu w programie Excel

Gdy tworzysz wykres w programie Excel i dane źródłowe ulegają zmianie, musisz zaktualizować źródło danych wykresu, aby upewnić się, że odzwierciedla ono nowe dane.

W przypadku pracy z wykresami, które są często aktualizowane, lepiej jest utworzyć dynamiczny zakres wykresu.

Co to jest dynamiczny zakres wykresu?

Dynamiczny zakres wykresu jest zakresem danych, który aktualizuje się automatycznie po zmianie źródła danych.

Ten dynamiczny zakres jest następnie używany jako dane źródłowe na wykresie. Gdy dane ulegają zmianie, zakres dynamiczny jest natychmiast aktualizowany, co prowadzi do aktualizacji wykresu.

Poniżej znajduje się przykład wykresu wykorzystującego zakres dynamiczny.

Zauważ, że wykres jest aktualizowany o nowe punkty danych dla maja i czerwca natychmiast po wprowadzeniu danych.

Jak utworzyć dynamiczny zakres wykresu w programie Excel?

Istnieją dwa sposoby tworzenia dynamicznego zakresu wykresu w programie Excel:

• Użycie tabeli programu Excel
• Użycie formuł

W większości przypadków użycie tabeli programu Excel jest najlepszym sposobem tworzenia dynamicznych zakresów w programie Excel.

Zobaczmy, jak działa każda z tych metod.

Kliknij tutaj, aby pobrać plik przykładowy.

Używanie tabeli Excela

Używanie tabeli Excela jest najlepszym sposobem tworzenia dynamicznych zakresów, ponieważ aktualizuje się ona automatycznie, gdy dodawany jest do niej nowy punkt danych.

Funkcja tabeli Excela została wprowadzona w programie Excel 2007 w wersji dla systemu Windows i jeśli masz wersje wcześniejsze, nie będziesz mógł z niej korzystać (zobacz następną sekcję dotyczącą tworzenia dynamicznych zakresów wykresów za pomocą formuł).

W poniższym przykładzie widać, że gdy tylko dodam nowe dane, tabela programu Excel rozszerza się, aby uwzględnić te dane jako część tabeli (zauważ, że obramowanie i formatowanie rozszerzają się, aby uwzględnić je w tabeli).

Teraz musimy użyć tej tabeli programu Excel podczas tworzenia wykresów.

Oto dokładne kroki tworzenia dynamicznego wykresu liniowego przy użyciu tabeli Excela:

• Zaznacz całą tabelę Excela.
• Przejdź do karty Wstaw.
• W grupie Wykresy wybierz wykres 'Linia z markerami’.

To wszystko!

Powyższe kroki spowodują wstawienie wykresu liniowego, który będzie automatycznie aktualizowany, gdy dodasz więcej danych do tabeli Excela.

Zauważ, że podczas gdy dodawanie nowych danych automatycznie aktualizuje wykres, usuwanie danych nie spowoduje całkowitego usunięcia punktów danych. Na przykład, jeśli usuniesz 2 punkty danych, na wykresie pojawi się pusta przestrzeń po prawej stronie. Aby to naprawić, przeciągnij niebieski znak w prawym dolnym rogu tabeli Excela, aby usunąć usunięte punkty danych z tabeli (jak pokazano poniżej).

Chociaż posłużyłem się przykładem wykresu liniowego, można również utworzyć inne typy wykresów, takie jak wykresy kolumnowe/barkowe, korzystając z tej techniki.

Korzystanie z formuł Excela

Jak już wspomniałem, korzystanie z tabeli Excela jest najlepszym sposobem tworzenia dynamicznych zakresów wykresów.

Jeśli jednak z jakiegoś powodu nie możesz korzystać z tabeli Excela (być może jeśli korzystasz z Excela 2003), istnieje inny (nieco skomplikowany) sposób tworzenia dynamicznych zakresów wykresów przy użyciu formuł Excela i nazwanych zakresów.

Załóżmy, że masz zestaw danych, jak pokazano poniżej:

Aby utworzyć dynamiczny zakres wykresu z tych danych, musimy:

1. Utworzyć dwa dynamiczne zakresy nazwane przy użyciu formuły OFFSET (po jednym dla kolumny „Wartości” i „Miesiące”). Dodanie/usunięcie punktu danych automatycznie zaktualizuje te zakresy nazwane.
2. Wstaw wykres, który używa zakresów nazwanych jako źródła danych.

Pozwól mi teraz szczegółowo wyjaśnić każdy krok.

Krok 1 – Tworzenie dynamicznych zakresów nazwanych

Poniżej znajdują się kroki tworzenia dynamicznych zakresów nazwanych:

• Przejdź do zakładki 'Formuły’.
• Kliknij na 'Menedżer nazw’.
• W oknie dialogowym Menedżer nazw, określ nazwę jako ChartValues i wprowadź następującą formułę w części Odnosi się do: =OFFSET(Formula!$B$2,,,COUNTIF(Formula!$B$2:$B$100,”<>”))
• Kliknij OK.
• W oknie dialogowym Menedżer nazw kliknij przycisk Nowy.
• W oknie dialogowym Menedżer nazw określ nazwę jako ChartMonths i wprowadź następującą formułę w części Odnosi się do: =OFFSET(Formula!$A$2,,,COUNTIF(Formula!$A$2:$A$100,”<>”))
• Kliknij przycisk Ok.
• Kliknij Zamknij.

Powyższe kroki spowodowały utworzenie w skoroszycie dwóch nazwanych zakresów – ChartValue i ChartMonth (odnoszą się one odpowiednio do zakresu wartości i miesięcy w zbiorze danych).

Jeśli pójdziesz i zaktualizujesz kolumnę wartości, dodając jeszcze jeden punkt danych, zakres nazwany ChartValue będzie teraz automatycznie aktualizowany, aby pokazać dodatkowy punkt danych w nim.

Magia jest wykonywana przez funkcję OFFSET tutaj.

W formule zakresu nazwanego „ChartValue” określiliśmy B2 jako punkt odniesienia. Formuła OFFSET zaczyna się tam i rozciąga się na wszystkie wypełnione komórki w kolumnie.

Ta sama logika działa również w formule zakresu nazwanego ChartMonth.

Krok 2 – Utwórz wykres używając tych zakresów nazwanych

Teraz wszystko, co musisz zrobić, to wstawić wykres, który będzie używał zakresów nazwanych jako źródła danych.

Oto kroki, które należy wykonać, aby wstawić wykres i użyć dynamicznych zakresów wykresu:

• Przejdź do zakładki Wstawianie.
• Kliknij na „Wstaw wykres liniowy lub obszarowy” i wstaw wykres „Linia ze znacznikami”. Spowoduje to wstawienie wykresu do arkusza.
• Z zaznaczonym wykresem przejdź do karty Projektowanie.
• Kliknij na Wybierz dane.
• W oknie dialogowym „Wybierz źródło danych” kliknij przycisk Dodaj w polu „Wpisy legendy (seria)”.
• W polu wartości serii wpisz =Formula!ChartValues (pamiętaj, że musisz podać nazwę arkusza przed nazwanym zakresem, aby to zadziałało).
• Kliknij przycisk OK.
• Kliknij przycisk Edytuj w polu 'Etykiety osi poziomej (kategoria)’.
• W oknie dialogowym 'Axis Labels’ wprowadź =Formula!ChartMonths
• Kliknij Ok.

To jest to! Teraz Twój wykres używa zakresu dynamicznego i będzie się aktualizował, gdy dodasz/usuniesz punkty danych na wykresie.

Kilka ważnych rzeczy, o których należy wiedzieć podczas używania nazwanych zakresów z wykresami:

• Nie powinno być żadnych pustych komórek w danych wykresu. Jeśli są puste, zakres nazwany nie będzie odnosił się do prawidłowego zbioru danych (ponieważ całkowita liczba komórek doprowadzi do tego, że będzie się odnosił do mniejszej liczby komórek).
• Musisz przestrzegać konwencji nazewnictwa podczas używania nazwy arkusza w źródle wykresu. Na przykład, jeśli nazwa arkusza jest pojedynczym słowem, takim jak Formula, możesz użyć =Formula!ChartValue. Ale jeśli jest więcej niż jedno słowo, takie jak Wykres Formuły, to musisz użyć =’Wykres Formuły’!ChartValue.

You May Also Like the Following Excel Tutorials:

• How to Create a Thermometer Chart in Excel.
• Jak utworzyć krzywą dzwonową w Excelu.
• Tworzenie wykresu krokowego w Excelu.
• Tworzenie wykresu Pareto w Excelu.
• Jak utworzyć histogram w Excelu
• Jak dodać słupki błędów w Excelu (poziomo/pionowo/standardowo)

.