*Kroki w tym artykule będą miały zastosowanie do Excela 2007-2016. Zdjęcia zostały wykonane przy użyciu programu Excel 2016.
Jeśli jesteś fanem funkcji formatowania warunkowego w programie Excel, prawdopodobnie szukasz coraz więcej sposobów na wyróżnienie przydatnych informacji w swoich danych. Pytanie, które często pojawia się wśród tych „uzależnionych od formatowania warunkowego” brzmi Can I use If/Then formula to format a cell?
Odpowiedź brzmi tak i nie. Każdy argument formatowania warunkowego musi wygenerować wynik TRUE, co oznacza, że na poziomie dosłownym, reguła formatowania warunkowego jest instrukcją If/Then w stylu „Jeśli ten warunek jest TRUE, THEN sformatuj komórkę w ten sposób”.
To, czego formatowanie warunkowe nie może zrobić w pojedynczej regule, to warunek IF/THEN/ELSE, taki jak „Jeśli # jest większy niż 10, sformatuj na czerwono, w przeciwnym razie sformatuj na zielono”. Zamiast tego potrzebne są DWIE reguły, jedna dla „większe niż 10” i jedna dla „mniej niż 10”.
Przyjrzyjrzyjmy się kilku scenariuszom, aby zrozumieć, w jaki sposób możemy stworzyć efekt formatowania warunkowego IF/THEN, nawet jeśli nie możemy go użyć w samej funkcji:
Aby skorzystać z naszych przykładów, pobierz 04-If-Then Conditional Formatting.xlsx
Scenariusz 1 (zakładka Urodzenia): Chcesz wyróżnić wszystkich pracowników w swoim dziale, którzy mają urodziny w tym miesiącu kolorem czerwonym, a wszystkie inne działy kolorem niebieskim.
Rozwiązanie: Utwórz dwie reguły – jedną dla swojego działu, jedną dla wszystkich pozostałych
Krok 1 – Podświetl urodziny w swoim dziale
Formuła identyfikująca urodziny w bieżącym miesiącu będzie miała postać (zobacz ten artykuł, aby dowiedzieć się więcej o używaniu dat w formatowaniu warunkowym):
Aby utworzyć formułę generującą zestawienie TRUE/FALSE, która podświetla urodziny tylko w jednym dziale, użyłbyś formuły:
Przykład ten został utworzony w kwietniu, więc podświetlone zostaną kwietniowe daty urodzin. Jeśli odtwarzasz to ćwiczenie w innym miesiącu, zobaczysz inne wyniki!
Następnie utwórz drugą regułę dla tego samego zakresu, używając tej formuły, aby wyróżnić daty urodzin, które nie znajdują się w Twoim dziale:
BONUS! W tym przykładzie zastosowaliśmy regułę do komórki działu, aby pokazać związek z formułą. Zmieniając ustawienie opcji Stosuje się do zakresu, możesz jednak łatwo zaznaczyć inną komórkę – np. datę urodzenia – lub cały wiersz. Więcej pomysłów znajdziesz w rozdziale Get the Most Out of Excel’s Conditional Formatting for more ideas.
Scenariusz 2 (karta Retainers): Masz tabelę z liczbą godzin, jakie Twoi pracownicy przepracowali dla konkretnych klientów, oraz masz tabelę z liczbą godzin, jakie każdy klient ma w swoim budżecie na retainery. Chcesz wyróżnić klientów, którzy przekroczyli swój retainer.
Rozwiązanie 1: Utwórz kolumnę pomocniczą za pomocą formuły IF/THEN, aby określić, czy klient przekroczył swój budżet retainera. Jeśli twój arkusz ma już logikę IF/THEN/ELSE, której potrzebujesz, osadzoną w komórce, formatowanie warunkowe może działać w oparciu o te wyniki. Nie musisz koniecznie odtwarzać logiki w samej regule.
W tym przykładzie mamy już formułę IF/THEN, która zwraca wynik „TAK”, jeśli nasz klient przekroczył budżet na utrzymanie. Nasza reguła formatowania warunkowego musi więc tylko wyszukać ciąg tekstowy „TAK” i zastosować formatowanie, gdy jest on prawdziwy.
Podświetl zakres komórek, kliknij Formatowanie warunkowe > Podświetl reguły komórek > Tekst, który zawiera, aby utworzyć regułę, a następnie wpisz TAK w oknie dialogowym Tekst, który zawiera.
Rozwiązanie 2: Utwórz formułę do obliczania budżetu zatrzymanego.
Jeśli nie masz lub nie chcesz tworzyć kolumny pomocniczej z instrukcją IF/THEN, możesz użyć tej samej metody, co w pierwszym scenariuszu, tworząc regułę określającą, czy klient przekracza budżet. W tym przykładzie zastosowaliśmy regułę do komórek Klient, a formuła byłaby następująca:
Jeśli jesteś przyzwyczajony do tworzenia złożonych formuł, które obejmują wszystkie przypadki w jednej komórce, może to wymagać trochę ponownego uczenia się, aby zorientować się w podejściu do formatowania warunkowego, które działa bardziej przyrostowo. Najlepszą wskazówką jest pamiętanie, że można stosować wiele reguł do tych samych komórek – rozbij kryteria formatowania na osobne kroki, a najprawdopodobniej uda Ci się dotrzeć tam, gdzie trzeba!