Formatowanie warunkowe pozwala wyróżnić ważne dane i zastosować określone formatowanie do komórek spełniających określone kryteria.
Formatowanie warunkowe programu Excel to potężna funkcja, która umożliwia zmianę formatu komórek na podstawie warunku (lub kryteriów). Formatowanie warunkowe pomaga wyróżnić, uwydatnić lub rozróżnić dane przechowywane w arkuszu kalkulacyjnym Excel.
Wszystkie reguły formatowania warunkowego opierają się na prostej logice: jeśli warunki są spełnione, zastosowane zostanie określone formatowanie; jeśli warunki są fałszywe, formatowanie nie zostanie zastosowane.
Formatowanie warunkowe pomaga wyróżnić ważne dane w zestawie danych, uwydatnić anomalie i wizualizować dane za pomocą pasków danych, kolorów i zestawów ikon. W tym samouczku dowiemy się, jak zastosować formatowanie warunkowe do wyróżniania komórek lub zakresów komórek na podstawie warunku.
Jak zastosować formatowanie warunkowe w programie Excel
Na przykład jesteś odpowiedzialny za zarządzanie dużą listą zapasów z kilkoma pozycjami i ich odpowiednią ilością w magazynie. A jeśli ilość danego artykułu na magazynie spadnie poniżej, powiedzmy 50, ważne jest, aby to wiedzieć, aby móc go uzupełnić.
Jeśli ta lista inwentaryzacyjna zawiera zestaw danych z setkami wierszy, przeszukiwanie wiersza po wierszu w celu sprawdzenia, czy w tej kolumnie są liczby poniżej „50”, nie jest zbyt efektywne. Tutaj przydaje się funkcja formatowania warunkowego. Za pomocą kilku kliknięć myszką możesz podświetlić wszystkie wartości w kolumnie, która jest mniejsza niż „50”.
Formatowanie warunkowe z regułami podświetlania
W naszym przykładzie mamy arkusz roboczy zawierający rekordy sprzedaży niektórych produktów. chcemy podkreślić kwotę, która jest mniejsza niż 500 w sprzedaży.
Aby to zrobić, najpierw wybierz zakres komórek, do którego chcesz zastosować regułę (warunek). W naszym przykładzie chcemy podświetlić kwotę mniejszą niż 500 w kolumnie „Kwota”, więc wybierz kolumnę D. Możesz podświetlić wartości w zakresie komórek lub wielu zakresach komórek, a nawet w całym arkuszu.
Następnie przejdź do zakładki „Strona główna” i kliknij „Formatowanie warunkowe”. Wybierz „Wyróżnij reguły komórek” z menu rozwijanego, a ponieważ chcemy znaleźć mniej niż 500 wartości, kliknij opcję „Mniej niż”. Excel oferuje siedem gotowych reguł podświetlania. Możesz użyć dowolnego z nich, aby wyróżnić dane.
Następnie pojawi się okno dialogowe „Mniej niż”. W tym celu wpisz „500” w polu „Formatuj komórki, które są MNIEJSZE NIŻ” i wybierz formatowanie dla podświetlenia w rozwijanym obok niego.
Teraz komórki, które zawierają mniej niż 500 w swoich wartościach, zostaną podświetlone w wybranym formatowaniu.
Zobaczmy, jak wyróżnić wartości zawierające określony ciąg tekstowy. W poniższym przykładzie chcemy wyróżnić wszystkich pracowników z Nowej Południowej Walii (NSW).
W tym celu przejdź do zakładki Strona główna –>Formatowanie warunkowe –>Zasady podświetlania komórek –>Tekst, który zawiera.
Możesz użyć innych opcji w poziomym menu rozwijanym „Wyróżniaj reguły komórek”, aby podświetlić wartości zduplikowane, daty, większe niż, równe lub między wartościami.
W oknie dialogowym Tekst zawierający wpisz „NSW” w polu i wybierz formatowanie, a następnie kliknij „OK”.
Wynik:
Formatowanie warunkowe z regułami góra/dół
Reguły górne / dolne to kolejne pomocne wbudowane wstępnie ustawione reguły formatowania warunkowego dostępne w programie Excel. Reguły te umożliwiają zwrócenie uwagi na najwyższą (n) liczbę elementów, górną (n) liczbę procent, dolną (n) liczbę elementów, dolną (n) liczbę procent lub wartości komórek, które są powyżej średnia lub poniżej średniej.
Załóżmy, że masz w arkuszu kalkulacyjnym wyniki oceny ucznia i chcesz znaleźć 10 najlepszych wykonawców (10 najlepszych rang) z tej listy. Dzięki formatowaniu warunkowemu możesz wyróżnić 10 pierwszych lub 15 pierwszych lub dowolną (n) liczbę najważniejszych elementów. Aby wyróżnić Top 10 elementów w zakresie komórek, najpierw wybierz zakres (Łącznie) w tabeli.
Następnie przejdź do „Formatowanie warunkowe”, rozwiń „Reguły górne/dolne” i wybierz opcję „Top 10 pozycji...”.
W oknie dialogowym „Top 10 items” zmień liczbę rang za pomocą małych strzałek w lewym polu. Jeśli chcesz wyróżnić 20 najwyższych rang (łączna ocena) na liście ocen, ustaw liczbę na 20. Wartość domyślna to już 10, więc ją zachowujemy. Wybierz formatowanie komórek w prawym polu i kliknij „OK”
10 górnych znaków z kolumny „Łącznie” jest podświetlonych, jak pokazano poniżej.
Załóżmy, że chcesz znaleźć oceny poniżej średniej w kolumnie „Egzamin 1” w tym samym arkuszu ocen. W tym celu przejdź do zakładki Strona główna –>Formatowanie warunkowe –>Reguły góra/dół –>Poniżej średniej.
Możesz ustawić własne formatowanie warunkowe zamiast wybierać jeden z predefiniowanych warunków. Aby utworzyć własne formatowanie, wybierz opcję „Format niestandardowy” w oknie „Poniżej średniej”.
Otworzy się nowe okno Formatuj komórki, w którym możesz dostosować swoje komórki formatowania. Wybieramy kolor pomarańczowy, aby podkreślić znaki poniżej średniej. Po zakończeniu kliknij dwukrotnie „OK”, aby zobaczyć wynik.
Wynik:
Zastosuj paski danych
Paski danych to po prostu poziome paski w twoich komórkach. Rozmiar paska jest zależny od wartości komórki odpowiadającej wartości innych komórek w wybranym zakresie. Oznacza to, że wartość krótkiego paska jest niska w porównaniu z innymi wartościami komórek, a długi pasek oznacza, że wartość jest wysoka w porównaniu z innymi wartościami komórek.
Najpierw wybierz zakres komórek, które chcesz zwizualizować za pomocą pasków danych.
Następnie przejdź do „Formatowanie warunkowe” na karcie „Strona główna”, a następnie rozwiń „Paski danych” w menu rozwijanym i wybierz pasek danych.
Różnica między formatem paska danych a innymi formatami polega na tym, że wyświetla się we wszystkich komórkach, zamiast spełniać określony warunek.
Zastosuj skale kolorów
Skale kolorów są bardzo podobne do pasków danych, ponieważ obie korelują wartość pojedynczej komórki z wartością innych komórek w wybranym zakresie. Jednak paski danych wizualizują wartość komórki według długości paska, podczas gdy skale kolorów robią to za pomocą gradientów kolorów.
Każda opcja skali kolorów wykorzystuje dwu- lub trzykolorowe wzory gradientu. Jeden kolor jest przydzielany do najwyższych wartości, inny kolor jest przydzielany do najniższych wartości, a wszystkie inne wartości pośrednie tworzą mieszankę tych dwóch kolorów.
W tym celu użyjemy tego samego przykładu, którego użyliśmy dla pasków danych. Wybierz zakres komórek, przejdź do Strona główna -> Formatowanie warunkowe -> Skale kolorów. Następnie wybierz zakres kolorów z poziomego menu rozwijanego Skale kolorów.
Kiedy wybieramy pierwszy zakres kolorów, kolor czerwony jest przydzielany do najniższej wartości, kolor zielony jest przydzielany do najwyższej wartości, a wszystkie wartości pomiędzy są kolorami przydzielonymi, które są mieszanką kolorów czerwonego i zielonego (jak pokazano poniżej). Ponieważ kolor żółty powstaje przez połączenie czerwonego i zielonego o równym natężeniu, są do niego przypisywane komórki o wartościach średnich.
Zastosuj zestawy ikon
Zestawy ikon to kolejna metoda wizualizacji danych w każdej komórce i skorelowania komórek ze sobą.
Zaznacz komórki i kliknij Strona główna -> Formatowanie warunkowe -> Zestawy ikon. Możesz wybrać dowolny z tych zestawów ikon, które są bardziej odpowiednie dla Twoich potrzeb. W naszym przykładzie wybierzemy pierwszą opcję w obszarze Kierunkowe.
Czerwone, żółte i zielone strzałki, które oznaczają odpowiednio produkty o niskiej, średniej lub wysokiej cenie.
Usuń formatowanie warunkowe
Aby usunąć formatowanie warunkowe, wróć do opcji „Formatowanie warunkowe” w zakładce „Strona główna” i kliknij „Wyczyść reguły”. Następnie wybierz reguły, które chcesz wyczyścić.
Wybierzemy „Wyczyść reguły z całego arkusza”, aby usunąć całe formatowanie warunkowe z arkusza roboczego.
Teraz nauczyłeś się używać formatowania warunkowego do formatowania komórek za pomocą wstępnie ustawionego warunku programu Excel.