Te 7 formuł formatowania warunkowego zamienia Excel w zautomatyzowany system powiadomień

Wstępnie ustawione podświetlenia w Excelu działają w prostych przypadkach, ale szybko zawodzą, gdy dane stają się bardziej złożone. Formatowanie warunkowe oparte na formułach zamienia statyczne arkusze kalkulacyjne w automatyczne systemy powiadomień, które reagują natychmiast, gdy zmieniają się Twoje dane. To prosty sposób na wbudowanie logiki „jeżeli-to” bezpośrednio w komórki.

Opanuj przepływ pracy formatowania oparty na formułach

Ustaw i zapomnij o tym przy użyciu tabel Excela

Każda reguła formatowania warunkowego w tym przewodniku używa tych samych podstawowych kroków, więc gdy zrobisz to raz, zrobiłeś to już tysiąc razy:

1. Wybierz swoje komórki danych — zaczynając od komórki w lewym górnym rogu i nie uwzględniając wiersza nagłówka.
2. Przejdź do Strona główna > Formatowanie warunkowe > Nowa reguła.
3. Wybierz Użyj formuły, aby określić, które komórki sformatować.
4. Wprowadź swoją formułę w polu formuły.
5. Kliknij Format, aby wybrać swój styl.
6. Kliknij OK.

Dla najlepszych wyników sformatuj swoje dane jako tabelę w Excelu (Ctrl+T) przed zastosowaniem reguł. Podczas gdy formatowanie warunkowe używa standardowych odwołań do komórek, tabele automatycznie rozszerzają formatowanie po dodaniu nowych wierszy.

Podświetl każdą kolumnę w wierszu na podstawie statusu jednej komórki

Twórz czyste, poziome alerty

Wbudowane reguły formatowania warunkowego w Excelu zazwyczaj kolorują tylko konkretną komórkę, która spełnia warunek. Działa to, ale często sprawia, że arkusz wygląda jak chaotyczna szachownica, którą trudno odczytać. Jeśli chcesz mieć czysty, czytelny układ, lepiej użyć niestandardowej formuły, aby podświetlić cały wiersz zawsze, gdy zmieni się pojedynczy status.

Scenariusz: Chcesz, aby cały wiersz tabeli zmienił kolor na żółty w momencie, gdy zmienisz status w kolumnie E na „Zakończono.”

Aby to zrobić, zaznacz cały zakres danych i utwórz regułę formatowania warunkowego przy użyciu tego wzoru:

=$E2="Complete"

Znak dolara blokuje regułę w kolumnie E, podczas gdy odwołanie do wiersza pozostaje względne, dzięki czemu każdy wiersz jest oceniany niezależnie.

Porównaj wartości między dwoma kolumnami, aby zidentyfikować rozbieżności

Śledź przekroczenia budżetu automatycznie

Standardowe zasady formatowania działają najlepiej z ustalonymi liczbami, ale w prawdziwym życiu rzadko jest tak prosto. W narzędziu do śledzenia projektów Twój budżet może się różnić dla każdej pojedynczej pozycji, więc ręczne sprawdzanie, czy nie przekroczyłeś wydatków, jest uciążliwością, której nie potrzebujesz.

Scenariusz: Musisz oznaczyć na różowo każdy projekt, w którym rzeczywiste wydatki (kolumna D) przekraczają budżet (kolumna C).

Oto formuła, której potrzebujesz:

=$D2>$C2

Ta formuła porównuje bezpośrednio każdy wiersz, oznaczając przypadki, w których rzeczywiste wydatki przekraczają budżet. Jeśli któraś z wartości zmieni się później, formatowanie aktualizuje się automatycznie.

Zidentyfikuj brakujące lub niekompletne informacje w zakresie danych

Utrzymuj całkowitą integralność danych

Wszyscy tam byliśmy — patrzysz na raport, tylko po to, by zorientować się, że ktoś zapomniał wpisać kluczową nazwę leada lub termin. Puste komórki zabijają formuły, więc zamiast szukać ich samodzielnie, pozwól Excelowi samemu je wypatrzyć.

Scenariusz: Chcesz wyróżnić każdy wiersz projektu zawierający brakujące informacje.

Oto magiczna formuła:

=COUNTBLANK($A2:$F2)>0

Funkcja COUNTBLANK zlicza liczbę pustych komórek w zakresie wiersza. Jeśli wynik jest większy niż zero, reguła zostaje uruchomiona.

Połącz wiele warunków w jedną zasadę

Odfiltruj szumy wizualne

Czasami jeden warunek nie wystarcza. Możesz interesować się tylko projektami, które są zarówno w toku, jak i przekraczają ustalony próg wydatków.

Scenariusz: Chcesz podświetlić na szaro tylko te wiersze, które są w trakcie realizacji (kolumna E) i wydały ponad 5 000 $(kolumna D).

To tutaj funkcja AND wchodzi w grę w twojej regule formatowania warunkowego:

=AND($E2="In Progress",$D2>5000)

To zapewnia, że reguła uruchamia się tylko wtedy, gdy obie warunki są prawdziwe w tym samym czasie, co pomaga zmniejszyć wizualny chaos w arkuszu.

Użyj dedykowanej komórki odniesienia do wyszukiwania słów kluczowych

Zbuduj pasek wyszukiwania na żywo

Excel ma wbudowane narzędzie wyszukiwania, ale powrót do menu za każdym razem, gdy chcesz znaleźć nowe słowo kluczowe, jest uciążliwy. Znacznie lepszym sposobem jest powiązanie formatowania z konkretną komórką, aby móc wyszukiwać w locie.

Scenariusz: Chcesz wpisać słowo kluczowe (takie jak „Audit”) w komórce H2 i sprawić, aby wiersz dla każdej pasującej nazwy projektu w kolumnie A natychmiast podświetlił się na zielono.

Tym razem, dla twojej formuły formatowania warunkowego, musisz połączyć ISNUMBER i SEARCH:

=ISNUMBER(SEARCH($H$2,$A2))

Funkcja SEARCH (niezależna od wielkości liter) szuka słowa kluczowego w komórce H2 w tekście w A2. Jeśli znajdzie dopasowanie, zwraca liczbę; w przeciwnym razie zwraca błąd. Funkcja ISNUMBER zamienia ten wynik na TRUE lub FALSE dla formatowania warunkowego.

Teraz po prostu wpisz inne słowo kluczowe w komórce H2 i zobacz, jak formatowanie zostaje odpowiednio zaktualizowane.

Obliczanie przypomnień cyklicznych na podstawie bieżącej daty

Śledź terminy w czasie rzeczywistym

Statyczne reguły dat szybko stają się nieaktualne. Aby terminy były użyteczne, potrzebujesz reguły, która aktualizuje się automatycznie i podświetla tylko ten przedział czasowy, który naprawdę Cię interesuje.

Scenariusz: Chcesz podświetlić na pomarańczowo każdy projekt, którego termin w kolumnie F przypada w ciągu najbliższych siedmiu dni — bez oznaczania zaległych pozycji. W tym przykładzie załóżmy, że dzisiejsza data to 2026-05-11.

Aby to zrobić prawidłowo, potrzebny jest zakres dat, a nie pojedyncze porównanie. Użyj tej formuły w regule formatowania warunkowego:

=AND($F2>=TODAY(),$F2<=TODAY()+7)

To sprawdza dwa warunki jednocześnie:

• Data nie jest przeszła ($F2>=DZIŚ())
• Data przypada w ciągu najbliższych 7 dni ($F2<=DZISIAJ()+7)

Formatowanie zostanie wywołane tylko wtedy, gdy oba warunki będą prawdziwe.

Ma to znaczenie, ponieważ reguła z jednym warunkiem, taka jak =$F 2<=TODAY()+7, również będzie obejmować zaległe zadania. Dodając dolną granicę, zapewniasz, że podświetlenie zachowuje się jak właściwe okno "nadchodzący termin", a nie jak filtr obejmujący wszystko.

Dopasuj wiele pól danych, aby znaleźć złożone duplikaty

Odkryj ukryte podwójne wpisy

Proste reguły duplikatów często oznaczają rzeczy, które w rzeczywistości nie są błędami. Możesz mieć tę samą nazwę przedmiotu pojawiającą się wielokrotnie w sposób uzasadniony, ale jeśli pojawia się dwukrotnie z dokładnie tymi samymi dodatkowymi szczegółami, prawdopodobnie znalazłeś błąd administracyjny. Możesz wykryć te duplikaty relacyjne, sprawdzając jednocześnie dwie lub więcej kolumn.

Scenariusz: Chcesz oznaczyć wiersz tylko wtedy, gdy wartości w oba kolumnie A i kolumnie B pasują do wcześniejszego wpisu na Twojej liście.

Oto wzór:

=COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2)>1

Zakres $A$2:$A2 rozszerza się w miarę przesuwania w dół arkusza, więc Excel ocenia tylko wiersze powyżej bieżącego. To pozwala mu wykrywać duplikaty stopniowo, zamiast od razu wszystkie naraz.

Przejście od zmian wizualnych do inteligentnej logiki

Formuły formatowania warunkowego zamieniają Excel w system reagujący, który aktualizuje się wraz ze zmianami w danych. Niezależnie od tego, czy oznaczasz brakujące wartości, śledzisz budżety czy wykrywasz niezgodności, te reguły utrzymują twoje arkusze kalkulacyjne w przejrzystości i automatyzacji. Gdy twoje alerty są już ustawione, nauka korzystania z narzędzia Go To Special w Excelu jest naturalnym kolejnym krokiem w audycie i oczyszczaniu danych.