2 sposoby zmiany tła w programie Excel

W tym artykule poznasz dwa proste sposoby zmiany tła komórek na podstawie ich zawartości w najnowszych wersjach programu Excel. Zrozumiesz również, których formuł użyć, aby zmienić odcień komórek lub komórek, w których formuły są napisane nieprawidłowo lub w których nie ma żadnych informacji.

Wszyscy wiedzą, że edycja tła prostej komórki to prosta procedura. Kliknij „kolor tła”. Ale co, jeśli potrzebujesz korekcji kolorów na podstawie określonej zawartości komórki? Jak mogę sprawić, by stało się to automatycznie? Poniżej znajduje się szereg przydatnych informacji, które pozwolą Ci znaleźć właściwy sposób na wykonanie wszystkich tych zadań.

Dynamiczna zmiana koloru tła komórki

Zadanie: Masz tabelę lub zestaw wartości i musisz edytować kolor tła komórek na podstawie wprowadzonej tam liczby. Musisz również upewnić się, że odcień reaguje na zmieniające się wartości.

Rozwiązanie: W tym zadaniu dostępna jest funkcja „formatowania warunkowego” programu Excel do kolorowania komórek z liczbami większymi niż X, mniejszymi niż Y lub między X i Y.

Załóżmy, że masz zestaw produktów w różnych stanach wraz z ich cenami i musisz wiedzieć, który z nich kosztuje więcej niż 3,7 USD. Dlatego zdecydowaliśmy się wyróżnić te produkty, które są powyżej tej wartości na czerwono. A komórki, które mają podobną lub większą wartość, postanowiono wybarwić na zielony odcień.

Uwaga: Zrzut ekranu został wykonany w wersji 2010 programu. Ale to nie ma wpływu na nic, ponieważ kolejność działań jest taka sama, niezależnie od tego, z której wersji – najnowszej czy nie – korzysta dana osoba.

Oto, co musisz zrobić (krok po kroku):

1. Wybierz komórki, w których odcień ma być edytowany. Na przykład zakres $B2$:$H$10 (nazwy kolumn i pierwsza kolumna, która zawiera nazwy stanów, są wykluczone z próbki).

2. Kliknij na „Dom” w grupie "styl". Będzie przedmiot "Formatowanie warunkowe". Tam też musisz wybrać "Nowa zasada". W angielskiej wersji programu Excel kolejność kroków jest następująca: „Strona główna”, „Grupa stylów”, „Formatowanie warunkowe > Nowa reguła».

3. W oknie, które się otworzy, zaznacz pole „Formatuj tylko komórki zawierające” („Formatuj tylko komórki zawierające” w wersji angielskiej).

4. Na dole tego okna pod napisem „Formatuj tylko komórki spełniające następujący warunek” (Formatuj tylko komórki za pomocą) możesz przypisać reguły, według których będzie wykonywane formatowanie. Wybraliśmy format dla określonej wartości w komórkach, która musi być większa niż 3.7, jak widać na zrzucie ekranu: 

5. Następnie kliknij przycisk "Format". Po lewej stronie pojawi się okno z obszarem wyboru koloru tła. Ale wcześniej musisz otworzyć zakładkę "Wypełnić" ("Wypełnić"). W tym przypadku jest czerwony. Następnie kliknij przycisk „OK”.

6. Następnie wrócisz do okna „Nowa reguła formatowania”, ale już na dole tego okna możesz podejrzeć jak ta komórka będzie wyglądać. Jeśli wszystko jest w porządku, musisz kliknąć przycisk „OK”.

W rezultacie otrzymujesz coś takiego:

Następnie musimy dodać jeszcze jeden warunek, czyli zmienić tło komórek o wartościach mniejszych niż 3.45 na zielone. Aby wykonać to zadanie, musisz kliknąć „Nowa reguła formatowania” i powtórz powyższe kroki, tylko warunek musi być ustawiony jako „mniej niż lub równoważny” (w wersji angielskiej „mniejszy lub równy”, a następnie wpisz wartość. Na koniec należy kliknąć przycisk „OK”.

Teraz tabela jest sformatowana w ten sposób.

Wyświetla najwyższe i najniższe ceny paliwa w różnych stanach i możesz od razu określić, gdzie sytuacja jest najbardziej optymistyczna (oczywiście w Teksasie).

Rekomendacja: W razie potrzeby możesz użyć podobnej metody formatowania, edytując nie tło, ale czcionkę. Aby to zrobić, w oknie formatowania, które pojawiło się w piątym etapie, musisz wybrać zakładkę "Czcionka" i postępuj zgodnie z instrukcjami wyświetlanymi w oknie. Wszystko jest intuicyjnie jasne i nawet początkujący może to rozgryźć.

W rezultacie otrzymasz taki stół:

Jak zachować ten sam kolor komórki, nawet jeśli zmieni się wartość?

Zadanie: Musisz pokolorować tło, aby nigdy się nie zmieniało, nawet jeśli tło zmieni się w przyszłości.

Rozwiązanie: znajdź wszystkie komórki o określonej liczbie za pomocą funkcji Excela "Znajdź wszystko" „Znajdź wszystko” lub dodatek „Wybierz specjalne komórki” („Wybierz specjalne komórki”), a następnie edytuj format komórki za pomocą funkcji „Formatuj komórki” („Formatuj komórki”).

To jedna z tych nieczęstych sytuacji, które nie są opisane w podręczniku Excela, a nawet w Internecie rozwiązanie tego problemu można znaleźć dość rzadko. Co nie jest zaskakujące, ponieważ to zadanie nie jest standardowe. Jeśli chcesz edytować tło na stałe, aby nigdy się nie zmieniało, dopóki nie zostanie ręcznie dostosowane przez użytkownika programu, musisz postępować zgodnie z powyższymi instrukcjami.

Zaznacz wszystkie komórki, które zawierają określony warunek

Istnieje kilka możliwych metod, w zależności od tego, jaka konkretna wartość ma zostać znaleziona.

Jeśli chcesz wyznaczyć komórki o określonej wartości ze specjalnym tłem, musisz przejść do zakładki „Dom” i wybierz „Znajdź i wybierz” – „Znajdź”.

Wprowadź wymagane wartości i kliknij "Znajdź wszystko".

Pomoc: Możesz kliknąć przycisk „Opcje” po prawej stronie, aby skorzystać z dodatkowych ustawień: gdzie szukać, jak przeglądać, czy przestrzegać wielkich i małych liter i tak dalej. Możesz także wpisać dodatkowe znaki, takie jak gwiazdka (*), aby znaleźć wszystkie wiersze zawierające te wartości. Jeśli użyjesz znaku zapytania, możesz znaleźć dowolny pojedynczy znak.

W naszym poprzednim przykładzie, jeśli chcielibyśmy znaleźć wszystkie oferty paliw między 3,7 a 3,799 USD, moglibyśmy zawęzić zapytanie.

Teraz wybierz dowolną z wartości znalezionych przez program w dolnej części okna dialogowego i kliknij jedną z nich. Następnie naciśnij kombinację klawiszy „Ctrl-A”, aby wybrać wszystkie wyniki. Następnie kliknij przycisk „Zamknij”. 

Oto jak wybrać wszystkie komórki z określonymi wartościami za pomocą funkcji Znajdź wszystko. W naszym przykładzie musimy znaleźć wszystkie ceny paliwa powyżej 3,7 USD, a niestety Excel nie pozwala na to za pomocą funkcji Znajdź i zamień.

Tutaj wychodzi na jaw „beczka miodu”, ponieważ istnieje inne narzędzie, które pomoże w tak skomplikowanych zadaniach. Nazywa się Wybierz komórki specjalne. Ten dodatek (który należy osobno zainstalować w programie Excel) pomoże:

• znajdź wszystkie wartości w określonym zakresie, na przykład między -1 a 45,
• uzyskać maksymalną lub minimalną wartość w kolumnie,
• znajdź ciąg lub zakres,
• znajdź komórki według koloru tła i nie tylko.

Po zainstalowaniu dodatku wystarczy kliknąć przycisk „Wybierz według wartości” („Wybierz według wartości”), a następnie sprecyzuj zapytanie w oknie dodatku. W naszym przykładzie szukamy liczb większych niż 3,7. Naciskać "Wybierać" („Wybierz”), a za sekundę otrzymasz taki wynik:

Jeśli jesteś zainteresowany dodatkiem, możesz pobrać wersję próbną ze strony łącze.

Zmiana tła zaznaczonych komórek w oknie „Formatowanie komórek”

Teraz, gdy wszystkie komórki o określonej wartości zostały wybrane za pomocą jednej z opisanych powyżej metod, pozostaje dla nich określenie koloru tła.

Aby to zrobić, musisz otworzyć okno „Format komórki”naciskając klawisz Ctrl + 1 (możesz również kliknąć wybrane komórki prawym przyciskiem myszy i kliknąć lewym przyciskiem myszy element „formatowanie komórek”) i dostosować potrzebne formatowanie.

Wybierzemy odcień pomarańczowy, ale możesz wybrać dowolny inny.

Jeśli chcesz edytować kolor tła bez zmiany innych parametrów wyglądu, możesz po prostu kliknąć „kolorowe wypełnienie” i wybierz kolor, który idealnie do Ciebie pasuje.

Rezultatem jest tabela taka:

W przeciwieństwie do poprzedniej techniki, tutaj kolor komórki nie zmieni się, nawet jeśli wartość zostanie zmieniona. Można to wykorzystać np. do śledzenia dynamiki towarów w określonej grupie cenowej. Zmieniła się ich wartość, ale kolor pozostał ten sam.

Edycja koloru tła dla komórek specjalnych (pustych lub z błędami podczas pisania formuły)

Podobnie jak w poprzednim przykładzie, użytkownik ma możliwość edycji koloru tła specjalnych komórek na dwa sposoby. Dostępne są opcje statyczne i dynamiczne.

Stosowanie formuły do ​​edycji tła

Tutaj kolor komórki będzie edytowany automatycznie na podstawie jego wartości. Ta metoda bardzo pomaga użytkownikom i jest pożądana w 99% sytuacji.

Jako przykład możesz użyć poprzedniej tabeli, ale teraz niektóre komórki będą puste. Musimy ustalić, które z nich nie zawierają żadnych odczytów i edytować kolor tła.

1. W zakładce „Dom” musisz kliknąć "Formatowanie warunkowe" ->  "Nowa zasada" (tak samo jak w kroku 2 pierwszej sekcji „Dynamiczna zmiana koloru tła”.

2. Następnie musisz wybrać przedmiot „Użyj wzoru, aby określić…”.

3. Wprowadź wzór =Puste() (w wersji IBLANK), jeśli chcesz edytować tło pustej komórki, lub =Wystąpił błąd() (w wersji ISERROR), jeśli chcesz znaleźć komórkę, w której znajduje się błędnie zapisana formuła. Ponieważ w tym przypadku musimy edytować puste komórki, wprowadzamy formułę =Puste(), a następnie umieść kursor w nawiasach i kliknij przycisk obok pola wprowadzania formuły. Po tych manipulacjach należy ręcznie wybrać zakres komórek. Dodatkowo możesz samodzielnie określić zakres, np. =Puste(B2:H12).

4. Kliknij przycisk „Format”, wybierz odpowiedni kolor tła i wykonaj wszystko zgodnie z opisem w paragrafie 5 sekcji „Dynamiczna zmiana koloru tła komórki”, a następnie kliknij „OK”. Tam również możesz zobaczyć, jaki będzie kolor komórki. Okno będzie wyglądać mniej więcej tak.

5. Jeśli podoba Ci się tło komórki, musisz kliknąć przycisk „OK”, a zmiany zostaną natychmiast wprowadzone do tabeli.

Statyczna zmiana koloru tła komórek specjalnych

W tej sytuacji po przypisaniu kolor tła pozostanie taki sam, bez względu na to, jak zmieni się komórka.

Jeśli chcesz na stałe zmienić komórki specjalne (puste lub zawierające błędy), postępuj zgodnie z poniższymi instrukcjami:

1. Wybierz swój dokument lub kilka komórek i naciśnij F5, aby otworzyć okno Przejdź do, a następnie naciśnij przycisk "Atrakcja".
2. W otwartym oknie dialogowym wybierz przycisk „Puste” lub „Puste komórki” (w zależności od wersji programu – lub angielskiej), aby zaznaczyć puste komórki.
3. Jeśli chcesz podświetlić komórki, które zawierają formuły z błędami, wybierz element „Formuły” i zostaw jedno pole wyboru obok słowa „Błędy”. Jak wynika z powyższego zrzutu ekranu, komórki można wybierać według dowolnych parametrów, a każde z opisanych ustawień jest dostępne w razie potrzeby.
4. Na koniec musisz zmienić kolor tła wybranych komórek lub dostosować je w inny sposób. Aby to zrobić, użyj metody opisanej powyżej.

Pamiętaj tylko, że wprowadzone w ten sposób zmiany formatowania będą się utrzymywać, nawet jeśli wypełnisz luki lub zmienisz specjalny typ komórki. Oczywiście jest mało prawdopodobne, że ktoś będzie chciał skorzystać z tej metody, ale w praktyce wszystko może się zdarzyć.

Jak najlepiej wykorzystać program Excel?

Jako użytkownicy programu Microsoft Excel powinniście wiedzieć, że zawiera on wiele funkcji. Niektóre z nich znamy i kochamy, podczas gdy inne pozostają tajemnicze dla przeciętnego użytkownika, a duża liczba blogerów stara się rzucić na nie choć trochę światła. Ale są wspólne zadania, które każdy z nas będzie musiał wykonać, a Excel nie wprowadza niektórych funkcji ani narzędzi do automatyzacji niektórych złożonych czynności.

A rozwiązaniem tego problemu są dodatki (dodatki). Niektóre z nich rozprowadzane są za darmo, inne – za pieniądze. Istnieje wiele podobnych narzędzi, które mogą pełnić różne funkcje. Na przykład znajdź duplikaty w dwóch plikach bez tajemniczych formuł lub makr.

Jeśli połączysz te narzędzia z podstawową funkcjonalnością Excela, możesz osiągnąć bardzo dobre wyniki. Na przykład możesz dowiedzieć się, które ceny paliwa uległy zmianie, a następnie znaleźć duplikaty w pliku za ostatni rok.

Widzimy, że formatowanie warunkowe to poręczne narzędzie, które pozwala zautomatyzować pracę na tabelach bez szczególnych umiejętności. Wiesz już, jak wypełniać komórki na kilka sposobów, na podstawie ich zawartości. Teraz pozostaje tylko wprowadzić to w życie. Powodzenia!