Условное форматирование по условиям в других ячейках (формулами) в excel

При помощи Макрофункции ПОЛУЧИТЬ.ЯЧЕЙКУ()

Сходу предупрежу, что начинающему юзеру EXCEL будет трудно разобраться с сиим и последующим разделом.

Мысль заключается в том, чтоб автоматом вывести в примыкающем столбце числовой код фона ячейки (в MS EXCEL все цвета имеют соответственный числовой код). Для этого нам будет нужно функция, которая может возвратить этот код. Ни одна рядовая функция этого не умеет. Используем макрофункцию ПОЛУЧИТЬ.ЯЧЕЙКУ(), которая возвращает код цвета заливки ячейки (она может много, но нам будет нужно лишь это ее свойство).

Примечание: Макрофункции — это набор функций к EXCEL 4-й версии, которые недозволено впрямую применять на листе EXCEL современных версий, а можно применять лишь в качестве Именованной формулы . Макрофункции — промежный вариант меж обыкновенными функциями и функциями VBA. Для работы с этими функциями требуется сохранить файл в формате с макросами *.xlsm

• Сделайте активной ячейку В7 (это принципиально, т.к. мы будем применять относительную адресацию в формуле)
• В Диспетчере имен введите формулу =ПОЛУЧИТЬ.ЯЧЕЙКУ(63;Макрофункция!A7)
• Назовите ее Цвет

• Закройте Диспетчер имен
• Введите в ячейку В7 формулу =Цвет и скопируйте ее вниз.

Сложение значений скооперировано так же как и в прошлом разделе.

Макрофункция работает кривовато:

• если вы измените цвет ячейки, то макрофункция не обновит значения кода (для этого необходимо снова скопировать формулу из В7 вниз либо выделить ячейку, надавить кнопку F2 и потом ENTER )
• функция возвращает лишь 56 цветов (так именуемая гамма EXCEL), т.е. если цвета близки, к примеру, зеленоватый и светло зеленоватый, то коды этих цветов могут совпасть. Подробнее о этом см. лист файла примера Colors . Как следствие, будут сложены значения из ячеек с различными цветами.

Изменить на другой вариант встроенного отрицательного числа

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

Для тех в США, Excel предоставляет следующие встроенные опции для отображения отрицательных чисел:

• Черным цветом с предшествующим знаком минус
• В красном
• В скобках (вы можете выбрать красный или черный)

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

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

Чтобы изменить другой встроенный формат, щелкните правой кнопкой мыши ячейку (или диапазон выбранных ячеек), а затем щелкните команду «Форматировать ячейки». Вы также можете нажать Ctrl + 1.

В окне «Формат ячеек» перейдите на вкладку «Число». Слева выберите категорию «Номер». Справа выберите вариант из списка «Отрицательные числа» и нажмите «ОК».

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

Здесь мы решили отображать отрицательные значения красным цветом в скобках.

Это отображение гораздо более узнаваемо, чем по умолчанию в Excel.

Подсветка ячеек с формулами.

Рассмотрим еще один подход, который поможет проконтролировать правильность ввода данных. Предположим, у нас есть числовые данные. Необходимо проверить, не получены ли какие-либо из них при помощи формул, поскольку все они должны быть введены вручную.

Проверим наличие формулы в ячейке при помощи функции ЕФОРМУЛА (ISFORMULA).

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

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

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

Используем функцию НЕ (NOT), которая изменит логический результат на противоположный:

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

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

Как выделить отрицательные значения в excel цветом

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

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

В Windows нажмите клавиши CTRL+1. Если вы используете Компьютер Mac, нажмите +1

В поле Категория выберите число или валюту.

В разделе Отрицательные числа выберите вариант для отрицательных чисел.

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

Как задать несколько условий для изменения цвета строки

Если нужно выделить строки одним и тем же цветом при появлении одного из нескольких различных значений, то вместо создания нескольких правил форматирования можно использовать функции И
(AND), ИЛИ
(OR) и объединить таким образом нескольких условий в одном правиле.

Например, мы можем отметить заказы, ожидаемые в течение 1 и 3 дней, розовым цветом, а те, которые будут выполнены в течение 5 и 7 дней, жёлтым цветом. Формулы будут выглядеть так:

ИЛИ($F2=»Due in 1 Days»;$F2=»Due in 3 Days»)
=OR($F2=»Due in 1 Days»,$F2=»Due in 3 Days»)

ИЛИ($F2=»Due in 5 Days»;$F2=»Due in 7 Days»)
=OR($F2=»Due in 5 Days»,$F2=»Due in 7 Days»)

Для того, чтобы выделить заказы с количеством товара не менее 5, но не более 10 (значение в столбце Qty.
), запишем формулу с функцией И
(AND):

И($D2>=5;$D2
=AND($D2>=5,$D2

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

ИЛИ($F2=»Due in 1 Days»;$F2=»Due in 3 Days»;$F2=»Due in 5 Days»)
=OR($F2=»Due in 1 Days»,$F2=»Due in 3 Days»,$F2=»Due in 5 Days»)

Подсказка:
Теперь, когда Вы научились раскрашивать ячейки в разные цвета, в зависимости от содержащихся в них значений, возможно, Вы захотите узнать, сколько ячеек выделено определённым цветом, и посчитать сумму значений в этих ячейках. Хочу порадовать Вас, это действие тоже можно сделать автоматически, и решение этой задачи мы покажем в статье, посвящённой вопросу Как в Excel посчитать количество, сумму и настроить фильтр для ячеек определённого цвета .

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

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

Пусть в диапазоне А6:С16

имеется таблица с перечнем работ, сроками выполнения и статусом их завершения (см. файл примера
).

1) Как использовать условное форматирование?

Очень просто использовать условное форматирование. Он находится на вкладке «Домашняя страница» на вкладке «Стили», как показано на рисунке ниже.

Чтобы запустить пример практики, создайте список строк 15 со значениями из 1 в 10 в excel, как показано в следующем примере:

Первый элемент в меню «Выделить ячейки правила» — это автоматическое форматирование из предварительно заданных условий. Его подпунктами являются:

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

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

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

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

д) Текст, содержащий … — вы определяете фрагмент текста, и вы можете выбрать форматирование ячейки (шрифт, цвет шрифта, границы и цвет фона) для всех вхождений этого фрагмента. Например, выбрав «больше» в списке, ячейки с «большим», «большим», «большим» и т. Д. Будут затронуты выбранным форматированием.

f) Дата, возникающая … — То же, что и элемент «равно …», но с датами.

g) Повторяющиеся значения … — Выбранное форматирование будет зависеть от любых повторяющихся значений.

При выборе списка и нажатии «Условное форматирование»> «Выделить правила ячеек»> «Больше …» вы должны увидеть следующую форму или что-то похожее в зависимости от вашей версии Excel:

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

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

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

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

Чтобы начать, нажмите «Новое правило …»:

Вы должны увидеть рамку, очень похожую на эту картинку

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

Когда мы нажимаем «Новое правило», мы можем увидеть новый фрейм, на этот раз для создания пользовательских правил из описанных вами условий:

На этой диаграмме вы можете исправить следующие факты:

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

— «Форматировать только первое или последнее значение» — аналогично «Первым / последним правилам», которые мы уже видели в этом посте, но с настраиваемыми значениями и цветами, на этот раз.

— «Формат только выше или ниже среднего» — как и предыдущий.

— «Форматировать только уникальные или повторяющиеся значения» — мы также видели это в «Улучшении правил сотовой связи».

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

Где находится условное форматирование

Как в экселе менять цвет ячейки в зависимости от значения – да очень просто и быстро. Для выделения ячеек цветом предусмотрена специальная функция «Условное форматирование», находящаяся на вкладке «Главная»:

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

Правила выделения ячеек

С помощью этого набора инструментов делают следующие выборки:

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

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

Обратите внимание, выбрать можно цвет фона и текста из предложенных в списке. Если хочется применить иные оттенки – сделать это можно перейдя в «Пользовательский формат»

Аналогичным образом реализуются все «Правила выделения ячеек».

