Regression di Excel: conto persamaan. régrési liniér

analisa regresi - metoda ulikan statistik pikeun nuduhkeun gumantungna tina parameter tina salah sahiji atawa leuwih bebas variabel. Di jaman pra-komputer, pamakéan na geus rada hese, utamana lamun eta sumping ka jilid badag data. Dinten, diajar kumaha carana ngawangun hiji regression di Excel, anjeun tiasa ngajawab masalah statistical kompléks di ngan sababaraha menit. Di handap ieu aya conto husus ékonomi.

jenis regression

Konsep ieu dipikawanohkeun ka Matematika ku Francis Galton dina 1886. Regression is:

• liniér;
• parabolic;
• kakuatan;
• eksponensial;
• hyperbolic;
• eksponensial;
• logaritmik.

conto 1

Mertimbangkeun masalah nangtukeun gumantungna teh Jumlah resignations anggota staf di upah rata dina 6 usaha industri.

Tugas. Genep pausahaan geus dianalisis gaji bulanan rata jeung jumlah karyawan anu kaluar sukarela. Dina formulir tabular kami kudu:

	A	B	C
1	X	Jumlah resignations	bayaran
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

Pikeun masalah nangtukeun gumantungna sahiji pagawe separations Jumlah ti gaji rata pikeun modél regression 6 usaha boga bentuk persamaan Y = a ₀ + a ₁ x ₁ + ... + a _k x _k, dimana x _i - influencing variabel, a _abdi - regression koefisien, ak - jumlah faktor.

Y pikeun tugas dibikeun - eta mangrupa indikator kana seuneu hiji pagawe, faktor contributing - gaji, anu dilambangkeun ku X.

Harnessing kakawasaan "Excel" spreadsheet

analisa regresi di Excel kudu dimimitian ku hiji aplikasi nepi ka data tabél aya diwangun-di fungsi. Najan kitu, pikeun tujuan ieu éta hadé pikeun ngagunakeun nambihkeun di "analisis pakét" pisan mangpaat. Pikeun ngaktipkeun eta, anjeun perlu:

• kalawan tab "Gambar" buka "Setélan";
• dina jandéla nu muka, pilih 'Tambahkeun-ons';
• klik dina tombol "Pindah", ayana di katuhu handap garis "manajemen";
• nempatkeun hiji tanda centang nu aya digigireun "Analisis ToolPak" na mastikeun Peta anjeun ku mencét "Heug".

Mun dipigawe neuleu, sisi katuhu tab "Data", ayana di luhur lambaran karya "Excel", nembongkeun tombol dipikahoyong.

Liniér Regression di Excel

Ayeuna nu gaduh on leungeun sakabeh parabot maya nu dipikabutuh pikeun itungan econometric, urang tiasa ngawitan alamat masalah urang. Jang ngalampahkeun ieu:

• Tombol ieu diklik dina "Analisis Data";
• klik dina tombol "regression" dina jandéla muka;
• a tab nu nembongan pikeun ngawanohkeun sauntuyan nilai Y (jumlah pagawé separations) jeung X (gaji maranéhanana);
• reaffirm lampah ku cara mencét «Ok» tombol.

Hasilna, program anu otomatis bakal ngeusian analisis régrési data lambar spreadsheet anyar. Nengetan! Dina Excel, aya kasempetan pikeun ngeset tempat nu resep keur kaperluan ieu. Contona, meureun nya lambaran anu sarua, dimana nilai Y sarta X, atawa malah hiji buku anyar, husus dirancang pikeun neundeun data sapertos.

Hasil analisa regresi keur Sunda-kuadrat

Data Excel diala dina conto data dianggap mibanda bentuk:

Munggaran sadaya, urang kedah nengetan kana nilai Sunda-kuadrat. Ieu ngagambarkeun koefisien tina tekad. Dina conto ieu, Sunda-kuadrat = 0.755 (75,5%), m. E. parameter The diitung model pikeun ngajelaskeun hubungan antara parameter dianggap ku 75,5%. Nu leuwih luhur pangaji koefisien tina tekad, modél dipilih dianggap leuwih mangpaat pikeun tugas nu tangtu. Hal ieu dipercaya mun neuleu ngajelaskeun kaayaan real di nilai Sunda-kuadrat luhur 0,8. Lamun Sunda-kuadrat <0,5, teras analisis régrési di Excel bisa dianggap lumrah.

analisis rasio

Jumlah 64,1428 mintonkeun naon bakal nilai Y, lamun sakabeh variabel xi dina modél urang bakal reset. Dina basa sejen, eta bisa pamadegan yen nilai parameter dianalisis anu dipangaruhan ku faktor sejen ti eta dijelaskeun dina modél husus.

Faktor hareup -0,16285 lokasina di B18 sél, nembongkeun pangaruh penting variabel X jeung Y. Ieu ngandung harti yén gaji rata-rata karyawan dina modél mangaruhan Jumlah resignations ti beurat -0,16285, t. E. Derajat dampak na pisan leutik. Tanda "-" nunjukkeun yén koefisien nyaeta négatip. Éta atra, saprak urang sadayana terang yén leuwih gaji di perusahaan, nu kirang urang geus dikedalkeun kahayang pikeun nungtungan kontrak pagawean atawa mecat.

regression sababaraha

Dina istilah ieu nujul kana persamaan komunikasi sareng sababaraha variabel bebas bentuk:

y = f (x ₁ + x ₂ + ... x _m) + ε, dimana y - mangrupakeun skor fitur (variabel terikat), sarta x _1, x _2, ... x _m - tanda faktor (variabel bebas).

estimasi parameter

Pikeun regression sababaraha (Bapak) eta anu dipigawé maké metoda kuadrat sahenteuna (LSM). Pikeun persamaan linier tina formulir Y = a + b ₁ x _{1 + ... + b m x m +} ε ngawangun sistem tina persamaan normal (cm. Handap)

Ngartos prinsip metoda, anggap we kasus dua-faktor. Lajeng kami geus kaayaan digambarkeun ku rumus

Mangkana, kami ménta:

numana σ - nyaeta varian ti fitur masing-masing, reflected dina indéks dina.

Ahérat téh lumaku kana persamaan Bapak ka standartiziruemom skala. Dina hal ieu, urang meunang kasaruan:

wherein t _y, t _{x 1, ...} t _xm - standartiziruemye variabel nu nilai average nu 0; β _i - koefisien regression standardized jeung simpangan baku - 1.

Perhatikeun yén sadaya β _i dina hal ieu ditetepkeun minangka dinormalisasi na tsentraliziruemye, kituna perbandingan antara hiji dianggap valid tur ditarima. Sajaba ti éta, katampi pikeun ngalakonan screening faktor, discarding maranéhanana anu boga nilai panghandapna tina βi.

Masalah sareng ngagunakeun persamaan régrési liniér

Anggap anjeun gaduh daptar dinamika harga produk N hususna pikeun 8 bulan panungtungan. Perlu mutuskeun mangrupakeun akusisi ti pihak na di harga 1850 rubles. / T.

	A	B	C
1	bulan	ngaran bulan	harga N
2	1	Januari	1750 rubles per ton
3	2	Pebruari	1755 rubles per ton
4	3	Maret	1767 rubles per ton
5	4	April	1760 rubles per ton
6	5	Mei	1770 rubles per ton
7	6	Juni	1790 rubles per ton
8	7	Juli	1810 rubles per ton
9	8	Agustus	1840 rubles per ton

Pikeun ngajawab masalah ieu di tabular processor "Excel" diperlukeun ngagunakeun geus dipikawanoh contona alat "Analisis Data" dibere luhur. Salajengna, milih "Regression" bagian tur nyetel parameter. Urang kudu inget yen di "rentang Input Y» kudu diwanohkeun ka sauntuyan nilai variabel terikat (dina hal ieu harga barang di bulan husus taun) jeung dina "Input interval X» - pikeun hiji bebas (bulan). Urang pastikeun peta ku ngaklik «Ok». Dina LKS anyar (mun kitu dituduhkeun), urang ménta data pikeun regression anu.

Kami gedong on aranjeunna persamaan linier tina formulir y = kampak + b, dimana jadi parameter a jeung b ngarupakeun koefisien tina jumlah garis bulan jeung ngaran tina koefisien na «Y-simpang" garis sahiji kolom lambaran kalawan hasil analisis régrési. Ku kituna, persamaan linear regression (EQ) 3 pikeun masalah bisa ditulis salaku:

Harga barang N = 11,714 * 1727.54 bulan angka +.

atanapi aljabar notasi

y = 11.714 x + 1727,54

analisis hasil

Mutuskeun naha ditampi persamaan regression adequately linier ngagunakeun sababaraha koefisien korelasi (CMC) jeung tekad sakumaha ogé test na Fisher-t test. Dina tabel "Excel" regression ku hasil aranjeunna meta dina ngaran sababaraha Sunda, Urang Sunda-alun, F-t-statistik sarta statistik, mungguh.

KMC Sunda nyandak keur estimasi closeness hubungan probabilistik antara variabel bebas jeung gumantung. nilai tinggi na nunjukkeun sambungan cukup kuat antara "Jumlah bulan" variable jeung "harga Produk N di rubles per 1 ton". Sanajan kitu, sifat hubungan ieu téh kanyahoan.

Kuadrat tina koefisien tina tekad Sunda ² (RI) nyaéta karakteristik numerik tina proporsi total paburencay tur nembongkeun paburencay tina bagian data eksperimen, i.e., nilai variabel terikat pakait ka persamaan régrési liniér. Dina masalah ieu, nilai ieu 84,8%, MP. E. Statistik sareng gelar luhur ti akurasi diala digambarkeun SD.

F-statistik, ogé katelah kriteria Fisher dipaké pikeun assess significance tina gumantungna linear atawa disproving hipotesa confirming ayana na.

Nilai t-statistik (uji t student) ngabantuan evaluate signifikan tina koefisien nu iraha wae bébas anggota gumantungna linier kanyahoan. Lamun nilai t-test> t _Cr, hipotesa tina persamaan insignificance linier istilah bébas ditolak.

Dina masalah ieu pikeun istilah bébas ngaliwatan instrumen "Excel" ieu kapanggih yén t = 169,20903, sarta p = 2,89E-12, t. E. Boga probability enol nu satia bakal jadi ditampik null tina insignificance tina istilah gratis. Pikeun koefisien kanyahoan di t = 5,79405, sarta p = 0,001158. Dina basa sejen, probabiliti yen null bener ditampik bakal insignificance tina koefisien keur kanyahoan, nyaeta 0,12%.

Ku kituna, bisa jadi pamadegan yén diala persamaan régrési liniér adequately.

Masalah nu advisability meuli saham

regression sababaraha ieu dipigawé dina Excel maké "Analisis Data" sarua alat nu. Mertimbangkeun aplikasi husus.

Guide parusahaan «NNN» kedah mutuskeun mésér 20% biasa tina JSC «mmm». harga paket (SP) nyaéta 70 juta dollar AS. Spesialis tina «NNN» dikumpulkeun data dina transaksi sarupa. Ieu mutuskeun pikeun assess nilai biasa dina parameter misalna, ditembongkeun dina jutaan dollar AS, kayaning:

• payables (VK);
• volume elehan taunan (VO);
• receivables (VD);
• nilai aset dibereskeun (SOF).

Sajaba ti éta, nganggo hutang upah tina usaha (V3 U) dina rébuan dollar AS.

Prosesor tabel kaputusan hartosna Excel

Kahiji maneh kudu nyieun tabel data input. Ieu saperti kieu:

salajengna:

• kotak panggero "analisis data";
• dipilih "Regression" bagian;
• jandéla "Input interval Y» dikaluarkeun rentang gumantung variabel nilai tina kolom G;
• klik ikon ku panah beureum ka katuhu tina jandela "Input interval X» tur diisolasi dina rentang lambar sakabeh nilai kolom B, C, D, F.

Cirian titik "New LKS" teras klik "Ok".

Meunang analisis régrési keur tugas ieu.

Hasil ulikan jeung conclusions

"Kumpulkeun" rounded tina data dibere luhur dina persamaan processor Excel regression tabel lambar:

SD = 0,103 * SOF + 0,541 * VO - 0.031 * VK + 0,405 * VD + 0,691 * VZP - 265.844.

Dina bentuk matematik langkung dawam éta bisa ditulis salaku:

y = 0.103 * x1 + 0,541 * x2 - 0.031 * x3 + 0,405 * x4 + 0,691 * x5 - 265.844

Data keur «mmm» JSC dibere dina tabel di handap:

SOF, Rp	VO, Rp	VK, Rp	VD, Rp	VZP, Rp	JV, Rp
102,5	535,5	45,2	41,5	21,55	64,72

Ngaganti kana persamaan regression, dicandak hiji sosok 64,72 juta dollar AS. Ieu ngandung harti yén biasa tina JSC «mmm» teu kudu meuli, lantaran biaya maranéhanana rada overpriced dina 70 juta dollar AS.

Salaku bisa ningali, pamakéan spreadsheet "Excel" jeung persamaan regression diwenangkeun nyieun kaputusan informed ngeunaan advisability urus rada husus.

Ayeuna anjeun terang naon regression a. Conto mun Excel, dibahas di luhur, baris nulungan anjeun ngarengsekeun masalah praktis ékonométri.