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

Абсолютные и относительные ссылки Excel: Как с ними работать[2019]

Пользуясь программой Excel, вы должны знать, что здесь можно пользоваться формулами. Абсолютные и относительные ссылки упростят работу, а как ими пользоваться – читайте в нашей статье!

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

Однако вы не переживайте – мы объясним все сложное максимально просто.

Содержание:

Абсолютная ссылка – что это?

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

Формулы со знаком доллара и их значение

В большинстве случаев в процессе создания формул, структура которых включает в себя абсолютную ссылку, пользователь прибегает к применению следующего формата: $A$2. Все остальные форматы, приведенные в изображении выше, хоть и используются на практике, но довольно редко.

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

к содержанию ↑

Создаем формулу, используя абсолютные ссылки

Разберем конкретный пример. Ячейка E1 будет содержать налоговую ставку 7.5 процента для определения налога с продаж для каждой позиции, введенной в столбец D.

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

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

Выделение ячейки

  • Вводим выражение, вычисляющее результат. Формула получит следующий вид: =(B3*C3)*$E$1.

Ввод выражения для вычисления результата

  • После нажатия клавиши «Enter»программа вычислит формулу, после чего мы увидим результат.
  • В правой нижней части найдите маркер автоматического заполнения, как показано на примере.

Использование маркера автозаполнения

  • Нажмите и удерживайте ЛКМ, перетащив маркер автоматического заполнения по оставшимся ячейкам. В этом примере речь идет о
    диапазоне D4:D13
    .

Копирование формулы во все оставшиеся ячейки

  • Отпустив ЛКМ, формула скопируется в ячейки с абсолютными ссылками, каждая из которых отобразит результаты вычислений.

Отображение результатов при использовании формулы

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

Проверка правильности формулы

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

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

Пример ошибки в вычислениях, когда формула не содержит знака доллара

к содержанию ↑

Относительная ссылка – что это?

Любая ссылка в Excel является относительной по умолчанию. В ходе копирования формулы они могут меняться, опираясь на относительное расположение столбцов и строк. Рассмотрим пример: копируя формулу =A1+B1 во вторую строку из первой, формула автоматически получит следующий вид: =A2+B2.

Использование относительных ссылок всегда удобно при необходимости продублировать идентичные расчеты по нескольким столбцам и/или строкам.

к содержанию ↑

Создание и копирование формул с относительными ссылками

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

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

Выделение ячейки

  • Чтобы вычислить необходимое значение вам нужно ввести выражение. В этой ситуации оно имеет следующий вид: =B2*C2.

Указание выражения для проведения вычислений

  • Нажимаем кнопку «Enter» на клавиатуре. Программа автоматически вычислит формулу, после чего отобразится итоговый результат.
  • Ищем маркер автоматического заполнения – он находится справа внизу нужной нам ячейки. Пример ниже задействует маркер автоматического заполнения для ячейки D2.

Использование маркера автозаполнения

  • Нажимаем ЛКМ и удерживаем ее, параллельно перетаскивая маркер по всем оставшимся ячейкам. Наш конкретный пример демонстрирует вычисление в ячейках с D3 по D12.

Копирование маркера автозаполнения по всем оставшимся ячейкам

  • Отпускаем ЛКМ. Формула автоматически скопируется в ячейки, содержащие относительные ссылки, и каждая из них получит вычисленные значения.

Получение итоговых результатов вычислений

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

Проверка правильности результатов с использованием формулы

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 решает использовать абсолютный путь для внешних ссылок, а не относительный путь

спросил

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

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

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

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

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

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

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

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

  • Microsoft-Excel-2013

3

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

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

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

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

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

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

1

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

Чтобы сделать это поведение по умолчанию для всех новых книг, измените/создайте шаблон книги по умолчанию. В общем нужно создать book.xltx в папке автозагрузки Excel (различается в разных версиях). Подробнее о шаблонах по умолчанию здесь https://exceljet.net/how-to-set-a-default-template-in-excel

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

1

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

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

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

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

HTH

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

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

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

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

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

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

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

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

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

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

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

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

.

простых относительных ссылок во внешних ссылках (Microsoft Excel)

Автор Allen Wyatt (последнее обновление 14 мая 2022 г.)
Этот совет относится к Excel 97, 2000, 2002 и 2003


Майк интересуется, есть ли способ использовать ссылки на информацию во внешней рабочей книге относительные ссылки. Например, если есть ссылка на ячейку в другой книге, для нее автоматически устанавливается явный путь, например c:\fullpath\otherfile.xls. Для целей Майка было бы лучше, если бы это был не явный путь, а просто другой файл.xls. Таким образом, он мог перемещать файлы и иметь ссылку на внешний файл, который находится относительно того, где находится моя текущая книга.

В Excel это невозможно сделать. Ссылки создаются при сохранении связанного файла. Когда открыты обе книги (та, на которую ссылается ссылка, и та, на которую ссылается ссылка), ссылки ссылаются только на имя файла. Полный путь сохраняется только при сохранении связанного файла.

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

Советы по Excel — ваш источник экономичного обучения работе с Microsoft Excel. Этот совет (7921) относится к Microsoft Excel 97, 2000, 2002 и 2003. всемирно признанный автор. Он является президентом Sharon Parq Associates, компании, предоставляющей компьютерные и издательские услуги. Узнать больше об Аллене…

Экономьте время и повышайте производительность Excel! Автоматизируйте практически любую рутинную задачу и сэкономьте часы, дни, а может быть, и недели. Затем узнайте, как заставить Excel делать то, что вы считали просто невозможным! Освоение расширенных макросов Excel никогда не было проще. Ознакомьтесь с Excel 2010 VBA и макросами уже сегодня!

Подписаться

БЕСПЛАТНАЯ СЛУЖБА: Получайте такие советы каждую неделю в ExcelTips, бесплатном информационном бюллетене по продуктивности. Введите свой адрес и нажмите «Подписаться».

Просмотреть последний информационный бюллетень.

(Ваш адрес электронной почты никому никогда не передается.)

Комментарии

Этот сайт

Есть версия Excel, которая использует интерфейс меню (Excel 97, Excel 2000, Excel 2002 или Excel 2003)? Этот сайт для вас! Если вы использовать более позднюю версию Excel, посетите наш сайт ExcelTips посвящен ленточному интерфейсу.

Новейшие советы

Подписаться

БЕСПЛАТНАЯ СЛУЖБА: Получайте подобные советы каждую неделю в ExcelTips, бесплатном информационном бюллетене по продуктивности.

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

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

Ваш адрес email не будет опубликован. Обязательные поля помечены *