Jeśli pracujesz w dziale sprzedaży i Twoje życie kręci się wokół celów, ta technika tworzenia wykresów jest dla Ciebie. A jeśli nie, przeczytaj to i tak, aby nauczyć się kilku fajnych sztuczek z wykresami w programie Excel.
W tym poście na blogu pokażę Ci super sposób na stworzenie dynamicznej linii docelowej na wykresie Excela, który pomoże Ci śledzić Twoje wyniki na przestrzeni miesięcy. Coś, jak pokazano poniżej: 
Linia docelowa jest kontrolowana przez pasek przewijania i jakby cel został osiągnięty (lub przekroczony) w którymkolwiek z miesięcy, zostaje podświetlony na zielono.
Tworzenie dynamicznej linii docelowej na wykresie słupkowym Excel
Ten wykres składa się z 3 części:
- Wykres słupkowy
- Linia docelowa (pozioma linia przerywana)
- Pasek przewijania (do sterowania wartością docelową)
Wykres słupkowy
Mam dane, jak pokazano poniżej:
Komórki B2:B13 mają wszystkie wartości, podczas gdy C2:C13 pokazuje wartość tylko wtedy, gdy przekracza wartość docelową (w komórce F2). Jeśli wartość jest niższa niż wartość docelowa, pokazuje #N/A. Teraz musimy wykreślić te wartości na wykresie skupień
- Wybierz cały zestaw danych (A1:C13)
- Przejdź do Wstaw -> Wykresy -> Wykres kolumnowy grupowany
- Wybierz dowolny z pasków dla wartości „Powyżej celu”, kliknij prawym przyciskiem myszy i wybierz Formatuj serie danych
- W sekcji Opcje serii zmień wartość nakładania serii na 100%
- Tworzy to wykres, na którym wszystkie wartości przekraczające wartość docelową są podświetlone innym kolorem (możesz to sprawdzić, zmieniając wartość docelową)
Linia docelowa
Tutaj pokażę Ci sprytny sposób na utworzenie linii docelowej za pomocą pasków błędów
- Wybierz wykres i przejdź do Projekt -> Wybierz dane
- W oknie dialogowym Wybierz źródło danych kliknij Dodaj
- W polu Edytuj serię wpisz Nazwę serii jako „Linia docelowa”, a w polu Wartość serii wybierz komórkę Wartość docelowa
- Spowoduje to wstawienie wykresu słupkowego tylko dla pierwszego punktu danych (styczeń)
- Wybierz ten pasek danych i kliknij prawym przyciskiem myszy i wybierz Zmień typ wykresu serii
- Zmień typ wykresu na wykres punktowy. W styczniu zmieni to pasek w pojedynczą kropkę
- Wybierz punkt danych i przejdź do Projekt -> Układy wykresu -> Dodaj element wykresu -> Słupki błędów -> Więcej opcji słupków błędów
- W przypadku programu Excel 2010 wybierz punkt danych i przejdź do Układ -> Analiza -> Słupki błędów -> Więcej opcji słupków błędów
- W przypadku programu Excel 2010 wybierz punkt danych i przejdź do Układ -> Analiza -> Słupki błędów -> Więcej opcji słupków błędów
- Zauważysz poziome linie słupków błędów po obu stronach punktu rozproszenia. Wybierz ten poziomy pasek błędu, a następnie w sekcji Opcje paska błędu wybierz Niestandardowy i kliknij Określ wartość
- Podaj wartość dodatnią jako 11, a wartość ujemną jako 0 (możesz użyć trafienia i próby, aby zobaczyć, jaka wartość wygląda dobrze na wykresie)
- Wybierz punkt danych Scatter, kliknij prawym przyciskiem myszy i wybierz opcję Formatuj dane serii. Przejdź do opcji znacznika i wybierz typ znacznika jako brak. To usuwa punkt danych i masz tylko pasek błędu (który jest twoją linią docelową)
- Zwróć uwagę, że pasek błędu zmieni się za każdym razem, gdy zmienisz wartość docelową. Po prostu sformatuj ją tak, aby była linią przerywaną i zmień jej kolor, aby wyglądała lepiej
Pasek przewijania
- Utwórz pasek przewijania i wyrównaj go z wykresem. Dopasuj pasek przewijania do wykresu. Kliknij tutaj, aby dowiedzieć się, jak utworzyć pasek przewijania w programie Excel.
- Ustaw maksymalną wartość paska przewijania równą maksymalnej wartości na wykresie i połącz wartość paska przewijania z dowolną komórką (użyłem G2)
- W komórce zawierającej wartość docelową użyj formuły =500-G2 (500 to maksymalna wartość na wykresie)
- Ma to na celu upewnienie się, że wartość docelowa porusza się teraz wraz z paskiem przewijania
Otóż to!! Teraz, gdy przesuniesz pasek przewijania i zmienisz wartości docelowe, paski spełniające cel zostaną automatycznie podświetlone na inny kolor.
Wypróbuj sam… Pobierz plik
