Dodanie formatowania warunkowego w programie Excel umożliwia zastosowanie różnych opcji formatowania do komórki lub zakresu komórek, które spełniają określone warunki. Ustawienie takich warunków może pomóc uporządkować arkusz kalkulacyjny i ułatwić skanowanie. Opcje formatowania, których można użyć, obejmują zmiany koloru czcionki i tła, style czcionek, obramowania komórek i dodawanie formatowania liczb do danych.
Excel ma wbudowane opcje dla często używanych warunków, takich jak znajdowanie liczb, które są większe lub mniejsze od określonej wartości lub znajdowanie liczb, które są powyżej lub poniżej średniej wartości. Oprócz tych wstępnie ustawionych opcji możesz również tworzyć niestandardowe reguły formatowania warunkowego za pomocą formuł programu Excel.
Te instrukcje dotyczą programów Excel 2019, 2016, 2013, 2010 i Excel dla Microsoft 365.
Stosowanie wielu warunków w programie Excel
Możesz zastosować więcej niż jedną regułę do tych samych danych, aby przetestować różne warunki. Na przykład dane budżetu mogą mieć ustawione warunki, które stosują zmiany formatowania po osiągnięciu określonych poziomów wydatków, takich jak 50%, 75% i 100% całkowitego budżetu.
W takich okolicznościach program Excel najpierw określa, czy różne reguły są sprzeczne, a jeśli tak, program postępuje zgodnie z ustaloną kolejnością pierwszeństwa, aby określić, która reguła formatowania warunkowego ma być zastosowana do danych.
Znajdowanie danych, których wzrost przekracza 25% i 50%
W poniższym przykładzie dwie niestandardowe reguły formatowania warunkowego zostaną zastosowane do zakresu komórek B2 do B5.
- Pierwsza reguła sprawdza, czy dane w komórkach A2:A5 są większe niż odpowiadająca im wartość w B2:B5 przez ponad 25%.
- Druga reguła sprawdza, czy te same dane w A2:A5 przekraczają odpowiednią wartość w B2:B5 o więcej niż 50%.
Jak widać na powyższym obrazku, jeśli którykolwiek z powyższych warunków jest spełniony, kolor tła komórki lub komórek w zakresie B1:B4 zmieni się.
- Dla 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 za pomocą okna dialogowego Nowa reguła formatowania. Zacznij od wprowadzenia przykładowych danych do komórek A1 do C5, jak widać na powyższym obrazku.
W końcowej części samouczka dodamy 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; pozwoli nam to sprawdzić poprawność reguł formatowania warunkowego.
Ustawianie reguł formatowania warunkowego
Najpierw zastosujemy formatowanie warunkowe, aby znaleźć co najmniej 25-procentowy wzrost.
Funkcja będzie wyglądać tak:
=(A2-B2)/A2>25%
- Podświetl komórki B2 do B5 w arkuszu.
- Kliknij kartę Strona główna z wstążka.
- Kliknij ikonę Formatowanie warunkowe na wstążce, aby otworzyć menu rozwijane.
-
Wybierz Nowa reguła, aby otworzyć okno dialogowe Nowa reguła formatowania.
- Pod Wybierz typ reguły kliknij ostatnią opcję: Użyj formuły, aby określić komórki do sformatowania.
- Wpisz formula zanotowaną powyżej w miejscu poniżej Sformatuj wartości, gdy ta formuła jest prawdziwa:
- Kliknij przycisk Format, aby otworzyć okno dialogowe. Kliknij kartę Wypełnij i wybierz kolor.
- Kliknij OK, aby zamknąć okna dialogowe i powrócić do arkusza roboczego.
- Kolor tła komórek B3 i B5 powinien zmienić się na wybrany kolor.
Teraz zastosujemy formatowanie warunkowe, aby znaleźć wzrost o 50 procent lub więcej. Formuła będzie wyglądać tak:
- Powtórz pierwsze pięć kroków powyżej.
- Wpisz formułę podaną powyżej w polu poniżej Sformatuj wartości tam, gdzie ta formuła jest prawdziwa:
- Kliknij przycisk Format, aby otworzyć okno dialogowe. Kliknij kartę Wypełnij i wybierz inny kolor niż w poprzednim zestawie kroków.
- Kliknij OK, aby zamknąć okna dialogowe i powrócić do arkusza roboczego.
Kolor tła komórki B3 powinien pozostać taki sam, wskazując, że procentowa różnica między liczbami w komórkach A3 iB3 jest większe niż 25 procent, ale mniejsze lub równe 50 procent. Kolor tła komórki B5 powinien zmienić się na nowy wybrany kolor, wskazując, że procentowa różnica między liczbami w komórkach A5 i B5 jest większe niż 50 procent.
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óre obliczą dokładną różnicę procentową między liczbami w zakresachA2:A5 i B2:B5.
Formuła w komórce C2 wygląda tak:
=(A2-B2)/A2
- Kliknij na komórka C2, aby uczynić ją aktywną komórką.
- Wpisz powyższą formułę i naciśnij klawisz Enter na klawiaturze.
- Odpowiedź 10% powinna pojawić się w komórce C2, wskazując, że liczba w komórka A2 jest o 10% większa niż liczba w komórka B2.
- Może być konieczna zmiana formatowania w komórce C2, aby wyświetlić odpowiedź jako procent.
- Użyj uchwytu wypełniania, aby skopiować formułę z cell C2 do cells C3 do C5.
- Odpowiedzi dla komórek C3 do C5 powinny wynosić 30%, 25% i 60%.
Odpowiedzi w tych komórkach pokazują, że reguły formatowania warunkowego są dokładne, ponieważ różnica między komórkami A3 i B3 jest większa niż 25 procent, a różnica między komórkami A5 i B5 jest większa niż 50 procent.
Komórka B4 nie zmieniła koloru, ponieważ różnica między komórkami A4 i B4 jest równa 25 procent, a nasza reguła formatowania warunkowego określała, że do zmiany koloru tła wymagany jest procent większy niż 25 procent.
Porządek pierwszeństwa dla formatowania warunkowego
Gdy stosujesz wiele reguł do tego samego zakresu danych, program Excel najpierw określa, czy występują konflikty. Sprzeczne reguły to te, w których nie można zastosować obu opcji formatowania do tych samych danych.
W naszym przykładzie reguły są sprzeczne, ponieważ oba używają tej samej opcji formatowania - zmiana koloru tła komórki.
W sytuacji, gdy druga reguła jest prawdziwa (różnica w wartości jest większa niż 50 procent między dwiema komórkami), wtedy pierwsza reguła (różnica w wartości jest większa niż 25 procent) również jest prawdziwa.
Ponieważ komórka nie może mieć jednocześnie dwóch różnych kolorów tła, program Excel musi wiedzieć, którą regułę formatowania warunkowego powinien zastosować.
Porządek pierwszeństwa programu Excel oznacza, że reguła znajdująca się wyżej na liście w oknie dialogowym Menedżer reguł formatowania warunkowego jest stosowana jako pierwsza.
Jak pokazano na powyższym obrazku, 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 jest zielony.
Domyślnie nowe reguły trafiają na górę listy; aby zmienić kolejność, użyj przycisków strzałek W górę i W dół w oknie dialogowym.
Stosowanie niekonfliktowych reguł
Jeśli dwie lub więcej reguł formatowania warunkowego nie powoduje konfliktu, obie są stosowane, gdy warunek testowany przez każdą regułę staje się prawdziwy.
Jeśli pierwsza reguła formatowania warunkowego w naszym przykładzie sformatowała zakres komórek B2:B5 z pomarańczową ramką zamiast pomarańczowego koloru tła, dwie reguły formatowania warunkowego nie konflikt, ponieważ oba formaty mogą być stosowane bez zakłócania drugiego.
Formatowanie warunkowe a formatowanie zwykłe
W przypadku konfliktów między regułami formatowania warunkowego a ręcznie stosowanymi opcjami formatowania, reguła formatowania warunkowego zawsze ma pierwszeństwo i zostanie zastosowana zamiast ręcznie dodanych opcji formatowania.
