Практическая работа 4.2 Относительные, абсолютные и смешанные ссылки в электронных таблицах

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

Ссылка на ячейку — её адрес, используемый в формуле для обращения к содержащимся в ней данным. Например, запись A1 указывает на столбец A и строку 1. По ссылке таблица понимает, какое число или текст нужно подставить в расчёт. Без адресов пришлось бы каждый раз вводить исходные данные прямо в формулу.

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

Абсолютная ссылка — ссылка на ячейку, которая не изменяется при копировании формулы; знак $ фиксирует и столбец, и строку, например $A$1. Каким бы способом ни перемещали формулу, она продолжит обращаться к A1. Это полезно для постоянного значения, например общей ставки или цены, которая нужна в нескольких расчётах. Два знака $ показывают, что закреплены обе части адреса.

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

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

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

При копировании формулы с относительной ссылкой таблица сохраняет смещение от ячейки с формулой до ячейки-источника. Например, если формула находится в C2 и ссылается на A2, источник расположен на две колонки левее в той же строке. При переносе формулы в C3 таблица сохраняет это расположение и обращается к A3. Поэтому изменяется адрес, но остаётся прежней логика расчёта для каждой строки.

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

В смешанной ссылке закреплённая часть адреса остаётся прежней, а незакреплённая изменяется. Запись $A1 сохраняет столбец A, поэтому при копировании вправо формула всё ещё обращается к этому столбцу, но при копировании вниз номер строки меняется. Запись A$1, наоборот, всегда указывает на строку 1, хотя буква столбца может сдвигаться. Такой выбор соответствует направлению, в котором заполняют таблицу.

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

Как ссылки изменяются при копировании формулы

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

Разбор задач

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

1. Определяем перемещение: формула переходит из C2 в C3, то есть на одну строку вниз. Столбец C при этом не меняется. Значит, относительные ссылки тоже должны сохранить своё положение относительно формулы.
2. Ссылка A2 указывает на ячейку в столбце A той же строки, что и формула. После копирования она становится A3. По той же причине ссылка B2 превращается в B3.
3. Собираем новую формулу, сохраняя знак сложения и меняя номера строк в обеих ссылках. Проверяем, что формула в C3 использует данные именно из строки 3.

Ответ: =A3+B3.

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

1. Сначала записываем умножение количества на цену. Количество находится в B2, поэтому используем ссылку B2: при копировании вниз она должна превратиться в B3, B4 и далее.
2. Цена во всех строках берётся из E1. Чтобы адрес не сдвигался при копировании, закрепляем и столбец E, и строку 1 знаками $. Получаем абсолютную ссылку $E$1.
3. Соединяем обе ссылки знаком умножения. Перед копированием проверяем формулу: количество указано относительной ссылкой, а постоянная цена — абсолютной.

Ответ: в C2 вводим =B2*$E$1. При копировании вниз формула станет, например, =B3*$E$1.

Задача 3. В строке 1 указаны цены разных товаров, а в столбце A — количества для разных заказов. Нужно заполнить таблицу стоимости, начиная с B2, и копировать формулу по строкам и столбцам. С каждой ячейкой расчёта связаны два источника: количество слева в столбце A и цена сверху в строке 1. Значит, при копировании должна меняться только одна часть каждой ссылки.

1. Для количества в первой строке расчёта подходит A2. При копировании вправо столбец A должен сохраниться, а при копировании вниз номер строки должен меняться. Закрепляем столбец и получаем $A2.
2. Цена для расчёта в B2 находится в B1. При копировании вправо должна изменяться буква столбца, чтобы брать цену другого товара, но строка 1 должна остаться прежней. Поэтому записываем B$1.
3. Умножаем количество на цену и проверяем обе части: знак $ перед A закрепляет столбец количества, знак $ перед 1 закрепляет строку с ценами. Теперь формулу можно размножать в обе стороны.

Ответ: в B2 вводим =$A2*B$1. При копировании вправо столбец количества A сохранится, а ссылка на цену станет C$1; при копировании вниз ссылка на количество станет $A3, а строка цены останется 1.

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

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

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

Ссылки $A1 и A$1 легко перепутать, потому что отличается только положение знака $. В $A1 закреплён столбец, а в A$1 — строка; если выбрать не тот вариант, копирование вправо или вниз даст неверные адреса. Чтобы проверить себя, проследи отдельно за буквой и номером при пробном перемещении. Та часть, перед которой стоит $, должна остаться прежней.

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

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

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

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

• Относительная ссылка изменяется при копировании формулы.
• Абсолютная ссылка записывается со знаками $ перед столбцом и строкой, например $A$1.
• Смешанная ссылка закрепляет только столбец или только строку.
• В записи $A1 закреплён столбец A.
• В записи A$1 закреплена строка 1.
• Тип ссылки выбирают с учётом направления и способа копирования формулы.