• Eşleştirilmiş bir regresyon modeli oluşturma indir excel tablosu. Konrad Carlberg. Microsoft Excel'de Regresyon Analizi

    Regresyon analizi, istatistiksel araştırmaların en popüler yöntemlerinden biridir. Bağımsız değişkenlerin bağımlı değişken üzerindeki etki derecesini belirlemek için kullanılabilir. Microsoft Excel'in işlevselliği, bu tür analizleri gerçekleştirmek için tasarlanmış araçlara sahiptir. Ne olduklarına ve nasıl kullanılacağına bir göz atalım.

    Ancak regresyon analizi yapmanızı sağlayan fonksiyonu kullanabilmeniz için öncelikle Analiz Paketini aktif hale getirmeniz gerekmektedir. Ancak o zaman bu prosedür için gerekli araçlar Excel şeridinde görünecektir.


    Şimdi sekmeye gittiğimizde "Veri", araç kutusundaki şeritte "Analiz" yeni bir düğme göreceğiz - "Veri analizi".

    Regresyon analizi türleri

    Birkaç tür regresyon vardır:

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

    Excel'de son tür regresyon analizinin uygulanması hakkında daha ayrıntılı olarak daha sonra konuşacağız.

    Excel'de Doğrusal Regresyon

    Aşağıda, örnek olarak, sokaktaki ortalama günlük hava sıcaklığını ve ilgili iş günü için mağaza müşteri sayısını gösteren bir tablo bulunmaktadır. Hava sıcaklığı şeklindeki hava koşullarının bir perakende kuruluşunun katılımını tam olarak nasıl etkileyebileceğini regresyon analizi yardımıyla öğrenelim.

    Genel lineer regresyon denklemi şuna benzer: Y = a0 + a1x1 + ... + axk. bu formülde Y etkisini incelemeye çalıştığımız değişken anlamına gelir. Bizim durumumuzda bu, alıcıların sayısıdır. Anlam X değişkeni etkileyen çeşitli faktörlerdir. Seçenekler A regresyon katsayılarıdır. Yani, belirli bir faktörün önemini belirlerler. dizin k aynı faktörlerin toplam sayısını gösterir.


    Analiz sonuçları analizi

    Regresyon analizinin sonuçları, ayarlarda belirtilen yerde tablo şeklinde görüntülenir.

    Ana göstergelerden biri, R Meydanı. Modelin kalitesini gösterir. Bizim durumumuzda bu katsayı 0,705 veya yaklaşık %70,5'tir. Bu kabul edilebilir bir kalite seviyesidir. 0,5'ten küçük bir ilişki kötüdür.

    Bir başka önemli gösterge, çizginin kesiştiği hücrede bulunur. "Y kavşağı" ve sütun "katsayılar". Burada Y'nin hangi değere sahip olacağı belirtilir ve bizim durumumuzda bu, diğer tüm faktörler sıfıra eşit olan alıcı sayısıdır. Bu tabloda bu değer 58.04'tür.

    Grafiğin kesişme noktasındaki değer "Değişken X1" Ve "katsayılar" Y'nin X'e bağımlılık düzeyini gösterir. Bizim durumumuzda bu, mağaza müşterisi sayısının sıcaklığa bağımlılık düzeyidir. 1.31 katsayısı oldukça yüksek bir etki göstergesi olarak kabul edilir.

    Gördüğünüz gibi, Microsoft Excel kullanarak bir regresyon analizi tablosu oluşturmak oldukça kolaydır. Ancak çıktıda elde edilen verilerle yalnızca eğitimli bir kişi çalışabilir ve bunların özünü anlayabilir.

    Önceki notlarda, yatırım fonu getirileri, Web sayfası yükleme süresi veya alkolsüz içecek tüketimi gibi tek bir sayısal değişkene odaklanılmıştır. Bu ve sonraki notlarda, bir veya daha fazla sayısal değişkenin değerlerine bağlı olarak sayısal bir değişkenin değerlerini tahmin etme yöntemlerini ele alacağız.

    Materyal, bir örnekle açıklanacaktır. Bir giyim mağazasında satış hacmini tahmin etmek. Sunflowers indirimli giyim mağazaları zinciri 25 yıldır sürekli genişlemektedir. Ancak, şirketin şu anda yeni satış noktaları seçmek için sistematik bir yaklaşımı yok. Şirketin yeni bir mağaza açmayı planladığı yer, sübjektif değerlendirmelere göre belirlenir. Seçim kriterleri, uygun kiralama koşulları veya yöneticinin mağazanın ideal konumu hakkındaki fikridir. Düşünün ki Özel Projeler ve Planlama Dairesi başkanısınız. Yeni mağazalar açmak için stratejik bir plan geliştirmekle görevlendirildiniz. Bu plan, yeni açılan mağazalardaki yıllık satış tahminini içermelidir. Satış alanı satışlarının doğrudan gelirle ilgili olduğuna inanıyor ve bu gerçeği karar verme sürecinize dahil etmek istiyorsunuz. Yeni mağaza büyüklüğüne göre yıllık satışları tahmin eden istatistiksel bir modeli nasıl geliştirirsiniz?

    Tipik olarak, bir değişkenin değerlerini tahmin etmek için regresyon analizi kullanılır. Amacı, bağımlı değişkenin veya yanıtın değerlerini en az bir bağımsız veya açıklayıcı değişkenin değerlerinden tahmin eden istatistiksel bir model geliştirmektir. Bu notta, bağımlı değişkenin değerlerini tahmin etmenizi sağlayan istatistiksel bir yöntem olan basit bir doğrusal regresyonu ele alacağız. Y bağımsız değişkenin değerlerine göre X. Aşağıdaki notlar, bağımsız değişkenin değerlerini tahmin etmek için tasarlanmış bir çoklu regresyon modelini açıklayacaktır. Y birkaç bağımlı değişkenin değerlerine göre ( X 1 , X 2 , …, X k).

    Notu veya formatında indirin, formatta örnekler

    Regresyon modeli türleri

    Nerede ρ 1 otokorelasyon katsayısıdır; Eğer ρ 1 = 0 (otokorelasyon yok), D≈ 2; Eğer ρ 1 ≈ 1 (pozitif otokorelasyon), D≈ 0; Eğer ρ 1 = -1 (negatif otokorelasyon), D ≈ 4.

    Uygulamada, Durbin-Watson kriterinin uygulanması, değerin karşılaştırılmasına dayanır. D kritik teorik değerlerle d L Ve d sen belirli sayıda gözlem için N, modelin bağımsız değişken sayısı k(basit doğrusal regresyon için k= 1) ve anlamlılık düzeyi α. Eğer D< d L , rastgele sapmaların bağımsızlığı hipotezi reddedilir (dolayısıyla, pozitif bir otokorelasyon vardır); Eğer D > d U, hipotez reddedilmez (yani otokorelasyon yoktur); Eğer dL< D < d U karar vermek için yeterli sebep yoktur. Hesaplanan değer D 2'yi aşıyor, o zaman d L Ve d sen karşılaştırılan katsayının kendisi değildir D ve ifade (4 – D).

    Durbin-Watson istatistiklerini Excel'de hesaplamak için, Şekil 1'deki alt tabloya dönüyoruz. 14 Bakiye çekme. İfadedeki (10) pay, = TOPLAKDIFF(dizi1, dizi2) ve payda = TOPLAM(dizi) işlevi kullanılarak hesaplanır (Şekil 16).

    Pirinç. 16. Durbin-Watson istatistiklerini hesaplama formülleri

    bizim örneğimizde D= 0.883. Ana soru şudur: Durbin-Watson istatistiğinin hangi değeri, pozitif bir otokorelasyon olduğu sonucuna varmak için yeterince küçük kabul edilmelidir? D değerini kritik değerlerle ilişkilendirmek gerekir ( d L Ve d sen) gözlem sayısına bağlı olarak N ve anlamlılık düzeyi α (Şekil 17).

    Pirinç. 17. Durbin-Watson istatistiklerinin kritik değerleri (tablo parçası)

    Bu nedenle, evinize mal teslim eden bir mağazadaki satış hacmi probleminde bir bağımsız değişken vardır ( k= 1), 15 gözlem ( N= 15) ve anlamlılık seviyesi α = 0.05. Buradan, d L= 1.08 ve Dsen= 1.36. Çünkü D = 0,883 < d L= 1.08, artıklar arasında pozitif otokorelasyon vardır, en küçük kareler yöntemi uygulanamaz.

    Eğim ve Korelasyon Katsayısı Hakkında Hipotezlerin Test Edilmesi

    Yukarıdaki regresyon yalnızca tahmin için uygulanmıştır. Regresyon katsayılarını belirlemek ve bir değişkenin değerini tahmin etmek Y belirli bir değişken değeri için X en küçük kareler yöntemi kullanıldı. Ek olarak, tahminin standart hatasını ve karma korelasyon katsayısını da dikkate aldık. Kalıntı analizi, en küçük kareler yönteminin uygulanabilirlik koşullarının ihlal edilmediğini ve basit doğrusal regresyon modelinin yeterli olduğunu teyit ediyorsa, örneklem verilerine dayanarak, popülasyondaki değişkenler arasında doğrusal bir ilişki olduğu söylenebilir.

    BaşvuruT - eğim kriterleri. Nüfus eğimi β 1'in sıfıra eşit olup olmadığı kontrol edilerek, değişkenler arasında istatistiksel olarak anlamlı bir ilişki olup olmadığı belirlenebilir. X Ve Y. Bu hipotez reddedilirse, değişkenler arasında X Ve Y doğrusal bir ilişki vardır. Boş ve alternatif hipotezler şu şekilde formüle edilmiştir: H 0: β 1 = 0 (doğrusal ilişki yok), H1: β 1 ≠ 0 (doğrusal ilişki var). bir manastır T-istatistik, numune eğimi ile varsayımsal popülasyon eğimi arasındaki farkın eğim tahmininin standart hatasına bölünmesine eşittir:

    (11) T = (B 1 – β 1 ) / Şb 1

    Nerede B 1 örnek verilere dayalı doğrudan regresyonun eğimi, β1 doğrudan genel popülasyonun varsayımsal eğimi, ve test istatistikleri T sahip T- ile dağıtım n - 2özgürlük derecesi.

    α = 0.05'te mağaza büyüklüğü ile yıllık satışlar arasında istatistiksel olarak anlamlı bir ilişki olup olmadığını kontrol edelim. T-kriter kullanılırken diğer parametrelerle birlikte görüntülenir Analiz paketi(seçenek gerileme). Analiz Paketinin tüm sonuçları Şekil 1'de gösterilmektedir. 4, t-istatistikleri ile ilgili bir parça - Şek. 18.

    Pirinç. 18. Başvuru sonuçları T

    Çünkü mağaza sayısı N= 14 (bkz. Şekil 3), kritik değer T- α = 0.05 anlamlılık seviyesindeki istatistikler aşağıdaki formülle bulunabilir: t L=STUDENT.INV(0,025;12) = -2,1788 burada 0,025 anlamlılık düzeyinin yarısıdır ve 12 = N – 2; t sen\u003d ÖĞRENCİ.RUB (0,975, 12) \u003d +2,1788.

    Çünkü T-istatistik = 10.64 > t sen= 2.1788 (Şekil 19), sıfır hipotezi H 0 reddedildi. Diğer tarafta, R için değer X\u003d 10.6411, \u003d 1-STUDENT.DAĞ (D3, 12, DOĞRU) formülü ile hesaplanır, yaklaşık olarak sıfıra eşittir, bu nedenle hipotez H 0 tekrar reddedilir. Gerçek şu ki R-değer neredeyse sıfırdır, yani mağaza büyüklüğü ile yıllık satışlar arasında gerçek bir doğrusal ilişki olmasaydı, onu doğrusal regresyon kullanarak bulmak neredeyse imkansız olurdu. Bu nedenle, ortalama yıllık mağaza satışları ile mağaza büyüklüğü arasında istatistiksel olarak anlamlı bir doğrusal ilişki vardır.

    Pirinç. 19. Genel popülasyonun eğimi hakkındaki hipotezin 0,05 anlamlılık düzeyinde ve 12 serbestlik derecesinde test edilmesi

    BaşvuruF - eğim kriterleri. Basit bir doğrusal regresyonun eğimi hakkındaki hipotezleri test etmek için alternatif bir yaklaşım, F-kriter. Hatırlamak F-ölçüt, iki varyans arasındaki ilişkiyi test etmek için kullanılır (ayrıntılara bakın). Eğim hipotezini test ederken, rasgele hataların ölçüsü hata varyansıdır (hataların karelerinin toplamı bölü serbestlik derecesi sayısı), dolayısıyla F-test, regresyon tarafından açıklanan varyansın oranını kullanır (yani, değerler SSR bağımsız değişken sayısına bölünür k), hata varyansına ( MSE=SYX 2 ).

    bir manastır F-istatistik, hata varyansına (MSE) bölünen regresyondan (MSR) kaynaklanan ortalama kare sapmalara eşittir: F = MSR/ MSE, Nerede MSR=SSR / k, MSE =SSE/(N– k – 1), k regresyon modelindeki bağımsız değişken sayısıdır. test istatistikleri F sahip F- ile dağıtım k Ve N– k – 1özgürlük derecesi.

    Belirli bir önem düzeyi α için, karar kuralı şu şekilde formüle edilir: F > Ksen, sıfır hipotezi reddedilir; aksi takdirde reddedilmez. Varyans analizinin özet tablosu şeklinde sunulan sonuçlar, şekil 2'de gösterilmektedir. 20.

    Pirinç. 20. Regresyon katsayısının istatistiksel anlamlılığının hipotezini test etmek için varyans analizi tablosu

    benzer şekilde T-kriter F-kriter kullanırken tabloda görüntülenir Analiz paketi(seçenek gerileme). Çalışmanın tam sonuçları Analiz paketiŞek. 4, ilgili parça F-istatistikler - Şek. 21.

    Pirinç. 21. Başvuru sonuçları F- Excel Analysis ToolPack kullanılarak elde edilen kriterler

    F-istatistik 113.23 ve R sıfıra yakın değer (hücre önemiF). Anlamlılık düzeyi α 0,05 ise, kritik değeri belirleyin F-1 ve 12 serbestlik dereceli dağılımlar formülden elde edilebilir FU\u003d F. OBR (1-0.05; 1; 12) \u003d 4.7472 (Şek. 22). Çünkü F = 113,23 > FU= 4,7472 ve R-değer 0'a yakın< 0,05, нулевая гипотеза H 0 sapar, yani Bir mağazanın büyüklüğü, yıllık satış hacmi ile yakından ilişkilidir.

    Pirinç. 22. Genel popülasyonun eğimi hakkındaki hipotezin 0,05 anlamlılık düzeyinde, bir ve 12 serbestlik derecesiyle test edilmesi

    Eğim β 1 içeren güven aralığı. Değişkenler arasında doğrusal bir ilişkinin varlığına ilişkin hipotezi test etmek için, β 1 eğimini içeren bir güven aralığı oluşturabilir ve β 1 = 0 varsayımsal değerinin bu aralığa ait olduğundan emin olabilirsiniz. β 1 eğimini içeren güven aralığının merkezi örnek eğimdir B 1 ve sınırları miktarlardır 1 ±t n –2 Şb 1

    Şek. 18, B 1 = +1,670, N = 14, Şb 1 = 0,157. T 12 \u003d ÖĞRENCİ.OBR (0.975, 12) \u003d 2.1788. Buradan, 1 ±t n –2 Şb 1 = +1,670 ± 2,1788 * 0,157 = +1,670 ± 0,342 veya + 1,328 ≤ β 1 ≤ +2,012. Böylece, popülasyonun 0,95 olasılıkla eğimi +1,328 ila +2,012 aralığındadır (yani, 1.328.000 ila 2.012.000 ABD Doları). Bu değerler sıfırdan büyük olduğu için yıllık satışlar ile mağaza alanı arasında istatistiksel olarak anlamlı doğrusal bir ilişki vardır. Güven aralığı sıfır içeriyorsa, değişkenler arasında herhangi bir ilişki olmayacaktır. Ek olarak, güven aralığı, her 1.000 metrekarede bir anlamına gelir. feet, ortalama satışlarda 1.328.000 $'lık artışla 2.012.000 $'a çıkıyor.

    KullanımT - korelasyon katsayısı için kriterler. korelasyon katsayısı tanıtıldı R, iki sayısal değişken arasındaki ilişkinin bir ölçüsüdür. İki değişken arasında istatistiksel olarak anlamlı bir ilişki olup olmadığını belirlemek için kullanılabilir. Her iki değişkenin popülasyonları arasındaki korelasyon katsayısını ρ sembolü ile gösterelim. Sıfır ve alternatif hipotezler aşağıdaki gibi formüle edilmiştir: H 0: ρ = 0 (korelasyon yok), H 1: ρ ≠ 0 (bir korelasyon vardır). Bir korelasyonun varlığının kontrol edilmesi:

    Nerede R = + , Eğer B 1 > 0, R = – , Eğer B 1 < 0. Тестовая статистика T sahip T- ile dağıtım n - 2özgürlük derecesi.

    Sunflowers mağaza zinciri probleminde r2= 0.904 ve b 1- +1.670 (bkz. Şekil 4). Çünkü b 1> 0, yıllık satışlar ile mağaza büyüklüğü arasındaki korelasyon katsayısı R= +√0,904 = +0,951. Bu değişkenler arasında hiçbir korelasyon olmadığına dair sıfır hipotezini test edelim. T- İstatistik:

    α = 0.05 anlamlılık düzeyinde, sıfır hipotezi reddedilmelidir çünkü T= 10.64 > 2.1788. Dolayısıyla yıllık satışlar ile mağaza büyüklüğü arasında istatistiksel olarak anlamlı bir ilişki olduğu söylenebilir.

    Popülasyon eğimleri hakkındaki çıkarımları tartışırken, hipotezleri test etmek için güven aralıkları ve kriterler birbirinin yerine kullanılabilen araçlardır. Ancak korelasyon katsayısını içeren güven aralığının hesaplanması istatistiğin örnekleme dağılımının şekli nedeniyle daha zor olmaktadır. R gerçek korelasyon katsayısına bağlıdır.

    Matematiksel beklenti tahmini ve bireysel değerlerin tahmini

    Bu bölüm, beklenen yanıtı tahmin etme yöntemlerini tartışmaktadır. Y ve bireysel değerlerin tahminleri Y değişkenin verilen değerleri için X.

    Bir güven aralığının oluşturulması.Örnek 2'de (yukarıdaki bölüme bakın) en küçük kareler yöntemi) regresyon denklemi, değişkenin değerini tahmin etmeyi mümkün kıldı Y X. Bir perakende satış yeri için yer seçme probleminde, 4000 metrekare alana sahip bir mağazada yıllık ortalama satış fit 7.644 milyon dolara eşitti.Ancak, genel nüfusun matematiksel beklentisinin bu tahmini bir noktadır. genel popülasyonun matematiksel beklentisini tahmin etmek için bir güven aralığı kavramı önerildi. Benzer şekilde, bir kavram tanıtılabilir Yanıtın matematiksel beklentisi için güven aralığı bir değişkenin belirli bir değeri için X:

    Nerede , = B 0 + B 1 X ben– tahmin edilen değer değişkeni Y de X = X ben, S YX ortalama kare hatasıdır, Nörneklem büyüklüğüdür, XBen- değişkenin verilen değeri X, µ Y|X = XBen– bir değişkenin matematiksel beklentisi Y de X = Ben,SSX=

    Formül (13)'ün analizi, güven aralığının genişliğinin birkaç faktöre bağlı olduğunu göstermektedir. Belirli bir anlamlılık düzeyinde, ortalama karesel hata kullanılarak ölçülen, regresyon çizgisi etrafındaki dalgalanmaların genliğinde bir artış, aralığın genişliğinde bir artışa yol açar. Öte yandan, beklendiği gibi, örneklem büyüklüğündeki bir artışa aralığın daralması eşlik etmektedir. Ayrıca değerlere bağlı olarak aralığın genişliği de değişmektedir. XBen. Değişkenin değeri ise Y miktarlar için tahmin X, ortalama değere yakın , güven aralığı, ortalamadan uzak değerler için yanıtı tahmin ederken olduğundan daha dar çıkıyor.

    Bir mağaza için yer seçerken 4000 metrekare alana sahip tüm mağazalarda yıllık ortalama satışlar için %95 güven aralığı oluşturmak istiyoruz diyelim. ayak:

    Dolayısıyla 4.000 metrekare alana sahip tüm mağazalarda yıllık ortalama satış hacmi. feet, %95 olasılıkla 6.971 ile 8.317 milyon dolar aralığında yer alıyor.

    Öngörülen değer için güven aralığını hesaplayın. Değişkenin belirli bir değeri için yanıtın matematiksel beklentisi için güven aralığına ek olarak X tahmin edilen değer için güven aralığını bilmek genellikle gereklidir. Böyle bir güven aralığını hesaplamak için kullanılan formül, formül (13)'e çok benzer olsa da, bu aralık tahmin edilen bir değeri içerir ve parametrenin bir tahminini içermez. Öngörülen yanıt aralığı YX = Xi değişkenin belirli bir değeri için XBen formül ile belirlenir:

    Bir perakende satış yeri için yer seçerken, 4000 metrekare alana sahip bir mağazada öngörülen yıllık satış hacmi için %95'lik bir güven aralığı oluşturmak istediğimizi varsayalım. ayak:

    Bu nedenle, 4.000 metrekarelik bir alan için öngörülen yıllık satış hacmi. feet, %95 olasılıkla 5,433 ile 9,854 milyon dolar aralığında yer almaktadır.Gördüğünüz gibi, tahmin edilen yanıt değerinin güven aralığı, matematiksel beklentisinin güven aralığından çok daha geniştir. Bunun nedeni, bireysel değerleri tahmin etmedeki değişkenliğin, beklenen değeri tahmin etmekten çok daha fazla olmasıdır.

    Regresyon kullanımıyla ilgili tuzaklar ve etik sorunlar

    Regresyon analizi ile ilgili zorluklar:

    • En küçük kareler yönteminin uygulanabilirlik koşullarının göz ardı edilmesi.
    • En küçük kareler yönteminin uygulanabilirlik koşullarının hatalı bir tahmini.
    • En küçük kareler yönteminin uygulanabilirlik koşullarına aykırı olarak alternatif yöntemlerin yanlış seçimi.
    • Çalışma konusu hakkında derinlemesine bilgi sahibi olmadan regresyon analizinin uygulanması.
    • Açıklayıcı değişken aralığının ötesinde regresyonun ekstrapolasyonu.
    • İstatistiksel ve nedensel ilişkiler arasındaki karışıklık.

    Elektronik tabloların ve istatistiksel yazılımların yaygın kullanımı, regresyon analizinin kullanılmasını engelleyen hesaplama problemlerini ortadan kaldırmıştır. Ancak bu durum regresyon analizinin yeterli nitelik ve bilgiye sahip olmayan kullanıcılar tarafından da kullanılmaya başlamasına neden olmuştur. Birçoğunun en küçük kareler yönteminin uygulanabilirlik koşulları hakkında hiçbir fikri yoksa ve uygulamalarını nasıl kontrol edeceklerini bilmiyorsa, kullanıcılar alternatif yöntemleri nasıl bilebilirler?

    Araştırmacı, sayıları taşlayarak - kayma, eğim ve karışık korelasyon katsayısını hesaplayarak - kendini kaptırmamalıdır. Daha derin bilgiye ihtiyacı var. Bunu ders kitaplarından alınan klasik bir örnekle açıklayalım. Anscombe, Şekil 1'de gösterilen dört veri setinin hepsinin olduğunu gösterdi. 23 aynı regresyon parametrelerine sahiptir (Şekil 24).

    Pirinç. 23. Dört yapay veri seti

    Pirinç. 24. Dört yapay veri setinin regresyon analizi; ile yapılır Analiz paketi(resmi büyütmek için resmin üzerine tıklayın)

    Dolayısıyla, regresyon analizi açısından tüm bu veri kümeleri tamamen aynıdır. Analiz burada biterse, pek çok faydalı bilgiyi kaybederiz. Bu, bu veri kümeleri için oluşturulan dağılım grafikleri (Şekil 25) ve artık grafikler (Şekil 26) ile kanıtlanmaktadır.

    Pirinç. 25. Dört veri kümesi için dağılım grafikleri

    Dağılım grafikleri ve kalıntı grafikleri, bu verilerin birbirinden farklı olduğunu göstermektedir. Düz bir çizgi boyunca dağıtılan tek küme, A kümesidir. A kümesinden hesaplanan artıkların grafiğinin bir modeli yoktur. Aynı şey B, C ve D kümeleri için söylenemez. B kümesi için çizilen dağılım grafiği, belirgin bir ikinci dereceden desen gösterir. Bu sonuç, parabolik bir şekle sahip artıkların çizimi ile doğrulanır. Dağılım grafiği ve kalıntı grafiği, veri kümesi B'nin bir aykırı değer içerdiğini gösterir. Bu durumda, aykırı değeri veri setinden çıkarmak ve analizi tekrarlamak gerekir. Gözlemlerdeki aykırı değerleri tespit etme ve ortadan kaldırma tekniğine etki analizi denir. Aykırı değer ortadan kaldırıldıktan sonra, modelin yeniden değerlendirilmesinin sonucu tamamen farklı olabilir. D veri kümesinden çizilen bir dağılım grafiği, ampirik modelin tek bir cevaba büyük ölçüde bağımlı olduğu alışılmadık bir durumu gösterir ( X 8 = 19, Y 8 = 12.5). Bu tür regresyon modellerinin özellikle dikkatli bir şekilde hesaplanması gerekir. Bu nedenle, saçılma ve kalıntı grafikleri, regresyon analizi için temel bir araçtır ve bunun ayrılmaz bir parçası olmalıdır. Onlar olmadan, regresyon analizi güvenilir değildir.

    Pirinç. 26. Dört veri seti için artıkların grafikleri

    Regresyon analizinde tuzaklardan nasıl kaçınılır:

    • Değişkenler arasındaki olası ilişkinin analizi X Ve Y her zaman bir dağılım grafiği ile başlayın.
    • Bir regresyon analizinin sonuçlarını yorumlamadan önce, uygulanabilirliği için koşulları kontrol edin.
    • Kalıntıları bağımsız değişkene karşı çizin. Bu, ampirik modelin gözlem sonuçlarına nasıl karşılık geldiğini belirlemeye ve varyansın sabitliğinin ihlalini tespit etmeye izin verecektir.
    • Hataların normal dağılımı varsayımını test etmek için histogramlar, gövde ve yaprak çizimleri, kutu çizimleri ve normal dağılım çizimlerini kullanın.
    • En küçük kareler yönteminin uygulanabilirlik koşulları sağlanmıyorsa alternatif yöntemler kullanın (örneğin, ikinci dereceden veya çoklu regresyon modelleri).
    • En küçük kareler yönteminin uygulanabilirlik koşulları sağlanıyorsa, regresyon katsayılarının istatistiksel anlamlılığına ilişkin hipotezin test edilmesi ve matematiksel beklenti ile tahmin edilen yanıt değerini içeren güven aralıklarının oluşturulması gerekir.
    • Bağımlı değişkenin değerlerini bağımsız değişkenin aralığı dışında tahmin etmekten kaçının.
    • İstatistiksel bağımlılıkların her zaman nedensel olmadığını unutmayın. Değişkenler arasındaki korelasyonun, aralarında nedensel bir ilişki olduğu anlamına gelmediğini unutmayın.

    Özet. Blok diyagramda (Şekil 27) gösterildiği gibi, not basit bir doğrusal regresyon modelini, uygulanabilirlik koşullarını ve bu koşulları test etme yollarını açıklar. Dikkate alınan T- regresyonun eğiminin istatistiksel önemini test etme kriteri. Bağımlı değişkenin değerlerini tahmin etmek için bir regresyon modeli kullanılmıştır. Yıllık satış hacminin mağaza alanına bağımlılığının incelendiği bir perakende satış yeri için yer seçimi ile ilgili bir örnek ele alınmıştır. Elde edilen bilgiler, mağaza için daha doğru bir yer seçmenize ve yıllık satışlarını tahmin etmenize olanak tanır. Aşağıdaki notlarda, çoklu regresyon modellerinin yanı sıra regresyon analizi tartışması devam edecektir.

    Pirinç. 27. Bir notun blok diyagramı

    Levin ve diğerleri kitabından materyaller Yöneticiler için istatistikler kullanılır. - M.: Williams, 2004. - s. 792–872

    Bağımlı değişken kategorik ise lojistik regresyon uygulanmalıdır.

    İÇİNDE mükemmel doğrusal bir regresyon çizmenin daha da hızlı ve daha kolay bir yolu vardır (ve hatta ana doğrusal olmayan regresyon türleri için aşağıya bakın). Bu şu şekilde yapılabilir:

    1) veri içeren sütunları seçin X Ve Y(bu sırada olmalılar!);

    2) çağrı Grafik Sihirbazı ve bir grupta seçin Tip – noktalı ve hemen basın Hazır;

    3) diyagramın seçimini kaldırmadan beliren ana menü öğesini seçin Diyagram, içinde öğeyi seçmeniz gereken Trend çizgisi ekle;

    4) beliren iletişim kutusunda trend çizgisi sekme Tip seçmek Doğrusal;

    5) sekmesi Seçenekler anahtarı etkinleştirilebilir Denklemi grafikte göster katsayıların (4.5) hesaplanacağı doğrusal regresyon denklemini (4.4) görmenizi sağlayacak.

    6) Aynı sekmede, anahtarı etkinleştirebilirsiniz Yaklaşım güveninin (R^2) değerini diyagrama koyun. Bu değer korelasyon katsayısının (4.3) karesidir ve hesaplanan denklemin deneysel bağımlılığı ne kadar iyi tanımladığını gösterir. Eğer R 2 bire yakınsa, o zaman teorik regresyon denklemi deneysel bağımlılığı iyi tanımlar (teori deneyle uyumludur) ve eğer R 2 sıfıra yakınsa, bu denklem deneysel bağımlılığı açıklamak için uygun değildir (teori deneyle uyuşmaz).

    Açıklanan eylemleri gerçekleştirmenin bir sonucu olarak, regresyon grafiğini ve denklemini içeren bir diyagram elde edeceksiniz.

    §4.3. Ana doğrusal olmayan regresyon türleri

    Parabolik ve polinom regresyon.

    Parabolik değer bağımlılığı Y değerden X ikinci dereceden bir fonksiyonla (2. dereceden parabol) ifade edilen bağımlılık şu şekilde adlandırılır:

    Bu denklem denir parabolik regresyon Y Açık X. Seçenekler A, B, İle isminde parabolik regresyon katsayıları. Parabolik regresyon katsayılarının hesaplanması her zaman külfetlidir, bu nedenle hesaplamalar için bir bilgisayar kullanılması önerilir.

    Parabolik regresyonun Denklem (4.8), polinom adı verilen daha genel bir regresyonun özel bir halidir. polinom değer bağımlılığı Y değerden X polinom tarafından ifade edilen bağımlılık olarak adlandırılır N-inci sıra:

    sayılar nerede bir ben (Ben=0,1,…, N) arandı polinom regresyon katsayıları.

    Güç gerilemesi.

    Güç değer bağımlılığı Y değerden X formun bağımlılığı olarak adlandırılır:

    Bu denklem denir güç regresyon denklemi Y Açık X. Seçenekler A Ve B isminde güç regresyon katsayıları.

    ln=ln A+B ln X. (4.11)

    Bu denklem, düzlemde logaritmik koordinat eksenleri ln olan düz bir çizgiyi tanımlar. X ve ln. Bu nedenle, güç regresyonunun uygulanabilirliği için kriter, ampirik verilerin logaritma noktalarının ln olması gerekliliğidir. x ben ve ln Ben düz çizgiye en yakındı (4.11).

    üstel regresyon.

    örnek(veya üstel) miktarın bağımlılığı Y değerden X formun bağımlılığı olarak adlandırılır:

    (veya ). (4.12)

    Bu denklem denir üstel denklem(veya üstel) regresyon Y Açık X. Seçenekler A(veya k) Ve B isminde üstel(veya üstel) gerileme.

    Güç regresyon denkleminin her iki tarafının logaritmasını alırsak, denklemi elde ederiz.

    ln = X ln A+ln B(veya ln = k x+ln B). (4.13)

    Bu denklem, bir ln niceliğinin logaritmasının başka bir niceliğe doğrusal bağımlılığını tanımlar. X. Bu nedenle, güç regresyonunun uygulanabilirliği için kriter, ampirik veri noktalarının aynı büyüklükte olması gerekliliğidir. x ben ve ln başka bir değerin logaritmaları Ben düz çizgiye en yakındı (4.13).

    logaritmik regresyon.

    Logaritmik değer bağımlılığı Y değerden X formun bağımlılığı olarak adlandırılır:

    =A+B ln X. (4.14)

    Bu denklem denir logaritmik regresyon Y Açık X. Seçenekler A Ve B isminde logaritmik regresyon katsayıları.

    hiperbolik regresyon.

    hiperbolik değer bağımlılığı Y değerden X formun bağımlılığı olarak adlandırılır:

    Bu denklem denir hiperbolik regresyon denklemi Y Açık X. Seçenekler A Ve B isminde hiperbolik regresyon katsayıları ve en küçük kareler yöntemi ile belirlenir. Bu yöntemi uygulamak formüllere yol açar:

    Formüllerde (4.16-4.17), toplama indeks üzerinden gerçekleştirilir Ben birden gözlem sayısına N.

    maalesef mükemmel hiperbolik regresyon katsayılarını hesaplayan bir fonksiyon yoktur. Ölçülen değerlerin ters orantılılıkla ilişkili olduğunun kesin olarak bilinmediği durumlarda, hiperbolik regresyon denklemi yerine bir güç regresyon denklemi aranması önerilir, bu nedenle mükemmel bulmak için bir prosedür var. Ölçülen değerler arasında hiperbolik bir bağımlılık varsayılırsa, regresyon katsayılarının yardımcı hesaplama tabloları ve formüller (4.16-4.17) kullanılarak toplama işlemleri kullanılarak hesaplanması gerekecektir.

    MS Excel paketi, doğrusal bir regresyon denklemi oluştururken işin çoğunu çok hızlı bir şekilde yapmanızı sağlar. Sonuçların nasıl yorumlanacağını anlamak önemlidir.

    Çalışmak için eklenti gerektirir Analiz paketi menü öğesinde etkinleştirilmesi gereken Hizmet\Eklentiler

    Excel 2007'de Analiz Paketini etkinleştirmek için Engellemeye Git'e tıklayın. Excel Seçenekleri, sol üst köşedeki düğmeyi ve ardından " Excel Seçenekleri» pencerenin altında:



    Bir regresyon modeli oluşturmak için öğeyi seçin Hizmet\Veri Analizi\Regresyon. (Excel 2007'de bu mod, Veri/Veri Analizi/Regresyon). Doldurulması gereken bir iletişim kutusu görünecektir:

    1) Giriş aralığı Y¾, ortaya çıkan özelliğin değerlerini içeren hücrelere bir bağlantı içerir y. Değerler bir sütunda olmalıdır;

    2) Giriş aralığı X¾, faktörlerin değerlerini içeren hücrelere bir bağlantı içerir. Değerler sütunlarda olmalıdır;

    3) İmzala Etiketler ilk hücrelerin açıklayıcı metin (veri etiketleri) içerip içermediğini ayarlayın;

    4) Güvenilirlik seviyesi¾, varsayılan olarak %95 olduğu varsayılan güven düzeyidir. Bu değer size uymuyorsa, bu özelliği etkinleştirmeniz ve gerekli değeri girmeniz gerekir;

    5) İmzala sıfır sabiti serbest değişkenin olduğu bir denklem oluşturmak gerekirse dahil edilir;

    6) Çıkış Seçenekleri sonuçların nereye yerleştirileceğini belirleyin. Varsayılan derleme modu Yeni çalışma sayfası;

    7) Blok Kalıntılar artıkların çıktısını ve bunların grafiklerinin yapımını dahil etmenize izin verir.

    Sonuç olarak, gerekli tüm bilgileri içeren ve üç blok halinde gruplandırılmış bilgiler görüntülenir: Regresyon istatistikleri, varyans analizi, Bakiye çekme. Onları daha ayrıntılı olarak ele alalım.

    1. Regresyon istatistikleri:

    çoklu R formül ile tanımlanır ( Pearson korelasyon katsayısı);

    R (determinasyon katsayısı);

    normalleştirilmiş R-kare formülle hesaplanır (çoklu regresyon için kullanılır);

    standart hata S formül ile hesaplanır ;

    Gözlemler ¾ veri miktarıdır N.

    2. varyans analizi, astar gerileme:

    Parametre df eşittir M(faktör setlerinin sayısı X);

    Parametre SS formül ile belirlenir;

    Parametre HANIM formül ile belirlenir;

    İstatistik F formül ile belirlenir;

    önemi F. Ortaya çıkan sayı aşarsa, hipotez kabul edilir (doğrusal ilişki yoktur), aksi takdirde hipotez kabul edilir (doğrusal bir ilişki vardır).


    3. varyans analizi, astar kalan:

    Parametre df eşittir;

    Parametre SS formül tarafından belirlenir ;

    Parametre HANIM formül ile belirlenir.

    4. varyans analizi, astar Toplam ilk iki sütunun toplamını içerir.

    5. varyans analizi, astar Y kavşağı katsayı değerini, standart hatayı içerir ve T-İstatistik.

    P-değer ¾, hesaplanan değere karşılık gelen anlamlılık düzeylerinin değeridir. T- istatistikçiler. ÖĞRENCİ tarafından belirlenir( T-İstatistik; ). Eğer P değeri aşarsa, karşılık gelen değişken istatistiksel olarak önemsizdir ve modelden çıkarılabilir.

    alt %95 Ve İlk %95¾, teorik doğrusal regresyon denkleminin katsayıları için yüzde 95 güven aralıklarının alt ve üst sınırlarıdır. Veri girişi bloğunda güven olasılığı değeri varsayılan olarak bırakılmışsa, son iki sütun öncekileri çoğaltır. Kullanıcı özel bir güven değeri girdiyse, son iki sütun belirtilen güven düzeyi için alt ve üst sınır değerleri içerir.

    6. varyans analizi, satırlar katsayıların değerlerini, standart hataları içerir, T-istatistikçi, P-karşılık gelen değerler ve güven aralıkları.

    7. Blok Bakiye çekme tahmin edilen değerleri içerir y(bizim notasyonumuzda öyledir) ve kalanlar .

    Regresyon analizi, istatistiksel araştırmaların en popüler yöntemlerinden biridir. Bağımsız değişkenlerin bağımlı değişken üzerindeki etki derecesini belirlemek için kullanılabilir. Microsoft Excel'in işlevselliği, bu tür analizleri gerçekleştirmek için tasarlanmış araçlara sahiptir. Ne olduklarına ve nasıl kullanılacağına bir göz atalım.

    Analiz paketini bağlama

    Ancak regresyon analizi yapmanızı sağlayan fonksiyonu kullanabilmeniz için öncelikle Analiz Paketini aktif hale getirmeniz gerekmektedir. Ancak o zaman bu prosedür için gerekli araçlar Excel şeridinde görünecektir.

    1. "Dosya" sekmesine gidin.
    2. "Ayarlar" bölümüne gidin.
    3. Excel Seçenekleri penceresi açılır. "Eklentiler" alt bölümüne gidin.
    4. Açılan pencerenin en alt kısmında "Yönetim" bloğundaki anahtarı farklı bir konumdaysa "Excel Eklentileri" konumuna yeniden düzenliyoruz. "Git" düğmesine tıklayın.
    5. Excel eklentileri penceresi açılır. "Analiz Paketi"nin yanındaki kutuyu işaretleyin. "Tamam" düğmesine tıklayın.

    Şimdi, "Analiz" araç bloğundaki şeritte "Veri" sekmesine gittiğimizde, yeni bir düğme göreceğiz - "Veri Analizi".

    Regresyon analizi türleri

    Birkaç tür regresyon vardır:

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

    Excel'de son tür regresyon analizinin uygulanması hakkında daha ayrıntılı olarak daha sonra konuşacağız.

    Excel'de Doğrusal Regresyon

    Aşağıda, örnek olarak, sokaktaki ortalama günlük hava sıcaklığını ve ilgili iş günü için mağaza müşteri sayısını gösteren bir tablo bulunmaktadır. Hava sıcaklığı şeklindeki hava koşullarının bir perakende kuruluşunun katılımını tam olarak nasıl etkileyebileceğini regresyon analizi yardımıyla öğrenelim.

    Genel lineer regresyon denklemi şuna benzer: Y = a0 + a1x1 + ... + axk. Bu formülde Y, faktörlerin etkisini incelemeye çalıştığımız değişken anlamına gelir. Bizim durumumuzda bu, alıcıların sayısıdır. X'in değeri, değişkeni etkileyen çeşitli faktörlerdir. a parametreleri regresyon katsayılarıdır. Yani, belirli bir faktörün önemini belirlerler. İndeks k, aynı faktörlerin toplam sayısını belirtir.


    Analiz sonuçları analizi

    Regresyon analizinin sonuçları, ayarlarda belirtilen yerde tablo şeklinde görüntülenir.

    Ana göstergelerden biri R-karesidir. Modelin kalitesini gösterir. Bizim durumumuzda bu katsayı 0,705 veya yaklaşık %70,5'tir. Bu kabul edilebilir bir kalite seviyesidir. 0,5'ten küçük bir ilişki kötüdür.

    Bir diğer önemli gösterge, "Y-kesişimi" çizgisi ile "Katsayılar" sütununun kesiştiği hücrede bulunur. Burada Y'nin hangi değere sahip olacağı belirtilir ve bizim durumumuzda bu, diğer tüm faktörler sıfıra eşit olan alıcı sayısıdır. Bu tabloda bu değer 58.04'tür.

    "Değişken X1" ve "Katsayılar" sütununun kesişimindeki değer, Y'nin X'e bağımlılık düzeyini gösterir. Bizim durumumuzda bu, mağaza müşterisi sayısının sıcaklığa bağımlılık düzeyidir. 1.31 katsayısı oldukça yüksek bir etki göstergesi olarak kabul edilir.

    Gördüğünüz gibi, Microsoft Excel kullanarak bir regresyon analizi tablosu oluşturmak oldukça kolaydır. Ancak çıktıda elde edilen verilerle yalnızca eğitimli bir kişi çalışabilir ve bunların özünü anlayabilir.

    Sorunu çözmenize yardımcı olabildiğimize sevindik.

    Sorunun özünü ayrıntılı olarak açıklayarak yorumlarda sorunuzu sorun. Uzmanlarımız mümkün olan en kısa sürede yanıt vermeye çalışacaktır.

    Bu makale size yardımcı oldu mu?

    Doğrusal regresyon yöntemi, bir dizi sıralı çifte (x, y) en iyi uyan düz bir çizgiyi tanımlamamızı sağlar. Doğrusal denklem olarak bilinen düz bir çizginin denklemi aşağıda verilmiştir:

    ŷ, belirli bir x değeri için y'nin beklenen değeridir,

    x - bağımsız değişken,

    a - düz bir çizgi için y eksenindeki segment,

    b düz çizginin eğimidir.

    Aşağıdaki şekilde, bu kavram grafiksel olarak temsil edilmektedir:

    Yukarıdaki şekil, ŷ =2+0,5x denklemiyle açıklanan bir çizgiyi göstermektedir. Y ekseni üzerindeki segment, çizginin y ekseni ile kesişme noktasıdır; bizim durumumuzda, a = 2. Doğrunun eğimi, b, çizgi yükselişinin çizgi uzunluğuna oranı, 0,5 değerine sahiptir. Pozitif bir eğim, çizginin soldan sağa doğru yükseldiği anlamına gelir. b = 0 ise çizgi yataydır, yani bağımlı ve bağımsız değişkenler arasında ilişki yoktur. Başka bir deyişle, x'in değerini değiştirmek y'nin değerini etkilemez.

    ŷ ve y genellikle karıştırılır. Grafik, verilen denkleme göre 6 sıralı nokta çiftini ve bir çizgiyi göstermektedir.

    Bu şekil, x = 2 ve y = 4 sıralı çiftine karşılık gelen noktayı göstermektedir. X= 2 ŷ'dir. Bunu aşağıdaki denklemle doğrulayabiliriz:

    ŷ = 2 + 0,5х =2 +0,5(2) =3.

    y değeri gerçek noktadır ve ŷ değeri, belirli bir x değeri için doğrusal bir denklem kullanılarak beklenen y değeridir.

    Bir sonraki adım, sıralı çiftler kümesiyle en iyi eşleşen doğrusal denklemi belirlemektir, bundan önceki makalede en küçük kareler yöntemini kullanarak denklemin şeklini belirlediğimizde bahsetmiştik.

    Doğrusal Regresyonu Tanımlamak için Excel'i Kullanma

    Excel'de yerleşik olan regresyon analizi aracını kullanmak için eklentiyi etkinleştirmeniz gerekir. Analiz paketi. Sekmesine tıklayarak bulabilirsiniz Dosya -> Seçenekler(2007+), görüntülenen iletişim kutusunda Seçeneklermükemmel sekmeye git eklentiler sahada Kontrol seçmek eklentilermükemmel ve tıklayın Gitmek. Görünen pencerede, yanındaki kutuyu işaretleyin. analiz paketi, tıklamak TAMAM.

    sekmesinde Veri grup içinde Analiz yeni bir düğme görünecek Veri analizi.

    Eklentinin nasıl çalıştığını göstermek için, bir erkek ve bir kızın banyoda aynı masayı paylaştığı önceki makaledeki verileri kullanalım. Banyo örneğimizin verilerini boş bir sayfanın A ve B sütunlarına girin.

    sekmeye git Veri, grup içinde Analiz tıklamak Veri analizi. Görünen pencerede Veri analizi seçme gerilemeşekilde gösterildiği gibi ve Tamam'a tıklayın.

    Pencerede gerekli regresyon parametrelerini ayarlayın gerileme, resimde gösterildiği gibi:

    Tıklamak TAMAM. Aşağıdaki şekil elde edilen sonuçları göstermektedir:

    Bu sonuçlar, önceki makaledeki bağımsız hesaplamalarla elde ettiğimiz sonuçlarla tutarlıdır.

    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 kullanımı oldukça zordu. Bugün, Excel'de bir regresyon oluşturmayı öğrendikten sonra, karmaşık istatistiksel sorunları birkaç dakika içinde çözebilirsiniz. Aşağıda ekonomi alanından belirli örnekler verilmiştir.

    regresyon türleri

    Kavramın kendisi, 1886'da Francis Galton tarafından matematiğe tanıtıldı. Gerileme olur:

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

    örnek 1

    6 sanayi işletmesinde emekli ekip üyelerinin sayısının ortalama maaşa bağımlılığını belirleme problemini ele alalım.

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

    6 işletmede işten çıkarılan işçi sayısının ortalama maaşa bağımlılığını belirleme sorunu için, regresyon modeli Y = a0 + a1 × 1 + ... + akxk denklemi biçimindedir, burada хi, etkileyen değişkenler, ai regresyon katsayılarıdır ve k faktör sayısıdır.

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

    "Excel" elektronik tablosunun yeteneklerini kullanma

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

    • "Dosya" sekmesinden "Seçenekler" bölümüne gidin;
    • açılan pencerede "Eklentiler" satırını seçin;
    • "Yönetim" satırının sağında, altta bulunan "Git" düğmesine tıklayın;
    • "Analiz Paketi" adının yanındaki kutuyu işaretleyin ve "Tamam"a 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 istenen düğme görünecektir.

    Excel'de Doğrusal Regresyon

    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 "Gerileme" 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;
    • İşlemlerimizi "Tamam" butonuna basarak onaylıyoruz.

    Sonuç olarak, program elektronik tablonun yeni bir sayfasını regresyon analizi verileriyle otomatik olarak dolduracaktır. Not! Excel, bu amaçla tercih ettiğiniz konumu manuel olarak ayarlama yeteneğine sahiptir. Örneğin, Y ve X değerlerinin olduğu 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, ele alınan örneğin verilerinin işlenmesi sırasında elde edilen veriler şöyle görünür:

    Öncelikle R-kare değerine dikkat etmelisiniz. Belirleme katsayısıdır. Bu örnekte, R-kare = 0,755 (%75,5), yani modelin hesaplanan parametreleri, ele 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 model belirli bir görev için o kadar uygulanabilir olur. 0.8'in üzerinde bir R-kare değeri ile gerçek durumu doğru bir şekilde tanımladığına inanılmaktadır. R-kare tcr ise, lineer denklemin serbest teriminin önemsizliği hipotezi reddedilir.

    Serbest üye için incelenmekte olan problemde, Excel araçları kullanılarak, t = 169.20903 ve p = 2.89E-12 olduğu elde edildi, yani serbest üyenin önemsizliği hakkında doğru hipotezin doğru olacağına dair sıfır olasılığımız var. Reddedilmiş. Bilinmeyen katsayı için t=5,79405 ve p=0,001158. Başka bir deyişle, bilinmeyen için katsayının anlamsızlığına ilişkin doğru hipotezin reddedilme olasılığı %0,12'dir.

    Böylece ortaya çıkan 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ı problem düşünün.

    NNN yönetimi, MMM SA'nın %20 hissesinin satın alınmasının tavsiye edilebilirliği konusunda bir karar vermelidir. Paketin (JV) maliyeti 70 milyon ABD dolarıdır. NNN uzmanları benzer işlemler hakkında veri topladı. Hisse bloğunun değerinin milyonlarca ABD doları cinsinden ifade edilen bu tür parametrelere göre aşağıdaki gibi değerlendirilmesine karar verildi:

    • ödenecek hesaplar (VK);
    • yıllık ciro (VO);
    • alacak hesapları (VD);
    • sabit varlıkların maliyeti (SOF).

    Ek olarak, binlerce ABD doları cinsinden işletmenin bordro borçları (V3 P) parametresi kullanılır.

    Excel elektronik tablosunu kullanarak çözüm

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

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

    "Yeni Çalışma Sayfası"nı seçin ve "Tamam"a tıklayın.

    Verilen problem için regresyon analizini alın.

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

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

    SP \u003d 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" için veriler tabloda sunulmaktadır:

    Bunları regresyon denkleminde yerine koyarsak, 64.72 milyon ABD doları elde ederler. Bu, JSC MMM'nin hisselerinin satın alınmaması gerektiği anlamına gelir, çünkü 70 milyon ABD doları değerindeki değerleri oldukça fazladır.

    Gördüğünüz gibi, Excel elektronik tablosunun ve regresyon denkleminin kullanılması, çok özel bir işlemin fizibilitesine ilişkin bilinçli bir karar vermeyi mümkün kıldı.

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

    Devamını oku: