2.4 Задание 4. Анализ данных средствами ms Excel
2.4.1 Постановка задачи
Используя команду Подбор параметра из меню Сервис, найти значение Х, при котором функция возвратит заданное значение.
Результат отобразить в виде таблицы:
Таблица 3
Переменные |
Функция |
Результат |
|||
x |
y |
z |
|||
|
4,87 |
3,23 |
|
7,03 |
|
2.4.2 Разработка макета электронной таблицы
Для решения задачи в MS Excel необходимо построить макет электронной таблицы для реализации разработанного алгоритма.
На листе размещаются следующие данные:
постановка задачи
исходные данные к задаче – ячейки А15:C15
описательная часть таблицы – ячейки А20:E20; A21:C21
формула для вычисления значения функции – ячейка D22
Готовый макет электронной таблицы изображен на рисунке 14.
Рисунок 14 – Макет таблицы
2.4.3 Вычисления в электронной таблице
Подбор параметра является частью блока задач, который иногда называют инструментами анализа "что-если". Когда желаемый результат одиночной формулы известен, но неизвестны значения, которые требуется ввести для получения этого результата, можно воспользоваться средством «Подбор параметра» выбрав команду Подбор параметра в меню Сервис. При подборе параметра Microsoft Excel изменяет значение в одной конкретной ячейке до тех пор, пока формула, зависимая от этой ячейки, не возвращает нужный результат.
Используя команду Подбор параметра из меню Сервис, в ячейке A22 находим значение Х, при котором функция возвратит заданное значение.
Порядок заполнения диалогового окна Подбор параметра указан на рисунке 15.
Рисунок 15 – Порядок заполнения диалогового окна Подбор параметра
Результаты вычислений показаны на рисунке 16.
Рисунок 16 – Результаты вычислений в таблице
2.5 Задание 5. Решение задачи математического программирования средствами ms Excel
2.5.1 Постановка задачи
Используя надстройку Microsoft Excel Поиск решения, определить размеры бака (a, b, h), имеющего форму прямоугольного параллелепипеда с крышкой объемом 10000 см3, чтобы стоимость материала, пошедшего на его изготовление, была минимальной. Стоимость 1 м2 материала равна 25 у.е.
2.5.2 Разработка математической модели задачи
В общем виде постановка данной задачи выглядит следующим образом:
Ц
Ф.:
F(Xi),
i = 1, n → min
ОГР: g(Xi), i = 1, n (=) b
ГРУ: Xi, i = 1, n > 0
В соответствии с поставленной задачей необходимо определить такие размеры бака (высоту h, длину а и ширину b), при которых стоимость материала, необходимого для его изготовления, будет минимальной.
Целевая функция (стоимость материала, пошедшего на изготовление бака) имеет вид: F(a, b, h)= 2*(a*h+b*h+a*b)*0,0025 → min
По условию задачи цилиндр с крышкой имеет объем 10000 см3. С учетом этого запишем ограничение: V= abh = 10 000 см куб
Граничные условия задают пределы изменения искомых переменных в оптимальных решениях. По условию задачи: a, b, h >0.
В соответствии с вышесказанным математическая модель задачи примет вид:
2.5.3 Разработка макета электронной таблицы
Для решения задачи на основе ее данных необходимо построить электронную таблицу. Таблица включает следующие данные:
искомые переменные – ячейки E2:E4
ячейку с ограничением – ячейки E6:G6
ячейку с целевой функцией (E5)
Макет электронной таблицы представлен на рисунке 17.
Рисунок 17 – Макет таблицы