Инженерные расчеты в Exel

Задание 3. С использованием средств Excel построить графики функций y=cos(x) и y=x2 на отрезке [-1;1] с шагом 0,1 совместно на одной диаграмме.

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

1.Откройте новый лист Excel.

2.По аналогии с предыдущим заданием сформируйте электронную форму для построения двух графиков и введите исходные данные:

3.По аналогии с предыдущими заданиями заполните столбцы соответствующими формулами.

8.Для построения графика заданной функции вызовите Мастер диаграмм, выберите тип диаграммы: Точечная и перейдите на вкладку Ряд.

9.Далее нажмите кнопку Добавить и введите значения рядов для первого графика, затем нажмите кнопку Добавить еще раз и введите значения рядов для второго графика.

					Графики
1,20
1,00
0,80										y=sin(x)
0,60
										y=x2
0,40
0,20
0,00					0					1
-1	0,8	0,6	0,4	0,2		0,2	0,4	0,6	0,8
	-	-	-	-

11

Лабораторная работа № 3. Обработка результатов экспериментов

Задание 1. Имеются результаты эксперимента по определению времени падения шара массой 1 кг с разной высоты:

№ эксперим.	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15
Высота (м)	4,9	7,1	9,7	9,6	9,6	12,6	15,9	19,7	19,6	19,7	23,7	26,0	30,6	33,1	33,2
Время	1,0	1,2	1,4	1,4	1,4	1,6	1,8	2,0	2,0	2,0	2,2	2,3	2,5	2,6	2,6
падения (сек)

Обработать результаты эксперимента:

1)Построить точечную диаграмму, отображающую зависимость времени падения от высоты.

2)При помощи полиномиальной линии тренда найти уравнение экспериментальной зависимости.

3)Спрогнозировать время падения шара с высоты 14 м и 40 м.

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

1.Откройте новый лист Excel.

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

3.Постройте линию тренда, аппроксимирующую экспериментальную зависимость. Для этого:

1)Выделите точки на графике, щелкните правую кнопку мыши и выберите пункт Добавить линию тренда…

2)В появившемся окне выберите полиномиальный вид тренда степени 2, согласно условию задачи:

12

3)Перейдите на вкладку Параметры, установите флажки вывода уравнения за-

висимости и коэффициент достоверности аппроксимации, а также осуще-

ствите прогноз вперед на 5 ед. (см. образец ниже).

4)Нажмите клавишу ОК.

4.Выведенное уравнение зависимости и коэффициент R2 сместите вправо, в соответствии с образцом ниже:

	3,0		Результаты эксперимента
			Эксперимент
	2,5
сек.)
	2,0		Полиномиальный
(в	1,5		(Эксперимент)
Время
	1,0
	0,5		y = -0,0008x2 + 0,0858x + 0,6307
			R2 = 0,9992
	0,0
	0,0	5,0	10,0 15,0 20,0 25,0 30,0 35,0 40,0 45,0
			Высота (в м)

Коэффициент достоверности аппроксимации R2 может изменяться в пределах от 0 до 1. Полученное значение R2 =0,9992 означает, что точность аппроксимации экспериментальной зависимости выбранным типом тренда составляет 99,92%.

5.Ниже в форме зарезервируйте две свободные ячейки и, воспользовавшись найденным уравнением, сделайте прогноз времени падения шара с высоты 14 и 40 м.

6.Сохраните полученный результат.

13

Лабораторная работа № 4.

Методика построения графиков объемных функций

Задание 1. С использованием средств MS Excel построить изображение трехмерной функции в диапазоне изменения X и Y от -10 до 10 с шагом 1, называемой “Ковбойская шляпа” и определяемой уравнением:

Z cos(x2 y 2 )

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

1.На листе Excel для табулирования значений заданной функции подготовьте экранную форму в соответствии со следующим образцом:

Для задания диапазонов изменения переменных X и Y введите в ячейки А9 и В8 ссылки на начальные значения переменных (соответственно: =А4 и =А5). Далее в ячейки А10 и С8 введите формулы увеличения значения переменных в зависимости от шага (соответственно =А9+$В$6 и =В8+$В$6) и протяните введенные формулы соответственно в вертикальном и горизонтальном положении.

2.Проведите заполнение предложенной таблицы. Для этого можно воспользоваться следующим способом:

1)Введите в ячейку В9 формулу сложной функции, содержащей смешанные ссылки на ячейки: =3*COS(КОРЕНЬ($А9^2+В$8^2)). Здесь знак $ перед буквой в координате $А9 означает, что при любом протягивании (вертикальном или горизонтальном) положение столбца А смещаться НЕ БУДЕТ, согласно правилам использования относительных и абсолютных ссылок, аналогично знак $ перед цифрой 8 в координате В$8 фиксирует положение строки 8 листа Excel.

14

2)Выделите введенную формулу и протяните вправо, заполнив первую строчку таблицы:

3)Далее, выделив всю строчку целиком, протяните вниз, и таблица автоматически заполнится соответствующими значениями.

3.Постройте график объемной функции. Для этого:

1)Выделите всю таблицу, НЕ ВКЛЮЧАЯ диапазоны изменения переменных, и нажмите кнопку на панели инструментов: Мастер диаграмм.

2)Далее в появившемся окне выберите тип диаграммы: Поверхность и нажмите кнопку Далее.

3)Перейдите на вкладку Ряд и заполните поля Имя и Подписи оси X. Нажмите кнопку Далее.

4)В следующем окне последовательно выбирая закладки и заполняя соответствующие поля, укажите название диаграммы, подпишите оси, выведите для каждой оси основные линии и уберите отображение легенды. Нажмите кнопку

Далее.

5)Укажите расположение диаграммы на текущем листе Excel и нажмите кнопку

Готово.

6)Для изменения формата проекции выделите диаграмму, нажмите правую кнопку мыши и выберите пункт меню Объемный вид. В открывшемся окне настройте формат проекции, указав Возвышение, равным 50, а Поворот, равным 30 (другие параметры не меняйте) и нажмите кнопку ОК.

"Ковбойская шляпа"

4
2
z 0
-2
-4
-10	-6

-2

x

10

6

		2
		-2
		y
2		-6
	6	-10
		10

15

Лабораторная работа № 5. Изучение матричных операций в среде MS Excel

Задание 1. С использованием средств Excel выполнить следующие матричные операции в соответствии с методическими указаниями:

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

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

Внимание! Для задания верхних (или нижних индексов) воспользуйтесь пунктом меню Формат/Ячейки/Шрифт и укажите Надстрочный или Подстрочный.

5.Выполните первое матричное действие: сложение матриц А и В. Для этого:

1)Выделите мышкой место под результат (диапазон D9:G12).

2)Поставьте знак равно и запишите сумму в соответствии с образцом ниже (диапазоны соответствующих матриц следует выделять с помощью мышки):

16

3)ОБЯЗАТЕЛЬНО завершите выполнение работы одновременным нажатием клавиш Shift+Ctrl+Enter. Данная операция распространяет действие формулы

на весь выделенный диапазон.

Аналогично можно выполнять и другие арифметические действия НАД ЭЛЕМЕНТАМИ матриц.

6.Выполните второе матричное действие: умножение матриц А и В по правилам матричного умножения. Это умножение возможно, так как число столбцов матрицы А совпадает с числом строк матрицы В. Для этого:

1)Отметьте место под матрицу-результат.

2)Обратитесь к мастеру функций, найдите функцию МУМНОЖ и выполните постановку задачи так, как показано ниже:

Здесь в качестве массива 1 следует указать диапазон адресов матрицы А, а в качестве массива 2 – диапазон адресов матрицы В.

3)Для получения результата нажмите одновременно клавиши Shift+Ctrl+Enter.

7.Осуществите обращение суммы матриц А и В, полученной на предыдущем шаге. Для этого:

1)Отметьте место для матрицы-результата.

2)Обратитесь к мастеру функций, найти функцию МОБР и выполните постановку задачи:

17

3)Завершить выполнение работы одновременным нажатием клавиш Shift+Ctrl+Enter

8.Осуществите транспонирование произведения матриц А и В, полученного на предыдущих этапах. Для этого:

1)Отметьте место для матрицы-результата.

2)Обратитесь к мастеру функций, найти функцию ТРАНСП и выполните постановку задачи:

4)Завершить выполнение работы одновременным нажатием клавиш Shift+Ctrl+Enter

9.Вычислите произведение обратной матрицы А+B, полученной на предыдущих этапах, на число. Для этого:

1)Отметьте место для матрицы-результата.

2)Поставьте знак равно и запишите произведение в соответствии с образцом ниже (диапазоны соответствующих матриц следует выделять с помощью мышки):

3) Завершить выполнение работы одновременным нажатием клавиш Shift+Ctrl+Enter

10.Вычислите определитель матрицы в соответствии с заданием. Для этого:

1)Определите место под результат.

2)Обратитесь к мастеру функций, найдите функцию МОПРЕД и выполните постановку задачи в соответствии с образцом, предложенным ниже.

3)Щелкните по кнопке ОК и получите значение определителя, равное значению: -1480.

18

Задание 2. С использованием средств Excel решить систему линейных уравнений матричным методом.

8x1 x2 2x3 0,

5x1 7x2 3x3 10,2x1 x2 2x3 2.

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

1.Представьте заданную систему в матричной форме: A X B , где A матрица коэффициентов при неизвестных X, а B вектор свободных членов. Тогда:

8	1	2	x1			0
5	7	- 3	x	2		10
2	1	- 2	x	3		2

2.Расчетная формула в этом случае примет следующий вид: X A 1 B

3.Сформированная электронная форма с решением данной задачи представлена ниже:

19

ЛИТЕРАТУРА

1.Долженков И. А., Колесников Ю. В. Microsoft Excel 2003 – Наиболее полное руководство. – СПб.: БХВ-Петербург, 2006 г. - 1024 с.

2.Блаттнер П. Использование Microsoft Office Excel 2003. – М. : Вильямс, 2005.

3.Додж М. , Стинсон К. Эффективная работа с Microsoft Excel 2003. –СПб. : Питер, 2005.

4.Уокенбах Дж. Excel 2003. Библия пользователя. – М. : Диалектика, 2005.

5.Берлинер Э.М., Глазырина И.Б., Глазырнн Б.Э. Microsoft Office 2003 — М.: ООО

«Бином-Пресс», 2004 г. — 576 с

6.Хабрейкен Дж. Эффективная работа с Microsoft Office 2003. – М. : Вильямс, 2006.

7.Информатика : Базовый курс / под ред. С. В. Симоновича. – СПб. : Питер, 2006.

8.Вильямс Орвис. Excel для ученых, инженеров и студентов/ Пер. с англ. – К.:

Юниор, 1999. -528 С.

Сергей Павлович Новиков

Практика инженерно-технических расчетов в среде MS Excel

Формат 60х84 1/16 Бумага писчая. Усл. п. л. 1,86 Тираж 100 экз. Отпечатано в БФ МИИТ

241020, Брянск, ул. Красных партизан, 13А