Условное форматирование в MS EXCEL

Условное форматирование – один из самых полезных инструментов EXCEL. Умение им пользоваться может сэкономить пользователю много времени и сил.

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

Эти правила используются довольно часто, поэтому в EXCEL 2007 они вынесены в отдельное меню Правила выделения ячеек.

Эти правила также же доступны через меню Главная/ Стили/ Условное форматирование/ Создать правило, Форматировать только ячейки, которые содержат.

Рассмотрим несколько задач:

СРАВНЕНИЕ С ПОСТОЯННЫМ ЗНАЧЕНИЕМ (КОНСТАНТОЙ)

Задача1. Сравним значения из диапазона A1:D1 с числом 4.

  • введем в диапазон A1:D1 значения 1, 3, 5, 7
  • выделим этот диапазон;
  • применим к выделенному диапазону Условное форматирование на значение Меньше (Главная/ Стили/ Условное форматирование/ Правила выделения ячеек/ Меньше);
  • в левом поле появившегося окна введем 4 – сразу же увидим результат применения Условного форматирования.
  • Нажмем ОК.


Результат можно увидеть в файле примера на листе Задача1.

СРАВНЕНИЕ СО ЗНАЧЕНИЕМ В ЯЧЕЙКЕ (АБСОЛЮТНАЯ ССЫЛКА)

Чуть усложним предыдущую задачу: вместо ввода в качестве критерия непосредственно значения (4), введем ссылку на ячейку, в которой содержится значение 4.

Задача2. Сравним значения из диапазона A1:D1 с числом из ячейки А2.

  • введем в ячейку А2 число 4;
  • выделим диапазон A1:D1;
  • применим к выделенному диапазону Условное форматирование на значение Меньше (Главная/ Стили/ Условное форматирование/ Правила выделения ячеек/ Меньше);
  • в левом поле появившегося окна введем ссылку на ячейку A2 нажав на кнопочку, расположенную в правой части окна (EXCEL по умолчанию использует абсолютную ссылку $А$2).

 

Нажмите ОК.

В результате, все значения из выделенного диапазона A1:D1 будут сравниваться с одной ячейкой $А$2. Те значения из A1:D1, которые меньше A2 будут выделены заливкой фона ячейки.

Результат можно увидеть в файле примера на листе Задача2.

Чтобы увидеть как настроено правило форматирования, которое Вы только что создали, нажмите Главная/ Стили/ Условное форматирование/ Управление правилами; затем дважды кликните на правиле или нажмите кнопку Изменить правило. В результате увидите диалоговое окно, показанное ниже.

ПОПАРНОЕ СРАВНЕНИЕ СТРОК/ СТОЛБЦОВ (ОТНОСИТЕЛЬНЫЕ ССЫЛКИ)

Теперь будем производить попарное сравнение значений в строках 1 и 2.

Задача3. Сравнить значения ячеек диапазона A1:D1 со значениями из ячеек диапазона A2:D2. Для этого будем использовать относительную ссылку.

  • введем в ячейки диапазона A2:D2 числовые значения (можно считать их критериями);
  • выделим диапазон A1:D1;
  • применим к выделенному диапазону Условное форматирование на значение Меньше (Главная/ Стили/ Условное форматирование/ Правила выделения ячеек/ Меньше)
  • в левом поле появившегося окна введем относительную ссылку на ячейку A2 (т.е. просто А2 или смешанную ссылку А$2). Убедитесь, что знак $ отсутствует перед названием столбца А.

Теперь каждое значение в строке 1 будет сравниваться с соответствующим ему значением из строки 2 в том же столбце! Выделены будут значения 1 и 5, т.к. они меньше соответственно 2 и 6, расположенных в строке 2.

Результат можно увидеть в файле примера на листе Задача3

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

Примечание-отступление: О важности фиксирования активной ячейки при создании правил Условного форматирования с относительными ссылками

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

СОВЕТ: Чтобы узнать адрес активной ячейки (она всегда одна на листе) можно посмотреть в поле Имя (находится слева от Строки формул). В задаче 3, после выделения диапазона A1:D1 (клавиша мыши должна быть отпущена), в поле Имя, там будет отображен адрес активной ячейки A1 или  D1. Почему возможно 2 вырианта и в чем разница для правил условного форматирования?

