Абсолютный, относительный и смешанный тип ссылок в Excel

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

Например, чтобы сложить число 1 (находящееся в ячейке A1) и число 2 (находящееся в ячейке B1) мы используем формулу =A1+B1, вместо того чтобы просто складывать сами значения. Это и есть ссылка.

Но какие бывают ссылки?

Всего в Excel есть 3 типа ссылок:

• Абсолютные
• Относительные
• Смешанные

Да-да, они бывают разные. А теперь давайте подробнее поговорим в чем же между ними разница.

Итак, начнём!

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

В этом случае, проще всего будет показать на примере.

Допустим, у нас есть такая табличка:

Например, нам нужно узнать сумму всех товаров. Для этого цена*кол-во.

Для товара А формула примет следующий вид:

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

Ссылки, которые Excel автоматически подстраивает под новые ячейки — относительные.

В каких ситуациях они лучше всего?

Эти ссылки лучше всего применять тогда, когда нужно скопировать функцию много раз. Так как в случае относительного типа ссылок — они подстраиваются автоматически, это выгодное использование, которое экономит ваше время.

Абсолютный тип ссылок

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

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

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

Значение, которое не должно изменяться при копировании и вставке формулы заключено в “$”.

Что этот «$» делает?

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

При копировании первая часть формулы (которая не заключена в «$» будет изменяться и «адаптироваться»), а вторая часть формулы (которая заключена в «$» изменяться не будет).

В каких ситуациях они лучше всего?

В таких, когда в вашей формуле есть какое-то значение, которое актуально для всех ячеек. Например, как в нашем случае, рассмотренном выше.  Это может быть полезно в совершенно разных ситуациях. При этом вы не хотите прописывать это значение вручную в формулу, потому что комиссия магазина, например, может в любой момент измениться. Тогда, если вы указали значение комиссии в формуле вручную, вам нужно будет менять её во всех формулах. В нашем же способе, вы просто измените его 1 раз в ячейке и все.

Смешанный тип ссылок

Тут будет немного сложнее чем ранее.

Смешанные ссылки делятся на 2 типа:

• При копировании формулы изменяется строка, а столбец остается статичным.
• И наоборот.

Покажу сразу на примере.

Допустим, у нас есть такая табличка и нам нужно посчитать комиссию.

Формула для расчета 10% примет такой вид:

= $ B4 * $ C4 * E $ 2

В этой формуле мы используем сразу оба варианта смешанного типа ссылок (вы можете понять это по знаку доллара, один блокирует строки, а второй столбики).

Как это работает:

• $B4*$C4 — в этом случае столбик, при копировании, изменяться не будет. Меняется только строка.
• E$2 — а здесь, при копировании, изменяться будет столбик, а строка останется прежней.

Как менять тип ссылок?

Наверное вы уже догадались, сделать это довольно просто.

Например, есть ссылка B2, сделаем её абсолютным типом добавив 2 доллара: $B$2.

Если ссылок мало, то поменять их не будет сложно. Но что делать если нужно поменять тип ссылок сразу на 100+ ячеек? Как это быстро сделать?

С помощью горячих клавиш, конечно же.

Давайте сразу рассмотрим пример.

Допустим, у вас есть много ссылок и вам нужно поменять их тип.

Пошаговая инструкция:

• Выделите диапазон ячеек и нажмите один раз на F4. Тип изменится на абсолютный;
• Если нажать F4 два раза, то тип станет смешанным, а неизменяемой, при копировании, переменной будет строка;
• Если нажать F4 три раза, то тип станет смешанным, а неизменяемой, при копировании, переменной будет столбик;
• Если нажать F4 четыре раза, то мы вернемся к относительному типу ссылки.