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.