Utwórz wykres ze średnią / docelową linią w programie Excel
W programie Excel typowym zadaniem może być wstawienie poziomej lub pionowej linii do wykresu, pozioma lub pionowa linia może odnosić się do jakiejś wartości docelowej lub średniej. Ten typ linii ułatwia porównanie rzeczywistych wartości z wartością docelową lub średnią, a za pomocą tej linii można łatwo sprawdzić, gdzie wartości znajdują się powyżej i poniżej tej wartości odniesienia, jak pokazano poniżej. W tym artykule opowiem o tym, jak utworzyć wykres kolumnowy lub słupkowy ze średnią lub docelową linią w programie Excel.
| Wykres kolumnowy z linią poziomą | Wykres słupkowy z linią pionową |
 |
 |
- Utwórz wykres kolumnowy ze średnią lub docelową linią w poziomie
- Dodaj linię docelową z różnymi wartościami na wykresie kolumnowym
- Utwórz wykres kolumnowy ze średnią linią dynamicznie za pomocą przycisków opcji
- Utwórz wykres słupkowy ze średnią lub docelową linią w pionie
- Dodaj linię docelową z różnymi wartościami na wykresie słupkowym
- Dodaj docelową / średnią linię do wykresu kolumnowego lub słupkowego za pomocą przydatnej funkcji
- Pobierz wykres z przykładowym plikiem średniej / linii docelowej
Utwórz wykres kolumnowy ze średnią lub docelową linią w poziomie
Aby utworzyć wykres kolumnowy ze średnią lub docelową linią poziomą, wykonaj następujące czynności:
1. Jeśli chcesz utworzyć wykres kolumnowy ze średnią linią, najpierw powinieneś obliczyć średnią wartość listy danych, wprowadź poniższą formułę do pustej komórki, a następnie przeciągnij uchwyt automatycznego wypełniania, aby skopiować formułę do drugiej komórki, patrz zrzut ekranu:
2. Następnie wybierz zakres danych, w tym wartości średnie, a następnie kliknij wstawka > Wstaw wykres kolumnowy lub słupkowy > Kolumna grupowanazobacz zrzut ekranu:
3. A wykres kolumnowy jest wstawiany do arkusza roboczego, możesz usunąć niepotrzebne elementy, takie jak tytuł wykresu, linie siatki, jak potrzebujesz, zobacz zrzuty ekranu:
 |
 |
 |
4. Następnie kliknij prawym przyciskiem myszy średnią serię danych (pomarańczowe słupki) i wybierz Zmień typ wykresu serii z menu kontekstowego, zobacz zrzut ekranu:
5, w Zmień typ wykresu okno dialogowe w obszarze Wybierz typ wykresu i oś dla serii danych pole listy, wybierz Wykres liniowy z Typ wykresu rozwijana lista dla Średni serie danych, patrz zrzut ekranu:
6. A następnie kliknij OK przycisk, otrzymasz wykres jak na poniższym zrzucie ekranu:
7. Następnie możesz dodać etykietę danych dla tej średniej linii, kliknij dwukrotnie, aby wybrać najbardziej prawy punkt danych linii, patrz zrzut ekranu:
8. A następnie kliknij Elementy wykresu, Sprawdź Etykiety danych pudełko, a etykieta danych została wstawiona dla średniej linii, patrz zrzut ekranu:
Dodaj linię docelową z różnymi wartościami na wykresie kolumnowym
Czasami możesz chcieć porównać rzeczywiste wartości z wartościami docelowymi, które są różne dla każdego wiersza, w tym przypadku, aby lepiej zwizualizować wartości docelowe, poniższy typ wykresu może Ci wyświadczyć przysługę.
 |
|
 |
