Абсолютная ссылка в Excel (как создать и использовать)

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

• Что такое абсолютная ссылка
• Как создавать абсолютные ссылки
• Использование абсолютной ссылки с примерами
• Расчет процента от общей суммы
• Поиск значения и возврат соответствующего совпадающего значения
• 2 щелчков для массового преобразования ссылок на ячейки в абсолютные с помощью Kutools
• Относительная и смешанная ссылки
• Важные моменты

Бесплатно загрузите образец файла

Видео: Абсолютная ссылка

Что такое абсолютная ссылка

Абсолютная ссылка — это тип ссылки на ячейку в Excel.

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

Абсолютная ссылка создаётся добавлением знака доллара ($) перед обозначениями столбца и строки в формуле. Например, чтобы сделать абсолютную ссылку на ячейку A1, запишите её как $A$1.

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

Например, диапазон A4:C7 содержит цены на товары, а в ячейке B2 указана ставка налога — на основе этих данных вы хотите рассчитать сумму налога к оплате для каждого товара.

Если вы используете относительную ссылку в формуле, например «=B5*B2», и перетащите маркер автозаполнения вниз для её копирования, результаты окажутся неверными. Дело в том, что ссылка на ячейку B2 будет смещаться вместе с формулой: в ячейке C6 она примет вид «=B6*B3», а в C7 — «=B7*B4».

Однако если использовать абсолютную ссылку на ячейку B2 в формуле «=B5*$B$2», ставка налога останется неизменной для всех ячеек при перетаскивании формулы вниз с помощью маркера автозаполнения, и результаты будут корректными.

Использование относительной ссылки		Использование абсолютной ссылки

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

Чтобы создать абсолютную ссылку в Excel, добавьте знаки доллара ($) перед обозначениями столбца и строки в формуле. Существует два способа создания абсолютной ссылки:

Добавление знаков доллара вручную

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

Например, если вы хотите сложить числа в ячейках A1 и B1, сделав обе ссылки абсолютными, просто введите формулу «=$A$1+$B$1». Это гарантирует, что ссылки на ячейки останутся неизменными при копировании или перемещении формулы в другие ячейки.

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

Использование сочетания клавиш F4 для преобразования относительной ссылки в абсолютную

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

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

A1 → $A$1 → A$1 → $A1 → A1

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

A1+B1 → $A$1++$B$1 → A$1++B$1 → $A1+$B1 → A1+B1

Использование абсолютной ссылки с примерами

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

Пример 1. Расчёт процента от общей суммы

Допустим, у вас есть диапазон данных (A3:B7), в котором указаны объёмы продаж каждого фрукта, а в ячейке B8 — общее количество продаж всех этих фруктов. Теперь вы хотите рассчитать, какой процент от общего объёма продаж приходится на каждый фрукт.

Универсальная формула для расчёта процента от общего значения:

Percentage = Sale/Amount

Используйте относительную ссылку в формуле для расчёта процента первого фрукта следующим образом:

=B4/B8

При перетаскивании маркера автозаполнения вниз для расчёта доли других фруктов появятся ошибки #ДЕЛ/0!

Когда вы перетаскиваете маркер автозаполнения для копирования формулы в ячейки ниже, относительная ссылка B8 автоматически корректируется на другие ссылки (B9, B10, B11) в зависимости от их относительного положения. Ячейки B9, B10 и B11 пусты (содержат нули), и поскольку деление на ноль невозможно, формула возвращает ошибку.

Чтобы устранить ошибки, в данном случае необходимо сделать ссылку на ячейку B8 абсолютной ($B$8) в формуле, чтобы она оставалась неизменной при перемещении или копировании. Теперь формула выглядит следующим образом:

=B4/$B$8

Затем перетащите маркер автозаполнения вниз, чтобы рассчитать процент для остальных фруктов.

Пример 2. Поиск значения и получение соответствующего результата

Допустим, вы хотите найти список имён в диапазоне D4:D5 и получить соответствующие оклады, используя данные об именах сотрудников и их годовых окладах из диапазона A4:B8.

Универсальная формула для поиска:

=VLOOKUP(lookup_value, table_range, column_index, logical)

Если вы используете относительную ссылку в формуле для поиска значения и возврата соответствующего результата следующим образом:

=VLOOKUP(D4,A4:B8,2,FALSE)

При перетаскивании маркера автозаполнения вниз для поиска следующего значения будет возвращена ошибка.

