EXCEL APPROXIMATION

Kabilang sa iba't ibang mga paraan ng pagtataya ay imposible na hindi makilala ang approximation. Sa tulong nito, maaari kang gumawa ng mga tinatayang kalkulasyon at kalkulahin ang nakaplanong mga tagapagpahiwatig sa pamamagitan ng pagpapalit sa orihinal na mga bagay na may mas simpleng mga bago. Sa Excel, mayroon ding posibilidad na gamitin ang pamamaraang ito para sa pagtataya at pag-aaral. Tingnan natin kung paano maaaring gamitin ang pamamaraang ito sa tinukoy na programa na may built-in na mga tool.

Pagpapatupad ng approximation

Ang pangalan ng pamamaraang ito ay nagmumula sa salitang Latin na proxima - "pinakamalapit." Ito ay isang pagtatantya sa pamamagitan ng pagpapasimple at paghawak ng mga kilalang tagapagpahiwatig, pagbubuo ng mga ito sa isang trend at ang batayan nito. Ngunit ang paraan na ito ay maaaring gamitin hindi lamang para sa pagtataya, kundi pati na rin para sa pagsasaliksik ng mga umiiral na resulta. Pagkatapos ng lahat, ang approximation ay, sa katunayan, isang pagpapadali ng orihinal na data, at isang pinasimple na bersyon ay mas madaling galugarin.

Ang pangunahing tool kung saan ang smoothing ay isinasagawa sa Excel ay ang pagtatayo ng trend line. Sa ilalim na linya ay, batay sa mga tagapagpahiwatig na magagamit, ang iskedyul ng function ay nakumpleto para sa hinaharap na mga panahon. Ang pangunahing layunin ng trend line, dahil hindi ito mahirap hulaan, ay gumagawa ng mga pagtataya o pagkilala sa pangkalahatang kalakaran.

Ngunit maaari itong itayo gamit ang isa sa limang uri ng pagtatantya:

Linear;
Pagpaparami;
Logarithmic;
Polinomyal;
Kapangyarihan.

Isaalang-alang ang bawat isa sa mga opsyon nang mas detalyado nang hiwalay.

Aralin: Paano bumuo ng trend ng linya sa Excel

Paraan 1: Linear Smoothing

Una sa lahat, isaalang-alang natin ang pinakamadaling approximation, lalo, gamit ang isang linear function. Matututunan natin ito nang mas detalyado, dahil itinatakda natin ang pangkalahatang mga punto na katangian ng iba pang mga pamamaraan, katulad, paglalagay at ilang iba pang mga nuances na hindi natin mapapanatili kapag isinasaalang-alang ang mga susunod na pagpipilian.

Una sa lahat, magtatayo tayo ng isang graph batay sa kung saan gagawin natin ang pagpapatakbo ng smoothing. Upang bumuo ng isang graph, kumuha kami ng isang talahanayan na kung saan ang yunit ng gastos ng produksyon na ginawa ng isang enterprise at ang kaukulang kita sa isang naibigay na panahon ay ipinahiwatig buwanan. Ang graphical na function na binuo namin ay sumasalamin sa pagtitiwala ng pagtaas sa kita sa pagbawas sa gastos ng produksyon.

Upang bumuo ng graph, una sa lahat, piliin ang mga haligi "Unit cost of production" at "Profit". Pagkatapos na ilipat sa tab "Ipasok". Susunod sa laso sa bloke ng tool na "Mga diagram" ay mag-click sa pindutan "Spot". Sa listahan na bubukas, piliin ang pangalan "Dot na may makinis na mga curve at marker". Ito ang uri ng mga tsart na pinaka-angkop para sa pagtatrabaho sa trend line, at samakatuwid, para sa pag-aaplay ng paraan ng pagtatantya sa Excel.

Itinayo ang iskedyul.

Upang magdagdag ng trend line, piliin ito sa pamamagitan ng pag-click sa kanang pindutan ng mouse. Lumilitaw ang isang menu ng konteksto. Pumili ng isang item sa loob nito "Magdagdag ng trend line ...".
May isa pang opsyon na idagdag ito. Sa isang karagdagang pangkat ng mga tab sa laso "Paggawa gamit ang Mga Tsart" lumipat sa tab "Layout". Susunod sa toolbox "Pagsusuri" mag-click sa pindutan "Trend line". Ang isang listahan ay bubukas. Dahil kailangan nating maglapat ng isang linear approximation, mula sa mga iniharap na posisyon na pinili natin "Linear approximation".

Kung, gayunpaman, pinili mo ang unang opsyon ng mga aksyon sa karagdagan sa pamamagitan ng menu ng konteksto, pagkatapos ay bubuksan ang format na window.
Sa block ng parameter "Pagbuo ng trend line (approximation and smoothing)" itakda ang switch sa posisyon "Linear".
Kung ninanais, maaari kang magtakda ng isang tick malapit sa posisyon "Ipakita ang equation sa chart". Pagkatapos nito, ipapakita ng diagram ang pagpapahid na equation ng function.
Gayundin sa aming kaso, para sa paghahambing ng iba't ibang mga opsyon na approximation, mahalagang suriin ang kahon "Ilagay sa tsart ang halaga ng isang maaasahang approximation (R ^ 2)". Maaaring mag-iba ang indicator na ito 0 hanggang sa 1. Ang mas mataas na ito ay, ang mas mahusay na approximation (mas maaasahan). Ito ay naniniwala na kapag ang halaga ng tagapagpahiwatig na ito 0,85 at ang mas mataas na smoothing ay maaaring ituring na maaasahan, at kung ang figure ay mas mababa, pagkatapos - hindi.
Matapos mo na ang lahat ng mga setting sa itaas. Pinindot namin ang pindutan "Isara"inilagay sa ilalim ng window.

Tulad ng makikita mo, ang trend line ay naka-plot sa tsart. Sa kaso ng linear approximation, ito ay ipinahiwatig ng isang itim na tuwid na linya. Ang ganitong uri ng smoothing ay maaaring ilapat sa mga pinaka-simpleng kaso, kapag ang data ay nagbabago nang masyadong mabilis at ang pag-asa ng halaga ng pag-andar sa argumento ay halata.

Ang smoothing, na ginagamit sa kasong ito, ay inilarawan ng sumusunod na formula:

y = palakol + b

Sa aming partikular na kaso, ang formula ay tumatagal ng sumusunod na form:

y = -0.1156x + 72.255

Ang laki ng katumpakan ng approximation ay katumbas ng sa amin 0,9418, na kung saan ay isang medyo katanggap-tanggap resulta, na naglalarawan ng smoothing bilang maaasahan.

Paraan 2: Ang pagpaparami ng pagpaparami

Ngayon isaalang-alang natin ang exponential na uri ng approximation sa Excel.

Upang baguhin ang uri ng trend line, piliin ito sa pamamagitan ng pag-click sa kanang pindutan ng mouse at sa drop-down na menu piliin ang item "Trend line format ...".

Pagkatapos nito, inilunsad ang format na window na pamilyar sa amin. Sa block para sa pagpili ng uri ng approximation, itakda ang switch sa "Ang pagpaparami". Ang natitirang mga setting ay mananatiling pareho sa unang kaso. Mag-click sa pindutan "Isara".

Pagkatapos nito, ang trend line ay isasagawa. Tulad ng iyong nakikita, kapag ginagamit ang pamamaraang ito, mayroon itong bahagyang hubog na hugis. Ang antas ng kumpyansa ay 0,9592, na mas mataas kaysa sa paggamit ng linear approximation. Ang paraan ng pagpaparami ay pinakamahusay na ginagamit kapag ang mga halaga ay unang mabilis na nagbabago at pagkatapos ay kumuha ng balanseng form.

Ang pangkalahatang view ng pag-andar ng smoothing ay ang mga sumusunod:

y = be ^ x

kung saan e - ito ang batayan ng likas na logarithm.

Sa aming partikular na kaso, kinuha ng formula ang sumusunod na form:

y = 6282.7 * e ^ (- 0.012 * x)

Paraan 3: Mag-log Smoothing

Ngayon ito ay ang pagliko upang isaalang-alang ang paraan ng logarithmic approximation.

Sa parehong paraan tulad ng sa nakaraang panahon, sa pamamagitan ng menu ng konteksto, ilunsad ang window ng format ng trend ng linya. Itakda ang switch sa posisyon "Logarithmic" at mag-click sa pindutan "Isara".

May isang trend line building procedure na may isang logarithmic approximation. Tulad ng sa nakaraang kaso, ang pagpipiliang ito ay mas mahusay na gamitin kapag ang data ay unang nagbago mabilis, at pagkatapos ay kumuha ng balanseng hitsura. Tulad ng makikita mo, ang antas ng kumpyansa ay 0.946. Ito ay mas mataas kaysa sa paggamit ng linear na paraan, ngunit mas mababa kaysa sa kalidad ng trend line na may exponential smoothing.

Sa pangkalahatan, ganito ang hitsura ng smoothing formula:

y = a * ln (x) + b

kung saan ln ang kalakhan ng likas na logarithm. Kaya ang pangalan ng pamamaraan.

Sa aming kaso, ang formula ay tumatagal ng sumusunod na form:

y = -62,81ln (x) +404.96

Paraan 4: polinomyal na smoothing

Panahon na upang isaalang-alang ang paraan ng pag-smoothing ng polinomyal.

Pumunta sa window ng format ng trend ng linya, tulad ng nagawa mo na nang higit sa isang beses. Sa block "Building a trend line" itakda ang switch sa posisyon "Polynomial". Sa kanan ng item na ito ay isang patlang "Degree". Kapag pumipili "Polynomial" ito ay nagiging aktibo. Dito maaari mong tukuyin ang anumang halaga ng kapangyarihan mula sa 2 (itakda bilang default) sa 6. Tinutukoy ng tagapagpahiwatig na ito ang bilang ng maxima at minima ng function. Kapag nag-i-install ng isang pangalawang degree na polynomial, isa lamang ang maximum na inilarawan, at kapag naka-install ang isang ika-anim na polinomyal na antas, hanggang sa limang maximum na maaaring maipaliwanag. Upang magsimula, iwanan namin ang mga default na setting, iyon ay, tinutukoy namin ang pangalawang degree. Ang natitirang mga setting ay mananatiling pareho ng itinakda namin ang mga ito sa mga nakaraang pamamaraan. Pinindot namin ang pindutan "Isara".

Ang trend ng linya gamit ang pamamaraang ito ay binuo. Tulad ng makikita mo, ito ay higit pang mga kurbado kaysa sa kapag gumagamit ng pagpaparami ng pagtatantya. Ang antas ng kumpyansa ay mas mataas kaysa sa alinman sa mga naunang ginamit na pamamaraan, at ito ay 0,9724.
Ang pamamaraan na ito ay maaaring matagumpay na inilapat kung ang data ay patuloy na nagbabago. Ang function na naglalarawan sa ganitong uri ng smoothing ganito ang hitsura nito:
y = a1 + a1 * x + a2 * x ^ 2 + ... + an * x ^ n
Sa aming kaso, kinuha ng formula ang sumusunod na form:
y = 0.0015 * x ^ 2-1.7202 * x + 507.01
Ngayon ay baguhin natin ang antas ng mga polynomials upang makita kung ang resulta ay naiiba. Bumalik kami sa window ng format. Ang uri ng approximation ay naiwan sa polinomyal, ngunit sa harap nito sa degree window itinakda namin ang maximum na posibleng halaga - 6.

Tulad ng makikita mo, pagkatapos nito, ang aming trend line ay kinuha ang anyo ng isang malinaw na curve, kung saan ang bilang ng mga highs ay anim. Ang antas ng kumpyansa ay nagdaragdag ng higit pa, paggawa 0,9844.

Ang formula na naglalarawan sa ganitong uri ng smoothing, kinuha ang sumusunod na form:

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

Paraan 5: Power Smoothing

Sa pagtatapos, isaalang-alang ang paraan ng pagtatantya ng kapangyarihan sa Excel.

Ilipat sa window "Trend Line Format". Itakda ang switch ng smoothing view sa posisyon "Kapangyarihan". Ipinapakita ang antas ng equation at confidence, gaya ng lagi, iwanan ito. Pinindot namin ang pindutan "Isara".

Ang programa ay bumubuo ng trend line. Tulad ng makikita mo, sa aming kaso, ito ay isang linya na may kaunting liko. Ang antas ng kumpyansa ay 0,9618na kung saan ay lubos na isang mataas na pigura. Sa lahat ng mga pamamaraan na inilarawan sa itaas, mas mataas ang antas ng kumpyansa kapag ginagamit ang polynomial na paraan.

Epektibong ginagamit ang pamamaraang ito sa mga kaso ng masinsinang pagbabago sa data ng pag-andar. Mahalagang tandaan na ang opsyon na ito ay naaangkop lamang kung ang pag-andar at ang argument ay hindi tumatanggap ng mga negatibong o zero na halaga.

Ang pangkalahatang pormula na naglalarawan sa pamamaraang ito ay ang mga sumusunod:

y = bx ^ n

Sa aming partikular na kaso, mukhang ganito:

y = 6E + 18x ^ (- 6.512)

Tulad ng makikita mo, kapag ginagamit ang partikular na data na ginamit namin para sa halimbawa, ang polinomyal na paraan ng pagtatantya sa polinomyal sa ikaanim na antas (0,9844), ang pinakamababang antas ng kumpiyansa sa paraan ng linear (0,9418). Ngunit ito ay hindi nangangahulugan na ang parehong pagkahilig ay magiging kapag gumagamit ng iba pang mga halimbawa. Hindi, ang antas ng kahusayan ng mga pamamaraan sa itaas ay maaaring magkakaiba, depende sa tiyak na uri ng pag-andar na kung saan ang linya ng trend ay itatayo. Samakatuwid, kung ang napiling pamamaraan ay pinaka-epektibo para sa pagpapaandar na ito, hindi ito nangangahulugan na ito ay magiging sulit din sa ibang sitwasyon.

Kung hindi mo agad matukoy, batay sa mga rekomendasyon sa itaas, anong uri ng approximation ang magkasya partikular sa iyong kaso, pagkatapos ay makatuwiran upang subukan ang lahat ng mga pamamaraan. Pagkatapos ng pagbuo ng trend line at pagtingin sa antas ng tiwala nito, maaari mong piliin ang pinakamahusay na pagpipilian.