No Image

Численные методы в excel

СОДЕРЖАНИЕ
4 просмотров
10 марта 2020

В этом разделе приведены примеры решенных задач разных разделов вычислительной математики, выполненные в пакете электронных таблиц Excel.

Примеры решений по численным методам в Эксель

Задача 1. Найти стационарные точки, проверить их на экстремальность, а также найти все локальные и глобальные максимумы и минимумы.

Задача 2. Решить приближенно уравнение $2x^3+3x-9=0$

Задача 3. Уплотнить часть $[a; b]$ таблицы заданной функции с шагом $H$, пользуясь интерполяционными формулами Ньютона. Составить таблицу конечных разностей. В каждом столбце, начиная с четвертого, будет на одно число меньше, чем в предыдущем. Результаты вычислений значений функции в промежуточных точках расположить в таблице. Интерполяционные формулы Ньютона дают хороший результат в случае, когда $tin[0; 1]$. Если внутри отрезка $[a; b]$, на котором требуется уплотнить таблицу, находится узловая точка $x_i$, то на каждом из отрезков $[a; x_i]$ и $[_i; b]$ вычисления выполняются отдельно. Все задания выполнить в Excel

Задача 4. Решить систему методами Ньютона, Брауна, итераций.

Задача 5. Решить систему Ax=b методом Гаусса (схема частичного выбора). Вычислить определитель и обратную матрицу для данной матрицы на основе метода Гаусса.

Задача 6. Функция y=y(x) задана таблицей своих значений. Построить многочлены нулевой и первой степени, приближающие функцию по методу наименьших квадратов. Вычислить величину среднеквадратичного отклонения. Построить на одном чертеже точечный график функции и графики многочленов.

Задача 7. Вычислить интеграл от заданной функции f(x) на отрезке [a,b] по формулам трапеций и Симпсона при делении отрезка на 12 равных частей. Повторить вычисления при делении отрезка на 6 равных частей. Записать ответ каждого метода, сохранив только верные цифры.

по предмету: ‘’Моделирование ’’

на тему: ‘’Численные методы и их реализация в Excel’’

Проверил: к.т.н. профессор. Бабак В. Ф.

При моделировании экономических ситуаций часто приходится решать уравнение вида:

где f-заданная функция, х-неизвестная переменная.

