При работе с формулами в Microsoft Excel пользователям необходимо работать со ссылками на другие ячейки в документе. Но не все пользователи знают, что эти ссылки бывают двух типов: абсолютные и относительные. Давайте узнаем, чем они отличаются друг от друга и как создать ссылку нужного типа.
Определение абсолютных и относительных ссылок
Что такое абсолютные и относительные ссылки в Excel?
Абсолютные ссылки — это ссылки, при копировании координаты ячеек не меняются, они находятся в фиксированном состоянии. В относительных ссылках координаты ячеек меняются при копировании относительно других ячеек на листе.
Пример относительной ссылки
Покажем, как это работает, на примере. Получите таблицу, содержащую количество и цену различных наименований продуктов. Нам нужно рассчитать стоимость.
Это делается путем простого умножения количества (столбец B) на цену (столбец C). Например, для первого названия продукта формула будет выглядеть так: «= B2 * C2». Вставляем в соответствующую ячейку таблицы.
Теперь, чтобы не вбивать вручную формулы для ячеек ниже, давайте просто скопируем эту формулу на весь столбец. Мы находимся в правом нижнем углу ячейки с формулой, щелкаем левой кнопкой мыши и, удерживая кнопку, перетаскиваем мышь вниз. Таким образом, формула будет скопирована в другие ячейки таблицы.
Но, как видите, формула в ячейке ниже больше не выглядит как «= B2 * C2», а «= B3 * C3». Следовательно, следующие формулы также изменились. Это свойство изменения при копировании и имеет свои ссылки.
Ошибка в относительной ссылке
Но ни в коем случае нам нужны только относительные ссылки. Например, нам нужно рассчитать удельный вес стоимости каждой позиции от общей суммы в той же таблице. Это делается путем деления значения на общую сумму. Например, чтобы рассчитать удельный вес картофеля, мы разделим его стоимость (D2) на общую сумму (D7). Получаем следующую формулу: «= D2 / D7».
Если мы попытаемся скопировать формулу в другие строки так же, как и в предыдущий раз, мы получим совершенно неудовлетворительный результат. Как видите, уже во второй строке таблицы формула выглядит как «= D3 / D8», т.е перемещена не только ссылка на ячейку с суммой в строке, но и ссылка на ячейку отвечает за общую сумму.
D8 — это полностью пустая ячейка, поэтому формула выдает ошибку. В результате формула в следующей строке будет ссылаться на ячейку D9 и так далее. При копировании нам нужно сохранить ссылку на ячейку D7, где находится общая сумма, и это свойство является только абсолютными ссылками.
Создание абсолютной ссылки
Итак, в нашем примере делителем должна быть относительная ссылка и изменение в каждой строке таблицы, а делимое должно быть абсолютной ссылкой, которая постоянно ссылается на ячейку.
У пользователей не возникнет проблем с созданием относительных ссылок, поскольку все ссылки в Microsoft Excel по умолчанию являются относительными. Но если вам нужно сделать абсолютную ссылку, вам придется применить трюк.
После ввода формулы просто вставьте знак доллара в ячейку или строку формул перед координатами столбца и строки ячейки, на которую вы хотите сослаться. Или же сразу после ввода адреса нажмите функциональную клавишу F7, и символы доллара перед координатами строки и столбца появятся автоматически. Формула в верхней ячейке будет выглядеть так: «= D2 / $ D $ 7».
Скопируйте формулу в столбец. Как видите, на этот раз все обошлось. Ячейки содержат правильные значения. Например, во второй строке таблицы формула имеет вид «= D3 / $ D $ 7», то есть делитель изменился, но дивиденд не изменился.
Смешанные ссылки
Помимо типичных абсолютных и относительных ссылок, существуют так называемые смешанные ссылки. В них одна из составляющих меняется, а вторая фиксируется. Например, для смешанной ссылки $ D7 строка изменяется, но столбец закрепляется. И наоборот, ссылка D $ 7 изменяет столбец, но строка имеет абсолютное значение.
Как видите, при работе с формулами в Microsoft Excel для выполнения различных задач нужно работать как с относительными, так и с абсолютными ссылками. В некоторых случаях также используются смешанные ссылки. Поэтому даже средний пользователь должен четко понимать разницу между ними и уметь пользоваться этими инструментами.
