ЗАДАНИЕ 9

ПОИСК ОПТИМАЛЬНЫХ РЕШЕНИЙ (ex_9_Name.xls)

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

ЗАДАЧА 1 ПЛАН ПРОИЗВОДСТВА.

Рассмотрим классическую оптимизационную задачу. Предприятие производит два вида продукции (например, хлеб черный и белый) по определенной Цене. На их производство требуется 4 вида ресурсов (например, мука, жиры, сахар, и дрожжи), которые есть в наличие на складе в определенном количестве (Запас). Также имеется информация. сколько нужно каждого ресурса на производство единицы продукции (Норма расхода), соответственно Товара А и Товара Б. Нужно найти такое сочетание количества выпускаемой (План) продукции, которое максимизирует выручку (Доход).

Взаимосвязь между ограничениями, планом и целевой функцией осуществлется в столбеце (Использовано), в котором записана формула СУММПРОИЗВ(Ресурс_M; План). Ресурс_N - норма затрат M-го ресурса на производство единицы продукции Товара А или Б, а План – количество продукции, которое мы ищем. В ячейку Доход вводим формулу СУММПРОИЗВ(Цена; План).

41

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

1.Создайте таблицу с формулами, которые устанавливают связи между ячейками.

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

3.Установите переключатель группы Равной в положение, соответствующие максимальному значению.

4.Укажите в поле Изменяя ячейки, в каких ячейках программа должна изменять значения.

5.Создайте ограничения в списке Ограничения. Укажите ячейки с заданными значениями (Запасы) условие и ячейки с расчетными значениями (Использовано).

6.Запустите процесс поиска решения. Когда появится диалоговое окно Результаты поиска решения, выберите переключатель

Сохранить найденное и нажмите на кнопку ОК.

42

ЗАДАЧА №2. СТИМУЛИРОВАНИЕ ПРОДАЖ.

Известно, что каждый покупатель, который входит в магазин, тратит в среднем $12. Чтобы не потерять интерес клиентов к магазину, вы посылаете покупателю одну открытку, по которой он может получить приз - от магнитика до футболки. Чем лучше приз, тем вероятнее, что покупатель придѐт ещѐ раз в магазин.

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

Наименование	Цена	Количество	Отклик
Футболка	3,00	150	92%
Купон на $2	2,00	250	76%
Ручка	0,75	3000	26%
Цепочка для часов	0,50	3300	18%
Магнитик	0,25	3300	10%
Всего		10000

Рассчитать чистый доход от продаж в результате откликов на призы.

Выручка от продажи = Количество призов * Стоимость средней покупки * Отклик

Затраты на призы = Цена приза * Количество призов * Отклик Доход = Выручка от продажи - Затраты на приз Доход от всего проекта = Сумме доходов по каждому призу.

Затраты на проект вычисляются как сумма затрат на каждый приз.

Используя надстройку Поиск решения, определить оптимальное количество различных призов, которое даст максимальный доход:

а) при общих затратах на проект не более $2500 и количестве открыток не более 10000 (количество - целое и положительное)

43

Ограничения на минимальное количество призов: Футболок не менее 150 Ручек не менее 2000 Купонов не менее 250

Цепочек и магнитиков не менее 50

Лист стимулирования продаж

Наименование	Цена	Кол	Отклик	Выручка	Затраты	Доход
				от продаж	на призы
Футболка	$3,00	150	92%
Купон на 2 $	$2,00	250	76%
Ручка	$0,75	3000	26%
Цепочка для часов	$0,50	3300	18%
Магнитик	$0,25	3300	10%
				Весь			Целевая
	Всего	10000		проект			функция
Стоимость средней покупки			$12

б) Как изменится доход от продаж, если увеличить затраты на проект до $5000. Ограничения на минимальное количество призов остаются прежними.

Задачи выполнить на разных листах, оформить решения, отформатировать данные.

44

ПРОГРАММИРОВАНИЕ

VBA-Visual Basic Аpplication

Язык VBA для приложений MS Office является мощным средством автоматизации офисной работы. Данный язык использует все преимущества объектно-ориентированного программирования.

Средства этого инструментария распространены во все приложения MS Office. Это дает возможность программировать прикладные комплексы, использующие стандартные готовые объекты: текстовый редактор, электронную таблицу, базу данных, слайды презентации. В практикуме рассматриваются принципы построения программ внутри MS Excel.

Многие офисные пакеты включают в свой состав элементы макропрограммирования, но все они используют специфический набор команд. Лексика VBA основывается на стандартном алгоритмическом языке Visual Basic. Новым в VBA является то, что все объекты офисного приложения, например Excel, доступны для программирования. Используя эту возможность, можно расширить или изменить действия стандартных команд Excel. Например, в меню Условное форматирование можно использовать только три условия, накладываемые на ячейку или диапазон таблицы. Средствами VBA можно расширить это ограничение. С помощью программирования могут быть построены совершенно новые функции, которых нет в стандартной поставке.

Освоение этой темы потребует от студента знания основ структурного программирования и записи стандартных алгоритмов на языке VB. Разбирая каждую программу, студент должен ее безошибочно записать в файл через редактор VBA, назначить событие или интерфейсный элемент для ее выполнения и проверить ее выполнение на тестовых примерах.

45

	Окно проекта со	Кнопка Старт
		Кнопка Стоп
	списком листов,
	модулей, форм

Лист отображения результатов выполнения программы

	Окно свойств Объекта	Окно модуля с тек-
		стом кода програм-
		мы на языке VB

46