Analiza danych programu Excel - formatowanie warunkowe

W programie Microsoft Excel możesz użyć Conditional Formattingdo wizualizacji danych. Musisz określić formatowanie zakresu komórek na podstawie zawartości zakresu komórek. Komórki spełniające określone warunki zostaną sformatowane zgodnie z definicją.

Przykład

W zakresie zawierającym dane o sprzedaży z ostatniego kwartału dla zbioru sprzedawców możesz wyróżnić komórki reprezentujące tych, którzy osiągnęli zdefiniowaną wartość docelową, powiedzmy 2500 USD.

Możesz ustawić warunek jako łączną sprzedaż osoby> = $2500i określ kod koloru - zielony. Excel sprawdza każdą komórkę w zakresie, aby określić, czy warunek, który podałeś, tj. Całkowita sprzedaż osoby> = 2500 USD, jest spełniony.

Excel zastosuje wybrany format, czyli zielony kolor do wszystkich komórek spełniających warunek. Jeśli zawartość komórki nie spełnia warunku, formatowanie komórki pozostaje niezmienione. Wynik jest zgodny z oczekiwaniami, tylko dla sprzedawców, którzy osiągnęli cel, komórki są podświetlone na zielono - szybka wizualizacja wyników analizy.

Możesz określić dowolną liczbę warunków formatowania, określając Rules. Możesz wybrać reguły, które pasują do twoich warunków

  • Wyróżnij reguły komórek
  • Zasady Top / Bottom

Możesz także zdefiniować własne zasady. Możesz -

  • Dodaj regułę
  • Wyczyść istniejącą regułę
  • Zarządzaj określonymi regułami

Ponadto masz kilka opcji formatowania w programie Excel, aby wybrać te, które są odpowiednie dla Twojej wizualizacji danych -

  • Paski danych
  • Skale kolorów
  • Zestawy ikon

Formatowanie warunkowe zostało promowane w stosunku do wersji Excel 2007, Excel 2010, Excel 2013. Przykłady, które można znaleźć w tym rozdziale, pochodzą z programu Excel 2013.

W kolejnych sekcjach poznasz reguły formatowania warunkowego, opcje formatowania oraz sposób pracy z regułami.

Wyróżnij reguły komórek

Możesz użyć Highlight Cells reguła przypisywania formatu do komórek, których zawartość spełnia dowolne z następujących kryteriów -

  • Liczby w podanym zakresie liczbowym -
    • Lepszy niż
    • Mniej niż
    • Between
    • Równy
  • Tekst zawierający podany ciąg tekstowy.
  • Data przypadająca w podanym zakresie dat względem daty bieżącej -
    • Yesterday
    • Today
    • Tomorrow
    • W ciągu ostatnich 7 dni
    • Zeszły tydzień
    • W tym tygodniu
    • W następnym tygodniu
    • W zeszłym miesiącu
    • Ten miesiąc
    • W następnym miesiącu
  • Wartości, które są zduplikowane lub unikalne.

Postępuj zgodnie z instrukcjami, aby warunkowo sformatować komórki -

  • Wybierz zakres do sformatowania warunkowego.

  • Kliknij Conditional Formatting w Styles grupa pod Home patka.

  • Kliknij Highlight Cells Rules z menu rozwijanego.

  • Kliknij Greater Thani określ> 750. Wybierz kolor zielony.

  • Kliknij Less Than i określ <500. Wybierz kolor czerwony.

  • Kliknij Between i podaj 500 i 750. Wybierz kolor żółty.

Dane zostaną podświetlone na podstawie podanych warunków i odpowiedniego formatowania.

Zasady Top / Bottom

Możesz użyć Top / Bottom Rules aby przypisać format do komórek, których zawartość spełnia dowolne z następujących kryteriów -

  • Top 10 items - Komórki, które zajmują pierwsze miejsce w N, gdzie 1 <= N <= 1000.

  • Top 10% - komórki, które zajmują najwyższe n%, gdzie 1 <= n <= 100.

  • Bottom 10 items - Komórki zajmujące dolną pozycję N, gdzie 1 <= N <= 1000.

  • Bottom 10% - komórki, które zajmują najniższe n%, gdzie 1 <= n <= 100.

  • Above average - Komórki, które są powyżej średniej dla wybranego zakresu.

  • Below average - Komórki, które są poniżej średniej dla wybranego zakresu.

Wykonaj kroki podane poniżej, aby przypisać reguły Góra / Dół.

  • Wybierz zakres do sformatowania warunkowego.

  • Kliknij Conditional Formatting w Styles grupa pod Home patka.

  • Kliknij Top/Bottom Rulesz menu rozwijanego. Pojawią się opcje reguł Top / Bottom.

  • Kliknij Top Ten Items i określ 5. Wybierz kolor zielony.

  • Kliknij Bottom Ten Items i określ 5. Wybierz kolor czerwony.

Dane zostaną podświetlone na podstawie podanych warunków i odpowiedniego formatowania.

  • Powtórz pierwsze trzy kroki podane powyżej.

  • Kliknij Top Ten% i określ 5. Wybierz kolor zielony.

  • Kliknij Bottom Ten% i określ 5. Wybierz kolor czerwony.

Dane zostaną podświetlone na podstawie podanych warunków i odpowiedniego formatowania.

  • Powtórz pierwsze trzy kroki podane powyżej.

  • Kliknij Above Average. Wybierz kolor zielony.

  • Kliknij Below Average. Wybierz kolor czerwony.

Dane zostaną podświetlone na podstawie podanych warunków i odpowiedniego formatowania.

Paski danych

Możesz użyć kolorowego Data Barsaby zobaczyć wartość w komórce w stosunku do wartości w innych komórkach. Długość paska danych reprezentuje wartość w komórce. Dłuższy słupek oznacza wyższą wartość, a krótszy słupek mniejszą wartość. Masz do wyboru sześć jednolitych kolorów pasków danych - niebieski, zielony, czerwony, żółty, jasnoniebieski i fioletowy.

Paski danych są pomocne w wizualizacji wyższych, niższych i pośrednich wartości w przypadku dużych ilości danych. Przykład - Temperatury dzienne w różnych regionach w danym miesiącu. Możesz użyć pasków koloru wypełnienia gradientowego, aby zwizualizować wartość w komórce w stosunku do wartości w innych komórkach. Masz sześćGradient Colors do wyboru dla pasków danych - niebieski, zielony, czerwony, żółty, jasnoniebieski i fioletowy.

  • Wybierz zakres do sformatowania warunkowego.

  • Kliknij Conditional Formatting w Styles grupa pod Home patka.

  • Kliknij Data Barsz menu rozwijanego. PlikGradient Fill opcje i Fill pojawią się opcje.

Kliknij niebieski pasek danych w Gradient Fill opcje.

  • Powtórz pierwsze trzy kroki.

  • Kliknij niebieski pasek danych w Solid Fill opcje.

Można również sformatować paski danych w taki sposób, aby pasek danych rozpoczynał się w środku komórki i rozciągał w lewo w przypadku wartości ujemnych i w prawo w przypadku wartości dodatnich.

Skale kolorów

Możesz użyć Color Scalesaby zobaczyć wartość w komórce względem wartości w innych komórkach w podanym zakresie. Jak w przypadkuHighlight Cells Rules, a Color Scaleużywa cieniowania komórek do wyświetlania różnic w wartościach komórek. Do zakresu komórek zostanie zastosowany gradient koloru. Kolor wskazuje, gdzie każda wartość komórki mieści się w tym zakresie.

Możesz wybierać spośród -

  • Trzy - skala kolorów -
    • Skala kolorów zielono-żółto-czerwona
    • Skala kolorów czerwono - żółto - zielonego
    • Skala kolorów zielono - biało - czerwono
    • Skala kolorów czerwono - biało - zielonego
    • Niebiesko - biało - czerwona skala kolorów
    • Skala kolorów czerwono-biało-niebieskich
  • Skala dwukolorowa -
    • Biało-czerwona skala kolorów
    • Skala kolorów czerwono-biała
    • Skala kolorów zielono-biała
    • Skala koloru biało-zielonego
    • Skala kolorów zielono-żółta
    • Żółto-zielona skala kolorów

Wykonaj czynności podane poniżej -

  • Wybierz zakres do sformatowania warunkowego.

  • Kliknij Conditional Formatting w Styles grupa pod Home patka.

  • Kliknij Color Scalesz menu rozwijanego. PlikColor Scale pojawią się opcje.

  • Kliknij Skalę kolorów zielono-żółto-czerwoną.

Dane zostaną podświetlone w oparciu o zielono - żółto - czerwoną skalę kolorów w wybranym zakresie.

  • Powtórz pierwsze trzy kroki.
  • Kliknij skalę kolorów zielono-białą.

Dane zostaną podświetlone w oparciu o zielono-białą skalę kolorów w wybranym zakresie.

Zestawy ikon

Możesz użyć zestawów ikon do wizualizacji różnic liczbowych. Dostępne są następujące zestawy ikon -

Jak zauważyłeś, zestaw ikon składa się z trzech do pięciu symboli. Możesz zdefiniować kryteria, aby skojarzyć ikonę z każdą wartością w zakresie komórek. Na przykład czerwona strzałka w dół dla małych liczb, zielona strzałka w górę dla dużych liczb i żółta strzałka pozioma dla wartości pośrednich.

  • Wybierz zakres do sformatowania warunkowego.

  • Kliknij Conditional Formatting w Styles grupa pod Home patka.

  • Kliknij Icon Setsz menu rozwijanego. PlikIcon Sets pojawią się opcje.

  • Kliknij kolorowe trzy strzałki.

