Абсолютные и относительные ссылки в Excel

Абсолютные и относительные ссылки в Excel

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

Определение абсолютных и относительных ссылок

Что такое абсолютные и относительные ссылки в Excel?

Абсолютные ссылки — это ссылки, при копировании координаты ячеек не меняются, они неподвижны. В относительных ссылках координаты ячеек изменяются при копировании относительно других ячеек на листе.

Пример относительной ссылки

Покажем на примере, как это работает. Получите таблицу, содержащую количество и цену различных наименований продуктов. Нам нужно рассчитать цену.

Абсолютные и относительные ссылки в Excel

Это делается путем умножения числа (столбец B) на цену (столбец C). Например, для названия первого продукта формула будет выглядеть так: "= B2 * C2" . Вводим его в соответствующую ячейку таблицы.

Абсолютные и относительные ссылки в Excel

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

Абсолютные и относительные ссылки в Excel

Но, как видите, формула в нижней ячейке не отображается "= B2 * C2" , но "= B3 * C3" . Соответственно, приведенные ниже формулы изменены. Это свойство изменения при копировании и имеет относительные ссылки.

Абсолютные и относительные ссылки в Excel

Относительная эталонная ошибка

Но во всех случаях нам не нужны относительные ссылки. Например, в той же таблице мы должны вычислить удельный вес стоимости каждого наименования продукта из общей суммы. Это делается путем деления затрат на общую сумму. Например, чтобы рассчитать удельный вес картофеля, мы делим его значение (D2) на общее количество (D7). Получаем следующую формулу: "= D2 / D7" .

Если мы попытаемся скопировать формулу в другие строки так же, как и предыдущую, мы получим совершенно неудовлетворительный результат. Как видите, во второй строке таблицы формула имеет вид "= D3 / D8" , т.е. не только ссылка на ячейку с суммой перемещенной строки, но и ссылка на ячейку, отвечающую за общий результат.

Абсолютные и относительные ссылки в Excel

D8 — полностью пустая ячейка, поэтому формула выдает ошибку. Соответственно, формула в строке ниже будет относиться к ячейке D9 и так далее. Нам также необходимо сохранить постоянную ссылку на ячейку D7 при копировании, где находится итог, и это свойство имеет только абсолютные ссылки.

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

Таким образом, в нашем примере делитель должен быть относительной ссылкой и изменением в каждой строке таблицы, а делимое должно быть абсолютной ссылкой, которая постоянно ссылается на одну ячейку.

У пользователей не возникнет проблем с созданием относительных ссылок, поскольку все ссылки в Microsoft Excel по умолчанию являются относительными. Но если вам нужно сделать абсолютную ссылку, вам придется применить технику.

После ввода формулы просто поместите строку в ячейку или формулу перед координатами столбца и строкой ячейки, на которую вы хотите сделать абсолютную ссылку — знак доллара. Вы также можете нажать программную клавишу F7, как только вы введете адрес, и доллар появится до того, как автоматически появятся координаты строки и столбца. Формула в верхней ячейке будет иметь следующий вид: "= D2 / $ D $ 7" .

Абсолютные и относительные ссылки в Excel

Скопируйте формулу в столбец. Как видите, на этот раз все получилось. Ячейки содержат правильные значения. Например, во второй строке таблицы формула имеет вид "= D3 / $ D $ 7" , т.е. делитель изменился, а размер дивиденда не изменился.

Абсолютные и относительные ссылки в Excel

Смешанные ссылки

Помимо типичных абсолютных и относительных ссылок, существуют так называемые смешанные ссылки. В них меняется одна из составляющих и фиксируется второй. Например, для смешанной ссылки $ D7 строка изменяется, а столбец фиксируется. На подключении D $ 7 столбец наоборот меняется, но линия имеет абсолютное значение.

Абсолютные и относительные ссылки в Excel

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

Понравилась статья? Поделиться с друзьями:
Что нужно знать пользователю?