Посмотрим внимательно на второй шаг решения предыдущей задачи3 - выделение диапазона A1:D1. Указанный диапазон можно выделить двумя способами: выделить ячейку А1, затем, не отпуская клавиши мыши, выделить весь диапазон, двигаясь вправо к D1; либо, выделить ячейку D1, затем, не отпуская клавиши мыши, выделить весь диапазон, двигаясь влево к А1. Разница между этими двумя способами принципиальная: в первом случае, после завершения выделения диапазона, активной ячейкой будет А1, а во втором D1!

Теперь посмотрим как это влияет на правило условного форматирования с относительной ссылкой.

Если мы выделили диапазон первым способом, то, введя в правило Условного форматирования относительную ссылку на ячейку А2, мы тем самым сказали EXCEL сравнивать значение активной ячейки А1 со значением в А2. Т.к. правило распространяется на диапазон A1:D1, то B1 будет сравниваться с В2 и т.д. Задача будет корректно решена.

Если при создании правила Условного форматирования активной была ячейка D1, то именно ее значение будет сравниваться со значением ячейки А2. А значение из A1 будет теперь сравниваться со значением из ячейки XFB2 (не найдя ячеек левее A2, EXCEL выберет самую последнюю ячейку XFD для С1, затем предпоследнюю для B1 и, наконец XFB2 для А1). Убедиться в этом можно, посмотрев созданное правило:

  • выделите ячейку A1;
  • нажмите Главная/ Стили/ Условное форматирование/ Управление правилами;
  • теперь видно, что применительно к диапазону $A$1:$D$1 применяется правило Значение ячейки <XFB2 (или <XFB$2).

EXCEL отображает правило форматирования (Значение ячейки <XFB2) применительно к активной ячейке, т.е. к A1. Правильно примененное правило, в нашем случае, выглядит так:

ВЫДЕЛЕНИЕ СТРОК

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

ВЫДЕЛЕНИЕ ЯЧЕЕК С ТЕКСТОМ

В разделе Условное Форматирование Текстовых значений приведен ряд специализированных статей о выделении условным форматированием ячеек содержащих текст:

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

Основная статья - Выделение ячеек c ТЕКСТом с применением Условного форматирования в MS EXCEL

ВЫДЕЛЕНИЕ ЯЧЕЕК С ЧИСЛАМИ

В разделе Условное Форматирование Числовых значений приведен ряд специализированных статей о выделении условным форматированием ячеек содержащих числа.

ВЫДЕЛЕНИЕ ЯЧЕЕК С ДАТАМИ

В разделе Условное Форматирование Дат приведен ряд статей о выделении условным форматированием ячеек содержащих даты.

ВЫДЕЛЕНИЕ ЯЧЕЕК С ПОВТОРАМИ

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

ПРИМЕНЕНИЕ НЕСКОЛЬКИХ ПРАВИЛ

Часто требуется выделить значения или даже отдельные строки в зависимости от того диапазона, которому принадлежит значение. Например, если Число меньше 0, то его нужно выделить красным фоном, если больше - то зеленым. О таком примере можно прочитать в статье Выделение Условным форматированием Чисел принадлежащих различным диапазонам.

ПРИОРИТЕТ ПРАВИЛ

Для проверки примененных к диапазону правил используйте Диспетчер правил условного форматирования (Главная/ Стили/ Условное форматирование/ Управление правилами).

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

Например, в ячейке находится число 9 и к ней применено два правила Значение ячейки >6 (задан формат: красный фон) и Значение ячейки >7 (задан формат: зеленый фон), см. рисунок выше. Т.к. правило Значение ячейки >6 (задан формат: красный фон) располагается выше, то оно имеет более высокий приоритет, и поэтому ячейка со значением 9 будет иметь красный фон. На Флажок Остановить, если истина можно не обращать внимание, он устанавливается для обеспечения обратной совместимости с предыдущими версиями EXCEL, не поддерживающими одновременное применение нескольких правил условного форматирования. Хотя его можно использовать для отмены одного или нескольких правил при одновременном использовании нескольких правил, установленных для диапазона (когда между правилами нет конфликта). Подробнее можно ]]>прочитать здесь]]>.

Если к диапазону ячеек применимо правило форматирования, то оно обладает приоритетом над форматированием вручную. Форматирование вручную можно выполнить при помощи команды Формат из группы Ячейки на вкладке Главная. При удалении правила условного форматирования форматирование вручную остается.

УСЛОВНОЕ ФОРМАТИРОВАНИЕ и ФОРМАТ ЯЧЕЕК

Условное форматирование не изменяет примененный к данной ячейке Формат (вкладка Главная группа Шрифт, или нажать CTRL+SHIFT+F). Например, если в Формате ячейки установлена красная заливка ячейки, и сработало правило Условного форматирования, согласно которого заливкая этой ячейки должна быть желтой, то заливка Условного форматирования "победит" - ячейка будет выделены желтым. Хотя заливка Условного форматирования наносится поверх заливки Формата ячейки, она не изменяет (не отменяет ее), а ее просто не видно.

Через Формат ячеек можно задать пользовательский формат ячейки, который достаточно гибок и иногда даже удобнее, чем Условное форматирование. Подробнее см. статью Пользовательский ЧИСЛОвой формат в MS EXCEL (через Формат ячеек).

ОТЛАДКА ПРАВИЛ УСЛОВНОГО ФОРМАТИРОВАНИЯ

Чтобы проверить правильно ли выполняется правила Условного форматирования, скопируйте формулу из правила в любую пустую ячейку (например, в ячейку справа от ячейки с Условным форматированием). Если формула вернет ИСТИНА, то правило сработало, если ЛОЖЬ, то условие не выполнено и форматирование ячейки не должно быть изменено.

Вернемся к задаче 3 (см. выше раздел об относительных ссылках). В строке 4 напишем формулу из правила условного форматирования =A1<A2 и скопируем ее вправо на 4 ячейки.

В тех столбцах, где результат формулы равен ИСТИНА, условное форматирование будет применено, а где ЛОЖЬ - нет.

ИСПОЛЬЗОВАНИЕ В ПРАВИЛАХ ССЫЛОК НА ДРУГИЕ ЛИСТЫ

До MS Excel 2010 для правил Условного форматирования нельзя было напрямую использовать ссылки на другие листы или книги. Обойти это ограничение можно было с помощью использования имен. Если в Условном форматирования нужно сделать, например, ссылку на ячейку А2 другого листа, то нужно сначала определить имя для этой ячейки, а затем сослаться на это имя в правиле Условного форматирования. Как это реализовано См. файл примера на листе Ссылка с другого листа.

ПОИСК ЯЧЕЕК С УСЛОВНЫМ ФОРМАТИРОВАНИЕМ

  • на вкладке Главная в группе Редактирование щелкните стрелку рядом с командой Найти и выделить,
  • выберите в списке пункт Условное форматирование.

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

ДРУГИЕ ПРЕДОПРЕДЕЛЕННЫЕ ПРАВИЛА

В меню Главная/ Стили/ Условное форматирование/ Правила выделения ячеек разработчиками EXCEL созданы разнообразные правила форматирования.

Чтобы заново не изобретать велосипед, посмотрим на некоторые их них внимательнее.

  • Текст содержит… Приведем пример. Пусть в ячейке имеется слово Дрель. Выделим ячейку и применим правило Текст содержит…Если в качестве критерия запишем ре (выделить слова, в которых содержится слог ре), то слово Дрель будет выделено.

Теперь посмотрим на только что созданное правило через меню Главная/ Стили/ Условное форматирование/ Управление правилами...

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

Пусть снова в ячейке имеется слово Дрель. Выделим ячейку и применим правило Текст содержит… Если в качестве критерия запишем р?, то слово Дрель будет выделено. Критерий означает: выделить слова, в которых содержатся слога ре, ра, ре и т.д. Надо понимать, что также будут выделены слова с фразами р2, рм, рQ, т.к. знак ? означает любой символ. Если в качестве критерия запишем ?????? (выделить слова, в которых не менее 6 букв), то, соответственно, слово Дрель не будет выделено. Можно, конечно подобного результата добиться с помощью формул с функциями ПСТР(), ЛЕВСИМВ(), ДЛСТР(), но этот подход, согласитесь, быстрее.

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

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

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

  • Последние 10 элементов.

Задача4. Пусть имеется 21 значение, для удобства отсортированных по возрастанию. Применим правило Последние 10 элементов и установим, чтобы было выделено 3 значения (элемента). См. файл примера, лист Задача4.

Слова "Последние 3 значения" означают 3 наименьших значения. Если в списке есть повторы, то будут выделены все соответствующие повторы. Например, в нашем случае 3-м наименьшим является третье сверху значение 10. Т.к. в списке есть еще повторы 10 (их всего 6), то будут выделены и они.

Соответственно, правила, примененные к нашему списку: "Последнее 1 значение", "Последние 2 значения", ... "Последние 6 значений" будут приводить к одинаковому результату - выделению 6 значений равных 10.

К сожалению, в правило нельзя ввести ссылку на ячейку, содержащую количество значений, можно ввести только значение от 1 до 1000.

