Jak skonfigurować formatowanie warunkowe w programie Excel dla dat

Formatowanie warunkowe w programie Excel można uznać za cenne narzędzie, które może pomóc wizualnie ulepszyć analizę i prezentację danych w arkuszu kalkulacyjnym poprzez zastosowanie określonego formatowania w oparciu o wcześniej zdefiniowane reguły lub warunki. W tym poście dowiesz się, jak skonfigurować formatowanie warunkowe w programie Excel dla dat. Zamieściliśmy także szczegółowe przykłady, które możesz wypróbować podczas czytania przewodnika.

Jak skonfigurować formatowanie warunkowe w programie Excel dla dat

Formatowanie warunkowe nie tylko pomaga wyróżnić komórki za pomocą kodów kolorów, ale także formatuje wiersze, kolumny i komórki składające się z dat w sposób, który można uznać za znaczący i atrakcyjny wizualnie.

1. Wstępnie zdefiniowane
2. Określony przez użytkownika
3. Utwórz niestandardową regułę formatowania
4. Zaznaczanie świąt w programie Excel
5. Warunkowe formatowanie dat na podstawie wielu warunków
6. Podkreślenie nadchodzących dat i opóźnień

Pamiętaj, aby dokładnie sprawdzić szczegóły w Excelu. Oferuje wiele opcji, z których niektóre mogą wykraczać poza zakres tego przewodnika.

1] Predefiniowane zasady formatowania warunkowego daty z datami

Formatowanie warunkowe z datami może być przydatne w przypadku zestawów danych zawierających informacje związane z datami. Excel udostępnia prawie dziesięć różnych opcji formatowania komórek na podstawie dat, jak pokazano poniżej:

• Aby sformatować komórkę z datą jako typem danych, kliknij Strona główna > Formatowanie warunkowe > Zaznacz reguły komórek >Data wystąpienia

• Opcje formatowania warunkowego można wybrać z menu rozwijanych w następnym oknie. Pierwsza lista rozwijana pozwala wybrać zakres dni, natomiast opcje formatowania można wybrać z drugiej.

• Kliknij OK, aby zastosować wybrane reguły formatowania.

2] Zdefiniowane przez użytkownika reguły formatowania warunkowego daty z datami

Oprócz zestawu wstępnie zdefiniowanych reguł, Excel udostępnia również opcję dostosowywania tych reguł w oparciu o wymagania użytkownika. Istnieje wiele sposobów ustawiania dostosowań, jak pokazano poniżej:

Korzystanie z formatu niestandardowego

1. Otwórz okno formatu Data występująca, wykonując czynności wymienione powyżej.
2. Po wybraniu zakresu dat wybierz Format niestandardowy z menu rozwijanego opcji formatowania.
3. Z powodu powyższego pojawi się okno Formatuj komórki, w którym możemy wybrać kolor, obramowanie, czcionkę i efekty naszego dokonaj wyboru, a następnie kliknij OK, aby zastosować zmiany.

Utwórz nowe reguły

Metoda ta polega na utworzeniu zupełnie nowej reguły formatowania warunkowego, w odróżnieniu od powyższej metody, w której możemy wybrać jedną z kilku predefiniowanych reguł i stworzyć jedynie własny format. Utworzenie nowej reguły polega na:

• Wybór komórek, dla których ma zostać zastosowane formatowanie.
• Na karcie Strona główna kliknij Formatowanie warunkowe > Nowa reguła, co powinno otworzyć okno Nowa reguła formatowania.

• W tym oknie możemy wybierać spośród dostępnych Typów reguł, a następnie edytować opis reguły, aby ustawić kryteria dla warunkowego formatowanie.
• Po zakończeniu można kliknąć przycisk Format w prawym dolnym rogu okna, aby wybrać formatowanie dla wspomnianej reguły. a>
• W otwartym oknie Formatuj komórki możemy wybrać czcionkę, obramowanie, kolor komórki, efekty itp. zgodnie z dokonaj wyboru i kliknij raz OK w tym oknie, a następnie wyświetli się okno Nowa reguła formatowania, w którym zmiany mają zostać zastosowane.

3] Jak utworzyć niestandardową regułę formatowania z datami

W powyższym przykładzie omówiliśmy etapy tworzenia nowej reguły formatowania warunkowego w oparciu o już podane kryteria. Excel umożliwia także zastosowanie wspomnianych reguł formatowania według kryteriów zdefiniowanych przez użytkownika.

Załóżmy na przykład, że musimy wyróżnić komórki z datami starszymi niż 30 lub 60 dni. W takim przypadku to samo można również zrealizować, stosując formuły dat i funkcje dostępne w Excelu, jak pokazano poniżej:

A] Podświetl daty bieżące i starsze

W obszarze Wybierz nową regułę w opcji Formatowanie warunkowe wybierz opcję Użyj formuły, aby określić, która komórkę do sformatowania, wpisz:

=$H4=TODAY() aby podkreślić dzień dzisiejszy

=$H4>TODAY() aby wyróżnić przyszłe dni

=$H4<TODAY() aby wyróżnić poprzednie dni

=$H4<=(TODAY()-30), aby wyróżnić daty starsze niż 30 dni.

=$H4<=(TODAY()-60), aby wyróżnić daty starsze niż 60 dni.

Powyższa formuła sprawdza wybrany zakres dat dla bieżącej daty i po znalezieniu dopasowania zmienia kolor tła na Fioletowy wybrany w opcji Format. $ użyte obok H oznacza, że ​​kolumna pozostanie stała, ale wiersze będą zmienne.

• Po wspomnieniu Opisu reguły można wybrać czcionkę, kolory i efekty, klikając przycisk Format.
• Kliknij OK w oknie Formatuj komórki, a następnie kliknij tę samą opcję w oknie Nowa reguła, aby zmiany zaczęły obowiązywać.< /span>

B] Podświetlanie na podstawie zakresu dat zdefiniowanego przez użytkownika

Podobne metody można zastosować do formatowania zakresów dat określonych przez użytkowników. Na przykład, jeśli chcemy wyróżnić daty starsze niż 30 dni, ale krótsze niż 45 dni, funkcji DZIŚ() można użyć w połączeniu z operatorem logicznym, takim jak AND, aby określić kryteria zawierające dwa warunki:

• Wybierz komórki, dla których chcesz zastosować formatowanie i otwórz okno Nowa reguła.
• W polu Edytuj opis reguły wpisz poniższą formułę,

=AND(H2>=(TODAY()-45),H2<=(TODAY()-30))

W powyższej formule zastosowano operator AND do podświetlenia na żółto dat starszych niż 30 dni, ale krótszych niż 45 dni w przypadku cały wybór, zaczynając od komórki nr. H2.

C] Zaznaczanie weekendów

W tym samym zakresie, korzystając ze wzoru WEEKDAY(), można wyróżnić także daty wypadające w weekendy:

• Wybierz komórki, dla których chcesz zastosować formatowanie i otwórz okno Nowa reguła.
• W polu Edytuj opis reguły wpisz poniższą formułę,

=WEEKDAY($H2, 2)>5

Składnię formuły dnia tygodnia można przedstawić jako DZIEŃ.TYG(numer_seryjny, [typ_powrotu]), gdzie numer_seryjny reprezentuje numer komórki data, którą próbujemy sprawdzić (w tym przypadku SA1).

Parametr return_types reprezentuje typ tygodnia rozpoczynającego się od poniedziałku(1) do niedzieli(7), biorąc pod uwagę dzień początkowy tygodnia jako poniedziałek (reprezentowany przez 1), a ostatni dzień tygodnia jako niedziela (reprezentowany przez 7).

Jednakże typy_zwrotów są w formule opcjonalne i są przedstawione w trzecich nawiasach. >5 służy do podświetlania soboty(6) i niedzieli(7) dla określonego typu zwrotu.

D] Podświetlanie wierszy na podstawie określonej daty

Ta opcja może być przydatna w przypadku dużych ilości danych, w których należy wyróżnić konkretną datę. To samo można osiągnąć ustalając wartość liczbową daty lub korzystając z funkcji DATEVALUE(). Aby zastosować formatowanie warunkowe dla określonej daty, należy najpierw określić wartość liczbową, ponieważ program Excel trwale przechowuje daty jako kolejne numery kolejne. Aby znaleźć wartość liczbową określonej daty,

• Kliknij prawym przyciskiem myszy komórkę z datą, kliknij Formatuj komórki >Liczba, a następnie wybierz Ogólne. W komórce zostanie wyświetlona 5-cyfrowa wartość liczbowa, którą można zapisać do wykorzystania w przyszłości.

• Po zanotowaniu numeru kliknij przycisk Anuluj, ponieważ nie musimy zmieniać formatu daty w komórce.
• Kliknij Formatowanie warunkowe>Nowa reguła> Wybierz nową regułę> Użyj formuły, aby określić, którą komórkę należy sformatować i wpisz następującą formułę,

=$G5=45249

• Korzystając z funkcji DATEVALUE() to samo można zrobić wpisując poniższą formułę,

=$G5=DATEVALUE("11/19/2023")

Znak $ użyty obok C oznacza, że ​​kolumna pozostanie stała, ale wiersze będą zmienne.

4] Podświetlanie świąt w Excelu

Ponieważ święta mogą się różnić w zależności od grupy demograficznej, w programie Excel nie ma żadnych wbudowanych funkcji wyróżniających święta państwowe. Aby więc zidentyfikować święta, musimy zapisać daty w innej kolumnie i zastosować formułę countif, aby sprawdzić występowanie dat świątecznych w arkuszu danych.

Na przykład mamy kilka dat wymienionych w kolumnie D i listę świąt w kolumnie G. Dzięki formatowaniu warunkowemu możemy wyróżnić święta na czerwono w kolumnie D, jeśli takie istnieją, porównując je z datami świąt wymienionymi w kolumnie G korzystając z poniższych kroków:

• Otwórz okno dialogowe Nowa reguła i wybierz Użyj formuły, aby określić, które komórki należy sformatować /span>Wybierz typ reguły.w obszarze
• Wprowadź formułę =COUNTIF($G$3:$G$5,$D3)
• Kliknij Formatuj > Wypełnij i wybierz kolor CZERWONY z palety i kliknij OK< /span>.

The countif() sprawdza występowanie dat wymienionych w komórkach G3 – G5 z datami wymienionymi w kolumnie D, zaczynając od komórki D3 .

5] Warunkowe formatowanie dat w oparciu o wiele warunków

Załóżmy, że mamy arkusz kalkulacyjny, w którym zgłoszono skargi dotyczące różnych artykułów gospodarstwa domowego. Arkusz zawiera daty złożenia reklamacji oraz terminy płatności i zakończenia, jeśli ma to zastosowanie.

Stosując formatowanie warunkowe, oznaczmy reklamacje na BIAŁO dla tych, które zostały ZAMKNIĘTE, CZERWONE dla tych, które minęły, ale jeszcze nie zostały zamknięte, i NIEBIESKO dla tych, które jeszcze nie osiągnęły terminu.

Proces ten obejmowałby utworzenie dwóch reguł formatowania warunkowego dla tych samych danych, jednej dla tych, które nie osiągnęły jeszcze terminu, a drugiej dla tych, które przekroczyły termin. Aby to zrobić,

• Otwórz okno Nowa reguła formatowania warunkowego i wybierz Użyj formuły, aby określić, które komórki mają zostać sformatowane pod Wybierz typ reguły.
• Wprowadź poniższą formułę

=AND($I10="",$H10>$K$6)

• Kliknij Formatuj, wybierz z palety NIEBIESKI kolor i kliknij OK.
• Utwórz kolejną Nową regułę z tym samym typem reguły i wprowadź poniższą formułę

=AND($I10="",$H10<$K$6)

• Kliknij Formatuj, wybierz z palety kolor CZERWONY i kliknij OK.

W tym wzorze najpierw sprawdzamy, czy kolumna Data zamknięcia jest PUSTA, czy też nie, aby ustalić, czy reklamacja jest nadal otwarta ORAZ, a następnie sprawdzamy jeśli DATA WYMAGANIA jest większa niż bieżąca data lub mniejsza, aby określić, czy termin zbliża się ($H10>$K$6) czy też został przekroczony ($ H10<6 K$). Data w komórce K6 reprezentuje bieżącą datę.

6] Podkreślenie nadchodzących dat i opóźnień

Aby zrozumieć, w jaki sposób możemy wyróżnić opóźnienia w odniesieniu do dat w arkuszu Excel, rozważmy przykład, w którym mamy listę zapasów z ich nazwami i datami ważności. Poprzez formatowanie warunkowe, jeśli musimy zaznaczyć te, które mają wygasnąć na ZIELONYM, a te, które już wygasły na CZERWONYM,

• Wybierz zakres danych i otwórz okno dialogowe Nowa reguła.
• Wybierz Formatuj tylko komórki zawierające w obszarze Wybierz typ reguły
• W Edytuj opis reguły wybierz Wartość komórki, Większy niż, =Now()+30 w odpowiednich menu rozwijanych.
• Kliknij Formatuj, wybierz ZIELONY z palety kolorów, i kliknij OK.

• Ponownie otwórz Nową regułę i wybierz ten sam Typ reguły, < a i=4>jak wyżej.
• W Edytuj opis reguły wybierz Wartość komórki, Mniej niż, =Teraz() w odpowiednich menu rozwijanych. a>
• Kliknij Format i wybierz CZERWONY z koloru paletę i kliknij OK.

Wniosek

Celem tego samouczka jest kompleksowe omówienie opcji i funkcji, których można użyć do stosowania formatowania warunkowego w arkuszach programu Excel na podstawie dat. Może pomóc zrozumieć, jak zastosować reguły formatowania w celu usprawnienia wizualizacji i analizy danych. Opanowanie formatowania warunkowego może również pomóc w identyfikowaniu trendów, sprawdzaniu terminów i ustalaniu priorytetów zadań, pomagając w podejmowaniu świadomych decyzji.

Jak utworzyć formatowanie warunkowe w programie Excel na podstawie dat?

Najpierw wybierz komórki daty, aby utworzyć formatowanie warunkowe w programie Excel na podstawie dat. Kliknij „Strona główna” > „Formatowanie warunkowe” > 'Nowa zasada’. Wybierz „Formatuj komórki zawierające”, wybierz „Daty występujące” w obszarze reguły, wybierz konkretne kryteria daty, a następnie ustaw preferowany format, klikając „Formatuj”. Zastosuj, naciskając „OK”. Spowoduje to szybkie wyróżnienie kluczowych dat w danych.

Jak warunkowo sformatować terminy w programie Excel?

Najpierw wybierz komórki z datami, aby warunkowo sformatować terminy w programie Excel. Następnie kliknij „Formatowanie warunkowe” na karcie „Strona główna”. Wybierz „Nowa reguła”, wybierz „Formatuj tylko komórki zawierające”, ustaw regułę na „Wartość komórki” i „Mniej niż” i wprowadź termin lub formułę. Kliknij „Format”, wybierz styl formatowania i naciśnij „OK”, aby zastosować.