Excel'de korelasyon analizi. Korelasyon analizi gerçekleştirme örneği

Korelasyon analizi, 1. değerin 2. değere bağımlılık düzeyini belirlemek için kullanılan yaygın bir araştırma yöntemidir. Elektronik tablo, bu tür araştırmaları uygulamanıza izin veren özel bir araca sahiptir.

Korelasyon analizinin özü

İki farklı miktar arasındaki ilişkiyi belirlemek gerekir. Yani saniyedeki değişimlere bağlı olarak değerin hangi yönde (küçük/büyük) değiştiğini ortaya çıkarır.

Korelasyon analizinin amacı

Bağımlılık, korelasyon katsayısının tanımlanması başladığında kurulur. Bu yöntem, korelasyon kullanılarak hesaplanan tek bir gösterge olduğu için regresyon analizinden farklıdır. Aralık +1'den -1'e değişir. Pozitif ise, ilk değerdeki artış, 2. değerdeki artışa katkıda bulunur. Negatifse, 1. değerdeki bir artış, 2. değerdeki azalmaya katkıda bulunur. Katsayı ne kadar yüksek olursa, bir değer ikinciyi o kadar güçlü etkiler.

Önemli! 0. katsayıda nicelikler arasında bir ilişki yoktur.

Korelasyon katsayısının hesaplanması

Hesaplamayı birkaç örnek üzerinde analiz edelim. Örneğin, reklam promosyonu harcamalarının ve satış hacminin aylarla ayrı sütunlarda açıklandığı tablo verileri vardır. Tabloya dayanarak, satış hacminin reklam promosyonuna harcanan paraya bağımlılık düzeyini bulacağız.

Yöntem 1: İşlev Sihirbazı Aracılığıyla Korelasyonu Belirleme

CORREL – bir korelasyon analizi uygulamanıza izin veren bir fonksiyon. Genel form - KOREL(massiv1;massiv2). Detaylı talimatlar:

1. Hesaplamanın sonucunu göstermesinin planlandığı hücreyi seçmek gerekir. Formülü girmek için metin alanının solunda bulunan “İşlev Ekle”ye tıklayın.

2. İşlev Sihirbazı açılır. Burada bulmanız gerekiyor KORELüzerine tıklayın, ardından “Tamam”a tıklayın.

3. Argümanlar penceresi açılır. “Array1” satırına, değerlerin 1.'sinin aralıklarının koordinatlarını girmelisiniz. Bu örnekte, bu Satış Değeri sütunudur. Bu sütundaki tüm hücreleri seçmeniz yeterlidir. Benzer şekilde, ikinci sütunun koordinatlarını “Array2” satırına eklemeniz gerekir. Örneğimizde bu, Reklam Maliyetleri sütunudur.

4. Tüm aralıkları girdikten sonra “Tamam” düğmesine tıklayın.

Katsayı, eylemlerimizin başında belirtilen hücrede görüntülendi. Elde edilen sonuç 0,97'dir. Bu gösterge, birinci değerin ikinciye olan yüksek bağımlılığını yansıtır.

Yöntem 2: Analiz Araç Paketini Kullanarak Korelasyonu Hesaplayın

Korelasyonu belirlemek için başka bir yöntem var. Burada analiz paketinde bulunan fonksiyonlardan biri kullanılır. Kullanmadan önce aracı etkinleştirmeniz gerekir. Detaylı talimatlar:

1. "Dosya" bölümüne gidin.

2. “Ayarlar” bölümüne tıklamanız gereken yeni bir pencere açılacaktır.
3. "Eklentiler" üzerine tıklayın.
4. En altta “Yönetim” öğesini buluyoruz. Burada içerik menüsünden “Excel Eklentileri”ni seçip “Tamam”a tıklamanız gerekiyor.

5. Özel bir eklenti penceresi açıldı. "Analiz Paketi" öğesinin yanına bir onay işareti koyun. “Tamam”a tıklıyoruz.
6. Aktivasyon başarılı oldu. Şimdi Data'ya geçelim. “Veri Analizi” ni tıklamanız gereken “Analiz” bloğu belirdi.
7. Görünen yeni pencerede “Korelasyon” öğesini seçin ve “Tamam” a tıklayın.

8. Ekranda analiz ayarları penceresi belirdi. “Giriş aralığı” satırında, analizde yer alan tüm sütunların aralığını kesinlikle girmek gerekir. Bu örnekte, bunlar "Satış değeri" ve "Reklam maliyetleri" sütunlarıdır. Çıktı görüntüleme ayarları başlangıçta Yeni Çalışma Sayfası olarak ayarlanır; bu, sonuçların farklı bir sayfada görüntüleneceği anlamına gelir. İsteğe bağlı olarak, sonucun çıktı konumunu değiştirebilirsiniz. Tüm ayarları yaptıktan sonra “Tamam” a tıklayın.

Son puanlar çıktı. Sonuç, ilk yöntemdekiyle aynıdır – 0,97.

MS Excel'de çoklu korelasyon katsayısının tanımı ve hesaplanması

Birkaç niceliğin bağımlılık düzeyini belirlemek için çoklu katsayılar kullanılır. Gelecekte sonuçlar, korelasyon matrisi adı verilen ayrı bir tabloda özetlenecektir.

Ayrıntılı rehber:

1. “Veri” bölümünde zaten bilinen “Analiz” bloğunu buluyoruz ve “Veri Analizi”ne tıklıyoruz.

2. Görünen pencerede, “Korelasyon” öğesine tıklayın ve “Tamam” a tıklayın.
3. “Giriş aralığı” satırında, kaynak tablonun üç veya daha fazla sütunu için aralıkta ilerliyoruz. Aralık manuel olarak girilebilir veya LMB ile basitçe seçilebilir ve istenen satırda otomatik olarak görünecektir. “Gruplama”da uygun gruplama yöntemini seçin. “Çıkış Parametresi”nde korelasyon sonuçlarının görüntüleneceği yeri belirtir. “Tamam”a tıklıyoruz.

4. Hazır! Korelasyon matrisi oluşturuldu.

Excel'de Çift Korelasyon Katsayısı

Bir Excel elektronik tablosunda çift korelasyon katsayısının nasıl doğru bir şekilde çizileceğini bulalım.

Excel'de çift korelasyon katsayısının hesaplanması

Örneğin, x ve y değerleriniz var.

X bağımlı değişkendir ve y bağımsızdır. Bu göstergeler arasındaki ilişkinin yönünü ve gücünü bulmak gerekir. Adım adım talimat:

1. Fonksiyonu kullanarak ortalama değerleri bulalım KALP.

2. Her birini hesaplayalım х и xortalama, у и ort «-» operatörünü kullanarak.

3. Hesaplanan farkları çarpıyoruz.

4. Bu sütundaki göstergelerin toplamını hesaplıyoruz. Pay, bulunan sonuçtur.

5. Farkın paydalarını hesaplayın х и x-ortalama, y и y-orta. Bunun için kare alma işlemini gerçekleştireceğiz.

6. İşlevi kullanma OTOSUMMA, sonuç sütunlarındaki göstergeleri bulun. çarpma işlemi yapıyoruz. işlevi kullanma KÖK sonucun karesini alın.

7. Payda ve payın değerlerini kullanarak bölümü hesaplıyoruz.

8. CORREL, karmaşık hesaplamaları önlemenizi sağlayan entegre bir işlevdir. “İşlev Sihirbazına” gidiyoruz, CORREL'i seçiyoruz ve gösterge dizilerini belirliyoruz х и у. Elde edilen değerleri gösteren bir grafik oluşturuyoruz.

Excel'de İkili Korelasyon Katsayılarının Matrisi

Eşleştirilmiş matrislerin katsayılarının nasıl hesaplanacağını analiz edelim. Örneğin, dört değişkenli bir matris var.

Adım adım talimat:

1. “Veri” sekmesinin “Analiz” bloğunda bulunan “Veri Analizi” ne gidiyoruz. Görünen listeden Korelasyon'u seçin.
2. Gerekli tüm ayarları yapıyoruz. "Giriş aralığı" - dört sütunun tümünün aralığı. “Çıkış aralığı” – toplamları görüntülemek istediğimiz yer. “Tamam” butonuna tıklıyoruz.
3. Seçilen yerde bir korelasyon matrisi oluşturulmuştur. Bir satır ve bir sütunun her kesişimi bir korelasyon katsayısıdır. Koordinatlar eşleştiğinde 1 sayısı görüntülenir.

Excel'de ilişki ve korelasyonu belirlemek için CORREL işlevi

CORREL – 2 dizi arasındaki korelasyon katsayısını hesaplamak için kullanılan bir fonksiyon. Bu işlevin tüm yeteneklerinin dört örneğine bakalım.

Excel'de CORREL işlevini kullanma örnekleri

İlk örnek. Şirket çalışanlarının on bir yıl boyunca ortalama maaşları ve dolar döviz kuru hakkında bilgi içeren bir levha var. Bu iki nicelik arasındaki ilişkiyi belirlemek gerekir. Tablo şöyle görünüyor:

Hesaplama algoritması şöyle görünür:

Görüntülenen puan 1'e yakındır. Sonuç:

Eylemlerin sonuç üzerindeki etkisinin korelasyon katsayısının belirlenmesi

İkinci örnek. İki teklif sahibi, on beş günlük bir promosyon için yardım için iki farklı kuruma başvurdu. Her gün, her başvuru sahibi için destek derecesini belirleyen bir sosyal anket yapıldı. Herhangi bir görüşmeci, iki başvurandan birini seçebilir veya hepsine karşı çıkabilir. Hangi şirketin daha verimli olduğunu, her bir reklam promosyonunun başvuru sahipleri için destek derecesini ne kadar etkilediğini belirlemek gerekir.

Aşağıdaki formülleri kullanarak korelasyon katsayısını hesaplıyoruz:

• =KOREL(A3:A17;B3:B17).
• =KOREL(A3:A17;C3:C17).

Sonuçlar:

Elde edilen sonuçlardan, reklam tanıtımının her günü 1. başvurana verilen desteğin derecesinin arttığı, dolayısıyla korelasyon katsayısının 1'e yaklaştığı açıkça görülmektedir. Reklam yayınlandığında, diğer başvuranın çok sayıda güveni vardı ve 5 gün olumlu bir eğilim vardı. Daha sonra güven derecesi azaldı ve on beşinci günde ilk göstergelerin altına düştü. Düşük puanlar, terfinin desteği olumsuz etkilediğini göstermektedir. Tablo şeklinde ele alınmayan diğer eşlik eden faktörlerin de göstergeleri etkileyebileceğini unutmayın.

Video görüntülemeleri ve yeniden gönderiler arasındaki korelasyona göre içerik popülerliğinin analizi

Üçüncü örnek. YouTube video barındırmada kendi videolarını tanıtmak için bir kişi, kanalın reklamını yapmak için sosyal ağları kullanır. Sosyal ağlardaki paylaşım sayısı ile kanaldaki izlenme sayısı arasında bir ilişki olduğunu fark ediyor. Elektronik tablo araçlarını kullanarak gelecekteki performansı tahmin etmek mümkün müdür? Tekrar sayısına bağlı olarak video görüntüleme sayısını tahmin etmek için doğrusal regresyon denkleminin uygulanmasının makul olup olmadığını belirlemek gerekir. Değerleri içeren tablo:

Şimdi aşağıdaki formüle göre 2 gösterge arasında bir ilişkinin varlığını belirlemek gerekiyor:

0,7;IF(CORREL(A3:A8;B3:B8)>0,7;”Güçlü doğrudan ilişki”;”Güçlü ters ilişki”);”Zayıf veya ilişki yok”)' class='formula'>

Elde edilen katsayı 0,7'den büyükse, doğrusal regresyon fonksiyonunu kullanmak daha uygundur. Bu örnekte şunları yapıyoruz:

Şimdi bir grafik oluşturuyoruz:

200, 500 ve 1000 paylaşımdaki görüntüleme sayısını belirlemek için bu denklemi uygularız: =9,2937*D4-206,12. Aşağıdaki sonuçları alıyoruz:

işlev TAHMİNİ örneğin iki yüz elli gönderi varsa, şu anda görüntüleme sayısını belirlemenize olanak tanır. Başvuruyoruz: 0,7;PREDICTION(D7;B3:B8;A3:A8);”Değerler birbiriyle ilişkili değil”)' class='formula'>. Aşağıdaki sonuçları alıyoruz:

CORREL işlevini Excel'de kullanmanın özellikleri

Bu fonksiyon aşağıdaki özelliklere sahiptir:

1. Boş hücreler dikkate alınmaz.
2. Boole ve Metin türü bilgilerini içeren hücreler dikkate alınmaz.
3. Çift olumsuzlama “-”, mantıksal değerleri sayı biçiminde hesaba katmak için kullanılır.
4. İncelenen dizilerdeki hücre sayısı eşleşmelidir, aksi takdirde #N/A mesajı görüntülenecektir.

Korelasyon katsayısının istatistiksel öneminin değerlendirilmesi

Bir korelasyon katsayısının önemini test ederken, sıfır hipotezi, göstergenin 0 değerine sahip olduğu, ancak alternatifin olmadığıdır. Doğrulama için aşağıdaki formül kullanılır:

Sonuç

Bir elektronik tablodaki korelasyon analizi, basit ve otomatik bir işlemdir. Bunu gerçekleştirmek için sadece gerekli araçların nerede olduğunu ve program ayarları aracılığıyla nasıl etkinleştirileceğini bilmeniz gerekir.