Условное форматирование в Excel: инструкции и использование

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

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

Условное форматирование имеет смысл и помогает вам

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

Информация: Сводная таблица («Сводная таблица») - это специальная таблица, с помощью которой можно рассчитать, обобщить и проанализировать большой объем данных. Кроме того, вы можете быстро сравнивать и определять тенденции в данных обследования.

Условное форматирование с существующими правилами для чисел, текстового содержимого или дат

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

Примеры:

• Работник склада хотел бы выделить цветным фоном все запасы менее 15 штук.
• Все города с населением более 150 000 человек должны быть отмечены в таблице.
• Производственная компания хочет выделить красным цветом все товары, произведенные в период с 1 января 2022-2023 по 31 января 2022-2023 годов.

Быстрое форматирование:

Выбор одной или нескольких ячеек.

Щелкните на вкладке «Пуск».

Перейдите к стрелке рядом с кнопкой «Условное форматирование» в «Стиле».

Щелкните «Правила выделения ячеек».

Выберите желаемую команду, например Б. больше, меньше, чем дата и т. Д.

Введите значения и выберите формат.

Расширенное форматирование:

1. Выберите одну или несколько ячеек в таблице или отчете сводной таблицы.

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

3. Щелкните «Управление правилами»: появится диалоговое окно «Диспетчер правил условного форматирования».

4. Выберите действие:

   • Щелкните «Новое правило»: откроется диалоговое окно «Новое правило форматирования».
   • Чтобы изменить формат, убедитесь, что в списке «Показать правило форматирования для» выбран правильный лист или таблица.
   • В поле «Применимо к» перейдите к «Диалоговое окно свертывания»; затем выберите новый диапазон ячеек на листе.
   • Нажмите кнопку «Развернуть диалоговое окно».
   • Выберите правило и нажмите «Изменить правило». Откроется диалоговое окно «Изменить правило форматирования».

5. Чтобы изменить диапазон полей в области значений «Применить правило к» отчета сводной таблицы, выполните следующие действия:

   • Выбор: Нажмите "Только эти ячейки".
   • Соответствующее поле: Нажмите «Все ячейки с одинаковыми полями».
   • Поле значения: Нажмите "Все ячейки".

6. В разделе «Выбрать тип правила» нажмите «Форматировать только те ячейки, которые содержат»

Теперь вы можете выполнять действия в разделе «Редактировать описание правила» в поле списка «Форматировать ячейки только с помощью»:

Форматировать по номеру, дате или времени:

Для этого выберите значение ячейки и оператор сравнения и введите число, дату или время. пример: Для «Между» введите от 300 до 500 или выберите «Равно» и введите дату.

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

Форматировать по тексту:

Выберите «Конкретный текст» и «Оператор отношения».

Введите текст, например B. на «содержит» слово «золото» или на «начинается с» префикса, который вы хотите найти.

Можно ввести максимум 255 символов.

Значения можно вернуть с помощью формул. Для этого формула должна начинаться с (=). Недействительные формулы не форматируются. Желательно сначала проверить формулу на наличие ошибок.

Форматировать по дате:

• Выберите дату и дату сравнения, например Б. Вчера, на следующей неделе или в этом месяце

Затем: Чтобы указать формат, нажмите «Форматировать», появится диалоговое окно «Форматирование ячеек». Выберите желаемый числовой формат, формат заливки, рамку или шрифт, нажмите «ОК».

Можно выбрать несколько форматов. Вы можете увидеть, какие форматы были выбраны в поле «Предварительный просмотр».

Условное форматирование с базами данных, цветовыми схемами и наборами символов

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

Форматирование ячеек с помощью гистограмм

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

Быстрое форматирование:

Отметьте желаемую область (ячейку, таблицу или весь лист).

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

Зайдите в «панель данных» и выберите заливку.

Форматирование ячеек с помощью цветных рамок

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

2-х цветная шкала

Быстрое форматирование:

Выберите ячейку, таблицу или весь лист.

На вкладке Главная щелкните Условное форматирование.

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

3-х цветная шкала

Третий цвет позволяет визуализировать средние значения - в дополнение к цветовому представлению высоких и низких значений, как в двухцветной шкале.

Быстрое форматирование:

Выберите ячейку, таблицу или весь лист.

На вкладке «Главная» щелкните «Условное форматирование».

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

Форматирование ячеек с помощью наборов символов

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

Быстрое форматирование:

Выберите ячейку, таблицу или весь лист.

На вкладке «Главная» щелкните «Условное форматирование».

Перейдите в «Наборы символов» и выберите нужный набор символов.

Создавайте правила форматирования самостоятельно

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

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

Перейдите в «Условное форматирование» на вкладке «Главная».

Выберите «Новое правило» и подтвердите нажатием «ОК».

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

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

Выделяя ячейки непосредственно на листе, вы можете вводить ссылки на ячейки в формуле. Это абсолютные ссылки на ячейки. Однако Excel также может настроить ссылки на ячейки для каждой ячейки в выбранной области. Если хотите, выберите относительные ссылки на ячейки.

Пошаговая инструкция по созданию правила

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

2. Возможны следующие действия:

   • Если вы хотите добавить условный формат, перейдите в «Новое правило».
   • Измените условный формат:
   • • Убедитесь, что вы находитесь на правильном листе.
     • Сначала в разделе «Применимо к» нажмите «Свернуть диалоговое окно». Затем выберите новый диапазон ячеек и перейдите в «Развернуть диалоговое окно».
     • Выберите желаемое правило, нажмите «Редактировать правило». Появится «Изменить правило форматирования».

3. В разделе «Выбрать тип правила» нажмите «Использовать формулу для определения форматируемых ячеек».

   • В разделе «Изменить описание правила» введите формулу в поле «Форматировать значения, для которых это правило верно». Формула начинается с (=) и должна возвращать «истина» (1) или «ложь» (0).
   • Щелкните Форматировать, чтобы отобразить диалоговое окно Формат ячеек.
   • Теперь выберите число, шрифт, формат заливки и т. Д., Которые следует использовать, если значение ячейки истинно, и подтвердите нажатием «OK». Выбранные форматы отображаются в «Предварительном просмотре».

Например, формула СЧЁТЕСЛИ возвращает логические значения. Если использовать эту формулу, могут быть определены повторяющиеся значения или содержимое, которое встречается с определенной частотой, может быть выделено цветом. Таким образом можно найти уникальные предметы и выделить их цветом. Другие формулы, которые можно использовать для условного форматирования:

• ОТДЫХ: автоматическая установка чередующихся цветов линий
• ИДЕНТИЧНО: с помощью этой формулы искомые данные могут быть отмечены в таблице Excel, которая соответствует указанному значению.
• ISERROR: автоматически помечает пустые ячейки в таблице Excel или удаляет значения ошибок из книги.
• НЕ и ЕЧИСЛО: форматирует все ячейки Excel, не содержащие чисел.
• МАКС .: в таблице выделяется наибольшее значение ячейки.
• MIN: позволяет условное форматирование наименьшего значения в списке Excel, чтобы минимальное значение можно было сразу увидеть.
• СЛЕВА: в зависимости от строки символов в начале ячейки Excel автоматически форматируются и получают одинаковый цвет текста.
• БОЛЬШОЙ и ВЛЕВО: разрешить раскрашивание ячейки с новым содержимым
• ВПРАВО: ячейки могут быть отформатированы на основе последнего символа строки.
• СЕГОДНЯ: при этом встречи могут быть отформатированы в цвете с использованием метода светофора или могут быть выделены значения даты, которые скоро будут актуальными.
• МЕСЯЦ или ГОД: позволяют разделить список Excel на месяцы или годы; С помощью YEAR также можно отмечать значения даты текущего года и выделять годовые значения.
• ИЛИ и ДЕНЬ НЕДЕЛИ: подсвечивает значения в соответствии с днем недели цветом

Пример создания правила для проверок И и ИЛИ:

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

Первое правило: Покупатель дома хочет проверить, соответствует ли запланированная покупка недвижимости его бюджету. Он может внести до 80 000 евро собственных средств в качестве первоначального взноса и обслуживать ежемесячный платеж по кредиту на сумму не более 1200 евро. Если и первоначальный взнос, и взнос по кредиту соответствуют этим требованиям, соответствующие ячейки будут отмечены зеленым цветом.

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

Формула для создания первого правила с использованием зеленого цвета выглядит следующим образом:

= И (ЕСЛИ ($ B $ 4 <= 80000,1); ЕСЛИ (ABS ($ B $ 5) <= 1200,1))

Формула для создания второго правила с использованием красного цвета выглядит следующим образом:

= ИЛИ (ЕСЛИ ($ B $ 4 = 1200,1))

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

Условное форматирование можно скопировать и применить к другим или новым данным на листе или удалить снова.

Перенести условное форматирование

Чтобы скопировать условное форматирование и вставить его в другие ячейки или диапазоны ячеек, используйте кнопку «Передать формат».

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

Теперь перейдите в «Пуск», а затем в «Формат передачи». Курсор выглядит как кисть.

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

С помощью ESC вы прекращаете использование кисти.

Вы по-прежнему можете использовать кисть для вставки условного форматирования, дважды щелкнув «Формат передачи».

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

Очистить условное форматирование

Различают процесс удаления на листе и процесс удаления в диапазоне ячеек.

Удалить на листе или на всем листе

Перейдите в «Условное форматирование» на вкладке «Главная».

Затем нажмите «удалить правила».

Затем нажмите «Удалить правила на всем листе».

Кроме того, вы можете удалить условное форматирование с помощью диалогового окна «Выбрать содержимое».

Удалить в диапазоне ячеек

Выделите ячейки, содержащие нужное условное форматирование.

Нажмите кнопку «Увеличительное стекло для анализа чтения» (кнопка расположена справа внизу рядом с выбранными данными).

Зайдите в «Удалить формат».

Найдите и удалите одинаковые условные форматы на всем листе

Щелкните ячейку с соответствующим условным форматом.

На вкладке «Главная» щелкните стрелку рядом с «Найти и выбрать», затем нажмите «Выбрать содержимое».

Щелкните «Условные форматы».

В разделе «Проверка данных» выберите «То же» (при этом будут выбраны все ячейки с одинаковым условным форматированием).

На вкладке «Пуск» перейдите в «Условное форматирование», затем «Удалить правила» и «Удалить правила в выбранных ячейках».

Условное форматирование в отчете сводной таблицы

С помощью отчета сводной таблицы пользователи могут быстро суммировать большие объемы данных. Диапазон ячеек, электронная таблица Excel или отчет сводной таблицы - условное форматирование практически везде одинаково. Только в отчете сводной таблицы есть несколько специальных функций, о которых вам следует знать:

• Некоторые условные форматы не работают с полями в диапазоне значений отчета сводной таблицы.
• Если вы каким-либо образом измените макет отчета сводной таблицы, условное форматирование будет сохраняться до тех пор, пока не будут удалены поля в базовых данных.
• Условное форматирование для полей в диапазоне значений может основываться на иерархии данных. Диапазон этого условного форматирования определяется следующим более низким или более высоким уровнем иерархии в строках для одного или нескольких столбцов или в столбцах для одной или нескольких строк.
• Диапазон условного форматирования для полей в диапазоне значений можно определить тремя способами:

• • отбор
  • Соответствующее поле
  • Поле значения

Метод по умолчанию - выбор.

Определение площади путем выбора

Перейдите к «Применить правило форматирования к».

Щелкните диалоговое окно «Новое правило форматирования» или «Изменить правило форматирования».

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

Определение площади через соответствующее поле

Этот метод работает, если у вас есть

• хотите избежать выбора множества не связанных между собой элементов
• хотите условно отформатировать несколько полей в диапазоне значений для уровня иерархии данных
• Хотите исключить частичные результаты

Определение площади с помощью поля значения

Этот метод работает, если у вас есть

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

Заключение: условное форматирование в Excel упрощает отслеживание данных

Если вы хотите выделить определенные значения данных в таблице Excel в цвете, условное форматирование в Excel идеально. У вас есть два варианта применения условного форматирования. Либо вы используете правила, предоставленные Excel, чтобы выделить текстовое содержимое, числовые значения или даты в цвете. Или вы можете сами определить правило с различными условиями, при которых должны быть отмечены соответствующие значения. Вы можете легко и эффективно сравнивать данные друг с другом, используя гистограммы, цветные полосы и наборы символов. Если варианты оформления больше не нужны, их можно удалить несколькими щелчками мыши.

Еще статьи об условном форматировании

• Как автоматически создавать стрелки в списках Excel
• Отображение отрицательных значений времени в таблицах Excel
• Цвет отрицательных процентов красный
• Распознавайте отрицательные проценты с первого взгляда
• Цветное форматирование чисел в произвольном формате
• Форматирование ячеек Excel, не имеющих указанного значения
• Автоматически сохранять ячейки с соответствующими денежными форматами
• Пометьте ячейки цветом с помощью функции макроса
• Используйте условное форматирование и выделяйте данные, которые больше или равны третьей по величине ячейке
• Значения обратной истинности