Всем известно, что в 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. Выберите «Имя» в меню «Вставка», затем выберите «Определить» в подменю.

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

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

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

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

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

=CompareMe

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

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

Этот совет (2876) применим к Microsoft Excel 97, 2000, 2002 и 2003. Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и более поздних версий) здесь:

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