Pekali penentuan dalam Excel

Salah satu petunjuk yang menggambarkan kualiti model yang dibina dalam statistik adalah pekali penentuan (R ^ 2), yang juga dikenali sebagai nilai ketepatan penghampiran. Dengan itu, anda boleh menentukan tahap ramalan ketepatan. Mari belajar bagaimana untuk mengira penunjuk ini menggunakan pelbagai alat Excel.

Pengiraan pekali penentuan

Bergantung pada tahap pekali penentuan, adalah adat untuk berkongsi model ke dalam tiga kumpulan:

• 0.8 - 1 - model kualiti yang baik;
• 0.5 - 0.8 - Model kualiti yang boleh diterima;
• 0 - 0.5 - Model kualiti yang kurang baik.

Dalam kes yang terakhir, kualiti model menunjukkan kemustahilan penggunaannya untuk ramalan.

Pilihan kaedah untuk mengira nilai yang dinyatakan dalam Excel bergantung kepada sama ada regresi adalah linear atau tidak. Dalam kes pertama, anda boleh menggunakan fungsi Firson, dan pada yang kedua anda perlu menggunakan alat khas dari pakej analisis.

Kaedah 1: Pengiraan pekali penentuan dengan fungsi linear

Pertama sekali, cari tahu bagaimana untuk mencari pekali penentuan dengan fungsi linear. Dalam kes ini, penunjuk ini akan sama dengan dataran koefisien korelasi. Kami akan membuatnya dikira menggunakan fungsi Excel yang terbenam pada contoh jadual tertentu, yang ditunjukkan di bawah.

1. Kami menyerlahkan sel di mana pekali penentuan adalah output selepas pengiraannya, dan klik pada ikon "Masukkan Fungsi".



2. Master Fungsi bermula. Kami berpindah ke kategori "statistik" dan mencatat nama "kvgirson". Seterusnya, tanah liat pada butang "OK".



3. Fungsi Quilinson Arguments Window bermula. Pengendali ini dari Kumpulan Statistik direka untuk mengira persegi koefisien korelasi fungsi Pearson, iaitu fungsi linear. Dan seperti yang kita ingat, dengan fungsi linear, pekali penentuan hanya sama dengan kuadrat koefisien korelasi.

   Sintaks pengendali ini adalah:

   = Cvgirson (Dikenali_stations_y; Dikenali_Name_x)

   Oleh itu, fungsi ini mempunyai dua pengendali, salah satunya adalah senarai nilai fungsi, dan argumen kedua. Pengendali boleh diwakili secara langsung kerana nilai-nilai yang disenaraikan melalui titik koma (;) dan dalam bentuk rujukan kepada julat di mana mereka berada. Ia adalah pilihan terakhir dan akan digunakan oleh kami dalam contoh ini.

   Pasang kursor dalam bidang "Nilai V yang Dikenali". Kami melakukan pengapit butang tetikus kiri dan mengeluarkan kandungan lajur "Y" meja. Seperti yang anda lihat, alamat pelbagai data yang ditentukan segera dipaparkan di dalam tetingkap.

   Begitu juga, isi medan "Dikenali x". Kami meletakkan kursor dalam bidang ini, tetapi kali ini memperuntukkan nilai-nilai lajur "X".

   Selepas semua data dipaparkan di tetingkap Hujah CVGirson, tanah liat di butang "OK" yang terletak di bahagian bawahnya.



4. Seperti yang anda lihat, selepas ini, program ini membuat pengiraan pekali penentuan dan memberikan hasil dalam sel yang telah diserlahkan sebelum memanggil wizard fungsi. Dalam contoh kami, nilai penunjuk yang dikira ternyata sama dengan 1. Ini bermakna bahawa model yang dibentangkan benar-benar boleh dipercayai, iaitu, menghapuskan kesilapan.

Pelajaran: Sarjana Fungsi di Microsoft Excel

Kaedah 2: Pengiraan pekali penentuan dalam fungsi tidak linear

Tetapi pilihan di atas untuk mengira nilai yang dikehendaki boleh digunakan hanya untuk fungsi linear. Apa yang perlu dilakukan untuk membuat pengiraannya dalam fungsi bukan linear? Dalam Excel ada peluang. Ia boleh dilakukan menggunakan alat "regresi", yang merupakan bahagian penting dalam pakej analisis data.

1. Tetapi sebelum menggunakan alat yang ditentukan, "Pakej Analisis" itu sendiri harus diaktifkan, yang dilumpuhkan secara lalai dalam Excele. Kami berpindah ke tab "Fail", dan kemudian melalui item "Parameter".



2. Dalam tetingkap yang dibuka, masukkan ke bahagian "ADD-IN" menggunakan navigasi pada menu menegak kiri. Di bahagian bawah tetingkap yang betul, medan kawalan terletak. Dari senarai subseksyen yang terdapat di sana, pilih nama "Add-in Excel ...", dan kemudian klik pada butang "pergi ...", yang terletak di sebelah kanan padang.



3. Tingkap dilancarkan. Di bahagian tengahnya terdapat senarai tambahan yang tersedia. Pasang kotak semak berhampiran kedudukan "Analisis Analisis". Berikutan ini, anda perlu klik pada butang "OK" di sebelah kanan antara muka tetingkap.



4. Pakej alat analisis data dalam contoh Excel semasa akan diaktifkan. Akses kepadanya terletak di pita dalam tab Data. Pindah ke tab yang dinyatakan dan tanah liat pada butang "Analisis Data" dalam kumpulan tetapan "Analisis".



5. Tetingkap "Analisis Data" diaktifkan dengan senarai alat pemprosesan maklumat. Kami memperuntukkan dari senarai ini item "regresi" dan tanah liat pada butang "OK".



6. Kemudian tetingkap alat "regresi" dibuka. Blok tetapan pertama adalah "data input". Di sini dalam dua bidang yang anda perlukan untuk menentukan alamat julat di mana nilai-nilai hujah dan fungsi terletak. Kami meletakkan kursor dalam bidang "Input Interval Y" dan memperuntukkan kandungan lajur "Y" pada helaian. Selepas alamat array muncul dalam tetingkap regresi, kami meletakkan kursor dalam medan "Input Interval Y" dan memperuntukkan sel-sel "X" lajur dengan cara yang sama.

   Berhampiran parameter "tag" dan bendera "malar-sifar" tidak diletakkan. Kotak semak boleh dipasang berhampiran parameter "kebolehpercayaan" dan dalam bidang yang bertentangan menentukan nilai yang dikehendaki penunjuk yang sepadan (secara lalai 95%).

   Dalam kumpulan "Output Parameter", anda mesti menentukan di mana kawasan akan dipaparkan hasil pengiraan. Terdapat tiga pilihan:

   • Kawasan pada helaian semasa;
   • Lembaran lain;
   • Buku lain (fail baru).

   Mewujudkan pilihan anda dalam penjelmaan pertama supaya data sumber dan hasilnya diletakkan pada satu helaian kerja. Kami meletakkan suis berhampiran parameter "Output Interval". Di lapangan bertentangan dengan item ini, kami meletakkan kursor. Klik butang kiri tetikus pada elemen kosong pada helaian, yang direka untuk menjadi sel atas kiri jadual output pengiraan. Alamat elemen ini harus diserlahkan dalam tetingkap "regresi".

   Kumpulan parameter "Residu" dan "kebarangkalian biasa" mengabaikan, kerana ia tidak penting untuk menyelesaikan tugas. Selepas itu, kami adalah tanah liat pada butang "OK", yang diletakkan di sudut kanan atas tetingkap "regresi".



7. Program ini membuat pengiraan berdasarkan data yang telah dimasukkan sebelumnya dan memaparkan hasilnya kepada julat yang ditentukan. Seperti yang anda lihat, alat ini memaparkan sejumlah besar hasil pada pelbagai parameter. Tetapi dalam konteks pelajaran semasa, kami berminat dengan penunjuk R-Square. Dalam kes ini, ia adalah sama dengan 0.947664, yang mencirikan model yang dipilih sebagai model kualiti yang baik.

Kaedah 3: Pekali penentuan untuk garis trend

Sebagai tambahan kepada pilihan di atas, pekali penentuan boleh dipaparkan secara langsung untuk garis trend dalam graf yang dibina di atas lembaran Excel. Ketahui bagaimana ini boleh dilakukan pada contoh tertentu.

1. Kami mempunyai jadual yang dibina berdasarkan jadual hujah dan fungsi fungsi yang digunakan untuk contoh sebelumnya. Kami akan membina garis trend kepadanya. Klik di mana-mana tapak kawasan pembinaan di mana jadual terletak klik kiri. Pada masa yang sama, satu set tab tambahan muncul pada pita - "Bekerja dengan carta". Pergi ke tab "Layout". Clay pada butang "Trend Line", yang terletak di bar alat "Analisis". Menu muncul dengan pilihan jenis garis trend. Mulakan pilihan pada jenis yang sepadan dengan tugas tertentu. Marilah kita memilih pilihan "penghampiran eksponen" untuk contoh kami.



2. Excel membina secara langsung di atas kapal terbang membina garis trend dalam bentuk lengkung hitam tambahan.



3. Sekarang tugas kami adalah untuk memaparkan pekali penentuan sebenar. Klik kanan klik di sepanjang garis trend. Menu konteks diaktifkan. Mulakan pilihan di dalamnya di titik format garis trend.

   

   Untuk melakukan peralihan ke tetingkap format trend, anda boleh membuat tindakan alternatif. Kami menyerlahkan garis trend dengan butang tetikus kiri di atasnya. Pindah ke tab "Layout". Tanah liat pada butang "Trend Line" dalam blok "Analisis". Dalam senarai senarai tanah liat pada titik terbaik senarai tindakan - "Parameter garis trend tambahan ...".



4. Selepas mana-mana dua tindakan di atas, tetingkap format dilancarkan di mana anda boleh menghasilkan tetapan tambahan. Khususnya, untuk memenuhi tugas kami adalah perlu untuk menubuhkan kotak pilihan yang bertentangan dengan item "Letakkan nilai ketepatan anggaran (R ^ 2)". Ia terletak di bahagian bawah tingkap. Iaitu, jadi kami memasukkan paparan pekali penentuan di kawasan pembinaan. Kemudian anda tidak lupa untuk mengklik butang "Tutup" di bahagian bawah tetingkap semasa.



5. Nilai ketepatan anggaran, iaitu, nilai pekali penentuan akan dipaparkan pada daun di kawasan pembinaan. Dalam kes ini, nilai ini dapat dilihat, sama dengan 0.9242, yang mencirikan perkiraan sebagai model yang berkualiti.



6. Sudah tentu dengan cara ini, anda boleh menubuhkan pertunjukan pekali penentuan untuk sebarang jenis trend lain. Anda boleh menukar jenis garis trend dengan membuat peralihan melalui butang pada reben atau menu konteks dalam tetingkap parameternya, seperti yang ditunjukkan di atas. Kemudian dalam tetingkap itu sendiri dalam kumpulan "membina garis trend", anda boleh beralih ke jenis lain. Kami tidak lupa untuk mengawalnya untuk "meletakkan nilai ketepatan penghampiran pada gambarajah nilai anggaran". Selepas melengkapkan tindakan di atas, klik pada butang "Tutup" di sudut kanan bawah tetingkap.



7. Dengan jenis linear, garis trend sudah mempunyai nilai ketepatan penghampiran yang sama dengan 0.9477, yang mencirikan model ini, sebagai lebih dipercayai daripada garis trend jenis eksponen yang dipertimbangkan oleh kami.



8. Oleh itu, bertukar antara pelbagai jenis trend dan membandingkan nilai kebolehpercayaan mereka (pekali penentuan), anda boleh mencari pilihan yang model paling tepat menerangkan jadual yang dikemukakan. Pilihan dengan kadar penentuan tertinggi akan menjadi yang paling dipercayai. Atas dasarnya, anda boleh membina ramalan yang paling tepat.

   Sebagai contoh, untuk kes kami, adalah mungkin untuk menentukan bahawa tahap kebolehpercayaan tertinggi mempunyai jenis polinomial trend ijazah kedua. Pekali penentuan dalam kes ini adalah 1. Ini menunjukkan bahawa model yang dinyatakan benar-benar boleh dipercayai, yang bermaksud pengecualian lengkap kesilapan.

   

   Tetapi, pada masa yang sama, ini tidak bermakna bahawa untuk graf lain, jenis trend jenis ini juga akan menjadi yang paling dipercayai. Pilihan optimum jenis garis trend bergantung kepada jenis fungsi, berdasarkan yang jadualnya dibina. Sekiranya pengguna tidak mempunyai pengetahuan yang cukup untuk "di mata" untuk menganggarkan pilihan kualiti tertinggi, maka satu-satunya output menentukan ramalan terbaik hanyalah perbandingan koefisien penentuan, seperti yang ditunjukkan dalam contoh di atas.

Lihat juga:

Membina garis trend dalam Excel

Penghampiran dalam Excel.

Dalam Excel, terdapat dua pilihan utama untuk mengira pekali penentuan: penggunaan pengendali Firson dan aplikasi alat regresi dari pakej alat analisis data. Pada masa yang sama, yang pertama dari pilihan ini bertujuan untuk digunakan hanya semasa pemprosesan fungsi linear, dan pilihan lain boleh digunakan dalam hampir semua situasi. Di samping itu, adalah mungkin untuk memaparkan pekali penentuan untuk garis graf grafik sebagai nilai ketepatan anggaran. Dengan penunjuk ini, adalah mungkin untuk menentukan jenis garis trend, yang mempunyai tahap kebolehpercayaan tertinggi untuk fungsi tertentu.