Formuła programu Excel: dynamiczna siatka kalendarza -

Spisie treści

Podsumowanie

Możesz skonfigurować dynamiczną siatkę kalendarza w arkuszu programu Excel za pomocą serii formuł, jak wyjaśniono w tym artykule. W pokazanym przykładzie formuła w B6 to:

=start-CHOOSE(WEEKDAY(start),0,1,2,3,4,5,6)

gdzie „początek” to nazwany zakres K5 i zawiera datę 1 września 2018 r.

Wyjaśnienie

Uwaga: w tym przykładzie założono, że data rozpoczęcia będzie pierwszym dniem miesiąca. Poniżej znajdziesz formułę, która dynamicznie zwróci pierwszy dzień bieżącego miesiąca.

Przy takim układzie siatki jak pokazano, głównym problemem jest obliczenie daty w pierwszej komórce kalendarza (B6). Odbywa się to za pomocą tej formuły:

=start-CHOOSE(WEEKDAY(start),0,1,2,3,4,5,6)

Ta formuła oblicza niedzielę przed pierwszym dniem miesiąca, używając funkcji WYBIERZ w celu „cofnięcia” odpowiedniej liczby dni do poprzedniej niedzieli. WYBIERZ sprawdza się doskonale w tej sytuacji, ponieważ pozwala na dowolne wartości dla każdego dnia tygodnia. Używamy tej funkcji, aby cofnąć zero dni, gdy pierwszy dzień miesiąca przypada w niedzielę. Więcej szczegółów na temat tego problemu znajduje się tutaj.

Po ustaleniu pierwszego dnia w B6 inne formuły w siatce po prostu zwiększają poprzednią datę o jeden, zaczynając od formuły w C6:

=IF(B6"",B6,$H5)+1

Ta formuła sprawdza wartość w komórce po lewej stronie. Jeśli nie zostanie znaleziona żadna wartość, pobiera wartość z kolumny H w powyższym wierszu. Uwaga: $ H5 to odniesienie mieszane, aby zablokować kolumnę, gdy formuła jest kopiowana w całej siatce. Ta sama formuła jest używana we wszystkich komórkach z wyjątkiem B6.

Reguły formatowania warunkowego

W kalendarzu stosowane są formuły formatowania warunkowego, które zmieniają formatowanie w celu zacienienia poprzednich i przyszłych miesięcy oraz wyróżnienia bieżącego dnia. Obie zasady mają zastosowanie do całej siatki. Wzór na poprzednie i następne miesiące wygląda następująco:

=MONTH(B6)MONTH(start)

Na bieżący dzień formuła jest następująca:

=B6=TODAY()

Aby uzyskać więcej informacji, zobacz: Formatowanie warunkowe za pomocą formuł (10 przykładów)

Nagłówek kalendarza

Tytuł kalendarza - miesiąc i rok - są obliczane za pomocą tej formuły w komórce B4:

=start

Sformatowany przy użyciu niestandardowego formatu liczbowego „mmmm rrrr”. Aby wyśrodkować tytuł nad kalendarzem, zakres B4: H4 ma wyrównanie poziome ustawione na „środek w poprzek zaznaczenia”. Jest to lepsza opcja niż scalanie komórek, ponieważ nie zmienia struktury siatki w arkuszu.

Wieczny kalendarz z aktualną datą

Aby utworzyć kalendarz, który aktualizuje się automatycznie na podstawie aktualnej daty, możesz użyć takiej formuły w K5:

=EOMONTH(TODAY(),-1)+1

Ta formuła pobiera bieżącą datę za pomocą funkcji DZIŚ, a następnie pobiera pierwszy dzień bieżącego miesiąca za pomocą funkcji EOMONTH. Zastąp DZIŚ () dowolną datą, aby utworzyć kalendarz na inny miesiąc. Więcej szczegółów na temat działania EOMONTH tutaj.