Если вы работаете в Excel более второго дня, то наверняка уже встречали или использовали ссылки со знаком доллара в формулах и функциях Excel, например 2 доллара США or 3 XNUMX XNUMX канадских долларов и т. д. Давайте наконец разберемся, что именно они означают, как работают и где могут быть полезны в ваших файлах.
Относительные ссылки
Это обычные ссылки в виде номера буквы-строки столбца ( A1, С5, то есть «линкор») встречается в большинстве файлов Excel. Их особенность в том, что при копировании формул они смещаются. Те. C5, например, превращается в С6, С7 и т. д. при копировании или D5, E5 и т. д. при копировании вправо и т. д. В большинстве случаев это нормально и проблем не создает:
Смешанные ссылки
Иногда нежелателен тот факт, что ссылка в формуле при копировании «скользит» относительно исходной ячейки. Тогда для исправления ссылки используется знак доллара ($), который позволяет исправить то, что было раньше. Так, например, ссылка $C5 не будет меняться в разных столбцах (т. е. С никогда не превратится в D, E or F), но может сдвигаться по строкам (т.е. может сдвигаться на $ C6, $ C7 и т. д.). Так же, C$5 – не будет перемещаться по строкам, но может «ходить» по столбцам. Такие ссылки называются смешанный:
Абсолютные ссылки
Ну а если в ссылку добавить оба доллара сразу(5 канадских долларов) – превратится в абсолютный и никак не изменится при любом копировании, т.е. доллары фиксируются намертво и в строке, и в столбце:
Самый простой и быстрый способ превратить относительную ссылку в абсолютную или смешанную ссылку — выделить ее в формуле и несколько раз нажать клавишу F4. Этот ключ обводит все четыре возможных варианта фиксации ссылки на ячейку: C5 → 5 канадских долларов → $C5 → C$5 и все сначала.
Все просто и понятно. Но есть одно «но».
Предположим, мы хотим сделать абсолютную ссылку на ячейку. С5. Такого, что она ВСЕГДА ссылалась на С5 независимо от дальнейших действий пользователя. Забавная вещь получается — даже если сделать ссылку абсолютной (т.е. 5 канадских долларов), он все еще меняется в некоторых ситуациях. Например: если вы удалите третью и четвертую строки, они изменятся на 3 канадских долларов. Если вы вставите столбец слева С, то оно изменится на D. Если вы разрежете клетку С5 и вставить в F7, то оно изменится на F7 и так далее. Что, если мне нужна действительно жесткая ссылка, которая всегда будет ссылаться на С5 и больше ничего ни при каких обстоятельствах или действиях пользователя?
Действительно абсолютные ссылки
Решение состоит в использовании функции КОСВЕННЫЕ (КОСВЕННО), который генерирует ссылку на ячейку из текстовой строки.
Если вы введете формулу в ячейку:
=ДВССЫЛ («C5»)
=ДВССЫЛ(«C5»)
тогда он всегда будет указывать на ячейку с адресом C5 независимо от дальнейших действий пользователя, вставки или удаления строк и т. д. Единственная небольшая сложность заключается в том, что если целевая ячейка пуста, то КОСВЕННЫЕ выводит 0, что не всегда удобно. Однако это можно легко обойти, применив чуть более сложную конструкцию с проверкой через функцию ISBLANK:
=ЕСЛИ(ISNULL(ДВССЫЛ("C5″))",", ДВССЫЛ("C5"))
=ЕСЛИ(ПУСТО(ДВССЫЛ(«C5″));»»;ДВССЫЛ(«C5»))
- Ссылки на XNUMXD группы листов при объединении данных из нескольких таблиц
- Зачем нужен стиль ссылки R1C1 и как его отключить
- Точное копирование формул макросом с надстройкой PLEX