history 14 января 2021 г.
- Группы статей
- Надстройка «Поиск решения»
Пусть дана функция с несколькими переменными F(x1, x2, …)=a1*x1+a2*x2+… Также даны граничные условия в виде b1*x1+b2*x2+…<=c (несколько условий). Нужно найти экстремум функции F (минимум или максимум). Это классическая задача для Поиска решения MS EXCEL, кроме того это линейная модель. Сделаем удобную форму для таких задач и покажем как настроить Поиск решения.
Задача
Пусть дана явная функция с 4 переменными:
Также даны несколько (семь) граничных условий (англ: restrictions, constraints):
Требуется найти максимум функции F.
Создание модели
Решим задачу с помощью инструмента MS EXCEL
Поиск решения
, хотя можно ее решить и другим способом, например аналитически.
На рисунке ниже приведена модель, созданная для решения задачи (см.
файл примера
).
Переменные (выделено зеленым)
. В качестве переменных модели, очевидно, выступают x1, x2, x3, x4. Эта задача хороша тем, что переменные задаются однозначно, не требуется осмысливать житейскую задачу, например как с
оптимизацией затрат
. Хотя математически — это эквивалентные задачи, только количество переменных разное.
После запуска Поиск решения будет методично (последовательно) по своему алгоритму подставлять в зеленые ячейки числовые значения и вычислять функцию F (красная ячейка).
Ограничения (выделено серым)
. Ограничения модели — это ограничения на область изменения переменных. Они могут задаваться как простыми выражениями для одной переменной, например х1>=0, так и для некой комбинации переменных 5*x1+4*x2-x3-2*x4<=-3. В первом случае х1>=0 ограничения можно ввести прямо в окне Поиска решения (будет показано ниже), для более сложных зависимостей удобно подготовить вспомогательную таблицу (С26:Е29).
Составить модель, особенно первую, непросто. Может помочь такой подход: считать, что переменные (зеленые ячейки) уже содержат некие значения, пусть даже не оптимальные. Так легче составлять огграничения. В нашем случае ограниечение 5*x1+4*x2-x3-2*x4 можно записать с помощью формулы =
СУММПРОИЗВ($D$19:$D$22;C26:C29)
. В диапазоне D19:D22 содержатся коэффициенты 5; 4; -1; -2. Кроме того, если значения переменных заданы, то и значение целевой функции также автоматически рассчитано (тоже не оптимальное пока, до запуска Поиска решения).
Целевая функция (выделено красным)
.
Целевая функция — это то, что требуется оптимизировать, т.е. F. Формула для ее вычисления задана в явном виде — не нужно догадываться из условий обычной задачи как ее подсчитать. Это не всегда очевидно (см., например, статью про
пропускную способность трубопровода
).
Ниже приведено окно Поиска решения с заполненными полями: целевая функция, переменные и ограничения.
После запуска Поиска решения ответ будет вычислен за доли секунды: F=3.
Если функция F(x) непрерывна на отрезке [a, b] и имеет внутри этого отрезка локаль-ный экстремум, то его можно найти, используя надстройку Excel Поиск решения. Рассмотрим последовательность нахождения экстремума функции на примере.
Пример 12. Задана неразрывная функция Y= X 2 +X +2. Требуется найти ее экстремум (минимальное значение) на отрезке [-2, 2].
Решение:
1. В ячейку А3 рабочего листа введите любое число, принадлежащее заданному отрезку, в этой ячейке будет находиться значение Х.
2. В ячейку В3 введите формулу, определяющую заданную функциональную зависимость (рис. 18). Вместо переменной Х в этой формуле должна быть ссылка на ячейку А3: = A2^2 + A2 +2.
3. Выполните команду меню Сервис — Поиск решения.
4. В открывшемся окне диалога Поиск решения в поле Установить целевую ячейку укажите адрес ячейки, содержащей формулу (В3), установите пере-ключатель Минимальному значению, в поле Изменяя значение ячейки укажите адрес ячейки, в которой содержится переменная х.
5. Добавьте два ограничения в соответствующее поле: A3>= -2 и A3<= 2.
6. Щелкните на кнопке Параметры и в от крывшемся диалоговом окне Пара-метры поиска решения установите относительную погрешность вычислений и предельное число итераций.
7. Щелкните на кнопке Выполнить.
В ячейке А3 будет помещено значение аргумента Х функции, при котором она принимает минимальное значение, а в ячейке В3 – минимальное значение функции. В результате выполнения вычислений в ячейке А3 будет получено значение независимой переменной, при котором функция принимает наименьшее значение, а в ячейке В3 – минимальное значение функции, равное 1,75. Постройте график заданной функции и убедитесь, что решение найдено верно.
Поиск решения MS EXCEL. Экстремум функции с несколькими переменными. Граничные условия заданы уравнениями
history 14 января 2021 г.
- Группы статей
- Надстройка «Поиск решения»
Пусть дана функция с несколькими переменными F(x1, x2, . )=a1*x1+a2*x2+. Также даны граничные условия в виде b1*x1+b2*x2+. файл примера ).
Переменные (выделено зеленым) . В качестве переменных модели, очевидно, выступают x1, x2, x3, x4. Эта задача хороша тем, что переменные задаются однозначно, не требуется осмысливать житейскую задачу, например как с оптимизацией затрат . Хотя математически — это эквивалентные задачи, только количество переменных разное.
После запуска Поиск решения будет методично (последовательно) по своему алгоритму подставлять в зеленые ячейки числовые значения и вычислять функцию F (красная ячейка).
Ограничения (выделено серым) . Ограничения модели — это ограничения на область изменения переменных. Они могут задаваться как простыми выражениями для одной переменной, например х1>=0, так и для некой комбинации переменных 5*x1+4*x2-x3-2*x4 =0 ограничения можно ввести прямо в окне Поиска решения (будет показано ниже), для более сложных зависимостей удобно подготовить вспомогательную таблицу (С26:Е29).
Составить модель, особенно первую, непросто. Может помочь такой подход: считать, что переменные (зеленые ячейки) уже содержат некие значения, пусть даже не оптимальные. Так легче составлять огграничения. В нашем случае ограниечение 5*x1+4*x2-x3-2*x4 можно записать с помощью формулы = СУММПРОИЗВ($D$19:$D$22;C26:C29) . В диапазоне D19:D22 содержатся коэффициенты 5; 4; -1; -2. Кроме того, если значения переменных заданы, то и значение целевой функции также автоматически рассчитано (тоже не оптимальное пока, до запуска Поиска решения).
Целевая функция (выделено красным) . Целевая функция — это то, что требуется оптимизировать, т.е. F. Формула для ее вычисления задана в явном виде — не нужно догадываться из условий обычной задачи как ее подсчитать. Это не всегда очевидно (см., например, статью про пропускную способность трубопровода ).
Ниже приведено окно Поиска решения с заполненными полями: целевая функция, переменные и ограничения.
После запуска Поиска решения ответ будет вычислен за доли секунды: F=3.
Рассмотрим пример решения задачи линейной оптимизации в Excel
Дана оптимизационная задача в виде таблицы
| Ресурсы | Нормы затрат на изготовление 1 ед. кровати | Нормы затрат на изготовление 1 ед. шкафа | Общее количество ресурсов |
| Сосна | 0,8 | 1,4 | 200 |
| Дуб | 1,2 | 0,6 | 150 |
| Трудоемкость (человеко-часов) | 4 | 5 | 800 |
| Прибыль от продажи одной единицы | 9 | 11 |
По условию задачи составим целевая функция, которая будет иметь вид
Z=9x1+11x2
Ограничения
0,8x1+1,4x2≤200
1,2x1+0,6x2≤150
4x1+5x2≤800
x1,x2≥0
В Excel создаём таблицу с формулами, пример показан ниже
Формулы можно скопировать из этой таблицы
| Переменные | |
| x1 | x2 |
| 0 | 0 |
| Функция целевая | =9*A4+11*B4 |
| =0.8*A4+1.4*B4 | 200 |
| =1.2*A4+0.6*B4 | 150 |
| =4*A4+5*B4 | 800 |
Затем переходим на вкладку Данные -> Поиск решения
Выбираем ячейку, в которой надо оптимизировать целевую функцию, в нашем случае B5. Ставим галочку на максимум, затем выбираем ячейки с изменяемыми переменными это x1 и x2 – A4 и B4 и прописываем ограничения, нажимаем на кнопку добавить.
Из условия задачи значения выражений левой части меньше или равно значений правой части. Указываем сразу диапазон значений. Жмём на кнопку добавить ограничения.
И выбираем из списка метод решения – решения линейной задачи симплекс методом.
Вылетает информационное окно — результаты поиска решения, жмём Ок.
| Переменные | |
| x1 | x2 |
| 75 | 100 |
| Функция целевая | 1775 |
| 200 | 200 |
| 150 | 150 |
| 800 | 800 |
В результате, в исходной таблице появятся значения неизвестных переменных и значение целевой функции. В итоги мы получили оптимизированные значения переменных, на этом задачи оптимизации линейного программирования решена.
5028
0
Поиск экстремумов функций в Excel
Читайте также
Чтобы было понятно, проведём небольшую тренировку. Напишите в какой-либо ячейке формулу, например такую =(A1-C1)/B1. Теперь «встаньте в эту ячейку», нажмите клавишу F2 (включается режим редактирования формул непосредственно в ячейке). В ячейке появится формула и курсор (в виде палочки). Переместите курсор по ячейке так, чтобы он находился либо рядом с тем адресом, которым Вы хотите управлять (в данном случае B1), либо непосредственно внутри адреса, т.е. либо так =(A1-C1)/|B1, либо так (A1-C1)/B1|, либо так (A1-C1)/B|1.
Нажмите клавишу F4. Формула примет вид =(A1-C1)/$B$1.
Нажмите клавишу F4 ещё раз. Формула примет вид =(A1-C1)/B$1.
Нажмите клавишу F4 ещё раз. Формула примет вид =(A1-C1)/$B1.
Нажмите клавишу F4 ещё раз. Формула примет первоначальный вид =(A1-C1)/B1.
Нажмите клавишу F4 ещё раз. Формула примет вид =(A1-C1)/$B$1. И так далее по кругу.
Итак, имеется 4 представления адреса B1, $B$1, B$1, $B1. Значок доллара $ означает, что следующий за ним элемент адреса при копировании формулы не будет изменяться.
Если в формуле записано B1, то при копировании формулы могут изменяться и название столбца и номер строки.
Если записано $B$1, то при копировании формулы не будут изменяться ни название столбца ни номер строки.
Если записано B$1 то при копировании формулы название столбца будет изменяться, а номер строки изменяться не будет.
Если записано $B1 то при копировании формулы номер строки будет изменяться, а название столбца изменяться не будет.
Время, в Excel является числом, точнее, десятичной дробью меньше единицы. (Соответственно дата — число, больше единицы. А дата с временем — это сумма этих чисел.).
Что бы данное число смотрелось в клетке как Дата, или Время, или Дата с Временем — для этого необходимо задать определенный формат с свойствах ячейки (Втор. кл. мыши по ячейке — пункт «Формат Ячейки»). Следственно, над любой датой и над любым временем можно производить операции сложения и вычитания. Так же надо помнить, что в Excel есть функция (ВРЕМЯ()), которая преобразовывает три значения (часы,минуты,секунды) в специальную десятичную дробь, которая, по сути, является временем «чч:мм:сс», если изменить формат ячейки на «Время-13:30:55».
Итак, например:
- Пусть в ячейке A1 у нас есть время «23:23:59«, тогда
- Пусть, клетках B1,C1,D1 у нас будет количество часов,минут,секунд, (целые числа) которое мы хотим добавить к нашему времени.
- Для получения результата, запишем в клетке E1 формулу: =A1+ВРЕМЯ(B1;C1;D1)
- Если мы поставим в клетку С1 значение 3, тогда мы получим десятичную дробь, которая соответствует 23:26:59. (или «00.01.1900 23:26»).
- Для того, что бы получить в клетке «только время» (без даты, или без дроби), откройте настройки формата ячейки (Правая кл мыши по ячейке и пункт «Формат ячейки») и задайте для нее формат «Время — 13:30:55». Если же у вас в сумме получается время, которое превышает сутки (24 часа), тогда поставьте формат «Время — 37:30:55»
- Как можно понять из приведенного примера, более простая формула в вашем случае (надо добавить 3 минуты к ячейке A1) — будет иметь вид: =A1+ВРЕМЯ(0;3;0)
График можно перенести как картинку обычным копированием.
Для того, что бы перенести график из EXCEL в Power Point. В Excel выделить график, выполнить «копировать», открываем Power Point, открываем нужный слайд, выполняем операцию «вставить».
Зайдите на вкладку «Рецензирование». Выберите необходимую команду — «Защитить лист» или «Защитить книгу». В окрывшемся окне введите пароль, который вы будите использовать при разблокировке файла, листа. Важно: пароль нельзя восстановить, поэтому составляйте комбинацию, которую вы сможете вспомнить, но другой пользователь не сможет её легко подобрать. Или записывайте пароль в отдельном источнике, блокноте и т.п.
В общем-то это просто делается. Когда копируете в буфер обмена содержимое ячейки (не важно с помощью меню, ленты или просто CTRL+C), то потом, когда в другую ячейку надо будет вставить только данные надо кликнуть по стрелочке расположенной рядом с кнопочкой в меню «Вставить». Там выпадет менюшка с запросом чтот именно вы хотите вставить. Если нет прямого указания (например, есть только иконки разные), то копайте глубже через пункт «Специальная вставка». Дальше думаю сами разберетесь…
Лично мне там нравится опция «Вставить ширину колонок». Часто, когда копируешь блок в новое место, в этом месте ширина колонок остается оригинальной, что сильно мешает восприятию информации. Так вот когда скопируешь туда ширины исходных колонок, все становится на свои места.