Analisis korelasi di Excel. Contoh melakukan analisis korelasi

Analisis korelasi adalah metode penelitian yang umum digunakan untuk menentukan tingkat ketergantungan nilai ke-1 pada nilai ke-2. Spreadsheet memiliki alat khusus yang memungkinkan Anda untuk menerapkan jenis penelitian ini.

Inti dari analisis korelasi

Penting untuk menentukan hubungan antara dua besaran yang berbeda. Dengan kata lain, ia mengungkapkan ke arah mana (lebih kecil / lebih besar) nilai berubah tergantung pada perubahan detik.

Tujuan analisis korelasi

Ketergantungan terbentuk ketika identifikasi koefisien korelasi dimulai. Metode ini berbeda dengan analisis regresi, karena hanya ada satu indikator yang dihitung menggunakan korelasi. Interval berubah dari +1 menjadi -1. Jika positif, maka peningkatan nilai pertama berkontribusi pada peningkatan kedua. Jika negatif, maka kenaikan nilai pertama berkontribusi pada penurunan nilai kedua. Semakin tinggi koefisien, semakin kuat satu nilai mempengaruhi yang kedua.

Penting! Pada koefisien ke-0, tidak ada hubungan antara besaran-besaran.

Perhitungan koefisien korelasi

Mari kita analisis perhitungan pada beberapa sampel. Misalnya, ada data tabular, di mana pengeluaran untuk promosi iklan dan volume penjualan dijelaskan berdasarkan bulan dalam kolom terpisah. Berdasarkan tabel tersebut, kita akan mengetahui tingkat ketergantungan volume penjualan terhadap uang yang dikeluarkan untuk promosi periklanan.

Metode 1: Menentukan Korelasi Melalui Fungsi Wizard

CORREL – fungsi yang memungkinkan Anda menerapkan analisis korelasi. Bentuk umum - CORREL(massiv1;massiv2). Instruksi rinci:

1. Penting untuk memilih sel di mana ia direncanakan untuk menampilkan hasil perhitungan. Klik "Sisipkan Fungsi" yang terletak di sebelah kiri bidang teks untuk memasukkan rumus.

2. Wizard Fungsi terbuka. Di sini Anda perlu menemukan COREL, klik di atasnya, lalu "OK".

3. Jendela argumen terbuka. Di baris "Array1" Anda harus memasukkan koordinat interval dari 1 nilai. Dalam contoh ini, ini adalah kolom Nilai Penjualan. Anda hanya perlu memilih semua sel yang ada di kolom ini. Demikian pula, Anda perlu menambahkan koordinat kolom kedua ke baris "Array2". Dalam contoh kita, ini adalah kolom Biaya Iklan.

4. Setelah memasukkan semua rentang, klik tombol "OK".

Koefisien ditampilkan di sel yang ditunjukkan di awal tindakan kami. Hasil yang diperoleh adalah 0,97. Indikator ini mencerminkan ketergantungan yang tinggi dari nilai pertama pada yang kedua.

Metode 2: Hitung Korelasi Menggunakan Analysis ToolPak

Ada metode lain untuk menentukan korelasi. Di sini salah satu fungsi yang ditemukan dalam paket analisis digunakan. Sebelum menggunakannya, Anda perlu mengaktifkan alat tersebut. Instruksi terperinci:

1. Buka bagian "File".

2. Jendela baru akan terbuka, di mana Anda perlu mengklik bagian "Pengaturan".
3. Klik pada "Add-on".
4. Kami menemukan elemen "Manajemen" di bagian bawah. Di sini Anda perlu memilih "Excel Add-in" dari menu konteks dan klik "OK".

5. Jendela pengaya khusus telah terbuka. Tempatkan tanda centang di sebelah elemen "Paket Analisis". Kami mengklik "Oke".
6. Aktivasi berhasil. Sekarang mari kita pergi ke Data. Blok "Analisis" muncul, di mana Anda perlu mengklik "Analisis Data".
7. Di jendela baru yang muncul, pilih elemen "Korelasi" dan klik "OK".

8. Jendela pengaturan analisis muncul di layar. Di baris "Interval input" perlu untuk memasukkan kisaran semua kolom yang benar-benar mengambil bagian dalam analisis. Dalam contoh ini, ini adalah kolom "Nilai penjualan" dan "Biaya iklan". Pengaturan tampilan output awalnya diatur ke Lembar Kerja Baru, yang berarti bahwa hasilnya akan ditampilkan pada lembar yang berbeda. Secara opsional, Anda dapat mengubah lokasi keluaran hasil. Setelah melakukan semua pengaturan, klik "OK".

Skor akhir sudah keluar. Hasilnya sama seperti pada cara pertama – 0,97.

Pengertian dan Perhitungan Koefisien Korelasi Berganda Dalam MS Excel

Untuk mengidentifikasi tingkat ketergantungan beberapa besaran, digunakan beberapa koefisien. Di masa depan, hasilnya dirangkum dalam tabel terpisah, yang disebut matriks korelasi.

Panduan lengkap:

1. Di bagian "Data", kami menemukan blok "Analisis" yang sudah dikenal dan klik "Analisis Data".

2. Di jendela yang muncul, klik elemen "Korelasi" dan klik "OK".
3. Di baris "Interval input" kami mengemudi dalam interval untuk tiga kolom atau lebih dari tabel sumber. Rentang dapat dimasukkan secara manual atau cukup pilih dengan LMB, dan secara otomatis akan muncul di baris yang diinginkan. Dalam "Pengelompokan" pilih metode pengelompokan yang sesuai. Dalam "Parameter Output" menentukan lokasi di mana hasil korelasi akan ditampilkan. Kami mengklik "Oke".

4. Siap! Matriks korelasi dibangun.

Pasangan Koefisien Korelasi di Excel

Mari kita cari tahu cara menggambar koefisien korelasi pasangan dengan benar dalam spreadsheet Excel.

Perhitungan koefisien korelasi pasangan di Excel

Misalnya, Anda memiliki nilai x dan y.

X adalah variabel terikat dan y adalah variabel bebas. Untuk itu perlu dicari arah dan kekuatan hubungan antara indikator-indikator tersebut. Instruksi langkah demi langkah:

1. Mari kita cari nilai rata-rata menggunakan fungsi JANTUNG.

2. Mari kita hitung masing-masing х и rata-rata, у и avg menggunakan operator «-».

3. Kami mengalikan perbedaan yang dihitung.

4. Kami menghitung jumlah indikator di kolom ini. Pembilang adalah hasil yang ditemukan.

5. Hitung penyebut selisihnya х и rata-rata x, y и y-sedang. Untuk melakukan ini, kami akan melakukan kuadrat.

6. Menggunakan fungsi OTOMATIS, temukan indikator di kolom yang dihasilkan. Kami melakukan perkalian. Menggunakan fungsi ROOT kuadratkan hasilnya.

7. Kami menghitung hasil bagi menggunakan nilai penyebut dan pembilang.

8. CORREL adalah fungsi terintegrasi yang memungkinkan Anda untuk mencegah perhitungan yang rumit. Kami pergi ke "Function Wizard", pilih CORREL dan tentukan array indikator х и у. Kami membangun grafik yang menampilkan nilai yang diperoleh.

Matriks Koefisien Korelasi Berpasangan di Excel

Mari kita menganalisis cara menghitung koefisien matriks berpasangan. Misalnya, ada matriks empat variabel.

Petunjuk langkah demi langkah:

1. Kami pergi ke "Analisis Data", yang terletak di blok "Analisis" pada tab "Data". Pilih Korelasi dari daftar yang muncul.
2. Kami mengatur semua pengaturan yang diperlukan. “Interval masukan” – interval keempat kolom. “Interval keluaran” – tempat kita ingin menampilkan total. Kami mengklik tombol "OK".
3. Matriks korelasi dibangun di tempat yang dipilih. Setiap persimpangan baris dan kolom adalah koefisien korelasi. Angka 1 ditampilkan ketika koordinat cocok.

Fungsi CORREL untuk menentukan hubungan dan korelasi di Excel

CORREL – fungsi yang digunakan untuk menghitung koefisien korelasi antara 2 array. Mari kita lihat empat contoh semua kemampuan fungsi ini.

Contoh penggunaan fungsi CORREL di Excel

Contoh pertama. Ada piring dengan informasi tentang gaji rata-rata karyawan perusahaan selama sebelas tahun dan nilai tukar $. Penting untuk mengidentifikasi hubungan antara dua besaran ini. Tabelnya terlihat seperti ini:

Algoritma perhitungan terlihat seperti ini:

Skor yang ditampilkan mendekati 1. Hasil:

Penentuan koefisien korelasi dampak tindakan terhadap hasil

Contoh kedua. Dua penawar mendekati dua agensi yang berbeda untuk meminta bantuan dengan promosi lima belas hari. Setiap hari jajak pendapat sosial dilakukan, yang menentukan tingkat dukungan untuk setiap pemohon. Setiap orang yang diwawancarai dapat memilih salah satu dari dua pelamar atau menentang semuanya. Penting untuk menentukan seberapa besar setiap promosi periklanan mempengaruhi tingkat dukungan bagi pelamar, perusahaan mana yang lebih efisien.

Dengan menggunakan rumus di bawah ini, kami menghitung koefisien korelasi:

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

hasil:

Dari hasil yang diperoleh, menjadi jelas bahwa tingkat dukungan untuk pelamar pertama meningkat setiap hari promosi iklan, oleh karena itu, koefisien korelasi mendekati 1. Ketika iklan diluncurkan, pelamar lain memiliki banyak kepercayaan, dan untuk 1 hari ada trend positif. Kemudian tingkat kepercayaan menurun dan pada hari kelima belas turun di bawah indikator awal. Skor rendah menunjukkan bahwa promosi memiliki dampak negatif terhadap dukungan. Jangan lupa bahwa faktor penyerta lainnya yang tidak dipertimbangkan dalam bentuk tabel juga dapat mempengaruhi indikator.

Analisis popularitas konten dengan korelasi penayangan video dan repost

Contoh ketiga. Seseorang untuk mempromosikan video mereka sendiri di hosting video YouTube menggunakan jejaring sosial untuk mengiklankan saluran. Dia memperhatikan bahwa ada beberapa hubungan antara jumlah repost di jejaring sosial dan jumlah penayangan di saluran. Apakah mungkin untuk memprediksi kinerja masa depan menggunakan alat spreadsheet? Penting untuk mengidentifikasi kewajaran penerapan persamaan regresi linier untuk memprediksi jumlah penayangan video tergantung pada jumlah repost. Tabel dengan nilai:

Sekarang perlu untuk menentukan ada tidaknya hubungan antara 2 indikator sesuai dengan rumus di bawah ini:

0,7;IF(CORREL(A3:A8;B3:B8)>0,7;”Hubungan langsung kuat”;”Hubungan terbalik kuat”);”Lemah atau tidak ada hubungan”)' class='formula'>

Jika koefisien yang dihasilkan lebih besar dari 0,7, maka lebih tepat menggunakan fungsi regresi linier. Dalam contoh ini, kami melakukan:

Sekarang kita sedang membangun grafik:

Kami menerapkan persamaan ini untuk menentukan jumlah penayangan pada 200, 500 dan 1000 share: =9,2937*H4-206,12. Kami mendapatkan hasil sebagai berikut:

fungsi RAMALAN CUACA memungkinkan Anda untuk menentukan jumlah tampilan saat ini, jika ada, misalnya, dua ratus lima puluh repost. Kami menerapkan: 0,7;PREDICTION(D7;B3:B8;A3:A8);”Nilainya tidak terkait”)' class='formula'>. Kami mendapatkan hasil sebagai berikut:

Fitur menggunakan fungsi CORREL di Excel

Fungsi ini memiliki beberapa fitur berikut:

1. Sel kosong tidak diperhitungkan.
2. Sel yang berisi informasi jenis Boolean dan Teks tidak diperhitungkan.
3. Negasi ganda “-” digunakan untuk menghitung nilai logika dalam bentuk angka.
4. Jumlah sel dalam array yang dipelajari harus cocok, jika tidak, pesan #N/A akan ditampilkan.

Penilaian signifikansi statistik dari koefisien korelasi

Saat menguji signifikansi suatu koefisien korelasi, hipotesis nolnya adalah bahwa indikator tersebut bernilai 0, sedangkan alternatifnya tidak. Rumus berikut digunakan untuk verifikasi:

Kesimpulan

Analisis korelasi dalam spreadsheet adalah proses yang sederhana dan otomatis. Untuk melakukannya, Anda hanya perlu mengetahui di mana alat yang diperlukan berada dan cara mengaktifkannya melalui pengaturan program.