Ссылки в Excel - statanaliz.info

Ссылки в Excel

Когда пользуешься различными формулами Excel, то приходится обращать внимание на ссылки. Ссылки в Excel, как известно, бывают разными: относительными, абсолютными, внешними, в виде имен диапазонов и др. 

Первым делом уясним, что такое относительные и абсолютные ссылки и зачем конструкторы Excel их придумали. Вопрос не праздный т.к. при работе с большими массивами данных формула прописывается обычно один раз, а затем «протягивается» (копируется) на другие ячейки (например, в итоговой строке или итоговом столбце таблицы). Корректность такого «протягивания» сильно зависит от типа ссылки. Здесь есть два варианта. Первый: диапазон, на который ведет ссылка, «перемещается» вслед за копируемой ячейкой, то есть остается на одном месте относительно ячейки с формулой. Второй: ссылка ведет на один и тот же адрес независимо от того, куда копируется формула. Недооценка важности вида ссылки приводит к ошибкам в расчетах и необходимости коррекции формул. Все это как минимум тормозит работу. Помните, что каждое лишнее действие вносит свой вклад в бесполезно потраченное время.

Рассмотрим простой пример. Нужно сложить два числа. Сделать это легко, прописав в свободной ячейке (например, внизу) знак «=» и затем через знак «+» сослаться на складываемые ячейки. Если чисел много, то суммировать лучше через функцию СУММ, указав сразу весь диапазон суммирования.

Простое суммирование

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

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

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

Протягивание

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

=A1,

где буква обозначает столбец, а число – строку.

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

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

Рассмотрим другой пример, когда нужно рассчитать долю каждого значения к итогу. Введем формулу для расчета доли первого числа.

Расчет доли

Теперь попробуем «протянуть» формулу вниз для расчета остальных долей. Сумма долей должна получиться ровно 100%. Однако уже на втором значении видно: что-то пошло не так.

Ошибка при использовании относительной ссылки

Числитель «поехал» вниз правильно, а вот знаменатель должен был остаться на месте. Чтобы все получилось правильно, нужно зафиксировать ссылку на итог, чтобы она не «ездила» вслед за формулой. Короче говоря, ссылку на итоговую сумму нужно сделать абсолютной, для чего перед названием строки и столбца ставится значок доллара $ (<Shift+4> в английской раскладке):

=$A$1

Выставление абсолютной ссылки

Таким же образом можно «закрепить» ссылку на валютный курс, пошлину, налог и прочее, что находится в одном месте для многих ссылающихся ячеек.

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

Помимо абсолютных и относительных ссылок встречаются еще и т.н. смешанные ссылки, когда фиксируется только строка или столбец:

=A$1

=$A1

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

Как быстро установить символ доллара $

Если ставить значок доллара с клавиатуры классическим методом, то потребуется вначале перейти в английскую раскладку, а затем нажать Shift+4. Скажем прямо, это долго и неудобно. Гораздо быстрее войти в режим редактирования формул с помощью клавиши F2 (если вы набираете формулу вручную, то уже находитесь в этом режиме), установить курсор на нужной ссылке и нажать F4. Ссылка начнет менять свой режим «абсолютности». После нажатия F4 относительная ссылка станет абсолютной (и по строкам, и по столбцам). Если нажать F4 еще раз, то абсолютной станет только строка, следующее нажатие сделает абсолютным только столбец. Очередное нажатие F4 сделает ссылку снова относительной. И так по кругу. Снова абсолютная, только строка, только столбец, относительная и т.д. Последовательным нажатием F4 останавливаетесь на нужном варианте.

Последовательное нажатия <F4>

Очень удобно, не нужно переключать раскладку и искать $. Эта же кнопка работает и в диалоговом окне «Вставки функции». Например, при написании формулы ВПР или СУММЕСЛИ нужно внимательно смотреть на ссылающиеся диапазоны, поэтому значки $ используются очень часто.
Как я уже говорил выше, если диапазон, на который нужно сослаться находится в той же книге (файле Excel), пусть даже и на другом листе, то Excel по умолчанию устанавливает относительные ссылки (без значка «$»). А вот ссылки на другие книги Excel по умолчанию делает абсолютными (вставляет «$» где только можно). Но про другие листы и книги поговорим в следующем параграфе.

Ссылки на другие листы и книги

Excel умеет ссылаться не только в пределах одного листа, но и на другие листы и даже другие файлы Excel. Такой функционал значительно расширяет диапазон вычислений. Если в формулах есть ссылки на другие файлы, то следует обратить внимание на следующее. Во-первых, при открытии файла с внешними связями Excel назойливо напоминает об этом выскакивающим посреди экрана окошком. Это, конечно, помогает не забывать, что в файле есть внешние ссылки, но лучше, чтобы их вообще не было. Во-вторых, при изменении или удалении внешнего источника мгновенно меняются и результаты вычислений, что нужно далеко не всегда. В третьих, если возникнут ошибки в расчетах, то на обнаружение их источника может потребоваться дополнительное время, т.к. данные находятся в другом файле. В общем, сразу после вычислений с использованием данных из других книг внешние ссылки рекомендуется удалить: Данные → Подключения → Изменить связи → Разорвать связь.

Окно разрыва внешних связей

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

Стиль ссылок

И еще кое-что про ссылки. Иногда они выглядят несколько непривычно. Это особый стиль ссылок, при котором адрес ячейки A1 имеет наименование R1C1 (первая строка (R1), первая колонка (C1)). Такой стиль ссылок иногда может быть полезен, но довольно редко. Поэтому неплохо уметь возвращать нормальный вид адреса ячейки. Для этого нужно выполнить следующие действия: Файл → Параметры → Формулы и далее нужно снять галочку с пункта Стиль ссылки R1C1.

Изменение стиля ссылок

Всего доброго и до новых встреч.

Поделиться в социальных сетях:
  • https://www.gate.io/zh/signup/612995 gate io

    Your article helped me a lot. what do you think? I want to share your article to my website: gate.io

  • https://accounts.binance.com/ar/register?ref=IJFGOAID سجل للحصول على 100 دولار أمريكي

    Can you be more specific about the content of your article? After reading it, I still have some doubts. Hope you can help me.

  • https://accounts.binance.com/sl/register?ref=10126709 www.binance.com prijava

    I don’t think the title of your article matches the content lol. Just kidding, mainly because I had some doubts after reading the article.

  • https://www.gate.io/tr/signup/XlQVXVo gateio güvenilir mi

    This article opened my eyes, I can feel your mood, your thoughts, it seems very wonderful. I hope to see more articles like this. thanks for sharing.

  • https://www.gate.io/tr/signup/XlQVXVo gate io fon şifresi

    This article opened my eyes, I can feel your mood, your thoughts, it seems very wonderful. I hope to see more articles like this. thanks for sharing.

  • https://community.articulate.com/users/20Bet20Bet 20bet

    Your article gave me a lot of inspiration, I hope you can explain your point of view in more detail, because I have some doubts, thank you.

  • https://20betperu.blogspot.com/ 20bet

    Your article gave me a lot of inspiration, I hope you can explain your point of view in more detail, because I have some doubts, thank you.

  • https://myspace.com/20bet 20bet

    Your article gave me a lot of inspiration, I hope you can explain your point of view in more detail, because I have some doubts, thank you. 20bet

  • https://casinotologin.com/gatecointr_com_http_output.html nimabi

    Thank you very much for sharing, I learned a lot from your article. Very cool. Thanks. nimabi

  • https://www.binance.com/pt-BR/register?ref=B4EPR6J0 código de indicac~ao da binance

    Thanks for sharing. I read many of your blog posts, cool, your blog is very good. https://www.binance.com/pt-BR/register?ref=B4EPR6J0

  • https://www.binance.info/zh-CN/join?ref=W0BCQMF1 注册

    Thank you for your sharing. I am worried that I lack creative ideas. It is your article that makes me full of hope. Thank you. But, I have a question, can you help me? https://www.binance.info/zh-CN/join?ref=W0BCQMF1

  • https://www.binance.com/vi/join?ref=IJFGOAID Tài khon binance

    Your article helped me a lot, is there any more related content? Thanks! https://www.binance.com/vi/join?ref=IJFGOAID

  • https://accounts.binance.com/de-CH/register-person?ref=FIHEGIZ8 binance Empfehlung

    Thanks for sharing. I read many of your blog posts, cool, your blog is very good. https://accounts.binance.com/de-CH/register-person?ref=FIHEGIZ8

  • https://www.binance.info/vi/join?ref=S5H7X3LP M tài khon binance

    I don’t think the title of your article matches the content lol. Just kidding, mainly because I had some doubts after reading the article. https://www.binance.info/vi/join?ref=S5H7X3LP

  • https://www.binance.com/lv/register?ref=FIHEGIZ8 binance us registrācija

    Thank you for your sharing. I am worried that I lack creative ideas. It is your article that makes me full of hope. Thank you. But, I have a question, can you help me?

  • https://www.binance.info/pt-PT/join?ref=B4EPR6J0 código binance

    Thank you for your sharing. I am worried that I lack creative ideas. It is your article that makes me full of hope. Thank you. But, I have a question, can you help me?

  • https://accounts.binance.com/en/register-person?ref=WTOZ531Y binance account creation

    Your point of view caught my eye and was very interesting. Thanks. I have a question for you.

  • https://www.binance.com/ur/register?ref=V2H9AFPY Аноним

    Can you be more specific about the content of your article? After reading it, I still have some doubts. Hope you can help me.

Комментарии для сайта Cackle
Пролистать наверх