Метод касательных на графике в excel
Суть метода состоит в том, что на -й итерации в точке строится касательная к кривой и ищется точка пересечения касательной с осью абсцисс (рис. 1.6). Если задан интервал изоляции корня , то за начальное приближение принимается тот конец отрезка, на котором
Уравнение касательной, проведенной к кривой в точке с координатами и , имеет вид:
За следующее приближение корня примем абсциссу точки пересечения касательной с ocью OX. Из (1.2) при , получим
Аналогично могут быть найдены и следующие приближения как точки пересечения с осью абсцисс касательных, проведенных в точках , и т.д. Формула для -го приближения имеет вид:
Для завершения итерационного процесса можно использовать условия или .
Объем вычислений в методе Ньютона больше, чем в других методах, поскольку приходится находить значение не только функции , но и ее производной. Однако скорость сходимости здесь значительно выше.
Пример 1.2. Решить уравнение на отрезке методом Ньютона c точностью .
Решение. Определим первые и вторые производные заданной функции : ; . Проверим выполнение условия сходимости на концах заданного интервала: — не выполняется, — выполняется. За начальное приближение корня можно принять . Находим первое приближение:
Так как , итерационный процесс заканчивается. Таким образом, приближенным решением данного уравнения является .
На рис. 1.7 приведена программа решения данного уравнения методом Ньютона. В качестве исходных данных вводятся начальное приближение и точность вычисления.
Пример 1.3. Решить уравнение на отрезке методом Ньютона c точностью с помощью программы Excel.
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. |
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).
Для использования этого метода исходное нелинейное уравнение необходимо привести к виду .
В качестве можно принять функцию ,где M ‑ неизвестная постоянная величина, которая определяется из условия сходимости метода простой итерации . При этом для определения M условие сходимости записывается в следующем виде:
Если известно начальное приближение корня , подставляя это значение в правую часть уравнения , получаем новое приближение .
Далее подставляя каждый раз новое значение корня в уравнение , получаем последовательность значений:
, ,. , k = 1,2. n.
Итерационный процесс прекращается, если результаты двух последовательных итераций близки, т.е. .
а) | б) |
Рис. 1.9. Геометрическая интерпретация метода простой итерации. |
Пример 1.4. Решить уравнение на отрезке методом простой итерации c точностью .
Решение. Из условия сходимости (1.5) , при определяем .Пусть .
Подставляя каждый раз новое значение корня в уравнение
Теперь и приближенным решением данного уравнения c точностью является .
На рис.1.10 приведена программа решения данного уравнения методом простой итерации. В качестве исходных данных вводятся начальное приближение, точность вычисления и значение постоянной М.
Пример 1.4. Решить уравнение на отрезке методом простой итерации c точностью с помощью программы Excel.
A | B | C | D |
x | f(x) | M | погрешность |
0,8 | 0,2 | ||
0,7376 | 0,312 | 0,0624 | |
0,70982 | 0,13889 | 0,02777881 | |
0,69633 | 0,06746 | 0,01349237 | |
0,68954 | 0,03396 | 0,00679209 | |
0,68606 | 0,01738 | 0,0034769 | |
0,68427 | 0,00897 | 0,00179463 | |
Рис.1.11. Решение уравнения методом простой итерации с помощью программы 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).
при сохранении книги Excel после поиска решения все значения, введенные в окнах диалога Поиск решения , сохраняются вместе с данными рабочего листа. Если же вы хотите что-то уточнить, обращайтесь ко мне!
· Метод поиска – служит для выбора алгоритма оптимизации. Метод Ньютона был рассмотрен ранее. В Методе сопряженных градиентов запрашивается меньше памяти, но выполняется больше итераций, чем в методе Ньютона. Данный метод следует использовать, если задача достаточно велика и необходимо экономить память, а также если итерации дают слишком малое отличие в последовательных приближениях.
Метод Ньютона (метод касательных).
Очевидно, что этот метод обеспечивает сходящийся процесс приближений лишь при выполнении некоторых условий (например при непрерывности и знакопостоянстве первой и второй производной функции в окрестности корня) и при их нарушении либо дает расходящийся процесс (4), либо приводит к другому корню (5).
Тангенциальная линия - это прямая линия на графике, которая проходит по касательной к кривой линии, составленной из точек данных. В Excel есть возможность автоматически создавать линию тренда, или вы можете вручную нарисовать касательную линию на графике. Тангенциальная линия рисуется касаясь изогнутой линии, так что когда кривая отходит от линии, она равноудалена от линии. В то время как функция линии тренда в Excel покажет тренд всей линии, ручное рисование линии создаст истинную касательную.
Кредит: Драган Радоевич / iStock / Getty Images
Шаг 1
Откройте лист Excel, содержащий данные, которые вы хотите использовать для тангенциальной линии. Изогнутый линейный график основан на наборах двух точек данных, например времени и амплитуды.
Шаг 2
Нажмите и перетащите на электронную таблицу, чтобы выделить данные, которые вы хотите показать, включая метки в вашем выборе.
Шаг 3
Нажмите на вкладку «Вставить», а затем нажмите стрелку вниз рядом с кнопкой «Разброс» в области «Диаграмма». Выберите «Разброс с гладкими линиями». Диаграмма создана в вашей электронной таблице.
Шаг 4
Перейдите на вкладку «Инструменты диаграммы: макет», а затем нажмите кнопку «Линия тренда» справа. Выберите «Линейный», чтобы создать прямую линию, которая приближается к прогрессу изогнутой линии. Он будет пересекать кривую в некоторых точках.
Шаг 5
Нажмите на вкладку «Вставка», а затем нажмите «Фигуры». Выберите «Линия» и нарисуйте линию так, чтобы она просто касалась одной из точек на вашей кривой.
Шаг 6
Нажмите на вкладку «Инструменты рисования: Формат» и нажмите кнопку «Повернуть» справа. Выберите «Дополнительные параметры вращения». Нажмите стрелку «Вверх» или «Вниз» рядом с полем «Поворот» в диалоговом окне, которое появляется, чтобы повернуть линию на кривой. Когда линия будет равноудалена от обеих сторон кривой, нажмите «ОК».
Как нарисовать цветную линию в фотошопе
Инструменты рисования в Photoshop позволяют создавать векторные фигуры с различными атрибутами. Линии являются настолько важным элементом дизайна, что Photoshop включает в себя инструмент для рисования .
Как нарисовать линию в фотошопе
В Photoshop есть несколько способов рисования линий. Узнайте, когда и как использовать инструменты «Кисть», «Линия» и «Перо» для прямых линий и кривых.
Как нарисовать линию в Excel
Excel, программа для работы с электронными таблицами, является частью Microsoft Office Suite. В Excel 2007 есть опции для включения изображений, картинок, фигур и SmartArt. Формы включают объекты, такие как .
ABC-анализ широко используется в различных областях экономики: при управлении запасами, выборе поставщиков, работе с клиентами и т. п. [1, 2]. Он основан на эмпирическом законе Парето (принципе 80/20), согласно которому наибольший вклад в суммарный результат S какой-либо деятельности или активности дают усилия небольшого числа ее участников [1]. Под участником (игроком) будем понимать любой объект или субъект, вклад которого учитывается в суммарном итоге. Фольклорная формулировка принципа Парето (ПП): 80% результата дает деятельность 20% игроков. В ПП полагается, что функция результата является аддитивной от вкладов участников, количество которых N.
При проведении ABC-анализа рекомендуют придерживаться следующего алгоритма:
формулировка цели анализа;
определение участников;
установление показателей для дифференциации участников;
получение данных для анализа;
оценка участников по выбранным показателям;
упорядочение игроков согласно величинам показателей;
разделение участников на группы;
экономическая интерпретация результатов анализа, принятие управленческих решений и т. п. [1, 3].
Нами будет рассмотрен случай одного показателя. При этом упорядочение участников обычно осуществляется по убыванию величины показателя [1–4]. Каждому участнику присваивают номер, начиная с игрока с наибольшим значением показателя. Далее по полученным упорядоченным значениям находят для каждого участника показатель с нарастающим итогом. Строят в прямоугольной декартовой системе координат кривую Лоренца (КЛ) [5] зависимости этого показателя y от номера участника x. При построении кривой учитывают точку с координатами (0;0) – нулевой вклад от фиктивного («нулевого») участника. Часто эту кривую строят на основании данных в процентах. Для получения таких данных значения показателя с нарастающим итогом делят на значение для последнего участника (суммарный результат S) и умножают на 100%. Аналогично поступают с номерами участников: все их делят на максимальный номер N и результаты умножают на 100%. Используя полученную кривую, тем или иным способом [1, 3, 4, 6, 7] производят разбиение множества участников на три непересекающихся подмножества (группы) A, B и C.
Методов такого разбиения достаточно много [3, 4, 6, 7]. Мы упомянем эмпирический и метод касательных. Внимание сконцентрируем на последнем, который можно реализовывать аналитически и графически [4].
При графической реализации (рис. 1) начальную и конечную точки кривой Лоренца соединяют отрезком прямой. Далее на этой кривой находят точку (xA; yA), касательная в которой параллельна построенному отрезку прямой. Абсцисса xA найденной точки разделяет группы A и B и сама включается в группу A.?Чтобы разделить оставшихся участников на группы B и C, найденную точку (xA; yA) соединяют отрезком прямой с крайней правой точкой КЛ. На участке КЛ, лежащем правее (xA; yA), находят точку (xB; yB), в которой касательная к кривой параллельна второму отрезку. Абсцисса xB этой точки разделяет группы B и C и сама включается в группу B.?Если абсциссы точек (xA; yA) и (xB; yB) не принадлежат экспериментальным данным, то эти абсциссы не включаются в соответствующую группу. Отметим, что графическое разделение на группы A, B и C подразумевает аппроксимацию экспериментальных данных некоторой кривой [4].
Подобная аппроксимация экспериментальных данных функциональной зависимостью осуществляется при аналитической реализации метода касательных [4, 6]. Пусть аппроксимирующая функция имеет вид y = f(x), причем a x b, f(a) y f(b) (a, b – левая, правая границы области задания функции y = f(x)). Если эмпирические данные нормированы на 100%, то 0 x 1; 0 y ? 1 (f(0) = 0; f(1) = 1).
Уравнение прямой, содержащей первый отрезок, имеет вид
, (1)
в случае нормирования данных оно принимает вид
y = x. (1a)
Если функция y = f(x) непрерывна на [a; b] и дифференцируема в (a; b), то, согласно теореме Лагранжа [8], существует точка xA ? [a; b], в которой производная
; (2)
в случае нормирования –
f'(xA) = 1. (2a)
Точка xA разделяет группы A и B.
Уравнение прямой, содержащей второй отрезок, имеет вид
; (3)
при нормировании данных это уравнение принимает вид
. (3a)
По той же теореме Лагранжа существует точка xB ? [xA; b], в которой производная
; (4)
при нормировке данных
. (4a)
Точка xB разделяет группы B и C.
Следовательно, при аналитическом подходе необходимо: аппроксимировать экспериментальную зависимость аналитической, например методом наименьших квадратов [9]; решить аналитически или численно уравнения (2) и (4) относительно xA и xB соответственно.
Отметим, что метод касательных при любой реализации позволяет осуществлять разбиение на большее, чем три, число групп – путем дальнейшего построения аналогичных (3) отрезков и отыскания касательных к кривой, параллельных этим отрезкам.
Приведем доводы о тесной связи метода касательных с ПП. Согласно последнему вклад SA, который больше половины общего результата S, дают участники, количество NA которых меньше половины общего их числа N.?Тогда средняя скорость нарастания результата для этой доли участников равна SA/NA > (S/2)/(N/2) = S/N, где S/N – средняя скорость нарастания результата для всего множества участников. S/N равна угловому коэффициенту первого отрезка (рис. 1). Тогда абсцисса xA точки касания разделяет участников, дающих скорость роста результата большую или равную S/N и меньшую S/N. Под этой скоростью, даваемой одним участником, понимаем отношение вклада этого участника к единице (один участник). В силу ранжирования исходных данных эти две группы находятся по разные стороны от точки xA (абсцисса xA может относиться к группе A).
Так, метод касательных обеспечивает разделение участников на группы по признаку отношения величины скорости роста результата, даваемой участником, к средней скорости этого роста на рассматриваемом участке КЛ. Непосредственная реализация метода требует, как указывалось выше, либо построения КЛ и нахождения графически касательной к ней, либо применения какого-либо математического пакета, например MathCAD, при аналитическом подходе [6].
Мы предлагаем использовать возможности Microsoft EXCEL. Для обоснования этого подхода вновь обратимся к теореме Лагранжа, точнее к ее доказательству [8]. При этом доказательстве вводится функция
, (6)
то есть в рассматриваемом нами случае функция разности уравнения кривой Лоренца и уравнения отрезка (1). Из (6) сразу получаем, что F(a) = F(b) = 0. Поскольку функция F(x) непрерывна на [a; b], дифференцируема в (a; b) и принимает на концах [a; b] одинаковые значения, то для F(x) выполняется утверждение теоремы Ролля [8]: существует точка xA ? [a; b], в которой F(x) = 0. По построению функция f(x) монотонно возрастает, тогда возможны два вида графика функции F(x):
все участники делают различные вклады в результат, поэтому график F(x) имеет единственный максимум в точке хА – граничной для групп A и B;
есть совокупность участников, дающих одинаковые вклады; тогда график функции F(x) имеет горизонтальный участок (плато), ординаты точек которого превосходят ординаты всех остальных точек графика.
Однозначное разделение на группы методом касательных возможно при первом виде графика функции F(x); функции с такими графиками и будут представлены в примерах. Замечание по поводу второго вида графика функции F(x) приведем в конце данной статьи.
Таким образом, для реальных дискретных экспериментальных данных в Microsoft EXCEL необходимо находить максимумы функций вида (6), построенных на соответствующих интервалах.
Конкретный пример реализации метода касательных в MS EXCEL. Для него взяты тридцать первых позиций табл. 3.1 из [4]. Расчеты в нормированных величинах представлены на листе EXCEL (рис. 2). В столбцах B8:B38 и D8:D38 содержатся исходные данные (учтен также «нулевой» участник – строка B8:H8), в столбцах C8:C38 и E8:E38 – нормированные (общий результат в 117 040 использован при нормировке данных из столбца D8:D38). Определяем границу групп A и B.?Столбец G8:G38 содержит ординаты точек первого отрезка (1а) (угловой коэффициент K1 = 1 из ячейки G5), столбец H8:H38 – значения функции (6). Максимальная величина в этом столбце – в ячейке H16. Тогда, согласно приведенной выше интерпретации метода касательных, количество участников группы A составляет 26,67% (ячейка C16) от общего их числа; они дают 77,54% (ячейка F16) конечного результата, то есть xA = 26,67%, f(xA) = 77,54%. Теперь определяем границу групп B и C. В столбец I16:I38 помещены ординаты точек второго отрезка (3а) (угловой коэффициент K2 = = (1 – f(xA))/(1 – xA) = (1–0,7754)/(1–0,2667) ? 0,3063 из ячейки F5), в столбец J16:J38 – значения функции (6), максимальное значение – в ячейке I16. Таким образом, xB = 56,67% (ячейка C25), f(xB) = 93,31% (ячейка F25). Поэтому группа B содержит 56,67% – 26,67% = 30% участников и дает 93,31% – 77,54% = 15,77% общего результата. Группа C содержит 100% – 56,67% = 43,33% игроков и дает 100% – 93,31% = 6,69% от общего результата. Итак, получили разделение на группы:
Приведем еще один способ реализации метода касательных в MS EXCEL. Для этого обратимся к функции f(x). Исходя из определения, она может быть представлена в виде:
(0 ? u ? N), (7)
где ?(z) – вклад участника с номером z. Функция ?(z) – убывающая, так как предварительно проведено ранжирование вкладов по убыванию. Рассматриваем ненормированный интервал участников. Интегральное представление (7) возможно при достаточно большом их количестве. В противном случае в (7) вместо интегрирования применяется суммирование по номерам участников. На результате это не сказывается.
Для нахождения xA берем соотношение (2). Выпишем его, используя (7):
, так как f(0) = 0.
Величина = представляет
собой среднее значение ??(z)?1 функции вклада всех N участников.
Аналогично после отделения группы A находим xB, используя (4) и (7):
– среднее значение вкладов участников с номерами из (xA; N].
Исходя из вышеизложенного, реализация в MS EXCEL такова. Для всех участников, кроме «нулевого», находим среднюю величину вклада. К группе A отнесем игроков с вкладами, большими либо равными среднему. Для оставшихся участников вновь находим средний вклад. К группе B отнесем тех из них, чьи вклады больше либо равны второму среднему вкладу. После отделения групп A и B получаем группу C.?При необходимости разбиения на большее число групп процедуру нужно продолжить с оставшимися после выделения групп A и B участниками.
Продемонстрируем реализацию в Microsoft EXCEL указанного подхода (рис. 3). Используем те же исходные данные, что и в первом примере; они содержатся в ячейках C4:C33 и D4:D33. Нормированные величины помещены в ячейки B4:B33 и E4:E33; для нормирования значений из E4:E33 взят конечный результат в 117 040 (ячейка D34). Средний вклад по всем участникам находится в D35 (для нормированных данных – в E35). Представители группы A отражены в ячейках B4:B11 и составляют 26,67% от всех участников; эти игроки дают 77,54% от общего результата. Средний вклад для оставшихся после выделения группы A участников находится в D36 (для нормированных данных – в E36). Представители группы B (в ячейках B12:B20) составляют 56,67% – 26,67% = 30% от всех участников и дают 93,31% – 77,54% = 15,77% от общего результата. Тогда группа C содержит 100% – 56,67% = 43,33% участников и дает 100% – 93,31% = 6,69% от общего результата. На листе EXCEL группы выделены цветом с помощью опции «Условное форматирование».
Разбиение на подмножества, полученное при таком подходе, естественно совпадает с разбиением в результате первого подхода, поскольку оба – суть реализации в EXCEL одного и того же метода касательных. Отметим одну особенность последнего. Если кривая Лоренца имеет участок, параллельный первому отрезку (это соответствует второму виду графика функции F(x)), то метод касательных даст не одну точку (xA; yA), а весь этот участок. Такая же ситуация возможна и на шаге разделения групп B и C, а также на других шагах (если они будут предприняты). Это качество относят к недостаткам метода [5], так как оно не позволяет однозначно провести разделение на группы. Наличие прямолинейного участка на КЛ свидетельствует о присутствии нескольких игроков с равными вкладами. Решение об их включении (полном или частичном) или невключении в ту или иную группу должен принимать исследователь, воспользовавшись другим методом, например эмпирическим [4].
Таким образом, изучен метод касательных. Указан признак, согласно которому осуществляется разделение на группы при его применении. Представлена связь метода с эмпирическим принципом Парето, по результатам анализа предложены два подхода для реализации в MS EXCEL. Их легко применять на практике, поскольку они освобождают лицо, проводящее ABC-анализ, от необходимости осуществлять какие-либо графические построения или использовать продвинутые математические пакеты при аналитическом определении границ групп.
Статья поступила в редакцию 09.02.2016?г.
Метод наименьших квадратов (МНК) основан на минимизации суммы квадратов отклонений выбранной функции от исследуемых данных. В этой статье аппроксимируем имеющиеся данные с помощью линейной функции y = a x + b .
Метод наименьших квадратов (англ. Ordinary Least Squares , OLS ) является одним из базовых методов регрессионного анализа в части оценки неизвестных параметров регрессионных моделей по выборочным данным.
Рассмотрим приближение функциями, зависящими только от одной переменной:
- Линейная: y=ax+b (эта статья)
- Логарифмическая : y=a*Ln(x)+b
- Степенная : y=a*x m
- Экспоненциальная : y=a*EXP(b*x)+с
- Квадратичная : y=ax 2 +bx+c
Примечание : Случаи приближения полиномом с 3-й до 6-й степени рассмотрены в этой статье. Приближение тригонометрическим полиномом рассмотрено здесь.
Линейная зависимость
Нас интересует связь 2-х переменных х и y . Имеется предположение, что y зависит от х по линейному закону y = ax + b . Чтобы определить параметры этой взаимосвязи исследователь провел наблюдения: для каждого значения х i произведено измерение y i (см. файл примера ). Соответственно, пусть имеется 20 пар значений (х i ; y i ).
Для наглядности рекомендуется построить диаграмму рассеяния.
Примечание: Если шаг изменения по х постоянен, то для построения диаграммы рассеяния можно использовать тип График , если нет, то необходимо использовать тип диаграммы Точечная .
Из диаграммы очевидно, что связь между переменными близка к линейной. Чтобы понять какая из множества прямых линий наиболее «правильно» описывает зависимость между переменными, необходимо определить критерий, по которому будут сравниваться линии.
В качестве такого критерия используем выражение:
Вышеуказанное выражение представляет собой сумму квадратов расстояний между наблюденными значениями y i и ŷ i и часто обозначается как SSE ( Sum of Squared Errors ( Residuals ), сумма квадратов ошибок (остатков) ) .
Метод наименьших квадратов заключается в подборе такой линии ŷ = ax + b , для которой вышеуказанное выражение принимает минимальное значение.
Примечание: Любая линия в двухмерном пространстве однозначно определяется значениями 2-х параметров: a (наклон) и b (сдвиг).
Считается, что чем меньше сумма квадратов расстояний, тем соответствующая линия лучше аппроксимирует имеющиеся данные и может быть в дальнейшем использована для прогнозирования значений y от переменной х. Понятно, что даже если в действительности никакой взаимосвязи между переменными нет или связь нелинейная, то МНК все равно подберет «наилучшую» линию. Таким образом, МНК ничего не говорит о наличии реальной взаимосвязи переменных, метод просто позволяет подобрать такие параметры функции a и b , для которых вышеуказанное выражение минимально.
Проделав не очень сложные математические операции (подробнее см. статью про квадратичную зависимость ), можно вычислить параметры a и b :
Как видно из формулы, параметр a представляет собой отношение ковариации и дисперсии , поэтому в MS EXCEL для вычисления параметра а можно использовать следующие формулы (см. файл примера лист Линейная ):
= КОВАР(B26:B45;C26:C45)/ ДИСП.Г(B26:B45) или
Также для вычисления параметра а можно использовать формулу = НАКЛОН(C26:C45;B26:B45) . Для параметра b используйте формулу = ОТРЕЗОК(C26:C45;B26:B45) .
И наконец, функция ЛИНЕЙН() позволяет вычислить сразу оба параметра. Для ввода формулы ЛИНЕЙН(C26:C45;B26:B45) необходимо выделить в строке 2 ячейки и нажать CTRL + SHIFT + ENTER (см. статью про формулы массива, возвращающими несколько значений ). В левой ячейке будет возвращено значение а , в правой – b .
Примечание : Чтобы не связываться с вводом формул массива потребуется дополнительно использовать функцию ИНДЕКС() . Формула = ИНДЕКС(ЛИНЕЙН(C26:C45;B26:B45);1) или просто = ЛИНЕЙН(C26:C45;B26:B45) вернет параметр, отвечающий за наклон линии, т.е. а . Формула = ИНДЕКС(ЛИНЕЙН(C26:C45;B26:B45);2) вернет параметр, отвечающий за пересечение линии с осью Y, т.е. b .
Вычислив параметры, на диаграмме рассеяния можно построить соответствующую линию.
Инструмент диаграммы Линия тренда
Еще одним способом построения прямой линии по методу наименьших квадратов является инструмент диаграммы Линия тренда . Для этого выделите диаграмму, в меню выберите вкладку Макет , в группе Анализ нажмите Линия тренда , затем Линейное приближение .
Поставив в диалоговом окне галочку в поле «показывать уравнение на диаграмме» можно убедиться, что найденные выше параметры совпадают со значениями на диаграмме.
Примечание : Для того, чтобы параметры совпадали необходимо, чтобы тип у диаграммы был Точечная, а не График . Дело в том, что при построении диаграммы График значения по оси Х не могут быть заданы пользователем (пользователь может указать только подписи, которые не влияют на расположение точек). Вместо значений Х используется последовательность 1; 2; 3; … (для нумерации категорий). Поэтому, если строить линию тренда на диаграмме типа График , то вместо фактических значений Х будут использованы значения этой последовательности, что приведет к неверному результату (если, конечно, фактические значения Х не совпадают с последовательностью 1; 2; 3; …).
СОВЕТ : Подробнее о построении диаграмм см. статьи Основы построения диаграмм и Основные типы диаграмм .
Экспоненциальное сглаживание используется для сглаживания краткосрочных колебаний во временных рядах, чтобы облегчить определение долгосрочного тренда, а также для прогнозирования. Произведем экспоненциальное сглаживание с помощью надстройки MS EXCEL Пакет анализа и формулами. Рассмотрим двойное и тройное экспоненциальное сглаживание для прогнозирования рядов с трендом и сезонностью.
Экспоненциальное сглаживание один из наиболее распространённых методов для сглаживания временных рядов. В отличие от метода Скользящего среднего, где прошлые наблюдения имеют одинаковый вес, Экспоненциальное сглаживание присваивает им экспоненциально убывающие веса, по мере того как наблюдения становятся старше. Другими словами, последние наблюдения дают относительно больший вес при прогнозировании, чем старые наблюдения.
Примечание: Перед прочтением этой статьи рекомендуется прочитать про Скользящее среднее.
Примечание: В англоязычной литературе для экспоненциального сглаживания используется термин Single Exponential Smoothing или Simple Exponential Smoothing (SES).
Напомним, что при усреднении методом Скользящего среднего веса, присвоенные наблюдениям, одинаковы и равны 1/n, где n – количество периодов усреднения. Например, в случае усреднения за 3 периода скользящее среднее равно:
В случае Экспоненциального сглаживания формула выглядит следующим образом:
Параметр альфа определяет степень сглаживания. При малых значениях альфа (0,1 – 0,2) имеет место сильное сглаживание. При значениях близких к 1, сглаженный ряд практически повторяет исходный ряд с задержкой (лагом) на один период. Для медленно меняющегося ряда часто берут небольшие значения альфа=0,1; а для быстро меняющегося 0,3-0,5.
Примечание: Формулы представляют собой рекуррентное соотношение – это когда последующий член ряда вычисляется на основе предыдущего.
Примечание: Существует альтернативный подход к Экспоненциальному сглаживанию: в нем в формуле вместо Yi-1 заменяют на Yi. Этот подход используется в контрольных картах экспоненциально взвешенного скользящего среднего (EWMA).
Получить Экспоненциально сглаженный ряд можно с помощью надстройки Пакет анализа (Analysis ToolPak). Надстройка доступна из вкладки Данные, группа Анализ.
СОВЕТ: Подробнее о других инструментах надстройки Пакет анализа и ее подключении – читайте в статье.
Разместим исходный числовой ряд в диапазоне B7:B32.
Для наглядности пронумеруем каждое значение ряда (столбец А).
Вызовем надстройку Пакет анализа, выберем инструмент Экспоненциальное сглаживание.
Если диапазон включает и заголовок, то нужно установить галочку в поле Метки. В нашем случае устанавливать галочку не требуется, т.к. заголовок столбца не входит в диапазон B7:B32.
Поле Фактор затухания, как и параметр альфа в вышеуказанной формуле, определяет степень сглаживания ряда. Фактор затухания равен (1- альфа). Чем больше Фактор затухания тем глаже получается ряд. Установим значение 0,8.
В поле Выходной интервал достаточно ввести ссылку на левую верхнюю ячейку диапазона с результатами (укажем ячейку D7).
Также поставим галочки в поле Вывод графика и Стандартные погрешности (будет выведен столбец с расчетами погрешностей, англ. Standard Errors).
Нажмем ОК.
В результате работы надстройки, MS EXCEL разместил значения ряда, полученного методом Экспоненциального сглаживания, в столбце D (см. файл примера лист Пакет анализа ).
Первое значение сглаженного ряда, точнее формула = B7 , содержится в ячейке D8. Второе значение вычисляется с помощью формулы = 0,2*B8+0,8*D8 .
Таким образом, Фактор затухания (0,8) определяет вес (вклад) предыдущего значения сглаженного ряда. Соответственно, (1-Фактор затухания)=альфа определяет вес предыдущего значения исходного ряда.
Для отображения рядов MS EXCEL создал диаграмму типа график. Сглаженный ряд на диаграмме называется «Прогноз» (ряд красного цвета).
Диаграмма позволяет визуально определить «выбросы», т.е. значения исходного ряда, которые существенно отличаются от средних значений. Такие «выбросы» могут быть следствием ошибки, но они оказывают существенное влияние на вид сглаженного ряда.
В столбце E, начиная с ячейки Е11, MS EXCEL разместил формулы для вычисления погрешностей (англ. Standard Errors):
Т.е. данная погрешность вычисляется по формуле:
Значения y – это значения исходного ряда в период i. Значения «y с крышечкой» - значения ряда, полученного методом Экспоненциального сглаживания, в тот же в период i. Значение n для экспоненциального сглаживания всегда равно 3, т.е. ошибка вычисляется за 3 последних периода (последние 3 значения учитываются с макимальным весом при расчете текущего значения сглаженного ряда и, соответственно, вносят более 50% вклада в его значение. Величина вклада сильно зависит от альфа).
Подробнее об этой погрешности см. соответствующий раздел в статье про Скользящее среднее.
Как было показано в статье про Взвешенное скользящее среднее веса значений исходного ряда берутся в зависимости от их удаленности от текущего периода. Например, для 3-х периодов усреднения для Взвешенного скользящего среднего можно использовать формулу:
Экспоненциальное сглаживание по сути является модификацией Взвешенного скользящего среднего – при расчете значения сглаженного ряда используются ВСЕ предыдущие значения исходного ряда с весами уменьшающимися в геометрической прогрессии по мере удаления от текущего периода.
Чтобы это показать воспользуемся формулой
и вычислим Yэксп.5, т.е. значения сглаженного ряда для 5-го периода. После очевидных преобразований получим:
Таким образом, вес 4-го (предыдущего) члена ряда =(1-альфа) 0 , а вес 3-го =(1-альфа) 1 и т.д. Пусть t – текущий период (в нашем случае =5). Вес (t-i)-го члена ряда =(1-альфа) t-1-i . Т.к. (1-альфа)
Как известно, экспоненциальный рост y=a*EXP(b*x) в случае дискретной области определения с равными интервалами x называют геометрическим ростом (значения экспоненциальной функции y=a*EXP(b*x) являются в этом случае членами геометрической прогрессии m^x).
В нашем случае, приравняв i-й вес (1-альфа) t-1-i соответствующему значению экспоненциальной функции a*EXP(b*i) получим уравнение, которое позволит вычислить коэффициенты a и b (понадобится еще одно уравнение, например, для i-1 веса).
Решив систему из 2-х уравнений получим, a= EXP((t-1)*LN(1-альфа)) и b= LN(1-альфа) .
В файле примера для 26-го члена сглаженного ряда (t=26) вычислены веса всех предыдущих членов. На диаграмме ниже показано, что веса уменьшаются с ростом i в геометрической прогрессии, что соответствует экспоненциальной функции y=0,0038*exp(0,2231*x), где x=i. Вычисления параметров экспоненциальной кривой сделаны с помощью надстройки Поиск решения.
Недостатком формул, получаемых с помощью Пакета анализа, является то, что при изменении Фактора затухания (1-альфа) приходится перезапускать расчет. В файле примера на листе Формулы создана форма для быстрого пересчета Экспоненциального сглаживания в зависимости от значения Фактора затухания (полученный результат, естественно, полностью совпадает с расчетами надстройки Пакет анализа).
Значения ряда вычисляются с помощью формулы:
в ячейке В6 содержится значение Фактора затухания.
Выбор значения Фактора затухания для удобства осуществляется с помощью элемента управления Счетчик с шагом 0,1.
Читайте также: