Excel-də korrelyasiya təhlili. Korrelyasiya təhlilinin aparılması nümunəsi

Korrelyasiya təhlili 1-ci qiymətin 2-cidən asılılıq səviyyəsini müəyyən etmək üçün istifadə olunan ümumi tədqiqat metodudur. Elektron cədvəldə bu tip tədqiqatları həyata keçirməyə imkan verən xüsusi alət var.

Korrelyasiya təhlilinin mahiyyəti

İki müxtəlif kəmiyyət arasındakı əlaqəni müəyyən etmək lazımdır. Başqa sözlə, ikincidəki dəyişikliklərdən asılı olaraq dəyərin hansı istiqamətdə (kiçik/böyük) dəyişdiyini ortaya qoyur.

Korrelyasiya təhlilinin məqsədi

Asılılıq korrelyasiya əmsalının müəyyənləşdirilməsinə başlandıqda qurulur. Bu metod reqressiya təhlilindən fərqlənir, çünki korrelyasiyadan istifadə etməklə hesablanan yalnız bir göstərici var. Aralıq +1 ilə -1 arasında dəyişir. Müsbətdirsə, birinci dəyərdəki artım 2-ci qiymətin artmasına kömək edir. Mənfi olarsa, 1-ci dəyərdə artım 2-ci dəyərdə azalmaya səbəb olur. Əmsal nə qədər yüksəkdirsə, bir dəyər ikinciyə bir o qədər güclü təsir göstərir.

Əhəmiyyətli! 0-cı əmsalda kəmiyyətlər arasında əlaqə yoxdur.

Korrelyasiya əmsalının hesablanması

Bir neçə nümunə üzrə hesablamanı təhlil edək. Məsələn, ayrı-ayrı sütunlarda reklamın təşviqi və satış həcminin aylarla təsvir edildiyi cədvəl məlumatları var. Cədvəl əsasında satış həcminin reklamın təşviqinə xərclənən puldan asılılığının səviyyəsini öyrənəcəyik.

Metod 1: Funksiya Sihirbazı vasitəsi ilə korrelyasiyanın müəyyən edilməsi

CORREL – korrelyasiya analizini həyata keçirməyə imkan verən funksiya. Ümumi forma - CORREL(massiv1;massiv2). Ətraflı təlimatlar:

1. Hesablamanın nəticəsinin göstərilməsi planlaşdırılan xananı seçmək lazımdır. Düsturu daxil etmək üçün mətn sahəsinin solunda yerləşən "Funksiya daxil et" düyməsini basın.

2. Funksiya Sihirbazı açılır. Burada tapmaq lazımdır CORREL, üzərinə klikləyin, sonra “OK” düyməsini basın.

3. Arqumentlər pəncərəsi açılır. “Array1” sətrində dəyərlərin 1-ci intervallarının koordinatlarını daxil etməlisiniz. Bu nümunədə bu, Satış Dəyəri sütunudur. Sadəcə bu sütunda olan bütün xanaları seçməlisiniz. Eynilə, ikinci sütunun koordinatlarını “Array2” xəttinə əlavə etməlisiniz. Bizim nümunəmizdə bu, Reklam Xərcləri sütunudur.

4. Bütün diapazonları daxil etdikdən sonra "OK" düyməsini basın.

Əmsal hərəkətlərimizin əvvəlində göstərilən xanada göstərildi. Alınan nəticə 0,97-dir. Bu göstərici birinci qiymətin ikincidən yüksək asılılığını əks etdirir.

Metod 2: Analiz alətlər paketindən istifadə edərək korrelyasiyanı hesablayın

Korrelyasiyanı təyin etmək üçün başqa bir üsul var. Burada analiz paketində tapılan funksiyalardan biri istifadə olunur. İstifadə etməzdən əvvəl aləti aktivləşdirməlisiniz. Ətraflı təlimat:

1. "Fayl" bölməsinə keçin.

2. Yeni bir pəncərə açılacaq, orada "Parametrlər" bölməsini vurmalısınız.
3. "Əlavələr" üzərinə klikləyin.
4. Aşağıda "İdarəetmə" elementini tapırıq. Burada kontekst menyusundan “Excel Əlavələri”ni seçmək və “OK” düyməsini sıxmaq lazımdır.

5. Xüsusi əlavələr pəncərəsi açıldı. "Analiz paketi" elementinin yanında bir işarə qoyun. "OK" düyməsini sıxırıq.
6. Aktivləşdirmə uğurlu oldu. İndi keçək Dataya. "Təhlil" bloku göründü, orada "Məlumatların təhlili" düyməsini sıxmalısınız.
7. Görünən yeni pəncərədə "Korrelyasiya" elementini seçin və "OK" düyməsini basın.

