Używanie formuł do formatowania warunkowego w programie Excel

Dodanie formatowania warunkowego w programie Excel pozwala zastosować różne opcje formatowania do komórki lub zakresu komórek, które spełniają określone warunki. Opcje formatowania są stosowane tylko wtedy, gdy wybrane komórki spełniają te ustawione warunki.

Opcje formatowania, które można zastosować, obejmują zmiany koloru czcionki i tła, style czcionek, granice komórek oraz dodawanie formatowania liczb do danych.

Excel ma wiele wbudowanych opcji dla często używanych warunków, takich jak znajdowanie liczb, które są większe lub mniejsze niż pewna wartość lub znajdowanie liczb, które są powyżej lub poniżej wartości średniej.

Oprócz tych wstępnie ustawionych opcji możliwe jest również tworzenie niestandardowych reguł formatowania warunkowego przy użyciu formuł Excela w celu przetestowania warunków określonych przez użytkownika.

01 z 04

Stosowanie wielu reguł w Excelu

Do tych samych danych można zastosować więcej niż jedną regułę w celu przetestowania różnych warunków. Na przykład dane budżetu mogą mieć ustawione warunki, które stosują zmiany formatowania, gdy wydawane są pewne poziomy, takie jak 50%, 75% i 100% całkowitego budżetu.

W takich okolicznościach program Excel najpierw określa, czy różne reguły są w konflikcie, a jeśli tak, to program podąża ustaloną kolejnością pierwszeństwa, aby określić, która reguła formatowania warunkowego zostanie zastosowana do danych.

Wyszukiwanie danych przekraczających 25% i 50%

W poniższym przykładzie dwie niestandardowe reguły formatowania warunkowego zostaną zastosowane do zakresu komórki B2 do B5.

• Pierwsza reguła sprawdza, czy dane w komórki A2 do A5 jest większa niż odpowiadająca jej wartość w B2 do B5 o więcej niż 25%.
• Druga reguła sprawdza, czy dane są takie same A2: A5 przekracza odpowiednią wartość w B2: B5 o ponad 50%.

Jak można zobaczyć na powyższym obrazku, jeśli którykolwiek z powyższych warunków jest prawdziwy, kolor tła komórki lub komórek w zakresie B1: B4 ulegnie zmianie.

• W przypadku danych, w których różnica jest większa niż 25%, kolor tła komórki zmieni się na zielony.
• Jeśli różnica jest większa niż 50%, kolor tła komórki zmieni się na czerwony.

Reguły użyte do wykonania tego zadania zostaną wprowadzone przy użyciu formatowania warunkowego Nowa reguła formatowania Okno dialogowe. Rozpocznij od wprowadzenia danych próbki do komórek od A1 do C5, jak pokazano na powyższym obrazku

w trzeci krok samouczka doda formuły do ​​komórek C2: C4 które pokazują dokładną różnicę procentową między wartościami w komórkach A2: A5 i B2: B5 w celu sprawdzenia dokładności warunkowych reguł formatowania.

02 z 04

Ustawianie reguł formatowania warunkowego

Jak już wspomniano, warunkowe reguły formatowania sprawdzające te dwa warunki zostaną wprowadzone przy użyciu formatowania warunkowego Nowa reguła formatowania Okno dialogowe. Zacznijmy od ustawienia naszego niestandardowego formatowania warunkowego:

Formatowanie warunkowe w celu znalezienia większego niż 25 procent wzrostu

= (A2-B2) / A2> 25%

1. Atrakcja komórki B2 do B5 w arkuszu roboczym.
2. Kliknij na Zakładka domyślna z faborek.
3. Kliknij na Formatowanie warunkowe ikona w faborek aby otworzyć listę rozwijaną.
4. Wybierać Nowa zasada otworzyć Nowa reguła formatowania Okno dialogowe.
5. Od Styl: wybierz opcję Klasyczny opcja.
6. W drugim menu kliknij ostatnią opcjęUżyj formuły, aby określić, które komórki należy sformatować.
7. Wpisz formuła dostarczone powyżej w przewidzianej przestrzeni.
8. Kliknij na Formatuj za pomocą: drop-down, a następnie wybierz Jasnoczerwone wypełnienie ciemnoczerwonym tekstem opcja.
9. Kliknij dobrze zamknąć okna dialogowe i wrócić do arkusza roboczego.
10. W tym momencie kolor tła komórki B3 i B5 powinien być zielony.

Formatowanie warunkowe w celu znalezienia większego niż 50 procent wzrostu

= (A2-B2) / A2> 50%

Atrakcja komórki B2 do B5 w arkuszu roboczym.

1. Kliknij na Zakładka domyślna z faborek.
2. Kliknij na Formatowanie warunkowe ikona w faborek aby otworzyć listę rozwijaną.
3. Wybierać Nowa zasada otworzyć Nowa reguła formatowania Okno dialogowe.
4. Od Styl: wybierz opcję Klasyczny opcja.
5. W drugim menu kliknij ostatnią opcjęUżyj formuły, aby określić, które komórki należy sformatować.
6. Wpisz formuła dostarczone powyżej w przewidzianej przestrzeni.
7. Kliknij na Formatuj za pomocą: drop-down, a następnie wybierz Jasnozielony wypełnij ciemnozielonym tekstem opcja.
8. Kliknij dobrze zamknąć okna dialogowe i wrócić do arkusza roboczego.