Применение правила "Последние 7 значений" приведет к выделению дополнительно всех значений равных 11, .т.к. 7-м минимальным значением является первое сверху значение 11.

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

  • Последние 10%

Рассмотрим другое родственное правило Последние 10%.

Обратите внимание, что на картинке выше не установлена галочка "% от выделенного диапазона". Эта галочка устанавливается либо в ручную или при применении правила Последние 10%.

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

Попробуем задать 20% последних в нашем списке из 21 значения: будет выделено шесть значений 10 (См. файл примера, лист Задача4). 10 - минимальное значение в списке, поэтому в любом случае будут выделены все его повторы. 

Задавая проценты от 1 до 33% получим, что выделение не изменится. Почему? Задав, например, 33%, получим, что необходимо выделить 6,93 значения. Т.к. можно выделить только целое количество значений, Условное форматирование округляет до целого, отбрасывая дробную часть. А вот при 34% уже нужно выделить 7,14 значений, т.е. 7, а с учетом повторов следующего за 10-ю значения 11, будет выделено 6+3=9 значений.

ПРАВИЛА С ИСПОЛЬЗОВАНИЕМ ФОРМУЛ

Создание правил форматирования на основе формул ограничено только фантазией пользователя. Здесь рассмотрим только один пример, остальные примеры использования Условного форматирования можно найти в этих статьях: Условное форматирование Дат; Условное форматирование Чисел; Условное форматирование Текстовых значений; другие задачи.

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

  • Выделите ячейки, к которым нужно применить Условное форматирование (пусть это ячейка А1).
  • Вызовите инструмент Условное форматирование (Главная/ Стили/ Условное форматирование/ Создать правило)
  • Выберите Использовать формулу для определения форматируемых ячеек

  • В поле «Форматировать значения, для которых следующая формула является истинной» введите =ЕОШ(A1) – если хотим, чтобы выделялись ячейки, содержащие ошибочные значения, т.е. будут выделены #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ? или #ПУСТО! (кроме #Н/Д)
  • Выберите требуемый формат, например, красный цвет заливки.

Того же результата можно добиться по другому:

  • Вызовите инструмент Условное форматирование (Главная/ Стили/ Условное форматирование/ Создать правило)
  • Выделите пункт Форматировать только ячейки, которые содержат;
  • В разделе Форматировать только ячейки, для которых выполняется следующее условие: в самом левом выпадающем списке выбрать Ошибки.

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

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 4.8 (12 оценок)

Комментарии

Татьяна (не проверено)

Добрый день,
подскажите пожалуйста, имеется таблица с названиями органицаций,их адресоми, видами работ.Часть организаций стадия - ПРОЕКТ, часть организаций стадия - РАБОТА. Я добавила столбец А2:А50 в таблицу , где отмечаю соответсвенно стадии Р или П.при ипомощи условного форматирования все П в столбце $A$2 закрашиваются синим.Но для наглядности мне нужно чтобы закрашивалась вся строка,как это сделать?

Татьяна (не проверено)

что то нет ответа - возможно вопрос сформулирован неккоректно.В общем нужно при форматировании одной ячейки в столбце А, автоматически форматироваласть вся строка таблицы., например: Стадия П ячейка А5 при вводе буквы П окрашивается синим, нужно чтобы автоматически закрашивались:В5, С5, Д5....т.е. вся строка с информацией о данном предприятии - название, адрес и т.д. Это для наглядности таблицы.Спасибо.

Creator

Попробуйте воспользоваться файлом примера к статье http://excel2.ru/articles/adres-yacheyki-soderzhashchey-maksimalnoe-znachenie Там закрашивается вся строка, если значение = максимальному. В Вашем случае в качестве условия введите =$A5="П", выделив перед вызовом условного форматирования всю таблицу.

Опять я. (не проверено)

Возможно ли сделать так, чтобы в таблице при вводе чисел в столбец А закрашивались ячейки, количественно соотвествующие этому числу? Например: А3 значение "2", тогда В3 и С3 должны залиться определенным цветом?Это я делаю график производства работ и соответсвенно "2" - это часы для выполнения определенного вида работ.Спасибо.

Creator

Татьяна, чтобы ячейки B3 и С3 в Вашем примере закрашивались в зависимости от значения ячейки А3 нужно сделать следующее: выделите B3 и С3, вызовите Условное Форматирование/Правило выделения ячеек/Равно... в левое поле введите ссылку на ячейку А3, т.е. =$A$3


Если нужно распространить Условное форматирование на ячейки ниже, например на диапазон А3:С10, то сделайте тоже самое, только ссылку на ячейку А3 укажите как смешанную т.е. =$A3

Татьяна (не проверено)

Спасибо за ответ. Попробовала сделать как вы написали - у меня получается что форматируются ячейки,если в них стоит буква П - (проект).Нашла другой вариант - который не совсем то, что хотелось бы, но все же: 1-Выделяю нужные ячейки в строке.2-форматирование.3-новые правила.4-применить только к ячейкам которые содержат.5- текст. 6-начинается с...7-вставляю смешанную формулу на ячейку. Минусы: 1.для каждой строки это нужно делать отдельно. 2.Не то что хотелось.Вышеописанным мной способом у меня отформатирована вся таблица.если в столбце А ставлю букву П - (проект), форматирование всей строчки В:I исчезает.хотелось бы наоборот: Таблица иммеет нормальный вид - при вставке буквы П в столбец А вся строчка А:I окрашивается. Вам спасибо за совет.

Creator

Татьяна, посмотрите о выделении строк Условным форматированием вот эту статью http://excel2.ru/articles/vydelenie-strok-tablicy-v-zavisimosti-ot-uslov...

Александр 555 (не проверено)

Добрый день! Подскажите, можно ли подобным образом форматировать столбцы, в зависимости от содержимого в определенной ячейке?

Creator

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

Если под "форматировать столбцы" подразумевается групповое форматирование границ, заливки, шрифта ячеек, которые входят в определенный столбец таблицы, то да. О решении аналогичной задачи Форматирование строки таблицы в зависимости от содержимого определенной ячейки в строке читайте здесь: http://excel2.ru/articles/vydelenie-strok-tablicy-v-zavisimosti-ot-uslov...

Константин (не проверено)

Доброго времени суток, как известно в новых бух балансах минусы переделали в скобки, дак вот собственно суть вопроса:
как отрицательное число заменить на скобки, например есть формула а1-б1= -27,11 , как мне эти -27,11 поменять визуально на (27,11) но что б формула сохранилась и я дальше мог эту ячейку как число использовать? спасибо.

Creator

Решение Вашей задачи можно посмотреть здесь http://excel2.ru/articles/polzovatelskiy-chislovoy-format-cherez-format-yacheek , т.е. нужно настроить пользовательский формат для ячейки как  # ##0,00;(# ##0,00);# ##0,00


Через Условное форматирование это сделать не получится.

Константин (не проверено)

Спасибо огромное!)

Антон (не проверено)

Добрый день! В таблице с процентами хочется выделить 3х цветным форматированием ниже 0% красным, 0%-50% желтым и 50% и выше зеленым. Значения от -3000% до 3000%. Подскажите, пожалуйста.

Creator

В статье http://excel2.ru/articles/informirovanie-polzovatelya-o-prinadlezhnosti-... показано как это сделать для дат.

Александра (не проверено)

