Denklem örneklerde: Excel içinde Regresyon. doğrusal regresyon

Regresyon analizi, - bir veya daha fazla bağımsız değişkenlerin bir parametre olan bağımlılığını göstermektedir için istatistiksel bir çalışma yöntemi. Önceden bilgisayar çağında, kullanmasının, veri geniş hacimli geldi, özellikle oldukça zor olmuştur. Bugün, Excel'de bir gerileme inşa etmek için öğrenme, sadece birkaç dakika içinde karmaşık istatistiksel sorunları çözebilir. Aşağıda ekonomi spesifik örnekleridir.

regresyon türleri

Bu kavram matematik tanıtıldı Francis Galton tarafından 1886 yılında. Gerileme geçerli:

• doğrusal;
• parabolik;
• güç;
• üstel;
• hiperbolik;
• üstel;
• logaritmik.

Örnek 1

6 sanayi işletmelerde ortalama ücretin personelin üyelerinin istifa sayısının bağımlılığını ölçülmesi sorununu ele alalım.

Görev. Altı şirketleri aylık ortalama maaş ve gönüllü çıkın çalışan sayısını analiz ettik. tablo şeklinde Elimizdeki:

	bir	B	C
1	X	istifalar sayısı	maaş
2		y	30000 ruble
3	1	60	35.000 ruble
4	2	35	40000 ruble
5	3	20	45000 ruble
6	4	20	50.000 ruble
7	5	15	55.000 ruble
8	6	15	60000 ruble

6 işletmeler regresyon modeli için ortalama maaş miktarı ayırma işçi bağımlılığı saptanması problemine için Y = denkleminin şekline sahip bir ₀ + ₁ x ₁ + ... + _{k xk,} burada x _i - etki değerleri, bir _I - regresyon katsayısı, ak - faktörlerin sayısı.

Y, belirli bir görev için - maaş, X tarafından gösterilir - bir çalışan yangın bir göstergesi, bir faktördür

"Excel" elektronik tablonun gücünü

Excel'de regresyon analizi yerleşik işlevleri mevcut tablo verilerine bir uygulama tarafından gelmelidir. Bununla birlikte, bu amaçlar için çok yararlı bir eklenti "paket analizi" kullanımı daha iyidir. Bu özelliği etkinleştirmek için yapmanız gerekenler:

• sekme "Dosya" ile "Ayarlar" gidin;
• Açılan pencerede, 'Add-ons' seçeneğini;
• hat "yönetim" sağ alt kısmında bulunan düğme "git" üzerine tıklayın;
• "ToolPak" seçeneğinin yanındaki onay işareti koymak ve "Tamam" tuşuna basarak işlemi onaylayın.

Doğru şekilde yapılırsa, iş levha "Excel" üzerinde bulunan "Data" sekmesinin sağ tarafında, istenilen düğmeye gösterir.

Excel'de Doğrusal regresyon

Şimdi ise ekonometrik hesaplamalar için gerekli tüm sanal araçlara sahip olduğu, bizim sorunu çözmek için başlayabilir. Bunun için:

• düğmesi "Veri Analizi" tıklanır;
• açık pencereden düğmesi "gerileme" yi tıklayın;
• bir değer aralığı tanıtmak için görünen bir sekme Y'nin (ayırma işçi sayısı) ve X (bunların maaş);
• «Tamam» butonuna basarak eylemlerini teyit ederler.

Sonuç olarak, program otomatik olarak yeni sac tablo veri regresyon analizi dolduracaktır. Dikkat! Excel'de, bu amaç için tercih yeri ayarlamak için bir fırsat vardır. Örneğin, bu değerlerin, Y ve X, ya da yeni bir kitap, özel olarak bu tür veri depolama için tasarlanmış aynı tabaka olabilir.

R-kare için regresyon analizi sonuçları

Dikkate alınan bir örnekte verilerin elde edilen Excel veri formu vardır:

Her şeyden önce, biz Ar-kare değerine dikkat edilmelidir. Bu belirleme katsayısını temsil eder. Bu örnekte, R-kare = 0.755 (% 75.5) oluştu. E. modeli hesaplanan parametreler% 75.5 tarafından dikkate parametreler arasındaki ilişkiyi açıklamak. belirleme katsayısı daha yüksek bir değer, seçilen model, belirli görevleri için daha faydalı olduğu düşünülmektedir. Doğru 0.8, yukarıda R-kare değerleri ile gerçek durumu tarif etmek için düşünülmektedir. R-kare <0.5, daha sonra Excel'de bir regresyon analizi makul kabul edilemezse.

oran analizi

Numara 64,1428 bizim modeldeki tüm değişkenler xi sıfırlanır eğer Y değerini ne olacağını gösterir. Diğer bir deyişle, analiz parametresinin değeri, belirli bir model tarif edilenler dışındaki faktörlerden etkilenir olduğu söylenebilir.

Hücre B18 bulunan bir diğer faktör de -,16285, X değişkeninin önemli etkisi Bu model içinde çalışanların ortalama maaş -0.16285 ağırlıkça t İstifalarını sayısını etkiler anlamına gelir Y olarak gösterir. E. kendi etki derecesi hiç küçük. işareti "-" katsayı negatif olduğunu gösterir. Hepimizin işletmedeki daha maaş, az kişinin girdiği veya görevden sözleşmesini sonlandırmak için bir istek belirttiler biliyoruz, çünkü açıktır.

çoklu regresyon

Bu terim ile formun birçok bağımsız değişkenler iletişim denkleme karşılık gelir:

y = f (x ₁ + x ₂ + ... x _m) + ε, y - bir özellik derecesi (bağımlı değişken) ve x _1, x _2, ... x _m - işaretleri faktörleri (bağımsız değişkenler) vardır.

parametre tahmini

çoklu regresyon (MR) için, en küçük kareler yöntemi (LSM) kullanılarak gerçekleştirilir. Y = a + b ₁ x _{1 + ... + B x m +} lineer denklem için ε , normal denklemler sistemi yapı (cm. Aşağıda)

Yöntemin prensibi anlamak için, iki faktörlü durumu dikkate alınız. Sonra durum formülle nitelendirdiler

Bu nedenle, elde edilir:

σ nerede - İlgili özelliğin değişkenliği olan, endekste yansıttı.

Denklem MR ölçeği standartiziruemom için MNC uygulanabilir. Bu durumda, biz denklemi elde edersiniz:

bu formülde, _{t, y} t _{x 1, ...} t _xm - ortalama değerleri 0 olan değişkenler standartiziruemye; _i p - standart regresyon katsayılarını ve standart sapma 1 -.

Bütün _i p unutmayınız Bu durumda ve bu nedenle arada bir karşılaştırma, normalize edilmiş ve tsentraliziruemye olarak tanımlanan geçerli ve kabul edilebilir olarak kabul. Buna ek olarak, βi en düşük değerlere sahip olanlar atarak, faktörlerin taramayı gerçekleştirme kabul edilmektedir.

Doğrusal regresyon denklemi kullanılarak sorun

Eğer son 8 aydır belirli bir ürün N fiyatının dinamiklerinin bir tablo olduğunu varsayalım. Bu 1850 ruble fiyata partisinin edinimi karar vermek gereklidir. / T.

	bir	B	C
1	ay	Ayın isim	Fiyat N
2	1	Ocak	ton 1750 ruble
3	2	Şubat	ton başına 1.755 ruble
4	3	Mart	ton başına 1.767 ruble
5	4	Nisan	ton başına 1.760 ruble
6	5	Mayıs	ton başına 1770 ruble
7	6	Haziran	ton başına 1.790 ruble
8	7	Temmuz	ton başına 1.810 ruble
9	8	Ağustos	ton başına 1.840 ruble

Yukarıda sunulan örnek aracı "Veri Analizi" ile tanınan kullanmak için gerekli tablo işlemcisi "Excel" bu sorunu çözmek için. Sonra, "Regresyon" bölümüne ve set parametreleri seçin. (Ay) bağımsız için - Biz Girdi aralığı X » "Giriş aralığı Y »ve (Yılın belirli aylarında bu durumda malın fiyatını) bağımlı değişkenin değerlerinin bir aralığına getirilmelidir" unutmamalıyız. Biz «Tamam» tıklayarak işlemi onaylayın. (Böylece belirtilmişse) Yeni bir çalışma, biz regresyon için veri elde.

Onlara parametreleri, a ve b olarak satır ayın numarası ve ismi katsayıları ve regresyon analizi sonuçları ile tabakanın «y-kesişimi" hattından katsayılarıdır formu kullanılarak y = ax + b, doğrusal denklem yapı vardır. Bu durumda, problem için lineer regresyon denklemi (EQ) 3 olarak yazılabilir:

Malların fiyatı N = 11714 * 1.727,54 ay numarası +.

veya cebirsel gösterimde

y = 11,714 x + 1727,54

Sonuçların analizi

çoklu korelasyon katsayıları (CMC) ve kararlılık gibi test ve Fisher t-testi ile yeterince doğrusal regresyon denklemi alınan karar vermek için. bunlar isimlerinin birden fazla R ', R-kare sırasıyla K-t-istatistikleri ve istatistikler altında hareket sonuçlar Tablo "Excel" regresyon.

KMC R bağımlı ve bağımsız değişkenler arasındaki yakınlık olasılıksal ilişkiyi tahmin sağlar. Onun değeri yüksek değişkeni "ayın Sayısı" arasında yeterince güçlü bir bağlantı olduğunu gösterir "1 ton başına ruble N Ürün fiyatı." Ancak, bu ilişkinin doğası bilinmemektedir.

belirleme ^R2 katsayısının kare (RI), yani, toplam dağılım oranının sayısal bir özelliğidir ve deneysel veri kısmı bir dağılım gösterir lineer regresyon denklemi tekabül eden bağımlı değişken değerleri. Bu soruna, bu değer, doğruluk elde yüksek kaliteli E. İstatistik SD tarif edilmiştir, en., 84.8% 'dir.

Ayrıca Fisher kriter olarak bilinen K-istatistikleri, doğrusal bağımlılık veya varlığını teyit hipotezi yanlışlamak önemini değerlendirmek için kullanılır.

t istatistiğinin (Student t testi) değeri herhangi bir serbest bilinmeyen doğrusal bağımlılık üyesi de katsayısının önemini değerlendirmek yardımcı olur. t-testi> t _cr değeri _ise, serbest terimi bir lineer denklem önemsiz hipotezi reddedilir.

enstrümanlar aracılığıyla ücretsiz bir dönem için bu problemde "Excel" o t 169,20903 = bulunmuştur, ve p = 2,89E-12, t., E. sadık serbest terimin önemsizliğini hipotezini reddedilecektir bir ihtimalin olmadığı var. bilinmeyen t = 5,79405 de katsayısı, ve p = 0,001158 için. Başka bir deyişle, bir reddedilen doğru hipotez bilinmeyen için katsayı değersizleşerek olacak olasılık, 0.12% 'dir.

Bu nedenle, yeterli lineer regresyon denklemi elde olduğu söylenebilir.

hisselerini satın advisability sorunu

Çoklu regresyon aynı "Veri Analizi" aracını kullanarak Excel'de gerçekleştirildi. Belirli bir uygulama düşünün.

Kılavuz şirketi «NNN» JSC «MMM» hisselerinin% 20'sini satın almaya karar vermelidir. Paket fiyatı (SP) 70 milyon ABD dolarıdır. «NNN» uzmanları benzer işlemlere ilişkin verileri toplanmıştır. Böyle parametreler üzerindeki hisselerinin değerini değerlendirmek için karar verildi gibi ABD milyonlarca dolar, ifade:

• borçlar (VK);
• ciro hacmi (VO);
• alacaklar (VD);
• duran varlıkların (SOF) değeri.

Buna ek olarak, ABD binlerce dolar işletmelerin ücret borçlarını (V3 U) kullanın.

karar tablosu işlemci Excel araçları

Önce giriş verilerinin bir tablo oluşturmak gerekir. Şöyle ki:

Sonraki:

• Çağrı kutusu "veri analizi";
• Seçilen "Regresyon" bölümü;
• cam kolon G "giriş aralığı Y» tatbik aralığı bağımlı değişken değerler
• penceresi "Giriş aralığı X» sağındaki kırmızı ok ile simgesini tıklayıp kolon B, C, D, F tüm değerleri bir tabaka dizi izole

Mark noktası "Yeni çalışma sayfası" ve "Tamam" a tıklayın.

Bu görev için bir regresyon analizi alın.

Çalışma sonuçları ve sonuçlar

tabaka tablo Excel işlemci regresyon denklemi yukarıda sunulan verilerden yuvarlak "Collect":

SS = 0.103 * SOF + 0541 * VO - 0031 * VK + 0405 * VD + 0691 * VZP - 265844.

daha olağan matematiksel formda o kadar yazılabilir:

Y = 0.103 x1 + 0.541 x2-0.031 x3 + 0.405 x4 + 0.691 x5-265.844

MMM'nin verileri aşağıdaki gibidir:

SOF, USD	VO, USD	VK, USD	VD, USD	VZP, USD	Japon Ortak, USD
102.5	535,5	45.2	41,5	21.55	64.72

Onları regresyon denklemine yerleştirerek 64,72 milyon ABD doları buluyoruz. Bu, MMM hisse senetlerinin satın alınmaması gerektiği anlamına geliyor, çünkü 70 milyon ABD Doları değerinin aşırı abartılmış olduğu görülüyor.

Gördüğünüz gibi, Excel tablo işlemcisinin ve regresyon denkleminin kullanılması, çok spesifik bir işlemin fizibilitesi hakkında bilinçli bir karar vermeyi mümkün kıldı.

Artık gerilemenin ne olduğunu biliyorsun. Excel'de yukarıda tartışılan örnekler, ekonometri alanındaki pratik problemleri çözmenize yardımcı olacaktır.