Решение задач по нахождению оптимального плана строительства с использованием электронных таблиц Excel, страница 2

где x₁, x₂, x₃, x₄, x₅ принимают целочисленные значения.

3.  Найти максимум функции

, при следующих ограничениях

,

где x₁, x₂, x₃, x₄, x₅ принимают только неотрицательные целочисленные значения.

Решение задачи средствами Microsoft Excel

1.  Запустим программу Microsoft Excel (Start = Programs = Microsoft Excel).

2.   Создадим новую книгу Строительство домов.xls в программе Microsoft Excel (кнопка Создать на стандартной панели инструментов).

3.  В рабочем листе Лист1 создадим таблицу, приведенную в условии задачи с дополнительными ячейками для проведения расчетов и вывода результатов. Заполнение таблицы осуществляется обычным способом. После введения необходимых данных проводится автоподбор ширины столбцов (Формат = Столбец = Автоподбор ширины) для более удобной и наглядной работы.

4.  В ячейках B6:F6 занесем нули – в дальнейшем значения этих ячеек будут подобраны автоматически.

5.  Столбец G содержит формулы для вычисления следующих параметров:

G2 – общее количество дней строительства фундаментов за год:

G3 – общее количество дней проведения остальных работ за год:

G4 – общая жилая площадь построенных домов за год:

G5 – произведение средней стоимости 1 кв.м на количество построенных домов:

G6 – общее количество построенных домов за год:

Тип дома	I	II	III	IV	V	Всего
Фундамент	20	30	35	30	40	=B2*$B$6+C2*$C$6+D2*$D$6+E2*$E$6+ F2*$F$6
Остальные работы	40	20	60	35	25	=B3*$B$6+C3*$C$6+D3*$D$6+E3*$E$6+ F3*$F$6
Жилая площадь	3000	2000	5000	4000	6000	=B4*$B$6+C4*$C$6+D4*$D$6+E4*$E$6+ F4*$F$6
Стоимость 1 кв.м	200	150	220	180	200	=B5*$B$6+C5*$C$6+D5*$D$6+E5*$E$6+ F5*$F$6
План строительства	0	0	0	0	0	=СУММ(B6:F6)

В ячейке G2 формула будет иметь вид =B2*$B$6+C2*$C$6+D2*$D$6+E2*$E$6+F2*$F$6, а остальные формулы можно получить методом автозаполнения.

В ячейке G6 формула занесена путем вычисления автосуммы ячеек из диапазона B6:F6 (выделить указанный диапазон и щелкнуть на кнопке Автосумма на стандартной панели инструментов) (см. рис.1)

6.  Для решения задачи используем одну из надстроек Excel – Поиск решения. Для этого откроем диалоговое окно Поиск решения (Сервис = Поиск решения…).

7.  В поле Установить целевую ячейку укажем ту из них, которая содержит оптимизирующее значение, то есть функцию f(x₁, x₂, x₃, x₄, x₅), для нашего случая, это ячейка G4. Установим переключатель Равной максимальному значению.

8.  В поле Изменяя ячейки зададим диапазон подбираемых параметров – B6:F6.

9.  Чтобы задать набор ограничений, щелкнем на кнопке Добавить.

10.  Для решения первого варианта задачи в диалоговом окне Добавление ограничения в поле Ссылка на ячейку укажем диапазон B6:F6. В качестве условия выберем пункт цел. Щелкнем на кнопке Добавить.

11.  Снова в поле Ссылка на ячейку укажем G2. В качестве условия зададим <=. В поле Ограничение зададим 300*10. Щелкнем кнопку Добавить.

12.  Снова в поле Ссылка на ячейку укажем G3. В качестве условия зададим <=. В поле Ограничение зададим 300*15. Щелкнем кнопку Добавить.

13.  Снова в поле Ссылка на ячейку укажем G6. В качестве условия зададим =. В поле Ограничение зададим 100. Щелкнем кнопку ОК. (см. рис.2)

14.  Чтобы изменить параметры поиска решения, щелкнем на кнопке Параметры.

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

16.  Щелкнем на кнопке Выполнить. По завершению оптимизации откроется диалоговое окно Результаты поиска решения.

17.  Установим переключатель Сохранить найденное решение. Для полного анализа результатов выберем тип отчета – Результаты, после чего щелкнем на кнопку ОК.

18.  Аналогично ищем решения для второго и третьего вариантов задачи.