Po utworzeniu wykresu w programie Excel i zmianie danych źródłowych należy zaktualizować źródło danych wykresu, aby upewnić się, że odzwierciedla nowe dane.
Jeśli pracujesz z wykresami, które są często aktualizowane, lepiej utworzyć dynamiczny zakres wykresów.
Co to jest dynamiczny zakres wykresu?
Dynamiczny zakres wykresu to zakres danych, który jest aktualizowany automatycznie po zmianie źródła danych.
Ten zakres dynamiczny jest następnie używany jako dane źródłowe na wykresie. Wraz ze zmianą danych zakres dynamiczny aktualizuje się natychmiast, co prowadzi do aktualizacji wykresu.
Poniżej znajduje się przykład wykresu wykorzystującego dynamiczny zakres wykresu.
Zwróć uwagę, że wykres aktualizuje się o nowe punkty danych z maja i czerwca, gdy tylko zostaną wprowadzone dane.
Jak utworzyć dynamiczny zakres wykresów w programie Excel?
Istnieją dwa sposoby tworzenia dynamicznego zakresu wykresu w programie Excel:
- Korzystanie z tabeli Excel
- Korzystanie z formuł
W większości przypadków użycie tabeli Excel jest najlepszym sposobem tworzenia zakresów dynamicznych w programie Excel.
Zobaczmy, jak działa każda z tych metod.
Kliknij tutaj, aby pobrać przykładowy plik.
Korzystanie z tabeli Excel
Korzystanie z tabeli programu Excel to najlepszy sposób na tworzenie zakresów dynamicznych, ponieważ aktualizuje się ona automatycznie po dodaniu do niej nowego punktu danych.
Funkcja tabeli Excel została wprowadzona w wersji Excel 2007 systemu Windows i jeśli masz wcześniejsze wersje, nie będziesz mógł z niej korzystać (zobacz następną sekcję dotyczącą tworzenia dynamicznego zakresu wykresu za pomocą formuł).
Wskazówka dla profesjonalistów: Aby przekonwertować zakres komórek na tabelę programu Excel, zaznacz komórki i użyj skrótu klawiaturowego Control + T (przytrzymaj klawisz Control i naciśnij klawisz T).W poniższym przykładzie widać, że jak tylko dodam nowe dane, tabela Excela 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 Excela podczas tworzenia wykresów.
Oto dokładne kroki tworzenia dynamicznego wykresu liniowego za pomocą tabeli programu Excel:
- Wybierz całą tabelę Excela.
- Przejdź do zakładki Wstaw.
- W grupie wykresów wybierz wykres „Linia ze znacznikami”.
Otóż to!
Powyższe kroki spowodowały wstawienie wykresu liniowego, który automatycznie zaktualizowałby się po dodaniu większej ilości danych do tabeli programu Excel.
Zwróć uwagę, że podczas dodawania nowych danych automatycznie aktualizuje wykres, usunięcie danych nie spowoduje całkowitego usunięcia punktów danych. Na przykład, jeśli usuniesz 2 punkty danych, wykres pokaże trochę pustego miejsca po prawej stronie. Aby to naprawić, przeciągnij niebieski znak w prawym dolnym rogu tabeli programu Excel, aby usunąć usunięte punkty danych z tabeli (jak pokazano poniżej).
Chociaż wziąłem przykład z wykresu liniowego, za pomocą tej techniki można również tworzyć inne typy wykresów, takie jak wykresy kolumnowe/słupkowe.
Korzystanie z formuł programu Excel
Jak wspomniałem, korzystanie z tabeli Excela to najlepszy sposób na tworzenie dynamicznych zakresów wykresów.
Jeśli jednak z jakiegoś powodu nie możesz użyć tabeli programu Excel (prawdopodobnie, jeśli używasz programu Excel 2003), istnieje inny (nieco skomplikowany) sposób tworzenia dynamicznych zakresów wykresów przy użyciu formuł programu Excel i nazwanych zakresów.
Załóżmy, że masz zestaw danych, jak pokazano poniżej:
Aby utworzyć dynamiczny zakres wykresu z tych danych, musimy:
- Utwórz dwa dynamiczne nazwane zakresy za pomocą formuły PRZESUNIĘCIE (po jednym dla kolumny „Wartości” i „Miesiące”). Dodanie/usunięcie punktu danych spowoduje automatyczną aktualizację tych nazwanych zakresów.
- Wstaw wykres, który używa nazwanych zakresów jako źródła danych.
Pozwólcie, że wyjaśnię teraz szczegółowo każdy krok.
Krok 1 - Tworzenie dynamicznych nazwanych zakresów
Poniżej znajdują się kroki tworzenia dynamicznych nazwanych zakresów:
- Przejdź do zakładki „Formuły”.
- Kliknij „Menedżer nazw”.
- W oknie dialogowym Menedżer nazw określ nazwę jako Wykres Wartości i wprowadź następującą formułę w Odnosi się do części: =OFFSET(Formula!$B$2,,,COUNTIF(Formula!$B$2:$B$100””))
- Kliknij OK.
- W oknie dialogowym Menedżer nazw kliknij Nowy.
- W oknie dialogowym Menedżer nazw określ nazwę jako Wykres Miesiące i wprowadź następującą formułę w Odnosi się do części: =OFFSET(Formula!$A$2,,,COUNTIF(Formula!$A$2:$A$100””))
- Kliknij OK.
- Kliknij Zamknij.
Powyższe kroki utworzyły dwa nazwane zakresy w skoroszycie - ChartValue i ChartMonth (odnoszą się one odpowiednio do wartości i zakresu miesięcy w zestawie danych).
Jeśli przejdziesz i zaktualizujesz kolumnę wartości, dodając jeszcze jeden punkt danych, nazwany zakres ChartValue będzie teraz automatycznie aktualizowany, aby pokazać w nim dodatkowy punkt danych.
Magii dokonuje tutaj funkcja OFFSET.
W formule nazwanego zakresu ‘ChartValue’ określiliśmy B2 jako punkt odniesienia. Formuła PRZESUNIĘCIE zaczyna się tam i rozciąga się na wszystkie wypełnione komórki w kolumnie.
Ta sama logika działa również w formule nazwanego zakresu ChartMonth.
Krok 2 - Utwórz wykres, korzystając z tych nazwanych zakresów
Teraz wszystko, co musisz zrobić, to wstawić wykres, który będzie używał nazwanych zakresów jako źródła danych.
Oto instrukcje wstawiania wykresu i korzystania z dynamicznych zakresów wykresu:
- Przejdź do zakładki Wstaw.
- Kliknij „Wstaw wykres liniowy lub warstwowy” i wstaw wykres „Linia ze znacznikami”. Spowoduje to wstawienie wykresu do arkusza roboczego.
- Po wybraniu wykresu przejdź do karty Projekt.
- Kliknij Wybierz dane.
- W oknie dialogowym „Wybierz źródło danych” kliknij przycisk Dodaj w „Wpisy legendy (seria)”.
- W polu Wartość serii wprowadź =Formula!ChartValues (pamiętaj, że aby to zadziałało, musisz określić nazwę arkusza przed nazwanym zakresem).
- Kliknij OK.
- Kliknij przycisk Edytuj w „Etykietach osi poziomych (kategorii)”.
- W oknie dialogowym „Etykiety osi” wpisz =Formula!ChartMonths
- Kliknij OK.
Otóż to! Teraz Twój wykres korzysta z zakresu dynamicznego i zostanie zaktualizowany, gdy dodasz/usuniesz punkty danych na wykresie.
Kilka ważnych rzeczy, o których należy wiedzieć, używając nazwanych zakresów z wykresami:
- W danych wykresu nie powinno być żadnych pustych komórek. Jeśli jest puste, nazwany zakres nie odwołuje się do prawidłowego zbioru danych (ponieważ całkowita liczba prowadziłaby do odnoszenia się do mniejszej liczby komórek).
- Podczas używania nazwy arkusza w źródle wykresu należy przestrzegać konwencji nazewnictwa. Na przykład, jeśli nazwa arkusza jest pojedynczym słowem, takim jak Formuła, możesz użyć =Formuła!WartośćWykresu. Ale jeśli istnieje więcej niż jedno słowo, takie jak Wykres formuły, musisz użyć ='Wykres formuły'!ChartValue.
