Изменение типа ссылки: относительная, абсолютная, смешанная
По умолчанию ссылка на ячейку является относительной ссылкой, которая означает, что ссылка относительна к расположению ячейки. Например, если вы ссылаетесь на ячейку A2 из ячейки C2, вы фактически ссылаетесь на ячейку, которая находится на два столбца слева (C минус A) в одной строке (2). При копировании формулы, содержаной относительную ссылку на ячейку, эта ссылка в формуле изменится.
Например, при копировании формулы =B4*C4 из ячейки D4 в D5 формула в ячейке D5 корректируется на один столбец вправо и становится =B5*C5. Если вы хотите сохранить исходную ссылку на ячейку в этом примере при копировании, необходимо сделать ссылку на ячейку абсолютной, предшествуя столбцам (B и C) и строке (2) знаком доллара($). Затем при копировании формулы =$B$4*$C$4 из D4 в D5 формула остается той же.
В меньшей степени может потребоваться смешанные абсолютные и относительные ссылки на ячейки, предшествуя столбецу или значению строки знаком доллара, что исправит столбец или строку (например, $B 4 или C$4).
Чтобы изменить тип ссылки на ячейку, выполните следующее.
- Выделите ячейку с формулой.
- В строке формул строка формул выделите ссылку, которую нужно изменить.
- Для переключения между типами ссылок нажмите клавишу F4. В приведенной ниже таблице по сумме обновляется тип ссылки при копировании формулы, содержащей ссылку, на две ячейки вниз и на две ячейки справа.
Копируемая формула
Первоначальная ссылка
Новая ссылка
$A$1 (абсолютный столбец и абсолютная строка)
$A$1 (абсолютная ссылка)
A$1 (относительный столбец и абсолютная строка)
C$1 (смешанная ссылка)
$A1 (абсолютный столбец и относительная строка)
$A3 (смешанная ссылка)
A1 (относительный столбец и относительная строка)
C3 (относительная ссылка)
Excel: Ссылки относительные и абсолютные
Часто при использовании формул в Excel после ввода формулы в одну ячейку необходимо скопировать или распространить ее на блок ячеек.
При копировании формул возникает необходимость управлять изменением адресов ячеек или ссылок.
Ссылка в Excel — адрес ячейки или связного диапазона ячеек.
Адрес ячейки определяется пересечением столбца и строки, например: A1, C16.
Адрес диапазона ячеек задается адресом верхней левой ячейки и нижней правой, например: A1:C5.
Ссылки в Excel бывают 3-х типов:
- Относительные ссылки (пример: A1);
- Абсолютные ссылки (пример: $A$1);
- Смешанные ссылки (пример: $A1 или A$1).
Относительные ссылки
«Относительность» ссылки означает, что из данной ячейки ссылаются на ячейку, отстоящую на столько-то строк и столбцов относительно данной.
Пример.
В ячейке А6 формула ссылается на две ячейки (С3 и С4), отстоящие от данной на два столбца вправо и на три (С3) и две (С4) ячейки выше.
При копировании или «протаскивании» c помощью Маркера заполнения формулы, например, в ячейку А7 формула изменяется (Excel пересчитывает адреса всех относительных ссылок в ней в соответствии с новым положением ячейки).
Теперь формула в ячейке А7 ссылается на ячейки С4 и С5. Названия ссылок изменились, но осталось неизменным их положение относительно ячейки, в которой находится формула (два столбца вправо и на три (С4) и две (С5) ячейки выше).
Относительные ссылки целесообразно использовать в формулах в двух случаях:
- Если формулу не предполагается копировать в другие ячейки.
- Если формулу необходимо скопировать в идентичные ячейки.
Абсолютные ссылки
Если формула требует, чтобы адрес ячейки оставался неизменным при копировании, то должна использоваться абсолютная ссылка. Для этого перед символами ссылки устанавливаются символы «$» (формат записи $А$1).
Абсолютные ссылки в формулах используются в случаях:
- Необходимости применения в формулах констант.
- Необходимости фиксации диапазона для проведения расчетов.
Пример.
В диапазоне А1:А5 указаны зарплаты сотрудников отдела, а в С1 – процент премии, установленный для всего отдела. Подсчитаем премию каждого сотрудника и поместим в диапазоне В1:В5.
Для расчета премии первого сотрудника введем в ячейку В1 формулу =А1*С1.
Если мы с помощью Маркера заполнения протянем формулу вниз, то получим в ячейке В2 формулу =А2*С2, в ячейке В3 — =А3*С3 и т.д. Так как в ячейках диапазона С2:С5 нет значений, то в диапазоне В2 : В5 получаем нули.
Для исправления ошибки, необходимо зафиксировать в формуле ссылку на ячейку С1, т.е. заменить относительную ссылку С1 на абсолютную $C$1.
- выделите ячейку В1
- в Строке формул поставьте знак «$» перед буквой столбца и адресом строки $С$1. Более быстрый способ — в Строке формул поставьте курсор на ссылку С1 (можно перед С, перед или после 1) и нажмите один раз клавишу «F4». Ссылка С1 выделится и превратится в $C$1.
- нажмите ENTER
Формула приняла вид « =А1*$С$1».
Маркером заполнения протяните полученную формулу вниз.
Теперь диапазон В2: В5 заполнен значениями премий сотрудников.
Быстрый способ сделать относительную ссылку абсолютной — выделить относительную ссылку и нажать один раз клавишу «F4», при этом 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 абсолютных или относительных ссылок результат окажется неверным.
Типы ссылок на ячейки в формулах Excel
Если вы работаете в Excel не второй день, то, наверняка уже встречали или использовали в формулах и функциях Excel ссылки со знаком доллара, например $D$2 или F$3 и т.п. Давайте уже, наконец, разберемся что именно они означают, как работают и где могут пригодиться в ваших файлах.
Относительные ссылки
Это обычные ссылки в виде буква столбца-номер строки ( А1, С5, т.е. «морской бой»), встречающиеся в большинстве файлов Excel. Их особенность в том, что они смещаются при копировании формул. Т.е. C5, например, превращается в С6, С7 и т.д. при копировании вниз или в D5, E5 и т.д. при копировании вправо и т.д. В большинстве случаев это нормально и не создает проблем:
Смешанные ссылки
Иногда тот факт, что ссылка в формуле при копировании «сползает» относительно исходной ячейки — бывает нежелательным. Тогда для закрепления ссылки используется знак доллара ($), позволяющий зафиксировать то, перед чем он стоит. Таким образом, например, ссылка $C5 не будет изменяться по столбцам (т.е. С никогда не превратится в D, E или F), но может смещаться по строкам (т.е. может сдвинуться на $C6, $C7 и т.д.). Аналогично, C$5 — не будет смещаться по строкам, но может «гулять» по столбцам. Такие ссылки называют смешанными:
Абсолютные ссылки
Ну, а если к ссылке дописать оба доллара сразу ($C$5) — она превратится в абсолютную и не будет меняться никак при любом копировании, т.е. долларами фиксируются намертво и строка и столбец:
Самый простой и быстрый способ превратить относительную ссылку в абсолютную или смешанную — это выделить ее в формуле и несколько раз нажать на клавишу F4. Эта клавиша гоняет по кругу все четыре возможных варианта закрепления ссылки на ячейку: C5 → $C$5 → $C5 → C$5 и все сначала.
Все просто и понятно. Но есть одно «но». Предположим, мы хотим сделать абсолютную ссылку на ячейку С5. Такую, чтобы она ВСЕГДА ссылалась на С5 вне зависимости от любых дальнейших действий пользователя. Выясняется забавная вещь — даже если сделать ссылку абсолютной (т.е. $C$5), то она все равно меняется в некоторых ситуациях. Например: Если удалить третью и четвертую строки, то она изменится на $C$3. Если вставить столбец левее С, то она изменится на D. Если вырезать ячейку С5 и вставить в F7, то она изменится на F7 и так далее. А если мне нужна действительно жесткая ссылка, которая всегда будет ссылаться на С5 и ни на что другое ни при каких обстоятельствах или действиях пользователя?
Действительно абсолютные ссылки
Решение заключается в использовании функции ДВССЫЛ (INDIRECT) , которая формирует ссылку на ячейку из текстовой строки. Если ввести в ячейку формулу: =ДВССЫЛ(«C5») =INDIRECT(«C5») то она всегда будет указывать на ячейку с адресом C5 вне зависимости от любых дальнейших действий пользователя, вставки или удаления строк и т.д. Единственная небольшая сложность состоит в том, что если целевая ячейка пустая, то ДВССЫЛ выводит 0, что не всегда удобно. Однако, это можно легко обойти, используя чуть более сложную конструкцию с проверкой через функцию ЕПУСТО: =ЕСЛИ(ЕПУСТО(ДВССЫЛ(«C5″));»»;ДВССЫЛ(«C5»)) =IF(ISBLANK(INDIRECT(«C5″));»»;INDIRECT(«C5»))
Ссылки по теме
- Трехмерные ссылки на группу листов при консолидации данных из нескольких таблиц
- Зачем нужен стиль ссылок R1C1 и как его отключить
- Точное копирование формул макросом с помощью надстройки PLEX