Некорректирующие ссылки в формулах (Microsoft Excel)
Всем известно, что в 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, которая редко используется большинством людей. Выполните следующие действия:
-
Выберите ячейку C2.
-
Откройте вкладку «Формулы» на ленте.
-
В группе «Определенные имена» щелкните инструмент «Определить имя». Excel отображает диалоговое окно «Новое имя». (См. Рис. 1.)
-
В поле Имя введите имя CompareMe. (Вы можете использовать другое имя, если хотите.)
-
Удалите все, что находится в поле «Ссылается на», заменив его следующей формулой:
-
Щелкните ОК.
На этом этапе вы создали свою именованную формулу. Теперь вы можете использовать его в любой ячейке столбца C следующим образом:
=CompareMe
Он сравнивает все, что находится в двух ячейках слева, так же, как и ваша исходная формула. Более того, формула не корректируется автоматически при вставке или удалении ячеек.
ExcelTips — ваш источник экономичного обучения Microsoft Excel.
Этот совет (12348) применим к Microsoft Excel 2007, 2010, 2013 и 2016.
Вы можете найти версию этого совета для старого интерфейса меню Excel здесь:
link: / excel-Non-Adjusting_References_in_Formulas [Некорректирующие ссылки в формулах]
.