Jak utworzyć wykres krokowy w Excelu – Automate ExcelAutomate Excel

Ten samouczek zademonstruje, jak utworzyć wykres krokowy we wszystkich wersjach Excela: 2007, 2010, 2013, 2016 i 2019.

Wykresy krokowe – które są powszechnie używane do analizy wahań cen i są cięte z tego samego materiału co wykresy liniowe – przychodzą na ratunek, gdy trzeba starannie wyświetlić regularne lub sporadyczne skoki ilościowe w wartościach, cenach, stopach procentowych i tak dalej.

Jednakże, ponieważ wykres nie jest obsługiwany w programie Excel, będziesz musiał włożyć trochę wysiłku, aby samemu go wykreślić. Aby zaoszczędzić Ci czasu, opracowaliśmy dodatek Chart Creator, łatwe w użyciu narzędzie do tworzenia zaawansowanych wykresów w programie Excel w ciągu kilku sekund.

W tym samouczku krok po kroku dowiesz się, jak zbudować prosty wykres krokowy od podstaw w programie Excel:

Rozpoczęcie

Dla ilustracji załóżmy, że zamierzasz uruchomić ogromną kampanię marketingową dla swojej marki i musisz dowiedzieć się, gdzie umieścić swoje dolary na reklamę w 2020 roku.

Przesiewając przez różne kanały marketingowe w poszukiwaniu najlepiej działającej opcji, zamierzasz wykreślić wykres krokowy wyświetlający koszt pozyskania klienta (CAC) w e-mail marketingu od 2019 roku, kluczową metrykę, która mierzy koszt działań marketingowych potrzebnych do zdobycia nowego klienta.

To powiedziawszy, oto przykładowa tabela podsumowująca wydajność kanału marketingowego w 2019 roku:

W biznesie czas to pieniądz, więc zaczynajmy.

Krok #1: Sklonuj oryginalną tabelę danych.

Ponieważ budowanie wykresu krokowego wiąże się z dużą ilością manipulacji danymi, utwórz kopię tabeli zawierającej oryginalne dane, aby zachować lepszą organizację.

Krok #2: Powiel wszystkie wartości w sklonowanej tabeli.

Jako obejście problemu przekształcania wykresu liniowego w wykres schodkowy, w nowo utworzonej tabeli ustaw dwa identyczne zestawy danych zawierające te same wartości.

W tym celu zaznacz wszystkie wartości w drugiej tabeli i naciśnij klawisze Ctrl + C, aby skopiować wybrane komórki. Następnie zaznacz pierwszą pustą komórkę pod tabelą (w naszym przypadku D14) i naciśnij Ctrl + V, aby wkleić dane.

Tutaj wygląda to w praktyce:

Krok #3: Posortuj tabelę według kolumny daty od najstarszej do najnowszej.

Czas położyć fundamenty pod niestandardowe etykiety danych, które utworzysz po drodze.

1. Podświetl całą sklonowaną tabelę (D1:E25).
2. Przejdź do karty Dane.
3. W grupie Filtr Sortuj & wybierz przycisk „Sortuj”.
4. W każdym menu rozwijanym posortuj według następujących elementów:
   1. Dla „Kolumna” wybierz „Miesiąc” (kolumna D).
   2. Dla „Sortuj na” wybierz „Wartości” / „Wartości komórek.”
   3. Dla „Kolejność” wybierz „Najstarsze do najnowszego.”
5. Kliknij przycisk OK, aby zamknąć okno dialogowe.

Krok #4: Przesuń wartości kosztów o jedną komórkę w dół.

Ta prosta technika pozwoli Ci zaoszczędzić kłopotów związanych z ręcznym wpisywaniem wartości dla każdej daty.

Podświetl wszystkie wartości z kolumny CAC (kolumna E) w tabeli duplikatów. Następnie przesuń wszystko o jedną komórkę w dół, naciskając Ctrl + X, aby wyciąć dane, a następnie Ctrl + V w następnej komórce w dół (E3), aby wkleić je w nowej pozycji.

Krok #5: Usuń pierwszy i ostatni wiersz zduplikowanej tabeli.

W tym miejscu usuń pierwszy i ostatni wiersz drugiej tabeli i przenieś pozostałe dane pod nagłówki kolumn, aby kolumny znów były równe.

Podświetl komórki D2 i E2, kliknij prawym przyciskiem myszy i wybierz polecenie Usuń. W oknie dialogowym wybierz opcję Przesuń komórki w górę. Zrób to samo dla komórki E25.

Krok #6: Zaprojektuj niestandardowe etykiety danych.

Następnym krokiem jest zaprojektowanie niestandardowych etykiet danych tylko dla wartości na wykresie, które odzwierciedlają oryginalne dane, pomijając pomocnicze punkty danych, które trzymają to wszystko razem.

