Aby wizualnie zobaczyć, kiedy linia na wykresie znajduje się powyżej lub poniżej średniej serii, możemy po prostu wstawić średnią linię na wykresie. To będzie wyglądać mniej więcej tak.
Czy to jest atrakcyjne? Albo ten?
Mogę odgadnąć twoją odpowiedź.
Jak więc zrobić ten wykres, który podświetla obszar, gdy linia znajduje się powyżej lub poniżej średniej? Nauczmy się tego krok po kroku.
Zaznacz obszar, gdy linia znajduje się powyżej i poniżej średniej
Najlepszym sposobem uczenia się jest uczenie się na przykładzie. Zacznijmy więc od przykładu.
Tutaj mam kilka danych, które mówią o całkowitej sprzedaży dokonanej przez poszczególnych sprzedawców. Chcę zwizualizować te dane na wykresie.
Krok 1: Dodaj trzy kolumny pomocnicze
Aby stworzyć taki wykres, będziemy potrzebować trzech kolumn pomocniczych. Pierwsze kolumny pomocnicze będą zawierały średnie dane. Druga kolumna pomocnicza będzie zawierała wszystkie punkty, w których sprzedaż jest większa niż średnia. A trzecia kolumna będzie zawierała punkty, w których sprzedaż jest mniejsza niż średnia.
Zatem formuła w średniej kolumnie będzie wyglądać tak:
| =ŚREDNIA($B$2:$B$7) |
Napisz tę formułę w pierwszej komórce średniej kolumny i przeciągnij ją w dół. Upewnij się, że wszystkie komórki mają te same wartości. Użyliśmy odwołań bezwzględnych, aby odniesienia nie zmieniały się po skopiowaniu formuły.
W następnej kolumnie potrzebujemy wszystkich wartości UP. Będzie to dodatnia różnica między danymi średnimi a sprzedażowymi. Innymi słowy, musimy odjąć dane średniej od sprzedaży tylko wtedy, gdy sprzedaż jest większa niż średnia. Wzór w D2 będzie następujący:
| =JEŻELI(B2>C2,B2-C2"") |
Przeciągnij go w dół.
W następnej kolumnie potrzebujemy wszystkich wartości Down. Będzie to ujemna różnica między średnią a danymi sprzedażowymi. Innymi słowy, musimy odjąć dane średniej od sprzedaży tylko wtedy, gdy sprzedaż jest mniejsza niż średnia. Wzór na E2 będzie następujący:
| =JEŻELI(B2<> |
Przeciągnij go w dół.
Krok 2: Wybierz Doradcę ds. Sprzedaży i Sprzedaż i wstaw wykres liniowy: W naszym przykładzie wybieramy zakres A1:C7. Przejdź do Wstaw--> Wykresy--> Linia i powierzchnia --> Linia ze znacznikami. Mamy wykres, który będzie wyglądał tak.
Krok 3: Jeszcze raz dodaj średnią serię
Kliknij prawym przyciskiem myszy wykres i kliknij opcję Wybierz dane. Tutaj kliknij przycisk Dodaj w sekcji Legenda i wpisy. Wybierz średni zakres C2:C7 jako wartości serii. Wstawi linię na oryginalnej średniej linii. Będziemy go potrzebować w formatowaniu. To jest ważne.
Krok 4: Dodaj serie w górę iw dół do danych
Po ponownym dodaniu średniej serii, podobnie dodaj kolejno serie Up i Down do serii. Na koniec otrzymasz wykres, który wygląda tak. to nie wygląda tak, jak chcemy, prawda? Poczekaj, to się rozwinie.
Krok 5: Zmień drugą średnią serię, górną i dolną serię na skumulowany wykres warstwowy
Kliknij prawym przyciskiem myszy dowolną serię na wykresie i kliknij opcję Zmień typ wykresu serii danych. To bardzo zmieni wykres i zacznie przybierać formę, jaką chcemy, aby był ten wykres liniowy Excela.
Krok 6: Wybierz Brak wypełnienia dla średniego wykresu skumulowanego obszaru:
Kliknij prawym przyciskiem myszy obszar części średniej i kliknij serię danych formatu. Zgodnie z opcją wypełnienia przejdź do wypełnienia i wybierz opcję bez wypełnienia.
Teraz wykres wygląda dokładnie tak, jak chcieliśmy, aby wyglądał. Musimy tylko trochę sformatować, aby pasował do naszego motywu pulpitu nawigacyjnego / prezentacji.
Krok 7: Sformatuj wykres, aby pasował do motywu.
Usuń wszystkie mniej wymagane elementy z wykresu. Usunąłem średnią legendę z wykresu, ponieważ jej nie potrzebuję. Usunąłem linie siatki, ponieważ chcę, aby mój wykres wyglądał czyściej. Zmieniłem kolor górnego obszaru na zielony, a dolnego na czerwony. Tytuł wykresu nazwałem „Sprzedaż w stosunku do średniej”.
Wreszcie mam ten wykres.
Notatka:Ten wykres może być używany jako tabela celów i osiągnięć. Po prostu zmień wartości średniej serii na cele i boom, otrzymasz w Excelu wykres celu kreatywnego vs.
Pobierz plik szablonu poniżej:
Więc tak, w ten sposób można kreatywnie wykorzystać wykres liniowy programu Excel do wyróżnienia obszarów, w których linia schodzi poniżej średniej lub powyżej średniej. Mam nadzieję, że ci się przyda. Jeśli masz jakiekolwiek wątpliwości dotyczące tego wykresu na jakikolwiek inny temat związany z Excelem / VBA, zapytaj w sekcji komentarzy poniżej.
Jak dodać cień do linii krzywej na wykresie Excel? | W Google Analytics i innych narzędziach online musiałeś widzieć wykresy liniowe. Na tych wykresach widać zacieniony obszar pod krzywymi. To cieniowanie wygląda fajnie, prawda? Pytanie brzmi, czy możemy to zrobić w programie Excel? Jak możemy zacienić obszar pod wykresem krzywej?
Jak wyróżnić maksymalne i minimalne punkty danych na wykresie Excel | Kteraz najwyższa i najniższa wartość w zbiorze danych jest niezbędna w prawie wszystkich rodzajach raportów. Ale na wykresach liniowych i kolumnowych Excela często trudno jest określić, która wartość jest najwyższa, a która najniższa.
Jak wstawić dynamiczną pionową linię znacznika na wykresie liniowym programu Excel |Możemy ręcznie narysować pionową linię na wykresie, ale to nie jest sprytne. Chcielibyśmy dynamicznie dodawać pionowe linie, aby zaznaczyć określony punkt danych, powiedzmy wartość maksymalną.
Jak utworzyć wykres kamieni milowych w programie Excel |Wykres kamieni milowych pokazuje datę lub godzinę osiągnięcia kamienia milowego w sposób graficzny. Ten wykres musi być łatwy do odczytania, wyjaśniający i atrakcyjny wizualnie.
Wykres wodospadu |Ten wykres jest również znany jako wykres latających cegieł lub wykres mostu. Służy do zrozumienia, w jaki sposób na wartość początkową wpływa szereg pośrednich wartości dodatnich lub ujemnych.
Wykresy Excela: małe wykresy w komórce | Sparklines to małe wykresy znajdujące się w jednej komórce. Sparklines służą do pokazania trendów, ulepszeń i przegranych w danym okresie. Wykresy przebiegu w czasie są wykresami, ale mają ograniczoną funkcjonalność w porównaniu ze zwykłymi wykresami.
Kreatywny wykres kolumnowy zawierający sumy | Uwzględnienie sumy kolumn skupionych na wykresie i porównanie ich z inną grupą kolumn na wykresie nie jest łatwe. Tutaj wyjaśniłem, jak inteligentnie uwzględniać sumy na wykresie kolumnowym grupowanym.
4 kreatywne wykresy celów i osiągnięć w programie Excel |Target vs Achievement Charts jest podstawowym wymogiem każdego pulpitu Excel. W raportach miesięcznych i rocznych wykresy Target vs Achievement są pierwszymi wykresami, do których kierownictwo się odnosi, a dobry wykres celów vs. Osiągnięcia z pewnością przyciągnie uwagę kierownictwa
Popularne artykuły:
50 skrótów Excela, które zwiększą Twoją produktywność | Przyspiesz swoje zadanie. Te 50 skrótów przyspieszy pracę w programie Excel.
Jak korzystać z funkcji WYSZUKAJ.PIONOWO w programie Excel | Jest to jedna z najczęściej używanych i popularnych funkcji programu Excel, która służy do wyszukiwania wartości z różnych zakresów i arkuszy.
Jak używać LICZ.JEŻELI w programie Excel 2016 | Policz wartości z warunkami za pomocą tej niesamowitej funkcji. Nie musisz filtrować danych, aby policzyć konkretną wartość. Funkcja Countif jest niezbędna do przygotowania deski rozdzielczej.
Jak korzystać z funkcji SUMIF w programie Excel? | To kolejna ważna funkcja deski rozdzielczej. Pomaga to zsumować wartości w określonych warunkach.