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

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

• Что такое абсолютная ссылка
• Как создать абсолютные ссылки
• Использование абсолютной ссылки с примерами
• Вычисление процента от общей суммы
• Поиск значения и возврат соответствующего совпадающего значения
• Два клика для массового преобразования ссылок на ячейки в абсолютные с помощью 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

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

Эта часть предоставляет 2 примера, показывающих, когда и как использовать абсолютные ссылки в формуле 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)

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

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

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

Чтобы сделать ссылки на ячейки абсолютными в нескольких формулах, выберите ячейки с формулами и нажмите «Kutools» > «Еще» > «Преобразовать ссылки». Затем выберите опцию «В абсолютные» и нажмите «Ok» или «Apply». Теперь все ссылки на ячейки выбранных формул были преобразованы в абсолютные.

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

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

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

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

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

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

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

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

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

=$B3*C2

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

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

При копировании формулы вниз позиция столбца ячейки C2 не меняется, но позиция строки меняется с C2 на C3, C4 и т. д. В результате формулы в ячейках ниже меняются на «=$B4C3», «=$B5C4» и т. д., что приведет к неправильным результатам.

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

=$B3*C$2

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

Важные моменты для запоминания

• Сводка по ссылкам на ячейки

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

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

  

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