Перейдем на Лист2 и вычислим значений функции двух переменных

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

Создание элементарных формул.

Относительные, абсолютные и смешанные адреса ячеек.

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

План занятия:

1. Создание элементарных формул.

2. Относительные адреса ячеек.

3. Абсолютные адреса ячеек.

4. Смешанные адреса ячеек.

 

1. Создание элементарных формул.

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

В формуле можно вводить не только цифры с клавиатуры, но и адреса ячеек.

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

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

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

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

Основные арифметические операции, которые можно использовать в формулах:

 

Оператор	Пример	Описание
^	= 7 ^ 3	Возводит 7 в степень 3 (возведение в степень)
/	= 4 / 2	Делит 4 на 2 (деление)
*	= 3 * 4 * 5	Умножает 3 на 4 и на 5 (умножение)
+	= 5 + 5	Складывает 5 и 5 (сложение)
–	= 5 – 5	Вычитает 5 и 5 (вычитание)

 

· Создайте файл Формулы.

· Перейдите на Листе1.

· Лист1 переименуйте в

· Создайте таблицу ПРОДАЖА АВТОМОБИЛЕЙ. Введите в соответствующие ячейки данные, приведенные в таблице.

 

ПРОДАЖА АВТОМОБИЛЕЙ
Торговый агент	Филиал	Модель	Количество	Цена	Выручка	Процент от продаж	Рентабельность
Иванов	Автодель	Ваз-99			.	.	.
Иванов	Автодель	Ваз-07			.	.	.
Степанов	АвтоГАЗ	Mazda			.	.	.
Егоров	Автодель	Ford-Passat			.	.	.
Михайлов	АвтоГАЗ	Volkswagen			.	.	.
Егоров	АвтоГАЗ	Tatra			.	.	.
Светлов	АвтоЗаз	Ваз-99			.	.	.
Пекарский	АвтоЗаз	Ford-Passat			.	.	.
Михайлов	АвтоГАЗ	Ваз-07			.	.	.
Закора	АвтоГАЗ	Газель			.	.	.
Терещенко	Автодель	РАФ			.	.	.
Терещенко	Автодель	Audi-90			.	.	.
Михайлов	АвтоГАЗ	Audi-80			.	.	.
Терещенко	Автодель	Renault			.	.	.
Светлов	АвтоЗаз	Ваз-07			.	.	.
Светлов	АвтоЗаз	Ford-Passat			.	.	.
Пекарский	АвтоЗаз	Tatra			.	.	.
Пекарский	АвтоЗаз	Газель			.	.	.
.	.	.	.	.	Общая сумма выручки	.

 

· Столбцы Выручка, Процент от продаж и Рентабельность вычисляем по формулам.

Относительные адреса ячеек.

· Выручка вычисляется по формуле: = Количество*Цена.

Формулу вводим только в одну ячейку и копируем на остальной блок ячеек.

Примечание:

При копировании формул с адресами ячеек Excel изменяет адреса, если это относительные адреса.

Относительный адрес – это адрес, который ссылается на ячейку, основываясь на ее текущем местоположении (например, если в A1 ввели формулу =A2+A3, то если вы скопируете формулу в ячейку B5 – в ней будет храниться следующая формула: =B6+B7). Исходные и относительные адреса были изменены с учетом нового положения скопированной формулы. Содержимое ячейки A1 не изменилось, а в ячейке, скопированной по адресу B5, хранится та же формула, но относящаяся к B5, а не к A1.

· Выведем Общую сумму выручки в последней ячейке столбца Выручка. Для этого воспользуемся функцией Автосумма.

· Установим курсор в последнюю ячейку столбца Выручка. На панели инструментов Стандартная нажимаем кнопку Автосумма. Весь суммируемый блок ячеек выделен пунктирной рамкой. Убедимся, что выделен необходимый блок ячеек (т.е. весь столбец Выручка) и нажимаем ENTER. Т.о. нашли общую сумму выручки.

· Процент от продаж = Выручка * 3%

Абсолютные адреса ячеек.

· Для нахождения Рентабельности воспользуемся абсолютной адресацией.

Абсолютный адрес – это адрес, который не изменяется при копировании формулы. Это адрес, который ссылается на ячейку, используя только ее точный адрес. Перед абсолютным адресом всегда ставится знак доллара $.

· Рентабельность = Выручка / Общую сумму выручки.

Т.к. Общая сумма выручки при копировании не должна изменяться, в формуле определения рентабельности перед адресом Общей суммы выручки необходимо поставить знак $ ( с помощью клавиши F4 ). Например, в ячейке G3 следующая формула: =F3/$F$21

· Как и при вычислении Выручки формулу Рентабельности вводим в первую ячейку и копируем на весь блок ячеек. Адрес Общей суммы выручки не должен меняться, т.к. это абсолютный адрес.

· Вычислим сумму рентабельности по столбцу (функция Автосумма). Сумма рентабельности =1.

Смешанные адреас ячеек.

Перейдем на Лист2 и вычислим значений функции двух переменных.

Необходимо вычислить значения функции

Z=x²+y, где x = 2, 4,6, 8,....., y = 1, 3, 5, 7....

· Введите последовательность значений x в столбец А, начиная с ячейки А2. Ячейка А1 должна остаться пустой.

· Введите последовательность значений y в строку 1. Начиная с ячейки В1.

· Введите в ячейку В2 формулу = $A2^2 + B$1

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

· Выделите ячейку В2 и заполните формулами строку 2

· Выделите ячейку В2 и заполните формулами столбец В.

· Повторите предыдущие действия для остальных столбцов таблицы.

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

· Аналогично создайте таблицу значений функции z = -5x² + 3y на Листе3.

 

Таблица значений функции z =x^2 + y

· Сохраните файл в С:\Мои документы\Студент\Группа№…\Создание формул.doc

· Скопируйте файл Создание формул.doc на диск 3,5 А.

 

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

1. Как осуществляется ввод формул в Excel?

2. Что такое относительные адреса ячеек?

3. Как изменяется относительный адрес при копировании?

4. Что такое абсолютные адреса ячеек?

5. Как изменяется абсолютный адрес при копировании?

6. Что такое смешанные адреса ячеек?

7. Как изменяется смешанный адрес при копировании?