Практическое занятие №12. ТЕМА: «Использование абсолютных, относительных и смешанных ссылок формул для выполнения вычислительных расчетов с копированием формул по строкам и столбцам».

Практическое занятие №12.

ТЕМА: «Использование абсолютных, относительных и смешанных ссылок формул для выполнения вычислительных расчетов с копированием формул по строкам и столбцам».

Цель работы: Научиться использовать:

1.     В формулах электронной таблицы относительные, абсолютные и смешанные ссылки;

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

3.     Установить требуемый формат представления данных в ячейках.

2Оборудование, приборы, аппаратура, материалы: проектор.

 3. Краткие теоретические сведения

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

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

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

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

 4.Задание

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

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

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

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

1.     Запустить электронную таблицу Excel командой Программы – Microsoft Excel. Создать новый документ с помощью команды Файл – Создать и присвоить листу имя Относительные ссылки.

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

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

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

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

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

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

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

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

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

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

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

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

1.     Перейти на новый лист и присвоить листу имя Абсолютные ссылки.

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

2.     Ввести наименование устройств, входящих в компьютер, в столбец А, их цены в условных единицах – в столбец В и курс доллара к рублю  – в ячейку Е2.

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

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

4.     Выделить диапазон ячеек С2:С11 и ввести команду Правка – Заполнить – Вниз.

Подсчитаем в ячейке С12 общую цену компьютера с помощью встроенной функции СУММ().

5.     Сделать активной ячейку С12. щелкнуть по кнопке Суммирование, будет выделен диапазон ячеек для суммирования, а в ячейку помещена функция  =СУММ(С2:С11). Если предложенный диапазон не подходит, перетащить указатель по ячейкам, которые нужно просуммировать. Нажать кнопку {Enter}.

Представим цены устройств компьютера и курс доллара к рублю в денежном формате.

6.     Выделить диапазон ячеек С2:С12 и ввести команду Формат – Ячейки.               На появившейся диалоговой панели Формат ячеек на вкладке Число в списке Числовые форматы: выбрать пункт Денежный. Повторить процедуру для ячейки Е2.

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

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

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

1.     Перейти на новый лист и присвоить листу имя Смешанные ссылки.                       Скопировать заполненные ячейки с листа  Абсолютные ссылки и вставить на лист Смешанные ссылки.

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

2.     Ввести  курс евро к  рублю   в ячейку F2.

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

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

4.     Выделить диапазон ячеек С2:С11 и ввести команду Правка – Заполнить – Вниз, а затем – команду Правка – Заполнить –Вправо.

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

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

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

  5. Содержание отчета

Отчет должен содержать:

1.      Название работы.

2.     Цель работы.

3.     Задание.

4.    Результаты выполнения задания.

5.     Вывод по работе.

 6. Контрольные вопросы

 7. Литература

1. Михеева, Е.В. Информатика: учебник для средн. професс. образования / Е.В. Михеева, О.И. Титова. М.: Изд. центр «Академия», 2014. – 352 с. [Допущено Мин образованием России]

            2. Михеева, Е.В. Практикум по информационным технологиям в профессиональной деятельности.: учеб. пособие, доп. Мин. образования РФ, реком. для студ. учрежден. средн. професс. образования / Е.В. Михеева. – М.: Издательский центр «Академия», 2015. – 256 с. [Допущено Мин образованием России]

3. Сергеева, И.И. Информатика: учебник для средн. професс. образования / И.И. Сергеева, А.А. Музалевская, Н.В. Тарасова - М.: ИД «ФОРУМ»: ИНФРА-М, 2014. – 336 с.: ил. – (Профессиональное образование). [Допущено Мин образованием России]

4. Фуфаев, Э.В. Пакеты прикладных программ: учеб. пособ. для средн. професс. образования / Э.В. Фуфаев, Л.И. Фуфаева. – М.: Издательский центр «Академия», 2013. – 352 с. [Допущено Мин образованием России]