Samouczek programu Excel: Jak przetestować formatowanie warunkowe za pomocą fikcyjnej formuły

W tym filmie pokażę, jak szybko przetestować reguły formatowania warunkowego za pomocą fikcyjnych formuł.

W przypadku stosowania formatowania warunkowego za pomocą formuł może być trudno zapewnić prawidłowe działanie formuł, ponieważ nie można zobaczyć, co się stanie z formułą po zastosowaniu reguły.

Możesz myśleć o formatowaniu warunkowym jako o „nakładce” niewidocznych formuł, które znajdują się nad komórkami.

Gdy formuła w nakładce zwraca wartość TRUE dla danej komórki, stosowane jest formatowanie.

Formuły, które nie zwracają wartości TRUE (lub odpowiednika), nic nie robią.

Problem polega na tym, że tego nie widzisz, więc musisz używać prób i błędów, co może być frustrujące i czasochłonne.

Dobrym sposobem na przyspieszenie jest użycie czegoś, co nazywam „fikcyjnymi formułami”.

Fałszywe formuły umożliwiają wizualizację zachowania formuł przed utworzeniem reguły.

Zilustruję to bardzo prostym przykładem. Powiedzmy, że chcemy wyróżnić wartości powyżej 100 w tym zestawie danych.

Na początek wybiorę obszar z boku, w jednej linii z rzędami.

Następnie napiszę pierwszą formułę względem lewej górnej komórki w danych.

W tym przypadku jest to B4, więc wzór to

= B4> 100

Teraz skopiuję wzór w poprzek iw dół.

Zauważ, że w każdej komórce otrzymujemy wynik PRAWDA lub FAŁSZ. Jeśli sprawdzimy kilka odniesień, zobaczysz, że każda formuła oblicza komórkę w danych względem B4.

Teraz wyobraź sobie te wyniki transponowane bezpośrednio na danych. Tam, gdzie zobaczysz wartość TRUE, zostanie zastosowane formatowanie.

Gdy widzisz FALSE, nic się nie dzieje.

Ta fikcyjna formuła wygląda dobrze, więc wypróbujmy ją w regule formatowania warunkowego.

Najpierw kopiuję pierwszą fikcyjną formułę. Następnie wybieram dane i tworzę nową regułę.

W obszarze formuły po prostu wklejam formułę. Następnie ustawiam format i zapisuję regułę.

Teraz wszystkie wartości powyżej 100 są podświetlone, dokładnie tak, jak przewidziano we wzorach fikcyjnych.

Spróbujmy tego samego pomysłu z bardziej skomplikowaną formułą. Zaznaczmy wiersze w tej tabeli z priorytetem „A”.

Tak jak poprzednio, pierwszym krokiem jest ustalenie, gdzie umieścić fikcyjne formuły. Mamy dużo miejsca po prawej stronie, więc zacznę od komórki G5.

Ponieważ chcemy wyróżnić zadania z priorytetem „A”, spróbujmy

= B5 = „A”

Kiedy kopiuję formuły, widzisz, że to nie zadziała.

Wyniki TRUE pokazują nam, że tylko wartości w kolumnie B zostaną podświetlone. Chcemy podświetlić całe wiersze, więc muszę dostosować formułę, aby zablokować odwołanie do kolumny, dodając znak dolara:

= $ B5 = „A”

Teraz fikcyjne formuły działają. Otrzymujemy wartości TRUE z pełnego wiersza, gdy priorytet ma wartość „A”.

Wypróbujmy formułę w nowej regule, wykonując ten sam proces, co poprzednio.

Kiedy ustawiam format i zapisuję, nowa reguła działa idealnie za pierwszym razem.

Następnym razem, gdy będziesz musiał zastosować formatowanie warunkowe z wymagającą formułą, ustaw fikcyjne formuły obok danych i modyfikuj formuły, aż uzyskasz potrzebne wyniki.

Pracując bezpośrednio w arkuszu, masz pełny dostęp do wszystkich narzędzi formuł programu Excel i możesz łatwo rozwiązywać problemy i dostosowywać formułę, aż będzie działać idealnie.