Лабораторная работа №6. Математические формулы и ссылки в Excel

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

Методические указания

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

При вычислении математических выражений по формуле Excel руководствуется следующими традиционными правилами, определяющими приоритет выполнения операций:

· В первую очередь вычисляются выражения внутри круглых скобок,

· Определяются значения, возвращаемые встроенными функциями,

· Выполняются операции возведения в степень (^), затем умножения (*) и деления (/), а после – сложения (+) и вычитания (-).

Необходимо помнить, что операции с одинаковым приоритетом выполняются слева направо.

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

Существует два способа, равноценных последнему, но не требующих предварительного ввода знака равенства:

· Через пункт меню Вставка \ Функция,

· С помощью кнопки Вставка функции на панели инструментов.

Функция определяется за два шага. На первом шаге в открывшемся окне диалога Мастер функций необходимо сначала выбрать категорию в списке Категория, а затем в алфавитном списке Функция выделить необходимую функцию. На втором шаге задаются аргументы функций. Второе окно диалога мастер функций содержит по одному полю для каждого аргумента выбранной функции. Если функция имеет переменное число аргументов, то окно диалога увеличивается при вводе дополнительных аргументов. После задания аргументов необходимо нажать кнопку ОК или клавишу Enter.

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

В Excel различают ссылки трех типов:

· Относительные,

· Абсолютные,

· Смешанные.

Существуют два стиля оформления ссылок:

· Стиль А1 или основной,

· Стиль R1C1.

Далее будем рассматривать только основной стиль.

Относительная ссылка. Необходимо различать отображаемое и хранимое значения.

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

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

Например, в формулах =А1+В1 и =А9+В9, находящихся в ячейках В5 и В13 (рис. 21), отображаемым значениям А1 и А9 соответствуют одинаковые хранимые значения: <текущий столбец - 1> <текущая строка - 4>.

Рисунок 20 Относительная ссылка

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

Абсолютная ссылка всегда указывает на зафиксированную при создании ячейку или диапазон и не изменяется при переносе или копировании формулы в другую ячейку. Механизм абсолютной адресации включается в двух случаях:

· При записи знака $ перед именем столбца и номером строки (рис. 21),

· При использовании имени ячейки.

Рисунок 21 Абсолютная ссылка

Смешанные ссылки представляют собой комбинацию из относительных и абсолютных ссылок. Можно определить два типа смешанных ссылок:

· Смешанная ссылка первого типа,

· Смешанная ссылка второго типа.

В смешанной ссылке первого типа символ $ стоит перед буквой, поэтому координата столбца рассматривается как абсолютная, а координата строки – как относительная (рис. 22).

Рисунок 22 Смешанная ссылка первого типа

В смешанной ссылке второго типа символ $ стоит перед числом, поэтому координата столбца рассматривается как относительная, а координата строки – как абсолютная (рис. 23).

Рисунок 23 Смешанная ссылка второго типа

Порядок выполнения работы

1. Включите компьютер. Загрузите Excel.

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

3. Создать на рабочем листе пользовательскую таблицу, изображенную на рис. 25. Рассчитайте надбавку к зарплате по следующему правилу. Размер надбавки зависит от оклада и стажа работы. Для сотрудников, стаж работы которых от 5 до 10 лет, надбавка равна 5% от оклада; для сотрудников, стаж работы которых от 10 до 15 лет, надбавка равна 10% от оклада; для сотрудников, стаж работы которых от 15 до 20 лет, надбавка равна 15% от оклада и так далее.

При выполнении расчетов необходимо использовать смешанные ссылки.

Рисунок 24 Пользовательская таблица к заданию

Рисунок 25 Пользовательская таблица к заданию

Порядок оформления отчета

Подготовьте отчет о выполненной лабораторной работе. Отчет о лабораторной работе должен содержать: титульный лист (с действующим вариантом титульного листа можно ознакомиться на http://standarts.guap.ru), цель лабораторной работы, полученные в ходе выполнения работы документы. На компьютере представляются файлы с результатами работы, записанные в папку с номером вашей группы/ваша фамилия/№ лабораторной работы. Сформулируйте выводы, которые можно сделать по результатам выполненной работы.

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

1. Каковы основные правила составления формул в Excel и особенности вызова встроенных математических функций?

2. Какие типы ссылок используются в Excel?

3. В чем разница между типами ссылок используемых в Excel?

4. Какие стили ссылок существуют в Excel?

5. В чем разница между отображаемым и хранимым значением?

6. На что указывает абсолютная ссылка?

Какие типы смешанных ссылок вы знаете?

Когда используется абсолютная ссылка?

Когда используется относительная ссылка?

Когда используется смешанная ссылка?