Regresyon analizi ile excel'de aralık tahmini. Microsoft Excel'de belirleme katsayısının hesaplanması

Ortaya çıkan y özelliğindeki değişiklik, x faktör özelliğindeki değişiklikten kaynaklanmaktadır. Regresyonun açıkladığı varyansın, sonuçta ortaya çıkan özelliğin toplam varyansı içindeki payı R2 belirleme katsayısını karakterize eder. Doğrusal bir bağımlılık için belirleme katsayısı, korelasyon katsayısının karesine eşittir:

R2 = rxy2, burada rxy korelasyon katsayısıdır.

Örneğin, R 2 = 0,83 değeri, vakaların %83'ünde x'teki değişikliklerin y'de bir değişikliğe yol açtığı anlamına gelir. Başka bir deyişle regresyon denkleminin seçim doğruluğu yüksektir.

Regresyon denklemine uyma kalitesini değerlendirmek için hesaplanır. Kabul edilebilir modeller için belirleme katsayısının %50'den büyük olması gerektiği varsayılır. Belirleme katsayısı %80'in üzerinde olan modeller oldukça iyi sayılabilir. Belirleme katsayısının değeri R 2 = 1, değişkenler arasındaki fonksiyonel ilişkiyi ifade eder.

Ne zaman doğrusal olmayan regresyon Belirleme katsayısı bu hesaplayıcı kullanılarak hesaplanır. Çoklu regresyonda, belirleme katsayısı Çoklu Regresyon hizmeti aracılığıyla bulunabilir.
Genel olarak belirleme katsayısı aşağıdaki formülle bulunur: veya
Varyans ekleme kuralı:
,
sapmaların karelerinin toplamı nerede;
- regresyondan kaynaklanan sapmaların karelerinin toplamı (“açıklanan” veya “faktöriyel”);
- sapmaların karelerinin kalan toplamı.

Bu çevrimiçi hesap makinesini kullanarak hesaplayabilirsiniz determinasyon katsayısı ve önemi kontrol edilir (Örnek çözüm).

Talimatlar. Giriş verilerinin miktarını belirtin. Ortaya çıkan çözüm bir Word dosyasına kaydedilir. Çözümü Excel'de test etmek için otomatik olarak bir şablon da oluşturulur.

Microsoft Excel'de Regresyon Analizi, iş zekası alanındaki regresyon analizi sorunlarını çözmek için MS Excel'in kullanılmasına ilişkin en kapsamlı kılavuzdur. Konrad Carlberg, hem kendiniz regresyon analizi yaparken hem de başkaları tarafından yapılan analizlerin sonuçlarını değerlendirirken birçok hatadan kaçınmanıza yardımcı olacak teorik konuları net bir şekilde açıklıyor. Basit korelasyonlar ve t-testlerinden çoklu kovaryans analizine kadar tüm materyaller gerçek örneklere dayanmaktadır ve ilgili adım adım prosedürlerin ayrıntılı bir açıklamasıyla birlikte verilmektedir.

Bu kitap, Excel'in regresyon işlevlerinin artılarını ve eksilerini tartışıyor, seçeneklerin ve argümanların her birinin kullanılmasının sonuçlarını inceliyor ve tıbbi araştırmalardan finansal analize kadar çeşitli alanlarda regresyon tekniklerinin nasıl güvenilir bir şekilde uygulanacağını açıklıyor.

Konrad Carlberg. Microsoft Excel'de regresyon analizi. - M .: Diyalektik, 2017. - 400 s.

Notu veya formatında indirin, formattaki örnekler

Bölüm 1. Veri değişkenliğinin tahmin edilmesi

İstatistikçilerin elinde birçok varyasyon (değişkenlik) göstergesi vardır. Bunlardan biri, bireysel değerlerin ortalamadan sapmalarının karelerinin toplamıdır. Excel bunun için SQUADROT() işlevini kullanır. Ancak daha sıklıkla dispersiyon kullanılır. Varyans, sapmaların karelerinin ortalamasıdır. Varyans, incelenmekte olan veri kümesindeki değerlerin sayısına duyarlı değildir (sapmaların karelerinin toplamı ölçüm sayısıyla birlikte artarken).

Excel, varyans döndüren iki işlev sunar: VARP.D() ve VARP.V():

İşlenecek değerler bir popülasyon oluşturuyorsa VAR.G() fonksiyonunu kullanın. Yani aralığın içerdiği değerler ilgilendiğiniz tek değerlerdir.
İşlenecek değerler daha büyük bir popülasyondan bir örnek oluşturuyorsa VAR.V() fonksiyonunu kullanın. Varyansını da tahmin edebileceğiniz ek değerlerin olduğunu varsayar.

Ortalama veya korelasyon katsayısı gibi bir değer genel nüfus bazında hesaplanıyorsa buna parametre denir. Bir örnek esas alınarak hesaplanan benzer bir değere istatistik denir. Sapmaları geri saymak ortalamadan bu sette, başka herhangi bir değerden saydığınızda elde ettiğinizden daha küçük miktardaki sapmaların karelerinin toplamını elde edeceksiniz. Benzer bir ifade varyans için de geçerlidir.

Örneklem boyutu ne kadar büyük olursa hesaplanan istatistik değeri de o kadar doğru olur. Ancak istatistiğin değerinin parametrenin değeriyle aynı olduğundan emin olabileceğiniz, popülasyon büyüklüğünden daha küçük bir örneklem yoktur.

Diyelim ki, fark ne kadar küçük olursa olsun, ortalamaları nüfus ortalamasından farklı olan 100 yükseklikten oluşan bir kümeniz var. Bir numunenin varyansını hesapladığınızda, örneğin 4 gibi bir değer elde edersiniz. Bu değer, 100 yükseklik değerinin her birinin numune ortalaması dışındaki herhangi bir değere göre sapmasını hesaplayarak elde edilebilecek diğer değerlerden daha küçüktür. , gerçek ortalamaya göre genel nüfus dahil. Bu nedenle hesaplanan varyans, bir şekilde örnek ortalama yerine bir popülasyon parametresi bulup kullandığınızda elde edeceğiniz varyanstan farklı ve daha küçük olacaktır.

Örneklem için belirlenen ortalama kareler toplamı, popülasyon varyansının daha düşük bir tahminini sağlar. Bu şekilde hesaplanan varyansa denir. yerinden edilmiş değerlendirme. Önyargıyı ortadan kaldırmak ve tarafsız bir tahmin elde etmek için sapmaların karelerinin toplamını bölmenin yeterli olduğu ortaya çıktı. N, Nerede N- örneklem büyüklüğü ve n – 1.

Büyüklük n – 1 serbestlik derecesinin sayısı (sayı) denir. Bu değeri hesaplamanın farklı yolları vardır, ancak bunların hepsi ya örneklem büyüklüğünden bir miktar sayı çıkarmayı ya da gözlemlerin içine düştüğü kategori sayısını saymayı içerir.

DISP.G() ve DISP.V() işlevleri arasındaki farkın özü aşağıdaki gibidir:

VARI.G() işlevinde, karelerin toplamı gözlem sayısına bölünür ve dolayısıyla varyansın taraflı tahminini, yani gerçek ortalamayı temsil eder.
DISP.B() işlevinde, karelerin toplamı gözlem sayısından 1 çıkarılarak bölünür; serbestlik derecesi sayısına göre; bu, numunenin alındığı popülasyonun varyansının daha doğru, tarafsız bir tahminini verir.

Standart sapma standart sapma, SD) – varyansın kareköküdür:

Sapmaların karesi, ölçüm ölçeğini orijinalin karesi olan başka bir metriğe çevirir: metre - metrekareye, dolar - doların karesine vb. Standart sapma varyansın kareköküdür ve bizi orijinal birimlere geri getirir. Hangisi daha uygunsa.

Veriler bazı manipülasyonlara tabi tutulduktan sonra genellikle standart sapmanın hesaplanması gerekir. Ve bu durumlarda sonuçların şüphesiz standart sapmalar olmasına rağmen, bunlara genel olarak denir. standart hatalar. Standart ölçüm hatası, standart orantı hatası, ortalamanın standart hatası dahil olmak üzere çeşitli standart hata türleri vardır.

Diyelim ki 50 eyaletin her birinden rastgele seçilmiş 25 yetişkin erkeğin boyuna ilişkin veri topladınız. Daha sonra, her eyaletteki yetişkin erkeklerin ortalama boyunu hesaplarsınız. Ortaya çıkan 50 ortalama değer ise gözlem olarak kabul edilebilir. Bundan standart sapmalarını hesaplayabilirsiniz; ortalamanın standart hatası. Pirinç. 1. 1250 orijinal bireysel değerin dağılımını (50 eyaletin her birinde 25 erkeğin boyuna ilişkin veriler) 50 eyaletin ortalama değerlerinin dağılımıyla karşılaştırmanıza olanak tanır. Ortalamanın standart hatasını tahmin etmeye yönelik formül (yani bireysel gözlemlerin değil, ortalamaların standart sapması):

ortalamanın standart hatası nerede; S– orijinal gözlemlerin standart sapması; N– numunedeki gözlem sayısı.

Pirinç. 1. Eyaletten eyalete ortalama değerlerdeki değişim, bireysel gözlemlerdeki değişimden çok daha azdır

İstatistikte, istatistiksel büyüklükleri belirtmek için Yunan ve Latin harflerinin kullanımına ilişkin bir gelenek vardır. Genel popülasyonun parametrelerini Yunan harfleriyle, örnek istatistikleri ise Latin harfleriyle belirtmek gelenekseldir. Bu nedenle popülasyon standart sapmasından bahsederken bunu σ olarak yazıyoruz; örneğin standart sapması dikkate alınırsa s notasyonunu kullanırız. Ortalamaları belirlemeye yönelik sembollere gelince, bunlar birbirleriyle pek uyuşmuyor. Nüfus ortalaması Yunanca μ harfiyle gösterilir. Ancak X̅ sembolü geleneksel olarak örnek ortalamayı temsil etmek için kullanılır.

