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

Изменение типа ссылки: относительная, абсолютная, смешанная

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

Например, при копировании формулы =B4*C4 из ячейки D4 в D5 формула в ячейке D5 корректируется на один столбец вправо и становится =B5*C5. Если вы хотите сохранить исходную ссылку на ячейку в этом примере при копировании, необходимо сделать ссылку на ячейку абсолютной, предшествуя столбцам (B и C) и строке (2) знаком доллара($). Затем при копировании формулы =$B$4*$C$4 из D4 в D5 формула остается той же.

В меньшей степени может потребоваться смешанные абсолютные и относительные ссылки на ячейки, предшествуя столбецу или значению строки знаком доллара, что исправит столбец или строку (например, $B 4 или C$4).

Чтобы изменить тип ссылки на ячейку, выполните следующее.

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

Копируемая формула

Первоначальная ссылка

Новая ссылка

$A$1 (абсолютный столбец и абсолютная строка)

$A$1 (абсолютная ссылка)

A$1 (относительный столбец и абсолютная строка)

C$1 (смешанная ссылка)

$A1 (абсолютный столбец и относительная строка)

$A3 (смешанная ссылка)

A1 (относительный столбец и относительная строка)

C3 (относительная ссылка)

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

По умолчанию ссылка на ячейку является относительной. Например, если вы ссылаетесь на ячейку A2 из ячейки C2, вы указываете адрес ячейки в том же ряду (2), но отстоящей на два столбца влево (C минус A). Формула с относительной ссылкой изменяется при копировании из одной ячейки в другую. Например, вы можете скопировать формулу =A2+B2 из ячейки C2 в C3, при этом формула в ячейке C3 сдвинется вниз на один ряд и превратится в =A3+B3.

Если необходимо сохранить исходный вид ссылки на ячейку при копировании, ее можно зафиксировать, поставив перед названиями столбца и строки знак доллара ($). Например, при копировании формулы =$A$2+$B$2 из C2 в D2 формула не изменяется. Такие ссылки называются абсолютными.

В некоторых случаях ссылку можно сделать «смешанной», поставив знак доллара перед указателем столбца или строки для «блокировки» этих элементов (например, $A2 или B$3). Чтобы изменить тип ссылки на ячейку, выполните следующее.

1. Выделите ячейку со ссылкой на ячейку, которую нужно изменить.
2. В строка формул щелкните ссылку на ячейку, которую вы хотите изменить.
3. Для перемещения между сочетаниями используйте клавиши +T. В следующей таблице огововодятся сведения о том, что происходит при копировании формулы в ячейке A1, содержаной ссылку. В частности, формула копируется на две ячейки вниз и на две ячейки справа, в ячейку C3.

Текущая ссылка (описание):

Новая ссылка

$A$1 (абсолютный столбец и абсолютная строка)

$A$1 (абсолютная ссылка)

A$1 (относительный столбец и абсолютная строка)

C$1 (смешанная ссылка)

$A1 (абсолютный столбец и относительная строка)

$A3 (смешанная ссылка)

A1 (относительный столбец и относительная строка)

C3 (относительная ссылка)

Создание и изменение ссылки на ячейку

ссылка на ячейку указывает на ячейку или диапазон ячеек листа. Ссылки можно применять в формула, чтобы указать приложению Microsoft Office Excel на значения или данные, которые нужно использовать в формуле.

Ссылки на ячейки можно использовать в одной или нескольких формулах для указания на следующие элементы:

• данные из одной или нескольких смежных ячеек на листе;
• данные из разных областей листа;
• данные на других листах той же книги.

Объект ссылки

Возвращаемое значение

Значение в ячейке C2

Значения во всех ячейках, но после ввода формулы необходимо нажать сочетание клавиш Ctrl+Shift+Enter.

Примечание. Эта функция не работает в Excel в Интернете.

Ячейки с именами «Актив» и «Пассив»

Разность значений в ячейках «Актив» и «Пассив»

Диапазоны ячеек «Неделя1» и «Неделя2»

Сумма значений в диапазонах ячеек «Неделя1» и «Неделя2» как формула массива

Ячейка B2 на листе Лист2

Значение в ячейке B2 на листе Лист2

Создание ссылки на ячейку на том же листе

1. Щелкните ячейку, в которую нужно ввести формулу.
2. В строка формул введите = (знак равенства).
3. Выполните одно из следующих действий.
4. Создайте ссылку на одну или несколько ячеек. Чтобы создать ссылку, выделите ячейку или диапазон ячеек на том же листе. Можно переместить границу выделения, перетащив границу ячейки, или перетащить угол границы, чтобы расширить выделение.
5. Создайте ссылку на определенное имя. Чтобы создать ссылку на определенное имя, выполните одно из указанных ниже действий.
6. Введите имя.
7. Нажмите клавишу F3, выберите имя в поле Вставить имя и нажмите кнопку ОК.

Примечание: Если в углу цветной границы нет квадратного маркера, значит это ссылка на именованный диапазон.

Примечание: Если у вас установлена текущая версия Microsoft 365, можно просто ввести формулу в верхней левой ячейке диапазона вывода и нажать клавишу ВВОД, чтобы подтвердить использование формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.

Создание ссылки на ячейку на другом листе

На ячейки, расположенные на других листах в той же книге, можно сослаться, вставив перед ссылкой на ячейку имя листа с восклицательным знаком (!). В приведенном ниже примере функция СРЗНАЧ используется для расчета среднего значения в диапазоне B1:B10 на листе «Маркетинг» в той же книге.

1. Ссылка на лист «Маркетинг».

2. Ссылка на диапазон ячеек с B1 по B10 включительно.

3. Ссылка на лист, отделенная от ссылки на диапазон значений.

1. Щелкните ячейку, в которую нужно ввести формулу.
2. В строка формул введите = (знак равенства) и формулу, которую нужно использовать.
3. Щелкните ярлычок листа, на который нужно сослаться.
4. Выделите ячейку или диапазон ячеек, на которые нужно сослаться.

Примечание: Если имя другого листа содержит знаки, не являющиеся буквами, необходимо заключить имя (или путь) в одинарные кавычки (‘).

Создание ссылки на ячейку с помощью команды «Ссылки на ячейки»

Также можно скопировать и вставить ссылку на ячейку, а затем воспользоваться командой Ссылки на ячейки для создания ссылки на ячейку. Эту команду можно использовать в указанных ниже случаях.

• Для отображения важных данных в более заметном месте. Предположим, существует книга с множеством листов, на каждом из которых есть ячейка, отображающая итоговые данные по другим ячейкам этого листа. Чтобы выделить такие итоговые ячейки, можно создать ссылки на них с первого листа книги, которые позволят увидеть итоговые данные из всей книги на ее первом листе.
• Для упрощения ссылок на ячейки между листами и книгами. Команда Ссылки на ячейки автоматически вставляет выражения с правильным синтаксисом.

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

Изменение ссылки на ячейку на другую ссылку на ячейку

1. Дважды щелкните ячейку, содержащую формулу, которую нужно изменить. Каждая ячейка или диапазон ячеек в Excel, на которые ссылается формула, выделяются своим цветом.
2. Выполните одно из указанных ниже действий.
   • Чтобы переместить ссылку на ячейку или диапазон, перетащите цветную границу к новой ячейке или диапазону.
   • Чтобы изменить количество ячеек в диапазоне, перетащите угол границы.
   • В строка формул выделите ссылку в формуле и введите новую ссылку .
   • Нажмите клавишу F3, выберите имя в поле Вставить имя и нажмите кнопку ОК.
3. Нажмите клавишу ВВОД или, в случае формула массива, клавиши CTRL+SHIFT+ВВОД.

Примечание: Если у вас установлена текущая версия Microsoft 365, можно просто ввести формулу в верхней левой ячейке диапазона вывода и нажать клавишу ВВОД, чтобы подтвердить использование формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.

Изменение ссылки на ячейку на именованный диапазон

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

1. Выполните одно из указанных ниже действий.
   • Выделите диапазон ячеек, содержащих формулы, в которых необходимо заменить ссылки на ячейки определенными именами.
   • Чтобы заменить ссылки именами во всех формулах листа, выделите одну пустую ячейку.
2. На вкладке Формулы в группе Определенные имена щелкните стрелку рядом с кнопкой Присвоить имя и выберите команду Применить имена.
3. Выберите имена в поле Применить имена, а затем нажмите кнопку ОК.

Изменение типа ссылки: относительная, абсолютная, смешанная

1. Выделите ячейку с формулой.
2. В строке формул строка формул выделите ссылку, которую нужно изменить.
3. Для переключения между типами ссылок нажмите клавишу F4.

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

покупка

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

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

• Функция «Преобразовать ссылки» изменит все ссылки на ячейки в формуле.
• Для использования функции «Конвертировать ссылки» вам следует сначала установить Kutools for Excel. Нажмите, чтобы загрузить и получить 30-дневную бесплатную пробную версию.

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

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

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