Kolorowe strzałki pojawiają się obok danych w oparciu o wartości w wybranym zakresie.

  • Powtórz pierwsze trzy kroki. PlikIcon Sets pojawią się opcje.

  • Wybierz 5 ocen. Ikony ocen pojawiają się obok danych w oparciu o wartości w wybranym zakresie.

Nowa zasada

Możesz użyć New Rule aby utworzyć własną formułę jako warunek formatowania komórki zgodnie z definicją.

Istnieją dwa sposoby korzystania z nowej reguły -

  • Z New Rule opcję z menu rozwijanego

  • Z New Rule przycisk Manage Rules Okno dialogowe

Z opcją Nowa reguła z menu rozwijanego

  • Wybierz zakres do sformatowania warunkowego.

  • Kliknij Conditional Formatting w Styles grupa pod Home patka.

  • Kliknij New Rule z menu rozwijanego.

Plik New Formatting Rule pojawi się okno dialogowe.

  • W polu Wybierz typ reguły wybierz opcję Użyj formuły, aby określić, które komórki mają zostać sformatowane. Edit the Rule Description pojawi się okno.

  • W wartościach formatu, w których ta formuła jest prawdziwa: wpisz formułę.

  • Kliknij przycisk formatowania i kliknij OK.

Komórki zawierające wartości z formułą PRAWDA są sformatowane zgodnie z definicją.

Za pomocą przycisku Nowa reguła w oknie dialogowym Zarządzaj regułami

  • Wybierz zakres do sformatowania warunkowego.

  • Kliknij Conditional Formatting w Styles grupa pod Home patka.

  • Kliknij Manage Rules z menu rozwijanego.

Plik Conditional Formatting Rules Manager pojawi się okno dialogowe.

Kliknij New Rule przycisk.

Plik New Formatting Rule pojawi się okno dialogowe.

Powtórz powyższe kroki, aby zdefiniować formułę i format.

Plik Conditional Formatting Rules Manager pojawi się okno dialogowe ze zdefiniowanym New Rulepodświetlony. KliknijApply przycisk.

Komórki zawierające wartości z formułą PRAWDA są sformatowane zgodnie z definicją.

Jasne zasady

Możesz wyczyścić reguły, aby usunąć wszystkie utworzone przez siebie formaty warunkowe

  • Wybrane komórki
  • Bieżący arkusz roboczy
  • Wybrana tabela
  • Wybrana tabela przestawna

Postępuj zgodnie z podanymi krokami -

  • Wybierz zakres / kliknij arkusz roboczy / kliknij tabelę> tabelę przestawną, w której należy usunąć reguły formatowania warunkowego.

  • Kliknij Conditional Formatting w Styles grupa pod Home patka.

  • Kliknij Clear Rulesz menu rozwijanego. Pojawią się opcje Wyczyść reguły.

Wybierz odpowiednią opcję. Formatowanie warunkowe jest usuwane z zakresu / arkusza roboczego / tabeli / tabeli przestawnej.

Zarządzaj regułami

Możesz Manage Rulesz Conditional Formatting Rules Managerokno. Możesz zobaczyć reguły formatowania dla bieżącego zaznaczenia, dla całego bieżącego arkusza roboczego, dla innych arkuszy w skoroszycie lub tabel lub tabel przestawnych w skoroszycie.

  • Kliknij Conditional Formatting w Styles grupa pod Home patka.

  • Kliknij Manage Rules z menu rozwijanego.

Plik Conditional Formatting Rules Manager pojawi się okno dialogowe.

Kliknij strzałkę w polu listy obok Show formatting rules for Pojawi się bieżący wybór, ten arkusz roboczy i inne arkusze, tabele, tabela przestawna, jeśli istnieją z regułami formatowania warunkowego.

Wybierz This Worksheetz listy rozwijanej. Reguły formatowania w bieżącym arkuszu roboczym pojawiają się w kolejności, w jakiej zostaną zastosowane. Możesz zmienić tę kolejność za pomocą strzałek w górę iw dół.

Możesz dodać nową regułę, edytować regułę i usunąć regułę.

  • Już widziałeś New Rulewe wcześniejszej sekcji. Możesz usunąć regułę, wybierając regułę i klikającDelete Rule. Podświetlona reguła zostanie usunięta.

  • Aby edytować regułę, wybierz REGUŁĘ i kliknij Edit Rule. Edit Formatting Rule pojawi się okno dialogowe.

  • Możesz

    • Wybierz typ reguły

    • Edytuj opis reguły

    • Edytuj formatowanie

  • Po wprowadzeniu zmian kliknij OK.

  • Zmiany w regule zostaną odzwierciedlone w Conditional Formatting Rules ManagerOkno dialogowe. KliknijApply.

  • Dane zostaną podświetlone na podstawie zmodyfikowanych Conditional Formatting Rules.