Лабораторная работа 8. Построение графика и поверхности

Модуль 2.14. Вычисление значений функции двух переменных

Excel позволяет работать с большим количеством исходных данных, организованных в таблицы. Благодаря механизму «размножения» формул при их копировании, используя смешанную адресацию, удается проводить вычисления одновременно над многими величинами.

Можно использовать смешанные ссылки на ячейки, т.е. сочетание относительных и абсолютных ссылок, например, A$1 или $A1. Часть ссылки, не содержащая знак «$», будет обновляться при копировании, а другая часть, со знаком «$», останется без изменения.

Задание 1. Проектирование на рабочем листе таблицы умножения.

1. Откройте книгу Упражнения.xlsx.

2. Добавьте в книгу новый лист с именем Упр.14.

3. Создание горизонтального заголовка с использованием арифметической прогрессии:

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

с помощью команды Заполнить, Прогрессия укажите расположение – по строкам, тип прогрессии – арифметическая, шаг – 1, предельное значение – 10.

4. Создание вертикального заголовка с помощью транспонирования:

разместите ту же последовательность в первом столбце, в диапазоне А2:А11. Для этого необходимо научиться выделять диапазон, выходящий за пределы экрана. Существует несколько приемов: нажать клавиши Shift + Ctrl + à. Здесь Ctrl + à – перемещение к последней заполненной ячейке, а Shift – выделение; нажать Ctrl + * (звездочка берется на цифровой клавиатуре). Это выделение текущей области, т.е. области, содержащей активную ячейку и ограниченной пустыми строками и столбцами.

установите курсор в ячейку В1 и воспользуйтесь любой клавиатурной комбинацией;

скопируйте выделенный диапазон в буфер обмена, нажав клавиатурную комбинацию Ctrl + C;

выделите ячейку А2 правой кнопкой мыши и в контекстном меню выберите команду Специальная вставка. Установите флажок Транспонировать и нажмите на ОK.

5. Заполнение таблицы:

- В ячейку В2 введите формулу=А2*В1, которой потом можно будет заполнить весь диапазон В2:11.

- Видоизмените формулу, чтобы в первом сомножителе не менялось обозначение столбца А, номер строки при этом должен изменяться, а во втором сомножителе неизменным должен быть номер строки 1, в то время как имя столбца должно изменяться. Для этого необходимо применять так называемую смешанную адресацию, т.е. поставить знаки доллара перед одним из компонентов адреса. Пользуясь функциональной клавишей F4, добейтесь следующего вида формулы в ячейке В2=$A2*B$1.

- Установите курсор в ячейку В2 и нажмите на клавиши Ctrl + Shift + End. Будет выделена область В2:К11;

- Последовательно нажмите клавиши Ctrl + D (копирование вниз) и Ctrl + R (копирование вправо). Таблица создана.

6. Форматирование таблицы:

- диапазоны В1:В11 и А2:А11 отобразите полужирным шрифтом;

- поместите курсор внутрь таблицы, выделите текущую область с помощью комбинации клавиш Ctrl + *.

Задание 2. Вычисление значений функции Z=X²+Y.

1. Введите последовательность значений Х в столбец А, начиная с А16 (2, 4, 6....). Ячейка А15 должна остаться пустой.

2. Введите последовательность значений Y в строку 15, начиная с ячейки В16 (1, 3, 5......).

3. Введите в ячейку В2 формулу =А16^2+B15. Чтобы эту формулу распространить по всей таблице в результате применения операции заполнения, видоизмените формулу самостоятельно, используя смешанную адресацию (см. задание 1, п.3).

4. В результате вычислений должна получиться следующая таблица (рис. 14.1).

Рис. 14.1.

Лабораторная работа 8. Построение графика и поверхности

Откройте книгу Лабораторные работы.xls и на листе Лаб_8 выполните следующие задания.

Задание 1. Построение графика функции Y=X³-12X²+3.

График в Excel строится по точкам. Исходные данные для графика располагаются в строках или столбцах:

- расположите значения аргумента в столбце А, а значения функции – в столбце В;

- разместите в строке 1 обозначения осей графика (X и Y);

- запишите в ячейку А2 начальное значение аргумента: -5, приращение аргумента равно 1, предельное значение равно +15;

- в ячейку В2 введите формулу Y=X3-12X2+3, используя смешанную адресацию.

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

Рис. 14.2. График функции Y=X³-12X²+3

Задание 2. Построение диаграмму в виде поверхности по формуле Y=Exp(-((X1-2)²-(X1-2)(X2-1)+(X2-1)²)).

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

- Х1 меняется от 0 до 3,5 приращение аргумента - 0,25;

- Х2 меняется от 0 до 4,5 минимальное значение - 0, приращение аргумента - 0,25;

- строка 1 должна содержать значения Х1, а столбец А – значения Х2.

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

Диаграмму с поверхностью расположите под исходной таблицей (рис. 14.3).

Рис. 14.3. График функции Y=Exp(-((X1-2)²-(X1-2)(X2-1)+(X2-1)²))

Задание 3. Расчет числа аварий.

На трех заводах «Альфа», «Плутон» и «Рубин» происходили аварии.

1. Введите исходные данные о количестве аварий по годам и предприятиям (рис. 14.4).

Год	«Альфа»	«Плутон»	«Рубин»	Всего аварий по годам
				?
				?
				?
				?
Всего аварий по предприятиям	?	?	?	?

Рис. 14.4. Исходные данные

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

3. Представьте количественные значения аварий в процентах (рис. 14.5), принимая за 100% суммарное число аварий за текущий год. Используйте в формулах смешанную адресацию.

Год	«Альфа»	«Плутон»	«Рубин»	Всего аварий по годам
	40%	0%	60%	100%
	25%	50%	25%	100%
	40%	60%	0%	100%
	25%	50%	25%	100%

Рис. 14.5. Число аварий по годам

4. Рассчитайте число аварий по предприятиям в процентах (рис. 14.6), принимая за 100% суммарное число аварий на каждом предприятии. Используйте в формуле смешанную адресацию.

Год	«Альфа»	«Плутон»	«Рубин»
	33%	0%	60%
	17%	29%	20%
	33%	43%	0%
	17%	29%	20%
Всего аварий по предприятиям	100%	100%	100%

Рис. 14.6. Число аварий по предприятиям

Вопросы для самоконтроля

1. Как обозначается смешанная ссылка?

2. Для чего применяется смешанная адресация?

Тесты

1. Какой вид ссылки является смешанной?

A. А1

B. $A$1

C. $A1

D. A$1

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

A. Условные и безусловные.

B. Абсолютные и условные.

C. Относительные, абсолютные и смешанные.

D. Постоянные и изменяемые.

3. Какую клавишу следует нажать, чтобы ссылка на ячейку стала смешанной?

A. F2

B. F4

C. ENTER

D. ESC