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

—

MS Office 2007: Microsoft Excel

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

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

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

• Относительные ссылки. При копировании формул эти ссылки автоматически изменяются в соответствии с новым положением формулы.
• Абсолютные ссылки.  Эти ссылки не изменяются при копировании формул.
• Смешанные ссылки. В этих ссылках номер строки (или столбца) является абсолютным, а столбца (строки) — относительным.

Отличительной особенностью абсолютных ссылок являются два знака доллара ($): один перед буквой столбца и второй перед номером строки (например, $А$5). Чтобы поставить два знака доллара ($) в адресе ячейки, следует поставить курсор в любом месте адреса ячейки в строке формул и нажать клавишу F4 на клавиатуре один раз.

В Excel также допускаются смешанные ссылки, в которых только одна часть адреса является абсолютной (например, $А4 или А$4). В этом случае клавишу F4 необходимо нажать два или три раза (соответственно А$4 или $А4). Четвертое нажатие F4 возвращает к относительной ссылке. Например, если необходимо поставить какую-либо ссылку на А1, то первое нажатие клавиши F4 преобразует ссылку на ячейку в $А$ 1, второе — в А$1, третье — в $А1, а четвертое вернет ей первоначальный вид — А1.

Нажимайте клавишу F4 до тех пор, пока не появится нужный тип ссылки.

Различие между разными типами ссылок проявляется при копировании формул. 

На рис.30 показана таблица, в ячейке D2 которой находится формула умножения количества наименований товара на его цену. Формула выглядит следующим образом: =В2*С2. Если ее скопировать маркером заполнения на ячейки D3 и D4, то получим изображенную на рисунке таблицу. Поскольку в этой формуле используются относительные ссылки, то при копировании формулы в ячейки D3 и D4 они соответствующим образом изменятся, то есть в ячейке D3 получим формулу: =ВЗ*СЗ, а в ячейке D4 соответственно =В4*С4.

Если в ячейке D2 заменить относительные ссылки абсолютными, то получим =$В$2*$С$2.

Если теперь скопировать эту формулу в ячейку D3, то получим неправильный результат. Формулы в ячейках D3 и D2 будут одинаковыми.

Теперь изменим этот пример и подсчитаем комиссионные. Значение процентной ставки комиссионных хранится в ячейке в 7 (рис. 31). Перенесем заголовок Всего на одну ячейку вправо, а в D1 впишем =А7.

В результате в ячейке D1 получим Комиссионные. В ячейку D2 введем формулу =В2*С2*$В$7. Количество умножается на цену, а затем результат умножается на процентную ставку комиссионных, значение которой хранится в ячейке В7. Обратите внимание на то, что ссылка на ячейку В7 является абсолютной. Скопировав ячейку D2 в D3, получим =В3*С3*$В$7. Ссылки на ячейки В2 и С2 изменились, а ссылка на ячейку В7 — нет, т.е. мы получили правильный результат.

На рис.32 показана таблица, в которой используются смешанные ссылки. В левом столбце хранится значение длины прямоугольника, а в верхней строке находится ширина. В остальных ячейках вычисляется площадь прямоугольника соответственно данной длине и ширине. Например, в ячейке D5 вычисляется площадь прямоугольника, длина которого — 2, а ширина — 1,5. Для вычисления площади в ячейку С3 вводится формула = $В3*С$2.

Обратите внимание на то, что в формуле используются смешанные ссылки. В ссылке на ячейку В3 абсолютной является ссылка на столбец ($В), а в ссылке на ячейку С2 используется абсолютная ссылка на строку ($2). Скопировав эту формулу во все ячейки диапазона, мы получим правильный результат вычислений. Например, в ячейке F7 будет содержаться такая формула =$B7*F$2.

При использовании в ячейке С3 абсолютных или относительных ссылок результат окажется неверным.

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

Продукты Office Excel для Microsoft 365 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Дополнительно… Меньше

В 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 , Alternate Startup File Location или Library , свойство записывается для указания одной из этих папок, и сохраняется только имя файла.

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

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

Имя папки Office

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

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

• org/ListItem»>

  Папка 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. Вы открываете файл по пути UNC или подключенному диску, сопоставленному с другой папкой в ​​общем ресурсе, например \\MyServer\MyShare\MyFolder2.

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

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

См.

также

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

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

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

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

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

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

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

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

Я работаю над книгой 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, чтобы обновить путь к файлу изображения. Я переместил свои файлы из /Desktop в /Dropbox. Я заменил «рабочий стол» на «дропбокс», и через несколько секунд все мои пути к изображениям обновились.