Условное форматирование с помощью формул

[NicholasLibre]

Уважаемые, друзья ! Почти с ума сошёл, пытаясь разобраться в том как работает условное форматирование с формулами. Я опытный пользователь, но не могу разобраться ... Прошу посмотреть приложенный файл и разъяснить, что там вообще происходит. Почему на листе1 подсвечивается ячейка первого столбца, хотя должна по идее ячейка, где указано соответствующее значение - "2". Почему на листе3 столбец. Наконец почему удаётся сработать условному форматированию на "листе4 - первый случай", а на "листе4 - второй случай" происходит какое-то смещение ?   

[mikekaganski]

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

Потому что формулы условного форматирования подчиняются тем же правилам относительной/абсолютной адресации, что и обычные формулы - но пишутся *относительно самой первой ячейки диапазона*. Поэтому с учётом, что диапазон - A1:R278 (т.е. первая ячейка диапазона - A1), а формула - С1 = 2, условие форматирования читается так: "подсветить ту ячейку, у которой справа через одну ячейку записана двойка".

[NicholasLibre]

Огромное спасибо за точный и быстрый ответ ! Вот эту самую суть я интуитивно, кажется, и уловил, но пока что в голове не совсем укладывается... Не могли бы Вы в таком же ключе по поводу других листов прокомментировать ?)

[mikekaganski]

Не могли бы Вы в таком же ключе по поводу других листов прокомментировать ?

Почему на листе3 столбец

Диапазон - A1:R300 → референсная ячейка A1. Формула - C$1=2, ссылающаяся на фиксированно первую строку столбца на два правее референсного. "Подсветить ячейку, если на два столбца правее в строке 1 двойка".

Наконец почему удаётся сработать условному форматированию на "листе4 - первый случай", а на "листе4 - второй случай" происходит какое-то смещение ?

Лист4 - первый случай. Первый диапазон: A1:R300 → референсная ячейка A1. Формула (избыточная) IF (OR(F1=7, F1=9), 1, 0) (можно обойтись просто OR(F1=7, F1=9)). "Подсвети меня, если у меня справа на 5 ячеек семёрка или девятка". Второй диапазон: C1:C300 → референсная ячейка C1. Формула IF (OR($C1=2, $C1=5), 1, 0). "Подсвети меня, если в моей строке, в фиксированном столбце C, двойка или пятёрка".
Лист4 - второй случай. Первый диапазон: A1:R300 → референсная ячейка A1. Формула повреждена, не рассматриваем. Второй диапазон: C1:C300 → референсная ячейка C1. Формула IF (OR($C5=2, $C5=5), 1, 0). "Подсвети меня, если на 4 строки ниже, в фиксированном столбце C, двойка или пятёрка".

Справка по относительной/абсолютной адресации.