Analisis korelasi dalam Excel. Contoh menjalankan analisis korelasi

Analisis korelasi ialah kaedah penyelidikan yang biasa digunakan untuk menentukan tahap pergantungan nilai pertama pada nilai kedua. Hamparan mempunyai alat khas yang membolehkan anda melaksanakan jenis penyelidikan ini.

Intipati analisis korelasi

Ia adalah perlu untuk menentukan hubungan antara dua kuantiti yang berbeza. Dalam erti kata lain, ia mendedahkan ke arah mana (lebih kecil / lebih besar) nilai berubah bergantung pada perubahan pada detik.

Tujuan analisis korelasi

Kebergantungan diwujudkan apabila pengenalpastian pekali korelasi bermula. Kaedah ini berbeza daripada analisis regresi, kerana hanya terdapat satu penunjuk yang dikira menggunakan korelasi. Selang berubah dari +1 kepada -1. Jika ia positif, maka peningkatan dalam nilai pertama menyumbang kepada peningkatan dalam ke-2. Jika negatif, maka peningkatan dalam nilai 1 menyumbang kepada penurunan dalam nilai ke-2. Semakin tinggi pekali, semakin kuat satu nilai mempengaruhi yang kedua.

Penting! Pada pekali ke-0, tiada hubungan antara kuantiti.

Pengiraan pekali korelasi

Mari analisa pengiraan pada beberapa sampel. Sebagai contoh, terdapat data jadual, di mana perbelanjaan untuk promosi pengiklanan dan volum jualan diterangkan mengikut bulan dalam lajur berasingan. Berdasarkan jadual, kita akan mengetahui tahap pergantungan volum jualan terhadap wang yang dibelanjakan untuk promosi pengiklanan.

Kaedah 1: Menentukan Korelasi Melalui Wizard Fungsi

CORREL – fungsi yang membolehkan anda melaksanakan analisis korelasi. Borang am - CORREL(massiv1;massiv2). Arahan terperinci:

1. Ia adalah perlu untuk memilih sel di mana ia dirancang untuk memaparkan hasil pengiraan. Klik "Sisipkan Fungsi" yang terletak di sebelah kiri medan teks untuk memasukkan formula.

2. Wizard Fungsi dibuka. Di sini anda perlu mencari CORREL, klik padanya, kemudian pada "OK".

3. Tetingkap hujah terbuka. Dalam baris "Array1" anda mesti memasukkan koordinat selang 1 nilai. Dalam contoh ini, ini ialah lajur Nilai Jualan. Anda hanya perlu memilih semua sel yang ada dalam lajur ini. Begitu juga, anda perlu menambah koordinat lajur kedua pada baris "Array2". Dalam contoh kami, ini ialah lajur Kos Pengiklanan.

4. Selepas memasukkan semua julat, klik pada butang "OK".

Pekali telah dipaparkan dalam sel yang ditunjukkan pada permulaan tindakan kami. Keputusan yang diperolehi ialah 0,97. Penunjuk ini mencerminkan pergantungan tinggi nilai pertama pada yang kedua.

Kaedah 2: Kira Korelasi Menggunakan Analysis ToolPak

Terdapat kaedah lain untuk menentukan korelasi. Di sini salah satu fungsi yang terdapat dalam pakej analisis digunakan. Sebelum menggunakannya, anda perlu mengaktifkan alat tersebut. Arahan terperinci:

1. Pergi ke bahagian "Fail".

2. Tetingkap baharu akan dibuka, di mana anda perlu mengklik pada bahagian "Tetapan".
3. Klik pada "Add-ons".
4. Kami dapati elemen "Pengurusan" di bahagian bawah. Di sini anda perlu memilih "Excel Add-in" dari menu konteks dan klik "OK".

5. Tetingkap alat tambah khas telah dibuka. Letakkan tanda semak di sebelah elemen "Pakej Analisis". Kami klik "OK".
6. Pengaktifan berjaya. Sekarang mari pergi ke Data. Blok "Analisis" muncul, di mana anda perlu mengklik "Analisis Data".
7. Dalam tetingkap baharu yang muncul, pilih elemen "Korelasi" dan klik pada "OK".

8. Tetingkap tetapan analisis muncul pada skrin. Dalam baris "Selang input" adalah perlu untuk memasukkan julat semua lajur yang mengambil bahagian dalam analisis. Dalam contoh ini, ini ialah lajur "Nilai jualan" dan "Kos pengiklanan". Tetapan paparan output pada mulanya ditetapkan kepada Lembaran Kerja Baharu, yang bermaksud bahawa keputusan akan dipaparkan pada helaian yang berbeza. Secara pilihan, anda boleh menukar lokasi output hasil. Selepas membuat semua tetapan, klik pada "OK".

Markah akhir sudah keluar. Hasilnya adalah sama seperti dalam kaedah pertama - 0,97.

Definisi dan pengiraan pekali korelasi berbilang dalam MS Excel

Untuk mengenal pasti tahap pergantungan beberapa kuantiti, pelbagai pekali digunakan. Pada masa hadapan, keputusan diringkaskan dalam jadual berasingan, dipanggil matriks korelasi.

Panduan terperinci:

1. Dalam bahagian "Data", kami menemui blok "Analisis" yang sudah diketahui dan klik "Analisis Data".

2. Dalam tetingkap yang muncul, klik pada elemen "Korelasi" dan klik pada "OK".
3. Dalam baris "Selang input" kami memandu dalam selang untuk tiga atau lebih lajur jadual sumber. Julat boleh dimasukkan secara manual atau pilih sahaja dengan LMB, dan ia akan muncul secara automatik dalam baris yang dikehendaki. Dalam "Pengumpulan" pilih kaedah pengumpulan yang sesuai. Dalam "Parameter Output" menentukan lokasi di mana hasil korelasi akan dipaparkan. Kami klik "OK".

4. sedia! Matriks korelasi telah dibina.

Pasangkan Pekali Korelasi dalam Excel

Mari kita fikirkan cara melukis pekali korelasi pasangan dengan betul dalam hamparan Excel.

Pengiraan pekali korelasi pasangan dalam Excel

Sebagai contoh, anda mempunyai nilai x dan y.

X ialah pembolehubah bersandar dan y ialah bebas. Ia adalah perlu untuk mencari arah dan kekuatan hubungan antara penunjuk ini. Arahan langkah demi langkah:

1. Mari cari nilai purata menggunakan fungsi tersebut HATI.

2. Mari kita mengira setiap satu х и xavg, у и avg menggunakan operator «-».

3. Kami mendarabkan perbezaan yang dikira.

4. Kami mengira jumlah penunjuk dalam lajur ini. Pengangka ialah hasil yang ditemui.

5. Kirakan penyebut perbezaan itu х и x-purata, y и y-sederhana. Untuk melakukan ini, kami akan melakukan kuasa dua.

6. Menggunakan fungsi AUTOSUMMA, cari penunjuk dalam lajur yang terhasil. Kami melakukan pendaraban. Menggunakan fungsi ROOT kuasa dua hasilnya.

7. Kami mengira hasil bagi menggunakan nilai penyebut dan pengangka.

8. CORREL ialah fungsi bersepadu yang membolehkan anda menghalang pengiraan yang rumit. Kami pergi ke "Function Wizard", pilih CORREL dan tentukan tatasusunan penunjuk х и у. Kami membina graf yang memaparkan nilai yang diperoleh.

Matriks Pekali Korelasi Berpasangan dalam Excel

Mari kita analisa cara mengira pekali matriks berpasangan. Sebagai contoh, terdapat matriks empat pembolehubah.

Arahan langkah demi langkah:

1. Kami pergi ke "Analisis Data", yang terletak di blok "Analisis" tab "Data". Pilih Korelasi daripada senarai yang muncul.
2. Kami menetapkan semua tetapan yang diperlukan. "Selang input" - selang semua empat lajur. "Selang keluaran" - tempat kami mahu memaparkan jumlah keseluruhan. Kami klik pada butang "OK".
3. Matriks korelasi telah dibina di tempat yang dipilih. Setiap persilangan baris dan lajur ialah pekali korelasi. Nombor 1 dipaparkan apabila koordinat sepadan.

CORREL berfungsi untuk menentukan hubungan dan korelasi dalam Excel

CORREL – fungsi yang digunakan untuk mengira pekali korelasi antara 2 tatasusunan. Mari kita lihat empat contoh semua kebolehan fungsi ini.

Contoh penggunaan fungsi CORREL dalam Excel

Contoh pertama. Terdapat plat dengan maklumat tentang purata gaji pekerja syarikat selama sebelas tahun dan kadar pertukaran $. Adalah perlu untuk mengenal pasti hubungan antara dua kuantiti ini. Jadual kelihatan seperti ini:

Algoritma pengiraan kelihatan seperti ini:

Markah yang dipaparkan adalah hampir 1. Keputusan:

Penentuan pekali korelasi kesan tindakan ke atas keputusan

Contoh kedua. Dua pembida menghubungi dua agensi berbeza untuk mendapatkan bantuan dengan promosi lima belas hari. Setiap hari tinjauan sosial dijalankan, yang menentukan tahap sokongan untuk setiap pemohon. Mana-mana orang yang ditemuduga boleh memilih salah satu daripada dua pemohon atau menentang semua. Adalah perlu untuk menentukan sejauh mana setiap promosi pengiklanan mempengaruhi tahap sokongan untuk pemohon, syarikat mana yang lebih cekap.

Menggunakan formula di bawah, kami mengira pekali korelasi:

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

Results:

Daripada keputusan yang diperoleh, menjadi jelas bahawa tahap sokongan untuk pemohon pertama meningkat dengan setiap hari promosi pengiklanan, oleh itu, pekali korelasi menghampiri 1. Apabila pengiklanan dilancarkan, pemohon lain mempunyai sejumlah besar kepercayaan, dan untuk 1 hari ada trend positif. Kemudian tahap kepercayaan menurun dan pada hari kelima belas ia jatuh di bawah penunjuk awal. Skor rendah menunjukkan bahawa kenaikan pangkat telah memberi kesan negatif kepada sokongan. Jangan lupa bahawa faktor bersamaan lain yang tidak dipertimbangkan dalam bentuk jadual juga boleh menjejaskan penunjuk.

Analisis populariti kandungan melalui korelasi tontonan video dan siaran semula

Contoh ketiga. Seseorang untuk mempromosikan video mereka sendiri di pengehosan video YouTube menggunakan rangkaian sosial untuk mengiklankan saluran tersebut. Dia mendapati terdapat beberapa hubungan antara bilangan siaran semula dalam rangkaian sosial dan bilangan tontonan pada saluran tersebut. Adakah mungkin untuk meramalkan prestasi masa hadapan menggunakan alat hamparan? Adalah perlu untuk mengenal pasti kewajaran menggunakan persamaan regresi linear untuk meramalkan bilangan tontonan video bergantung pada bilangan siaran semula. Jadual dengan nilai:

Sekarang adalah perlu untuk menentukan kehadiran hubungan antara 2 penunjuk mengikut formula di bawah:

0,7;IF(CORREL(A3:A8;B3:B8)>0,7;”Hubungan langsung yang kuat”;”Hubungan songsang yang kuat”);”Hubungan lemah atau tiada”)' class='formula'>

Jika pekali yang terhasil lebih tinggi daripada 0,7, maka lebih sesuai untuk menggunakan fungsi regresi linear. Dalam contoh ini, kita lakukan:

Sekarang kita sedang membina graf:

Kami menggunakan persamaan ini untuk menentukan bilangan tontonan pada 200, 500 dan 1000 perkongsian: =9,2937*D4-206,12. Kami mendapat keputusan berikut:

fungsi PERKARA membolehkan anda menentukan bilangan tontonan pada masa ini, jika terdapat, sebagai contoh, dua ratus lima puluh siaran semula. Kami memohon: 0,7;PREDICTION(D7;B3:B8;A3:A8);”Nilai tidak berkaitan”)' class='formula'>. Kami mendapat keputusan berikut:

Ciri menggunakan fungsi CORREL dalam Excel

Fungsi ini mempunyai ciri-ciri berikut:

1. Sel kosong tidak diambil kira.
2. Sel yang mengandungi maklumat jenis Boolean dan Teks tidak diambil kira.
3. Penafian berganda “-” digunakan untuk mengambil kira nilai logik dalam bentuk nombor.
4. Bilangan sel dalam tatasusunan yang dikaji mesti sepadan, jika tidak, mesej #N/A akan dipaparkan.

Penilaian kepentingan statistik bagi pekali korelasi

Apabila menguji kepentingan pekali korelasi, hipotesis nol ialah penunjuk mempunyai nilai 0, manakala alternatif tidak. Formula berikut digunakan untuk pengesahan:

Kesimpulan

Analisis korelasi dalam hamparan ialah proses yang mudah dan automatik. Untuk melaksanakannya, anda hanya perlu mengetahui di mana alat yang diperlukan berada dan cara mengaktifkannya melalui tetapan program.