2 sposoby zmiany koloru wypełnienia komórek w programie Excel na podstawie ich wartości

W tym artykule znajdziesz dwa szybkie sposoby zmiany koloru komórki na podstawie jej wartości w programach Excel 2013, 2010 i 2007. Ponadto dowiesz się, jak używać formuł w programie Excel do zmiany koloru pustych komórek lub komórki z błędami formuł.

Wszyscy wiedzą, że aby zmienić kolor wypełnienia pojedynczej komórki lub całego zakresu w Excelu, wystarczy kliknąć przycisk Wypełnij kolor (Kolor wypełnienia). Ale co, jeśli musisz zmienić kolor wypełnienia wszystkich komórek zawierających określoną wartość? Co więcej, co jeśli chcesz, aby kolor wypełnienia każdej komórki zmieniał się automatycznie wraz ze zmianą zawartości tej komórki? W dalszej części artykułu znajdziesz odpowiedzi na te pytania i uzyskasz kilka przydatnych wskazówek, które pomogą Ci wybrać odpowiednią metodę rozwiązania każdego konkretnego problemu.

• Zmień kolor wypełnienia komórek na podstawie ich wartości (dynamicznie) – Kolor wypełnienia zmieni się automatycznie, gdy zmieni się wartość komórki.
• Zmień kolor komórki na podstawie jej bieżącej wartości (statycznie) — po ustawieniu kolor wypełnienia nie zmienia się bez względu na zmianę wartości komórki.
• Zmień kolor niektórych komórek (puste, z błędami, z formułami)

Jak dynamicznie zmieniać kolor komórki w Excelu na podstawie jej wartości?

Kolor wypełnienia zmieni się w zależności od wartości komórki.

Problem: Masz tabelę lub zakres danych i chcesz zmienić kolor wypełnienia komórek na podstawie ich wartości. Ponadto konieczne jest, aby kolor ten zmieniał się dynamicznie, odzwierciedlając zmiany danych w komórkach.

Decyzja: Użyj formatowania warunkowego w programie Excel, aby wyróżnić wartości większe niż X, mniejsze niż Y lub między X i Y.

Załóżmy, że masz listę cen gazu w różnych stanach i chcesz, aby ceny były wyższe niż $ 3.7, były podświetlone na czerwono i mniejsze lub równe $ 3.45 - Zielony.

Uwaga: Zrzuty ekranu dla tego przykładu zostały wykonane w programie Excel 2010, jednak w programach Excel 2007 i 2013 przyciski, okna dialogowe i ustawienia będą dokładnie takie same lub nieco inne.

Oto, co musisz zrobić krok po kroku:

1. Wybierz tabelę lub zakres, w którym chcesz zmienić kolor wypełnienia komórki. W tym przykładzie podkreślamy $B2$:$H$10 (nagłówki kolumn i pierwsza kolumna zawierająca nazwy stanów nie są zaznaczone).
2. Kliknij Strona główna (Dom), w dziale style (Style) kliknij Formatowanie warunkowe (Formatowanie warunkowe) > Nowe zasady (Utwórz regułę).
3. U góry okna dialogowego Nowa reguła formatowania (Utwórz regułę formatowania) w polu Wybierz typ reguły (Wybierz typ reguły) wybierz Formatuj tylko komórki zawierające (Formatuj tylko komórki, które zawierają).
4. Na dole okna dialogowego w polu Formatuj tylko komórki za pomocą (Formatuj tylko komórki, które spełniają następujący warunek) Ustaw warunki reguły. Wybieramy formatowanie tylko komórek z warunkiem: Wartość komórki (wartość komórki) – większa niż (jeszcze) - 3.7jak pokazano na poniższym rysunku.Następnie naciśnij przycisk Rozmiar (Format), aby wybrać kolor wypełnienia, który ma zostać zastosowany, jeśli zostanie spełniony określony warunek.
5. W wyświetlonym oknie dialogowym Formatowanie komórek Zakładka (Formatuj komórki) Wypełniać (Wypełnij) i wybierz kolor (wybraliśmy czerwonawy) i kliknij OK.
6. Potem wrócisz do okna Nowa reguła formatowania (Tworzenie reguły formatowania) gdzie w polu Podgląd (Przykład) pokaże próbkę Twojego formatowania. Jeśli jesteś zadowolony, kliknij OK.

Wynik ustawień formatowania będzie wyglądał mniej więcej tak:

Ponieważ musimy ustawić inny warunek, który pozwoli nam zmienić kolor wypełnienia na zielony dla komórek o wartościach mniejszych lub równych 3.45, a następnie ponownie naciśnij przycisk Nowe zasady (Utwórz regułę) i powtórz kroki od 3 do 6, ustawiając żądaną regułę. Poniżej znajduje się przykład drugiej utworzonej przez nas reguły formatowania warunkowego:

Gdy wszystko będzie gotowe – kliknij OK. Masz teraz ładnie sformatowaną tabelę, która pozwala na pierwszy rzut oka zobaczyć maksymalne i minimalne ceny gazu w różnych stanach. Dobrze dla nich tam, w Teksasie!

Wskazówka: W ten sam sposób możesz zmienić kolor czcionki w zależności od wartości komórki. Aby to zrobić, po prostu otwórz kartę chrzcielnica (Czcionka) w oknie dialogowym Formatowanie komórek (Format komórki) tak jak w kroku 5 i wybierz żądany kolor czcionki.

Jak ustawić stały kolor komórki na podstawie jego aktualnej wartości?

Po ustawieniu kolor wypełnienia nie zmieni się, bez względu na to, jak zmieni się zawartość komórki w przyszłości.

Problem: Chcesz dostosować kolor komórki na podstawie jej bieżącej wartości i chcesz, aby kolor wypełnienia pozostał taki sam nawet po zmianie wartości komórki.

Decyzja: Znajdź wszystkie komórki o określonej wartości (lub wartościach) za pomocą narzędzia Znajdź wszystko (Znajdź wszystko), a następnie zmień format znalezionych komórek za pomocą okna dialogowego Formatowanie komórek (format komórki).

Jest to jedno z tych rzadkich zadań, dla których nie ma wyjaśnienia w plikach pomocy Excela, forach czy blogach i dla których nie ma bezpośredniego rozwiązania. I jest to zrozumiałe, ponieważ to zadanie nie jest typowe. A jednak, jeśli musisz zmienić kolor wypełnienia komórki na stałe, to znaczy raz na zawsze (lub dopóki nie zmienisz go ręcznie), wykonaj następujące kroki.

Znajdź i zaznacz wszystkie komórki spełniające dany warunek

Możliwych jest tutaj kilka scenariuszy, w zależności od rodzaju poszukiwanej wartości.

Jeśli chcesz pokolorować komórki o określonej wartości, na przykład 50, 100 or 3.4 – następnie w zakładce Strona główna (Dom) w dziale Edycja (Edycja) kliknij Znajdź Wybierz (Znajdź i zaznacz) > Znajdź (Odnaleźć).

Wprowadź żądaną wartość i kliknij Znajdź wszystko (Znajdź wszystko).

Wskazówka: Po prawej stronie okna dialogowego Znajdowanie i zamienianie (Znajdź i zamień) jest przycisk Opcje (Opcje), po naciśnięciu którego uzyskasz dostęp do wielu zaawansowanych ustawień wyszukiwania, takich jak Mecz Case (wielkość liter ma znaczenie) i Dopasuj całą zawartość komórki (Cała komórka). Możesz użyć symboli wieloznacznych, takich jak gwiazdka (*), aby dopasować dowolny ciąg znaków, lub znak zapytania (?), aby dopasować dowolny pojedynczy znak.

Odnośnie poprzedniego przykładu, jeśli musimy znaleźć wszystkie ceny benzyny z 3.7 do 3.799, wtedy ustawimy następujące kryteria wyszukiwania:

Teraz kliknij dowolny z znalezionych elementów na dole okna dialogowego Znajdowanie i zamienianie (Znajdź i zamień) i kliknij Ctrl +aby podświetlić wszystkie znalezione wpisy. Następnie naciśnij przycisk Fermer (Blisko).

W ten sposób możesz zaznaczyć wszystkie komórki z podaną wartością (wartościami) za pomocą opcji Znajdź wszystko (Znajdź wszystko) w programie Excel.

Jednak w rzeczywistości musimy znaleźć wszystkie ceny benzyny, które przekraczają $ 3.7. Niestety narzędzie Znajdowanie i zamienianie (Znajdź i zamień) nie może nam w tym pomóc.

Zmień kolory wypełnienia wybranych komórek za pomocą okna dialogowego Formatuj komórki

Masz teraz wybrane wszystkie komórki z podaną wartością (lub wartościami), właśnie zrobiliśmy to za pomocą narzędzia Znajdowanie i zamienianie (Znajdź i zamień). Wszystko, co musisz zrobić, to ustawić kolor wypełnienia dla wybranych komórek.

Otwórz okno dialogowe Formatowanie komórek (Format komórki) na jeden z 3 sposobów:

• pilny Ctrl + 1.
• klikając dowolną wybraną komórkę prawym przyciskiem myszy i wybierając element z menu kontekstowego Formatowanie komórek (format komórki).
• Zakładka Strona główna (Dom) > Komórki. Komórki. (komórki) > Rozmiar (Format) > Formatowanie komórek (format komórki).

Następnie dostosuj opcje formatowania według własnych upodobań. Tym razem dla odmiany ustawimy kolor wypełnienia na pomarańczowy 🙂

Jeśli chcesz zmienić tylko kolor wypełnienia bez dotykania pozostałych opcji formatowania, możesz po prostu kliknąć przycisk Wypełnij kolor (Kolor wypełnienia) i wybierz kolor, który Ci się podoba.

Oto wynik naszych zmian formatowania w programie Excel:

W przeciwieństwie do poprzedniej metody (z formatowaniem warunkowym), ustawiony w ten sposób kolor wypełnienia nigdy nie zmieni się bez Twojej wiedzy, bez względu na to, jak zmienią się wartości.

Zmień kolor wypełnienia komórek specjalnych (puste, z błędem w formule)

Podobnie jak w poprzednim przykładzie, możesz zmienić kolor wypełnienia określonych komórek na dwa sposoby: dynamicznie i statycznie.

Użyj formuły, aby zmienić kolor wypełnienia specjalnych komórek w programie Excel

Kolor komórki zmieni się automatycznie w zależności od wartości komórki.

Najprawdopodobniej użyjesz tej metody rozwiązania problemu w 99% przypadków, to znaczy wypełnienie komórek zmieni się zgodnie z określonym przez Ciebie warunkiem.

Na przykład weźmy ponownie tabelę cen benzyny, ale tym razem dodamy jeszcze kilka stanów i opróżnimy niektóre komórki. Teraz zobacz, jak możesz znaleźć te puste komórki i zmienić ich kolor wypełnienia.

1. Na karcie Zaawansowane Strona główna (Dom) w dziale style (Style) kliknij Formatowanie warunkowe (Formatowanie warunkowe) > Nowe zasady (Utwórz regułę). Podobnie jak w drugim kroku przykładu Jak dynamicznie zmieniać kolor komórki na podstawie jej wartości.
2. W oknie dialogowym Nowa reguła formatowania (Utwórz regułę formatowania) wybierz opcję Użyj formuły, aby określić, które komórki Formatować (Użyj formuły, aby określić, które komórki należy sformatować). Dalej w pole Formatuj wartości tam, gdzie ta formuła jest prawdziwa (Wartości formatu, dla których poniższa formuła jest prawdziwa) wprowadź jedną z formuł:
   • zmienić wypełnienie pustych komórek

     =ISBLANK()

     =ЕПУСТО()

   • zmienić cieniowanie komórek zawierających formuły, które zwracają błąd

     =ISERROR()

     =ЕОШИБКА()

   Ponieważ chcemy zmienić kolor pustych komórek, potrzebujemy pierwszej funkcji. Wprowadź go, a następnie umieść kursor między nawiasami i kliknij ikonę wyboru zakresu po prawej stronie linii (lub wpisz żądany zakres ręcznie):

   =ISBLANK(B2:H12)

   =ЕПУСТО(B2:H12)

   

3. Naciśnij przycisk Rozmiar (Format), wybierz żądany kolor wypełnienia na karcie Wypełniać (Wypełnij), a następnie kliknij OK. Szczegółowe instrukcje podano w kroku 5 przykładu „Jak dynamicznie zmieniać kolor komórki na podstawie jej wartości”. Przykładowe formatowanie warunkowe, które ustawisz, będzie wyglądać mniej więcej tak:
4. Jeśli jesteś zadowolony z koloru, kliknij OK. Zobaczysz, jak utworzona reguła zostanie natychmiast zastosowana do tabeli.

Zmień kolor wypełnienia specjalnych komórek statycznie

Po skonfigurowaniu wypełnienie pozostanie niezmienione, niezależnie od wartości komórki.

Jeśli chcesz ustawić stały kolor wypełnienia pustych komórek lub komórek z formułami zawierającymi błędy, użyj tej metody:

1. Wybierz tabelę lub zakres i kliknij F5aby otworzyć okno dialogowe przejdź do (skok), a następnie naciśnij przycisk Specjalny (Atrakcja).
2. W oknie dialogowym Idź do Special (Wybierz grupę komórek) zaznacz opcję puste miejsca (Puste komórki), aby zaznaczyć wszystkie puste komórki.Jeśli chcesz podświetlić komórki zawierające formuły z błędami, zaznacz opcję Wzory (wzory) > Błędy (Błędy). Jak widać na powyższym obrazku, dostępnych jest wiele innych ustawień.
3. Na koniec zmień wypełnienie wybranych komórek lub ustaw inne opcje formatowania za pomocą okna dialogowego Formatowanie komórek (Formatuj komórki), zgodnie z opisem w Zmienianie wypełnienia wybranych komórek.

Nie zapominaj, że wprowadzone w ten sposób ustawienia formatowania zostaną zachowane nawet po wypełnieniu pustych komórek wartościami lub poprawieniu błędów w formułach. Trudno sobie wyobrazić, by ktoś musiał iść w ten sposób, chyba że na potrzeby eksperymentu 🙂