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

Относительные и абсолютные ссылки в Эксель

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

На прошлом шаге мы создали целый столбец значений просто воспользовавшись функцией автозаполнения Excel.

Программа автоматически «поняла» нашу задумку и значения адресов ячеек в формулы были подставлены правильные, но ка это произошло?

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

Если в формуле сделана ссылка на ячейку В3 (в которой находится какое-то число) и мы поменяем значение в этой ячейке, то автоматически будет пересчитана и формула.

Поясню на примере.

В ячейку B3 введем цифру 2, а в ячейку B4 вставим следующую формулу:

= B3+3

Какой будет результат вычислений?

Правильно, после нажатия клавиши Enter в ячейке B4 отобразится вычисленное значение — 5.

Что произойдет, если мы изменим значение ячейки B3 с 2 на 4?

Верно! Значение в ячейке B4 автоматически пересчитается и будет равным 7. Это означает, что формула будет рассчитываться в соответствии с новым значением ячейки В3.

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

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

Мы с вами научились использовать автозаполнение для тиражирования однотипных формул в таблице. По сути автозаполнение нам позволяет скопировать формулу из одной ячейки и вставить ее в следующую. То же самое происходит и при фактическом копировании. Чтобы в этом убедиться, давайте скопируем ячейку В8. Для этого выделим ячейку (1) и щелкнем на ней правой кнопкой мыши для вызова контекстного меню (2).

Данное меню называется контекстным, так как его содержимое изменяется в зависимости от того, на каком объекте оно вызывается, то есть от контекста конкретной ситуации.

Выберем из контекстного меню пункт Копировать. Ячейка выделилась динамической рамкой.

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

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

Буфер обмена на вкладке Главная — нажимаем на кнопку Вставить и получаем результат.

Если мы посмотрим на формулу в ячейке B19, то увидим в ней ссылку на ячейка В18.

Понимаете что произошло? Это очень важно понять!

Мы копировали ячейку В8, в которой в формуле использовалась ссылка на вышестоящую ячейку В7 (1, см. рис. выше), то есть использовалась ссылка на ячейку, которая была выше ячейки с формулой. Именно так она и была скопирована — в ячейке В19 (2, см. рис. выше) в формуле используется ссылка на вышестоящую ячейку В18. То есть адрес ячейки в формуле берется относительно местоположения ячейки с формулой.

Для проверки попробуйте ответить на вопрос — если сейчас скопировать формулу из ячейки В7 (см. рис. выше), которая у нас несколько отличается от формул в других ячейках, и вставить ее в ячейку В20, то ссылка на какую ячейку мы увидим в итоге?

Ответили?

Давайте проверим — в формулу войдет ячейка B16, которая находится выше на четыре ячейки, как и ячейка В3, относительно ячейки В7.

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

А что делать, если нужно, чтобы в формуле участвовало значение из какой-то конкретной ячейки? Все логично — нужно использовать абсолютные ссылки, то есть ссылки на конкретные ячейки таблицы.

Как мы знаем, адрес ячейки состоит из обозначения столбца и строки, например, ячейка B3 находится на пересечении третьей строки и столбца B.

Чтобы сделать ссылку на ячейку абсолютной, нужно поставить знак

$ перед обозначением столбца и строки (знак доллара ставится с помощью сочетаний клавиш Shift + 4). То есть, если я хочу, чтобы в формуле у меня всегда участвовало значение из ячейки В3, то изменю В3 на $B$3.

Давайте так и сделаем — изменим формулу в ячейке В7:

Одна ссылка на ячейку B3 у нас абсолютная, а другая остается относительной. Если теперь скопировать значение ячейки B7 и вставить его в любую другую ячейку, например, В21, то мы увидим следующее:

Абсолютная ссылка по-прежнему ведет на ячейку B3, а относительная изменилась и теперь указывает на ячейку B17.

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

При этом мы можем запрещать изменять в ссылке что-то одно — либо столбец, либо строку. Такая ссылка будет называться смешанной. Такие ссылки выглядят так — B$2 или $B2, что, соответственно, запретит Эксель менять адрес строки или столбца.

Ну а теперь давайте попрактикуемся и применим полученные знания.

В ячейке А4 напишем — Процент, а в ячейку В4 подставим значение процента по вкладу — 11.

Теперь изменим формулы — меняем в ячейке В7 значение процента на абсолютную ссылку — $B$4, затем тоже самое проделаем в ячейке В8:

Растиражируем с помощью автозаполнения формулу из ячейки B8 на остальные ячейки столбца B.

Что мы получили в итоге? А то, что теперь мы можем менять и сумму вклада, и процент, а остальные данные будут вычисляться автоматически!

Давайте снизу таблицы в ячейке А19 напишем — Доход, а в ячейку В19 подставим простую формулу, вычисляющую этот доход: =B18-B3, то есть сумма итогового вклада с процентами минус сумма начального вклада.

С точки зрения функциональности таблица готова, но вот значения в ней не очень наглядны. Поэтому стоит применить некоторое форматирование, чтобы сделать таблицу более читаемой.

Именно это мы и сделаем на следующем шаге.

Абсолютные и относительные ссылки в 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

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

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

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

Ссылки рабочих книг на исходные рабочие книги создаются относительным образом, когда это возможно. Это означает, что записывается не полный путь к исходной книге, а часть пути, относящаяся к целевой книге. С помощью этого метода вы можете перемещать книги, не разрывая связи между ними. Однако ссылки на рабочие книги остаются нетронутыми, только если рабочие книги остаются в одном и том же месте относительно друг друга. Например, если целевая рабочая книга — C:\Mydir\Destination.xlsx, а исходная рабочая книга — C:\Mydir\Files\Source.xlsx, вы можете переместить файлы на диск D, если исходная рабочая книга все еще находится. в подпапке под названием «Файлы».

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

Способы хранения ссылок в книгах различаются следующим образом:

Тип хранилища 1: тот же диск с той же папкой или дочерней папкой

Исходная книга находится либо в той же папке, либо в дочерней папке, что и целевая книга. В этом случае мы сохраняем относительный путь к файлу, например, subfolder/source.xlsx и target.xlsx.

Этот тип лучше всего подходит для облачных книг и при перемещении обеих книг.

Тип хранилища 2: тот же диск, но с разными родственными папками

Исходная и конечная книги находятся на одном диске, но в разных родственных папках. В этом случае мы храним путь относительно сервера, например, /root/parent/sibling1/source.xlsx и /root/parent/sibling2/destination.xlsx.

Этот тип лучше всего работает, если рабочая книга назначения перемещается в пределах того же диска, но исходная рабочая книга остается в том же месте.

Тип хранилища 3: разные диски

Исходная книга находится на другом диске, чем целевая книга. Например, целевая папка рабочей книги находится на диске C, а исходная папка рабочей книги — на диске H. В этом случае мы сохраняем абсолютный путь, например, H:\folder\source.xlsx или https://tenant.sharepoint.com/teams/site/folder/source.xlsx.

Этот тип лучше всего подходит, если целевая книга перемещается, но исходная книга остается на том же месте. Это предполагает, что целевая книга по-прежнему может обращаться к исходной книге.

Если исходная книга находится в папке XLStart , Альтернативное расположение файла запуска или Библиотека , свойство записывается для указания одной из этих папок, и сохраняется только имя файла.

Excel распознает две папки XLStart по умолчанию, из которых автоматически открываются файлы при запуске. Эти две папки следующие:

Папка XLStart , которая находится в профиле пользователя, является 9-й.0018 XLStart Папка, которая хранится как свойство ссылки на книгу. Если вы используете папку XLStart , которая находится в папке установки Office, эта папка XLStart обрабатывается как любая другая папка на жестком диске.

Имя папки Office изменяется между версиями Office. Например, имя папки Office может быть Office14, Office15 или Office16 в зависимости от используемой версии Office. Это изменение имени папки приводит к разрыву ссылок на книги, если вы переходите на компьютер, на котором работает версия Excel, отличная от версии, в которой была установлена ​​ссылка.

• Папка XLStart , которая находится в папке установки Office, например C:\Program Files\Microsoft Office\<папка Office>\XLStart

• Папка XLStart в профиле пользователя, например C:\Documents and Settings\\Application Data\Microsoft\Excel\XLStart

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

Кроме того, ссылка на книгу, отображаемая в Excel, может выглядеть по-разному в зависимости от того, как была открыта книга. Ссылка на книгу может соответствовать либо корневому общему ресурсу UNC, либо букве корневого диска, которая использовалась для открытия файла.

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

Сценарий 1

1. Вы сопоставляете диск с корнем общего ресурса. Например, вы сопоставляете диск Z с \\MyServer\MyShare\MyFolder1.

2. Вы создаете ссылки на книгу-источник, которая хранится в сопоставленном расположении, после того как вы открываете рабочую книгу назначения на этом подключенном диске.

3. Вы открываете рабочую книгу по пути UNC.

4. В результате ссылка на рабочую книгу будет разорвана.

Если вы закроете целевую книгу, не сохранив ее, ссылки на книгу не изменятся. Однако, если вы сохраните целевую книгу перед ее закрытием, вы сохраните ссылки на книгу с текущим неверным путем. Папки между корнем общего ресурса и сопоставленной папкой не будут включены в путь. В приведенном выше примере ссылка изменится на \\MyServer\MyFolder1. Другими словами, имя общего ресурса исключается из пути к файлу.

Сценарий 2

1. Вы сопоставляете диск с корнем общего ресурса. Например, вы сопоставляете диск Z с \\MyServer\MyShare\MyFolder1.

2. org/ListItem»>

   Вы открываете файл по пути UNC или подключенному диску, сопоставленному с другой папкой в ​​общем ресурсе, например \\MyServer\MyShare\MyFolder2.

3. В результате ссылка на рабочую книгу будет разорвана.

Если вы закроете целевую книгу, не сохранив ее, ссылки на книгу не изменятся. Однако если вы сохраните целевую книгу перед ее закрытием, вы сохраните ссылки на книгу с текущим неверным путем. Папки между корнем общего ресурса и сопоставленной папкой не будут включены в путь. В приведенном выше примере ссылка изменится на \\MyServer\MyFolder1. Другими словами, имя общего ресурса исключается из пути к файлу.

См. также

Создание ссылок на книги

Управление ссылками на книгу

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

Microsoft Excel — Могу ли я связать изображения, используя относительный путь?

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

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

Изменено 1 месяц назад

Просмотрено 14 тысяч раз

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

Связывание изображений без их сохранения работает очень хорошо — теперь у меня есть документ Excel и папка «pictures» рядом с ним, содержащая все мои файлы изображений.

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

Есть ли удобный способ сохранить их как относительные пути или Excel автоматически обновляет информацию о пути?

Обновление: важно, чтобы изображения отображались на листе и могли быть распечатаны.

Я работаю с Microsoft Excel для Mac 2008 и 2011. Я действительно ценю твою помощь.

• Microsoft-Excel
• Microsoft-Excel-2011
• Microsoft-Excel-2008

3

У вас есть 2 основных вопроса: рендеринг изображений в Excel и относительные пути.

Визуализация изображений в Excel

Для этого есть сценарий на SO:

 Dim url_column As Range
Затемнить image_column как диапазон
Установить url_column = Worksheets(1).UsedRange.Columns("A")
Установите image_column = Worksheets(1).UsedRange.Columns("B")
Дим и пока
Для i = 1 Для url_column.Cells.Count
  С image_column.Worksheet.Pictures.Insert(url_column.Cells(i).Value)
    .Слева = image_column.Cells(i).Слева
    .Top = image_column.Cells(i).Top
    image_column.Cells(i).EntireRow.RowHeight = .Height
  Конец с
Следующий
 

Относительные пути

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

2

Используйте функцию «Найти/Заменить» в Excel, чтобы обновить путь к файлу изображения.