Материал: Технологии обработки информации в среде табличного процессора Microsoft Excel. Болгов В.В

Затраты на рекламу для каждого квартала – по 10000 р.

Цена – 40 р.; себестоимость – 25 р.

2. Отформатировать таблицу: ячейкам, содержащим денежные величины, назначить денежный формат; ячейкам строки Коэффициент прибыльности назначить процентный формат; расчертить таблицу линиями.

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

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

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

6. Изменить ограничение, установив верхний предел расходов та рекламу за год 50000 р. Сохранить установки задачи в виде модели. После просмотра результата восстановить исходные значения. Объяснить причины не схождения результатов поиска в двух последних случаях.

7. Сохранить в качестве сценария первоначальные значения величин затрат на рекламу в каждом квартале. Установить сезонный фактор каждого квартал равным 1 и провести поиск решения. Почему в последнем случае решение удалось найти, а в двух предпоследних случаях решения не оказалось?

10. Восстановить первоначальные значения с помощью первого сценария.

Упражнение №8 Поиск решения. Транспортная задача

Цель работы:

1.Освоить процедуру разработки задачи поиска решения на примере транспортной задачи.

Задание на самоподготовку:

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

1. Создания таблицы;

2. Заполнения таблицы данными с использованием копирования данных;

3. Создание и копирование формул в таблицах с использованием относительных и абсолютных адресов;

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

5. Сохранение результатов поиска в качестве сценария.

Изучить материал по решению транспортной задачи.

Постановка и решение транспортной задачи

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

При решении задачи минимизации расходов на перевозку товаров между пунктами учитываются:

1. Наличие товаров на i-ом складе ;

2. Потребности в них j-того потребителя ;

3. Количество перевезенных со i-того склада j-ому потребителю товара обозначим , а количество товаров, перевезенных этому потребителю со всех складов, обозначим , количество товаров, перевезенных всем потребителям с I-того одного склада равно .

4. Стоимость перевозки единицы товара с i–того склада до j-того потребителя .

5. Стоимость перевозки от i–того склада до j-того потребителя товаров ;

6. Стоимость перевозки всех товаров всем потребителям .

Целевая функция задачи формулируется как минимизация расходов на перевозку всех товаров всем потребителям:

→min.

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

<=

>=

Эти условия должны быть внесены в таблицу для решения задачи (таблица 4).

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

Таблица является исходной таблицей оптимизации перевозок.

Наличие товаров на складах указанj в клетках D13:D17.

Потребности в товарах указаны в клетках C11:F11.

Таблица 4

В клетках C13:F17 указаны стоимости перевозки единицы товара с каждого склада каждому поставщику .

Стоимость перевозки всех товаров с отдельного склада отдельному потребителю указана в клетках C13:F17.

В клетках C11:F11 указаны количества товаров, требующиеся каждому поставщику (ограничения на количество доставленных ему товаров) .

Таблица 5

Полная стоимость перевозки товара отдельному поставщику со всех складов указана в клетках C25:F25.

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

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

Выполнение работы:

1. Сформировать таблицу для ввода условий решения транспортной задачи. определить диапазоны ячеек для ввода отдельных

2. Ввести условия решения задачи в таблицу и произвести поиск решения.

3. Изменить условия задачи (стоимость перевозок между отдельными пунктами) и повторить задачу поиска решения.

4. Изменить требуемое количество изделий, требующихся отдельным потребителям, и провести поиск решения.

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

6. Сделать выводы о применении задачи поиска решения.