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

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

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

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

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

На примере ниже мы хотим создать формулу, которая поможет в расчетах итогового счета за заказ в ресторане. Мы хотим в колонке “Итог” рассчитать сумму к оплате за каждое блюдо, в зависимости от его стоимости за единицу и количества. Для этого, мы создадим одну формулу с относительной ссылкой и скопируем ее на все строки таблицы. Для этого проделаем следующие шаги:

  • Выделим первую ячейку, в столбце “Итог” в которой будет создана наша формула:

относительные ссылки в excel - 1

  • Вставим в ячейку D2 формулу, которая перемножает цену за блюдо и количество: =B2*C2.
  • Нажмите клавишу “Enter” на клавиатуре. Формула произведет расчет и его результат вы увидите в ячейке D2.

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

  • Зажав левую клавишу мыши, протяните ячейку D2 за правый нижний угол по всему диапазону ячеек D3:D12. Таким образом, вы скопируете формулу из ячейки D2 и перенесете ее на каждую ячейку диапазона.

относительные ссылки в excel - 3

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

относительные ссылки в excel - 4

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

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

Для создания абсолютной ссылки используется знак доллара “$”. С его помощью вы можете зафиксировать от изменений столбец, строку или всех вместе:

  • $A$2 – столбец и строка не изменяются при копировании формулы;

  • A$2 – при копировании формулы не меняется только строка;

  • $A2 – столбец не изменяется при копировании формулы.

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

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

В нашем примере мы будем использовать в ячейке E1 – 18% как значение НДС для расчета налога на товары в колонке D. Для правильного расчета нам потребуется использовать абсолютную ссылку $E$1 в нашей формуле, так как нам важно, чтобы  стоимость каждого товара перемножалась на ставку НДС, указанную в ячейке E1. Ниже рассмотрим как мы будем это делать:

  • Выделим ячейку, в которую мы хотим вставить формулу для расчета налога. В нашем примере это ячейка D3.

абсолютные ссылки в excel - 1

  • Напишем формулу, рассчитывающую сумму налога для каждого товара, с учетом его стоимости и количества =(B3*C3)*$E$1.

абсолютные ссылки в excel - 21

  • Протянем полученную формулу на все ячейки в диапазоне D4:D13.

абсолютные ссылки в excel - 3

  • Дважды кликните на любой ячейке из диапазона D4:D13 и убедитесь, что формула сработала корректно. Важно убедиться, что вы правильно указали ссылку на ячейку $E$1 в абсолютном формате.

Как создать ссылки на другие листы в Excel

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

=Sheet1!A1

ВАЖНО! Если в название листа, на ячейку с которого вы ссылаетесь есть пробелы, то название этого листа в ссылке должно быть заключено в кавычки (‘ ‘). Например, если название вашего листа Бюджет Финал, то ссылка на ячейку A1 будет выглядеть так:

‘Бюджет Финал’!A1

На примере ниже, мы хотим добавить в таблицу ссылку на ячейку, в которой уже произведены вычисления между двумя листами Excel файла. Это позволит нам использовать одно и то же значение на двух разных листах без перезаписи формулы или копирования данных между рабочими листами. Для этого проделаем следующие шаги:

  • Выберем ячейку, на которую мы хотим сослаться и обратим внимание на название листа. В нашем случае это ячейка E14 на вкладке “Меню”:

Ссылки между листами в Excel - 11

  • Перейдем на лист и выберем ячейку, в которой мы хотим поставить ссылку. В нашем примере это ячейка B2.

Ссылки между листами в Excel - 22

  • В ячейке B2 введем формулу, ссылающуюся на ячейку E14 с листа “Меню”: =Меню!E14
  • Нажмем клавишу “Enter” на клавиатуре и увидим в ячейке B2 значение ячейки E14 с листа “Меню”.

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

 

 

comments powered by HyperComments
Понравилась статья? Поделиться с друзьями: