Jak utworzyć dynamicznie zmieniający się kalendarz w programie Excel

w tym artykule pokażemy, jak utworzyć dynamicznie zmieniający się kalendarz w programie Excel.

Pobierz skoroszyt

wypełniaj zmienne

pierwszym krokiem jest utworzenie zmiennych do użycia w formułach. Zmienne te muszą dostarczać informacje o dniu bieżącym, aby kalendarz mógł być aktualizowany automatycznie. Oto lista zmiennych:

• obecna Data
• liczba obecnego dnia miesiąca
• liczba obecnego miesiąca
• obecny rok
• Data pierwszego dnia bieżącego miesiąca
• liczba obecnego dnia tygodnia
• numer obecnego tygodnia w roku

będziemy używać funkcji dzisiaj, Dzień, Miesiąc, Rok, data, dzień tygodnia i tydzień. W skrócie, podczas gdy funkcja TODAY zwraca datę dnia bieżącego, funkcja DAY, MONTH, YEAR, WEEKDAY i WEEKNUM analizuje odpowiednie wartości daty. Funkcja DATE Zwraca wartość daty podanego roku, miesiąca i dnia.

na poniższym zrzucie ekranu możesz zobaczyć podane przez nas nazwy i formuły wygenerowane 20 listopada 2020 r.

nazwaliśmy każdą zmienną, aby ułatwić nam pracę podczas tworzenia formuł. Następnym krokiem do utworzenia kalendarza w programie Excel jest utworzenie konspektu interfejsu kalendarza.

zarys kalendarza w programie Excel

przed utworzeniem rzeczywistych formuł, które generują dni miesiąca, najpierw musimy umieścić zarys, aby pomóc w tworzeniu formuł, a także zapewnić odniesienie wizualne.

dni zostaną umieszczone na tabeli 7 kolumn i 6 wierszy. Musimy zwiększyć te liczby o 2 dla tytułów i komórek pomocniczych. Podczas gdy tytuły kolumn i wierszy zawierają dni tygodnia dla kolumn i numery tygodni według wierszy, komórki pomocnicze będą kolejnymi liczbami zaczynającymi się od 1.

kalendarz może wyglądać jak poniżej.

użyj ramek lub kolorów tła, jak chcesz. Ważnym punktem jest użycie dynamicznego numeru tygodnia w tytule. Odwołaj się do funkcji WEEKNUM dla pierwszego tygodnia i dodaj 1 dla każdego kolejnego tygodnia.

kontynuujmy tworzenie kalendarza w programie Excel, wypełniając liczby dni.

generowanie dni

jeśli używasz podobnego układu i nie chcesz się martwić o szczegóły formuły, skopiuj poniższą formułę. Następnie wklej go do lewej górnej komórki w obszarze dnia kalendarza i wypełnij pozostałe komórki. Komórki G2 i E4 odnoszą się do pierwszych komórek kolumn pomocniczych. Dlatego może być konieczne dostosowanie tych odniesień, chyba że pierwszą komórką jest g4.

teraz przyjrzyjmy się bliżej formule. Po pierwsze, formuła wykorzystuje funkcję DATE, która zwraca datę według podanego roku, miesiąca i dnia. Pierwsze 2 argumenty są dostarczane przez zmienne YearNumber i MonthNumber, które zostały już obliczone.

część dzienna formuły ma na celu generowanie liczb dziennych na bazie 7, ponieważ w tygodniu jest 7 dni. 7*($E4-1) część formuły sprawdza numer tygodnia w kolumnie pomocniczej E i generuje bazę, zaczynając od 0 i zwiększając o 7 za każdym razem.

możemy zwiększyć lub zmniejszyć tę liczbę o tyle, o ile różnica między dniem tygodnia pierwszego dnia miesiąca o liczbę dni tygodnia. Innymi słowy, otrzymujemy pierwszy dzień obecnego miesiąca jako punkt zaczepienia. Na przykład jest to siódmy dzień tygodnia (w bazie Od Poniedziałku do niedzieli) dla 1 listopada 2020 r.