Kolor tła komórka B3 powinien być zielony, wskazując, że różnica procentowa między liczbami w komórki A3 i B3 jest większy niż 25 procent, ale mniejszy niż lub równy 50 procent. Kolor tła komórka B5 powinien zmienić się na czerwony wskazując, że różnica procentowa między liczbami w komórki A5 i B5 jest większa niż 50 procent.

03 z 04

Sprawdzanie reguł formatowania warunkowego

Aby sprawdzić, czy wprowadzone reguły formatowania warunkowego są poprawne, możemy wprowadzić formuły do ​​komórek C2: C5 który obliczy dokładną różnicę procentową między liczbami w zakresach A2: A5 i B2: B5.

= (A2-B2) / A2

1. Kliknij komórka C2 aby stała się aktywną komórką.
2. Wpisz powyższą formułę i naciśnij Wchodzić klawisz na klawiaturze.
3. Odpowiedź 10% powinna pojawić się w komórka C2, wskazując, że liczba w komórka A2 jest o 10% większa niż liczba w komórka B2.
4. Może być konieczna zmiana formatowania komórka C2 aby wyświetlić odpowiedź jako procent.
5. Użyj napełnij uchwyt aby skopiować formułę z komórka C2 do komórki C3 do C5.
6. Odpowiedzi dla komórki C3 do C5 powinno być 30%, 25% i 60%.

Odpowiedzi w tych komórkach pokazują, że utworzone reguły formatowania warunkowego są poprawne, ponieważ różnica między nimi komórki A3 i B3 jest większa niż 25 procent, a różnica między komórki A5 i B5 jest większa niż 50 procent.

Komórka B4 nie zmieniło koloru, ponieważ różnica między komórki A4 i B4 wynosi 25 procent, a nasza reguła formatowania warunkowego określa, że ​​wymagany jest procent większy niż 25 procent, aby kolor tła zmienił się na zielony.

04 z 04

Kolejność pierwszeństwa dla formatowania warunkowego

Gdy wiele reguł zostanie zastosowanych do tego samego zakresu danych, program Excel najpierw określa, czy reguły są w konflikcie. Sprzeczne reguły to te, w których wybrane opcje formatowania dla każdej reguły nie mogą być zastosowane do tych samych danych.

W przykładzie użytym w tym samouczku reguły są w konflikcie, ponieważ obie reguły używają tej samej opcji formatowania - zmiany koloru komórki tła.

W sytuacji, gdy druga zasada jest prawdziwa (różnica w wartości jest większa niż 50 procent między dwiema komórkami), wówczas pierwsza zasada (różnica w wartości jest większa niż 25 procent) jest również prawdą.

Kolejność pierwszeństwa w Excelu

Ponieważ komórka nie może jednocześnie mieć czerwonego i zielonego tła, program Excel musi wiedzieć, która reguła warunkowego formatowania powinna obowiązywać.

To, która reguła zostanie zastosowana, określa kolejność priorytetów programu Excel, która stwierdza, że ​​reguła znajdująca się wyżej na liście w oknie dialogowym Menedżer reguł formatowania ma pierwszeństwo.

Jak pokazano na powyższym rysunku, druga reguła użyta w tym samouczku znajduje się wyżej na liście i dlatego ma pierwszeństwo przed pierwszą regułą. W rezultacie kolor tła komórki B5 zmienia się na czerwony.

Domyślnie nowe reguły są dodawane do początku listy i dlatego mają wyższy priorytet. Aby zmienić kolejność pierwszeństwa użyj W górę i w dół przyciski strzałek w oknie dialogowym zgodnie z powyższym obrazem.

Stosowanie reguł niezwiązanych z konfliktem

Jeśli dwie lub więcej reguł formatowania warunkowego nie powoduje konfliktu, oba są stosowane, gdy warunek każdej reguły jest testowany, staje się prawdziwy.

Jeśli pierwsza reguła formatowania warunkowego w naszym przykładzie sformatowała zakres komórek B2: B5 z zieloną obwódką zamiast zielonego koloru tła, dwie warunkowe reguły formatowania nie będą powodować konfliktu, ponieważ oba formaty mogą być stosowane bez zakłócania drugiego.

W rezultacie, komórka B5 miałby zarówno zieloną obramowanie, jak i czerwony kolor tła, ponieważ różnica między liczbami w komórki A5 i B5 jest większy niż 25 i 50 procent.

Formatowanie warunkowe a standardowe formatowanie

W przypadku konfliktów między regułami formatowania warunkowego i ręcznie zastosowanymi opcjami formatowania, reguły formatowania warunkowego mają zawsze pierwszeństwo i zostaną zastosowane zamiast ręcznie dodanych opcji formatowania.

Jeśli początkowo zastosowano żółty kolor tła komórki B2 do B5 w tym przykładzie, po dodaniu warunkowych reguł formatowania, tylko komórki B2 i B4 pozostanie żółty. Ponieważ wprowadzone warunkowe reguły formatowania mają zastosowanie do komórek B3 i B5, ich kolory tła zmieniłyby się odpowiednio z żółtego na zielony i czerwony.