Kurs Excel: Formatowanie warunkowe
od Podstaw
6 godz. 10 min · Excel · Biznes i Automatyzacje
Michał KowalczykTrener i założyciel w Excellent Work - Skuteczna Nauka ExcelaCzego się nauczysz
- Zyska lepsze zrozumienie działania programu Excel.
- Zyska umiejętność pracy na dużych zestawach danych (filtrowanie, przekształcanie, raportowanie).
- Dowie się jak doszukiwać i łączyć potrzebne dane z innych plików i arkuszy.
- Pozna sposoby wyciągania z komórki istotnych fragmentów danych.
- Pozna podstawy analizy dużych zestawów danych z użyciem Tabel Przestawnych.
- Będzie umiał podsumować dane według kryteriów (np. sprzedaż w rozbiciu na kategorie).
- Będzie umiał zliczyć wystąpienia według kryteriów (np. liczba transakcji nieopłaconych).
- Nauczy się tworzyć proste algorytmy przypisujące wartości na podstawie spełnionych kryteriów.
Formatowanie i zarządzanie danymi
Efektywna praca w Excelu zaczyna się od płynnego poruszania się w arkuszach i tabelach. W tej części kursu skupimy się na wprowadzaniu danych, ich edycji i tworzeniu układów, które znacznie poprawią czytelność raportów. Nauczysz się też, jak sprawnie sortować i filtrować duże zestawy informacji, by w kilka chwil wyciągać z nich najważniejsze wnioski. Pokażę Ci również sztuczki przydatne na co dzień, dzięki którym oszczędzisz mnóstwo czasu.
Formuły i kalkulacje
Excel oferuje imponującą liczbę funkcji, które pozwalają na głęboką analizę dowolnych danych – od formuł tekstowych, przez przetwarzanie dat, po budowanie złożonych wyrażeń logicznych. W tej części kursu zobaczysz, jak łączyć różne funkcje w jedno, aby poszerzyć ich możliwości. Dowiesz się, jak tworzyć warunkowe formuły, które będą automatycznie reagować na zmiany w arkuszu. Dzięki temu aktualizacje danych w tabeli wyliczą się same, bez dodatkowej pracy z Twojej strony.
Praca z tabelami przestawnymi i wykresami
Nawet najlepsze dane nie mają większej wartości, jeśli nie umiesz ich właściwie zaprezentować. Dlatego w kolejnej sekcji nauczysz się tworzyć klarowne tabele przestawne i atrakcyjne wizualizacje. Wykorzystasz listy wyboru, by ograniczyć liczbę błędów przy wprowadzaniu danych, a następnie za pomocą wykresów pokażesz wyniki w jasny i przystępny sposób. Zyskasz praktyczne umiejętności tworzenia zestawień w oparciu o Tabele Przestawne.
Podsumowanie i praktyczne wskazówki
Na koniec kursu zebrałem najczęstsze błędy, na jakie trafiają nawet zaawansowani użytkownicy, i pokazałem, jak skutecznie ich unikać. Dostaniesz pakiet najlepszych praktyk, przydatnych skrótów klawiszowych oraz zestaw praktycznych rad, które pomogą Ci rozwiązać typowe problemy w Excelu. Do zobaczenia w kursie!
Dla kogo jest ten kurs?
Ten kurs powstał z myślą o osobach, które pracują już z Excelem oraz tych, które dopiero rozpoczynają przygodę z tym programem. Z kursu najwięcej wyniosą osoby, które:
Witam Cię w kolejnej lekcji, która będzie dotyczyła formatowania warunkowego.
Formatowanie warunkowe to narzędzie, którego głównym celem jest to, aby
sterować uwagą użytkownika końcowego.
Bez względu na to, czy to Ty jesteś tym użytkownikiem końcowym, czy na przykład
ktoś w organizacji, w zespole, a może jakiś przełożony.
A więc zacznijmy od miejsca, w którym znajduje się formatowanie warunkowe.
Formatowanie warunkowe znajdziesz na karcie Narzędzia główne.
No i tu znajdują się poszczególne grupy tychże.
Formatowanie warunkowe.
Aby formatowanie mogło zostać zastosowane, należy najpierw zaznaczyć obszar, na
którym chcemy takie formatowanie warunkowe zastosować.
Ustawmy się w komórce G2 i wciśniemy Ctrl+Shift.
Strzałka w dół.
To jest obszar, na którym będziemy implementowali nasze
formatowanie warunkowe.
Wybieramy formatowanie warunkowe i załóżmy, że chcemy zobrazować nasze
pensje przy pomocy pasków danych.
Załóżmy, że wybierzemy takie jednolite zielone paski danych.
Zwróćmy uwagę teraz te paski danych działają w następujący sposób.
Im dłuższy pasek, tym wyższa pensja.
Jeżeli wejdziemy sobie w formatowanie warunkowe, a następnie zarządzanie
regułami i kolejno wybierzemy tę regułę, którą przed momentem dodaliśmy, a
następnie wybierzemy edytuj regułę, pojawi się tutaj możliwość dalszej konfiguracji
tego formatowania warunkowego.
U nas minimum i maksimum są ustawione automatycznie.
My umówimy się, że naszym minimum będzie najniższa wartość, a nasze maksimum
jest ustawione automatycznie.
Kliknijmy sobie OK. No i zobaczmy.
Zastosuj.
To powoduje, że ta osoba, która ma najniższe wynagrodzenie jest
tym punktem odniesienia.
Długość paska pokazuje w odniesieniu do tego najniższego wynagrodzenia, jak
prezentują się pozostałe wynagrodzenia.
Gdybyśmy chcieli usunąć to formatowanie warunkowe, możemy wybrać
formatowanie warunkowe.
Zarządzanie regułami.
Zaznaczamy tę konkretną regułę i wybieramy Usuń regułę.
Klikamy OK.
No i reguła przestaje działać.
Kolejnym formatowaniem warunkowym wartym rozważenia jest skala kolorów.
Zachęcam tutaj do ostrożnego korzystania ze skali kolorów, dlatego, że jak
spojrzymy robi się dość kolorowo.
Mieliśmy sterować uwagą użytkownika końcowego, a nie ją rozpraszać.
Dlatego zachęcam Cię do korzystania z.
Maksymalnie dwóch kolorów, czyli biały i zielony, biały i czerwony.
To mogą być jakieś kierunki, którymi możemy podążać.
No i takie formatowanie szczególnie dobrze działa, gdy wykorzystamy
je razem z sortowaniem.
Nałóżmy formatowanie warunkowe.
Im wyższa pensja, tym bardziej zielony kolor, czyli skala kolorów, a następnie
skorzystajmy z sortowania, które znamy już z poprzedniej lekcji, czyli udajemy się na
kartę Dane, a następnie wybierzmy sortowanie od do, a to powoduje, że nasze
pensje będą pięknie przechodziły gradientem od najciemniejszego
do najjaśniejszego koloru.
I faktycznie wygląda to teraz całkiem, całkiem nieźle.
Wciśnij sobie Ctrl+F, a następnie przejdźmy na kartę Narzędzia główne.
Zaznaczmy sobie Ctrl+Shift strzałka w dół jeszcze raz naszą kolumnę.
Formatowanie warunkowe zarządzanie regułami.
Pozbądźmy się tej reguły, kliknijmy, zastosuj i przejdźmy do
kolejnych możliwości, jakimi są zestawy ikon.
Z tym również byłbym ostrożny.
Strzałki może ok, te również.
Natomiast ewentualnie jeszcze te trzy światła.
Natomiast w pozostałych przykładach formatowanie warunkowe.
Te oznaczenia nie są zbyt jasne i jest ich dużo.
To może spowodować problem.
Dlatego, gdy korzystamy z formatowania warunkowego, róbmy to w sposób ostrożny,
taki, żeby faktycznie pomagał nam patrzeć na dane, a nie nas rozpraszał.
Dobrym pomysłem może być też dodanie legendy, czyli pokazanie np.
Co oznacza zielona strzałka w górę, żółta w prawo czy czerwona w dół, tak, aby ktoś,
kto patrzy na te dane jasno wiedział o co nam chodzi.
Tyle w temacie ikon, kolorów i pasków danych.
Przejdźmy teraz do reguł związanych z kolorowaniem już bezpośrednio na podstawie
wartości możemy skorzystać z reguły wyróżniania komórek ze
względu na zawartość.
Na szczególną uwagę zasługuje tutaj opcja podświetlania duplikujących się wartości.
Umówmy się, że skorzystamy sobie z kolumny A jesteśmy w komórce A2.
Ctrl+Shift strzałka w dół.
Zaznaczam ten obszar.
Wybieram formatowanie warunkowe.
Następnie reguły wyróżniania komórek, duplikujące się wartości.
No i teraz mogę wybrać, czy chcę podświetlić zduplikowane wartości,
czy też wartości unikatowe.
Ja chcę podświetlić zduplikowane.
No i teraz mógłbym sobie wybrać, w jaki sposób te wartości mają być podświetlone.
Ja bym chciał skorzystać z tego domyślnego, jasnoczerwonego
wypełnienia z ciemnoczerwonym tekstem. Klikam ok.
I teraz czy są w mojej tabeli jakieś duplikaty?
No na razie jeszcze ich nie ma, a spróbuję sobie wstawić ze dwa wiersze.
Zaznaczam dwa wiersze.
Klikam prawym przyciskiem myszy, wybieram wstaw.
No i skopiuję sobie celowo tutaj jedno z imion i nazwisk, żeby pokazać Ci jak
działa formatowanie warunkowe duplikujących się wartości.
A więc w tym przypadku w naszej tabeli znajdują się duplikaty.
Excel nie rozstrzyga, która z tych pozycji jest unikatem.
Jeżeli występuje więcej niż raz, to obie pozycje są podświetlone,
obie trzy i więcej.
Jeżeli tabela jest dość długa i chcielibyśmy w łatwy sposób wyłapać, czy
znajdują się w niej jakieś duplikaty, możemy skorzystać z filtra.
Stosuję Ctrl+ShiftL, aby założyć filtr.
No i mogę skorzystać z filtra koloru, który już znamy z poprzednich lekcji.
W ten oto sposób możemy łatwo wychwycić, czy w naszej tabeli
znajdują się duplikaty.
Możemy teraz usunąć sobie te dwa wiersze. Prawy przycisk myszy.
Usuń wiersze. Duplikat już przestaje istnieć.
Rozwińmy. Kliknijmy sobie, ok?
No i pamiętajmy o tym, że to formatowanie warunkowe.
Mimo że go nie widać, dalej znajduje się w naszym arkuszu.
No właśnie. Zwróć uwagę.
Jeżeli kliknę formatowanie warunkowe, zarządzanie regułami, to widzę to
formatowanie dlatego, że jestem w komórce, Która podlega formatowanie uwarunkowemu.
Gdybym zaznaczył jakąś dowolną komórkę w moim arkuszu, wybrał formatowanie
warunkowe i wybrał zarządzanie regułami, to nie widzę tego formatowania.
Jak znaleźć formatowanie warunkowe w naszym arkuszu?
Czasem zdarza się, że tych formatowania nałożymy bardzo dużo,
co może spowolnić plik.
Możemy skorzystać z opcji Bieżące zaznaczenie, czyli ta komórka,
w której jesteśmy, albo np. Ten arkusz.
To powoduje, że w tym momencie menedżer reguł formatowania warunkowego odnosi się
do wszystkich formatowania warunkowych założonych w tym konkretnym arkuszu.
Widzimy nawet, do którego obszaru odwołuje się to nasze formatowanie warunkowe.
Gdy kliknąłem sobie w dotyczy, po lewej stronie pojawiły się te nasze już znajome
mrówki, pokazujące na jakim obszarze to formatowanie zostało zastosowane.
Usunę sobie tę regułę, kliknę OK.
No i przejdę sobie dalej, aby pokazać Ci, w jaki sposób możemy kolorować
nasze wartości tekstowe.
Nasze wartości liczbowe w zależności od zawartości.
Czyli gdybym chciał na przykład tutaj podświetlić osoby z jakimś konkretnym
imieniem, użyłbym formatowania warunkowego reguły Tekst zawierający.
Mógłbym tutaj wpisać na przykład jakieś konkretne imię, a potem
zdecydować o formatowaniu.
To spowodowałoby podświetlenie np.
Wszystkich osób z konkretnym imieniem.
Natomiast częściej będziemy stosowali to, jeżeli chodzi o jakieś
konkretne wartości liczbowe.
A więc zaznaczam sobie obszar G2G98.
Udaję się na formatowanie warunkowe reguły wyróżniania komórek i załóżmy, że
chciałbym teraz podświetlić wszystkie pensje, które są mniejsze
niż jakaś konkretna wartość.
Załóżmy, że spróbujemy wpisać 2500 zł. Będzie ok.
Zdecyduj się na skorzystanie z formatu niestandardowego.
I teraz mogę sobie wybierać, w jaki sposób mają zostać sformatowane pozycje,
które spełniają ten warunek.
Mniejszy niż 2500 zł.
Mogę wybrać konkretny format liczbowy, mogę dodać kolor czcionki, obramowanie,
wypełnienie wszystko to mogę zrobić na raz.
Ja dodam tylko jedną zmienną, a mianowicie dodam taki jasny zielony
kolor, kliknę OK i kliknę ok. Świetnie.
Mam teraz osoby, których pensja netto wynosi mniej niż 2,5 tysiąca złotych.
Wybieramy formatowanie warunkowe.
Nałożymy drugi format na ten sam obszar i zobaczymy co się stanie.
Reguły wyróżniania komórek.
Znów wybiorę mniejsze niż w tym przypadku.
Wybiorę mniej niż 3000, a następnie znowu skorzystam z formatu niestandardowego i
tym razem wybiorę taki blado niebieski kolor, a następnie kliknę OK.
I teraz zwróćmy uwagę, co się stało.
Wszystkie osoby, które były pokolorowane na zielono nie są już widoczne.
Dlaczego?
Dlatego, że zawierają się w zbiorze osób mniej niż 3000, czyli są teraz
zasłonięte kolorem niebieskim.
Czy to znaczy, że tamto poprzednie formatowanie warunkowe już nie istnieje?
Wcale nie.
Wybieram formatowanie warunkowe, zarządzanie regułami.
I tutaj faktycznie widzę dwa formatowanie warunkowe.
Istotne jest to, które jest wyżej, czyli w przypadku formatowania warunkowego To,
które jest wyżej zastępuje to, które jest poniżej.
Jeżeli.
Oba formatowania warunkowe formatują to samo w naszym przypadku.
Pracowaliśmy i w tym i w tym przypadku na tle.
W związku z tym tło niebieskie zastępuje tło zielone.
Gdybyśmy zamienili miejscami formatowanie.
Czyli teraz ważniejsze jest mniej niż 2,500, a potem mniej niż 3000.
Klikam zastosuj ok.
No i teraz dostaję informację, że na przykład tutaj mamy osobę, która zarabia
pomiędzy 2,5 tysiąca i mniej niż 3 tysiące, a tu mamy osobę, która
zarabia mniej niż 2,5 tysiąca.
Już się ta kolejność odmieniła.
W związku z tym niebieski jest już pod zielonym.
Dodajmy sobie jeszcze jedno formatowanie warunkowe, zaznaczając oczywiście obszar
od G2 do G98 Zarządzanie regułami i tym razem sformatujmy dla ćwiczenia czcionkę.
Dodajmy sobie nową regułę.
Z tej pozycji również możemy dodawać regułę.
Formatujemy komórki ze względu na ich wartość, a więc wybieramy sobie formatuj
tylko komórki, które są na przykład mniejsze niż załóżmy 3,5 tysiąca.
Czyli to jest dokładnie to samo, co robiliśmy wcześniej, tylko z innego
konfiguratora, Z innego miejsca wybieramy sobie formatowanie i
dodajemy sobie teraz np. Czcionkę.
Załóżmy, że dodamy sobie czcionkę np.
Pogrubioną i kolor tej czcionki niech będzie jakiś taki powiedzmy ciemny szary.
Kliknijmy sobie OK i kliknijmy sobie OK.
Jak teraz widzisz co się stało?
Zostało dodane nowe formatowanie.
Znajduje się ono na samej górze.
Gdybym nawet je przeciągnął na sam dół, ono odnosi się tylko i
wyłącznie do formatu czcionki.
Jeżeli kliknę zastosuj, to zwróć uwagę co się stało teraz.
W tym przypadku, mimo że formatowanie jest na samym dole, jest
ono cały czas widoczne.
Dlaczego ono jest cały czas widoczne?
Jest ono dlatego widoczne, że tutaj odnosiliśmy się do formatu czcionki, do
koloru tej czcionki, a tu odnosiliśmy się do tła.
Czcionkę zostawiliśmy nietkniętą.
Gdybyśmy do naszych reguł dodali tutaj w formacie na przykład jakiś konkretny kolor
czcionki, no to ten kolor czcionki już by nam zasłonił ten nasz szary.
Ale my nie dotykaliśmy czcionki.
Odnieśliśmy się tylko do wypełnienia, co powoduje, że po prostu można powiedzieć
ta czcionka ze spodu przebija.
Co byśmy mogli zrobić, aby nie przebijała.
Moglibyśmy wybrać opcję zatrzymaj, jeżeli warunek jest spełniony.
Jeżeli warunek jest prawdziwy zastosuj to.
Zwróć uwagę dalej.
Już formatowanie warunkowe nie sprawdza, co powoduje, że jeżeli mamy wartość
zieloną, to ona już przestaje mieć szarą czcionkę.
Jeżeli odznaczę ten warunek wybiorę Zastosuj.
Tutaj ta szara czcionka faktycznie się pojawia.
Ok.
Myślę, że mocno tutaj przeszliśmy przez formatowanie warunkowe, przez poziomy tego
formatowania, jego rodzaje tego jak możesz wykorzystać formatowanie warunkowe także w
kontekście wykrywania duplikatów, ale i podświetlania wartości czy tekstów.
To tyle w tej lekcji.
W kolejnej pokażę Ci dwa bardzo przydatne triki pracy z Excelem usuwanie duplikatów,
a także rozdzielanie tekstu do kolumn. Do zobaczenia!