Как найти корни уравнения подбором параметра

Часто нам нужно предварительно спрогнозировать, какие будут результаты вычислений при определенных входящих параметрах. Например, если получить кредит на закупку товара в банке с более низкой процентной ставкой, а цену товара немного повысить – существенно ли возрастет прибыль при таких условиях?

При разных поставленных подобных задачах, результаты вычислений могут завесить от одного или нескольких изменяемых условий. В зависимости от типа прогноза в Excel следует использовать соответствующий инструмент для анализа данных.

Подбор параметра и решение уравнений в Excel

Данный инструмент следует применять для анализа данных с одним неизвестным (или изменяемым) условием. Например:

2x+1=7

• y=7 является функцией x;
• нам известно значение y, следует узнать при каком значении x мы получим y вычисляемый формулой.

Решим данную задачу встроенными вычислительными инструментами Excel для анализа данных:

1. Заполните ячейки листа, так как показано на рисунке:



2. Перейдите в ячейку B2 и выберите инструмент, где находится подбор параметра в Excel: «Данные»-«Работа с данными»-«Анализ что если»-«Подбор параметра».
3. В появившемся окне заполните поля значениями как показано на рисунке, и нажмите ОК:

В результате мы получили правильное значение 3.

Получили максимально точный результат: 2*3+1=7



Второй пример использования подбора параметра для уравнений

Немного усложним задачу. На этот раз формула выглядит следующим образом:

x²=4

Решение:

1. Заполните ячейку B2 формулой как показано на рисунке:



2. Выберите встроенный инструмент: «Данные»-«Работа с данными»-«Анализ что если»-«Подбор параметра» и снова заполните его параметрами как на рисунке (в этот раз значение 4):



3. Сравните 2 результата вычисления:

Обратите внимание! В первом примере мы получили максимально точный результат, а во втором – максимально приближенный.

Это простые примеры быстрого поиска решений формул с помощью Excel. Сегодня каждый школьник знает, как найти значение x. Например:

x=(7-1)/2

Excel в своих алгоритмах инструментов анализа данных использует более простой метод – подстановки. Он подставляет вместо x разные значения и анализирует, насколько результат вычислений отклоняется от условий указанных в параметрах инструмента. Как только будет, достигнут результат вычисления с максимальной точностью, процесс подстановки прекращается.

По умолчанию инструмент выполняет 100 повторений (итераций) с точностью 0.001. Если нужно увеличить количество повторений или повысить точность вычисления измените настройки: «Файл»-«Параметры»-«Формулы»-«Параметры вычислений»:

Таким образом, если нас не устраивает результат вычислений, можно:

1. Увеличить в настройках параметр предельного числа итераций.
2. Изменить относительную погрешность.
3. В ячейке переменной (как во втором примере, A3) ввести приблизительное значение для быстрого поиска решения. Если же ячейка будет пуста, то Excel начнет с любого числа (рандомно).

Используя эти способы настроек можно существенно облегчить и ускорить процесс поиска максимально точного решения.

О подборе нескольких параметров в Excel узнаем из примеров следующего урока.

Решение Решение нелинейного уравнения методом «Подбор параметра».

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

Подбор параметра – это итерационный
процесс. Для ячейки, которая задана в
поле Изменяя значение ячейки,Excelпоочередно проверяет различные значения
до тех пор, пока не отыщет наиболее
подходящее. По умолчанию программа
производит от 100 итераций или продолжает
вычислять значения до достижения
относительной погрешности 0,001.

Дано: 2x+x⁵-1=0,x(0;1]

Найти корни уравнения с помощью метода
Excel«Подбор параметра».

1. Построим
   график данной функции

1. В
   ячейку G2
   введем формулу для вычисления значения
   функции. В качестве аргумента используем
   ссылку на ячейку F5,
   т.е. = 2*F5+F5^5-1

1. В окне диалога Подбор параметра в
   полеУстановить в ячейке ввел ссылку
   на ячейку с формулой (ячейкуL6),
   в полеЗначение– ожидаемый результат
   (в данном примере ожидаемый результат
   = 0), в полеИзменяя значение ячейки– ссылку на ячейку $L$7, в
   которой будет храниться значение
   подбираемого параметра.

1. После нажатия на кнопку Ok,
   получаю результат подбора параметра.

