Всем известно, что в Excel можно ввести формулу. (В конце концов, какой была бы электронная таблица без формул?) Если вы используете адресные ссылки в формуле, эти ссылки автоматически обновляются, если вы вставляете или удаляете ячейки, строки или столбцы, и эти изменения каким-то образом влияют на адресную ссылку. Рассмотрим, например, следующую простую формулу:

=IF(A7=B7,"YES","NO")

Если вы вставляете ячейку выше B7, формула автоматически корректируется Excel, чтобы она выглядела следующим образом:

=IF(A7=B8,"YES","NO")

Что делать, если вы не хотите, чтобы Excel корректировал формулу? Вы можете попробовать добавить к адресу несколько знаков доллара, но это повлияет только на адреса в формулах, которые позже копируются; это не влияет на саму формулу, если вы вставляете или удаляете ячейки, которые влияют на формулу.

Лучший способ сделать ссылки на формулы «нерегулируемыми» — это изменить саму формулу для использования различных функций рабочего листа. Например, вы можете использовать эту формулу в ячейке C7:

=IF(INDIRECT("A"&ROW(C7))=INDIRECT("B"&ROW(C7)),"YES","NO")

Эта формула создает адрес на основе той ячейки, в которой находится формула. Функция СТРОКА возвращает номер строки ячейки (в данном случае C7, поэтому возвращаемое значение равно 7), а затем функция ДВССЫЛ используется для ссылки на построенный адрес, такие как A7 и B7. Если вы вставляете (или удаляете) ячейки выше A7 или B7, ссылка в ячейке C7 не нарушается, поскольку она просто беспечно создает новый адрес.

Другой подход — использовать функцию OFFSET для создания ссылки аналогичного типа:

=IF(OFFSET($A$1,ROW()-1,0)=OFFSET($B$1,ROW()-1,0),"YES","NO")

Эта формула просто смотрит, где оно находится (в столбце C), и сравнивает значения в ячейках слева от нее. Эта формула также не изменится, если вам случится вставить или удалить ячейки в столбце A или B.

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

  1. Выберите ячейку C2.

  2. Откройте вкладку «Формулы» на ленте.

  3. В группе «Определенные имена» щелкните инструмент «Определить имя». Excel отображает диалоговое окно «Новое имя». (См. Рис. 1.)

  4. В поле Имя введите имя CompareMe. (Вы можете использовать другое имя, если хотите.)

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

  6. Щелкните ОК.

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

=CompareMe

Он сравнивает все, что находится в двух ячейках слева, так же, как и ваша исходная формула. Более того, формула не корректируется автоматически при вставке или удалении ячеек.

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (12348) применим к Microsoft Excel 2007, 2010, 2013 и 2016.

Вы можете найти версию этого совета для старого интерфейса меню Excel здесь:

link: / excel-Non-Adjusting_References_in_Formulas [Некорректирующие ссылки в формулах].