Аппроксимация в Excel
Учет и контроль! Те, кому за 40 должны хорошо помнить этот лозунг из эпохи построения социализма и коммунизма в нашей стране.
«Мы, помню, 5 лет назад изготавливали до 1000 штук таких изделий в месяц, а сейчас и 700 еле-еле собираем!». Открываем статистику и видим, что 5 лет назад и 500 штук не изготавливали…
«Во сколько обходится километр пробега твоего автомобиля с учетом всех затрат?» Открываем статистику – 6 руб./км. Поездка на работу – 107 рублей. Дешевле, чем на такси (180 рублей) более чем в полтора раза. А бывали времена, когда на такси было дешевле…
«Сколько времени требуется для изготовления металлоконструкций уголковой башни связи высотой 50 м?» Открываем статистику – и через 5 минут готов ответ…
«Сколько будет стоить ремонт комнаты в квартире?» Поднимаем старые записи, делаем поправку на инфляцию за прошедшие годы, учитываем, что в прошлый раз купили материалы на 10% дешевле рыночной цены и – ориентировочную стоимость мы уже знаем…
Ведя учет своей профессиональной деятельности, вы всегда будете готовы ответить на вопрос начальника: «Когда. ». Ведя учет домашнего хозяйства, легче спланировать расходы на крупные покупки, отдых и прочие расходы в будущем, приняв соответствующие меры по дополнительному заработку или по сокращению необязательных расходов сегодня.
В этой статье я на простом примере покажу, как можно обрабатывать собранные статистические данные в Excel для возможности дальнейшего использования при прогнозировании будущих периодов.
Оцифровка и аппроксимация графиков функций при помощи Wolfram Mathematica и Graph Digitizer / Хабр
Стоит отметить! При создании поверхностной диаграммы мы изменили числовые значения столбца A в текстовые, поместив их в столбец B с помощью формулы . Если бы мы этого не сделали, то Excel воспринял бы эти числовые значения (столбца A) как данные для построения поверхностной диаграммы, а не как подписи данных.
Как рассчитать значения полинома в Excel?
Выделяем ряд со значениями и строим график временного ряда.
На график добавляем полином 6-й степени.
Затем в формате линии тренда ставим галочку «показать уравнение на диаграмме»
После этого уравнение выводится на график y = 3,7066x 6 — 234,94x 5 + 4973,6x 4 — 35930x 3 — 7576,8x 2 + 645515x + 5E+06 . Для того чтобы последний коэффициент сделать читаемым, мы зажимаем левую кнопку мыши и выделяем уравнение полинома
Нажимаем правой кнопкой и выбираем «формат подписи линии тренда»
В настройках подписи линии тренда выбираем число и в числовых форматах выбираем «Числовой».
Получаем уравнение полинома в читаемом формате:
y = 3,71x 6 — 234,94x 5 + 4 973,59x 4 — 35 929,91x 3 — 7 576,79x 2 + 645 514,77x + 4 693 169,35
Каждому периоду во временном ряду присваиваем порядковый номер, который будем подставлять в уравнение вместо X.
Рассчитаем значения полинома для каждого периода. Для этого вводим формулу полинома y = 3,71x 6 — 234,94x 5 + 4 973,59x 4 — 35 929,91x 3 — 7 576,79x 2 + 645 514,77x + 4 693 169,35 в первую ячейку и фиксируем ссылки на коэффициенты тренда (см. статью как зафиксировать ссылки)
= R2C8 *RC[-3]^6+ R3C8 *RC[-3]^5+ R4C8 *RC[-3]^4+ R5C8 *RC[-3]^3+ R6C8 *RC[-3]^2+ R7C8 *RC[-3]+ R8C8
в которой коэффициенты тренда зафиксированы и вместо «x» мы подставляем ссылку на номер текущего временного ряда (для первого значение 1, для второго 2 и т.д.)
Также «X» возводим в соответствующую степень (значок в Excel «^» означает возведение в степень)
=R2C8*RC[-3] ^6 +R3C8*RC[-3] ^5 +R4C8*RC[-3] ^4 +R5C8*RC[-3] ^3 +R6C8*RC[-3] ^2 +R7C8*RC[-3]+R8C8
Теперь протягиваем формулу до конца временного ряда и получаем рассчитанные значения полиномиального тренда для каждого периода.
Аппроксимация в Excel | Блог Александра Воробьева
- Коэффициентов полиномиального тренда выведенных на график;
- Коэффициентов полинома рассчитанных с помощью функцию Excel =ЛИНЕЙН
- и с помощью Forecast4AC PRO одним нажатием клавиши, легко и быстро.
Одной из отличных возможностей программы Excel является возможность построить графики различных математических функций. Под функцией понимается множество точек (x, y), которые удовлетворяют условию y=f(x). Это означает, что для построения графика требуется сформировать массив этих точек, а программа Excel уже выстроит на этом основании графическое отображение функции.
