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

Содержание

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

Excel для Microsoft 365 для Mac Excel 2021 для Mac Excel 2019 для Mac Excel 2016 для Mac Excel для Mac 2011 Еще…Меньше

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

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

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

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

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

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

Текущая ссылка (описание):	Новая ссылка
$A$1 (абсолютный столбец и абсолютная строка)	$A$1 (абсолютная ссылка)
A$1 (относительный столбец и абсолютная строка)	C$1 (смешанная ссылка)
$A1 (абсолютный столбец и относительная строка)	$A3 (смешанная ссылка)
A1 (относительный столбец и относительная строка)	C3 (относительная ссылка)

Абсолютная ссылка в Excel фиксирует ячейку в формуле

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

В Excel существует несколько типов ссылок: абсолютные, относительные и смешанные. Сюда так же относятся «имена» на целые диапазоны ячеек. Рассмотрим их возможности и отличия при практическом применении в формулах.

Абсолютные и относительные ссылки в Excel

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

Заполните диапазон ячеек A2:A5 разными показателями радиусов.
В ячейку B2 введите формулу вычисления объема сферы, которая будет ссылаться на значение A2. Формула будет выглядеть следующим образом: =(4/3)*3,14*A2^3

Скопируйте формулу из B2 вдоль колонки A2:A5.

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

Так же стоит отметить закономерность изменения ссылок в формулах. Данные в B3 ссылаются на A3, B4 на A4 и т.д. Все зависит од того куда будет ссылаться первая введенная формула, а ее копии будут изменять ссылки относительно своего положения в диапазоне ячеек на листе.

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

Заполните табличку, так как показано на рисунке:

Описание исходной таблицы. В ячейке A2 находиться актуальный курс евро по отношению к доллару на сегодня. В диапазоне ячеек B2:B4 находятся суммы в долларах. В диапазоне C2:C4 будут находится суммы в евро после конвертации валют. Завтра курс измениться и задача таблички автоматически пересчитать диапазон C2:C4 в зависимости от изменения значения в ячейке A2 (то есть курса евро).

Для решения данной задачи нам нужно ввести формулу в C2: =B2/A2 и скопировать ее во все ячейки диапазона C2:C4. Но здесь возникает проблема. Из предыдущего примера мы знаем, что при копировании относительные ссылки автоматически меняют адреса относительно своего положения. Поэтому возникнет ошибка:

Относительно первого аргумента нас это вполне устраивает. Ведь формула автоматически ссылается на новое значение в столбце ячеек таблицы (суммы в долларах). А вот второй показатель нам нужно зафиксировать на адресе A2. Соответственно нужно менять в формуле относительную ссылку на абсолютную.

Как сделать абсолютную ссылку в Excel? Очень просто нужно поставить символ $ (доллар) перед номером строки или колонки. Или перед тем и тем. Ниже рассмотрим все 3 варианта и определим их отличия.

Наша новая формула должна содержать сразу 2 типа ссылок: абсолютные и относительные.

В C2 введите уже другую формулу: =B2/A$2. Чтобы изменить ссылки в Excel сделайте двойной щелчок левой кнопкой мышки по ячейке или нажмите клавишу F2 на клавиатуре.
Скопируйте ее в остальные ячейки диапазона C3:C4.

Описание новой формулы. Символ доллара ($) в адресе ссылок фиксирует адрес в новых скопированных формулах.

Абсолютные, относительные и смешанные ссылки в Excel:

$A$2 – адрес абсолютной ссылки с фиксацией по колонкам и строкам, как по вертикали, так и по горизонтали.
$A2 – смешанная ссылка. При копировании фиксируется колонка, а строка изменяется.
A$2 – смешанная ссылка. При копировании фиксируется строка, а колонка изменяется.

Для сравнения: A2 – это адрес относительный, без фиксации. Во время копирования формул строка (2) и столбец (A) автоматически изменяются на новые адреса относительно расположения скопированной формулы, как по вертикали, так и по горизонтали.

Примечание. В данном примере формула может содержать не только смешанную ссылку, но и абсолютную: =B2/$A$2 результат будет одинаковый. Но в практике часто возникают случаи, когда без смешанных ссылок не обойтись.

Полезный совет. Чтобы не вводить символ доллара ($) вручную, после указания адреса периодически нажимайте клавишу F4 для выбора нужного типа: абсолютный или смешанный. Это быстро и удобно.

все уроки

Относительные и абсолютные ссылки на ячейки

Урок 4: Относительные и абсолютные ссылки на ячейки

/en/excelformulas/complex-formulas/content/

Введение

Существует два типа ссылок на ячейки: относительная и 9 абсолютная . Относительные и абсолютные ссылки ведут себя по-разному при копировании и заполнении других ячеек. Относительные ссылки изменяются на , когда формула копируется в другую ячейку. Абсолютные ссылки, с другой стороны, остаются константа независимо от того, куда они копируются.

Необязательно: Загрузите наш пример файла для этого урока.

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

Относительные ссылки

По умолчанию все ссылки на ячейки являются относительными ссылками . При копировании в несколько ячеек они изменяются в зависимости от относительного положения строк и столбцов. Например, если вы скопируете формулу =A1+B1 из строки 1 в строку 2, формула станет равной 9.0009 =А2+В2. Относительные ссылки особенно удобны, когда вам нужно повторить одно и то же вычисление в нескольких строках или столбцах.

Чтобы создать и скопировать формулу с использованием относительных ссылок:

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

Выберите ячейку , которая будет содержать формулу. В нашем примере мы выберем ячейку D2 .
Введите формулу для расчета требуемого значения. В нашем примере мы введем =B2*C2 .
Нажмите Введите на клавиатуре. Формула будет рассчитана , а результат будет отображаться в ячейке.
Найдите маркер заполнения в правом нижнем углу нужной ячейки. В нашем примере мы найдем дескриптор заполнения для ячейки 9.0009 Д2 .
Нажмите, удерживайте и перетащите маркер заполнения по ячейкам, которые вы хотите заполнить. В нашем примере мы выберем ячейки D3:D12 .
Отпустите кнопку мыши. Формула будет скопирована в выбранные ячейки с относительными ссылками , и значения будут рассчитаны в каждой ячейке.

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

Абсолютные ссылки

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

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

В большинстве формул вы будете использовать относительный ( A2 ) и абсолютный ( $A$2 ) форматы. Смешанные ссылки используются реже.

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

Чтобы создать и скопировать формулу с использованием абсолютных ссылок:

В нашем примере мы будем использовать 7,5% ставку налога с продаж в ячейке E1 для расчета налога с продаж для всех товаров в столбце D . Нам нужно будет использовать абсолютную ссылку на ячейку $E$1 в нашей формуле. Поскольку в каждой формуле используется одна и та же налоговая ставка, мы хотим, чтобы эта ссылка оставалась постоянной, когда формула копируется и заполняется другими ячейками в столбце D.

Выберите ячейку , которая будет содержать формулу. В нашем примере мы выберем ячейку Д3 .
Введите формулу для расчета требуемого значения. В нашем примере мы введем = (B3*C3)*$E$1 .
Нажмите Введите на клавиатуре. Формула рассчитает, и результат отобразится в ячейке.
Найдите маркер заполнения в правом нижнем углу нужной ячейки. В нашем примере мы найдем маркер заполнения для ячейки D3 .
Нажмите, удерживайте и перетащите маркер заполнения над ячейками, которые вы хотите заполнить, ячейки D4:D13 в нашем примере.
Отпустите кнопку мыши. Формула будет скопирована в выбранные ячейки с абсолютной ссылкой , и значения будут рассчитаны в каждой ячейке.

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

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

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

Большинство программ для работы с электронными таблицами позволяют ссылаться на любую ячейку на любом рабочем листе , что может быть особенно полезно, если вы хотите сослаться на конкретное значение с одного рабочего листа на другой. Для этого вам просто нужно начать ссылку на ячейку с 9Рабочий лист 0009 имя , за которым следует восклицательный знак точка ( ! ). Например, если вы хотите сослаться на ячейку A1 на Sheet1 , ее ссылкой на ячейку будет Sheet1!A1 .

Обратите внимание, что если имя рабочего листа содержит пробел , вам нужно будет заключить одинарных кавычек ( ‘ ‘ ) вокруг имени. Например, если вы хотите сослаться на ячейку A1 на листе с именем Бюджет на июль ссылка на его ячейку будет ‘Бюджет на июль’!A1 .

Для ссылки на ячейки между рабочими листами:

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

Найдите ячейку, на которую вы хотите сослаться, и обратите внимание на ее рабочий лист. В нашем примере мы хотим сослаться на ячейку 9.0009 E14 на рабочем листе заказа меню .
Перейдите к нужному рабочему листу . В нашем примере мы выберем рабочий лист Catering Invoice .
Появится выбранный рабочий лист .
Найдите и выберите ячейку , в которой должно отображаться значение. В нашем примере мы выберем ячейку B2 .
Введите знак равенства (=) , лист имя , за которым следует цифра 9.0009 восклицательный знак ( ! ) и адрес ячейки . В нашем примере мы введем =’Порядок меню’!E14 .
Нажмите Введите на клавиатуре. Появится значение указанной ячейки. Если значение ячейки E14 изменится на рабочем листе заказа меню, оно будет автоматически обновлено на рабочем листе счета за питание.

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

Вызов!

Откройте существующую книгу Excel. Если вы хотите, вы можете использовать файл примера для этого урока.
Создайте формулу, использующую относительную ссылку . Если вы используете пример, используйте маркер заполнения , чтобы заполнить формулу в ячейках с E4 по E14 . Дважды щелкните ячейку, чтобы увидеть скопированную формулу и относительные ссылки на ячейки.
Создайте формулу, использующую абсолютное значение ссылка . Если вы используете пример, исправьте формулу в ячейке D4 , чтобы она ссылалась только на налоговую ставку в ячейке E2 как на абсолютную ссылку , затем используйте дескриптор заполнения, чтобы заполнить формулу из ячеек D4 в Д14 .
Попробуйте сослаться на ячейку на рабочих листах . Если вы используете пример, создайте ссылку на ячейку в ячейке B3 на листе Catering Invoice для ячейки 9.0009 E15 на рабочем листе Menu Order .

Продолжать

Предыдущий: Сложные формулы

Далее:Функции

/en/excelformulas/functions/content/