На этом шаге мы рассмотрим абсолютные и относительные ссылки.
По умолчанию Excel создает в формулах относительные ссылки. На рисунке 1 показана рабочая таблица с формулой, введенной в ячейку D2. Эта формула, в которой по умолчанию используются относительные ссылки, выглядит следующим образом: = В2 * С2.
Рис. 1. Пример рабочей таблицы с формулой, введенной в ячейку D2
Если скопировать эту формулу в две ячейки, расположенные под ячейкой D2, то Excel вставит копии приведенной формулы, при этом видоизменит их следующим образом:
- В ячейку D3 будет скопирована формула = В3 * СЗ;
- В ячейку D4 будет скопирована формула = В4 * С4.
Другими словами, Excel изменяет ссылки на ячейки в соответствии с новым положением формулы.
Если вырезать и вставить формулу(т.е. переместить ее на другое место), то содержащиеся в ней адреса ячеек изменены не будут.
Однако бываю случаи, когда необходимо, чтобы адреса ячеек копировались без изменений. Для этого используют абсолютные ссылки. Пример формулы, содержащей абсолютную ссылке, показан на рисунке 2.
Рис. 2. Формула, в которой используется абсолютная ссылка на ячейку B6
В данном примере в ячейке В6 содержится значение ставки налога на продажу. Формула в ячейке D2 выглядит следующим образом: = В2 * С2 * $В$б
В ссылке на ячейку В6 перед буквой столбца и перед номером строки стоят знаки доллара. Это означает, что данная ссылка является абсолютной. При копировании этой формулы в две ячейки снизу будут получены следующие формулы.
- В ячейку D3 будет скопирована формула = B3 * C3 * $B$6;
- В ячейку D4 будет скопирована формула = B4 * C4 * $B$6.
Как видно из примера, относительные ссылки изменились, тогда как ссылка на ячейку B6 осталась прежней, поскольку она является абсолютной.
В абсолютной ссылке используется два знака доллара: один — перед именем столбца, а другой —перед номером строки. Однако в Excel существуют также смешанные ссылки, в которых только одна часть адреса (относящаяся к строке или ко столбцу) является абсолютной. Приведем все возможные типы ссылок на ячейки:
- A1 - относительная ссылка;
- $A$1 - абсолютная ссылка;
- $A1 - смешанная ссылка (абсолютной является координата столбца);
- A$1 - смешанная ссылка (абсолютной является координата строки).
На рисунке 3 показана таблица, в каждой ячейке которой должно находиться значение, взятое из столбца А, умноженное на значение из строки 1. Формула в ячейке В2 выглядит следующим образом: = B$1 * $A2.
Рис. 3. Пример использования смешанных ссылок на ячейки
Приведенная формула содержит две смешанные ссылки на ячейки. В ссылке B$1 координата строки является абсолютной, тогда как координата столбца — относительной. А в ссылке $А2, наоборот, координата строки является относительной, тогда как координата столбца — абсолютной. Данную формулу можно скопировать в диапазон В2 : Е5, и в каждой ячейке будут содержаться правильные формулы. Например, формула в ячейке Е5 будет выглядеть следующим образом: = Е$1 * $А5.
Неотносительные (т.е. абсолютные или смешанные) ссылки можно ввести вручную, вставив в нужных местах знаки доллара. Можно также воспользоваться удобным клавиатурным эквивалентом — клавишей F4. При вводе ссылки на ячейку — либо вручную, либо путем, указания — нужно нажать клавишу F4 несколько раз, чтобы программа "прокрутила" по циклу все четыре типа ссылок.
Например, если в начале формулы ввести = A1, то первое нажатие клавиши F4 преобразует ссылку на ячейку в $А$1, второе — в А$1, третье - в $А1, а четвертое вернет ей первоначальный вид — А1. Нажимать клавишу F4 необходимо до тех пор, пока не появится нужный тип ссылки.
На следующем шаге мы рассмотрим организацию ссылок на ячейки за пределами текущего рабочего листа.
