6 stycznia 2021
Formatowanie warunkowe z formułą w Excelu
Formatowanie warunkowe całego wiersza Możesz chcesz sformatować cały wiersz w tabeli, na podstawie wartości tylko… Czytaj więcej
Excel to gąszcz liczb i wyrazów (głównie jednak liczb), który może przyprawić o ból głowy. Świetnym sposobem na to, żeby był bardziej przejrzysty jest używanie kolorów. Pod przeróżnymi postaciami:
Te wszystkie zmiany kolorów i wyglądu komórki, nazywamy formatowaniem.
Czym w takim razie jest formatowanie warunkowe?
Jest to wstawienie tych kolorów tak, żeby pojawiały się tylko czasami i żeby ich pojawienie się informowało nas o czymś ważnym.
Możesz wstawić formatowanie warunkowe, które będzie kolorowało na zielono komórki ze słowem „Tak”, a na czerwono ze słowem „Nie”. Możesz wstawić czerwone obramowanie do komórek, gdzie jest liczba mniejsza niż 1.000. Możesz zmienić kolor czcionki na szary, w komórkach gdzie zapisane jest słowo „nieaktywny”. Przykłady można mnożyć w nieskończoność, ale myślę, że rozumiesz o co chodzi. W takim razie, przejdźmy głębiej do tematu formatowania warunkowego w Excelu.
Wspomniałem o tym już w sekcji powyżej, ale teraz rozwinę temat. Zacznę od ogółu i przejdę do szczegółu. W Excelu, formatowanie warunkowe przydaje się do
Wyróżnienia automatycznie, w sposób wizualny, ważnych dla Ciebie informacji.
Oto tłumaczenie powyższego zdania:
Jako, że główną zaletą formatowania warunkowego jest to, że automatycznie się włącza lub wyłącza (wstawia kolor lub nie), to używamy go właśnie wtedy. Gdyby się nie zmieniała, to moglibyśmy na stałe wstawić konkretny kolor.
Gdy zawartość się zmienia, to format warunkowy nas o tym informuje (pokazując odpowiedni kolor). Przykłady:
Mamy w tabeli kolumnę ze statusem (zlecenia, produkcji, rekrutacji, itp.). Gdy status zmieniamy, np. z „oczekujący” na „zrealizowany”, to wtedy zmienia się kolor z żółtego na zielony. Dzięki temu, „rzuca się w oczy” które elementy w tabeli są już zrealizowane.
Rysunek 1. Statusy, które mogą się zmieniać.
Mamy kolumnę z datami, kiedy należy zapłacić za faktury. Jeśli data przypada w obecnym tygodniu, to komórka koloruje się na pomarańczowo. Przypomina nam, żeby zapłacić fakturę. Codziennie, gdy otwierasz plik Excela, to data jest inna i w ten sposób format warunkowy codziennie się „aktualizuje” i może kolorować inne komórki.
Rysunek 2. Formatowanie dat z obecnego tygodnia.
Druga sytuacja, gdy format warunkowy przydaje się w Excelu, to gdy masz wiele komórek i chcesz pokolorować je w stały sposób, ale niektóre z nich mają być np. niebieskie, inne zielone, inne czerwone. Wówczas szukanie, zaznaczanie i wstawianie koloru ręcznie może być czasochłonne, w przeciwieństwie do użycia formatowania warunkowego. Przykłady:
Mamy w tabeli kolumnę ze statusami. Nie będą one się zmieniały. W tabeli jest jednak 10.000 wierszy i chcemy w szybki sposób każdy ze statusów sformatować innym kolorem, żeby łatwo je od siebie odróżniać. Użyjemy formatowania warunkowego, tak że Excel sam będzie odróżnił statusy i sam będzie wstawiał kolor. Można by tu użyć także sortowania, aby ułatwić sobie pracę, ale czasem nie chcemy sortować tabeli, żeby nie stracić oryginalnej kolejności.
Rysunek 3. Szybkie formatowanie, gdy danych jest dużo.
Mamy w tabeli kolumnę z wielkością zamówienia. Jeśli jest ono większe niż 1.000 sztuk, to komórka koloruje się na żółto. W ten sposób „rzucają nam się w oczy” bardzo duże zamówienia i może wtedy poświęcić im nieco więcej uwagi.
Rysunek 4. Wyróżnienie dużych liczb.
Mógłbym wymieniać więcej sytuacji, gdy formatowanie warunkowe jest przydatne w Excelu, ale ważne jest, żeby zrozumieć te najpopularniejsze przypadki. Potem zaczniesz samodzielnie zauważać coraz to nowsze zastosowania.
Formatowanie warunkowe to jedno z moich ulubionych narzędzi w Excelu. Dlaczego? Dlatego, że jest bardzo proste w zastosowaniu i jednocześnie bardzo zwiększa przejrzystość arkusza. Powiem Ci teraz o kilku prostych zasadach, które warto znać.
Jeśli chcesz użyć formatu warunkowego, to najpierw zaznaczasz komórki, w którym ma się znaleźć – po prostu. Jeśli jakichś komórek nie zaznaczysz, to tam na pewno format się nie pojawi.
Gdy raz wstawisz formatowanie warunkowe (zakładam, że w prawidłowy sposób), to potem nie musisz z nim nic robić. Gdy zawartość komórek się zmienia, to kolor też odpowiednio się zmienia – nie musisz nic dodatkowo klikać, ani odświeżać.
Gdy chcesz w kolumnie mieć zarówno kolor zielony (np. dla słowa „Tak”), jak i czerwony (np. dla słowa „Nie”), to musisz wstawić dwa razy formatowanie warunkowego. Jedno będzie wprowadzać kolor zielony, a drugie czerwony. Jedno formatowanie warunkowe, może wstawić tylko jeden format.
Przejdę teraz do konkretu, czyli jak wstawić formatowanie warunkowe, gdy w komórce znajduje się jakieś słowo, np. „Tak”. Trochę teorii opisałem już wcześniej, więc teraz konkretne kroki:
Rysunek 5. Formatowanie warunkowe w menu Excela i opcja Równe
Jeśli przyjrzysz się liście Reguły wyróżniania komórek (po przejściu Narzędzia główne – Formatowanie warunkowe – Reguły wyróżniania komórek), to znajdziesz tam najbardziej przydatne opcje. Najczęściej z nich będziesz korzystać.
Jeśli chcesz w podobny sposób pokolorować komórki z konkretną liczbą, to po prostu zamiast tekstu wpisz liczbę, której szukasz.
W takiej sytuacji, działasz prawie tak samo, jak wtedy, gdy szukasz konkretnego tekstu/liczby, czyli w przypadku opcji Równe. Powiedzmy, że chcesz sformatować komórki, gdzie liczba jest większa niż 200:
Tak samo działasz, gdy szukasz liczb mniejszych od innej, wskazanej przez Ciebie.
Rysunek 6. Opcja „Większe niż” działa z liczbami i z datami.
Możesz sprawić, aby Twoje formatowanie warunkowe było jeszcze bardziej inteligentne i automatyczne. Dzięki temu, Twoja praca będzie łatwiejsza. Gdy chcesz wskazać jaki tekst lub liczba mają być kolorowane, to zamiast zapisywać ją ręcznie w oknie formatowania warunkowego, możesz wprowadzić ją do komórki, a to ułatwi Ci pracę. Przejdźmy przez ten proces po kolei:
Rysunek 7. Wskazanie komórki Excela jako wartości do porównania.
Teraz, gdy zmienisz wartość w tej wskazanej komórce, to formatowanie warunkowe automatycznie się zmienia. Jeśli w komórce było słowo „oczekujący”, to takie komórki były kolorowane. Jeśli w komórce zmienić słowo na „zrealizowany”, to teraz takie komórki będą kolorowane. Ułatwia to „sterowanie” formatem warunkowym, gdy Twoje potrzeby się zmieniają.
Rysunek 8. Po zmianie słowa na „Tak”, komórki ze słowem „Tak” są kolorowane.
Innym, popularnym przykładem, gdzie w Excelu przydaje się formatowanie warunkowe, jest kolorowanie największych wartości spośród zaznaczonych. W tym przypadku nie masz żadnego progu, powyżej którego chcesz formatować. Chcesz po prostu pokolorować 5 największych wartości w kolumnie. Żeby to zrobić, przejdź do Narzędzia główne – Formatowanie warunkowe – Reguły pierwszych/ostatnich. Tutaj znajduje się opcja 10 pierwszych elementów. Wybierz ją i w oknie wybierz ile największych elementów chcesz pokolorować – ja wspomniałem wyżej o pięciu. Oczywiście wskaż ten kolor.
Rysunek 9. Wyróżnianie największych i najmniejszych wartości.
W analogiczny sposób, możesz pokolorować najmniejsze wartości – wybierz opcję 10 ostatnich elementów.
Możesz wskazywać liczbę największych/najmniejszych komórek, tak jak opisałem to w poprzedniej sekcji. Możesz powiedzieć Excelowi, żeby formatował jakiś procent największych/najmniejszych wartości. Przykładowo, jeśli wskaże 1%, a zaznaczonych komórek będzie 1.000, to Excel pokoloruje 10 z nich.
Przejdź do Narzędzia główne – Formatowanie warunkowe – Reguły pierwszych/ostatnich. Wybierz Pierwsze 10% albo Ostatnie 10%. Liczba, którą wpiszesz w oknie, będzie oznaczała procent – jeśli chcesz pokolorować 1% komórek, to wpisz po prostu 1.
Rysunek 10. 20% największych wartości w kolumnie.
Poniżej wymieniam kilka innych opcji, z których użytkownicy Excela dość często korzystają i które mogą przydać się również Tobie.
Rysunek 11. Inne przydatne narzędzia formatowania warunkowego w Excelu.
Jeśli chcesz wyróżnić powtarzające się teksty/liczby, to możesz to zrobić właśnie formatowaniem warunkowym. Przejdź do Narzędzia główne – Formatowanie warunkowe – Reguły wyróżniania komórek i wybierz Duplikujące się wartości.
Jeśli potrzebujesz szczegółowych informacji, to wejdź do artykułu na temat formatowania warunkowego duplikatów.
Możesz szukasz komórek, które zawierają jakiś charakterystyczny fragment tekstu. Przykładowo, masz kolumnę pełną adresów (ulica, numer, kod pocztowy, miasto) i chcesz wyróżnić adresy z Katowic. Wówczas nie szukasz komórek, który są równe „Katowice”, ale w których zawiera się gdzieś słowo „Katowice”.
Przejdź do Narzędzia główne – Formatowanie warunkowe – Reguły wyróżniania komórek i wybierz Tekst zawierający.
Więcej informacji o bardziej skomplikowanych formatowaniach warunkowych z tekstami, znajdziesz u nas w osobnym wpisie.
Możesz formatować komórki z datami ze wskazanego okresu w stosunku do aktualnej daty. Excel wie jaka jest dzisiaj data. Wie, że jeśli otworzysz plik jutro, to jest o jeden dzień później. Na tej podstawie, jest w stanie kolorować np. komórki z dzisiejszą datą, albo komórki z przyszłego tygodnia, albo komórki z datą z obecnego miesiąca, itp.
Przejdź do Narzędzia główne – Formatowanie warunkowe – Reguły wyróżniania komórek i wybierz Data występująca.
W osobnym artykule znajdziesz więcej informacji o posługiwaniu się datami w formatowaniu warunkowym.
Do tej pory mówiłem o różnych warunkach i przykładach formatowania warunkowego, ale zawsze efektem było wstawienie koloru. Koloru tła w komórce, ewentualnie obramowania, koloru czcionki, czyli typowych elementów formatowania, które możesz w Excelu zmieniać.
Istnieją specjalne sposoby wyróżniania danych w Excelu, których możesz użyć tylko w formatowaniu warunkowym.
Rysunek 12. Specjalne grafiki formatowania warunkowego.
Są to takie „mini wykresy”, które mieszczą się w pojedynczej komórce. Jeśli masz wiele komórek z liczbami, to w każdej z nich pojawi się taki pasek danych. Im liczba jest większa, tym pasek jest dłuższy. W ten sposób, długość paska obrazuje wielkość liczby, która znajduje się w tej komórce.
Rysunek 13. Paski danych.
Aby użyć pasków danych, najpierw zaznacz komórki, a potem przejdź do narzędzi formatu warunkowego i wybierz Paski danych. Z listy wybierz taki kolor, jaki Ci odpowiada.
Nie będę wchodzi w tym artykule w szczegóły, ale jeśli potrzebujesz dodatkowych wyjaśnień to zapraszam Cię do osobnego wpisu na naszym poradniku, właśnie na temat pasków danych w formatowaniu warunkowym.
Używa się jej w podobnych sytuacjach, jak paski danych – czyli, gdy chcesz zobrazować, dla wielu komórek, jak dużo są w nich liczby. Skala kolorów, to zestaw dwóch lub trzech barw. Jeśli wybiorę skalę czerwono-zieloną, to najmniejsze z zaznaczonych komórek będą intensywnie czerwone, a największe będą intensywnie zielone. Wszystkie wartości pomiędzy, będą miały inne kolor, mniej intensywne, gdzieś pomiędzy czerwonym, a zielonym – im bardziej zielone, tym liczby są większe.
Rysunek 14. Skala kolorów w Excelu.
W osobnym wpisie przeczytasz więcej na temat skali kolorów w Excelu.
Trzecim z tych dodatkowych narzędzi, które udostępnia nam Excel, są zestawy ikon. Możesz wybrać np. zestaw składający się z trzech ikon: kółko zielone, kółko żółte, kółko czerwone. Za pomocą formatowania warunkowego, przypiszesz zielone kółko dwudziestu procentom największych komórek. Żółte będzie wyświetlać się przy sześćdziesięciu środkowych procentach komórek. Czerwone będzie przy 20% najmniejszych liczb.
Rysunek 15. Zestawy ikon w formatowaniu warunkowym.
Przykładowe ustawienia, które opisałem wyżej, możesz różnie zmieniać, co szczegółowo wyjaśniam w artykule o zestawach ikon w Excelu.
Czemu mam ich szukać? Bo czasami chcemy je po prostu zmienić, albo usunąć. Czasem chcemy sprawdzić jak działają, bo widzimy że w arkuszu Excela pojawiają się kolory, ale nie rozumiemy dlaczego akurat wtedy.
Formaty warunkowe można w Excelu znaleźć w bardzo prosty sposób, bo jest dla nich stworzony specjalny manager. Przejdź do Narzędzi głównych – Formatowanie warunkowe i wybierz Zarządzaj regułami.
Rysunek 16. Gdzie znaleźć managera formatów warunkowych w Excelu.
W tym oknie masz listę stworzonych wcześniej formatów warunkowych. Może zdarzyć się tak, że nie widać na niej nic, choć wiesz, że w arkuszu są formaty. Wynika to z tego, że domyślnie, okno pokazuje formaty tylko z komórek, które obecnie są zaznaczone. Więc to, co zaznaczysz przed otwarciem tego okna, jest niejako filtrem. Dlaczego tak to działa? Bo możesz mieć w arkuszu wiele formatów warunkowych i w ten sposób filtrujesz je, żeby zobaczyć tylko te potrzebne.
Rysunek 17. Okno zarządzania formatowaniem warunkowym.
Jeśli chcesz zobaczyć formaty warunkowe z całego arkusza, to w tym oknie zmień opcję Bieżące zaznaczenie na Ten arkusz. Nie możesz zobaczyć jednocześnie formatów z całego pliku Excela – jedynie z jednego arkusza jednocześnie (ale nie uważam, żeby był to problem).
Gdy już znajdziesz na liście odpowiedni format warunkowy, to możesz go usunąć, naciskając przycisk Usuń regułę. Możesz go także edytować, co opisuję dokładniej w sekcji poniżej.
Gdy już znalazłeś/aś format warunkowy, to możesz go zmienić na jeden z trzech sposobów:
Rysunek 18. Okno, które otwiera się pod przyciskiem „Edytuj regułę”.
Rysunek 19. Miejsce, w którym zmieniasz zakres komórek dla formatowania warunkowego.
Tak jak pisałem wcześniej, jeśli chcesz używać wielu kolorów, to potrzebujesz mieć wiele formatów warunkowych. Jeden format to tylko jeden kolor. Może się więc zdarzyć, że w jednej kolumnie będziesz mieć kilka formatów, czyli będą one na siebie „nałożone”.
Czy ich „nakładanie się” to problem? Nie, ale warto wiedzieć, co się wtedy dzieje.
Nawet się nakładają się na siebie, to ich reguły mogą się nie pokrywać. Przykład: zielony kolor dla słowa „Tak”, czerwony kolor dla słowa „Nie”. W takiej sytuacji nie jest możliwe, żeby Excel chciał jednocześnie użyć i koloru zielonego i koloru czerwonego, bo ich reguły się wzajemnie wykluczają. Nie ma wówczas nad czym się zastanawiać – nic nie trzeba zmieniać.
Może być tak, że reguły formatów warunkowych pokrywają się. Przykład: koloruj na niebiesko liczby większe niż 100, koloruj na zielono liczby większe niż 200. Co się stanie jeśli w komórce będzie liczba 230? Oba formaty powinny się włączyć. I technicznie biorąc tak się dzieje. Jednak komórka nie może być jednocześnie i zielona i niebieska, więc jaki będzie efekt. Widoczny będzie ten kolor, który na liście formatów warunkowy (okno Zarządzaj regułami) jest wyżej na liście. Po prostu.
Rysunek 20. Priorytety formatów. Ten, który jest wyżej na liście, jest ważniejszy.
Jeśli chcesz zmienić ich kolejność, to zaznacz jeden z nich i naciskach na strzałkach skierowanych w górę lub w dół.
Formatowanie warunkowe całego wiersza Możesz chcesz sformatować cały wiersz w tabeli, na podstawie wartości tylko… Czytaj więcej
Tekst zawierający – to opcja formatowania warunkowego, które sprawdza teksty w komórkach. Jeśli potrzebujesz poznać… Czytaj więcej
Formatowanie warunkowe pozwala w Excelu na ciekawe zobrazowanie zawartości komórek – nie jesteśmy ograniczeni do… Czytaj więcej
SPIS TREŚCI Kiedy formatować cały wiersz Zacznijmy od tego, w jakich sytuacjach będziemy formatować cały… Czytaj więcej
Dzięki uzależnieniu formatowania warunkowego od innej komórki, zmieniając jej zawartość, szybko zmieniamy wygląd innych komórek…. Czytaj więcej
Często zdarza się, że w naszych plikach Excela pewne dane się powtarzają. Czy to źle?… Czytaj więcej