🗊Презентация Оптимизационное моделирование в EXСEL. Решение задач с помощью надстроек EXCEL

Решение задач с помощью надстроек EXCEL: Поиск решения и Подбор параметра

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

Задача оптимизации – поиск оптимального (наилучшего) решения данной задачи при соблюдении некоторых условий. Задача оптимизации – поиск оптимального (наилучшего) решения данной задачи при соблюдении некоторых условий. В EXCEL подобные задачи решаются с использованием надстроек.

Выбрать Сервис – Надстройки Выбрать Сервис – Надстройки На панели Надстройки в списке Доступные надстройки выбрать нужные путем установки флажков Нажать ОК

Что такое задача оптимизации? Что такое задача оптимизации? Приведите примеры оптимизационных задач? Необходимы ли специальные способы для решения таких задач? Как установить надстройки в EXCEL?

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

Разобрать условие задачи; Разобрать условие задачи; Построить математическую модель; Выбрать поисковые переменные; Задать ограничения; Выбрать критерий оптимизации; Решить задачу на компьютере; Проанализировать полученный результат.

Число 10 представьте в виде суммы двух неотрицательных слагаемых так, чтобы сумма кубов этих чисел была наибольшей. Число 10 представьте в виде суммы двух неотрицательных слагаемых так, чтобы сумма кубов этих чисел была наибольшей.

Число а (а≥0), Число а (а≥0), Число b: 10-a ≥0, Выражение S =а³+ b³ стремится к максимуму.

a – первое число; a – первое число;

а≥0, а≥0, 10-a ≥0.

Сумма кубов чисел a и b должна быть максимальной: Сумма кубов чисел a и b должна быть максимальной: S =а*а*а+ b* b* b = max

Заполним таблицу, указав произвольное значение для поисковой переменной и вычислим значение второй переменной: Заполним таблицу, указав произвольное значение для поисковой переменной и вычислим значение второй переменной:

Найдем оптимальное решение, для этого необходимо: Найдем оптимальное решение, для этого необходимо: Выделить целевую ячейку С6; Выбрать Сервис, Поиск решения;

Установить целевую ячейку, равную максимальному значению; Установить целевую ячейку, равную максимальному значению; Указать диапазон изменяемых ячеек;

Выбрать кнопку Добавить для записи ограничений; Выбрать кнопку Добавить для записи ограничений; После записи ограничения нажать Добавить; Для последнего ограничения –ОК; Нажать кнопку Выполнить;

Выбрать Тип отчета, Результаты, ОК; Выбрать Тип отчета, Результаты, ОК;

На новом листе Отчет по результатам 1 можно увидеть: На новом листе Отчет по результатам 1 можно увидеть:

В электронных таблицах найдено оптимальное решение: В электронных таблицах найдено оптимальное решение: Искомые числа а =10, b=0. Решение задачи в EXEL Математическое решение задачи

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

Суть задачи в нахождении количества банок краски, для этого необходимо знать: Суть задачи в нахождении количества банок краски, для этого необходимо знать: площадь всего зала; какую площадь можно покрыть содержимым одной банки.

Измерим длину зала – а м. (пусть 18,1 ≤ а≤18,3) и ширину b м. (пусть 7,6 ≤ b≤7,7), Измерим длину зала – а м. (пусть 18,1 ≤ а≤18,3) и ширину b м. (пусть 7,6 ≤ b≤7,7), Найдем площадь зала по формуле: S=ab, Выясним какую площадь S1, можно покрыть содержимым одной банки (пусть меньше 10м квадратных), Вычислим необходимое количество банок по формуле: n=S/S1.

а – длина зала, а – длина зала, b – ширина зала, S1 – площадь, которую можно покрыть одной банкой краски.

а ≥ 18,1; а ≥ 18,1; а ≤ 18,3; b ≥ 7,6; b ≤ 7,7; S1 ≤ 10.

Количество банок должно быть минимальным: Количество банок должно быть минимальным: n=S/S1=min

Заполним таблицу, указав произвольные значения для поисковых переменных: Заполним таблицу, указав произвольные значения для поисковых переменных:

Найдем оптимальное решение, для этого: Найдем оптимальное решение, для этого: Выделить целевую ячейку С7; Выбрать Сервис, Поиск решения;

Установить целевую ячейку, равную минимальному значению; Установить целевую ячейку, равную минимальному значению; Указать диапазон изменяемых ячеек;

Выбрать кнопку Добавить для записи ограничений; Выбрать кнопку Добавить для записи ограничений; После записи ограничения нажать Добавить; Для последнего ограничения –ОК; Нажать кнопку Выполнить;

Выбрать Тип отчета, Результаты, ОК; Выбрать Тип отчета, Результаты, ОК;

На новом листе Отчет по результатам 1 можно увидеть: На новом листе Отчет по результатам 1 можно увидеть:

В электронных таблицах найдено оптимальное решение: В электронных таблицах найдено оптимальное решение: для покраски пола в актовом зале необходимо не более 14 банок. Решение задачи в EXCEL

Какие задачи можно решать используя надстройку Поиск решения? Какие задачи можно решать используя надстройку Поиск решения? Перечислите этапы решения задач при работе с надстройкой Поиск решения? Можно ли в целевой ячейке записать какое-либо значение, а не формулу? Какие возможности дает надстройка Поиск решения? Где могут пригодиться функции надстройки Поиск решения?

Подбор параметра является частью блока задач, который иногда называют инструментами анализа «что-если» (процесс изменений значений ячеек и анализ влияний этих изменений на результат вычислений формул) Подбор параметра является частью блока задач, который иногда называют инструментами анализа «что-если» (процесс изменений значений ячеек и анализ влияний этих изменений на результат вычислений формул)

Решите уравнение Решите уравнение х³-sinx-0,5=0.

Для решения подобных уравнений действуют по следующему алгоритму: Для решения подобных уравнений действуют по следующему алгоритму: составляют таблицу значений функции у= х³-sinx-0,5; Строят график, который позволит определить значение аргумента х при у=0.

Построим таблицу значений функции: у= х³-sinx-0,5 на интервале от -1,5 до 1,5 с шагом 0,5; Построим таблицу значений функции: у= х³-sinx-0,5 на интервале от -1,5 до 1,5 с шагом 0,5;

Построим график по значениям таблицы Построим график по значениям таблицы

По графику приближенно можно определить, что корень уравнения х≈1 По графику приближенно можно определить, что корень уравнения х≈1

Сервис – Подбор параметра Сервис – Подбор параметра

2. Установим значение функции у=0 изменяя значение аргумента 2. Установим значение функции у=0 изменяя значение аргумента

Нажмем ОК и на панели Результат подбора параметра будет выведена информация о величине подбираемого и подобранного значений, а в таблице изменятся значения аргумента и функции Нажмем ОК и на панели Результат подбора параметра будет выведена информация о величине подбираемого и подобранного значений, а в таблице изменятся значения аргумента и функции

В ячейке G2 появится искомое значение аргумента, с заданной точностью В ячейке G2 появится искомое значение аргумента, с заданной точностью х=1,11854 Решение в EXCEL

Заведующий больницей должен составить штатное расписание: сколько сотрудников, на какие должности и с каким окладом принять на работу. Общий месячный фонд зарплаты составляет 10000 у.е. Известно, что для нормальной работы больницы нужно 5 — 7 санитарок ,8—10 медсестер, 10—12 врачей, 1 зав. Аптекой, 3 зав. Отделениями, 1 главный врач, 1 завхоз, 1 зав. Больницей. Совет решил, беря за основу оклад санитарки, что медсестра должна получать в 1,5 раза больше санитарки врач в 3 раза больше санитарки; зав. отделением — на 30 у.е. больше, чем врач; зав. аптекой — в 2 раза больше санитарки; завхоз — на 40 у.е. больше медсестры; главный врач — в 4 раза больше санитарки; зав. больницей — на 20 у.е. больше главного врача. Составьте штатное расписание больницы. Заведующий больницей должен составить штатное расписание: сколько сотрудников, на какие должности и с каким окладом принять на работу. Общий месячный фонд зарплаты составляет 10000 у.е. Известно, что для нормальной работы больницы нужно 5 — 7 санитарок ,8—10 медсестер, 10—12 врачей, 1 зав. Аптекой, 3 зав. Отделениями, 1 главный врач, 1 завхоз, 1 зав. Больницей. Совет решил, беря за основу оклад санитарки, что медсестра должна получать в 1,5 раза больше санитарки врач в 3 раза больше санитарки; зав. отделением — на 30 у.е. больше, чем врач; зав. аптекой — в 2 раза больше санитарки; завхоз — на 40 у.е. больше медсестры; главный врач — в 4 раза больше санитарки; зав. больницей — на 20 у.е. больше главного врача. Составьте штатное расписание больницы.

Так как за основу взять оклад санитарки, тогда рассчитаем зарплаты сотрудников по следующей формуле: АС + В, где С — оклад санитарки, А и В — коэффициенты, которые определены решением совета. Так как за основу взять оклад санитарки, тогда рассчитаем зарплаты сотрудников по следующей формуле: АС + В, где С — оклад санитарки, А и В — коэффициенты, которые определены решением совета. Для медсестры А=1,5, В=0, и т.д. Необходимо уложиться в фонд зарплаты, изменяя оклад санитарки.

Заполним следующую таблицу, установив значение оклада санитарки 150 у.ед.: Заполним следующую таблицу, установив значение оклада санитарки 150 у.ед.:

Изменяя количество сотрудников, можно составить несколько вариантов штатного расписания Изменяя количество сотрудников, можно составить несколько вариантов штатного расписания Решение в EXCEL

Какие задачи можно решать используя надстройку Подбор параметра? Какие задачи можно решать используя надстройку Подбор параметра? Какие возможности дает надстройка Подбор параметра? Где могут пригодиться функции надстройки Подбор параметра? Пригодится ли вам и где материал данной презентации?