Cara Menghitung Rata-Rata Tertimbang di Excel dengan SUMPRODUCT

Dalam dunia data, sering kali kita tidak hanya membutuhkan rata-rata biasa, melainkan rata-rata tertimbang. Hal ini karena setiap data bisa memiliki bobot yang berbeda, sehingga rata-rata sederhana tidak lagi cukup mewakili nilai yang sebenarnya. Misalnya, dalam kasus penjualan mobil, kita ingin tahu harga rata-rata tertimbang berdasarkan jumlah unit terjual. Jika menggunakan rata-rata biasa, hasilnya bisa menyesatkan, sebab penjualan 1 unit dan 10 unit akan dianggap sama penting.

Di artikel ini, kita akan membahas cara menghitung rata-rata tertimbang di Excel menggunakan rumus kombinasi SUMPRODUCT dan SUMIF. Kita juga akan menggunakan contoh tabel penjualan mobil bekas seperti yang terlihat pada gambar di atas agar lebih mudah dipahami.

Apa itu Rata-Rata Tertimbang?

Sebelum masuk ke Excel, mari pahami dulu definisinya. Rata-rata tertimbang (Weighted Average) adalah metode untuk menghitung rata-rata dengan mempertimbangkan bobot dari setiap nilai. Dalam kasus penjualan, bobot tersebut biasanya adalah jumlah unit yang terjual.

Contohnya:

• Jika 1 unit mobil terjual dengan harga Rp200 juta, dan
• 5 unit mobil lain terjual dengan harga Rp180 juta,

Maka rata-rata tertimbang tidak akan dihitung dengan (200 juta + 180 juta)/2, tetapi dengan mempertimbangkan jumlah unit:

Inilah mengapa cara menghitung rata-rata tertimbang sangat penting digunakan dalam data penjualan, keuangan, maupun analisis lainnya.

Contoh Tabel di Excel

Pada gambar, kita memiliki tabel penjualan dengan kolom berikut:

• Tanggal: mencatat tanggal transaksi
• Produk Terjual: nama mobil yang terjual
• Qty: jumlah unit terjual
• Harga Rata-Rata: harga rata-rata per unit pada transaksi tersebut

Selain itu, di sebelah kanan ada ringkasan berisi:

• Produk Terjual (unik)
• Harga Rata-Rata Tertimbang untuk setiap produk

Tujuan kita adalah menghitung harga rata-rata tertimbang untuk setiap jenis mobil berdasarkan data transaksi.

Rumus Excel untuk Menghitung Rata-Rata Tertimbang

Rumus yang digunakan adalah:

=SUMPRODUCT(--($C$3:$C$18=G3);$D$3:$D$18;$E$3:$E$18)/SUMIF($C$3:$C$18;G3;$D$3:$D$18)

Mari kita bedah langkah demi langkah agar jelas.

1. SUMPRODUCT bagian pertama

SUMPRODUCT(--($C$3:$C$18=G3);$D$3:$D$18;$E$3:$E$18)

• $C$3:$C$18=G3 → Mengecek apakah produk di kolom C sama dengan produk yang ada di kolom G (misalnya Suzuki Ertiga 2019). Hasilnya berupa array TRUE/FALSE.
• -- → Mengubah TRUE/FALSE menjadi angka (TRUE = 1, FALSE = 0).
• $D$3:$D$18 → Jumlah unit terjual (Qty).
• $E$3:$E$18 → Harga rata-rata per transaksi.

Dengan begitu, bagian ini akan menghitung jumlah unit × harga rata-rata, tetapi hanya untuk produk yang sesuai dengan G3.

Misalnya: jika Suzuki Ertiga 2019 terjual 5 unit dengan harga Rp181 juta, maka perhitungannya = 5 × 181 juta = Rp905 juta. Semua hasil dari baris produk yang cocok akan dijumlahkan.

2. SUMIF bagian kedua

SUMIF($C$3:$C$18;G3;$D$3:$D$18)

Bagian ini menjumlahkan Qty (jumlah unit) hanya untuk produk tertentu. Jadi, jika total penjualan Suzuki Ertiga 2019 adalah 25 unit, hasilnya akan 25.

