Условное форматирование в excel 2019: Применение условного форматирования с помощью формулы в Excel для Mac

Применение условного форматирования с помощью формулы в Excel для Mac

Excel для Microsoft 365 для Mac Excel 2021 for Mac Excel 2019 для Mac Excel 2016 для Mac Еще…Меньше

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

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

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

Как создать первое правило

org/ItemList»>

1. Выделите ячейки от A2 до A7 (для этого щелкните и перетащите указатель мыши с ячейки A2 в ячейку A7).

2. На вкладке Главная выберите Условное форматирование > Создать правило.

3. В поле Стиль выберите Классический.

4. Под полем Классический выберите элемент Форматировать только первые или последние значения

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

5. org/ListItem»>

   В следующем поле введите формулу: =A2>СЕГОДНЯ()

   Функция СЕГОДНЯ используется в формуле для определения значений дат в столбце A, превышающих значение сегодняшней даты (будущих дат). Ячейки, удовлетворяющие этому условию, форматируются.

6. В поле Форматировать с помощью выберите пользовательский формат.

7. В диалоговом окне

   Формат ячеек откройте вкладку Шрифт.

8. В поле Цвет выберите значение Красный. В поле Начертание шрифта выберите Полужирный.

9. org/ListItem»>

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

Теперь форматирование применено к столбцу A.

Как создать второе правило

1. Выделите ячейки от C2 до C7.

2. На вкладке Главная выберите Условное форматирование > Создать правило.

3. В поле Стиль выберите Классический.

4. org/ListItem»>

   Под полем Классический выберите элемент Форматировать только первые или последние значения и измените его на Использовать формулу для определения форматируемых ячеек.

5. В следующем поле введите формулу: =C2=»Y»

   Формула определяет ячейки в столбце C, содержащие символ «Y» (прямые кавычки вокруг символа «Y» указывают Excel, что это текст). Ячейки, удовлетворяющие этому условию, форматируются.

6. В поле Форматировать с помощью выберите пользовательский формат.

7. В верхней части окна откройте вкладку Шрифт.

8. В поле Цвет выберите Белый. В поле Начертание шрифта

   выберите Полужирный.

9. В верхней части окна откройте вкладку Заливка и для параметра Цвет фона выберите значение Зеленый.

10. Нажмите кнопку ОК несколько раз, чтобы закрыть все диалоговые окна.

Теперь форматирование применено к столбцу C.

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

Вот еще один пример для тех, кто хочет узнать больше. В книге создайте таблицу данных со значениями, приведенными ниже. Начните с ячейки A1. Затем выделите ячейки D2:D11 и задайте новое правило условного форматирования с помощью следующей формулы:

=СЧЁТЕСЛИ($D$2:$D$11;D2)>1

При создании правила убедитесь в том, что оно применяется к ячейкам D2:D11. Задайте цветовое форматирование, которое должно применяться к ячейкам, удовлетворяющим условию (т. е. если название города встречается в столбце D более одного раза, а это — Москва и Мурманск).

Имя	Фамилия	Телефон	Город
Юлия	Ильина	555-1213	Москва
Сергей	Климов	555-1214	Электросталь
Вадим	Корепин	555-1215	Мурманск
Андрей	Гладких	555-1216	Дубна
Станислав	Песоцкий	555-1217	Коломна
Ольга	Костерина	555-1218	Мурманск
Евгений	Куликов	555-1219	Верея
Николай	Новиков	555-1220	Домодедово
Светлана	Омельченко	555-1221	Москва
Инна	Ожогина	555-1222	Электрогорск

Условное форматирование в Excel: ничего сложного

Наверх

08. 05.2018

Автор: CHIP

1 звезда2 звезды3 звезды4 звезды5 звезд

Условное форматирование в Excel: ничего сложного

3

5

1

10

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

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

Отформатируйте все ячейки на основе их значений

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

• Выберите область в электронной таблице Excel и нажмите вкладку «Главная».
• Теперь кликните «Условное форматирование», а затем нажмите «Создать правило …».
• В новом окне выберите тип «Форматировать все ячейки на основании их значений».
• Выделите цветом те ячейки, значения которых на единицу больше или меньше среднего значения
• Значения, которые не входят в определенную область, могут быть легко найдены с помощью Excel. Эта функция отмечает цветом ячейки, которые не соответствуют значению.
• Выберите область в электронной таблице Excel и нажмите вкладку «Главная».
  Снова перейдите в «Условное форматирование», а затем в «Создать правило …».
• Если вы теперь выберете «Стандарт», то зададите настройки, по которым ячейки со значениями выше или ниже среднего будут выделены цветом.

Двух- и трехцветная шкала в Excel

Значения с цветовой шкалой

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

