Excel относительная ссылка: Относительные, абсолютные и смешанные ссылки на ячейки в Excel

Содержание

Абсолютная и относительная ссылка в Excel. Узнаем как использовать?

Даже если вы недавно начали работать с программой Microsoft Excel, то, наверное, знаете, что одним из её преимуществ является удобная работа с формулами и таблицами. Поэтому сегодня мы с вами рассмотрим адресование в таких таблицах и узнаем, что такое относительные, абсолютные и смешанные ссылки. Их работа достаточно проста, поэтому проще всего будет рассмотреть их на практике. Так что такое абсолютная и относительная ссылка?

Определение

Ссылки в Microsoft Excel — это не то же самое, что и ссылки в интернете. Ссылкой называется адрес ячейки (например: А1, В10). Они делятся на два основных вида — абсолютные и относительные ссылки. В Excel адресация происходит по ячейкам, поэтому при переносе и копировании формул нам частенько необходимо сохранить либо, наоборот, заменить данные.

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

Данный вид встречается наиболее часто. Если вы не ходили на специальные курсы, вас никто не обучал, то вы вряд ли встречались с абсолютными ссылками. Поэтому мы начнем с того, что проще.

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

Пусть нам даны ячейки с данными (столбцы) — М1, М2, М3, Н1, Н2, Н3. Под ними в ячейке М4 мы пишем формулу, результатом которой будет сумма трёх чисел (М4=М1+М2+М3). Вроде всё просто. Теперь нам необходимо посчитать содержимое ячейки Н4. Она также является суммой тех данных, которые находятся над ней. Перетягиванием или просто копированием ячейки М4, мы переносим формулу в Н4. В результате все ссылки в ней (М1, М2, М3) будут заменены на Н1, Н2 и Н3, соответственно.

Это и называется относительной адресацией. Пример кажется простым и возникает естественный вопрос, а не проще ли заново написать формулу? Нет, в больших таблицах и объёмных вычислениях, в которых могут использоваться самые разные функции и операторы намного надежнее просто скопировать и перенести формулу, чем заново составлять выражение.

Абсолютная ссылка

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

Допустим, вам надо решить какую-нибудь физическую задачку. У вас имеется некая константа, записанная в ячейке А1. Конечно, можно было бы каждый раз писать это число вручную, однако проще записать её в одно определённое место. Также нам даны два столбца данных (М1,М2,Р1,Р2). В ячейках М3 и Р3 необходимо посчитать следующую формулу: М3=(М1+М2)*$А$1. Теперь при перетаскивании и копировании в ячейку Р3 получится следующий результат Р3=(Р1+Р2)*$А$1. То есть адрес используемой ячейки не сдвинется.

Чтобы адрес ссылки сделать абсолютным, нажмите на формулу, выделите необходимую «ссылку» и нажмите F4. Либо вручную напечатайте символы «доллара».

Смешанная

Это последний тип, встречающийся в формулах. Как понятно из названия, это абсолютная и относительная ссылка одновременно. Несложно догадаться, что на практике она обозначается, как $А1 или А$1. Таким образом, это позволяет сохранять адресацию на столбец или строку, но продолжать «скользить» по нему. Рассмотрим пример.

	А	В	С
1		3,5	4,5
2	70
3	80

Абсолютные и относительные ссылки в MS Excel

Главная » Табличный редактор Excel » Абсолютные и относительные ссылки в MS Excel

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

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

По умолчанию, все ссылки на ячейки MS Excel являются относительными. При копировании формул, их содержимое меняется в зависимости от относительного расположения строк и столбцов. Другими словами, если вы скопируете формулу =A1+B1 из строки 1 в строку 2, формула превратится в =A2+B2.

Простейшая формула в MS Excel состоит из относительных ссылок

Разумеется это очень удобно в том случае, если мы имеем таблицу, где каждая строка в определенном столбце имеет один и тот же принцип вычисления результата. Например, в таблице из 20 строк нам нужно вычислить сумму ячеек А1 и В1, а результат поместить в ячейку С1. Нет ничего проще – мы помещаем в ячейку С1 формулу =А1+В1 и насаждаемся результатом.

При копировании относительной ссылки, MS Excel автоматически смещает значения формулы на нужное количество строк и столбцов

А как вычислить результат аналогичного сложения для следующей строки? Поместить в С2 формулу А2+В2 – вариант отличный, но не слишком ли это жирно – пройти по всем строкам таблицы и в каждой записать нужную формулу? К счастью, делать этого не нужно – за счет того, что ссылки в Excel по умолчанию относительны, нам достаточно просто скопировать исходную формулу (=А1+В1) на следующую строку, и программа сама подставит на нужные места изменившиеся за счет смещения относительные данные ячеек (формула изменит вид на =А2+В2).

Да, работая с относительными ссылками мы можем просто протянуть ссылку на на весь столбец и наслаждаться результатом

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

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

Не всегда нужно, чтобы формулы (или часть формулы) изменялись при копировании и перемещении в другую ячейку. Простейший пример: нам нужно провести ту же самую операцию, что и выше, но дополнительно, итоговый результат в столбце “С” нужно умножить на некий коэффициент – постоянный на данный момент времени (в последующем он может изменится).

Такая формула обычно имеет вид: =(А1+В1)*Коэффициент

К примеру, коэффициент имеет вид 2,6, тогда формула будет выглядеть как =(А1+В1)*2,6 На первый взгляд этот самый коэффициент можно просто вписать в формулу вручную (как я указал выше). Но что если завтра коэффициент изменится? Гораздо проще поместить его в ячейку таблицы (например D1) и привести формулу к виду =(А1+В1)*D1. Однако как мы помним, по умолчанию все ссылки в MS Excel относительны, а это значит что при копировании этой формулы на строку ниже, она приобретет вид =(А2+В2)*D2.

А вот и минус относительных ссылок – смещается вся формула и даже те фрагменты, которые по идее смещать не надо

Выхода тут два: или добавить в таблицу новую колонку “коэффициент” заполненную одинаковыми данными (тогда при абсолютном смещении сместится и ячейка с коэффициентом), или, сделать эту конкретную ячейку D1 не относительной, а абсолютной.

Можно решить проблему с относительными ссылками введя дополнительную колонку с одинаковыми значениями

Добиться этого просто: к каждому знаку “постоянного” элемента нужно всего лишь добавить специальный символ “$”. Иными словами, формула =(А1+В1)*$D$1 будет работать совсем не так как раньше: если “относительные” ячейки А1 и В1 будут изменяться при копировании формулы в другое место по обычным правилам относительных ссылок, то $D$1 всегда будет ссылаться на одну и ту же ячейку, как бы мы не перемещали исходную формулу.

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

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

Подробнее об абсолютных ссылках.

Теги: excel, Основы работы

Также вас может заинтересовать:

Как Excel решает использовать абсолютный путь для внешних ссылок, а не относительный путь

спросил 9 лет, 2 месяца назад

Изменено 1 год, 7 месяцев назад

Просмотрено 34к раз

Похоже, что поведение относительных ссылок в Excel 2013 отличается от поведения в Excel 2010.

То, что я испытываю в Excel 2013, следующее:

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

Итак, я хотел бы знать следующее:

Все ли то, что я только что описал, является ожидаемым поведением для Excel 2013?
Зависит ли это от версии Excel 2013 или среды?
Есть ли способ изменить значение по умолчанию «Сохранить значения внешних ссылок» для новых книг? (Было бы полезно получить на это ответ, даже если это «нет»).

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

Microsoft-Excel-2013

Я вижу повреждение внешних гиперссылок даже в Excel 2010 для электронных таблиц, хранящихся на сервере SharePoint. При сохранении все абсолютные гиперссылки превращаются в относительные гиперссылки, что их портит. Переход к файлу\Параметры\Дополнительно\Общие\Веб-параметры\вкладка «файлы» и снятие флажка с именем «Обновлять ссылки при сохранении» останавливает это поведение.

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

Это означает, что каждый пользователь, который когда-либо может получить доступ к электронной таблице, должен быть предупрежден об изменении этого параметра, потому что, похоже, нет никакого способа сделать это изменение «прикрепленным» к самому файлу.

Это особенно плохо для пользователей SharePoint, поскольку SharePoint разработан для упрощения обмена файлами.

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

Надеюсь, это поможет….

Чтобы Excel не менял гиперссылки с абсолютных на относительные, перейдите в «Файл» — «Информация», нажмите «Показать все свойства » и заполните свойство База гиперссылок значением c:\ . Действия действительны для Office 365, но для других версий должны быть аналогичные шаги.

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

book.xltx

Обходной путь отсюда https://www.excelforum.com/excel-general/ 1197648-общий-абсолютный-путь-гиперссылки-решен-остановить-гиперссылки-из-изменения-на-относительный-путь.html

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

Только что попытался переместить пару файлов со ссылкой, созданной без особой осторожности, и она оставалась относительной,

пока связанный файл был фактически открыт

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

Я провел несколько тестов, переместив пару связанных файлов в разные места, а затем переместив только ссылку и оставив связанный файл позади.

HTH

(с использованием Excel 2013)

Зарегистрируйтесь или войдите в систему

Зарегистрируйтесь с помощью Google

Зарегистрироваться через Facebook

Зарегистрируйтесь, используя адрес электронной почты и пароль

Опубликовать как гость

Электронная почта

Требуется, но никогда не отображается

Опубликовать как гость

Электронная почта

Требуется, но не отображается

Нажимая «Опубликовать свой ответ», вы соглашаетесь с нашими условиями обслуживания, политикой конфиденциальности и политикой использования файлов cookie

excel — Как указать относительный путь к книге в поле ССЫЛКА?

Задавать вопрос

спросил

Изменено 3 года, 10 месяцев назад

Просмотрено 4к раз

После специальной привязки диапазона ячеек из Excel в Word (2013) поле выглядит так:

{ ССЫЛКА Excel.Sheet.12 "D:\\RelativePath\\1\\work\\tmp.xlsx" Sheet1!NamedRange \a \p }

При перемещении источника (.xlsx) и получателя (.docx) в папку 2, необходимо изменить ссылку в поле ССЫЛКА, чтобы она выглядела так:

{ ССЫЛКА Excel.Sheet.12 "D:\\RelativePath\\2\\work\\ tmp.xlsx" Sheet1!NamedRange \a \p }

Когда таких полей много, это неудобно.

Я пробовал оба варианта:

{ССЫЛКА Excel.Sheet.12 "...\\...\\work\\tmp.xlsx" Sheet1!NamedRange \a \p }

и что:

{ССЫЛКА Excel.Sheet.12 "~\\work\\tmp.xlsx" Sheet1!NamedRange \a \p } но ничего не работает.

Тоже не работает:

Как создавать абсолютные гиперссылки и относительные гиперссылки в документах Word

Можно ли в поле ССЫЛКА указывать не абсолютный, а относительный адрес источника?

Upd @Cindy Meister предложила решение, и после некоторой доработки код работает нормально.

Вот он:

 Sub updateLINKs()
Dim doc As Word.Document
Dim fld As Word.Field
Dim sFilePath как строка, sFileName как строка
Установить документ = активный документ
sFilePath = док.Путь
Для каждого эт. в полях документа
    Если fld.Type = wdFieldLink Тогда
      Если fld.Result.InlineShapes.Count > 0 и _
         InStr(fld.OLEFormat.ClassType, "Excel") И _
         fld.LinkFormat.SourcePath <> sFilePath Тогда
           sFileName = fld.  LinkFormat.SourceName
           fld.LinkFormat.SourceFullName = sFilePath & "\" & sFileName
      Конец, если
    Конец, если
Следующий
Конец сабвуфера

excel
ms-word
word-field

Невозможно использовать относительные пути в полях Link . Путь необходимо обновить/изменить в коде поля. Это можно сделать довольно просто, используя возможности, предоставляемые свойством LinkFormat .

Например, предположив, что относительный путь будет таким же, как и путь документа Word, следующий код:

Получает путь из Document.Path свойство
Зацикливает все поля в документе и определяет, являются ли они полем LINK из источника Excel, который отображает InlineShape (объект, а не текст). Поля зацикливаются в обратном порядке, поскольку изменение пути приводит к тому, что Word удаляет и воссоздает ССЫЛКУ в фоновом режиме. Это означает, что цикл с самого начала документа будет неоднократно «попадать» в одно и то же поле, что приводит к бесконечному циклу.
Определяет текущее имя файла для связанного объекта из Поле ссылки с использованием LinkFormat.SourceName
Объединяет информацию и присваивает ее свойству LinkFormat.SourceFullName

Обратите внимание, что свойства LinkFormat.SourcePath и LinkFormat.SourceName доступны только для чтения, поэтому путь можно изменить только с помощью SourceFullName .

 Sub UpdateLinks()
 Dim doc As Word.Document
 Dim fld As Word.Field, nrFields As Long, i As Long
 Dim sFilePath как строка, sFileName как строка
 Установить документ = активный документ
 sFilePath = док.путь
 nrFields = doc.Fields.Count
 Для i = nrFields To 1 Шаг -1
 Установите fld = doc.Fields(i)
 Если fld.Type = wdFieldLink Тогда
 Если fld.result.InlineShapes.Count >  0 и _
 InStr(fld.OleFormat.ClassType, "Excel") Тогда
 sFileName = fld.

Штирлиц — Сеть печатных салонов в Перми

Excel относительная ссылка: Относительные, абсолютные и смешанные ссылки на ячейки в Excel

Абсолютная и относительная ссылка в Excel. Узнаем как использовать?

Определение

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

Абсолютная ссылка

Смешанная

Абсолютные и относительные ссылки в MS Excel

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

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

Как Excel решает использовать абсолютный путь для внешних ссылок, а не относительный путь

Зарегистрируйтесь или войдите в систему

Опубликовать как гость

Опубликовать как гость

excel — Как указать относительный путь к книге в поле ССЫЛКА?

Добавить комментарий Отменить ответ