W tym samouczku zademonstrujemy, jak testować formuły przed zastosowaniem ich do formatowania warunkowego w programie Excel.
Testowanie niestandardowych formuł - Excel
Podczas stosowania formuły niestandardowej do formatowania warunkowego w programie Excel ważne jest, aby upewnić się, że formuła faktycznie zwraca w arkuszu prawidłową wartość PRAWDA lub FAŁSZ, aby formatowanie warunkowe działało poprawnie.
Przed utworzeniem reguły formatowania warunkowego możemy utworzyć formułę w „wolnym miejscu” w naszym skoroszycie programu Excel i upewnić się, że formuła działa poprawnie.
Testowanie formuły w celu wyróżnienia komórki arkusza roboczego
W powyższym przykładzie chcemy napisać formułę, aby sprawdzić, czy każda komórka w arkuszu jest większa niż 5, a jeśli jest większa niż 5, chcemy pokolorować tło komórki.
Możemy zacząć od pierwszej komórki w zakresie - w tym przypadku B3, a następnie przetestować każdy wiersz i kolumnę zakresu - aż do E11.
Zaczynając od prostej formuły, widzimy, że B3 ma wartość 5, a zatem NIE jest większe niż 5.
Następnie możemy użyć uchwytu, aby przeciągnąć tę formułę w dół do wiersza 11, zauważając, że część wiersza adresu komórki (tj. 3) zmieni się na następną liczbę, gdy przeciągniemy formułę w dół przez wiersze, więc B3 stanie się B4, a następnie B5 i tak dalej.
Następnie możemy przeciągnąć podświetlony zakres komórek przez 4 kolumny, aby sprawdzić, czy komórki w kolumnach od C do E mają wartość większą niż 5. Gdy przeciągamy formułę przez, część adresu komórki w kolumnie zmieni się odpowiednio - od kolumna od C do D do E do F.
Jak teraz widzimy, otrzymujemy poprawną wartość TRUE lub FALSE w zależności od wartości w odpowiedniej komórce.
Teraz wiemy, że nasza formuła jest poprawna i możemy użyć tej formuły do utworzenia reguły formatowania warunkowego. Podobnie jak w przypadku naszej formuły testowej, używamy pierwszej komórki z zakresu, B3.
=B3>5
Gdy mamy już typy w formule, możemy ustawić Format dla koloru tła i kliknąć OK.
Jak widać formuła =B3>5 dotyczy zakresu B3:E11. Komórka formuły musi zawsze odpowiadać pierwszej komórce w zakresie, który ma zostać sformatowany.
Kliknij Zastosować zastosować formatowanie do arkusza.
Testowanie formuły w celu wyróżnienia wiersza w arkuszu
Stosowanie formuły do zmiany koloru tła wiersza zamiast kolumny jest nieco inne i nieco bardziej skomplikowane.
Rozważ poniższy arkusz.
W tym arkuszu chcemy podświetlić cały wiersz, jeśli projekt jest zaległy - więc jeśli kolumna E ma komórkę, która zwraca wartość „Zaległy" zamiast "Na czas”, następnie chcemy podświetlić cały wiersz, w którym znajduje się ta komórka.
Sposób na to jest prosty:
=JEŻELI(E4=”Zaległy”, PRAWDA, FAŁSZ)
Jeśli jednak zastosujemy to do naszego formatowania warunkowego, to właśnie to zostanie zwrócone.
Tylko pierwsza kolumna z zakresu zostanie sformatowana.
Wypróbujmy tę formułę w naszym arkuszu programu Excel.
Otrzymujemy pożądany wynik, TRUE, gdy projekt jest przeterminowany w kolumnie E. Jednak gdybyśmy skopiowali tę formułę dla 5 kolumn w zakresie (np. z kolumny B do kolumny E), zwrócony wynik byłby FALSE .
Formuła uległaby zmianie - więc kolumna E zmieniłaby się na kolumnę F - i oczywiście w kolumnie F nie ma nic, więc formuła za każdym razem zwraca FALSE.
Musimy się upewnić, że formuła dotyczy TYLKO kolumny E - ale dotyczy to właściwego wiersza - więc kiedy kopiujemy formułę w poprzek, kolumna E pozostaje taka sama. Aby to zrobić, musimy użyć odwołania mieszanego, które blokuje kolumnę na miejscu.
=$E4=”Zaległe”
Teraz, gdy skopiujemy go do 4 kolumn, kolumna w formule pozostanie taka sama, ale wiersz się zmieni.
Gdy nasza formuła działa teraz w arkuszu, możemy utworzyć niestandardową formułę w formatowaniu warunkowym.
Kiedy klikniemy ok, oraz Zastosować, nasze wiersze, które mają kolumnę E z napisem zaległym, zostaną podświetlone.