Помогите мне, пожалуйста, тоже. Задача такая: 2 столбца, в одном текст, в другом числовые значения. Надо чтобы обе ячейки строки 1 (А1 с текстом и В1 с числом) выделялись серым, если В1=0, и, скажем, желтым, если В1>0.
У меня ексель 2011 для Мас. Выделяю диапазон нужный, Ввожу в поле для условного форматирования формулу: $B1=0, выбираю цвет заливки, нажимаю Ок
Ничего не меняется, ячейки не окрашиваются (ячейки в столбце В имеют разное значение от 0 до 1000 примерно. Ввожу второе условие: $В1>0, опять ничего. Что я не так делаю?

Creator

Вы делаете все правильно. Единственное, Вы не указали какая ячейка является активной при выделении диапазона. Это важно, т.к. Вы используете относительную ссылку. К статье прицепил файл примера, там решена Ваша задача. Посмотрите еще статью http://excel2.ru/articles/vydelenie-strok-tablicy-v-zavisimosti-ot-uslov...

Мила (не проверено)

Есть файл Excel. В нём находится 18 листов с таблицами вероятностей. 36 на 36 ячеек. Каждая последующая таблица на каждом листе имеет те же значения по строкам, но с небольшими сдвижками. Необходимо решить такую задачу: при изменении цвета ячейки в таблице на 1 листе (каждый день на одной из строк 5 ячеек выделяются красным цветом), необходимо, чтобы ячейки с этими же значениями (которые при сдвижке занимают уже другие позиции) на последующих листах также автоматически или путём несложных действий окрашивались красным цветом, как на листе 1, чтобы не делать этого вручную (18 листов!). Возможно ли это и как этого добиться? Заранее благодарю за подсказки!!!

Creator

Если Вы выделяете ячейки цветом, то Условное форматирование (как и любое другое стандартное средство EXCEL) не "почувствует" это. Поэтому Вам нужно использовать VBA. Хотя наш сайт посвящен EXCEL, но пока мы не даем консультации и не размещаем статьи про VBA (ресурсов маловато). Но, Вы можете не выделять ячейки на листе1 цветом, а за место этого в соседних столбцах справа вводить нужные значения из строки (5 шт на строку). В этом случае, на остальных 17 листах совпадающие значения в соответствующих строках могут быть выделены красным. Для этого нужно создать имя (например Массив) для массивов значений из каждой строки Листа1 с использованием относительной ссылки (=Лист1!$A1:$AJ1, если Ваш блок значений 36*36 начинается с ячейки А1). Затем выделите блок 36*36 на листе2 (блок должен начинаться также с первой строки листа) и создайте правило Условного форматирования =МАКС(--(A1=Массив)) То же нужно проделать для остальных листов. Теперь, например, каждое значение на строке 1 листа2 будет сравниваться со всеми значениями на строке 1 листа1,  каждое значение на строке 2 листа2 будет сравниваться со всеми значениями на строке 2 листа1, каждое значение на строке 12 листа5 будет сравниваться со всеми значениями на строке 12 листа1 и т.д. При совпадении значений они будут выделяться на листах 2-18 в соответствующих строках условным форматированием. Если нужен файл - пишите на creator@excel2.ru

Руслан (не проверено)

Подскажите, пожалуйста! Можно ли с помощью условного форматирования решить проблему: есть ячейка А с датой, есть ячейка В с датой, есть ячейка С с числом, вопрос - можно условным форматированием вывести в ячейку D число из ячейки С при условии, что даты в ячейках А и В совпадают? Почему через условное форматирование - потому, что в ячейке D может быть внесена информация текстовая вручную.

Creator

Нет, нельзя.

egor

Подскажите, как с используя какую-нить формулу (возвращающую конкретное значение) определить применялось ли к ячейке условное форатирование. Т.е. если к ячейкам А1:A5 назначено условное форматирование в зависимости от значения ячейки А1 (к примеру, =$A$1=1) и оно не "сработало" (т.е. А1=0), то неизвестная мне функция возвращает одно значение, если А1=1, то "срабатывает" условное форматирование и ячейки А1:A5 окрашиваются в красный цвет, то неизвестная мне функция возвращает иное значение.
Дело в том, что при "сработке" условного форматирование (при использовании в качестве действия изменения заливки) заливки не происходит (имеется ввиду Главное\Шрифт\Цвет заливки\Нет заливки), хотя визуально ячейки раскрашиваются.

Creator
Условное форматирование не изменяет цвет заливки ячейки (имеется ввиду Главное\Шрифт\Цвет заливки), это форматирование наносится поверх заливки, но не изменяет/ не отменяет ее (ее просто не видно).
Чтобы проверить выполняется ли правило УФ, введите формулу из правила в любую пустую ячейку. Если формула вернет ИСТИНА, то правило сработало, если ЛОЖЬ, то условие не выполнено.
МариЖЁ

Здравствуйте! У меня все значения таблицы - это формулы. Можно ли сделать так, чтобы значения, которые находятся "между" конкретных значений выделялись цветом? Или для формул это не возможно?

Creator

Условное форматирование работает для всех значений в ячейке: не важно как получено это значение - формулой или введено вручную.

Игорь

дравствуйте! Спасибо за Ваши труды, за этот крайне полезный ресурс! Прочитал пару статей, но прямой ответ на свой вопрос не нашел, хотя понимаю, что решение есть довольно простое. Помогите пожалуйста решить задачу.
В столбце А есть несколько ячеек, в каждой из которых по одному слову. В столбце B есть много ячеек с фразами. Нужно сделать так, что бы Эксель искал каждое слово из столбца А в столбце В (не полное соответствие, а именно содержание слов из А во фразах В). Я могу это делать вручную, условным форматированием, но приходится каждое слово из столбца А перебирать по порядку, это очень долго. Автоматизация нужна. Картинка с результатом ручного поиска http://clip2net.com/s/3uxqlCF
Большое спасибо заранее за ответ!

Creator
Яндекс.Метрика