Остановить, если true в условном форматировании Excel

Обновлено: 21.11.2024

Условное форматирование не будет продвигаться вперед по правилам, созданным с параметром «Использовать формулу для определения форматируемых ячеек». Он ведет себя так, как будто блоки «Остановить, если истина» отмечены, хотя это не так. Приведенные ниже примеры созданы в Excel 2016 (Win 10). Идентичные результаты получены в Excel 2013 (Win 10) и Excel 2007 (Win 8.1) на одном и том же примере. Для параметра «Параметры Excel» > «Формулы» > «Параметры расчета» > «Расчет рабочей книги» установлено значение «Автоматически». Те же результаты получаются, если для формата ячейки установлено значение «Общий» или «Число». Нажатие F9 ("Рассчитать все листы во всех открытых книгах") не помогает.

Ячейка A1 имеет формат "Общий". Выполнение останавливается на правиле 1 (ИСТИНА) и не переходит к правилам 2 и 3 (также ИСТИНА).

Ячейка A1 отформатирована как число. Выполнение снова останавливается на правиле 1 (ИСТИНА) и не переходит к правилам 2 и 3 (также ИСТИНА).

Порядок правил изменен на обратный, и ячейка A1 изменена на 15. Теперь правило 1 (ЛОЖЬ) пропускается, затем выполнение останавливается после выполнения правила 2 (ИСТИНА), даже если правило 3 имеет значение ИСТИНА.

Я вижу, что люди годами жаловались на условное форматирование. Microsoft, вы серьезно еще не исправили это?

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

Сообщить о нарушении

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

Ответы (6) 

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

Сообщить о нарушении

Был ли этот ответ полезен?

Извините, это не помогло.

Отлично! Благодарим за отзыв.

Насколько вы удовлетворены этим ответом?

Спасибо за ваш отзыв, он помогает нам улучшить сайт.

Насколько вы удовлетворены этим ответом?

Спасибо за отзыв.

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

Сообщить о нарушении

Был ли этот ответ полезен?

Извините, это не помогло.

Отлично! Благодарим за отзыв.

Насколько вы удовлетворены этим ответом?

Спасибо за ваш отзыв, он помогает нам улучшить сайт.

Насколько вы удовлетворены этим ответом?

Спасибо за отзыв.

Сообщить о нарушении

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

Был ли этот ответ полезен?

Извините, это не помогло.

Отлично! Благодарим за отзыв.

Насколько вы удовлетворены этим ответом?

Спасибо за ваш отзыв, он помогает нам улучшить сайт.

Насколько вы удовлетворены этим ответом?

Спасибо за отзыв.

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

Примечание. Это веб-сайт, не принадлежащий Microsoft. Страница предоставляет точную и безопасную информацию. Следите за объявлениями на сайте, которые могут рекламировать продукты, часто классифицируемые как ПНП (потенциально нежелательные продукты). Тщательно изучите любой продукт, рекламируемый на сайте, прежде чем принять решение о его загрузке и установке.

Сообщить о нарушении

Был ли этот ответ полезен?

Извините, это не помогло.

Отлично! Благодарим за отзыв.

Насколько вы удовлетворены этим ответом?

Спасибо за ваш отзыв, он помогает нам улучшить сайт.

Насколько вы удовлетворены этим ответом?

Спасибо за отзыв.

Я ценю ваш ответ и понимаю, что вы являетесь посланником. Как в примере с TechRepublic, так и в моем случае несколько правил оцениваются как ИСТИНА. В статье говорится, что Excel «возвращает неожиданные результаты» из-за «двусмысленности», которую это вызывает. Только нет двусмысленности. В их примере и в моем каждое правило однозначно оценивается как ИСТИНА или ЛОЖЬ. Пользователи ожидают, что условное форматирование будет выполнять правила последовательно вплоть до последнего правила в списке включительно. У нас есть высокая уверенность в том, что это так и будет, потому что наличие неотмеченных флажков рядом с каждым правилом с пометкой «Остановить, если верно» означает, что Excel не остановится. Но это так. Microsoft в любом случае взяла на себя обязательство остановить выполнение при возникновении условия TRUE. Эта логика необъяснима.

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

Сообщить о нарушении

Функция Stop if True может остановить обработку текущего правила при выполнении условия и проигнорировать другие правила. Давайте посмотрим, как это работает!

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

Использовать Stop If True при условном форматировании в Excel

Давайте рассмотрим простой пример: в заданном диапазоне, содержащем цены, мы хотели бы применить два правила одновременно.

Правило 1. Набор значков светофора (красно-желто-зеленый) указывает значение в данной ячейке по сравнению со значениями в других ячейках. Красный – верхняя треть, желтый – средняя треть, зеленый – нижняя треть.

Правило 2: Подчеркните цены, которые относятся к верхней 1/3, но здесь используется другой метод форматирования! Это неплохая идея сама по себе!

Будет ли дублирование правил?

Да, будет!

Поскольку на основе примененного правила набора значков оно заполнит все ячейки. И это будет содержать значения, которые принадлежат верхней 1/3. Итак, как мы можем справиться с проблемой?

Условная функция Excel предлагает элегантное решение. Давайте посмотрим, что происходит, когда мы используем инструмент "Остановить, если это правда".
Мы выбрали диапазон J2:J24 и использовали фигуру светофора, установленную для столбца J, чтобы выделить товары с низкой, средней и высокой ценой.

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

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

Установите флажок Stop if true справа от нового правила, которое вы только что создали.

Выглядит отлично! В указанном диапазоне будут отображаться только наборы значков для элементов, а НЕ первые 33%.

Иштван является соучредителем Excelkid. Он пишет сообщения в блогах и помогает людям достичь вершин в Excel.

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

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

На вкладке ГЛАВНАЯ в группе Стили щелкните стрелку рядом с пунктом Условное форматирование и выберите Управление правилами.

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

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

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

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

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

Хотите еще?

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

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

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

По умолчанию последнее созданное правило находится вверху списка и имеет приоритет над ранее созданными правилами.

Например, в ячейках с B2 по B10 я уже создал два правила.

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

Следующее правило, которое я создал и, следовательно, имеющее приоритет, заключается в том, что если значение ячейки больше 60 000, сделать цвет заливки синим.

Например, в ячейке B2 значение больше 70 000, но это значение также больше 60 000, поэтому ячейка имеет синюю заливку.

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

Правило 60 000 имеет приоритет над правилом 70 000.

Если мы изменим порядок правил, тем самым изменив приоритет, ячейки с размером более 70 000 будут отформатированы желтой заливкой, а ячейки с размером более 60 000, но менее 70 000 — с синей заливкой.

Чтобы узнать, в каких ячейках рабочего листа действуют правила условного форматирования, и, возможно, выяснить, почему они работают не так, как вы ожидаете, нажмите кнопку "Найти и выбрать" на вкладке ГЛАВНАЯ, а затем нажмите "Условное форматирование".

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

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

На листе нет ни одного регистра, поэтому условное форматирование не отображается.

Но правила есть.

Еще один способ управления условным форматированием — сортировка и фильтрация на основе применяемых цветов.

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

Нажмите кнопку "Сортировка и фильтр" на вкладке ГЛАВНАЯ (группа "Сортировка и фильтр" на вкладке "ДАННЫЕ" предоставляет аналогичные функции).

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

Для сортировки я выбираю значок ячейки; Мне нужны зеленые значки вверху.

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

Я нажимаю "ОК", и рабочий лист сортируется по условному форматированию.

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

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

И отображаются только строки с красными значками в столбце "Прибыль".

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

Конечно, всегда есть чему поучиться.

Поэтому ознакомьтесь с кратким изложением курса в конце, а лучше всего изучите Excel 2013 самостоятельно.

Я действительно запутался в работе функции Stop If True условного форматирования. Рассмотрим следующий пример:

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

Теперь, если я отмечу Stop if True перед самым первым правилом, я думаю, что правило, при обнаружении чисел в области больше 67, закрасится КРАСНЫМ цветом и перестанет работать, аналогично, если перед самым первым правилом установлен флажок Stop if True второго правила также проверяется, я думаю, что оно должно запуститься, а числа больше 33 также должны быть окрашены в ЖЕЛТЫЙ цвет, и работа должна прекратиться, т.е. оставить финальное правило.

Будет оценена любая логика/объяснение/разъяснение с правильным примером, как применить вышеуказанную стратегию?

Сообщить о нарушении

Я не уверен, что есть какой-то приоритет цвета (или что-то другое).

Рассмотрите другой пример, используя только ячейку B2 и следующие формулы и форматы CF:

<р>1. =B2>67 синяя заливка, черный шрифт Stop if True

<р>2. =B2>33 желтая заливка, белый шрифт Остановить, если True

<р>3. =B2>0 черная сплошная граница, черный шрифт

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

Теперь удалите Stop if True из правила 1, и я увижу синюю заливку, белый шрифт, без рамки.

Теперь удалите Stop if True из правила 2, и я увижу синюю заливку, белый шрифт и черную сплошную границу ячейки.

На самом деле проверка на странице справки CF показала, как я и предполагал:

Что происходит, когда несколько правил условного форматирования оцениваются как истина

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

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

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

Сообщить о нарушении

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