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

• Excel 2016 (часть 12): форматирование страниц и печать электронных таблиц
• Excel 2016 (часть 13): Знакомство с формулами
• Excel 2016 (часть 14): создание сложных формул

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

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

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

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

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

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

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

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

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

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 на Листе Sheet1 , ее ссылка на ячейку будет Sheet1!A1 .

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

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

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

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

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

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

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

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

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

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

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

• Excel 2016 (часть 3): как создавать новые и открывать существующие электронные таблицы
• Excel 2016 (часть 4): как хранить электронные таблицы и делиться ими
• Excel 2016 (часть 5): основы работы с ячейками и диапазонами

Веселиться!