Ten samouczek pokaże, jak tworzyć wykresy programu Excel z formatowaniem warunkowym we wszystkich wersjach programu Excel: 2007, 2010, 2013, 2016 i 2022.
Formatowanie warunkowe to praktyka przypisywania niestandardowego formatowania do komórek programu Excel - kolor, czcionka itp. Na podstawie określonych kryteriów (warunków). Funkcja pomaga w analizowaniu danych, znajdowaniu wartości istotnych statystycznie i identyfikowaniu wzorców w danym zbiorze danych.
Niestety, Excel nie oferuje żadnych wbudowanych narzędzi do stosowania formatowania warunkowego do wykresów Excela.
Jednak przy odrobinie magii arkusza kalkulacyjnego żaden problem nie jest nie do pokonania. W tym samouczku dowiesz się, jak utworzyć dynamiczny wykres z formatowaniem warunkowym, w którym wykreślone punkty danych są automatycznie formatowane na podstawie ich odpowiednich wartości rzeczywistych.
Jako przykład spójrz na ten wykres kolumnowy, przedstawiający wyniki menedżera sprzedaży w okresie ośmiu miesięcy. Zwróć uwagę, jak kolor kolumny automatycznie dopasowuje się w zależności od jej podstawowej wartości:
Pierwsze kroki
Rozważ ten przykładowy zestaw danych zawierający dane o skuteczności sprzedaży:
Zanim zaczniemy, przenieś swój zestaw danych, tak aby nad tabelą z nieprzetworzonymi danymi były trzy puste wiersze. Wiersze będą używane do ustawiania reguł formatowania warunkowego.
Krok 1: Przygotuj dane wykresu.
Aby wszystko działało, musisz dodać do równania dodatkowe dane wykresu. Dla Twojej wygody rzuć okiem na to, jak powinny wyglądać Twoje dane na wykresie pod koniec fazy przygotowawczej:
Naszym pierwszym krokiem jest zdefiniowanie wartości granicznych (warunków), które powinny podzielić rzeczywiste wartości na wiele kategorii, umożliwiając ich wykreślenie jako oddzielne serie danych.
Na powyższym zrzucie ekranu widać na przykład, że wyniki sprzedaży w danym miesiącu muszą mieścić się w przedziale od 90 000 do 150 000 USD, aby można je było uznać za zadowalające.
Aby utworzyć reguły, użyj trzech pustych wierszy nad zbiorem danych:
- Rząd 1 (etykieta): Użyj tych komórek do etykietowania reguł formatowania.
- Wiersz 2 (wartość minimalna): Wartości w tym wierszu definiują dolną granicę dla każdego warunku.
- Wiersz 3 (wartość maksymalna): Podobnie te wartości wyznaczają górne granice.
Dzięki tej metodzie możesz utworzyć tyle reguł formatowania warunkowego, ile chcesz - po prostu upewnij się, że zakresy wartości się nie nakładają.
Kontynuując konfigurację wykresu, zaprojektuj niestandardowe elementy legendy wykresu, które zapewnią kontekst wykresowi. Wprowadź tę formułę do C4 i skopiuj go do D4 przeciągając uchwyt wypełniania:
| 1 | =C1&": od "&TEKST(C2, "$#,##")&" do "&TEKST(C3, "$#,##") |
Formuła tworzy ładnie wyglądającą dynamiczną etykietę opartą na wcześniej ustawionych regułach formatowania warunkowego. Funkcja TEKST formatuje wartości jako walutę. Ale jeśli Twój typ danych jest inny, zastosuj tę formułę:
| 1 | =C1&": od "&TEKST(C2, "#,##")&" do "&TEKST(C3, "#,##") |
Lub ten, gdy pracujesz z procentami:
| 1 | =C1&": od "&TEKST(C2, "#%")&" do "&TEKST(C3, "#%") |
Na koniec znajdź wartości na wykresie. Wpisz następującą formułę do C5, skopiuj to do C12 i w poprzek do pasujących komórek w kolumna D:
| 1 | =JEŻELI(ORAZ(C$2<$B5;$B5<=C$3);$B5;NA()) |
Formuła porównuje podaną wartość rzeczywistą w kolumna B w stosunku do określonych wartości granicznych za pomocą funkcji JEŻELI i ORAZ. Jeśli wartość mieści się w zakresie, zostanie skopiowana do tej kolumny. W przeciwnym razie formuła zwraca błąd #N/D, aby zapobiec wielokrotnemu wykreślaniu tej samej wartości.
Krok 2: Skonfiguruj wykres kolumnowy.
Po zebraniu wszystkich danych wykresu, skonfiguruj prosty wykres kolumnowy lub wykres słupkowy jako alternatywę:
- Zaznacz wszystkie dane wykresu z wyjątkiem kolumn zawierających rzeczywiste wartości i reguły, przytrzymując przycisk klawisz kontrolny klucz (A4:A12 i C4:D12).
- Przejdź do Wstawić patka.
- Wybierz "Wstaw wykres kolumnowy lub słupkowy.”
- Wybierać "Kolumna skupiona/pasek skupiony.”
W rezultacie otrzymasz wykres z punktami danych wykreślonymi na podstawie reguł formatowania warunkowego - dokładnie tak, jak obiecano:
Krok #3: Zmodyfikuj wartości nakładania się i szerokości odstępu.
Przed zamknięciem tego samouczka napraw problem z niewłaściwie umieszczonymi kolumnami spowodowanymi przez niewidoczne puste wartości (błędy #N/A) wykreślone na wykresie.
Kliknij prawym przyciskiem myszy dowolną kolumnę i wybierz „Formatuj serię danych” z menu kontekstowego, które się pojawi.
W okienku zadań zmień położenie i szerokość kolumn:
- Przełącz na Opcje serii patka.
- Reszta "Serie nakładają się" do "100%.”
- Ustaw Szerokość szczeliny do "60%.”
Krok 4: Dostosuj schemat kolorów.
Na koniec dodaj ostatnie szlify. Bez zamykania Formatuj serię danych w okienku zadań zmodyfikuj schemat kolorów wykresu:
- Przejdź do Wypełnij i linia patka.
- Pod "Napełnić," wybierać "Wypełnienie stałe.”
- Kliknij "Kolor wypełnienia” ikona i wybierz Zielony z palety kolorów.
Po dotarciu na miejsce ponownie pokoloruj inne serie danych, zmień tytuł wykresu, a w pełni dynamiczny wykres z formatowaniem warunkowym jest gotowy do pracy!
