Microsoft Excel

Excel 2016 – Урок 15. Относительные и абсолютные ссылки на ячейки

Существует два типа ссылок на ячейки: относительные и абсолютные. Присоединяйтесь к LuckyTemplates в этой статье с инструкциями по относительным и абсолютным ссылкам на ячейки в Excel 2016!

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

Присоединяйтесь к LuckyTemplates в этой статье с инструкциями по относительным и абсолютным ссылкам на ячейки в Excel 2016 !

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

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

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

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

Создайте и скопируйте формулу, использующую относительные ссылки.

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

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

Excel 2016 – Урок 15. Относительные и абсолютные ссылки на ячейки

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

Excel 2016 – Урок 15. Относительные и абсолютные ссылки на ячейки

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

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

Excel 2016 – Урок 15. Относительные и абсолютные ссылки на ячейки

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

Excel 2016 – Урок 15. Относительные и абсолютные ссылки на ячейки

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

Excel 2016 – Урок 15. Относительные и абсолютные ссылки на ячейки

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

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

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

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

Excel 2016 – Урок 15. Относительные и абсолютные ссылки на ячейки

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

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

Создание и копирование формулы по абсолютной ссылке

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

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

Excel 2016 – Урок 15. Относительные и абсолютные ссылки на ячейки

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

Excel 2016 – Урок 15. Относительные и абсолютные ссылки на ячейки

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

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

Excel 2016 – Урок 15. Относительные и абсолютные ссылки на ячейки

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

Excel 2016 – Урок 15. Относительные и абсолютные ссылки на ячейки

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

Excel 2016 – Урок 15. Относительные и абсолютные ссылки на ячейки

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

Excel 2016 – Урок 15. Относительные и абсолютные ссылки на ячейки

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

Excel 2016 – Урок 15. Относительные и абсолютные ссылки на ячейки

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

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

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

Справочные ячейки на листах

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

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

Excel 2016 – Урок 15. Относительные и абсолютные ссылки на ячейки

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

Excel 2016 – Урок 15. Относительные и абсолютные ссылки на ячейки

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

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

Excel 2016 – Урок 15. Относительные и абсолютные ссылки на ячейки

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

Excel 2016 – Урок 15. Относительные и абсолютные ссылки на ячейки

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

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

Excel 2016 – Урок 15. Относительные и абсолютные ссылки на ячейки

Обратитесь к еще нескольким статьям:

Веселиться!

More in Microsoft Excel