Excel'de regresyon: denklem, örnekler. Doğrusal Regresyon

Microsoft Excel'de Regresyon Analizi, iş zekası alanındaki regresyon analizi problemlerini çözmek için MS Excel'i kullanmanın en kapsamlı kılavuzudur. Konrad Carlberg, bilgisi 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 dayalıdır ve ilgili adım adım prosedürlerin ayrıntılı bir açıklamasıyla birlikte sunulur.

Bu kitap, Excel'in regresyon fonksiyonlarının tüm ayrıntılarını tartışıyor, seçeneklerin ve argümanların her birini kullanmanın sonuçlarını inceliyor ve regresyon tekniklerinin tıbbi araştırmadan finansal analize kadar uzanan alanlarda güvenilir bir şekilde nasıl uygulanacağını açıklıyor.

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

Notu veya formatında indirin, formatta örnekler

Bölüm 1. Veri değişkenliğini tahmin etme

İstatistikçilerin emrinde birçok varyasyon (değişkenlik) göstergesi vardır. Bunlardan biri, bireysel değerlerin ortalamadan kare sapmalarının 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, incelenen veri kümesindeki değer sayısına duyarsızdır (oysa karesi alınmış sapmaların toplamı, ölçüm sayısıyla artar).

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

İşlenecek değerler bir popülasyon oluşturuyorsa VAR.G() işlevini kullanın. Yani, aralıkta yer alan değerler, yalnızca ilgilendiğiniz değerlerdir.
İşlenecek değerler daha büyük bir popülasyondan bir örnek oluşturuyorsa VAR.V() işlevini kullanın. Varyansını da tahmin edebileceğiniz ek değerler olduğunu varsayar.

Ortalama veya korelasyon katsayısı gibi bir değer, genel popülasyon bazında hesaplanırsa buna parametre denir. Bir örneklem temelinde hesaplanan benzer bir değere istatistik denir. Sapmaları saymak ortalamadan bu kümede, sapmaların karelerinin toplamını başka herhangi bir değerden saydığınızdan daha küçük bir miktarda elde edeceksiniz. Benzer bir ifade dağılım için de geçerlidir.

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

Diyelim ki, ortalamaları popülasyon ortalamasından farklı olan, fark ne kadar küçük olursa olsun, 100 boydan oluşan bir kümeniz var. Numune için varyansı hesapladığınızda, 4 gibi bir değer elde edersiniz. Bu değer, 100 büyüme değerinin her birinin numune ortalaması dışındaki herhangi bir değerden sapması hesaplanarak elde edilebilecek diğer tüm değerlerden daha azdır. , genel nüfus için gerçek ortalama dahil. Bu nedenle, hesaplanan varyans, örneklem ortalamasını değil de popülasyon parametresini bir şekilde bilip kullanmış olsaydınız elde edeceğiniz varyanstan daha az ölçüde farklı olacaktır.

Örnek 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 yansı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üğüdür ve n - 1.

Değer n - 1 serbestlik derecesi sayısı (sayı) olarak adlandırılır. Bu değeri hesaplamanın farklı yolları vardır, ancak bunların tümü ya örneklem büyüklüğünden bazı sayıların çıkarılmasını ya da gözlemlerin içine düştüğü kategorilerin sayısını saymayı içerir.

DISP.G() ve DISP.V() işlevleri arasındaki farkın özü şu şekildedir:

VARI.G() işlevinde, kareler toplamı gözlem sayısına bölünür ve bu nedenle varyansın önyargılı tahminini, yani gerçek ortalamayı temsil eder.
VAR.B() işlevinde, kareler toplamı gözlem sayısı eksi 1'e bölünür, yani örneğin alındığı popülasyonun varyansının daha doğru, yansız bir tahminini veren serbestlik derecesi sayısına göre.

standart sapma (İngilizce) standart sapma, SD) varyansın kareköküdür:

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

Veriler bir miktar manipülasyona tabi tutulduktan sonra genellikle standart sapmayı hesaplamak gerekir. Ve bu durumlarda sonuçlar şüphesiz standart sapmalar olsa da, genellikle 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 boyu hakkında veri topluyorsunuz. Ardından, her eyaletteki yetişkin erkeklerin ortalama boyunu hesaplarsınız. Ortaya çıkan 50 ortalama değer, sırayla gözlem olarak kabul edilebilir. Bundan, standart sapmalarını hesaplayabilirsiniz, ki bu 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ı ile karşılaştırmanıza olanak tanır. Ortalamanın standart hatasını tahmin etme formülü (yani, bireysel gözlemlerin değil, ortalamaların standart sapması):

ortalamanın standart hatası nerede; S orijinal gözlemlerin standart sapmasıdır; Nörneklemdeki gözlem sayısıdır.

Pirinç. 1. Eyaletten eyalete ortalama değerlerdeki değişiklik, bireysel gözlemlerin varyasyonundan ç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 ve örnek istatistikleri Latin harfleriyle belirtmek gelenekseldir. Bu nedenle, popülasyonun standart sapmasından bahsediyorsak, bunu σ olarak yazarız; örneğin standart sapması dikkate alınırsa, s gösterimini kullanırız. Ortalamalar için sembollere gelince, birbirleriyle pek uyuşmuyorlar. Nüfus ortalaması, Yunanca μ harfi ile gösterilir. Bununla birlikte, X̅ sembolü geleneksel olarak örnek ortalamayı temsil etmek için kullanılır.

z puanı standart sapma birimleri cinsinden dağılımdaki gözlemin konumunu ifade eder. Örneğin, z = 1,5, gözlemin ortalamadan 1,5 standart sapma uzakta, daha yüksek değerlere doğru olduğu anlamına gelir. Terim z puanı bireysel değerlendirmeler için kullanılır, örn. örneğin bireysel öğelerine atfedilen ölçümler için. Bu tür istatistikler için (örneğin, durum ortalaması), terim kullanılır. z değeri:

burada X̅ örneğin ortalama değeridir, μ genel popülasyonun ortalama değeridir, örnek kümesinin araçlarının standart hatasıdır:

σ genel popülasyonun standart hatasıdır (bireysel ölçümler), Nörneklem büyüklüğüdür.

Diyelim ki bir golf eğitmenisiniz. Uzun süredir vuruş menzilini ölçebiliyorsunuz ve ortalama 205 yarda ve 36 yarda standart sapma olduğunu biliyorsunuz. Menzilinizi 10 yard artıracağını iddia ederek size yeni bir sopa teklif edildi. Sonraki 81 kulüp müdaviminin her birinden yeni bir kulüple denemelerini ve menzillerini kaydetmelerini istiyorsunuz. Yeni bir sopayla ortalama vuruş menzilinin 215 yarda olduğu ortaya çıktı. 10 yarda farkın (215 - 205) yalnızca örnekleme hatasından kaynaklanma olasılığı nedir? Veya başka bir deyişle, daha büyük bir testte, yeni bir sopanın mevcut uzun vadeli ortalama olan 205 yardaya kıyasla menzilde bir artış göstermeme olasılığı nedir?

Bunu bir z değeri üreterek test edebiliriz. Ortalamanın standart hatası:

O zaman z değeri:

Numune ortalamasının popülasyon ortalamasından 2,5σ uzakta olma olasılığını bulmamız gerekiyor. Olasılık küçükse, farklılıklar şansa değil, yeni kulübün kalitesine bağlıdır. Excel'de bir z puanının olasılığını belirlemek için hazır bir işlev yoktur. Ancak, =1-NORM.ST.DAĞ(z-değeri, DOĞRU) formülünü kullanabilirsiniz; burada NORM.ST.DAĞ(), z-değerinin solundaki normal eğrinin altındaki alanı döndürür (Şekil 2) .

Pirinç. 2. NORM.S.DIST() işlevi, z-değerinin solundaki eğri altındaki alanı döndürür; Bir görüntüyü büyütmek için üzerine sağ tıklayın ve seçin Resmi yeni sekmede aç

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

Popülasyonun ortalaması (μ) ve standart sapması (σ) bilinmiyorsa, t-değeri kullanılır (bakınız ). Z- ve t-skoru yapıları, t-değerini bulmak için popülasyon parametresi σ'nin bilinen değeri yerine numune sonuçlarından elde edilen standart sapma s kullanılması bakımından farklılık gösterir. Normal eğri tek bir şekle sahiptir ve t değerlerinin dağılımının şekli serbestlik derecesi sayısına bağlı olarak değişir df (İngilizlerden. özgürlük derecesi) temsil ettiği numunenin. Numunenin serbestlik derecesi sayısı n - 1, Nerede N- örneklem büyüklüğü (Şekil 3).

Pirinç. 3. σ parametresi bilinmediğinde ortaya çıkan t dağılımlarının şekli, normal dağılımın şeklinden farklıdır.

Excel'in t-dağılımı için Student t-dağılımı olarak da adlandırılan iki işlevi vardır: STUDENT.DIST(), verilen t-değerinin solundaki eğrinin altındaki alanı ve sağdaki STUDENT.DIST.RT()'yi döndürür.

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 ile +1.0 aralığında değerler alabilir.

Nerede S x Ve Sy değişkenlerin standart sapmalarıdır X Ve Y, Sxy– kovaryans:

Bu formülde kovaryans, değişkenlerin standart sapmalarına bölünür. X Ve Y, böylece kovaryanstan birim ile ilgili ölçekleme etkilerini ortadan kaldırır. Excel, CORREL() işlevini kullanır. Bu işlevin adı, STDEV(), VARV() veya COVARIANCE() gibi işlevlerin adlarında kullanılan G ve C niteleyici öğelerini içermez. Örnek korelasyon katsayısı taraflı bir tahmin sağlasa da, sapmanın nedeni varyans veya standart sapma durumundakinden farklıdır.

Genel korelasyon katsayısının büyüklüğüne bağlı olarak (genellikle Yunan harfi ile gösterilir) ρ ), korelasyon katsayısı R azalan örneklem büyüklüğü ile artan yanlılığın etkisi ile yanlı bir tahmin verir. Bununla birlikte, bu önyargıyı, örneğin standart sapmayı hesaplarken yaptığımız gibi, karşılık gelen formüle gözlem sayısını değil, serbestlik derecesi sayısını koyduğumuz 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ı, doğrusal bir ilişki ile birbiriyle ilişkili değişkenlerle kullanılmak üzere tasarlanmıştır. Verilerde (aykırı değerler) doğrusal olmama ve / veya hataların varlığı, korelasyon katsayısının yanlış hesaplanmasına yol açar. Veri sorunlarını teşhis etmek için dağılım grafikleri önerilir. Bu, Excel'de hem yatay hem de dikey eksenleri değer eksenleri olarak ele alan tek grafik türüdür. Çizgi grafiği ise sütunlardan birini kategori ekseni olarak tanımlar ve bu da verilerin resmini bozar (Şekil 4).

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

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

Korelasyon genellikle nedensel bir ilişki olduğu anlamına gelse de, bunun kanıtı olarak kullanılamaz. İstatistikler, bir teorinin doğru veya yanlış olduğunu göstermek için kullanılmaz. Gözlemlerin sonuçlarının birbiriyle çelişen açıklamalarını hariç tutmak için planlanmış deneyler. İstatistikler ayrıca, bu tür deneyler sırasında toplanan bilgileri özetlemek ve kanıtlar temelinde verilen 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 örneğin 0,5'ten büyükse, o zaman bir değişkenin bilinmeyen değerini diğerinin bilinen değerinden (biraz doğrulukla) tahmin etmek mümkündür. Şekil l'de verilen verilere dayanarak fiyatın tahmin edilen değerlerini elde etmek için. 5, birkaç olası yoldan herhangi birini kullanabilirsiniz, ancak şekil 2'de gösterileni neredeyse kesinlikle kullanmayacaksınız. 5. Yine de okumalısınız, çünkü başka hiçbir yol korelasyon ve tahmin arasındaki ilişkiyi bu kadar açık bir şekilde gösteremez. Şek. 5, B2:C12 aralığında, on evden oluşan rastgele bir örnektir ve her evin alanı (fit kare olarak) ve satış fiyatı hakkında veri sağlar.

Pirinç. 5. Satış fiyatı tahminleri düz bir çizgi oluşturur

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

Öngörülen değerin her zaman ortalamaya, yani 0'a 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 ve tahmin fiyatı 1,0*0,67'dir, yani. 0.67. Bu, standart sapmanın üçte ikisine eşit, ortalama değerin üzerindeki değerin fazlasına karşılık gelir. Korelasyon katsayısı 0,5'e eşit olsaydı, o zaman tahmin fiyatı 1,0 * 0,5 olurdu, yani. 0,5. Bu, standart sapmanın sadece yarısına eşit, ortalama değerin üzerinde bir değer fazlalığına karşılık gelir. Korelasyon katsayısının değeri idealden farklı olduğunda, yani. -1.0'dan büyük ve 1.0'dan küçükse, yordayıcı değişkenin tahmini, yordayıcı (bağımsız) değişkenin tahmininden kendi ortalama değerine daha yakın olmalıdır. Bu fenomene ortalamaya gerileme veya basitçe gerileme denir.

Regresyon çizgisi denkleminin katsayılarını belirlemek için Excel'de çeşitli işlevler vardır (Excel'de buna eğilim çizgisi denir) y=kx + B. belirlemek için k işleve hizmet eder

=EĞİM(bilinen_y-değerleri; bilinen_x-değerleri)

Burada de tahmin edilen değişkendir ve X bağımsız bir değişkendir. Bu değişken sırasını kesinlikle takip etmelisiniz. Regresyon çizgisinin eğimi, korelasyon katsayısı, değişkenlerin standart sapmaları ve kovaryans yakından ilişkilidir (Şekil 6). INTERCEPT() işlevi, dikey eksende regresyon çizgisi tarafından kesilen değeri döndürür:

= KESME(bilinen_y-değerleri; bilinen_x-değerleri)

Pirinç. 6. Standart sapmalar arasındaki oran, 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 - R 2 (R-kare) veya belirleme katsayısı. arasındaki ilişkinin genel veri değişkenliğine ne gibi bir katkı yaptığını belirler. X Ve de. Excel, CORREL() işleviyle tam olarak aynı bağımsız değişkenleri alan QVPIRSON() işlevine sahiptir.

Aralarında sıfır olmayan bir korelasyon katsayısı olan 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. Kalan %19'luk kısım rastgele dalgalanmalardan kaynaklanmaktadır.

Excel, hesaplamaları basitleştiren bir TREND işlevine sahiptir. TREND() işlevi:

verdiğiniz bilinen değerleri alır X ve bilinen değerler de;
regresyon doğrusunun eğimini ve sabiti (bölüm) hesaplar;
tahmini değerleri döndürür de regresyon denkleminin bilinen değerlere uygulanmasıyla belirlenir X(Şek. 7).

TREND() işlevi bir dizi işlevidir (bu tür işlevlerle daha önce karşılaşmadıysanız tavsiye ederim).

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

TREND() işlevini G3:G12 hücrelerinde 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 o zaman tuşuna basın . Formülün kaşlı ayraçlar içine alındığını unutmayın: ( ve ). Excel size bu formülün bir dizi formülü olarak algılandığını bu şekilde söyler. Köşeli parantezleri kendiniz girmeyin: bunları bir formülün parçası olarak kendiniz girmeye çalışırsanız, Excel girişinizi normal bir metin dizesi olarak kabul eder.

TREND() işlevinin iki bağımsız değişkeni daha vardır: yeni_değerler_x Ve sabit. İlki, gelecek için bir tahmin oluşturmanıza izin verir ve ikincisi, regresyon çizgisini orijinden geçmeye zorlayabilir (DOĞRU değer, Excel'e hesaplanan sabiti, YANLIŞ değer - sabit = 0) kullanmasını söyler. Excel, orijinden geçmesi için bir grafik üzerinde 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 bağlam menüsünde öğ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üsünün etkisini hariç tutarak ikisi arasındaki korelasyonu belirlemek istiyorsanız, kullanabilirsiniz. kısmi korelasyon. Üniversiteyi bitiren şehir sakinlerinin yüzdesi ile şehir kütüphanelerindeki kitapların sayısı arasındaki ilişkiyle ilgilendiğinizi varsayalım. 50 şehir için veri topladınız, ancak... Sorun şu ki, bu parametrelerin her ikisi de belirli bir şehrin sakinlerinin refahına bağlı olabilir. Tabii ki, sakinlerinin tam olarak aynı refah düzeyine sahip başka 50 şehir bulmak çok zor.

Refahın hem kütüphane desteği hem de üniversite eğitimi üzerindeki etkisini ortadan kaldırmak için istatistiksel yöntemler uygulayarak, ilgilendiğiniz değişkenler arasındaki ilişkiyi, yani kitap sayısı ve mezun sayısı arasındaki ilişkiyi daha iyi ölçebilirsiniz. Diğer değişkenlerin değerleri sabitken iki değişken arasındaki bu koşullu korelasyona kısmi korelasyon denir. Bunu hesaplamanın bir yolu, denklemi kullanmaktır:

Nerede RCB . W- Zenginlik (Zenginlik) değişkeninin hariç tutulan etkisi (sabit değer) ile Kolej (Kolej) ve Kitaplar (Kitaplar) değişkenleri arasındaki korelasyon katsayısı; RCB- Kolej ve Kitap değişkenleri arasındaki korelasyon katsayısı; RCW- Kolej ve Refah değişkenleri arasındaki korelasyon katsayısı; Rbw- Kitaplar ve Refah değişkenleri arasındaki korelasyon katsayısı.

Öte yandan, kısmi korelasyon artık analize dayalı olarak hesaplanabilir, yani tahmin edilen değerler ile bunlarla ilişkili gerçek gözlemler arasındaki farklar (her iki yöntem de Şekil 8'de gösterilmektedir).

Pirinç. 8. Kalan Korelasyon Olarak Kısmi Korelasyon

Korelasyon katsayıları matrisinin (B16: E19) hesaplanmasını basitleştirmek için Excel analiz paketini (menü) kullanın Veri –> Analiz –> Veri analizi). Varsayılan olarak, bu paket Excel'de etkin değildir. Yüklemek için menüden gidin Dosya –> Seçenekler –> eklentiler. Açılan pencerenin alt kısmında Seçeneklermükemmel alanı bul Kontrol, seçme eklentilermükemmel, tıklamak Gitmek. Eklentinin yanındaki kutuyu işaretleyin Analiz paketi. A'yı tıklayın veri analizi, bir seçenek 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 belirtin.

Başka bir olasılık, yarı-kısmi bir korelasyon tanımlamaktır. Örneğin, boy ve yaşın kilo üzerindeki etkisini araştırıyorsunuz. Böylece, boy ve yaş olmak üzere iki öngörücü değişkeniniz ve bir yordayıcı değişkeniniz, kilonuz var. Bir yordayıcı değişkenin diğeri üzerindeki etkisini hariç tutmak istiyorsunuz, ancak yordayıcı değişken üzerinde değil:

burada H - Boy (Boy), W - Ağırlık (Kilo), A - Yaş (Yaş); Yarı-kısmi korelasyon katsayısı indeksi, hangi değişkenin hangi değişkenden elendiğini 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 önemli olmadığı izlenimi edinilebilir. 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öreli katkıları sorunu ise ikincil görünmektedir. Ancak durum böyle değil. Çoklu regresyon denkleminde herhangi bir değişken kullanıp kullanmamayı düşünmeye başladığınız anda 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 analizi istatistiğini 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 tuşuna basın. (Şek. 9):

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

Pirinç. 9. DOT() 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

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

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

Bu istatistiklerin her birine ve etkileşimlerine bakalım.

standart hata bizim durumumuzda bu, örnekleme hataları için hesaplanan standart sapmadır. Yani bu, genel popülasyonun bir istatistiğe sahip olduğu ve örneklemin 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ır ise, tahmin edilen değişkenin en iyi tahmini ortalamasıdır. İki buçuk standart hata oldukça büyük bir sayıdır ve popülasyon için regresyon katsayısının sıfır olmayan bir değere sahip olduğunu güvenle varsayabilirsiniz.

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

ÖĞRENCİ.DAĞ.PH (t-testi = 2.559; serbestlik derecesi sayısı = 18)

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

Bu formül, 0,00987 veya %1'e yuvarlanmış bir değer döndürür. Bize şunu söylüyor: eğer popülasyon için regresyon katsayısı %0 ise, o zaman regresyon katsayısının hesaplanan değeri 2.092 olan 20 kişilik bir örneklem elde etme olasılığı mütevazi bir %1'dir.

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

Şimdiye kadar aralık değişkenlerini ele aldık. Erkek ve Kadın veya Sürüngen, Amfibi ve Balık gibi basit isimler olan birden fazla değer alabilen değişkenleriniz varsa, bunları sayısal bir 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ı.Şek. Tablo 4.9, Boy değişkeni ile ilişkisi temelinde elde edilen Ağırlık değişkeninin tahmin edilen değerlerini göstermektedir. E2:E21 aralığı, Ağırlık değişkeni için artıkların değerlerini içerir. Daha kesin olarak, bu artıklara hatalar denir - dolayısıyla tahminin standart hatası terimi takip eder.

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 denklemdeki herhangi bir tahminin gerçek gözlemle eşleşmesini o kadar yakın beklersiniz. Tahminin standart hatası, bu beklentileri ölçmek için bir yol sağlar. Belirli bir yüksekliğe 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 gruplar arası varyansın grup içi varyansa oranıdır. Bu isim, 20. yüzyılın başında varyans analizini (ANOVA, Analysis of Variance) 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. Değer (1 - R 2), artıklarla ilişkili toplam kareler toplamının oranını ifade eder - tahmin hataları. F testi, DOT işlevi (Şekil 11'deki F5 hücresi), kareler toplamı (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ılırken yapay kodlama kullanılır (Şekil 12). Değerleri kodlamak için 0 ve 1 değerlerini kullanmak uygundur. F olasılığı, işlev 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ğer ile aynıdır.

Pirinç. 12. Kukla değişkenler kullanılarak yapılan regresyon analizi

Pirinç. 13. λ = 0 için 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 değişkeni değerlerini, iki tahmin edici için A ve B sütunları veya üç tahmin edici için 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şken olduğu kadar çok sütun ve ayrıca 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ğu düşünülen düşük yoğunluklu lipoproteinler olan LDL seviyeleri üzerindeki yaş ve diyetin olası etkisini incelemek istiyorsunuz (Şekil 14).

Pirinç. 14. Çoklu Regresyon

Çoklu regresyonun (F13 hücresinde gösterilen) R2'si, herhangi bir basit regresyonun (E4, H4) R2'sinden büyüktür. Çoklu regresyon, aynı anda birden fazla öngörücü değişken kullanır. Bu durumda, R2 hemen hemen her zaman artar.

Bir tahmin değişkeni olan herhangi bir basit doğrusal regresyon denklemi için, tahmin edici değerler ile tahmin edici değişken değerleri arasında her zaman mükemmel bir korelasyon olacaktır, çünkü böyle bir denklemde tahmin değerleri bir sabitle çarpılır ve başka bir sabit eklenir. her ürüne Bu etki çoklu regresyonda korunmaz.

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

Pirinç. 15. Çalışma yaprağında katsayılar ve standart hataları ters sırayla gösterilir.

Tek bir yordayıcı değişkenle yapılan regresyon analizinde kullanılan ilke ve prosedürler, çoklu yordayıcı değişkenleri hesaba katmak için kolayca uyarlanır. Görünüşe göre, bu uyarlamanın çoğu, yordayıcı değişkenlerin birbiri üzerindeki etkisinin ortadan kaldırılmasına bağlıdır. İkincisi, özel ve yarı özel korelasyonlarla ilişkilidir (Şekil 16).

Pirinç. 16. Çoklu regresyon artıkların ikili regresyonu ile ifade edilebilir (Excel dosyasındaki formüllere bakın)

Excel'de t ve F dağılımları hakkında bilgi sağlayan işlevler vardır. STUDENT.DAĞ() ve F.DAĞ() gibi adları bir DIST parçası içeren işlevler, bağımsız değişken olarak bir t- veya F-testi alır ve belirtilen değeri gözlemleme olasılığını döndürür. STUDENT.TERS() ve F.TERS() gibi adları bir OBR parçası içeren işlevler, bağımsız değişken olarak bir olasılık değeri alır ve belirtilen olasılığa karşılık gelen bir ölçüt değeri döndürür.

Kuyruk bölgelerinin kenarlarını kesen t dağılımının kritik değerlerini aradığımızdan, bu olasılığa karşılık gelen bir değer döndüren STUDENT.INV() işlevlerinden birine argüman olarak %5 geçiyoruz. (Şek. 17, 18).

Pirinç. 17. İki kuyruklu t-testi

Pirinç. 18. Tek kuyruklu t-testi

Tek kuyruklu bir alfa bölgesi durumunda bir karar kuralı oluşturarak, testin istatistiksel gücünü artırırsınız. Deneyinize başladığınızda, pozitif (veya negatif) bir regresyon katsayısı beklemek için her türlü nedeninizin olduğundan eminseniz, o zaman tek uçlu bir test 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 terimi kullanmayı tercih ediyor yönlendirilmiş test terim yerine tek kuyruk testi ve terim yönlendirilmemiş test terim yerine iki kuyruklu test. Yönlü ve yönsüz terimleri dağılımın kuyruklarının doğasından ziyade hipotez tipini vurguladıkları için tercih edilir.

Modellerin karşılaştırılmasına dayalı olarak öngörücülerin etkisini değerlendirmeye yönelik bir yaklaşım.Şek. Şekil 19, Diet değişkeninin regresyon denklemine katkısını test eden bir regresyon analizinin sonuçlarını göstermektedir.

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

LINEST() sonuçları (H2:K6 aralığı), Diyet, Yaş ve HDL'deki LDL değişkenini gerileyen tam model dediğim şeyle ilişkilidir. H9:J13 aralığında, tahmin değişkeni Diyet dikkate alınmadan hesaplamalar sunulur. Ben buna sınırlı model diyorum. Tam modelde, LDL bağımlı değişkenindeki varyansın %49,2'si yordayıcı değişkenler tarafından açıklanmaktadır. Sınırlı modelde, LDL'nin yalnızca %30,8'i Yaş ve HDL ile açıklanmaktadır. Diyet değişkeninin modelden çıkarılmasından kaynaklanan R 2 kaybı 0,183'tür. G15:L17 aralığında, Diyet değişkeninin etkisinin yalnızca 0,0288 olasılıkla rastgele olduğunu gösteren hesaplamalar yapılmıştır. Kalan %97,1'de Diyetin LDL üzerinde etkisi vardır.

Bölüm 6. Regresyon analizine ilişkin varsayımlar ve uyarılar

"Varsayım" terimi kesin olarak tanımlanmamıştır ve kullanım şekli, varsayım karşılanmazsa tüm analizin sonuçlarının en azından sorgulanabilir veya muhtemelen geçersiz olduğunu düşündürür. Aslında, varsayımın ihlalinin resmi temelden değiştirdiği durumlar olsa da, durum böyle değildir. Ana varsayımlar şunlardır: a) Y değişkeninin artıkları, regresyon çizgisi boyunca X'in herhangi bir noktasında normal olarak dağılır; b) Y değerleri, X değerlerine doğrusal olarak bağlıdır; c) kalıntıların varyansı her bir X noktasında yaklaşık olarak aynıdır; d) kalıntılar arasında ilişki yoktur.

Varsayımlar önemli bir rol oynamıyorsa, istatistikçiler varsayımın ihlaliyle ilgili olarak analizin sağlamlığından bahseder. Ö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 olarak dağıldığı varsayımı önemli değildir: testler, normallik varsayımının ihlaline karşı dayanıklıdır. Grafikleri kullanarak verileri analiz etmek önemlidir. Örneğin, eklentiye dahil Veri analizi alet gerileme.

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

Pirinç. 20. Lojistik regresyon

Şek. Şekil 6.8, yıllık gelir ile bir 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ı, artan gelirle birlikte satın alma olasılığı artacaktır. Grafikler, doğrusal regresyon kullanarak bir 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 etmeyi kolaylaştırır.

İstatistiksel tabirle, gerçekte doğru olduğu halde boş hipotezin reddedilmesi Tip I hata olarak adlandırılır.

eklentide Veri analizi kullanıcının istenen dağılım şeklini (örneğin, Normal, Binom veya Poisson) ve ayrıca ortalama ve standart sapmayı belirlemesine olanak tanıyan kullanışlı bir rasgele sayı üretimi aracı sunulur.

STUDENT.DIST() ailesinin işlevleri arasındaki farklar. Excel 2010'dan başlayarak, belirli bir t-testi değerinin soluna ve/veya sağına bir dağılımın kesirini döndüren üç farklı fonksiyon biçimi mevcuttur. STUDENT.DAĞ() işlevi, belirttiğiniz t-testi değerinin solundaki dağılım eğrisi 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. 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Ğ() için üçüncü bağımsız değişken DOĞRU veya YANLIŞ olabilir. DOĞRU olarak ayarlanırsa işlev, verilen t-testinin solundaki eğrinin altındaki kümülatif alanı kesir olarak ifade ederek döndürür. YANLIŞ ise işlev, t-testine karşılık gelen noktada eğrinin göreli yüksekliğini döndürür. STUDENT.DAĞ() işlevinin diğer sürümleri - STUDENT.DIST.PX() 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 bağımsız değişken gerektirmez .

Pirinç. 21. Dağılımın sol kuyruğundaki daha koyu gölgeli alan, büyük 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 aşağıdaki formüllerden birini kullanın:

1 - ÖĞRENCİ.DAĞ (1, 69; 34; DOĞRU)

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

Eğrinin altındaki toplam alan %100 olmalıdır, bu nedenle fonksiyon tarafından döndürülen t-testi değerinin solundaki alan kesrinin 1'den çıkarılması, t-testi değerinin sağındaki alan kesrini verir. STUDENT.DIST.RH() işlevini kullanarak ilgilendiğiniz alan kesirini doğrudan elde etmeyi daha tercih edilebilir bulabilirsiniz; burada RH, dağılımın sağ kuyruğu anlamına gelir (Şekil 22).

Pirinç. 22. Yön testi için %5 alfa alanı

STUDENT.DIST() veya STUDENT.DIST.PH() işlevlerinin kullanılması, yönlendirilmiş bir çalışma hipotezi seçtiğiniz anlamına gelir. Yönlü çalışma hipotezi, alfa değerini %5'e ayarlamakla birleştiğinde, %5'in tamamını dağılımların sağ kuyruğuna koyduğunuz anlamına gelir. Sıfır hipotezini yalnızca t-testi değerinizin elde edilme olasılığı %5 veya daha azsa reddetmeniz gerekecektir. Yönlü hipotezler genellikle daha hassas istatistiksel testlerle sonuçlanır (bu daha yüksek hassasiyet aynı zamanda daha büyük istatistiksel güç olarak da adlandırılır).

Yönsüz bir test ile alfa değeri aynı %5 düzeyinde kalır, ancak dağılım farklı olacaktır. İki sonuca izin vermeniz gerektiğinden, yanlış pozitif olasılığı dağılımın iki kuyruğu arasında dağıtılmalıdır. Bu olasılığı eşit olarak dağıtmak genellikle kabul edilir (Şekil 23).

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

ÖĞRENCİ MESAFESİ 2X(1.69;34)

Belirli bir neden olmaksızın, STUDENT.DIST.2X() işlevi, ilk bağımsız değişkeni olarak negatif bir t-testi değeri verilirse #SAYI!

Örnekler farklı sayıda veri içeriyorsa, pakette bulunan farklı varyanslarla iki örnekli t-testini kullanın Veri analizi.

Bölüm 7 Grup Ortalamaları Arasındaki Farkları Test Etmek İçin Regresyonu Kullanma

Önceden yordayıcı değişkenler olarak adlandırılan değişkenler, bu bölümde sonuç değişkenleri olarak anı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: sahte kodlama(Şek. 24).

Pirinç. 24. Kukla kodlamaya dayalı regresyon analizi

Herhangi bir tür sahte kodlama kullanırken, aşağıdaki kurallara uyulmalıdır:

Yeni veriler için ayrılan sütun sayısı, eksi faktör düzeyleri sayısına eşit olmalıdır
Her vektör bir faktör seviyesini temsil eder.
Genellikle kontrol grubu olan bir seviyedeki denekler, tüm vektörlerde 0 kodunu alırlar.

F2:H6 =DOT(A2:A22;C2:D22;;DOĞRU) hücrelerindeki formül, regresyon istatistiklerini döndürür. Karşılaştırma için, Şek. Şekil 24, araç tarafından döndürülen geleneksel varyans analizinin sonuçlarını gösterir. Tek yönlü varyans analiziüst yapılar Veri analizi.

Efekt kodlaması. Adı verilen başka bir kodlama türünde efekt kodlama, her grubun ortalaması, grup ortalamalarının ortalaması ile karşılaştırılır. Etki kodlamasını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 Kodlama

Kukla 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 eşittir (J2 hücresi).

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ınlanan 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ülebileceğidir: genel ortalama, μ; işleme etkisi j ve j ; bireysel nicel gösterge Yij'nin genel ortalamanın birleşik değerinden ve j'inci tedavinin etkisinden sapmasını temsil eden eij değeri (Şekil 26). Regresyon denkleminin amacı, artıkların karelerinin toplamını en aza indirmektir.

Pirinç. 26. Genel Doğrusal Modelin Bileşenlerine Ayrıştırılmış Gözlemler

Faktor analizi. Ortaya çıkan değişken ile aynı anda iki veya daha fazla faktör arasındaki ilişki araştırılıyorsa, bu durumda faktör analizinin kullanılmasından söz edilir. Tek yönlü varyans analizine bir veya daha fazla faktör eklemek, istatistiksel gücü artırabilir. Tek yönlü ANOVA'da, sonuç değişkenindeki bir faktöre atfedilemeyen varyasyon, artık ortalama kareye dahil edilir. Ancak bu varyasyonun başka bir faktörle ilgili olması da pekala mümkündür. Daha sonra bu varyasyon, azalması F testinin değerlerinde bir artışa ve dolayısıyla testin istatistiksel gücünde bir artışa yol açan ortalama karesel hatadan çıkarılabilir. üst yapı Veri analizi iki faktörün aynı anda işlenmesini sağlayan bir araç içerir (Şekil 27).

Pirinç. 27. Tekrarlar Analiz Paketi ile Araç İki Yönlü Varyans Analizi

Bu şekilde kullanılan varyans analizi aracı, sonuçta ortaya çıkan değişkenin ortalamasını ve varyansını ve ayrıca tasarıma dahil edilen her grup için sayaç değerini döndürmesi açısından yararlıdır. Masa varyans analizi ANOVA aracının tek yönlü sürümünün çıktısında olmayan iki parametreyi görüntüler. Varyasyon Kaynaklarına Dikkat Edin Örnek Ve sütunlar 27 ve 28. satırlarda. Değişkenlik kaynağı sütunlar cinsiyete atıfta bulunur. Varyasyon Kaynağı Örnek değerleri farklı satırları kaplayan herhangi bir değişkeni ifade eder. Şek. 27, CourseLech1 grubu için değerler 2-6 satırlarında, CourseLech2 grubu 7-11 satırlarında ve CourseLech3 grubu 12-16 satırlarındadır.

Ana nokta, hem Cinsiyet (E28 hücresinde Sütunlar olarak etiketlenmiştir) hem de Tedavinin (E27 hücresinde Örnek olarak etiketlenmiştir) ANOVA tablosuna varyasyon kaynakları olarak dahil edilmesidir. Erkeklerin ortalamaları kadınların ortalamalarından farklıdır ve bu bir varyasyon kaynağı oluşturur. Üç tedavinin ortalamaları da farklıdır - işte başka bir varyasyon kaynağı. Cinsiyet ve Muamele değişkenlerinin birleşik etkisine atıfta bulunan Etkileşim adlı üçüncü bir kaynak da vardır.

Bölüm 8

Kovaryans Analizi veya ANCOVA (Analiz of Covariation), yanlılığı azaltır ve istatistiksel gücü artırır. Regresyon denkleminin güvenilirliğini değerlendirmenin yollarından birinin F testleri olduğunu hatırlatmama izin verin:

F = MS Regresyon/MS Kalıntı

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

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

burada SS (Kareler Toplamı) karelerin toplamıdır ve df serbestlik derecesi sayısıdır. Bir regresyon denklemine kovaryans eklediğinizde, toplam kareler toplamının bir kısmı SS ResiduaI'e değil, SS Regression'a dahil edilir. Bu, SS Kalıntı 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 o kadar büyük olur ve ortalamalar arasında hiçbir fark olmadığına dair sıfır hipotezini reddetme olasılığınız o kadar artar. Sonuç olarak, ortaya çıkan değişkenin oynaklığını yeniden dağıtırsınız. ANOVA'da kovaryans dikkate alınmadığında değişkenlik hataya girer. Ancak ANCOVA'da, değişkenliğin daha önce hataya atfedilen kısmı ortak değişkene atanır ve SS Regresyonunun bir parçası olur.

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

Pirinç. 28. ANOVA analizi, regresyon denklemi kullanılarak elde edilen sonuçların güvenilir olmadığını gösteriyor

Çalışma, kas gücünü geliştiren fiziksel egzersiz ile beyin aktivitesini harekete geçiren bilişsel egzersizin (bulmaca bulmacaları) göreceli etkilerini karşılaştırıyor. Denekler, deneyin başında her iki grubun da aynı koşullarda olması için rastgele iki gruba ayrıldı. Üç ay sonra deneklerin bilişsel özellikleri ölçüldü. Bu ölçümlerin sonuçları sütun B'de gösterilmektedir.

A2:C21 aralığı, efekt kodlamasını kullanarak analiz gerçekleştirmek için LINEST() işlevine iletilen ilk verileri içerir. LINEST() işlevinin sonuçları, E2:F6 aralığında gösterilir; burada E2 hücresi, etki vektörüyle ilişkili regresyon katsayısını gösterir. E8 hücresi t-testi = 0,93 içerir ve E9 hücresi bu t-testinin güvenilirliğini test eder. E9 hücresindeki değer, popülasyonda grup ortalamaları eşitse, bu deneyde gözlemlenen grup ortalamaları arasındaki farkla karşılaşma olasılığının %36 olduğunu gösterir. Sadece birkaçı bu sonucun istatistiksel olarak anlamlı olduğunu düşünüyor.

Şek. Şekil 29, analize bir ortak değişken eklendiğinde ne olduğunu göstermektedir. Bu durumda, veri kümesine her deneğin yaşını ekledim. Ortak değişkeni kullanan regresyon denklemi için belirleme katsayısı R2 0,80'dir (F4 hücresi). Ortak değişkeni kullanmadan elde ettiğim ANOVA sonuçlarını yeniden ürettiğim F15:G19 aralığındaki R2 değeri yalnızca 0,05'tir (F17 hücresi). Bu nedenle, bir ortak değişken içeren bir regresyon denklemi, Bilişsel Puan değişkeninin değerlerini tek başına Etki vektörünü kullanmaktan çok daha doğru bir şekilde tahmin eder. ANCOVA için, F5 hücresinde görüntülenen F testi değerini rastgele elde etme olasılığı %0,01'den azdır.

Pirinç. 29. ANCOVA tamamen farklı bir resim getiriyor

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

Analiz sonucu önceliklendirme yapmanızı sağlar. Ve ana faktörlere dayanarak, öncelikli alanların gelişimini tahmin etmek, planlamak, yönetim kararları almak.

Gerileme olur:

doğrusal (y = a + bx);

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

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

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

hiperbolik (y = b/x + a);

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

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

Excel'de bir regresyon modeli oluşturma ve sonuçları yorumlama örneğini ele alalım. Doğrusal bir regresyon türü ele alalım.

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

Doğrusal regresyon modeli aşağıdaki forma sahiptir:

Y \u003d 0 + a 1 x 1 + ... + a k x k.

Burada a, regresyon katsayılarıdır, x, etkileyen değişkenlerdir ve k, faktörlerin sayısıdır.

Örneğimizde Y işten ayrılanların göstergesidir. Etkileyen faktör ücretlerdir (x).

Excel, bir doğrusal regresyon modelinin parametrelerini hesaplamak için kullanılabilecek yerleşik işlevlere sahiptir. Ancak Analysis ToolPak eklentisi bunu daha hızlı yapacaktır.

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

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

2. Aşağıda, açılır listenin altında, "Yönetim" alanında bir "Excel Eklentileri" yazısı olacaktır (orada değilse, sağdaki onay kutusuna tıklayın ve seçin). Ve bir Git düğmesi. Tıklamak.

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

Etkinleştirildiğinde, eklenti Veri sekmesi altında bulunacaktır.

Şimdi doğrudan regresyon analizi ile ilgileneceğiz.

1. Veri Analizi aracının menüsünü açın. "Gerileme"yi seçin.

2. Giriş değerlerini ve çıkış seçeneklerini (sonucun nerede gösterileceğini) seçmek için bir menü açılacaktır. Başlangıç verileri alanlarında, açıklanan parametrenin (Y) aralığını ve onu etkileyen faktörü (X) belirtiyoruz. Gerisi tamamlanabilir veya tamamlanmayabilir.

3. Tamam'ı tıklattıktan sonra, program hesaplamaları yeni bir sayfada görüntüleyecektir (mevcut sayfada görüntülenecek aralığı seçebilir veya çıktıyı yeni bir çalışma kitabına atayabilirsiniz).

Öncelikle R-kare ve katsayılarına dikkat ediyoruz.

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

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

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

İstatistikte oluşturulan modelin kalitesini tanımlayan göstergelerden biri de yaklaşık güvenilirlik değeri olarak da adlandırılan belirleme katsayısıdır (R^2). Tahmin doğruluğunun seviyesini belirlemek için kullanılabilir. Çeşitli Excel araçlarını kullanarak bu göstergeyi nasıl hesaplayabileceğinizi öğ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 kalite modeli;
0 - 0,5 - kalitesiz model.

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

Excel'in belirtilen değeri nasıl hesaplayacağı, regresyonun doğrusal olup olmamasına bağlıdır. İlk durumda, işlevi kullanabilirsiniz QVPIRSON 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ı

Her şeyden önce, doğrusal bir fonksiyon için 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 hesaplamak için ne yapılmalıdır? Excel'de de bu seçenek vardır. Alet ile yapılabilir "Gerileme" paketin bir parçası olan "Veri analizi".

Ancak bu aracı kullanmadan önce kendiniz etkinleştirmelisiniz. "Analiz Paketi" Excel'de varsayılan olarak devre dışıdır. Sekmeye taşınıyor "Dosya" ve ardından öğeyi gözden geçirin "Seçenekler".

Açılan pencerede bölüme gidin "Eklentiler" sol 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üğmesine tıklayın "Gitmek..." alanın sağında bulunur.

Eklenti penceresi açılır. Orta kısmında mevcut eklentilerin bir listesi var. Konumun yanındaki onay kutusunu ayarlayın "Analiz Paketi". Bunu butona tıklayarak takip eder 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üğmesine tıklayın "Veri analizi" ayarlar grubunda "Analiz".

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

Ardından araç penceresi açılır "Gerileme". İlk ayar grubu "Giriş verileri". Burada iki alanda, argüman ve fonksiyon değerlerinin bulunduğu aralıkların adreslerini belirtmeniz gerekir. İmleci alana getirin "Giriş aralığı Y" ve sayfadaki sütunun içeriğini seçin "Y". Dizinin adresi pencerede görüntülendikten sonra "Gerileme", imleci alana getirin "Giriş aralığı Y" ve aynı şekilde sütunun hücrelerini seçin "X".
parametreler hakkında "İşaret" Ve "Sabit Sıfır" kutuları işaretlemeyin. Onay kutusu parametrenin yanında ayarlanabilir "Güvenilirlik seviyesi" ve karşısındaki alanda karşılık gelen göstergenin istenen değerini belirtin (varsayılan olarak %95).

Grup içinde "Çıktı Seçenekleri" hesaplama sonucunun hangi alanda gösterileceğini belirtmeniz gerekir. Üç seçenek vardır:
- Geçerli sayfadaki alan;
- Başka bir sayfa;
- Başka bir kitap (yeni dosya).
Seçimimizi ilk seçenekte durduralım, böylece ilk veriler ve sonuç aynı çalışma sayfasına yerleştirilir. Anahtarı parametrenin yanına koyun "Çıkış Aralığı". İmleci bu öğenin yanındaki alana getirin. Sayfada, hesaplama sonuçları çıktı tablosunun sol üst hücresi olması amaçlanan boş bir öğeye sol tıklıyoruz. Bu öğenin adresi pencere alanında vurgulanmalıdır. "Gerileme".

Parametre grupları "Kalıntılar" Ve "Normal Olasılık" problemin çözümü için önemli olmadıkları için göz ardı edilirler. Bundan sonra düğmesine tıklayın TAMAM pencerenin sağ üst köşesinde bulunur "Gerileme".

Program önceden girilen verilere göre hesaplar ve sonucu belirtilen aralıkta görüntüler. Gördüğünüz gibi, bu araç, sayfadaki ç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 Meydanı". Bu durumda, seçilen modeli kaliteli bir model olarak nitelendiren 0,947664'e eşittir.

Yöntem 3: trend çizgisi için belirleme katsayısı

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

Bir önceki örnek için kullanılan fonksiyonun bağımsız değişkenler tablosuna ve değerlerine dayanan bir grafiğimiz var. Buna bir trend çizgisi oluşturalım. Grafiğin yerleştirildiği inşaat alanının herhangi bir yerine farenin sol tuşu ile tıklıyoruz. Bu durumda, şeritte ek bir dizi sekme görünür - "Grafiklerle çalışma". sekmeye git "Düzen". butona tıklayın "Trend Çizgisi" araç kutusunda bulunan "Analiz". Trend çizgisi türü seçimiyle bir menü görünür. Belirli bir göreve karşılık gelen türün seçimini durdururuz. Ö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 oluşturur.

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

Trendline Format penceresine gitmek için alternatif bir eylem gerçekleştirilebilir. Farenin sol tuşu ile üzerine tıklayarak trend çizgisini seçin. Sekmeye taşınıyor "Düzen". butona tıklayın "Trend Çizgisi" blokta "Analiz". Açılan listede, eylemler listesindeki en son öğeye tıklayın - "Ek Eğilim Çizgisi Seçenekleri...".

Yukarıdaki iki eylemden herhangi birinin ardından, ek ayarlar yapabileceğiniz bir biçim penceresi açılır. Özellikle, görevimizi gerçekleştirmek için öğenin yanındaki kutuyu işaretlemelisiniz. "Yaklaşım güveninin (R^2) değerini diyagrama koyun". Pencerenin en altında bulunur. Yani, bu şekilde inşaat alanındaki belirleme katsayısının görüntüsünü açıyoruz. O zaman düğmeye basmayı unutma "Kapalı" geçerli pencerenin alt kısmında

Yaklaşım güven değeri, yani belirleme katsayısının değeri, inşaat alanındaki sayfa üzerinde görüntülenecektir. Bu durumda, gördüğümüz gibi, bu değer 0,9242'ye eşittir ve bu da yaklaşımı kaliteli bir model olarak nitelendirir.

Kesinlikle tam olarak bu şekilde, diğer herhangi bir trend çizgisi türü için belirleme katsayısının görüntüsünü ayarlayabilirsiniz. Trend çizgisinin türünü, yukarıda gösterildiği gibi, şeritteki düğmeden veya bağlam menüsünden parametreler penceresine giderek değiştirebilirsiniz. Sonra zaten gruptaki pencerenin kendisinde "Bir trend çizgisi oluşturmak" başka bir türe geçebilirsiniz. Aynı zamanda, noktanın yakınında bunu kontrol etmeyi unutmayın. "Yaklaşım güveninin değerini diyagrama koyun" onay kutusu işaretlendi. Yukarıdaki adımları tamamladıktan sonra, düğmesine tıklayın "Kapalı" pencerenin sağ alt köşesinde.

Doğrusal tipte, trend çizgisi zaten 0,9477'lik bir yaklaşık güvenilirlik değerine sahiptir ve bu, bu modeli daha önce ele aldığımız üstel tip trend çizgisinden bile daha güvenilir olarak karakterize eder.

Böylece, farklı trend çizgisi türleri arasında geçiş yaparak ve bunların yaklaşım güvenilirlik değerlerini (belirleme katsayısı) karşılaştırarak, modeli sunulan grafiği en doğru şekilde tanımlayan değişken bulunabilir. En yüksek belirleme katsayısına sahip seçenek en güvenilir olacaktır. Buna dayanarak, en doğru tahmini oluşturabilirsiniz.
Örneğin, bizim durumumuz için, ikinci dereceden trend çizgisinin polinom tipinin en yüksek güvenilirliğe sahip olduğunu deneysel olarak tespit etmeyi başardık. Bu durumda belirleme katsayısı 1'e eşittir. Bu, belirtilen modelin kesinlikle güvenilir olduğunu gösterir, bu da hataların tamamen ortadan kaldırılması anlamına gelir.

Ancak aynı zamanda bu, bu tür bir trend çizgisinin başka bir grafik için de en güvenilir olacağı anlamına gelmez. Trend çizgisi türünün optimal seçimi, grafiğin oluşturulduğu işlevin 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.

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, belirli eylemlerin ayrıntılı açıklamalarını vererek işi büyük ölçüde kolaylaştıran Excel'de gerçekleştirilir. Bu nedenle, analiz araçlarından biri olan "Regresyon", en küçük kareler yöntemini kullanarak bir dizi gözlem için bir grafik seçmek için kullanılır. Programın bu aracının ne olduğunu ve kullanıcı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 yönerge bulunmaktadır.

Ana görevler ve gerileme 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, ürün çeşitliliği, fiyatlar ve mağazanın konumu gibi çeşitli faktörlerden etkilenir. Excel'de regresyonu kullanarak, mevcut satışların sonuçlarına göre bu faktörlerin her birinin etki derecesini belirleyebilir ve ardından elde edilen verileri başka bir ayın veya yakınlarda bulunan başka bir mağazanın satışlarını tahmin etmek için uygulayabilirsiniz.

Tipik olarak regresyon, bir grubun bağımlı veya içsel, diğerinin bağımsız veya dışsal olduğu iki değişken grubu arasındaki ilişkinin bağımlılıklarını ve gücünü ortaya koyan basit bir denklem olarak sunulur. Birbiriyle ilişkili göstergeler grubu varsa, bağımlı değişken Y muhakeme 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:

Anlamlı bağımsız değişkenlerin seçimi (Х1, Х2, …, Xk).
İşlev türünün seçilmesi.
Katsayılar için tahminlerin oluşturulması.
Güven aralıklarının ve regresyon fonksiyonlarının oluşturulması.
Hesaplanan tahminlerin ve oluşturulan regresyon denkleminin anlamlılığının 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:

Doğrusal, değişkenler arasında katı bir doğrusal ilişkiyi gösteren.
Doğrusal Olmayan—Kuvvetleri, kesirleri ve trigonometrik fonksiyonları içerebilen Denklemler.

Model oluşturma talimatı

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

Daha fazla hesaplama için, Y Değerlerini, X Değerlerini, Const ve istatistikleri belirterek "Linear()" işlevi kullanılmalıdır. Bundan sonra, "Trend" işlevini kullanarak regresyon çizgisindeki noktaları belirleyin - Y değerleri, X değerleri, Yeni değerler, Sabit. Verilen parametreleri kullanarak, problemin verilen koşullarına göre katsayıların bilinmeyen değerini hesaplayın.

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

Aşağıda, somut pratik örnekler kullanarak, iktisatçılar arasında çok popüler olan bu iki analizi ele alacağız. Birleştirildiğinde sonuç elde edilmesine de bir örnek vereceğiz.

Excel'de Regresyon Analizi

Analiz sonucu önceliklendirme yapmanızı sağlar. Ve ana faktörlere dayanarak, öncelikli alanların gelişimini tahmin etmek, planlamak, yönetim kararları almak.

Gerileme olur:

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

Excel'de bir regresyon modeli oluşturma ve sonuçları yorumlama örneğini ele alalım. Doğrusal bir regresyon türü ele alalım.

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

Doğrusal regresyon modeli aşağıdaki forma sahiptir:

Y \u003d 0 + a 1 x 1 + ... + a k x k.

Burada a, regresyon katsayılarıdır, x, etkileyen değişkenlerdir ve k, faktörlerin sayısıdır.

Örneğimizde Y işten ayrılanların göstergesidir. Etkileyen faktör ücretlerdir (x).

Excel, bir doğrusal regresyon modelinin parametrelerini hesaplamak için kullanılabilecek yerleşik işlevlere sahiptir. Ancak Analysis ToolPak eklentisi bunu daha hızlı yapacaktır.

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

Etkinleştirildiğinde, eklenti Veri sekmesi altında bulunacaktır.

Şimdi doğrudan regresyon analizi ile ilgileneceğiz.

Öncelikle R-kare ve katsayılarına dikkat ediyoruz.

Excel'de korelasyon analizi

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

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

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

Korelasyon katsayısını bulmak için Excel'i nasıl kullanacağınızı düşünün.

CORREL fonksiyonu eşleştirilmiş katsayıları bulmak için kullanılır.

Görev: Bir torna tezgahının çalışma süresi ile bakım maliyeti arasında bir ilişki olup olmadığını belirleyin.

İmleci herhangi bir hücreye getirin ve fx düğmesine basın.

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

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

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

Ortaya çıkan katsayılar korelasyon matrisinde görüntülenecektir. Bunun gibi:

korelasyon-regresyon analizi

Pratikte bu iki teknik sıklıkla birlikte kullanılmaktadır.

Örnek:

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