Вокне Подбор параметрав поле Значениеввести величину — 2000

6.В поле Изменяя значение ячейки ввести ссылку на ячейку со значением величины займа или ее имя — В4.

7.ОК.

8.Чтобы сохранить полученное значение, нажать кнопку ОК в окне Результат подбора параметра, а для восстановления исходного значения — кнопку Отмена.

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

1.На р/л ПодборПараметра ввести в Е1 формулу Е2^2

2.Использовать команду Сервис|Подбор параметра для нахождения значения Е2, которое сделает Е1 равным 4.

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

• Графический подбор параметра. Excel предоставляет способ подбора параметра с помощью манипулирования графиками. Таблица

Коэффициент	1,40
	Год	Продажи
		250 000
		350 000
		490 000
		686 000
		960 400
		1 344 560
		1 882 384
		2 635 338
		3 689 473
		5 165 262
		7 231 366

отображает прогнозируемый объем продаж фирмы. Известно, что объем продаж за 2000 г. составил 250 000 $. Его хотят довести до 10 000 000. Коэффициент роста — 1,40. Как видно из таблицы, при таком коэффициенте объем продаж составит немногим более 7 000 000 $.

Для нахождения желаемого коэффициента необходимо построить диаграмму по приведенной таблице, выделить последний маркер диаграммы и увеличить его значение. В появившемся диалоговом окне Подбор параметра ввести в поле Значение величину 10 000 000, а в поле Изменяя значение ячейки - адрес ячейки, где находится коэффициент 1,40. Программа вычислит значение нового коэффициента, а диаграмма изменится автоматически.

• Поиск решения. Рассмотрим задачу линейного программирования в следующей постановке. Составить план рекламной кампании нового товара. Общий бюджет на рекламу составляет 120 000 р. Общее число публикаций рекламных объявлений желательно довести до 800 млн экз. Рекламу следует разместить в шести изданиях — Изд1, Изд2,..., Изд6. Каждое издание имеет свое количество читателей и разную стоимость печатного текста. Решение состоит в достижении заданного числа читателей с наименьшими затратами при следующих дополнительных ограничениях:

1.Общая стоимость изготовления и распространения рекламы не должна превышать 120 000 р.

2.Общее число публикаций должно быть не менее 800 млн экз.

3.В каждом издании должно появиться по крайней мере 6 объявлений.

4.Нельзя тратить больше одной трети средств на одно издание.

5.Общая стоимость размещения рекламы в Изд3 и Изд4 не должна превышать 75000 р.

Структура задачи представлена в виде таблицы:

 

Издания	Стоимость объявления	Кол-во читателей (млн)	Кол-во размещенных объявлений	Общая стоимость	Процент от общей суммы	Общее количество читателей
Изд1	1 474,2	9,9		8 845	26,3%
Изд2	1 244,1	8,4		7 465	22,2%
ИздЗ	1 131	8,2		6 786	20,1%
Изд4	700,7	5,1	6'	4 204	12,5%
Изд5		3,7		3 180	9,4%
Издб	534,4	3,6		3 206	9,5%
Всего по изданиям			33 686	100,0%
Всего по ИздЗ+Изд4		10 990

 

Ограничения	Всего расходов на рекламу		120 000
		Всего расходов на издания 3 и 4		75 000
		Минимальная аудитория (млн)
		Максимальный расход на одно издание (%)	33,33%
		Минимальное количество объявлений в издании

Решение задачи выполняется на р/л ПоискРешен по следующему алгоритму:

1.Копировать таблицу на р/л ПоискРешен.

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

3.Выполнить команду Сервис|Поиск решения.

4. В диалоговом окне Поиск решения заполнить поле Изменяя ячейки (диапазон Количество размещенных объявлений).

5. В диалоговом окне Поиск решения установить курсор в поле Ограничения и заполнить его, используя кнопку Добавить.

6.После ввода последнего ограничения в диалоговом окне Добавление ограничения вместо кнопки Добавить нажать кнопку ОК.

7.В диалоговом окне Поиск решения нажать кнопку Выполнить.

Задание 3. Изучите решение транспортной задачи.

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

Транспортная задача является одной из наиболее распространенных задач линейного программирования. Она находит широкое практическое применение.

Постановка задачи. Некоторый продукт, сосредоточенный у т поставщиков А. в количестве а_i (i = 1, 2,..., т), необходимо доставить п потребителям В в количестве b_j, (J = 1, 2,..., п). Модель, в которой суммарные запасы равны суммарным потребностям, т. е.

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

Известна стоимость с. перевозки груза от i -го поставщика j -му потребителю. Количество груза, перевозимого от i -го поставщика к j -му потребителю —х_ij. Стоимость перевозки с_ij * x_ij Нужно минимизировать стоимость всего плана перевозок

Система ограничений:

1. Все грузы должны быть перевезены

2. Все потребности должны быть удовлетворены

Таким образом, математическая модель имеет вид: найти

при ограничениях

 

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

 

Поставщики	Потребители

 

Алгоритм решения задачи:

1.В р/к вставить новый р/л под именем ТранспортнЗадача.

2. В диапазон В10:Е13 ввести стоимости перевозок из пп. А_i.в пп. B_j.

Примечание. Диапазон изменяется в зависимости от размерности задачи.

3.Диапазон решений ВЗ:Е6, соответствующий по размерам диапазону стоимостей перевозок, не заполнять!

4.В диапазоны А10:А13 и В9:Е9 ввести соответственно цифровые значения возможностей поставщиков и потребностей потребителей.

Примечание. Указанные диапазоны зависят от размерности задачи.

5. В диапазоны АЗ:А6 и В2:Е2 ввести соответственно формулы
суммирования диапазона решений по столбцам и по строкам, напри
мер, =СУММ (В3:Е3) или = СУММ (В3:В6).

Примечание. Диапазоны суммирования зависят от размерности задачи.

6.В ячейку В15 ввести формулу =СУММПРОИЗВ(В3:Е6; В10:Е13).

7.Выполнить команду Сервис|Поиск решения.

8. В диалоговом окне Поиск решения Установить целевую ячейку В15 Равной минимальному значению, Изменяя ячейки - ВЗ:Е6, Ограничения:

А10:А13 = А3:А6,

В9:Е9 = В2:Е2,

В3:Е6> 0.

9. Вдиалоговом окне Поиск решения нажать кнопку Параметры. Впоявившемся диалоговом окне Параметры поиска решения установить флажки на опциях Линейная модель и Неотрицательные значения. Щелкнуть ОК.

10.В диалоговом окне Поиск решения нажать кнопку Выполнить.

11.В диалоговом окне Результаты поиска решения выбрать все типы отчетов и просмотреть их на соответствующих р/л.