Jak utworzyć dynamiczny zakres wykresów w programie Excel

Ten samouczek pokaże, jak utworzyć dynamiczny zakres wykresów we wszystkich wersjach programu Excel: 2007, 2010, 2013, 2016 i 2022.

Domyślnie po rozszerzeniu lub zawężeniu zestawu danych używanego do kreślenia wykresu w programie Excel należy również ręcznie dostosować bazowe dane źródłowe.

Jednak tworząc dynamiczne zakresy wykresów, możesz uniknąć tego kłopotu.

Dynamiczne zakresy wykresów umożliwiają automatyczną aktualizację danych źródłowych za każdym razem, gdy dodajesz lub usuwasz wartości z zakresu danych, oszczędzając mnóstwo czasu i wysiłku.

W tym samouczku dowiesz się wszystkiego, co musisz wiedzieć, aby uwolnić moc Dynamiczne zakresy wykresów.

Dynamiczne zakresy wykresów - wprowadzenie

Rozważ następujący przykładowy zestaw danych analizujący wahania marży zysku:

Zasadniczo istnieją dwa sposoby na skonfigurowanie dynamicznego zakresu wykresu:

1. Konwersja zakresu danych do tabeli
2. Używanie dynamicznych nazwanych zakresów jako danych źródłowych wykresu.

Obie metody mają swoje wady i zalety, dlatego omówimy każdą z nich bardziej szczegółowo, aby pomóc Ci określić, która będzie dla Ciebie najlepsza.

Bez zbędnych ceregieli zacznijmy.

Metoda stołu

Zacznę od pokazania najszybszego i najłatwiejszego sposobu wykonania zadania. Oto ćwiczenie: przekształć zakres danych w tabelę, a będziesz łatwiejszy niż łuskanie grochu.

W ten sposób wszystko, co wpiszesz do komórek na końcu tej tabeli, zostanie automatycznie uwzględnione w danych źródłowych wykresu.

Oto, jak możesz to zrobić w zaledwie dwóch prostych krokach.

Krok 1: Przekształć zakres danych w tabelę.

Zaraz po wyjściu z bramki przekształć zakres komórek zawierający dane wykresu w tabelę.

1. Zaznacz cały zakres danych (A1:B6).
2. Kliknij Wstawić patka.
3. Uderz w "Tabela" przycisk.

w Utwórz tabelę w oknie dialogowym wykonaj następujące czynności:

1. Sprawdź dokładnie, czy podświetlony zakres komórek pasuje do całej tabeli danych.
2. Jeśli tabela nie zawiera wiersza nagłówka, usuń zaznaczenie „Moja tabela ma nagłówki" skrzynka.
3. Kliknij "OK.“

W rezultacie powinieneś otrzymać tę tabelę:

Krok 2: Utwórz wykres na podstawie tabeli.

Fundament został położony, co oznacza, że ​​możesz teraz ustawić wykres za pomocą tabeli.

1. Zaznacz całą tabelę (A1:B6).
2. Przejdź do Wstawić patka.
3. Utwórz dowolny wykres 2D. W celach ilustracyjnych skonfigurujmy prosty wykres kolumnowy (Wstaw wykres kolumnowy lub słupkowy > Kolumnowy grupowany).

Otóż ​​to! Aby przetestować technikę, spróbuj dodać nowe punkty danych na dole stołu aby zobaczyć je automatycznie naniesione na wykres. O ile prostsze może być?

NOTATKA: Przy takim podejściu zbiór danych powinien: nigdy zawierać w nim puste komórki, które zrujnują wykres.

Metoda dynamicznego nazwanego zakresu

Chociaż łatwe do zastosowania, wcześniej zademonstrowane, Metoda tabeli ma poważne wady. Na przykład wykres ulega pomieszaniu, gdy zestaw świeżych danych jest mniejszy niż początkowa tabela danych plus, czasami po prostu nie chcesz, aby zakres danych został przekonwertowany na tabelę.

Wybór nazwanych zakresów może zająć trochę więcej czasu i wysiłku z Twojej strony, ale technika neguje wady metody stolikowej, a ponadto sprawia, że ​​zakres dynamiczny jest o wiele wygodniejszy w pracy na dłuższą metę.

Krok 1: Utwórz dynamiczne nazwane zakresy.

Na początek skonfiguruj nazwane zakresy, które ostatecznie będą używane jako dane źródłowe dla przyszłego wykresu.

