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

Ссылка на ячейку может быть относительной, абсолютной и смешанной. До сих пор мы пользовались относительными ссылками. Фактически они задают смещение ячейки, на которую производится ссылка, относительно ячейки, в которой эта ссылка указывается. По этой причине при копировании адрес ячейки, на которую производится ссылка, изменяется таким образом, чтобы смещение осталось прежним. Это очень полезное свойство, и именно благодаря ему мы имеем возможность при копировании формул избежать ручной работы. Чтобы вы лучше поняли, как работают относительные ссылки в электронных таблицах, приведем пример. Предположим, что в ячейку В2 введена такая формула:
=А1
Она указывает на ячейку, находящуюся на одну ячейку выше и левее ячейки В2 (то есть на ячейку А1). После копирования содержимого ячейки В2 в ячейку С 4 формула в ячейке С4 уже будет указывать не на ячейку А1, а на ячейку ВЗ (то есть на ячейку, расположенную на одну ячейку выше и левее С4). Таким образом, при копировании содержимого ячейки в любое место рабочего листа расположенная в ней формула будет ссылаться не на конкретную ячейку, а на ячейку, находящуюся на некотором расстоянии от ячейки с формулой. Пунктирные линии на указывают, куда переадресуется ссылка при копировании формулы из ячейки В2 в ячейки С4 и D6.
Если же произвести копирование этой формулы в любую ячейку строки 1 или столбца А, то в ячейке и строке формул появится следующая надпись:
=#ССЫЛКА!
Она означает, что дана ссылка на несуществующее место рабочего листа.
ПРИМЕЧАНИЕ

На в нижней части окна Excel находится панель Рисование, с помощью которой были нанесены стрелки.

Если вы хотите, чтобы в формуле осуществлялась ссылка на конкретную ячейку, необходимо задать абсолютную ссылку. После перемещения и копирования такой формулы ссылка на ячейку не изменяется, поскольку абсолютная ссылка задает фиксированную позицию на рабочем листе, которая находится на пересечении данного столбца и данной строки.
Признаком абсолютной ссылки является знак доллара ($). Наличие двух таких знаков означает, что в какое бы место рабочего листа мы ни копировали формулу, она не изменится:
=$А$1
Это продемонстрировано на, где формула с абсолютной ссылкой на ячейку А1 копируется из ячейки В2 в ячейки С4 и D6.
Широкие возможности предоставляют смешанные ссылки. Это ссылки с одним знаком доллара — перед именем столбца или перед номером строки. Например, если в ячейке В2 имеется формула
=А$1
то после копирования ее в любое место рабочего листа изменится лишь название столбца, а строка 1 будет присутствовать в формуле всегда. Таким образом, в данном случае мы имеем абсолютную ссылку на строку и относительную — на столбец.
Аналогично, если скопировать в другое место ячейку В2 с формулой
=$А1
то изменится только номер строки, а имя столбца останется прежним. Следовательно, здесь речь идет об абсолютной ссылке на столбец и относительной — на строку.
Приведенные ниже рисунки иллюстрируют применение в ячейке В2 абсолютной ссылки на строку и абсолютной ссылки на столбец . При копировании данной формулы в ячейки С4 и D6 получаются разные формулы.
Изменение типа ссылки производится циклически, в результате последовательных нажатий функциональной клавиши [F4] в то время, когда курсор ввода находится в тексте ссылки. Если, например, в ячейке В2 имеется ссылка на ячейку А1. то при каждом нажатии клавиши [F4] ее вид в строке формул будет изменяться: А1 — $A$1 — A$1 — $А1 — А1 — $A$1 и т. д.
Ввод формулы со смешанной ссылкой в область вычислений
Для того чтобы быстро заполнить формулами область вычислений, в ячейку В2 необходимо ввести формулу со смешанной ссылкой:
=$А2*В$1
Эту формулу можно смело копировать в другие ячейки — вычисления в таблице будут производиться правильно. При копировании в первом компоненте формулы изменяется только номер строки (=$A2, $АЗ, $А4 и т. д.), а ссылка на столбец, в котором находятся элементы сомножителя 1, остается постоянной. Во втором компоненте, наоборот, изменяется имя столбца (=С$1, D$1, Е$1 и т. д.), а ссылка на строку, где находятся элементы сомножителя 2, остается постоянной. Для ввода формулы умножения в область вычислений таблицы воспользуемся методом заполнения ячеек одинаковыми данными. Оптимальной в этом случае является такая последовательность действий:
1. Выделите диапазон ячеек В2:К11.
2. Введите с клавиатуры знак "=" и нажмите клавишу [Left], чтобы создать ссылку на ячейку А2.
3. Три раза нажмите функциональную клавишу [F4] (создание абсолютной ссылки на столбец А).
4. Введите знак "*" (умножить) и нажмите клавишу [Up] для создания ссылки на ячейку В1.
5. Два раза нажмите функциональную клавишу [F4] (создание абсолютной ссылки на строку 1).
6. Нажмите комбинацию клавиш [Ctrl+Enter].
Таблица умножения с формулами и результатами расчетов представлена на и 3.8.
Оформление таблицы
Последний этап — это придание таблице привлекательного внешнего вида и задание числового формата. Методы оформления ячеек цветом и рамками вы освоили в предыдущей главе. А что касается форматов данных, то отметим следующее. Если таблица умножения будет использоваться только для операций над целыми числами, то числовой формат можно не изменять. В случае применения сомножителей, имеющих дробную часть, можно задать необходимое количество знаков после запятой. Для этого следует вызвать диалоговое окно Формат ячеек, открыть вкладку Число, в списке Числовые форматы выбрать элемент Числовой и в соответствующем поле указать требуемое количество десятичных разрядов.
Создание области ввода в таблице умножения
Итак, с заданием, поставленным в начале главы, мы справились. Однако разработанная нами таблица обладает рядом недостатков. В частности, она предоставляет слишком ограниченные возможности в плане управления диапазоном сомножителей. Изменения можно вносить только в ячейку А1.
Давайте несколько усложним задачу. Предположим, что в таблице нужен другой шаг изменения сомножителей как по горизонтали (скажем, 0,3), так и по вертикали (к примеру, 0,2). К тому же нам нужно изменить начальные значения сомножителей (например, по вертикали начать не с единицы, а с пяти, по горизонтали-с трех). При этом желательно, чтобы обновление результатов происходило моментально.
Любая таблица должна быть понятна для стороннего пользователя. Если пользователь, открыв таковую, сразу получает информацию о том, для чего она создана и какие вычисления производит, в каких ячейках можно изменять содержимое, а в каких находятся формулы, значит, таблица составлена удачно. Модифицируем созданную таблицу умножения таким образом, чтобы в ней были учтены все перечисленные требования.

В верхней части рабочего листа над таблицей умножения разместим область ввода. Она будет состоять из шести строк:
- название области ввода;
- начальное значение для сомножителей, расположенных по вертикали;
- начальное значение для сомножителей, расположенных по горизонтали;
- шаг изменения сомножителей, расположенных по вертикали;
- шаг изменения сомножителей, расположенных по горизонтали;
- название области вычислений.