Narzędzia menedżera – status zadań w arkuszu Excel


Na rynku dostępnych jest cała gama narzędzi pozwalających definiować listę zadań, wprowadzać daty wykonania, śledzić statusy. W podstawowych przypadkach doskonale sprawdzają się jednak proste rozwiązania, do których można zaliczyć pakiet MS Office. W tym artykule pokażę Ci, jak w kilku krokach zamieniłem arkusz Excel w wykres obrazujący postęp poszczególnych zadań.

Kilka lat temu potrzebowałem przygotować narzędzie, które pozwoliłoby na gromadzenie danych z różnych zespołów i umożliwiałoby wysokopoziomową kontrolę nad projektem. Poza standardowymi kolumnami do wpisywania danych, istotna była również ich graficzna prezentacja. Dzięki temu możliwe było szybkie przetworzenie dużej ilości wpisów i skupienie się tylko na tych, które faktycznie wymagały interwencji. W kilku następnych krokach pokażę Ci, jak uzyskałem taki efekt.

Zdefiniuj podstawowe kolumny

Otwórz nowy plik i wprowadź nazwy kolumn. Możesz oczywiście dodać pola jakie tylko chcesz, jak na przykład typ zadania, osoba przypisana lub link do innego systemu. Dużo łatwiej będzie Ci wykonać to ćwiczenie, jeżeli zachowasz taką samą jak ja numerację następujących kolumn:

  • E – Start Date
  • F – Original Due Date
  • H – Date Delivered
  • I – Status
  • Od M – kolejne tygodnie (o tym za chwilę)

Kompletna lista kolumn w moim pliku jest następująca

Dodaj walidacje, wyliczenia i formatowania

W pliku wprowadziłem kilka formuł i innych usprawnień. Część z nich jest obowiązkowa do prawidłowego działania, część stanowi tylko dodatkową wartość.

  • Duration (G) – dodałem formułę wyliczającą czas trwania, lub wstawiającą „NA”, jeżeli to nie jest możliwe.
    =JEŻELI((NIE(CZY.TEKST(F4))*ORAZ(F4>0));F4-E4;"NA")
  • Delivery Date (H) – tutaj dodałem oznaczenie, które przypomni o uzupełnieniu tej daty, gdy zadanie jest zakończone. W tym celu, w kolumnie H – Narzędzia główne – Formatowanie warunkowe i Nowa reguła.

    Zastosowana formuła:
    =($I4="Complete")*ORAZ($H4="")
    ustawia tło pola na czerwono, jeżeli status jest „Complete” a data zakończenia pusta.
  • Status (I) – ponieważ w oparciu o wartości w tej kolumnie będzie budowany wykres, dlatego ważne jest zablokowanie możliwości wpisywania tutaj dowolnego tekstu. Utworzyłem więc dodatkową zakładkę (Dict), gdzie od A1 do A4 wprowadziłem wartości: Incomplete, In Progress, Complete, On Hold. Następnie, w kolumnie I (zaczynając od I4) wybrałem zakładkę Dane – Poprawność Danych – Poprawność Danych, po czym ustawiłem tam:

  • Today (J) – wartość w tej kolumnie pozwala wyliczać opóźnienie/wyprzedzenie w realizacji zadań. Zastosowałem tam prostą formułę:
    =DZIŚ()
  • Days Late (K) – wstawiłem formułę, która dla nieukończonych zadań wyliczała wartość na podstawie dzisiejszej daty, natomiast dla ukończonych prac bazowała na wprowadzonej przez użytkownika dacie zakończenia.
    =JEŻELI(CZY.TEKST(H4);"NA";JEŻELI(CZY.TEKST(F4);"NA";(JEŻELI(F4*H4>0;F4-H4;(JEŻELI(F4>0;F4-J4;"NA"))))))
    Dodatkowo wprowadziłem formatowanie warunkowe, jak w przypadku Delivery Date (H).

Dodaj wykres postępu prac

Pora zając się właściwym wykresem, czyli graficznym obrazem postępu prac.
W tym celu, zaczynając od M2, wprowadź datę początkową. Następnie, w kolejnych kolumnach (M…) dodaj okresy z zakładaną częstotliwością (np. tygodniowo, wtedy =M2+7). Taką formułę „przeciągnij” odpowiednio daleko.
W wierszu poniżej (3) dodałem jeszcze numerację tygodni. Nie jest to obowiązkowe, tylko ułatwia przegląd wykresu.

Teraz najważniejsze – postaw kursor w M4 i wybierz ponownie Formatowanie warunkowe – Zarządzaj regułami. Dodaj kolejno reguły:

  • Ustawianie wskaźnika bieżącego dnia.
    =JEŻELI(M$2<=DZIŚ();JEŻELI(N$2>=DZIŚ();PRAWDA;FAŁSZ);FAŁSZ)

    Jako Formatowanie, użyj obramowania tylko z prawej strony. Jedynie dla tej reguły, opcja „Zatrzymaj gdy warunek jest prawdziwy” ma być odznaczona.
    Dla wszystkich reguł ustaw zakres obowiązywania zgodnie z oczekiwanym zakresem danych, np. =$M$4:$BE$144
  • Sprawdzenie czy data końca została prawidłowo ustawiona =(CZY.TEKST($F4)) (brak formatowania)
  • Usunięcie formatowania dla komórek sprzed daty rozpoczęcia =M$2<$E4 (brak formatowania)
  • Ustawienie ciemnoczerwonego paska dla zadań zakończonych po spodziewanym terminie =(M$2<$H4)*ORAZ($H4>$F4)*ORAZ(NIE(CZY.TEKST($H4)))*ORAZ($I4="Complete")*ORAZ(M$2>$E4) (możesz ustawić kolor jaki chcesz).
  • Ustawienie ciemnozielonego paska dla zadań zakończonych przed spodziewanym terminem =($I4="Complete")*ORAZ(M$2<$H4)*ORAZ(M$2>$E4) (j.w., ustaw dowolny kolor formatowania).
  • Ustawienie jasnoczerwonego paska dla zadań nieskończonych, które już są po terminie =(M$2<$F4)*ORAZ($F4<DZIŚ())*ORAZ($I4<>"Complete")*ORAZ(M$2>$E4)
  • Ustawienie jasnozielonego paska dla zadań nieskończonych, które jeszcze mają szansę zakończyć się przed terminem =(M$2<$F4)*ORAZ($I4 <>"Complete")*ORAZ(M$2>$E4)

Pobierz kompletny plik

Jeżeli chcesz otrzymać kompletny szablon, wprowadź poniżej adres email.

Name *

Email *

Przykłady

  • Zadanie niedokończone, po terminie.
  • Zadanie niedokończone, jeszcze przed terminem.
  • Zadanie skończone, po terminie
  • Zadanie skończone, przed terminem

Dodaj komentarz

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