Verilerin istatistiksel olarak işlenmesi, eklenti kullanılarak da gerçekleştirilebilir. ANALİZ PAKETİ(şek. 62).

Önerilen öğelerden öğeyi seçiyor " REGRESYON"Ve farenin sol tuşuyla üzerine tıklayın. Ardından Tamam'ı tıklayın.

Şekilde gösterilen pencere. 63.

Analiz aracı " REGRESYON»En küçük kareler yöntemini kullanarak bir dizi gözlem için bir grafiğe sığdırmak için kullanılır. Regresyon, bir veya daha fazla açıklayıcı değişkenin değerlerinin bireysel bağımlı değişken üzerindeki etkisini analiz etmek için kullanılır. Örneğin, yaş, boy ve kilo gibi çeşitli faktörler bir sporcunun atletik performansını etkiler. Bu üç faktörün her birinin bir sporcunun performansı üzerindeki etkisini hesaplayabilir ve ardından verileri başka bir sporcunun performansını tahmin etmek için kullanabilirsiniz.

Regresyon aracı işlevi kullanır LINEST.

REGRESYON İletişim Kutusu

Etiketler Giriş aralığının ilk satırı veya ilk sütunu başlıklar içeriyorsa onay kutusunu seçin. Başlık yoksa bu kutunun işaretini kaldırın. Bu durumda, çıktı tablosu verileri için uygun başlıklar otomatik olarak oluşturulacaktır.

Güven düzeyi Çıkış toplamları tablosuna ek bir düzey eklemek için onay kutusunu seçin. Uygun alana, varsayılan %95 düzeyine ek olarak uygulanacak güvenilirlik düzeyini girin.

Sabit - sıfır Regresyon çizgisinin orijinden geçmesini sağlamak için onay kutusunu seçin.

Çıktı Aralığı Çıktı aralığının sol üst hücresine bir referans girin. Çıktı toplamları tablosu için aşağıdakileri içerecek en az yedi sütun ayırın: ANOVA sonuçları, katsayılar, Y hesaplamasının standart hatası, standart sapmalar, gözlem sayısı, katsayılar için standart hatalar.

Yeni Çalışma Sayfası Çalışma kitabında yeni bir çalışma sayfası açmak ve A1 hücresinden başlayarak analiz sonuçlarını eklemek için bu anahtarı seçin. Gerekirse, ilgili anahtar konumunun karşısındaki alana yeni sayfa için bir ad girin.

Yeni çalışma kitabı Sonuçların yeni bir sayfaya ekleneceği yeni bir çalışma kitabı oluşturmak için anahtarı bu konuma getirin.

Artıklar Artıkları çıktı tablosuna dahil etmek için onay kutusunu seçin.

Standartlaştırılmış artıklar Standartlaştırılmış artıkları çıktı tablosuna dahil etmek için onay kutusunu seçin.

Artıkları Çiz Her bağımsız değişken için artıkları çizmek için onay kutusunu seçin.

Uydurma Grafiği Tahmin edilen ve gözlemlenen değerlerin grafiğini çıkarmak için onay kutusunu seçin.

Normal Olasılık Grafiği Normal olasılık grafiği oluşturmak için kutuyu işaretleyin.

İşlev LINEST

Hesaplamaları yapmak için ortalama değeri göstermek istediğimiz hücreyi imleç ile seçin ve klavyede = tuşuna basın. Ardından, Ad alanında istediğiniz işlevi belirtin, örneğin ORTALAMA(şek. 22).

İşlev LINEST mevcut verilere en iyi uyan düz çizgiyi hesaplamak için en küçük kareleri kullanarak bir dizi için istatistikleri hesaplar ve ardından elde edilen düz çizgiyi tanımlayan bir dizi döndürür. Ayrıca işlevi birleştirebilirsiniz LINEST polinom, logaritmik, üstel ve kuvvet serileri dahil olmak üzere bilinmeyen parametrelerde doğrusal olan (bilinmeyen parametreleri doğrusal olan) diğer model türlerini hesaplamak için diğer işlevlerle. Bir dizi değer döndürüldüğü için işlevin bir dizi formülü olarak belirtilmesi gerekir.

Düz bir çizginin denklemi aşağıdaki gibidir:

y = m 1 x 1 + m 2 x 2 +… + b (birkaç x değeri aralığı olması durumunda),

bağımlı değer y, bağımsız değer x'in bir fonksiyonu olduğunda, m değerleri, her bir bağımsız değişken x'e karşılık gelen katsayılardır ve b bir sabittir. y, x ve m'nin vektör olabileceğine dikkat edin. İşlev LINEST bir dizi döndürür (mn; mn-1;…; m 1; b). LINEST ek regresyon istatistikleri de verebilir.

LINEST(bilinen_y'ler; bilinen_x'ler; const; istatistikler)

Bilinen_y'ler, y = mx + b ilişkisi için zaten bilinen y değerleri kümesidir.

Bilinen_y'lerin bir sütunu varsa, bilinen_x'lerdeki her sütun ayrı bir değişken olarak yorumlanır.

Bilinen_y'lerin bir satırı varsa, bilinen_x'lerdeki her satır ayrı bir değişken olarak yorumlanır.

Bilinen_x'ler, y = mx + b için zaten bilinen isteğe bağlı bir x değerleri kümesidir.

Bilinen_x'ler bir veya daha fazla değişken kümesi içerebilir. Yalnızca bir değişken kullanılıyorsa, bilinen_y'ler ve bilinen_x'ler aynı boyuta sahip oldukları sürece herhangi bir şekilde olabilir. Birden fazla değişken kullanılıyorsa, bilinen_y'ler bir vektör olmalıdır (yani bir satır yüksekliğinde veya bir sütun genişliğinde).

Bilinen_x dizisi atlanırsa, bu dizinin (1; 2; 3; ...) dizi_bilinen_y'lerle aynı boyutta olduğu varsayılır.

Const, b sabitinin 0 olması gerekip gerekmediğini gösteren bir Boole değeridir.

const TRUE ise veya atlanırsa, b sabiti olağan şekilde değerlendirilir.

"const" argümanı YANLIŞ ise, b'nin değeri 0'a ayarlanır ve m değerleri, y = mx ilişkisi sağlanacak şekilde seçilir.

İstatistikler, ek regresyon istatistikleri döndürmek isteyip istemediğinizi belirten bir Boole değeridir.

İstatistikler DOĞRU ise, LINEST ek regresyon istatistikleri döndürür. Döndürülen dizi şöyle görünecektir: (mn; mn-1; ...; m1; b: sen; sen-1; ...; se1; seb: r2; sey: F; df: ssreg; ssresid).

İstatistikler YANLIŞ veya atlanmışsa, DOĞRU yalnızca m katsayılarını ve b sabitini döndürür.

Ek regresyon istatistikleri (Tablo 17)

Miktar	Açıklama
se1, se2, ..., sen	m1, m2, ..., mn katsayıları için standart hata değerleri.
seb	b sabiti için standart hata değeri (seb = # N / A sabit YANLIŞ ise).
r2	Determinizm katsayısı. Gerçek y değerleri, doğrunun denkleminden elde edilen değerlerle karşılaştırılır; karşılaştırma sonuçlarına dayanarak, determinizm katsayısı hesaplanır, 0'dan 1'e normalleştirilir. 1'e eşitse, modelle tam bir korelasyon vardır, yani gerçek ve tahmini değerler arasında fark yoktur ​y. Aksi takdirde, determinizm katsayısı 0 ise, y değerlerini tahmin etmek için bir regresyon denklemi kullanmanın bir anlamı yoktur. r2'nin nasıl hesaplandığı hakkında daha fazla bilgi için bu bölümün sonundaki "Açıklamalar"a bakın.
sey	y tahmini için standart hata.
F	F-istatistiği veya F-gözlenen değer. F istatistiği, bağımlı ve bağımsız değişkenler arasında gözlenen ilişkinin rastgele olup olmadığını belirlemek için kullanılır.
df	Özgürlük derecesi. Serbestlik dereceleri, istatistiksel bir tabloda F-kritik değerleri bulmak için kullanışlıdır. Modelin güvenilirlik seviyesini belirlemek için tablodaki değerleri LINEST tarafından döndürülen F istatistiği ile karşılaştırın. df'nin hesaplanması hakkında daha fazla bilgi için bu bölümün sonundaki Açıklamalara bakın. Aşağıdaki Örnek 4, F ve df değerlerinin kullanımını göstermektedir.
ssreg	Karelerin regresyon toplamı.
ssresid	Artık kareler toplamı. ssreg ve ssresid değerlerinin hesaplanması hakkında daha fazla bilgi için bu bölümün sonundaki Açıklamalara bakın.

Aşağıdaki şekil, ek regresyon istatistiklerinin döndürülme sırasını göstermektedir (Şekil 64).

Notlar:

Herhangi bir düz çizgi, eğimi ve y ekseni ile kesişimi ile tanımlanabilir:

Eğim (m): genellikle m ile gösterilen düz bir çizginin eğimini belirlemek için, düz çizginin (x 1, y 1) ve (x 2, y 2) iki noktasını almanız gerekir; eğim (y 2 -y 1) / (x 2 -x 1) olacaktır.

Y-kesişim (b): Genellikle b ile gösterilen bir doğrunun y-kesişimi, doğrunun y eksenini kestiği noktanın y değeridir.

Düz çizgi denklemi y = mx + b biçimindedir. Eğer m ve b değerlerini biliyorsanız denklemdeki y veya x değerlerini yerine koyarak doğru üzerindeki herhangi bir noktayı hesaplayabilirsiniz. TREND işlevini de kullanabilirsiniz.

Yalnızca bir bağımsız x değişkeni varsa, aşağıdaki formülleri kullanarak eğimi ve y-kesişimini doğrudan alabilirsiniz:

Eğim: İNDEKS (ÇİZGİ (bilinen_y'ler; bilinen_x'ler); 1)

Y-kesişim: İNDEKS (ÇİZGİ (bilinen_y'ler; bilinen_x'ler); 2)

DOĞRU çizgi yaklaşımının doğruluğu, verilerdeki saçılma derecesine bağlıdır. Veriler düz bir çizgiye ne kadar yakınsa, DOĞRU model o kadar doğru olur. LINEST, verilere en uygun olanı belirlemek için en küçük kareler yöntemini kullanır. Yalnızca bir bağımsız değişken olduğunda x, m ve b aşağıdaki formüller kullanılarak hesaplanır:

burada x ve y örnek ortalamalardır, örneğin x = ORTALAMA (bilinen_x'ler) ve y = ORTALAMA (bilinen_y'ler).

DOĞRU ve LOGEST sığdırma işlevleri, verileri en iyi tanımlayan düz veya üstel eğriyi hesaplayabilir. Ancak, eldeki görevi çözmek için iki sonuçtan hangisinin daha uygun olduğu sorusuna bir cevap vermezler. Ayrıca düz bir çizgi için TREND'i (bilinen_y'ler; bilinen_x'ler) veya üstel bir eğri için BÜYÜME (bilinen_y'ler; bilinen_x'ler) hesaplayabilirsiniz. Bu işlevler, yeni_x_değerleri belirtmezseniz, bir düz çizgi veya eğri boyunca gerçek x değerleri için hesaplanan y değerlerinin bir dizisini döndürür. Hesaplanan değerler daha sonra gerçek değerlerle karşılaştırılabilir. Görsel karşılaştırma için grafikler de oluşturabilirsiniz.

Microsoft Excel, regresyon analizi ile her nokta için tahmin edilen y değeri ile gerçek y değeri arasındaki farkın karesini hesaplar. Bu kare farklarının toplamına artık kareler toplamı (ssresid) denir. Microsoft Excel daha sonra toplam kareler toplamını (sstotal) hesaplar. const = TRUE veya bu argüman atlanırsa, toplam kareler toplamı, gerçek y değerleri ile ortalama y değerleri arasındaki farkın karelerinin toplamıdır. const = YANLIŞ olduğunda, toplam kareler toplamı, y'nin gerçek değerlerinin karelerinin toplamına eşit olacaktır (y'nin ortalama değerini y'nin bölüm değerinden çıkarmadan). Karelerin regresyon toplamı daha sonra şu şekilde hesaplanabilir: ssreg = sstotal - ssresid. Artık kareler toplamı ne kadar küçükse, regresyon analizi kullanılarak elde edilen denklemin değişkenler arasındaki ilişkiyi ne kadar iyi açıkladığını gösteren determinizm katsayısı r2'nin değeri o kadar büyük olur. r2 katsayısı ssreg / sstotal'dır.

Bazı durumlarda, bir veya daha fazla X sütunu (sütunlarda Y ve X değerleri olsun) diğer X sütunlarında ek bir tahmin değerine sahip değildir.Başka bir deyişle, bir veya daha fazla X sütununu silmek Y değerlerine neden olabilir ​aynı hassasiyetle hesaplanır. Bu durumda, fazlalık X sütunları regresyon modelinden hariç tutulacaktır. Bu fenomene "eşdoğrusallık" denir, çünkü fazlalıklı X sütunları, fazlalık olmayan birden çok sütunun toplamı olarak temsil edilebilir. LINEST, eşdoğrusallığı kontrol eder ve herhangi bir gereksiz X sütununu bulursa, regresyon modelinden kaldırır. Silinen X sütunları, LINEST çıktısında 0 faktörü ve 0 se değeri ile tanımlanabilir. Bir veya daha fazla sütunu fazlalık olarak kaldırmak, df değerini değiştirir çünkü bu, tahmin amacıyla fiilen kullanılan X sütunlarının sayısına bağlıdır. df'nin hesaplanması hakkında daha fazla bilgi için aşağıdaki Örnek 4'e bakın.. Fazlalık sütunların kaldırılması nedeniyle df değiştiğinde, sey ve F de değişir. Doğrusallık genellikle önerilmez. Ancak, X sütunlarından bazıları 0 veya 1 içeriyorsa, deney konusunun ayrı bir grupta olup olmadığını gösteren bir gösterge olarak kullanılmalıdır. const = TRUE veya atlanmışsa, DOT, kesişme noktasını modellemek için ek bir X sütunu ekler. Erkekler için 1 ve kadınlar için 0 değerlerine sahip bir sütun varsa ve ayrıca kadınlar için 1 ve erkekler için 0 değerlerine sahip bir sütun varsa, o zaman değerlerinin alınabileceğinden son sütun kaldırılır. “erkek cinsiyet göstergesi” sütunundan elde edilmiştir.

X'in sütunlarının doğrusallık nedeniyle modelden çıkarılmadığı durumlar için df'nin hesaplanması şu şekildedir: bilinen_x'lerin k sütunu varsa ve const = TRUE değeri belirtilmemişse, df = n - k - 1. const = YANLIŞ, sonra df = n - k. Her iki durumda da, doğrusallık nedeniyle X sütunlarının kaldırılması df değerini 1 artırır.

Dizileri döndüren formüller dizi formülleri olarak girilmelidir.

Örneğin bilinen_x'ler için bir dizi sabit girdiğinizde, aynı satırdaki değerleri ayırmak için noktalı virgül ve satırları ayırmak için iki nokta üst üste kullanın. Ayırıcı karakterler, kontrol panelindeki Dil ve Standartlar penceresinde ayarlanan seçeneklere göre değişiklik gösterir.

Unutulmamalıdır ki, regresyon denklemi tarafından tahmin edilen y değerleri, denklemi tanımlamak için kullanılan y değerleri aralığının dışındaysa doğru olmayabilir.

Fonksiyonda kullanılan ana algoritma LINEST, fonksiyonların ana algoritmasından farklıdır EĞİM ve BÖLÜM... Algoritmalar arasındaki farklılıklar, tanımsız ve eşdoğrusal veriler için farklı sonuçlara yol açabilir. Örneğin, bilinen_y'lerin veri noktaları 0 ve bilinen_x'lerin veri noktaları 1 ise, o zaman:

İşlev LINEST 0'a eşit bir değer döndürür. Fonksiyon algoritması LINEST eşdoğrusal veriler için geçerli değerleri döndürmek için kullanılır, bu durumda en az bir cevap bulunabilir.

EĞİM ve KESME işlevleri # DIV / 0! Hatası döndürür. SLOPE ve INTERCEPT fonksiyon algoritması sadece bir cevap aramak için kullanılır ve bu durumda birkaç cevap olabilir.

Diğer regresyon türleri için istatistikleri hesaplamaya ek olarak, DOĞRU için x ve y işlevlerini x ve y serileri olarak girerek diğer regresyon türleri için aralıkları hesaplamak için DOT kullanılabilir. Örneğin, aşağıdaki formül:

DOĞRU (y değerleri, x değerleri ​​^ SÜTUN ($ A: $ C))

aşağıdaki formda bir küp (3. derece polinom) için bir yaklaşım hesaplamak için bir Y değerleri sütununa ve bir X değerleri sütununa sahip olarak çalışır:

y = m 1 x + m 2 x 2 + m 3 x 3 + b

Formül, diğer regresyon türlerini hesaplamak için değiştirilebilir, ancak bazı durumlarda çıktı değerlerinde ve diğer istatistiklerde ayarlamalar yapılması gerekir.

Regresyon analizi, bir parametrenin bir veya daha fazla bağımsız değişkene bağımlılığını göstermenizi sağlayan istatistiksel bir araştırma yöntemidir. Bilgisayar öncesi çağda, özellikle büyük miktarda veri söz konusu olduğunda, uygulaması oldukça zordu. Bugün, Excel'de bir regresyon oluşturmayı öğrendikten sonra, karmaşık istatistiksel sorunları sadece birkaç dakika içinde çözebilirsiniz. Aşağıda ekonomi alanından özel örnekler verilmiştir.

Regresyon türleri

Kavramın kendisi matematiğe 1886'da tanıtıldı. Gerileme olur:

• doğrusal;
• parabolik;
• Güç yasası;
• üstel;
• hiperbolik;
• gösterge;
• logaritmik.

örnek 1

6 sanayi kuruluşunda işten ayrılan çalışan sayısının ortalama maaşa bağımlılığını belirleme problemini ele alalım.

Görev. Altı işletme, ortalama aylık maaşı ve kendi isteğiyle işten ayrılan çalışan sayısını analiz etti. Tablo biçiminde, elimizde:

		istifa sayısı	Maaş
			30.000 ruble
			35.000 ruble
			40.000 ruble
			45.000 ruble
			50.000 ruble
			55.000 ruble
			60.000 ruble

6 işletmede işten ayrılan çalışan sayısının ortalama maaşa bağımlılığını belirleme problemi için, regresyon modeli Y = a 0 + a 1 x 1 + ... + akxk denklemi biçimindedir, burada xi, etkileyen değişkenler, ai, regresyon katsayılarıdır ve ak, faktörlerin sayısıdır.

Bu görev için Y, işten ayrılan çalışanların bir göstergesidir ve etkileyen faktör, X ile gösterdiğimiz maaştır.

Excel tablo işlemcisinin özelliklerini kullanma

Excel'deki regresyon analizinden önce, mevcut tablo verilerine yerleşik işlevlerin uygulanması gelmelidir. Ancak bu amaçlar için çok kullanışlı "Analiz Paketi" eklentisini kullanmak daha iyidir. Etkinleştirmek için ihtiyacınız olan:

• "Dosya" sekmesinden "Parametreler" bölümüne gidin;
• açılan pencerede "Eklentiler" satırını seçin;
• "Kontrol" satırının sağında bulunan aşağıda bulunan "Git" düğmesine tıklayın;
• "Analiz paketi" adının yanına bir onay işareti koyun ve "Tamam" düğmesine tıklayarak işlemlerinizi onaylayın.

Her şey doğru yapılırsa, "Excel" çalışma sayfasının üzerinde bulunan "Veri" sekmesinin sağ tarafında gerekli düğme görünecektir.

Excel'de

Artık ekonometrik hesaplamalar yapmak için gerekli tüm sanal araçlara sahip olduğumuza göre, problemimizi çözmeye başlayabiliriz. Bunun için:

• "Veri Analizi" düğmesine tıklayın;
• açılan pencerede "Regresyon" düğmesine tıklayın;
• görünen sekmede Y (işten ayrılan çalışan sayısı) ve X (maaşları) için değer aralığını girin;
• "Tamam" butonuna basarak işlemlerimizi onaylıyoruz.

Sonuç olarak program, elektronik tablo işlemcisinin yeni sayfasını otomatik olarak regresyon analizi verileriyle dolduracaktır. Not! Excel, bu amaç için tercih ettiğiniz konumu bağımsız olarak tanımlama yeteneğine sahiptir. Örneğin, Y ve X değerlerini içeren aynı sayfa veya hatta bu tür verileri depolamak için özel olarak tasarlanmış yeni bir çalışma kitabı olabilir.

R-Kare için Regresyon Sonuçlarının Analizi

Excel'de, söz konusu örneğin verilerinin işlenmesi sırasında elde edilen veriler aşağıdaki gibidir:

Öncelikle R-kare değerine dikkat etmelisiniz. Belirleme katsayısını temsil eder. Bu örnekte, R-kare = 0,755 (%75,5), yani modelin hesaplanan parametreleri, dikkate alınan parametreler arasındaki ilişkiyi %75.5 oranında açıklamaktadır. Belirleme katsayısının değeri ne kadar yüksek olursa, seçilen modelin belirli bir görev için o kadar uygulanabilir olduğu kabul edilir. R-kare değeri 0,8'in üzerinde olduğunda gerçek durumu doğru bir şekilde tanımladığına inanılmaktadır. R-kare ise<0,5, то такой анализа регрессии в Excel нельзя считать резонным.

Oran analizi

64.1428 sayısı, modelimizdeki tüm xi değişkenleri sıfırlanırsa Y'nin değerinin ne olacağını gösterir. Başka bir deyişle, analiz edilen parametrenin değerinin belirli bir modelde tanımlanmayan diğer faktörlerden etkilendiği iddia edilebilir.

B18 hücresinde bulunan bir sonraki -0.16285 katsayısı, X değişkeninin Y üzerindeki etkisinin önemini gösterir. Bu, dikkate alınan modeldeki çalışanların ortalama aylık maaşının -0.16285 ağırlığında işten ayrılan kişi sayısını etkilediği anlamına gelir. , yani, etkisinin derecesi hiç de küçük. “-” işareti katsayının negatif olduğunu gösterir. Bu açıktır, çünkü herkes, işletmedeki maaş ne kadar yüksek olursa, iş sözleşmesini feshetme veya işten ayrılma arzusunu o kadar az kişinin ifade ettiğini bilir.

Çoklu regresyon

Bu terim, formun birkaç bağımsız değişkenine sahip bir kısıtlama denklemi olarak anlaşılır:

y = f (x 1 + x 2 +… x m) + ε, burada y etkin bir özelliktir (bağımlı değişken) ve x 1, x 2,… x m özellikler-faktörlerdir (bağımsız değişkenler).

parametre tahmini

Çoklu regresyon (MR) için en küçük kareler yöntemi (OLS) kullanılarak gerçekleştirilir. Y = a + b 1 x 1 +… + b m x m + ε biçimindeki doğrusal denklemler için, bir normal denklem sistemi oluştururuz (aşağıya bakın)

Yöntemin ilkesini anlamak için iki faktörlü durumu düşünün. O zaman formülle açıklanan bir durumumuz var

Buradan şunu elde ederiz:

burada σ, indekste yansıtılan ilgili özelliğin varyansıdır.

OLS, standartlaştırılmış bir ölçekte MR denklemine uygulanır. Bu durumda denklemi elde ederiz:

burada t y, t x 1,… t xm, ortalama değerlerin 0'a eşit olduğu standartlaştırılmış değişkenlerdir; β i standartlaştırılmış regresyon katsayılarıdır ve standart sapma 1'dir.

Bu durumda tüm β i'nin normalleştirilmiş ve merkezileştirilmiş olarak belirtildiğine dikkat edin, bu nedenle birbirleriyle karşılaştırmaları doğru ve geçerli olarak kabul edilir. Ek olarak, en küçük βi değerlerine sahip olanları atarak faktörleri filtrelemek gelenekseldir.

Doğrusal bir regresyon denklemi kullanma sorunu

Son 8 ay boyunca belirli bir ürün N için bir fiyat dinamikleri tablonuz olduğunu varsayalım. Partisini 1850 ruble / t fiyatla satın almanın tavsiye edilebilirliği konusunda bir karar vermek gerekiyor.

	ay numarası	ayın adı	ürün fiyatı N
			ton başına 1750 ruble
			ton başına 1755 ruble
			ton başına 1767 ruble
			ton başına 1760 ruble
			ton başına 1770 ruble
			ton başına 1790 ruble
			ton başına 1810 ruble
			ton başına 1840 ruble

Excel elektronik tablo işlemcisindeki bu sorunu çözmek için, yukarıda sunulan örnekten zaten bilinen Veri Analizi aracını kullanmanız gerekir. Ardından, "Regresyon" bölümünü seçin ve parametreleri ayarlayın. Unutulmamalıdır ki "Giriş aralığı Y" alanına bağımlı değişken (bu durumda ürüne ait yılın belirli aylarındaki fiyatları) için bir değer aralığı ve "Giriş" alanına girilmelidir. X" aralığı - bağımsız değişken için (ay sayısı). İşlemleri "Tamam"a tıklayarak onaylıyoruz. Yeni bir sayfada (eğer belirtilmişse) regresyon verilerini alırız.

Bunları, y = ax + b biçiminde doğrusal bir denklem oluşturmak için kullanırız; burada, ay numarasının adı ile çizginin katsayıları ve regresyon analizi sonuçlarıyla sayfadan "Y-kesişim" katsayıları ve çizgileri hareket eder. a ve b parametreleri olarak Böylece, problem 3 için lineer regresyon denklemi (SD) şu şekilde yazılır:

Ürün fiyatı N = 11.714 * ay numarası + 1727.54.

veya cebirsel gösterimde

y = 11.714 x + 1727.54

Sonuçların analizi

Elde edilen lineer regresyon denkleminin yeterli olup olmadığına karar vermek için Fisher testi ve Student testinin yanı sıra çoklu korelasyon ve belirleme katsayıları kullanılır. Regresyon sonuçlarının olduğu Excel tablosunda sırasıyla çoklu R, R-kare, F-istatistikleri ve t-istatistikleri olarak adlandırılırlar.

KMC R, bağımsız ve bağımlı değişkenler arasındaki olasılıksal ilişkinin yakınlığını değerlendirmeyi mümkün kılar. Yüksek değeri, “Ay numarası” ve “Ton başına ruble cinsinden ürün fiyatı N” değişkenleri arasında oldukça güçlü bir ilişki olduğunu gösterir. Ancak, bu bağlantının doğası bilinmemektedir.

R2 (RI) belirleme katsayısının karesi, toplam yayılma oranının sayısal bir özelliğidir ve deneysel verilerin hangi bölümünün yayılımını gösterir, yani. bağımlı değişkenin değerleri lineer regresyon denklemine karşılık gelir. Ele alınan problemde bu değer %84,8'dir, yani istatistiksel veriler elde edilen SD ile yüksek derecede doğrulukla tanımlanır.

Fisher testi olarak da adlandırılan F istatistiği, doğrusal bir ilişkinin önemini değerlendirmek, varlığının hipotezini reddetmek veya doğrulamak için kullanılır.

(Öğrenci kriteri), doğrusal bir ilişkinin bilinmeyen veya serbest terimiyle katsayının önemini değerlendirmeye yardımcı olur. t-kriterinin değeri> t cr ise, lineer denklemin serbest teriminin önemsizliği hipotezi reddedilir.

Excel araçlarını kullanarak bir serbest terim için ele alınan problemde, t = 169.20903 ve p = 2.89E-12 olduğu elde edildi, yani serbest terimin önemsizliğine ilişkin doğru hipotezin sıfır olasılığına sahibiz. reddedilmiş. Bilinmeyen t = 5.79405 ve p = 0.001158'deki katsayı için. Başka bir deyişle, katsayının bilinmeyenle önemsizliğine ilişkin doğru hipotezin reddedilme olasılığı %0,12'dir.

Böylece elde edilen lineer regresyon denkleminin yeterli olduğu söylenebilir.

Bir hisse bloğu satın almanın uygunluğu sorunu

Excel'de çoklu regresyon, aynı Veri Analizi aracı kullanılarak gerçekleştirilir. Belirli bir uygulamalı görevi ele alalım.

"NNN" şirketinin yönetimi, JSC "MMM" de %20 hisse satın almanın tavsiye edilebilirliğine karar vermelidir. Paketin (JV) maliyeti 70 milyon ABD dolarıdır. NNN uzmanları, benzer işlemler hakkında veri topladı. Milyonlarca ABD doları olarak ifade edilen bu tür parametrelerle hisse bloğunun değerinin aşağıdaki gibi değerlendirilmesine karar verildi:

• ödenecek hesaplar (VK);
• yıllık cironun hacmi (VO);
• alacak hesapları (VD);
• sabit kıymetlerin maliyeti (SOF).

Ek olarak, parametre, işletmenin (V3 P) binlerce ABD doları cinsinden ödenmemiş maaşlarıdır.

Excel elektronik tablo çözümü

Her şeyden önce, bir başlangıç ​​verileri tablosu oluşturmanız gerekir. Şuna benziyor:

• "Veri Analizi" penceresini çağırın;
• "Gerileme" bölümünü seçin;
• G sütunundaki bağımlı değişkenlerin değer aralığı "Giriş aralığı Y" kutusuna girilir;
• "Giriş aralığı X" penceresinin sağındaki kırmızı oklu simgeye tıklayın ve sayfada B, C, D, F sütunlarından tüm değerlerin aralığını seçin.

"Yeni Çalışma Sayfası" öğesini kontrol edin ve "Tamam" ı tıklayın.

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

Sonuçların ve sonuçların incelenmesi

Excel elektronik tablo sayfasında yukarıda sunulan yuvarlatılmış verilerden regresyon denklemini "toplarız":

SP = 0.103 * SOF + 0.541 * VO - 0.031 * VK + 0.405 * VD + 0.691 * VZP - 265.844.

Daha tanıdık bir matematiksel formda şu şekilde yazılabilir:

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

JSC "MMM" verileri tabloda sunulmaktadır:

Bunları regresyon denkleminde yerine koyarsak rakam 64,72 milyon ABD dolarıdır. Bu, JSC "MMM" hisselerinin satın alınmaması gerektiği anlamına gelir, çünkü 70 milyon ABD doları değeri oldukça fazladır.

Gördüğünüz gibi, Excel elektronik tablo işlemcisinin ve regresyon denkleminin kullanılması, çok özel bir işlemin tavsiye edilebilirliği konusunda bilinçli bir karar vermeyi mümkün kıldı.

Artık regresyonun ne olduğunu biliyorsunuz. Yukarıda tartışılan Excel'deki örnekler, ekonometri alanındaki pratik sorunları çözmenize yardımcı olacaktır.

Regresyon ve korelasyon analizi - istatistiksel araştırma yöntemleri. Bunlar, bir parametrenin bir veya daha fazla bağımsız değişkene nasıl bağlı olduğunu göstermenin en yaygın yollarıdır.

Aşağıda, belirli pratik örnekler kullanarak, ekonomistler arasında çok popüler olan bu iki analizi ele alacağız. Ayrıca sonuçların birleştirildiğinde elde edilmesine ilişkin bir örnek vereceğiz.

Excel'de regresyon analizi

Bazı değerlerin (bağımsız, bağımsız) bağımlı değişken üzerindeki etkisini gösterir. Örneğin, ekonomik olarak aktif nüfus sayısının işletme sayısına, ücretlerin büyüklüğüne ve diğer parametrelere nasıl bağlı olduğu. Veya: yabancı yatırımlar, enerji fiyatları vb. GSYİH seviyesini nasıl etkiler?

Analizin sonucu önceliklendirmenizi sağlar. Ve ana faktörlere dayanarak, tahmin edin, öncelikli alanların gelişimini planlayın, yönetim kararları verin.

Gerileme olur:

• doğrusal (y = a + bx);
• parabolik (y = a + bx + cx 2);
• üstel (y = a * exp (bx));
• güç (y = a * x ^ b);
• hiperbolik (y = b / x + a);
• logaritmik (y = b * 1n (x) + a);
• üstel (y = a * b ^ x).

Excel'de bir regresyon modeli oluşturmaya ve sonuçları yorumlamaya ilişkin bir örneğe bakalım. Doğrusal bir regresyon türü alalım.

Görev. 6 işletmede ortalama aylık maaş ve işten ayrılan çalışan sayısı analiz edilmiştir. İşten ayrılan çalışan sayısının ortalama maaşa bağımlılığının belirlenmesi gerekmektedir.

Doğrusal regresyon modeli aşağıdaki gibidir:

Y = 0 + 1 x 1 + ... + bir k x k.

a - regresyon katsayıları, x - etkileyen değişkenler, k - faktör sayısı.

Örneğimizde Y, işten ayrılan çalışanların göstergesidir. Etkileyen faktör ücretlerdir (x).

Excel, doğrusal bir regresyon modelinin parametrelerini hesaplamak için kullanabileceğiniz yerleşik işlevlere sahiptir. Ancak Analiz Paketi eklentisi bunu daha hızlı yapacaktır.

Güçlü bir analitik aracı etkinleştiriyoruz:

Etkinleştirmenin ardından, eklenti Veri sekmesinde mevcut olacaktır.

Şimdi doğrudan regresyon analizine geçelim.

Her şeyden önce, R-kare ve katsayılara dikkat edin.

R-kare, belirleme katsayısıdır. Örneğimizde - 0,755 veya %75.5. Bu, modelin hesaplanan parametrelerinin, çalışılan parametreler arasındaki ilişkiyi %75,5 oranında açıkladığı anlamına gelir. Belirleme katsayısı ne kadar yüksek olursa, model o kadar iyi olur. İyi - 0.8'in üzerinde. Kötü - 0,5'ten az (böyle bir analiz pek makul kabul edilemez). Örneğimizde - "fena değil".

64.1428 katsayısı, incelenen modeldeki tüm değişkenlerin 0'a eşit olması durumunda Y'nin ne olacağını gösterir. Yani modelde açıklanmayan diğer faktörler de analiz edilen parametrenin değerini etkiler.

-0.16285 katsayısı, X değişkeninin Y üzerindeki ağırlığını gösterir. Yani, bu modeldeki ortalama aylık maaş, -0.16285 ağırlıkla ayrılan kişi sayısını etkiler (bu küçük bir etki derecesidir). “-” işareti olumsuz bir etkiyi gösterir: maaş ne kadar yüksekse, bırakan o kadar az olur. Hangisi adil.



Excel'de korelasyon analizi

Korelasyon analizi, bir veya iki örneklemdeki göstergeler arasında bir ilişki olup olmadığını belirlemeye yardımcı olur. Örneğin, makinenin çalışma süresi ile onarım maliyeti, ekipmanın fiyatı ve çalışma süresi, çocukların boy ve kilosu vb.

Bir ilişki varsa, o zaman bir parametredeki artışın diğerinde bir artışa (pozitif korelasyon) veya bir azalmaya (negatif) yol açıp açmadığı. Korelasyon analizi, analistin bir göstergenin değerinin diğerinin olası değerini tahmin edip edemeyeceğini belirlemesine yardımcı olur.

Korelasyon katsayısı r ile gösterilir. +1 ile -1 arasında değişir. Farklı alanlar için korelasyonların sınıflandırılması farklı olacaktır. Katsayı 0 olduğunda, örnekler arasında doğrusal bir ilişki yoktur.

Korelasyon katsayısını bulmak için Excel araçlarının nasıl kullanılacağına bakalım.

Eşleştirilmiş katsayıları bulmak için CORREL işlevi kullanılır.

Amaç: Torna tezgahının çalışma süresi ile bakım maliyeti arasında bir ilişki olup olmadığını belirleyin.

İmleci herhangi bir hücreye koyuyoruz ve fx düğmesine basıyoruz.

1. "İstatistiksel" kategorisinde CORREL işlevini seçin.
2. Argüman "Dizi 1" - ilk değer aralığı - makine çalışma süresi: A2: A14.
3. Dizi 2 argümanı - ikinci değer aralığı - onarım maliyeti: B2: B14. Tamam'ı tıklayın.

Bağlantı türünü belirlemek için katsayının mutlak sayısına bakmanız gerekir (her faaliyet alanının kendi ölçeği vardır).

Birkaç parametrenin (2'den fazla) korelasyon analizi için Veri Analizi (Analiz Paketi eklentisi) kullanmak daha uygundur. Listede bir korelasyon seçmeniz ve bir dizi belirlemeniz gerekir. Her şey.

Elde edilen katsayılar korelasyon matrisinde görüntülenecektir. Bunun gibi:

Korelasyon-regresyon analizi

Uygulamada, bu iki teknik genellikle birlikte kullanılır.

Örnek:

Artık regresyon verileri de görülebilir.

Bazı değerlerin (bağımsız, bağımsız) bağımlı değişken üzerindeki etkisini gösterir. Örneğin, ekonomik olarak aktif nüfus sayısının işletme sayısına, ücretlerin büyüklüğüne ve diğer parametrelere nasıl bağlı olduğu. Veya: yabancı yatırımlar, enerji fiyatları vb. GSYİH seviyesini nasıl etkiler?

Analizin sonucu önceliklendirmenizi sağlar. Ve ana faktörlere dayanarak, tahmin edin, öncelikli alanların gelişimini planlayın, yönetim kararları verin.

Gerileme olur:

Doğrusal (y = a + bx);

Parabolik (y = a + bx + cx 2);

Üstel (y = a * exp (bx));

Güç (y = a * x ^ b);

Hiperbolik (y = b / x + a);

Logaritmik (y = b * 1n (x) + a);

Üstel (y = a * b ^ x).

Excel'de bir regresyon modeli oluşturmaya ve sonuçları yorumlamaya ilişkin bir örneğe bakalım. Doğrusal bir regresyon türü alalım.

Görev. 6 işletmede ortalama aylık maaş ve işten ayrılan çalışan sayısı analiz edilmiştir. İşten ayrılan çalışan sayısının ortalama maaşa bağımlılığının belirlenmesi gerekmektedir.

Doğrusal regresyon modeli aşağıdaki gibidir:

Y = 0 + 1 x 1 + ... + bir k x k.

a - regresyon katsayıları, x - etkileyen değişkenler, k - faktör sayısı.

Örneğimizde Y, işten ayrılan çalışanların göstergesidir. Etkileyen faktör ücretlerdir (x).

Excel, doğrusal bir regresyon modelinin parametrelerini hesaplamak için kullanabileceğiniz yerleşik işlevlere sahiptir. Ancak Analiz Paketi eklentisi bunu daha hızlı yapacaktır.

Güçlü bir analitik aracı etkinleştiriyoruz:

1. "Ofis" düğmesine basın ve "Excel Seçenekleri" sekmesine gidin. "Eklentiler".

2. Altta, açılır listenin altında, "Kontrol" alanında bir "Excel eklentileri" yazısı olacaktır (eğer orada değilse, sağdaki onay kutusuna tıklayın ve seçin). Ve "Git" düğmesi. basıyoruz.

3. Kullanılabilir eklentilerin bir listesi açılır. "Analiz Paketi"ni seçin ve Tamam'a tıklayın.

Etkinleştirmenin ardından, eklenti Veri sekmesinde mevcut olacaktır.

Şimdi doğrudan regresyon analizine geçelim.

1. "Veri Analizi" araç menüsünü açın. "Gerileme" yi seçiyoruz.

2. Giriş değerlerini ve çıkış parametrelerini seçmek için bir menü açılacaktır (sonucun nerede gösterileceği). İlk veriler için alanlarda, açıklanan parametrenin (Y) aralığını ve onu etkileyen faktörü (X) belirtiriz. Gerisi boş bırakılabilir.

3. Tamam'a tıkladıktan sonra program, hesaplamaları yeni bir sayfada görüntüleyecektir (geçerli sayfada görüntülemek için bir aralık seçebilir veya yeni bir kitaba çıktı atayabilirsiniz).

Her şeyden önce, R-kare ve katsayılara dikkat edin.

R-kare, belirleme katsayısıdır. Örneğimizde - 0,755 veya %75.5. Bu, modelin hesaplanan parametrelerinin, çalışılan parametreler arasındaki ilişkiyi %75,5 oranında açıkladığı anlamına gelir. Belirleme katsayısı ne kadar yüksek olursa, model o kadar iyi olur. İyi - 0.8'in üzerinde. Kötü - 0,5'ten az (böyle bir analiz pek makul kabul edilemez). Örneğimizde - "fena değil".

64.1428 katsayısı, incelenen modeldeki tüm değişkenlerin 0'a eşit olması durumunda Y'nin ne olacağını gösterir. Yani modelde açıklanmayan diğer faktörler de analiz edilen parametrenin değerini etkiler.

-0.16285 katsayısı, X değişkeninin Y üzerindeki ağırlığını gösterir. Yani, bu modeldeki ortalama aylık maaş, -0.16285 ağırlıkla ayrılan kişi sayısını etkiler (bu küçük bir etki derecesidir). “-” işareti olumsuz bir etkiyi gösterir: maaş ne kadar yüksekse, bırakan o kadar az olur. Hangisi adil.

28 Ekim

İyi günler, sevgili blog okuyucuları! Bugün doğrusal olmayan regresyonlardan bahsedeceğiz. Doğrusal regresyonların çözümü LINK'te bulunabilir.

Bu yöntem esas olarak ekonomik modelleme ve tahminde kullanılır. Amacı, iki gösterge arasındaki ilişkiyi gözlemlemek ve belirlemektir.

Doğrusal olmayan regresyonların ana türleri şunlardır:

• polinom (kuadratik, kübik);
• hiperbolik;
• Güç yasası;
• gösterge;
• logaritmik.

Çeşitli kombinasyonlar da kullanılabilir. Örneğin, bankacılık, sigorta ve demografik çalışmalarda zaman serisi analistleri için bir tür logaritmik regresyon olan Gompzer eğrisi kullanılır.

Doğrusal olmayan regresyonları kullanarak tahminde esas olan, iki parametre arasında yakın bir ilişki olup olmadığını bize gösterecek olan korelasyon katsayısını bulmaktır. Kural olarak, korelasyon katsayısı 1'e yakınsa, o zaman bir bağlantı vardır ve tahmin oldukça doğru olacaktır. Doğrusal olmayan regresyonların bir diğer önemli unsuru, ortalama bağıl hatadır ( A ) aralığında ise<8…10%, значит модель достаточно точна.

Bu konuda belki teorik bloğu bitirip pratik hesaplamalara geçeceğiz.

15 yıllık araba satışları tablomuz var (X ile göster), ölçüm adımlarının sayısı argüman n olacak, bu dönemler için de gelir var (Y ile göster), gelirin ne olacağını tahmin etmemiz gerekiyor gelecekte ol. Aşağıdaki tabloyu oluşturalım:

Araştırma için şu denklemi çözmemiz gerekiyor (Y'nin X'e bağımlılığı): y = ax 2 + bx + c + e. Bu ikili ikinci dereceden bir regresyondur. Bu durumda bilinmeyen argümanları bulmak için en küçük kareler yöntemini uygulayalım - a, b, c. Formun bir cebirsel denklem sistemine yol açacaktır:

Bu sistemi çözmek için örneğin Cramer yöntemini kullanacağız. Sisteme dahil edilen toplamların bilinmeyenlerin katsayıları olduğunu görüyoruz. Bunları hesaplamak için tabloya birkaç sütun (D, E, F, G, H) ekleyin ve hesaplamaların anlamına göre işaretleyin - D sütununda x'in karesini, E'de bir küpte, F'de 4. güç, G'de x ve y üslerini H'de x'in karesini çarparız ve y ile çarparız.

Denklemi çözmek için gerekli olanlarla dolu bir form tablosu alacaksınız.

bir matris oluşturalım A denklemlerin sol tarafındaki bilinmeyenlerin katsayılarından oluşan bir sistem. A22 hücresine yerleştirin ve “ bir =". Regresyonu çözmek için seçtiğimiz denklem sistemini takip ediyoruz.

Yani, B21 hücresinde, X üssünün dördüncü güce yükseltildiği sütunun toplamını koymalıyız - F17. Sadece hücreye atıfta bulunalım - "= F17". Sonra, X'in bir küp - E17'ye yükseltildiği sütunun toplamına ihtiyacımız var, sonra kesinlikle sisteme göre gidiyoruz. Bu nedenle, tüm matrisi doldurmamız gerekecek.

Cramer'in algoritmasına göre, A'ya benzer bir A1 matrisi yazıyoruz, ilk sütunun elemanları yerine sistemin denklemlerinin sağ taraflarının elemanlarının yerleştirilmesi gerekiyor. Yani, X sütununun karesi çarpı Y'nin toplamı, XY sütununun toplamı ve Y sütununun toplamı.

Ayrıca iki matrise daha ihtiyacımız var - ikinci ve üçüncü sütunların denklemlerin sağ taraflarının katsayılarından oluşacağı A2 ve A3 diyelim. Resim böyle olacak.

Seçilen algoritmanın ardından elde edilen matrislerin determinantlarının (determinant, D) değerlerini hesaplamamız gerekecek. MOPRED formülünü kullanalım. Sonuçları J21: K24 hücrelerine yerleştirin.

Kramer'e göre denklemin katsayılarının hesaplanması, aşağıdaki formüle göre ilgili belirleyicilerin karşısındaki hücrelerde yapılacaktır: a(M22 hücresinde) - "= K22 / K21"; B(M23 hücresinde) - "= K23 / K21"; ile birlikte(M24 hücresinde) - "= K24 / K21".

İstediğimiz ikili ikinci dereceden regresyon denklemini elde ederiz:

y = -0.074x 2 + 2.151x + 6.523

Doğrusal bağlantının yakınlığını korelasyon indeksi ile tahmin edelim.

Hesaplama için tabloya ek bir J sütunu ekleyin (buna y * diyelim). Hesaplama şu şekilde olacaktır (elde ettiğimiz regresyon denklemine göre) - "= $ M $ 22 * ​​​​B2 * B2 + $ M $ 23 * B2 + $ M $ 24". J2 hücresine yerleştirin. Otomatik tamamlama işaretçisini J16 hücresine kadar uzatmak için kalır.

Toplamları (Y-Y ortalaması) 2 hesaplamak için, karşılık gelen formüllerle tabloya K ve L sütunlarını ekleyin. Y sütununun ortalaması, ORTALAMA işlevi kullanılarak hesaplanır.

K25 hücresine korelasyon indeksini hesaplamak için formülü yerleştireceğiz - "= KÖK (1- (K17 / L17))".

0,959 değerinin 1'e çok yakın olduğunu görüyoruz, bu da satışlar ile yıllar arasında doğrusal olmayan yakın bir ilişki olduğu anlamına geliyor.

Elde edilen ikinci dereceden regresyon denkleminin (belirleme indeksi) uydurma kalitesini değerlendirmek için kalır. Korelasyon indeksinin karesi formülü kullanılarak hesaplanır. Yani, K26 hücresindeki formül çok basit olacaktır - "= K25 * K25".

0,920 faktörü 1'e yakındır ve bu da yüksek kaliteli uyumu gösterir.

Son adım, bağıl hatayı hesaplamaktır. Bir sütun ekleyin ve şu formülü girin: “= ABS ((C2-J2) / C2), ABS - modül, mutlak değer. İşaretçiyi aşağı sürükleyin ve M18 hücresinde ortalama değeri (ORTALAMA) görüntüleyin, hücrelere bir yüzde biçimi atayın. Elde edilen sonuç - %7,79 kabul edilebilir hata değerleri içinde<8…10%. Значит вычисления достаточно точны.

Gerekirse, elde edilen değerlere göre bir grafik oluşturabiliriz.

Örnek bir dosya ektedir - LINK!

Kategoriler:/ / 28.10.2017 tarihinden itibaren