§ 22. Работа с диапазонами. Относительная адресация

Определения и обозначения

Диапазон ячеек — группа ячеек электронной таблицы, обычно образующая прямоугольник. Чтобы выделить его, указывают первую и последнюю ячейки области или протягивают выделение по таблице. Диапазон удобен, когда нужно обработать сразу несколько значений, например сложить их функцией или построить по ним вычисления. В формулах и функциях он избавляет от записи адреса каждой ячейки по отдельности.

Адрес диапазона — запись адресов его первой и последней ячеек через двоеточие, например A1:C4. Первая и последняя ячейки задают противоположные углы прямоугольника, поэтому в диапазон входят также все ячейки между ними. Так, A1:C4 включает строки с 1-й по 4-ю и столбцы A, B и C. Эта запись помогает однозначно указать целую область таблицы.

Относительная адресация — способ задать ссылку на ячейку в формуле так, чтобы адрес менялся при переносе формулы. Например, ссылка на ячейку слева от формулы останется ссылкой на ячейку слева и после копирования в другое место. Такой способ нужен для повторяющихся вычислений: формулу можно перенести на соседние строки или столбцы, сохранив её смысл. Поэтому относительная ссылка зависит не только от названия ячейки, но и от места формулы.

Копирование формулы — перенос формулы в другие ячейки, при котором электронная таблица автоматически изменяет относительные адреса. Копировать можно в соседние ячейки или в область, где выполняются одинаковые вычисления для разных данных. Например, одна формула подсчёта суммы подходит для каждой строки таблицы. Это позволяет не вводить и не исправлять формулу вручную в каждой ячейке.

Как это устроено

Две ячейки в записи диапазона задают его противоположные углы. Таблица определяет все строки между номерами этих ячеек и все столбцы между их буквами, а затем включает каждую ячейку на пересечении выбранных строк и столбцов. Например, B2:D4 охватывает столбцы B, C, D и строки 2, 3, 4 — всего девять ячеек. Такой способ записи короче перечисления каждой ячейки и сразу показывает границы прямоугольной области.

Адрес в формуле удобно понимать как расстояние от ячейки с формулой до ячейки, на которую она ссылается. Если в C1 записано =A1+B1, формула использует ячейки на две и на одну позиции левее соответственно. При копировании формулы таблице нужно сохранить именно эти отношения, чтобы вычисление продолжало использовать соседние исходные данные. Поэтому ссылки перемещаются вместе с формулой, а не остаются привязанными к прежним координатам.

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

Чтобы найти новую ссылку, сначала сравните исходную и целевую ячейки с формулой. Например, переход из C2 в D4 означает смещение на один столбец вправо и две строки вниз. Затем примените оба изменения к каждой ссылке: адрес A2 станет B4, а B2 — C4. Этот способ надёжнее, чем запоминать отдельные правила для каждой формулы, потому что он учитывает любое направление и расстояние переноса.

Диапазон можно указать в формуле как аргумент функции, то есть как данные, с которыми функция выполняет действие. Например, функция суммы может получить диапазон A1:A5 и сложить значения всех пяти ячеек. При копировании формулы с диапазоном его адрес тоже изменяется по правилам относительной адресации. Это полезно, когда в каждой строке нужно обработать свою группу данных: достаточно скопировать формулу, и она будет обращаться к соответствующей области.

Диапазоны и относительные адреса

Сначала найдите в таблице исходное положение формулы и направление переноса, а затем проследите, как изменились ссылки. Строка о диапазоне напоминает, что его адрес охватывает всю область, а не только две крайние ячейки. Если перенос идёт сразу по строкам и столбцам, учитывайте оба смещения.

Разбор задач

В каждой задаче важно сначала найти смещение самой формулы. После этого такое же смещение применяют к каждой относительной ссылке, чтобы сохранить расстояние от формулы до ячеек с данными. Буква показывает столбец, а число — строку, поэтому перенос по вертикали и горизонтали может менять разные части адреса. Разберём этот порядок на примерах.

Формулу из C2 копируют в C3

В C2 записана формула =A2+B2. Нужно определить, что получится после копирования в C3. Целевая ячейка расположена на одну строку ниже исходной, но в том же столбце. Значит, обе ссылки сдвинутся вниз на одну строку, а буквы столбцов сохранятся.

1. Сравниваем адреса ячеек с формулой: из C2 в C3 — одна строка вниз.
2. Изменяем строку в каждой ссылке: A2 становится A3, B2 становится B3.
3. Подставляем новые адреса, сохраняя знаки сложения и равенства.

Ответ: в C3 будет формула =A3+B3. Она складывает значения из той же строки, в которой находится сама формула. Это сохраняет смысл вычисления при переходе к следующей строке таблицы.

Формулу из D4 копируют в E4

В D4 записана формула =B4*C4. Её переносят в E4, то есть на один столбец вправо и без изменения строки. Поэтому адрес каждой ссылки должен сдвинуться на один столбец вправо. Номера строк останутся прежними, поскольку вертикального перемещения не было.

1. Определяем перенос: D4 → E4 — один столбец вправо.
2. Сдвигаем буквы столбцов: B становится C, а C становится D.
3. Оставляем номер 4 в обеих ссылках и записываем формулу заново.

Ответ: в E4 получится =C4*D4. Формула по-прежнему перемножает значения из двух соседних ячеек слева. Проверка по смыслу помогает заметить ошибку: ссылки должны остаться слева от формулы и находиться в той же строке.

Формулу из C2 копируют в D4

В C2 записана формула =A2+B2, а скопировать её нужно в D4. Теперь перенос происходит сразу в двух направлениях: на один столбец вправо и на две строки вниз. Если учесть только строки или только столбцы, получится неверный результат. Поэтому применим оба изменения отдельно к каждой ссылке.

1. Сравниваем адреса: C2 → D4 означает +1 столбец и +2 строки.
2. Ссылку A2 сдвигаем так же: A становится B, а 2 — 4, получаем B4.
3. Ссылку B2 сдвигаем на то же расстояние: получаем C4.
4. Сохраняем действие сложения и составляем новую формулу.

Ответ: в D4 получится =B4+C4. Формула будет ссылаться на B4 и C4 — те же две позиции слева от неё, что и A2 и B2 относительно исходной ячейки C2. Сохранение относительного расположения подтверждает, что обе координаты изменены верно.

Типичные ошибки

Первая ошибка — считать, что при копировании формулы адреса всегда остаются прежними. Так думают, потому что в записи формулы видны конкретные буквы и числа, но относительная ссылка зависит от положения формулы. Проверка простая: сравните исходную и целевую ячейки, а затем проследите, сохранилось ли расстояние до каждой ссылки. Если формула переехала, а адреса не изменились, перепроверьте результат.

Вторая ошибка — менять только номер строки или только букву столбца, не учитывая направление переноса. Это часто происходит, когда ученик замечает движение вниз, но забывает, что формулу могли перенести ещё и вправо. Проверьте обе координаты: отдельно найдите изменение номера строки и буквы столбца. Если одна из них должна была сдвинуться, но осталась прежней, новая ссылка неверна.

Третья ошибка — воспринимать адрес диапазона как перечисление только двух ячеек. Например, A1:C3 обозначает не одну A1 и одну C3, а весь прямоугольник между ними. Ошибка возникает из-за того, что в записи указаны лишь крайние адреса. Для проверки перечислите промежуточные столбцы и строки: область A1:C3 содержит девять ячеек, значит, это целый прямоугольник.

Четвёртая ошибка — определять новую ссылку по её абсолютному адресу, не учитывая смещение формулы. Например, можно пытаться заменить ссылку ближайшей ячейкой, не проверив, как она была расположена относительно исходной формулы. Чтобы избежать этого, сначала вычислите перенос по строкам и столбцам, а затем примените его к ссылке. Контрольный вопрос: осталось ли между формулой и каждой ячейкой то же взаимное расположение?

Связь с другими темами

Для работы с диапазонами нужно уметь находить ячейки по адресам, где буква указывает столбец, а число — строку. Также понадобятся простые формулы: например, сложение значений из двух ячеек. Если эти действия знакомы, можно проследить за тем, как ссылка ведёт себя при переносе формулы.

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

Дальше изучают абсолютные и смешанные адреса. Они позволяют закрепить при копировании всю ссылку или только её часть — строку либо столбец. Это пригодится, когда одной формуле нужно менять одни ссылки, но сохранять неизменной общую ячейку или строку с постоянными данными.

Что запомнить

• Диапазон задаётся адресами его крайних ячеек, разделёнными двоеточием.
• Адрес A1:B3 обозначает все ячейки прямоугольника от A1 до B3.
• Относительный адрес зависит от положения ячейки с формулой.
• При копировании формулы относительные ссылки изменяются вслед за ней.
• При переносе формулы учитывай смещение и по строкам, и по столбцам.
• Диапазоны можно использовать в формулах и передавать функциям.