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

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

Цели

• Научиться создавать абсолютные, относительные и смешанные ссылки;
• Усовершенствовать навыки работы в Excel;

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

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

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

Для успешной работы с данным учебным элементом  необходимо знать основные клавиши  на клавиатуре, навыки работы с мышью, с диалоговыми окнами, иметь навыки  работы в 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.

В электронных  таблицах с использованием в формулах относительных ссылок вычислить значения функции у = х^2 – 3 на отрезке (-4;4)

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

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

3. В ячейку А1 введем х.

4. В  ячейки В1;J1 введем значения х от -4 до 4.

5. В  ячейку А2 введем нашу функцию у = х^2 – 3.

6. Ввести  в ячейку В2 формулу =В1^2-3, содержащую относительную ссылку на значение х.

7. Установите курсор на правый нижний угол ячейки В2.

8. Нажмите правую кнопку мыши и протяните на ячейки В2;J2 и отпустите кнопку мыши. Появится контекстное меню, в котором надо выбрать "Копировать ячейки".

В результате получим значения функции у = х^2 – 3 на отрезке (-4;4) .

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

Проверьте свои результаты.

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

Абсолютная  ссылка на ячейку или область ячеек будет всегда ссылаться на один и тот же адрес строки и столбца. При сравнении с направлениями улиц это будет примерно следующее: "Идите на пересечение Арбата и Бульварного кольца". Вне зависимости от места старта это будет приводить к одному и тому же месту. Если формула требует, чтобы адрес ячейки оставался неизменным при копировании, то должна использоваться абсолютная ссылка (формат записи $А$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. Ввести  наименования стран, в столбец А: Англия, Болгария, Бельгия, Югославия, Польша.

3. Цены на путевки ввести в условных единицах — в столбец В и курс доллара к рублю — в ячейку В1.

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

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

5. Установите курсор на правый нижний угол ячейки С3.

6. Нажмите правую кнопку мыши и протяните на ячейки С3;С7 и отпустите кнопку мыши. Появится контекстное меню, в котором надо выбрать "Копировать ячейки".

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

Проверьте свои результаты

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

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

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

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

1. Перейти  на новый лист и присвоить  листу имя Смешанные ссылки. Величина курса доллара должн храниться в ячейке В1.

2. Ввести  наименования стран, в столбец А: Англия, Болгария, Бельгия, Югославия, Польша.

3. Цены на путевки ввести в условных единицах — в столбец В и курс доллара к рублю — в ячейку В1.

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

5. Установите курсор на правый нижний угол ячейки С3.

6. Нажмите правую кнопку мыши и протяните на ячейки С3;С7 и отпустите кнопку мыши. Появится контекстное меню, в котором надо выбрать "Копировать ячейки".

Проверьте свой результат.

Упражнение  №4 (итоговое).

1. Заполните таблицу по образцу по образцу.

 

2. С использованием в формулах относительных, абсолютных и смешанных ссылок вычислите:

• Цену путевок в рублях
• Стоимость проживания в различных странах за 1 день
• Стоимость тура в рублях для двух взрослых и ребенка, если скидка на путевку для ребенка составляет 10 %.