Добро пожаловать на наш урок по работе с Excel!
Приветствуем всех участников нашего курса по работе с Excel от команды MoiSovety.ru! Сегодня мы с вами поговорим об относительных и абсолютных ссылках в 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. Если у вас возникли конкретные вопросы, пожалуйста, напишите их в комментариях под этой статьей, и мы постараемся разобраться и помочь вам.
