Метод наближення в Microsoft Excel

Зміст

• Виконання апроксимації
  • Спосіб 1: лінійне згладжування
  • Спосіб 2: експоненціальне наближення
  • Спосіб 3: логарифмічне згладжування
  • Спосіб 4: поліноміальне згладжування
  • Спосіб 5: згладжування потужності
• Питання та відповіді

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


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

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

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

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


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

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

Y=ax+b

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

Y=-0,1156x+72,255

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

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

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

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

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

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

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

Y=be^x

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

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

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

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

Тепер настала черга розглянути метод логарифмічного наближення.

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

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

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

Y=a*ln(x)+b

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Y=bx^n

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

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

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

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