Относительные и абсолютные ссылки в Excel от А до Я

Всех приветствую в очередном уроке нашего курса по работе с Excel от WiFiGid! Здесь мы поговорим про относительные и абсолютные ссылки в Excel, узнаем что это такое, как их использовать, как поменять одно на другое. Коротко, без воды, с картинками

Краткая справка

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

=F7 – относительная ссылка
=$F$7 – абсолютная ссылка
=$F7 – смешанная ссылка с закреплением по столбцу
=F$7 – смешанная ссылка с закреплением по строке

Что такое ссылки в Excel?

Все мы помним про гиперссылки в интернете или в Word (хотя их можно вставлять и в Excel) – когда мы просто с какого-то текста даем возможность по клику сразу же перейти в нужное местов документе или на какой-то сайт. Так вот не стоит путать гиперссылки и простые ссылки на ячейки в Excel – они работают совсем по-другому.

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

Пример ссылок:

=F7 (ссылка на одну ячейку F7, просто берет значение из нее)
=F7 + F8 (а это уже формула, которая получает сумму двух ячеек, и на эти две ячейки мы имеем две ссылки F7 и F8)

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

По умолчанию ссылки в Excel являются относительными (ссылки из прошлого раздела тому пример). Относительные ссылки в электронной таблице Excel – это ссылки, которые при копировании в другую ячейку изменяются. Причем это изменение проходит ОТНОСИТЕЛЬНО: скопировали формулу в новую ячейку правее – значение ссылки изменилось на одну букву, сместили вниз – увеличилась численная часть ссылки.

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

Например, формулу из предыдущего раздела (=F7 + F8) я просто копирую в одну ячейку ниже и получаю уже другой результат:

Заметили, что я ячейку скопировал на одну вниз, а значение тоже на всех ссылках поменялось на одну строку «вниз»: =F8 + F9. Вот так и работают относительные ссылки – буквально в два клика (если используете маркеры автозаполнения) можно одну формулу изменить под все строки или столбцы. И это классно! Хотя такое поведение нужно не всегда…

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

Иногда в формуле все-таки какую-то ячейку нужно «закрепить». Например, мы вводим курс доллара в одну ячейку, а в таблице содержится сумма в долларах, которую мы хотим конвертировать в честные рубли. Конечно, можно сделать вот так:

Т.е. мы просто продублировали один и тот же курс доллара на все строки, а потом помножили на него. А если вдруг курс изменится? Конечно, можно поменять его во всех строках (и видел, что так некоторые и делают), но гораздо проще выделить под него отдельную ячейку:

Работает? Для первой ячейки да, но остальные ячейки при копировании просто взяли не те значения – вторая ячейка захватила пустую строку, а третья вообще попыталась умножить число на строку «Рубли». Как решение можно просто руками в каждой ячейке проставить правильную ссылку на курс доллара. Но если таких ячеек тысячи?

Вот для этого и придумали абсолютные ссылки. Абсолютные ссылки при копировании и автозаполнении НЕ изменяются и всегда ссылаются на одну и ту же ячейку. Главное отличие от относительной – наличие символа доллара ($) перед фиксирующей позицией.

Относительная ячейка H5 у нас превращается в $H$5, и без всякой магии все начинает работать:

Смешанные ссылки

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

=$H5 – закрепили столбец, а строка остается относительной.
=H$5 – закрепили строку, а столбец остается относительным.

Т.е. мы ставим знак доллара в ячейке или перед строкой, или перед столбцом. И тогда при копировании или автозаполнении смещаться будет только что-то одно. Такое на практике встречается не так часто как варианты с чисто относительными, или чисто абсолютными ссылками, но бывает.

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

Если остались вопросы

Вот мы и рассмотрели основные моменты адресации в Excel. Но если у вас остались конкретные вопросы – просто напишите их в комментариях под этой статьей, а мы попробуем в этом разобраться и помочь.

Видео по теме