Formatowanie warunkowe w programie Excel: instrukcje i zastosowanie

Skorzystaj z tych instrukcji, aby utworzyć różne formaty

Formatowanie warunkowe w programie Microsoft Excel jest przydatnym narzędziem do zwiększania widoczności tabel, raportów lub komórek, wyróżniania obszaru lub kilku kolumn, analizowania danych, wyjaśniania różnic lub identyfikowania trendów. Służą do tego paski danych, zestawy symboli lub kolorowe skale. W zależności od określonych warunków wyświetlanie komórek jest zmieniane lub zakres komórek jest formatowany. Możesz stworzyć własne dla wielu zintegrowanych warunków.

Formatowanie warunkowe ma sens i pomaga

Formatowanie warunkowe w programie Excel można zastosować na wiele sposobów, niezależnie od tego, czy dotyczy to tabeli programu Excel, określonego zakresu komórek czy raportu w formie tabeli przestawnej. Dostępne są różne szablony działań i formatów. Decydującą zaletą dla użytkowników Excela jest to, że specjalne wartości lub wyniki można wyróżnić kodowaniem kolorami. W przypadku zmiany wartości formatowanie jest automatycznie dostosowywane - w zależności od warunków.

informacje: Tabela przestawna („Tabela przestawna”) to specjalna tabela, za pomocą której można obliczyć, podsumować i przeanalizować dużą ilość danych. Ponadto możesz szybko dokonywać porównań i identyfikować trendy w danych egzaminacyjnych.

Formatowanie warunkowe z istniejącymi regułami dotyczącymi liczb, treści tekstowych lub dat

Jeśli sformatujesz za pomocą operatora porównania, łatwiej będzie znaleźć komórki. Możesz sprawić, by wartości były bardziej widoczne, na przykład większe lub mniejsze niż określona wartość lub pewne wartości, które znajdują się między górną a dolną granicą. Oznacza to, że żądane lub wyszukiwane dane są wyświetlane w kolorze. Do edycji dostępne jest szybkie lub zaawansowane formatowanie. Dzięki rozszerzonemu formatowaniu możesz pracować z formułami, na przykład automatycznie oznaczać kolorem kolumnę Excela.

Przykłady:

• Magazynier chciałby zaznaczyć wszystkie zapasy poniżej 15 sztuk kolorowym tłem.
• W tabeli należy zaznaczyć wszystkie miasta powyżej 150 000 mieszkańców.
• Firma produkcyjna chce wyróżnić na czerwono wszystkie towary wyprodukowane w okresie od 1 stycznia 2022-2023 do 31 stycznia 2022-2023.

Szybkie formatowanie:

Wybór jednej lub więcej komórek.

Kliknij „Start” na karcie.

Przejdź do strzałki obok przycisku „Formatowanie warunkowe” w „Styl”.

Kliknij „Zasady wyróżniania komórek”.

Wybierz żądane polecenie, np. B. większe niż, mniejsze niż, pomiędzy, data itd.

Wprowadź wartości i wybierz format.

Rozszerzone formatowanie:

1. Zaznacz co najmniej jedną komórkę w tabeli lub raporcie w formie tabeli przestawnej.

2. Przejdź do karty „Strona główna”, w grupie Style kliknij strzałkę obok „Formatowanie warunkowe”.

3. Kliknij "Zarządzaj regułami": Pojawi się okno dialogowe "Menedżer reguł formatowania warunkowego".

4. Wybierz akcję:

   • Kliknij „Nowa reguła”: Pojawi się okno dialogowe „Nowa reguła formatowania”.
   • Aby zmienić format, upewnij się, że w polu listy „Pokaż regułę formatowania dla” zaznaczono poprawny arkusz lub tabelę.
   • W polu „Dotyczy” przejdź do „Zwiń okno dialogowe”; następnie wybierz nowy zakres komórek w arkuszu.
   • Kliknij przycisk „Rozwiń okno dialogowe”.
   • Wybierz regułę i kliknij „Edytuj regułę”: Pojawi się okno dialogowe „Edytuj regułę formatowania”.

5. Aby zmienić zakres pól w obszarze wartości „Zastosuj regułę do” raportu w formie tabeli przestawnej, wykonaj następujące czynności:

   • Wybór: Kliknij „Tylko te komórki”
   • Odpowiednie pole: Kliknij „Wszystkie komórki z tymi samymi polami”
   • Pole wartości: Kliknij „Wszystkie komórki”

6. W sekcji „Wybierz typ reguły” kliknij „Formatuj tylko komórki zawierające”

Teraz możesz wykonywać akcje pod "Edytuj opis reguły" w polu listy "Formatuj komórki tylko za pomocą":

Formatuj według numeru, daty lub godziny:

Aby to zrobić, wybierz wartość komórki i operator porównania oraz wprowadź liczbę, datę lub godzinę. przykład: W przypadku „Między” wpisz 300 i 500 lub wybierz „Równe” i wprowadź datę.

Możesz użyć formuły, aby odzyskać wartość liczbową, datę lub godzinę.

Formatuj według tekstu:

Wybierz „Określony tekst” i „Operator relacyjny”.

Wpisz tekst, np. B. na „zawiera” słowo „złoto” lub na „zaczyna się od” przedrostka, który chcesz wyszukać.

Można wprowadzić maksymalnie 255 znaków.

Wartości można zwracać za pomocą formuł. Aby to zrobić, formuła musi zaczynać się od (=). Nieprawidłowe formuły nie są formatowane. Zaleca się najpierw przetestować formułę pod kątem błędów.

Formatuj według daty:

• Wybierz datę i datę porównania, np. B. Wczoraj, w przyszłym tygodniu lub w tym miesiącu

Następnie: Aby określić format, kliknij „Format”, pojawi się okno dialogowe „Formatuj komórki”. Wybierz żądany format liczb, format wypełnienia, ramkę lub czcionkę, kliknij "OK".

Możliwy jest wybór wielu formatów. Możesz zobaczyć, które formaty zostały wybrane w polu „Podgląd”.

Formatowanie warunkowe z bazami danych, schematami kolorów i zestawami symboli

Zawartość i wartości w komórkach można wizualnie aktualizować za pomocą baz danych, skal kolorów lub zestawów symboli oraz uatrakcyjniać i czytelność. Jeśli wartości w zakresie komórek mają być ze sobą porównywane, jest to łatwiejsze dzięki formatowaniu warunkowemu.

Formatuj komórki za pomocą pasków danych

Paski danych ułatwiają porównywanie komórek ze sobą. Wartość w komórce jest wskazywana przez długość paska. Im wyższa wartość, tym dłuższy pasek danych, im niższa wartość, tym krótszy pasek. Pozwala to na szybkie określenie wysokich lub niskich wartości.

Szybkie formatowanie:

Zaznacz żądany obszar (komórkę, tabelę lub cały arkusz).

Na karcie Narzędzia główne kliknij przycisk Formatowanie warunkowe.

Przejdź do „paska danych” i wybierz wypełnienie.

Sformatuj komórki za pomocą kolorowych ramek

Skale kolorów ułatwiają klasyfikację różnych informacji o danych i umożliwiają szybkie porównywanie ich zawartości ze sobą. W zależności od odcienia koloru wyświetlane są wartości wysokie lub niskie. Możesz formatować komórki w skali 2- lub 3-kolorowej.

2-kolorowa skala

Szybkie formatowanie:

Wybierz komórkę, tabelę lub cały arkusz.

Na karcie Narzędzia główne kliknij opcję Formatowanie warunkowe.

Przejdź do „skala kolorów” i kliknij żądany format 2-kolorowej skali. Górny kolor oznacza wyższe wartości, dolny kolor niższe wartości.

3-kolorowa skala

Trzeci kolor umożliwia wizualizację wartości średnich – oprócz odwzorowania kolorami wartości wysokich i niskich jak w skali 2-kolorowej.

Szybkie formatowanie:

Wybierz komórkę, tabelę lub cały arkusz.

Na karcie Narzędzia główne kliknij opcję Formatowanie warunkowe.

Przejdź do „skala kolorów” i kliknij żądany format 3-kolorowej skali. Górny kolor oznacza wyższe wartości, dolny kolor niższe wartości.

Formatuj komórki za pomocą zestawów symboli

Dzięki zestawowi symboli dane można przeglądać i klasyfikować w osobnych kategoriach. Symbol oznacza pewien zakres wartości. Formatowanie warunkowe w programie Excel oferuje różne kolorowe symbole: strzałki, kropki, krzyżyki, wykrzykniki i wiele innych.

Szybkie formatowanie:

Wybierz komórkę, tabelę lub cały arkusz.

Na karcie Narzędzia główne kliknij opcję Formatowanie warunkowe.

Przejdź do „Zestawów symboli” i wybierz żądany zestaw symboli

Sam twórz reguły formatowania

Jeśli nie możesz znaleźć odpowiedniej opcji w formatowaniu warunkowym oferowanym przez Excel, możesz łatwo stworzyć indywidualne reguły formatowania.

Zaznacz zakres komórek, które mają być sformatowane warunkowo.

Przejdź do „Formatowanie warunkowe” na karcie „Strona główna”.

Wybierz „Nowa reguła” i potwierdź przyciskiem „OK”.

Jeśli nie możesz znaleźć odpowiedniej opcji dla własnej reguły formatowania, może pomóc formuła logiczna. Na przykład możesz użyć formuły, aby automatycznie pokolorować komórki programu Excel, gdy tylko zawartość będzie dostępna. I odwrotnie, komórki, które są puste, mogą być formatowane warunkowo.

Przykład: Zastosowanie formuły pozwala na porównanie danych spoza wybranego zakresu. Obejmuje to nawet dane znajdujące się w innym arkuszu w tym samym skoroszycie. Lub możesz porównać wartości z wynikiem zwracanym przez funkcję. Formuła musi być logiczna i zwracać „prawda” lub „fałsz”.

Wyróżniając komórki bezpośrednio w arkuszu, możesz wprowadzić odwołania do komórek w formule. Są to bezwzględne odwołania do komórek. Jednak Excel może również dostosować odwołania do komórek dla każdej komórki w wybranym obszarze. Jeśli chcesz, wybierz względne odwołania do komórek.

Instrukcje krok po kroku dotyczące tworzenia reguły

1. Na karcie „Strona główna” w grupie „Style” przejdź do strzałki obok „Formatowanie warunkowe” i kliknij „Zarządzaj regułami”. Pojawi się okno dialogowe Menedżer reguł formatowania warunkowego.

2. Możliwe są następujące działania:

   • Jeśli chcesz dodać format warunkowy, przejdź do „Nowa reguła”.
   • Zmień format warunkowy:
   • • Sprawdź, czy jesteś we właściwym arkuszu.
     • Najpierw w opcji Dotyczy, kliknij Zwiń okno dialogowe. Następnie wybierz nowy zakres komórek i przejdź do „Rozwiń okno dialogowe”.
     • Wybierz żądaną regułę, kliknij „Edytuj regułę”. Pojawi się „Edytuj regułę formatowania”.

3. W sekcji „Wybierz typ reguły” kliknij „Użyj formuły, aby określić komórki do sformatowania”.

   • W sekcji „Edytuj opis reguły” wprowadź formułę w „Formatuj wartości, dla których ta reguła jest prawdziwa”. Formuła zaczyna się od (=) i musi zwracać „prawda” (1) lub „fałsz” (0).
   • Kliknij Format, aby wyświetlić okno dialogowe Formatowanie komórek.
   • Teraz wybierz liczbę, czcionkę, format wypełnienia itp., które mają być użyte, jeśli wartość komórki jest prawdziwa i potwierdź przyciskiem „OK”. Wybrane formaty są wyświetlane w „Podglądzie”.

Na przykład formuła LICZ.JEŻELI zwraca wartości logiczne. W przypadku zastosowania tej formuły można określić zduplikowane wartości lub zawartość występującą z określoną częstotliwością można wyróżnić kolorem. W ten sposób można również znaleźć i wyróżnić kolorem unikatowe przedmioty. Inne formuły, których można użyć do formatowania warunkowego, to

• REST: więc zmienne kolory linii mogą być ustawiane automatycznie
• IDENTYCZNE: Dzięki tej formule dane, których szukasz, można zaznaczyć w tabeli Excela, która odpowiada określonej wartości
• ISERROR: automatycznie zaznacza puste komórki w tabeli Excela lub sprawia, że wartości błędów znikają ze skoroszytu
• NOT i ISNUMBER: Formatuje wszystkie komórki programu Excel, które nie zawierają liczb
• MAX: największa wartość komórki jest podświetlona w tabeli
• MIN: umożliwia warunkowe formatowanie najmniejszej wartości na liście Excela, aby minimum było widoczne na pierwszy rzut oka
• PO LEWEJ: w zależności od ciągu znaków na początku, komórki Excela są automatycznie formatowane i otrzymują jednolity kolor tekstu
• DUŻY i LEWY: umożliwiają pokolorowanie komórki z nową zawartością
• PRAWO: Komórki mogą być formatowane na podstawie ostatniego znaku ciągu
• DZIŚ: Dzięki temu spotkania można sformatować w kolorze za pomocą metody sygnalizacji świetlnej lub można podświetlić wartości dat, które wkrótce będą aktualne
• MIESIĄC lub ROK: pozwalają podzielić listę Excela na miesiące lub lata; Za pomocą ROK można również zaznaczyć wartości dat z bieżącego roku oraz podświetlić wartości roczne
• OR i WEEKDAY: podświetla kolorem wartości według dnia tygodnia

Przykład tworzenia reguły dla sprawdzeń AND i OR:

W tym przykładzie do formatowania warunkowego używane są dwie wartości. Jeśli pierwsza reguła nie ma zastosowania, druga reguła ma zastosowanie automatycznie.

Pierwsza zasada: Nabywca domu chce sprawdzić, czy planowany zakup nieruchomości jest zgodny z jego budżetem. Może wnieść do 80 000 euro własnych środków w formie zaliczki i obsłużyć miesięczną ratę kredytu w maksymalnej wysokości 1200 euro. Jeżeli zarówno zadatek, jak i rata kredytu spełniają te wymagania, odpowiednie komórki są zaznaczone na zielono.

Druga zasada: Jeżeli zaliczka lub rata nie spełniają wymagań, odpowiednie komórki są zaznaczone na czerwono.

Wzór na utworzenie pierwszej reguły przy użyciu koloru zielonego jest następujący:

= AND (JEŚLI ($4$<= 80000,1); JEŻELI (ABS (5$$$)<= 1200,1))

Wzór na utworzenie drugiej reguły przy użyciu koloru czerwonego jest następujący:

= LUB (JEŻELI (4 $ B $ = 1200,1))

Przenieś i usuń formatowanie warunkowe

Formatowanie warunkowe można skopiować i zastosować do innych lub nowych danych w arkuszu lub ponownie usunąć.

Przenieś formatowanie warunkowe

Aby skopiować formatowanie warunkowe i wkleić je do innych komórek lub zakresów komórek, użyj przycisku „Przenieś format”.

Zaznacz komórki z formatowaniem warunkowym, które chcesz skopiować i wkleić do innych komórek, i kliknij je.

Teraz przejdź do „Start”, a następnie „Format transferu”. Kursor pojawia się jako pędzel.

Możesz dodać formatowanie warunkowe do komórki lub zakresu komórek, przesuwając pędzel po komórce.

Dzięki ESC kończysz używanie pędzla.

Nadal możesz używać pędzla do wstawiania formatowania warunkowego, klikając dwukrotnie „Przenieś format”.

Alternatywnie formatowanie warunkowe można skopiować za pomocą skrótów klawiaturowych i wkleić do innej komórki programu Excel.

Wyczyść formatowanie warunkowe

Rozróżnia się proces usuwania w arkuszu i proces usuwania w zakresie komórek.

Wymaż w arkuszu lub w całym arkuszu

Przejdź do „Formatowanie warunkowe” na karcie „Strona główna”.

Następnie kliknij „usuń reguły”

Następnie kliknij „Usuń reguły w całym arkuszu”.

Alternatywnie możesz usunąć formatowanie warunkowe za pomocą okna dialogowego „Wybierz zawartość”.

Usuń w zakresie komórek

Zaznacz komórki zawierające odpowiednie formatowanie warunkowe.

Kliknij przycisk „Lupa do analizy odczytu” (przycisk znajduje się w prawym dolnym rogu obok wybranych danych).

Przejdź do „Usuń format”.

Znajdź i usuń te same formaty warunkowe w całym arkuszu

Kliknij komórkę z odpowiednim formatem warunkowym.

Na karcie „Strona główna” kliknij strzałkę obok „Znajdź i wybierz”, a następnie kliknij „Wybierz zawartość”.

Kliknij „Formaty warunkowe”.

W sekcji „Weryfikacja danych” przejdź do „Taka sama” (spowoduje to zaznaczenie wszystkich komórek z tym samym formatowaniem warunkowym).

Na karcie „Start” przejdź do „Formatowanie warunkowe”, następnie „Usuń reguły” i „Usuń reguły w wybranych komórkach”.

Formatowanie warunkowe w raporcie w formie tabeli przestawnej

Dzięki raportowi w formie tabeli przestawnej użytkownicy mogą szybko podsumować duże ilości danych. Zakres komórek, arkusz kalkulacyjny Excel lub raport w formie tabeli przestawnej - formatowanie warunkowe działa w zasadzie wszędzie tak samo. Tylko w raporcie w formie tabeli przestawnej znajduje się kilka specjalnych funkcji, o których należy pamiętać:

• Niektóre formaty warunkowe nie działają w polach z zakresu wartości raportu w formie tabeli przestawnej.
• Jeśli w jakikolwiek sposób zmienisz układ raportu w formie tabeli przestawnej, formatowanie warunkowe będzie obowiązywać do momentu usunięcia pól w danych źródłowych.
• Formatowanie warunkowe dla pól w zakresie wartości może być oparte na hierarchii danych. Zakres tego formatowania warunkowego jest określony przez następny niższy lub następny wyższy poziom hierarchii w wierszach dla co najmniej jednej kolumny lub w kolumnach dla co najmniej jednego wiersza.
• Zakres formatowania warunkowego dla pól z zakresu wartości można zdefiniować trzema metodami:

• • wybór
  • Odpowiednie pole
  • Pole wartości

Domyślną metodą jest selekcja.

Definicja obszaru przez wybór

Przejdź do „Zastosuj regułę formatowania do”.

Kliknij okno dialogowe Nowa reguła formatowania lub okno dialogowe Edytuj regułę formatowania.

Ta metoda jest odpowiednia dla ciągłej serii pól w zakresie wartości, np. B. dane ankietowe dla regionu, jak również dla nieciągłych serii pól w zakresie wartości, takich jak dane ankietowe dla różnych regionów na różnych poziomach hierarchii danych.

Definicja obszaru przez odpowiednie pole

Ta metoda działa, jeśli masz

• chcesz uniknąć konieczności wybierania wielu niepowiązanych elementów
• chcesz warunkowo sformatować kilka pól w zakresie wartości dla poziomu hierarchii danych
• Chcesz wykluczyć częściowe wyniki

Definicja obszaru za pomocą pola wartości

Ta metoda działa, jeśli masz

• chcesz uniknąć konieczności wybierania wielu niepowiązanych elementów
• chcesz warunkowo sformatować kilka pól w zakresie wartości dla wszystkich poziomów hierarchii
• Chcesz uwzględnić sumy i wyniki częściowe.

Wniosek: formatowanie warunkowe w programie Excel ułatwia śledzenie danych

Jeśli chcesz wyróżnić określone wartości danych w tabeli programu Excel w kolorze, formatowanie warunkowe w programie Excel jest idealne. Masz dwie możliwości zastosowania formatowania warunkowego. Albo użyjesz reguł dostarczonych przez Excel, aby podświetlić zawartość tekstu, wartości liczbowe lub daty w kolorze. Możesz też samodzielnie zdefiniować regułę z różnymi warunkami, pod którymi mają być oznaczane odpowiednie wartości. Możesz łatwo i efektywnie porównywać dane ze sobą za pomocą pasków danych, pasków kolorów i zestawów symboli. Jeśli opcje projektu nie są już potrzebne, możesz je usunąć kilkoma kliknięciami.

Więcej artykułów na temat formatowania warunkowego

• Jak automatycznie generować strzałki na listach Excel
• Wyświetlaj ujemne wartości czasu w tabelach Excel
• Kolor ujemnych procentów czerwony
• Rozpoznaj ujemne wartości procentowe na pierwszy rzut oka
• Formatuj liczby z niestandardowym formatem w kolorze
• Formatuj komórki Excela, które nie mają określonej wartości
• Automatycznie przechowuj komórki z odpowiednimi formatami walut
• Zaznacz komórki w kolorze za pomocą funkcji makra
• Użyj formatowania warunkowego i podświetl dane, które są większe lub równe trzeciej co do wielkości komórce
• Odwrotne wartości prawdy