Учебный элемент 

«Относительные, абсолютные и смешанные ссылки в Excel»

Цели

Изучив  данный учебный элемент вы сможете:

• Создавать абсолютные ссылки;
• Создавать относительные ссылки;
• Создавать смешанные ссылки;

 

Задачи:

Образовательные

• - научить использовать в формулах адрес клетки (относительный, абсолютный, смешанный);
• - продолжать совершенствовать навыки работы в программе Excel;
• - повторение операций копирования, изучение особенностей этих операций при применении принципа абсолютной адресации;
• - повторение правил ввода данных в ячейки Excel;

Развивающие

• - развитие воображения, памяти, внимания, самостоятельности мышления, навыков работы на ПК;
• - познавательного интереса;
• - формирование информационной культуры, потребности в приобретении и применении знаний;

Воспитательные 

• - воспитание появления настойчивости в завершении задания;
• - привитие учащимся навыков самостоятельной работы.

Оборудование  и материалы

• Установленная на компьютере программа Microsoft Excel.

Сопутствующие учебные элементы и материалы

Для успешной работы с данным учебным элементом  необходимо знать основные клавиши  на клавиатуре, пользоваться мышью, работать с окнами, иметь навыки  работы в Excel.

Создание  относительной ссылки.

Относительная ссылка указывает на ячейку, согласно ее положения относительно ячейки, содержащей формулу.

Использование относительных ссылок аналогично указанию направления движения по улице - "идти три квартала на север, затем два  квартала на запад". Следование этим инструкциям из различных начальных  мест будет приводить в разные места назначения.

Обычно ссылки на ячейки описываются и используются как относительные (формат записи А1). Когда формула, содержащая эти ссылки, копируется, происходит изменение формулы для поддержания относительности ссылок.  

Разберем  данный тип ссылок на примере.

1. Введите в ячейки следующие значения:

А1 - 20 В1 - 200

А2 - 30 В2 - 300

2. В ячейку А3 введите формулу =А1+А2 и нажмите ввод.

 

 

3. Установите курсор на правый нижний угол ячейки А3.
4. Нажмите правую кнопку мыши и протяните на ячейку В3 и отпустите кнопку мыши. Появится контекстное меню, в котором надо выбрать "Копировать ячейки".

 

 

После этого значение формулы из ячейки А3 будет скопировано в ячейку В3.

5. Активизируйте ячейку В3 и посмотрите какая формула получилась - В1+В2.

Почему  так получилось? Когда мы записали в ячейке А3 формулу А1+А2, Excel интерпретировал эту запись так: "Взять значения из ячейки, расположенной в текущем столбце на две строки выше и сложить со значением ячейки, расположенной в текущем столбце на одну строку выше".

Упражнение  №1.

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

1. Запустить  электронные таблицы Excel командой [Программы - Microsoft Office – Microsoft Excel]

2. Создать новый документ с помощью команды [Файл-Создать – Лист  Microsoft Office Excel] и присвоить листу имя Относительные ссылки.

3. Создадим фрагмент таблицы умножения, в которой числа от 1 до 9, хранящиеся в диапазоне ячеек А1:А9, умножаются на числа от 1 до 9, хранящиеся в диапазоне ячеек В1:В9.

4. Ввести в ячейки А1 и В1 число 1, а в ячейку С1 — формулу =А1*В1, содержащую относительные ссылки.

5. Для быстрого ввода последовательностей чисел, различающихся на фиксированную величину (в данном случае на 1), ввести в ячейки А2 и В2 формулы =А1+1 и =В1+1, содержащие относительные ссылки.

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

4. Выделить  диапазон ячеек А2;А9 и ввести команду [Правка-Заполнить-Вниз].

5. Выделить  диапазон ячеек В2:В9 и ввести команду [Правка-Заполнить-Вниз].

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

6. Выделить  диапазон ячеек С1:С9 и ввести команду [Правка-Заполнить-Вниз].

7. Для  отображения в ячейках не чисел,  а формул ввести команду [Сервис-Параметры...] и на появившейся диалоговой панели Параметры на вкладке Вид установить флажок формулы.

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

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

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

Абсолютная  ссылка на ячейку или область ячеек будет всегда ссылаться на один и тот же адрес строки и столбца. При сравнении с направлениями улиц это будет примерно следующее: "Идите на пересечение Арбата и Бульварного кольца". Вне зависимости от места старта это будет приводить к одному и тому же месту. Если формула требует, чтобы адрес ячейки оставался неизменным при копировании, то должна использоваться абсолютная ссылка (формат записи $А$1). Абсолютная ссылка может быть создана только при наборе формулы, перед адресом строки и столбца вводится знак доллара - $.

Разберем  данный тип ссылок на примере.

1. Введите в ячейки следующие значения:

А1 - 20 В1 - 200

А2 - 30 В2 - 300

2. Введите в ячейку С1 число 5.
3. В ячейку А3 введите следующую формулу =А1+А2+$C$1.
4. Установите курсор на правый нижний угол ячейки А3.
5. Нажмите правую кнопку мыши и протяните на ячейку В3 и отпустите кнопку мыши. Появится контекстное меню, в котором надо выбрать "Копировать ячейки".
6. Посмотрите что получилось. Относительные ссылки "подстроились" под новые значения, а вот абсолютная - осталась неизменной.

 

Упражнение  №2

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

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

2. Ввести  наименования стран, в столбец А, их цены в условных единицах — в столбец В и курс доллара к рублю — в ячейку В1.

3. Ввести  в ячейку С3 формулу =В3*$В$1, содержащую относительную ссылку на цену в условных единицах и абсолютную ссылку на курс доллара к рублю.

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

4. Выделить  диапазон ячеек С3:С7 и ввести команду [Правка-Заполнитъ-Вниз].

8. Обратить  внимание, что при копировании формул относительные ссылки изменяются, а абсолютные не изменяются.

Полученная  таблица

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

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