Очень творчески реализуются «Другие правила»: в шести вариантах сценария придумывайте те, которые наиболее удобны для работы, например, градиент:

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

Правила отбора первых и последних значений.

Рассмотрим вторую группу функций «Правила отбора первых и последних значений». В ней вы сможете:

• выделить цветом первое или последнее N-ое количество ячеек;
• применить форматирование к заданному проценту ячеек;
• выделить ячейки, содержащие значение выше или ниже среднего в массиве;
• во вкладке «Другие правила» задать необходимый функционал.

Гистограммы

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

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

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

Наборы значков

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

Создание, удаление и управление правилами

Функция «Создать правило» полностью дублирует «Другие правила» из перечисленных выше, создает выборку изначально по требованию пользователя.

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

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

Применить условное форматирование для каждой строки в Excel

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

Метод A Изменение применимости к в диспетчере правил условного форматирования

Например, здесь вы хотите применить цвет фона заливки к ячейкам, если A2> B2, A3> B3,…, An> Bn с условным форматированием.

1. Во-первых, примените условное форматирование к A2: B2. Выберите A2: B2, затем щелкните Главная > Условное форматирование > Новое правило. Если в строке уже есть правила, просто переходите к шагу 4.

2. в Новое правило форматирования диалоговое окно, выберите Используйте формулу, чтобы определить, какие ячейки следует форматировать. от Выберите тип правила раздел, затем введите = $ A2> $ B2 в текстовое поле под Формат значений, где эта формула истинна.

3. Нажмите Формат кнопку, чтобы перейти к Формат ячеек диалоговое окно, а затем вы можете выбрать нужный тип форматирования. Например, заливка цветом фона. Нажмите OK > OK закрыть диалоги.

Теперь к строке A2: B2 применяется условное форматирование.

4. Не снимая выделения с A2: B2, нажмите Главная > Условное форматирование > Управление правилами.

5. в Диспетчер правил условного форматирования найдите правило, которое вы применили к A2: B2, измените диапазон, как вам нужно, в Относится к раздел и нажмите OK.

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

Метод B Перетащите маркер автозаполнения

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

Предположим, в строке A2: B2 применено два условного форматирования, если A2> B2, заливка красным цветом фона, если A2

Теперь вы хотите применить эти два правила к A3: B9.

1. Во-первых, вам нужно применить правила условного форматирования к строке A2: B2. Если в строке есть правила, просто перейдите к шагу 4. Выберите диапазон A2: B2, щелкните Главная > Условное форматирование > Новое правило. Если в строке уже есть правила, просто переходите к шагу 4.

2. в Новое правило форматирования диалоговое окно, выберите Используйте формулу, чтобы определить, какие ячейки нужно отформатировать, из Выберите тип правила раздел, затем введите = $ A2> $ B2 в текстовое поле под Формат значений, где эта формула истинна. Затем нажмите Формат кнопку в Формат ячеек диалога под Заполнять на вкладке выберите красный цвет. Нажмите OK > OK.

3. Не снимая выделения с строки A2: B2, включите Диалоговое окно «Новое правило форматирования» снова выберите Используйте формулу, чтобы определить, какие ячейки следует форматировать. от Выберите тип правила раздел, затем введите = $ A2 <$ B2 в текстовое поле под Формат значений, где эта формула истинна. Затем нажмите Формат кнопку в Формат ячеек диалога под Заполнять на вкладке выберите зеленый цвет. Нажмите OK > OK.

4. Затем перетащите Автозаполнение дескриптор соседних строк, к которым вы хотите применить условное правило, затем выберите Только форматирование заливки из Параметры автозаполнения.

Другие операции (статьи), связанные с форматированием Conditioanl

Подсчет / суммирование ячеек по цветам с условным форматированием в ExcelТеперь это руководство расскажет вам о некоторых удобных и простых методах быстрого подсчета или суммирования ячеек по цвету с условным форматированием в Excel.

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

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

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

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

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

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

На вкладке Главная нажмите кнопку Формат >формат ячеек.

В окне Формат ячеек в списке Категория выберите настраиваемые.

В поле Тип введите следующий формат: 0,00%;-0,00 %.

Отрицательный процент будет выделен красным цветом.

Совет: Пользовательские форматы сохраняются вместе с книгой. В следующий раз, когда потребуется применить его, выполните шаги 2 и 3 выше, и вы найдете пользовательский формат в списке Тип. Чтобы использовать пользовательский формат в других книгах, сохраните книгу в качестве шаблона, а затем на его основе будут базироваться будущие книги.

Как сделать автоматическую заливку в Excel?

Как в Экселе сделать автоматическую заливку ячеек?

Как в Excel динамически изменять цвет ячейки, основываясь на её значении

1. Выделите таблицу или диапазон, в котором Вы хотите изменить цвет заливки ячеек. …
2. Откройте вкладку Home (Главная), в разделе Styles (Стили) нажмите Conditional Formatting (Условное форматирование) > New Rule (Создать правило).

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

1. Чтобы сделать подобное, выделите ячейки, которые должны автоматически менять свой цвет, и выберите в меню Формат — Условное форматирование (Format — Conditional formatting).
2. В открывшемся окне можно задать условия и, нажав затем кнопку Формат (Format), параметры форматирования ячейки, если условие выполняется.

Как изменить цвет выделения ячеек в Excel 2016?

На вкладке Главная нажмите кнопку вызова диалогового окна Формат ячеек или просто нажмите клавиши CTRL+SHIFT+F. На вкладке Заливка выберите в разделе Цвет фона нужный цвет.

Как закрасить ячейку по условию?

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

Как в Экселе настроить изменение цвета ячейки?

Применение узора или способов заливки

1. Выделите ячейку или диапазон ячеек, которые нужно отформатировать.
2. На вкладке Главная нажмите кнопку вызова диалогового окна Формат ячеек или просто нажмите клавиши CTRL+SHIFT+F.
3. На вкладке Заливка выберите в разделе Цвет фона нужный цвет.

Как в Excel сделать строки разного цвета?

Чередуем цвет строк в Excel

1. Выделите диапазон ячеек, в котором нужно настроить чередование цветов строк.
2. На вкладке Вставка (Insert) кликните Таблица (Table) или нажмите Ctrl+T.
3. Готово! Чётные и нечётные строки созданной таблицы раскрашены в разные цвета.

Как изменить цвет текста в Excel формулой?

Изменение цвета текста в ячейке или диапазоне

1. Выделите ячейку или диапазон ячеек с данными, которые вы хотите отформатировать. Вы также можете выбрать часть текста в ячейке.
2. На вкладке Главная щелкните стрелку рядом с кнопкой Цвет шрифта .
3. Выберите цвет в группе Цвета темы или Стандартные цвета.

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

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

Как выделить ячейки в Excel?

Выделение содержимого ячеек в Excel

1. Чтобы выделить ячейку, щелкните ее. …
2. Чтобы выбрать диапазон, выберем ячейку, а затем, нажимая левую кнопку мыши, перетащите указатель мыши над другими ячейками. …
3. Чтобы выделить несмежные ячейки и диапазоны ячеек, выберите их, удерживая нажатой клавишу CTRL.

Как в Excel сделать выделение цветом?

1. Выделите диапазон ячеек B2:L15 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило». …
2. В поле ввода введите формулу:
3. Щелкните на кнопку «Формат» и укажите на вкладке «Заливка» каким цветом будут выделены ячейки актуального месяца.

Что такое табличный процессор Excel его назначение?

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

Как выделить активную ячейку в Excel цветом?

Выделите целую вторую строку таблицы A2:H2 и выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило»-«Использовать формулу для определения форматированных ячеек:». Щелкните по кнопке «Формат» и на вкладке «Заливка» укажите зеленый цвет. На всех окнах жмем ОК. Скопируйте вторую строку таблицы.

Как закрасить строку в Excel по условию?

Как изменить цвет строки на основании числового значения одной из ячеек

1. Первым делом, выделим все ячейки, цвет заливки которых мы хотим изменить.
2. Чтобы создать новое правило форматирования, нажимаем Главная > Условное форматирование > Создать правило (Home > Conditional Formatting > New rule).

