Использование относительных и абсолютных ссылок
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 (смешанная ссылка) |
|
$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
Предыдущий: Сложные формулы
Далее:Функции
/en/excelformulas/functions/content/
Относительные и абсолютные ссылки на ячейки
Урок 15: Относительные и абсолютные ссылки на ячейки
/en/excel/creating-more-complex-formulas/content/
Введение
Существует два типа ссылок на ячейки: относительная и абсолютная . Относительные и абсолютные ссылки ведут себя по-разному при копировании и заполнении других ячеек. Относительные ссылки изменяются на , когда формула копируется в другую ячейку. Абсолютные ссылки, с другой стороны, остаются постоянными независимо от того, куда они копируются.
Дополнительно: загрузите нашу рабочую тетрадь.
Посмотрите видео ниже, чтобы узнать больше о ссылках на ячейки.
Относительные ссылки
По умолчанию все ссылки на ячейки являются относительными ссылками . При копировании в несколько ячеек они изменяются в зависимости от относительного положения строк и столбцов. Например, если скопировать формулу =A1+B1 из строки 1 в строку 2, формула примет вид =A2+B2 . Относительные ссылки особенно удобны, когда вам нужно повторить одни и те же вычисления в нескольких строках или столбцах.
Чтобы создать и скопировать формулу с использованием относительных ссылок:
В следующем примере мы хотим создать формулу, которая будет умножать цену каждого товара на количество . Вместо создания новой формулы для каждой строки мы можем создать одну формулу в ячейке D4 , а затем скопировать ее в другие строки. Мы будем использовать относительные ссылки, чтобы формула правильно вычисляла сумму для каждого элемента.
- Выберите ячейку , которая будет содержать формулу. В нашем примере мы выберем ячейку D4 9.0010 .
- Введите формулу для расчета требуемого значения. В нашем примере мы введем =B4*C4 .
- Нажмите Введите на клавиатуре. Формула будет рассчитана, а результат отобразится в ячейке.
- Найдите маркер заполнения в правом нижнем углу нужной ячейки. В нашем примере мы найдем маркер заполнения для ячейки D4 .
- Нажмите и перетащите маркер заполнения на ячейки, которые вы хотите заполнить. В нашем примере мы выберем ячейки Д5:Д13 .
- Отпустите кнопку мыши. Формула будет скопирована в выбранные ячейки с относительными ссылками , отображая результат в каждой ячейке.
Вы можете дважды щелкнуть заполненных ячеек , чтобы проверить их формулы на точность. Относительные ссылки на ячейки должны быть разными для каждой ячейки, в зависимости от их строк.
Абсолютные ссылки
Может быть время, когда вы не хотите, чтобы ссылка на ячейку менялась при копировании в другие ячейки. В отличие от относительных ссылок, абсолютные ссылки не изменяются при копировании или заполнении. Вы можете использовать абсолютную ссылку, чтобы сохранить строку и/или столбец постоянным .
Абсолютная ссылка в формуле обозначается добавлением знака доллара ($) . Может предшествовать ссылке на столбец, ссылку на строку или и то, и другое.
При создании формул, содержащих абсолютные ссылки, обычно используется формат $A$2 . Два других формата используются гораздо реже.
При написании формулы вы можете нажать клавишу F4 на клавиатуре, чтобы переключаться между относительными и абсолютными ссылками на ячейки, как показано в видео ниже. Это простой способ быстро вставить абсолютную ссылку.
Чтобы создать и скопировать формулу с использованием абсолютных ссылок:
В приведенном ниже примере мы будем использовать ячейку E2 (которая содержит налоговую ставку 7,5%) для расчета налога с продаж для каждого элемента в столбец D . Чтобы убедиться, что ссылка на налоговую ставку остается постоянной — даже когда формула копируется и заполняется в другие ячейки — нам нужно сделать ячейку $E$2 абсолютная ссылка.
- Выберите ячейку , которая будет содержать формулу. В нашем примере мы выберем ячейку D4 .
- Введите формулу для расчета требуемого значения. В нашем примере мы введем = (B4*C4)*$E$2 , что сделает $E$2 абсолютной ссылкой.
- Нажмите Введите на клавиатуре. Формула рассчитает, и результат отобразится в ячейке.
- Найдите заполните маркер в правом нижнем углу нужной ячейки. В нашем примере мы найдем маркер заполнения для ячейки D4 .
- Нажмите и перетащите маркер заполнения на ячейки, которые вы хотите заполнить (ячейки D5:D13 в нашем примере).
- Отпустите кнопку мыши. Формула будет скопирована в выбранные ячейки с абсолютной ссылкой , и значения будут рассчитаны в каждой ячейке.
Вы можете дважды щелкнуть заполнили ячейки , чтобы проверить их формулы на точность. Абсолютная ссылка должна быть одинаковой для каждой ячейки, тогда как другие ссылки относятся к строке ячейки.
Не забудьте включить знак доллара ($) всякий раз, когда вы делаете абсолютную ссылку на несколько ячеек. В приведенном ниже примере знаки доллара были опущены. Это заставило Excel интерпретировать его как относительную ссылку , что дало неверный результат при копировании в другие ячейки.
Использование ссылок на ячейки с несколькими рабочими листами
Excel позволяет ссылаться на любую ячейку на любом рабочем листе , что может быть особенно полезно, если вы хотите сослаться на определенное значение с одного рабочего листа на другой. Для этого вам просто нужно начать ссылку на ячейку с рабочего листа имени , за которым следует восклицательный знак точка (!) . Например, если вы хотите сослаться на ячейку A1 на Sheet1 , его ссылка на ячейку будет Sheet1!A1 .
Обратите внимание: если имя рабочего листа содержит пробел , вам нужно будет заключить одинарных кавычек (‘ ‘) вокруг имени. Например, если вы хотите сослаться на ячейку A1 на листе с именем Бюджет на июль , ссылка на эту ячейку будет ‘Бюджет на июль’!A1 .
Для ссылки на ячейки между рабочими листами:
В нашем примере ниже мы будем ссылаться на ячейку с вычисленным значением между двумя рабочими листами. Это позволит нам использовать точно такое же значение на двух разных листах без перезаписи формулы или копирования данных.
- Найдите ячейку, на которую вы хотите сослаться, и обратите внимание на ее рабочий лист. В нашем примере мы хотим сослаться на ячейку E14 на листе Порядок меню .
- Перейдите к нужному рабочему листу . В нашем примере мы выберем рабочий лист Catering Invoice .
- Найдите и выберите ячейку , в которой должно отображаться значение. В нашем примере мы выберем ячейку С4 .
- Введите знак равенства (=) , лист имя , затем восклицательный знак (!) и адрес ячейки . В нашем примере мы введем =’Порядок меню’!E14 .
- Нажмите Введите на клавиатуре. Появится значение указанной ячейки. Теперь, если значение ячейки E14 изменится на рабочем листе «Заказ меню», оно будет автоматически обновлено на на рабочем листе «Счет-фактура общественного питания».
Если вы переименуете свой рабочий лист позже, ссылка на ячейку будет автоматически обновлена, чтобы отразить новое имя рабочего листа.
Если вы введете имя листа неправильно, #REF! В ячейке появится ошибка . В нашем примере ниже мы опечатались в имени рабочего листа. Чтобы отредактировать, проигнорировать или исследовать ошибку, нажмите кнопку Ошибка рядом с ячейкой и выберите параметр в меню .
Вызов!
- Откройте нашу рабочую тетрадь.
- Щелкните вкладку Paper Goods в левом нижнем углу рабочей книги.
- В ячейку D4 введите формулу, умножающую цену за единицу в поле B4 , количество в поле C4 и налоговую ставку в поле E2 . Обязательно используйте абсолютную ссылку на ячейку для налоговой ставки, потому что она будет одинаковой во всех ячейках.
- Используйте ручку заполнения , чтобы скопировать только что созданную формулу в ячейки D5:D12 .
- Измените налоговую ставку в ячейке E2 на 6,5%. Обратите внимание, что все ваши ячейки обновлены. Когда вы закончите, ваша книга должна выглядеть так:
- Щелкните вкладку Счет-фактура за питание .
- Удалите значение в ячейке C5 и замените его ссылкой на общую стоимость бумажных товаров.