• В нижней части окна теперь выберите стиль формата «двухцветная шкала».
• В качестве типа выберите «минимальное значение» для минимального и «максимальное значение» для максимального.
• При необходимости измените цвета и закройте окно нажатием на «ОК». Для трехцветной шкалы можно просто установить форматирование для среднего значения.

Гистограммы

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

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

• Просто выберите «Гистограмма» в качестве стиля.
• В качестве типа выберите «Минимум» и «Автоматический».
• Оформите нижеуказанные столбцы и закройте, нажав на «ОК».

Символы

Таблица символов в Excel

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

• В нижней части окна выберите стиль.
• В таблице выберите подходящий символ.
• Для первого параметра «ЕСЛИ ЗНАЧЕНИЕ:» установите «>». Остальное можно оставить без изменений. Нажатие на «ОК» сохраняет правило Excel.

«Форматировать только ячейки, которые содержат…»

Форматировать ячейки на основе содержимого

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

• Выберите область в таблице, для которой вы хотите применить форматирование, и создайте новое правило с типом «Форматировать только ячейки, содержащие».
• Установите следующий параметр: «Значение ячейки меньше 0»
• Нажмите кнопку «Формат …» и выберите красный цвет в новом окне как цвет выделения.
• Дважды нажмите «ОК», чтобы создать правило.

Представить числовые данные в виде текста

Если у вас есть предопределенные повторяющиеся текстовые данные, этот тип форматирования идеален для них. Он не только экономит ваше время, но и предотвращает опечатки.

• Выберите область в электронной таблице Excel, к которой вы хотите применить форматирование, и создайте новое правило с помощью «Форматировать только ячейки, содержащие».
• Установите следующий параметр: «Значение ячейки, равное 1»,
• Нажмите кнопку «Формат …» и выберите вкладку «Числа» в новом окне.
• В левой части боковой панели нажмите «Пользовательский».
• В текстовом поле под меткой «Тип:» введите следующее: «Hello World!» — кавычки при вводе обязательны.
• Дважды нажмите «ОК», чтобы завершить действия.
• Если вы вводите единицу в ячейку, находящуюся в определенной вами области, вместо этого появится текст «Hello World!». Таким же образом вы можете указать дополнительные тексты для других чисел.

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

• Как следует из названия, данный тип отформатирует любое значение, к которому применяется конкретная формула. В этом примере все данные Excel, которые касаются будущих дат, будут выделены красным цветом.
• Выберите область в таблице, для которой вы хотите применить форматирование, и создайте новое правило с типом «Использовать формулу для определения форматируемых ячеек».
• Введите формулу = B2> СЕГОДНЯ ().
• Нажмите кнопку «Формат …» и выберите красный цвет в новом окне.
• Подтвердите выбор дважды нажав «ОК».

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

Фото: компании-производители, pixabay.com

Читайте также: 

• Как добавить комментарии в формулы Excel
• Как в Excel вставить кнопку для запуска макроса

Автор

Полина Осокина

Была ли статья интересна?

Поделиться ссылкой

Нажимая на кнопку «Подписаться»,
Вы даете согласие на обработку персональных данных

Рекомендуем

Реклама на CHIP Контакты

Как использовать условное форматирование в Excel 2019

         Повторяющиеся значения

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

Больше, чем форматирование

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

(Конфигурация форматирования больше, чем)

Когда вы работаете с функциями условного форматирования, вы можете видеть, какие ячейки изменятся при работе с конфигурациями. С предварительным просмотром вы можете увидеть, какие ячейки будут выделены, прежде чем сохранять конфигурации. Число, отображаемое в текстовом поле значения в окне конфигурации, является средним значением выделенных строк. Это позволяет быстро установить условное форматирование на основе чисел, превышающих среднее значение. Например, 87,50 долларов — это средняя плата за коммунальные услуги, поэтому Excel устанавливает это значение по умолчанию и выделяет красным цветом все значения, превышающие среднее значение.

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

После завершения настройки нажмите «ОК», и изменения вступят в силу. Так работает большинство правил условного форматирования.

Хотите узнать больше? Почему бы не пройти онлайн-курс Advanced Excel 2019?

Меньше, чем форматирование

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

(Конфигурация форматирования меньше чем)

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

Между форматированием

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

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

(между конфигурациями форматирования)

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

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

Вы можете увидеть изменения в предварительном просмотре своей электронной таблицы, но нажмите «ОК», чтобы завершить их и применить изменения к выделенным ячейкам.

Равно форматированию

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

(равно форматированию конфигураций)

