Какие типы ссылок используются в 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
Виды ссылок в Excel
Достаточно написать формулу только в одной ячейке (C1 в примере), а далее протянуть ячейку в нужном направлении. При этом автоматически A1 будет заменено на A2, B1 на B2 и так далее.
Абсолютные ссылки
Если в формуле необходимо сослаться на конкретную ячейку без ее изменения при перетаскивании, необходимо поставить символ $ перед названием столбца и строки.
В примере формула написана в ячейке B2 и протянута вниз. Видно, что при этом ссылка на ячейку в столбце A автоматически меняется, но ссылка на ячейку E1 остается неизменной.
Смешанные ссылки
Включают в себя оба варианта одновременно.
Это актуально, когда необходимо протянуть ячейку на несколько столбцов или строк.
В примере формула написана в ячейке B3 и протянута как вправо, так и вниз.
При этом мы видим, что закреплен только для Длины закреплен только столбец (символ $ перед A), но строки не закреплены. А для значения Ширины наоборот – закреплена только строка 2, при этом столбцы изменяются автоматически.
Переключение между различными видами ссылок в Excel
Для того, чтобы переключиться между различными видами ссылок, необходимо после указания ячейки в формуле нажать клавишу F4.
Переключение происходит по следующей схеме: A2 → $A$2 → $A2 → A$2 и далее по кругу.
В уже готовой формуле необходимо поставить курсор в ссылку на ячейку (или сразу после нее) и также нажать F4.
Копирование формул и типы ссылок в Microsoft Excel:
Расписание ближайших групп:
Ссылки в формулах. Типы ссылок
Относительная ссылка – это ссылка в формуле, основанная на относительном расположении ячейки, в которой находится формула, и ячейки, на которую указывает ссылка. При этом при изменении позиции ячейки с формулой соответственно изменяется и ссылка на связанную ячейку. Так что, например, при копировании формулы вдоль столбцов или строк ссылка автоматически корректируется с учетом перемещения ячейки с формулой. Данный тип ссылок используется по умолчанию.
Абсолютные ссылки
Абсолютная ссылка – это неизменная ссылка в формуле на ячейку, расположенную в определенном месте. При перемещении ячейки с формулой адрес ячейки с абсолютной ссылкой не корректируется. Абсолютная ссылка указывается символом $.
Например, абсолютная ссылка на ячейку $A$1 указывает на неизменность адреса ячейки А1 при копировании формулы вдоль столбца или строки.
Смешанные ссылки
Смешанная ссылка – это ссылка с использованием либо абсолютной ссылки на столбец и относительной – на строку ($A1), либо абсолютной ссылки на строку и относительной – на столбец (A$1).
При этом при изменении позиции ячейки с формулой относительная ссылка строки или столбца изменяется, а абсолютная часть ссылки остается прежней.
Стиль трехмерных ссылок
Трехмерные ссылки – это ссылки на одну и ту же ячейку или диапазон ячеек, расположенные на нескольких листах одной книги. При этом трехмерная ссылка включает в себя имя листа.
Например, трехмерная ссылка Лист1:Лист5!А1 указывает на все ячейки А1, расположенные с Листа1 по Лист5.
Трехмерные ссылки нельзя использовать в формулах массива (см. далее пункт «Как создать формулу массива?»), а также сочетать с оператором.
При добавлении или удалении листов, попадающих в диапазон листов трехмерной ссылки, автоматически происходит учет всех изменений. То есть новые данные, расположенные на ячейках вставляемых или удаляемых листов, прибавляются или вычитаются.