Как закрасить ячейку в таблице?

Выделение ячеек цветом (цветная заливка).

1. Выделить ячейки левой кнопкой мыши;
2. Кликнуть по выделению правой кнопкой мыши;
3. В появившемся контекстном меню кликнуть по строчке «Формат ячеек… …
4. В появившемся окне формы настроек следует выбрать вкладку «Заливка»;

Решение

Выбор Типа поиска организуем с помощью Элементов управления формы Группа и Переключатель. Свяжем все Переключатели с ячейкой B9 . Теперь при выборе Типа поиска Точно совпадает в этой ячейке будет содержаться число 1, при выборе Содержит — 2, Начинается — 3, Заканчивается — 4 (см. файл примера Лист 1 критерий (текст) ).

Настроим Условное форматирование для выделения ячеек, удовлетворяющих критерию. Для этого:

• выделите ячейки, в которые введен перечень инструментов ( А9:A17 );
• вызовите инструмент Условное форматирование ( Главная/ Стили/ Условное форматирование/ Создать правило )
• выберите Использовать формулу для определения форматируемых ячеек;
• в поле « Форматировать значения, для которых следующая формула является истинной » введите =ВЫБОР($B$9;$A$6=A9;ПОИСК($A$6;A9);ПОИСК($A$6;A9)=1;$A$6=ПРАВСИМВ(A9;ДЛСТР($A$6)))
• выберите требуемый формат, например, красный цвет фона .

Если в качестве примера Критерия используется текст « Дрель », а Тип поиска выбран Точно совпадает (1), то красным фоном выделится только ячейка A14 , содержащая значение в точности совпадающее с искомым текстом.

Разберем работу формулы из вышеуказанного правила Условного форматирования .

Функция ВЫБОР() позволяет в одной формуле совместить 4 формулы для всех Типов поиска:

• Точно совпадает: $A$6=A9 ;
• Содержит: ПОИСК($A$6;A9) ;
• Начинается: ПОИСК($A$6;A9)=1 ;
• Заканчивается: $A$6=ПРАВСИМВ(A9;ДЛСТР($A$6)) .

В зависимости от значения в ячейке B9 , связанной с Переключателями, функция ВЫБОР() возвращает соответствующую формулу.

Функция ПОИСК() пытается найти первое вхождение Критерия в каждом значении перечня и, в случае успеха, возвращает начальную позицию найденного значения. В противном случае возвращается ошибка #ЗНАЧ!

Формула ПОИСК($A$6;A9) возвращает номер позиции текстовой строки из ячейки А9 , с которой начинается текст, указанный в критерии (если он найден).

Например, для ячейки А14 формула ПОИСК($A$6;A14) вернет 1, а для А15 формула ПОИСК($A$6;A15) вернет 8 и обе ячейки будут выделены. Если в ячейке не содержится искомый текст, то формула возвращает ошибку и ячейка не выделяется.

Примечание : Обратите внимание, что будет выделена ячейка со словом Мотодрель , как ячейка содержащая критерий. Но, если нужно найти ячейки содержащие слово Дрель (т.е

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

Формула $A$6=ПРАВСИМВ(A9;ДЛСТР($A$6)) возвращает правую часть каждого значения перечня длиной равной критерию, т.е. в нашем случае 5 (т.к. в критерии «Дрель» 5 букв), а затем сравнивает ее с критерием. Если оба выражения совпадают, то это означает, что значение перечня заканчивается на текст, указанный в Критерии.

Примечание : Обратите внимание, что будет выделена ячейка со словом Мотодрель , как ячейка заканчивающаяся на критерий. Но, если нужно найти ячейки содержащие слово Дрель (т.е

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

Если критерий содержится в начале текстовой строки, то ПОИСК() вернет 1. Следовательно, формула ПОИСК($A$6;A9)=1 вернет ИСТИНА только для ячеек начинающихся с искомого текста.

Совет Для настройки поиска ячеек, значения которых удовлетворяют критерию с учетом РЕгиСТРА, читайте статью Выделение ячеек c ТЕКСТом с применением Условного форматирования (с учетом РЕгиСТРА) .