z-puanı Bir gözlemin dağılımdaki konumunu standart sapma birimleri cinsinden ifade eder. Örneğin z = 1,5, gözlemin ortalamadan 1,5 standart sapma uzakta olduğu anlamına gelir. Terim z-puanı bireysel değerlendirmeler için kullanılır; bireysel örnek öğelere atanan boyutlar için. Bu tür istatistiklere atıfta bulunmak için kullanılan terim (eyalet ortalaması gibi) z-puanı:

burada X̅ örnek ortalama, μ popülasyon ortalaması, bir dizi örnek ortalamanın standart hatasıdır:

burada σ popülasyonun standart hatasıdır (bireysel ölçümler), N- örnek boyut.

Diyelim ki bir golf kulübünde eğitmen olarak çalışıyorsunuz. Uzun bir süre boyunca atışlarınızın mesafesini ölçebildiniz ve ortalamanın 205 yarda, standart sapmanın ise 36 yarda olduğunu biliyorsunuz. Vuruş mesafenizi 10 yard artıracağı iddia edilen yeni bir sopa teklif ediliyor. Sonraki 81 kulüp patronunun her birinden yeni bir sopayla deneme atışı yapmasını ve vuruş mesafelerini kaydetmesini istiyorsunuz. Yeni kulüple ortalama mesafenin 215 yarda olduğu ortaya çıktı. 10 yarda (215 – 205) farkın yalnızca örnekleme hatasından kaynaklanma olasılığı nedir? Veya başka bir deyişle: Daha kapsamlı testlerde yeni kulübün, mevcut uzun vadeli ortalama olan 205 yardın üzerinde vuruş mesafesinde bir artış göstermeme olasılığı nedir?

Bunu bir z-puanı oluşturarak kontrol edebiliriz. Ortalamanın standart hatası:

Daha sonra z-puanı:

Örnek ortalamasının popülasyon ortalamasından 2,5σ uzakta olma olasılığını bulmamız gerekiyor. Olasılık küçükse, fark şanstan değil, yeni kulübün kalitesinden kaynaklanıyor demektir. Excel'in z-puanı olasılığını belirlemek için hazır bir işlevi yoktur. Ancak =1-NORM.ST.DAĞ(z-puanı,DOĞRU) formülünü kullanabilirsiniz; burada NORM.ST.DAĞ() işlevi, z-puanının solundaki normal eğrinin altındaki alanı döndürür (Şekil 2).

Pirinç. 2. NORM.ST.DAĞ() işlevi, z değerinin solundaki eğrinin altındaki alanı döndürür; Bir resmi büyütmek için üzerine sağ tıklayın ve Resmi yeni sekmede aç

NORM.ST.DAĞ() işlevinin ikinci bağımsız değişkeni iki değer alabilir: DOĞRU – işlev, ilk bağımsız değişken tarafından belirtilen noktanın solundaki eğrinin altındaki alanın alanını döndürür; YANLIŞ – işlev, ilk bağımsız değişken tarafından belirtilen noktadaki eğrinin yüksekliğini döndürür.

Popülasyon ortalaması (μ) ve standart sapma (σ) bilinmiyorsa t değeri kullanılır (ayrıntılara bakın). Z-puanı ve t-puanı yapıları, t-puanını bulmak için popülasyon parametresi σ'nun bilinen değeri yerine örnek sonuçlarından elde edilen standart sapmanın kullanılması bakımından farklılık gösterir. Normal eğri tek bir şekle sahiptir ve t-değeri dağılımının şekli, df serbestlik derecesi sayısına bağlı olarak değişir. özgürlük derecesi) temsil ettiği numunenin. Numunenin serbestlik derecesi sayısı eşittir n – 1, Nerede N- numune boyutu (Şekil 3).

Pirinç. 3. σ parametresinin bilinmediği durumlarda ortaya çıkan t dağılımlarının şekli normal dağılım şeklinden farklıdır

Excel'in t dağıtımı için Öğrenci dağıtımı olarak da adlandırılan iki işlevi vardır: STUDENT.DIST(), belirli bir t değerinin solundaki eğrinin altındaki alanı döndürür ve STUDENT.DIST.PH(), verilen t değerinin solundaki alanı döndürür. Sağ.

Bölüm 2. Korelasyon

Korelasyon, bir dizi sıralı çiftin elemanları arasındaki bağımlılığın bir ölçüsüdür. Korelasyon karakterize edilir Pearson korelasyon katsayıları-R. Katsayı –1,0 ila +1,0 aralığında değerler alabilir.

Nerede Sx Ve Evet– değişkenlerin standart sapmaları X Ve e, S xy– kovaryans:

Bu formülde kovaryans, değişkenlerin standart sapmalarına bölünür. X Ve e böylece kovaryanstan birim ile ilgili ölçeklendirme etkileri kaldırılır. Excel CORREL() işlevini kullanır. Bu işlevin adı, STANDARDEV(), VARIANCE() veya COVARIANCE() gibi işlevlerin adlarında kullanılan Г ve В niteleyici öğelerini içermez. Örnek korelasyon katsayısı taraflı bir tahmin sağlasa da, önyargının nedeni varyans veya standart sapma durumunda olduğundan farklıdır.

Genel korelasyon katsayısının büyüklüğüne bağlı olarak (genellikle Yunanca harfle gösterilir) ρ ), korelasyon katsayısı Rörnek boyutları azaldıkça artan yanlılık etkisi ile yanlı bir tahmin üretir. Bununla birlikte, bu önyargıyı, örneğin standart sapmayı hesaplarken, karşılık gelen formülde gözlem sayısını değil serbestlik derecesi sayısını kullandığımızda yaptığımız gibi düzeltmeye çalışmıyoruz. Gerçekte kovaryansı hesaplamak için kullanılan gözlem sayısının büyüklük üzerinde hiçbir etkisi yoktur.

Standart korelasyon katsayısının birbiriyle doğrusal bir ilişkiyle ilişkili değişkenlerle kullanılması amaçlanmıştır. Verilerde doğrusal olmama ve/veya hataların (aykırı değerler) varlığı, korelasyon katsayısının yanlış hesaplanmasına yol açar. Veri sorunlarını teşhis etmek için dağılım grafikleri oluşturulması önerilir. Bu, Excel'de hem yatay hem de dikey eksenleri değer eksenleri olarak ele alan tek grafik türüdür. Çizgi grafik, sütunlardan birini kategori ekseni olarak tanımlar, bu da verilerin resmini bozar (Şekil 4).

Pirinç. 4. Regresyon çizgileri aynı görünüyor ancak denklemlerini birbirleriyle karşılaştırın

Çizgi grafiğini oluşturmak için kullanılan gözlemler yatay eksen boyunca eşit uzaklıktadır. Bu eksendeki bölme etiketleri sayısal değerler değil, yalnızca etiketlerdir.

Korelasyon çoğu zaman nedensel bir ilişkinin olduğu anlamına gelse de, bunun kanıtı değildir. İstatistikler bir teorinin doğru mu yanlış mı olduğunu göstermek için kullanılmaz. Gözlemsel sonuçlara yönelik rakip açıklamaları hariç tutmak için, planlanmış deneyler. İstatistikler aynı zamanda bu tür deneyler sırasında toplanan bilgileri özetlemek ve kanıta dayalı olarak kararın yanlış olma olasılığını ölçmek için de kullanılır.

Bölüm 3: Basit Regresyon

İki değişken ilişkiliyse, yani korelasyon katsayısının değeri 0,5'ten büyükse, o zaman bir değişkenin bilinmeyen değerini diğerinin bilinen değerinden tahmin etmek (bir miktar doğrulukla) mümkündür. Şekil 2'de gösterilen verilere dayanarak tahmini fiyat değerlerini elde etmek için. 5'te, olası birkaç yoldan herhangi birini kullanabilirsiniz, ancak neredeyse kesinlikle Şekil 2'de gösterileni kullanmayacaksınız. 5. Yine de okumalısınız çünkü korelasyon ve tahmin arasındaki ilişkiyi bu kadar açık bir şekilde ortaya koyabilecek başka bir yol yoktur. İncirde. B2:C12 aralığındaki Şekil 5, on evden oluşan rastgele bir örnektir ve her evin alanı (metrekare cinsinden) ve satış fiyatı hakkında veri sağlar.

Pirinç. 5. Tahmini satış fiyatı değerleri düz bir çizgi oluşturur

Ortalamaları, standart sapmaları ve korelasyon katsayısını (aralık A14:C18) bulun. Alan z-puanlarını hesaplayın (E2:E12). Örneğin, E3 hücresi şu formülü içerir: =(B3-$B$14)/$B$15. Tahmin fiyatının z-puanlarını hesaplayın (F2:F12). Örneğin, F3 hücresi şu formülü içerir: =ЕЗ*$В$18. Z-puanlarını dolar fiyatlarına dönüştürün (H2:H12). NZ hücresindeki formül şu şekildedir: =F3*$C$15+$C$14.

Tahmin edilen değerin her zaman 0'ın ortalamasına doğru kayma eğiliminde olduğuna dikkat edin. Korelasyon katsayısı sıfıra ne kadar yakınsa, tahmin edilen z puanı da sıfıra o kadar yakın olur. Örneğimizde alan ile satış fiyatı arasındaki korelasyon katsayısı 0,67, tahmin fiyatı ise 1,0 * 0,67'dir, yani. 0.67. Bu, standart sapmanın üçte ikisine eşit ortalamanın üzerinde bir değerin fazlalığına karşılık gelir. Korelasyon katsayısı 0,5'e eşit olsaydı, tahmin fiyatı 1,0 * 0,5 olurdu, yani. 0,5. Bu, ortalamanın üzerinde, standart sapmanın yalnızca yarısına eşit bir değerin fazlalığına karşılık gelir. Korelasyon katsayısının değeri ideal değerden farklı olduğunda; -1,0'dan büyük ve 1,0'dan küçükse, tahmin edilen değişkenin puanı kendi ortalamasına, yordayıcı (bağımsız) değişkenin kendi skorundan daha yakın olmalıdır. Bu olguya ortalamaya gerileme veya basitçe gerileme denir.

Excel'in, bir regresyon çizgisi denkleminin (Excel'de eğilim çizgisi olarak adlandırılır) katsayılarını belirlemek için çeşitli işlevleri vardır. y =kx + B. Belirlemek için k işlev görür

=EĞİM(bilinen_y_değerleri, bilinen_x_değerleri)

Burada en tahmin edilen değişkendir ve X- bağımsız değişken. Bu değişken sırasına kesinlikle uymanız gerekir. Regresyon çizgisinin eğimi, korelasyon katsayısı, değişkenlerin standart sapması ve kovaryans birbiriyle yakından ilişkilidir (Şekil 6). INTERMEPT() işlevi dikey eksende regresyon çizgisinin kestiği değeri döndürür:

=LIMIT(bilinen_y_değerleri, bilinen_x_değerleri)

Pirinç. 6. Standart sapmalar arasındaki ilişki, kovaryansı bir korelasyon katsayısına ve regresyon çizgisinin eğimine dönüştürür

SLOPE() ve INTERCEPT() işlevlerine argüman olarak sağlanan x ve y değerlerinin sayısının aynı olması gerektiğini unutmayın.

Regresyon analizinde başka bir önemli gösterge kullanılır - R2 (R-kare) veya belirleme katsayısı. arasındaki ilişkinin verinin genel değişkenliğine nasıl bir katkı sağlayacağını belirler. X Ve en. Excel'de bunun için CVPIERSON() adı verilen ve CORREL() işleviyle tamamen aynı argümanları alan bir işlev vardır.

Aralarında sıfır olmayan korelasyon katsayısına sahip iki değişkenin varyansı açıkladığı veya varyansı açıkladığı söylenir. Tipik olarak açıklanan varyans yüzde olarak ifade edilir. Bu yüzden R 2 = 0,81, iki değişkenin varyansının (dağılımının) %81'inin açıklandığı anlamına gelir. Geriye kalan %19 ise rastgele dalgalanmalardan kaynaklanmaktadır.

Excel'de hesaplamaları kolaylaştıran bir TREND işlevi vardır. TREND() işlevi:

sağladığınız bilinen değerleri kabul eder X ve bilinen değerler en;
regresyon çizgisinin eğimini ve sabiti (kesme noktası) hesaplar;
tahmin edilen değerleri döndürür en regresyon denkleminin bilinen değerlere uygulanmasıyla belirlenir X(Şekil 7).

TREND() fonksiyonu bir dizi fonksiyonudur (eğer daha önce bu tarz fonksiyonlara rastlamadıysanız tavsiye ederim).

Pirinç. 7. TREND() işlevini kullanmak, bir çift SLOPE() ve INTERCEPT() işlevini kullanmaya kıyasla hesaplamaları hızlandırmanıza ve basitleştirmenize olanak tanır.

TREND() işlevini G3:G12 hücrelerine dizi formülü olarak girmek için G3:G12 aralığını seçin, TREND formülünü (SZ:S12; OT:B12) girin, tuşları basılı tutun ve ancak bundan sonra tuşa basın . Formülün küme parantezleri ( ve ) içine alındığını unutmayın. Excel bu formülün dizi formülü olarak algılandığını bu şekilde söylüyor. Parantezleri kendiniz girmeyin: Bir formülün parçası olarak bunları kendiniz girmeye çalışırsanız, Excel girişinizi normal bir metin dizesi olarak değerlendirecektir.

TREND() fonksiyonunun iki argümanı daha vardır: new_values_x Ve yapı. Birincisi geleceğe yönelik bir tahmin yapmanızı sağlar ve ikincisi regresyon çizgisini başlangıç noktasından geçmeye zorlayabilir (DOĞRU değeri Excel'e hesaplanan sabiti kullanmasını söyler, FALSE değeri Excel'e bir sabit = 0 kullanmasını söyler) ). Excel, grafik üzerinde orijinden geçecek şekilde bir regresyon çizgisi çizmenize olanak tanır. Bir dağılım grafiği çizerek başlayın, ardından veri serisi işaretçilerinden birine sağ tıklayın. Açılan içerik menüsündeki öğeyi seçin Trend çizgisi ekle; bir seçenek seçin Doğrusal; gerekirse paneli aşağı kaydırın, kutuyu işaretleyin Bir kavşak kurun; ilişkili metin kutusunun 0,0 olarak ayarlandığından emin olun.

Üç değişkeniniz varsa ve üçüncünün etkisini ortadan kaldırırken ikisi arasındaki korelasyonu belirlemek istiyorsanız, şunu kullanabilirsiniz: kısmi korelasyon. Bir şehirde üniversiteyi bitirmiş sakinlerin yüzdesi ile şehrin kütüphanelerindeki kitap sayısı arasındaki ilişkiyle ilgilendiğinizi varsayalım. 50 şehir için veri topladınız ama... Sorun şu ki, bu parametrelerin her ikisi de belirli bir şehrin sakinlerinin refahına bağlı olabilir. Elbette, sakinlerinin tam olarak aynı düzeyde refaha sahip olduğu başka 50 şehir bulmak çok zor.

Zenginliğin hem kütüphane mali desteği hem de üniversitenin karşılanabilirliği üzerindeki etkisini kontrol etmek için istatistiksel yöntemler kullanarak, ilgilenilen değişkenler, yani kitap sayısı ve mezun sayısı arasındaki ilişkinin gücüne ilişkin daha kesin bir niceliksel ölçüm elde edebilirsiniz. Diğer değişkenlerin değerleri sabit olduğunda, iki değişken arasında böyle bir koşullu korelasyona kısmi korelasyon denir. Bunu hesaplamanın bir yolu denklemi kullanmaktır:

Nerede RC.B. . K- Zenginlik değişkeninin etkisi (sabit değer) hariç tutularak Üniversite ve Kitap değişkenleri arasındaki korelasyon katsayısı; RC.B.- Üniversite ve Kitap değişkenleri arasındaki korelasyon katsayısı; RCW- Üniversite ve Refah değişkenleri arasındaki korelasyon katsayısı; RB.W.- Kitaplar ve Refah değişkenleri arasındaki korelasyon katsayısı.

Öte yandan, artıkların analizine dayalı olarak kısmi korelasyon hesaplanabilir; tahmin edilen değerler ile gerçek gözlemlerin ilişkili sonuçları arasındaki farklar (her iki yöntem de Şekil 8'de sunulmaktadır).

Pirinç. 8. Artık Korelasyon Olarak Kısmi Korelasyon

Korelasyon katsayıları matrisinin (B16:E19) hesaplanmasını basitleştirmek için Excel analiz paketini kullanın (menü Veri –> Analiz –> Veri analizi). Varsayılan olarak bu paket Excel'de etkin değildir. Yüklemek için menüye gidin Dosya –> Seçenekler –> Eklentiler. Açılan pencerenin alt kısmında Seçeneklerexcel alanı bul Kontrol, seçme Eklentilerexcel, tıklamak Gitmek. Eklentinin yanındaki kutuyu işaretleyin Analiz paketi. A'ya tıklayın veri analizi, seçeneği seçin Korelasyon. Giriş aralığı olarak $B$2:$D$13 belirtin, kutuyu işaretleyin İlk satırdaki etiketler, çıktı aralığı olarak $B$16:$E$19'u belirtin.

Diğer bir olasılık ise yarı kısmi korelasyonun belirlenmesidir. Örneğin boy ve yaşın kilo üzerindeki etkilerini araştırıyorsunuz. Böylece, iki öngörücü değişkeniniz (boy ve yaş) ve bir öngörücü değişkeniniz (ağırlık) olur. Bir yordayıcı değişkenin diğeri üzerindeki etkisini hariç tutmak, ancak yordayıcı değişkeni hariç tutmak istiyorsunuz:

burada H – Boy, W – Ağırlık, A – Yaş; Yarı kısmi korelasyon katsayısı endeksi, hangi değişkenin hangi değişkenden çıkarıldığını belirtmek için parantez kullanır. Bu durumda W(H.A) gösterimi, Yaş değişkeninin etkisinin Boy değişkeninden kaldırıldığını ancak Ağırlık değişkeninden kaldırılmadığını gösterir.

Tartışılan konunun çok önemli olmadığı anlaşılıyor. Sonuçta en önemli şey, genel regresyon denkleminin ne kadar doğru çalıştığıdır; bireysel değişkenlerin açıklanan toplam varyansa göreceli katkıları sorunu ise ikincil öneme sahiptir. Ancak durum böyle değil. Bir değişkenin çoklu regresyon denkleminde kullanılmaya değer olup olmadığını merak etmeye başladığınızda konu önem kazanır. Analiz için model seçiminin doğruluğunun değerlendirilmesini etkileyebilir.

Bölüm 4. LINEST() İşlevi

LINEST() işlevi 10 regresyon istatistiği döndürür. LINEST() işlevi bir dizi işlevidir. Girmek için beş satır ve iki sütun içeren bir aralık seçin, formülü yazın ve (Şekil 9):

DOT(B2:B21;A2:A21;DOĞRU;DOĞRU)

Pirinç. 9. LINEST() işlevi: a) D2:E6 aralığını seçin, b) formülü formül çubuğunda gösterildiği gibi girin, c) tıklayın

LINEST() işlevi şunu döndürür:

regresyon katsayısı (veya eğim, D2 hücresi);
segment (veya sabit, E3 hücresi);
Regresyon katsayısı ve sabitin standart hataları (D3:E3 aralığı);
regresyon için belirleme katsayısı R2 (hücre D4);
tahminin standart hatası (E4 hücresi);
Tam regresyon için F testi (hücre D5);
kalan kareler toplamı için serbestlik derecesi sayısı (E5 hücresi);
karelerin regresyon toplamı (hücre D6);
kalan kareler toplamı (E6 hücresi).

Bu istatistiklerin her birine ve bunların nasıl etkileşime girdiğine bakalım.

Standart hata bizim durumumuzda örnekleme hataları için hesaplanan standart sapmadır. Yani bu, genel nüfusun bir istatistiğe sahip olduğu, örneklemin ise başka bir istatistiğe sahip olduğu bir durumdur. Regresyon katsayısını standart hataya bölmek size 2,092/0,818 = 2,559 değerini verir. Başka bir deyişle, 2,092'lik bir regresyon katsayısı sıfırdan iki buçuk standart hata uzaktadır.

Regresyon katsayısı sıfırsa, tahmin edilen değişkenin en iyi tahmini ortalamasıdır. İki buçuk standart hata oldukça büyüktür ve popülasyona ilişkin regresyon katsayısının sıfırdan farklı olduğunu rahatlıkla varsayabilirsiniz.

Fonksiyonu kullanarak popülasyondaki gerçek değeri 0,0 ise, 2,092'lik bir örnek regresyon katsayısı elde etme olasılığını belirleyebilirsiniz.

ÖĞRENCİ.MESAFE.PH (t-kriteri = 2,559; serbestlik derecesi sayısı = 18)

Genel olarak serbestlik derecesi sayısı = n – k – 1 olup burada n gözlem sayısı, k ise yordayıcı değişkenlerin sayısıdır.

Bu formül 0,00987 değerini döndürür veya %1'e yuvarlanır. Bu bize, eğer popülasyon için regresyon katsayısı %0 ise, o zaman tahmin edilen regresyon katsayısı 2,092 olan 20 kişilik bir örneklem elde etme olasılığının %1 olduğunu söylüyor.

F testi (Şekil 9'daki D5 hücresi), tam regresyona ilişkin olarak, basit ikili regresyon katsayısına ilişkin t testiyle aynı işlevleri yerine getirir. F testi, bir regresyon için R2 belirleme katsayısının, popülasyonda 0,0 değerine sahip olduğu hipotezini reddedecek kadar büyük olup olmadığını test etmek için kullanılır; bu, yordayıcı ve yordanan değişken tarafından açıklanan herhangi bir varyans olmadığını gösterir. Yalnızca bir yordayıcı değişken olduğunda, F testi tam olarak t testinin karesine eşittir.

Şu ana kadar aralık değişkenlerine baktık. Basit adları temsil eden, birden fazla değer alabilen değişkenleriniz varsa (örneğin, Erkek ve Kadın veya Sürüngen, Amfibi ve Balık), bunları sayısal kod olarak gösterin. Bu tür değişkenlere nominal denir.

R2 istatistikleri açıklanan varyansın oranını ölçer.

Tahminin standart hatası.İncirde. Şekil 4.9, Boy değişkeni ile ilişkisi temel alınarak elde edilen Ağırlık değişkeninin tahmin değerlerini sunmaktadır. E2:E21 aralığı Weight değişkenine ait artık değerleri içerir. Daha kesin olarak, bu artıklara hata denir - dolayısıyla tahminin standart hatası terimi de buradan gelir.

Pirinç. 10. Hem R 2 hem de tahminin standart hatası, regresyon kullanılarak elde edilen tahminlerin doğruluğunu ifade eder.

Tahminin standart hatası ne kadar küçük olursa, regresyon denklemi o kadar doğru olur ve denklem tarafından üretilen herhangi bir tahminin gerçek gözlemle eşleşmesine o kadar yakın beklersiniz. Tahminin standart hatası bu beklentileri ölçmenin bir yolunu sağlar. Belirli bir boya sahip kişilerin %95'inin ağırlığı şu aralıkta olacaktır:

(yükseklik * 2,092 – 3,591) ± 2,092 * 21,118

F istatistiği grup içi varyansın grup içi varyansa oranıdır. Bu isim, 20. yüzyılın başında varyans analizini (ANOVA, Varyans Analizi) geliştiren Sir'in onuruna istatistikçi George Snedecor tarafından tanıtıldı.

Belirleme katsayısı R2, regresyonla ilişkili toplam kareler toplamının oranını ifade eder. (1 - R 2) değeri, artıklarla - tahmin hatalarıyla ilişkili toplam kareler toplamının oranını ifade eder. F testi, DOT işlevi (Şekil 11'deki F5 hücresi), karelerin toplamları (G10:J11 aralığı) ve varyans kesirleri (G14:J15 aralığı) kullanılarak elde edilebilir. Formüller ekteki Excel dosyasında incelenebilir.

Pirinç. 11. F kriterinin hesaplanması

Nominal değişkenler kullanıldığında kukla kodlama kullanılır (Şekil 12). Değerleri kodlamak için 0 ve 1 değerlerinin kullanılması uygundur. F olasılığı aşağıdaki fonksiyon kullanılarak hesaplanır:

F.DAĞ.PH(K2;I2;I3)

Burada, F.DIST.RT() işlevi, değeri I2 ve I3 hücrelerinde verilen serbestlik derecelerine sahip iki veri kümesi için merkezi F dağılımını (Şekil 13) takiben bir F testi elde etme olasılığını döndürür. K2 hücresinde verilen değerle aynıdır.

Pirinç. 12. Kukla değişkenleri kullanan regresyon analizi

Pirinç. 13. λ = 0'da merkezi F dağılımı

Bölüm 5. Çoklu Regresyon

Tek tahmin değişkenli basit ikili regresyondan çoklu regresyona geçtiğinizde, bir veya daha fazla tahmin değişkeni eklersiniz. Tahmin edici değişkenlerin değerlerini, iki tahminci olması durumunda A ve B sütunları veya üç tahminci olması durumunda A, B ve C gibi bitişik sütunlarda saklayın. LINEST() işlevini içeren bir formül girmeden önce, beş satır ve öngörücü değişkenlerin sayısı kadar sütunun yanı sıra sabit için bir tane daha seçin. İki yordayıcı değişkenli regresyon durumunda aşağıdaki yapı kullanılabilir:

DOT(A2: A41; B2: C41;;DOĞRU)

Benzer şekilde üç değişken durumunda:

DOT(A2:A61;B2:D61;;DOĞRU)

Diyelim ki aterotromboza neden olan aterosklerotik plakların oluşumundan sorumlu olduğuna inanılan düşük yoğunluklu lipoproteinler olan LDL düzeyleri üzerindeki yaş ve diyetin olası etkilerini incelemek istiyorsunuz (Şekil 14).

Pirinç. 14. Çoklu regresyon

Çoklu regresyonun R2'si (F13 hücresinde yansıtılır), herhangi bir basit regresyonun (E4, H4) R2'sinden daha büyüktür. Çoklu regresyonda birden fazla yordayıcı değişken aynı anda kullanılır. Bu durumda R2 neredeyse her zaman artar.

Bir öngörücü değişkene sahip herhangi bir basit doğrusal regresyon denklemi için, tahmin edilen değerler ile öngörücü değişkenin değerleri arasında her zaman mükemmel bir korelasyon olacaktır çünkü denklem, öngörücü değerleri bir sabitle çarpar ve buna başka bir sabit ekler. her ürün. Çoklu regresyonda bu etki kalıcı değildir.

Çoklu regresyon için LINEST() fonksiyonu tarafından döndürülen sonuçların görüntülenmesi (Şekil 15). Regresyon katsayıları, LINEST() işlevi tarafından döndürülen sonuçların bir parçası olarak çıkarılır değişkenlerin ters sırasına göre(G–H–I, C–B–A'ya karşılık gelir).

Pirinç. 15. Çalışma sayfasında katsayılar ve bunların standart hataları ters sırada gösterilmektedir.

Tek yordayıcı değişken regresyon analizinde kullanılan prensipler ve prosedürler, birden çok yordayıcı değişkeni hesaba katacak şekilde kolaylıkla uyarlanabilir. Bu adaptasyonun büyük kısmının, yordayıcı değişkenlerin birbirleri üzerindeki etkisinin ortadan kaldırılmasına bağlı olduğu ortaya çıktı. İkincisi kısmi ve yarı kısmi korelasyonlarla ilişkilidir (Şekil 16).

Pirinç. 16. Çoklu regresyon, artıkların ikili regresyonu yoluyla ifade edilebilir (formüller için Excel dosyasına bakın)

Excel'de t ve F dağılımları hakkında bilgi sağlayan işlevler vardır. ÖĞRENCİ.DAĞ() ve F.DAĞ() gibi adları DIST kısmını içeren işlevler, bağımsız değişken olarak bir t-testi veya F-testi alır ve belirtilen bir değerin gözlemlenme olasılığını döndürür. STUDENT.INV() ve F.INR() gibi adları OBR kısmını içeren işlevler, argüman olarak bir olasılık değeri alır ve belirtilen olasılığa karşılık gelen bir kriter değeri döndürür.

Kuyruk bölgelerinin kenarlarını kesen t dağılımının kritik değerlerini aradığımız için, bu olasılığa karşılık gelen değeri döndüren STUDENT.INV() işlevlerinden birine argüman olarak %5'i iletiyoruz. (Şekil 17, 18).

Pirinç. 17. İki kuyruklu t testi

Pirinç. 18. Tek kuyruklu t testi

Tek kuyruklu alfa bölgesi için karar kuralı oluşturarak testin istatistiksel gücünü artırırsınız. Bir deneye başladığınızda, pozitif (veya negatif) bir regresyon katsayısı beklemek için her türlü nedene sahip olduğunuzdan eminseniz, o zaman tek kuyruk testi yapmalısınız. Bu durumda popülasyonda sıfır regresyon katsayısı hipotezini reddederek doğru karar verme olasılığınız daha yüksek olacaktır.

İstatistikçiler bu terimi kullanmayı tercih ediyor yönlendirilmiş test terim yerine tek kuyruk testi ve dönem yönlendirilmemiş test terim yerine iki kuyruklu test. Yönlendirilmiş ve yönlendirilmemiş terimleri, dağılımın kuyruklarının doğasından ziyade hipotezin türünü vurguladıkları için tercih edilir.

Model karşılaştırmasına dayalı öngörücülerin etkisini değerlendirmeye yönelik bir yaklaşım.İncirde. Şekil 19'da Diyet değişkeninin regresyon denklemine katkısını test eden bir regresyon analizinin sonuçları sunulmaktadır.

Pirinç. 19. Sonuçlarındaki farklılıkları test ederek iki modeli karşılaştırmak

LINEST() fonksiyonunun sonuçları (H2:K6 aralığı), Diyet, Yaş ve HDL değişkenleri üzerindeki LDL değişkenini regrese eden, tam model dediğim şeyle ilgilidir. H9:J13 aralığı, öngörücü değişken Diyet'i hesaba katmadan hesaplamalar sunar. Ben buna sınırlı model diyorum. Tam modelde bağımlı değişken LDL'deki varyansın %49,2'si yordayıcı değişkenler tarafından açıklandı. Kısıtlı modelde LDL'nin yalnızca %30,8'i Yaş ve HDL değişkenleri tarafından açıklanmaktadır. Diyet değişkeninin modelden çıkarılması nedeniyle R2'deki kayıp 0,183'tür. G15:L17 aralığında Diyet değişkeninin etkisinin rastgele olmasının yalnızca 0,0288 olasılığının olduğunu gösteren hesaplamalar yapılır. Geriye kalan %97,1'lik kısımda Diyetin LDL üzerinde etkisi vardır.

Bölüm 6: Regresyon Analizine İlişkin Varsayımlar ve Uyarılar

"Varsayım" terimi yeterince kesin bir şekilde tanımlanmamıştır ve kullanılma şekli, eğer varsayım karşılanmazsa, tüm analizin sonuçlarının en azından sorgulanabilir veya belki de geçersiz olacağını düşündürmektedir. Aslında durum böyle değildir, ancak bir varsayımın ihlal edilmesinin tabloyu temelden değiştirdiği durumlar kesinlikle vardır. Temel varsayımlar: a) Y değişkeninin artıkları, regresyon çizgisi boyunca herhangi bir X noktasında normal olarak dağıtılır; b) Y değerleri X değerlerine doğrusal olarak bağlıdır; c) artıkların dağılımı her X noktasında yaklaşık olarak aynıdır; d) Artıklar arasında bağımlılık yoktur.

Varsayımların önemli bir rol oynamaması durumunda istatistikçiler, analizin varsayımın ihlaline karşı dayanıklı olduğunu söylüyor. Özellikle, grup ortalamaları arasındaki farkları test etmek için regresyon kullandığınızda, Y değerlerinin ve dolayısıyla artıkların normal şekilde dağıldığı varsayımı önemli bir rol oynamaz: testler normallik varsayımının ihlallerine karşı dayanıklıdır. Grafikleri kullanarak verileri analiz etmek önemlidir. Örneğin, eklentiye dahil Veri analizi alet Regresyon.

Veriler doğrusal regresyon varsayımlarına uymuyorsa, doğrusal olmayan başka yaklaşımlar da emrinizdedir. Bunlardan biri lojistik regresyondur (Şekil 20). Tahmin edici değişkenin üst ve alt sınırlarına yakın yerlerde, doğrusal regresyon gerçekçi olmayan tahminlerle sonuçlanır.

Pirinç. 20. Lojistik regresyon

İncirde. Şekil 6.8, yıllık gelir ile ev satın alma olasılığı arasındaki ilişkiyi araştırmayı amaçlayan iki veri analizi yönteminin sonuçlarını göstermektedir. Açıkçası, gelir arttıkça satın alma olasılığı da artacaktır. Grafikler, doğrusal regresyon yoluyla ev satın alma olasılığını tahmin eden sonuçlar ile farklı bir yaklaşım kullanarak elde edebileceğiniz sonuçlar arasındaki farkları tespit etmenizi kolaylaştırır.

İstatistikçilerin dilinde, gerçekte doğru olan sıfır hipotezini reddetmeye Tip I hata denir.

Eklentide Veri analizi Rastgele sayı üretimi için kullanışlı bir araç sunulur ve kullanıcıya istenen dağılım şeklini (örneğin, Normal, Binom veya Poisson) ve ayrıca ortalama ve standart sapmayı belirtme olanağı tanır.

STUDENT.DIST() ailesinin işlevleri arasındaki farklar. Excel 2010'dan başlayarak, bir dağılımın kesirini belirli bir t-testi değerinin soluna ve/veya sağına döndüren üç farklı işlev biçimi mevcuttur. STUDENT.DIST() işlevi, belirttiğiniz t testi değerinin solundaki dağılım eğrisinin altındaki alanın oranını döndürür. Diyelim ki 36 gözleminiz var, dolayısıyla analiz edilecek serbestlik derecesi sayısı 34 ve t-testi değeri 1,69'dur. Bu durumda formül

ÖĞRENCİ.DAĞ(+1.69;34;DOĞRU)

0,05 veya %5 değerini döndürür (Şekil 21). ÖĞRENCİ.DAĞ() fonksiyonunun üçüncü argümanı DOĞRU veya YANLIŞ olabilir. DOĞRU olarak ayarlanırsa işlev, oran olarak ifade edilen, belirtilen t testinin solundaki eğrinin altındaki kümülatif alanı döndürür. YANLIŞ ise işlev, t testine karşılık gelen noktadaki eğrinin göreceli yüksekliğini döndürür. STUDENT.DIST() işlevinin diğer sürümleri - STUDENT.DIST.PH() ve STUDENT.DIST.2X() - bağımsız değişken olarak yalnızca t-testi değerini ve serbestlik derecesi sayısını alır ve üçüncü bir değerin belirtilmesini gerektirmez argüman.

Pirinç. 21. Dağılımın sol kuyruğundaki daha koyu gölgeli alan, büyük bir pozitif t-testi değerinin solundaki eğrinin altındaki alanın oranına karşılık gelir.

T testinin sağındaki alanı belirlemek için formüllerden birini kullanın:

1 — STIODENT.DAĞ (1, 69;34;DOĞRU)

ÖĞRENCİ.MESAFE.PH(1.69;34)

Eğrinin altındaki alanın tamamı %100 olmalıdır; bu nedenle, fonksiyonun döndürdüğü t-testi değerinin solundaki alanın kesrinin 1'den çıkarılması, t-testi değerinin sağındaki alanın kesirini verir. İlgilendiğiniz alan kesirini doğrudan STUDENT.DIST.PH() işlevini kullanarak elde etmeyi tercih edebilirsiniz; burada PH, dağılımın sağ kuyruğu anlamına gelir (Şekil 22).

Pirinç. 22. Yön testi için %5 alfa bölgesi

STUDENT.DIST() veya STUDENT.DIST.PH() işlevlerini kullanmak, yönlü bir çalışma hipotezi seçtiğiniz anlamına gelir. Alfa değerinin %5'e ayarlanmasıyla birleştirilen yönlü çalışma hipotezi, %5'in tamamını dağılımların sağ kuyruğuna yerleştirdiğiniz anlamına gelir. Yalnızca elde ettiğiniz t-testi değerinin olasılığı %5 veya daha azsa sıfır hipotezini reddetmeniz gerekecektir. Yönlü hipotezler genellikle daha duyarlı istatistiksel testlerle sonuçlanır (bu daha yüksek duyarlılığa aynı zamanda daha büyük istatistiksel güç de denir).

Yönlendirilmemiş bir testte alfa değeri aynı %5 seviyesinde kalır ancak dağılım farklı olacaktır. İki sonuca izin vermeniz gerektiğinden, yanlış pozitif olasılığı dağılımın iki ucu arasında dağıtılmalıdır. Bu olasılığın eşit olarak dağıtılması genel olarak kabul edilmektedir (Şekil 23).

Önceki örnekte olduğu gibi elde edilen aynı t-testi değerini ve aynı sayıda serbestlik derecesini kullanarak aşağıdaki formülü kullanın:

ÖĞRENCİ.DAĞ.2Х(1.69;34)

STUDENT.DIST.2X() işlevi, ilk argüman olarak negatif bir t-testi değeri verilirse, belirli bir neden olmaksızın #NUM! hata kodunu döndürür.

Örnekler farklı miktarda veri içeriyorsa pakette yer alan farklı varyanslarla iki örnekli t testini kullanın. Veri analizi.

Bölüm 7: Grup Ortalamaları Arasındaki Farkları Test Etmek İçin Regresyonun Kullanılması

Daha önce yordayıcı değişkenler adı altında yer alan değişkenler bu bölümde sonuç değişkenleri olarak adlandırılacak ve yordayıcı değişkenler yerine faktör değişkenleri terimi kullanılacaktır.

Nominal bir değişkeni kodlamanın en basit yaklaşımı şudur: kukla kodlama(Şek. 24).

Pirinç. 24. Kukla kodlamaya dayalı regresyon analizi

Herhangi bir türde kukla kodlama kullanılırken aşağıdaki kurallara uyulmalıdır:

Yeni veriler için ayrılan sütun sayısı, faktör düzeyi sayısından eksi olarak eşit olmalıdır
Her vektör bir faktör seviyesini temsil eder.
Genellikle kontrol grubu olan düzeylerden birindeki denekler tüm vektörlerde 0 olarak kodlanır.

F2:H6 =LINEST(A2:A22,C2:D22,;TRUE) hücrelerindeki formül, regresyon istatistiklerini döndürür. Karşılaştırma için, Şekil 2'de. Şekil 24, aracın döndürdüğü geleneksel ANOVA'nın sonuçlarını göstermektedir. Tek yönlü ANOVA eklentiler Veri analizi.

Efekt kodlaması. Başka bir kodlama türünde efekt kodlaması, Her grubun ortalaması, grup ortalamalarının ortalaması ile karşılaştırılır. Efekt kodlamanın bu yönü, tüm kod vektörlerinde aynı kodu alan bir grup için kod olarak 0 yerine -1 kullanılmasından kaynaklanmaktadır (Şekil 25).

Pirinç. 25. Efekt Kodlaması

Sahte kodlama kullanıldığında, LINEST() tarafından döndürülen sabitin değeri, tüm vektörlerde (genellikle kontrol grubu) sıfır kod atanan grubun ortalamasıdır. Efekt kodlaması durumunda, sabit genel ortalamaya (J2 hücresi) eşittir.

Genel doğrusal model, ortaya çıkan değişkenin değer bileşenlerini kavramsallaştırmanın yararlı bir yoludur:

Y ij = μ + α j + ε ij

Bu formülde Latin harfleri yerine Yunan harflerinin kullanılması, örneklerin alındığı popülasyona atıfta bulunduğunu vurgulamaktadır ancak yayınlanmış popülasyondan alınan örneklere atıfta bulunduğunu belirtmek için yeniden yazılabilir:

Y ij = Y̅ + a j + e ij

Buradaki fikir, her Y ij gözleminin aşağıdaki üç bileşenin toplamı olarak görülebilmesidir: genel ortalama μ; işleme etkisi j ve j; e ij değeri, bireysel niceliksel gösterge Y ij'nin genel ortalamanın birleşik değerinden ve j'inci tedavinin etkisinden sapmasını temsil eder (Şekil 26). Regresyon denkleminin amacı, artıkların karelerinin toplamını en aza indirmektir.

Pirinç. 26. Genel bir doğrusal modelin bileşenlerine ayrıştırılmış gözlemler

Faktor analizi. Sonuç değişkeni ile iki veya daha fazla faktör arasındaki ilişki aynı anda inceleniyorsa, bu durumda faktör analizinin kullanılmasından söz edilir. Tek yönlü ANOVA'ya bir veya daha fazla faktörün eklenmesi istatistiksel gücü artırabilir. Tek yönlü varyans analizinde, sonuç değişkenindeki bir faktöre atfedilemeyen varyans, kalan ortalamanın karesine dahil edilir. Ancak bu varyasyonun başka bir faktörle ilişkili olması da mümkündür. Daha sonra bu değişiklik ortalama kare hatasından çıkarılabilir; bu azalma F testi değerlerinde bir artışa ve dolayısıyla testin istatistiksel gücünde bir artışa yol açar. Üstyapı Veri analizi iki faktörü aynı anda işleyen bir araç içerir (Şekil 27).

Pirinç. 27. Araç Analiz Paketinin tekrarları ile iki yönlü varyans analizi

Bu şekilde kullanılan ANOVA aracı faydalıdır çünkü tasarıma dahil edilen her grup için sayaç değerinin yanı sıra sonuç değişkeninin ortalamasını ve varyansını da döndürür. Masada Varyans analizi ANOVA aracının tek faktörlü versiyonunun çıktısında bulunmayan iki parametreyi görüntüler. Farklılık kaynaklarına dikkat edin Örnek Ve Sütunlar 27 ve 28. satırlarda. Değişikliğin kaynağı Sütunlar cinsiyete işaret eder. Varyasyonun Kaynağı Örnek değerleri farklı satırlarda yer alan herhangi bir değişkeni ifade eder. İncirde. KursLech1 grubu için 27 değer 2-6 satırlarda, KursLech2 grubu 7-11 satırlarda ve KursLechZ grubu 12-16 satırlarda yer almaktadır.

Ana nokta, Cinsiyet (E28 hücresindeki etiket Sütunları) ve Tedavi (E27 hücresindeki Etiket Örneği) faktörlerinin her ikisinin de ANOVA tablosuna varyasyon kaynakları olarak dahil edilmesidir. Erkeklerin olanakları kadınlarınkinden farklı, bu da bir çeşitlilik kaynağı yaratıyor. Üç tedavinin araçları da farklıdır ve bu da başka bir varyasyon kaynağı sağlar. Ayrıca Cinsiyet ve Tedavi değişkenlerinin birleşik etkisini ifade eden üçüncü bir kaynak olan Etkileşim vardır.

Bölüm 8. Kovaryans Analizi

Kovaryans Analizi veya ANCOVA (Ortak Değişim Analizi), önyargıyı azaltır ve istatistiksel gücü artırır. Bir regresyon denkleminin güvenilirliğini değerlendirmenin yollarından birinin F testleri olduğunu hatırlatmama izin verin:

F = MS Regresyon/MS Kalıntısı

burada MS (Ortalama Kare) ortalama karedir ve Regresyon ve Artık endeksler sırasıyla regresyon ve artık bileşenleri gösterir. MS Kalıntısı şu formül kullanılarak hesaplanır:

MS Artık = SS Artık / df Artık

burada SS (Karelerin Toplamı) karelerin toplamıdır ve df serbestlik derecesinin sayısıdır. Bir regresyon denklemine kovaryans eklediğinizde, toplam kareler toplamının bir kısmı SS Kalıntısına değil, SS Regresyonuna dahil edilir. Bu, SS Kalıntısı l'de ve dolayısıyla MS Kalıntısında bir azalmaya yol açar. MS Kalıntısı ne kadar küçük olursa, F testi de o kadar büyük olur ve ortalamalar arasında fark olmadığını belirten sıfır hipotezini reddetme olasılığınız da o kadar artar. Sonuç olarak, sonuç değişkeninin değişkenliğini yeniden dağıtırsınız. ANOVA'da kovaryans dikkate alınmadığında değişkenlik hata haline gelir. Ancak ANCOVA'da, daha önce hata terimine atfedilen değişkenliğin bir kısmı bir ortak değişkene atanır ve SS Regresyonunun bir parçası olur.

Aynı veri setinin önce ANOVA, ardından ANCOVA ile analiz edildiği bir örneği düşünün (Şekil 28).

Pirinç. 28. ANOVA analizi regresyon denkleminden elde edilen sonuçların güvenilmez olduğunu göstermektedir

Çalışma, kas gücünü geliştiren fiziksel egzersiz ile beyin aktivitesini uyaran bilişsel egzersizin (çanza bulmaca yapmak) göreceli etkilerini karşılaştırıyor. Deneyin başlangıcında her iki grubun da aynı koşullara maruz kalması için denekler rastgele iki gruba ayrıldı. Üç ay sonra deneklerin bilişsel performansı ölçüldü. Bu ölçümlerin sonuçları B sütununda gösterilmektedir.

A2:C21 aralığı, efekt kodlamasını kullanarak analiz gerçekleştirmek için LINEST() işlevine iletilen kaynak verilerini içerir. LINEST() işlevinin sonuçları E2:F6 aralığında verilir; burada E2 hücresi, etki vektörüyle ilişkili regresyon katsayısını görüntüler. E8 hücresi t-testi = 0,93'ü içerir ve E9 hücresi bu t-testinin güvenilirliğini test eder. E9 hücresinde yer alan değer, popülasyonda grup ortalamalarının eşit olması durumunda bu deneyde gözlemlenen grup ortalamaları arasında farkla karşılaşma olasılığının %36 olduğunu göstermektedir. Çok az kişi bu sonucun istatistiksel olarak anlamlı olduğunu düşünüyor.

İncirde. Şekil 29, analize bir ortak değişken eklediğinizde ne olacağını gösterir. Bu durumda her deneğin yaşını veri setine ekledim. Ortak değişkeni kullanan regresyon denkleminin belirleme katsayısı R2 0,80'dir (F4 hücresi). Ortak değişken olmadan elde edilen ANOVA sonuçlarını kopyaladığım F15:G19 aralığındaki R2 değeri yalnızca 0,05'tir (F17 hücresi). Bu nedenle, ortak değişkeni içeren bir regresyon denklemi, Bilişsel Puan değişkenine ilişkin değerleri, Etki vektörünü tek başına kullanmaktan çok daha doğru bir şekilde tahmin eder. ANCOVA için F5 hücresinde görüntülenen F-testi değerinin tesadüfen elde edilme olasılığı %0,01'den azdır.

Pirinç. 29. ANCOVA bambaşka bir tabloyu geri getiriyor

İstatistikte oluşturulan modelin kalitesini açıklayan göstergelerden biri de yaklaşıklık güvenirliği değeri olarak da adlandırılan belirleme katsayısıdır (R^2). Tahmin doğruluğunun düzeyini belirlemek için kullanılabilir. Çeşitli Excel araçlarını kullanarak bu göstergenin nasıl hesaplanacağını öğrenelim.

Belirleme katsayısının seviyesine bağlı olarak modelleri üç gruba ayırmak gelenekseldir:

0,8 – 1 – kaliteli model;
0,5 – 0,8 – kabul edilebilir kalitede model;
0 – 0,5 – düşük kaliteli model.

İkinci durumda, modelin kalitesi, onu tahmin için kullanmanın imkansızlığını gösterir.