Aby utworzyć ten typ wykresu, wykonaj następujące czynności:
1. Najpierw wybierz zakres danych, a następnie kliknij wstawka > Wstaw wykres kolumnowy lub słupkowy > Kolumna grupowana aby wstawić wykres.
2. Następnie kliknij prawym przyciskiem myszy docelową serię danych (pomarańczowe słupki) i wybierz Zmień typ wykresu serii z menu kontekstowego w Zmień typ wykresu okno dialogowe, wybierz Wykres liniowy dla serii danych docelowych z Typ wykresu rozwijana lista, zobacz zrzut ekranu:
3. Następnie kliknij OK , a następnie kliknij dwukrotnie linię docelową i wybierz Formatuj serie danychzobacz zrzut ekranu:
4. W otwartym Formatuj serie danych okienko, pod Wypełnij i wyrównaj kartę, wybierz Brak linii z Linia sekcja, patrz zrzut ekranu:
5. Wciąż w Wypełnij i wyrównaj przejdź do Marker sekcja, wybierz Wbudowany z Opcje markera sekcji, a następnie wybierz poziomy pasek w Rodzaj Nieruchomości w dół, na koniec ustaw rozmiar odpowiadający szerokości twoich pasków w Rozmiar pudełko, patrz zrzut ekranu:
6. A następnie ustaw kolor linii docelowych, jak chcesz z Wypełniać sekcja, patrz zrzut ekranu:
7. W końcu możesz usunąć niechciane elementy, takie jak tytuł wykresu, linie siatki, zgodnie z potrzebami.
Utwórz wykres kolumnowy ze średnią linią dynamicznie za pomocą przycisków opcji
Jeśli masz dużą tabelę ze sprzedażą produktu za wiele lat, teraz chcesz pokazać średnią linię dla każdego roku na wykresie. Aby poradzić sobie z tym zadaniem, możesz użyć wykresu ze średnią linią, klikając przycisk opcji, jak pokazano poniżej. Po wybraniu odpowiedniego roku wykres z linią średniej będzie wyświetlany dynamicznie.
Aby utworzyć ten wykres dynamiczny, należy najpierw wstawić kilka przycisków opcji, a następnie utworzyć tabelę danych pomocniczych, na podstawie której wykres jest oparty, na końcu wstawić łącze do przycisku opcji do wykresu. Wykonaj następujące czynności:
Najpierw wstaw kilka przycisków opcji
1. Kliknij Deweloper > wstawka > Przycisk opcji (kontrolka formularza), a następnie narysuj cztery przyciski opcji i edytuj tekst zgodnie z potrzebami, zobacz zrzuty ekranu:
 |
|
 |
2. Następnie kliknij prawym przyciskiem pierwszy przycisk opcji i wybierz Kontrola formatu z menu kontekstowego, zobacz zrzut ekranu:
3. W otwartym Formatuj obiekt okno dialogowe w obszarze Control wybierz komórkę, aby połączyć przycisk opcji z pliku Komórka pole tekstowe łącza i kliknij OK aby zamknąć, zobacz zrzut ekranu:
Po drugie, wstaw tabelę danych pomocniczych do tworzenia wykresu na podstawie
4. Skopiuj nazwy produktów z oryginalnej tabeli do nowego zakresu, a następnie zastosuj poniższą formułę do pustej komórki obok nowych skopiowanych danych, a następnie przeciągnij uchwyt wypełniania, aby uzyskać wynik, patrz zrzut ekranu:
5. Teraz po kliknięciu jednego przycisku opcji odpowiedni rok danych zostanie wyświetlony automatycznie, jak pokazano poniżej:
6. Następnie możesz obliczyć średnie wartości za pomocą poniższego wzoru, patrz zrzut ekranu:
Po trzecie, utwórz wykres na podstawie nowej tabeli danych pomocniczych
Następnie wybierz nowo utworzone dane, a następnie zastosuj powyższe krok 2 - krok 8 metody 1 do tworzenia wykresu kolumnowego ze średnią linią, a otrzymasz dynamicznie wykres ze średnią linią. Zobacz poniżej demo:
Utwórz wykres słupkowy ze średnią lub docelową linią w pionie
W tej sekcji dowiesz się, jak utworzyć wykres słupkowy ze średnią lub docelową w pionie, wykonaj następujące czynności:
1. Najpierw utwórz pomocniczą tabelę danych, wprowadź X i Y na dwie komórki, a następnie obliczyć średnie wartości w komórkach D2 i D3, patrz zrzut ekranu:
2. Następnie wprowadź numer 0 i 1 do komórki E2 i E3 dla wartości Y, patrz zrzut ekranu:
3. Następnie wybierz oryginalny zakres danych, a następnie kliknij wstawka > Wstaw wykres kolumnowy lub słupkowy > Clustered Barzobacz zrzut ekranu:
4. Wykres słupkowy został wstawiony do arkusza, a następnie kliknij pasek prawym przyciskiem myszy i wybierz Wybierz dane z menu kontekstowego, zobacz zrzut ekranu:
5. W wyskoczył Wybierz źródło danych dialog, kliknij przycisk Dodaj przycisk, zobacz zrzut ekranu:
6. A potem w Edytuj serię wprowadź nazwę serii do Nazwa serii pole tekstowe, a następnie zaznacz komórki z wartościami X (w tym przykładzie D2: D3) w pliku Wartości serii pole tekstowe, patrz zrzut ekranu:
7. Kliknij OK > OK aby zamknąć okna dialogowe, a nowa seria danych zostanie dodana do wykresu słupkowego (dwa pomarańczowe słupki), a następnie kliknij prawym przyciskiem myszy pomarańczowy pasek i wybierz Zmień typ wykresu seriizobacz zrzut ekranu:
8. W wyskoczył Zmień typ wykresu okno dialogowe, wybierz Rozpraszaj gładkimi liniami z Typ wykresu upuść dla Średni serie danych, patrz zrzut ekranu:
9. Następnie kliknij OK aby zamknąć okno dialogowe, a nowa seria danych jest konwertowana na punkt danych wzdłuż głównej osi Y, a następnie kliknij prawym przyciskiem myszy pasek, a następnie wybierz Wybierz dane z menu kontekstowego, zobacz zrzut ekranu:
10, w Wybierz źródło danych W oknie dialogowym W polu listy Wpisy legendy (serie) kliknij przycisk Średni opcję, a następnie kliknij Edytuj przycisk, zobacz zrzut ekranu:
11. Następnie plik Edytuj serię pojawi się okienko zachęty w pliku Wartości serii X. wybierz dwie komórki X (D2: D3), a w Wartości serii Y. pudełko, wybierz dwie komórki Y (E2: E3), zobacz zrzut ekranu:
12. Kliknij OK > OK aby zamknąć okna dialogowe i widać, że do wykresu została dodana pionowa średnia linia, zobacz zrzut ekranu:
13. Następnie kliknij prawym przyciskiem myszy pomocniczą oś pionową (prawa oś) i wybierz Formatuj oś z menu kontekstowego, zobacz zrzut ekranu:
14. W otwartym Formatuj oś okienko, pod Opcje osi zakładka, w Opcje osi sekcja, typ 1.0 Maksymalny oprawione pudełko, a otrzymasz wykres jak na poniższym zrzucie ekranu:
15. W końcu możesz usunąć pomocniczą oś pionową i niechciane linie siatki, tak jak potrzebujesz, teraz wykres słupkowy z pionową średnią linią jest gotowy. Zobacz zrzut ekranu:
Dodaj linię docelową z różnymi wartościami na wykresie słupkowym
Aby dodać linię docelową z różnymi wartościami na wykresie słupkowym, jak pokazano na poniższych zrzutach ekranu, wykonaj następujące czynności krok po kroku:
 |
|
 |
1. Wybierz zakres danych, a następnie kliknij wstawka > Wstaw wykres kolumnowy lub słupkowy > Clustered Bar aby wstawić wykres słupkowy, zobacz zrzut ekranu:
2. Następnie kliknij prawym przyciskiem myszy docelową serię danych (pomarańczowe słupki) i wybierz Formatuj serie danych z menu kontekstowego, zobacz zrzut ekranu:
3. W otwartym Formatuj serie danych okienko, pod Opcje serii zmień nakładanie się serii na 100%zobacz zrzut ekranu:
4. Wciąż w Formatuj serie danych okienku, przełącz się na Wypełnij i wyrównaj kartę, wybierz Nie wypełniać z Wypełniać sekcja, patrz zrzut ekranu:
5. Następnie przejdź do Dodawanie efektów karta, pod Shadow w sekcji, ustaw następujące operacje:
- Określ wymagany kolor z pliku Kolor upuścić;
- Ustaw rozmycie na 0 pt;
- Następnie dostosuj kąt do 0 °;
- W końcu zmień odległość na 2pt tak jak potrzebujesz.
6. Teraz przejdź do Opcje serii ponownie wybierz Oś drugorzędna z Opcje serii przekrój i zmień szerokość przerwy na 90% tak jak potrzebujesz. Zobacz zrzut ekranu:
7. Na koniec usuń niechciane elementy, takie jak kafelek wykresu, górna oś X, linie siatki, jak potrzebujesz, otrzymasz wykres słupkowy z różnymi liniami docelowymi, jak pokazano na poniższym zrzucie ekranu:
Dodaj docelową / średnią linię do wykresu kolumnowego lub słupkowego za pomocą przydatnej funkcji
Kutools dla programu Excel zapewnia ponad 50 specjalnych typów wykresów, których nie ma w programie Excel, takich jak Wykres punktowy, Wykres docelowy i rzeczywisty, Wykres strzałek różnic i tak dalej. Dzięki poręcznemu narzędziu do tworzenia wykresów Dodaj linię do wykresu, możesz dodać docelową lub średnią linię do wykresu kolumnowego lub słupkowego za pomocą jednego kliknięcia w programie Excel. Kliknij, aby pobrać Kutools for Excel za bezpłatną wersję próbną!
Pobierz wykres z przykładowym plikiem średniej / linii docelowej
Najlepsze narzędzia biurowe
Kutools dla programu Excel - pomaga wyróżnić się z tłumu
Kutools dla programu Excel oferuje ponad 300 funkcji, Pewność, że to, czego potrzebujesz, jest w zasięgu jednego kliknięcia...
Karta Office - Włącz czytanie i edycję na kartach w Microsoft Office (w tym Excel)
- Jedna sekunda, aby przełączać się między dziesiątkami otwartych dokumentów!
- Zmniejsz liczbę kliknięć myszą każdego dnia, pożegnaj się z dłonią myszy.
- Zwiększa produktywność o 50% podczas przeglądania i edytowania wielu dokumentów.
- Wprowadza wydajne karty do pakietu Office (w tym programu Excel), podobnie jak przeglądarki Chrome, Edge i Firefox.
