ИНФОРМАТИКА · 9 КЛАСС
Электронные таблицы · Интерактивный конспект

§ 14. Ссылки в формулах

Разберём, как меняются ссылки при копировании формул, чем относительные ссылки отличаются от абсолютных и смешанных и как закрепить ссылку знаком $ или клавишей F4.

Попробовать на модели ↓
Разбираемся в теме

Основные пункты параграфа

1

Копирование формул

Формулы с одинаковой структурой не вводят заново, а копируют.

НАБЛЮДАЙТЕ И ПРОБУЙТЕ

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

Формула =A2*B2 копируется на одну строку вниз и становится =A3*B3. Введите свою формулу, например =C3*D3, и задайте смещение.

Рассмотрим таблицу для подсчёта стоимости купленных продуктов. Чтобы найти стоимость, содержимое ячейки столбца Цена умножают на содержимое ячейки столбца Количество.

СтрокаB: продуктC: ценаD: количествоE: стоимостьФормула в E
3Молоко1,4511,45=C3*D3
4Хлеб0,9321,86=C4*D4
5Шоколад1,5423,08=C5*D5
6Яблоко0,3451,7=C6*D6
7Йогурт1,244,8=C7*D7

Формулы различаются только номерами строк — как и их местоположение. По своему виду это одна формула, записанная для разных исходных данных. Если вычисления выполняются по формулам одной структуры, но для разных исходных данных, формулу вводят один раз и копируют, например маркером заполнения.

Главная мысль: Формулы одной структуры для разных исходных данных копируют; в отличие от текстов и чисел, формулы при копировании меняются.
2

Относительные ссылки

Ссылки изменяются относительно перемещения формулы.

НАБЛЮДАЙТЕ И ПРОБУЙТЕ

Копирование формулы =B5+C7

Введите =B5+C7 (формула из F10) и задайте смещения: в E10 — столбцов −1; в F8 — строк −2; в H10 — столбцов 2; в I9 — столбцов 3 и строк −1.

При копировании формулы из одной строки в другую номера строк в ссылках изменяются на столько, на сколько строк переместилась формула; при копировании в другой столбец обозначения столбцов изменяются на столько, на сколько столбцов она переместилась. Пусть в F10 введена формула =B5+C7:

Копия в ячейкуСмещениеФормула
E10на 1 столбец влево=A5+B7
H10на 2 столбца вправо=D5+E7
F8на 2 строки вверх=B3+C5
F13на 3 строки вниз=B8+C10
I9на 3 столбца вправо и 1 строку вверх=E4+F6

В пределах одной строки меняются только имена столбцов, в пределах одного столбца — только номера строк, а в I9 изменились и столбцы, и строки.

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

Главная мысль: По принципу относительной адресации ссылки при копировании формулы изменяются относительно её перемещения; такие ссылки называют относительными.
3

Абсолютные и смешанные ссылки

Знак $ закрепляет имя столбца и (или) номер строки.

НАБЛЮДАЙТЕ И ПРОБУЙТЕ

Смешанные ссылки

Формула =$A2*B$1 копируется на строку вниз и столбец вправо: в $A2 меняется только строка, в B$1 — только столбец.

У смешанных ссылок при копировании остаётся неизменной только одна часть: номер строки (E$4) или имя столбца ($E4).

СсылкаВидЧто происходит при копировании
C7относительнаяИзменяется по принципу относительной адресации
$A$4абсолютнаяНе изменяется
E$4смешаннаяИзменяется при копировании в другие столбцы, не меняется при копировании в другие строки
$E4смешаннаяИзменяется при копировании в другие строки, не меняется при копировании в другие столбцы
Главная мысль: Абсолютная ссылка ($A$4) при копировании не меняется, у смешанной (E$4, $E4) неизменной остаётся только одна часть.
4

Абсолютная ссылка в расчётах

Общее для всех строк значение берут по абсолютной ссылке.

НАБЛЮДАЙТЕ И ПРОБУЙТЕ

Курс перевода в $D$2

Введите =E4*$D$2 и задайте смещение строк 1, 2, 3, 4: ссылка E4 меняется, а $D$2 остаётся прежней.

Пусть стоимость покупки нужно перевести из белорусских рублей в российские. Курс перевода записан в ячейке D2, стоимости — в E4:E8. Для молока в F4 можно ввести =E4*D2, но при копировании вниз получатся =E5*D3, =E6*D4… — результат будет ошибочным. Ссылка на курс не должна меняться, т. е. должна быть абсолютной: =E4*$D$2. При копировании в F5, F6, … получаем =E5*$D$2, =E6*$D$2 и т. д.

ПродуктСтоимость (E)Формула в FВ российских рублях
Молоко2,9=E4*$D$290,94 р.
Хлеб1,86=E5*$D$258,33 р.
Шоколад6,16=E6*$D$2193,18 р.
Яблоко1,7=E7*$D$253,31 р.
Йогурт4,8=E8*$D$2150,53 р.
Всего17,42=E9*$D$2546,29 р.

Курс в D2 — 31,36. Формулу из F4 копируют в F5:F8 маркером заполнения: делают активной ячейку F4 и протягивают маркер.

Главная мысль: Чтобы при копировании формула брала одно и то же значение, ссылку на него делают абсолютной; тип ссылки меняют клавишей F4.
Интерактивная практика

Исследуйте и примените

Тренажёр показывает, как изменится формула при копировании на заданное число строк и столбцов. Проверьте на нём примеры параграфа, а затем выполните упражнения.

НАБЛЮДАЙТЕ И ПРОБУЙТЕ

Лаборатория: копирование формул

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

Соберём главное

Шесть выводов

01

Формулы одной структуры для разных исходных данных копируют; в отличие от текстов и чисел, формулы при копировании меняются.

02

По принципу относительной адресации ссылки при копировании формулы изменяются относительно её перемещения; такие ссылки называют относительными.

03

Абсолютная ссылка ($A$4) при копировании не меняется, у смешанной (E$4, $E4) неизменной остаётся только одна часть.

04

Чтобы при копировании формула брала одно и то же значение, ссылку на него делают абсолютной; тип ссылки меняют клавишей F4.

05

Тип ссылки меняют клавишей F4: A1 → $A$1 → A$1 → $A1 → A1.

06

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

Самопроверка

Тест: 10 вопросов

Один верный ответ. За ответ — 1 балл. Откройте и проверьте себя.

1. Что происходит со ссылками в формуле при её копировании на новое место?

2. В F10 записана формула =B5+C7. Какой она станет при копировании в F13?

3. В F10 записана формула =B5+C7. Какой она станет при копировании в H10?

4. Какие ссылки называют относительными?

5. Какая из ссылок является абсолютной?

6. Формулу со ссылкой E$4 скопировали на 2 строки вниз и на 1 столбец вправо. Какой стала ссылка?

7. Формулу со ссылкой $E4 скопировали на 2 строки вниз и на 1 столбец вправо. Какой стала ссылка?

8. Какой клавишей можно изменить тип ссылки при вводе формулы?

9. В F4 записана формула =E4*$D$2. Какой она станет при копировании в F6?

10. Над строкой с исходными данными вставили новую строку. Что произойдёт со ссылками формул на эти данные?

ВыводыТестВ началоВсе параграфы
↑

Загрузка прогресса…

↑