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

описание, характеристика / Справочник :: Бингоскул

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

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

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

Давайте разберемся в том, что собой представляет ссылка в таблицах Excel. Для более понятного разъяснения можно сказать о том, что ссылка является фактическим адресом той или иной ячейки. Вы указываете адрес ссылки в одной ячейке, чтобы та взяла информацию с другой и использовала ее для расчетов. Например, можно взять простую формулу «=А1», которую вы вводите в ячейке «С5». Эта формула выполняет простое действие, информация с ячейки А1 будет полностью скопирована в С5. Вы создали простую ссылку, которая позволила одной ячейке найти адрес другой и скопировать там данные. В этом и заключается вся суть работы ссылок в таблицах Excel. Грамотно оперируя ссылками, можно создавать формулы любой сложности, автоматизировать практически любые процессы и создавать функциональные базы данных.

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

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

Если говорить простыми словами, то это самая простая ссылка, которая имеет в своем составе букву и номер. Она несет в себе информацию об определенной ячейке в столбце и строке. Отображается относительная ссылка в Excel просто: А1, В2, Е5, К14 и т. д. Особенностью данных ссылок является то, что если вы копируете информацию с определенной ячейки, то формулы, которые находятся внутри нее, меняют фактический адрес и переходят в новую ячейку, куда приводит указанный адрес ссылки. Все ссылки в Excel по умолчанию являются относительными, поэтому пользователи чаще всего и применяют данный тип во время работы.

Во всем этом можно разобраться на простых примерах, которые вы проделаете у себя в электронных таблицах на компьютере. Допустим, что в ячейке В1 у вас есть формула типа = A1*10. Если вы берете и просто копируете формулу в соседнюю ячейку В2, то формула будет изменена, теперь она будет скорректирована для того, чтобы все работало во второй ячейке. По сути, программа “думает” о том, что вы собираетесь умножать значения на 10 в каждой ячейке столбца А. В какую бы строку и столбец вы не копировали данную формулу, она будет меняться в зависимости от своего текущего местоположения. При этом, если вы копируете формулу с относительной ссылкой в другую ячейку той же строки, то меняются параметры только строки, если же меняется и столбец, то оба параметра будут подогнаны к новому местоположению формулы.  

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

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

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

Например, у вас в ячейке А1 имеется значение 20, вы используете абсолютный адрес ячейки Excel и создаете формулу типа $A$1+10. Независимо от того, в какой бы ячейке вы ни разместили данную формулу, получаться будет числовое значение равное 30. Если в относительных ссылках выполнялось то действие, которое должно быть выполнено в новой ячейке, то при абсолютной ссылке все наоборот, выполняется действие соответствующее первоисточнику. Следует сразу отметить тот факт, что в реальной жизни пользователи чрезвычайно редко используют абсолютные ссылки, ими пользуются только лишь при сложных бухгалтерских вычислениях, и даже там они применяются изредка. 

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

---

 

Смотри также: 

• Назначение и возможности табличного процессора Excel
• Что представляет собой форматирование ячеек, таблиц в 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, то есть сумма итогового вклада с процентами минус сумма начального вклада.

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

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

Описание управления связями и хранением книг в 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 месяца назад

Изменено 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, чтобы обновить путь к файлу изображения.