9. Абсолютные и относительные ссылки, копирование и перемещение формул

9.1. Ссылки на ячейки в формулах могут быть относительными, частично абсолютными (по столбцу либо по строке) и абсолютными. Абсолютность ссылки означает фиксацию адреса ссылки при копировании данной формулы в другие ячейки. На расчет формулы в данной ячейке вид ссылки значения не имеет.

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

Виды ссылок	Примеры
Относительные	=А2+1
Частично абсолютные	=$А2+1 (по столбцу) =А$2+1 (по строке)
Абсолютные	=$А$2+1

9.3. Сделать в формуле из относительной ссылки абсолютную можно, поставив курсор в любой символ ссылки и нажав клавишу <F4>.

Пример 1.

В ячейку В4 введем число. В ячейках С1, С2, С3 и С4 построим формулы с ссылками на ячейку B4 различных видов:

=B4 относительная;

=$B4 абсолютная по столбцу;

=B$4 абсолютная по строке;

=$B$4 абсолютная.

Для вычисления формул в ячейках С1, С2, С3 и С4 вид ссылки на ячейку В4 роли не играет, все четыре формулы выдают значение, равное числу в ячейке В4. Различия между ссылками различных видов будут видны только при копировании формул из ячеек С1, С2, С3 или С4 в другие ячейки таблицы.

9.4. При копировании формулы в другую ячейку относительные ссылки изменяются соответственно движению формулы:

• если формула скопирована в ячейку ниже исходной на N строк, то номер строки в относительной ссылке увеличится на N, при копировании вверх номер строки в ссылке соответственно уменьшится;

• при копировании формулы на N столбцов вправо именем столбца в относительной ссылке будет латинская буква, стоящая в алфавите на N знаков дальше, а при копировании влево, соответственно, ближе к началу алфавита;

• если формула при копировании сдвинулась и по горизонтали и по вертикали, то в относительной ссылке соответственно изменятся и имя столбца, и номер строки.

Пример 2.

Заполнить численными значениями ячейки С5, D4, D5, D6, D7, D8, Е5, Е6.

В ячейке H4 построить формулу =D5*2. Скопировать формулу вниз в ячейки Н5, Н6 и Н7. Формула превратится, соответственно, в формулы =D6*2, =D7*2 и =D8*2. При копировании формулы из Н4 в Н3 (вверх) она превратится в формулу =D4*2.

При копировании формулы в горизонтальном направлении будут меняться имена столбцов: при копировании в ячейку I4 формула превратиться в =Е5*2, а при копировании в G4 преобразуется в =С5*2.

При копировании «наискосок» в ячейку I5 в формуле изменятся обе компоненты адреса относительной ссылки и она превратиться в формулу =Е6*2.

9.5. В абсолютных по столбцу ссылках останется неизменным номер столбца, а номер строки меняется в соответствии с движением формулы вверх или вниз.

Пример 3.

Переделать формулу в ячейке Н4 из предыдущего примера в формулу =$D5*2. Произвести все копирования, аналогичные примеру 2. Убедиться, что на изменение формулы влияет движение вверх или вниз, а движение по горизонтали не влияет (в ссылке закреплен столбец D).

9.6. В абсолютных по строке ссылках останется неизменным номер строки, а имя столбца меняется соответственно движению формулы влево или вправо;

Пример 4.

Переделать формулу в ячейке Н4 из предыдущего примера в формулу =D$5*2. Произвести все копирования, аналогичные примеру 2. Убедиться, что на изменение формулы влияет движение влево или вправо, а движение по вертикали не влияет (в ссылке закреплена строка 5).

9.7. абсолютные ссылки остаются неизменными при любых движениях формулы при копировании.

Пример 5.

Переделать формулу в ячейке Н4 из предыдущего примера в формулу =$D$5*2. Произвести все копирования, аналогичные примеру 2. Убедиться, что формула не меняется при любом направлении движения (в ссылке закреплены столбец D и строка 5).

9.8. Внимание! Изменение относительных и частично абсолютных ссылок происходит только прикопированииформул. Приперемещенииформулы все ссылки в ней остаются неизменными независимо от их типа.

Пример 6.

Вернуться к формулу в ячейке Н4 из примера 2: =D5*2. Переместить формулу «наискосок» в ячейку I5. Убедиться, что формула не изменится и останется на новом месте в прежнем виде =D5*2.

9.9. Формулу из данной ячейки можно скопировать во все ячейки блока сразу. Для этого нужно:

а) активировать ячейку с формулой;

б) навести курсор на нижний правый угол ячейки до появления маленького черного крестика;

в) нажать левую клавишу мыши и зачертить блок ячеек в данном столбце или в данной строке.

Внимание!Для копирования формул на блоки ячеек рекомендуется именно этот способ как наиболее быстрый и удобный.

Примечание:при копировании формулы на блок ячеек указанным способом сохраняется разница в изменении абсолютных и относительных ссылок.

9.10. Копировать в блок ячеек можно не только формулы, но и числовые значения. Для этого нужно:

а) активировать ячейку с данными числового типа;

б) навести курсор на нижний правый угол ячейки до появления маленького черного крестика;

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

г) в появившемся окне со списком вариантов выбрать вариант Заполнить. Блок заполняется числами, причем при движении от начальной ячейки вниз или вправо значения возрастают на 1 в каждой следующей ячейке. Если блок зачерчен вверх или влево от начальной ячейки, то значения убывают на 1 в каждой следующей ячейке зачерченного блока.

Задание 9: 1) Откройте чистый лист, дайте ему имя Ссылки. Введите числовые значения в ячейку А2, блок В2:В7. В ячейку А3 введите текст – название изучаемого предмета, скопируйте его в ячейки А4:А7. В ячейке С2 постройте формулу, складывающую значения А2 и В2. Скопируйте формулу в блок ячеек С3:С7. Изучите полученный результат.

2) Очистите блок С3:С7. Переделайте формулу в ячейке С2 таким образом, чтобы ссылка на ячейку А2 не менялась при любом копировании формулы, а на ячейку В2 менялась при движении вниз и не менялась при движении вправо. Скопируйте формулу в блок С3:С7 и в блок D3:Е3.

3) Введите в ячейку J12 число 1. Руководствуясь пунктом 9.10., подпункт г) Заполните блоки J13 : J17, J11 : J5, I12 : F12, K12 : М12.

4) Попытайтесь в ячейке В10 создать формулу =1+В10. Проанализируйте результат.

5) Сохраните файл Технология.