Excel'de regresyon çözümleri örnekleri. Psikolojide Matematiksel Yöntemler

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. işlevsellikte Microsoft Excel Bu tür analizler için araçlar mevcuttur. Gelin bunların ne olduğuna ve nasıl kullanılacağına bir göz atalım.

Ancak regresyon analizi yapmanızı sağlayan fonksiyonu kullanabilmek için öncelikle Analiz Paketini aktif etmeniz 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;
• lineer regresyon.

Son görünümün yürütülmesi hakkında regresyon analizi Excel hakkında 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 şöyle görünür: 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ı sayısıdır. Anlam x değişkeni etkileyen çeşitli faktörlerdir. parametreler a regresyon katsayılarıdır. Yani, belirli bir faktörün önemini belirlerler. dizin k aynı faktörlerin toplam sayısını ifade eder.

Analiz sonuçları analizi

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

Ana göstergelerden biri, R Meydanı. Modelin kalitesini gösterir. bizim durumumuzda verilen 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, çizginin kesişme noktasındaki hücrede bulunur. "Y-kavşak" ve sütun "katsayılar". Burada Y değerine sahip olacağı belirtilir ve bizim durumumuzda bu, diğer tüm faktörler sıfıra eşit olan alıcıların sayısıdır. Bu tabloda bu değer 58.04'tür.

Grafiğin kesişim 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üş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üğümüz gibi, ile Microsoft programları Excel'de 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 özlerini anlayabilir.

Bina doğrusal regresyon, Excel analiz paketi (Regresyon) kullanıldığında parametrelerinin değerlendirilmesi ve önemleri çok daha hızlı gerçekleştirilebilir. Genel durumda elde edilen sonuçların yorumunu ele alalım ( k açıklayıcı değişkenler) Örnek 3.6'ya göre.

Tablo regresyon istatistikleri değerler verilir:

çoklu r – çoklu korelasyon katsayısı;

r- Meydan- determinasyon katsayısı r 2 ;

normalleştirilmiş r - Meydan- ayarlanmış r 2 serbestlik derecesi sayısı için ayarlanmış;

standart hata regresyonun standart hatasıdır S;

gözlemler - gözlem sayısı n.

Tablo varyans analizi verilen:

1. Sütun df - serbestlik derecesi sayısı, eşit

dize için regresyon df = k;

dize için kalandf = n – k – 1;

dize için Toplamdf = n– 1.

2. Sütun SS- kare sapmaların toplamı, eşit

dize için regresyon ;

dize için kalan ;

dize için Toplam .

3. Sütun HANIM formül tarafından belirlenen varyanslar HANIM = SS/df:

dize için regresyon– faktör varyansı;

dize için kalan kalan varyanstır.

4. Sütun F - hesaplanan değer F- formül tarafından hesaplanan kriterler

F = HANIM(gerileme)/ HANIM(geri kalan).

5. Sütun Önemi F hesaplanan değere karşılık gelen anlamlılık düzeyi değeridir. F-İstatistik .

Önemi F= İLK( F-İstatistik, df(gerileme), df(kalan)).

eğer önemi F < стандартного уровня значимости, то r 2 istatistiksel olarak anlamlıdır.

	katsayılar	standart hata	t-istatistikleri	p değeri	alt %95	En yüksek %95
Y	65,92	11,74	5,61	0,00080	38,16	93,68
x	0,107	0,014	7,32	0,00016	0,0728	0,142

Bu tablo şunları gösterir:

1. oranlar– katsayı değerleri a, B.

2. Standart hata regresyon katsayılarının standart hatalarıdır bir, Sb.

3. T-İstatistik– hesaplanan değerler T - aşağıdaki formülle hesaplanan kriterler:

t-istatistik = Katsayılar / Standart hata.

4.r-değer (önem T) hesaplanan değere karşılık gelen anlamlılık düzeyinin değeridir. T-İstatistik.

