Excel решение слау через поиск решения

2.7. Регрессия

Для построения используются
несколько стандартных функций листа.

TREND / ТЕНДЕНЦИЯ

Строит

y=b+m₁x₁+…+m_J x_J+e

Аппроксимирует известные значения вектора откликов
known_y’s для заданных значений матрицы предикторов
known_x’s и возвращает значения y,
для заданного массива new_x’s.  

Синтаксис 

TREND(known_y’s
)

Примечания 

• Вектор
  known_y’s должен занимать один столбец,
  тогда каждый столбец матрицы массива known_x’s
  интерпретируется как отдельная переменная;

• Если
  аргумент known_x’sопущен, то предполагается, что это вектор чисел {1;2;3;…}
  такого же размера, как и known_y’s;

• Матрица
  новых значений new_x’sдолжна иметь столько же столбцов
  (переменных), как и матрица known_x’s;

• Если
  аргумент new_x’sопущен, то предполагается, что он совпадает с
  массивом known_x’s.
  Результат является вектором, в котором число строк равно
  числу строк в массиве new_x’s.

Пример 
 

Рис.34 Функция TREND

Функция TRENDявляется функцией
массива и ее ввод должен завершаться нажатием комбинации
CTRL+SHIFT+ENTER. 

LINEST /
ЛИНЕЙН

Дополняет функцию TREND и выводит некоторые
статистические значения, связанные с регрессией  

y=b+m₁x₁+…+m_J x_J+e

Синтаксис 

LINEST(known_y’s
)

Рис. 35 Таблица вывода функция LINEST

m_J, …,
m₂, m₁
и b – оценки регрессионных
коэффициентов;

s_J, …,
s₂, s₁
и s_b
– стандартные ошибки для оценок регрессионных коэффициентов;

R2 –
коэффициент детерминации;

s_y –
стандартная ошибка оценки y;

F – F-статистика;

DoF – число степеней
свободы;

SS_reg –
регрессионная сумма квадратов;

SS_res–
остаточная сумма квадратов.

Примечания 

• LINEST – это
  очень плохо сконструированная функция, очень неудобная в
  практическом применении;

• Примечания,
  представленные в описании функции полностью применимы к
  функции LINEST.

Пример 
 

Рис.36 Функция LINEST

Функция LINEST является функцией массива и ее ввод должен
завершаться нажатием комбинации CTRL+SHIFT+ENTER. 

Формулы массива

Построение матрицы средствами Excel в большинстве случаев требует использование формулы массива. Основное их отличие – результатом становится не одно значение, а массив данных (диапазон чисел).

Порядок применения формулы массива:

1. Выделить диапазон, где должен появиться результат действия формулы.
2. Ввести формулу (как и положено, со знака «=»).
3. Нажать сочетание кнопок Ctrl + Shift + Ввод.

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

Чтобы изменить или удалить формулу массива, нужно выделить весь диапазон и выполнить соответствующие действия. Для введения изменений применяется та же комбинация (Ctrl + Shift + Enter). Часть массива изменить невозможно.

решение уравнений в excel

Решение нелинейных
уравнений и систем»

Цель работы:
Изучение возможностей пакета Ms Excel 2007
при решении нелинейных уравнений и
систем. Приобретение навыков решения
нелинейных уравнений и систем средствами
пакета.

Задание1.Найти корни
полинома x3
— 0,01x2
— 0,7044x + 0,139104 = 0.

Для начала решим
уравнение графически. Известно, что
графическим решением уравнения f(x)=0
является точка пересечения графика
функции f(x) с осью абсцисс, т.е. такое
значение x, при котором функция обращается
в ноль.

Проведем табулирование
нашего полинома на интервале от -1 до 1
с шагом 0,2. Результаты вычислений
приведены на ри., где в ячейку В2 была
введена формула: = A2^3 — 0,01*A2^2 — 0,7044*A2 +
0,139104.

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

определены интервалы, на
которых находятся корни данного полинома:
, и .

Теперь можно найти
корни полинома методом последовательных
приближений с помощью команды
Данные→Работа с данными→Анализ
«Что-Если» →Подбор параметра.

После ввода
начальных приближений и значений функции
можно обратиться к команде Данные→Работа
с данными→Анализ «Что-Если» →Подбор
параметра
и заполнить диалоговое окно следующим
образом.

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

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

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

Два оставшихся
корня находим аналогично. Результаты
вычислений будут помещены в ячейки А15
и А16.

Задание 2. Решить
уравнение ex—
(2x — 1)2
= 0.

Проведем локализацию
корней нелинейного уравнения.

Для этого представим
его в виде f(x) = g(x) , т.е. ex
= (2x — 1)2
или f(x) = ex,
g(x) = (2x — 1)2,
и решим графически.

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

Построим графики
f(x) и g(x). Для этого в диапазон А3:А18 введем
значения аргумента. В ячейку В3 введем
формулу для вычисления значений функции
f(x): = EXP(A3), а в С3 для вычисления g(x): =
(2*A3-1)^2.

Результаты
вычислений и построение графиков f(x) и
g(x):

На графике видно,
что линии f(x)
и g(x)
пересекаются дважды, т.е. данное уравнение
имеет два решения. Одно из них тривиальное
и может быть вычислено точно:

Теперь можно найти
корень уравнения на отрезке методом
последовательных приближений.

• Введём начальное
  приближение в ячейку Н17 = 1,5, и само
  уравнение, со ссылкой на начальное
  приближение, в ячейку I17
  = EXP(H17)
  — (2*H17-1)^2.
• Далее воспользуемся
  командой
  Данные→Работа с данными→Анализ
  «Что-Если» →Подбор параметра.
• и заполним
  диалоговое окно Подбор
  параметра.

Результат поиска
решения будет выведен в ячейку Н17.

Задание 3.Решить
систему уравнений:

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

Для первого
уравнения системы имеем:

Выясним ОДЗ
полученной функции:

Второе уравнение
данной системы описывает окружность.

Фрагмент рабочего
листа MS Excel с формулами, которые необходимо
ввести в ячейки для построения линий,
описанных уравнениями системы. Точки
пересечения линий изображенных являются
графическим решением системы нелинейных
уравнений.

Не трудно заметить,
что заданная система имеет два решения.
Поэтому процедуру поиска решений системы
необходимо выполнить дважды, предварительно
определив интервал изоляции корней по
осям Оx и Oy .

В нашем случае первый корень
лежит в интервалах (-0.5;0)x
и (0.5;1)y,
а второй — (0;0.5)x
и (-0.5;-1)y.
Далее поступим следующим образом.

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

Теперь дважды
воспользуемся командой Данные→Анализ→Поиск
решений, заполняя появляющиеся диалоговые
окна.

Сравнив полученное
решение системы с графическим, убеждаемся,
что система решена верно.

Задания для
самостоятельного решения

Задание
1.
Найти корни полинома

Задание 2.
Найдите решение нелинейного уравнения.

Задание
3.
Найдите решение системы нелинейных
уравнений.

Нахождение определителя матрицы

Это одно единственное число, которое находится для квадратной матрицы. Используемая функция – МОПРЕД.

Ставим курсор в любой ячейке открытого листа. Вводим формулу: =МОПРЕД(A1:D4).

Таким образом, мы произвели действия с матрицами с помощью встроенных возможностей Excel.

Вычислить значения корней сформированной системы уравнений двумя методами: обратной матрицы и методом Крамера.

Введем данные значения в ячейки А2:С4 – матрица А и ячейки D2:D4 – матрица В.

Решение системы уравнений методом обратной матрицы

Найдем матрицу, обратную матрице А. Для этого в ячейку А9 введем формулу =МОБР(A2:C4). После этого выделим диапазон А9:С11, начиная с ячейки, содержащей формулу. Нажмем клавишу F2, а затем нажмем клавиши CTRL+SHIFT+ENTER. Формула вставится как формула массива. =МОБР(A2:C4). Найдем произведение матриц A-1 * b. В ячейки F9:F11 введем формулу: =МУМНОЖ(A9:C11;D2:D4) как формулу массива. Получим в ячейках F9:F11

корни уравнения:

Решение системы уравнений методом Крамера

Решим систему методом Крамера, для этого найдем определитель матрицы. Найдем определители матриц, полученных заменой одного столбца на столбец b.

В ячейку В16 введем формулу =МОПРЕД(D15:F17),

В ячейку В17 введем формулу =МОПРЕД(D19:F21).

В ячейку В18 введем формулу =МОПРЕД(D23:F25).

Найдем корни уравнения, для этого в ячейку В21 введем: =B16/$B$15, в ячейку В22 введем: = =B17/$B$15, в ячейку В23 введем: ==B18/$B$15.

Получим корни уравнения:

Способ 1

Рассмотрим матрицу А
размерностью 3х4
. Умножим эту матрицу на число k
. При умножении матрицы на число получается матрица такой же размерности, что и исходная, при этом каждый элемент матрицы А
умножается на число k
.

