Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
РЕШЕНИЕ УРАВНЕНИЙ СРЕДСТВАМИ MS Excel
Разнообразные проблемы механики, физики, техники сводятся к вопросу о нахождении корней многочлена, причем, иногда достаточно высоких степеней. Точные решения известны для квадратных уравнений, кубических (формула Кардано) и уравнений 4-й степени (метод Феррари). Для уравнений выше 5-й степени не существует формул для выражения корней многочлена. Однако в технических приложениях обычно достаточно знать лишь приближенные значения корней с некоторой заранее заданной точностью. В общем же случае надежд на простое аналитическое решение нет. Более того, доказано, что даже алгебраическое уравнение выше четвертой степени неразрешимо в элементарных функциях. Поэтому решение уравнения проводят численно в два этапа (здесь разговор идет лишь о вещественных корнях уравнения). На первом этапе производится отделение корней – поиск интервалов, в которых содержится только по одному корню. Второй этап решения связан с уточнением корня в выбранном интервале (определением значения корня с заданной точностью).
В общем виде уравнение n-й степени выглядит следующим образом:
,
где n − некоторое положительное число, − произвольные числа, причем старший коэффициент должен быть не равен нулю.
Выражение называется многочленом (полиномом) n − й степени от неизвестного x.
Если при некотором x = x0 выполняется равенство , то x0 называется корнем многочлена .
Приведем некоторые рекомендации по отысканию действительных корней многочленов с действительными коэффициентами:
Для границы a формула справедлива если
Решение отыскания корней многочлена с помощью электронной таблицы MS Excel предполагает следующие шаги:
При последующем уточнении корня на обнаруженном интервале не надейтесь никогда найти точное значение и добиться обращения функции в нуль при использовании калькулятора или компьютера, где сами числа представлены ограниченным числом знаков. Здесь критерием может служить приемлемая абсолютная или относительная погрешность корня. Если корень близок к нулю, то лишь относительная погрешность даст необходимое число значащих цифр. Если же он весьма велик по абсолютной величине, то критерий абсолютной погрешности часто дает совершенно излишние верные цифры. Для функций, быстро изменяющихся в окрестности корня, может быть привлечен и критерий: абсолютная величина значения функции не превышает заданной допустимой погрешности.
Пример
Найти все действительные корни уравнения:
f(x) = Х5 + 2Х4 + 5Х3 + 8Х2 − 7Х – 3 = 0,
где а5 = 1, а4 = 2, а3 = 5, а2 = 8, а1 = −7, а0 = −3.
Рис. 1. Локализация корней уравнения
Из таблицы и графика видно, что многочлен f(x) содержит 3 корня, находящихся в границах отрезков: 1-й корень , ; 2-й корень , ; 3 - й корень , .
Уточнение корней методом половинного деления (дихотомии)
Самым простейшим из методов уточнения корней является метод половинного деления, или метод дихотомии, предназначенный для нахождения корней уравнений, представленных в виде f(x)=0.
Пусть непрерывная функция f(x) на концах отрезка [a,b] имеет значения разных знаков, т.е. f(a)×f(b) ≤ 0 (рис. 2), тогда на отрезке имеется хотя бы один корень.
Рис. 2. Метод деления отрезка пополам
Возьмем середину отрезка с=(a+b) / 2. Если f(a)×f(с)≤ 0, то корень явно принадлежит отрезку от a до (a+b) / 2 и в противном случае от (a+b) / 2 до b.
Поэтому берем подходящий из этих отрезков, вычисляем значение функции в его середине и т.д. до тех пор, пока длина очередного отрезка не окажется меньше заданной предельной абсолютной погрешности
(b-a) < ε.
Так как каждое очередное вычисление середины отрезка c и значения функции f(c) сужает интервал поиска вдвое, то при исходном отрезке [a,b] и предельной погрешности ε количество вычислений n определяется условием (b-a)/2n < ε, или n ~ log2((b-a)/ε). Например, при исходном единичном интервале и точности порядка 6 знаков (ε ~ 10-6) после десятичной точки достаточно провести 20 вычислений (итераций) значений функции.
С точки зрения машинной реализации этот метод наиболее прост и используется во многих стандартных программных средствах, хотя существуют и другие более эффективные по затратам времени методы.
Вычислительная процедура в Excel может быть реализована так.
В ячейки вносим следующие формулы:
В ячейку А2 − а (левая граница интервала локализации корня);
В ячейку В2 − b (правая граница интервала локализации корня);
В ячейку С2 − =(А2+В2)/2;
В ячейку D2 − =f(A2)*f(C2);
В ячейку F2 − 0,0001 (абсолютная погрешность);
В ячейку А3 − =ЕСЛИ(D2<=0;A2;C2);
В ячейку B3 − =ЕСЛИ(D2<=0;С2;B2);
В ячейку D3 − =f(A3)*f(C3);
В ячейку Е3 − =ЕСЛИ(ABS(B3-A3)>$F$2;”продолжаем”;”конец”);
После этого выделяются ячейки А3:Е3 и автозаполнением буксируются вниз до появления в столбце Е сообщения “конец”. Вычисленный корень с заданной точностью будет находиться в конце столбца F.
Вернемся к примеру, и с помощью метода половинного деления уточним значения корней в выделенных отрезках.
Первый корень находится внутри отрезка [a, b] = [-2,1; -2] расположенного по адресу А2:В2. Заполняем рабочий лист формулами и с заданной точностью 0,0001 определяем его значение (рис. 3). Ответ находится в ячейке С12 и равен X1 = -2,073.
Рис. 3. Вычисление корня Х1
Границы отрезка второго корня находящегося внутри отрезка
[a, b] = [-0,4; -0,3] подставляем в таблицу по адресу А2:В2. Определяем его значение (рис. 4). Ответ находится в ячейке С12 и равен X2 = -0,328.
Границы отрезка третьего корня находящегося внутри отрезка [a, b] = [0,7; 0,8] подставляем в таблицу по адресу А2:В2. Определяем его значение (рис. 5). Ответ находится в ячейке С12 и равен X3 = 0,7893.
Как и предполагалось, имеется три корня, два из которых отрицательные (Х1 = -2,073; Х2 = -0,32808; Х3 = 0,789307).
Рис. 4. Вычисление корня Х2
Рис. 5. Вычисление корня Х3
Уточнение корней рекуррентным методом
(метод касательных Ньютона)
В отличие от метода дихотомии при использовании рекуррентного метода уточнения корней уравнения задается не начальный интервал местонахождения корня, а его начальное приближение.
Наиболее популярным из рекуррентных методов является метод касательных Ньютона.
Рекуррентный метод один из методов задания последовательностей. Любой член последовательности, начиная с некоторого, часто выражают через предшествующие (один или несколько). Например, последовательность 1, 2, 3, 5, 8, 13, … может быть задана следующим образом:
Действительно,
При рекуррентном способе задания последовательности обычно указывают: а) первый член последовательности или несколько первых членов; б) формулу позволяющую определить любой член последовательности по известным предшествующим членам.
На практике вычисление в MS Excel последовательностей по рекуррентным соотношениям приводит к ошибке (предупреждение о циклической ссылке).
Это значит, что в ячейку, в которую введена формула содержит ссылку на эту же самую ячейку (может быть и не напрямую, а опосредовано – через цепочку других ссылок), то говорят, что имеет место циклическая ссылка (цикл). MS Excel не может провести вычисления, так как циклические ссылки порождают бесконечное количество вычислений. Есть два выхода из этой ситуации: устранить циклические ссылки (отказаться от рекуррентного способа вычислений) или допустить вычисления по формулам с циклическими ссылками (в последнем случае число повторений цикла должно быть конечным). Для реализации второй возможности щелкните на кнопке Office (в левом верхнем углу), а затем на Параметры Excel.
В открывшемся окне Параметры Excel перейдите на вкладку Формулы и отметьте Включить итеративные вычисления и установить Предельное число итераций и Относительную погрешность вычислений.
На этой же вкладке, можно выбрать, как будут вестись вычисления: автоматически или вручную. При автоматическом вычислении MS Excel сразу рассчитает конечный результат, при вычислениях, вручную, можно будет наблюдать результат каждой итерации (простым нажатием функциональной клавиши F9 запуская каждый новый цикл вычисления).
Уточним корни уравнения рис.1 рекуррентным методом касательных Ньютона. Для решения уравнения нам понадобится рекуррентная формула (то есть, формула, выражающая каждый член последовательности через один или несколько предыдущих членов):
,
где – переменная;
– функция, задающая уравнение, корни которого мы ищем; ;
– производная функции ; .
Итоговая формула имеет вид:
Выберем любую ячейку (например, О2) на листе MS Excel, присвоим ей имя x, и введем в нее формулу:
=x–(x^5+2*x^4+5*x^3+8*x^2–7*x–3)/(5*x^4+8*x^3+15*x^2+16*x–7).
После ввода в ячейку формулы и ее выполнения в ячейке появится значение –0,32804, соответствующее одному из корней уравнения.
В нашем случае начальное приближение не задавалось, вычислительный процесс начинался со значения, по умолчанию хранимого в ячейке х и равного нулю. При этом ближним оказался корень , принадлежащий промежутку (рис. 1). А как получить первый и третий корни? Для этого мы будем использовать логическую функцию ЕСЛИ, где в качестве второго аргумента будем подставлять значения из промежутков корня и для которых значения промежутков и ближе к 0: значение –2,1 (для корня ) и значение 0,8 (для корня ).
Рис. 6. Уточнение корня Х1.
Рис. 7. Уточнение корня Х3.
Ответ:
Уточнение корней средством “Подбор параметра”
Когда желаемый результат вычислений по формуле известен (подстановка значения корня в уравнение делает его равным нулю), но неизвестны значения, необходимые для получения этого результата, можно воспользоваться средством Подбор параметра. При подборе параметра MS Excel изменяет значение в одной конкретной ячейке до тех пор, пока вычисления по формуле, ссылающейся на эту ячейку, не дадут нужного результата.
Когда задаются условия для применения средства Подбор параметра, в одной ячейке обычно вводится формула, а переменная, которая используется в формуле (с некоторым стартовым значением), задана в другой ячейке.
В формуле можно применять больше одной переменной, но средство Подбор параметра позволяет работать только с одной переменной зараз. Для поиска решения в средстве Подбор параметра применяется итеративный алгоритм. Это означает, что функция сначала проверяет заданное исходное значение параметра и проверяет, дает ли это значение нужный результат. Если исходное значение параметра не дает желаемого результата, средство перебирает другие значения, пока не будет найдено решение или компромисс по точности решения или максимальному количеству итераций.
Средство Подбор параметра вызывается командой: лента Данные → группа Работа с данными →Анализ «что-если» → Подбор параметра (рис.8).
Рис.8. Основное диалоговое окно средства Подбор параметра
В окне диалога Подбор параметра в поле Установить в ячейке введем ссылку на ячейку с формулой, в поле Значение − ожидаемый результат, в поле Изменяя значение ячейки − ссылку на ячейку, в которой будет храниться значение подбираемого параметра (содержимое этой ячейки не может быть формулой).
ПРИМЕР
Вычислить корень уравнения f(x) = -5х + 6 = 0.
В ячейку В2 введем любое число, например, 0.
В ячейку В3 введем формулу =-5*В2+6.
Вызовем диалоговое окно Подбор параметра и заполним соответствующие поля. После нажатия на кнопку ОК Excel выведет окно диалога Результат подбора параметра. Если подобранное значение необходимо сохранить, то нажмите на ОК, и результат будет сохранен в ячейке, заданной ранее в поле Изменяя значения ячейки. Для восстановления значения, которое было в ячейке В2 до использования команды Подбор параметра, нажмите кнопку Отмена.
Как видно из примера в ячейке B2 установилось точное значение корня уравнения Х = 1,2.
При подборе параметра Excel использует итерационный (циклический) процесс. Количество итераций и точность устанавливаются в меню Сервис | Параметры … | вкладка Вычисления, в которой задается Предельное число итераций (по умолчанию 100) и Относительная погрешность (по умолчанию 0,001).
Рис. 9. Диалоговое окно Параметры
Если Excel выполняет сложную задачу подбора параметра, можно нажать кнопку Пауза в окне диалога Результат подбора параметра и прервать вычисление, а затем нажать кнопку Шаг, чтобы выполнить очередную итерацию и посмотреть результат. При решении задачи в пошаговом режиме появляется кнопка Продолжить − для возврата в обычный режим подбора параметра.
Возьмем в качестве примера уравнение (рис. 1)
f(x) = Х5 + 2Х4 + 5Х3 + 8Х2 − 7Х – 3 = 0.
Для нахождения корней уравнения с помощью средства Подбор параметра выполним следующие действия:
- В таблице функции (рис.1) выявляем интервалы локализации корней уравнения (перемена знака в значении функции): первый интервал ячейки Е20:Е21, значение (-1,2698 и 3); второй интервал ячейки Е37:Е38, значение (0,80096 и -0,3012); третий интервал ячейки Е48:Е49, значение (-1,6167 и 0,22688);
- В каждом интервале выбираем то значение функции, которое ближе к 0 и составляем пары ячеек «аргумент-значение»: первый корень D20:E20; второй корень D38:E38; третий корень D49:E49.
- Уточняем значения корней средством Подбор параметра (рис. 10, 11, 12).
Рис. 10. Корень уравнения Х1 = -2,073
Рис. 11. Корень уравнения Х2 = -0,32804
Рис. 12. Корень уравнения Х3 = 0,78934
Ответ: Х1 = -2,073; Х2 = -0,328; Х3 = 0,789.
PAGE 10
EMBED Equation.3
EMBED Equation.3