r-değer = STUDRASP(T-İstatistik, df(kalan)).

Eğer r-anlam< стандартного уровня значимости, то соответствующий коэффициент статистически значим.

5. Alt %95 ve Üst %95 teorik lineer regresyon denkleminin katsayıları için %95 güven aralıklarının alt ve üst sınırlarıdır.

KALAN ÇEKİM
Gözlem	tahmin edilen y	e kalır
	72,70	-29,70
	82,91	-20,91
	94,53	-4,53
	105,72	5,27
	117,56	12,44
	129,70	19,29
	144,22	20,77
	166,49	24,50
	268,13	-27,13

Tablo KALAN ÇEKİM belirtilen:

bir sütunda Gözlem– gözlem numarası;

bir sütunda tahmin edilen y bağımlı değişkenin hesaplanan değerleridir;

bir sütunda Kalıntılar e bağımlı değişkenin gözlenen ve hesaplanan değerleri arasındaki farktır.

Örnek 3.6. Gıda harcamalarına ilişkin mevcut veriler (arb. birimler) y ve kişi başına düşen gelir x dokuz aile grubu için:

x
y

Excel analiz paketinin (Regresyon) sonuçlarını kullanarak, gıda maliyetlerinin kişi başına gelir değerine bağımlılığını analiz ediyoruz.

Regresyon analizinin sonuçları genellikle şu şekilde yazılır:

burada parantez içinde regresyon katsayılarının standart hataları verilmiştir.

Regresyon katsayıları fakat = 65,92 ve B= 0.107. arasındaki iletişim yönü y Ve x regresyon katsayısının işaretini belirler B= 0.107, yani ilişki doğrudan ve pozitiftir. katsayı B= 0.107, kişi başına gelirde 1 arb artışla olduğunu gösterir. birimler gıda maliyetleri 0,107 dönş. birimler

Elde edilen modelin katsayılarının önemini tahmin edelim. Katsayıların önemi ( bir, b) karşı kontrol edilir T- Ölçek:

p değeri ( a) = 0,00080 < 0,01 < 0,05

p değeri ( B) = 0,00016 < 0,01 < 0,05,

dolayısıyla katsayılar ( bir, b) %1 düzeyinde anlamlıdır ve %5 anlamlılık düzeyinde daha da anlamlıdır. Bu nedenle regresyon katsayıları anlamlıdır ve model orijinal veriler için yeterlidir.

Regresyon tahmin sonuçları, yalnızca regresyon katsayılarının elde edilen değerleriyle değil, aynı zamanda bazı kümeleriyle (güven aralığı) uyumludur. %95 olasılıkla katsayılar için güven aralıkları (38.16 - 93.68) şu şekildedir: a ve (0.0728 - 0.142) için B.

Modelin kalitesi, belirleme katsayısı ile değerlendirilir. r 2 .

Değer r 2 = 0.884, kişi başına gelir faktörünün gıda harcamalarındaki değişimin (dağılımın) %88,4'ünü açıklayabileceği anlamına gelir.

Önemi r 2 tarafından kontrol edildi F- test: önem F = 0,00016 < 0,01 < 0,05, следовательно, r 2, %1 düzeyinde anlamlıdır ve hatta %5 anlamlılık düzeyinde daha da anlamlıdır.

İkili doğrusal regresyon durumunda, korelasyon katsayısı şu şekilde tanımlanabilir: . Korelasyon katsayısının elde edilen değeri, gıda harcamaları ile kişi başına gelir arasındaki ilişkinin çok yakın olduğunu göstermektedir.

Ortaya çıkan y özelliğindeki değişiklik, x faktör özelliğinin varyasyonundan kaynaklanmaktadır. Elde edilen özniteliğin toplam varyansında regresyon tarafından açıklanan varyansın payı belirleme katsayısını karakterize eder R 2. İçin doğrusal bağımlılık belirleme katsayısı, korelasyon katsayısının karesine eşittir:

R 2 = r xy 2 , burada r xy 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. Diğer bir deyişle, regresyon denkleminin seçiminin doğruluğu yüksektir.

Regresyon denkleminin seçiminin 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ı ile hesaplanır. saat çoklu regresyon, belirleme katsayısı Çoklu Regresyon hizmeti aracılığıyla bulunabilir.
Genel durumda, belirleme katsayısı şu formülle bulunur: veya
Varyans ekleme kuralı:
,
kare sapmaların toplam toplamı nerede;
- regresyona bağlı sapmaların karelerinin toplamı (“açıklanmış” veya “faktöriyel”);
- kare sapmaların kalan toplamı.

Bu çevrimiçi hesap makinesi determinasyon katsayısı ve önemi kontrol edilir (Çözüm örneği).

Talimat. Kaynak veri miktarını belirtin. Ortaya çıkan çözüm şurada saklanır: Kelime dosyası. Çözümü Excel'de test etmek için otomatik olarak bir şablon da oluşturulur.

Doğrusal regresyon yöntemi, bir dizi sıralı çifte (x, y) en iyi uyan düz bir çizgiyi tanımlamamızı sağlar. Düz bir çizgi için denklem olarak bilinen Doğrusal Denklem, aşağıda sunulmuştur:

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

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

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

b, doğrunun eğimidir.

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

Yukarıdaki şekil, ŷ =2+0.5x denklemi ile tanımlanan bir çizgiyi göstermektedir. Y eksenindeki doğru parçası, doğrunun y eksenini kestiği noktadır; bizim durumumuzda, a = 2. Doğrunun eğimi, b, doğru yüksekliğinin doğru uzunluğuna oranı 0,5 değerindedir. Pozitif eğim, çizginin soldan sağa doğru yükseldiği anlamına gelir. b = 0 ise doğru yataydır, yani bağımlı ve bağımsız değişkenler arasında bir 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 doğruyu gösterir.

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 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ümesine maksimum olarak karşılık gelen doğrusal denklemi belirlemektir, bundan denklemin şeklini belirlediğimiz önceki makalede bahsetmiştik.

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

Excel'de yerleşik olarak bulunan regresyon analizi aracını kullanmak için eklentiyi etkinleştirmeniz gerekir. Analiz paketi. Sekmeye tıklayarak bulabilirsiniz Dosya –> Seçenekler(2007+), görünen iletişim kutusunda parametrelermükemmel sekmeye git Eklentiler. alanında Kontrol Seç eklentilermükemmel ve tıklayın Gitmek. Görünen pencerede, yanındaki kutuyu işaretleyin. analiz paketi, Tıklayın 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 bir masayı paylaştığı verileri kullanalım. Banyo örneğimiz için verileri boş bir sayfanın A ve B sütunlarına girin.

Sekmeye git Veri, grup içinde analiz Tıklayın Veri analizi. Görünen pencerede Veri analizi Seçme regresyonşekilde gösterildiği gibi 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, içinde bağımsız hesaplamalarla elde ettiğimiz sonuçlarla tutarlıdır.

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

Ana görevler ve regresyon türleri

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

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

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

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

Birkaç tür regresyon analizi vardır:

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

İki tür regresyon denklemi vardır:

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

Model oluşturma talimatları

Excel'de belirli bir yapıyı gerçekleştirmek için talimatları izlemelisiniz:

Daha fazla hesaplama için, Y Değerlerini, X Değerlerini, Sabit ve istatistikleri belirten "Linear()" işlevi kullanılmalıdır. Bundan sonra, "Trend" işlevini kullanarak regresyon çizgisi üzerindeki nokta kümesini belirleyin - Y değerleri, X değerleri, Yeni değerler, Sabit. yardım ile parametreleri ayarla Problemin verilen koşullarına göre katsayıların bilinmeyen değerini hesaplar.