Ссылки на ячейки Excel — относительные и абсолютные ссылки в правилах условного форматирования

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

«Мое условное правило было правильным, за исключением смешанных ссылок». Об этом часто сообщают в комментариях читатели нашего блога. Итак, почему бы нам не потратить несколько минут, чтобы разобраться в этом? Это, безусловно, сэкономит вам гораздо больше времени в долгосрочной перспективе!

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

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

  • Абсолютные ссылки на ячейки (со знаком $, например, $A$1) всегда остаются постоянными, независимо от того, куда они копируются.
  • Относительные ссылки на ячейки (без знака $, например, A1) изменяются в зависимости от относительного положения строк и столбцов при копировании в несколько ячеек.
  • Ссылки на смешанные ячейки (абсолютный столбец и относительная строка (например, $A1) или относительный столбец и абсолютная строка (например, A$1). В правилах условного форматирования Excel чаще всего используются смешанные ссылки на ячейки, указывающие на то, что буква столбца или номер строки чтобы оставаться фиксированным, когда правило применяется ко всем другим ячейкам в выбранном диапазоне.

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

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

Пример 1. Абсолютный столбец и относительная строка

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

Давайте создадим очень простое правило, которое сравнивает значения в столбцах A и B и выделяет значение в столбце A, если оно больше, чем значение в столбце B в той же строке.

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

=$A1>$B1

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

Пример 2. Относительный столбец и абсолютная строка

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

Например, приведенная ниже формула сравнивает значения в строках 1 и 2, и правило выделяет значение в строке 1, если оно больше, чем значение в строке 2 в том же столбце:

=А$1>А$2
Относительные столбцы и абсолютные ссылки на ячейки строк в условном форматировании Excel

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

Пример 3. Абсолютный столбец и абсолютная строка

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

Например, давайте создадим правило, которое выделяет все значения в столбце A, превышающие значение в ячейке B1. Формула выглядит следующим образом:

=$A1>$B$1

Обратите внимание на использование следующих ссылок:

  • $A1 — вы используете абсолютные ссылки на столбцы и относительные строки, потому что мы хотим сверять значения во всех ячейках столбца A со значением в ячейке B1.
  • $B$1 — вы используете абсолютный столбец и абсолютную строку, потому что ячейка B1 содержит значение, с которым вы хотите сравнить все остальные значения, и вы хотите, чтобы эта ссылка на ячейку была постоянной.

Абсолютные ссылки на ячейки столбцов и строк в условном форматировании Excel

Пример 4. Относительный столбец и относительная строка

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

Предположим, вы хотите выделить все ячейки в столбцах A и B, которые больше значения в ячейке B1. Вы можете просто скопировать формулу из предыдущий пример и замените $A1 на A1, так как вы не хотите исправлять ни строку, ни столбец:

=$A1>$B$1

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

Кончик. Чтобы быстро переключаться между абсолютными и относительными ссылками, выберите ссылку на ячейку в строке формул и нажмите функциональную клавишу F4. Ссылка будет чередоваться между четырьмя типами от относительной к абсолютной, например: A1 > $A$1 > A$1 > $A1, а затем обратно к относительной ссылке A1.

Полезные ресурсы

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

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *