Czy udało Ci się znaleźć typ wykresu kolumnowego skumulowanego skumulowanego w programie Excel?
Cóż, niestety go tam nie ma. Ale program Excel jest potężnym narzędziem i możemy skorzystać z innych opcji tworzenia wykresu, którego szukamy, dzięki tej prostej wskazówce, aby utworzyć własny wykres skumulowany kolumnowy programu Excel. Ten samouczek koncentruje się na programach Microsoft Excel 2013, Excel 2016 i Excel 2019. Ale najprawdopodobniej będzie działać w wersjach po 2019 r. Jeśli chcesz zobaczyć kroki wymagane w przypadku programów Excel 20010 i Excel 2007, zapoznaj się z tym samouczkiem: instrukcje easy-create-a-stacked-clustered-column-chart-in-excel
Podział
Oto podstawowe kroki, przez które przejdziemy w tym samouczku. Przykładowy plik do pobrania i demonstracja wideo znajdują się na końcu artykułu.
1) Dane konfiguracyjne
2) Utwórz skumulowany wykres kolumnowy
3) Zmień wiersze / kolumny wykresu (w razie potrzeby)
4) Zmniejsz szerokość przerwy w szeregu (zgodnie z potrzebami)
Opcjonalne kroki dla funkcji rozszerzonego wykresu:
5) Dodaj serię wypełniaczy do separacji
5a) Wstaw wiersze zgodnie z potrzebami do wcześniej utworzonego wykresu
6) Dodaj Seria etykiet na drugiej osi pionowej
6a) Dodaj serię
6b) Powtórz kroki 2-3
6c) Przenieś nową serię etykiet pionowych osi 2 na oś 2
6d) Zmień nowe wypełnienie serii etykiet na drugiej osi pionowej na brak wypełnienia
6e) Zmniejsz odstępy między seriami Szerokość (zgodnie z wymaganiami)
6f) Usuń wpis legendy etykiety drugiej osi
Krok po kroku
1) Konfiguracja danych
Aby utworzyć symulowany typ wykresu kolumnowego grupowanego, wystarczy wykonać 4 kroki. Teraz ta wskazówka wykorzystuje opcję wykresu Excel „Etykiety kategorii wielopoziomowych” dla wykresów kolumnowych. Aby skorzystać z tej opcji, musimy skonfigurować nasze dane w określony sposób. To jest nasz pierwszy krok.
Nasz scenariusz jest taki, że tworzymy wykres dla zespołu marketingowego na podstawie wydanej kwoty i kwoty w budżecie dla 2 produktów w 4 typach mediów (druk, internet, telewizja, radio). Dane, które otrzymaliśmy, były w tym formacie.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Budżet sprzedaży a rzeczywisty | ||||||||
| 2 | Radio | Radio | Drukuj | Drukuj | Telewizja | TV | Internet | Internet | |
| 3 | Budżet | Rzeczywisty | Budżet | Rzeczywisty | Budżet | Rzeczywisty | Budżet | Rzeczywisty | |
| 4 | Produkt 1 | 123 | 166 | 128 | 91 | 163 | 181 | 171 | |
| 5 | Produkt 2 | 63 | 149 | 157 | 101 | 53 | 140 | 198 | 153 |
Oryginalne dane
Aby wykorzystać opcję „Wielopoziomowe etykiety kategorii” na wykresie kolumnowym, musimy skonfigurować nasze dane z kategorie warstwowe. Oznacza to, że mamy główną kategorię w jednej kolumnie i wiele kategorii w kolumnie po prawej stronie. Dlatego musimy zmienić nasze dane na następujący format.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Budżet sprzedaży a rzeczywisty | |||||
| 2 | Radio | Drukuj | Telewizja | Internet | ||
| 3 | Produkt 1 | Budżet | 123 | 155 | 91 | 181 |
| 4 | Rzeczywiste | 166 | 128 | 163 | 171 | |
| 5 | Produkt 2 | Budżet | 63 | 157 | 53 | 198 |
| 6 | Rzeczywiste | 149 | 101 | 140 | 153 |
Podstawowa konfiguracja skumulowanego wykresu skumulowanego
Możesz więc zobaczyć, że nasze dane został tak uporządkowany Z produktem 1 są skojarzone 2 wiersze. Jeden dla wartości budżetu i jeden dla rzeczywistych wartości dla wszystkich 4 rodzajów reklam w mediach. Jesteśmy teraz gotowi do utworzenia wykresu skumulowanego kolumnowego w programie Excel.
2) Utwórz skumulowany wykres kolumnowy
Aby utworzyć nasz wykres, zaznacz zakres danych w zestawie danych powyżej z punktu A2: F6. Następnie przejdź do wstążki Wstaw i kliknij przycisk wykresu kolumnowego na wykresie, tak jak tutaj widzisz.
Twój wykres powinien teraz wyglądać następująco:
3 ) Przełącz wiersze / kolumny wykresu (w razie potrzeby)
Nasz wykres nie wygląda dobrze. W większości przypadków musisz zamienić wiersze / kolumny na wykresie. Dlaczego muszę to zrobić? Obejrzyj ten film tutaj
Aby naprawić wykres, po prostu kliknij wykres, a następnie przejdź do wstążki Projekt i kliknij przycisk Przełącz wiersze / kolumny.
Twój wykres jest już kompletny i powinien wyglądać następująco:
Jeśli chcesz przesunąć kolumny bliżej siebie, wykonaj następny krok, ale jest to opcjonalne.
4) Zmniejsz szerokość odstępu w szeregu (zgodnie z życzeniem)
Jeśli chcesz, i jest to mój preferowany wygląd, możesz zamknąć szerokość szczeliny kolumn. Aby to zrobić, zaznacz wykres, a następnie wybierz dowolną serię danych i naciśnij klawisze CTRL + F1 (lub kliknij prawym przyciskiem myszy i wybierz opcję Formatuj serie danych). Następnie w oknie dialogowym Formatuj serie danych zmień szerokość przerwy na 15% lub cokolwiek uważasz, że wygląda najlepiej.
Twój ostateczny wykres powinien teraz wyglądać tak, jak powyżej. Ale to nie koniec, jeśli chcesz dodać dodatkowe zmiany. Poniższe jest całkowicie opcjonalne i zgodnie z życzeniem, ale myślę, że dodaje trochę jasności i dodatkowych szczegółów, które pomogą czytelnikowi.
Opcjonalne kroki wykresu kolumnowego grupowanego
5) Dodaj serię wypełniaczy dla separacji
Jeśli chcesz rozdzielić grupy, wykonaj ten krok.
5a) Wstaw wiersze zgodnie z potrzebami do wcześniej utworzonego wykresu
Zrób to tak, jak uważasz , po prostu wstaw wiersz w danych, aby oddzielić grupy.
Po wstawieniu wiersza należy wykonać kroki 1-3 powyżej, aby utworzyć wykres od początku.Po wykonaniu kroków 1–3 zobaczysz, że wykres nie wygląda dobrze, ponieważ grupa produktów 1 zawiera nową przestrzeń, tak jak tutaj:
Aby to naprawić, musisz oszukać Excela, tworząc kolejną grupę pierwszego poziomu, nie pojawia się na wykresie. Jest to bardzo proste, wystarczy wstawić spację w zestawie danych w komórce tuż nad wpisem Produkt 2.
Twój ostateczny wykres z dodaną przerwą powinien wygląda tak z poprawionym grupowaniem:
6) Dodaj pionową serię etykiet drugiej osi
Opcjonalny krok 5 powyżej wygląda dobrze, ale dane produktu 2 są dalej od liczb, więc czasami lubię dodać dodatkowe serie wykresów na osi pomocniczej, aby numery osi pionowej były bliższe tej serii. Jest to krok opcjonalny, więc używaj go tylko wtedy, gdy podoba Ci się wygląd.
6a) Dodaj serię
Aby wartość maksymalnego limitu osi pomocniczej odpowiadała maksymalnemu limitowi osi głównej, musimy utworzyć formułę dla naszej dodatkowej serii osi pomocniczej. Aby to zrobić, utwórz formułę w pierwszym wierszu danych za pomocą formuły obliczającej maksymalną kolumnę skumulowaną ze wszystkich kolumn skumulowanych, które znajdą się na wykresie. Dlatego w komórce G3 tworzymy formułę MAX () z sumą każdego wiersza. Aby uzyskać więcej informacji, zapoznaj się z poniższym wzorem Max i Suma.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Budżet sprzedaży a rzeczywisty | ||||||
| 2 | Radio | Drukuj | Telewizja | Internet | Etykiety drugiej osi | ||
| 3 | Produkt 1 | Budżet | 123 | 155 | 91 | 181 | 628 |
| 4 | Rzeczywiste | 166 | 128 | 163 | 171 | ||
| 5 | |||||||
| 6 | Produkt 2 | Budżet | 63 | 157 | 53 | 198 | |
| 7 | Rzeczywiste | 149 | 101 | 140 | 153 |
Opcjonalny wykres drugiej osi
Formuły arkusza roboczego
|
6b) Powtórz kroki 2–3
Po utworzeniu zestawu danych po prostu wykonaj wszystkie pierwotne podstawowe kroki 1–3 powyżej, aby utworzyć wykres kolumnowy skumulowany skumulowany ale zmień zakres wykresu na A2: G6 lub A2: G7, jeśli dodałeś dodatkowy wiersz na lukę między grupami. W moim przypadku dodałem wiersz z luką, więc mój zakres wykresu to A2: G7.Śmiało i utwórz początkowy wykres, tak jak tutaj:
Początek wykresu powinien teraz wyglądać tak, jeśli w serii danych po kroku 2 znajduje się wiersz z luką.
Po przełączeniu wierszy / kolumn wykres po kroku 3 będzie wyglądał następująco:
6c) Przenieś nową serię etykiet 2 osi pionowej do osi 2
Nasz wykres wygląda bardzo podobnie do naszego podstawowego wykresu, ale istnieje dodatkowa seria, której użyjemy dla naszej osi pomocniczej. Wybierz więc wykres, a następnie wybierz serię „Etykiety drugiej osi” i naciśnij klawisze CTRL + F1 lub kliknij prawym przyciskiem myszy i wybierz opcję Formatuj serie danych… Spowoduje to wyświetlenie opcji przeniesienia go do osi pomocniczej. Zrób to teraz .
6d) Zmień nowe wypełnienie serii etykiet na drugiej osi pionowej na brak wypełnienia
Kiedy przenieśliśmy serię do osi pomocniczej, zakrywa ona jedną z naszych ułożonych w stos kolumn. Aby obejść ten problem, musimy zmienić wypełnienie serii na Bez wypełnienia. Jeśli nadal jesteś w oknie dialogowym Formatuj serie danych, kliknij Opcje wypełnienia (wygląda jak puszka z farbą) i zmień Wypełnienie na Bez wypełnienia.
6d) Zmniejsz szerokość odstępu w serii (jako Desired)
To jest to samo, co w opcjonalnym kroku 4 powyżej. Zmieniłem szerokość odstępu kolumn ułożonych w stos na osi głównej na 15%, jak widać tutaj:
6f) Usuń wpis legendy etykiety drugiej osi
Ostatnią rzeczą, jaką musimy zrobić, jest usunięcie wpisu legendy„ Etykiety drugiej osi ”z legendy. Aby to zrobić, zaznacz wykres, a następnie legendę, a następnie legendę, a następnie wpis legendy „Etykiety drugiej osi” i naciśnij klawisz usuwania.
Twój ostateczny wykres będzie teraz wyglądał następująco:
To świetna technika wykres mieszany, który jest często potrzebny w przypadku złożonych zestawów danych. Sprawdza się nawet w przypadku większej liczby zestawów danych produktów. Oto przykład tego, jak wyglądałaby inna seria.
Demonstracja wideo
Obejrzyj samouczek wideo dotyczący technik przedstawione powyżej.
Pobieranie przykładowego pliku
Kliknij tutaj, aby pobrać bezpłatny przykładowy plik szablonu Excel:
Clustered-Stacked -Wykresy-kolumnowe
