Heading

Formatowanie warunkowe jest fantastycznym sposobem na szybką wizualizację danych w arkuszu kalkulacyjnym. Dzięki formatowaniu warunkowemu można m.in. wyróżnić daty w ciągu następnych 30 dni, zaznaczyć problemy z wprowadzaniem danych, wyróżnić wiersze zawierające najlepszych klientów, pokazać duplikaty i wiele innych.

Excel dostarcza dużą liczbę „ustawień wstępnych”, które ułatwiają tworzenie nowych reguł bez użycia formuł. Można jednak również tworzyć reguły z własnymi formułami. Używając własnej formuły, użytkownik przejmuje warunek, który wywołuje regułę i może zastosować dokładnie taką logikę, jaka jest mu potrzebna. Formuły dają Ci maksymalną moc i elastyczność.

Na przykład, używając ustawienia wstępnego „Równe do”, łatwo jest podświetlić komórki równe „jabłku”.

A co, jeśli chcesz podświetlić komórki równe „jabłku”, „kiwi” lub „limonce”? Oczywiście, możesz stworzyć regułę dla każdej z tych wartości, ale jest to bardzo kłopotliwe. Zamiast tego można po prostu użyć jednej reguły opartej na formule z funkcją OR:

Oto wynik zastosowania reguły do zakresu B4:F8 w tym arkuszu kalkulacyjnym:

A oto dokładna zastosowana formuła:

=OR(B4="apple",B4="kiwi",B4="lime")

Szybki start
Możesz utworzyć regułę formatowania warunkowego opartą na formule w czterech prostych krokach:
1. Zaznacz komórki, które chcesz sformatować.

2. Utwórz regułę formatowania warunkowego i wybierz opcję Formuła

3. Wprowadź formułę, która zwraca wartość TRUE lub FALSE.

4. Ustaw opcje formatowania i zapisz regułę.

Funkcja ISODD zwraca wartość TRUE tylko dla liczb nieparzystych, wywołując regułę:

Wideo: Jak zastosować formatowanie warunkowe za pomocą formuły
Oferujemy również szkolenie wideo na ten temat.
Logika formuły
Formuły, które stosują formatowanie warunkowe, muszą zwracać TRUE lub FALSE, lub odpowiedniki liczbowe. Oto kilka przykładów:
=ISODD(A1)=ISNUMBER(A1)=A1>100=AND(A1>100,B1<50)=OR(F1="MN",F1="WI")

Wszystkie powyższe formuły zwracają TRUE lub FALSE, więc doskonale sprawdzają się jako wyzwalacze formatowania warunkowego.
Gdy formatowanie warunkowe jest stosowane do zakresu komórek, należy wprowadzić odwołania do komórek w odniesieniu do pierwszego wiersza i kolumny w zaznaczeniu (tj. lewej górnej komórki). Aby zrozumieć, jak działają formuły formatowania warunkowego, należy wyobrazić sobie, że ta sama formuła jest stosowana do każdej komórki w zaznaczeniu, a odwołania do komórek są aktualizowane w zwykły sposób. Wyobraź sobie, że wprowadziłeś formułę w lewej górnej komórce zaznaczenia, a następnie skopiowałeś ją do całego zaznaczenia. Jeśli masz z tym problem, zobacz sekcję Formuły Dummy poniżej.

Przykłady formuł
Poniżej znajdują się przykłady niestandardowych formuł, których możesz użyć do zastosowania formatowania warunkowego. Niektóre z tych przykładów można utworzyć przy użyciu wbudowanych ustawień wstępnych Excela do wyróżniania komórek, ale niestandardowe formuły mogą wykraczać daleko poza ustawienia wstępne, jak widać poniżej.
Zobacz także: Ponad 30 formuł formatowania warunkowego 
Podświetl zamówienia z Teksasu
Aby podświetlić wiersze, które reprezentują zamówienia z Teksasu (skrót TX), użyj formuły, która blokuje odwołanie do kolumny F:
=$F5="TX"


Więcej szczegółów znajdziesz w tym artykule: Podkreślanie wierszy za pomocą formatowania warunkowego.
Wideo: Jak wyróżnić wiersze za pomocą formatowania warunkowego
Wyświetl daty w ciągu najbliższych 30 dni 
Aby wyróżnić daty występujące w ciągu najbliższych 30 dni, potrzebujemy formuły, która (1) upewni się, że daty są w przyszłości i (2) upewni się, że daty są 30 dni lub mniej od dnia dzisiejszego. Jednym ze sposobów, aby to zrobić, jest użycie funkcji AND wraz z funkcją NOW, jak poniżej:
=AND(B4>NOW(),B4<=(NOW()+30))

Przy bieżącej dacie 18 sierpnia 2016 r. formatowanie warunkowe podświetla daty w następujący sposób:

Funkcja TERAZ zwraca bieżącą datę i godzinę. Aby uzyskać szczegółowe informacje o tym, jak ta formuła, działa, zobacz ten artykuł: Highlight dates in the next N days.
Highlight column differences 
Dając dwie kolumny, które zawierają podobne informacje, możesz użyć formatowania warunkowego, aby zauważyć subtelne różnice. Formuła użyta do wywołania poniższego formatowania jest następująca:
=$B4<>$C4


Zobacz też: wersja tej formuły, która używa funkcji EXACT do porównania z uwzględnieniem wielkości liter.
Podkreślanie brakujących wartości
Aby podświetlić wartości na jednej liście, których brakuje na innej, możesz użyć formuły opartej na funkcji COUNTIF:
=COUNTIF(list,B5)=0


Ta formuła po prostu sprawdza każdą wartość z listy A względem wartości z nazwanego zakresu „lista” (D5:D10). Gdy liczba ta wynosi zero, formuła zwraca wartość TRUE i uruchamia regułę, która podświetla wartości z listy A, których brakuje w liście B.
Wideo: How to find missing values with COUNTIF
Highlight properties with 3+ bedrooms under $350k
Aby znaleźć nieruchomości na tej liście, które mają co najmniej 3 sypialnie, ale są mniejsze niż $300,000, możesz użyć formuły opartej na funkcji AND:
=AND($C5<350000,$D5>=3)

Znaki dolara ($) blokują odwołanie do kolumn C i D, a funkcja AND jest używana do upewnienia się, że oba warunki są TRUE. W wierszach, w których funkcja AND zwróci wartość TRUE, zastosowane zostanie formatowanie warunkowe:


Wyświetlaj najwyższe wartości (przykład dynamiczny)
Pomimo że Excel ma wstępnie zdefiniowane ustawienia dla „najwyższych wartości”, ten przykład pokazuje, jak zrobić to samo za pomocą formuły i jak formuły mogą być bardziej elastyczne. Używając formuły, możemy uczynić arkusz interaktywnym – gdy wartość w F2 jest aktualizowana, reguła natychmiast reaguje i podświetla nowe wartości.

Formuła użyta w tej regule to:
=B4>=LARGE(data,input)

Gdzie „dane” to nazwany zakres B4:G11, a „dane wejściowe” to nazwany zakres F2. Ta strona zawiera szczegóły i pełne wyjaśnienie.
Wykresy Gantta
Możesz wierzyć lub nie, ale możesz nawet używać formuł do tworzenia prostych wykresów Gantta z formatowaniem warunkowym, takich jak ten:

Ten arkusz używa dwóch reguł, jednej dla słupków i jednej dla cieniowania weekendowego:
=AND(D$4>=$B5,D$4<=$C5) // bars=WEEKDAY(D$4,2)>5 // weekends

Ten artykuł wyjaśnia formułę dla słupków, a ten artykuł wyjaśnia formułę dla cieniowania weekendowego.
Proste pole wyszukiwania
Jedną z fajnych sztuczek, jakie można zrobić z formatowaniem warunkowym, jest zbudowanie prostego pola wyszukiwania. W tym przykładzie reguła podświetla komórki w kolumnie B, które zawierają tekst wpisany w komórce F2:

Użyta formuła to:
=ISNUMBER(SEARCH($F$2,B2))

Więcej szczegółów i pełne wyjaśnienie znajdziesz w:

Artykuł: Jak wyróżnić komórki, które zawierają określony tekst 
Artykuł: Jak podświetlić wiersze, które zawierają określony tekst 
Wideo: How to build a search box to highlight data 

Troubleshooting
Jeśli nie możesz sprawić, aby Twoje reguły formatowania warunkowego odpaliły się poprawnie, najprawdopodobniej jest problem z Twoją formułą. Po pierwsze, upewnij się, że rozpocząłeś formułę od znaku równości (=). Jeśli zapomnisz o tym kroku, Excel po cichu przekształci całą formułę na tekst, czyniąc ją bezużyteczną. Aby to naprawić, wystarczy usunąć podwójne cudzysłowy dodane przez Excela po obu stronach i upewnić się, że formuła zaczyna się od znaku równości (=).
Jeśli formuła jest wpisana poprawnie, ale nie uruchamia reguły, być może trzeba będzie pogrzebać trochę głębiej. Normalnie możesz użyć klawisza F9, aby sprawdzić wyniki w formule, lub użyć funkcji Oceniaj, aby przejść przez formułę. Niestety, nie można użyć tych narzędzi z formułami formatowania warunkowego, ale można użyć techniki zwanej „formuły dummy”.
Formuły dummy
Formuły dummy są sposobem na przetestowanie formuł formatowania warunkowego bezpośrednio na arkuszu, dzięki czemu można zobaczyć, co faktycznie robią. Może to być duża oszczędność czasu, gdy zmagasz się z poprawnym działaniem odwołań do komórek.
W skrócie, wprowadzasz tę samą formułę w zakresie komórek, który odpowiada kształtowi Twoich danych. Dzięki temu można zobaczyć wartości zwracane przez każdą formułę i jest to świetny sposób na wizualizację i zrozumienie działania formatowania warunkowego opartego na formułach. Szczegółowe wyjaśnienie znajdziesz w tym artykule.

Wideo: Testuj formatowanie warunkowe za pomocą formuł dummy
Ograniczenia
Istnieją pewne ograniczenia związane z formatowaniem warunkowym opartym na formułach:

Nie można stosować ikon, skal kolorów ani pasków danych za pomocą formuły niestandardowej. Jesteś ograniczony do standardowego formatowania komórek, w tym formatów liczb, czcionek, kolorów wypełnienia i opcji obramowania.
Nie możesz używać pewnych konstrukcji formuł, takich jak związki, przecięcia lub stałe tablicowe dla kryteriów formatowania warunkowego.
Nie możesz odwoływać się do innych skoroszytów w formule formatowania warunkowego.

Czasami można obejść punkty #2 i #3. Możesz być w stanie przenieść logikę formuły do komórki w arkuszu, a następnie odwołać się do tej komórki w formule zamiast tego. Jeśli próbujesz użyć stałej tablicowej, spróbuj utworzyć nazwany zakres zamiast tego.