Excel 2019 снова использует среднее значение, чтобы заполнить текстовое поле значением по умолчанию, но вы можете ввести любое значение в это текстовое поле. Используйте раскрывающийся список, чтобы выбрать цвет шрифта или фона. Когда вы закончите настройку стилей, нажмите кнопку «ОК», чтобы завершить их и применить изменения к электронной таблице.

Текст, содержащий форматирование

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

(Текст, содержащий конфигурацию форматирования)

Помните, что строковые значения чувствительны к регистру. Это означает, что термины «Платеж», «ПЛАТЕЖ» и «платеж» — это разные значения для Excel 2019. Вы можете использовать такие функции, как ПРОПИСНЫЕ и НИЖНИЕ, для оценки текста, который сначала преобразуется в верхний или нижний регистр, а затем оценивается. Это гарантирует, что ваше условие будет сначала сравниваться с нормализованным текстом. Если вы знаете формат текста, убедитесь, что вы вводите правильную строку с правильными заглавными и строчными буквами, включенными в значение.

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

Дата возникновения Форматирование

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

(Конфигурация форматирования даты возникновения)

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

Повторяющееся форматирование

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

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

(Конфигурация форматирования повторяющихся значений)

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

Дополнительное форматирование

Доступны не только эти основные функции условного форматирования. Excel предлагает некоторые другие менее распространенные функции для условного форматирования данных.

(раскрывающиеся параметры условного форматирования)

Эти дополнительные условные элементы стилизуют ячейки на основе условий, отсутствующих в основных правилах. Вы можете форматировать ячейки на основе порядка сортировки и создавать правила с гистограммами и цветовыми шкалами, уникальными для основных сплошных цветов. Если вы не можете найти правило, которое вас устраивает, вы также можете установить новое правило. Новое правило позволит вам условно форматировать на основе вашего собственного логического ввода и вывода. Параметр «Новое правило» открывает окно конфигурации, в котором вы можете создать пользовательское правило без каких-либо Excel 2019пресеты. Используйте этот параметр, если вы не можете найти правило, соответствующее вашим требованиям, в предустановленных параметрах Excel.

Выделение шаблонов и тенденций с условным форматированием в Excel для Mac

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

В следующем примере показана информация о температуре с условным форматированием, примененным к верхним 20% и нижним 20% значениям:

А вот пример с примененным условным форматированием по 3-цветной шкале:

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

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

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

3. Выполните одно из следующих действий:

Чтобы выделить	Сделай это
Значения в определенных ячейках. Примерами могут служить даты после этой недели, числа от 50 до 100 или последние 10 % результатов.	Укажите на Выделите правила ячеек или Верхние/нижние правила , а затем щелкните соответствующий параметр.
Связь значений в диапазоне ячеек. Расширяет полосу цвета по всей ячейке. Примерами являются сравнения цен или населения в крупнейших городах.	Точка на Панели данных , а затем щелкните нужную заливку.
Связь значений в диапазоне ячеек. Применяет цветовую шкалу, в которой интенсивность цвета ячейки отражает размещение значения в верхней или нижней части диапазона. Например, распределение продаж по регионам.	От до цветовых шкал , а затем выберите нужный масштаб.
Диапазон ячеек, содержащий от трех до пяти групп значений, где каждая группа имеет собственное пороговое значение. Например, вы можете назначить набор из трех значков для выделения ячеек, отражающих продажи ниже 80 000, ниже 60 000 и ниже 40 000 долларов. Или вы можете назначить 5-балльную рейтинговую систему для автомобилей и применить набор из пяти значков.	Укажите на Наборы значков , а затем щелкните набор.

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

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

2. На вкладке Главная щелкните Условное форматирование , выберите Правила выделения ячеек , а затем щелкните Текст, содержащий .

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

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

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

2. org/ListItem»>

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

3. Выберите стиль, например, 3-цветная шкала, выберите нужные условия и нажмите ОК .

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

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

2. На вкладке Главная щелкните Условное форматирование , выберите Правила выделения ячеек , а затем щелкните Повторяющиеся значения .

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

Копировать условное форматирование в дополнительные ячейки

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

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

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

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

1. Щелкните любую ячейку.

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

2. В меню Редактировать щелкните Найти , а затем щелкните Перейти к .

3. Нажмите Special , а затем нажмите Условные форматы .

4. Если вы хотите найти только ячейки с определенным условным форматом ячейки, выбранным на шаге 1, в разделе Параметры щелкните То же .

Удалить условное форматирование из выделения

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

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

   Совет:  Чтобы удалить все условные форматы и все другие форматы ячеек для выбранных ячеек, в меню Изменить выберите Очистить , а затем нажмите Форматы .

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

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

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

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

3. Выберите правило и нажмите Изменить правило .

4. Внесите необходимые изменения, нажмите OK , а затем еще раз нажмите OK .