2.5 Задание 5. Решение задачи математического программирования средствами ms Excel

2.5.1 Постановка задачи

Используя надстройку Microsoft Excel Поиск решения, определить размеры бака (R,h), имеющего форму цилиндра с крышкой, чтобы объем был максимальный, а длина сварного шва при его изготовлении не превышала 2000 см. Размеры должны быть целыми числами.

2.5.2 Разработка математической модели задачи

В общем виде постановка данной задачи выглядит следующим образом:

ЦФ.: F(X_i), i = 1, n → max

ОГР: g(X_i), i = 1, n (<=) b

ГРУ: X_i, i = 1, n > 0, X_i – цел

В соответствии с поставленной задачей необходимо определить такие размеры бака (высоту hи радиусR), имеющего форму цилиндра с крышкой, при которых объем бака будет максимальным.

Целевая функция (объем бака) имеет вид: F(R,h) =V=R²h→max

По условию задачи длина сварного шва при изготовлении бака не превышает 2000 см. С учетом этого запишем ограничение: L= 2 * 2R+h <= 2 000 см

Граничные условия задают пределы изменения искомых переменных в оптимальных решениях. По условию задачи: (R,h) >0, (R,h) – цел.

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

2.5.3 Разработка макета электронной таблицы

Для решения задачи на основе ее данных необходимо построить электронную таблицу. Таблица включает следующие данные:

• искомые переменные – ячейки E2:E3

• ячейку с ограничением – ячейки E5:G5

• ячейку с целевой функцией (E4)

Макет электронной таблицы представлен на рисунке 21.

Рисунок 21 – Макет таблицы

2.5.4 Вычисления в электронной таблице

Для проведения расчетов выделяем ячейку E4, содержащую целевую функцию, и вызываем Данные/Анализ/Поиск решения. В диалоговое окно, представленное на рисунке 22, вводим данные с учетом максимизации целевой функции.

Рисунок 22 – Ввод данных в диалоговое окно Поиск решений

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

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

Рисунок 23 – Результаты поиска решений

Численные результаты найденного решения автоматически вносятся в таблицу, указанную на рисунке 24.

Рисунок 24 – Таблица с результатами работы надстройки Поиск решения

2.5.5 Анализ результатов

В списке Тип отчета (см. рис. 23) можно задать вид и объем выводимой в отчет информации по процессу нахождения решения.

Отчет по Результатам представлен на рисунке 25.

Рисунок 25 – Анализ результатов

Заключение

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

Современные табличные процессоры, в частности Microsoft Excel, представляют собой чрезвычайно мощное средство по решению широкого диапазона задач: от проведения простейших расчетов до создания средств автоматизации вычислений [6].

В результате выполнения работы мы научились:

• работать с книгами и листами в целом;

• создавать таблицы и вводить в них исходные данные;

• использовать для вычислений в таблице формулы и функции. Применение функций значительно расширяет возможности Excel, делает эту программу действительно универсальной. Нами получены знания и умения в использовании таких функций, как ОСТАТ, ЕСЛИ, АСЧ, КОРЕНЬ, СТЕПЕНЬ и др.

• представлять данные в виде разнотипных диаграмм;

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

• работать со многими надстройками, командами MicrosoftExcel(Поиск решения, Подбор параметра и др.).

Возможность использовать макросы в Excel делает продукт корпорации Microsoft удобным и гибким решением для выполнения различных задач.

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

Изучение электронных таблиц является весьма актуальным, т. к. Microsoft Excel на сегодняшний день является одним из наиболее популярных приложений в мире [1].

Microsoft Office Excel широко применяется в различных сферах, позволяет осуществлять расчеты, обладает графическими инструментами, позволяющими формировать отчеты в виде графиков. Таким образом, Microsoft Office Excel используют практически все, кто сталкивается с делопроизводством и расчетами, с экономическими и математическими вычислениями. Именно поэтому знакомство с Excel, как и с другими Windows – приложениями, входящими в состав пакета Microsoft Оffice, является важной частью курса Информатики.