Wykresy dynamiczne wykorzystujące funkcję OFFSET i nazwy zakresów w programie Excel zmieniają rozmiar, gdy dane są dodawane lub usuwane. W przeciwieństwie do korzystania z tabel do tworzenia wykresów dynamicznych, ta metoda jest wstecznie kompatybilna z wczesnymi wersjami programu Excel.
Użyj funkcji OFFSET i COUNTA programu Excel do tworzenia dynamicznych nazw zakresów automatycznie dostosowywanych w miarę dodawania lub usuwania danych.
Kliknij, aby pobrać przykładowy plik samouczka dotyczący dynamicznej zmiany rozmiaru wykresów przy użyciu funkcji OFFSET i nazw zakresów. Przykład obejmuje zarówno dane poziome, jak i pionowe.
Metoda OFFSET i nazw zakresów wymaga więcej pracy niż dwa kliknięcia używane w przypadku tabeli, ale daje kontrolę nad wszystkim, co dotyczy danych na wykresie, jak pokazuje moja książka „Balanced Scorecards and Operational Dashboards with Microsoft Excel”. Ta strona, CriticalToSuccess.com, zawiera przykłady innych sposobów użycia OFFSET, na przykład tworzenie wykresu przewijanego, kontrolowanie wykresów z menu rozwijanym i wiele innych. Wyszukaj w CriticalToSuccess.com używając terminu „OFFSET” lub „dynamic range” aby zobaczyć przykłady.
Tworzenie wykresu statycznego
Zacznij swój dynamiczny wykres tworząc najpierw wykres statyczny w następujący sposób,
1. Kliknij wewnątrz zakresu B7:D12, aby program Excel wiedział, gdzie znajdują się dane wykresu.
2. Na karcie Wstawianie, w grupie Wykresy, wybierz prosty wykres, taki jak kolumnowy, słupkowy lub liniowy.
Przesuń ten wykres, aby mieć miejsce na dodanie danych poniżej zakresu danych.
Tworzenie dynamicznych nazw zakresów za pomocą polecenia OFFSET
Różne wersje programu Excel mają różne metody tworzenia i edytowania formuł nazw zakresów. Poniższe instrukcje dotyczą programów Excel 2007, 2010 i 2013.
1. Na karcie Formuły, w grupie Zdefiniowana nazwa kliknij przycisk Zdefiniuj nazwę. Zostanie wyświetlone okno Zdefiniuj nazwę.
Utwórz nazwy zakresów w oknie Zdefiniuj nazwę.
2. W polu tekstowym Nazwa wpisz rngVData. Konwencja nazewnictwa to,
rng Nazwa zakresu
V Orientacja pionowa danych w B7:B16
Data Typ danych w zakresie
3. W polu tekstowym Komentarz napisz komentarz, który pomoże Tobie lub innym osobom dowiedzieć się, do czego służy ta nazwa zakresu.
4. W polu Odnosi się do wpisz formułę OFFSET, która oblicza zakres danych Data.
=OFFSET(wrksht!$B$7,0,0,COUNTA(wrksht!$B$7:$B$16),1)
5. Kliknij przycisk Ok.
Dzięki tej formule OFFSET możesz wprowadzić dodatkowe daty w dowolnym miejscu zakresu B7:B16, o ile nowe daty znajdują się na dole starej listy dat bez pustych komórek między datami.
Teraz powtórz ten sam proces, aby utworzyć nazwy zakresów dla danych Sprzedaż i Jednostki.
Nazwy i formuły, które dodasz, to,
Nazwa zakresu danych Dotyczy
Sales rngVSales =OFFSET(wrksht!$C$7,0,0,COUNTA(wrksht!$C$7:$C$16),1)
Units rngVUnits =OFFSET(wrksht!$D$7,0,0,COUNTA(wrksht!$D$7:$D$16),1)
Kliknij tutaj, aby dowiedzieć się, jak OFFSET oblicza rozmiar nazwy zakresu dynamicznego. Ten artykuł zawiera również wskazówki dotyczące sprawdzania nazw zakresów dynamicznych.
Kliknij tutaj, aby poznać błąd, którego nigdy nie chcesz popełnić z OFFSET. Czy popełniasz ten błąd?
Kliknij, aby pobrać przykładowy plik instruktażowy dotyczący dynamicznej zmiany rozmiaru wykresów przy użyciu funkcji OFFSET i nazw zakresów. Przykład zawiera zarówno dane poziome, jak i pionowe.
Wstawianie nazwy zakresu do formuły daty wykresu
Teraz możesz uczynić swój wykres dynamicznym, wstawiając nazwy zakresów, które utworzyłeś, do formuł używanych przez wykresy do odwoływania się do danych. Nie martw się. To jest bardzo proste. W rzeczywistości, nauka jak to zrobić daje mistyczne zdolności i moc, aby nagiąć każdy wykres do swojej woli. (Umożliwia również naprawianie wykresów, które się zepsuły.)
Powyżej utworzyłeś trzy dynamiczne nazwy zakresów, które odwołują się do danych używanych przez wykres,
rngVDate
rngVSales
rngVUnits
Poniżej zamień odwołania do komórek używane przez wykres na utworzone przez Ciebie dynamiczne nazwy zakresów,
1. Zaznacz wykres, klikając jego krawędź.
2. Na karcie Projektowanie narzędzi wykresu, w grupie Dane, kliknij narzędzie Wybierz dane, aby wyświetlić okno dialogowe Wybierz źródło danych.
Okno dialogowe Wybierz źródło danych zapewnia magiczną kontrolę nad danymi wykorzystywanymi przez wykres.
3. W obszarze Etykiety osi poziomej (kategorii) kliknij przycisk Edytuj, aby wyświetlić okno Etykiety osi. Trudno jest zobaczyć formułę w tym oknie dialogowym, dlatego kliknij ikonę selektora po prawej stronie odwołania do komórki. Spowoduje to wyświetlenie szerszego okna Axis Labels, w którym można odwołać się do formuły. Przed przystąpieniem do edycji należy zachować ostrożność!
W oknie Axis Labels wyświetlane jest odwołanie do osi poziomej.
4. BĄDŹ OSTROŻNY! Zanim spróbujesz edytować formułę, kliknij wewnątrz formuły i naciśnij klawisz Edycja, F2, aby móc edytować formułę. Naciśnij klawisz End, aby przejść na koniec formuły. Zamień odwołania do komórek po znaku ! na nazwę zakresu, jak pokazano na tym rysunku.
Twoja formuła powinna teraz wyglądać tak,
=’Dynamiczna zmiana rozmiaru wykresów do Data.xlsx’!rngVDate
Okno Axis Labels wygląda tak po naciśnięciu klawisza F2 i edycji formuły w celu włączenia nazwy zakresu dat.
Wskazówka: Jeśli popełnisz błąd podczas edycji formuły, naciśnij klawisze Ctrl+Z (Cofnij), aby się cofnąć.
5. Kliknij przycisk Ok, aby powrócić do okna dialogowego Wybierz źródło danych i dodać nazwy sprzedaży i jednostek.
Wstawianie nazwy zakresu do formuły sprzedaży i jednostek na wykresie
Zastąp teraz formuły osi Sprzedaż i Jednostki nazwami zakresów, korzystając z prawie tego samego procesu.
1. W oknie dialogowym Wybierz źródło danych kliknij pozycję Sprzedaż i przycisk Edytuj, aby wyświetlić okno dialogowe Edycja serii.
Zastąp odwołanie do komórki Salesdata jej nazwą zakresu.
2. Kliknij ikonę selektora po prawej stronie formuły Wartość serii, która odwołuje się do serii Sprzedaż.
3. Kliknij wewnątrz formuły, naciśnij klawisz F2, aby edytować, i zmień odwołania do komórek danych Sales na nazwę zakresu, rngVSales.
Wykres używa teraz dynamicznej nazwy zakresu do odwoływania się do danych Sales.
.