Лабораторная работа: решение уравнений

Корни уравнения Y= f(x) – это значения х, при которых Y обращается в 0 (график функции пересекает ось абсцисс).

1. Построить параболу с двумя корнями, найти корни, используя функцию Подбор параметра: сделать активной ячейку Y вблизи одного из корней, вызвать Подбор параметра (в Меню Сервис), заставить компьютер подобрать х, чтобы Y обратился в 0.

2. Найти корни, двигая мышью точку графика в 0. Компьютер сам вызовет Подбор параметра.

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

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

Лабораторная работа: решение систем уравнений

Решите систему из трех уравнений с тремя неизвестными вида a_iX₊b_iY+c_iZ=d_i (i = 1,2,3), используя команду Сервис-Поиск решения. Для этого внесите в таблицу приблизительные значения неизвестных X, Y, Z, значения коэффициентов при этих неизвестных a_i, b_i, c_i (i = 1,2,3); перемножьте X, Y, Z на соответствующие коэффициенты и просуммируйте произведения по строкам. Запустите Поиск решения; В качестве целевой ячейки возьмите первую сумму, задайте установку в ней значения первого свободного члена d₁, изменяя ячейки X, Y, Z (поставьте курсор в окно Изменяя ячейки и проведите курсором по X, Y, Z; на две другие суммы наложите ограничения: равенство двум другим свободным членам d₂ и d₃; нажмите кнопку “ Параметры ” и ознакомьтесь с параметрами и методами, используемыми при оптимизационных расчетах; закройте окно “ Параметры ”, нажав ОК, и запустите выполнение программы (Выполнить). Пример:

	C	D	E	F	Комментарии
	X	Y	Z		Неизвестные
	a1	b1	c1	d1	Коэффициенты в
	a2	b2	c2	d2	уравнениях
	a3	b3	c3	d3
	a1*X	b1* Y	c1 *Z	=S(C9:E9)	F9-целевая, = d1
	a2*X	b2 *Y	c2 *Z	=S(C10:E10)	Ограничения: F10=d2,F11=d3
	a3*X	b3 *Y	c3 *Z	=S(C11:E11)

Лабораторная работа № 17: Планирование закупок

Основная цель планирования любой деятельности - получение максимального результата (прибыли, объема производства и т.п.) при имеющихся ограничениях. Разработке оптимальных программ-планов посвящен раздел математики под названием “математическое программирование”. Стандартная формулировка задачи математического программирования: требуется найти минимум (или максимум ) целевой функции, наиболее полно характеризующей бизнес-процесс, при наложенных ограничениях-равенствах и ограничениях-неравенствах. Допустимое решение, отвечающее этим условиям, называется оптимальным планом. Его может не существовать, если наложенные ограничения противоречивы, а иногда может существовать множество решений (например, если все заводы расположены рядом и безразлично, с какого возить продукцию). Если целевая функция и ограничения задаются линейными уравнениями, то метод называется “линейное программирование”.

Для решения задач линейного программирования используются различные методы (Ньютона, наискорейшего спуска, симплекс-метод), общий принцип которых таков: выбирается неоптимальный опорный план (аналогично приблизительным значениям X, Y, Z в Лаб. № 15), и его параметры варьируются с целью последовательного улучшения плана, то есть оптимизации целевой функции с использованием опции Поиск решения, что дает возможность решать оптимизационные задачи, не вникая в сложную математику.

1. Составьте рацион для коровы из 4 видов кормов, содержащих 4 компонента (жиры, белки, углеводы, витамины), имеющий минимальную стоимость:

ü составьте таблицу по приведенному образцу; рацион (количество кормов) задайте произвольно;

ü перемножьте содержание компонент в кормах и их цены на количество соответствующих кормов (используйте копирование формулы);

ü просуммируйте результаты умножения по столбикам (результаты – сколько всего компонент будет съедено и сколько это стоит);

ü вызовите Сервис – Поиск решения;

ü задайте Целевую ячейку с суммарной стоимостью (здесь F18), цель – Минимальное значение,

ü Изменяя ячейки с количеством кормов (здесь G8:G11),

ü Добавить Ограничения: суммарное потребление компонент должно быть не меньше норм (здесь B16:E16 ³ B6:E6) и количество кормов не может быть отрицательным (здесь G8:G11 ³ 0);

ü ознакомьтесь с Параметрами и нажмите Выполнить.

	A	B	C	D	E	F	G
		жиры	белки	углеводы	витамины	цена	количество
	нормы
	Корма
	Сено
	Овес
	Ячмень
	Силос

	Сено	=B8*$G8
	Овес
	Ячмень
	Силос
	Сумма	=S(B13:B16)

Лабораторная работа № 18: Планирование перевозок

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

Создайте таблицы:

· расстояния между заводами и стройками,

· потребности строек (строка),

· мощности заводов (столбец)

· первоначальный план перевозок - количество рейсов с i-го завода

на j-ю стройку:

Ячейка	C	D	E	F	I	J
	Р а с с т о я н и я
		Стройка1	Стройка2	Стройка3	Стройка4	Планы заводов
	Завод 1
	Завод 2
	Завод 3
	Потребности строек					S (D8:I*)=S(J5:J7)
	План перевозок (число рейсов с заводов на стройки)	Вывезено с заводов
	Завод 1					=CУММА(D9:I9)
	Завод 2					=CУММА(D10:I10)
	Завод 3					=CУММА(D11:I11)
	Завезено на стройки	S(D10: D12)	S(E10: E12)	S(F10: F12)	S(I10: I12)
		Число рейсов * расстояния	Cуммарный
	Завод 1	=D10*D5				пробег
	Завод 2	Скопируйте формулу на всю таблицу	всех машин
	Завод 3					=СУММА(D14:I16)

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

- Запустите Сервис - Поиск решения и заполните окна появившейся экранной формы. Целевая ячейка в данном случае - J17, в которой находится суммарный пробег машин со всех заводов на все стройки, и значение в которой надо сделать минимальным (или заданным, если надо “нагнать” план по километражу). Изменять можно ячейки D10 - I12 (план перевозок) при условии равенства мощностей заводов и потребностей строек, то есть ячеек J10 - J12 и D13 - I13 значениям, заданным в J5 - J7 и D8 - I8. Кроме того, следует задать условие, что количества рейсов - величины положительные и целые. Запустите выполнение программы (Выполнить).