Excel 2019 (часть 14): относительные и абсолютные ссылки на ячейки

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

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

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

Как создавать и копировать формулы, используя относительные ссылки

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

1. Выберите ячейку, которая будет содержать формулу. Например, мы выберем ячейку D4.

2. Введите формулу для расчета желаемого значения. Например, введите =B4*C4.

3. Нажмите Enter на клавиатуре. Формула будет рассчитана, и результаты отобразятся в ячейке.

4. Найдите маркер заполнения в правом нижнем углу нужной ячейки. В примере будет определена позиция маркера заполнения для ячейки D4.

5. Нажмите и перетащите маркер заполнения в ячейки, которые хотите заполнить. Например, мы выберем ячейки D5:D13.

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

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

Абсолютная ссылка

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

Абсолютная ссылка указывается в формуле путем добавления знака доллара ( $ ). Этот символ может предшествовать ссылке на столбец, ссылке на строку или тому и другому.

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

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

Как создавать и копировать формулы, используя абсолютные ссылки

В приведенном ниже примере будет использоваться ячейка E2 (которая содержит ставку налога 7,5%) для расчета налога с продаж для каждого товара в столбце D. Чтобы ссылка на ставку налога оставалась неизменной (даже если формула копируется и заполняется в других ячейках), ячейку $E$2 необходимо установить как абсолютную ссылку.

1. Выберите ячейку, которая будет содержать формулу. Например, мы выберем ячейку D4.

2. Введите формулу для расчета желаемого значения. В примере будет введено =(B4*C4)*$E$2 , что сделает $E$2 абсолютной ссылкой.

3. Нажмите Enter на клавиатуре. Формула выполнит расчет, и результат отобразится в ячейке.

4. Найдите маркер заполнения в правом нижнем углу нужной ячейки. В примере будет определена позиция маркера заполнения для ячейки D4.

5. Нажмите и перетащите маркер заполнения к ячейкам, которые хотите заполнить (ячейки D5: D13 в примере).

6. Отпустите мышь. Формула будет скопирована в выбранные ячейки с абсолютными ссылками и значения будут рассчитаны в каждой ячейке.

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

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

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

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

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

Как ссылаться на ячейки на листах

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

1. Найдите ячейку, на которую хотите сослаться, и запишите ее лист. Например, мы хотим сослаться на ячейку E14 на листе «Порядок меню» .

2. Перейдите к нужному листу. Например, мы выберем таблицу счетов за питание .

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

4. Введите знак равенства ( = ), имя листа, затем восклицательный знак (!) и адрес ячейки. Например, введите ='Порядок меню'!E14.

5. Нажмите Enter на клавиатуре. Появится значение указанной ячейки. Теперь, если значение ячейки E14 изменится на листе «Заказ меню» , оно будет автоматически обновлено на листе «Счет за питание».

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

Если вы введете неправильное имя листа, ошибка #REF! в ячейке появится ошибка. В примере ниже имя листа написано с ошибкой. Чтобы отредактировать, игнорировать или проверить наличие ошибок, нажмите кнопку «Ошибка» рядом с ячейкой и выберите вариант в меню.