Введем элементы матрицы в диапазон В3:Е5
, а число k
— в ячейку Н4
. В диапазоне К3:
N
5
вычислим матрицу В
, полученную при умножении матрицы А
на число k
: В=А*
k
. Для этого введем формулу =B3*$H$4
в ячейку K
3
, где В3
— элемент а 11
матрицы А
.

Примечание:

адрес ячейки H
4
вводим как абсолютную ссылку, чтобы при копировании формулы ссылка не менялась.

С помощью маркера автозаполнения копируем формулу ячейки К3
В
.

Таким образом, мы умножили матрицу А
в Excel и получим матрицу В
.

Для деления матрицы А
на число k в ячейку K
3
введем формулу =B3/$H$4
В
.

Способ 2

Этот способ отличается тем, что результат умножения/деления матрицы на число сам является массивом. В этом случае нельзя удалить элемент массива.

Для деления матрицы на число этим способом выделяем диапазон, в котором будет вычислен результат, вводим знак «=», выделяем диапазон, содержащий исходную матрицу А, нажимаем на клавиатуре знак умножить (*) и выделяем ячейку с числом k
Ctrl+
Shift+
Enter

Для выполнения деления в данном примере в диапазон вводим формулу =B3:E5/H4, т.е. знак «*» меняем на «/».

· Аналитическим методом

·
Графическим методом

·
Численным методом

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

Нашел с заданной точностью корень уравнения
0,25-x+sin(x)=0 на промежутке методом простой итерации.

Приложение.

1.Ручной расчет.

2.Автоматический расчет.

3.Решение уравнения 0.25-x-sin(x)=0
графическим способом.

Библиографический список.

1.
Волков Е.А. «Числовые методы».

2.
Самарский А.А. «Введение в
числовые методы».

3.
Игалеткин И.И. «Числовые методы».

Пример 3.1.

Найти решение системы линейных алгебраических уравнений (3.1) методом Якоби.

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

Расчетная схема метода Якоби приведена на рис (3.1).

Приведите систему(3.1). к нормальному виду:

, (3.2)

или в матричной форме

, (3.3)

Для определения количества итераций, необходимое для достижения заданной точности e,
и приближенного решения системы полезно в столбце Н
установить Условный формат
. Результат такого форматирования виден на рис.3.1. Ячейки столбца Н,
значения которых удовлетворяют условию (3.4) тонированы.

(3.4)

Анализируя результаты, принимаем за приближенное решение исходной системы с заданной точностью e=0,1 четвертую итерацию,

т.е. х 1
=10216; х 2
= 2,0225, х 3
= 0,9912

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

Проанализируйте сходимость итерационного процесса, построив графики изменения каждой компоненты решения СЛАУ в зависимости от номера итерации.

Для этого выделите блок ячеек А10:D20
и, используя Мастер диаграмм
, постройте графики, отражающие сходимость итерационного процесса, рис.3.2.

Аналогично решается система линейных алгебраических уравнений методом Зейделя.

Лабораторная работа №4

Тема. Численные методы решения линейных обыкновенных дифференциальных уравнений с краевыми условиями. Метод конечных разностей

Задание.
Решить краевую задачу методом конечных разностей, построив два приближения (две итерации) с шагом h и с шагом h/2.

Проанализировать полученные результаты. Варианты заданий приведены в приложении 4.

Порядок выполнения работы

1. Постройте вручную
конечноразностную аппроксимацию краевой задачи (конечноразностную СЛАУ) с шагом h

, заданным вариантом.

2. Используя метод конечных разностей, сформируйте в Excel
систему линейных алгебраических конечно-разностных уравнений для шага h

разбивки отрезка . Запишите эту СЛАУ на рабочем листе книги Excel
. Расчетная схема приведена на рис.4.1.

3. Полученную СЛАУ решите методом прогонки.

4. Проверьте правильность решения СЛАУ с помощью надстройки Excel Поиск решения
.

5. Уменьшите шаг сетки в 2 раза и еще раз решите задачу. Результаты представьте в графическом виде.

6. Сравните полученные результаты. Сделайте вывод о необходимости продолжения или о прекращении счета.

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

Пример 4.1.
Методом конечных разностей найти решение краевой задачи ,
y(1)=1, y ’ (2)=0,5
на отрезке xÎ
с шагом h=0,2 и с шагом h=0,1. Сравнить полученные результаты и сделать вывод о необходимости продолжения или о прекращении счета.

Расчетная схема для шага h=0,2 приведена на рис.4.1.

Полученное решение (сеточную функцию) Y

{1.000, 1.245, 1.474, 1.673, 1.829, 1.930}, Х

{1; 1,2; 1,4; 1,6; 1,8;2} в столбце L и B можно принять за первую итерацию (первое приближение) исходной задачи.

Для нахождения второй итерации
сделайте сетку вдвое гуще (n=10, шаг h=0,1) и повторите приведенный выше алгоритм.

Это можно проделать на том же или на другом листе книги Excel
. Решение (второе приближение) приведено на рис.4.2.

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

Порядок построения графиков приближенных решений краевой задачи

1. Постройте график решения задачи для разностной сетки с шагом h=0,2 (n=5).

2.
Активизируйте уже построенный график и выберите команду меню Диаграмма\Добавить данные

3. В окне Новые данные
укажите данные x i , y i
для разностной сетки с шагом h/2 (n=10).

4. В окне Специальная вставка
установите флажки в полях:

Ø новые ряды,

Как видно из приведенных данных, два приближенных решения краевой задачи (две сеточные функции) отличаются друг от друга не более, чем на 5%. Поэтому за приближенное решение исходной задачи принимаем вторую итерацию, т.е.

Y
{1, 1.124, 1.246, 1.364, 1.478, 1.584, 1.683, 1.772, 1.849, 1.914, 1.964}

Лабораторная работа №5

Метод Гаусса

Процесс решения по методу Гаусса состоит из двух этапов.

1. Прямой ход:
система приводится к ступенчатому (в частности, треугольному) виду.

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

2. Обратный ход:
идет последовательное определение неизвестных из этой ступенчатой системы.

Пример.
Установить совместность и решить системуРешение.
Прямой ход:
Выпишем расширенную матрицу системы и поменяем местами первую и вторую строки для того, чтобы элемент равнялся единице (так удобнее производить преобразования матрицы)..

Имеем Ранги матрицы системы и ее расширенной матрицы совпали с числом неизвестных. Согласно теореме Кронекера-Капелли система уравнений совместна и решение ее единственно.Обратный ход:
Выпишем систему уравнений, расширенную матрицу которой мы получили в результате преобразований:
Итак, имеем .
Далее, подставляя в третье уравнение, найдем .
Подставляя и во второе уравнение, получим .
Подставляя в первое уравнение найденные получим .
Таким образом, имеем решение системы .

Решение СЛАУ в MS EXCEL

С системой линейных алгебраических уравнений (СЛАУ) часто приходится сталкиваться не только в курсе математики. Их решение пригодится в других науках, например, физике или химии.

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

Мы рассмотрим решение одной и той же простой системы уравнений первыми двумя способами, чтобы сравнить результаты. Если при решении разными способами ответы будут совпадать, значит СЛАУ решена верно.

Обучение. Лекции по численным методам. Методы решения систем линейных алгебраических уравнений
Из определения транспонированной матрицы следует, что если исходная матрица А имеет размер m × n , то транспонированная матрицаА Т имеет размер n × m .

прямыхитерационных

3 .1. Программирование. Язык VBA

Иногда стандартных возможностей Excel не хватает и приходится
добавлять свои собственные подпрограммы. Для этой цели служит специальный язык
программирования – Microsoft Visual Basic for Applications (VBA)
. С его помощью
можно создавать макросы
– наборы команд, выполняющих определенную
последовательность действий, и функции
– программы для специальных вычислений на
листе. Макросы – это способ автоматизации стандартных процедур. Однажды создав
макрос, его можно использовать для повтора рутинных действий. Обратиться к
макросу можно через меню Tools-Macro-Macros
. Иногда
удобно бывает приписать макрос к новой кнопке на панели инструментов или на
листе.

Функции, созданные пользователем, вызываются также как и
стандартные, встроенные функции – через Formula Bar
.

Для того, чтобы макросы и пользовательские функции были
доступны для применения, нужно установить соответствующий уровень
безопасности через меню Tools-Macro-Security

(Excel 2003)

Рис.39
Выбор уровня безопасности в Excel 2003

В Excel 2007 установка уровня безопасности происходит
через Office Button-
Excel
Options-
Trust Center.

Рис.40
Выбор уровня безопасности в Excel 2007

Если выбран уровень Medium

(2003) или Disable all macros with notification

(2007), то при каждом входе в Excel система будет запрашивать разрешение
на использование макросов. Мы рекомендуем установить уровни так, как
показано на Рис. 39
или Рис. 40
, но не пренебрегать надежным антивирусом
для проверки посторонних файлов Excel.

При начальной установке Excel 2007 возможности работы с
VBA сильно ограничены. Чтобы восстановить их нужно пройти по цепочке
Office Button– Excel Options–Popular
и
включить опцию Show Developer Tab in the Ribbon
.