Cara menghitung koefisien korelasi linier. Koefisien korelasi berganda di Excel (Excel)

Artikel hari ini akan membahas tentang bagaimana variabel dapat dihubungkan satu sama lain. Dengan menggunakan korelasi, kita dapat menentukan apakah terdapat hubungan antara variabel pertama dan kedua. Saya harap aktivitas ini sama menyenangkannya dengan aktivitas sebelumnya!

Korelasi mengukur kekuatan dan arah hubungan antara x dan y. Gambar tersebut menunjukkan berbagai jenis korelasi dalam bentuk plot sebar pasangan terurut (x, y). Secara tradisional, variabel x ditempatkan pada sumbu horizontal dan variabel y ditempatkan pada sumbu vertikal.

Grafik A adalah contoh korelasi linier positif: jika x bertambah, y juga bertambah, dan linier. Grafik B menunjukkan contoh korelasi linier negatif, dimana jika x bertambah, y berkurang secara linier. Pada grafik C kita melihat tidak ada korelasi antara x dan y. Variabel-variabel ini tidak saling mempengaruhi satu sama lain.

Terakhir, Grafik D merupakan contoh hubungan nonlinier antar variabel. Ketika x bertambah, y mula-mula berkurang, kemudian berubah arah dan bertambah.

Sisa artikel ini berfokus pada hubungan linear antara variabel dependen dan independen.

Koefisien korelasi

Koefisien korelasi, r, memberi kita kekuatan dan arah hubungan antara variabel bebas dan variabel terikat. Nilai r berkisar antara -1,0 dan +1,0. Jika r positif, maka hubungan antara x dan y adalah positif (grafik A pada gambar), dan jika r negatif, hubungannya juga negatif (grafik B). Koefisien korelasi yang mendekati nol menunjukkan tidak adanya hubungan antara x dan y (grafik C).

Kuat tidaknya hubungan antara x dan y ditentukan oleh apakah koefisien korelasinya mendekati -1,0 atau +-1,0. Pelajari gambar berikut.

Grafik A menunjukkan korelasi positif sempurna antara x dan y pada r = + 1,0. Grafik B - korelasi negatif ideal antara x dan y pada r = - 1.0. Grafik C dan D merupakan contoh lemahnya hubungan antara variabel terikat dan variabel bebas.

Koefisien korelasi, r, menentukan kekuatan dan arah hubungan antara variabel terikat dan bebas. Nilai r berkisar antara -1,0 (hubungan negatif kuat) hingga +1,0 (hubungan positif kuat). Ketika r = 0 tidak ada hubungan antara variabel x dan y.

Kita dapat menghitung koefisien korelasi sebenarnya menggunakan persamaan berikut:

Baiklah! Saya tahu persamaan ini terlihat seperti kumpulan simbol-simbol aneh yang menakutkan, namun sebelum kita panik, mari kita terapkan contoh nilai ujian pada persamaan tersebut. Katakanlah saya ingin menentukan apakah ada hubungan antara jumlah jam yang dicurahkan seorang siswa untuk mempelajari statistik dan nilai ujian akhir. Tabel di bawah ini akan membantu kita memecah persamaan ini menjadi beberapa perhitungan sederhana dan membuatnya lebih mudah dikelola.

Seperti yang Anda lihat, ada korelasi positif yang sangat kuat antara jumlah jam yang dicurahkan untuk mempelajari suatu mata pelajaran dan nilai ujian. Guru akan sangat senang mengetahui hal ini.

Apa manfaat menjalin hubungan antar variabel serupa? Pertanyaan bagus. Jika ditemukan adanya hubungan, kita dapat memprediksi hasil ujian berdasarkan jumlah jam yang dihabiskan untuk mempelajari mata pelajaran tersebut. Sederhananya, semakin kuat hubungannya, prediksi kita akan semakin akurat.

Menggunakan Excel untuk Menghitung Koefisien Korelasi

Saya yakin setelah melihat perhitungan koefisien korelasi yang buruk ini, Anda akan sangat senang mengetahui bahwa Excel dapat melakukan semua pekerjaan ini untuk Anda menggunakan fungsi CORREL dengan karakteristik berikut:

CORREL (susunan 1; susunan 2),

array 1 = rentang data untuk variabel pertama,

array 2 = rentang data untuk variabel kedua.

Misalnya, gambar menunjukkan fungsi CORREL yang digunakan untuk menghitung koefisien korelasi untuk contoh nilai ujian.

Karakteristik kuantitatif dari hubungan tersebut dapat diperoleh dengan menghitung koefisien korelasi.

Analisis Korelasi di Excel

Fungsinya sendiri memiliki bentuk umum CORREL(array1, array2). Di bidang “Array1”, masukkan koordinat rentang sel dari salah satu nilai, yang ketergantungannya harus ditentukan. Seperti yang Anda lihat, koefisien korelasi berupa angka muncul di sel yang kita pilih sebelumnya. Jendela dengan parameter analisis korelasi terbuka. Berbeda dengan metode sebelumnya, pada kolom “Input interval” kita memasukkan interval bukan setiap kolom secara terpisah, tetapi semua kolom yang berpartisipasi dalam analisis. Seperti yang Anda lihat, aplikasi Excel menawarkan dua metode analisis korelasi sekaligus.

Grafik korelasi di excel

6) Elemen pertama dari tabel akhir akan muncul di sel kiri atas area yang dipilih. Oleh karena itu hipotesis H0 ditolak, yaitu parameter regresi dan koefisien korelasi tidak berbeda secara acak dari nol, tetapi signifikan secara statistik. 7. Estimasi persamaan regresi yang diperoleh memungkinkan untuk digunakan dalam peramalan.

Cara menghitung koefisien korelasi di excel

Jika koefisiennya 0, berarti tidak ada hubungan antar nilai. Untuk menemukan hubungan antara variabel dan y, gunakan fungsi “CORREL” bawaan Microsoft Excel. Misalnya, untuk "Array1" pilih nilai y, dan untuk "Array2" pilih nilai x. Hasilnya, Anda akan menerima koefisien korelasi yang dihitung oleh program. Selanjutnya, Anda perlu menghitung selisih antara setiap x dan xav, dan yav. Di sel yang dipilih, tulis rumus x-x, y-. Jangan lupa menyematkan sel dengan rata-rata. Hasil yang diperoleh akan menjadi koefisien korelasi yang diinginkan.

Rumus penghitungan koefisien Pearson di atas menunjukkan betapa padat karya proses ini jika dilakukan secara manual. Kedua, mohon rekomendasi jenis analisis korelasi apa yang dapat digunakan untuk sampel berbeda dengan penyebaran data yang besar? Bagaimana saya dapat membuktikan secara statistik bahwa ada perbedaan yang signifikan antara kelompok berusia di atas 60 tahun dan kelompok lainnya?

DIY: Menghitung Korelasi Mata Uang Menggunakan Excel

Misalnya, kami menggunakan Microsoft Excel, tetapi program lain yang menggunakan rumus korelasi juga bisa digunakan. 7.Setelah ini, pilih sel dengan data EUR/USD. 9.Tekan Enter untuk menghitung koefisien korelasi untuk EUR/USD dan USD/JPY. Tidak ada gunanya memperbarui angka setiap hari (kecuali Anda terobsesi dengan korelasi mata uang).

Pernahkah Anda menghadapi kebutuhan untuk menghitung derajat hubungan antara dua besaran statistik dan menentukan rumus korelasinya? Untuk melakukan ini, saya menggunakan fungsi CORREL - ada beberapa informasi tentangnya di sini. Ini mengembalikan tingkat korelasi antara dua rentang data. Secara teoritis, fungsi korelasi dapat disempurnakan dengan mengubahnya dari linier menjadi eksponensial atau logaritmik. Analisis data dan grafik korelasi dapat meningkatkan reliabilitasnya secara signifikan.

Misalkan sel B2 berisi koefisien korelasi itu sendiri, dan sel B3 berisi jumlah observasi lengkap. Apakah Anda memiliki kantor berbahasa Rusia? Omong-omong, saya juga menemukan kesalahan - signifikansi tidak dihitung untuk korelasi negatif. Jika kedua variabel berbentuk metrik dan berdistribusi normal, maka pilihannya tepat. Dan apakah mungkin untuk mengkarakterisasi kriteria kesamaan kurva hanya dengan menggunakan satu CC? Anda tidak memiliki kesamaan "kurva", tetapi kesamaan dua deret, yang pada prinsipnya dapat digambarkan dengan kurva.

1.Buka Excel

2.Buat kolom data. Dalam contoh kita, kita akan membahas hubungan, atau korelasi, antara agresi dan keraguan diri pada siswa kelas satu. Eksperimen ini diikuti oleh 30 anak, datanya disajikan pada tabel excel:

1 kolom - nomor subjek

2 kolom - agresivitas dalam poin

3 kolom - ketakberanian dalam poin

3.Kemudian Anda perlu memilih sel kosong di sebelah tabel dan klik ikonnya f(x) di panel Excel

4. Menu fungsi akan terbuka, Anda harus memilih di antara kategori Statistik , lalu temukan di antara daftar fungsi berdasarkan abjad KOREL dan klik oke

5.Kemudian akan terbuka menu argumen fungsi, yang memungkinkan Anda memilih kolom data yang kita butuhkan. Untuk memilih kolom pertama Agresivitas Anda perlu mengklik tombol biru di sebelah garis Array1

6.Pilih data untuk Array1 dari kolom Agresivitas dan klik tombol biru di kotak dialog

7. Kemudian, mirip dengan Array 1, klik tombol biru di sebelah garis Array2

8.Pilih data untuk Array2- kolom Ketakberanian dan tekan kembali tombol biru, lalu OK

9. Di sini, koefisien korelasi r-Pearson telah dihitung dan ditulis di sel yang dipilih. Dalam kasus kami, nilainya positif dan kira-kira sama dengan 0,225 . Ini berbicara tentang positif sedang hubungan antara agresivitas dan keraguan diri pada siswa kelas satu

Dengan demikian, inferensi statistik percobaan akan menjadi: r = 0,225, hubungan positif sedang antara variabel terungkap agresivitas Dan ketakberanian.

Beberapa penelitian memerlukan tingkat signifikansi p dari koefisien korelasi untuk ditentukan, tetapi Excel, tidak seperti SPSS, tidak menyediakan opsi ini. Tidak apa-apa, ada (A.D. Nasledov).

Anda juga bisa melampirkannya pada hasil penelitian.

Pernahkah Anda menghadapi kebutuhan untuk menghitung derajat hubungan antara dua besaran statistik dan menentukan rumus korelasinya? Orang normal mungkin bertanya mengapa hal ini perlu dilakukan. Anehnya, hal ini sebenarnya perlu. Mengetahui korelasi yang andal dapat membantu Anda menghasilkan banyak uang jika Anda, misalnya, seorang pedagang saham. Masalahnya adalah karena alasan tertentu tidak ada yang mengungkapkan korelasi ini (mengejutkan, bukan?).

Mari kita hitung sendiri! Misalnya, saya memutuskan untuk mencoba menghitung korelasi rubel terhadap dolar melalui euro. Mari kita lihat bagaimana hal ini dilakukan secara detail.

Artikel ini dirancang untuk kemahiran Microsoft Excel tingkat lanjut. Jika Anda tidak punya waktu untuk membaca keseluruhan artikel, Anda dapat mengunduh file dan mencari tahu sendiri.

Jika Anda sering merasa perlu melakukan hal seperti ini Saya sangat menyarankan Anda mempertimbangkan untuk membeli buku tersebut. Perhitungan statistik di Excel.

Yang penting untuk diketahui tentang korelasi

Untuk menghitung korelasi yang andal, Anda perlu memiliki sampel yang andal; semakin besar sampelnya, semakin andal pula hasilnya. Untuk keperluan contoh ini, saya mengambil sampel harian nilai tukar selama 10 tahun. Datanya tersedia gratis, saya ambil dari situs http://oanda.com.

Apa yang sebenarnya saya lakukan

(1) Setelah saya mendapatkan data mentahnya, saya mulai dengan memeriksa tingkat korelasi antara kedua kumpulan data tersebut. Untuk melakukan ini, saya menggunakan fungsi CORREL - ada sedikit informasi tentangnya. Ini mengembalikan tingkat korelasi antara dua rentang data. Hasilnya, sejujurnya, tidak terlalu mengesankan (hanya sekitar 70%). Secara umum, derajat korelasi antara dua besaran biasanya dianggap kuadrat dari besaran tersebut, yaitu korelasi tersebut ternyata dapat diandalkan sekitar 49%. Ini sangat sedikit!

(2) Ini tampak sangat aneh bagi saya. Kesalahan apa yang bisa menyusup ke dalam perhitungan saya? Jadi saya memutuskan untuk membuat grafik dan melihat apa yang bisa terjadi. Grafik tersebut secara khusus dikelompokkan berdasarkan tahun sehingga Anda dapat melihat secara visual di mana letak korelasinya. Ternyata jadwalnya seperti ini

(3) Terlihat jelas dari grafik bahwa dalam kisaran sekitar 35 rubel per euro korelasinya mulai terpecah menjadi dua bagian. Karena itu, ternyata tidak bisa diandalkan. Penting untuk mengetahui mengapa hal ini terjadi.

(4) Warna menunjukkan bahwa data tersebut mengacu pada tahun 2007, 2008, 2009. Tentu! Periode puncak dan resesi ekonomi biasanya tidak dapat diandalkan secara statistik, dan itulah yang terjadi dalam kasus ini. Oleh karena itu, saya mencoba mengecualikan periode-periode ini dari data (dan untuk memeriksanya, saya memeriksa tingkat korelasi data pada periode ini). Derajat korelasi data ini saja adalah 0,01%, artinya tidak ada sama sekali. Namun tanpa mereka, data berkorelasi sekitar 81%. Ini sudah merupakan korelasi yang cukup dapat diandalkan. Berikut adalah grafik dengan fungsinya.

Langkah selanjutnya

Secara teoritis, fungsi korelasi dapat disempurnakan dengan mengubahnya dari linier menjadi eksponensial atau logaritmik. Dalam hal ini, keandalan statistik korelasi meningkat sekitar satu persen, namun kompleksitas penerapan rumus meningkat luar biasa. Oleh karena itu, saya bertanya pada diri sendiri: apakah ini benar-benar perlu? Terserah Anda untuk memutuskan - untuk setiap kasus tertentu.

Untuk wilayah wilayah, disediakan data 200X.

Latihan:

1. Membangun bidang korelasi dan merumuskan hipotesis tentang bentuk hubungannya.

2. Menghitung parameter persamaan regresi linier

4. Dengan menggunakan koefisien elastisitas rata-rata (umum), berikan penilaian komparatif mengenai kekuatan hubungan antara faktor dan hasilnya.

7. Hitung nilai prediksi hasil jika nilai prediksi faktor meningkat 10% dari rata-ratanya. Tentukan interval kepercayaan perkiraan untuk tingkat signifikansi.

Larutan:

Mari selesaikan masalah ini menggunakan Excel.

1. Dengan membandingkan data x dan y yang tersedia, misalnya, mengurutkannya berdasarkan faktor x, kita dapat mengamati adanya hubungan langsung antara karakteristik, ketika peningkatan rata-rata tingkat penghidupan per kapita meningkatkan rata-rata harian gaji. Berdasarkan hal tersebut dapat kita asumsikan bahwa hubungan antar sifat bersifat langsung dan dapat digambarkan dengan persamaan garis lurus. Kesimpulan yang sama dikonfirmasi berdasarkan analisis grafis.

Untuk membangun bidang korelasi, Anda bisa menggunakan Excel PPP. Masukkan data awal secara berurutan: pertama x, lalu y.

Pilih area sel yang berisi data.

Lalu pilih: Sisipkan / Sebarkan Plot / Sebarkan dengan Spidol seperti yang ditunjukkan pada Gambar 1.

Gambar 1 Konstruksi bidang korelasi

Analisis bidang korelasi menunjukkan adanya ketergantungan yang mendekati bujursangkar, karena titik-titiknya terletak hampir pada satu garis lurus.

2. Untuk menghitung parameter persamaan regresi linier
Mari gunakan fungsi statistik bawaan GARIS.

Untuk ini:

1) Buka file yang ada berisi data yang dianalisis;
2) Pilih area sel kosong berukuran 5x2 (5 baris, 2 kolom) untuk menampilkan hasil statistik regresi.
3) Aktifkan Penyihir Fungsi: di menu utama pilih Rumus / Sisipkan Fungsi.
4) Di jendela Kategori kamu mengambil Statistik, di jendela fungsi - GARIS. Klik tombolnya OKE seperti yang ditunjukkan pada Gambar 2;

Gambar 2 Kotak Dialog Wizard Fungsi

5) Isi argumen fungsi:

Nilai yang diketahui untuk

Nilai x yang diketahui

Konstan- nilai logika yang menunjukkan ada tidaknya suku bebas dalam persamaan; jika Konstanta = 1, maka suku bebasnya dihitung dengan cara biasa, jika Konstanta = 0, maka suku bebasnya adalah 0;

Statistik- nilai logika yang menunjukkan apakah akan menampilkan informasi tambahan pada analisis regresi atau tidak. Jika Statistik = 1, maka informasi tambahan yang ditampilkan, jika Statistik = 0, maka hanya perkiraan parameter persamaan yang ditampilkan.

Klik tombolnya OKE;

Gambar 3 Kotak Dialog Argumen Fungsi LINEST

6) Elemen pertama dari tabel akhir akan muncul di sel kiri atas area yang dipilih. Untuk membuka seluruh tabel, tekan tombol , lalu ke kombinasi tombol ++ .

Statistik regresi tambahan akan ditampilkan dalam urutan yang ditunjukkan pada diagram berikut:

Gambar 4 Hasil perhitungan fungsi LINEST

Kami mendapatkan tingkat regresi:

Kami menyimpulkan: Dengan peningkatan rata-rata tingkat subsisten per kapita sebesar 1 gosok. upah harian rata-rata meningkat rata-rata 0,92 rubel.

Artinya, 52% variasi upah (y) dijelaskan oleh variasi faktor x - rata-rata upah hidup per kapita, dan 48% - oleh faktor lain yang tidak termasuk dalam model.

Dengan menggunakan koefisien determinasi yang dihitung, koefisien korelasi dapat dihitung: .

Keterhubungannya dinilai dekat.

4. Dengan menggunakan koefisien elastisitas rata-rata (umum), kita menentukan kekuatan pengaruh faktor terhadap hasil.

Untuk persamaan garis lurus, kita menentukan koefisien elastisitas rata-rata (total) dengan menggunakan rumus:

Kita akan mencari nilai rata-rata dengan memilih luas sel dengan nilai x dan memilih Rumus / JumlahOtomatis / Rata-rata, dan kita akan melakukan hal yang sama dengan nilai y.

Gambar 5 Perhitungan nilai rata-rata fungsi dan argumennya

Jadi, jika rata-rata biaya hidup per kapita berubah sebesar 1% dari nilai rata-ratanya, maka rata-rata upah harian akan berubah rata-rata sebesar 0,51%.

Menggunakan alat analisis data Regresi tersedia:
- hasil statistik regresi,
- hasil analisis varians,
- hasil interval kepercayaan,
- grafik kesesuaian garis residu dan regresi,
- residu dan probabilitas normal.

Prosedurnya adalah sebagai berikut:

1) periksa akses ke Paket analisis. Di menu utama, pilih: File/Opsi/Add-on.

2) Dalam daftar tarik-turun Kontrol pilih barang Add-in Excel dan tekan tombolnya Pergi.

3) Di jendela Pengaya centang kotaknya Paket analisis lalu klik tombolnya OKE.

Jika Paket analisis tidak ada dalam daftar lapangan Add-on yang tersedia, tekan tombolnya Tinjauan untuk melakukan pencarian.

Jika Anda menerima pesan yang menunjukkan bahwa paket analisis tidak diinstal pada komputer Anda, klik Ya untuk menginstalnya.

4) Di menu utama, pilih: Data / Analisis Data / Alat Analisis / Regresi lalu klik tombolnya OKE.

5) Isi kotak dialog parameter input dan output data:

Interval masukan Y- rentang yang berisi data atribut yang dihasilkan;

Interval masukan X- rentang yang berisi data karakteristik faktor;

Tag- sebuah bendera yang menunjukkan apakah baris pertama berisi nama kolom atau tidak;

Konstan - nol- bendera yang menunjukkan ada tidaknya suku bebas dalam persamaan;

Interval keluaran- cukup dengan menunjukkan sel kiri atas rentang masa depan;

6) Lembar kerja baru - Anda dapat menentukan nama sembarang untuk lembar baru.

Kemudian klik tombolnya OKE.

Gambar 6 Kotak dialog untuk memasukkan parameter alat Regresi

Hasil analisis regresi data permasalahan disajikan pada Gambar 7.

Gambar 7 Hasil penggunaan alat regresi

5. Mari kita evaluasi kualitas persamaan menggunakan kesalahan perkiraan rata-rata. Mari kita gunakan hasil analisis regresi yang disajikan pada Gambar 8.

Gambar 8 Hasil penggunaan alat regresi “Penarikan sisa”

Mari kita buat tabel baru seperti yang ditunjukkan pada Gambar 9. Di kolom C, kita menghitung kesalahan perkiraan relatif menggunakan rumus:

Gambar 9 Perhitungan kesalahan perkiraan rata-rata

Kesalahan perkiraan rata-rata dihitung menggunakan rumus:

Kualitas model yang dibangun dinilai baik karena tidak melebihi 8 - 10%.

6. Dari tabel statistik regresi (Gambar 4) kita tuliskan nilai sebenarnya dari uji F Fisher:

Karena pada tingkat signifikansi 5%, maka dapat disimpulkan bahwa persamaan regresi tersebut signifikan (terbukti hubungannya).

8. Kami akan menilai signifikansi statistik dari parameter regresi menggunakan statistik-t Student dan dengan menghitung interval kepercayaan setiap indikator.

Kami mengajukan hipotesis H 0 tentang perbedaan yang tidak signifikan secara statistik antara indikator dan nol:

.

untuk jumlah derajat kebebasan

Gambar 7 memiliki nilai t-statistik aktual:

Uji-t untuk koefisien korelasi dapat dihitung dengan dua cara:

Metode I:

Di mana - kesalahan acak dari koefisien korelasi.

Data untuk perhitungan akan kita ambil dari tabel pada Gambar 7.

Metode II:

Nilai t-statistik sebenarnya melebihi nilai tabel:

Oleh karena itu hipotesis H 0 ditolak, yaitu parameter regresi dan koefisien korelasi tidak kebetulan berbeda dari nol, tetapi signifikan secara statistik.

Interval kepercayaan untuk parameter a didefinisikan sebagai

Untuk parameter a, batas 95% seperti pada Gambar 7 adalah:

Interval kepercayaan untuk koefisien regresi didefinisikan sebagai

Untuk koefisien regresi b, batas 95% seperti pada Gambar 7 adalah:

Analisis batas atas dan batas bawah interval kepercayaan mengarah pada kesimpulan bahwa dengan probabilitas parameter a dan b, karena berada dalam batas yang ditentukan, tidak mengambil nilai nol, mis. secara statistik tidak signifikan dan berbeda secara signifikan dari nol.

7. Estimasi persamaan regresi yang diperoleh memungkinkan untuk digunakan dalam peramalan. Jika perkiraan biaya hidup adalah:

Maka perkiraan nilai biaya hidup adalah:

Kami menghitung kesalahan perkiraan menggunakan rumus:

Di mana

Kami juga akan menghitung varians menggunakan Excel PPP. Untuk ini:

1) Aktifkan Penyihir Fungsi: di menu utama pilih Rumus / Sisipkan Fungsi.

3) Isikan range yang berisi data numerik karakteristik faktor. Klik OKE.

Gambar 10 Perhitungan varians

Kami mendapat nilai varians

Untuk menghitung variance residual per derajat kebebasan, kita akan menggunakan hasil analisis variance seperti pada Gambar 7.

Interval kepercayaan untuk memprediksi nilai individu y dengan probabilitas 0,95 ditentukan oleh ekspresi:

Intervalnya cukup lebar, terutama karena kecilnya volume observasi. Secara umum, perkiraan gaji bulanan rata-rata dapat diandalkan.

Kondisi soal diambil dari: Workshop ekonometrika: Proc. tunjangan / I.I. Eliseeva, S.V. Kurysheva, N.M. Gordeenko dan lainnya; Ed. aku. Eliseeva. - M.: Keuangan dan Statistik, 2003. - 192 hal.: sakit.