Wykres Gantta

Posted on Posted in Dashboard

Przyszedł czas na opublikowanie pierwszego dashboardu. Przedstawione na nim zagadnienia dotyczą zarządzania projektami a ściślej mówiąc kontroli projektów przy użyciu wykresu Gantta. Wykres ten zazwyczaj wykonuje się w Excelu korzystając ze zwykłych wykresów słupkowych. Problem pojawia się gdy do wykresu chcemy dodać dynamikę, w postaci przewijania wykresu w czasie lub pomiędzy projektami. Z pomocą przychodzi wtedy formatowanie warunkowe oraz kilka innych użytecznych sztuczek. Ze względu na złożoną strukturę dashboardu nie będę omawiał krok po kroku jak go odtworzyć, skupie się natomiast na kluczowych technikach, bez których wykonanie pulpitu nie było by możliwe. Dashboard można pobrać tutaj.

1. Pole kombi, Menadżer nazw, Funkcja WYBIERZ, Aparat fotograficzny

Powyższe narzędzie pozwolą nam stworzyć dynamicznie wybierany zakres danych. Na początku wstawmy do naszego arkusza Pole kombi. W tym celu należy z karty Deweloper w grupie Formanty kliknąć przycisk Wstaw a następnie wybrać Pole kombi. Pole to pozwoli nam na stworzenie rozwijanej listy wyboru.

 

Pole kombi na karcie Deweloper

 

Po utworzeniu Pola kombi klikamy na niego prawym klawiszem myszy, wybieramy formatowanie formantu i definiujemy zakres wejściowy oraz łącze komórki. Zakres wejściowy to zakres komórek, które będą zawarte w polu listy natomiast łącze do komórki to komórka, w której będzie znajdować się wartość określająca element wybrany w polu listy. W naszym przypadku zakresem wejściowym będą komórki w przedziale $F$2:$F$4 określające dni tygodnia natomiast pole łącza komórki ustawimy w polu $H:$2.

 

Formatowanie Pola kombi

 

Po wykonaniu powyższych kroków widzimy, że zmieniając wartości w Polu kombi w komórce $H:$2 pokazuje się liczba oznaczająca kolejność wybranej opcji. Kiedy Pole kombi mamy już gotowe zaznaczmy 3 identyczne zakresy komórek kolorując je różnymi kolorami.

 

Kolorowe pola

 

Następnie na karcie Formuły w grupie Nazwy zdefiniowane klikamy na przycisk Menadżer nazw. To narzędzie pozwoli nam nazwać określone zakresy danych. Klikając na przycisk Nowy pojawi się okienko, w którym w polu Nazwa wpiszemy poniedziałek a jako Odwołuje się do wybierzemy zakres komórek pomalowanych na pomarańczowo. Tą czynność wykonujemy analogicznie dla pozostałych dni tygodnia i pokolorowanych komórek.

 

Definiowanie nowej nazwy w menadżerze danych

 

Po zakończonej pracy powinniśmy w okienku Menadżera zobaczyć następujące nazwy.

 

Zdefiniowane pola w Menadżerze nazw

 

W kolejnym kroku stworzymy ostatnią nazwę odwołująca się do funkcji WYBIERZ. Jako pierwszy argument funkcji wybieramy łącze do Pola kombi, czyli komórkę $H:$2. Następnie korzystając z przycisku Użyj w formule w grupie Nazwy zdefiniowane, jako kolejne argumenty funkcji wybieramy wcześniej zdefiniowane nazwy dni.

 

Zastosowanie przycisku Użyj w formule

 

Ukończona nazwa Wybór powinna wyglądać następująco.

 

nazwa-wybór

 

Podczas ostatniego etapu prac skorzystamy z Aparatu fotograficznego. Aparat nie jest od razu widoczny wśród narzędzi Excela. Aby go użyć należy wejść w Opcje programu Excel, Dostosowywanie wstążki a następnie z Poleceń, których nie ma na wstążce wybrać Aparat fotograficzny. Przyszedł czas na zrobienie pierwszego zdjęcia. W tym celu klikamy ikonkę Aparatu i zaznaczamy dowolnie wybrany obszar komórek. Następnie do nowo powstałego zdjęcia, w pasku formuł wpisujemy nazwę zdefiniowaną Wybór.

 

Przypisanie nazwy zdefiniowanej do zdjęcia

 

Po tym zabiegu zmieniając kolejne dni w Polu kombi zmienia się kolor naszego zdjęcia zgodnie z kolejnością przypisanych nazw dni tygodnia. Przedstawione tutaj narzędzia, w przypadku Dashboardu projektów, były pomocne do stworzenia dynamicznego zdjęcia wybierającego odpowiedni interwał prezentacji danych.

 

2. Sortowanie danych przy użyciu funkcji

Jedną z funkcjonalności Dashboardu projektów jest możliwość sortowania danych według określonych kryteriów. Aby wykonać takie sortowanie posłużyłem się funkcją tablicową oraz połączeniem kilku znanych funkcji. W podanym zestawieniu mamy przedstawioną listę zadań oraz budżet dla każdego z nich. Naszym zadaniem będzie posegregowanie zadań według wysokości budżetu. Aby znaleźć maksymalną wartość wśród budżetów posłużmy się funkcją MAX.K. Funkcja ta wyznacza maksymalną k-tą wartość z zadanej tablicy danych. Jako parametr K użyjemy funkcji WIERSZ, która przeciągnięta w dół odlicza kolejne wartości parametru K.

 

Funkcja MAX.K

 

W tym momencie przyszedł czas na formułę tablicową, czyli funkcję którą zatwierdzamy klawiszami Ctrl+Shift+Enter. Ma ona następującą postać =INDEKS(A:A;MIN.K(JEŻELI(F2=$B$2:$B$13;WIERSZ($B$2:$B$13);"");LICZ.JEŻELI($F$2:F2;F2))). W tej funkcji wyszukujemy w kolumnie A:A zadanie na podstawie numeru wiersza podanego przez funkcję MIN.K. Funkcja MIN.K sprawdza czy wartość w posortowanej liście jest równa wartości z listy pierwotnej i jeżeli tak to zwraca nr wiersza zadania z nieposortowanej listy. Parametr K funkcji MIN.K obsługuje funkcja LICZ.JEŻELI. Funkcja ta sprawdza czy na posortowanej liście występują zduplikowane wartości, jeżeli tak to podaje kolejny numer odpowiadający ilości wystąpienia danej wysokości budżetu.

 

Funkcja tablicowa pomocna przy sortowaniu danych

 

3. Lista z pominięciem pustych pól

Tworząc wykres Gantta w układzie miesięcznym napotykamy na problem przesunięcia dni na wykresie o miesiąc. Z pomocą w tym wypadku przychodzi po raz kolejny funkcja tablicowa. Dzięki niej utworzymy listę dat jedna pod drugą zaczynających się pierwszego dnia miesiąca. Na początek stwórzmy listę dat począwszy od przyjętej daty startu wykresu a w kolumnie obok umieśćmy liczby zaczynając od 0.

 

Lista z datami

 

Prócz powyższej listy potrzebny nam będzie również Pasek przewijania, którego podobnie jak Pole kombi tworzymy korzystając z karty Deweloper.

 

Pasek przewijania do dashboardu

 

Tworzymy Pasek w dowolnym miejscu arkusza, klikamy na niego prawym przyciskiem myszy i zaznaczamy komórkę $E:$2 jako łącze komórki. Teraz z każdym przesunięciem Paska komórka $E:$2 będzie zmieniała swoją wartość. W komórkę I2 wpisujemy następującą formułę =WYSZUKAJ.PIONOWO(E2;A2:B14;2;FAŁSZ). Funkcja ta wyszuka wartość Łącza paska komórki w podanym zakresie i zwróci określoną datę. Do komórki I3 wpisujemy =I2+1 i przeciągamy w dół aż zobaczymy miesiąc styczeń. Tym sposobem stworzyliśmy dynamiczną listę dat.

 

Lista miesięcy z polem kombi

 

Następnie korzystając z prostych formuł daty DZIEŃ i MIESIĄC oraz funkcji logicznej JEŻELI sprawdzamy czy dany dzień jest pierwszym dniem miesiąca. Jeżeli otrzymamy wartość prawdziwą pobieramy z daty jej miesiąc. Po wprowadzeniu formuły =JEŻELI(DZIEŃ(I2)=1;MIESIĄC(I2);"") do komórki J2 i przeciągnięciu w dół pojawią się nam liczy 1 i 2 odpowiadające dniom miesiąca. Naszym zadaniem będzie stworzenie obok w kolumnie K formuły tablicowej, która stworzy listę umieszczając liczby 1 i 2 jedna pod drugą. Do komórki K2 wprowadzamy następującą formułę =ADR.POŚR(ADRES(MIN.K(JEŻELI($J$2:$J$37<>"";WIERSZ($J$2:$J$37);"");WIERSZ(1:1));10)) i zatwierdzamy tablicowo. Formuła sprawdza czy w zakresie $J$2:$J$37 występuje wartość różna od pustej i jeżeli jest to zwraca nr wiersza, w którym znajduje się nie pusta komórka. Następnie z pomocą funkcji ADRES tworzy odwołanie do komórki w postaci adresu kolumny, który wpisujemy "z ręki" oraz nr wiersza utworzonego dzięki funkcji MIN.K. Na koniec całość obejmuje funkcja ADRES.POŚREDNI zwracająca adres wskazany przez wartość tekstową.

 

Lista bez pustych pól, formuła tablicowa

 

Tutaj można pobrać plik z przykładami omówionymi powyżej oraz Dashboard Projektów.

 

Kilka słów o Dashboardzie Projektów:

-pulpit pozwala na monitorowanie 100 projektów

- nowe zadania wprowadzamy do arkusza Zadania projektowe (zakres danych z zadaniami sformatowany jest jako tabela - nie trzeba przepisywać formuł w zakresie)

- usuwamy poszczególne zadania usuwając cały wiersz

- poszczególne zadania można analizować i sortować pod kątem długości trwania, % wykonania zadania oraz % wykorzystania budżetu

- dashboard informuje o sumie pozostałego do wykorzystania budżetu, % realizacji całego projektu oraz maksymalnych 3 opóźnieniach poszczególnych zadań

- dashboard umożliwia przegląd projektów w widokach: dzienny,tygodniowy i miesięczny

- pulpit można łatwo wydrukować dopasowując go do jednej strony ekranu

 

Dashboard Projektów jest ciekawym rozwiązaniem pozwalającym efektywnie kontrolować zaplanowane zadania. Jednak jego funkcjonalności wciąż można rozbudowywać:

- na pulpicie można kontrolować kilka projektów a nie tylko jeden

- zamieszczone mogą zostać dodatkowe informacje o projektach/zadaniach np. ilość osób w projektach, ważne daty, ryzyka związane z projektem

- pulpit można wzbogacić o wykresy przydatne do kontroli projektów np. wykresy burn down, najważniejsze wydarzenia projektu na osi czasu, wykresy pociskowe do kontroli wykonanego budżetu

 

Jeżeli interesuje Cię współpraca przy tworzeniu tego lub innych dashboardów napisz pod kontakt@excelraport.pl

Dodaj komentarz

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