Искусство
Языки
Языкознание
Вычислительная техника
Информатика
Финансы
Экономика
Биология
Сельское хозяйство
Психология
Ветеринария
Медицина
Юриспруденция
Право
Физика
История
Экология
Промышленность
Энергетика
Этика
Связь
Автоматика
Математика
Электротехника
Философия
Религия
Логика
Химия
Социология
Политология
Геология
ЛАБ_4_EX. Методические указания Решение задач оптимизации
 Скачать 383 Kb.
|
|
ЛАБОРАТОРНАЯ РАБОТА ЭЛЕКТРОННАЯ ТАБЛИЦА EXCEL. ПОИСК РЕШЕНИЯ. ПОДБОР ПАРАМЕТРА. Цель работы : Изучить «Поиск решения» и «Подбор параметра» в электронной таблице Excel. Методические указания 1. Решение задач оптимизации Рассматривается технология разработки модели и решения задач с помощью программы ExcelПоиск решений. Необходимо: 1. Научиться составлять наилучший (оптимальный) план производства продукции с учетом ограниченного обеспечения материальными ресурсами. 2. Освоить методику и технологию оптимизации планов производства продукции в табличном процессоре Excel с помощью программы Поиск решения (Solver). Рассмотрим пример. (пример необходимо выполнить) Постановка задачи: Предприятие выпускает телевизоры, стерео и акустические системы, используя общий склад комплектующих. Каждому типу изделий соответствует своя норма прибыли. Запас комплектующих на складе ограничен. Задача сводится к определению плана производства количества каждого вида изделий для получения наибольшей прибыли, т.е. оптимальное соотношение объемов выпуска разных типов изделий в плане. Табличная модель. В таблице1. приведены исходные данные Таблица 1  Для решения задачи необходимо внести исходные данные и формулы для вычислений Запустите Excel, переименуйте один из рабочих листов и присвойте ему имя Оптимизация_1. Исходные данные рассматриваемой задачи разместите на этом листе. Сохраните рабочую книгу в рабочей папке. Имя файла выберите самостоятельно. Внесение исходных данных и формул. При составлении таблицы необходимо ввести исходные данные и формулы для вычислений. Представление формул и исходных данных дано в таблице 2. Примечание:
Таблица 2.  В колонке С (плановый расход комплектующих) введены формулы вычисления суммы произведений норм расхода ресурсов на плановое количество продукции. В строке 13 (Прибыль по видам изделий) прибыль на единицу продукции умножается на количество изделий по плану В строке 14 суммируется прибыль по всей продукции из строки 14. Решение задачи 1. Программа оптимизации Поиск решения (Solver) Для вызова программы оптимизатора выберите команду меню Сервис>Поиск решения. Если команда Поиск решения отсутствует в меню Сервис, то надо установить эту надстройку. 1.1. Установка программы Поиск решения В меню Сервис выберите команду Надстройки. В диалоговом окне Надстройки установите флажок Поиск решения. Если диалоговое окно Надстройки не содержит команды Поиск решения, нажмите кнопку Обзор и укажите диск и папку, в которой содержится файл надстройки Solver.xla (как правило, это папка Library\Solver folder) или запустите программу Setup, если найти файл не удается. Надстройка, указанная в диалоговом окне Надстройки, остается активной до тех пор, пока она не будет удалена. 1.2. Настройка математической модели Для обработки таблицы Excel оптимизатором необходимо вызвать его диалоговое окно Поиск решения (рис. 1) и настроить математическую модель. Диалоговое окно Поиск решения Окно Поиск решения вызывается командой меню Сервис Поиск решения. Поле Установить целевую ячейку служит для указания целевой ячейки, значение которой необходимо максимизировать, минимизировать или установить равным заданному числу. Эта ячейка должна содержать формулу. В нашем примере — это ячейка D14 (прибыль).  Рис 1 Кнопка Равной служит для выбора варианта с заданным значением целевой ячейки. Чтобы установить заданное число, введите его в поле. В нашем примере для максимизации прибыли мы нажимаем кнопку максимальному значению. Поле Изменяя ячейки служит для указания ячеек, значения которых изменяются в процессе поиска решения до тех пор, пока не будут выполнены наложенные ограничения и условие оптимизации значения ячейки, указанной в поле Установить целевую ячейку. В поле Изменяя ячейки вводятся имена или адреса изменяемых ячеек, разделяя их запятыми. В нашем примере введен диапазон ячеек D4:F4, содержащий искомые величины плана производства продукции. Изменяемые ячейки должны быть прямо или косвенно связаны с целевой ячейкой. Допускается установка до 200 изменяемых ячеек. Поле Предположить используется для автоматического поиска ячеек, влияющих на формулу, ссылка на которую дана в поле Установить целевую ячейку. Результат поиска отображается в поле Изменяя ячейки. Поля Ограничения служат для отображения списка граничных условий поставленной задачи. В нашем примере это величины диапазона расхода комплектующих С7:С11. Они не должны превышать запаса на складе В7 :В11 Команда Добавить служит для отображения диалогового окна Добавить ограничение. Команда Изменить служит для отображения диалоговое окна Изменение ограничения. Команда Удалить служит для снятия указанного курсором ограничения. Команда Выполнить служит для запуска поиска решения , поставленной задачи. Команда Закрыть служит для выхода из окна диалога без запуска поиска решения поставленной задачи. При этом сохраняются установки сделанные в окнах диалога, появлявшихся после нажатий на кнопки Параметры, Добавить, Изменить или Удалить. Кнопка Параметры служит для отображения диалогового окна Параметры поиска решения, в котором можно загрузить или сохранить оптимизируемую модель и указать предусмотренные варианты поиска решения. Кнопка Восстановить служит для очистки полей окна диалога и восстановления значений параметров поиска решения, используемых по умолчанию. Ввод и редактирование ограничений Диалоговые окна изменения и добавления ограничений одинаковы см. рис. 2. В поле Ссылка на ячейку вводится адрес или имя ячейки или диапазона, на значения которых накладываются ограничения. Ввод осуществляется набором на клавиатуре или выделением нужного диапазона в таблице с исходными данными. Выберите из раскрывающегося списка условный оператор, который необходимо разместить между ссылкой и ее ограничением. Это знаки операторов: не более, не менее, равно и т. д.  Рис. 2 В поле Ограничения введите число, формулу или имя ячейки или диапазона содержащих или вычисляющих ограничивающие значения. Чтобы приступить к набору нового условия, нажмите кнопку Добавить. Чтобы вернуться в диалоговое окно Поиск Решения, нажмите кнопку ОК. Условные операторы целого и двоичного типа можно применять только при наложении ограничений на изменяемые ячейки. Для запуска оптимизатора нажмите кнопку Выполнить в окне Поиск решения. По окончании счета появляется таблица и диалоговое окно Результаты поиска решения, рис. 3 После нажатия клавиши OK отображается таблица с исходными данными и результами решения  Рис. 3 Решить самостоятельно Переименуйте один из рабочих листов и присвойте ему имя Оптимизация_2. Исходные данные задачи разместите на этом листе. Задача. Завод производит электронные приборы трех видов (прибор А, прибор В и прибор С), используя при сборке микросхемы трех видов (тип 1, тип 2 и тип 3). Расход микросхем задается следующей таблицей:
Ежедневно на склад завода поступает 500 микросхем типа 1 и по 400 микросхем типов 2 и 3. Каково оптимальное соотношение дневного производства приборов различного типа, если производственные мощности завода позволяют использовать запас поступивших микросхем полностью? 2. Решение уравнений средствами программы Excel Задача. Найти решение уравнения x3 - 3x2 + х = -1. 1. Создайте новый рабочий лист (Вставка > Лист), дважды щелкните на его ярлычке и присвойте ему имя Уравнение. 2. Занесите в ячейку А1 значение 0. 3. Занесите в ячейку В1 левую часть уравнения, используя в качестве переменной x ссылку на ячейку А1. Соответствующая формула может, например, иметь вид =А1 ^3-3*A1 ^2+А1. 4. Дайте команду Сервис > Подбор параметра. 5. В поле Установить в ячейке укажите В1, в поле Значение задайте -1, в поле Изменяя значение ячейки укажите А1. 6. Щелкните на кнопке ОК и посмотрите на результат подбора, отображаемый в диалоговом окне Результат подбора параметра. Щелкните на кнопке ОК, чтобы сохранить полученные значения ячеек, участвовавших в операции. 7. Повторите расчет, задавая в ячейке А1 другие начальные значения, например 0,5 или 2. Совпали ли результаты вычислений? Чем можно объяснить различия? Выполнить задания. Решить уравнения используя команду подбор параметра 1)x3 + 8x- 15=0 4) 3x - 3 x-2 = 72 2) 9*5 x+1 - 5x = 5500 5) x3 + 8x- 9 =0 3) 3 3x+1 - 2*33x = 27 6) 2x2 - 8x+ 8 =0 Решить системы уравнений используя команду поиск решения.  3 x1 - 5x2 =13 3 x1 - 4x2 = -62 x1+ 7x2 =81 3 x1 + 4x2 =81 5  x1 + 8x2+ x3 = 2 2x1 - 3x2 + x3 = -73x1 - 2x2+6x3= -7 x1 + 4x2 + 2x3 = -1 2 x1 + x2 - x3= -5 x1 - 4x2 = -5 2   x - 4 y + 9 =28 5x1 + 8x2 + x3 = 27 x+3 y – 6 = -1 3x1 - 2x2 + 6x3 = -7 7 x+9 y – 9 = 5 2x1 + x2 - x3 = -5  2 x - 7 y + z = - 4 2 x - 4 y + 9 z = 283 x + y – z = 17 7 x + 3 y - 6z = - 1 x - y +3z = 3 7x + 9y - 9z = 5 Решение выполнить с использованием трех методов. Используя Поиск решения, матричное уравнение (AX = B X = A-1B) и применяя метод Крамера. Расчетные таблицы для разных методов разместите на различных листах рабочей книги. Варианты выполняемых заданий согласовать с преподавателем Решение с использованием матричного уравнения X = A-1B– матричная запись решения рассматриваемой системы А – это основная матрица системы A-1 – обратная матрица А. X и B – это вектор столбец независимых переменных и вектор столбец свободных членов Решение. Используем функции обработки массивов, которые находятся в категории математические. Выполним следующие этапы: 1. Исходные данные внести на рабочий лист. 2. Для расчета обратной матрицы выделим диапазон ячеек, совпадающий по размеру с основной матрицей. 3. Активизируем верхнюю левую ячейку диапазона, создаваемой обратной матрицы и вызываем функцию МОБР. 4. В диалоговом окне вводим диапазон, в котором размещена основная матрица системы. Для ввода выделяем мышью диапазон в таблице исходных данных. 5. CTRL + SHIFT + ENTER. 6. Для определения вектора столбца независимых переменных выделим диапазон. 7. Активизируем верхнюю ячейку диапазона, и вызываем функцию МУМНЖ. 8. В диалоговом окне указываем параметры перемножаемых массивов 9. CTRL + SHIFT + ENTER Метод Крамера Метод Крамера (хi =i / , где - определитель основной матрицы, I - определитель полученный из основного подстановкой вместо j-ro столбца столбец свободных членов. Для вычисления определителя используется функция МОПРЕД, аргументом которой является диапазон размещения соответствующего массива. Исходные данные для создания всех требуемых массивов и последующего вычисления определителей представлены в расчетной таблице, которая использовалась при решении другими методами. |