При перетаскивании маркера заполнения вниз формула копируется в ячейку ниже, и все ссылки в ней автоматически смещаются на одну строку вниз. В результате диапазон A4:B8 превращается в A5:B9. Поскольку Лиза отсутствует в диапазоне A5:B9, формула возвращает ошибку.

Чтобы избежать ошибок, используйте абсолютную ссылку $A$4:$B$8 вместо относительной ссылки A4:B8 в формуле:

=VLOOKUP(D4,$A$4:$B$8,2,FALSE)

Затем перетащите маркер автозаполнения вниз, чтобы получить оклад Лизы.

2 кликов для массового преобразования ссылок на ячейки в абсолютные с помощью Kutools

Независимо от того, вводите ли вы формулы вручную или используете клавишу F4, в Excel можно изменить лишь одну формулу за раз. Если вам нужно преобразовать ссылки на ячейки в сотнях формул в абсолютные — инструмент «Преобразовать ссылки на ячейки» из Kutools для Excel справится с этой задачей всего за два клика!

Чтобы преобразовать ссылки на ячейки в абсолютные сразу в нескольких формулах, выделите ячейки с этими формулами и перейдите по пути: «Kutools» > «Дополнительно» > «Преобразовать ссылки на ячейки». Затем выберите опцию «В абсолютные» и нажмите «ОК» или «Применить». Все ссылки на ячейки в выбранных формулах станут абсолютными.

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

Помимо абсолютной ссылки существуют ещё два типа: относительная и смешанная.

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

Например, если вы введёте в ячейку формулу «=A1+1», а затем перетащите маркер автозаполнения вниз, чтобы заполнить ею следующую ячейку, формула автоматически обновится до «=A2+1».

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

Возьмём в качестве примера таблицу умножения: строки и столбцы содержат числа от 1 до 9, которые нужно перемножить между собой.

Для начала введите формулу «=B3*C2» в ячейку C3, чтобы умножить значение из ячейки B3 на число (1) из первой строки. Однако, когда вы перетащите маркер автозаполнения вправо для заполнения остальных ячеек, окажется, что все результаты, кроме первого, неверны.

Это происходит потому, что при копировании формулы вправо номер строки остаётся неизменным, а номер столбца меняется: B3 превращается в C3, D3 и так далее. В результате формулы в ячейках справа (D3, E3 и т. д.) становятся «=C3*D2», «=D3*E2» и далее, тогда как на самом деле должны быть «=B3*D2», «=B3*E2» и т. д.

В этом случае добавьте знак доллара ($), чтобы зафиксировать ссылку на столбец в ячейке B3. Используйте следующую формулу:

=$B3*C2

Теперь при перетаскивании формулы вправо результаты будут отображаться корректно.

Затем умножьте число 1 из ячейки C2 на числа в строках ниже.

При копировании формулы вниз номер столбца в ссылке на ячейку C2 остаётся неизменным, а номер строки автоматически увеличивается: C2 → C3 → C4 и так далее. В результате формулы в нижестоящих ячейках принимают вид «=$B4*C3», «=$B5*C4» и т.д., что приводит к ошибочным результатам.

Чтобы решить эту проблему, замените «C2» на «C$2» — так ссылка на строку станет абсолютной при перетаскивании маркера автозаполнения вниз для копирования формул.

=$B3*C$2

Теперь вы можете перетащить маркер автозаполнения вправо или вниз — и получить все результаты.

Важные моменты

• Обзор ссылок на ячейки

  Тип	Пример	Краткое описание
  Абсолютная ссылка	$A$1	Не изменяется при копировании формулы в другие ячейки
  Относительная ссылка	A1	Ссылки на строку и столбец изменяются в зависимости от относительного положения при копировании формулы в другие ячейки
  Смешанная ссылка	$A1/A$1	Ссылка на строку изменяется при копировании формулы в другие ячейки, а ссылка на столбец фиксирована / Ссылка на столбец изменяется при копировании формулы в другие ячейки, а ссылка на строку фиксирована;

• Как правило, абсолютные ссылки остаются неизменными при перемещении формулы. Однако они автоматически корректируются при добавлении или удалении строки или столбца выше или левее ячейки на листе. Например, если в формуле «=$A$1+1» вы вставите строку в начало листа, она автоматически изменится на «=$A$2+1».

  

• Клавиша «F4» позволяет легко переключаться между относительными, абсолютными и смешанными ссылками.