Excel'de doğrusal olmayan bir regresyon modeli oluşturma. Excel'de regresyon denklemi nasıl yapılır

Regresyon çizgisi, fenomenler arasındaki ilişkinin grafiksel bir yansımasıdır. Excel'de çok net bir şekilde bir regresyon çizgisi oluşturabilirsiniz.

Bu gerektirir:

1.Excel programını açın

2. Verilerle sütunlar oluşturun. Örneğimizde, birinci sınıf öğrencilerinde saldırganlık ve kendinden şüphe duyma arasında bir regresyon çizgisi veya ilişki kuracağız. Deney 30 çocuğu içeriyordu, veriler Excel tablosunda sunuldu:

1 sütun - Konu numarası

2 sütun - saldırganlık puan olarak

3 sütun - kendinden şüphe etmek puan olarak

3.Ardından her iki sütunu da seçmeniz gerekir (sütun adı olmadan), sekmeye tıklayın sokmak , Seçme puan ve önerilen düzenlerden ilkini seçin işaretçilerle nokta .

4.Yani regresyon satırı için bir boşluğumuz var - sözde - dağılım grafiği... Regresyon çizgisine gitmek için ortaya çıkan şekle tıklamanız, sekmeye tıklamanız gerekir. yapıcı, panelde bul grafik düzenleri ve Seç m a ket9 , ayrıca diyor f(x)

5. Yani bir regresyon çizgimiz var. Grafik ayrıca denklemini ve korelasyon katsayısının karesini de gösterir.

6. Grafiğin adını, eksenlerin adını eklemek için kalır. Ayrıca dilerseniz lejantı kaldırabilir, yatay ızgara çizgilerinin sayısını azaltabilirsiniz (sekme Yerleşim , sonra ağ ). Sekmede temel değişiklikler ve ayarlar yapılır Düzen

Regresyon çizgisi MS Excel'de oluşturuldu. Artık eserin metnine eklenebilir.

Bu yazılım yardımcı programının çalışmalarında kullanıldığı ekonometri gibi disiplinler de dahil olmak üzere çeşitli faaliyet alanlarında yararlı olduğu bilinmektedir. Temel olarak, pratik ve laboratuvar alıştırmalarının tüm eylemleri, çalışmayı büyük ölçüde kolaylaştıran ve belirli eylemlerin ayrıntılı açıklamalarını veren Excel'de gerçekleştirilir. Bu nedenle, analiz araçlarından biri olan "Regresyon", en küçük kareler yöntemini kullanarak bir dizi gözlem için bir grafik seçmek için kullanılır. Programın bu aracının ne olduğunu ve kullanıcılara faydasının ne olduğunu düşünelim. Aşağıda ayrıca bir regresyon modeli oluşturmak için kısa ama anlaşılması kolay bir talimat bulunmaktadır.

Ana görevler ve regresyon türleri

Regresyon, verilen değişkenler arasındaki bir ilişkidir, bu nedenle bu değişkenlerin gelecekteki davranışlarının tahminini belirlemek mümkündür. Değişkenler, insan davranışı da dahil olmak üzere çeşitli periyodik olaylardır. Bu Excel analizi, bir veya daha fazla değişkenin değerlerinin belirli bir bağımlı değişken üzerindeki etkisini analiz etmek için kullanılır. Örneğin, bir mağazadaki satışlar, çeşitler, fiyatlar ve mağaza konumu gibi çeşitli faktörlerden etkilenir. Excel'deki regresyon sayesinde, mevcut satışların sonuçlarına göre bu faktörlerin her birinin etki derecesini belirleyebilir ve ardından elde edilen verileri başka bir aya veya yakındaki başka bir mağazaya yönelik satışları tahmin etmek için uygulayabilirsiniz.

Regresyon genellikle, bir grubun bağımlı veya içsel, diğerinin bağımsız veya dışsal olduğu iki değişken grubu arasındaki ilişkinin ilişkisini ve gücünü ortaya koyan basit bir denklem olarak sunulur. Birbiriyle ilişkili bir grup göstergenin varlığında, bağımlı değişken Y, akıl yürütme mantığına göre belirlenir ve geri kalanı bağımsız X değişkenleri olarak hareket eder.

Bir regresyon modeli oluşturmanın ana görevleri şunlardır:

1. Önemli bağımsız değişkenlerin seçimi (X1, X2,…, Xk).
2. Fonksiyon tipinin seçilmesi.
3. Katsayılar için tahminlerin oluşturulması.
4. Güven aralıklarının ve regresyon fonksiyonlarının oluşturulması.
5. Hesaplanan tahminlerin ve oluşturulan regresyon denkleminin öneminin kontrol edilmesi.

Birkaç tür regresyon analizi vardır:

• eşleştirilmiş (1 bağımlı ve 1 bağımsız değişken);
• çoklu (birkaç bağımsız değişken).

İki tür regresyon denklemi vardır:

1. Değişkenler arasında katı bir doğrusal ilişkiyi gösteren doğrusal.
2. Doğrusal Olmayan - Kuvvetler, kesirler ve trigonometrik işlevleri içerebilen denklemler.

Model oluşturma talimatı

Belirtilen yapıyı Excel'de tamamlamak için talimatları izlemelisiniz:

Daha fazla hesaplama için, Y-Değerleri, X-Değerleri, Sabit ve istatistikleri belirterek "Doğrusal ()" fonksiyonunu kullanın. Ardından, "Trend" işlevini kullanarak regresyon çizgisi üzerindeki nokta kümesini tanımlayın - Y-Values, X-Values, New Values, Const. Belirtilen parametreleri kullanarak, problemin belirtilen koşullarına göre katsayıların bilinmeyen değerini hesaplayın.

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 bu 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 onay kutusunu temizleyin. 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, gözlemlenen değerlere karşı tahmin edilen değerlerin grafiğini çizmek için onay kutusunu seçin.

Normal Olasılık Grafiği Normal olasılık grafiğini çizmek 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 istenen 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 (bilinmeyen parametreleri doğrusal olan) 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 x değerinin bir fonksiyonu olduğunda, m değerleri her bağımsız x değişkenine 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 ayrıca ek regresyon istatistikleri de verebilir.

LINEST(bilinen_y'ler; bilinen_x'ler; sabit; 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 tek 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'ler ile 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, regresyon için ek istatistikler 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)

Büyüklük	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şimi (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).

LINEST ve LOGEST fit 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 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, düz bir ç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.

Regresyon analizi ile Microsoft Excel, 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 = DOĞRU veya atlanmışsa, toplam kareler toplamı, gerçek y değerleri ile ortalama y değerleri arasındaki farkın karelerinin toplamına eşittir. 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ğeri yoktur.Yani bir veya daha fazla X sütununun silinmesi 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 fazla X sütununu bulursa, regresyon modelinden kaldırır. Silinen X sütunları, DOT çı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ın göstergesi olarak kullanılmalıdır. const = DOĞRU veya atlanmışsa, DOT, kesişme noktasını simüle etmek 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 dişiler için 1 ve erkekler için 0 değerlerine sahip bir sütun varsa, o zaman değerleri olabileceği için son sütun kaldırılır. “erkek cinsiyet göstergesi” sütunundan elde edilmiştir.

X'in sütunlarının eşdoğrusallık nedeniyle modelden çıkarılmadığı durumlar için df'nin hesaplanması şu şekildedir: bilinen_x'lerin k sütunu varsa ve const = DOĞRU veya 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 girerken, 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:

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

aşağıdaki biçimde 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.

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 nasıl işletme sayısına, ücretlerin büyüklüğüne ve diğer parametrelere 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 = bir 0 + bir 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.

Aktivasyon üzerine, eklenti "Veri" sekmesinde bulunacaktır.

Şimdi doğrudan regresyon analizine geçelim.

1. "Veri Analizi" araç menüsünü açın. "Regresyon" u 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ülenecek aralığı 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 işten 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.

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

Bu kavramın kendisi matematiğe 1886'da girmiştir. 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 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, yerleşik işlevlerin mevcut tablo verilerine uygulanması gerekir. 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 otomatik olarak elektronik tablo işlemcisinin yeni sayfasını 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-Square 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ı, ele aldığımız modeldeki tüm xi değişkenlerinin sıfır olması durumunda Y'nin değerinin ne olacağını gösterir. Başka bir deyişle, analiz edilen parametrenin değerinin belirli bir modelde açıklanmayan 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 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 göstergedir (bağımlı değişken) ve x 1, x 2,… x m gösterge-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 + ε şeklindeki lineer denklemler için bir normal denklem sistemi oluşturuyoruz (aşağıya bakınız)

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 karşılık gelen özelliğin varyansıdır.

OLS, standart 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ının doğru ve geçerli olduğu kabul edilir. Ek olarak, en küçük βi değerlerine sahip olanları atarak faktörleri filtrelemek gelenekseldir.

Lineer Regresyon Denklemi Kullanma Problemi

Son 8 aydaki 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. "Giriş aralığı Y" alanına bağımlı değişken (bu durumda ürünün yılın belirli aylarındaki fiyatı) için bir değer aralığı ve "Giriş" alanına girilmesi gerektiği unutulmamalıdır. X" aralığı - bağımsız değişken için (ay sayısı). Eylemleri "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 katsayılar ve "Y-kesişim" ç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's t 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ği hakkındaki doğru hipotezin sıfır olasılığına sahibiz. reddedilmek. Bilinmeyen katsayı için t = 5.79405 ve p = 0.001158. 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ı problemi 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ımı, ç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.