Najpierw utwórz nową kategorię danych o nazwie „Etykieta” w kolumnie sąsiadującej z drugą tabelą (w naszym przypadku w kolumnie F).

Aby utworzyć etykiety zawierające daty sparowane z odpowiadającymi im wartościami, musisz uwolnić moc funkcji JEŻELI, CHAR i TEKST. Ten specjalny koktajl funkcji Excela pomoże nam wykonać to zadanie:

Dla niewtajemniczonych, oto rozkodowana wersja wzoru:

W prostym języku angielskim formuła porównuje wartości E1 i E2 między sobą, a jeśli się nie zgadzają, komórka F2 zostaje wypełniona niestandardową etykietą wyświetlającą zarówno odpowiedni miesiąc, jak i koszt. Funkcja CHAR(10) pozostaje niezmienna i oznacza przerwanie wiersza. W przeciwnym razie formuła zwraca pustą wartość.

Jak to wygląda w praktyce:

Teraz przeciągnij uchwyt wypełnienia w prawym dolnym rogu wybranej komórki aż do dolnej części kolumny F, aby wykonać formułę dla pozostałych komórek (F3:F24).

Krok #7: Utwórz wykres liniowy.

Wszystkie przeszkody zostały już usunięte. Czas utworzyć prosty wykres liniowy i zobaczyć, co się stanie.

1. Podświetl wszystkie komórki zawierające wartości daty i kosztu (D2:E24).
2. Przejdź na kartę Wstaw.
3. Kliknij ikonę „Wstaw wykres liniowy lub obszarowy”.
4. Wybierz „Liniowy.”

Cudownie, wyskakuje wykres schodkowy! Warto jednak wspomnieć o jednym zastrzeżeniu. Załóżmy, że postanowiłeś przeanalizować skuteczność email marketingu tylko na przestrzeni trzech miesięcy, a nie:

Jak zapewne zauważyłeś, oś pozioma wygląda dość niechlujnie. Na szczęście naprawienie tego problemu jest tak proste, jak łuskanie grochu. Najpierw kliknij prawym przyciskiem myszy oś poziomą (etykiety na dole) i wybierz polecenie „Formatuj oś.”

W wyświetlonym oknie zadań wykonaj następujące czynności:

1. Przełącz się na kartę Opcje osi.
2. W sekcji Typ osi wybierz przycisk radiowy „Oś daty”.
3. W sekcji Jednostki ustaw wartość Podstawowy na „Dni/miesiąc/rok”, w zależności od rzeczywistych danych.

Teraz wracamy do naszego wykresu krokowego.

Krok #8: Dodaj etykiety danych.

Następnie musimy dodać do wykresu krokowego niestandardowe etykiety danych, które wcześniej przygotowałeś.

Kliknij prawym przyciskiem myszy linię ilustrującą wahania kosztów i wybierz opcję „Dodaj etykiety danych.”

Krok #9: Dodaj niestandardowe etykiety danych i pozbądź się domyślnych wartości etykiet.

Kliknij prawym przyciskiem myszy dowolną etykietę na wykresie i wybierz polecenie „Formatuj etykiety danych.”

Po wyświetleniu okienka zadań wykonaj następujące czynności:

1. Przejdź do karty Opcje etykiet.
2. Zaznacz pole „Wartość z komórek”.
3. Podświetl wszystkie wartości z kolumny Etykieta (F2:F24).
4. Kliknij „OK”, aby zamknąć okno dialogowe.
5. Zaznacz pole „Wartość”, aby usunąć domyślne etykiety.

Krok #10: Przesuń etykiety ponad linię wykresu.

Już prawie skończyliśmy! Na koniec trzeba jeszcze ustawić etykiety we właściwych miejscach. Etykiety reprezentujące wzrost kosztów zostaną umieszczone powyżej linii. I odwrotnie, etykiety pokazujące spadek kosztów zostaną przesunięte poniżej linii.

W okienku zadań Formatuj etykiety danych, na karcie Opcje etykiet, przewiń w dół do pozycji Etykieta i wybierz opcję „Powyżej.”

Krok #11: Przesuń etykiety pokazujące spadki kosztów w dół poniżej linii wykresu.

Na koniec wybierz kolejno etykiety pokazujące spadki CAC i ustaw wartość Label Position dla każdej z nich na „Below.”

Dla ostatniej korekty pogrub etykiety (zakładka Strona główna >Czcionka) i zmień tytuł wykresu. Twój wykres krokowy jest gotowy.

Więc masz to: więcej informacji prowadzących do lepszych decyzji, wszystko dzięki twojemu wykresowi krokowemu!

.