Метод аппроксимации в Microsoft Excel
Главный инструмент, с помощью которого проводится сглаживания в Excel – это построение линии тренда. Суть состоит в том, что на основе уже имеющихся показателей достраивается график функции на будущие периоды. Основное предназначение линии тренда, как не трудно догадаться, это составление прогнозов или выявление общей тенденции.
Но она может быть построена с применением одного из пяти видов аппроксимации:
Рассмотрим каждый из вариантов более подробно в отдельности.
Аппроксимация в Excel: 5 простых способов
Примечание : Если размеры диапазонов не совпадают, то надстройка выведет соответствующее предупреждение. Делаем вывод, что программа имеет защиту «от дурака», что, безусловно, является большим плюсом надстройки.
Метод Ньютона (метод касательных).
Суть метода состоит в том, что на -й итерации в точке строится касательная к кривой и ищется точка пересечения касательной с осью абсцисс (рис. 1.6). Если задан интервал изоляции корня , то за начальное приближение принимается тот конец отрезка, на котором
Уравнение касательной, проведенной к кривой в точке с координатами и , имеет вид:
За следующее приближение корня примем абсциссу точки пересечения касательной с ocью OX. Из (1.2) при , получим
Аналогично могут быть найдены и следующие приближения как точки пересечения с осью абсцисс касательных, проведенных в точках , и т.д. Формула для -го приближения имеет вид:
Для завершения итерационного процесса можно использовать условия или .
Объем вычислений в методе Ньютона больше, чем в других методах, поскольку приходится находить значение не только функции , но и ее производной. Однако скорость сходимости здесь значительно выше.
Пример 1.2. Решить уравнение на отрезке методом Ньютона c точностью .
Решение. Определим производные заданной функции : ; . Проверим выполнение условия сходимости на концах заданного интервала: — не выполняется, — выполняется. За начальное приближение корня можно принять .
Так как , итерационный процесс заканчивается. Таким образом, приближенным решением данного уравнения является .
На рис. 1.7 приведена программа решения данного уравнения методом Ньютона. В качестве исходных данных вводятся начальное приближение и точность вычисления.
Пример 1.3. Решить уравнение на отрезке методом Ньютона c точностью с помощью программы Excel.
1) Ввести в ячейки A1:D1 заголовки столбцов.
2) В ячейку A2 – значение начального приближения
3) В ячейку B3 – формулу функции =A2^3+A2-1
4) В ячейку C3 – формулу производной функции =3*A2^2+1
5) В ячейку A3 – формулу первого приближения =A2-B3/C3
6) В ячейку D3 – погрешность =ABS(A3-A2)
7) Выделить ячейки A3:D3 и скопировать формулы в соседние ячейки расположенных ниже строк A4:D4, A5:D5, и т.д. при помощи маркера заполнения. Каждая новая строка содержит результаты очередного приближения.
В столбце A найти значение корня, соответствующее заданной точности.
Приближенное решение данного уравнения содержится в ячейке A6 (погрешность в ячейке D6).
| A | B | C | D |
| x | F(x) | F'(x) | погрешность |
| 1,00000 | |||
| 0,75000 | 1,00000 | 4,00000 | 0,25000 |
| 0,68605 | 0,17188 | 2,68750 | 0,06395 |
| 0,68234 | 0,00894 | 2,41198 | 0,00371 |
| 0,68233 | 0,00003 | 2,39676 | 0,00001 |
| Рис. 1.8. Решение уравнения методом Ньютона с помощью программы Excel. |
Для использования этого метода исходное нелинейное уравнение необходимо привести к виду .
В качестве можно принять функцию ,где M ‑ неизвестная постоянная величина, которая определяется из условия сходимости метода простой итерации . При этом для определения M условие сходимости записывается в следующем виде:
Если известно начальное приближение корня , подставляя это значение в правую часть уравнения , получаем новое приближение .
Далее подставляя каждый раз новое значение корня в уравнение , получаем последовательность значений:
, ,. , k = 1,2. n.
Итерационный процесс прекращается, если результаты двух последовательных итераций близки, т.е. .
| а) | б) |
| Рис. 1.9. Геометрическая интерпретация метода простой итерации. |
Пример 1.4. Решить уравнение на отрезке методом простой итерации c точностью .
Решение. Из условия сходимости (1.5) , при определяем .Пусть .
Подставляя каждый раз новое значение корня в уравнение
Теперь и приближенным решением данного уравнения c точностью является .
На рис.1.10 приведена программа решения данного уравнения методом простой итерации. В качестве исходных данных вводятся начальное приближение, точность вычисления и значение постоянной М.
| Исходные данные | Результаты | |||
| A | B | C | D | E |
| x0 | e | M | x | F(x) |
| 0,001 | 0,683335 | 0,002416 |
Пример 1.4. Решить уравнение на отрезке методом простой итерации c точностью с помощью программы Excel.
1) Ввести в ячейки A1:D1 заголовки столбцов.
2) В ячейку A2 – значение начального приближения
3) В ячейку B3 – формулу функции =A2^3+A2-1
4) В ячейку C2 – значение M 5
5) В ячейку A3 – формулу первого приближения =A2-B3/$C$2
6) В ячейку D3 – погрешность =ABS(A3-A2)
7) Выделить ячейки A3:D3 и скопировать формулы в соседние ячейки расположенных ниже строк A4:D4, A5:D5, и т.д. при помощи маркера заполнения. Каждая новая строка содержит результаты очередного приближения.
В столбце A найти значение корня, соответствующее заданной точности.
Приближенное решение данного уравнения содержится в ячейке A9 (погрешность в ячейке D9).
ABC анализ в Excel
ABC-анализ (англ. ABC-analysis) – это метод классификации товаров, клиентов или ресурсов по уровню их значимости и влияния на заданный показатель деятельности компании (например, на выручку, затраты и пр.).
| A | B | C | D |
| x | F(x) | F'(x) | погрешность |
| 1,00000 | |||
| 0,75000 | 1,00000 | 4,00000 | 0,25000 |
| 0,68605 | 0,17188 | 2,68750 | 0,06395 |
| 0,68234 | 0,00894 | 2,41198 | 0,00371 |
| 0,68233 | 0,00003 | 2,39676 | 0,00001 |
| Рис. 1.8. Решение уравнения методом Ньютона с помощью программы Excel. |
