Formatowanie warunkowe w Excelu – szczegółowo z przykładami

Jeśli chcesz wizualnie przedstawić informacje, bardzo wygodnie jest to zrobić za pomocą formatowania warunkowego. Użytkownik korzystający z tej usługi oszczędza ogromną ilość energii i czasu. Aby uzyskać niezbędne informacje, wystarczy szybki podgląd pliku.

Jak wykonać formatowanie warunkowe w programie Excel

Możesz znaleźć "Formatowanie warunkowe" na pierwszej karcie wstążki, przechodząc do sekcji "Style". 

Następnie musisz znaleźć ikonę strzałki nieco po prawej stronie, przesuń do niej kursor. Następnie otworzą się ustawienia, w których możesz elastycznie skonfigurować wszystkie niezbędne parametry. 

Następnie należy wybrać odpowiedniego operatora, aby porównać żądaną zmienną z liczbą. Istnieją cztery operatory porównania — większy niż, mniejszy niż, równy i pomiędzy. Są one wymienione w menu zasad. 

Następnie określiliśmy serię liczb z zakresu A1:A11.

Sformatujmy odpowiedni zestaw danych. Następnie otwieramy menu ustawień „Formatowanie warunkowe” i ustawiamy wymagane kryteria wyboru danych. W naszym przypadku wybierzemy np. kryterium „Więcej”. 

Otworzy się okno z zestawem opcji. W lewej części okna znajduje się pole, w którym należy podać liczbę 15. W prawej części wskazany jest sposób podświetlania informacji, pod warunkiem, że spełnia określone wcześniej kryterium. Wynik zostanie wyświetlony natychmiast.

Następnie zakończ konfigurację przyciskiem OK.

Do czego służy formatowanie warunkowe?

Do pewnego stopnia można powiedzieć, że jest to narzędzie, po poznaniu którego praca z Excelem już nigdy nie będzie taka sama. To dlatego, że znacznie ułatwia życie. Zamiast ręcznie ustawiać formatowanie komórki, która za każdym razem pasuje do określonego warunku, a także próbować samodzielnie sprawdzić zgodność z tym kryterium, wystarczy raz ustawić ustawienia, a wtedy Excel zrobi wszystko sam.

Na przykład możesz sprawić, że wszystkie komórki zawierające liczbę większą niż 100 zmienią kolor na czerwony. Lub określ, ile dni pozostało do następnej płatności, a następnie pokoloruj na zielono te komórki, w których termin jeszcze nie jest wystarczająco odległy. 

Weźmy przykład.

Ta tabela pokazuje zapasy, które są w magazynie. Ma kolumny. jako produkt, średnia sprzedaż (mierzona w jednostkach na tydzień), zapasy i ile tygodni pozostało tego produktu.

Ponadto zadaniem kierownika zakupów jest określenie tych stanowisk, których uzupełnienie jest konieczne. Aby to zrobić, musisz spojrzeć na czwartą kolumnę od lewej, która rejestruje zapasy towarów według tygodnia.

Załóżmy, że kryterium do ustalenia przyczyny paniki jest mniej niż 3 tygodnie inwentaryzacji. Oznacza to, że musimy przygotować zamówienie. Jeśli zapas towarów jest krótszy niż dwa tygodnie, oznacza to konieczność pilnego złożenia zamówienia. Jeśli tabela zawiera ogromną liczbę pozycji, to ręczne sprawdzenie, ile pozostało tygodni, jest dość problematyczne. Nawet jeśli używasz wyszukiwania. A teraz zobaczmy, jak wygląda stół, który podkreśla deficytowe towary na czerwono.

I rzeczywiście, nawigacja jest znacznie łatwiejsza. 

To prawda, że ​​ten przykład ma charakter edukacyjny, co jest nieco uproszczone w porównaniu z rzeczywistym obrazem. A zaoszczędzone sekundy i minuty przy regularnym korzystaniu z takich tabel zamieniają się w godziny. Teraz wystarczy spojrzeć na tabelę, aby zrozumieć, jakich towarów brakuje, a nie spędzać godzin na analizowaniu każdej komórki (jeśli takich towarów są tysiące).

Jeśli są towary „żółte”, musisz zacząć je kupować. Jeśli odpowiednia pozycja jest czerwona, musisz to zrobić natychmiast. 

Według wartości innej komórki

Przyjrzyjmy się teraz następującemu praktycznemu przykładowi.

Załóżmy, że mamy taką tabelę i stajemy przed zadaniem podświetlenia wierszy zawierających określone wartości. 

Jeśli więc projekt jest nadal realizowany (to znaczy nie został jeszcze ukończony, dlatego jest oznaczony literą „P”), musimy ustawić jego tło na czerwono. Zrealizowane projekty są zaznaczone na zielono.

Nasza sekwencja działań w tej sytuacji będzie następująca:

1. Wybierz zakres wartości. 
2. Kliknij przycisk „Formatowanie warunkowe” – „Utwórz regułę”.
3. Należy pamiętać, że w naszym przypadku wzór należy zastosować w postaci reguły. Następnie używamy funkcji IFaby podświetlić pasujące linie. 

Następnie wypełnij linie, jak pokazano na tym obrazku.

Ważne! Do ciągów należy odwoływać się bezwzględnie. Jeśli odwołujemy się do komórki, to w tym przypadku jest ona mieszana (z utrwaleniem kolumny).

Podobnie tworzona jest reguła dla tych projektów pracy, które nie zostały do ​​tej pory ukończone. 

Tak wyglądają nasze kryteria.

I w końcu mamy taką bazę danych.

Wiele warunków

Dla jasności weźmy zakres A1:A11 z tabeli, aby zademonstrować, jak w prawdziwym praktycznym przykładzie można użyć kilku warunków do określenia formatowania komórki.

Same warunki są następujące: jeśli liczba w komórce przekracza wartość 6, jest podświetlona na czerwono. Jeśli jest zielony, to kolor jest zielony. I na koniec największe liczby, ponad 20, zostaną podświetlone na żółto. 

Istnieje kilka sposobów przeprowadzania formatowania warunkowego zgodnie z kilkoma regułami.

1. Metoda 1. Wybieramy nasz zakres, po czym w menu ustawień formatowania warunkowego wybierz taką regułę wyboru komórek, jak „Więcej”. Cyfra 6 jest zapisana po lewej stronie, a formatowanie jest ustawione po prawej stronie. W naszym przypadku wypełnienie powinno być czerwone. Następnie cykl powtarza się dwukrotnie, ale inne parametry są już ustawione – odpowiednio ponad 10 i kolor zielony oraz ponad 20 i kolor żółty. Otrzymasz taki wynik.
   

   

2. Metoda 2. Przejdź do głównego menu ustawień narzędzia „Formatowanie warunkowe” programu Excel. Tam znajduje się menu „Utwórz regułę” i kliknij ten element lewym przyciskiem myszy.
   

   

    Następnie wybierz pozycję „Użyj formuły…” (podświetloną na rysunku czerwoną ramką) i ustaw pierwszy warunek. Następnie kliknij OK. A następnie cykl powtarza się dla kolejnych warunków w taki sam sposób, jak opisano powyżej, ale stosuje się wzór.

   

Ważne jest, aby wziąć to pod uwagę. W naszym przykładzie niektóre komórki spełniają jednocześnie kilka kryteriów. Program Excel rozwiązuje ten konflikt w następujący sposób: powyższa reguła jest stosowana jako pierwsza. 

A oto zrzut ekranu dla lepszego zrozumienia.

Na przykład mamy liczbę 24. Spełnia ona jednocześnie wszystkie trzy warunki. W takim przypadku pojawi się wypełnienie spełniające pierwszy warunek, mimo że mieści się bardziej pod trzecim. Dlatego jeśli ważne jest, aby liczby powyżej 20 były wypełnione kolorem żółtym, trzeci warunek należy postawić na pierwszym miejscu. 

Warunkowe formatowanie daty

Zobaczmy teraz, jak formatowanie warunkowe działa z datami. Mamy taki fajny asortyment.

W takim przypadku musisz ustawić regułę formatowania warunkowego, taką jak „Data”.

Następnie otworzy się okno dialogowe z całym zestawem warunków. Możesz się z nimi szczegółowo zapoznać na tym zrzucie ekranu (są wymienione po lewej stronie ekranu w formie listy).

Wystarczy wybrać odpowiedni i kliknąć przycisk „OK”.

Widzimy, że te daty, które odnoszą się do ostatniego tygodnia w czasie rysowania tego zakresu, zostały podświetlone na czerwono. 

Korzystanie z formuł

Zestaw standardowych reguł formatowania warunkowego jest dość duży. Ale sytuacje są różne, a lista standardowa może nie wystarczyć. W takim przypadku możesz utworzyć własną regułę formatowania za pomocą formuły. 

Aby to zrobić, kliknij przycisk "Utwórz regułę" w menu formatowania warunkowego, a następnie wybierz element wyświetlany na zrzucie ekranu.

Wiersze (według wartości komórki)

Załóżmy, że musimy podświetlić wiersz zawierający komórkę o określonej wartości. W takim przypadku musimy wykonać tę samą sekwencję działań, co powyżej, ale dla ciągów. Oznacza to, że możesz wybrać zakres, do którego ma obowiązywać formatowanie warunkowe. 

Jak stworzyć regułę

Aby utworzyć regułę, musisz użyć odpowiedniej opcji w sekcji „Formatowanie warunkowe”. Przyjrzyjmy się określonej sekwencji działań. 

Najpierw musisz znaleźć element „Formatowanie warunkowe” na wstążce na karcie „Strona główna”. Można go rozpoznać po charakterystycznych wielokolorowych komórkach o różnych rozmiarach, obok których znajduje się obrazek z przekreślonym znakiem równości. 

Jest przycisk „Utwórz regułę”. Możesz także kliknąć przycisk "Zarządzaj regułami", po czym otworzy się okno dialogowe, za pomocą którego możesz również utworzyć regułę, klikając odpowiedni przycisk w lewym górnym rogu. 

Możesz również zobaczyć w tym oknie te zasady, które zostały już zastosowane.

Zasady wyboru komórek

No dobrze, dużo mówimy o tym, jak tworzyć reguły, ale co to jest? Reguła wyboru komórek odnosi się do funkcji, które są brane pod uwagę przez program, aby zdecydować, jak sformatować komórki, które do nich pasują. Na przykład warunki mogą być większe niż pewna liczba, mniejsze niż pewna liczba, formuła i tak dalej. Jest ich cały zestaw. Możesz się z nimi zapoznać i przećwiczyć ich użycie „w piaskownicy”. 

Formatowanie wszystkich komórek na podstawie ich wartości

Osoba może samodzielnie ustawić zakres: całą linię, cały dokument lub pojedynczą komórkę. Program może skupić się tylko na wartości jednej komórki. Zwiększa to elastyczność procesu. 

Formatuj tylko unikalne lub zduplikowane komórki

Istnieje wiele reguł bezpośrednio podłączonych do programu. Możesz się z nimi zapoznać w menu „Formatowanie warunkowe”. W szczególności, aby formatowanie dotyczyło wyłącznie zduplikowanych wartości lub tylko unikalnych, istnieje specjalna opcja – „Zduplikowane wartości”.

Jeśli wybierzesz tę pozycję, otworzy się okno z ustawieniami, w którym możesz również wybrać przeciwną opcję – wybierz tylko unikalne wartości. 

Formatuj wartości w zakresie poniżej i powyżej średniej

Aby sformatować wartości poniżej lub powyżej średniej, w tym samym menu znajduje się również specjalna opcja. Musisz jednak wybrać inne podmenu – „Zasady wyboru pierwszej i ostatniej wartości”. 

Formatowanie tylko pierwszej i ostatniej wartości

W tym samym podmenu możliwe jest podświetlenie tylko pierwszej i ostatniej wartości specjalnym kolorem, czcionką i innymi metodami formatowania. Zgodnie ze standardem istnieje możliwość wybrania pierwszych i ostatnich dziesięciu elementów, a także 10% całkowitej liczby komórek zawartych w tym zakresie. Ale użytkownik może samodzielnie określić, ile komórek musi wybrać.

Formatowanie tylko komórek o określonej zawartości

Aby sformatować tylko komórki z określoną zawartością, musisz wybrać regułę formatowania Równe lub Tekst zawiera. Różnica między nimi polega na tym, że w pierwszym przypadku ciąg musi całkowicie odpowiadać kryterium, a w drugim tylko częściowo. 

Jak widać, formatowanie warunkowe to wielofunkcyjna funkcja programu Excel, która daje ogromną liczbę korzyści osobie, która ją opanowała. Wiemy, że na pierwszy rzut oka wszystko to może wydawać się skomplikowane. Ale w rzeczywistości przyciągają do niego ręce, gdy tylko wydaje się, że podświetla określony fragment tekstu na czerwono (lub formatuje go w inny sposób), na podstawie jego treści lub zgodnie z innym kryterium. Ta funkcja jest zawarta w podstawowym zestawie wiedzy Excela, bez niej nawet amatorska praca z arkuszami kalkulacyjnymi i bazami danych jest niemożliwa.