Ссылки в Excel – абсолютные, относительные и смешанные. Ошибки при работе с относительными ссылками в Excel

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

1. Относительные ссылки. Используется для простых вычислений. Копирование формулы влечет за собой изменение координат.
2. Абсолютные ссылки. Если вам необходимо произвести более сложные расчеты, этот вариант подойдет. Для фиксации используйте символ «$». Пример: $А$1.
3. смешанные ссылки. Этот вид адресации используется в расчетах, когда необходимо отдельно зафиксировать столбец или строку. Пример: $A1 или A$1.

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

Относительная ссылка на ячейку в Excel

Это набор символов, определяющих расположение ячейки. Ссылки в программе автоматически прописываются с относительной адресацией. Например: А1, А2, В1, В2. При переходе к другой строке или столбцу символы в формуле изменяются. Например, стартовая позиция А1. Перемещение по горизонтали меняет букву на В1, С1, D1 и т. д. Точно так же изменения происходят и при движении по вертикальной линии, только в этом случае меняется цифра – А2, А3, А4 и т. д. Если необходимо продублировать однотипный расчет в соседней ячейке, расчет производится по относительной ссылке. Чтобы использовать эту функцию, выполните несколько шагов:

1. Как только данные войдут в ячейку, наведите курсор и щелкните мышкой. Выделение зеленым прямоугольником указывает на активацию ячейки и готовность к дальнейшей работе.
2. Нажав комбинацию клавиш Ctrl+C, копируем содержимое в буфер обмена.
3. Активируем ячейку, в которую вы хотите перенести данные или ранее написанную формулу.
4. Нажатием комбинации Ctrl+V переносим данные, сохраненные в системном буфере обмена.

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

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

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

Порядок действий:

1. В примере видно, что в столбцах B и C было заполнено количество проданного товара и его цена. Соответственно, чтобы написать формулу и получить ответ, выберите столбец D. Формула выглядит следующим образом: = БИ 2 *C

Обращать внимание! Чтобы облегчить процесс написания формулы, воспользуйтесь небольшой хитростью. Поставьте знак «=», нажмите на количество проданного товара, установите знак «*» и нажмите на цену товара. Формула после знака равенства запишется автоматически.

2. Для окончательного ответа нажмите «Enter». Далее необходимо подсчитать общую сумму прибыли, полученной от других видов продукции. Ну а если количество строк не велико, то все манипуляции можно выполнить вручную. Для одновременного заполнения большого количества строк в Excel есть одна полезная функция, позволяющая перенести формулу в другие ячейки.
3. Наведите курсор на правый нижний угол прямоугольника с формулой или готовым результатом. Появление черного крестика служит сигналом о том, что курсор можно перетащить вниз. Таким образом производится автоматический расчет прибыли, полученной по каждому товару в отдельности.
4. Отпустив зажатую кнопку мыши, мы получим правильные результаты во всех строках.

Нажав на ячейку D3, вы увидите, что координаты ячейки были автоматически изменены и теперь выглядят так: =БИ 3 *C3. Отсюда следует, что связи были относительными.

Возможные ошибки при работе с относительными ссылками

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

1. Создайте таблицу и заполните: А – название продукта; Б – проданное количество; С – стоимость; D – полученная сумма. Предположим, в ассортименте всего 11 позиций. Следовательно, с учетом описания столбцов заполняется 12 строк и общая сумма прибыли равна D.
2. Нажмите на ячейку E2 и введите =D2 /D13.
3. После нажатия кнопки «Ввести» появится коэффициент относительной доли продаж первой позиции.
4. Растяните столбик вниз и дождитесь результата. Однако система выдает ошибку «#DIV/0!»

Причина ошибки – использование относительной ссылки для расчетов. В результате копирования формулы координаты изменяются. То есть для Е3 формула будет выглядеть так =D3 /D13. Поскольку ячейка D13 не заполнена и теоретически имеет нулевое значение, программа выдаст ошибку с информацией о том, что деление на ноль невозможно.

Важно! Чтобы исправить ошибку, необходимо записать формулу так, чтобы координаты D13 были зафиксированы. Относительная адресация не имеет такой функции. Для этого существует другой тип ссылок – абсолютные. 

Как сделать абсолютную ссылку в Excel

Благодаря использованию символа $ появилась возможность фиксировать координаты ячеек. Как это работает, мы рассмотрим далее. Так как программа по умолчанию использует относительную адресацию, соответственно, чтобы сделать ее абсолютной, вам потребуется выполнить ряд действий. Разберем решение ошибки «Как найти коэффициент от продажи нескольких наименований товара», выполнив расчет с использованием абсолютной адресации:

1. Нажмите на E2 и введите координаты ссылки. =D2 /D13. Поскольку ссылка относительная, для фиксации данных необходимо установить символ.
2. Зафиксировать координаты ячейки D. Для выполнения этого действия перед буквой, обозначающей столбец, и номером строки поставьте знак «$».

Совет эксперта! Чтобы облегчить задачу ввода, достаточно активировать ячейку редактирования формулы и несколько раз нажать клавишу F4. Пока не получите удовлетворительные значения. Правильная формула выглядит следующим образом: =D2/$D$13.

3. Нажмите кнопку «Ввести». В результате выполненных действий должен появиться правильный результат.
4. Перетащите маркер в нижнюю строку, чтобы скопировать формулу.

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

Как поставить смешанную ссылку в Excel

Для расчетов по формулам используются не только относительные и абсолютные ссылки, но и смешанные. Их отличительная особенность в том, что они фиксируют одну из координат.

• Например, чтобы изменить положение строки, необходимо перед буквенным обозначением написать знак $.
• Напротив, если после буквенного обозначения будет написан знак доллара, то показатели в строке останутся неизменными.

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

1. Для точных расчетов введите =D1/$D$3 и нажмите «Ввод». Программа дает точный ответ.
2. Чтобы переместить формулу в последующие ячейки столбца и получить точные результаты, перетащите маркер в нижнюю ячейку.
3. В результате программа выдаст правильные расчеты.

Внимание! Если перед буквой поставить знак $, то Excel выдаст ошибку «#ДЕЛ/0!», что будет означать, что данную операцию выполнить невозможно.

«СуперАбсолютная» адресация

В заключение давайте рассмотрим еще один пример абсолютной ссылки – «Суперабсолютную» адресацию. В чем его особенности и различия. Возьмите примерное число 30 и впишите его в ячейку В2. Именно это число будет основным, с ним необходимо совершить ряд действий, например, возвести его в степень.

1. Для корректного выполнения всех действий введите в столбец С следующую формулу: =$B$2^$D2. В столбец D вписываем значение градусов.

2. После нажатия кнопки «Ввод» и активации формулы растягиваем маркер вниз по столбцу.
3. Мы получаем правильные результаты.

Суть в том, что все выполняемые действия относились к одной фиксированной ячейке В2, поэтому:

• Копирование формулы из ячейки C3 в ячейку E3, F3 или H3 не изменит результат. Оно останется неизменным – 900.
• Если вам потребуется вставить новый столбец, координаты ячейки с формулой изменятся, но результат также останется неизменным.

В этом особенность ссылки «СуперАбсолют»: при необходимости переместить результат не изменится. Однако бывают ситуации, когда данные вставляются из сторонних источников. Таким образом, столбцы смещаются в сторону, а данные по-старому заносятся в столбец B2. Что происходит в этом случае? При смешивании формула меняется в зависимости от выполняемого действия, то есть она будет указывать уже не на В2, а на С2. Но поскольку вставка произведена в B2, конечный результат будет неверным.

Справка! Чтобы иметь возможность вставлять макросы из сторонних источников, необходимо включить настройки разработчика (по умолчанию они отключены). Для этого зайдите в «Параметры», откройте настройки ленты и установите флажок в правой колонке напротив «Разработчик». После этого откроется доступ ко многим функциям, которые ранее были скрыты от глаз обычного пользователя.

Возникает вопрос: можно ли изменить формулу из ячейки C2 так, чтобы исходное число собиралось из ячейки B, несмотря на вставку новых столбцов данных? Чтобы изменения в таблице не повлияли на определение итога при установке данных из сторонних источников, необходимо выполнить следующие действия:

1. Вместо координат ячейки В2 введите следующие показатели: =ДВССЫЛ(“B2”). В результате после перемещения формулирующий состав будет выглядеть так: =ДВССЫЛ(“B2”)^$E2.
2. Благодаря этой функции ссылка всегда указывает на квадрат с координатами B2, независимо от того, добавлены или удалены столбцы в таблице.

Необходимо понимать, что ячейка, не содержащая никаких данных, всегда показывает значение «0».

Заключение

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