Excel

Метод апроксимації в Microsoft Excel

Серед різних методів прогнозування можна не виділити апроксимацію. З її допомогою можна проводити приблизні підрахунки і обчислювати плановані показники, шляхом заміни вихідних об'єктів на більш прості. У Ексель теж існує можливість використання даного методу для прогнозування і аналізу. Давайте розглянемо, як цей метод можна застосувати в зазначеній програмі вбудованими інструментами.

виконання апроксимації

Найменування даного методу походить від латинського слова proxima - «найближча» Саме наближення шляхом спрощення і згладжування відомих показників, вибудовування їх у тенденцію і є його основою. Але даний метод можна використовувати не тільки для прогнозування, а й для дослідження вже наявних результатів. Адже апроксимація є, по суті, спрощенням вихідних даних, а спрощений варіант досліджувати легше.

Головний інструмент, за допомогою якого проводиться згладжування в Excel - це побудова лінії тренда. Суть полягає в тому, що на основі вже наявних показників добудовується графік функції на майбутні періоди. Основне призначення лінії тренда, як не важко здогадатися, це складання прогнозів або виявлення загальної тенденції.

Але вона може бути побудована із застосуванням одного з п'яти видів апроксимації:

  • лінійної;
  • експоненційної;
  • логарифмічною;
  • поліноміальної;
  • Статечної.

Розглянемо кожен з варіантів більш докладно окремо.

урок: Як побудувати лінію тренда в Excel

Спосіб 1: лінійне згладжування

Перш за все, давайте розглянемо найпростіший варіант апроксимації, а саме за допомогою лінійної функції. На ньому ми зупинимося докладніше всього, так як викладемо загальні моменти характерні і для інших способів, а саме побудова графіка і деякі інші нюанси, на яких при розгляді наступних варіантів вже зупинятися не будемо.

Перш за все, побудуємо графік, на підставі якого будемо проводити процедуру згладжування. Для побудови графіка візьмемо таблицю, в якій помісячно вказана собівартість одиниці продукції, яку виробляє підприємство, і відповідна прибуток в даному періоді. Графічна функція, яку ми побудуємо, буде відображати залежність збільшення прибутку від зменшення собівартості продукції.

  1. Для побудови графіка, перш за все, виділяємо стовпці «Собівартість одиниці продукції» та «Прибуток». Після цього переміщаємося у вкладку «Вставка». Далі на стрічці в блоці інструментів «Діаграми» клацаємо по кнопці «Точкова». У списку вибираємо найменування «Точкова з гладкими кривими і маркерами». Саме цей вид діаграм найбільш підходить для роботи з лінією тренда, а значить, і для застосування методу апроксимації в Excel.

    2. Побудова діаграми в Microsoft Excel

  2. Графік побудований.

    3. Графік побудований в Microsoft Excel

  3. Для додавання лінії тренду виділяємо його кліком правої кнопки миші. З'являється контекстне меню. Вибираємо в ньому пункт «Додати лінію тренда ...».

    Додавання лінії тренду через контекстне меню в Microsoft Excel

    Існує ще один варіант її додавання. У додатковій групі вкладок на стрічці «Робота з діаграмами» переміщаємося у вкладку «Макет». Далі в блоці інструментів «Аналіз» клацаємо по кнопці «Лінія тренда». Відкривається список. Так як нам потрібно застосувати лінійну апроксимацію, то з представлених позицій вибираємо «Лінійне наближення».

    4. Додавання лінії тренду через блок інструментів на стрічці в Microsoft Excel

  4. Якщо ж ви вибрали все-таки перший варіант дій з додаванням через контекстне меню, то відкриється вікно формату.

    У блоці параметрів «Побудова лінії тренду (апроксимація і згладжування)» встановлюємо перемикач в позицію «Лінійна».
    При бажанні можна встановити галочку біля позиції «Показувати рівняння на діаграмі». Після цього на діаграмі буде відображатися рівняння згладжує функції.

    Також в нашому випадку для порівняння різних варіантів апроксимації важливо встановити галочку біля пункту «Помістити на діаграму величину достовірної апроксимації (R ^ 2)». Даний показник може варіюватися від 0 до 1. Чим він вищий, тим апроксимація якісніше (достовірніше). Вважається, що при величині даного показника 0,85 і вище згладжування можна вважати достовірним, а якщо показник нижче, то - ні.

    Після того, як провели всі вищевказані налаштування. Тиснемо на кнопку «Закрити», розміщену в нижній частині вікна.

    5. Включення лінійної апроксимації в Microsoft Excel

  5. Як бачимо, на графіку лінія тренду побудована. При лінійної апроксимації вона позначається чорної прямий смугою. Зазначений вид згладжування можна застосовувати в найбільш простих випадках, коли дані змінюються досить швидко і залежність значення функції від аргументу очевидна.

Лінія тренда побудована за допомогою лінійної апроксимації в Microsoft Excel

Згладжування, яке використовується в даному випадку, описується наступною формулою:

y=ax+b

У конкретно нашому випадку формула приймає такий вигляд:

y=-0,1156x+72,255

Величина достовірності апроксимації у нас дорівнює 0,9418, що є досить прийнятним підсумком, що характеризує згладжування, як достовірне.

Спосіб 2: Експоненціальна апроксимація

Тепер давайте розглянемо експонентний тип апроксимації в Ексель.

  1. Для того, щоб змінити тип лінії тренда, виділяємо її кліком правої кнопки миші і в меню, що розкрилося вибираємо пункт «Формат лінії тренда ...».

    2. Перехід в формат лини тренда в Microsoft Excel

  2. Після цього запускається вже знайоме нам вікно формату. У блоці вибору типу апроксимації встановлюємо перемикач в положення «Експоненціальна». Інші налаштування залишимо такими ж, як і в першому випадку. Клацаємо по кнопці «Закрити».

    3. Побудова експоненційної лінії тренду в Microsoft Excel

  3. Після цього лінія тренда буде побудована на графіку. Як бачимо, при використанні даного методу вона має кілька вигнуту форму. При цьому рівень достовірності дорівнює 0,9592, що вище, ніж при використанні лінійної апроксимації. Експонентний метод найкраще використовувати в тому випадку, коли спочатку значення швидко змінюються, а потім приймають збалансовану форму.

Експоненціальна лінія тренда побудована в Microsoft Excel

Загальний вигляд функції згладжування при цьому такий:

y=be^x

де e - це основа натурального логарифма.

У конкретно нашому випадку формула прийняла наступну форму:

y=6282,7*e^(-0,012*x)

Спосіб 3: логарифмічна згладжування

Тепер настала черга розглянути метод логарифмічною апроксимації.

  1. Тим же способом, що і в попередній раз через контекстне меню запускаємо вікно формату лінії тренда. Встановлюємо перемикач в позицію «Логарифмічна» і тиснемо на кнопку «Закрити».

    2. Включення логарифмічною апроксимації в Microsoft Excel

  2. Відбувається процедура побудови лінії тренда з логарифмічною аппроксимацией. Як і в попередньому випадку, такий варіант краще використовувати тоді, коли спочатку дані швидко змінюються, а потім приймають збалансований вигляд. Як бачимо, рівень достовірності дорівнює 0,946. Це вище, ніж при використанні лінійного методу, але нижче, ніж якість лінії тренда при експоненційному згладжуванні.

Логарифмічна лінія тренду побудована в Microsoft Excel

У загальному вигляді формула згладжування виглядає так:

y=a*ln(x)+b

де ln - це величина натурального логарифма. Звідси і найменування методу.

У нашому випадку формула приймає наступний вигляд:

y=-62,81ln(x)+404,96

Спосіб 4: поліноміальний згладжування

Настала черга розглянути метод полиномиального згладжування.

  1. Переходимо у вікно формату лінії тренда, як вже робили не раз. У блоці «Побудова лінії тренду» встановлюємо перемикач в позицію «Поліноміальна». Праворуч від даного пункту розташоване поле «Ступінь». При виборі значення «Поліноміальна» воно стає активним. Тут можна вказати будь-який статечне значення від 2 (встановлено за умовчанням) до 6. Даний показник визначає число максимумів і мінімумів функції. При установці полінома другого ступеня описується тільки один максимум, а при установці полінома шостого ступеня може бути описано до п'яти максимумів. Для початку залишимо налаштування за замовчуванням, тобто, вкажемо другий ступінь. Інші налаштування залишаємо такими ж, якими ми виставляли їх у попередніх способах. Тиснемо на кнопку «Закрити».

    2. Включення полиномиальной апроксимації в Microsoft Excel

  2. Лінія тренда з використанням даного методу побудована. Як бачимо, вона ще більш вигнута, ніж при використанні експоненціальної апроксимації. Рівень достовірності вище, ніж при будь-якому з використаних раніше способів, і становить 0,9724.

    Поліноміальна лінія тренду в Microsoft Excel

    Даний метод найбільш успішно можна застосовувати в тому випадку, якщо дані носять постійно мінливий характер. Функція, що описує даний вид згладжування, виглядає таким чином:

    y=a1+a1*x+a2*x^2+…+an*x^n

    У нашому випадку формула прийняла такий вигляд:

    y=0,0015*x^2-1,7202*x+507,01

  3. Тепер давайте змінимо ступінь поліномів, щоб побачити, чи буде відрізнятися результат. Повертаємося у вікно формату. Тип апроксимації залишаємо поліноміальних, але навпроти нього у вікні ступеня встановлюємо максимально можливе значення - 6.

    4. Включення полиномиальной апроксимації в шостого ступеня в Microsoft Excel

  4. Як бачимо, після цього наша лінія тренда прийняла форму яскраво вираженою кривої, у якої число максимумів дорівнює шести. Рівень достовірності підвищився ще більше, склавши 0,9844.

Поліноміальна лінія тренда в шостого ступеня в Microsoft Excel

Формула, яка описує даний тип згладжування, прийняла такий вигляд:

y=8E-08x^6-0,0003x^5+0,3725x^4-269,33x^3+109525x^2-2E+07x+2E+09

Спосіб 5: статечне згладжування

На завершення розглянемо метод статечної апроксимації в Excel.

  1. Подорожуємо у вікно «Формат лінії тренду». Встановлюємо перемикач виду згладжування в позицію «Степенева». Показ рівняння і рівня достовірності, як завжди, залишаємо включеними. Тиснемо на кнопку «Закрити».

    2. Поліноміальна лінія тренда в шостого ступеня в Microsoft Excel

  2. Програма формує лінію тренда. Як бачимо, в нашому випадку вона являє собою лінію з невеликим вигином. Рівень достовірності дорівнює 0,9618, що є досить високим показником. З усіх вищеописаних способів рівень достовірності був вище тільки при використанні полиномиального методу.

Статечна лінія тренда побудована в Microsoft Excel

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

Загальна формула, що описує даний метод має такий вигляд:

y=bx^n

У конкретно нашому випадку вона виглядає так:

y = 6E+18x^(-6,512)

Як бачимо, при використанні конкретних даних, які ми застосовували для прикладу, найбільший рівень достовірності показав метод поліноміальної апроксимації з поліномом в шостого ступеня (0,9844), найменший рівень достовірності у лінійного методу (0,9418). Але це зовсім не означає, що така ж тенденція буде при використанні інших прикладів. Ні, рівень ефективності у наведених вище методів може значно відрізнятися, в залежності від конкретного виду функції, для якої буде будуватися лінія тренда. Тому, якщо для цієї функції обраний метод найбільш ефективний, то це зовсім не означає, що він також буде оптимальним і в іншій ситуації.

Якщо ви поки не можете відразу визначити, грунтуючись на вищенаведених рекомендаціях, який вид апроксимації підійде конкретно у вашому випадку, то є сенс спробувати всі методи. Після побудови лінії тренда і перегляду її рівня достовірності можна буде вибрати оптимальний варіант.