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

Ссылка в Экселе это имя ячейки, которое мы можем внести в формулу для расчета. Например, B1- это первая строка 2 столбец, и если в другой ячейке мы в формулу вставим B1 – это будет ссылка на ячейку. Таким образом, мы указываем Экселю с какой ячейки ему нужно взять число для расчета. Существует 2 вида ссылок – абсолютные и относительные.

 

Создание и работа с абсолютными и относительными ссылками в excel 

 

Относительная ссылка изменяется в след за копированием формулы. Например, есть у нас данные для расчета суммы по депозитам.
Изменение относительной ссылки в excel

 

 

В столбцах Доход от основного процента, Доход от  депозитного процента и Доход от  долгосрочного процента необходимо рассчитать значения, используя формулу: Сумма вклада умножить на соответственно Основной, Депозитный и Долгосрочный процент. Для расчета дохода от основного процента введем формулу: =D8*5%, т.е. сумма вклада * 5% (основной процент). С помощью автозаполнения копируем формулу на ячейки ниже D8.

 

Как скопировать формулу на другие ячейки смотрите в уроке КАК РАСТЯНУТЬ ФОРМУЛУ НА НЕСКОЛЬКО ЯЧЕЕК.

Изменение относительной ссылки в excel

 

Заметим, что в выделенной ячейке Е9 формула несколько изменилась, вместоD8 стоит ужеD9. И все логично, поскольку для расчет а Дохода от основного процента для клиента Буковка, нам нужна в формуле именно его сумма вклада, а не клиента Анисимова. Также и происходит с расчетом для всех других клиентов. СсылкаD8 является относительной, поскольку с опусканием формулы на 1 шаг, также на 1 шаг изменяется и ссылка в формуле.

 

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

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

 

Мы ввели формулу и вроде все правильно. Для 1 человека взяты правильные данные для расчета и нам выдаст верный результат. Однако, мы уже знаем, что прокопировав формулу вниз, ссылки изменятся. Для клиента Буковка ячейки в формуле сместятся на 1 шаг вниз и Эксель возьмет для расчета Сумму вклада Буковки (ячейкаD9) и не ячейку Е4 с 12%, а ячейку на шаг ниже, т.е. ячейку Е5 с 7%. Еще ниже, для клиента Волчанская, будет взята ее сумма вклада (D10) и ячейка, Е6, где пусто. Тут мы понимаем, что с суммой вклада все в порядке, а с процентом что-то не так. Чтобы закрепить ячейку Е4 с 12%, чтобы она никуда не смещалась вслед за копированием формулы, необходимо поставить курсор возле ячейки Е4 и нажатьF4 на клавиатуре. В итоге возле ячейки Е4 должны появиться значки доллара.

Отключение изменений при перемещении ячейки

 

Это означает, что ячейка по строке и по столбцу закреплена и никуда не будет двигаться. Также можно не пользоваться кнопкойF4, а ввести с клавиатуры 2 раза значок доллара, использовав комбинацию клавиш в английской раскладке [Shift+4]. Результат будет тот же. Теперь скопировав формулу на ячейки ниже Е8, мы увидим верный результат.

Отключение изменений при перемещении ячейки

 

Заметим, что для Волчанской в формуле берется сумма вклада этого клиента, ячейкаD10, а ячейка Е4 не изменяется. Так вот, закрепленная ссылка на ячейку является абсолютной.

 

Теперь если у нас изменится Основной процент, и станет равным, например, 7% — нам нужно будет зайти в формулу для расчета  Дохода от основного процента, изменить там цифру и скопировать формулу на всех остальных клиентов. И это при том, что мы использовали информацию  об Основном проценте только в 1 месте. Если таких мест будет много – придется менять цифру везде. Для Депозитного процента не нужно всего этого делать. Достаточно в ячейке Е4, где находится депозитный процент, поставить нужную цифру. Таким образом везде, где для расчетов использовалась ячейка Е4 будет сделан перерасчет и результаты вычислений изменятся.

 

Итоги. Если ссылка в формуле изменяется вслед за копированием формулы – это относительная ссылка. Если ссылка остается неизменной – абсолютная.

 

Скачать пример из урока: absolute_and_relative_references_in_excel

 

comments powered by HyperComments