Метод последовательных приближений в excel
Рис. 1. Пример циклической ссылки
Рис. 2. Параметры Excel
В открывшемся окне «Параметры Excel» перейдите на вкладку Формулы и отметьте «Включить итеративные вычисления» (рис. 3). Помните, что эта опция включается для приложения Excel в целом (а не для одного файла), и будет действовать, пока вы ее не отключите.
Рис. 3. Включить итеративные вычисления
На этой же вкладе, можно выбрать, как будут вестись вычисления: автоматически или вручную. При автоматическом вычислении Excel сразу рассчитает конечный результат, при вычислениях, вручную, можно будет наблюдать результат каждой итерации (простым нажатием F9 запуская каждый новый цикл вычисления).
Решим уравнение третьей степени: х 3 – 4х 2 – 4х + 5 = 0 (рис. 4). Для решения этого уравнения (и любого другого уравнения совершенно произвольного вида) понадобится всего одна ячейка Excel.
Рис. 4. График функции f(x)
Для решения уравнения нам понадобится рекуррентная формула (то есть, формула, выражающая каждый член последовательности через один или несколько предыдущих членов):
(1) x = x – f(x)/f’(x), где
f(x) – функция, задающая уравнение, корни которого мы ищем; f(x) = х 3 – 4х 2 – 4х + 5
f’(x) – производная нашей функции f(x); f’(x) = 3х 2 – 8х – 4; производные основных элементарных функций можно посмотреть здесь.
Если вы заинтересовались, откуда взялась формула (1), можете почитать, например, здесь.
Итоговая рекуррентная формула имеет вид:
(2) х = x – (х 3 – 4х 2 – 4х + 5)/(3х 2 – 8х – 4)
Выберем любую ячейку на листе Excel (рис. 5; в нашем примере это ячейка G19), присвоим ей имя х, и введем в нее формулу:
Можно вместо х использовать адрес ячейки… но согласитесь, что имя х, смотрится привлекательнее; следующую формулу я ввел в ячейку G20:
Рис. 5. Рекуррентная формула: (а) для поименованной ячейки; (б) для обычного адреса ячейки
Как только мы введем формулу и нажмем Enter, в ячейке сразу же появится ответ – значение 0,77. Это значение соответствует одному из корней уравнения, а именно второму (см. график функции f(x) на рис. 4). Поскольку начальное приближение не задавалось, итерационный вычислительный процесс начинался со значения, по умолчанию хранимого в ячейке х и равного нулю. Как же получить остальные корни уравнения?
Для изменения стартового значения, с которого рекуррентная формула начинает свои итерации, предлагается использовать функцию ЕСЛИ: [1]
Здесь значение «-5» – начальное значение для рекуррентной формулы. Изменяя его, можно выйти на все корни уравнения:
Начальное значение | Корень уравнения |
1 | 0,77 |
-5 | -1,40 |
8 | 4,63 |
[1] Идея подсмотрена здесь
7 комментариев для “Excel. Использование циклических ссылок для решения уравнений итерационным способом”
Офигенный сайт!
И как всегда когда не нужно все находишь!
Блин у меня по экономическому моделированию в Excell курсовик был в институте, вот время помню кучу потерял а тут все в одном флаконе:)
Все равно инфа пригодится, даже очень!
И нам зараза в этом гребанном институте мать их так этих учителей даже близко ничего подобного не рассказывали. Я не про этот пример говорю а про остальные..
«Вычисление стандартного отклонения для данных с тенденцией», «Нормальное распределение» и т.п.. даже объяснить не могли или не хотели как это все применять, а тут все наглядно и понятно! Огромное спасибо! Не зря я на этот сайт наткнулся, чую он мне еще окажет неплохую помощь))))
В упор не понимаю откуда берется предел для определения корней уравнения, если «Здесь значение «-5» – начальное значение для рекуррентной формулы. Изменяя его, можно выйти на все корни уравнения»
У меня график получается, который с осью Х не имеет вообще пересечений, мож где накосячила?
Пожалуйста подскажите, а то у меня взрыв мозга будет скоро…((((
Тамара, если Вы строите график на основе моих данных, откройте файл Excel; если Вы используете собственные данные, пришлите мне на mail Ваш файл, попробую помочь))
Спасибо заранее за беспокойство, вот такое уравнение у^3-20у^2-158у-420=0, если не трудно объясните пожалуйста как вы определяте предел в каких знчениях надо считать корни.
Функция ЛГРФПРИБЛ в Excel предназначена для определения значений, на основе которых может быть построена экспоненциальная кривая, аппроксимирующая имеющиеся числовые данные, и возвращает массив значений. Для корректной работы рассматриваемой функции ее следует вводить как формулу массива.
Методы аппроксимации табличных данных в Excel
Функция ЛГРФПРИБЛ возвращает данные, необходимые для построения кривой, описываемой следующим уравнением:
Если имеется две и более переменных, это уравнение переписывается следующим образом:
Возвращаемые рассматриваемой функцией данные представляют собой следующий массив:
То есть, имеем массив оснований, возводимых в степени (известные значения переменных x), и коэффициент b.
Пример 1. В таблице приведены данные, характеризующие динамику курса доллара на протяжении 10 лет (с 2006 по 2016 год). Необходимо спрогнозировать курс доллара на 2019 год на основании имеющихся данных.
Вид таблицы данных:
Для расчета тренда (коэффициент, используемый для предсказания последующих значений курса) используем функцию:
- B2:B12 – известные данные зависимой переменной (значения курса);
- A2:A12 – известные данные независимой переменной (года).
Для предсказания курса на 2019 год используем формулу:
Как видно, полученное значение имеет небольшую степень достоверности. Использование данного типа аппроксимации для предсказания курса валют нерационально.
Прогнозирование финансовых результатов методом аппроксимации в Excel
Пример 2. В таблице имеются данные о зарплатах за прошедший год (помесячно). Определить оптимальный способ предсказания размеров зарплат для последующих периодов.
Вид таблицы данных:
Определим коэффициенты достоверности аппроксимации для линейной и экспоненциальной функций с помощью следующих функций (вводить как формулы массива CTRL+SHIFT+Enter):
Поскольку обе функции возвращают результат в виде массива данных, в котором в третьей строке первого столбца содержится искомое значение R^2, используем функцию ИНДЕКС для возврата желаемого результата.
Чем ближе значение R^2 к 1, тем выше точность аппроксимации. Как видно, наибольшую точность обеспечивает экспоненциальная функция. Однако разница не является существенной, поэтому использование функции ЛИНЕЙН является допустимым в данном случае.
Правила метода аппроксимации по функции ЛГРФПРИБЛ в Excel
Функция имеет следующую синтаксическую запись:
=ЛГРФПРИБЛ( известные_значения_y; [известные_значения_x];[конст];[статистика])
В программе Excel имеется обширный инструментарий для решения различных видов уравнений разными методами.
Рассмотрим на примерах некоторые варианты решений.
Решение уравнений методом подбора параметров Excel
Инструмент «Подбор параметра» применяется в ситуации, когда известен результат, но неизвестны аргументы. Excel подбирает значения до тех пор, пока вычисление не даст нужный итог.
Путь к команде: «Данные» - «Работа с данными» - «Анализ «что-если»» - «Подбор параметра».
Рассмотрим на примере решение квадратного уравнения х 2 + 3х + 2 = 0. Порядок нахождения корня средствами Excel:
- Введем в ячейку В2 формулу для нахождения значения функции. В качестве аргумента применим ссылку на ячейку В1.
- Открываем меню инструмента «Подбор параметра». В графе «Установить в ячейку» - ссылка на ячейку В2, где находится формула. В поле «Значение» вводим 0. Это то значение, которое нужно получить. В графе «Изменяя значение ячейки» - В1. Здесь должен отобразиться отобранный параметр.
- После нажатия ОК отобразится результат подбора. Если нужно его сохранить, вновь нажимаем ОК. В противном случае – «Отмена».
Для подбора параметра программа использует циклический процесс. Чтобы изменить число итераций и погрешность, нужно зайти в параметры Excel. На вкладке «Формулы» установить предельное количество итераций, относительную погрешность. Поставить галочку «включить итеративные вычисления».
Как решить систему уравнений матричным методом в Excel
Дана система уравнений:
- Значения элементов введем в ячейки Excel в виде таблицы.
- Найдем обратную матрицу. Выделим диапазон, куда впоследствии будут помещены элементы матрицы (ориентируемся на количество строк и столбцов в исходной матрице). Открываем список функций (fx). В категории «Математические» находим МОБР. Аргумент – массив ячеек с элементами исходной матрицы.
- Нажимаем ОК – в левом верхнем углу диапазона появляется значение. Последовательно жмем кнопку F2 и сочетание клавиш Ctrl + Shift + Enter.
- Умножим обратную матрицу Ах -1х на матрицу В (именно в таком порядке следования множителей!). Выделяем диапазон, где впоследствии появятся элементы результирующей матрицы (ориентируемся на число строк и столбцов матрицы В). Открываем диалоговое окно математической функции МУМНОЖ. Первый диапазон – обратная матрица. Второй – матрица В.
- Закрываем окно с аргументами функции нажатием кнопки ОК. Последовательно нажимаем кнопку F2 и комбинацию Ctrl + Shift + Enter.
Получены корни уравнений.
Решение системы уравнений методом Крамера в Excel
Возьмем систему уравнений из предыдущего примера:
Для их решения методом Крамера вычислим определители матриц, полученных заменой одного столбца в матрице А на столбец-матрицу В.
Для расчета определителей используем функцию МОПРЕД. Аргумент – диапазон с соответствующей матрицей.
Рассчитаем также определитель матрицы А (массив – диапазон матрицы А).
Определитель системы больше 0 – решение можно найти по формуле Крамера (Dx / |A|).
Для расчета Х1: =U2/$U$1, где U2 – D1. Для расчета Х2: =U3/$U$1. И т.д. Получим корни уравнений:
Решение систем уравнений методом Гаусса в Excel
Для примера возьмем простейшую систему уравнений:
3а + 2в – 5с = -1
2а – в – 3с = 13
а + 2в – с = 9
Коэффициенты запишем в матрицу А. Свободные члены – в матрицу В.
Для наглядности свободные члены выделим заливкой. Если в первой ячейке матрицы А оказался 0, нужно поменять местами строки, чтобы здесь оказалось отличное от 0 значение.
- Приведем все коэффициенты при а к 0. Кроме первого уравнения. Скопируем значения в первой строке двух матриц в ячейки В6:Е6. В ячейку В7 введем формулу: =B3:Е3-$B$2:$Е$2*(B3/$B$2). Выделим диапазон В7:Е7. Нажмем F2 и сочетание клавиш Ctrl + Shift + Enter. Мы отняли от второй строки первую, умноженную на отношение первых элементов второго и первого уравнения.
- Копируем введенную формулу на 8 и 9 строки. Так мы избавились от коэффициентов перед а. Сохранили только первое уравнение.
- Приведем к 0 коэффициенты перед в в третьем и четвертом уравнении. Копируем строки 6 и 7 (только значения). Переносим их ниже, в строки 10 и 11. Эти данные должны остаться неизменными. В ячейку В12 вводим формулу массива.
- Прямую прогонку по методу Гаусса сделали. В обратном порядке начнем прогонять с последней строки полученной матрицы. Все элементы данной строки нужно разделить на коэффициент при с. Введем в строку формулу массива: .
- В строке 15: отнимем от второй строки третью, умноженную на коэффициент при с второй строки (). В строке 14: от первой строки отнимаем вторую и третью, умноженные на соответствующие коэффициенты (). В последнем столбце новой матрицы получаем корни уравнения.
Примеры решения уравнений методом итераций в Excel
Вычисления в книге должны быть настроены следующим образом:
Делается это на вкладке «Формулы» в «Параметрах Excel». Найдем корень уравнения х – х 3 + 1 = 0 (а = 1, b = 2) методом итерации с применением циклических ссылок. Формула:
M – максимальное значение производной по модулю. Чтобы найти М, произведем вычисления:
f’ (1) = -2 * f’ (2) = -11.
Полученное значение меньше 0. Поэтому функция будет с противоположным знаком: f (х) = -х + х 3 – 1. М = 11.
В ячейку А3 введем значение: а = 1. Точность – три знака после запятой. Для расчета текущего значения х в соседнюю ячейку (В3) введем формулу: =ЕСЛИ(B3=0;A3;B3-(-B3+СТЕПЕНЬ(B3;3)-1/11)).
В ячейке С3 проконтролируем значение f (x): с помощью формулы =B3-СТЕПЕНЬ(B3;3)+1.
Корень уравнения – 1,179. Введем в ячейку А3 значение 2. Получим тот же результат:
Одна из наиболее актуальных проблем компьютерного обучения – проблема отбора и использования педагогически целесообразных обучающих программ.
При изучении отдельных тем и решении некоторых задач на уроках математики в старших классах громоздкие вычисления как, например, при решении уравнений методом деления отрезка пополам или методом последовательных приближений, затмевают существо математической задачи, не дают увидеть красоту, рациональность применяемого метода решения.
В данной статье я представила те задачи, решение которых с помощью MS EXCEL позволяет получить наглядное, доступное для понимания учащимися решение, показать его логику, рациональность. Попутно учащиеся получают устойчивые навыки работы с программой.
Нахождение корней уравнения с помощью подбора параметра Пример 1.
Пусть известно, что в штате больницы состоит 6 санитарок, 8 медсестер, 10 врачей, 3 заведующих отделениями, главный врач, заведующий аптекой, заведующая хозяйством и заведующий больницей. Общий месячный фонд зарплаты составляет 1000 000 условных единиц. Необходимо определить, какими должны быть оклады сотрудников больницы.
Решение такой задачи можно искать методом перебора. Однако в лучшем случае на это уходит много времени. Можно предложить другой способ решения. В EXCEL он реализован как поиск значения параметра формулы, удовлетворяющего ее конкретному значению.
Построим модель решения этой задачи. За основу возьмем оклад санитарки, а остальные оклады будем вычислять, исходя из него: во столько-то раз или на столько-то больше. Говоря математическим языком, каждый оклад является линейной функцией от оклада санитарки: Ai*С+Вi, где С – оклад санитарки; Аi и Вi – коэффициенты, которые для каждой должности определяют следующим образом:
– медсестра получает в 1,5 раза больше санитарки (А2=1,5; В2=0);
– врач – в 3 раза больше санитарки (А3=3; В3=0);
– заведующий отделением – на 30 y. e. больше, чем врач (А4=3; B4=30);
– заведующий аптекой – в 2 раза больше санитарки (А5=2; В5=0);
– заведующий хозяйством – на 40 y. e. больше медсестры (А6=1,5; В6=40);
– заведующий больницей – на 20 y. e. больше главного врача (А8=4; В8=20);
– главный врач – в 4 раза больше санитарки (А7=4; В7=0);
Зная количество человек на каждой должности, нашу модель можно
где N1 – число санитарок, N2 – число медсестер и т. д.
В этом уравнении нам известны A1. . . A8, B1. . . B8 и N1. . . N8, а С неизвестно. Анализ уравнения показывает, что задача вычисления заработной платы свелась к решению линейного уравнения относительно С. Предположим, что зарплата у санитарки 150,00 y. e.
Введите исходные данные в рабочий лист электронной таблицы, как показано ниже.
В столбце D вычислите заработную плату для каждой должности. Например, для ячейки D4 формула расчета имеет вид =B4*$D$3+C4.
В столбце F вычислите заработную плату всех работников данной должности. Например, для ячейки F3 формула расчета имеет вид =D3*E3.
В ячейке F11вычислите суммарный фонд заработной платы больницы. Рабочий лист электронной таблицы будет выглядеть, как показано ниже.
Чтобы определите оклад санитарки так, чтобы расчетный фонд был равен заданному надо:
1. Активизировать команду Подбор параметра во вкладке Данные / Работа с данными /Анализ «Что, если»;
2. В поле "Установить в ячейке" появившегося окна ввести ссылку на ячейку F11, содержащую формулу;
3. В поле "Значение" набрать искомый результат 1000000;
4. В поле "Изменяя значение ячейки" ввести ссылку на изменяемую ячейку D3 и щелкните на кнопке ОК.
Анализ задачи показывает, что с помощью Excel можно решать линейные уравнения. Конечно, такое уравнение может решить любой школьник. Однако, благодаря этому простому примеру стало, очевидным, что поиск значения параметра формулы, удовлетворяющего ее конкретному значению, – это не что иное, как численное решение уравнений. Другими словами, используя Excel, можно решать любые уравнения с одной переменной.
Приложение 1
Задание для учащихся:
Составить несколько вариантов штатного расписания с использованием функции Подбор параметра и оформить их в виде таблицы:
– Изменить количество сотрудников на различных должностях;
– Подобрать зарплату санитарки в новых условиях;
– Составить таблицу нескольких вариантов штатного расписания.
Рассмотрим еще один пример нахождения корней уравнения с помощью подбора параметра. При решении этого уравнения используется также метод последовательных приближений. Учащиеся в классах с углубленным изучением математики знакомы с этим методом. Поэтому, чтобы этот пример был доступен для других учащихся, предлагаю краткую теорию этого метода.
Пусть дано уравнение, записанное в виде x=F(x). Выбирают некоторое начальное приближение x1 и подставляют его вместо x в F(x). Полученное значение x2=F(x1) этой функции считают вторым приближением. Далее находят третье приближение по формуле x3=F(x2) и так далее. Таким образом, получаем последовательность x1, x2, x3,…, xn,… чисел, имеющая предел α. Тогда если функция F(x) непрерывна, из равенства xn+1=F(xn) получаем α=F(α). Это означает, что α является решением уравнения x=F(x).
Пример 2.
Пусть нам дан многочлен третьей степени:
Так как мы ищем корни полинома третьей степени, то имеются не более трех вещественных корней. Для нахождения корней их первоначально надо локализовать, то есть найти интервалы, на которых они существуют. Такими интервалами локализации корней могут служить промежутки, на концах которых функция имеет противоположный знак. С целью нахождения интервалов, на концах которых функция изменяет знак, необходимо построить ее график или протабулировать ее. Составим таблицу значений функции на интервале [-1;1] с шагом 0,2. Для этого необходимо:
Тема: «Нахождение с заданной точностью корня уравнения F(x)=0 на промежутке [a;b] методом простой итерации».
Контрольный пример: 0,25-х+sinx=0
Условия задачи: для заданной функции F(x) на интервале [0,5;2] найти корень уравнения F(x)=0 методом простой итерации.
Корень вычислить дважды(с помощью автоматического и ручного расчета).
Предусмотреть построение графика функции на заданном интервале.
1.Теоретическая часть 5
2.Описание хода работы 7
Библиографический список 12
В ходе данной работы мне необходимо ознакомиться с различными методами решения уравнения и найти корень нелинейного уравнения 0,25-х+sin(x)=0 численным методом – методом простой итерации. Для проверки правильности нахождения корня необходимо решить уравнение графически ,найти приближенное значение и сравнить его с полученным результатом.
Метод простой итерации.
Итерационный процесс состоит в последовательном уточнении начального приближения х0 (корня уравнения). Каждый такой шаг называется итерацией.
Для использования этого метода исходное нелинейное уравнение записывается в виде: х=j(х), т.е. выделяется х; j(х) – непрерывна и дифференцируема на интервале (а; в). Обычно это можно сделать несколькими способами:
arcsin(2x+1)-x 2 =0 (f(x)=0)
x=0.5(sinx 2 -1) (x=j(x))
x=x+arcsin(2x+1)-x 2 (x=j(x))
x= (x=j(x)),знак берется в зависимости от интервала [а;b].
Пусть известно начальное приближение корня x=c0.Подставляя это значение в правую часть уравнения x=j(x),получаем новое приближение корня:c=j(c0).Далее, подставляя каждый раз новое значение корня в x=j(x),получаем последовательность значений
Процесс итераций следует продолжать до тех пор,пока для двух последовательных приближений не будет выполнено условие: ½cn-cn-1½
Программа Excel реализует метод простой итерации двумя способами с помощью ручного расчета и с автоматическим контролем точности.
у у=х
1.
j(с0)
Рис. График итерационного процесса
2.Описание хода работы.
2. Построил график функции y=x и y=0,25+sin(x) на отрезке [0,5;2] с шагом 0,1 назвал лист «График».
3. Выбрал команду Сервис®Параметры.
Открыл вкладку Вычисления.
Включил режим Вручную.
Отключил флажок Пересчет перед сохранением. Сделал значение поля Пре-дельное число итераций равным 1,относительную погрешность 0,001.
4. Ввел в ячейку А1 строку «Решение уравнения x=0,25+sin(x) методом простой итерации».
5. Ввел в ячейку А3 текст «Начальное значение»,в ячейку А4 текст «Начальный флаг»,в ячейку В3 значение 0,5 ,в ячейку В4 слово ИСТИНА.
6. Присвоил ячейкам В3 и В4 имя «нач_зн» и «нач».
В ячейке В6 будет выполняться проверка,равна ли истина значению ячейки «нач».Если это так,х будет установлено равным начальному значению, в противоположном случае равным ячейке В7,т.е. 0,25+синуса х.В ячейке В7 выч-исляется 0,25-синуса ячейки В6,и тем организуется циклическая ссылка.
7. В ячейку А6 ввел y=x,и в ячейку А7 y=0,25+sin(x).В ячейку В6 формулу:
=ЕСЛИ(нач;нач_зн;В7).
В ячейку В7 формулу: y=0,25+sin(B6).
8. В ячейку А9 ввел слово Погрешность.
9. В ячейку В9 ввел формулу: =В7-В6.
10. С помощью команды Формат-Ячейки(вкладка Число) преобразовал ячейку В9 в экспоненциальный формат с двумя цифрами после запятой.
11. Затем организовал вторую циклическую ссылку-для подсчета количества ите-раций.В ячейку А11 ввел текст «Количество итераций».
12. В ячейку В11 ввел формулу: =ЕСЛИ(нач;0;В12+1).
13. В ячейку В12 ввел =В11.
14. Для выполнения расчета установил табличный курсор в ячейку В4 и нажал клавишу F9(Вычислить) для запуска решения задачи.
15. Изменил значение начального флага на ЛОЖЬ,и снова нажал F9.При каждом нажатии F9 выполняется одна итерация и вычисляется следующее приближен-ное значение х.
16. Нажимал клавишу F9 до тех пор, пока значение х не достигло необходимой точности.
При автоматическом расчете:
17. Перешел на другой лист.
18. Повторил пункты с 4 по 7,только в ячейку В4 ввел значение ЛОЖЬ.
19. Выбрал команду Сервис®Параметры(вкладка Вычисления).Установил зна-чение поля Предельное число итераций равным 100,относительную погреш-ность равной 0,0000001.Включил ркжим Автоматически.
Начальный флаг ЛОЖЬ.
Начальное значение 0,5
Границы интервала [0,5;2]
Точность вычисления при ручном расчете 0,001
при автоматическом
1. Ручной расчет:
число итераций 37
корень уравнения 1,17123
2. Автоматический расчет:
число итераций 100
корень уравнения 1,17123
3. Решение уравнения графическим способом:
корень уравнения 1,17
В ходе данной курсовой работы я ознакомился с различными методами решения уравнений:
· Аналитическим методом
Но так как большинство численных методов решения уравнений являются итерационными, то я на практике использовал этот метод.
Нашел с заданной точностью корень уравнения 0,25-x+sin(x)=0 на промежутке [0,5;2] методом простой итерации.
Читайте также: