Как изменяются абсолютные ссылки при перемещении ячеек входящих в формулу: Относительные, абсолютные и смешанные ссылки — урок. Информатика, 9 класс.

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

Excel для Microsoft 365 для Mac Excel 2021 for 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).

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

1. Выделите ячейку со ссылкой на ячейку, которую нужно изменить.

2. В строка формул щелкните ссылку на ячейку, которую вы хотите изменить.

3. Для перемещения между сочетаниями используйте клавиши +T.

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

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

—

MS Office 2007: Microsoft Excel

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

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

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

• Относительные ссылки. При копировании формул эти ссылки автоматически изменяются в соответствии с новым положением формулы.
• Абсолютные ссылки. Эти ссылки не изменяются при копировании формул.
• Смешанные ссылки. В этих ссылках номер строки (или столбца) является абсолютным, а столбца (строки) — относительным.

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

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

В Excel также допускаются смешанные ссылки, в которых только одна часть адреса является абсолютной (например, $А4 или А$4). В этом случае клавишу F4 необходимо нажать два или три раза (соответственно А$4 или $А4). Четвертое нажатие F4 возвращает к относительной ссылке. Например, если необходимо поставить какую-либо ссылку на А1, то первое нажатие клавиши F4 преобразует ссылку на ячейку в $А$ 1, второе — в А$1, третье — в $А1, а четвертое вернет ей первоначальный вид — А1. Нажимайте клавишу F4 до тех пор, пока не появится нужный тип ссылки.

Различие между разными типами ссылок проявляется при копировании формул. 

На рис.30 показана таблица, в ячейке D2 которой находится формула умножения количества наименований товара на его цену. Формула выглядит следующим образом: =В2*С2.

Если ее скопировать маркером заполнения на ячейки D3 и D4, то получим изображенную на рисунке таблицу. Поскольку в этой формуле используются относительные ссылки, то при копировании формулы в ячейки D3 и D4 они соответствующим образом изменятся, то есть в ячейке D3 получим формулу: =ВЗ*СЗ, а в ячейке D4 соответственно =В4*С4.

Если в ячейке D2 заменить относительные ссылки абсолютными, то получим =$В$2*$С$2.

Если теперь скопировать эту формулу в ячейку D3, то получим неправильный результат. Формулы в ячейках D3 и D2 будут одинаковыми.

Теперь изменим этот пример и подсчитаем комиссионные. Значение процентной ставки комиссионных хранится в ячейке в 7 (рис.31). Перенесем заголовок Всего на одну ячейку вправо, а в D1 впишем =А7.

В результате в ячейке D1 получим Комиссионные. В ячейку D2 введем формулу =В2*С2*$В$7. Количество умножается на цену, а затем результат умножается на процентную ставку комиссионных, значение которой хранится в ячейке В7.

Обратите внимание на то, что ссылка на ячейку В7 является абсолютной. Скопировав ячейку D2 в D3, получим =В3*С3*$В$7. Ссылки на ячейки В2 и С2 изменились, а ссылка на ячейку В7 — нет, т.е. мы получили правильный результат.

На рис.32 показана таблица, в которой используются смешанные ссылки. В левом столбце хранится значение длины прямоугольника, а в верхней строке находится ширина. В остальных ячейках вычисляется площадь прямоугольника соответственно данной длине и ширине. Например, в ячейке D5 вычисляется площадь прямоугольника, длина которого — 2, а ширина — 1,5. Для вычисления площади в ячейку С3 вводится формула = $В3*С$2.

Обратите внимание на то, что в формуле используются смешанные ссылки. В ссылке на ячейку В3 абсолютной является ссылка на столбец ($В), а в ссылке на ячейку С2 используется абсолютная ссылка на строку ($2). Скопировав эту формулу во все ячейки диапазона, мы получим правильный результат вычислений. Например, в ячейке F7 будет содержаться такая формула =$B7*F$2.

При использовании в ячейке С3 абсолютных или относительных ссылок результат окажется неверным.

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

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

/en/excelformulas/complex-formulas/content/

Введение

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Вызов!

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

Продолжать

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

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

/en/excelformulas/functions/content/

Переключение между относительными и абсолютными ссылками

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

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

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

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

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

2. В строке формул щелкните ссылку на ячейку, которую вы хотите изменить.