При ссылке на ячейку в формуле в 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 может переключать ссылку между относительной ссылкой, абсолютной ссылкой и смешанной ссылкой.

А1 → $А$1 → А$1 → $А1 → А1

Если вы хотите сделать все ссылки в формуле абсолютными, выделите всю формулу в строке формул, нажмите 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

При перетаскивании манипулятора автозаполнения вниз, чтобы вычислить процент других фруктов, #DIV/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. Если вы хотите сделать ссылки на ячейки в сотнях формул абсолютными в Excel, Преобразовать ссылки инструмент Kutools for Excel может помочь вам справиться с работой с 2 ​​кликами.

Выберите ячейки формулы, для которых вы хотите сделать ссылки на ячейки абсолютными, щелкните Кутулс > Больше (эффекты) > Преобразовать ссылки. Затем выберите К абсолютному вариант и нажмите Ok or Применить. Теперь все ссылки на ячейки выбранных формул преобразованы в абсолютные.

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

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

Относительная ссылка — тип ссылки по умолчанию в 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 в ячейке С2 на числа в строках ниже.

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

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

=$B3*C$2

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

То, что нужно запомнить

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

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

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

  

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