Microsoft Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

More in Microsoft Excel