1. Ответ:
   

Вывод: Я ознакомился со способами
решения нелинейных уравнений методомExcelи «Подбор параметра».
Это очень удобный метод нахождения
неизвестных параметров.

Список использованных источников информации.

1. Додж М. Эффективная работа в Microsoft
Excel 2000/ М. Додж,

К.Стинсон.– СПб.: Питер, 2002. – 1056с.

2. Биллиг В.А. VBA и Office 97. Офисное
программирование/ В.А.

Биллиг, М.И. Дехтярь.– М.: Издательский
отдел «Русская Редакция» ТОО

«Channel Trading Ltd.», 1998.– 720 с.

3. Курицкий Б. Поиск оптимальных решений
средствами Excel 7.0.–

СПб.: BHV – Санкт-Петербург, 1997.– 384с.

4. Гетц К. Программирование в Microsoft Office.
Полное руководство

по VBA/ К. Гетц, М. Джилберт.– Киев:
Издательская группа BHV, 1999.–

768 с.

5. Гарнаев А.Ю. Excel, VBA, Internet в экономике и
финансах.– СПб.:

BHV – Санкт-Петербург, 2001.– 816 с.

6. Петруцос Э. Visual Basic 6 и VBA для профессионалов/
Э. Петруцос,

К. Хау.– СПб.: Питер, 2000.– 432 с.

7. Гусева О.Л. Практикум по Excel/ О.Л. Гусева,
Н.Н. Миронова.– М.:

Финансы и статистика, 1997.– 160 с.

8. Турчак Л.И. Основы численных методов:
Учеб. пособие.– М.: Наука;

Гл. ред. физ.-мат. лит., 1987.– 320 с.

9. Мудров А.Е. Численные методы для ПЭВМ
на языках Бейсик, Фор-

тран и Паскаль.– Томск: МП «Раско»,
1991.– 272 с.

10. http://www.exponenta.ru

11. http://necomod.narod.ru

25

Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]

• #
• #
• #
• #
• #
• #
• #
• #
• #
• #
• #

Тип урока: Обобщение, закрепление
пройденного материала и объяснение нового.

Цели и задачи урока:

• повторение изученных графиков функций;
• повторение и закрепление графического
  способа решения уравнений;
• закрепление навыков записи и
  копирования формул, построения графиков
  функций в электронных таблицах Excel 2007;
• формирование и первичное закрепление
  знаний о решении уравнений с
  использованием возможностей электронных
  таблиц Excel 2007;
• формирование мышления, направленного на
  выбор оптимального решения;
• формирование информационной культуры
  школьников.

Оборудование: персональные
компьютеры, мультимедиапроектор,
проекционный экран.

Материалы к уроку: презентация Power Point
на компьютере учителя (Приложение 1).

Ход урока

Организационный момент.

Слайд 1 из Приложения1 ( далее
ссылки на слайды идут без указания
Приложения1).

Объявление темы урока.

1. Устная работа (актуализация
знаний).

Слайд 2 — Соотнесите перечисленные
ниже функции с графиками на чертеже (Рис. 1):

у = 6 — х; у = 2х + 3; у = (х + 3)²; у = -(х — 4)²;
.

Рис. 1.

Слайд 3 Графический способ решения
уравнений вида f(x)=0.

Корнями уравнения f(x)=0 являются
значения х₁, х₂, … точек
пересечения графика функции y=f(x) с осью
абсцисс (Рис. 2).

Рис. 2.

Слайд 4

Найдите корни уравнения х²-2х-3=0,
используя графический способ решения
уравнений (Рис.3).

Ответ: -1; 3.

Рис. 3.

Слайд 5 Графический способ решения
уравнений вида f (x)=g (x).

Корнями уравнения f(x)=g(x) являются
значения х₁, х₂, … точек
пересечения графиков функций y=f(x) и у=g(x).
(Рис. 4):

Рис. 4.

Слайд 6 Найдите корни уравнения ,
используя графический способ решения
уравнений (Рис. 5).

Ответ: 4.

Рис. 5.

2. Объяснение нового материала.
Практическая работа.

Решение уравнений графическим способом
требует больших временных затрат на
построение графиков функций и в
большинстве случаев дает грубо
приближенные решения. При использовании
электронных таблиц, в данном случае – Microsoft
Excel 2007, существенно экономится время на
построение графиков функций, и появляются
дополнительные возможности нахождения
корней уравнения с заданной точностью (метод
Подбор параметра).

I. Графический способ решения
уравнений вида f(x)=0 в Excel.

Дальнейшая работа выполняется учителем в
Excel одновременно с учениками с подробными (при
необходимости) инструкциями и выводом
результатов на проекционный экран. Слайды
Приложения 1 используются для формулировки
задач и подведения промежуточных итогов.

Слайд 7

Пример1: Используя средства построения
диаграмм в Excel, решить графическим способом
уравнение —х²+5х-4=0.

Для этого: построить график функции у=-х²+5х-4
на промежутке [ 0; 5 ] с шагом 0,25; найти значения х точек пересечения
графика функции с осью абсцисс.

Выполнение задания можно разбить на этапы:

1 этап: Представление функции в
табличной форме (рис. 6):

Рис. 6.

Для этого:

• в ячейку А1 ввести текст Х, в
  ячейку A2 — Y;
• в ячейку В1 ввести число 0, в ячейку С1
  – число 0,25;
• выделить ячейки В1:С1, подвести
  указатель мыши к маркеру выделения, и в
  тот момент, когда указатель мыши примет
  форму черного крестика, протянуть маркер
  выделения вправо до ячейки V1 (Рис. 7).

Рис. 7.

• в ячейку B2 ввести формулу =-(B1^2)+5*B1-4;

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

После ввода формулы в ячейке
окажется результат вычисления по
формуле, а в поле ввода строки формул —
сама формула (Рис. 8):

Рис. 8.

• скопировать содержимое ячейки B2 в
  ячейки C2:V2 за маркер выделения. Весь
  ряд выделенных ячеек заполнится
  содержимым первой ячейки. При этом ссылки
  на ячейки в формулах изменятся
  относительно смещения самой формулы.

2 этап: Построение диаграммы типа График.

Для этого:

• выделить диапазон ячеек B2:V2;
• на вкладке Вставка|Диаграммы|График
  выбрать вид График;
• на вкладке Конструктор|Выбрать данные
  (Рис. 9) в открывшемся окне «Выбор
  источника данных» щелкнуть по кнопке Изменить
  в поле Подписи горизонтальной оси —
  откроется окно «Подписи оси». Выделить в
  таблице диапазон ячеек B1:V1 (значения
  переменной х). В обоих окнах щелкнуть
  по кнопкам ОК;

Рис. 9.

• на вкладке Макет|Оси|Основная
  горизонтальная ось|Дополнительные
  параметры основной горизонтальной оси
  выбрать:

Интервал между делениями: 4;

Интервал между подписями: Единица
измерения интервала: 4;

Положение оси: по делениям;

Выбрать ширину и цвет линии (Вкладки
Тип
линии и Цвет линии);

• самостоятельно изменить ширину и цвет
  линии для вертикальной оси;
• на вкладке Макет|Сетка|Вертикальные
  линии сетки по основной оси выбрать Основные
  линии сетки.

Примерный результат работы приведен на
рис. 10:

Рис. 10.

3 этап: Определение корней уравнения.

График функции у=-х²+5х-4
пересекает ось абсцисс в двух точках и,
следовательно, уравнение -х²+5х-4=0 имеет
два корня: х₁=1; х₂=4.

II. Графический способ решения уравнений
вида f(x)=g(x) в Excel.

Слайд 8

Пример 2: Решить графическим способом
уравнение .

Для этого: в одной системе координат
построить графики функций у₁=
и у₂=1-х
на промежутке [ -1; 4 ] с шагом 0,25; найти значение х точки
пересечения графиков функций.

1 этап: Представление функций в
табличной форме (рис. 1):

• Перейти на Лист2.
• Аналогично Примеру 1, применив
  приемы копирования, заполнить таблицу.
  При табулировании функции у₁=
  воспользоваться встроенной функцией Корень
  (Рис. 11).

Рис. 11.

2 этап: Построение диаграммы типа График.

• Выделить диапазон ячеек (А2:V3);
• Аналогично Примеру 1 вставить и
  отформатировать диаграмму типа График,
  выбрав дополнительно в настройках
  горизонтальной оси: вертикальная ось
  пересекает в категории с номером 5.

