Dynamiczna linia celu w MS Excel

Pracujesz w handlu, a twoja praca opiera się o cele sprzedażowe? Poprzez cele kontrolujesz pracę swojego zespołu? Jeżeli chcesz kontrolować realizację wyznaczonego celu i na bieżąco go korygować  dynamiczna linia celu będzie idealna dla Ciebie!

Dynamiczna linia celu – jak ją stworzyć?

Stworzenie źródła danych i wykresu słupkowego

Przykładowe źródło danych obejmuje liczbę zadań zamkniętych w poszczególnych miesiącach.

Określmy teraz cel oraz wyznaczmy w których miesiącach cel został osiągnięty. W komórkach C2:C13 użyjemy formuły JEŻELI.

Stawiamy więc warunek – jeżeli liczba zamkniętych w danym miesiącu zadań (zawartych w komórkach B2:B13) jest większa od wartości wyznaczonego celu (zawartego w naszym wypadku w komórce F3), formuła zwróci wartość równą liczbie zamkniętych zadań. W przeciwnym wypadku zobaczymy błąd NAZWA().

Na podstawie danych możemy teraz utworzyć wykres słupkowy. W tym celu zaznaczamy całą tabelę zawierającą nasze dane (komórki A1:C13) i z zakładki Wstawianie wybieramy Wykres Kolumnowy Grupowany 2-W.

Teraz klikamy na jeden z słupków, przedstawiających przekroczenie celu (pomarańczowych) i wybieramy Formatuj serię danych.

W Opcjach serii zmieniamy wartość nakładania serii na 100%.

Teraz na wykresie miesiące, w których osiągnięty został cel wyświetlają się na inny kolor.

Dodanie linii celu

Linię celu dodamy wykorzystując słupki błędów.

Wybieramy stworzony wcześniej wykres słupkowy. Z menu Narzędzie wykresów Projektowanie wybieramy Zaznacz dane.

W otwartym okienku wybieramy Dodaj. Pojawi się okienko Edytowanie serii. Nazwijmy naszą serię Linia celu, jej wartością będzie komórka zawierająca wyznaczony przez nas cel – F3.

Spowoduje to dodanie słupka (o wartości naszej linii celu) tylko w miesiącu styczniu (zaznaczonego kolorem szarym).

Musimy więc zmienić typ części wykresu odpowiadającego za linię celu. Klikamy prawym przyciskiem myszy na utworzony słupek i wybieramy Zmień typ wykresu seryjnego. Zmieniamy typ dla serii Linia celu na Punktowy.

Teraz linia celu przedawniona jest za pomocą punktu (zaznaczonego kolorem szarym w miesiącu styczniu). Wybierzmy powstały punkt. Z menu Narzędzie wykresów  Projektowanie wybieramy Dodaj element wykresu -> Słupki błędów -> Więcej opcji słupków błędów.

Wokół punktu powstały linie błędu. Nie mają one jeszcze satysfakcjonującej nas długości. Aby to zmienić w Formatowaniu słupków błędów wybieramy Wielkość błędu jako Niestandardową.

Wybieramy przycisk Określ wartość. W naszym przypadku ustawiamy Dodatnią wartość błędu jako 11 (jest to pomniejszona o 1 liczba wartości znajdujących się w osi poziomej wykresu – w tym wypadku jest to 12 miesięcy). Ujemna wartość błędu wyniesie 0.

Aby usunąć szarą kropkę znajdującą się na początku naszej linii celu klikamy na niej i wybieramy Formatuj serię danych. W zakładce Wypełnienie i linia -> Znacznik -> Opcje znaczników wybieramy brak.

W ten sposób powstaje linia celu, zależna od założonej wartości celu.

Dodanie paska przewijania i utworzenie dynamicznej linii celu.

Zastosowanie paska przewijania umożliwia przesuwanie linii celu w zależności od potrzeb.

W pierwszej kolejności należy dodać pasek przewijania. Znaleźć go można w zakładce Deweloper -> Wstaw -> Pasek przewijania.

Jeżeli nie widzisz zakładki Deweloper, należy włączyć jej wyświetlanie. W tym celu klikamy prawym przyciskiem myszy w dowolnym miejscu wstążki i wybieramy Dostosuj wstążkę.

Następnie wśród widocznych kart odnajdujemy kartę Deweloper, zaznaczamy ją i akceptujemy przyciskiem OK.

Pasek przewijania musimy wpasować do naszego wykresu, a następnie przypisać mu odpowiednie wartości. W tym celu w dowolnej komórce (u nas jest to komórka F4) wstawiamy najwyższą możliwą wartość celu, natomiast w komórce zawierającej nasz cel (komórka F3) wstawiamy formułę =35 – F4, gdzie 35 maksymalna wartość jaką może osiągnąć nasza linia celu (można zastąpić ją dowolną inną liczbą).

Następnie klikamy na pasku przewijania prawym przyciskiem myszy i wybieramy Formatuj formant. W zakładce Kontrolka ustawiamy odpowiednio:

  • Wartość minimalna 0;
  • Wartość maksymalna 35 (równa maksymalnej wartości jaką może osiągnąć nasza linia celu)
  • Zmiana przyrostowa 1 – o tyle będzie przeskakiwać nasza linia po kliknięciu na strzałce pasku przewijania;
  • Łącze komórki F4 – komórka, której wartość zmienia się wraz z przesuwaniem linii celu.

Gotowe!

W ten sposób powstał wykres z dynamiczną linią celu! Teraz wraz ze zmienianiem się linii celu kolor zmienią także słupki tych miesięcy, których wartości spełniają nasz cel.

Spróbuj sam! Ściągnij przykładowy arkusz Excel!

    Dodaj komentarz

    Your email address will not be published. Required fields are marked *

    Post comment

    Witryna wykorzystuje Akismet, aby ograniczyć spam. Dowiedz się więcej jak przetwarzane są dane komentarzy.