Jeśli tydzień naszego kalendarza zakłada, że poniedziałek jest pierwszym dniem tygodnia, poniedziałek powinien być 6 dni przed (=1-7) pierwszym dniem miesiąca. W ten sposób otrzymujemy liczby takie jak poniżej.

dodaj te wartości do 1, aby przesunąć liczby. Tak więc niedziela może stać się pierwszym dniem, jak w poniższym przykładzie.

w rezultacie mamy 2 tablice dla 2 wymiarów. Aby utworzyć tabelę, musimy dodać obie tablice razem.

liczby te reprezentują dzień obecnego miesiąca. Istnieją liczby mniejsze niż 1 lub większe niż 31, które oczywiście nie mogą być dniami miesiąca.

funkcja DATE obsługuje te typy liczb, generując datę w poprzednim miesiącu lub następnym. Na przykład, -2 staje się dwa dni przed pierwszym dniem danego miesiąca.

wreszcie, jeśli użyjesz tych liczb w funkcji daty, możesz uzyskać rzeczywiste daty bieżącego miesiąca.

formatowanie

liczby dni

teraz wypełnimy dni. Można zmieniać formatowanie wartości bez zmiany rzeczywistej wartości. Ta funkcja nazywa się Formatowanie liczb. Postępuj zgodnie z instrukcjami, aby dostosować Formatowanie liczb tak, aby pokazywało tylko dni.

1. Wybierz dowolne daty w obszarze dnia
2. naciśnij Ctrl + 1, aby otworzyć okno dialogowe Formatuj komórki
3. aktywuj kartę Numer, jeśli nie jest już otwarta
4. Wybierz niestandardowe na liście kategorii
5. wpisz literę d w polu Typ
6. kliknij OK, aby zapisać
   

teraz układ kalendarza jest gotowy.

jeśli chcesz usunąć wypełnienie dla dat jednocyfrowych, możesz użyć dd zamiast pojedynczego D. Aby dowiedzieć się więcej o formatowaniu liczb: Formatowanie liczb w programie Excel – wszystko, co musisz wiedzieć

podświetlanie dnia dzisiejszego

wreszcie będziemy używać formatowania warunkowego, aby ustawić różne kolory dla dni, które nie są w bieżącym miesiącu, i wyróżnić dzień dzisiejszy.

aby dodać te opcje formatowania warunkowego;

1. Wybierz zakres dnia
2. kliknij ikonę formatowania warunkowego na karcie Strona główna wstążki
3. wybierz nową regułę
   
4. Wybierz Użyj formuły, aby określić, które komórki mają formatować
5. wprowadź formułę, aby zdefiniować regułę podświetlania bieżącego dnia
   =G4=TODAY()
   G4: lewa górna komórka zakresu zawiera dni. Użyj odniesienia względnego (nie używaj $).
6. kliknij przycisk Formatuj w oknie podglądu okna dialogowego Formatuj komórki
   
7. w oknie dialogowym wybierz opcje formatowania, które chcesz zobaczyć w komórce dnia dzisiejszego
   w tym przykładzie użyliśmy pomarańczowego tła.
8. kliknij OK, aby zastosować
   
9. okno podglądu wyświetli Twoje preferencje
10. kliknij OK, aby zastosować formatowanie warunkowe
    

tak to wygląda:

formuła zwraca wartość logiczną. Jeśli wartość komórki (G4) jest równa wyniku funkcji TODAY, która zwraca dzień dzisiejszy w formacie daty, formuła zwraca TRUE. Inaczej, fałsz. Jeśli wynik jest prawdziwy, program Excel zastosuje formatowanie do komórki.

tu są 2 ważne punkty:

1. musisz użyć odniesienia względnego, np. G4, chyba że nie chcesz, aby Excel wypełniał odniesienie wzdłuż zakresu. Zastanów się, jak zmieniają się formuły, gdy kopiujesz je gdzie indziej. Program Excel stosuje to samo również do formatowania warunkowego. Aby uzyskać więcej informacji, zobacz: Jak utworzyć bezwzględne odniesienie do programu Excel i względne odniesienie
2. komórki dnia muszą zwracać rzeczywiste wartości daty. Dlatego używamy formatowania liczb, aby pokazać porcje dnia.