Примерный результат работы приведен на
Рис. 12:

Рис. 12.

3 этап: Определение корней уравнения.

Графики функций у₁=
и у₂=1-х пересекаются в одной
точке (0;1) и, следовательно, уравнение
имеет один корень – абсцисса этой точки: х=0.

III. Метод Подбор параметра.

Слайд 9

Графический способ решения уравнений
красив, но далеко не всегда точки
пересечения могут быть такими «хорошими»,
как в специально подобранных примерах 1 и 2.

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

Слайд 10

Пример 3: Разберем метод Подбор
параметра на примере решения уравнения —х²+5х-3=0.

1 этап: Построение диаграммы типа График
для приближенного определения корней
уравнения.

Построить график функции у=—х²+5х-3,
отредактировав полученные в Примере 1
формулы.

Для этого:

• выполнить двойной щелчок по ячейке B2,
  внести необходимые изменения;
• с помощью маркера выделения
  скопировать формулу во все ячейки
  диапазона C2:V2.

Все изменения сразу отобразятся на
графике.

Примерный результат работы приведен на
Рис. 13:

Рис. 13.

2 этап: Определение приближенных
значений корней уравнения.

График функции у=-х²+5х-3
пересекает ось абсцисс в двух точках и,
следовательно, уравнение -х²+5х-4=0 имеет
два корня.

По графику приближенно можно
определить, что х₁≈0,7; х₂≈4,3.

3 этап: Поиск приближенного решения
уравнения с заданной точностью методом Подбор
параметра.

1) Начать с поиска более точного
значения меньшего корня.

По графику видно, что ближайший
аргумент к точке пересечения графика с
осью абсцисс равен 0,75. В таблице
значений функции этот аргумент
размещается в ячейке E1.

• Выделить ячейку Е2;
• перейти на вкладку Данные|Анализ «что-если»|Подбор
  параметра…;

В открывшемся диалоговом окне Подбор
параметра (Рис. 14) в поле Значение
ввести требуемое значение функции: 0.

В поле Изменяя значение ячейки:
ввести $E$1 (щелкнув по ячейке E1).

Щелкнуть по кнопке ОК.

Рис. 14.

Рис. 15.

• В окне Результат подбора (Рис. 15)
  выводится информация о величине
  подбираемого и подобранного значения
  функции:
• В ячейке E1 выводится подобранное
  значение аргумента 0,6972 с требуемой
  точностью (0,0001).

Установить точность можно путем
установки в ячейках таблицы точности
представления чисел – числа знаков
после запятой (Формат ячеек|Число|Числовой).

Итак, первый корень уравнения
определен с заданной точностью: х₁≈0,6972.

2) Самостоятельно найти значение
большего корня с той же точностью. (х₂≈4,3029).

IV. Метод Подбор параметра для
решения уравнений вида f(x)=g(x).

При использовании метода Подбор
параметров для решения уравнений вида f(x)=g(x)
вводят вспомогательную функцию y(x)=f(x)-g(x)
и находят с требуемой точностью значения х
точек пересечения графика функции y(x) с
осью абсцисс.

3. Закрепление изученного материала. Самостоятельная
работа.

Слайд 11

Задание: Используя метода Подбор
параметров, найти корни уравнения
с точностью до 0,001.

Для этого:

• ввести функцию у=
  и построить ее график на промежутке [ -1; 4 ] с
  шагом 0,25 (Рис. 16):

Рис. 16.

• найти приближенное значение х
  точки пересечения графика функции с
  осью абсцисс (х≈1,4);
• найти приближенное решение уравнения с
  точностью до 0,001 методом Подбор
  параметра (х≈1,438).

4. Итог урока.

Слайд 12 Проверка результатов самостоятельной
работы.

Слайд 13 Повторение графического
способа решения уравнения вида f(x)=0.

Слайд 14 Повторение графического
способа решения уравнения вида f(x)=g(x).

Выставление оценок.

5. Домашнее задание.

Слайд 15 .

Используя средства построения диаграмм
в Excel и метод Подбор параметра, определите
корни уравнения х²-5х+2=0 с
точностью до 0,01.

Практическое занятие

