Formula generik
=SUMPRODUCT(weights,values)/SUM(weights)
Ringkasan
Untuk mengira purata berwajaran, anda boleh menggunakan fungsi SUMPRODUCT bersama dengan fungsi SUM. Dalam contoh yang ditunjukkan, formula dalam G5, disalin ke bawah, adalah:
=SUMPRODUCT(weights,C5:E5)/SUM(weights)
di mana berat adalah julat bernama I5: K5.
Penjelasan
Purata berwajaran, juga disebut min berwajaran, adalah purata di mana beberapa nilai lebih banyak daripada yang lain. Dengan kata lain, beberapa nilai mempunyai lebih banyak "berat". Kita dapat mengira purata wajaran dengan mengalikan nilai menjadi rata-rata dengan bobot yang sesuai, kemudian membahagi jumlah hasil dengan jumlah bobot. Di Excel, ini dapat ditunjukkan dengan formula generik di bawah, di mana berat dan nilai adalah julat sel:
=SUMPRODUCT(weights,values)/SUM(weights)
Dalam lembaran kerja yang ditunjukkan, skor untuk 3 ujian muncul di lajur C hingga E, dan bobot berada dalam julat pemberat yang dinamakan (I5: K5). Formula dalam sel G5 adalah:
=SUMPRODUCT(weights,C5:E5)/SUM(weights)
Bekerja dari dalam ke luar, pertama-tama kita menggunakan fungsi SUMPRODUCT untuk mengalikan berat dengan skor yang sesuai dan menjumlahkan hasilnya:
=SUMPRODUCT(weights,C5:E5) // returns 88.25
SUMPRODUCT terlebih dahulu menggandakan unsur yang sepadan dari dua tatasusunan itu bersama-sama, kemudian mengembalikan jumlah produk:
=SUMPRODUCT((0.25,0.25,0.5),(90,83,90)) =SUMPRODUCT((22.5,20.75,45)) =88.25
Hasilnya kemudian dibahagi dengan jumlah timbang:
=88.25/SUM(weights) =88.25/SUM((0.25,0.25,0.5)) =88.25/1 =88.25
Oleh kerana formula disalin ke bawah lajur G, bobot julat dinamakan I5: K5 tidak berubah, kerana berkelakuan seperti rujukan mutlak. Walau bagaimanapun, skor di C5: E5, dimasukkan sebagai rujukan relatif, kemas kini di setiap baris baru. Hasilnya adalah rata-rata tertimbang untuk setiap nama dalam senarai seperti yang ditunjukkan. Rata-rata dalam lajur F dikira untuk rujukan hanya dengan fungsi RATA-RATA:
=AVERAGE(C5:E5)
Berat yang tidak berjumlah 1
Dalam contoh ini, bobot dikonfigurasikan untuk menambah hingga 1, jadi pembahagi selalu 1, dan hasilnya adalah nilai yang dikembalikan oleh SUMPRODUCT. Walau bagaimanapun, ciri formula yang baik adalah bahawa berat tidak perlu menambah hingga 1.
Sebagai contoh, kita boleh menggunakan berat 1 untuk dua ujian pertama dan berat 2 untuk final (kerana final dua kali lebih penting) dan purata berwajaran akan sama:
Dalam sel G5, formula diselesaikan seperti ini:
=SUMPRODUCT(weights,C5:E5)/SUM(weights) =SUMPRODUCT((1,1,2),(90,83,90))/SUM(1,1,2) =SUMPRODUCT((90,83,180))/SUM(1,1,2) =353/4 =88.25
Catatan: nilai dalam pendakap keriting () di atas adalah julat yang dinyatakan sebagai tatasusunan.
Memindahkan berat
Fungsi SUMPRODUCT memerlukan dimensi array agar serasi. Sekiranya dimensi tidak serasi, SUMPRODUCT akan mengembalikan ralat # NILAI. Dalam contoh di bawah, beratnya sama dengan contoh asal, tetapi beratnya disenaraikan dalam julat menegak:
Untuk mengira purata berwajaran dengan formula yang sama, kita perlu "membalikkan" timbangan menjadi tatasusunan mendatar dengan fungsi TRANSPOSE seperti ini:
=SUMPRODUCT(TRANSPOSE(weights),C5:E5)/SUM(weights)
Selepas TRANSPOSE dijalankan, susunan menegak:
=TRANSPOSE((0.25;0.25;0.5)) // vertical array
menjadi:
=(0.25,0.25,0.5) // horizontal array
Dan dari sudut ini, formula tersebut berkelakuan seperti sebelumnya.
Baca lebih lanjut: tatasusunan menegak dan mendatar.
