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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Видео по теме

Автор статьи
Ботан 1012 статей
Мастер занудных текстов и технического слога. Мистер классные очки и зачётная бабочка. Дипломированный Wi-Fi специалист.
WiFiGid
Комментарии: 4
  1. Ктистина

    Спасибо! Все очень доступно

  2. даша

    ох…. может кто-то за меня все сделает? так же проще будет

  3. Аноним

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

  4. Айдар

    А можно ссылаться на ячейку не как на число, а как на операцию? после = пишем название функции, а если эти функции текстом в ячейки вписать и обращаться как к функции? Спасибо)

Добавить комментарий
После отправки комментарий может не отображаться - это нормально. Сразу же после модерации он будет опубликован. Если Вы хотите быстро узнать о получении ответа, рекомендуем оставить свой e-mail (это необязательно). E-mail используется исключительно для Вашего оповещения, мы не занимаемся спамом.

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:

Нажимая на кнопку "Отправить комментарий", я даю согласие на обработку персональных данных и принимаю политику конфиденциальности.