Patrząc na suche figury tabel, na pierwszy rzut oka trudno uchwycić duży obraz, który reprezentują. Ale w Microsoft Excel istnieje graficzne narzędzie do wizualizacji, za pomocą którego można wizualizować dane zawarte w tabelach. Pozwala to łatwiej i szybciej przyswajać informacje. To narzędzie nazywa się formatowaniem warunkowym. Zobaczmy, jak korzystać z formatowania warunkowego w programie Microsoft Excel.

Aby sformatować określony obszar komórek, musisz zaznaczyć ten obszar (najczęściej kolumnę), a na karcie „Strona główna” kliknij przycisk „Formatowanie warunkowe”, który znajduje się na wstążce na pasku narzędzi „Style”.

Następnie otworzy się menu formatowania warunkowego. Oto trzy główne typy formatowania:

• Histogramy
• Wagi cyfrowe;
• Odznaki

Aby warunkowo sformatować jako histogram, wybierz kolumnę danych i kliknij odpowiedni element menu. Jak widać, wydaje się, że wybranych jest kilka rodzajów histogramów z gradientem i wypełnieniem pełnym. Wybierz ten, który Twoim zdaniem jest najbardziej zgodny ze stylem i zawartością tabeli.

Jak widać, histogramy pojawiły się w wybranych komórkach kolumny. Im większa wartość liczbowa w komórkach, tym dłuższy histogram. Ponadto w wersjach programu Excel 2010, 2013 i 2016 możliwe jest prawidłowe wyświetlanie wartości ujemnych na histogramie. Ale wersja 2007 nie ma takiej możliwości.

Podczas korzystania z paska koloru zamiast histogramu można również wybrać różne opcje dla tego narzędzia. Co więcej, z reguły im większa wartość znajduje się w komórce, tym bardziej nasycony jest kolor skali.

Najbardziej interesującym i złożonym narzędziem wśród tego zestawu funkcji formatowania są ikony. Istnieją cztery główne grupy ikon: kierunki, kształty, wskaźniki i oceny. Każda opcja wybrana przez użytkownika wymaga użycia różnych ikon podczas oceny zawartości komórki. Cały wybrany obszar jest skanowany przez program Excel, a wszystkie wartości komórek są dzielone na części zgodnie z określonymi w nich wartościami. Zielone ikony dotyczą największych wartości, żółty zakres do średnich wartości, a wartości znajdujące się w najmniejszej trzeciej są oznaczone czerwonymi ikonami.

Wybierając strzałki, jako ikonę, oprócz kolorowego projektu, używana jest również sygnalizacja w postaci kierunków. Tak więc strzałka skierowana w górę jest stosowana do dużych wartości, w lewo - do średnich, w dół - do małych. Podczas używania figurek największe wartości są oznaczone kółkiem, średnie trójkątem i małe rombem.

Reguły wyboru komórek

Domyślnie stosowana jest reguła, w której wszystkie komórki wybranego fragmentu są oznaczone określonym kolorem lub ikoną, zgodnie z wartościami w nich zlokalizowanymi. Ale korzystając z menu, o którym wspomnieliśmy powyżej, możesz zastosować inne reguły nazewnictwa.

Kliknij pozycję menu „Reguły wyboru komórek”. Jak widać, istnieje siedem podstawowych zasad:

• Więcej;
• Mniej;
• Na równi;
• Pomiędzy;
• Data;
• Zduplikowane wartości.

Rozważ zastosowanie tych działań przez przykłady. Wybierz zakres komórek i kliknij element „Więcej ...”.

Otworzy się okno, w którym należy ustawić wartości większe niż liczba, która zostanie podświetlona. Odbywa się to w polu „Formatuj większe komórki”. Domyślnie średnia wartość zakresu automatycznie pasuje tutaj, ale możesz ustawić dowolną inną lub podać adres komórki zawierającej ten numer. Ta ostatnia opcja jest odpowiednia dla dynamicznych tabel, w których dane ciągle się zmieniają, lub do komórki, w której stosowana jest formuła. Na przykład ustawiliśmy wartość na 20 000.

W następnym polu musisz zdecydować, jak komórki zostaną podświetlone: \u200b\u200bjasnoczerwone wypełnienie i ciemnoczerwony kolor (domyślnie); żółte wypełnienie i ciemnożółty tekst; czerwony tekst itp. Ponadto istnieje niestandardowy format.

Gdy przejdziesz do tego elementu, otworzy się okno, w którym możesz edytować zaznaczenie, prawie tak jak chcesz, używając różnych opcji czcionek, wypełnień i ramek.

Po podjęciu decyzji, z wartościami w oknie ustawień reguł wyboru, kliknij przycisk „OK”.

Jak widać, komórki są wybierane zgodnie z ustaloną regułą.

Zgodnie z tą samą zasadą wartości są przydzielane przy stosowaniu reguł Less, Between i Equals. Tylko w pierwszym przypadku komórki są przydzielane mniej niż ustawiona przez ciebie wartość; w drugim przypadku ustawiany jest przedział liczb, którym komórki zostaną przydzielone; w trzecim przypadku ustawiana jest konkretna liczba i wybierane są tylko te, które ją zawierają.

Tekst zawiera regułę zaznaczania stosowaną głównie do komórek formatu tekstowego. W oknie konfiguracji reguły należy określić słowo, część słowa lub sekwencyjny zestaw słów, gdy zostaną znalezione, odpowiednie komórki zostaną podświetlone w sposób ustawiony przez użytkownika.

Reguła daty ma zastosowanie do komórek zawierających wartości w formacie daty. Jednocześnie w ustawieniach możesz ustawić wybór komórek według tego, kiedy zdarzenie miało miejsce lub nastąpi: dzisiaj, wczoraj, jutro, przez ostatnie 7 dni itp.

Stosując zasadę „Powtarzanie wartości”, możesz skonfigurować wybór komórek zgodnie z tym, czy dane w nich pasujące spełniają jedno z kryteriów: czy dane są powtarzane, czy unikalne.

Zasady wyboru pierwszej i ostatniej wartości

Ponadto menu formatowania warunkowego zawiera jeszcze jedną interesującą pozycję - „Zasady wybierania pierwszej i ostatniej wartości”. Tutaj możesz ustawić wybór tylko największych lub najmniejszych wartości w zakresie komórek. Jednocześnie można użyć wyboru, zarówno według wartości porządkowych, jak i procentowych. Istnieją następujące kryteria wyboru, które są wskazane w odpowiednich pozycjach menu:

• Pierwsze 10 elementów;
• Pierwsze 10%;
• Ostatnie 10 pozycji;
• Ostatnie 10%;
• Powyżej średniej;
• Poniżej przeciętnej.

Ale po kliknięciu odpowiedniego elementu możesz nieznacznie zmienić reguły. Otworzy się okno, w którym wybrany jest typ wyboru, a jeśli to konieczne, możesz ustawić inną granicę wyboru. Na przykład, klikając element „Pierwsze 10 elementów”, w oknie, które zostanie otwarte, w polu „Formatuj pierwsze komórki” zamień liczbę 10 na 7. W ten sposób, po kliknięciu przycisku „OK”, nie zostanie podświetlonych 10 największych wartości, ale tylko 7.

Utwórz reguły

Powyżej rozmawialiśmy o regułach, które są już ustawione w programie Excel, a użytkownik może po prostu wybrać dowolną z nich. Ale dodatkowo, w razie potrzeby, użytkownik może tworzyć własne reguły.

Aby to zrobić, musisz kliknąć pozycję „Inne reguły ...” znajdującą się na samym dole listy, w dowolnej podsekcji menu formatowania warunkowego. Lub kliknij element „Utwórz regułę ...”, który znajduje się w dolnej części głównego menu formatowania warunkowego.

Otworzy się okno, w którym musisz wybrać jedną z sześciu rodzajów reguł:

1. Sformatuj wszystkie komórki na podstawie ich wartości;
2. Formatuj tylko komórki zawierające;
3. Sformatuj tylko pierwszą i ostatnią wartość;
4. Formatuj tylko wartości powyżej lub poniżej średniej;
5. Formatuj tylko unikalne lub zduplikowane wartości;
6. Użyj formuły, aby zdefiniować sformatowane komórki.

Zgodnie z wybranym typem reguł, w dolnej części okna musisz skonfigurować zmianę opisu zasad, ustawiając wartości, interwały i inne wartości, które omówiliśmy już poniżej. Tylko w tym przypadku ustawienie tych wartości będzie bardziej elastyczne. Natychmiast ustawia się, zmieniając czcionkę, ramki i wypełnienie, jak dokładnie będzie wyglądał wybór. Po zakończeniu wszystkich ustawień należy kliknąć przycisk „OK”, aby zapisać zmiany.

Zarządzanie regułami

W programie Excel możesz zastosować kilka reguł jednocześnie do tego samego zakresu komórek, ale tylko ostatnia wprowadzona reguła zostanie wyświetlona na ekranie. Aby regulować wdrażanie różnych reguł dotyczących określonego zakresu komórek, musisz wybrać ten zakres, aw menu głównym formatowania warunkowego przejdź do pozycji zarządzania regułami.

Otworzy się okno, w którym przedstawione są wszystkie reguły dotyczące wybranego zakresu komórek. Reguły są stosowane od góry do dołu, gdy są wymienione. Zatem jeśli reguły są ze sobą sprzeczne, to w rzeczywistości wykonanie tylko najnowszej z nich jest wyświetlane na ekranie.

Do zamiany reguł służą przyciski w postaci strzałek skierowanych w górę i w dół. Aby reguła była wyświetlana na ekranie, musisz ją wybrać i kliknąć przycisk w formie strzałki skierowanej w dół, aż reguła zajmie ostatni wiersz na liście.

Jest inna opcja. Musimy zaznaczyć pole w kolumnie o nazwie „Zatrzymaj, jeśli prawda” naprzeciwko reguły, której potrzebujemy. Tak więc, przeglądając reguły od góry do dołu, program zatrzyma się dokładnie przy regule, przy której ten znak jest, i nie spadnie, co oznacza, że \u200b\u200breguła ta faktycznie się spełni.

W tym samym oknie znajdują się przyciski do tworzenia i zmiany wybranej reguły. Po kliknięciu tych przycisków uruchamiane są okna tworzenia i zmiany reguł, które omówiliśmy powyżej.

Aby usunąć regułę, musisz ją wybrać i kliknąć przycisk „Usuń regułę”.

Ponadto można usunąć reguły za pomocą głównego menu formatowania warunkowego. Aby to zrobić, kliknij element „Usuń reguły”. Zostanie otwarte podmenu, w którym możesz wybrać jedną z opcji usuwania: albo usuń reguły tylko w wybranym zakresie komórek, albo usuń absolutnie wszystkie reguły znajdujące się w otwartym arkuszu programu Excel.

Jak widać formatowanie warunkowe jest bardzo potężnym narzędziem do wizualizacji danych w tabeli. Za jego pomocą możesz skonfigurować tabelę, aby ogólne informacje na jej temat zostały przyswojone przez użytkownika na pierwszy rzut oka. Ponadto formatowanie warunkowe nadaje dokumentowi dużą estetykę.

Formatowanie warunkowe pomaga zapewnić przejrzystość podczas badania i analizy danych, identyfikowania krytycznych problemów, identyfikowania wzorców i trendów.

Korzystając z formatowania warunkowego, możesz łatwo wybrać niezbędne komórki lub zakresy, podkreślić nietypowe wartości i wizualizować dane za pomocą histogramów, skal kolorów i zestawów ikon.

Po zastosowaniu formatowania warunkowego wygląd komórek zmienia się w zależności od określonych warunków. Jeśli warunki są spełnione, formatowanie jest stosowane do zakresu komórek. Jeśli warunki nie są spełnione, formatowanie nie jest stosowane. Istnieje wiele wbudowanych warunków i możesz tworzyć własne warunki (w tym za pomocą formuły, która daje wartość „Prawda” lub „Fałsz”).

Warunkowe formatowanie miesięcznego zapisu danych o wysokiej temperaturze dla różnych miejsc, kolory intuicyjnie odpowiadają wartościom (wyższe wartości temperatury to pomarańczowy i czerwony, niskie wartości to żółty i zielony)
Formatowanie warunkowe z wykorzystaniem koloru tła komórek w celu wyróżnienia różnych kategorii produktów; ikona z trzema strzałkami pokazuje trendy wartości (wzrost, zapis, spadek), a wykresy słupkowe pokazują różnicę we wzroście kosztów produktów.

Formatowanie warunkowe można zastosować do zakresu komórek (wybranych lub nazwanych), do arkusza kalkulacyjnego Excel, a nawet do raportu tabeli przestawnej w programie Excel dla systemu Windows. Zwróć uwagę na szereg dodatkowych czynników, które wpływają na formatowanie warunkowe w raporcie tabeli przestawnej - patrz sekcja Zastosuj formatowanie warunkowe w raporcie tabeli przestawnej poniżej na karcie Windows.

Pobieranie przykładowej książki

Możesz pobrać przykładową książkę zawierającą różne przykłady korzystania z formatowania warunkowego przy użyciu standardowych reguł, takich jak góra i dół, powtarzanie danych, histogramy, zestawy ikon i skale kolorów, a także niestandardowe reguły tworzone ręcznie.

Formatowanie komórek za pomocą dwukolorowego paska

Kolorowe paski to elementy wizualne, które pomagają zrozumieć rozkład i rozprzestrzenianie się danych. Dwukolorowa skala pomaga porównać zakresy komórek z gradacją dwóch kolorów. Poziom jasności kolorów jest wysoki, średni lub niski. Na przykład w pokazanej poniżej żółto-zielonej skali możesz wskazać, że komórki o wysokich wartościach będą bardziej zielone, a komórki o niskich wartościach będą bardziej żółte.

Wskazówka: JEST lub JEŚLI BŁĄD aby zwrócić wartość inną niż błąd.

Szybkie formatowanie

Opcje formatowania

Zaawansowane formatowanie

Patka Dom w grupie Style i wybierz Zarządzanie regułami. Otworzy się okno dialogowe.



• Stwórz zasadę. Otworzy się okno dialogowe.

  1. Minimalizuj okno dialogowe w polu Dotyczy to Rozwiń okno dialogowe.

     Zmień regułę. Otworzy się okno dialogowe.

W sekcji Zastosuj regułę

• do wybranych komórekaby wybrać komórki przez zaznaczenie;

  <метка значения> aby wybrać komórki z określoną etykietą.

  do wszystkich komórek zawierających wartości<метка значения> dla<название строки> aby wybrać komórki z określoną etykietą, z wyjątkiem sum częściowych i sum.

W sekcji Wybierz typ reguły wybierz (domyślnie).

W grupie Zmień opis reguły w polu kombi Formatuj styl Wybierz przedmiot Skala dwukolorowa.

W polach Typ parametry Minimalna wartość i Maksymalna wartość wybierz ich typy.

• Wybierz minimalna wartość i maksymalna wartość.

  W takim przypadku nie wchodź minimum i maksymalnywartości.

  Wybierz przedmiot Numera następnie wprowadź minimum i maksymalnywartości.

  Wchodzić minimum i maksymalnywartości.

  Formatowanie percentylowe. Wybierz przedmiot Percentyl Minimalna wartość i Maksymalny wartość

  Użyj percentyla, jeśli chcesz wizualizować grupę wysokich wartości (na przykład górny 20 percentyl) z jednym odcieniem, a grupę niskich wartości (na przykład dolny 20 percentyl) z innym, ponieważ odpowiadają one ekstremalnym wartościom, które mogą zastąpić wizualizację danych.

  Wybierz przedmiot Formułaa następnie wprowadź minimum i maksymalny wartości.

  • Uwagi:

    • Upewnij się, że minimum wartość mniejsza niż maksymalny.

      Dla parametrów Minimalna wartość i Maksymalna wartość minimalna wartość i typ procentowy dla maksymalna wartość.

Aby wybrać opcje Minimalna wartość i Maksymalna wartość na pasku kolorów kliknij element Kolor dla każdej wartości, a następnie wybierz kolor.

Inne kolory. Wybrany pasek kolorów pojawi się w oknie. zapowiedź.

Formatowanie komórek za pomocą trójkolorowej skali

Kolorowe paski to elementy wizualne, które pomagają zrozumieć rozkład i rozprzestrzenianie się danych. Trójkolorowa skala pomaga porównać zakres komórek za pomocą gradacji trzech kolorów. Poziom jasności kolorów jest wysoki, średni lub niski. Na przykład w skali czerwono-żółto-zielonej można wskazać, że komórki o wysokich wartościach będą zielone, komórki o średnich wartościach będą żółte, a komórki o niskich wartościach będą czerwone.

Szybkie formatowanie

Możesz zmienić metodę definiowania obszaru dla pól z obszaru „Wartości” w raporcie tabeli przestawnej za pomocą przycisku Opcje formatowania obok pola tabeli przestawnej, do której zastosowano formatowanie warunkowe.

Zaawansowane formatowanie

Wybierz jedną lub więcej komórek w raporcie zakresu, tabeli lub tabeli przestawnej.

Patka Dom w grupie Style kliknij strzałkę obok przycisku i wybierz Zarządzanie regułami. Zostanie otwarte okno dialogowe Menedżer reguł formatowania warunkowego.



Wykonaj jedną z następujących czynności:

• Aby dodać formatowanie warunkowe, kliknij Stwórz zasadę. Otworzy się okno dialogowe.

  Aby zmienić formatowanie warunkowe, wykonaj następujące kroki:

  1. Upewnij się, że pole kombi Pokaż reguły formatowania dla Wybrany jest odpowiedni raport arkusza, tabeli lub tabeli przestawnej.

     W razie potrzeby możesz zmienić zakres komórek. Aby to zrobić, kliknij Minimalizuj okno dialogowe w polu Dotyczy toaby tymczasowo ukryć okno dialogowe. Następnie wybierz nowy zakres komórek na arkuszu i naciśnij przycisk Rozwiń okno dialogowe.

     Wybierz regułę, a następnie kliknij Zmień regułę. Zostanie otwarte okno dialogowe Zmiana reguły formatowania.

W sekcji Zastosuj regułę wybierz jedną z następujących opcji, aby zmienić wybór pól w panelu raportu tabeli przestawnej:

• Wybierz tylko te komórki;

  Wybierz wszystkie komórki<поле значения> z tymi samymi polami;

  Wybierz wszystkie komórki<поле значения> .

W grupie Wybierz typ reguły Wybierz przedmiot Sformatuj wszystkie komórki na podstawie ich wartości.

W grupie Zmień opis reguły w polu kombi Formatuj styl Wybierz przedmiot Skala trójkolorowa.

Wybierz typ parametrów Minimalna wartość, Oznaczać i Maksymalna wartość

• Formatowanie wartości minimalnej i maksymalnej. Wybierz przedmiot Oznaczać.

  W takim przypadku nie wchodź minimum i maksymalny wartości.

  Formatowanie wartości liczbowej, daty lub wartości czasu. Wybierz przedmiot Numera następnie wprowadź minimum, Średnia i maksymalny wartości.

  Formatowanie wartości procentowej. Wybierz przedmiot Procenta następnie wprowadź minimum, Średnia i maksymalny wartości. Prawidłowe wartości wynoszą od 0 (zero) do 100. Nie wpisuj znaku procentu.

  Formatowanie percentylowe. Wybierz przedmiot Percentyla następnie wprowadź Minimalna wartość, Oznaczać i Maksymalna wartość.

  Użyj percentyla, jeśli chcesz wizualizować grupę wysokich wartości (na przykład górny 20 percentyl) z jednym odcieniem, a grupę niskich wartości (na przykład dolny 20 percentyl) z innym, ponieważ odpowiadają one ekstremalnym wartościom, które mogą zastąpić wizualizację danych.

  Formatowanie wyniku formuły. Wybierz przedmiot Formułaa następnie wprowadź minimum, Średnia i maksymalny wartości.

  Formuła musi zwrócić liczbę, datę lub godzinę. Rozpocznij wprowadzanie formuły ze znakiem równości ( = ) Niepoprawna formuła nie zezwala na formatowanie. Sprawdź formułę, aby upewnić się, że nie zwraca wartości błędu.

  Uwagi:

  • Możesz ustawić minimalną, średnią i maksymalną wartość dla zakresu komórek. Upewnij się, że minimum mniej wartości środkowy, a to z kolei jest mniejsze maksymalny.

    Dla parametrów Minimalna wartość, Oznaczać i Maksymalna wartość Możesz wybrać różne typy. Na przykład możesz wybrać typ liczbowy dla minimalna wartośćpercentyl dla Średnia wartość i typ procentowy dla maksymalna wartość.

    W wielu przypadkach oznaczać domyślnie 50 procent jest optymalne, ale można je dostosować do konkretnych wymagań.

Aby wybrać pasek kolorów Minimalna wartość, Oznaczać i Maksymalna wartość kliknij element Kolor dla każdego z nich, a następnie wybierz kolor.

• Aby wybrać dodatkowe kolory lub utworzyć kolor niestandardowy, kliknij Inne kolory.

  Wybrany pasek kolorów pojawi się w oknie. zapowiedź.

Formatowanie komórek za pomocą histogramów

Histogramy pomagają rozważyć wartość w komórce w stosunku do innych komórek. Długość histogramu odpowiada wartości w komórce. Im dłużej, tym większa wartość. Histogramy są optymalne do określania kluczowych wskaźników, zwłaszcza w dużych ilościach danych - na przykład maksymalnej i minimalnej sprzedaży zabawek w raporcie sprzedaży świątecznej.

Pokazany tutaj przykład wykorzystuje histogramy do podkreślenia ekstremalnych wartości dodatnich i ujemnych. Histogramy można sformatować tak, aby zaczynały się od środka komórki i rozciągały w lewo do wartości ujemnych.

Wskazówka: Jeśli którakolwiek z komórek w zakresie zawiera formułę zwracającą błąd, formatowanie warunkowe nie jest do niej stosowane. Aby upewnić się, że formatowanie warunkowe jest zastosowane do tych komórek, użyj funkcji JEST lub JEŚLI BŁĄD

Szybkie formatowanie

Zaawansowane formatowanie

Wybierz jedną lub więcej komórek w raporcie zakresu, tabeli lub tabeli przestawnej.

Patka Dom w grupie Style kliknij strzałkę obok przycisku i wybierz Zarządzanie regułami. Zostanie otwarte okno dialogowe Menedżer reguł formatowania warunkowego.



Wykonaj jedną z następujących czynności:

• Aby dodać formatowanie warunkowe, kliknij Stwórz zasadę. Otworzy się okno dialogowe.

  Aby zmienić formatowanie warunkowe, wykonaj następujące kroki:

  1. Upewnij się, że pole kombi Pokaż reguły formatowania dla Wybrany jest odpowiedni raport arkusza, tabeli lub tabeli przestawnej.

     W razie potrzeby możesz zmienić zakres komórek. Aby to zrobić, kliknij Minimalizuj okno dialogowe w polu Dotyczy toaby tymczasowo ukryć okno dialogowe. Następnie wybierz nowy zakres komórek na arkuszu i naciśnij przycisk Rozwiń okno dialogowe.

     Wybierz regułę, a następnie kliknij Zmień regułę. Zostanie otwarte okno dialogowe Zmiana reguły formatowania.

W sekcji Zastosuj regułę wybierz jedną z następujących opcji, aby zmienić wybór pól w panelu raportu tabeli przestawnej:

• aby wybrać pola według wyboru, Wybierz tylko te komórki;

  aby wybrać pola według odpowiedniego pola, Wybierz wszystkie komórki<поле значения> z tymi samymi polami;

  aby wybrać pola według pola wartości, Wybierz wszystkie komórki<поле значения> .

W grupie Wybierz typ reguły Wybierz przedmiot Sformatuj wszystkie komórki na podstawie ich wartości.

W grupie Zmień opis reguły w polu kombi Formatuj styl Wybierz przedmiot wykres słupkowy.

Wybierz wartości Minimum i Maksymalny dla typu. Wykonaj jedną z następujących czynności:

• Formatowanie wartości minimalnej i maksymalnej. Wybierz Minimalna wartość i Maksymalna wartość.

  W takim przypadku nie wchodź Minimalna wartość i Maksymalna wartość.

  Formatowanie wartości liczbowej, daty lub wartości czasu. Wybierz przedmiot Numera następnie wprowadź minimum i maksymalnywartości.

  Formatowanie wartości procentowej. Wybierz przedmiot Procenta następnie wprowadź minimum i maksymalny wartości.

  Prawidłowe wartości wynoszą od 0 (zero) do 100. Nie wpisuj znaku procentu.

  Użyj wartości procentowych, jeśli chcesz wizualizować wszystkie wartości proporcjonalnie, ponieważ rozkład wartości w tym przypadku jest proporcjonalny.

  Formatowanie percentylowe. Wybierz przedmiot Percentyla następnie wprowadź wartości parametrów Minimalna wartość i Maksymalna wartość.

  Poprawne wartości to percentyle od 0 (zero) do 100.

  Użyj percentyli, jeśli chcesz wizualizować grupę wysokich wartości (na przykład górny 20 percentyl) w jednej proporcji histogramu, a grupę niskich wartości (na przykład dolny 20 percentyl) w innej, ponieważ odpowiadają one ekstremalnym wartościom, które mogą zastąpić wizualizację danych.

  Formatowanie wyniku formuły. Wybierz przedmiot Formułaa następnie wprowadź wartości parametrów Minimalna wartość i Maksymalna wartość.

  • Formuła musi zwrócić liczbę, datę lub godzinę.

    Rozpocznij wprowadzanie formuły ze znakiem równości ( = ).

    Niepoprawna formuła nie zezwala na formatowanie.

    Sprawdź formułę, aby upewnić się, że nie zwraca wartości błędu.

Uwagi:

Upewnij się, że minimum wartość mniejsza niż maksymalny.

Dla parametrów Minimalna wartość i Maksymalna wartość Możesz wybrać różne typy. Na przykład możesz wybrać typ liczbowy dla minimalna wartość i typ procentowy dla maksymalna wartość.

Aby wybrać skale kolorów Minimalna wartość i Maksymalna wartość Wybierz przedmiot Kolor kolumny.

Jeśli chcesz wybrać dodatkowe kolory lub utworzyć niestandardowy kolor, kliknij Inne kolory. Wybrany kolor kolumny pojawi się w oknie. zapowiedź.

Aby wyświetlić tylko histogram, ale nie wartość w komórce, zaznacz pole Pokaż tylko kolumnę.

Aby zastosować solidną ramkę do kolumn histogramu, wybierz Granica opcja Solidna granicaa następnie wybierz kolor obramowania.

Aby kolumny były pełne lub gradientowe, wybierz z listy Zsyp opcja Pełne wypełnienie lub Wypełnienie gradientowe.

Aby ustawić format kolumny dla wartości ujemnych, kliknij Wartości ujemne i ośa następnie w oknie dialogowym Ustawienie wartości ujemnych i osi dostosuj kolory wypełnienia i obramowania dla kolumn odpowiadających wartościom ujemnym. Możesz dostosować położenie i kolor osi. Po wybraniu opcji kliknij ok.

Kierunek histogramu można ustawić w polu Kierunek kolumny. Domyślnie wartość jest tam wybierana. w kontekścieMożesz jednak wybierać od lewej do prawej lub od prawej do lewej, w zależności od sposobu prezentacji danych.

Formatowanie komórek za pomocą zestawu ikon

Zestaw ikon służy do opisywania i klasyfikowania danych w trzech do pięciu kategorii, oddzielonych wartością progową. Każda ikona odpowiada zakresowi wartości. Na przykład w zestawie ikon z 3 strzałkami czerwona strzałka w górę wskazuje wysokie wartości, żółta strzałka skierowana w bok odpowiada wartościom średnim, a zielona strzałka w dół odpowiada wartościom niskim.

W pokazanym tutaj przykładzie formatowanie warunkowe wykorzystuje kilka zestawów ikon.

Możesz wyświetlać tylko ikony komórek, które spełniają określone warunki (na przykład, wyświetlać ikonę ostrzeżenia dla komórek, których wartości są poniżej poziomu krytycznego, i nie ma ikony dla komórek przekraczających ten poziom). Aby to zrobić, podczas konfigurowania warunków ukryj komórki, wybierając Brak ikony komórki na liście rozwijanej obok żądanej ikony. Ponadto możesz tworzyć własne zestawy ikon, na przykład zielony znacznik wyboru, żółte światło drogowe i czerwoną flagę.

Wskazówka: Jeśli którakolwiek z wybranych komórek zawiera formułę zwracającą błąd, formatowanie warunkowe nie ma zastosowania do tych komórek. Aby upewnić się, że formatowanie warunkowe jest zastosowane do tych komórek, użyj funkcji JEST lub JEŚLI BŁĄD aby zwrócić wartość (na przykład 0 lub „nie dotyczy”) inną niż błąd.

Szybkie formatowanie

Możesz zmienić sposób definiowania obszaru dla pól z obszaru „Wartości” w raporcie tabeli przestawnej za pomocą przełącznika Zastosuj regułę formatowania do.

Zaawansowane formatowanie

Patka Dom w grupie Style kliknij strzałkę obok przycisku i wybierz Zarządzanie regułami. Zostanie otwarte okno dialogowe Menedżer reguł formatowania warunkowego.



Wykonaj jedną z następujących czynności:

• Aby dodać formatowanie warunkowe, kliknij Stwórz zasadę. Otworzy się okno dialogowe.

  Aby zmienić formatowanie warunkowe, wykonaj następujące kroki:

  1. Upewnij się, że pole kombi Pokaż reguły formatowania dla Wybrany jest odpowiedni raport arkusza, tabeli lub tabeli przestawnej.

     W razie potrzeby możesz zmienić zakres komórek. Aby to zrobić, kliknij Minimalizuj okno dialogowe w polu Dotyczy toaby tymczasowo ukryć okno dialogowe. Następnie wybierz nowy zakres komórek na arkuszu i naciśnij przycisk Rozwiń okno dialogowe.

     Wybierz regułę, a następnie kliknij Zmień regułę. Zostanie otwarte okno dialogowe Zmiana reguły formatowania.

W sekcji Zastosuj regułę wybierz jedną z następujących opcji, aby zmienić wybór pól w panelu raportu tabeli przestawnej:

• aby wybrać pola według wyboru, Wybierz tylko te komórki;

  aby wybrać pola według odpowiedniego pola, Wybierz wszystkie komórki<поле значения> z tymi samymi polami;

  aby wybrać pola według pola wartości, Wybierz wszystkie komórki<поле значения> .

W grupie Wybierz typ reguły Wybierz przedmiot Sformatuj wszystkie komórki na podstawie ich wartości.

W sekcji Zmień opis reguły na liście Format stylu Wybierz przedmiot Zestaw ikon.

1. Wybierz zestaw ikon. Domyślny zestaw to 3 sygnały drogowe bez ramki. Liczba ikon, domyślne operatory porównania i wartości progowe dla każdej ikony mogą być różne dla różnych zestawów ikon.

   Możesz zmienić operatory porównania i progi. Domyślne rozmiary zakresu są takie same dla wszystkich ikon, ale można je zmienić, aby spełnić określone wymagania. Upewnij się, że progi są w logicznej kolejności - od najwyższej do najniższej, od góry do dołu.

   Wykonaj jedną z następujących czynności:

   • Formatowanie wartości liczbowej, daty lub wartości czasu. Wybierz przedmiot Numer.

     Formatowanie wartości procentowej. Wybierz przedmiot Procent.

     Prawidłowe wartości wynoszą od 0 (zero) do 100. Nie wpisuj znaku procentu.

     Użyj wartości procentowych, jeśli chcesz wizualizować wszystkie wartości proporcjonalnie, ponieważ rozkład wartości w tym przypadku jest proporcjonalny.

     Formatowanie percentylowe. Wybierz przedmiot Percentyl. Poprawne wartości to percentyle od 0 (zero) do 100.

     Użyj percentyli, jeśli chcesz wizualizować grupę wartości maksymalnych (na przykład górny 20 percentyl) z jedną ikoną, a grupę wartości minimalnych (na przykład dolny 20 percentyl) z inną, ponieważ odpowiadają one ekstremalnym wartościom, które mogą zastąpić wizualizację danych.

     Formatowanie wyniku formuły. Wybierz przedmiot Formułaa następnie wprowadź formuły w każdym polu Wartość.

     • Formuła musi zwrócić liczbę, datę lub godzinę.

       Rozpocznij wprowadzanie formuły ze znakiem równości (\u003d).

       Niepoprawna formuła nie zezwala na formatowanie.

       Sprawdź formułę, aby upewnić się, że nie zwraca wartości błędu.

Aby pierwsza ikona odpowiadała niższym wartościom, a ostatnia - dużej, wybierz Odwróć kolejność ikon.

Aby wyświetlić tylko ikonę, ale nie wartość w komórce, wybierz Pokaż tylko ikonę.

Uwagi:

• Konieczne może być dostosowanie szerokości kolumn, aby mogły one pomieścić ikony.

  Rozmiar wyświetlanej ikony zależy od rozmiaru czcionki użytej w tej komórce. Wraz ze wzrostem rozmiaru czcionki rozmiar ikony również rośnie proporcjonalnie.

Formatowanie komórek zawierających tekst, wartości liczbowe, datę lub godzinę

Aby ułatwić znajdowanie określonych komórek, możesz sformatować je za pomocą operatora porównania. Na przykład w ekwipunku posortowanym według kategorii możesz wyróżnić żółte produkty, które mają mniej niż 10 sztuk. W arkuszu podsumowania sklepu detalicznego możesz wybrać wszystkie sklepy o przychodzie większym niż 10%, ze sprzedażą poniżej 100 000 USD oraz w regionie południowo-wschodnim.

Przykłady tutaj pokazują wbudowane kryteria formatowania warunkowego, takie jak „większe niż” i „% _best”. Miasta o populacji powyżej 2 000 000 są zaznaczone na zielono, a 30% najlepszych (najwyższych) średnich miesięcznych maksymalnych temperatur jest zaznaczonych na pomarańczowo.

Uwaga: Nie można zastosować formatowania warunkowego do pól w obszarze „Wartość” raportu tabeli przestawnej według tekstu lub daty (tylko według liczb).

Szybkie formatowanie

Możesz zmienić sposób definiowania obszaru dla pól z obszaru „Wartości” w raporcie tabeli przestawnej za pomocą przełącznika Zastosuj regułę formatowania do.

Jeśli chcesz obejrzeć film o tych metodach, zobacz Wideo: warunkowe formatowanie tekstu i wideo: warunkowe formatowanie daty.

Zaawansowane formatowanie

Wybierz jedną lub więcej komórek w raporcie zakresu, tabeli lub tabeli przestawnej.

Na karcie Narzędzia główne w grupie Style kliknij strzałkę obok przycisku Formatowanie warunkowe i wybierz opcję Zarządzaj regułami. Zostanie otwarte okno dialogowe Menedżer reguł formatowania warunkowego.



Wykonaj jedną z następujących czynności:

• Aby dodać formatowanie warunkowe, kliknij Stwórz zasadę. Otworzy się okno dialogowe.

  Aby zmienić formatowanie warunkowe, wykonaj następujące kroki:

  1. Upewnij się, że pole kombi Pokaż reguły formatowania dla Wybrany jest odpowiedni raport arkusza, tabeli lub tabeli przestawnej.

     W razie potrzeby możesz zmienić zakres komórek. Aby to zrobić, kliknij Minimalizuj okno dialogowe w polu Dotyczy to Rozwiń okno dialogowe.

     Wybierz regułę, a następnie kliknij Zmień regułę. Zostanie otwarte okno dialogowe Zmiana reguły formatowania.

W sekcji Zastosuj regułę wybierz jedną z następujących opcji, aby zmienić wybór pól w panelu raportu tabeli przestawnej:

• aby wybrać pola według wyboru, Wybierz tylko te komórki;

  aby wybrać pola według odpowiedniego pola, Wybierz wszystkie komórki<поле значения> z tymi samymi polami;

  aby wybrać pola według pola wartości, Wybierz wszystkie komórki<поле значения> .

Na liście Wybierz typ reguły Wybierz przedmiot Sformatuj tylko komórki zawierające.

W grupie Zmień opis reguły w polu kombi Sformatuj tylko komórki, dla których spełniony jest następujący warunek. Wykonaj jedną z następujących czynności:

1. Aby ustawić format, kliknij Format. Pojawi się okno dialogowe. Format komórki.

   Wybierz liczbę, czcionkę, ramkę lub format wypełnienia, które chcesz zastosować, jeśli wartość w komórce odpowiada warunkowi, a następnie kliknij ok.

   Możesz wybrać kilka formatów. Wybrane formaty pojawią się w oknie. zapowiedź.

Szybkie formatowanie

Możesz zmienić sposób definiowania obszaru dla pól z obszaru „Wartości” w raporcie tabeli przestawnej za pomocą przełącznika Zastosuj regułę formatowania do.

Zaawansowane formatowanie

Szybkie formatowanie

Możesz zmienić sposób definiowania obszaru dla pól z obszaru „Wartości” w raporcie tabeli przestawnej za pomocą przełącznika Zastosuj regułę formatowania do.

Zaawansowane formatowanie

Formatowanie tylko unikatowych lub zduplikowanych wartości

Uwaga: Nie jest możliwe zastosowanie formatowania warunkowego do pól w obszarze Wartości raportu tabeli przestawnej dla unikatowych lub zduplikowanych wartości.

W podanym tutaj przykładzie w kolumnie „Nauczyciel” zastosowano formatowanie warunkowe, aby wyróżnić nauczycieli prowadzących więcej niż jeden przedmiot (zduplikowane nazwy są wyróżnione na różowo). Wartości oceny, które pojawiają się tylko raz w kolumnie Oceny, są podświetlone na zielono.

Szybkie formatowanie

Zaawansowane formatowanie

Utwórz własne reguły formatowania warunkowego

Jeśli żadna z powyższych opcji Ci nie odpowiada, możesz utworzyć własną regułę formatowania warunkowego, wykonując kilka prostych kroków.

Wybierz komórki do formatowania za pomocą formuły

Jeśli nie masz niezbędnych opcji podczas tworzenia własnej reguły formatowania warunkowego, możesz użyć logicznej formuły, aby ustawić warunki formatowania. Na przykład możesz porównać wartości w wybranych komórkach z wynikiem zwróconym przez funkcję lub ocenić dane w komórkach poza wybranym zakresem, które mogą znajdować się na innym arkuszu tej samej książki. Formuła powinna zwracać wartość Prawda lub Fałsz (1 lub 0), ale można użyć logiki warunkowej, aby połączyć zestaw odpowiednich formatów warunkowych, takich jak różne kolory dla każdego z małych zestawów wartości tekstowych (na przykład nazwy kategorii produktu).

Uwaga: Możesz wprowadzić odwołania do komórek w formule. Aby to zrobić, zaznacz komórki bezpośrednio na arkuszu lub innych arkuszach. Po wybraniu komórek w arkuszach tworzone są bezwzględne odwołania do komórek. Aby program Excel dostosował odwołania do komórek w wybranym zakresie, użyj łączy względnych. Aby uzyskać więcej informacji, zobacz Tworzenie i edytowanie łącza komórki i Zmiana typu łącza: względny, bezwzględny, mieszany.

Wskazówka: Jeśli którakolwiek komórka zawiera formułę, która zwraca błąd, formatowanie warunkowe nie ma zastosowania do takich komórek. Aby rozwiązać ten problem, użyj w formule funkcje IS lub funkcja JEŚLI BŁĄDaby zwrócić podaną wartość (na przykład 0 lub „nie dotyczy”) zamiast wartości błędu.

Patka Dom w grupie Style kliknij strzałkę obok przycisku i wybierz Zarządzanie regułami.



Zostanie otwarte okno dialogowe Menedżer reguł formatowania warunkowego.



Wykonaj jedną z następujących czynności:

• Aby dodać formatowanie warunkowe, kliknij Stwórz zasadę. Otworzy się okno dialogowe.

  Aby zmienić formatowanie warunkowe, wykonaj następujące kroki:

  1. Upewnij się, że pole kombi Pokaż reguły formatowania dla Wybrany jest odpowiedni raport arkusza, tabeli lub tabeli przestawnej.

     W razie potrzeby możesz zmienić zakres komórek. Aby to zrobić, kliknij Minimalizuj okno dialogowe w polu Dotyczy toaby tymczasowo ukryć okno dialogowe, a następnie wybierz nowy zakres komórek na tym lub innych arkuszach i kliknij Rozwiń okno dialogowe.

     Wybierz regułę, a następnie kliknij Zmień regułę. Zostanie otwarte okno dialogowe Zmiana reguły formatowania.

W sekcji Zastosuj regułę wykonaj jedną z następujących czynności, aby zmienić wybór pól w panelu wartości raportu tabeli przestawnej:

• Aby określić obszar według wybranego fragmentu Kliknij Wybrane komórki.

  Aby określić obszar według odpowiedniego pola Kliknij do wszystkich komórek zawierających wartości<поле значения> .

  Aby zdefiniować obszar za pomocą pola Wartość Wybierz do wszystkich komórek zawierających wartości<поле значения> dla<строка> .

W grupie Wybierz typ reguły naciśnij przycisk Użyj formuły, aby zdefiniować sformatowane komórki.

Poniższy film przedstawia podstawy korzystania z formuł formatowania warunkowego.

Skopiuj i wklej formatowanie warunkowe

Jeśli chcesz zastosować styl formatowania warunkowego do nowych lub innych danych na arkuszu, możesz skopiować go za pomocą polecenia Formatuj wzór.

Uwaga: Jeśli użyłeś formuły w regule wykorzystującej formatowanie warunkowe, po wklejeniu takiego formatowania może być konieczne skonfigurowanie odwołań do komórek w tej formule. Aby uzyskać więcej informacji na ten temat, zobacz Przełączanie między łączami względnymi, bezwzględnymi i mieszanymi.

Wyszukaj komórki z formatowaniem warunkowym

Jeśli arkusz zawiera formatowanie warunkowe, możesz szybko znaleźć te komórki, aby skopiować, zmodyfikować lub usunąć format warunkowy. Aby wyszukać komórki z określonym formatowaniem warunkowym lub wszystkie komórki z formatowaniem warunkowym, użyj polecenia Wybierz grupę komórek.

Wyszukaj wszystkie komórki z formatowaniem warunkowym

Wyszukaj komórki z tym samym formatowaniem warunkowym

Zarządzaj regułami formatowania warunkowego

Korzystając z formatowania warunkowego, definiujesz reguły, według których program Excel określa, kiedy należy zastosować formatowanie warunkowe. Aby zarządzać tymi regułami, powinieneś zrozumieć kolejność sprawdzania tych reguł oraz działania w przypadku konfliktu kilku z nich. Należy również zrozumieć, w jaki sposób kopiowanie i wklejanie wpływa na sprawdzanie reguł; musisz wiedzieć, jak zmienić sekwencję tego sprawdzania i kiedy go zatrzymać.

• Menedżer reguł formatowania warunkowego. (Aby to zrobić, na zakładce Dom wybierz polecenie i wybierz Zarządzanie regułami.)

  

  Zostanie otwarte okno dialogowe Menedżer reguł formatowania warunkowego.

  

  Przy stosowaniu kilku reguł formatowania warunkowego priorytet ich weryfikacji zależy od kolejności ich lokalizacji w tym oknie dialogowym (od góry do dołu).

  

  

  DZISIAJ \u003d B2<СЕГОДНЯ () Dotyczy to \u003d B2<СЕГОДНЯ()+60

  W górę i Na dół.

  

  Czasami można zastosować kilka reguł formatowania warunkowego do szeregu komórek. Oto przykłady stosowania reguł: najpierw te, które nie powodują konfliktów, a następnie te, które powodują konflikty.

  Jeśli jakakolwiek reguła formatowania warunkowego zostanie sprawdzona z wynikiem „Prawda”, ma ona pierwszeństwo przed dowolnym ustawionym ręcznie formatem dla tych samych wybranych komórek. Oznacza to, że w przypadku konfliktu stosowane jest formatowanie warunkowe, a formatowanie ręczne nie jest stosowane. Po usunięciu reguły formatowania warunkowego pozostaje ręczne formatowanie zastosowane do zakresu komórek.

  Menedżer reguł formatowania warunkowego

  Zarządzanie regułami Sprawdź pudełko Przestań, jeśli to prawda

  Przestań, jeśli to prawda

  Przestań, jeśli to prawda dla pierwszej zasady;

  Przestań, jeśli to prawda dla drugiej zasady;

  Zaznacz lub odznacz Przestań, jeśli to prawda

Jeśli chcesz obejrzeć film o zarządzaniu regułami formatowania warunkowego, zobacz Wideo: Kontrolowanie formatowania warunkowego.

Wyczyść formatowanie warunkowe

Wyczyść formatowanie warunkowe w arkuszu

Patka Dom kliknij\u003e Usuń reguły > .

Aby wyczyścić formatowanie warunkowe na arkuszu, postępuj zgodnie z instrukcjami poniżej.

Całość arkusz

Patka Dom kliknij\u003e Usuń reguły > Usuń reguły z całego arkusza.

W zakresie komórek

Znajdź i usuń to samo formatowanie warunkowe w arkuszu

Kliknij komórkę z formatowaniem warunkowym, które chcesz usunąć z całego arkusza.

Patka Dom kliknij strzałkę obok przycisku Znajdź i wyróżnij i wybierz zespół Wybierz grupę komórek.

Wybierz opcję Formaty warunkowe.

Wybierz przedmiot to samo w grupie Sprawdzanie danych. Wszystkie komórki z tymi samymi regułami formatowania warunkowego zostaną podświetlone.

Patka Dom naciśnij przycisk, wybierz Usuń reguły, i wtedy - .

Formatowanie tylko pierwszej lub ostatniej wartości

Maksymalne i minimalne wartości można znaleźć w zakresie komórek na podstawie określonej wartości progowej. Na przykład możesz znaleźć 5 najpopularniejszych produktów w raporcie regionalnym, 15% najmniej popularnych produktów w ankiecie wśród klientów lub 25 najwyższych pensji w raporcie personelu działu.

Wybierz komórki, do których chcesz zastosować formatowanie warunkowe.

Patka Dom w grupie Stoły Wybierz.

Wybierz na przykład żądane polecenie Pierwsze 10 pozycji lub Ostatnie 10%.

Wprowadź wartości, których chcesz użyć, a następnie wybierz format (kolor wypełnienia, tekst lub ramkę) i kliknij ok.

Formatowanie tylko wartości powyżej lub poniżej średniej

Możesz znaleźć wartości powyżej lub poniżej średniej lub odchylenia standardowego w zakresie komórek. Na przykład w corocznym przeglądzie wyników można znaleźć artystów o ponadprzeciętnej wydajności lub zidentyfikować wyprodukowane materiały, których jakość jest poniżej dwukrotności standardowego odchylenia.

Wybierz komórki, do których chcesz zastosować formatowanie warunkowe.

Patka Dom w grupie Stoły Wybierz przedmiot Zasady wyboru pierwszej i ostatniej wartości.

1. Kopiuj i wklej formatowanie warunkowe Zarządzaj regułami formatowania warunkowego

   Korzystając z formatowania warunkowego, definiujesz reguły, według których program Excel określa, kiedy należy zastosować formatowanie warunkowe. Aby zarządzać tymi regułami, ważne jest, aby zrozumieć kolejność sprawdzania tych reguł i działania w przypadku konfliktu kilku z nich. Należy również zrozumieć, w jaki sposób kopiowanie i wklejanie wpływa na sprawdzanie reguł; musisz wiedzieć, jak zmienić sekwencję tego sprawdzania i kiedy go zatrzymać.

   Zasady formatowania warunkowego Informacje o priorytecie przetwarzania

   Tworzenie, edycja, usuwanie i przeglądanie wszystkich reguł formatowania warunkowego w książce odbywa się w oknie dialogowym Menedżer reguł formatowania warunkowego. (Aby to zrobić, na zakładce Dom wybierz polecenie i wybierz Zarządzanie regułami.)

   

   Zostanie otwarte okno dialogowe Menedżer reguł formatowania warunkowego.

   

   Po zastosowaniu kilku reguł formatowania warunkowego do zakresu komórek, priorytet przetwarzania (od góry do dołu) jest określony przez kolejność ich ułożenia w tym oknie dialogowym.

   Oto przykład z datami ważności kart identyfikacyjnych. Należy zmienić tło certyfikatów, których okres ważności upływa po 60 dniach, ale jeszcze nie upłynął, na żółty, a kolor tła certyfikatów, których ważność wygasła, na czerwony.

   

   W tym przykładzie komórki z numerami identyfikacyjnymi pracowników, których daty certyfikacji wygasają po 60 dniach, są sformatowane na żółto, a numery identyfikacyjne pracowników z wygasłą certyfikacją są sformatowane na czerwono (patrz rysunek poniżej).

   

   Pierwsza reguła (która ustawia kolor tła komórki na czerwony, jeśli wartość jest prawdziwa) sprawdza wartość daty w kolumnie B naprzeciwko bieżącej daty (obliczoną za pomocą funkcji DZISIAJ we wzorze). Wzór tej reguły jest przypisany do pierwszej wartości w kolumnie B - B2 i wygląda następująco: \u003d B2<СЕГОДНЯ () . Ta formuła sprawdza komórki w kolumnie B (komórki B2: B15). Jeśli formuła dla komórki w kolumnie B ma wartość true, odpowiadająca jej komórka w kolumnie A (na przykład A5 odpowiada B5 i A11 do B11) jest sformatowana za pomocą czerwonego koloru tła. Po dokonaniu oceny za pomocą tej pierwszej reguły wszystkie komórki określone w grupie Dotyczy to, sprawdzana jest druga reguła. Ta formuła sprawdza, czy wartości w kolumnie B są o 60 dni opóźnione od bieżącej daty (załóżmy, że dzisiejsza data to 11 sierpnia 2010 r.). Komórka w kolumnie B4 „10/04/2010” jest opóźniona o ponad 60 dni od dzisiejszej daty, dlatego jest oceniana jako „prawdziwa” i jest sformatowana żółtym kolorem tła. Wzór tej reguły wygląda następująco: \u003d B2<СЕГОДНЯ()+60 . Wszystkie komórki, najpierw sformatowane na czerwono według reguły o najwyższym priorytecie na liście, pozostają niezmienione.

   Reguła znajdująca się wyżej na liście ma wyższy priorytet niż reguła znajdująca się niżej na liście. Domyślnie nowe reguły są zawsze dodawane na początku listy, a zatem mają wyższy priorytet, więc musisz śledzić ich kolejność. Kolejność reguł można zmienić w oknie dialogowym za pomocą przycisków strzałek. W górę i Na dół.

   

   Co dzieje się, gdy używasz wielu reguł formatowania warunkowego jednocześnie?

   Możesz zastosować kilka reguł formatowania warunkowego do szeregu komórek. Oto przykłady stosowania reguł: najpierw te, które nie powodują konfliktów, a następnie te, które powodują konflikty.

   Gdy nie ma konfliktu między regułami Na przykład jeśli zgodnie z jedną regułą czcionka w komórce powinna być pogrubiona, a zgodnie z inną regułą kolor tła tej samej komórki powinien być czerwony, wówczas czcionka w komórce zostanie sformatowana jako pogrubiona, a kolor tła komórki będzie czerwony. W takim przypadku nie ma konfliktu między tymi dwoma formatami, więc obowiązują obie reguły.

   Kiedy występuje konflikt między regułami Na przykład zgodnie z jedną regułą czcionka w komórce powinna być czerwona, a według innej reguły zielona. W takim przypadku dwie reguły są ze sobą sprzeczne i można zastosować tylko jedną z nich. Zostanie zastosowana reguła o wyższym priorytecie (znajdująca się wyżej na liście w oknie dialogowym).

   Wpływ operacji wstawiania, wypełniania i stosowania formatu na model na reguły formatowania warunkowego

   W trakcie edycji arkusza kopiowane i wklejane są wartości komórek w formatach warunkowych, wypełnianie zakresu komórek formatami warunkowymi i stosowanie formatu zgodnie z próbką. Te działania mogą wpływać na priorytet reguł formatowania warunkowego: dla komórek docelowych tworzona jest nowa reguła formatowania warunkowego na podstawie komórek źródłowych.

   Podczas kopiowania i wklejania wartości komórek z formatowaniem warunkowym do arkusza otwartego w innym wystąpieniu programu Excel (inny proces Excel.exe jest jednocześnie uruchomiony na komputerze), reguła formatowania warunkowego nie jest tworzona w innym wystąpieniu i formatowanie nie jest kopiowane do tego wystąpienia.

   Co dzieje się, gdy konflikt formatowania warunkowego i formatowania ręcznego

   Jeśli reguła formatowania ma zastosowanie do zakresu komórek, ma pierwszeństwo przed formatowaniem ręcznym. Formatowanie ręczne można wykonać za pomocą przycisków z grupy Czcionka na zakładce Dom w programie Excel 2016 lub 2013 lub z grupy Komórki na zakładce Dom w programie Excel 2010 lub 2007. Po usunięciu reguły formatowania warunkowego pozostaje formatowanie, które jest ręcznie stosowane do zakresu komórek.

   Formatowanie ręczne nie jest określone w oknie dialogowym Menedżer reguł formatowania warunkowego i nie jest używany do ustalania priorytetów reguł.

   Kontroluj zatrzymanie przetwarzania reguł za pomocą pola wyboru „Zatrzymaj, jeśli prawda”

   Aby zachować zgodność wsteczną z wersjami wcześniejszymi niż Excel 2007, można użyć okna dialogowego Zarządzanie regułami Sprawdź pudełko Przestań, jeśli to prawdasymulować wyświetlanie formatowania warunkowego we wcześniejszych wersjach programu Excel, które nie obsługują stosowania więcej niż trzech reguł formatowania warunkowego w tym samym zakresie.

   Na przykład, jeśli we wcześniejszych wersjach programu Excel wcześniejszych niż Excel 2007, do zakresu komórek zastosowano więcej niż trzy reguły formatowania warunkowego, w tej wersji programu Excel:

   • oceniane są tylko trzy pierwsze reguły;

     obowiązuje pierwsza prawdziwa reguła w sekwencji;

     prawdziwe reguły o niższym priorytecie są pomijane.

   Poniższa tabela zawiera podsumowanie wszystkich możliwych warunków dla pierwszych trzech zasad.

   Możesz zaznaczyć lub wyczyścić pole wyboru. Przestań, jeśli to prawda aby zmienić domyślne zachowanie:

   aby przetworzyć tylko pierwszą regułę, zaznacz pole Przestań, jeśli to prawda dla pierwszej zasady;

   aby przetworzyć tylko pierwszą i drugą regułę, zaznacz pole Przestań, jeśli to prawda dla drugiej zasady;

   Zaznacz lub odznacz Przestań, jeśli to prawda niemożliwe, jeśli reguła zostanie sformatowana przy użyciu histogramu, paska kolorów lub zestawu ikon.

   Zmień kolejność sprawdzania reguł formatowania warunkowego

   Procedura sprawdzania reguł formatowania warunkowego - ich priorytet - zależy od ich względnego znaczenia: im wyższa reguła na liście reguł formatowania warunkowego, tym ważniejsze jest. Oznacza to, że jeśli dwie reguły formatowania warunkowego są ze sobą w konflikcie, obowiązuje reguła znajdująca się wyżej na liście, a reguła poniżej nie ma zastosowania.

   

   Usuwanie formatowania warunkowego

   Możesz usunąć formatowanie warunkowe w wybranych komórkach lub z całego arkusza.

   Aby usunąć formatowanie warunkowe w wybranych komórkach:

   1. Wybierz komórki na arkuszu.

      Kliknij zakładkę Dom > > Usuń reguły > Usuń reguły z wybranych komórek.

   Aby usunąć formatowanie warunkowe z całego arkusza, kliknij kartę Dom > > Usuń reguły > Usuń reguły z całego arkusza.

   Uwaga: Formatowanie warunkowe z zewnętrznymi linkami do innych książek nie jest możliwe.

   Dodatkowe informacje

   Zawsze możesz zadać pytanie ekspertowi Excel Tech Community, poprosić o pomoc w społeczności Answers, a także zaoferować nową funkcję lub ulepszenie na stronie

Praca z projektem VB (12)
Formatowanie warunkowe (5)
Listy i zakresy (5)
Makra (procedury VBA) (63)
Różne (39)
Błędy i usterki w programie Excel (4)

Zbiór wzorów do formatowania warunkowego

W tym artykule znajduje się lista formuł, których można użyć w warunkowym formatowaniu komórek określonych za pomocą formuły:

• Excel 2003 : Format-- formuła;
• Excel 2007-2010 : tab Dom-Formatowanie warunkowe-Utwórz regułę (Nowa reguła)-Użyj formuły, aby zdefiniować sformatowane komórki (Użyj formuły, aby określić, które komórki sformatować)

Przeczytaj więcej na temat formatowania warunkowego w artykule: Podstawowe pojęcia formatowania warunkowego i sposób jego tworzenia

Wszystkie warunki podano dla zakresu A1: A20. Oznacza to, że aby warunek został spełniony poprawnie, należy wybrać zakres A1: A20 (może być więcej kolumn), zaczynając od komórki A1, a następnie przypisać warunek.
Jeśli musisz wybrać nie z pierwszego wiersza, ale powiedzmy, z czwartego, musisz wybrać zakres A4: A20, a we wzorze dla warunku określ jako kryterium pierwszą komórkę wybranego zakresu - A4.

Jeśli konieczne jest wybranie poprzez sformatowanie nie tylko konkretnej komórki, która spełnia warunek, ale całego wiersza tabeli opartego na komórce jednej kolumny, to przed ustawieniem reguły należy wybrać całą tabelę, której wiersze należy sformatować, i naprawić link do kolumny za pomocą kryterium:
\u003d A1 $ \u003d MAKS (1 $ A $: 20 $ A)
przy wybranym zakresie A1: F20 (zakres zastosowania formatowania warunkowego) wiersz A7: F7 zostanie podświetlony, jeśli maksymalna liczba znajduje się w komórce A7.

Można go również zastosować nie do konkretnej pojedynczej kolumny, ale do całego zakresu. Ale w tym przypadku musisz znać zasadę przesunięcia łączy w formułach, aby warunki były stosowane konkretnie do pożądanych komórek. Na przykład, jeśli ustawisz warunek dla zakresu B1: D10 w postaci wzoru: \u003d B1

WARTOŚCI NUMERYCZNE
1. Wybierz komórki z liczbami:
   \u003d NUMBER (A1)
2. Wybierz komórki z liczbami, ale bez uwzględnienia zer:
   \u003d ORAZ (LICZBA (A1); A1 0)
3. Podświetl wiersze o wartości większej niż 0:
   \u003d A1\u003e 0
4. Podświetl wiersze o wartości z zakresu od 3 do 10:
   \u003d I (A1\u003e \u003d 3; A1
5. Wybór w zakresie $ A $ 1: $ A $ 20 komórek o maksymalnej wartości:
   \u003d A1 \u003d MAKS (1 A $ 1: 20 A $)
6. Wybór w zakresie $ A $ 1: $ A $ 20 komórek o minimalnej wartości:
   \u003d ORAZ (LICZBA (A1); A1 \u003d MIN (1 A $: 20 A $ 20))
7. Wybór w zakresie $ A $ 1: $ A $ 20 komórek z drugą największą liczbą. Te. spośród liczb 1,2,3,4,5,6,7 liczba 6 zostanie podświetlona:
   \u003d A1 \u003d NAJWIĘKSZY (1 $ A $: 20 $ A; 2)

WARTOŚCI TEKSTOWE

8. Wybierz komórki z dowolnym tekstem:
   \u003d ETEXT (A1)
9. Wybierz komórki z tekstem Całkowity:
   \u003d A1 \u003d „Podsumowanie”
10. Wybierz komórki zawierające tekst Całkowity:
    \u003d LICZ.JEŻELI (A1; „* ogółem *”)
    \u003d NIE (EOSH (SZUKAJ („ogółem”; A1)))
11. Wybierz komórki, które nie zawierają tekstu Całkowity:
    \u003d LICZ.JEŻELI (A1; „* ogółem *”) \u003d 0
    \u003d EOSH (SZUKAJ („ogółem”; A1))
12. Wybierz komórki, których tekst zaczyna się od słowa Całkowity:
    \u003d LEVSIMV (A1; 4) \u003d „Ogółem”
13. Wybierz komórki, których tekst kończy się słowem Całkowity:
    \u003d WYMAGANE (A1; 4) \u003d „Podsumowanie”

DATA CZASU

14. Podświetl aktualną datę:
    \u003d A1 \u003d DZISIAJ ()
15. Wybierz komórkę z datą większą niż bieżąca:
    \u003d A1\u003e DZISIAJ ()
16. Wybierz komórkę z datą za tydzień:
    \u003d A1 \u003d DZISIAJ () + 7
17. Wybierz komórki z datami bieżącego miesiąca (dowolnego roku):
    \u003d MIESIĄC (A1) \u003d MIESIĄC (DZISIAJ ())
18. Wybierz komórki z datami bieżącego miesiąca bieżącego roku:
    \u003d I (MIESIĄC (A1) \u003d MIESIĄC (DZISIAJ ()); ROK (A1) \u003d ROK (DZISIAJ ()))
    lub
    \u003d TEKST (A1; „RRRRMM”) \u003d TEKST (DZISIAJ (); „RRRRMM”)
19. Podświetlanie komórek w dni wolne:
    \u003d DZIEŃ (A1; 2)\u003e 5
20. Wybierz komórki z dniami powszednimi:
    \u003d DZIEŃ (A1; 2)
21. Wybór komórek zawartych w określonym okresie (okresie) dat:
    \u003d ORAZ ($ A1\u003e DATA (2015; 9; 1); $ A1

    INNY

22. Podkreślanie różnic w komórkach według warunków:
    \u003d A1<>B1 USD
23. Wybierz komórkę, jeśli komórka w następnej kolumnie (B) tego samego rzędu jest mniejsza:
    \u003d A1\u003e B1
24. Podświetlanie linii w kolorze przez jeden:
    \u003d RESIDUE (LINE (); 2)
25. Zaznacz wiersze, jeśli wartość komórki w kolumnie A jest w zakresie $ F $ 1: $ H 5000 $:
    \u003d LICZ.JEŻELI (1 F $ 1: 5000 H $; A1)
26. Podświetlanie wierszy, jeśli wartość komórki kolumny A nie mieści się w zakresie $ F $ 1: $ H 5000 $:
    \u003d LICZ.JEŻELI ($ F $ 1: $ H 5000 $; A1) \u003d 0
27. Podświetlanie koloru komórki, jeśli jego wartość w zakresie A1: A20 jest drugim z rzędu:
    \u003d LICZ.JEŻELI (1 A $ 1: A1 A1; A1) \u003d 2
28. Wybierz komórki zawierające błędy ( #WARTOŚĆ!; # Nie dotyczy; #POŁĄCZYĆ! itp.). Oprócz zwykłej identyfikacji komórek z błędami, możesz zastosować, gdy chcesz ukryć błędne wartości w komórkach (ustawiając kolor czcionki na taki sam jak kolor wypełnienia):
    \u003d BŁĄD (A)
29. Podświetl niepuste komórki w kolumnie A:
    \u003d $ A1 „”

Czy artykuł pomógł? Udostępnij link znajomym! Lekcje wideo

(„Dolny pasek” :( „styl tekstu”: „statyczny”, „textpositionstatic”: „dół”, „textautohide”: true, „textpositionmarginstatic”: 0, „textpositiondynamic”: „bottomleft”, „textpositionmarginleft”: 24, „ textpositionmarginright ”: 24,„ textpositionmargintop ”: 24,„ textpositionmarginbottom ”: 24,„ texteffect ”:„ slide ”,„ texteffecteasing ”:„ relaxationOutCubic ”,„ texteffectduration ”: 600,„ texteffectslided direction ”:„ left ”,„ texteffectslided : 30, „texteffectdelay”: 500, „texteffectseparate”: false, „texteffect1”: „slide”, „texteffectslidedirection1”: „right”, „texteffectslidedistance1”: 120, „texteffecteasing1”: „relaxationOutCubic”, „texteffectduration1”: 600 , „texteffectdelay1”: 1000, „texteffect2”: „slide”, „texteffectslidedirection2”: „right”, „texteffectslidedistance2”: 120, „texteffecteasing2”: „relaxationOutCubic”, „texteffectduration2”: 600, „texteffectdelay2”: 1500, textcss ":" display: block; padding: 12px; text-align: left; "," textbgcss ":" display: block; pozycja: bezwzględna; góra: 0px; lewo: 0px; szerokość: 100%; wysokość: 100% ; kolor tła: # 333333; krycie: 0,6; filtr: a lpha (opacity \u003d 60); "," titlecss ":" display: block; pozycja: względna; czcionka: pogrubiona 14px \\ "Lucida Sans Unicode \\", \\ "Lucida Grande \\", sans-serif, Arial; color: #fff; "," descriptioncss ":" display: block; pozycja: względna; czcionka: 12px \\ "Lucida Sans Unicode \\", \\ "Lucida Sans Unicode \\", sans-serif, Arial; kolor: #fff; margin-top: 8px; "," buttoncss ":" display: block; pozycja: względna; margin-top: 8px; ”,„ texteffectresponsiveize ”: true,„ texteffectresponsivesize ”: 640,„ titlecssresponsive ”:„ font-size: 12px; ”,„ descriptioncssresponsive ”:„ display: none! ważne; ”,„ buttoncssresponsive ”: „”, „addgooglefonts”: false, „googlefonts”: „”, „textleftrightpercentforstatic”: 40))

Formatowanie warunkowe jest jednym z najbardziej przydatnych narzędzi EXCEL. Możliwość korzystania z niego może zaoszczędzić użytkownikowi dużo czasu i wysiłku.

Zacząć naukę Formatowanie warunkoweod sprawdzania wartości liczbowych do więcej / mniej / równy / pomiędzy w porównaniu ze stałymi numerycznymi.

Reguły te są używane dość często, dlatego w EXCEL 2007 są umieszczone w osobnym menu Reguły wyboru komórek.

Te zasady są również dostępne w menu. Strona główna / Style / Formatowanie warunkowe / Utwórz regułę, Formatuj tylko komórki zawierające.

Rozważ kilka zadań:

PORÓWNANIE Z STAŁĄ WARTOŚCIĄ (STAŁĄ)

Zadanie 1 A1: D1 z numerem 4.

• wprowadzić do oferty A1: D1 wartości 1, 3, 5, 7
• wybierz ten zakres;
• Formatowanie warunkowe na wartości Mniej ();
• w lewym polu okna, które się pojawi, wprowadź 4 - natychmiast zobacz wynik zastosowania Formatowanie warunkowe.
• Kliknij OK.

Zadanie 1.

PORÓWNANIE Z WARTOŚCIĄ W KOMÓREK (ODNIESIENIE ABSOLUTNE)

Skomplikujmy trochę poprzednie zadanie: zamiast wpisywać bezpośrednio wartość (4) jako kryterium, wprowadzamy link do komórki zawierającej wartość 4.

Zadanie 2. Porównaj wartości z zakresu A1: D1 z numerem z komórki A2 .

• wejść do komórki A2 numer 4;
• wybierz zakres A1: D1 ;
• dotyczy wybranego zakresu Formatowanie warunkowe na wartości Mniej (Strona główna / Style / Formatowanie warunkowe / Reguły wyboru komórek / Mniej);
• w lewym polu okna, które się pojawi, wprowadź link do komórki A2 klikając przycisk znajdujący się po prawej stronie okna (EXCEL domyślnie używa linku 2 USD ).

Kliknij OK.

W rezultacie wszystkie wartości z wybranego zakresu ZA 1: re 1 zostanie porównany z jedną komórką 2 USD . Te wartości z ZA 1: re 1 które są mniejsze ZA 2 zostanie podświetlony przez wypełnienie tła komórki.

Wynik można zobaczyć w przykładowym pliku na arkuszu. Zadanie 2.

Aby zobaczyć, jak skonfigurowana jest właśnie utworzona reguła formatowania, kliknij ; następnie kliknij dwukrotnie regułę lub kliknij przycisk Zmień regułę. W rezultacie zobaczysz okno dialogowe pokazane poniżej.

POWIĄZANE PORÓWNANIE WIERSZY / KOLUMN (ODNIESIENIA WZGLĘDNE)

• wejdź do komórek zakresu ZA 2: re 2 wartości liczbowe (można uznać za ich kryteria);
• wybierz zakres ZA 1: re 1 ;
• dotyczy wybranego zakresu Formatowanie warunkowe na wartości Mniej (Strona główna / Style / Formatowanie warunkowe / Reguły wyboru komórek / Mniej)
• w lewym polu okna, które się pojawi, wprowadź względny link do komórki ZA 2 (tj. po prostu A2 lub link mieszany 2 USD ) Upewnij się, że znak $ nie znajduje się przed kolumną A.

Teraz każda wartość z rzędu 1 zostanie porównany z odpowiednią wartością z ciągu 2 w tej samej kolumnie! Wartości 1 i 5 zostaną podświetlone, ponieważ mają odpowiednio mniej niż 2 i 6, umieszczone w linii 2.

Wynik można zobaczyć w przykładowym pliku na arkuszu. Zadanie 3.

Uwaga!Podczas korzystania z linków względnych w regułach Formatowanie warunkowe konieczne jest monitorowanie, która komórka jest aktywna w momencie wywołania narzędzia Formatowanie warunkowe .

Uwaga dotycząca dygresji: Znaczenie poprawiania aktywnej komórki podczas tworzenia reguł formatowania warunkowego z odnośnikami względnymi

Podczas tworzenia względnych łączy w regułach Formatowanie warunkowe, „przyciągają” do komórki, to znaczy aktywny w momencie wywołania narzędzia Formatowanie warunkowe.

WSKAZÓWKA: Aby znaleźć adres aktywnej komórki (zawsze jest to jeden na arkuszu), możesz spojrzeć w pole Imię (znajduje się po lewej stronie). W zadaniu 3 po przydzieleniu zakresu A1: D1 (przycisk myszy powinien zostać zwolniony), w, tam zostanie wyświetlony adres aktywnej komórki A1 lub re 1 . Dlaczego są 2 możliwe opcje i jaka jest różnica dla reguł formatowania warunkowego?

Przyjrzymy się uważnie drugiemu krokowi rozwiązania poprzedniego problemu3 - wybór zakresu ZA 1: re 1 . Określony zakres można rozróżnić na dwa sposoby: wybierz komórkę A1 , a następnie, nie zwalniając przycisku myszy, wybierz cały zakres, przechodząc w prawo do D1 ; albo wybierz komórkę D1 , a następnie, nie zwalniając przycisku myszy, wybierz cały zakres, przesuwając w lewo do A1 . Różnica między tymi dwiema metodami jest fundamentalna: w pierwszym przypadku po zakończeniu wyboru zakresu aktywna komórka będzie A1 , a w drugim re 1 !

Zobaczmy teraz, jak wpływa to na regułę formatowania warunkowego za pomocą łącza względnego.

Jeśli wybraliśmy zakres w pierwszy sposób, to wprowadzając regułę Formatowanie warunkowe względne odwołanie do komórki A2 , tym samym powiedzieliśmy EXCEL, aby porównał wartość aktywnej komórki A1 z wartością w A2 . Ponieważ reguła dotyczy zakresu ZA 1: re 1 następnie b 1 będzie porównywany do W 2 itp. Zadanie zostanie poprawnie rozwiązane.

Jeśli podczas tworzenia reguły Formatowanie warunkowe komórka była aktywna D1 , wówczas jego wartość zostanie porównana z wartością komórki A2 . I wartość ZA 1 będzie teraz porównywany z wartością z komórki Xfb2 (nie znajduje komórek po lewej stronie ZA 2 EXCEL wybierze najnowszą komórkę Xfd dla C1 następnie przedostatni dla b 1 i w końcu Xfb2 dla A1 ) Możesz to sprawdzić, patrząc na utworzoną regułę:

• wybierz komórkę A1 ;
• kliknij Strona główna / Style / Formatowanie warunkowe / Zarządzanie regułami;
• teraz można to zauważyć w odniesieniu do zasięgu $ A $ 1: $ D $ 1 reguła obowiązuje Wartość komórki <Xfb2 (lub<XFB 2 USD ).

EXCEL wyświetla regułę formatu ( Wartość komórki ) w zastosowaniu do aktywnej komórki, tj. do A1 . Prawidłowo zastosowana reguła w naszym przypadku wygląda następująco:

WYBÓR LINII

WYBÓR KOMÓREK Z NUMERAMI

PRIORYTET ZASAD

Aby sprawdzić reguły zastosowane do zakresu, użyj Menedżer reguł formatowania warunkowego(Strona główna / Style / Formatowanie warunkowe / Zarządzanie regułami).

Gdy dwie lub więcej reguł dotyczy jednej komórki Formatowanie warunkowe, priorytet przetwarzania zależy od kolejności, w jakiej są wymienione Menedżer reguł formatowania warunkowego. Reguła znajdująca się na powyższej liście ma wyższy priorytet niż reguła znajdująca się na poniższej liście. Nowe reguły są zawsze dodawane na początku listy i dlatego mają wyższy priorytet, jednak kolejność reguł można zmienić w oknie dialogowym za pomocą przycisków strzałek W górę i Na dół.

Na przykład komórka zawiera liczbę 9 i stosuje się do niej dwie reguły. Wartość komórki\u003e 6 (zestaw formatów: czerwone tło) i Wartość komórki\u003e 7(zestaw formatów: zielone tło), patrz rysunek powyżej. Ponieważ zasada Wartość komórki\u003e 6 (format jest ustawiony: czerwone tło) znajduje się wyżej, a następnie ma wyższy priorytet, a zatem komórka o wartości 9 będzie miała czerwone tło. Na Pole wyboru Zatrzymaj, jeśli prawda możesz go zignorować, jest on instalowany w celu zapewnienia zgodności z poprzednimi wersjami EXCEL, które nie obsługują jednoczesnego stosowania kilku reguł formatowania warunkowego. Chociaż można go użyć do anulowania jednej lub więcej reguł podczas korzystania z kilku zestawów reguł dla zakresu (gdy nie ma konfliktu między regułami). Więcej szczegółów jest dostępnych.

Jeśli reguła formatowania dotyczy zakresu komórek, ma ona pierwszeństwo przed formatowaniem ręcznym. Formatowanie ręczne można wykonać za pomocą polecenia Format z grupy Komórki na karcie Narzędzia główne. Po usunięciu reguły formatowania warunkowego pozostaje formatowanie ręczne.

FORMACJA WARUNKOWA I FORMAT KOMÓREK

Formatowanie warunkowe nie zmienia formatu zastosowanego do tej komórki (karta główna, grupa czcionek lub kliknięcie) CTRL + SHIFT + F.) Na przykład, jeśli czerwone wypełnienie komórki jest ustawione w formacie komórki i działała reguła formatowania warunkowego, zgodnie z którą wypełnienie tej komórki powinno być żółte, wówczas wypełnienie formatowania warunkowego „wygra” - komórka zostanie podświetlona na żółto. Chociaż wypełnienie Formatowanie warunkowe jest stosowane na wierzchu wypełnienia Format komórki, nie zmienia się (nie anuluje go) i jest po prostu niewidoczne.

DEBUGOWANIE WARUNKÓW REGULAMINU FORMACJI

Aby sprawdzić, czy reguły formatowania warunkowego są wykonywane poprawnie, skopiuj formułę z reguły do \u200b\u200bdowolnej pustej komórki (na przykład do komórki po prawej stronie komórki z formatowaniem warunkowym). Jeśli formuła zwraca wartość PRAWDA, reguła działała; jeśli FAŁSZ, warunek nie jest spełniony i formatowanie komórki nie powinno być zmieniane.

Wracamy do problemu 3 (zobacz sekcję dotyczącą odnośników powyżej). W wierszu 4 piszemy formułę z reguły formatowania warunkowego \u003d A1

W tych kolumnach, w których wynikiem formuły jest PRAWDA, zastosowane zostanie formatowanie warunkowe, a gdzie FAŁSZ - nie.

WYKORZYSTANIE ODNIESIENIA DO INNYCH ARKUSZY REGULAMINU

Przed MS Excel 2010 dla reguł Formatowanie warunkowe niemożliwe było bezpośrednie użycie linków do innych arkuszy lub książek. Można obejść to ograniczenie, używając. Jeśli w Formatowanie warunkowemusisz na przykład utworzyć link do komórki A2 inny arkusz, musisz najpierw ustalić imię dla tej komórki, a następnie odwołać się do niej imię z reguły Formatowanie warunkowe. Jak to jest realizowane Zobacz plik przykładowy na arkuszu. Link z innego arkusza.

WYSZUKIWANIE WARUNKOWO WYKONANYCH KOMÓREK

• na zakładce Dom w grupie Redagowanie kliknij strzałkę obok polecenia Znaleźć i wyróżnij,
• wybierz z listy Formatowanie warunkowe.

Zostaną wybrane wszystkie komórki, dla których określono reguły formatowania warunkowego.

INNE ZDEFINIOWANE ZASADY

W menu Strona główna / Style / Formatowanie warunkowe / Reguły wyboru komórek Programiści EXCEL stworzyli różnorodne reguły formatowania.

Aby nie wymyślać koła na nowo, przyjrzymy się bliżej niektórym z nich.

• Tekst zawiera ...Dajemy przykład. Niech w komórce będzie słowo Wiercić Tekst zawiera... Jeśli piszemy jako kryterium re (zaznacz słowa zawierające sylabę re), a następnie słowo Wiercić zostanie podświetlony.

Teraz spójrzmy na właśnie utworzoną regułę w menu Strona główna / Style / Formatowanie warunkowe / Zarządzanie regułami ...

Jak widać na powyższym rysunku, Formatowanie warunkowe Możesz skonfigurować, aby wybierać nie tylko komórki, zawierający konkretny tekst, ale także nie zawiera, zaczynając od i kończąc na konkretny tekst. Ponadto w przypadku warunków zawiera i nie zawiera aplikacja jest możliwa.

Niech znowu będzie słowo w komórce Wiercić. Wybierz komórkę i zastosuj regułę. Tekst zawiera... Jeśli piszemy jako kryterium r ?, to słowo Wiercić zostanie podświetlony. Kryterium oznacza: wyróżnij słowa zawierające sylaby re, ra, re itp. Musisz zrozumieć, że słowa z frazami również zostaną podświetlone. p2, pm, pQponieważ znak? oznacza dowolną postać. Jeśli piszemy jako kryterium ?????? (wybierz słowa zawierające co najmniej 6 liter), a następnie odpowiednio słowo Wiercić nie będzie podświetlony. Oczywiście podobny wynik można osiągnąć za pomocą formuł z funkcjami PSTR (), LEVSIMV (), DLSTR (), ale takie podejście, jak widać, jest szybsze.

• Zduplikowane wartości ... Ta reguła pozwala szybko skonfigurować Formatowanie warunkowe wyświetlać i powtarzać wartości. Pod unikalną wartością Formatowanie warunkowe implikuje wartość niepowtarzalną, tj. wartość występująca tylko raz w zakresie, do którego stosowana jest reguła. Aby wyróżnić unikalne wartości (tj. Wszystkie wartości bez ich powtarzania), patrz.
• Data… Poniższy rysunek pokazuje kryteria wyboru dla tej reguły. Aby osiągnąć ten sam wynik, stosowanie formuł będzie wymagało znacznie więcej czasu.

• Wartość komórki Ta reguła jest dostępna w menu. W wyświetlonym oknie wybierz sformatuj komórki, które zawierają.Wybór opcji pozwoli Ci wykonać większość zadań związanych z wyborem wartości liczbowych.

Radzę również zwrócić uwagę na następujące zasady z menu Strona główna / Style / Formatowanie warunkowe / Reguły wyboru pierwszej i ostatniej wartości.

• Ostatnie 10 pozycji.

Zadanie 4. Dla wygody niech będą 21 znaczeń. Zastosuj regułę Ostatnie 10 pozycji i ustaw tak, aby 3 wartości (elementy) były podświetlone. Zobacz przykładowy arkusz pliku Zadanie 4.

Słowa „3 ostatnie wartości” oznaczają 3 najmniejsze znaczenia. Jeśli na liście są powtórzenia, wszystkie odpowiadające powtórzenia zostaną podświetlone. Na przykład w naszym przypadku trzecia najmniejsza to trzecia wartość powyżej 10. Ponieważ na liście jest 10 powtórzeń (w sumie jest ich 6), a następnie zostaną podświetlone.

W związku z tym zasady zastosowane do naszej listy: „Ostatnia 1 wartość”, „Ostatnie 2 wartości”, ... „Ostatnie 6 wartości” doprowadzą do tego samego rezultatu - przydział 6 wartości równych 10.

Zastosowanie reguły „Ostatnie 7 wartości” doprowadzi do przydzielenia dodatkowo wszystkich wartości równych 11, Tk. Siódma wartość minimalna to pierwsza wartość powyżej 11.

Podobnie możesz utworzyć regułę, aby podkreślić potrzebę liczby największych wartości, stosując regułę Pierwsze 10 przedmiotów.

• Ostatnie 10%

Rozważ inną pokrewną zasadę Ostatnie 10%.

Pamiętaj, że na obrazku powyżej zaznaczenie „% wybranego zakresu” nie jest ustawione. To pole wyboru jest ustawiane ręcznie lub podczas stosowania reguły Ostatnie 10%.

Ta reguła ustawia procent najmniejszych wartości całkowitej liczby wartości na liście. Na przykład ustawienie 20% tych ostatnich spowoduje wyróżnienie 20% najmniejszych wartości.

Spróbujmy ustawić 20% ostatnich na naszej liście 21 wartości: sześć wartości 10 zostanie podświetlonych (patrz przykładowy plik, arkusz Zadanie 4). 10 - minimalna wartość na liście, więc w każdym przypadku wszystkie jej powtórzenia zostaną podświetlone.

Ustawiając wartości procentowe od 1 do 33%, otrzymujemy, że wybór się nie zmieni. Dlaczego? Po ustawieniu na przykład 33%, otrzymujemy, że konieczne jest przydzielenie 6,93 wartości. Ponieważ możesz wybrać tylko liczbę całkowitą wartości; Formatowanie warunkowe zaokrągla całość, odrzucając część ułamkową. Ale przy 34% konieczne jest już przydzielenie wartości 7,14, tj. 7 oraz biorąc pod uwagę powtórzenia wartości 11 po 10, podświetlone zostaną wartości 6 + 3 \u003d 9.

ZASADY FORMUŁY

Tworzenie reguł formatowania na podstawie formuł jest ograniczone tylko wyobraźnią użytkownika. Tutaj rozważamy tylko jeden przykład, pozostałe przykłady użycia Formatowanie warunkowe można znaleźć w tych artykułach :; ; ; .

Załóżmy, że chcesz wybrać komórki zawierające błędne wartości:

• Wybierz komórki, które chcesz zastosować Formatowanie warunkowe (niech to będzie komórka A1 ).
• Zadzwoń do narzędzia Formatowanie warunkowe (Strona główna / Style / Formatowanie warunkowe / Utwórz regułę)
• Wybierz Użyj formuły, aby zdefiniować sformatowane komórki

• W polu ” Sformatuj wartości, dla których jest zgodna następująca formuła»Enter \u003d EOSH (A1) - jeśli chcemy wyróżnić komórki zawierające błędne wartości, tj zostanie podświetlony # WARTOŚĆ!, #LINK!, #CAS / 0!, #NUMBER!, #NAME? lub # PUSTY! (oprócz # nie dotyczy)
• Wybierz żądany format, na przykład czerwony kolor wypełnienia.

Ten sam wynik można osiągnąć w inny sposób:

• Zadzwoń do narzędzia Formatowanie warunkowe (Strona główna / Style / Formatowanie warunkowe / Utwórz regułę)
• Atrakcja Sformatuj tylko komórki zawierające;
• W sekcji Sformatuj tylko komórki, dla których spełniony jest następujący warunek: z listy rozwijanej najbardziej po lewej stronie wybierz Błędy

Komentarze

Tatyana (niezweryfikowany)

Tatyana (niezweryfikowany)

Twórca

Znowu ja. (Nie zweryfikowany)

Twórca

Tatyana (niezweryfikowany)

Twórca

Alexander 555 (niezweryfikowany)

Twórca

Każdy menedżer, księgowy, a nawet zwykły użytkownik wielokrotnie natrafiał na doskonałe funkcje programu Excel 2010, który pozwala szybko i wygodnie pracować z tabelami. Ta wersja programu ma zaawansowane formatowanie warunkowe tabel przestawnych, co poprawia cały przepływ pracy.

Mamy funkcję wizualizacji, która zapewnia możliwość korzystania z histogramów, różnorodnych ikon i wszelkiego rodzaju skal kolorów w komórkach. Teraz możesz łatwo utworzyć okno w stylu konsoli sterowania, co ułatwia wyszukiwanie informacji. Również teraz tego typu formatowanie zaczęło być z powodzeniem stosowane w tabelach przestawnych, a ściślej - do ich struktury (a nie tylko danych). Metoda opisana poniżej jest odpowiednia nawet dla tych, którzy zamierzają kupić nową pralkę i wybrać model.

Oto najprostszy przykład tabeli przestawnej, która opisuje wielkość sprzedaży produktu według regionu:

Teraz, w oparciu o te dane, stworzymy graficzny raport sprzedaży dla każdego okresu, aby ułatwić odbiór informacji. Oczywiście możesz użyć wykresu przestawnego, ale formatowanie warunkowe w programie Excel 2010 jest również dobre.

Najłatwiejszą opcją jest użycie skal kolorów. Aby to zrobić, wybierz pole „Sprzedaż” obejmujące wszystkie okresy. Pozostaje otworzyć zakładkę „Strona główna”, gdzie klikamy przycisk „Formatowanie warunkowe” (jeśli nagle użyjesz wersji angielskiej, wówczas funkcja ta nazywa się „Formatowanie warunkowe”). Przesuń kursor nad histogramami.

Istniejący zestaw wypełnień gradientowych i pełnych w istocie przypomina standardowy histogram poziomy. A jeśli teraz zastosujemy filtrowanie danych dowolnego wykresu w zakresie filtra, zauważymy dynamiczną zmianę histogramów.

Excel 2010 ma już znaczną liczbę całkowicie gotowych skryptów formatujących, które znacznie oszczędzają czas. Nawiasem mówiąc, aby utworzyć histogramy opisane powyżej, program wykorzystuje algorytm, który niezależnie określa maksymalne i minimalne wartości zakresu wybranych komórek, a dopiero potem formatuje je na podstawie przetworzonych informacji.

Tak więc - gotowe skrypty, które mogą pomóc w większości sytuacji:

- pierwsze 10 elementów;

- ostatnie 10 elementów;

- pierwsze 10%;

- ostatnie 10%;

- więcej niż średnia;

- mniej niż średnia.

Użyte już formatowanie warunkowe jest usuwane w programie Excel 2010 zgodnie z następującym schematem: w tabeli przestawnej przejdź do karty „Strona główna”, kliknij „Formatowanie warunkowe”, a następnie „Style”, a następnie w menu rozwijanym użyj polecenia „Usuń reguły” - „Usuń reguły z” ta tabela przestawna ”(w wersji angielskiej -„ Wyczyść reguły ”i„ Wyczyść reguły z tej tabeli przestawnej ”).

Możesz jednak łatwo stworzyć własne reguły formatowania.

Ta tabela jest skomplikowaną wersją pierwszej, przejdźmy więc do przykładu. Śledźmy sprzedaż i przychody na godzinę. W programie Excel 2010 użyjemy formatowania warunkowego, aby przyspieszyć wyszukiwanie dopasowań i różnic. Podświetl „Sprzedaż”. Następnie, zgodnie ze standardową procedurą, aktywujemy skrypt („Home” - „Formatowanie warunkowe”), ale wybieramy nie gotową opcję, ale funkcję „Utwórz regułę” (lub „Nowa reguła”).

To tutaj możesz określić komórki, do których zostanie zastosowane formatowanie warunkowe w programie Excel 2010, rodzaj zastosowanej reguły i, w rzeczywistości, opcje formatowania. Komórki są ustawiane jako pierwsze i tutaj jest dość prosty wybór:

- wybrane („Wybrane komórki”);

- uwzględnione w kolumnie „Sprzedaż” („Wszystkie komórki pokazujące” Sprzedaż_kwota „Wartości”), w tym sumy częściowe i sumy całkowite. Nawiasem mówiąc, ta opcja dobrze nadaje się do analizy danych, które wymagają określenia wartości średniej, procentowej lub innej wartości, które są w jakiś sposób różnymi poziomami tej samej wartości;

- uwzględnione w kategorii „Wielkość sprzedaży” tylko dla „Rynku sprzedaży” („Wszystkie komórki pokazujące” Kwota sprzedaży ”Wartości dla„ Rynku ”). Ta opcja całkowicie wyklucza ogólne i pośrednie sumy, co jest wygodne do analizy niektórych indywidualnych wartości.

Pamiętaj, że zespoły „Wielkość sprzedaży”, „Rynek sprzedaży” podczas tworzenia reguł zmieniają się w zależności od dostępnych arkuszy.

W naszym przykładzie opcja „3” jest najbardziej opłacalna, dlatego używa się tej opcji:

Wybierając regułę (sekcja „Wybierz regułę” lub „Wybierz regułę”), określamy dokładnie tę, która spełnia nasze wymagania.

Mogłoby być:

- „Formatowanie komórek na podstawie ich wartości”. Służy do formatowania komórek pasujących do zakresu użytych wartości. Najlepiej nadaje się do określania szerokiej gamy odchyleń, jeśli musisz pracować z ogromnym zestawem danych.

- „Formatowanie komórek zawierających” („Formatuj tylko komórki zawierające”). Formatuje komórki spełniające odpowiednie warunki. W takim przypadku porównanie wartości sformatowanych komórek z normalnymi nie występuje. Służy do porównania ogólnego zestawu danych z poprzednio określoną charakterystyką.

- „Formatowanie pierwszej i ostatniej wartości” („Formatuj tylko górne lub dolne wartości rankingowe”).

- „Formatuj wartości poniżej lub powyżej średniej” („Formatuj tylko wartości powyżej lub poniżej średniej”).

- „Użyj formuły, aby określić, które komórki sformatować”. Tutaj warunki formatowania warunkowego są już oparte na formule określonej przez użytkownika. Jeśli wartość komórki (z podstawionych we wzorze) zawiera wartość „prawda”, wówczas formatowanie jest stosowane do komórki. W przypadku fałszu formatowanie nie jest stosowane.

Korzystanie z histogramów, zestawów ikon i skal kolorów jest możliwe tylko wtedy, gdy formatowanie wybranych komórek opiera się na wprowadzonych w nich wartościach. Aby to zrobić, ustaw pierwszy przełącznik na „Formatuj wszystkie komórki na podstawie ich wartości”. Do identyfikacji obszarów problemowych można użyć zestawu ikon, co jest również odpowiednie dla tego scenariusza.

Cóż, pozostaje określić dokładne parametry naszego formatowania. Przydaje się sekcja „Edytuj opis ruie”. Aby dodać ikony do problematycznych komórek, używamy menu rozwijanego „Formatuj styl” i wybieramy „Zestawy ikon”.

Na liście „Stylu ikon” pozostaje wybrać wartość „3 znaki”. Jest to odpowiednie, jeśli istniejącego stołu nie można całkowicie pomalować. W rezultacie w oknie powinniśmy uzyskać:

W tej konfiguracji Excel niezależnie doda ikony do komórek, wykonując tę \u200b\u200bfunkcję:

\u003e \u003d 67,\u003e \u003d 33 i<33.

Umożliwi to natychmiastowe rozdzielenie ikon na 3 kategorie, co wizualnie ułatwi otrzymywanie informacji podczas pracy ze stołem.

Dodajemy, że w każdym przypadku wartości graniczne można łatwo zmienić, aw tym przykładzie po prostu zastosowano wartości domyślne.

Pozostaje tylko zastosować nasze zasady do tabeli. W rezultacie zmieni się tak:

Pozostaje zastosować tę samą regułę w kolumnie „Przychody na godzinę”.

Ale nie spiesz się, aby natychmiast zamknąć przykład - lepiej go przeanalizować. W końcu każdy mniej lub bardziej doświadczony menedżer natychmiast zauważy związek między przychodem na godzinę a przychodem całkowitym. Na przykład osoba odpowiedzialna za rynek w Dallas ustali, że jego wielkość sprzedaży jest minimalna, mimo że przychód na godzinę jest największy. To pozwala nam stwierdzić, że przychód na godzinę jest zbyt wysoki dla jego rynku.

Tymczasem jego kolega z Nowego Jorku ma inną sytuację. Dzięki wysokiemu ogólnemu poziomowi dochodów godzinowy przychód jest minimalny. I to już jest problem, pomimo dużej luki w całkowitych dochodach.

Jakie było ostatnie wytłumaczenie? Dla dobrego przykładu formatowanie warunkowe w programie Excel 2010 ułatwia postrzeganie różnych informacji. Posiadając niezbędne umiejętności w zakresie tworzenia filtrów formatowania, możesz szybko uzyskać niezbędne informacje.