1. Przejdź do Formuły patka.
2. Kliknij "Menedżer nazw.”
3. w Imię i nazwisko menedżera w wyświetlonym oknie dialogowym wybierz „Nowy.”

w Nowe imie w oknie dialogowym, utwórz zupełnie nowy nazwany zakres:

1. Rodzaj "Jedna czwarta" obok "Nazwa" pole. Dla Twojej wygody ustaw nazwę zakresu dynamicznego odpowiadającą komórce wiersza nagłówka kolumna A (A1).
2. W "Zakres”, wybierz bieżący arkusz. W naszym przypadku to jest Arkusz1.
3. Wprowadź następującą formułę w „Odnosi się do" pole: =PRZESUNIĘCIE(Arkusz1!$A$2,0,0;LICZBA(Arkusz1!$A:$A)-1,1)

Mówiąc prostym językiem, za każdym razem, gdy zmienisz dowolną komórkę w arkuszu, funkcja PRZESUNIĘCIE zwraca tylko rzeczywiste wartości w kolumna A, pomijając komórkę wiersza nagłówka (A1), podczas gdy funkcja ILE.NIEPUSTYCH ponownie oblicza liczbę wartości w kolumnie za każdym razem, gdy arkusz jest aktualizowany, wykonując za Ciebie całą brudną robotę.

Rozłóżmy formułę bardziej szczegółowo, aby pomóc Ci zrozumieć, jak to działa:

NOTATKA: Nazwa nazwanego zakresu musi zaczynać się od litery lub podkreślenia i nie może zawierać spacji.

W ten sam sposób skonfiguruj inny nazwany zakres na podstawie kolumna Marża zysku (kolumna B) za pomocą tego wzoru i oznacz go „Marża zysku”:

1	=PRZESUNIĘCIE(Arkusz1!$B$2,0,0;ILE.LICZBA(Arkusz1!$B:$B)-1,1)

Powtórz ten sam proces, jeśli tabela danych zawiera wiele kolumn z rzeczywistymi wartościami. W naszym przypadku w rezultacie powinieneś mieć dwa nazwane zakresy gotowe do działania:

Krok 2: Utwórz pusty wykres.

Przeszliśmy przez najtrudniejszą część. Teraz nadszedł czas, aby skonfigurować pusty wykres, aby można było ręcznie wstawić do niego dynamiczne nazwane zakresy.

1. Wybierz dowolną pustą komórkę w bieżącym arkuszu (Arkusz1).
2. Wróć do Wstawić patka.
3. Skonfiguruj dowolny wykres 2D. W naszym przykładzie stworzymy wykres kolumnowy (Wstaw wykres kolumnowy lub słupkowy > Kolumnowy grupowany).

Krok #3: Dodaj nazwany zakres/zakresy zawierające rzeczywiste wartości.

Najpierw wstaw nazwany zakres (Marża zysku) powiązane z rzeczywistymi wartościami (kolumna B) do wykresu.

Kliknij prawym przyciskiem myszy pusty wykres i wybierz „Wybierz dane” z menu kontekstowego.

w Wybierz źródło danych w oknie dialogowym kliknij „Dodać.”

w Edytuj serię pole, utwórz nową serię danych:

1. Pod "Nazwa serii”, podświetl odpowiednią komórkę wiersza nagłówka (B1).
2. Pod "Wartości serii”, określ nazwany zakres do wykreślenia na wykresie, wpisując: „=Arkusz1! Zysk_Marża.” Odniesienie składa się z dwóch części: nazwy bieżącego arkusza roboczego (=Arkusz1) i odpowiedni zakres dynamiczny nazwany (Marża zysku). Wykrzyknik służy do powiązania dwóch zmiennych.
3. Wybierz "OK.”

Gdy już tam dotrzesz, Excel automatycznie wykreśli wartości:

Krok 4: Wstaw nazwany zakres z etykietami osi.

Na koniec zastąp domyślne etykiety osi kategorii nazwanym zakresem składającym się z kolumna A (Jedna czwarta).

w Wybierz źródło danych okno dialogowe, pod „Etykiety osi poziomej (kategorii)," Wybierz "Edytować" przycisk.

Następnie wstaw nazwany zakres do wykresu, wprowadzając następujące odniesienie pod „Zakres etykiet osi:”

1	=Arkusz1!Kwartał

Wreszcie wykres kolumnowy oparty na dynamicznym zakresie wykresu jest gotowy:

Sprawdź to: wykres jest aktualizowany automatycznie za każdym razem, gdy dodasz lub usuniesz dane w zakresie dynamicznym.