Понятие относительных и абсолютных ссылок в формулах

В зависимости от выполняемых задач в MS Excel можно использовать относительные ссылки (relative references), определяющие положение ячейки относительно положения ячейки, содержащей формулу, или абсолютные ссылки (absolute references), которые всегда указывают на конкретные ячейки. Если перед буквой или номером стоит знак доллара, например, $A$1, то ссылка на столбец или строку является абсолютной.

При копировании формулы относительные ссылки (relative references) автоматически корректируются относительно направления копирования формулы, а абсолютные ссылки (absolute references) остаются неизменными.

Создание Абсолютных ссылок

Если необходимо, чтобы ссылки не изменялись при копировании формулы в другую ячейку, воспользуйтесь абсолютными ссылками (absolute references). Для этого:

1. Выделите ячейку с формулой (Рисунок 6.1).

2. В строке формул выделите ссылку, которую необходимо изменить (например, H12).

3. Нажимайте F4 пока не получите желаемой ссылки. Каждое нажатие F4 переключает тип ссылки в следующей последовательности:

$ H$12 — абсолютный столбец и абсолютная строка;
H$12 — относительный столбец и абсолютная строка;
$ H12 — абсолютный столбец и относительная строка;
H12 — относительный столбец и относительная строка.

4. Нажмите [ENTER].
MS Excel запишет формулу с Абсолютными ссылками.

Ячейка, содержащая формулу

Абсолютный адрес ячейки

Рисунок 6.1. Использование абсолютных ссылок

Использование ссылок на ячейки других листов и рабочих книг

Создание ссылок на ячейки других листов и рабочих книг

При использовании в создаваемых формулах ссылок на ячейки других листов и/или книг в процессе создания формулы следует перейти на другой лист текущей книги или в другую книгу и выделить там необходимую ячейку:

· При каждом переходе на другой лист, его имя автоматически добавляется к ссылке на ячейку. Имя листа и адрес ячейки разделены служебным символом! (восклицательный знак) (Рисунок 6.2).

Рисунок 6.2. Использование ссылки на ячейку другого листа

! Если имя рабочего листа состоит из нескольких слов, в формуле оно должно быть заключено в одинарные кавычки

· При переходе в другую книгу, имя книги и имя листа добавляются к ссылке на ячейку в одинарных кавычках (апострофах). При этом имя файла рабочей книги отображается в квадратных скобках, а адрес ячейки является абсолютной ссылкой на выделенную ячейку (Рисунок 6.3).

Рисунок 6.3. Использование ссылки на ячейку другой книги

Открытие книги содержащей ссылки на ячейки других листов и рабочих книг

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

Рисунок 6.4. Предупреждение системы безопасности

 

Чтобы обновить данные на листе, нажмите на кнопку Включить содержимое.

Если программа не сможет найти источник, например, если рабочая книга или рабочий лист удалены или переименованы, возникнет окно предупреждения, извещающее, что в документе присутствует нарушенная связь. (Рисунок 6.5)

Рисунок 6.5. Предупреждение о нарушении связи

В этом случае выполните следующие действия:

1. В окне предупреждения нажмите кнопку Обновить (Рисунок 6.5).

2. В открывшемся окне нажмите кнопку Изменить связи. Откроется окно диалога Изменение связей (Рисунок 6.6).

3. Выделите в списке связей ту связь, у которой в столбце Состояние содержится слово Ошибка:

4. Выполните одно из следующих действий:

Ø Если источник недоступен (удален) нажмите кнопку Разорвать связь. В окне подтверждения нажмите кнопку Разорвать связи.

Ø Если источник перемещен или переименован нажмите кнопку Изменить. В окне диалога Изменить источник, укажите книгу-источник.

5. Закройте окно Изменение связей, нажав кнопку Закрыть.

Рисунок 6.6. Окно диалога Изменение связей