Dynamiczna oś danych w excelu

Dynamiczna oś czasu projektu w Excelu (milestone/timeline)

Posted on Posted in wykresy

Wykres osi czasu projektu to kolejny z niestandardowych wykresów możliwych do utworzenia w Excelu. Posługują się nim najczęściej osoby związane z branżą zarządzania projektami lub marketingiem. Wykres osi czasu projektu, z ang. milestone lub timeline, wykorzystuje się do śledzenia etapów danego projektu, wzdłuż osi czasu. Rozwiązanie to umożliwia prostą kontrolę terminowości zaplanowanych zadań. W dzisiejszym wpisie, przedstawię jak do wykresu osi czasu, dodać możliwość dynamicznego przewijania dat oraz umieścić na nim informację o aktualnej dacie. Gotowy plik do pobrania tutaj.

 

Przygotowanie danych

Do budowy wykresu posłużymy się tabelką, w której znajdują się informacje dotyczące daty etapu, jego nazwy oraz pomocniczych kolumn z wartościami. W ostatnim wierszu tabeli, nagłówku Data umieścimy bieżącą datę.

 

Tabela z danymi do wykresu

 

Aby nasz wykres stał się dynamiczny potrzebujemy skorzystać z Paska przewijania, który wstawiamy korzystając z karty Deweloper.

 

Pasek przewijania w Excelu

 

Gotowy pasek umieszczamy w nowym arkuszu o nazwie Wykres, ustawiając jego właściwości jak poniżej. Następnie w arkuszu z danymi w komórce A1 wpisujemy formułę odnosząca się do komórki A1 arkusza Wykres.

Ustawienia paska przewijania

 

W kolejnym kroku w komórce J2 arkusza z danymi, wpisujemy formułę =MIN.K(C:C;WIERSZ(1:1)+$A$1), która posortuje w kolejności rosnącej daty z tabeli z projektami. Podana formuła będzie posiadała element dynamiczny w postaci komórki A1, która stanowi łącze do paska przewijania. Powyższą formułę przeciągamy 6 wierszy w dół. W komórce obok tzn. K2, za pomocą funkcji =JEŻELI.BŁĄD(JEŻELI($I2=2;"";WYSZUKAJ.PIONOWO($J2;$C$2:$F$32;NR.KOLUMNY(B:B);FAŁSZ));"") znajdujemy opis projektu przyporządkowany do danej daty. Wspomnianą formułę przeciągamy o dwie kolumny w prawo. W ostatniej kolumnie o nazwie Pomoc3 umieszczamy formułę =LICZ.JEŻELI($J$2:J2;J2), która sprawdzi czy bieżąca data pokrywa się z datą wyszczególnioną w tabeli etapów.

 

Wartość paska przewijania

 

Przed przystąpieniem do budowy wykresu zbudujemy jeszcze jedną tabelkę pomocniczą. Posłuży nam ona za źródło danych do stworzenia linii z aktualną datą. A zatem do komórki P2 wpisujemy =JEŻELI(ORAZ($I2=1;$J2=$C$32);$J2;""), natomiast do Q2 =JEŻELI(ORAZ($I2=1;$J2=$C$32);30;""), a następnie całość przeciągamy w dół o 6 wierszy.

 

Zapisz

Zapisz

Zapisz

Zapisz

Zapisz

Zapisz

Budowa wykresu

Jak już wspomniałem wcześniej, wykres osi czasu projektu nie należy do grupy wykresów standardowo wbudowanych w Excela. W celu jego budowy należy skorzystać z połączenia wykresów linowego ze znacznikami oraz kolumnowego. Sam wykres nie mógłby również istnieć, gdyby nie słupki błędu, które często przydają się przy tego rodzaju niestandardowych wykresach. Na początek zaznaczamy dane z kolumny pomocniczej o nagłówkach Data oraz Pomoc2 i na ich podstawie tworzymy wykres liniowy ze znacznikami.

 

Wykres liniowy ze znacznikami

 

 

Następnie zaznaczamy dane z kolumn Nazwa etapu oraz Pomoc1 i wklejamy je do wykresu.

 

Wykres z nową serią danych

 

Na powyższym wykresie klikamy na nowo dodaną serię danych a następnie zmieniamy jej rodzaj na Oś pomocniczą i wykres kolumnowy.

 

Zmiana serii danych

 

W kolejnym kroku klikamy na nasz wykres i dodajemy do niego kolejną serię danych, zaznaczając dane z kolumny Wartość. Nową serię danych również ustawiamy jako pomocniczą, o typie wykresu kolumnowego. Następnie ponownie zaznaczamy wykres i zmieniamy wartości osi poziomej dla pierwszej serii danych na wartości z kolumny pomocniczej Data. Skalę osi pionowych ustawiamy na przedział -30 do 40.

 

Wykres liniowo kolumnowy

 

Po zaznaczeniu dowolnego ze słupków zmieniamy w jego opcjach serii, parametry nakładania serii na 100% oraz szerokości przerwy na 0%. W celu wykonania linii, na końcach których pojawi się opis danego etapu projektu wykorzystamy etykiety danych wykresu słupkowego oraz wspomniane już słupki błędu. Wartość etykiet ustawiamy na Nazwę kategorii, natomiast słupki błędu na ujemne, bez zakończania, procentowe, o wartości 100%. Na koniec usuwamy wypełnienie wykresu kolumnowego. Po kliknięciu na dany słupek możemy edytować jego zakończenie wybierając symbol strzałki.

 

Słupki błędu ze strzałkami

 

Aby przedstawić na wykresie aktualną datę musimy skorzystać z trzeciej, ostatniej serii danych. W tym celu należy przesunąć wykres korzystając z paska danych, tak daleko, aż zauważymy pojedynczy wykres słupkowy znajdujący się w miejscu aktualnej daty. Dla wspomnianego słupka wykonujemy analogiczne zabiegi jak dla słupków z opisami etapów. Wyjątkiem będzie jedynie podanie jako źródła etykiet, wartości z kolumny Data dziś.

 

Aktualna data na wykresie

 

Jak widać wykres jest już prawie gotowy. Jedyne co budzi zastrzeżenia to fakt, iż etykiety osi nie pokrywają się z datami podanymi jako etapy poszczególnych faz projektu. Aby naprawić zaistniałą sytuację, usuwamy etykiety osi oraz jej znaczniki a następnie, dla niebieskich punktów dodajemy etykiety, których wartość ustawiamy na opcję Nazwa kategorii.

 

Etykiety jako oś danych

 

Kiedy usuniemy zbędne elementy wykresu w tym: znaczniki i linię wykresu liniowego, osie pionowe oraz poziome linie siatki. Wykres będzie można uznać za prawie gotowy.

 

Oś czasu w excelu

 

Pozostał jeszcze tylko jedne mały szczegół. Jak widzicie wartość bieżącej daty pokazuje się dwukrotnie na wykresie. Raz poniżej osi danych a następnie na końcu słupka błędu. Aby usunąć zbędą etykietę posłużymy się makrem, które umieszczamy w module arkusza. Po kliknięciu PPM na pasek przewijania wybieramy opcję Nowe a następnie do powstałej akcji Sub Pasekprzewijania1_Zmienianie(), powiązanej z  paskiem danych, wklejamy poniższe makro.

 

Nowa akcja w module

 

Zapisz

Zapisz

Zapisz

On Error Resume Next
    Set rng = Worksheets("Dane").Range("P2:P8")
    
    i = 0
    For Each cel In rng
        i = i + 1
        If cel.Value <> "" Then
            ' ukrywanie etykiet
            ActiveSheet.ChartObjects("Wykres 1").Activate
            ActiveChart.FullSeriesCollection(1).DataLabels.Select
            ActiveChart.FullSeriesCollection(1).Points(i).DataLabel.Select
            Selection.Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(255, 255, 255)
        Else
            ' odkrywanie etykiet
            ActiveSheet.ChartObjects("Wykres 1").Activate
            ActiveChart.FullSeriesCollection(1).DataLabels.Select
            ActiveChart.FullSeriesCollection(1).Points(i).DataLabel.Select
            Selection.Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(0, 0, 0)
            ActiveSheet.ChartObjects("Wykres 1").Activate
        End If
    Next cel
       
       On Error GoTo 0

 

Powyższe makro edytuje etykiety wykresu według podanej reguły. Dla każdej komórki zakresu sprawdza, czy jej wartość jest różna od pustej wartości. Jeżeli zależność ta jest spełniona wybierana jest pozycja danej etykiety, której kolor ustawiany jest na biały. W przeciwnym wypadku kolor etykiet pozostaje czarny. Dodatkowo w celu aktualizacji bieżącej daty na wykresie, do arkusza z wykresem, można dodać poniższe makro.

 

Private Sub Worksheet_Activate()
Call Pasekprzewijania1_Zmienianie
End Sub

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *