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

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

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

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

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

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

Например, в ячейку А1 введена формула =В1*2.

При копировании формулы в ячейку А2 она будет автоматически откорректирована на формулу =В2*2.

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

Если мы скопируем формулу в ячейку С1, формула превратится в =D1*2. Теперь корректировка коснулась столбца, а смещение осталось прежним.

По умолчанию, если не указано обратное, для адресации ячеек в формулах Excel используются относительные ссылки, позволяющие при копировании формул автоматически изменять адреса, на которые они ссылаются. Относительные ссылки записываются простым указанием номеров строк и букв столбцов ячеек, например А2, С38, AD23 и т. д. Если в ячейке С11 содержится формула =СУММ(C1:С10), то при копировании этой формулы в ячейку D11 она автоматически преобразуется в формулу =СУММ(D1:D10). Если необходимо просуммировать данные в нескольких столбцах, то достаточно ввести формулу суммирования только один раз, используя относительные ссылки, а затем скопировать введенную формулу под все суммируемые столбцы. При этом ссылки в формулах поменяются так, как описано ранее. Относительные ссылки удобны в большинстве случаев, поэтому применяются наиболее часто.

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

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

Для создания абсолютной ссылки удобно использовать клавишу абсолютной ссылки F4, которая осуществляет преобразование относительной ссылки в абсолютную и наоборот.

Знак доллара ($) появится как перед ссылкой на столбец, так и перед ссылкой на строку (например, $С$2), Последовательное нажатие F4 будет добавлять или убирать знак перед номером столбца или строки в ссылке (С$2 или $С2 – так называемые смешанные ссылки).

Абсолютные ссылки при копировании не корректируются.

Формула из нашего примера в абсолютной нотации будет выглядеть так: =$B$1*2.

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

Вводить абсолютные ссылки просто – при вводе формулы после ввода ссылки (обычно это делается мышью – Вы открываете формулу знаком “=” и щелкаете мышью нужную ячейку. В формуле появляется относительная ссылка) нужно нажать клавишу F4. Ссылка преобразуется в абсолютную.

F4 – “закрепить” ссылку, преобразовать ее в абсолютную нотацию.

Повторное нажатие F4 преобразует ссылку в смешанную. Преобразования происходят в такой последовательности:

А1 > $A$1 > $A1 > A$1 > A1

операционный система ссылка глобальный

и дальше по кругу.

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

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

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

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

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

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

Так, при копировании формулы из активной ячейки С1, содержащей относительные ссылки на ячейки А1 и В1, в ячейку D2 имена столбцов и номера строк в формуле изменятся на один шаг соответственно вправо и вниз. При копировании формулы в ячейку ЕЗ имена столбцов и номера строк в формуле изменятся на два шага соответственно вправо и вниз и т.д. (табл. 1).

Таблица 1 – Относительные ссылки (из ячейки С1 формула скопирована в ячейки D2 и Е3)

А	В	С	D	Е
1	=A1*B1
2	=B2*C2
3	=C3*D3

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

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

Так, при копировании формулы из активной ячейки С1, содержащей абсолютные ссылки на ячейки $А$1 и $В$1, значения столбцов и строк в формуле не изменятся (табл. 2).

Таблица 2 – Относительные ссылки (из ячейки С1 формула скопирована в ячейки D2 и Е3)

А	В	С	D	Е
1	=$А$1*$В$1
2	=$А$1*$В$1
3	=$А$1*$В$1

В формулах можно использовать смешанные ссылки, в которых координата столбца абсолютная, а строки – относительная, или, наоборот, координата столбца относительная, а строки – абсолютная. Так, если символ доллара стоит перед буквой (например: $A1), то координата столбца абсолютная, а строки – относительная. Если символ доллара стоит перед числом (например, А$1), то, наоборот, координата столбца относительная, а строки з абсолютная. Такие ссылки называются смешанными.

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

Абсолютная ссылка – это не изменяющаяся при копировании и перемещении формулы адрес ячейки, содержащий исходное данное (операнд).

Те пользователи, которые работают с программой 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 или А$1. Это дает возможность сохранить адресацию на столбец или строку, однако продолжать «скользить» по нему. Можно рассмотреть пример смешанной ссылки. Допустим, существует таблица с определенными данными. В столбце «А» есть некая величина, а в строке «1» имеется коэффициент, на который потребуется умножать. Ячейки В2, С3 содержат результаты вычислений.

Как можно заметить, абсолютная и относительная ссылки в данном случае бессильны. В таких ситуациях нужно использовать смешанный тип. Необходимо записать формулу в первую ячейку: В2=А2*В1. Чтобы переместить её вправо, придется закрепить столбец «А». Для ее перетаскивания вниз, нужно закрепить строку «1». Таким образом, правильным вид формулы будет следующий: В2=$А2*В$1. В процессе перетаскивания получается ряд значений в каждой из оставшихся ячеек:

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

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

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

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

Содержание:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читайте также:

VBA Excel: что это такое, как пользоваться объектами и применить макросы

Как объединить столбцы в Экселе: полезные советы, способы и подходы

Вычисление промежуточных итогов в Excel с примерами

Как быстро посчитать проценты в Excel (Эксель) — графическая инструкция

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Excel позволяет создать отдельные ссылки внутри документа для разных листов. Но об этом мы расскажем в других статьях.

Выводы

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

Если у вас возникают трудности с использованием рассмотренных инструментов, напишите свой комментарий. Мы же постараемся вам помочь!

Мы с вами знаем, что в умелых руках Excel — очень мощный инструмент. При произведении расчетов с помощью этого табличного редактора мы можем оперировать как содержимым ячеек так и их адресами (ссылками). И вот эти самые ссылки бывают двух типов — абсолютные и относительные. В чем же отличие этих двух способов адресации? Давайте рассмотрим на примере — очередном уроке Excel.

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

Исходная таблица

Чтобы рассчитать общую стоимость молока мы должны умножить количество товара на цену одной единицы, т. е.  содержимое ячейки C2 умножить на содержимое D2. Для этого мы можем написать в E2

=41*10

В итоге получим в ячейке E2 число 410 — это и есть стоимость 10 единиц молока.

Но, предположим, что цена молока повысилась до 43 рублей или количество изменилось с 10 до 9. Тогда нам придется вносить исправления не только в ячейках C2 и D2, но и в формуле в ячейке E2. Иначе в E2 так и останется 410, что неверно. Это не очень удобно.

Чтобы избежать подобного, мы, при написании формул, можем использовать не содержимое ячеек, а их адреса. В этом случае в E2 мы можем написать

=C2*D2

И теперь эта ячейка станет зависимой от ячеек C2 и D2. Любое изменение содержимого ячеек C2 и D2 тут же скажется на ячейке E2, ее содержимое будет пересчитано. И нам не нужно будет заниматься этим вручную.

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

Ячейка	Содержимое
E2	=C2*D2
E3	=C3*D3
E4	=C4*D4

А таблица примет вид:

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

При копировании мы не меняли вручную адреса ячеек в формулах — они менялись автоматически. Это и есть относительная адресация. При копировании формулы =C2*D2 из ячейки E2 в ячейку E3, в ячейку E3 будет помещена формула =C3*D3, т. е. адреса ячеек в формуле будут преобразованы относительно ячейки E3. Именно относительный способ адресации используется в Excel по умолчанию. Это удобно, но иногда является преградой.

Давайте добавим в нашу таблицу еще один столбец, в котором общая стоимость будет выражена в долларах. Курс доллара мы поместим в ячейку G2. Таблица будет выглядеть так:

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

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

=E2/G2

В итоге получим следующее:

Абсолютная адресация в Excel

На дробную часть можно не обращать внимание — наша цель не в этом.

Все замечательно получилось. Далее попробуем скопировать содержимое ячейки F2 в F3 чтобы получить общую стоимость хлеба в долларах. И получим ошибку #ДЕЛ/0! которая говорит, что мы пытаемся делить на ноль:

Ошибка #ДЕЛ/0!

А если мы посмотрим содержимое ячейки F3 то увидим формулу =E3/G3. А так как в ячейке E3 у нас 391, а в G3 — пусто, то Excel и выдал нам такую ошибку. Ведь он пытался использовать относительную адресацию. Давайте исправлять ситуацию.

Для того, чтобы Excel использовал не относительную адресацию, а абсолютную (т. е. не менял адрес ячейки при копировании) мы должны перед символом столбца и номером строки поставить символ $. Этот символ указывает Excel, что менять адрес этой ячейки при копировании не нужно, мы его «замораживаем».

Итак, поменяем содержимое ячейки F2 на

=E2/$G$2

Здесь мы адресуем ячейку E2 относительно, а G2 — абсолютно. В итоге при копировании в ячейку F3 ячейка E2 изменится на E3, а вот G2 так и останется — $G$2. Это нам и нужно. Скопируем содержимое ячейки F2 в F3 и F4 и увидим, что все работает правильно. Этого мы и добивались.

Итоговая таблица Excel

Обратите внимание, что так как мы заполняем ячейки ниже F2, то у ячейки G2 меняется только номер строки, а столбец остается прежним. Поэтому мы можем поставить символ $ только перед номером строки — замораживать столбец не обязательно, он и так не меняется при копировании. В итоге в ячейках F2, F3, F4 должны быть записаны формулы:

Ячейка	Содержимое
F2	=E2/G$2
F3	=E3/G$2
F4	=E4/G$2

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

Оцените статью, это очень поможет развитию сайта.