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

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


Как сделать ссылки абсолютными или относительными

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

А относительная ссылка – ссылка, при копировании которой координаты ячеек будут меняться относительно других ячеек.


Относительная ссылка


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



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

То есть мы выбираем строку «Сумма», и в строке формул вводим формулу «=В2*С2».

Получается что мы умножили «Количество (В2)» на «Цену (С2)».



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

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



А теперь что значит изменение координат при копировании относительной ссылки. Это проверить просто.

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


Ошибки в относительных ссылках

Но использовать относительные ссылки получится не всегда. Давайте возьмем ту же таблицу, но теперь нам нужно рассчитать удельный вес каждого товара из списка от общей суммы.

Сделать мы это можем при помощи деления стоимости товара на общую сумму товара. То есть для расчета удельного веса картофеля нам нужно рассчитать его сумму на общую сумму. Получается формула «=D2/D7».



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

Например, формула уже во второй ячейке выглядит так «=D3/D8», в общем сдвинулась вся формула на шаг вниз. Но ячейка D8 пуста, и соответственно производиться деление на 0, и в таком случае никакого результата и не может быть.

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

Нам же необходимо что бы менялась только сумма каждого продукта, но общая сумма (D7), оставалась все той же, это мы сможем сделать при помощи абсолютной ссылки.


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

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

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

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

Либо же, после ввода формулы нажмем кнопку F4. И ссылка автоматически станет абсолютной, то есть знак доллара сам выставиться в нужных местах. И получится формула вида «=D2/$D$7».



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

Если посмотреть на формулу в ячейке ниже, то она будет отображена так: «=D3/$D$7». Получается, значение товара было изменено, а общая сумма осталась той же.




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

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

Предположим вам нужно, что бы ячейка оставалась той же, но ее номер менялся, тогда знак доллара ставится только перед наименованием ячейки и получим значение $D7. А если наоборот (D$7), то номер ячейки будет неизменяем, но столбец изменится.




Заключение

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

Рейтинг:(голосов:1)