MS Excel (110

6.Скопируйте содержимое этой ячейки на оставшиеся ячейки.

7.Выделите табличные данные и, используя Мастер диаграмм, постройте точечный график функции.

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

чения:-3,8;-0,5 и 3,8.

9.В ячейку А23 введите первое приближенное значение -3,8, в

ячейку В23 введите формулу =В23^3-12*В23-6.

10.Выберите команду Сервис Подбор параметра. Откроется диалоговое окно Подбор параметра.

11.В поле ввода Установить в ячейке введите В23 или щелкните мышкой по ячейке В23.

12.В поле ввода Значение введите число 0.

13.В поле ввода Изменяя ячейки введите А23 или щелкните на ячейке А23.

14.Щелкните на кнопке ОК.

15.В открывшемся окне Результат подбора параметра щелкните на кнопке ОК.

Ответ: первый корень уравнения равен -3,18.

Аналогично определяются второй и третий корни уравнения.

31

Лабораторная работа 8 Решение задач линейного программирования средствами

Excel

Цель работы: решение задач линейного программирования сред-

ствами MS Excel 2003.

Задание 1 (Задача о смесях).

Стандартом предусмотрено, что октановое число автомобильного бензина А-76 должно быть не ниже 76, а содержание серы в нем – не более 0,3%. Для изготовления такого бензина на заводе используется смесь из четырех компонентов.

Данные о ресурсах смешиваемых компонентов, их себестоимости и их октановом числе, а также о содержании серы приведены в таблице.

Приказом директора завода изготовителя установлен следующий расход каждого компонента: 1 – 550 т, 2 – 10 т, 3 – 150 т, 4 – 290 т. Требуется определить, сколько на самом деле тонн каждого компонента следует использовать для получения 1000 т автомобильного бензина А-76, чтобы его себестоимость была минимальной. Какова упущенная выгода предприятия при производстве каждых 1000 т бензина при таком решении дирекции?

Указания к решению.

Пусть xi i 1, 2,3, 4 – количество в смеси компонента с но-

мером i. С учетом этих обозначений задача минимума себестоимости принимает вид

32

min f x 40 x1 45 x2 60 x3 90 x4 , x1 x2 x3 x4 1000,

68 x1 72 x2 80 x3 90 x4 76 1000,

0, 35 x1 0, 35 x2 0, 3 x3 0, 2 x4 0, 3 1000, x1 700,

x2 600 x3 500 x4 300

x j 0 , j 1, 2, 3, 4 .

Первое функциональное ограничение отражает необходимость получения заданного количества смеси (1000 т), второе и третье – ограничения по октановому числу и содержанию серы в смеси, остальные – ограничения на имеющиеся объемы соответствующих ресурсов (компонентов). Прямые ограничения очевидны, но принципиально важны для выбора метода решения. Для решения задачи средствами Excel необходимо составить таблицу.

Образец таблицы

Решение задачи о смесях средствами Excel

Для решения задачи средствами Excel нужно воспользоваться программой-надстройкой ПОИСК РЕШЕНИЯ, расположенной в пункте меню СЕРВИС.

33

В открывшемся диалоговом окне следует установить:

адрес целевой ячейки,

диапазон адресов изменяемых ячеек,

систему ограничений.

Добавления, изменения и удаления ограничений производятся с помощью кнопок ДОБАВИТЬ, ИЗМЕНИТЬ, УДАЛИТЬ. Кнопка ПАРАМЕТРЫ открывает окно, в котором следует установить флажок НЕОТРИЦАТЕЛЬНЫЕ РЕШЕНИЯ. Для нахождения оптимального решения следует нажать кнопку ВЫПОЛНИТЬ.

Диалоговое окно РЕЗУЛЬТАТЫ ПОИСКА РЕШЕНИЯ позволяет:

сохранить на текущем рабочем листе найденное оптимальное решение;

восстановить первоначальные значения;

сохранить сценарий;

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

Если щелкнуть по кнопке ОК, то на месте исходной таблицы получим таблицу с найденными оптимальными значениями.

Таким образом, упущенная выгода предприятия при производстве каждых 1000 т бензина при таком решении дирекции составляет 407 у.е.

Задание 2. Решение задач перевозки грузов средствами Excel.

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

34

1.Для решения задачи создайте таблицу в программе MS Excel по образцу.

2.Ячейки C3:G5 – количество единиц товара перевозимых от завода на склады. Эти ячейки заполняются самостоятельно единицами.

3.С7 сумма ячеек С3, С4, С5. Аналогично считаются ячейки D7, E7, F7, G7.

4.B3 – сумма ячеек C3, D3, E3, F3, G3. Аналогично считаются ячейки В4 и В5.

5.Ячейки С9:G9, B11:B13, C11:G13 заполняются данными из условия задачи.

6.В ячейку С15 записывается формула : =С3*С11+С4*С12+С5*С13. Ячейки D15, E15, F15, G15 считают-

ся аналогично.

7.Ячейка В15 – сумма ячеек С15, D15, E15, F15, G15.

8.Выберите команду Сервис→Поиск решения.

9.В диалоговом окне Поиск решения щелкните по кнопке Восстановить.

35

10.В поле Установить целевую ячейку введите В15 или щелкните мышкой в этом поле и затем – на ячейке В15.

11.Щелкните на переключателе минимальному значению.

12.Щелкните в поле Изменяя ячейки, затем выделите диапазон С3:G5.

13.Щелкните по кнопке Добавить.

14.В диалоговом окне Добавление ограничения щелкните в поле Ссылка на ячейку и затем выделите диапазон В3:В5.

15.В соседнем раскрывающемся списке выберите элемент <=.

16.Щелкните в поле Ограничение и выделите диапазон В11:В13.

17.Щелкните на кнопке Добавить.

18.Щелкните в поле Ссылка на ячейку и затем выделите диапазон С7:G7.

19.В соседнем раскрывающемся списке выберите элемент =.

20.Щелкните в поле Ограничение и выделите диапазон С9:G9.

21.Щелкните на кнопке Добавить.

22.Щелкните в поле Ссылка на ячейку и затем выделите диапазон С3:G5.

23.В соседнем раскрывающемся списке выберите элемент >=.

24.В поле Ограничение введите число 0.

25.Щелкните на кнопке Добавить.

26.Щелкните в поле Ссылка на ячейку и затем выделите диапазон С3:G5.

27.В соседнем раскрывающемся списке выберите элемент целое.

28.Щелкните на кнопке ОК. Вы вернетесь в диалоговое окно Поиск решения.

29.Щелкните на кнопке Параметры.

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

31.Щелкните на кнопке ОК. Вы вернетесь в диалоговое окно Поиск решения.

32.Щелкните на кнопке Выполнить.

33.Сделайте вывод по полученным результатам.

36

Самостоятельная работа

Задание . Заводы фирмы расположены в городах Лидсе и Кардиффе; они доставляют товары на склады городов Манчестер, Бирмингем и Лондон. Расстояния между этими городами приведены в таблице.

Завод в г. Лидсе выпускает в год 800 т товаров, а в г. Кардиффе – 500 т. Манчестерский склад вмещает 400 т, бирмингемский – 600 т, а лондонский – 300 т. Как следует транспонировать товары для минимизации цен на перевозки.

Стоимость перевозки составляет 2 дол. за километр.

Задание 2. Фирма занимается составлением диеты, содержащей по крайней мере 20 единиц белков, 30 единиц углеводов, 10 единиц жиров и 40 единиц витаминов. Как дешевле всего достичь этого при указанных ценах на 1 кг (или 1 л) пяти имеющихся продуктов?

37

Рекомендуемая литература

1.Безручко, В. Т. Информатика : учебное пособие. – М. : ИД

"ФОРУМ" : ИНФРА-М, 2006. – 432 с.

2.Безручко, В.Т. Компьютерный практикум по курсу "Информатика" : учебное пособие. – 3-е изд., перераб. и доп. – М. : ИД "ФОРУМ" : ИНФРА-М, 2010. – 368 с.

3.Макарова, Н. В. Информатика : учебник – 5-е изд., перераб. – М. : Финансы и статистика, 2009. – 768 с.

4.Симонович, С.В. Информатика. Базовый курс : учебник. – 2-е изд. – СПб. : Питер, 2008. – 640 с.

5.Семакин, И. Г. Информационные системы и модели: учебн.пособие / И. Г. Семакин, Е. К. Хеннер. – 2-е изд. – М. : БИНОМ ; Лаборатория знаний, 2007. – 303 с.

6.Соболь, Б. В. Информатика : учебник / Б. В. Соболь [и др.]. – Ростов-на-Дону : Феникс, 2010. – 448 с.

7.Степанов, А. Н. Информатика : учебник. – 5-е изд. – СПб. :

Питер, 2007. – 765 с.

38

39