Анализ данных Excel – условное форматирование

В Microsoft Excel вы можете использовать условное форматирование для визуализации данных. Вы должны указать форматирование для диапазона ячеек на основе содержимого диапазона ячеек. Ячейки, которые соответствуют указанным условиям, будут отформатированы так, как вы определили.

пример

В диапазоне, содержащем данные о продажах за последний квартал для группы продавцов, можно выделить те ячейки, представляющие, которые достигли определенной цели, скажем, 2500 долларов США.

Вы можете установить условие как общий объем продаж человека> = $ 2500 и указать зеленый цветовой код. Excel проверяет каждую ячейку в диапазоне, чтобы определить, удовлетворяется ли указанное вами условие, т. Е. Общие продажи человека> = 2500 долларов США.

Excel применяет выбранный формат, т. Е. Зеленый цвет, ко всем ячейкам, которые удовлетворяют условию. Если содержимое ячейки не удовлетворяет условию, форматирование ячейки остается неизменным. Результат, как и ожидалось, только для продавцов, которые достигли цели, ячейки выделены зеленым цветом – быстрая визуализация результатов анализа.

Вы можете указать любое количество условий для форматирования, указав Правила . Вы можете выбрать правила, которые соответствуют вашим условиям из

Вы также можете определить свои собственные правила. Вы можете –

Кроме того, у вас есть несколько вариантов форматирования в Excel, чтобы выбрать те, которые подходят для вашей визуализации данных –

Условное форматирование продвигалось в версиях Excel 2007, Excel 2010, Excel 2013. Примеры, которые вы найдете в этой главе, взяты из Excel 2013.

В следующих разделах вы поймете правила условного форматирования, параметры форматирования и порядок работы с правилами.

Выделите правила ячеек

Вы можете использовать правило Highlight Cells, чтобы назначить формат ячейкам, содержимое которых соответствует любому из следующих критериев:

