Excel

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

Сред различните методи за прогнозиране не можем да изолирам сближаването. С негова помощ можете да направите приблизителни изчисления и да изчислите планираните индикатори, като замените оригиналните обекти с опростените. В 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: експоненциално сближаване

Сега нека разгледаме експоненциалния тип приближение в Excel.

  1. За да промените вида на тенденцията, изберете го като кликнете с десния бутон на мишката и изберете "Trendline format ..." в отвореното меню.

    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. По същия начин като в предишното време чрез контекстното меню стартираме прозореца за формата на тренда. Задайте ключа в позиция "Logarithmic" и кликнете върху бутона "Close" .

    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 ). Но това не означава, че същата тенденция ще бъде използвана и при използването на други примери. Не, нивото на ефективност на горепосочените методи може да варира значително в зависимост от конкретния тип функция, за която ще бъде изградена тенденцията. Следователно, ако избраният метод е най-ефективен за тази функция, това не означава, че той също ще бъде оптимален в друга ситуация.

Ако не можете веднага да определите, въз основа на горепосочените препоръки, какъв вид сближаване е подходящо във вашия случай, тогава има смисъл да изпробвате всички методи. След като създадете линия на тенденция и видите нейното доверие, можете да изберете най-добрата опция.