Содержание
- Как найти экстремумы функции эксель
- Поиск экстремумов функции одной переменной в MS Excel.
- Лаб_работы / Лабораторная_работа_10 / Лабораторная работа 10
- 1. Нахождение экстремумов функций
- III. Нахождение экстремумов функций с помощью инструмента Поиск решения
Как найти экстремумы функции эксель
Если функция 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.
Цель работы: Изучение возможностей пакета Ms Excel при решении одномерных задач оптимизации. Приобретение навыков поиска экстремумов одномерной функции средствами пакета.
Решение этой задачи сводится к поиску на заданном отрезке такого значения аргумента, которое доставляет максимальное и (или) минимальное значение целевой функции.
Последовательность и содержание действий такие же, как и при уточнении корня нелинейного уравнения с помощью надстройки Поиск решения в MS EXcel.
Для поиска максимума переключатель варианта в диалоговом окне Поиск решения установить максимальному значению, а для минимума переключатель варианта установить минимальному значению.
Далее задать ограничения для изменяемой ячейки. Порядок установки ограничений следующий:
1. щёлкнуть мышью по кнопке Добавить в диалоговом окне Поиск решения;
2. в появившемся окне Добавление ограничения щелчком мыши по ячейке установить абсолютный адрес изменяемой ячейки в окнеСсылка на ячейку: ;
3. в среднем окне выбрать вид ограничения ( =; =);
4. в окне Ограничение: ввести значение соответствующей границы (в решаемой задаче дваограничения);
5. после установки ограничения щёлкнуть мышью по кнопке ОK;
6. в окне Поиск решения щёлкнуть мышью по кнопке Выполнить.
После завершения поиска решения в ячейке, содержащей формулу для вычисления значений целевой функции, будет отображаться найденный максимум или минимум, а в изменяемой ячейке будет значение аргумента, доставляющее этот экстремум.
Для изменения (корректировки) ограничения надо выделить строку с ограничением и щёлкнуть мышкой по кнопке Изменить, а затем выполнить корректировку. Назначение остальных кнопок в диалоговом окне Поиск решения уяснить самостоятельно.
Нахождение локальных экстремумов функции
Если функция F(x) непрерывна на отрезке [a, b] и имеет внутри этого отрезка локальный экстремум, то его можно найти, используя надстройку Excel Поиск решения. Рассмотрим последовательность нахождения экстремума функции на примере.
Пример 1. Задана неразрывная функция Y= X 2 +X +2. Требуется найти ее экстремум (минимальное значение) на отрезке [-2, 2].
1. В ячейку А3 рабочего листа введите любое число, принадлежащее заданному отрезку, в этой ячейке будет находиться значение Х.
2. В ячейку В3 введите формулу, определяющую заданную функциональную зависимость (рис. 18). Вместо переменной Х в этой формуле должна быть ссылка на ячейку А3: = A2^2 + A2 +2.
3. Выполните команду меню Сервис — Поиск решения.
4. В открывшемся окне диалога Поиск решения в поле Установить целевую ячейку укажите адрес ячейки, содержащей формулу (В3), установите пере-ключатель Минимальному значению, в поле Изменяя значение ячейки укажите адрес ячейки, в которой содержится переменная х.
5. Добавьте два ограничения в соответствующее поле: A3> = -2 и A3 3
Источник
Лаб_работы / Лабораторная_работа_10 / Лабораторная работа 10
Лабораторная работа № 10
Нахождение экстремумов функций
Решение системы нелинейных уравнений
Цель работы: выработать навык использования надстройки MS Excel Поиск решения.
Методика выполнения лабораторной работы
1. Нахождение экстремумов функций
Если функция f(x) непрерывна на отрезке [a, b] и имеет внутри этого отрезка локальный экстремум, то его можно найти, используя надстройку MS Excel Поиск решения.
Рассмотрим последовательность нахождения экстремума функции на следующих примерах.
Пусть задана функция Z = X 4 + Y 4 – X 2 – 2XY – Y 2 . Требуется найти ее экстремум (минимальное значение).
Для решения задачи выполните действия (рис. 1):
В ячейку В4 рабочего листа введите любое число принадлежащее области определения функции, в этой ячейке будет находиться значение Х (например 5);
В ячейку В5 введите любое число принадлежащее области определения функции, в этой ячейке будет находиться значение У (например 2);
В ячейку В6 введите формулу определяющую заданную функцию. Вместо переменной Х в этой формуле должна быть ссылка на ячейку А4, а вместо переменной У ссылка на ячейку В5.
Р 
ис. 1.
Выполните команду меню Сервис | Поиск решения (рис.2);
Н 
ажав на кнопку Параметры в окне инструмента Поиск решения, откройте окно Параметры поиска решения (рис. 3) и установите: число итераций – 1000, относительная погрешность 0,00001.
В поле Установить целевую ячейку укажите адрес ячейки, содержащей формулу ( B6), установите переключатель Минимальному значению, в поле Изменяя ячейки введите адрес ячеек, содержащих Х и У (А4:А5);
Щелкните на кнопке Выполнить. В ячейках В4 и В5 будут помещены значения Х и У при которых функция Z имеет минимальное значение, а в ячейке В6 – минимальное значение функции.
Ответ: X = 0,99999; Y = 1; Z = −2.
Пусть задана функция Z = X 2 + Y 2 . Требуется найти ее экстремум (минимальное значение) при условии: X + 2Y = 1.
Для решения задачи выполните действия (рис. 4):
В ячейку В4 рабочего листа введите любое число принадлежащее области определения функции, в этой ячейке будет находиться значение Х;
В ячейку В5 введите любое число принадлежащее области определения функции, в этой ячейке будет находиться значение У;
В ячейку В6 введите формулу, определяющую заданную функцию. Вместо переменной Х в этой формуле должна быть ссылка на ячейку В4, а вместо переменной У ссылка на ячейку В5.
В ячейку В6 введите формулу функции =В4^2 + В5^2.
В ячейку B7 введите формулу условия = В4 + 2*В5.
В 
ячейки В4 и В5 введите начальные значения (например 1 и 3 соответственно).
Выполните команду меню Сервис | Поиск решения;
Настройте параметры инструмента Поиск решения: число итераций – 1000, относительная погрешность 0,00001.
В поле Установить целевую ячейку укажите адрес ячейки, содержащей формулу ( B6), установите переключатель Минимальному значению, в поле Изменяя ячейки введите адрес ячеек содержащих Х и У (В4:В5);
Д 
обавьте ограничение, заполнив форму (рис. 5) и щелкните по кнопке
Выполнить. В ячейке В6 будет помещено минимальное значение функции. В ячейках В4 и В5 величины Х и У при которых функция Z достигает минимума.
Ответ: X = 0,2; Y = 0,4; min Z = 0,2.
2. Решение систем нелинейных уравнений
Применяя надстройку MS Excel Поиск решения можно решать системы нелинейных уравнений. Рассмотрим последовательность решения на примере.
Дана система двух уравнений:
Требуется найти все корни приведенных уравнений, если такие существуют.
Шаг 1. Для определения существования корней построим графики уравнений, представив их в виде y = f(x).
Первое уравнение: 
Границы изменения аргумента [‑1,7; 1,7].
Второе уравнение: 
Границы изменения аргумента [-; +].
Ш 
аг 2. Построим графики уравнений Y1, Y2 и Y3 в границах изменения аргумента [-1,7; 1,7]. В этих границах находится решение первого уравнения. В ячейки А4:А38 вводим аргумент Х. В ячейку В4 вводим формулу =КОРЕНЬ(3−А4^2). В ячейку С4 вводим формулу =−КОРЕНЬ(3−А4^2). В ячейку D4 вводим формулу =(1−2*A4)/3. Введенные формулы копируем до 38 строки. Выделяем диапазоны: В4:В38; С4:С38 и D4:D38. Строим график (рис. 6).
Как видно из рисунка решением системы уравнений являются точки пересечения окружности и прямой. Приблизительное значение корней: [X1; Y1]
Шаг 3. Для дальнейшего поиска корней необходимо привести систему к одному равносильному уравнению. Пара (х, у) является решением системы тогда и только тогда, когда она является решением следующего уравнения с двумя неизвестными:
(х 2 + у 2 − 3) 2 + (2х + 3у − 1) 2 = 0.
Шаг 4. Для уточнения значения 1-го корня выполняем следующие действия (адреса ячеек могут быть другие):
В 
ячейки B1:B2 заносим приблизительные значения 1-го корня -1,3 и 1,2 соответственно.
В ячейку В3 записываем формулу равносильного уравнения.
Вызываем надстройку Поиск решения, и вносит в ее форму наши данные.
Аналогично уточняем 2-й корень, занося в ячейки B1:B2 приблизительные значения 2-го корня 1,7 и -0,8 соответственно.
Шаг 5. Табуляция равносильного уравнения по двум переменным X и Y. Для табуляции уравнения из рисунка 6 определяем отрезки табуляции по переменной X [−1,7; 1,7], по переменной Y [−1; 1,6] и выполняем следующие действия:
На новом рабочем листе в столбец А начиная с ячейки А2 введите последовательность значений Х с шагом 0,1, а в строку 1, начиная с ячейки В1 введите последовательность значений Y с шагом 0,2.
Присвойте диапазонам значений Х и У имена Х и У, соответственно (команда Вставка | Имя | Присвоить …)
В ячейку В2 введите формулу
=(Х^2 + Y^2 −3)^2+(2*X+3*Y−1)^2 и скопируйте ее по всем переменным, заполнив ею диапазон В2:О36.
П 
о полученным данным строим график равносильного уравнения представляющего собой поверхность. Корни системы уравнений находятся во впадинах, где уравнение принимает наименьшие значения.
Для решения, каких задач применяется надстройка MS Excel Поиск решения?
Объясните процесс подготовки задачи к решению с помощью надстройки Поиск решения.
Объясните назначение элементов диалоговых окон: Поиск решения и Параметры поиска решения.
Источник
III. Нахождение экстремумов функций с помощью инструмента Поиск решения
МЕТОДИЧЕСКИЕ УКАЗАНИЯ
К лабораторным работам по дисциплине
«Информационные технологии»
ЭЛЕКТРОННЫЕ ТАБЛИЦЫ MS EXCEL 2007
Решение математических задач в среде Excel
Содержание
I. Численное дифференцирование………………………………………………..1
II. Численное вычисление определенных интегралов………………………. 2
III. Нахождение экстремумов функций с помощью инструмента Поиск решения………………………………………………………………………………3
IV. Решение систем линейных уравнений……………………………………….4
V. Решение нелинейных уравнений методом подбора параметра……………6
VI. Решение систем нелинейных уравнений……………………………………..8
Задания для самостоятельной работы…………………………………………..10
I. Численное дифференцирование
Известно, что численными приближенными методами производная функции в заданной точке может быть вычислена с использованием конечных разностей. Выражение, записанное в конечных разностях, для вычисления производной функции одного переменного имеет вид:
Для вычисления производной в Excel будем использовать приведенную зависимость.
Рассмотрим методику вычисления производной на примере упражнения.
Допустим требуется найти производную функции Y= 2x 3 + x 2 в точке x=3. Производная, вычисленная аналитическим методом, равна 60.
Для вычисления производной выполните следующие действия:
§ табулируйте заданную функцию в окрестности точки х=3 с достаточно малым шагом, например 0,001 (см рис.)
§ в ячейку С2 введите формулу вычисления производной. Здесь ячейка В2 содержит значение хк+1, ячейка А2 — хк.
§ буксировкой скопируйте формулу до строки 7, получим значения производных в точках табуляции аргумента.
Для значения х =3 производная функции равна значению 60,019, что близко к значению, вычисленному аналитически.
II. Численное вычисление определенных интегралов
Для численного вычисления определенного интеграла методом трапеций используется формула:
Методику вычисления определенного интеграла в Excel с использованием приведенной формулы рассмотрим на примере.
Пусть требуется вычислить определенный интеграл
Величина интеграла, вычисленная аналитически равна 9. Для численного вычисления величины интеграла с использованием приведенной формулы выполните следующие действия:
§ табулируйте подинтегральную функцию в диапазоне изменения значений аргумента 0 – 3 (см. рис.).
§ в ячейку С3 введите формулу =(A3-A2)*B2+(A3-A2)*(B3-B2)/2+C2,которая реализует подинтегральную функцию.
§ Скопируйте буксировкой формулу, записанную в ячейке С3 до значения аргумента х = 3. Вычисленное значение в ячейке С17 и будет величиной заданного интеграла — 9.
III. Нахождение экстремумов функций с помощью инструмента Поиск решения
Если функция F(x) непрерывна на отрезке [a, b] и имеет внутри этого отрезка локальный экстремум, то его можно найти используя надстройку Excel Поиск решения.
Рассмотрим последовательность нахождения экстремума функции на примере следующего упражнения.
Пусть задана неразрывная функция Y= X 2 +X +2. Требуется найти ее экстремум (минимальное значение).
Для решения задачи выполните действия:
§ В ячейку А2 рабочего листа введите любое число принадлежащее области определения функции, в этой ячейке будет находиться значение Х;
§ В ячейку В2 введите формулу, определяющую заданную функцию. Вместо переменной Х в этой формуле должна быть ссылка на ячейку А2: =A2^2 + A2 +2
§ Выполните команду меню Сервис/Поиск решения;
§ Настройте параметры инструмента Поиск решения: число итераций – 1000, относительная погрешность 0,00001.
§ в поле Установить целевую ячейку укажите адрес ячейки, содержащей формулу ( А2), установите переключатель Минимальному значению, в поле Изменяя ячейки введите адрес ячейки, содержащей Х (А2);
§ Щелкните на кнопке Выполнить. В ячейке А2 будет помещено значение Х функции, при котором она имеет минимальное значение, а в ячейке В2 – минимальное значение функции.
Обратите внимание, что в окне Поиск решения можно устанавливать ограничения. Их целесообразно использовать, если функция многоэкстремальна, а нужно найти экстремум в заданном диапазоне изменения аргумента.
Воспользуйтесь поиском по сайту:
studopedia.org — Студопедия.Орг — 2014-2023 год. Студопедия не является автором материалов, которые размещены. Но предоставляет возможность бесплатного использования (0.012 с) .
Источник
Adblock
detector
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.
Microsoft Excel поддерживает множество функций, предназначенных для упрощения математических расчетов в электронной таблице. МИН – одна из них, и по названию уже можно понять, что она как-то связана с минимальным значением. Все об этой функции и ее применении в Excel вы узнаете далее.
Объявление и написание функции МИН
Начать стоит с самого важного – синтаксиса функции, поскольку при неправильной записи на экране просто появится ошибка. МИН (MIN) состоит всего из одного аргумента, поэтому запутаться будет тяжело. Задача этой функции – возвращение наименьшего значения из указанного перечня данных. То есть вы выбираете несколько ячеек, а функция покажет, какое число в них минимальное. Кстати, точно так же работает и МАКС, поэтому сегодняшнее руководство можете применить при вычислении максимального значения, объявив подходящую функцию.
Выше вы видите скриншот с окном «Аргументы функции», которое можно использовать для создания функции. В поле «Число» для МИН вы вписываете тот самый диапазон, который и нужно обработать. Если таких диапазонов несколько, запишите второй в «Число2», после чего на экране появится поле «Число3», и так может продолжаться долго, пока вы не впишите все требуемые диапазоны. Если хотите написать функцию в ячейке самостоятельно, используйте для этого =МИН(), а в скобках указывайте диапазоны чисел, отделяя их знаком «;», как об этом сказано в подсказке от разработчиков.
Во время объявления диапазона вы можете выделить его прямо в таблице, и запись появится автоматически. Это общее правило, относящееся ко всем функциям в Excel, поэтому вы, скорее всего, знаете о его существовании. В случае с МИН использование подобного выделения как никогда окажется кстати.
Ниже вы видите, что после расчетов, то есть когда вы нажимаете клавишу Enter, в ячейке с функцией отображается всего одно число, которое и является минимальным в указанных диапазонах. Не получится перечислить все стороны применения подобных расчетов, но обычно пользователь уже сам знает, зачем ему нужно возвращать минимальное число в диапазоне.
При указании в одной функции МИН нескольких диапазонов с данными в результате все равно будет одно число, то есть то, которое является среди всех значений самым маленьким. Это удобно, когда в подсчеты берется сразу несколько столбцов значений, находящихся в одной или разных таблицах.
Комьюнити теперь в Телеграм
Подпишитесь и будьте в курсе последних IT-новостей
Подписаться
Математические операции и функция МИН
Кратко расскажу о том, как вы можете использовать функцию МИН, выполняя различные математические расчеты. Самый простой пример – сложение двух диапазонов данных. Вам нужно объявить несколько функций МИН в одной ячейке, поставив между ними «+», в результате чего два минимальных значения сложатся, а на экране отобразится их сумма. Конечно, вместо плюса вы можете подставить любой знак, а в качестве второй функции при расчетах необязательно использовать МИН.
Запись МИН в других функциях
Некоторые пользователи, только начинающие свой путь в Microsoft Excel, не знают, что в рамках одной функции может находиться и другая. Конечно, непосредственно в МИН нельзя засунуть еще какое-то выражение, поскольку функция применяет только аргумент, а вот саму МИН можно поместить в другую функцию. Например, объявите логическую операцию =ЕСЛИ(МИН(A1:A10)). Вы видите, что первое выражение для ЕСЛИ – это возвращение минимального значения.
Теперь нужно решить, какой знак поставить, а затем записать значения лжи и истины. Полностью формула выглядит следующим образом: =ЕСЛИ(МИН(A1:A10)>X;”+”;”-”). То есть если МИН будет больше указанного числа, значение ЕСЛИ станет «+», в противном случае «-». Вместо X можно подставить любую другую функцию, если в этом есть надобность.
Если описание выше вам показалось сложным, объявите ЕСЛИ самостоятельно и откройте окно «Аргументы функции». В нем вы увидите разделение на поля и поймете, что в логическом выражении нужно написать саму функцию МИН и число либо другую функцию после знака «больше», «меньше» или «равно». Дополнительно сами разработчики представляют подсказки, поэтому понять принцип действий будет нетрудно.
Как видно, МИН – простая функция, которая прекрасно сочетается с другими и может помочь при обработке массивов данных, когда вручную найти требуемое значение не получается. Соблюдайте синтаксис, следите за тем, с какими диапазонами данных работаете, и все у вас получится.
Excel для Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Еще…Меньше
Предположим, вы хотите узнать, у кого самый маленький показатель погрешности в производственной цехе или самая большая заработная плата в вашем отделе. Существует несколько способов вычисления наименьшего или наибольшего числа в диапазоне.
Если ячейки находятся в соединимой строке или столбце
-
Вы можете выбрать ячейку снизу или справа от чисел, для которых нужно найти наименьшее число.
-
На вкладке Главная в группе Редактирование щелкните стрелку рядом с кнопкой
, выберите min (вычисляет наименьшее) или Max (вычисляет наибольшее) и нажмите клавишу ВВОД.
, выберите min (вычисляет наименьшее) или Max (вычисляет наибольшее) и нажмите клавишу ВВОД.Если ячейки не находятся в подрядной строке или столбце
Для этого используйте функции МИН, МАКС, МАЛЫЙ или БОЛЬШОЙ.
Пример
Скопируйте следующие данные на пустой лист.
|
|
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
См. также
НАИБОЛЬШИЙ
MAX
MIN
НАИМЕНЬШИЙ
Нужна дополнительная помощь?
Нужны дополнительные параметры?
Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.
В сообществах можно задавать вопросы и отвечать на них, отправлять отзывы и консультироваться с экспертами разных профилей.
