Что такое начальное приближение в экселе
Рис. 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 курсовик был в институте, вот время помню кучу потерял а тут все в одном флаконе:)
Все равно инфа пригодится, даже очень!
Тамара, если Вы строите график на основе моих данных, откройте файл Excel; если Вы используете собственные данные, пришлите мне на mail Ваш файл, попробую помочь))
Спасибо заранее за беспокойство, вот такое уравнение у^3-20у^2-158у-420=0, если не трудно объясните пожалуйста как вы определяте предел в каких знчениях надо считать корни.
Microsoft Office Excel может здорово помогать студентам и магистрантам в решении различных задач из высшей математики. Не многие пользователи знают, что базовые математические методы поиска неизвестных значений в системе уравнений реализованы в редакторе. Сегодня рассмотрим, как происходит решение уравнений в excel.
Первый метод
Суть этого способа заключается в использовании специального инструмента программы – подбор параметра. Найти его можно во вкладке Данные на Панели управления в выпадающем списке кнопки Анализ «что-если».
1. Зададимся простым квадратичным уравнением и найдем решение при х=0.
2. Переходите к инструменту и заполняете все необходимые поля
3. После проведения вычислений программа выдаст результат в ячейке с иксом.
4. Подставив полученное значение в исходное уравнение можно проверить правильность решения.
Второй метод
Используем графическое решение этого же уравнения. Суть заключается в том, что создается массив переменных и массив значений, полученных при решении выражения. Основываясь на этих данных, строится график. Место пересечения кривой с горизонтальной осью и будет неизвестной переменной.
1. Создаете два диапазона.
На заметку! Смена знака результата говорит о том, что решение находится в промежутке между этими двумя переменными.
2. Переходите во вкладку Вставка и выбираете обычный график.
3. Выбираете данные из столбца f (x), а в качестве подписи горизонтальной оси – значения иксов.
Важно! В настройках оси поставьте положение по делениям.
4. Теперь на графике четко видно, что решение находится между семеркой и восьмеркой ближе к семи. Чтобы узнать более точное значение, необходимо изменять масштаб оси и уточнять цифры в исходных массивах.
Такая исследовательская методика в первом приближении является достаточно грубой, однако позволяет увидеть поведение кривой при изменении неизвестных.
Третий метод
Решение систем уравнений можно проводить матричным методом. Для этого в редакторе есть отдельная функция МОБР. Суть заключается в том, что создаются два диапазона: в один выписываются аргументы при неизвестных, а во второй – значения в правой стороне выражения. Массив аргументов трансформируется в обратную матрицу, которая потом умножается на цифры после знака равно. Рассмотрим подробнее.
1. Записываете произвольную систему уравнений.
2. Отдельно выписываете аргументы при неизвестных в каждую ячейку. Если нет какого-то из иксов – ставите ноль. Аналогично поступаете с цифрами после знака равно.
3. Выделяете в свободной зоне диапазон ячеек равный размеру матрицы. В строке формул пишете МОБР и выбираете массив аргументов. Чтобы функция сработала корректно нажимаете одновременно Ctrl+Shift+Enter.
4. Теперь находите решение при помощи функции МУМНОЖ. Также предварительно выделяете диапазон размером с матрицу результатов и нажимаете уже известное сочетание клавиш.
Четвертый метод
Методом Гаусса можно решить практически любую систему уравнений. Суть в том, чтобы пошагово отнять одно уравнение из другого умножив их на отношение первых коэффициентов. Это прямая последовательность. Для полного решения необходимо еще провести обратное вычисление до тех пор, пока диагональ матрицы не станет единичной, а остальные элементы – нулевыми. Полученные значения в последнем столбце и являются искомыми неизвестными. Рассмотрим на примере.
Важно! Если первый аргумент является нулевым, то необходимо поменять строки местами.
1. Зададимся произвольной системой уравнений и выпишем все коэффициенты в отдельный массив.
2. Копируете первую строку в другое место, а ниже записываете формулу следующего вида: =C67:F67-$C$66:$F$66*(C67/$C$66).
Поскольку работа идет с массивами, нажимайте Ctrl+Shift+Enter, вместо Enter.
3. Маркером автозаполнения копируете формулу в нижнюю строку.
4. Выделяете две первые строчки нового массива и копируете их в другое место, вставив только значения.
5. Повторяете операцию для третьей строки, используя формулу
=C73:F73-$C$72:$F$72*(D73/$D$72). На этом прямая последовательность решения закончена.
6. Теперь необходимо пройти систему в обратном порядке. Используйте формулу для третьей строчки следующего вида =(C78:F78)/E78
7. Для следующей строки используйте формулу =(C77:F77-C84:F84*E77)/D77
8. В конце записываете вот такое выражение =(C76:F76-C83:F83*D76-C84:F84*E76)/C76
9. При получении матрицы с единичной диагональю, правая часть дает искомые неизвестные. После подстановки полученных цифр в любое из уравнений значения по обе стороны от знака равно являются идентичными, что говорит о правильном решении.
Метод Гаусса является одним из самых трудоемких среди прочих вариантов, однако позволяет пошагово просмотреть процесс поиска неизвестных.
Как видите, существует несколько методов решения уравнений в редакторе. Однако каждый из них требует определенных знаний в математике и четкого понимания последовательности действий. Однако для упрощения можно воспользоваться онлайн калькулятором, в который заложен определенный метод решения системы уравнений. Более продвинутые сайты предоставляют несколько способов поиска неизвестных.
В этой главе я продолжу рассмотрение задач, которые входят в сферу традиционного образования прикладных математиков и, обычно, даются при изучении программирования. Возможно, задачи этой главы потребуют чуть большей математической подготовки. Основное внимание я буду уделять здесь решению этих задач программным путем, даже в тех случаях, когда их решение может быть получено с использованием встроенных стандартных функций. Но прежде, чем перейти к рассмотрению отдельных задач давайте рассмотрим несколько вопросов, проясняющих семантику вычислений в Excel . Я хочу рассмотреть некоторые особенности пользовательских функций - функций, написанных на VBA и вызываемых в формулах рабочего листа
Функции с побочным эффектом и неявная передача данных
Возможность написать функцию с побочным эффектом или неявной передачей данных является одной из основных причин вычисления всех пользовательских функций при пересчете электронной таблицы. Давайте приведем примеры, проясняющие ситуацию. С этой целью я написал три функции:
- ПравильнаяФункция( X As Variant) As Variant . Это пример хорошей, правильно построенной функции. Через параметр X ей передается значение некоторой ячейки рабочего листа (объект Range ). В качестве результата она возвращает значение функции,- в нашем примере результат является копией входного параметра X .
- ПобочныйЭффект(X As Variant, Y As Variant) As Variant . В данной функции помимо вычисления результата изменяется и значение параметра Y . Поскольку по умолчанию параметр передается по ссылке ( By Ref ), то это должно было бы привести к побочному эффекту и изменить содержимое ячейки рабочего листа, переданной в качестве параметра Y . Мы увидим, что этого, однако, не происходит.
- НеявнаяПередача(X As Variant) As Variant В данной функции результат зависит не только от входного параметра X , но и от значения другой, неявно используемой ячейки рабочего листа.
Вот как выглядят описания наших функций:
Вот как выглядит рабочий лист Excel , на котором вызываются эти функции:
Рис. 2.1. Побочный эффект и неявная передача данных
Анализируя полученные результаты, обратим внимание на следующие моменты:
Циклические вычисления
Укажем, особенности семантики циклических вычислений:
- Формулы, связанные циклическими ссылками, вычисляются многократно.
- Запись формул на листе определяет порядок их вычисления. Формулы вычисляются сверху вниз, слева направо.
- Число повторений цикла определяется параметрами, заданными на вкладке Вычисления. Цикл заканчивается при достижении максимального числа итераций или, когда изменения значений во всех ячейках не превосходят заданной точности.
В каких же ситуациях требуется прибегать к циклическим вычислениям? Это, возможно, следует делать, когда речь идет о реализации итерационного процесса, вычислениях по рекуррентным соотношениям. У нас уже были примеры реализации итерационных процессов, например, вычисление суммы ряда, задающего экспоненту, в которых не применялись циклические ссылки. Платой за это было использование дополнительных ячеек таблицы Excel . Правда, появлялись и новые возможности, - возможность построить график , проанализировать процесс сходимости и т.д. Тем не менее, программисту, привыкшему к традиционным языкам, и привыкшему "с детства" экономить на переменных, может показаться странным предложенное решение задачи о нахождении корня уравнения, где на экран выводятся результаты всех приближений. В Excel экономия ячеек не главная задача. Тем не менее, при реализации итерационных процессов можно, конечно, и в Excel иметь одну единственную ячейку X , значение которой изменяется, начиная от начального приближения до искомого результата. Это в большей степени соответствует понятию переменной в языках программирования.
Циклические вычисления и нахождение корней уравнения
Покажем, как можно использовать циклические вычисления на примере задачи нахождения корня уравнения методом Ньютона. Для простоты я начну с квадратного уравнения, а позже рассмотрю и более "серьезные" уравнения. Итак, рассмотрим квадратное уравнение: X 2 -5X+6 =0 . Найти корень этого (и любого другого уравнения) можно, используя всего одну единственную ячейку Excel . Для этого достаточно включить режим циклических вычислений и ввести в произвольную ячейку с именем, скажем X , рекуррентную формулу, задающую вычисления по Ньютону:
где F и F1 задают соответственно выражения, вычисляющие функцию и производную. Для нашего квадратного уравнения после ввода формулы в ней появится значение 2 , соответствующее одному из корней уравнения. А как получить второй корень? Обычно, это можно сделать путем изменения начального приближения. В нашем случае начальное приближение не задавалось, итерационный процесс вычислений начинался со значения, хранимого в ячейке X по умолчанию и равного нулю. Как же задать начальное приближение в циклических вычислениях? Возникшая проблема не связана с данной конкретной задачей. Она возникает всегда в циклических вычислениях, - до начала цикла надо задать начальные установки. В рекуррентных соотношениях всегда есть некоторый начальный отрезок . Решать задачу задания начальных установок в каждом случае можно по -разному. Я продемонстрирую один прием, основанный на использовании функции ЕСЛИ . Вот как выглядит "настоящее" решение этой задачи, использующее 4 ячейки, две из которых нужны по существу дела, а две используются для повышения наглядности процесса вычислений:
Вы можете продлить ряд значений, который описывается простым линейным или экспоненциальным приближением, используя маркер заполнения.
Ниже приведены инструкции по отображению и распознаванию трендов, а также по составлению прогноза.
Прогнозирование трендов на базе имеющихся данных
Создание линейного приближения
В линейных последовательностях шаг (разница между начальным и следующим значением последовательности) добавляется к начальному и к каждому последующему значению.
Выделите не менее двух ячеек, содержащих начальные значения для тренда.
Чтобы повысить точность значений последовательности, укажите дополнительные начальные значения.
Перетащите маркер заполнения в сторону увеличения или уменьшения значений.
Например, если вы выбрали ячейки C1:E1, содержащие начальные значения 3, 5 и 8, то при перетаскивании маркера заполнения вправо значения будут возрастать, а влево — убывать.
Совет: Чтобы вручную настроить создаваемые последовательности, в меню Правка выберите пункт Заполнить и команду Ряд.
Создание экспоненциального приближения
В экспоненциальных последовательностях начальное значение умножается на шаг для получения следующего значения. Получившийся результат и каждый последующий результат умножаются на шаг.
Выделите не менее двух ячеек, содержащих начальные значения для тренда.
Чтобы повысить точность значений последовательности, укажите дополнительные начальные значения.
Удерживая нажатой клавишу CONTROL, перетащите маркер заполнения в нужном направлении, чтобы заполнить ячейки возрастающими или убывающими значениями.
Например, если вы выбрали ячейки C1:E1, содержащие начальные значения 3, 5 и 8, то при перетаскивании маркера заполнения вправо значения будут возрастать, а влево — убывать.
Отпустите клавишу CONTROL и кнопку мыши, а затем в контекстном меню выберите команду Экспоненциальное приближение.
Excel автоматически рассчитывает экспоненциальное приближение и продолжает ряд, заполняя значениями выделенные ячейки.
Совет: Чтобы вручную настроить создаваемые последовательности, в меню Правка выберите пункт Заполнить и команду Ряд.
Отображение ряда на диаграмме с помощью линии тренда
С помощью линии тренда и скользящего среднего на двумерных диаграммах можно отобразить тренды и аналитически решить задачу прогнозирования (выполнить регрессионный анализ). Использование скользящего среднего позволяет сгладить колебания данных и более наглядно показать тенденцию. С помощью регрессионного анализа можно получить представление о зависимости между переменными и продлить линию тренда на диаграмме за пределы фактических данных, чтобы предсказать будущие значения.
На диаграмме выберите ряд данных, для которого требуется добавить линию тренда или скользящее среднее.
На вкладке Конструктор нажмите кнопку Добавить элемент диаграммы и выберите пункт Линия тренда.
Примечание: Линии тренда можно добавлять только на диаграммы без накопления, плоские диаграммы, диаграммы с областями, линейчатые диаграммы, гистограммы, графики, биржевые, точечные и пузырьковые диаграммы.
Выберите нужный тип линии тренда или скользящего среднего.
Примечание: При добавлении скользящего среднего на точечная диаграмма скользящие средние значения основаны на порядке, за исключением значений X, относящегося к диаграмме. Чтобы получить нужный результат, перед добавлением скользящего среднего может потребоваться отсортировать значения X.
С помощью линии тренда и скользящего среднего на двумерных диаграммах можно отобразить тренды и аналитически решить задачу прогнозирования (выполнить регрессионный анализ). Использование скользящего среднего позволяет сгладить колебания данных и более наглядно показать тенденцию. С помощью регрессионного анализа можно получить представление о зависимости между переменными и продлить линию тренда на диаграмме за пределы фактических данных, чтобы предсказать будущие значения.
На диаграмме выберите ряд данных, для которого требуется добавить линию тренда или скользящее среднее.
В меню Диаграмма выберите команду Добавить линию тренда, а затем — пункт Тип.
Примечание: Линии тренда можно добавлять только на диаграммы без накопления, плоские диаграммы, диаграммы с областями, линейчатые диаграммы, гистограммы, графики, биржевые, точечные и пузырьковые диаграммы.
Выберите нужный тип линии тренда или скользящего среднего.
Выполните одно из указанных ниже действий.
Необходимые действия
Полиномиальная
В поле Степень укажите наибольшую степень для независимой переменной.
Скользящее среднее
В поле Период укажите число периодов, которые нужно использовать для расчета скользящего среднего.
Примечание: При добавлении скользящего среднего на точечная диаграмма скользящие средние значения основаны на порядке, за исключением значений X, относящегося к диаграмме. Чтобы получить нужный результат, перед добавлением скользящего среднего может потребоваться отсортировать значения X.
Читайте также: