Абсолютные и относительные ссылки в 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), то номер ячейки будет неизменяем, но столбец изменится.

[expert_bq id=»1570″]Таким образом, у нас отпадает необходимость о добавлении в таблицу целого столбца с одинаковыми данными, а формулы становятся более компактными и простыми. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] При помощи символа абсолютной адресации Вы можете гибко варьировать способ адресации ячеек. Например $B11 обозначает , что при копировании формул будет изменяться только адресация строки ячейки, а при обозначении B$11 — только столбца. Такая адресация называется смешанной.

Абсолютные и относительные ссылки в MS Excel — Вектор развития. Офисные системы для бизнеса

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