• Числа в данном числовом диапазоне –
  • Лучше чем
  • Меньше, чем
  • Между
  • Равно
  • Вчера
  • сегодня
  • Завтра
  • За последние 7 дней
  • Прошлая неделя
  • На этой неделе
  • Следующая неделя
  • Прошлый месяц
  • Этот месяц
  • Следующий месяц

  Нажмите « Условное форматирование» в группе « Стили » на вкладке « Главная ».

  Нажмите « Выделить правила ячеек» в раскрывающемся меню.

  Нажмите « Условное форматирование» в группе « Стили » на вкладке « Главная ».

  Нажмите « Выделить правила ячеек» в раскрывающемся меню.

  

  Нажмите « Больше чем» и укажите> 750. Выберите зеленый цвет.

  Нажмите Between и укажите 500 и 750. Выберите желтый цвет.

  Нажмите « Больше чем» и укажите> 750. Выберите зеленый цвет.

  Нажмите Between и укажите 500 и 750. Выберите желтый цвет.

  

  Данные будут выделены в зависимости от заданных условий и соответствующего форматирования.

  

  Верхние / нижние правила

  Вы можете использовать правила Top / Bottom, чтобы назначить формат ячейкам, содержимое которых соответствует любому из следующих критериев:

  Топ 10 предметов – Клетки, которые ранжируются в верхнем N, где 1

  Верхние 10% – ячейки, которые занимают место в верхнем n%, где 1

  Нижние 10 элементов – ячейки, которые ранжируются в нижней части N, где 1

  Нижние 10% – ячейки, которые ранжируются в нижней части n%, где 1

  Выше среднего – клетки выше среднего для выбранного диапазона.

  Ниже среднего – ячейки ниже среднего для выбранного диапазона.

  Топ 10 предметов – Клетки, которые ранжируются в верхнем N, где 1

  Верхние 10% – ячейки, которые занимают место в верхнем n%, где 1

  Нижние 10 элементов – ячейки, которые ранжируются в нижней части N, где 1

  Нижние 10% – ячейки, которые ранжируются в нижней части n%, где 1

  Выше среднего – клетки выше среднего для выбранного диапазона.

  Ниже среднего – ячейки ниже среднего для выбранного диапазона.

  Следуйте приведенным ниже инструкциям, чтобы назначить верхние / нижние правила.

  Нажмите « Условное форматирование» в группе « Стили » на вкладке « Главная ».

  Выберите Top / Bottom Rules в раскрывающемся меню. Появятся варианты верхних / нижних правил.

  Нажмите « Условное форматирование» в группе « Стили » на вкладке « Главная ».

  Выберите Top / Bottom Rules в раскрывающемся меню. Появятся варианты верхних / нижних правил.

  

  Нажмите Top Ten Items и укажите 5. Выберите зеленый цвет.

  Нажмите Нижние десять пунктов и укажите 5. Выберите красный цвет.

  Нажмите Top Ten Items и укажите 5. Выберите зеленый цвет.

  Нажмите Нижние десять пунктов и укажите 5. Выберите красный цвет.

  Данные будут выделены в зависимости от заданных условий и соответствующего форматирования.

  Нажмите Top Ten% и укажите 5. Выберите зеленый цвет.

  Нажмите Нижнюю Десять% и укажите 5. Выберите красный цвет.

  Нажмите Top Ten% и укажите 5. Выберите зеленый цвет.

  Нажмите Нижнюю Десять% и укажите 5. Выберите красный цвет.

  Данные будут выделены в зависимости от заданных условий и соответствующего форматирования.

  

  Нажмите выше среднего . Выберите зеленый цвет.

  Нажмите ниже среднего . Выберите красный цвет.

  Нажмите выше среднего . Выберите зеленый цвет.

  Нажмите ниже среднего . Выберите красный цвет.

  Данные будут выделены в зависимости от заданных условий и соответствующего форматирования.

  

  Панели данных

  Нажмите « Условное форматирование» в группе « Стили » на вкладке « Главная ».

  Нажмите Панели данных в раскрывающемся меню. Появятся параметры градиентной заливки и заливки .

  Нажмите « Условное форматирование» в группе « Стили » на вкладке « Главная ».

  Нажмите Панели данных в раскрывающемся меню. Появятся параметры градиентной заливки и заливки .

  

  Нажмите на синюю строку данных в параметрах градиентной заливки .

  

  Нажмите на синюю строку данных в параметрах Solid Fill .

  Нажмите на синюю строку данных в параметрах Solid Fill .

  

  Вы также можете отформатировать столбцы данных так, чтобы строка данных начиналась в середине ячейки и растягивалась влево для отрицательных значений и растягивалась вправо для положительных значений.

  Цветовые шкалы

  • Три – Цветовая шкала –
    • Зеленый – Желтый – Красный Цвет Шкалы
    • Красный – Желтый – Зеленый Цвет Шкалы
    • Зеленый – Белый – Красный Цветовая шкала
    • Красный – Белый – Зеленый Цвет Шкалы
    • Синий – белый – красный цвет шкалы
    • Красный – Белый – Синий Цветовая шкала
    • Белый – красный цвет шкалы
    • Красно-белая цветовая шкала
    • Зелено-белая цветовая шкала
    • Белый – Зеленый Цвет шкалы
    • Зелено-желтая цветовая шкала
    • Желто-зеленая шкала

    Нажмите « Условное форматирование» в группе « Стили » на вкладке « Главная ».

    Нажмите Color Scales из выпадающего меню. Появятся параметры цветовой шкалы .

    Нажмите « Условное форматирование» в группе « Стили » на вкладке « Главная ».

    Нажмите Color Scales из выпадающего меню. Появятся параметры цветовой шкалы .

    Данные будут выделены на основе зеленой – желтой – красной цветовой шкалы в выбранном диапазоне.

    

    Данные будут выделены на основе зелено-белой цветовой шкалы в выбранном диапазоне.

    

    Наборы иконок

    Вы можете использовать наборы значков для визуализации числовых различий. Доступны следующие наборы значков:

    Как вы заметили, набор иконок состоит из трех-пяти символов. Вы можете определить критерии, чтобы связать значок с каждым значением в диапазоне ячеек. Например, красная стрелка вниз для небольших чисел, зеленая стрелка вверх для больших чисел и желтая горизонтальная стрелка для промежуточных значений.

    Нажмите « Условное форматирование» в группе « Стили » на вкладке « Главная ».

    Нажмите Наборы иконок в раскрывающемся меню. Появятся параметры « Наборы значков» .

    Нажмите « Условное форматирование» в группе « Стили » на вкладке « Главная ».

    Нажмите Наборы иконок в раскрывающемся меню. Появятся параметры « Наборы значков» .

    Цветные стрелки отображаются рядом с данными на основе значений в выбранном диапазоне.

    

    Повторите первые три шага. Появятся параметры « Наборы значков» .

    Выберите 5 оценок. Значки рейтинга отображаются рядом с данными на основе значений в выбранном диапазоне.

    Повторите первые три шага. Появятся параметры « Наборы значков» .

    Выберите 5 оценок. Значки рейтинга отображаются рядом с данными на основе значений в выбранном диапазоне.

    Новое правило

    Вы можете использовать Новое правило, чтобы создать собственную формулу в качестве условия для форматирования ячейки, которую вы определяете.

    Кнопка « Новое правило» в диалоговом окне « Управление правилами »

    Кнопка « Новое правило» в диалоговом окне « Управление правилами »

    С опцией New Rule из выпадающего меню

    Нажмите « Условное форматирование» в группе « Стили » на вкладке « Главная ».

    Нажмите « Условное форматирование» в группе « Стили » на вкладке « Главная ».

    

    Откроется диалоговое окно « Новое правило форматирования ».

    В поле «Выбрать тип правила» выберите «Использовать формулу», чтобы определить ячейки для форматирования. Откроется окно « Редактировать описание правила» .

    В значениях формата, где эта формула верна: введите формулу.

    В поле «Выбрать тип правила» выберите «Использовать формулу», чтобы определить ячейки для форматирования. Откроется окно « Редактировать описание правила» .

    В значениях формата, где эта формула верна: введите формулу.

    

    Ячейки, которые содержат значения с формулой TRUE, отформатированы как определено.

    

    С помощью кнопки «Создать правило» в диалоговом окне «Управление правилами»

    Нажмите « Условное форматирование» в группе « Стили » на вкладке « Главная ».

    Нажмите « Управление правилами» в раскрывающемся меню.

    Нажмите « Условное форматирование» в группе « Стили » на вкладке « Главная ».

    Нажмите « Управление правилами» в раскрывающемся меню.

    Откроется диалоговое окно диспетчера правил условного форматирования .

    Откроется диалоговое окно « Новое правило форматирования ».

    Повторите шаги, указанные выше, чтобы определить формулу и формат.

    

    Откроется диалоговое окно диспетчера правил условного форматирования с выделенным определенным новым правилом . Нажмите кнопку Применить .

    Ячейки, которые содержат значения с формулой TRUE, отформатированы как определено.

    

    Очистить правила

    Вы можете очистить правила, чтобы удалить все условные форматы, которые вы создали для

    Выберите диапазон / щелкните рабочий лист / щелкните таблицу> сводную таблицу, где необходимо удалить правила условного форматирования.

    Нажмите « Условное форматирование» в группе « Стили » на вкладке « Главная ».

    Нажмите Clear Rules в раскрывающемся меню. Появятся параметры очистки правил.

    Выберите диапазон / щелкните рабочий лист / щелкните таблицу> сводную таблицу, где необходимо удалить правила условного форматирования.

    Нажмите « Условное форматирование» в группе « Стили » на вкладке « Главная ».

    Нажмите Clear Rules в раскрывающемся меню. Появятся параметры очистки правил.

    

    Выберите подходящий вариант. Условное форматирование очищается из диапазона / рабочего листа / таблицы / сводной таблицы.

    Управлять правилами

    Вы можете управлять правилами из окна диспетчера правил условного форматирования . Вы можете увидеть правила форматирования для текущего выбора, для всей текущей рабочей таблицы, для других рабочих таблиц в рабочей книге или таблиц или сводных таблиц в рабочей книге.

    Нажмите « Условное форматирование» в группе « Стили » на вкладке « Главная ».

    Нажмите « Управление правилами» в раскрывающемся меню.

    Нажмите « Условное форматирование» в группе « Стили » на вкладке « Главная ».

    Нажмите « Управление правилами» в раскрывающемся меню.

    

    Откроется диалоговое окно диспетчера правил условного форматирования .

    Щелкните стрелку в поле «Список» рядом с надписью «Показать правила форматирования для текущего выбора», «Этот рабочий лист и другие листы», «Таблицы», «Сводная таблица», если они существуют с правилами условного форматирования.

    

    Выберите этот лист из раскрывающегося списка. Правила форматирования на текущем рабочем листе отображаются в том порядке, в котором они будут применены. Вы можете изменить этот порядок, используя стрелки вверх и вниз.

    

    Вы можете добавить новое правило, отредактировать правило и удалить правило.

    Вы уже видели Новое правило в предыдущем разделе. Вы можете удалить правило, выбрав Правило и нажав Удалить правило . Выделенное правило будет удалено.

    Чтобы отредактировать правило, выберите ПРАВИЛО и нажмите « Изменить правило». Откроется диалоговое окно « Редактировать правило форматирования ».

    Изменения в правиле будут отражены в диалоговом окне диспетчера правил условного форматирования . Нажмите Применить .

    Данные будут выделены на основе измененных правил условного форматирования .

    Анализ данных Excel - условное форматирование.

    Вы можете установить условие как общий объем продаж человека> = $ 2500 и указать зеленый цветовой код. Excel проверяет каждую ячейку в диапазоне, чтобы определить, удовлетворяется ли указанное вами условие, т. Е. Общие продажи человека> = 2500 долларов США.
    

    Мнение эксперта

    Знайка, самый умный эксперт в Цветочном городе

    Если у вас есть вопросы, задавайте их мне!

    Задать вопрос эксперту

    Результат, как и ожидалось, только для продавцов, которые достигли цели, ячейки выделены зеленым цветом быстрая визуализация результатов анализа. Если же вы хотите что-то уточнить, я с радостью помогу!
    Если вы выберите стрелки в качестве значков для выражения цветового диапазона, то интересным вариантом являются стрелки. Так как здесь, помимо выражения диапазона ячейки цветом используются ещё и направления.
    

    Условное форматирование в Excel » Компьютерная помощь

    • Числа в данном числовом диапазоне –
      • Лучше чем
      • Меньше, чем
      • Между
      • Равно
      • Вчера
      • сегодня
      • Завтра
      • За последние 7 дней
      • Прошлая неделя
      • На этой неделе
      • Следующая неделя
      • Прошлый месяц
      • Этот месяц
      • Следующий месяц

      Одна из самых полезных функций в Excel — это условное форматирование. Это позволяет вам предоставлять визуальные индикаторы для данных в вашей электронной таблице. Используя условное форматирование в Excel, вы можете показать, находятся ли данные выше или ниже пределов, в тренде вверх или вниз, или намного больше.

      КАК: Использование формул для условного форматирования в Excel — 2024

      Excel 2013 Применение условного форматирования с помощью формул (Апрель 2024).

      Table of Contents:

      Добавление условного форматирования в Excel позволяет применять различные параметры форматирования к ячейке или диапазону ячеек, которые соответствуют определенным условиям, которые вы установили. Параметры форматирования применяются только тогда, когда выбранные ячейки отвечают этим установленным условиям.

      Параметры форматирования, которые могут быть применены, включают изменения цвета шрифта и фона, стили шрифтов, границы ячеек и добавление форматирования чисел к данным.

      Excel имеет ряд встроенных опций для обычно используемых условий, таких как поиск чисел, которые больше или меньше определенного значения, или для нахождения чисел, которые выше или ниже среднего значения.

      В дополнение к этим предустановленным параметрам также можно создавать собственные правила условного форматирования с использованием формул Excel для проверки условий, заданных пользователем.

      Применение нескольких правил в Excel

      Для одних и тех же данных можно применять более одного правила для проверки различных условий. Например, в бюджетных данных могут быть установлены условия, которые применяют форматирование изменений, когда расходуются определенные уровни, такие как 50%, 75% и 100% от общего бюджета.

      В таких обстоятельствах Excel сначала определяет, конфликтуют ли различные правила, и если да, то программа следует установленному порядку приоритета, чтобы определить, какое условное правило форматирования применяется к данным.

      В следующем примере два специальных правила условного форматирования будут применяться к диапазону ячейки B2 в B5.

      • Первое правило проверяет, были ли данные в ячейки A2 в A5 больше соответствующего значения в Би 2 в B5 более чем на 25%.
      • Второе правило проверяет, не совпадают ли те же данные в A2: A5 превышает соответствующее значение в B2: B5 более чем на 50%.

      Как видно на изображении выше, если выполнено одно из указанных выше условий, цвет фона ячейки или клеток в диапазоне B1: B4 изменится.

      Правила, используемые для выполнения этой задачи, будут введены с использованием условного форматирования Новое правило форматирования диалоговое окно. Начните с ввода данных образца в ячейки A1-C5, как показано на изображении выше

      в третий шаг учебника добавят формулы к ячейкам С2: С4 которые показывают точную процентную разницу между значениями в ячейках A2: A5 а также B2: B5 чтобы проверить точность условных правил форматирования.

      Настройка правил условного форматирования

      Как уже упоминалось, условные правила форматирования, проверяющие два условия, будут введены с использованием условного форматирования Новое правило форматирования диалоговое окно. Начнем с установки пользовательского условного форматирования:

      Условное форматирование для поиска более 25 процентов увеличения

      1. основной момент ячейки B2 в B5 на листе.
      2. Нажми на Главная вкладка из лента.
      3. Нажми на Условное форматирование значок в лента чтобы открыть раскрывающийся список.
      4. выберите Новое правило для открытия Новое правило форматирования диалоговое окно.
      5. От Стиль: , выберите классический вариант.
      6. Во втором раскрывающемся списке нажмите на последнюю опциюИспользуйте формулу, чтобы определить, какие ячейки нужно форматировать.
      7. Введите формула приведенные выше в предоставленное пространство.
      8. Нажми на Формат с: выпадающий список, затем выберите Светло-красный наполнить темно-красным текстом вариант.
      9. Нажмите Хорошо закрыть диалоговые окна и вернуться на рабочий лист.
      10. На этом этапе цвет фона ячейки B3 а также B5 должен быть зеленым.

      Условное форматирование для поиска более 50 процентов увеличения

      основной момент ячейки B2 в B5 на листе.

      1. Нажми на Главная вкладка из лента.
      2. Нажми на Условное форматирование значок в лента чтобы открыть раскрывающийся список.
      3. выберите Новое правило для открытия Новое правило форматирования диалоговое окно.
      4. От Стиль: , выберите классический вариант.
      5. Во втором раскрывающемся списке нажмите на последнюю опциюИспользуйте формулу, чтобы определить, какие ячейки нужно форматировать.
      6. Введите формула приведенные выше в предоставленное пространство.
      7. Нажми на Формат с: выпадающий список, затем выберите Светло-зеленый Заполнить темным зеленым текстом вариант.
      8. Нажмите Хорошо закрыть диалоговые окна и вернуться на рабочий лист.

      Проверка правил условного форматирования

      Чтобы проверить правильность введенных правил форматирования, мы можем вводить формулы в ячейки С2: С5 который рассчитает точную процентную разницу между числами в диапазонах A2: A5 а также B2: B5.

      1. Нажмите на ячейка C2 чтобы сделать его активной ячейкой.
      2. Введите приведенную выше формулу и нажмите Войти на клавиатуре.
      3. Ответ 10% должен появиться в ячейка C2, указывая, что число в ячейка A2 на 10% больше, чем число в ячейка B2.
      4. Возможно, потребуется изменить форматирование на ячейка C2 чтобы отобразить ответ в процентах.
      5. Использовать ручка заполнения скопировать формулу из ячейка C2 в ячейки C3 в C5.
      6. Ответы для ячейки C3 в C5 должен составлять 30%, 25% и 60%.

      Ответы в этих ячейках показывают, что созданные условные правила форматирования верны, поскольку разница между ячейки A3 а также B3 превышает 25 процентов, а разница между ячейки A5 а также B5 составляет более 50 процентов.

      Cell B4 не изменил цвет, поскольку разница между ячейки A4 а также B4 равен 25 процентам, и в нашем правиле условного форматирования указано, что для цвета фона для зеленого цвета требуется процент больше 25%.

      Порядок приоритета для условного форматирования

      Когда к одному и тому же диапазону данных применяются несколько правил, Excel сначала определяет, конфликтуют ли правила. Конфликтующими правилами являются те, в которых выбранные параметры форматирования для каждого правила не могут применяться одновременно к тем же данным.

      В примере, используемом в этом руководстве, конфликты правил, поскольку оба правила используют один и тот же параметр форматирования — изменение цвета фона.

      В ситуации, когда второе правило истинно (разница в значении больше 50 процентов между двумя ячейками), то верно и первое правило (разница в значении больше 25 процентов).

      Поскольку ячейка не может одновременно иметь как красный, так и зеленый фон, Excel должен знать, какое условное правило форматирования должно применяться.

      Применяемое правило определяется порядком приоритета Excel, в котором указано, что правило, которое выше в списке в диалоговом окне «Диспетчер правил условного форматирования» имеет приоритет.

      Как показано на рисунке выше, второе правило, используемое в этом руководстве, выше в списке и, следовательно, имеет приоритет над первым правилом. В результате цвет фона ячейки B5 изменяется на красный.

      По умолчанию новые правила добавляются в верхнюю часть списка и, следовательно, имеют более высокий приоритет. Чтобы изменить порядок приоритета, используйте Вверх и вниз стрелки в диалоговом окне, как указано на изображении выше.

      Если два или более условных правила форматирования не конфликтуют, оба применяются, когда условие, которое каждое правило тестирует, становится истинным.

      Если первое правило условного форматирования в нашем примере отформатировало диапазон ячеек B2: B5 с зеленой рамкой вместо зеленого цвета фона, два условных правила форматирования не будут конфликтовать, так как оба формата могут применяться без вмешательства в другое.

      В следствии, ячейка B5 будет иметь как зеленую границу, так и красный цвет фона, поскольку разница между числами в ячейки A5 а также B5 больше, чем 25 и 50 процентов.

      Условное форматирование и регулярное форматирование

      В случае конфликтов между условными правилами форматирования и параметрами форматирования вручную применяются правила условного форматирования всегда имеют приоритет и будут применяться вместо любых добавленных вручную параметров форматирования.

      Использование форматирования форматированного HTML-текста в AIM Mail или AOL Mail

      Вот как использовать богатое форматирование HTML в ваших почтовых ящиках AIM Mail или AOL Mail, выделение жирным шрифтом, подчеркивание или выделение курсивом того, что вы пишете.

      Использование меток для упрощения ваших формул Excel 2003

      Использование меток в функциях и формулах в Excel 2003 — это простой способ ссылаться на блок данных в электронной таблице Excel.

      Использование пользовательских правил условного форматирования для дат в Excel

      Настройте внешний вид ячеек в Excel на основе математики даты — например, чтобы выделить прошлые счета-фактуры.

      КАК: Использование формул для условного форматирования в Excel - 2024

      Чтобы проверить правильность введенных правил форматирования, мы можем вводить формулы в ячейки С2: С5 который рассчитает точную процентную разницу между числами в диапазонах A2: A5 а также B2: B5.
      

      Мнение эксперта

      Знайка, самый умный эксперт в Цветочном городе

      Если у вас есть вопросы, задавайте их мне!

      Задать вопрос эксперту

      Во втором раскрывающемся списке нажмите на последнюю опцию Используйте формулу, чтобы определить, какие ячейки нужно форматировать. Если же вы хотите что-то уточнить, я с радостью помогу!
      Также здесь присутствует возможность «отбирать» ячейки не только относительно порядковых номер, но и «процентовки». Здесь присутствует несколько критериев, относительно которых будут выделяться определённые ячейки. Это:

      Руководство по условному форматированию в Excel — Технологии и программы

      • Первое правило проверяет, были ли данные в ячейки A2 в A5 больше соответствующего значения в Би 2 в B5 более чем на 25%.
      • Второе правило проверяет, не совпадают ли те же данные в A2: A5 превышает соответствующее значение в B2: B5 более чем на 50%.

      Называется данная функция «Условное форматирование». Итак, сегодня мы в подробностях разберём принцип работы с данным инструментом и расскажем, как сделать вашу таблицу более привлекательной. Давайте приступим.

      Технические характеристики → Полезное видео → Общие данные → Таблица размеров → Полезные советы → Как правильно рассчитать → Сыпучие продукты → Натяжные потолки и технологии→ Мера жидких продуктов