Решение таких уравнений может быть как самостоятельной, так и частью более сложных задач. Как правило, исследователя интересует поведение решения в зависимости от параметров pk , k=`1,n

Решениями или корнями уравнения (1) называют такие значения переменной х, которые при подстановке в уравнение обращают его в тождество.

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

В большинстве же случаев приходится решать уравнение (1) численными методами, в которых процедура решения задается в виде многократного применения некоторого алгоритма. Полученное решение всегда является приближенным, хотя может быть сколь угодно близко к точному.

Рассмотрим последовательность действий для получения решения нелинейного уравнения в среде электронной таблицы.

Пусть надо решить уравнение вида:

Cформируем лист электронной таблицы, как показано на рис.1. Уравнение (2) запишем в клетку С5, начиная со знака равенства, а вместо переменной x укажем адрес клктки В5, которая содержит значение начального приближения решения.

вместо переменной x укажем адрес клетки В5. которая содержит значение начального приближения решения

Метод, применяемый в EXCEL для решения таких уравнений -модифицированный конечными разностями метод Ньютона, который позволяет не сильно заботится о начальном приближении, как этого требуют другие численные методы решения уравнений (метод хорд, дихотомии и др.) Единственно, что следует учесть — это то, что будет’ найдено решение ближайшее к выбранному начальному приближению.

Для получения решения уравнения (2) надо выполнить следующую последовательность действий:

1. Выполнить команду Сервис/Подбор параметра. (получим лист электронной таблицы, как показано на Рис. 2);

2. Заполнить диалоговое окно Подбор параметра.

2,1 Щелкнуть левой клавишей мыши в поле Установить в ячейке, после появления в нем курсора, переместить указатель мыши и щелкнуть на клетке с формулой, в нашем случае это клетка С5, абсолютный адрес которой $С$5 появится в поле рис.1

Этот адрес можно было бы набрать на клавиатуре, после появления курсора в поле. Установить в ячейке

2.2. В поле Значение ввс

В нашем случае это значение равно О.

2.3 В поле, Изменяя значение ячейки ввести адрес клетки, где задано начальное приближение решения, в нашем случае это клетка В 5 (абсолютный адрес которой $В$5 появится в поле после щелчка левой клавиши мыши на клетке В5).После выполнения пунктов 1-2 страница электронной таблицы будет выглядеть так, как показано на Рис.3.

Читайте также:  Melissa это макровирус для файлов

Правая часть решаемого уравнения не обязана быть всегда нулем равнение (2) преобразовать к виду 10*х*(х+10)/(х-9)=2. то в поле Значение следовало бы установить 2.

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

Для нашего примера Результат подбора параметра показан на Рис.4

При значении аргумента –0,187204141 функция, стоящая в левой части уравнения (2) отличается от нуля на – 0,000484158.

Достигнутая точность решения равна – 1.0Е-3

Если полученные значения следует "отразить на листе электронной таблицы, то надо щелкнуть на кнопке ОК . .если же нет то на кнопку Отмена. В первом случае найденные значения зафиксируются в клетках В5 и С5 и лист электронной таблицы будет выглядеть как на Рис.5, или как на Рис.6, если установить режим отображения результатов, предварительно сняв режим отображения формул, выполнив команду Сервис/Параметры/Вид/Формулы.

Численные методы решения уравнений хороши тем, что мoжно получить приближенное решение с заданной точностью. EXCEL име (возможность управлять выбором точности. Для этого надо выполни’ команду Сервис/Параметры/Вычисления и в соответствующих полз установить. значения относительной погрешности и количества итераш Рис.7

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

В каждом уравнении системы функции у явна выражена через х

Преобразуем систему (3) в одно уравнение вида (+)

Полученное уравнение уже можно решить с помощью Подбора параметра. так как это было описано выше.

В качестве примера рассмотрим нахождение равновесных цены и объема продаж для рынка некоторого товара.

Пусть функция спроса на товар имеет вид Q = 40/(Р+3) а функция предложения: Q = 20Р-14

Найти равновесные цену и объем , построить графики спроса и предложения.

преобразуем в одно уравнение вида 40 / (р + 3) — 20 р +14=0

Подбором параметра. описанным выше, находим равновесную цену, она равна 1,17, подставив это значение в одно из уравнений системы, получим и значение равновесного объема — 9,57. Для построения графика, иллюстрирующего ситуацию равновесия спроса и предложения на рынке, воспользуемся знанием равновесной цены и возьмем значения цен в некоторой окрестности от нее. например от 0 до 4 с шагом 0,1.

Используя все возможности мастера диаграмм, получим следующую иллюстрацию решения задачи о равновесии на рынке. Рис.8.

Найти ближайшее к начальному приближению решение следующих уравнений. Исследовать влияние начального приближения на найденное решение

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

По мнению крупнейшего экономиста нашей эпохи В.В.Леонтьева. «Дифференциальное исчисление и элементарная алгебра — два традиционных инструмента экономиста-математика заменяются . или, по крайней мере дополняются матричной алгеброй.» 4 Матричная алгебра тесно связана с линейными функциями и с линейными ограничениями в связи с чем находит себе применение в различных экономических задачах:

в эконометрике, для оценки параметров множественных линейных регрессий;

при решении задач линейного программирования;

при макроэкономическом моделировании и т.д. Особое отношение к матричной алгебре в экономике появилось после создания моделей типа «Затраты — Выпуск», где с помощью матриц технологических коэффициентов объясняется уровень производства в каждой отрасли через связь с соответствующими уровнями во всех прочих отраслях.

Электронная таблица EXCEL имеет ряд встроенных функций для работы с матрицами:

ЧИСЛЕННЫЕ МЕТОДЫ

В.В.Бурляев

под редакцией профессора В.Ф.Корнюшко

Численные методы в примерах на EXCEL.

Под редакцией проф. Корнюшко В.Ф.

Рецензент — д.т.н., профессор Бахвалов Л.А.

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

Основное внимание уделено тщательно подобранным примерам, позволяющим наиболее ярко проиллюстрировать те или иные особенности каждого метода. Все примеры выполнены на одном из самых мощных современных программных средств — табличном процессоре EXCEL, входящим в состав широко распространенного пакета MICROSOFT OFFICE.

Читайте также:  Ieetwcollectorservice что за служба

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

1. РЕШЕНИЕ НЕЛИНЕЙНОГО УРАВНЕНИЯ с одним неизвестным. 5

1.1 Отделение корней. 5

1.2 Уточнение корней: метод итераций. 6

1.3 Уточнение корней: метод Ньютона. 8

1.4. Уточнение корней: метод бисекции ( деления отрезка пополам ). 10

1.5 Уточнение коней: подпрограмма EXCEL “Подбор параметра”. 12

2. СИСТЕМЫ ЛИНЕЙНЫХ АЛГЕБРАИЧЕСКИХ УРАВНЕНИЙ. 13

2.1. Матричный метод. 13

2.2. Метод приближенных вычислений. 15

2.3. Метод Гаусса – Зайделя. 18

3. РЕШЕНИЕ СИСТЕМ НЕЛИНЕЙНЫХ УРАВНЕНИЙ. 20

3.1. Выбор начальных приближений. 20

3.2 Метод Ньютона. 21

3.3. Метод итераций. 23

4. ЧИСЛЕННЫЕ МЕТОДЫ ОДНОМЕРНОЙ ОПТИМИЗАЦИИ. 25

4.1. Метод дихотомии. 25

4.2. Метод золотого сечения. 27

4.3. Встроенная подпрограмма EXCEL “Поиск решения”. 29

5. МНОГОМЕРНЫЕ ЗАДАЧИ ОПТИМИЗАЦИИ. 30

5.1. Безусловная оптимизация: метод покоординатного спуска. 30

5.2. Безусловная оптимизация: метод наискорейшего спуска. 32

5.3. Безусловная оптимизация: подпрограмма EXCEL “Поиск решения”. 35

5.4. Условная оптимизация: метод штрафных функций. 35

5.5. Условная оптимизация: подпрограмма EXCEL “Поиск решения”. 38

5.6. Условная оптимизация: линейное программирование. 39

6. МЕТОД НАИМЕНЬШИХ КВАДРАТОВ. 43

7. ВЫЧИСЛЕНИЕ ОПРЕДЕЛЕННЫХ ИНТЕГРАЛОВ. 48

8. ЧИСЛЕННОЕ РЕШЕНИЕ ОБЫКНОВЕННОГО ДИФФЕРЕНЦИАЛЬНОГО УРАВНЕНИЯ 51

8.1. Метод Эйлера. 51

8.2. Метод Рунге-Кутта четвертого порядка. 52

8.3. Метод прогноза и коррекции: метод Адамса. 53

9. РЕШЕНИЕ СИСТЕМ ОБЫКНОВЕННЫХ ДИФФЕРЕНЦИАЛЬНЫХ УРАВНЕНИЙ. 55

9.1. Задача Коши. 55

9.2. Краевая задача: метод стрельбы. 57

9.3. Краевая задача: метод прогонки. 57

10. ЧИСЛЕННОЕ РЕШЕНИЕ УРАВНЕНИЙ С ЧАСТНЫМИ ПРОИЗВОДНЫМИ 60

1. РЕШЕНИЕ НЕЛИНЕЙНОГО УРАВНЕНИЯ с одним неизвестным.

Уравнение с одним неизвестным можно записать в каноническом виде

Решение уравнения заключается в нахождении корней, т.е. таких значений х, которые обращают уравнение в тождество. В зависимости от того, какие функции входят в уравнение, разделяют два больших класса уравнений — алгебраические и трансцендентные. Функция называется алгебраической, если для получения значения функции по данному значению х нужно выполнить арифметические операции и возведение в степень. К трансцендентным функциям относятся показательная, логарифмическая, тригонометрические прямые и обратные и т.п.

Найти точные значения корней можно лишь в исключительных случаях. Как правило, используются методы приближенного вычисления корней с заданной степенью точности Е. Это означает, что если установлено, что искомый корень лежит внутри интервала [a,b], где a — левая граница, а b — правая граница интервала, и длина интервала (b-a) x -10x = 0

Для этого надо протабулировать функцию f(Х) = exp(Х) — 10*Х, записанную по правилам EXCEL, и построить ее график при изменении Х от какого-то Хнач до Хкон с шагом dХ. Пусть эти значения сначала будут таковы: Хнач = 0, Хкон = 5, dХ = 0,5. Если в этих пределах изменения Х нам не удастся отделить ни одного корня, тогда надо будет задать новые начальное и конечное значения х и, может быть, изменить шаг.

Для построения таблицы целесообразно воспользоваться специальной подпрограммой ТАБЛИЦА. Для этого на новом рабочем листе в ячейке B1 введем текст: ОТДЕЛЕНИЕ КОРНЕЙ. Затем в ячейку А2 введем текст: x, а в смежную ей ячейку В2 — текст: f(x). Далее оставим ячейку А3 пустой, но в ячейку В3 введем формулу исследуемой функции по правилам EXCEL, а именно

Затем заполним числовой ряд изменений X в строках А4:A14 от 0 до 5 с шагом 0,5.

Выделим блок ячеек А3:B14. Теперь дадим команду меню Данные- Таблица. Результаты табулирования будут помещены в блок ячеек В4:В14. Для того чтобы сделать их более наглядными, нужно отформатировать блок В4:B14 так, чтобы отрицательные числа окрашивались в красный цвет. В этом случае легко найти два смежных значения X, для которых значения функции имеют разные знаки. Их и надо принять за концы интервала отделения корней. В нашем случае таких интервалов, как видно из таблицы два — [0;0,5] и [ 3,5;4].

Далее следует построить график нашей функции, выделив блок А4:B14 и вызвав Мастер Диаграмм. В результате получим на экране диаграмму изменения f(X), из которой видны следующие интервалы отделения корней [0;1] и [3;4].

Читайте также:  Lg h818p не включается

Если изменять теперь числовые значения х в блоке А4:A14 то значения функции в ячейках B4:B14и график будут изменяться автоматически.

1.2 Уточнение корней: метод итераций.

Для уточнения корня методом итераций должно быть задано:

1) уравнение f(X) = 0, причем f(X) должно быть задано в виде формулы,

2) числа a — левая граница и b — правая граница интервала, внутри которого лежит один корень,

3) число Е — заданная точность получения корня.

Сам метод можно разбить на два этапа:
а) переход от канонического вида записи уравнения f(X)=0 к итерирующему виду X = g(X),
б) вычислительная итерирующая процедура уточнения корня.

Перейти от канонического вида уравнения к итерирующему можно различными способами, важно лишь чтобы при этом выполнялось достаточное условие сходимости метода: çg’(X)ç 0 сходимость будет монотонной, т.е. с увеличением итераций D будет приближаться к Е монотонно (не меняя знака), в то время как при g’(X) 1 на интервале [a,b] и характер сходимости будет монотонный.

Запрограммируем метод итераций для этого примера на том же рабочем листе, где мы проводили отделение корней. В ячейку А22 внесем число, равное 0. В ячейку В22 запишем формулу =0,1*EXP(A22), а в ячейку С22 формулу =А22- В22. Таким образом 22 строка содержит данные по первой итерации. Чтобы получить в строке 23 данные по второй итерации, скопируем содержимое ячейки В22 в ячейку А23, записав в А23 формулу =В22. Далее надо скопировать формулы ячеек В22 и С22 в ячейки В23 и С23. Для получения данных всех остальных итераций надо выделить ячейки А23,В23,С23 и скопировать их содержимое в блок А24:C32. После этого следует проанализировать изменение D = Х — g(X) в столбце С, найти D 0. Достаточные условия сходимости метода заключаются в том, что первая и вторая производные исследуемой функции должны сохранять знак на интервале [a,b]. В качестве начального приближения выбирают обычно или a, или b, в зависимости от того, кто из них соответствует формуле выбора Х.

Метод Ньютона допускает простую геометрическую интерпретацию. Если через точку с координатами (Xi;f(Xi)) провести касательную к кривой f(X), то абсцисса точки пересечения этой касательной с осью 0Х и есть очередное приближение корня Хi+1.

Метод Ньютона можно рассматривать как некоторую модификацию метода итераций, дающую наилучшую итерирующую функцию g(X) на каждом шаге итерации. Проведем следующие преобразования с исходным каноническим уравнением f(X)=0. Умножим левую и правую его части на некоторое число l, отличное от нуля. Затем прибавим слева и справа по Х. Тогда будем иметь

Дифференцируя g(X), получим g’(X) = 1 + l*f’(X). Из достаточного условия сходимости метода итераций çg’(X)ç 0.

Запрограммируем метод Ньютона для этого примера на том же рабочем листе, где мы проводили отделение корней. В ячейку А42 внесем число, равное Х=0. В ячейку В42 запишем формулу =EXP(A42)-10*А42, в ячейку С42 формулу =EXP(A42)-10, а в ячейку D42 формулу =А42- В42/C42. Затем в ячейку Е42 запишем формулу =А42-D42. Таким образом 42 строка содержит данные по первой итерации.

Чтобы получить в строке 43 данные по второй итерации, скопируем содержимое ячейки D42 в ячейку А43, записав в А43 формулу =D42. Далее надо скопировать формулы ячеек В42, С42, D42, E42 в ячейки В43, С43, D43, E43. Для получения данных всех остальных итераций надо выделить ячейки в 43 строке и скопировать их содержимое в блок А44:Е47. После этого следует проанализировать изменение D в столбце E, найти D k 0, то значение А53 равно С52. В противном случае оно должно быть равно А52. В ячейке В53 наоборот: если F52 0” (разумеется без кавычек!), в поле Значение_если_истина внесем С52, а в поле Значение_если_ложь — А52. Щелкнем по кнопке Закончить. Вот и все.

То же самое надо проделать с ячейкой В53. Только Логическое выражение будет “F52

Дата добавления: 2014-12-27 ; Просмотров: 1087 ; Нарушение авторских прав? ;

Нам важно ваше мнение! Был ли полезен опубликованный материал? Да | Нет

Комментировать
4 просмотров
Комментариев нет, будьте первым кто его оставит

Это интересно
Adblock detector