Excel: Абсолютные и относительные ссылки. Таблица Пифагора

Сегодняшняя статья для новичков. Помнится, не так давно на глаза попалась «экселевская» шутка, про то, что если ты не знаешь, чем $A$1 отличается от A1, то нам не о чем с тобой разговаривать. Но нам всегда есть о чём поговорить с нашими читателями и тем более о таком базовом вопросе, как абсолютные и относительные ссылки в Excel.

Итак, чем же эти ссылки отличаются одна от другой? Относительная ссылка в формуле вида A1 – это адресация на ячейку, которая удалена от ячейки с формулой на определённое расстояние. Поэтому, при протягивании формулы в сторону, она будет ссылаться на то же самое расстояние, а не на определённую ячейку. Например, если формулу протянуть вправо на одну ячейку, то она уже будет ссылаться не на A1, а на B1. Если вместо этого мы протянем формулу вниз, то ссылка окажется не на A1, а на A2. Таким образом, ссылки собьются и формула выдаст неправильный результат.

Если же мы вместо ссылки на ячейку A1, проставим $A$1, то куда бы мы не сдвигали формулу и на какое бы количество ячеек, формула всё равно будет ссылаться именно на ячейку A1. Сделать такую ссылку можно во время выбора ячейки в формуле, нажав клавишу F4.

Кроме жёсткой привязки к определённой ячейке может быть смешанная ссылка вида $A1 (фиксация столбца) или A$1 (фиксация строки). Это также достигается нажатием клавиши F4 два или три раза, пока знак $ не появится в нужном месте. Что это значит? При фиксации столбца $A1 во время сдвига формулы в сторону не будет происходить изменений. Ссылка будет изменяться только при движении по столбцу, то есть при протягивании формулы вверх или вниз. Соответственно, с учётом того, что ссылка идёт на ячейку A1, то при протягивании вверх, будет выдаваться ошибка #ССЫЛКА! (потому что выше A1 ячеек нет), а при протягивании формулы вниз, ссылка на ячейку примет вид $A2.

При создании ссылки вида A$1, будет зафиксирована строка, в результате, при протягивании формулы вверх или вниз, изменений в адресации ячейки не будет. При протягивании формулы влево будет выдаваться ошибка #ССЫЛКА! (слева от A1 нет ячеек), при протягивании формулы вправо, ссылка примет вид B$1 и выдаст значение из этой ячейки. Наглядным примером использования разной адресации ячеек служит таблица Пифагора. Если кто-то не помнит – это таблица умножения в шахматном виде, где перекрёстно перемножаются цифры из верхней строки (от 1 до 10) и из левого столбца (от 1 до 10).

Таблица Пифагора 1

Соответственно, мы получаем значения от 1 до 100. Если мы в ячейку B2 введём формулу =A2*B1 и протянем строки и столбцы, то формула будет считать неправильно, или, верней сказать, выдаст не те значения, которые нам нужны, так как, если вы помните, формула будет перемножать данные на определённом расстоянии от себя.

Таблица Пифагора 2

Если мы поставим формулу с жёсткой фиксацией (абсолютная адресация), то также не получим нужный результат. Формула просто скопирует одно и то же значение.

Таблица Пифагора 3

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

Таблица Пифагора 4

Наглядно ещё раз этот пример нам поможет проиллюстрировать короткое видео.

Если Вы новичок, то отличным подспорьем для расчётов в excel станет наш шаблон Финансовых вычислений https://fin-analis.ru/finexcel/