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

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

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

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

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

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

Что такое ссылка на ячейку?

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

Например, если вы введете простую формулу =A1 в ячейку C1, Excel вытащит значение из ячейки A1 в C1:

Абсолютные и относительные ссылки в Excel – Ссылка на ячейку в Excel

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

Абсолютные и относительные ссылки в Excel – Использование $ в формулах Excel

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

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

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

Предположим, что у вас есть следующая формула в ячейке B1:

Если вы скопируете эту формулу в другую строку в том же столбце, например, в ячейку B2, формула будет корректироваться для строки 2 (A2*10), потому что Excel предполагает, что вы хотите умножить значение в каждой строке столбца А на 10.

Абсолютные и относительные ссылки в Excel – Относительная ссылка в Excel

Если вы копируете формулу с относительной ссылкой на ячейку в другой столбец в той же строке, Excel соответственно изменит ссылку на столбец :

Абсолютные и относительные ссылки в Excel – Копирование формулы с относительной ссылкой в другой столбец

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

Абсолютные и относительные ссылки в Excel – Копирование формулы с относительной ссылкой в другой столбец и другую строку

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

Пример относительных ссылок в Excel

Пусть у нас есть электронная таблица, в которой отражены наименование, цена, количество и стоимость товаров.

Абсолютные и относительные ссылки в Excel – Исходные данные

Нам нужно рассчитать стоимость для каждого товара. В ячейке D2 введем формулу, в которой перемножим цену товара А и количество проданных единиц. Формула в ячейке D2 ссылается на ячейку B2 и C2, которые являются относительными ссылками. При перетаскивании маркера заполнения вниз на ячейки, которые необходимо заполнить, формула автоматически изменяется.

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

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

Абсолютные и относительные ссылки в Excel – Относительные ссылки (режим формул)

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

Абсолютная ссылка в Excel

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

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

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

Например, если у вас есть значение 10 в ячейке A1, и вы используете абсолютную ссылку на ячейку ($A$1), формула = $A$1+5 всегда будет возвращать число 15, независимо от того, в какие ячейки копируется формула. С другой стороны, если вы пишете ту же формулу с относительной ссылкой на ячейку (A1), а затем скопируете ее в другие ячейки в столбце, для каждой строки будет вычисляться другое значение. Следующее изображение демонстрирует разницу абсолютных и относительных ссылок в MS Excel:

Абсолютные и относительные ссылки в Excel – Разница между абсолютными и относительными ссылками в Excel

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

Пример использования абсолютной и относительных ссылок в Excel

Пусть в рассматриваемой выше электронной таблице необходимо дополнительно рассчитать десятипроцентную скидку. В ячейке Е2 вводим формулу =D2*(1-$H$1). Ссылка на ячейку $H$1 является абсолютной ссылкой на ячейку, и она не будет изменяться при заполнении других ячеек.

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

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

Читайте также  Факторный анализ в excel как сделать

Абсолютные и относительные ссылки в Excel – Переключение между относительной, абсолютной ссылкой и смешанными ссылками

Или вы можете сделать абсолютную ссылку, введя символ $ вручную с клавиатуры.

Если вы используете Excel for Mac, то для преобразования относительной в абсолютную ссылку или в смешанные ссылки используйте сочетание клавиш COMMAND+T.

Абсолютные и относительные ссылки в Excel – Абсолютная ссылка (режим формул)

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

Смешанные ссылки в Excel

Смешанные ссылки используют, когда необходимо закрепить адрес ячейки только по строке или только по столбцу. Смешанные ссылки могут быть абсолютными по столбцу и относительными по строке (например, $A1), или относительными по столбцу и абсолютными по строке (например, A$1).

Как вы помните, абсолютная ссылка в Excel содержит 2 знака доллара ($), которые блокируют как столбец, так и строку. В смешанной ссылке фиксируется только одна координата (абсолютная), а другая (относительная) изменяется в зависимости от относительного положения строки или столбца:

  • Абсолютный столбец и относительная строка, например $A1. Когда формула со смешанной ссылкой копируется в другие ячейки, знак $ перед буквой столбца блокирует ссылку на указанный столбец, чтобы он никогда не менялся. Ссылка относительной строки, без знака доллара, изменяется в зависимости от строки, в которую копируется формула.
  • Относительный столбец и абсолютная строка, например A$1. В этой смешанной ссылке ссылка на строку не изменится, а ссылка на столбец будет меняться.

Абсолютные и относительные ссылки в Excel – Смешанные ссылки

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

Пример смешанных ссылок в Excel

Если нам нужно узнать какая будет стоимость с учетом 10%, 25% и 30% скидки, то в ячейку Е3 вводим формулу =$D3*(1-E$2), фиксируя таким образом столбец D (стоимость) и строку 2 (скидку). В данной формуле используются две смешанные ссылки:

$D3 — Абсолютный столбец и относительная строка

E$2 — Относительный столбец и абсолютная строка

Абсолютные и относительные ссылки в Excel – Пример использования смешанных ссылок

Заполняем ячейки по столбцам и по строкам. В режиме формул расчеты со смешанными ссылками будут выглядеть так:

Абсолютные и относительные ссылки в Excel – Пример использования смешанных ссылок (режим формул)

При заполнении диапазона E3:G6 такая формула со смешанными ссылками дает соответствующие значения в каждой ячейке.

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

Источник:
http://naprimerax.org/absoliutnye-i-otnositelnye-ssylki-v-excel

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

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

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

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

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

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

Допустим, у нас есть таблица, в которой заполнены столбцы с ценой за единицу товара и количеством реализованной продукции. Наша задача – вычислить сумму по каждому наименованию в соответствующей колонке.

Вот что нам нужно сделать:

  1. Переходим в самую верхнюю ячейку результирующего столбца (не считая шапки таблицы), ставим знак “равно” (“=”) и пишем в ней формулу: = B2*C2 .
  2. Когда выражение готово, нажимаем клавишу Enter на клавиатуре, после чего получаем результат в ячейке с формулой.

Возможные ошибки при работе с относительными ссылками

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

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

  1. Встаем в первую ячейку столбца для расчетов, где пишем формулу: =D2/D13 .
  2. Нажимаем Enter, чтобы получить результат. После того, как мы скопируем формулу на оставшиеся ячейки столбца, вместо результатов увидим следующую ошибку: #ДЕЛ/0! .

Дело в том, что из-за того, что все ссылки на ячейки в формуле, которую мы скопировали, относительные, координаты в последующих ячейках сдвинулись. Т.е. для ячейки E3 формула выглядит следующим образом: =D3/D14 . Но, как мы видим, ячейка D14 – пустая, из-за чего программа и выдает ошибку, информирующую о том, что делить на цифру нельзя.

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

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

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

По умолчанию, все ссылки в формулах Эксель относительные, поэтому, чтобы сделать их абсолютными, выполняем следующие действия:

  1. Для начала пишем формулу в привычном виде в требуемой ячейке. В нашем случае она выглядит так: = D2/D13 .
  2. Когда формула готова, не спешим нажимать клавишу Enter. Теперь нам нужно зафиксировать координаты ячейки D13. Для этого перед названием столбца и порядковым номером строки печатаем символ “$”. Или же можно просто после ввода адреса сразу нажать клавишу F4 на клавиатуре (курсор может находиться до, после или внутри координат). В итоге формула должна выглядеть следующим образом: D2/$D$13 .
  3. Теперь можно нажать Enter, чтобы вывести результат в ячейку.
  4. Остается только скопировать формулу с помощью маркера заполнения на нижние строки. На этот раз, благодаря тому, что мы зафиксировали ячейку с итоговой суммой, результат появится и в других ячейках.

Смешанные ссылки

Помимо ссылок, рассмотренных выше, в Excel также предусмотрены смешанные ссылки – когда при копировании формулы меняется одна из координат ячейки (столбец или номер строки).

  • Если мы напишем ссылку как “$G5”, это означает, что будет меняться строка, а столбец будет зафиксирован.
  • Если мы укажем “G$5”, в этом случае, фиксироваться будет номер строки, в то время, как столбец будет меняться.

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

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

Заключение

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

Источник:
http://microexcel.ru/absolyutnye-i-otnositelnye-ssylki/