Regression sa Excel: equation halimbawa. linear regression

Regression analysis - isang statistical pag-aaral na paraan upang ipakita ang pagtitiwala ng isang parameter ng isa o higit pang mga independiyenteng mga variable. Sa pre-computer na panahon, ang paggamit nito ay naging sa halip mahirap, lalo na kapag ito ay dumating sa mga malalaking volume ng data. Ngayon, pag-aaral kung paano bumuo ng isang pagbabalik sa Excel, maaari mong malutas ang mga kumplikadong statistical problema sa loob lamang ng ilang minuto. Nasa ibaba ang mga tukoy na halimbawa ng economics.

uri regression

konsepto na ito ay ipinakilala sa matematika sa pamamagitan ng Francis Galton noong 1886. Regression ay:

• linear;
• parabolic;
• kapangyarihan;
• exponential;
• hyperbolic;
• exponential;
• logarithmic.

EXAMPLE 1

Isaalang-alang ang problema ng pagtukoy ng pagpapakandili ng ang bilang ng mga resignations ng mga miyembro ng kawani ng average na pasahod sa 6-industriya enterprise.

Task. Anim na mga kumpanya ay may pinag-aralan ang average na buwanang suweldo at ang bilang ng mga empleyado na mag-quit kusang-loob. Sa hugis ng mga talaan form na kami ay may:

	A	B	C
1	X	Bilang ng mga resignations	suweldo
2		y	30000 rubles
3	1	60	35000 rubles
4	2	35	40000 rubles
5	3	20	45000 rubles
6	4	20	50,000 Rubles
7	5	15	55000 rubles
8	6	15	60000 rubles

Para sa mga problema ng pagtukoy ng pagtitiwala ng mga manggagawa na halaga ng mga paghihiwalay mula sa average na suweldo para sa 6 na mga enterprise pagbabalik modelo ay ang form ng equation Y = a ₀ + ng ₁ x ₁ + ... + a _k x _k, kung saan x _i - impluwensya variable, ang isang _i - regression coefficients, ak - bilang ng mga kadahilanan.

Y para sa isang naibigay na gawain - ito ay isang tagapagpahiwatig upang sunog isang empleyado, ang isang nag-aambag factor - ang suweldo, na kung saan ay naka-denote sa pamamagitan X.

Harnessing ang kapangyarihan ng "Excel" spreadsheet

Regression analysis sa Excel ay dapat na maunahan ng isang application upang ang mga umiiral na data ng talahanayan built-in na pag-andar. Gayunpaman, para sa mga layuning ito ito ay mas mahusay na gumamit ng isang napaka-kapaki-pakinabang na add-in "packet analysis". Upang paganahin ang mga ito, kailangan mong:

• sa tab na "File" pumunta sa "Mga Setting";
• sa window na bubukas, piliin ang 'Add-ons';
• i-click sa pindutan ng "Go", na matatagpuan sa kanang ibaba ng linya "management";
• ilagay ang isang marka ng tsek sa tabi ng "Pagsusuri Toolpak" at kumpirmahin ang iyong pagkilos sa pamamagitan ng pagpindot sa "OK".

Kung nagawa nang tama, sa kanang bahagi ng tab na "Data", na matatagpuan sa itaas ng work sheet "Excel", ay nagpapakita ng mga ninanais na pindutan.

De-pagbabalik sa Excel

Ngayon na mayroon ka sa kamay ng lahat ng kinakailangang virtual kasangkapan para sa econometric mga kalkulasyon, maaari naming simulan upang matugunan ang aming mga problema. Upang gawin ito:

• pindutan ay nag-click sa "Data Analysis";
• i-click sa pindutan ng "pagbabalik" sa buksan ang bintana;
• isang tab na lalabas upang maipakilala ang isang hanay ng mga halaga Y (ang bilang ng mga paghihiwalay ng mga manggagawa) at X (ang kanilang suweldo);
• magpatibay na muli ang kanilang mga pagkilos sa pamamagitan ng pagpindot sa «Ok» button.

Bilang isang resulta, ang programa ay awtomatikong punan ang bagong sheet spreadsheet data pagbabalik pagtatasa. Bigyang-pansin! Sa Excel, may ay isang pagkakataon upang i-set ang lugar na ginusto mo para sa hangaring ito. Halimbawa, maaaring ito ay ang parehong sheet, kung saan ang mga halaga ng Y at X, o kahit isang bagong aklat, partikular na dinisenyo para sa imbakan ng mga naturang data.

resulta Regression pagtatasa para sa R-square

Ang Excel data na nakuha sa itinuturing na data halimbawa ay may form na ito:

Una sa lahat, dapat naming bigyang-pansin ang halaga ng R-squared. Kinakatawan nito ang koepisyent ng pagpapasiya. Sa halimbawang ito, R-square = 0.755 (75.5%), m. E. Ang kinakalkula parameter ng modelo upang ipaliwanag ang kaugnayan sa pagitan ng ang mga parameter itinuturing na sa pamamagitan ng 75.5%. Ang mas mataas na halaga ng koepisyent ng pagpapasiya, ang mga napiling mga modelo ay itinuturing na mas kapaki-pakinabang para sa partikular na mga gawain. Ito ay pinaniniwalaan na tama ilarawan ang tunay na sitwasyon sa R-square na halaga sa itaas 0.8. Kung ang R-square <0.5, at pagkatapos ng isang pagbabalik-aaral sa Excel ay hindi maaaring itinuturing na mga makatwirang.

ratio analysis

Numero 64.1428 nagpapakita kung ano ang magiging halaga ng Y, kung ang lahat ng mga variable xi sa aming mga modelo ay magiging reset. Sa ibang salita, maaari itong Nagtalo na ang halaga ng mga pinag-aralan ng parameter ay naiimpluwensyahan ng iba pang mga kadahilanan kaysa sa mga inilarawan sa mga tiyak na mga modelo.

Ang susunod na kadahilanan -.16285 matatagpuan sa cell B18, ay nagpapakita ng mahalagang impluwensiya ng variable X sa Y. Ito ay nangangahulugan na ang average na suweldo ng mga empleyado sa loob ng modelo ay nakakaapekto sa bilang ng mga resignations mula sa bigat ng -.16285, t. E. Ang antas ng epekto nito sa lahat maliit. Ang pag-sign "-" ay nagpapahiwatig na ang koepisyent ay negatibo. Ito ay kitang-kita, dahil namin ang lahat ng malaman na ang mas maraming mga suweldo sa enterprise, mas mababa ang mga tao na nagpahayag ng pagnanais na wakasan ang kontrata ng pagtatrabaho o dismiss.

maramihang pagbabalik

Sa ilalim ng term na ito ay tumutukoy sa mga equation pakikipag-usap sa ilang mga independiyenteng mga variable ng form:

y = f (x ₁ + x ₂ + ... x _m) + ε, kung saan y - ay isang tampok na marka (ang umaasa variable), at x _1, x _2, ... x _m - mga palatandaan salik (independent variable).

parameter pagtatantya

Para sa maramihang pagbabalik (MR) ng ito ay isinagawa gamit ang isang hindi bababa sa squares (LSM). Para sa mga linear na equation ng form Y = a + b ₁ x _{1 + ... + b m x m +} ε pagbuo ng isang sistema ng mga normal na equation (cm. Sa ibaba)

Upang maunawaan ang prinsipyo ng ang paraan, isaalang-alang natin ang dalawang-factor na kaso. Pagkatapos namin ang sitwasyon na inilarawan sa pamamagitan ng mga formula

Kaya, makuha namin ang:

kung saan σ - ay ang pag-iiba ng kanya-kanyang tampok na ito, na nakalarawan sa index.

MNC ay naaangkop sa mga equation MR sa standartiziruemom scale. Sa kasong ito, makuha namin ang equation:

kung saan t _y, t _{x 1, ...} t _xm - standartiziruemye variable na average na halaga ay 0; β _i - ulirang pagbabalik coefficients at standard na paglihis - 1.

Mangyaring tandaan na ang lahat ng β _i sa kasong ito tinukoy bilang ang naging normal at tsentraliziruemye, samakatuwid isang paghahambing sa pagitan ng isang itinuturing na wasto at katanggap-tanggap. Bilang karagdagan, ito ay tinanggap upang magsagawa ng screening ng mga kadahilanan, discarding ang mga na ang pinakamababang halaga ng βi.

Ang problema sa paggamit ng linear pagbabalik equation

Ipagpalagay na mayroon kang isang talaan ng mga dinamika ng presyo ng isang partikular na produkto N para sa huling 8 buwan. Ito ay kinakailangan upang magpasya kung ang pagkuha ng kanyang partido sa presyo ng 1850 Rubles. / T.

	A	B	C
1	buwan	pangalan ng buwan	presyo N
2	1	Enero	1750 rubles per tonelada
3	2	Pebrero	1755 rubles per tonelada
4	3	Marso	1767 rubles per tonelada
5	4	Abril	1760 rubles per tonelada
6	5	Mayo	1770 rubles per tonelada
7	6	Hunyo	1790 rubles per tonelada
8	7	Hulyo	1810 rubles per tonelada
9	8	Agosto	1840 rubles per tonelada

Upang malutas ang problemang ito sa hugis ng mga talaan processor "Excel" kinakailangan upang gamitin ang na kilala bilang halimbawa tool "Data Analysis" iniharap sa itaas. Susunod, piliin ang "Regression" na seksyon at i-set parameter. Dapat nating tandaan na sa "Input range Y» ay dapat na ipinakilala sa isang hanay ng mga halaga ng variable na umaasa (sa kasong ito ang presyo ng mga kalakal sa mga tiyak na mga buwan ng taon) at sa "Input interval X» - para sa isang independiyenteng (ang buwan). nakumpirma naming ang aksyon sa pamamagitan ng pag-click «Ok». Sa isang bagong worksheet (kung gayon ipinahiwatig), makuha namin ang data para sa pagbabalik.

Kami ay pagbuo sa kanila linear equation ng form y = palakol + b, kung saan ang bilang ng mga parameter ng isang at b ang mga coefficients mula sa numero ng linya ng buwan at pangalan ng coefficients at «Y-intersection" line ng sheet sa mga resulta ng pagbabalik pagtatasa. Kaya, ang mga linear pagbabalik equation (EQ) 3 para sa ang problema ay maaaring nakasulat na bilang:

Ang presyo ng mga kalakal N = 11,714 * 1727.54 buwan number +.

o sa algebraic notasyon

y = 11,714 x + 1727,54

pagtatasa ng mga resulta

Upang magpasya kung ang mga natanggap sapat linear pagbabalik equation gamit ang maramihang mga ugnayan coefficients (CMC) at pagpapasiya pati na rin ang test at ni Fisher t-test. Sa talahanayang "Excel" pagbabalik sa mga resulta kumilos sila sa ilalim ng pangalan ng maraming R, R-Square, F-t-istatistika at mga istatistika, ayon sa pagkakabanggit.

KMC R ay nagbibigay-daan upang matantya ang pagkakalapit probabilistic relasyon sa pagitan ng malaya at umaasa variable. Nito mataas na halaga ay nagpapahiwatig ng isang malakas na sapat na koneksyon sa pagitan ng mga variable na "Number ng buwan" at "N Presyo ng produkto sa rubles bawat 1 tonelada." Gayunpaman, ang likas na katangian ng relasyon na ito ay hindi kilala.

Ang plaza ng koepisyent ng pagpapasiya R ² (RI) ay isang numeric katangian ng ang bahagdan ng kabuuang mga scatter at nagpapakita ng isang scatter ng pang-eksperimentong bahagi ng data, ibig sabihin, mga halaga ng variable na umaasa naaayon sa isang linear pagbabalik equation. Sa problemang ito, ang halaga na ito ay 84.8%, mp. E. Statistics na may isang mataas na antas ng kawastuhan nakuha ay inilarawan SD.

F-istatistika, na kilala rin bilang Fisher criterion na ginagamit upang masuri ang kahalagahan ng linear pagtitiwala o disproving hypothesis na nagpapatunay sa pagkakaroon nito.

Ang halaga ng t-istatistika (t test Estudyante) ay tumutulong sa pag-aralan ang kahalagahan ng ang koepisyent sa anumang libreng kilalang linear miyembro pagpapakandili. Kung ang halaga ng t-test> t _cr, ang teorya ng isang linear equation kawalan ng kabuluhan ng libreng term ay tinanggihan.

Sa problemang ito para sa isang libreng matagalang pamamagitan ng mga instrumento "Excel" ito ay natagpuan na ang t = 169,20903, at p = 2,89E-12, t. E. Magkaroon ng isang zero posibilidad na ang tapat na tatanggihan ang teorya ng kawalan ng halaga sa mga libreng term. Para sa hindi kilalang koepisyent sa t = 5,79405, at p = 0,001158. Sa ibang salita, ang probabilidad na ang isang tinanggihan wastong teorya ay kawalan ng halaga ng koepisyent para sa hindi kilala, ay 0.12%.

Kaya, maaari itong Nagtalo na ang nakuha linear pagbabalik equation sapat.

Ang problema ng advisability ng pagbili ng namamahagi

Maramihang pagbabalik ay ginanap sa Excel gamit ang parehong "Data Analysis" tool. Isaalang-alang ang mga tiyak na aplikasyon.

Gabay kumpanya «NNN» ay kailangang magdesisyon kung upang bumili ng 20% ng namamahagi ng JSC «MMM». Package presyo (SP) ay 70 milyong US dollars. Dalubhasa ng «NNN» nakolektang data sa mga katulad na mga transaksyon. Ito ay nagpasya upang masuri ang halaga ng namamahagi sa mga naturang mga parameter, na ipinahayag sa milyon-milyong mga US dollars, tulad ng:

• payables (VK);
• taunang pagbabalik ng puhunan ng lakas ng tunog (VO);
• receivables (VD);
• halaga ng fixed asset (SOF).

Bilang karagdagan, gamitin ang mga utang na sahod ng negosyo (V3 U) sa libu-libong US dollars.

Ang desisyon mesa processor Excel ibig sabihin nito

Una kailangan mong lumikha ng isang talahanayan ng data input. Ito ay ang mga sumusunod:

Next:

• call box "na pagsusuri ng data";
• napiling "Regression" na seksyon;
• window "Input interval Y» pinangangasiwaan hanay umaasa variable halaga mula column G;
• click sa icon na may pulang arrow sa kanan ng window ng "Input interval X» at ihiwalay sa isang sheet na hanay ng lahat ng mga halaga ng haligi B, C, D, F.

Markahan ang point "Bagong worksheet" at i-click ang "Ok".

Kumuha ng isang pagbabalik-aaral para sa gawaing ito.

Ang mga resulta ng pag-aaral at konklusyon

"Kolektahin" bilugan mula sa data na ipinakita sa itaas sa sheet talahanayan Excel processor pagbabalik equation:

SD = 0.103 * SOF + 0541 * VO - 0031 * VK + 0405 * VD + 0691 * VZP - 265,844.

Sa mga mas karaniwang mathematical form na ito ay maaaring nakasulat na bilang:

y = 0103 * x1 + 0541 * x2 - 0031 * x3 + 0405 * x4 + 0691 * x5 - 265,844

Ang data para sa «MMM» JSC iniharap sa talahanayan sa ibaba:

SOF, USD	VO, USD	VK, USD	VD, USD	VZP, USD	JV, USD
102.5	535.5	45.2	41.5	21.55	64.72

Substituting sila sa pagbabalik equation, na nakuha ng isang pigura ng 64,720,000 US dollars. Nangangahulugan ito na ang pagbabahagi ng JSC «MMM» ay hindi dapat bumili, dahil ang kanilang mga gastos ay lubos na overpriced sa 70 milyong US dollars.

Tulad ng iyong nakikita, ang paggamit ng spreadsheet "Excel" at ang pagbabalik equation pinahihintulutan na gumawa ng kaalamang desisyon tungkol sa advisability lubos na tiyak na transaksyon.

Ngayon alam mo kung ano ang isang pagbabalik. Mga Halimbawa sa Excel, tinalakay sa itaas, ay makakatulong sa iyo sa paglutas ng mga praktikal na mga problema ng econometrics.