Excel'in belirtilen değeri nasıl hesaplayacağının seçimi, regresyonun doğrusal olup olmamasına bağlıdır. İlk durumda, işlevi kullanabilirsiniz. KVPIERSON ve ikincisinde analiz paketinden özel bir araç kullanmanız gerekecek.

Yöntem 1: Doğrusal bir fonksiyon için belirleme katsayısının hesaplanması

Öncelikle doğrusal bir fonksiyonun belirleme katsayısının nasıl bulunacağını bulalım. Bu durumda bu gösterge korelasyon katsayısının karesine eşit olacaktır. Aşağıda verilen belirli bir tablo örneğini kullanarak yerleşik Excel işlevini kullanarak hesaplayalım.

Yöntem 2: Doğrusal olmayan fonksiyonlarda belirleme katsayısının hesaplanması

Ancak istenen değeri hesaplamak için yukarıdaki seçenek yalnızca doğrusal fonksiyonlara uygulanabilir. Doğrusal olmayan bir fonksiyonda bunu hesaplamak için ne yapmalısınız? Excel'in de bu seçeneği var. Bu, araç kullanılarak yapılabilir "Gerileme" paketin bir parçası olan "Veri analizi".

Ancak bu aracı kullanmadan önce kendiniz etkinleştirmeniz gerekir. "Analiz Paketi" Excel'de varsayılan olarak devre dışıdır. Sekmeye taşıma "Dosya" ve ardından öğeye geçin "Seçenekler".

Açılan pencerede bölüme gidin "Eklentiler" soldaki dikey menüde gezinerek. Pencerenin sağ alanının alt kısmında bir alan var "Kontrol". Orada bulunan alt bölümler listesinden adı seçin "Excel Eklentileri..." ve ardından düğmeye tıklayın "Gitmek..." sahanın sağında yer alır.

Eklentiler penceresi başlatılır. Orta kısmında mevcut eklentilerin bir listesi vardır. Konumun yanındaki onay kutusunu ayarlayın "Analiz Paketi". Bunu butona tıklayarak takip ediyoruz "TAMAM" pencere arayüzünün sağ tarafında.

Araç paketi "Veri analizi" Excel'in mevcut örneğinde etkinleştirilecektir. Buna erişim sekmedeki şeritte bulunur "Veri". Belirtilen sekmeye gidin ve düğmeye tıklayın "Veri analizi" ayarlar grubunda "Analiz".

Pencere etkinleştirildi "Veri analizi"özel bilgi işleme araçlarının bir listesiyle. Bu listeden öğeyi seçiyoruz "Gerileme" ve düğmeye tıklayın "TAMAM".

Daha sonra araç penceresi açılır "Gerileme". İlk ayar seti "Veri girişi". Burada iki alanda argüman ve fonksiyon değerlerinin bulunduğu aralıkların adreslerini belirtmeniz gerekir. İmleci alana yerleştirin "Giriş aralığı Y" ve sayfadaki sütunun içeriğini vurgulayın "E". Dizi adresi pencerede görüntülendikten sonra "Gerileme", imleci alana yerleştirin "Giriş aralığı Y" ve sütun hücrelerini de aynı şekilde seçin "X".
Parametreler hakkında "İşaret" Ve "Sabit sıfır" Kutuları işaretlemiyoruz. Onay kutusu parametrenin yanında ayarlanabilir "Güvenilirlik düzeyi" ve karşıdaki alanda ilgili göstergenin istenen değerini belirtin (varsayılan olarak %95).

Grup içinde "Çıktı Seçenekleri" hesaplama sonucunun hangi alanda görüntüleneceğini belirtmeniz gerekir. Üç seçenek var:
- Geçerli sayfadaki alan;
- Başka bir sayfa;
- Başka bir kitap (yeni dosya).
Kaynak verinin ve sonucun tek bir çalışma sayfasına yerleştirilmesi için ilk seçeneği seçelim. Anahtarı parametrenin yanına yerleştirin "Çıktı Aralığı". İmleci bu öğenin karşısındaki alana yerleştirin. Hesaplama sonuçlarını görüntülemek için tablonun sol üst hücresi olması amaçlanan sayfadaki boş bir öğeye sol tıklıyoruz. Bu öğenin adresi pencere alanında görüntülenmelidir "Gerileme".

Parametre grupları "Kalıntılar" Ve "Normal Olasılık" Eldeki görevi çözmek için önemli olmadıklarından onları görmezden geliriz. Bundan sonra düğmeye tıklayın "TAMAM" pencerenin sağ üst köşesinde bulunur "Gerileme".

Program, önceden girilen verilere dayanarak hesaplamalar yapar ve sonucu belirtilen aralıkta görüntüler. Gördüğünüz gibi bu araç, sayfada çeşitli parametreler için oldukça fazla sayıda sonuç görüntüler. Ancak mevcut ders bağlamında göstergeyle ilgileniyoruz "R-kare". Bu durumda seçilen modeli kaliteli bir model olarak nitelendiren 0,947664'e eşittir.

Yöntem 3: Trend çizgisinin belirleme katsayısı

Yukarıdaki seçeneklere ek olarak, bir Excel sayfasında oluşturulan bir grafikte trend çizgisi için belirleme katsayısı doğrudan görüntülenebilir. Bunun belirli bir örnekle nasıl yapılabileceğini bulalım.

Önceki örnekte kullanılan fonksiyonun argüman tablosuna ve değerlerine dayanan bir grafiğimiz var. Ona bir trend çizgisi oluşturalım. Grafiğin bulunduğu çizim alanında herhangi bir yere farenin sol tuşuyla tıklayın. Bu durumda şeritte ek bir dizi sekme görünür - "Diyagramlarla çalışma". Sekmeye git "Düzen". Düğmeye tıklayın "Trend Hattı" Araç bloğunda bulunan "Analiz". Trend çizgisi türü seçimini içeren bir menü görüntülenir. Belirli göreve uygun türü seçiyoruz. Örneğimiz için seçeneği seçelim "Üstel Yaklaşım".

Excel, doğrudan çizim düzleminde ek bir siyah eğri biçiminde bir eğilim çizgisi çizer.

Şimdi görevimiz belirleme katsayısının kendisini göstermektir. Trend çizgisine sağ tıklayın. Bağlam menüsü etkinleştirilir. Bu noktada seçimi durduruyoruz "Trend Çizgisi Formatı...".

Trend Çizgisi Formatı penceresine gitmek için gerçekleştirebileceğiniz alternatif bir eylem vardır. Farenin sol tuşuyla tıklayarak trend çizgisini seçin. Sekmeye taşıma "Düzen". Düğmeye tıklayın "Trend Hattı" blokta "Analiz". Açılan listede eylemler listesindeki en son öğeye tıklayın - "Ek trend çizgisi seçenekleri...".

Yukarıdaki iki eylemden herhangi birinin ardından, ek ayarların yapılabileceği bir biçimlendirme penceresi açılır. Özellikle görevimizi tamamlamak için öğenin yanındaki kutuyu işaretlemeniz gerekir. “Yaklaşım güvenilirlik değerini (R^2) diyagrama yerleştirin”. Pencerenin en altında bulunur. Yani bu sayede tespit katsayısının inşaat alanı üzerinde görüntülenmesini sağlıyoruz. Daha sonra butona tıklamayı unutmayın "Kapalı" Geçerli pencerenin altında.

Yaklaşım güvenirlik değeri yani belirleme katsayısının değeri inşaat alanındaki pafta üzerinde gösterilecektir. Bu durumda, gördüğümüz gibi, bu değer 0,9242'ye eşittir ve bu, yaklaşımın iyi kalitede bir model olduğunu karakterize eder.

Kesinlikle bu şekilde, diğer herhangi bir trend çizgisi türü için belirleme katsayısının görüntüsünü ayarlayabilirsiniz. Yukarıda gösterildiği gibi şerit üzerindeki düğmeden veya içerik menüsünden parametre penceresine giderek trend çizgisinin türünü değiştirebilirsiniz. Daha sonra gruptaki pencerenin kendisinde "Bir trend çizgisi oluşturmak" başka bir türe geçebilirsiniz. Aynı zamanda noktaya yakın olanı da kontrol etmeyi unutmayın. “Yaklaşım güvenilirliği değerini diyagrama yerleştirin” onay kutusu işaretlendi. Yukarıdaki adımları tamamladıktan sonra butona tıklayın "Kapalı" pencerenin sağ alt köşesinde.

Doğrusal tipte trend çizgisinin yaklaşık güvenilirlik değeri zaten 0,9477'dir; bu, bu modeli daha önce ele aldığımız üstel tipteki trend çizgisinden bile daha güvenilir olarak nitelendirir.

Böylece, farklı trend çizgileri türleri arasında geçiş yaparak ve bunların yaklaşık güvenilirlik değerlerini (belirleme katsayısı) karşılaştırarak, modeli sunulan grafiği en doğru şekilde tanımlayan seçeneği bulabilirsiniz. Belirleme katsayısı en yüksek olan seçenek en güvenilir olan olacaktır. Buna dayanarak en doğru tahmini oluşturabilirsiniz.
Örneğin, bizim durumumuzda, en yüksek güvenilirlik seviyesinin ikinci dereceden polinom tipi bir trend çizgisine sahip olduğunu tespit etmek deneysel olarak mümkündü. Bu durumda belirleme katsayısı 1'e eşittir. Bu, belirtilen modelin kesinlikle güvenilir olduğunu, yani hataların tamamen ortadan kaldırıldığını gösterir.

Ancak aynı zamanda bu, başka bir grafik için bu tür bir trend çizgisinin de en güvenilir olacağı anlamına gelmez. Trend çizgisi türünün optimum seçimi, grafiğin oluşturulduğu fonksiyonun türüne bağlıdır. Kullanıcı, en iyi seçeneği "gözle" tahmin etmek için yeterli bilgiye sahip değilse, o zaman en iyi tahmini belirlemenin tek yolu, yukarıdaki örnekte gösterildiği gibi belirleme katsayılarını karşılaştırmaktır.

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 bu tür analizleri gerçekleştirmek için tasarlanmış araçları vardır. Gelin bunların ne olduğuna 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 etmeniz gerekmektedir. Ancak o zaman bu prosedür için gerekli araçlar Excel şeridinde görünecektir.

"Dosya" sekmesine gidin.
"Ayarlar" bölümüne gidin.
Excel Seçenekleri penceresi açılır. “Eklentiler” alt bölümüne gidin.
Açılan pencerenin en altında “Yönetim” bloğundaki anahtarı farklı bir konumda ise “Excel Eklentileri” konumuna getirin. “Git” butonuna tıklayın.
Kullanılabilir Excel eklentilerini içeren bir pencere açılır. “Analiz Paketi”nin yanındaki kutuyu işaretleyin. “Tamam” düğmesine tıklayın.

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

Regresyon Analizi Türleri

Birkaç tür regresyon vardır:

parabolik;
sakinleştirici;
logaritmik;
üstel;
gösterici;
hiperbolik;
doğrusal regresyon.

Regresyon analizinin son türünün Excel'de yapılması konusunu daha sonra detaylı olarak konuşacağız.

Excel'de Doğrusal Regresyon

Aşağıda örnek olarak, dışarıdaki 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şuna katılımı tam olarak nasıl etkileyebileceğini regresyon analizini kullanarak öğrenelim.

Genel doğrusal regresyon denklemi şu şekildedir: Y = a0 + a1x1 +…+ akhk. Bu formülde Y, faktörlerin etkisini incelemeye çalıştığımız değişken anlamına gelir. Bizim durumumuzda bu alıcı 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 belirleyenler onlardır. k endeksi 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-karedir. Modelin kalitesini gösterir. Bizim durumumuzda bu katsayı 0,705 yani %70,5 civarındadır. Bu kabul edilebilir bir kalite seviyesidir. Bağımlılığın 0,5'ten az olması kötüdür.

Bir diğer önemli gösterge ise “Y-kesişimi” satırı ile “Katsayılar” sütununun kesişimindeki hücrede bulunur. Bu, Y'nin hangi değere sahip olacağını gösterir ve bizim durumumuzda bu, diğer tüm faktörlerin sıfıra eşit olduğu alıcıların sayısıdır. Bu tabloda bu değer 58,04'tür.

“Değişken X1” ve “Katsayılar” sütunlarının kesişimindeki değer, Y'nin X'e bağımlılık düzeyini gösterir. Bizim durumumuzda bu, mağaza müşteri 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 regresyon analiz tablosu oluşturmak oldukça kolaydır. Ancak yalnızca eğitimli bir kişi çıktı verileriyle çalışabilir ve özünü anlayabilir.

Sorunu çözmenize yardımcı olabildiğimiz için mutluyuz.

Sorunun özünü ayrıntılı olarak açıklayarak sorunuzu yorumlarda sorun. Uzmanlarımız mümkün olan en kısa sürede cevap vermeye çalışacaklardı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ıza olanak tanır. 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ğeri,

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

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

b, düz çizginin eğimidir.

Aşağıdaki şekil bu kavramı grafiksel olarak göstermektedir:

Yukarıdaki şekil ŷ =2+0,5x denklemiyle tanımlanan doğruyu göstermektedir. Y kesme noktası, doğrunun y ekseniyle kesiştiği noktadır; bizim durumumuzda a = 2. Doğrunun eğimi b, yani çizginin yükselişinin çizginin uzunluğuna oranı 0,5 değerine sahiptir. Pozitif eğim, doğrunun soldan sağa doğru yükseldiği anlamına gelir. Eğer b = 0 ise çizgi yataydır, yani bağımlı ve bağımsız değişkenler arasında ilişki yoktur. Yani x'in değerini değiştirmek y'nin değerini etkilemez.

ŷ ve y sıklıkla karıştırılır. Grafik, verilen denkleme göre 6 sıralı nokta çiftini ve bir doğruyu göstermektedir.

Bu şekil x = 2 ve y = 4 sıralı ikilisine 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 noktayı temsil eder ve ŷ değeri, belirli bir x değeri için doğrusal bir denklem kullanılarak y'nin beklenen 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 biçimini belirlediğimizde bahsetmiştik.

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

Excel'de yerleşik olan regresyon analizi aracını kullanmak için eklentiyi etkinleştirmelisiniz Analiz paketi. Sekmeye tıklayarak bulabilirsiniz Dosya -> Seçenekler(2007+), görüntülenen iletişim kutusunda Seçeneklerexcel sekmeye git Eklentiler. Tarlada Kontrol seçmek Eklentilerexcel ve tıklayın Gitmek. Görüntülenen pencerede yanındaki kutuyu işaretleyin Analiz paketi, tıklamak TAMAM.

Sekmede 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. Küvet örneğimizdeki verileri boş 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 Regresyonşekilde gösterildiği gibi seçin ve Tamam'a tıklayın.

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

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

Bu sonuçlar, önceki makalede 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östermenize olanak tanıyan istatistiksel bir araştırma yöntemidir. Bilgisayar öncesi dönemde, özellikle büyük miktarda veri söz konusu olduğunda kullanımı oldukça zordu. Bugün, Excel'de nasıl regresyon oluşturulacağını öğrendikten sonra, karmaşık istatistiksel problemleri yalnızca birkaç dakika içinde çözebilirsiniz. Aşağıda ekonomi alanından spesifik örnekler bulunmaktadır.

Regresyon Türleri

Bu kavramın kendisi matematiğe 1886'da Francis Galton tarafından tanıtıldı. Regresyon gerçekleşir:

doğrusal;
parabolik;
sakinleştirici;
üstel;
hiperbolik;
gösterici;
logaritmik.

örnek 1

6 sanayi işletmesinde emekli ekip üyesi sayısının ortalama maaşa bağımlılığını belirleme problemini düşünün.

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

6 işletmede işten ayrılan işçi sayısının ortalama maaşa bağımlılığını belirlemek için regresyon modeli Y = a0 + a1×1 +…+аkxk denklemi formuna sahiptir; burada хi, etkileyen değişkenlerdir, ai regresyon katsayılarıdır ve k, faktör sayısıdır.

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

"Excel" elektronik tablosunun yeteneklerini kullanma

Excel'deki regresyon analizinden önce, yerleşik işlevlerin mevcut tablo verilerine uygulanması gerekir. Ancak bu amaçlar için çok kullanışlı olan “Analiz Paketi” eklentisini 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, aşağıda bulunan “Git” butonuna tıklayın;
“Analiz paketi” adının yanındaki kutuyu işaretleyin ve işlemlerinizi “Tamam”a tıklayarak onaylayın.

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

Excel'de Doğrusal Regresyon

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

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

Sonuç olarak, program yeni bir elektronik tabloyu otomatik olarak regresyon analizi verileriyle dolduracaktır. Not! Excel, bu amaç için tercih ettiğiniz konumu manuel olarak ayarlamanıza olanak tanır. Örneğin bu, Y ve X değerlerinin bulunduğu 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 örnekteki verilerin işlenmesi sırasında elde edilen veriler şu şekildedir:

Ö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, söz konusu 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 uygundur. R-kare değeri 0,8'in üzerinde olduğunda gerçek durumu doğru tanımladığı düşünülmektedir. R-kare tcr ise, doğrusal denklemin serbest teriminin önemsizliğine ilişkin hipotez reddedilir.

Serbest terim için ele alınan problemde Excel araçlarını kullanarak t = 169.20903 ve p = 2.89E-12 elde edildi, yani serbest terimin önemsizliğiyle ilgili doğru hipotezin reddedilme olasılığı sıfır . Bilinmeyen katsayı için t=5,79405 ve p=0,001158. Yani bir bilinmeyen için katsayının önemsizliğine ilişkin doğru hipotezin reddedilme olasılığı %0,12'dir.

Dolayısıyla ortaya çıkan doğrusal regresyon denkleminin yeterli olduğu söylenebilir.

Bir blok hisse satın almanın fizibilite sorunu

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

NNN şirketinin yönetimi, MMM JSC'nin %20 hissesini satın almanın tavsiye edilebilirliğine karar vermelidir. Paketin (SP) maliyeti 70 milyon ABD dolarıdır. NNN uzmanları benzer işlemler hakkında veri topladı. Hisse blokunun değerinin milyonlarca ABD doları cinsinden ifade edilen parametrelere göre aşağıdaki şekilde değerlendirilmesine karar verildi:

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

Ek olarak, işletmenin binlerce ABD doları cinsinden ödenmemiş ücret borçları (V3 P) parametresi kullanılmaktadır.

Excel elektronik tablo işlemcisini kullanan çözüm

Her şeyden önce, kaynak verilerden oluşan bir tablo oluşturmanız gerekir. Şuna benziyor:

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

“Yeni çalışma sayfası” öğesini işaretleyin ve “Tamam”a tıklayın.

Belirli bir problem için regresyon analizi elde edin.

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

Regresyon denklemini yukarıda Excel elektronik tablosunda sunulan yuvarlatılmış verilerden "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

MMM JSC'ye ilişkin veriler tabloda sunulmaktadır:

Bunları regresyon denkleminde yerine koyarsak 64,72 milyon ABD doları tutarında bir rakam elde ederiz. Bu, MMM JSC'nin hisselerinin satın almaya değer olmadığı anlamına geliyor, çünkü 70 milyon ABD doları değerindeki değerleri oldukça şişirilmiş.

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

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

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

x bağımsız bir değişkendir,

a, düz bir çizginin y eksenindeki bir parçasıdır,