method_excel

Задания для самостоятельной работы

1. Рассчитать число витков L катушки на броневом цилиндриче-

F

ском ферромагнитном сердечнике при различных значениях индуктив-

ности L (L изменяется от 3·105 нГн до 5·105 нГн с шагом 5000 нГн).

						1			1						1		d				d
																			3			4
F	19.74											B
			,	A (h1 h2 )							,					ln
		A B			d 2	d 2		d 2	d 2				h	2	h			d		1	d
					4	3	2		1						1							2

d1 1см, d2 2см, d3 4см, d4 5см, h1 3см, h2 4см, 10 .

Предусмотреть создание электронной расчетной таблицы. Построить

график изменения числа витков .

Расчетная таблица

Индуктивность		Промежуточные данные				Число витков
(нГн)
L	A		B		F
3E5
3,05E5
…
5E5

2.Рассчитать взаимную индуктивность M двух тонких проводов длиной l=10 см, расположенных параллельно при различных значениях рас-

стояния h между ними (h изменяется от 1 см до 5 см с шагом 0,5 см).

Построить таблицу зависимости M(h).

l

h

, где S

h2

1

Расчетная таблица

M 2l arsh

S

h

l

l 2

Расстояние

S

Индуктивность

Обратный гиперболический синус

h (см)

M (нГн)

arsh(z) ln(z

z

2

1) .

1

1,5

…

5

3. Построить таблицу и диаграмму изменения силы тока I 3 sin( t )

6

в интервале t [0; 0,004] через 0,0005. Угловая частота изменения напря-

жения 2 f , где f=250 Гц.

32

Created with novaPDF Printer (www.novaPDF.com)

Лабораторная работа №2

Построение графиков функций и поверхностей с помощью Мастера диаграмм

Задача 1. Постройте график функции y x cos ( x 3 ) ,

где x [-4 , 4 ] с шагом /2.

	Решение в Excel
	Заполнение столбца X
1.	Введите x в ячейку A1, а y в B1.	3. Введите в A3 правило заполнения
2.	Введите формулу = -4*ПИ() в	ячеек: предыдущее значение плюс
	ячейку A2.	шаг заполнения =A2+ПИ()/2.

4. В столбце x скопируйте Марке-	5. Выделите диапазон ячеек, со-
ром заполнения формулу из А3	держащий значения аргумента,
в другие ячейки диапазона до	и присвойте имя x (меню
значения 4 (12,566).	Вставка, Имя , Присвоить).

33

Created with novaPDF Printer (www.novaPDF.com)

Заполнение столбца Y

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

функций .

1.Введите в ячейку B2 формулу =x*COS(x+3) и нажмите Enter.

2.Заполните столбец Y значениями с помощью Маркера заполнения.

Построение графика

Для построения графика, выделите диапазон значений A1:B18 и выбе-

рите Мастер диаграмм. Переход к следующему шагу построения диа-

граммы выполняйте кнопкой Далее>.

Выберите тип диаграммы Точечная, гладкого вида без маркеров.

Укажите место расположения данных на листе (в строках или столб-

цах).

Оформите, если нужно, Заголовки, Оси, Линии сетки, Легенду, Под-

писи данных.

Укажите место размещения диаграммы (на отдельном или текущем листе). Выберите Готово.

34

Created with novaPDF Printer (www.novaPDF.com)

Задача 2. В системе электронных таблиц MS Excel постройте график

функции, которая заданна параметрически:

x 2a cos t a cos 2t , y 2a sint a sin2t ,

где a=5, переменная t изменяется на отрезке [-2 , 2 ] с шагом /6.

Задача 3. Постройте поверхность x 2 y 2 2 z , где x [-5, 4] с

шагом 1, y [-5, 5] с шагом 1.

Решение в Excel

1.Заполните диапазон ячеек A2:A11 значе-

ниями аргумента х от –5 до 4 с шагом 1.

2.Аналогично заполните диапазон ячеек

B1:L1 значениями аргумента y от –5 до

5 с шагом 1.

3.Закрасьте диапазоны ячеек A2:A11 и B1:L1 произвольным цветом за-

ливки .

4.Выделите диапазон ячеек A2:A11 и присвойте имя x, выбрав меню

Вставка, Имя, Присвоить. Аналогично присвойте диапазону ячеек

B1:L1 имя y.

35

Created with novaPDF Printer (www.novaPDF.com)

5. Введите в ячейку B2 формулу поверхности.

6.Маркер заполнения ячейки B2 протащите вниз, копируя формулу по столбцу. Не снимая выделения, протащите Маркер заполнения вправо, за-

полняя оставшиеся ячейки таблицы значениями z для всех пар x и y.

7.Постройте диаграмму по диапазону ячеек A1:L11, выбрав тип Поверх-

ность непрозрачного вида.

8.Аналогично, постройте линии уровня, выбрав тип Поверхность, контур-

ная диаграмма прозрачного вида.

36

Created with novaPDF Printer (www.novaPDF.com)

Задания для самостоятельной работы

1. Построить графики функций:

a.

y

e x

sin 2

x , где x [-4 , 4 ] с шагом 1.

b.

y

1 cos

t 2 , где t [-3 , 3 ] с шагом

.

4

2. Построить на одной координатной плоскости графики функций:

a. y1

и y 2

3 ,5

x

, где x [-2, 2] с шагом 0,2.

cos 3 3x 5

x 3

23

b.

f ( x ) sin

2

( 2x

),

x

, где x [-1, 3] с шагом 0,1.

3

g( x )

cos

2

4

3.Рассчитать индуктивность L однослойной катушки круглого сечения со средним радиусом a=1см, числом витков 10. Значения длины ка-

тушки b изменяется от 1 см до 6 см с шагом 0,5 см. Создать таблицу рас-

чета индуктивности и график зависимости L(b).

1

L F

2

,

где F 4 a

ln

1

.

b

2,3 1,6 b

0,44 b

a

a

a

4. Построить

поверхность

e t

t 2

,

где t [-5,

5] t =1;

z

t 3 y 3

2

y [- , 4 ] y=

.

3

5.Для электростатического поля двух зарядов q1 2 10 8 Кл и q2 10 8 Кл,

находящихся друг от друга на расстоянии l=0,2 м, построить поверх-

ность изменения потенциала в точках плоскости с координатами xi, yj и

эквипотенциальные поверхности поля двух зарядов в виде карт линий уровня.

z (x, y)

1

q1

1

q2

,

4

0

x2 y2

4

0

l 2 2 l x x2 y2

где 0 8,85 10 12

- электрическая постоянная;

xi

2 l

i 0,00001

l

;

yj

l

j 0,00001

l

l

; i=0..N; j=0..N; N=9.

N

2,5

N

1,9

2,5

37

Created with novaPDF Printer (www.novaPDF.com)

Лабораторная работа №3

Решение задач в Excel с помощью режима Поиск решения

Задача 1. Решите систему уравнений

2 x			y		3 z	13
		y		z		6
x
	3 x		y		z 8

Решение в Excel

1.Введите заголовки столбцов таблицы Имя, Значение, Левая часть уравнения в ячейки A1, B1, C1.

2.Заполните столбец Имя неизвестными переменными x, y, z.

3.В столбце Значение будут отображаться искомые значения неизвест-

ных переменных. Присвойте ячейкам B2, B3, B4 столбца Значение

имена x, y, z соответственно, выбрав меню Вставка, Имя, Присвоить.

4.В ячейки C2, С3, С4 столбца Левая часть уравнения введите формулы левых частей уравнений системы.

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

выберите меню Сервис, Поиск решения.

6.В окне Поиск решения установите для ячейки C2 значение 13, равное правой части первого уравнения.

7.Выделите мышью для режима Изменяя ячейки диапазон ячеек B2:B4,

в которых «ожидается» результат .

8.В режиме Ограничение через кнопку Добавить введите ограничения: $C$3=6, $C$4=8.

9. В окне Поиск решения выберите режим Выполнить.

38

Created with novaPDF Printer (www.novaPDF.com)

10.В окне Результаты поиска решений сохраните полученный результат.

Решение задач оптимизации методом математического мо-

делирования

Математическая модель представляет собой систему математи-

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

сы, с большей или меньшей точностью происходящие в оригинале.

Задача 2. Для изготовления трех видов изделий P1, P2, P3 используют четыре вида материалов: S1, S2, S3, S4. Составить план выпуска изделий,

обеспечивающий их максимальный выпуск по стоимости.

Технологические нормы расхода материалов на каждое изделие и цена изделия

	Вид	Запас	Норма расхода материалов на одно изделие, кг
		материала,
	материала		Р1	Р2	Р3
		кг
	S1	150 000	4	2	1
	S2	170 000	6	0	2
	S3	100 000	0	2	4
	S4	200 000	8	7	0
	Цена одного изделия, усл. ед		100	150	200

39

Created with novaPDF Printer (www.novaPDF.com)

Решение в Excel

Обозначим за неизвестные x, y, z количество изделий вида P1, P2, P3

соответственно.

Для решения данной задачи построим математическую модель:

	Целевая функция	Ограничения ресурсов	Условие
	f max 100x 150y 200z	4x 2y z 150000	неотрицательности
		6x 2z 170000	x 0	y 0	z 0

2y 4z 100000

8x 7 y 200000

Задания для самостоятельной работы

1. Решить системы уравнений:

3,2x 1,5y 0,5z 0,9	2,1x 4,5y 2z 19,07
a) 1,6x 2,5y z 1,55	b) 3x 2,5y 4,3z 3,21
x 4,1y 1,5z 2,08	6x 3,5y 2,5z 18,25
x y z t 1	8,3x 2,62y 4,1z 1,9t 10,65
x 2y 3z t 2	3,92x 8,45y 7,78z 2,46t 12,21
c)	d)
3x y z t 6	3,77x 7,21y 8,04z 2,28t 15,45
x y z 3t 2	2,21x 3,65y 1,69z 6,99t 8,35

40

Created with novaPDF Printer (www.novaPDF.com)

2. Найти наибольшее и наименьшее значение функций:

a)

y(x)=x2 – 4x+6

на отрезке [-1; 5];

b)

y( x ) x3

на отрезке [-5; 6];

x 1

c)

y( x )

x2

3x 2

на отрезке [-4; 2];

x2 2x 1

d)

y( x )

x

e

x 1

на отрезке [-3; 4].

3.Требуется составить план перевозок, ми-

нимизируя сумму транспортных затрат.

Известны объемы груза в хранилищах: b1=400т, b2=250т, b3=500т, b4=350т; запросы потребителей: a1=600т, a2=450т, a3=450т.

Транспортные издержки отражены в мат-

рице Сij.

	40		70	20
			20	30
Сij	10
		20	40	10	Хранилища
		30	20	50

Потребители

4.Вычислить общее сопротивление R0 и силы тока I1, I2, I3, I4, I5 на каждом участке электрической цепи:

I0=10 A

A

	R1=1 Oм		B			R4=3 Ом
	I1					I4

I0=10 A

	I3
			C
I2			I5

R2=3 Ом D

R5=1 Ом

Указание.

Применяя законы Кирхгофа, составить систему уравнений из восьми линейных уравнений с шестью неизвестными и решить ее в Excel.

5.Требуется найти оптимальное сочетание посевов трех культур, при ус-

ловии, что в хозяйстве 850 га пашни, 50000 чел./дней трудовых ресурсов и 15000т органических удобрений. Критерий оптимальности – максимум

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

Затраты ресурсов и выход валовой продукции в стоимостном выражении на 1 га

Показатели			Культуры
	капуста	картофель		Многолетние травы
				на сено
Затраты труда, чел.-дней	55	40		15
Затраты органических удобрений, т	15	15		20
Выход валовой продукции, руб.	1000	900		300

41

Created with novaPDF Printer (www.novaPDF.com)