8. Ekranda təhlil parametrləri pəncərəsi göründü. "Giriş intervalı" sətrində təhlildə iştirak edən tamamilə bütün sütunların diapazonunu daxil etmək lazımdır. Bu nümunədə bunlar “Satış dəyəri” və “Reklam xərcləri” sütunlarıdır. Çıxış ekranı parametrləri əvvəlcə Yeni İş Vərəqinə təyin edilir, yəni nəticələr başqa vərəqdə göstəriləcək. İsteğe bağlı olaraq, nəticənin çıxış yerini dəyişə bilərsiniz. Bütün parametrləri etdikdən sonra "OK" düyməsini basın.

Yekun xallar çıxdı. Nəticə birinci üsuldakı kimidir - 0,97.

MS Excel-də çoxlu korrelyasiya əmsalının tərifi və hesablanması

Bir neçə kəmiyyətin asılılıq səviyyəsini müəyyən etmək üçün çoxlu əmsallardan istifadə olunur. Gələcəkdə nəticələr korrelyasiya matrisi adlanan ayrıca cədvəldə ümumiləşdirilir.

Ətraflı bələdçi:

1. “Məlumat” bölməsində biz artıq məlum olan “Analiz” blokunu tapırıq və “Məlumatların təhlili” düyməsini sıxırıq.

2. Görünən pəncərədə "Korrelyasiya" elementini vurun və "OK" düyməsini basın.
3. "Giriş intervalı" sətirində mənbə cədvəlinin üç və ya daha çox sütunu üçün intervalda hərəkət edirik. Aralığı əl ilə daxil etmək və ya sadəcə LMB ilə seçmək olar və o, avtomatik olaraq istədiyiniz sətirdə görünəcək. “Qruplaşdırma” bölməsində müvafiq qruplaşdırma metodunu seçin. “Çıxış Parametri”ndə korrelyasiya nəticələrinin göstəriləcəyi yeri müəyyən edir. "OK" düyməsini sıxırıq.

4. Hazır! Korrelyasiya matrisi quruldu.

Excel-də cüt korrelyasiya əmsalı

Excel elektron cədvəlində cüt korrelyasiya əmsalını necə düzgün çəkəcəyimizi anlayaq.

Excel-də cüt korrelyasiya əmsalının hesablanması

Məsələn, x və y dəyərləriniz var.

X asılı dəyişən, y isə müstəqildir. Bu göstəricilər arasında əlaqənin istiqamətini və gücünü tapmaq lazımdır. Addım-addım təlimat:

1. Funksiyadan istifadə edərək orta dəyərləri tapaq ÜRƏK.

2. Gəlin hər birini hesablayaq х и xavg, у и avg «-» operatorundan istifadə etməklə.

3. Hesablanmış fərqləri çoxaldırıq.

4. Bu sütundakı göstəricilərin cəmini hesablayırıq. Numerator tapılan nəticədir.

5. Fərqin məxrəclərini hesablayın х и x-orta, y и y-orta. Bunun üçün kvadratlaşdırmanı həyata keçirəcəyik.

6. Funksiyadan istifadə etməklə AVTOZUMMA, nəticədə çıxan sütunlarda göstəriciləri tapın. Biz vurma edirik. Funksiyadan istifadə etməklə KÖK nəticəni kvadrat edin.

7. Məxrəc və payın dəyərlərindən istifadə edərək hissəni hesablayırıq.

8. CORREL kompleks hesablamaların qarşısını almağa imkan verən inteqrasiya olunmuş funksiyadır. "Funksiya Sihirbazı"na gedirik, CORREL seçin və göstəricilərin massivlərini təyin edirik. х и у. Alınan dəyərləri əks etdirən bir qrafik qururuq.

Excel-də Cütlü Korrelyasiya Əmsallarının Matrisi

Qoşalaşmış matrislərin əmsallarının necə hesablanacağını təhlil edək. Məsələn, dörd dəyişəndən ibarət matris var.

Addım-addım təlimat:

1. "Məlumatlar" sekmesinin "Təhlil" blokunda yerləşən "Məlumatların Təhlili" na gedirik. Görünən siyahıdan Korrelyasiya seçin.
2. Bütün lazımi parametrləri təyin edirik. “Giriş intervalı” – bütün dörd sütunun intervalı. “Çıxış intervalı” – yekunları göstərmək istədiyimiz yer. "OK" düyməsini sıxırıq.
3. Seçilmiş yerdə korrelyasiya matrisi quruldu. Sətir və sütunun hər kəsişməsi korrelyasiya əmsalıdır. Koordinatlar uyğunlaşdıqda 1 rəqəmi göstərilir.

Excel-də əlaqə və korrelyasiya müəyyən etmək üçün CORREL funksiyası

CORREL – 2 massiv arasında korrelyasiya əmsalını hesablamaq üçün istifadə olunan funksiya. Bu funksiyanın bütün qabiliyyətlərinin dörd nümunəsinə baxaq.

Excel-də CORREL funksiyasından istifadə nümunələri

Birinci misal. Orada şirkət işçilərinin on bir il ərzində orta əmək haqqı və dolların məzənnəsi haqqında məlumat olan lövhə var. Bu iki kəmiyyət arasındakı əlaqəni müəyyən etmək lazımdır. Cədvəl belə görünür:

Hesablama alqoritmi belə görünür:

Göstərilən xal 1-ə yaxındır. Nəticə:

Hərəkətlərin nəticəyə təsirinin korrelyasiya əmsalının təyini

İkinci misal. İki iddiaçı on beş günlük təşviqatla bağlı kömək üçün iki fərqli quruma müraciət etdi. Hər gün hər bir müraciət edənə dəstək dərəcəsini müəyyən edən sosial sorğu keçirilirdi. İstənilən müsahibə verən iki ərizəçidən birini seçə və ya hamısına qarşı çıxa bilər. Hər bir reklam təşviqinin müraciət edənlərə dəstək dərəcəsinə nə qədər təsir etdiyini, hansı şirkətin daha səmərəli olduğunu müəyyən etmək lazımdır.

Aşağıdakı düsturlardan istifadə edərək korrelyasiya əmsalını hesablayırıq:

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

Nəticələr:

Əldə edilən nəticələrdən məlum olur ki, 1-ci ərizəçiyə dəstək dərəcəsi reklamın təşviqi ilə bağlı hər gün artır, buna görə də korrelyasiya əmsalı 1-ə yaxınlaşır. Reklam işə salındıqda digər ərizəçinin çoxlu etibarı var idi və 5 gün ərzində müsbət tendensiya var idi. Sonra etimad dərəcəsi azaldı və on beşinci günə qədər ilkin göstəricilərdən aşağı düşdü. Aşağı ballar, təşviqatın dəstəyə mənfi təsir etdiyini göstərir. Unutmayın ki, cədvəl şəklində nəzərə alınmayan digər əlaqəli amillər də göstəricilərə təsir göstərə bilər.

Video baxışlarının və repostların nisbəti ilə məzmunun populyarlığının təhlili

Üçüncü misal. YouTube video hostinqində öz videolarını tanıtmaq istəyən şəxs kanalı reklam etmək üçün sosial şəbəkələrdən istifadə edir. O qeyd edir ki, sosial şəbəkələrdə repostların sayı ilə kanala baxış sayı arasında müəyyən nisbət var. Cədvəl alətlərindən istifadə edərək gələcək performansı proqnozlaşdırmaq mümkündürmü? Repostların sayından asılı olaraq video baxışlarının sayını proqnozlaşdırmaq üçün xətti reqressiya tənliyinin tətbiqinin məqsədəuyğunluğunu müəyyən etmək lazımdır. Dəyərləri olan cədvəl:

İndi aşağıdakı düstura görə 2 göstərici arasında əlaqənin mövcudluğunu müəyyən etmək lazımdır:

0,7;IF(CORREL(A3:A8;B3:B8)>0,7;”Güclü birbaşa əlaqə”;”Güclü tərs əlaqə”);”Zəif və ya heç bir əlaqə”)' class='formula'>

Nəticə əmsalı 0,7-dən yüksək olarsa, o zaman xətti reqressiya funksiyasından istifadə etmək daha məqsədəuyğundur. Bu nümunədə biz:

İndi bir qrafik qururuq:

200, 500 və 1000 paylaşımda baxışların sayını müəyyən etmək üçün bu tənliyi tətbiq edirik: =9,2937*D4-206,12. Aşağıdakı nəticələri alırıq:

Function Proqnoz məsələn, iki yüz əlli repost varsa, bu anda baxışların sayını təyin etməyə imkan verir. Biz müraciət edirik: 0,7;PREDICTION(D7;B3:B8;A3:A8);”Qiymətlər əlaqəli deyil”)' class='formula'>. Aşağıdakı nəticələri alırıq:

Excel-də CORREL funksiyasından istifadə xüsusiyyətləri

Bu funksiya aşağıdakı xüsusiyyətlərə malikdir:

1. Boş xanalar nəzərə alınmır.
2. Boolean və Mətn tipli məlumatları olan xanalar nəzərə alınmır.
3. İkiqat inkar “-” rəqəmlər şəklində məntiqi dəyərləri hesablamaq üçün istifadə olunur.
4. Tədqiq olunan massivlərdə xanaların sayı uyğun olmalıdır, əks halda #N/A mesajı göstərilir.

Korrelyasiya əmsalının statistik əhəmiyyətinin qiymətləndirilməsi

Korrelyasiya əmsalının əhəmiyyətini yoxlayarkən sıfır fərziyyə ondan ibarətdir ki, göstərici 0 qiymətinə malikdir, alternativ isə yox. Doğrulama üçün aşağıdakı formula istifadə olunur:

Nəticə

Elektron cədvəldə korrelyasiya təhlili sadə və avtomatlaşdırılmış prosesdir. Bunu həyata keçirmək üçün yalnız lazımi vasitələrin harada yerləşdiyini və proqram parametrləri vasitəsilə onları necə aktivləşdirəcəyini bilmək lazımdır.