Математические задачи. Решение уравнений и систем уравнений.

Пояснения к работе

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

«Подбор параметра» помогает
находить в общем случае приближенные решения уравнений вида f(x)
= 0. Решим простое уравнение:

f(x) = x² — 5x + 6 = 0

Для решения этого уравнения
подготовим рабочий лист. Ячейка B4 будет содержать значение неизвестной x, а
ячейка B5 – значение функции f(x).
Для этого в B5
поместим формулу = B4* B4 — 5* B4+6, как показано на рис. 1

Рис. 1. Подготовка к решению уравнения

Выберем команду. Сервис/Подбор
параметра. Excel отобразит диалоговое окно Подбор
параметра, приведенное на рисунке 2.В этом окне заполним все три окна в
соответствии с результатом, который мы хотим получить. В поле Установить в
ячейке введем адрес формулы (B5),
результаты которой будут подобраны. В поле Значение введем желаемый
результат вычисления формулы (0). Наконец, используя поле Изменяя значения
ячейки, определим адрес ячейки, которая содержит значение, которое нужно
изменить.

Рис. 2. Заполнение окна Подбор
параметра

После щелчка кнопкой Ok Excel выполнит необходимые вычисления и
выведет диалоговое окно Результат подбора параметра.

Рис. 3. Результат Подбора параметра

Ячейка B4 будет содержать найденный корень
уравнения.

Примечание. В нашем случае уравнение имеет
два корня x₁ =2 и x₂ = 3. Excel всегда дает только один корень в
зависимости от начального значения изменяемой ячейки.

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

Решение систем уравнений.

Для решения систем уравнений с несколькими
неизвестными используется надстройка «Поиск решения». Пусть требуется решить
систему уравнений

x² + 5y = 29

5x + y² = 31

Подготовим рабочий лист так, как показано
на рис. 4. Ячейки D4 и D5
содержат формулы, выражающие левые части уравнений, ячейки E1 и E2 – значения неизвестных x и y
(изменяемые ячейки).

Рис. 4. Подготовка к решению системы
уравнений

Выполним команду Сервис/Поиск решения, на
экране откроется диалоговое окно Поиск решения (рис.5).

Рис. 5. Поиск решения. Надстройки

Установим в поле Установить целевую ячейку
адрес первой формулы D4, в поле Равной значению – число 29 (правая
часть первого уравнения), а в поле Изменяя ячейки диапазон E1:E2 (рис.
6)

Рис. 6. Поиск решения

Второе уравнение мы запишем как ограниченное в поле Ограничения.
Для этого нажмите кнопку Добавить в открывшемся диалоговом окне Добавить
ограничения. Заполним соответствующие поля как показано на рис. 7

Рис. 7. Результат поиска решения

После нажатия кнопки ОК произойдет возврат в окно Поиск
решения. Нам остается только щелкнуть по кнопке Выполнить.

Результат поиска решения показан на рис. 7. Полученные
результаты можно сохранить, нажав кнопку ОК.

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

Надстройка «Поиск решения» как и «Выбор параметра»
позволяет находить только одно решение системы.

Надстройка «Поиск решения» помогает решать довольно
сложные задачи на экстремумы функций нескольких переменных при наличии
ограничений на эти переменные.

Варианты заданий

Задание 1

На плоскости заданы координаты точек.
Определить, сколько точек попадает в заданную фигуру, рис. 1а.

Результат определения принадлежности точек и подсчет
количества точек, принадлежащих заданной фигуре, представлен на рис. 2. В
ячейку C4 помещена формула для определения принадлежности
точек фигуре.

Рис. 2. Подсчет количества точек

Количество точек
находим с помощью автосуммы.

Задание 2. Решить
уравнения и системы уравнений

1.    
x³ – x² + 4 cos πx/2 = 0

2.    
x = log x + 5

3.    
x² + xy = 7 –y²

x + 5y² = 9 –x/3

4.    
2x² + 3y
= 10

—x
+ 6y² =4

5.      
3x
-4y = 3

6.      
x³ – sinx – 0,5 = 0

7.      
x² –sinx + 0,1 = 0

8.      
x³ + x²
-12x = 0

9.      
x³ -19 x – 30 = 0

10. 
x³ – x² +
3x – 10 = 0