Решение нелинейных уравнений и систем»
Цель работы : Изучение возможностей пакета Ms Excel 2007 при решении нелинейных уравнений и систем. Приобретение навыков решения нелинейных уравнений и систем средствами пакета.
Задание1. Найти корни полинома x 3 - 0,01x 2 - 0,7044x + 0,139104 = 0.
Для начала решим уравнение графически. Известно, что графическим решением уравнения f(x)=0 является точка пересечения графика функции f(x) с осью абсцисс, т.е. такое значение x, при котором функция обращается в ноль.
Необходимо переоценить торговые балансы. Если продукт хранится на складе более 8 месяцев, мы должны снизить его цену в 2 раза. Мы формируем таблицу с начальными параметрами. 
Теперь у нас есть такое условие: если продукт хранится более 8 месяцев, цена снижается в 2 раза.
Если это более 5 месяцев, но менее 8, это 5 раз. Формула принимает следующий вид. 
В качестве аргументов вы можете использовать другие функции. Это, например, математические. Студенты перед поступлением в гимназию проходят математику, английский и русский. Вы должны получить не менее 4 баллов по математике для приема.
Проведем табулирование нашего полинома на интервале от -1 до 1 с шагом 0,2. Результаты вычислений приведены на ри., где в ячейку В2 была введена формула: = A2^3 - 0,01*A2^2 - 0,7044*A2 + 0,139104. На графике видно, что функция три раза пересекает ось Оx, а так как полином третьей степени имеется не более трех вещественных корней, то графическое решение поставленной задачи найдено. Иначе говоря, была проведена локализация корней, т.е. определены интервалы, на которых находятся корни данного полинома: [-1,-0.8], и .
Необходимо сравнить общее количество очков с проходящей оценкой. И проверьте, что математический балл не был ниже 4. В столбце «результат» поставить «принято» или «не принято». 
Проанализируйте стоимость остатков денежных средств после девальвации. Если цена продукта после переоценки ниже средних значений, мы должны списать продукт со склада.
Работа с таблицей в предыдущем разделе. Для решения проблемы используйте формулу такой формы. Найдите средние продажи в магазинах. Мы установили таблицу с исходными данными. 
Вам нужно найти арифметическое значение для ячеек, значение которого соответствует предопределенному условию. То есть объединить логическое и статистическое решение.
Теперь можно найти корни полинома методом последовательных приближений с помощью команды Данные→Работа с данными→Анализ «Что-Если» →Подбор параметра .
После ввода начальных приближений и значений функции можно обратиться к команде Данные→Работа с данными→Анализ «Что-Если» →Подбор параметра и заполнить диалоговое окно следующим образом.
Решение нелинейных уравнений и систем»
Ниже таблицы с условием есть таблица для отображения результатов. Мы решаем задачу с одной функцией. Это количество магазинов в таблице продаж. 
Найдите средние продажи в магазине №1 в Нью-Йорке и №2 в Лос-Анджелесе. Измените таблицу из предыдущего примера.
Задания для самостоятельного решения
Необходимо выполнить два условия - использовать функцию вида. 
Четвертый и пятый аргументы - это диапазоны для проверки второго условия соответственно. После =, то, что будет дальше, зависит от того, что вы пытаетесь сделать. Если бы вы умножали числа, вы бы просто набрали соответствующие числа и математический символ. Как базовая формула, вам нужно начать с знака равенства.
В поле Установить в ячейке дается ссылка на ячейку, в которую введена формула, вычисляющая значение левой части уравнения (уравнение должно быть записано так, чтобы его правая часть не содержала переменную). В поле Значение вводим правую часть уравнения, а в поле Изменяя значения ячейки дается ссылка на ячейку, отведенную под переменную. Заметим, что вводить ссылки на ячейки в поля диалогового окна Подбор параметров удобнее не с клавиатуры, а щелчком на соответствующей ячейке.
После этого вы должны поместить имя функции, а затем диапазон ячеек в круглых скобках, разделенных двоеточием. Сумма данных выбранного диапазона ячеек появится в ячейке с помощью формулы. Если какое-либо из значений в этом диапазоне ячеек будет изменено, ячейка с формулой суммирования автоматически обновится, чтобы отразить новую сумму.
Они очень полезны, если вам нужно вычислить некоторые значения на основе некоторых логических критериев. Например, вы хотите, чтобы поле показывало вам число, когда другое поле больше определенного значения. 
Категория Логическая Эта категория содержит логические функции.
После нажатия кнопки ОК появится диалоговое окно Результат подбора параметра с сообщением об успешном завершении поиска решения, приближенное значение корня будет помещено в ячейку А14.
Два оставшихся корня находим аналогично. Результаты вычислений будут помещены в ячейки А15 и А16.
Если функция ожидает одно значение, но вы ввели диапазон ячеек, значение берется из диапазона ячеек, которые находятся в том же столбце или строке, что и формула. Логическое значение 30 - условия, которые необходимо проверить. Когда диапазон вводится как параметр, функция использует значение из диапазона, который находится в текущем столбце или строке.
Если функция ожидает одно значение, но вы ввели диапазон ячеек, значение, которое находится в том же столбце или строке, что и формула, взято из диапазона ячеек. Представлен доступ к различным источникам данных и использованию таблиц, а также поиск данных и информации. На основе собранных данных вы создаете сложные и содержательные оценки и отчеты.
Задание 2. Решить уравнение e x - (2x - 1) 2 = 0.
Проведем локализацию корней нелинейного уравнения.
Для этого представим его в виде f(x) = g(x) , т.е. e x = (2x - 1) 2 или f(x) = e x , g(x) = (2x - 1) 2 , и решим графически.
Графическим решением уравнения f(x) = g(x) будет точка пересечения линий f(x) и g(x).
Запуск инструмента Поиск решения
Семинар начинается с «простого» создания сводных таблиц. Функции этой категории. Что делает эта функция, это указать логический тест для выполнения. . В этом типе функций появляются три элемента, также называемые аргументами. Условие или несколько, которые являются теми, которые позволят оценить содержимое ячеек. Фальшивое значение, напротив, будет результатом части Условие, которое не выполнено. Истинное значение, результат части выполняемого условия. . Это означает следующее.
Если продавец достигли оборота более 500 евро, он получает плюс для целей. Постепенно вы будете связаны с этой функцией, и вы найдете много поводов для ее применения. Эти функции можно сгруппировать по нескольким категориям. Агрегатные функции Функции подсчета Функции Логические функции Информационные функции Текстовые функции Функции даты. Кроме того, используя клавиши со стрелками вверх и вниз на клавиатуре, вы можете выделить любую из доступных функций, и будет отображаться краткое описание.
Построим графики f(x) и g(x). Для этого в диапазон А3:А18 введем значения аргумента. В ячейку В3 введем формулу для вычисления значений функции f(x): = EXP(A3), а в С3 для вычисления g(x): = (2*A3-1)^2.
Результаты вычислений и построение графиков f(x) и g(x):
На графике видно, что линии f(x) и g(x) пересекаются дважды, т.е. данное уравнение имеет два решения. Одно из них тривиальное и может быть вычислено точно:
Эти функции работают только с числовыми столбцами и обычно могут собирать только один столбец за раз. Эти функции проходят через таблицу и оценивают выражение для каждой строки. Эти функции подсчитывают разные элементы, такие как разные значения, непустые значения и строки таблицы.
Эти специальные функции также могут быть выражены операторами. Хотя эти функции могут быть косвенно полезными, важно заранее знать тип данных столбцов, а не полагаться на эти функции для предоставления типа данных. Хотя эти функции полезны для расчета и извлечения информации из значений даты, они не применяются к временному анализу данных, который использует таблицу дат.
Для второго можно определить интервал изоляции корня: 1,5 < x < 2.
Теперь можно найти корень уравнения на отрезке методом последовательных приближений.
Введём начальное приближение в ячейку Н17 = 1,5, и само уравнение, со ссылкой на начальное приближение, в ячейку I17 = EXP(H17) - (2*H17-1)^2.
Создание формул для выполнения определенных вычислений может быть сложной и трудоемкой задачей. Функции представляют собой предопределенные формулы, которые делают вычисления с использованием определенных значений, вызывая аргументы в определенной форме, называемой синтаксисом.
Построение функций может быть относительно сложным в нескольких ситуациях, особенно в случае тех, кто работает с различными аргументами. Чтобы использовать мастер функций, выполните следующие действия. Выберите ячейку, в которую будет помещена функция. Нажмите кнопку «Вставить функцию» на стандартной панели инструментов.
и заполним диалоговое окно Подбор параметра .
Результат поиска решения будет выведен в ячейку Н17.
Задание 3 . Решить систему уравнений:
Прежде чем воспользоваться описанными выше методами решения систем уравнений, найдем графическое решение этой системы. Отметим, что оба уравнения системы заданы неявно и для построения графиков, функций соответствующих этим уравнениям, необходимо разрешить заданные уравнения относительно переменной y.
В открывшемся окне «Вставить функцию» вы выберите тип требуемой функции и ее имя. После выбора функции открывается новое диалоговое окно, в котором вы должны сначала прочитать его описание. После этого значения вводятся во все аргументы, которые составляют выбранную функцию.
После закрытия окна мастера функций процесс записи функции завершен. Эта функция подсчитывает элементы, составляющие определенный список данных. Он суммирует ячейки в соответствии с определенным условием. Умножает все аргументы и возвращает продукт. Нажмите кнопку «Вставить функцию».
Для первого уравнения системы имеем:
Выясним ОДЗ полученной функции:
Второе уравнение данной системы описывает окружность.
Фрагмент рабочего листа MS Excel с формулами, которые необходимо ввести в ячейки для построения линий, описанных уравнениями системы. Точки пересечения линий изображенных являются графическим решением системы нелинейных уравнений.
Сделать ссылки из предыдущей точки абсолютными. Окончательный результат показан на рисунке 1. Сравнивает значение, найденное в первой строке списка, и возвращает связанное значение в том же столбце, но в указанной строке. Пример. Нажмите значок «Вставить функцию».
Затем измените формулу следующим образом: «Очень хорошо», чтобы «одобрить». Различие; «Хорошо», «Достаточно» для «Утвержден» и «Недостаточно» для «Отклонено». Рассмотрим следующую таблицу и предположим, что вы хотите знать, сколько садов произвело более 700 кг ч-1.
Эта функция используется для определения коэффициента корреляции между значениями двух наборов данных. В следующей таблице представлены данные, относящиеся к нескольким годам, которые относятся к производству кукурузы-фуража с азотным удобрением и предназначены для определения корреляции между этими двумя факторами.
Не трудно заметить, что заданная система имеет два решения. Поэтому процедуру поиска решений системы необходимо выполнить дважды, предварительно определив интервал изоляции корней по осям Оx и Oy . В нашем случае первый корень лежит в интервалах (-0.5;0) x и (0.5;1) y , а второй - (0;0.5) x и (-0.5;-1) y . Далее поступим следующим образом. Введем начальные значения переменных x и y, формулы отображающие уравнения системы и функцию цели.
Соответствие между двумя переменными можно увидеть на рис. 1-23. Рисунок 1-23: Связь между производством и оплодотворением. В приведенной ниже таблице приведены данные за несколько лет, касающиеся производства кукурузы и фуража для внесения удобрений в азот, и направлены на определение стандартного отклонения от производства.
Таким образом, стандартное отклонение соответствует 274. Эти функции позволяют рассчитать, например, ежемесячную оплату кредита, будущую стоимость аннуитета или норму прибыли от инвестиций. Процентная ставка и факторы риска лежат в основе большинства финансовых формул. То есть, эти факторы позволяют сравнить между «тратой» евро сейчас и «тратой» того же евро в будущем плюс вознаграждение за «прохождение» времени, так называемую «премию». Это можно сделать, сравнивая «текущую стоимость» суммы с ожидаемой «будущей стоимостью» этой же суммы.
Теперь дважды воспользуемся командой Данные→Анализ→Поиск решений, заполняя появляющиеся диалоговые окна.
Связь между этими двумя значениями такова. Большинство формул в этом классе включают эти три фактора в сочетании с двумя другими факторами: количеством периодов, количеством платежей или вкладов, сделанных во время займа или инвестиций; и оплата, которая представляет собой сумму денег, уплаченных или вложенных в каждый период.
Когда кто-то занимается кредитом на покупку оборудования или дома, основная проблема заключается почти всегда в том, что они будут платить за каждый период, чтобы их бюджет не был несбалансирован. Чтобы рассчитать этот периодический платеж, используйте функцию.
Сравнив полученное решение системы с графическим, убеждаемся, что система решена верно.
Задания для самостоятельного решения
Задание 1 . Найти корни полинома
Задание 2 . Найдите решение нелинейного уравнения.
Задание 3 . Найдите решение системы нелинейных уравнений.
Как Вы уже знаете, формулы в Microsoft Excel позволяют определить значение функции по ее аргументам. Однако может возникнуть ситуация, когда значение функции известно, а аргумент требуется найти (т.е. решить уравнение). Для решения подобных проблем предназначена специальная функция Goal Seek (Подбор параметра) .
Поиск параметра.
Специальная функция Goal Seek (Подбор параметра) позволяет определить параметр (аргумент) функции если известно ее значение. При подборе параметра значение влияющей ячейки (параметра) изменяется до тех пор, пока формула, зависящая от этой ячейки, не возвратит заданное значение.
Рассмотрим процедуру поиска параметра на простом примере: решим уравнение 10 * x - 10 / x = 15
. Здесь параметр (аргумент) - x
. Пусть это будет ячейка A3
. Введем в эту ячейку любое число, лежащее в области определения функции (в нашем примере это число не может быть равно нулю). Это значение будет использовано в качестве начального. Пусть это будет 3
. Введем формулу =10*A3-10/A3
, по которой должно быть получено требуемое значение, в какую-либо ячейку, например, B3
. Теперь можно запустить функцию поиска параметра, выбрав команду Goal Seek (Подбор параметра)
в меню Tools (Сервис)
. Введите параметры поиска:
- В поле Set cell (Установить в ячейке) введите ссылку на ячейку, содержащую необходимую формулу.
- Введите искомый результат в поле To value (Значение) .
- В поле By changing cell (Изменяя значение ячейки) введите ссылку на ячейку, содержащую подбираемое значение.
- Кликните на клавише OK .
По окончании работы функции на экране появится окно, в котором будут отображены результаты поиска. Найденный параметр появится в ячейке, которая была для него зарезервирована. Обратите внимание на тот факт, что в нашем примере уравнение имеет два решения, а параметр подобран только один - это происходит потому, что параметр изменяется только до тех пор, пока требуемое значение не будет возвращено. Первый найденный таким образом аргумент и возвращается к нам в качестве результата поиска. Если в качестве начального значения в нашем примере указать -3
, тогда будет найдено второе решение уравнения: -0,5
.
Достаточно сложно правильно определить наиболее подходящее начальное значение. Чаще мы можем сделать какие-либо предположения об искомом параметре, например, параметр должен быть целым (тогда мы получим первое решение нашего уравнения) или неположительным (второе решение).
Задачу поиска параметра при налагаемых граничных условиях поможет решить специальная надстройка Microsoft Excel Solver (Поиск решения) .
Поиcк решения.
Надстройка Microsoft Excel Solver (Поиск решения) не устанавливается автоматически при обычной установке:
- В меню Tools (Сервис) выберите команду Add-Ins (Надстройки) . Если диалоговое окно Add-Ins (Надстройки) не содержит команды Solver (Поиск решения) , нажмите кнопку Browse (Обзор) и укажите диск и папку, в которой содержится файл надстройки Solver.xla (как правило, это папка Library\Solver ) или запустите программу установки Microsoft Office, если найти файл не удается.
- В диалоговом окне Add-Ins (Надстройки) установите флажок Solver (Поиск решения) .
Процедура поиска решения позволяет найти оптимальное значение формулы, содержащейся в ячейке, которая называется целевой. Эта процедура работает с группой ячеек, связанных с формулой в целевой ячейке. Процедура изменяет значения во влияющих ячейках до тех пор, пока не получит оптимальный результат по формуле, содержащейся в целевой ячейке. Чтобы сузить множество значений, применяются ограничения, которые могут иметь ссылки на другие влияющие ячейки. Процедуру поиска решения можно также использовать для определения значения влияющей ячейки, которое соответствует экстремуму целевой ячейки, например, количество учебных занятий, обеспечивающее максимальную успеваемость.
В диалоговом окне Solver (Поиск решения)
так же, как и в диалоговом окне Goal Seek (Подбор параметра)
, необходимо указать целевую ячейку, ее значение и ячейки, которые следует изменять для достижения цели. Для решения задач оптимизации целевую ячейку следует указать равной максимальному или минимальному значению.
Если Вы щелкните на кнопке Guess (Предположить) , Excel сам попытается найти все ячейки, влиящие на формулу.
Вы можете добавить граничные условия, кликнув на клавише Add (Добавить) .
Кликнув на кнопке Options (Параметры) , можно изменить условия поиска решения: максимальное время поиска решения, количество итераций, точность решения, допуск на отклонение от оптимального решения, метод экстраполяции (линейная или квадратичная), алгоритм оптимизации и т.д.
Вернемся к предыдущему примеру: для того, чтобы получить второе (неположительное) решение, достаточно добавить граничное условие A3 . Так же как и при подборе параметра, на экране появится окно, в котором будет отображен отчет о результатах поиска требуемого решения. Само решение будет показано в предназначенных для него ячейках (в ячейке A3 отобразится значение -0.50 ).
Надстройка Microsoft Excel Solver (Поиск решения)
позволяет, также, решать системы уравнений или неравенств. Рассмотрим простой пример: попробуем решить систему уравнений
x + y = 2
x - y = 0