3. Pembagian

Akhirnya, hasil dari SUMPRODUCT (total nilai × qty) dibagi dengan SUMIF (total qty).

Inilah inti dari cara menghitung rata-rata tertimbang di Excel.

Hasil Perhitungan di Contoh Tabel

Berdasarkan rumus di atas, kita mendapatkan hasil rata-rata tertimbang untuk setiap produk:

• Suzuki Ertiga 2019 = Rp182.357.500
• Daihatsu Xenia 2020 = Rp199.551.654
• Toyota Avanza 2019 = Rp187.442.571

Berikut hasilnya jika rumus di atas diimplemetasikan pada tabel contoh:

Nilai ini tentu berbeda dengan rata-rata biasa, sebab jumlah unit terjual benar-benar diperhitungkan.

Kelebihan Menggunakan SUMPRODUCT + SUMIF

Mengapa kita perlu memahami cara menghitung rata-rata tertimbang di Excel dengan rumus ini? Berikut beberapa alasannya:

1. Akurat – Menghasilkan nilai rata-rata yang lebih representatif karena mempertimbangkan jumlah unit.
2. Fleksibel – Bisa diterapkan pada berbagai kasus, tidak hanya penjualan, tetapi juga data keuangan, nilai ujian dengan bobot, dan lain-lain.
3. Otomatis – Rumus ini dinamis, jadi jika data diperbarui, hasilnya ikut berubah tanpa perlu perhitungan manual.

Aplikasi Lain dalam Dunia Nyata

Selain untuk penjualan mobil, cara menghitung rata-rata tertimbang di Excel juga sering digunakan dalam:

• Menghitung nilai ujian siswa yang memiliki bobot berbeda untuk tugas, UTS, dan UAS.
• Menghitung harga pokok penjualan dengan metode rata-rata tertimbang di akuntansi.
• Menentukan rata-rata return investasi portofolio berdasarkan jumlah modal yang ditanamkan.

Dengan kata lain, konsep ini sangat berguna dalam berbagai bidang yang membutuhkan analisis data.

Tips Menggunakan Rumus

Agar cara menghitung rata-rata tertimbang di Excel lebih mudah dipahami dan digunakan, berikut beberapa tips:

1. Gunakan referensi absolut (\$) agar range tidak berubah saat rumus disalin ke bawah.
2. Pastikan kolom yang digunakan sesuai: produk (sebagai kriteria), qty (sebagai bobot), dan harga (sebagai nilai).
3. Jika ingin lebih sederhana, bisa gunakan PivotTable dengan menambahkan kolom perhitungan tambahan.

Kesimpulan

Dari pembahasan ini, kita bisa memahami bahwa cara menghitung rata-rata tertimbang di Excel sangat penting, terutama dalam analisis data penjualan atau keuangan. Dengan kombinasi rumus:

=SUMPRODUCT(--($C$3:$C$18=G3);$D$3:$D$18;$E$3:$E$18)/SUMIF($C$3:$C$18;G3;$D$3:$D$18)

kita dapat memperoleh hasil rata-rata yang lebih akurat karena mempertimbangkan jumlah unit terjual sebagai bobot.

Jadi, mulai sekarang, jika Anda ingin menghitung rata-rata yang benar-benar mewakili kondisi data, gunakanlah cara menghitung rata-rata tertimbang di Excel ini. Metode ini akan membantu Anda membuat keputusan yang lebih tepat dan berbasis data.

Download Template Gratis

Bagi Anda yang ingin mencoba teknik ini secara langsung, kami telah menyiapkan template gratis yang dirancang khusus untuk membantu Anda mempraktikkan Cara Menghitung Rata-Rata Tertimbang di Excel dengan SUMPRODUCT dan SUMIF ini. File Excel bisa didownload melalui tautan berikut:

Atau juga jika ingin mempelajari langkah demi langkah secara visual, bisa kunjungi Youtube Channel Depot Excel. Di sana teman-teman akan melihat bagaimana rumusnya bekerja. Semoga bermanfaat!