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

Question

При использовании условного форматирования в Excel 2013 я не могу применить одно правило к нескольким строкам. Я использую данные, которые не связаны в несколько строк. Например, строка A содержит цены, строка B содержит затраты, а строка C содержит цены акций.

Я использую следующее правило "Форматировать только значения выше или ниже среднего", так как я хочу выделить ячейки, которые являются одним st.dev. ниже или один ст.дев. выше среднего. Если я использую средство рисования формата, чтобы применить правила, установленные для строки A, к строке B, это не сработает (поскольку использует среднее значение, рассчитанное в строке A). Если я попытаюсь изменить формулу ($ A $ 2:$ A $ 20 -> A $ 2:A $ 22), Excel не применит изменения и продолжит изменять их на ($ A $ 2:$ A $ 20).

У кого-нибудь есть идеи, как я могу решить эту проблему?

Answer 1

"Секрет" состоит в том, чтобы сделать формулу с "относительной ссылкой", следовательно, если ваша формула выглядит следующим образом:= $ B $ 2> $ A $ 2

затем уберите знак $ перед номером строки, чтобы он выглядел так:= $ B2> $ A2

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

Надеюсь, это полезно.

Answer 2

Ключевым моментом для понимания условного форматирования Excel является знание того, что

The formula you create for Conditional Formatting is based on the cell that is currently active. The cells affected (to be colored) are those in the Selection Range.

Источник

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

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

Answer 3

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

Мой условный формат применяется к строке, начинающейся со столбца A до столбца C, в соответствии с вашим примером, но вы можете понять концепцию.

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

= $ А2 = ср ($ A $ 2:$ A $ 22)

нажмите OK после выбора вашего формата. Затем для применения к разделу и ввода

= $ A:$ C

Будет работать для разных ситуаций.

Answer 4

Я столкнулся с той же проблемой, желая применить условное форматирование к нескольким строкам. Это было простое решение, которое я нашел на сайте поддержки Microsoft Office. Вы можете использовать средство рисования формата на вкладке «Главная» ленты в группе «Буфер обмена». Это кисть, расположенная прямо под кнопкой копирования.

Answer 5

Это невозможно с базовой функциональностью Excel. После долгих поисков, попыток рекомендуемых решений и проб и ошибок я должен согласиться с тем, что макрос предлагает единственное решение; По какой-то причине Excel не корректирует ссылки на ячейки, когда пользователь пытается скопировать условное форматирование (как это происходит при копировании формулы ячейки). (Я предполагаю, что Microsoft назвал бы это "особенностью".)

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

К сожалению, это не дает обходного пути к проблеме ОП. Сохранение (из Kingsoft) в формате .xlsx и повторное открытие в Microsoft Excel было неудачным; (скопированное) условное форматирование было повреждено. Но если нужно много условного форматирования и не обязательно использовать Excel, это может быть полезно.

Answer 6

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

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

Допустим, ваш первый ряд от А1 до J1. Перейдите в A1, добавьте правило для условного форматирования с помощью формулы и, чтобы выделить самое низкое значение строки, поместите в поле формулы: = A1 = MIN($ A1: $ J1)

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