Адресация ячеек в формулах в электронных таблицах
Колесникова Елена Евгеньевна,
учитель информатики ГБОУ школа № 113
с углубленным изучением предметов
информационно-технологического профиля
Приморского района Санкт-Петербурга
учитель информатики ГБОУ школа № 113
с углубленным изучением предметов
информационно-технологического профиля
Приморского района Санкт-Петербурга
Предполагается, что перед изучением данной темы обучающиеся уже имеют представление о том, что такое табличный процессор, каковы основные элементы интерфейса Microsoft Excel, а также владеют навыком ввода и редактирования формул.
1. Описание теоретических положений.
Как правило, в формулах используются не сами исходные данные, а ссылки на ячейки, в которых эти данные находятся. Ссылка на ячейку состоит из адреса ячейки. Ячейка, в которую вводится формула, и ячейка, ссылка на которую используется в формуле, могут находиться на разных листах и даже в разных книгах. В таких случаях в ссылках к адресу ячейки добавляется указание на её месторасположение. Например, Лист2!С4 является ссылкой на ячейку С4 листа Лист2.
При изменении данных в каких-либо ячейках происходит автоматический пересчёт значений всех формул, содержащих ссылки на эти ячейки. Возможность автоматического пересчёта формул при изменении исходных данных — одна из ключевых идей электронных таблиц. Благодаря этому электронные таблицы называют динамическими.
Различают относительные, абсолютные и смешанные ссылки.
Большинство ссылок в формулах относительные. При копировании в составе формулы в другую ячейку они автоматически изменяются в соответствии с новым положением скопированной формулы, т. е. они изменяются относительно месторасположения формул. В этом состоит суть принципа относительной адресации.
Ссылка, которая изменяется при копировании формулы, называется относительной.
Пример относительных ссылок: C2, B5, D15
Ссылка, которая не изменяется при копировании формулы, называется абсолютной. Иногда нужно, чтобы при копировании формул адреса ячеек не менялись. В этом случае используют абсолютные ссылки. Для создания абсолютной ссылки служит знак $.
Пример абсолютных ссылок: $C$2, $B$5.
С помощью этого знака можно зафиксировать весь адрес ($А$1), только столбец ($А1) или только строку (А$1). В двух последних случаях говорят о смешанных ссылках. Ссылка, в которой при копировании формулы изменяется только номер строки или только имя столбца, называется смешанной.
Чтобы быстро преобразовать ссылку из относительной в абсолютную и наоборот, можно выделить её в строке ввода и нажать клавишу F4 (Microsoft Excel) или Shift+F4 (OpenOffice Calc).
Если в формуле для ссылки на ячейку использовать её имя, то при копировании формулы эта ссылка изменяться не будет. Иначе говоря, имя ячейки в формуле является абсолютной ссылкой.
При перемещении формулы, имеющиеся в ней ссылки не изменяются. В этом состоит суть принципа абсолютной адресации.
При изменении данных в каких-либо ячейках происходит автоматический пересчёт значений всех формул, содержащих ссылки на эти ячейки. Возможность автоматического пересчёта формул при изменении исходных данных — одна из ключевых идей электронных таблиц. Благодаря этому электронные таблицы называют динамическими.
Различают относительные, абсолютные и смешанные ссылки.
Большинство ссылок в формулах относительные. При копировании в составе формулы в другую ячейку они автоматически изменяются в соответствии с новым положением скопированной формулы, т. е. они изменяются относительно месторасположения формул. В этом состоит суть принципа относительной адресации.
Ссылка, которая изменяется при копировании формулы, называется относительной.
Пример относительных ссылок: C2, B5, D15
Ссылка, которая не изменяется при копировании формулы, называется абсолютной. Иногда нужно, чтобы при копировании формул адреса ячеек не менялись. В этом случае используют абсолютные ссылки. Для создания абсолютной ссылки служит знак $.
Пример абсолютных ссылок: $C$2, $B$5.
С помощью этого знака можно зафиксировать весь адрес ($А$1), только столбец ($А1) или только строку (А$1). В двух последних случаях говорят о смешанных ссылках. Ссылка, в которой при копировании формулы изменяется только номер строки или только имя столбца, называется смешанной.
Чтобы быстро преобразовать ссылку из относительной в абсолютную и наоборот, можно выделить её в строке ввода и нажать клавишу F4 (Microsoft Excel) или Shift+F4 (OpenOffice Calc).
Если в формуле для ссылки на ячейку использовать её имя, то при копировании формулы эта ссылка изменяться не будет. Иначе говоря, имя ячейки в формуле является абсолютной ссылкой.
При перемещении формулы, имеющиеся в ней ссылки не изменяются. В этом состоит суть принципа абсолютной адресации.
2. Задания и примеры для разбора в классе.
Пример 1. В ячейке B1 записана формула =2*$A1. Какой вид приобретёт формула, после того как содержимое ячейки B1 скопируют в ячейку C2?
Решение:
В формуле используется смешанная ссылка: при копировании формулы имя столбца останется неизменным, а номер строки увеличится на 1. Таким образом, после копирования в ячейке С2 окажется формула =2*$A2.
Пример 2. Дан фрагмент электронной таблицы:
Чему станет равным значение ячейки С1, если в неё скопировать формулу из ячейки С2?
Решение:
Так как копирование формулы происходит внутри одного столбца, имена столбцов в формуле не изменятся, а номер строки в ссылках уменьшится на единицу. Формула примет вид: =($A1+B1)/2. В ячейке С1 отобразится число 14.
Пример 3. Дан фрагмент электронной таблицы:
Чему будет равна сумма значений диапазона ячеек E1:E4 после копирования в него формулы из ячейки D2?
Решение:
Формулы копируются в ячейки соседнего столбца. Поэтому буквенное обозначение столбца в относительной ссылке изменится на следующее по алфавиту. Следовательно, первое слагаемое в формуле примет вид: С$3 (ссылка на номер строки здесь абсолютная, она останется неизменной). Во втором слагаемом неизменным является обозначение столбца. А номер строки при копировании формулы в ячейки E1, E2, E3 и E4 соответственно: уменьшится на единицу, останется неизменным, увеличится на единицу, увеличится на 2.
Таким образом, в ячейке E1 будет формула =C$3+$C1; в ячейке E2 будет формула =C$3+$C2; в ячейке E3 будет формула =C$3+$C3; в ячейке E4 будет формула =C$3+$C4.
После вычисления значений по формулам ячеек E1, E2, E3 и E4 (4, 5, 6 и 7) находим сумму значений диапазона ячеек E1:E4, равную 22.
Решение:
В формуле используется смешанная ссылка: при копировании формулы имя столбца останется неизменным, а номер строки увеличится на 1. Таким образом, после копирования в ячейке С2 окажется формула =2*$A2.
Пример 2. Дан фрагмент электронной таблицы:
Чему станет равным значение ячейки С1, если в неё скопировать формулу из ячейки С2?
Решение:
Так как копирование формулы происходит внутри одного столбца, имена столбцов в формуле не изменятся, а номер строки в ссылках уменьшится на единицу. Формула примет вид: =($A1+B1)/2. В ячейке С1 отобразится число 14.
Пример 3. Дан фрагмент электронной таблицы:
Чему будет равна сумма значений диапазона ячеек E1:E4 после копирования в него формулы из ячейки D2?
Решение:
Формулы копируются в ячейки соседнего столбца. Поэтому буквенное обозначение столбца в относительной ссылке изменится на следующее по алфавиту. Следовательно, первое слагаемое в формуле примет вид: С$3 (ссылка на номер строки здесь абсолютная, она останется неизменной). Во втором слагаемом неизменным является обозначение столбца. А номер строки при копировании формулы в ячейки E1, E2, E3 и E4 соответственно: уменьшится на единицу, останется неизменным, увеличится на единицу, увеличится на 2.
Таким образом, в ячейке E1 будет формула =C$3+$C1; в ячейке E2 будет формула =C$3+$C2; в ячейке E3 будет формула =C$3+$C3; в ячейке E4 будет формула =C$3+$C4.
После вычисления значений по формулам ячеек E1, E2, E3 и E4 (4, 5, 6 и 7) находим сумму значений диапазона ячеек E1:E4, равную 22.
