Использование абсолютных и относительных ссылок в Excel

Excel использует мощную векторную систему управления формулами. С помощью формул, каждая ячейка может быть связана с другими. К примеру, простейшим вариантом описания этого подхода является сложение значений, находящихся в двух ячейках, используя формулу.

Названия колонок определены с помощью буквенных символов, строк – цифровых. Таким образом первая клетка имеет название A1.

На рисунке изображены две ячейки со значениями. Формула, которая складывает значения первой и второй ячейки, будет выглядеть следующим образом: =A1+A2. Если мы пропишем эту формулу в ячейке A3, мы увидим сумму первых двух ячеек.

6-add-two-values   6-add-two-values-1

Теперь давайте рассчитаем процентное соотношение между двумя ячейками. Это можно сделать, разделив значение первой ячейки на результат суммирования, таким образом получим формулу: =A1/A3. Поместив ее в ячейку B1, получим результат 14%. Однако, если мы скопируем эту ячейку в B2, excel выдаст нам ошибку «деления на ноль». Скопированная формула A1/A3 превратилась в A2/A4. Это происходит потому, что excel изменяет ссылки на другие ячейки относительно изменения положения первоначальной ячейки. На самом деле нам необходимо, чтобы A1 стало A2, но A3 при этом не менялось. Этого можно добиться, воспользовавшись абсолютными ссылками, которые не изменяют своего адреса при копировании ячеек. Для этого необходимо добавить знак $ перед номером колонки и строки, должно получится $A$1

6-use-relative-links   6-use-relative-links1   6-use-absolute-links

Подробнее об абсолютных ссылках

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

В таблице показано, как изменяется тип ссылки, если формула со ссылкой копируется на две ячейки вниз и на две ячейки вправо.

6-references

Первоначальная ссылка Новая ссылка
$A$1 – абсолютный столбец и абсолютная строка. Во время использования абсолютных ссылок на ячейку, адрес остается неизменным вне зависимости от позиции ячейки, содержащей формулу. $A$1 – абсолютная ссылка
A1 – относительный столбец и относительная строка. Во время использования относительной ссылки на ячейку, формула автоматически регулируется при копировании. C3 – относительная ссылка
A$1 – относительный столбец и абсолютная строка C$1 – смешанная ссылка
$A1 – абсолютный столбец и относительная строка $A3 – смешанная ссылка

Маленький прием на последок

Мне очень нравится использовать сочетания клавиш. Чтобы редактировать ячейки, содержащие формулы (например, требуется изменить ячейку F22), убедитесь, что ячейка выделена и нажмите клавишу «F4». При каждом нажатии, ссылка будет принимать все возможные варианты по циклу $F$22, F$22, $F22, F22.


3 комментариев

  1. Ренат, спасибо за хорошие тексты, могу добавить лишь «запятую» —
    обратите внимание на глагольные формы!
    Примеры из текста:

    … Мне очень нравиться использовать …
    будет нравиться? Или, всё же, «нравится»?
    … запись, которая находиться в той же группе …
    будет находиться? Или, всё же, «находится»?

  2. Для создания абсолютной ссылки удобно использовать клавишу абсолютной ссылки F4, которая осуществляет преобразование относительной ссылки в абсолютную и наоборот.

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *