Урок №28. Относительная, абсолютная и смешанная адресация.
Давайте проведём эксперимент: введём любые числа в ячейки диапазона А1:В2, а затем формулу =А1+В1 в ячейку С1 (рис. 4.32).
Затем скопируем формулу из ячейки С1 в ячейку С2 (например, через буфер обмена или перетащив мышью при нажатой клавише Ctrl).
Выясните, как изменилась формула при копировании. Как вы думаете, почему она изменилась именно так?
В нашей формуле были две ссылки на другие ячейки, обе они изменились (рис. 4.33). Мы скопировали формулу на одну ячейку вниз, поэтому в каждой ссылке номера строк увеличились на единицу (из А1 получилось А2, а из В1 — В2). Если скопировать формулу вправо, то увеличатся номера столбцов (см. рис. 4.33).
В общем случае, если формула скопирована на n ячеек вправо и m ячеек вниз, во всех ссылках имена столбцов увеличиваются на n, а номера строк — на m. Такие ссылки называются относительными.
Адрес ячейки в относительной ссылке при копировании изменяется так же, как изменяется адрес ячейки, в которой записана формула.
Другими словами, относительная ссылка «запоминает» взаимное расположение ячеек. При копировании формулы сохраняется связь ячеек между собой.
Формула =C13+F4 записана в ячейку D8. Какая формула получится, если скопировать формулу из ячейки D8 в ячейки:
а) В8; б) F8; в) D6; г) D12; д) В6; е) В12; ж) F6; з) F12; и) А8; к) G3?
Такое изменение формул при копировании очень удобно при заполнении больших таблиц. Предположим, что в двух столбцах таблицы хранятся доходы и расходы компании за первые месяцы года, и нужно подсчитать прибыль (разность доходов и расходов) за каждый месяц (рис. 4.34).
Конечно, для нескольких строк можно и вручную вписать в ячейки D3 и D4 нужные формулы, но можно просто скопировать в них формулу из D2, при этом ссылки изменятся как раз так, как нужно.
Для быстрого копирования удобно использовать маркер заполнения — чёрный квадратик в правом нижнем углу выделенной ячейки: если перетащить его мышью вниз, то формула из D2 будет скопирована во все ячейки, через которые прошел указатель мыши. Если выполнить двойной щелчок на маркере заполнения, то формула будет скопирована вниз до конца данных в предыдущем столбце. Это удобно, если количество строк в таблице велико, например 1000 или 10000.
Проверьте экспериментально, можно ли с помощью маркера заполнения копировать формулу в других направлениях: вверх, вправо и влево.
Часто бывает нужно, чтобы при копировании ссылка не изменялась. Например, пусть в столбце В записаны данные о зарплате работников компании. Из этой зарплаты вычитается подоходный налог, и остаток выдаётся на руки сотруднику. Размер налога записан в ячейку В1 и нужно, чтобы при изменении значения этой ячейки пересчитывались все данные в таблице (рис. 4.35).
оля зарплаты, которая выдаётся сотруднику (за вычетом налога), равна 1-В1, это значение нужно умножить на величину полной зарплаты. Таким образом, в ячейке С4 должна быть записана формула =В4*(1-В1).
Что получится, если скопировать формулу =В4*(1-В1) из ячейки С4 в ячейки С5 и С6?
Как вы поняли, нам нужно как-то запретить изменение ссылки на В1 при копировании. Для этого перед именем столбца и номером строки вставляют знак «$», так что формула в С4 принимает вид =В4*(1-$В$1). Здесь ссылка на ячейку В1 — абсолютная, она не изменяется при копировании, потому что запоминается точное место ячейки в таблице.
Абсолютная ссылка при копировании не изменяется.
Для того чтобы быстро сделать ссылку в формуле абсолютной, нужно установить курсор внутрь ссылки и нажать клавишу F4 (в Excel) или комбинацию клавиш Shift+F4 (в Calc).
Теперь попробуем построить таблицу умножения 5×5 (рис. 4.36).
При изменении чисел в первой строке и в столбце А все значения в центральной части таблицы должны пересчитываться, т. е. в ячейках диапазона B2:F6 должны быть записаны формулы.
Конечно, можно вписать в каждую из 25 ячеек свою формулу, но это довольно утомительно. Попробуем записать одну формулу в ячейку В2, а затем скопировать её во все остальные ячейки.
Запишем нужные формулы для двух угловых ячеек, В2 и F6. В В2 должна быть записана формула =А2*В1, а в F6 — формула =A6*F1 (рис. 4.37).
Запишите в тетради формулы, по которым должны вычисляться значения в ячейках СЗ, F2, В6 и Е4.
Мы видим, что первый сомножитель в формулах — это значение ячейки из столбца А, а номер строки меняется. Поэтому имя столбца А в первой ссылке нужно заблокировать от изменений, а номер строки — нет. Для формулы в В2 получаем такую ссылку: $А2. Аналогично поступаем для второго сомножителя: он всегда берётся из первой строки, а столбец меняется. Поэтому нужно заблокировать номер строки, оставив свободным изменяющееся имя столбца. В итоге получаем для ячейки В2 такую формулу: =$А2*В$1. Эту формулу нужно записать в В2, затем скопировать вправо на диапазон B2:F2, а потом перетащить маркер заполнения (уже целого диапазона) вниз на остальную часть таблицы.
Ссылки вида $А2 и В$1 называются смешанными, у них одна часть (номер строки или имя столбца) защищены от изменений при копировании, а вторая может изменяться.
Смешанная ссылка — это ссылка, в которой только одна часть (номер строки или имя столбца) изменяется при копировании.
Для того чтобы быстро изменить тип ссылки в формуле, нужно установить курсор внутри ссылки и нажать клавишу F4 (в Excel) или комбинацию клавиш Shift+F4 (в Calc). Например, если начать со ссылки В1, после первого нажатия получится абсолютная ссылка $В$1, после второго — смешанная ссылка В$1, а после третьего — $В1.