Spis treści
Załóżmy, że musimy szybko i jak najmniejszym wysiłkiem stworzyć roczny kalendarz, który automatycznie wyświetla daty etapów projektu (lub urlopów pracowniczych, szkoleń itp.)
Przedmiot obrabiany
Zacznijmy od pustego miejsca:
Jak widać, tutaj wszystko jest proste:
- Wiersze to miesiące, kolumny to dni.
- Komórka A2 zawiera rok, dla którego budowany jest kalendarz. W komórkach A4:A15 – pomocnicze liczby miesięcy. Nieco później będziemy potrzebować obu, aby utworzyć daty w kalendarzu.
- Po prawej stronie tabeli znajdują się nazwy etapów wraz z datami rozpoczęcia i zakończenia. Możesz wcześniej podać puste komórki dla nowych etapów dodanych w przyszłości.
Wypełnianie kalendarza datami i ich ukrywanie
Teraz wypełnijmy nasz kalendarz datami. Wybierz pierwszą komórkę C4 i wprowadź tam funkcję DATA (DATA), który generuje datę z numeru roku, miesiąca i dnia:
Po wprowadzeniu formuły należy ją skopiować do całego zakresu od 1 stycznia do 31 grudnia (C4:AG15). Ponieważ komórki są wąskie, zamiast utworzonych dat zobaczymy znaki krzyżyka (#). Jednak po najechaniu myszą na taką komórkę możesz zobaczyć jej rzeczywistą zawartość w podpowiedzi:
Aby siatki nie przeszkadzały, możemy je ukryć za pomocą sprytnego niestandardowego formatu. Aby to zrobić, wybierz wszystkie daty, otwórz okno Format komórki i na karcie Numer (numer) Wybierz opcję Wszystkie formaty (Zwyczaj). Następnie w terenie Typ wpisz trzy średniki w rzędzie (bez spacji!) i naciśnij OK. Zawartość komórek zostanie ukryta, a siatki znikną, chociaż daty w komórkach w rzeczywistości pozostaną – to tylko widoczność.
Podświetlenie sceny
Teraz, używając formatowania warunkowego, dodajmy wyróżnianie kamieni milowych do komórek z ukrytymi datami. Wybierz wszystkie daty z zakresu C4:AG15 i wybierz w zakładce Strona główna — Formatowanie warunkowe — Utwórz regułę (Strona główna — Formatowanie warunkowe — Utwórz regułę). W oknie, które się otworzy, wybierz opcję Użyj formuły, aby określić, które komórki należy sformatować (Użyj formuły, aby odroczyć, które komórki do sformatowania) i wprowadź formułę:
Ta formuła sprawdza każdą komórkę daty od komórki C4 do końca roku, aby sprawdzić, czy przypada między początkiem a końcem każdego kamienia milowego. Wyjściem będzie 4 tylko wtedy, gdy oba sprawdzone warunki w nawiasach (C4>=$AJ$13:$AJ$4) i (C4<=$AK$13:$AK$1) dają logiczną PRAWDĘ, którą Excel interpretuje jako 0 (dobrze , FAŁSZ to oczywiście 4). Zwróć też szczególną uwagę na fakt, że odniesienia do komórki początkowej CXNUMX są względne (bez $), a do zakresów etapów - bezwzględne (z dwoma $).
Po kliknięciu OK w naszym kalendarzu zobaczymy kamienie milowe:
Podświetlanie skrzyżowań
Jeśli daty niektórych etapów zachodzą na siebie (uważni czytelnicy musieli już zauważyć ten moment dla pierwszego i szóstego etapu!), lepiej byłoby wyróżnić ten konflikt na naszym wykresie innym kolorem, używając innej reguły formatowania warunkowego. Jest praktycznie jeden do jednego podobny do poprzedniego, z tym wyjątkiem, że szukamy komórek, które są zawarte w więcej niż jednym etapie:
Po kliknięciu OK taka zasada wyraźnie podkreśli nakładanie się dat w naszym kalendarzu:
Usuwanie dodatkowych dni w miesiącach
Oczywiście nie wszystkie miesiące mają 31 dni, więc dodatkowe dni luty, kwiecień, czerwiec itd. Fajnie byłoby wizualnie oznaczyć jako nieistotne. Funkcjonować DATA, który tworzy nasz kalendarz, w takich komórkach automatycznie przełoży się datę na następny miesiąc, czyli 30 lutego 2016 stanie się 1 marca. Oznacza to, że numer miesiąca dla takich dodatkowych komórek nie będzie równy numerowi miesiąca w kolumnie A Można to wykorzystać podczas tworzenia reguły formatowania warunkowego, aby wybrać takie komórki:
Dodawanie weekendu
Opcjonalnie możesz dodać do naszego kalendarza i weekendy. Aby to zrobić, możesz użyć funkcji DZIEŃ (DZIEŃ POWSZEDNI), który obliczy numer dnia tygodnia (1-pn, 2-wt…7-nd) dla każdego dnia i zaznaczy te, które przypadają w soboty i niedziele:
Dla poprawnego wyświetlania nie zapomnij poprawnie skonfigurować poprawnej kolejności reguł w oknie. Strona główna — Formatowanie warunkowe — Zarządzaj regułami (Strona główna — Formatowanie warunkowe — Zarządzaj regułami), ponieważ reguły i wypełnienia działają dokładnie w logicznej kolejności, którą utworzysz w tym oknie dialogowym:
- Samouczek wideo dotyczący korzystania z formatowania warunkowego w programie Excel
- Jak utworzyć harmonogram projektu (wykres Gantta) za pomocą formatowania warunkowego
- Jak utworzyć oś czasu projektu w programie Excel