Cara Menggunakan Fungsi SUMPRODUCT Excel


Apakah Anda tahu fungsi SUMPRODUCT di Excel? Mari kita bahas tentang cara menggunakan fungsi SUMPRODUCT Excel.

Jika tidak, apa yang tidak Anda ketahui dapat membebani Anda dalam hal waktu dan tenaga, karena Excel SUMPRODUCT adalah fungsi yang kuat, tetapi agak jarang digunakan. Dalam banyak kasus, ini memungkinkan Anda melakukan tugas yang mungkin sering Anda lakukan sekarang, tetapi jauh lebih cepat.

Tujuan SUMPRODUCT

Secara default, fungsi SUMPRODUCT mengalikan nilai dalam rentang atau larik, lalu menemukan jumlah produk tersebut. Namun, rumus dapat dimodifikasi untuk melakukan operasi penambahan, pengurangan, atau pembagian.

SUMPRODUCT tidak seperti kebanyakan fungsi lainnya karena berperilaku seperti fungsi array tanpa harus menekan Control + Shift + Enter (CSE). Fungsi array melakukan beberapa perhitungan pada nilai atau nilai dalam satu baris atau kolom, semuanya dalam satu entri.

Sintaksis

Untuk menggunakan operasi default (perkalian), sintaksnya adalah:

=SUMPRODUCT(array1, [array2], [array3], ...)

Argumen

    • Array1 adalah rentang atau larik pertama yang dikalikan.
    • Array2 adalah rentang atau larik kedua yang akan dikalikan. Array2 dan semua array berikutnya adalah opsional.

Nilai Kembali

SUMPRODUCT mengembalikan nilai tunggal yang merupakan jumlah produk dari array yang digunakan dalam rumus.

Penggunaan dasar

Penggunaan dasar SUMPRODUCT diilustrasikan dalam contoh di bawah ini.

Fungsi penjumlahan ExcelUntuk menghitung jumlah yang dibelanjakan untuk bahan makanan di atas, cara yang biasa dilakukan secara manual adalah dengan memperluas setiap baris dengan mengalikan B2 dengan C2, B3 dengan C3, dan seterusnya, menempatkan hasilnya di kolom D. Setelah jumlah yang dibayarkan setiap produk telah dihitung, kami kemudian akan menemukan totalnya.

Excel menyederhanakan seluruh proses ini dengan SUMPRODUCT. Operasi perkalian yang sama dilakukan di latar belakang: ($1,16 * 5), ($2,50 * 10), ($5,18*100), ($0,88 * 20). Tetapi hasilnya disimpan dalam memori, dan kemudian ditambahkan bersama untuk mendapatkan total $566,40.

Catatan

    • Jika hanya satu array yang digunakan, SUMPRODUCT hanya akan menambahkan semua nilai dalam array.
    • Array harus berukuran sama agar fungsi SUMPRODUCT berfungsi.
    • Jika ada argumen array yang berisi nilai non-numerik, mereka akan diperlakukan sebagai nol.
    • Bukan ide yang baik untuk menggunakan SUMPRODUCT dengan referensi kolom penuh (misalnya, A:A atau B:B). Karena kolom Excel memiliki lebih dari satu juta baris, Anda akan meminta Excel untuk memindai semua baris tersebut, yang secara tidak perlu memperlambat kinerja lembar kerja Anda.
Baca Juga:  Gaya Sel di Excel

Lakukan operasi aritmatika lainnya

Untuk menggunakan SUMPRODUCT untuk melakukan pembagian, penambahan, atau pengurangan pada array, pisahkan setiap argumen dengan simbol aritmatika yang sesuai (/, +, -) untuk operasi yang diperlukan. Setelah semua operasi dilakukan, hasilnya dijumlahkan seperti biasa.

Sebagai contoh:

=SUMPRODUCT(A1:A3/B1:B3)

akan membagi nilai di A1 dengan nilai di B1, nilai di A2 dengan nilai di B2, dan nilai di A3 dengan nilai di B3, dan menjumlahkan hasilnya.

Fungsi penjumlahan Excel - operasi aritmatikaMelakukan operasi pengurangan akan menerapkan prinsip yang sama.

Fungsi penjumlahan Excel - operasi aritmatikaTentu saja, jika array lain diperkenalkan, sebaiknya gunakan tanda kurung agar operasi dilakukan dalam urutan yang diinginkan.

Sebagai contoh:

=SUMPRODUCT(A1:A3-B1:B3*B1:B3)

menghasilkan hasil yang berbeda dari SUMPRODUCT((A1:A3-B1:B3)*B1:B3)

Fungsi penjumlahan Excel - operasi aritmatikaPada rumus di atas, Excel melakukan operasi perkalian terlebih dahulu, mengikuti aturan operasi PEMDAS (Kurung – Eksponen – Perkalian – Pembagian – Penambahan – Pengurangan).

Perkalian array B1:B3 dan B1:B3 dilakukan terlebih dahulu, kemudian nilai yang dihasilkan dikurangi dari A1:A3, untuk mengembalikan nilai 18.

Fungsi penjumlahan Excel - operasi aritmatikaSaat rumus ditulis seperti di atas, Excel melakukan persamaan pengurangan dalam tanda kurung terlebih dahulu, lalu mengalikan nilai yang dihasilkan dengan larik B1:B3, memberikan hasil 48.

Hitung rata-rata tertimbang

Aplikasi SUMPRODUCT yang cukup umum adalah menghitung rata-rata tertimbang menggunakan format berikut: SUMPRODUCT(nilai, bobot) / SUM(bobot)

Nilai dapat diberikan bobot tertentu berdasarkan prioritasnya, atau “bobot” yang diberikan untuk nilai yang totalnya 100%.

Dalam contoh berikut, berbagai bobot telah ditetapkan untuk setiap tes untuk menentukan skor akhir untuk mata pelajaran itu.

Fungsi sumproduct Excel - rata-rata tertimbangTanpa SUMPRODUCT, masalah ini kemungkinan akan diselesaikan dengan mengalikan setiap skor dengan bobotnya masing-masing dan kemudian menambahkan hasil tersebut untuk mendapatkan nilai keseluruhan.

Baca Juga:  Cara Menghitung Regresi Linear Sederhana Menggunakan Excel

Menggunakan format SUMPRODUCT yang disebutkan di atas mengurangi perhitungan tersebut menjadi satu perhitungan di Excel.

=SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5)

Fungsi sumproduct Excel - rata-rata tertimbang

Penggunaan tingkat lanjut

SUMPRODUCT dapat digunakan dengan cara yang kreatif untuk melakukan apa yang dapat dilakukan oleh fungsi lain. Ini sangat berguna ketika beberapa fungsi ini tidak tersedia dalam versi Excel yang Anda gunakan. Salah satu contohnya adalah fungsi UNIK.

Fungsi UNIK di Excel dirancang untuk menghitung jumlah nilai yang berbeda dalam larik, atau dapat menghitung jumlah nilai yang muncul tepat satu kali.

Ide nilai ‘berbeda’ pada dasarnya menjawab pertanyaan, “Berapa banyak? berbeda nilai dirujuk dalam daftar ini, menghapus duplikat?”

Namun, karena fungsi UNIK hanya tersedia di Excel 365 dan Excel online, berikut adalah solusinya, menggunakan SUMPRODUCT.

Hitung nilai yang berbeda di Excel

SUMPRODUCT dapat digunakan untuk menghitung nilai yang berbeda dengan format:

=SUMPRODUCT(1/COUNTIF(range, range))

Dengan menggunakan format ini untuk menghitung jumlah nama kota yang berbeda dari daftar 17 kota dalam rentang di bawah ini, kita akan memasukkan:

=SUMPRODUCT(1/COUNTIF(B2:B18, B2:B18))

Fungsi sumproduct Excel - hitung nilai yang berbedaHasil yang dikembalikan adalah terdapat 11 nama kota yang berbeda. Pemeriksaan manual mengungkapkan bahwa ini benar, tetapi mari kita pecahkan rumus untuk melihat bagaimana Excel mendapatkan hasil itu. Kita akan mulai dengan bagian dalam rumus, COUNTIF.

COUNTIF(B2:B18, B2:B18)

Seperti yang mungkin sudah Anda ketahui, kami menggunakan fungsi COUNTIF untuk mengetahui berapa kali setiap nilai individu muncul dalam rentang yang ditentukan. Dengan memilih rentang B2 hingga B18 sebagai rentang referensi dan rentang kriteria, kami meminta Excel untuk menghitung berapa kali setiap nilai muncul dalam larik.

Hasil dalam contoh kita adalah: {2,1,3,3,1,1,1,3,1,3,2,3,2,2,1,1,3}. Excel menyimpan larik ini dalam memorinya, dan menggunakannya untuk memproses bagian rumus yang berbunyi:

1/COUNTIF(B2:B18, B2:B18)

Ini hanya membagi angka 1 dengan setiap nilai dari hasil COUNTIF secara bergantian. Jika Anda berpikir sejenak tentang hal ini, apa yang akan terjadi adalah bahwa nilai dalam array yang sama dengan 1 akan mengembalikan nilai 1 jika dibagi dengan 1.

Baca Juga:  Rincian Lengkap Pekerjaan yang Memerlukan Keterampilan Excel

Namun, nilai yang sama dengan 2 akan mengembalikan nilai 0,5 bila dibagi dengan 1. Oleh karena itu, kedua nilai yang muncul dua kali dalam kumpulan data akan berjumlah 1. Ini menyelesaikan tugas kita menghitung setiap nilai berbeda hanya sekali.

Bagian SUMPRODUCT dari rumus:

SUMPRODUCT(1/COUNTIF(B2:B18, B2:B18))

kemudian melihat hasilnya sebagai larik yang akan ditambahkan, sehingga mengembalikan hasil 11 — jumlah total semua nilai yang berbeda dalam kumpulan data asli.

Menggunakan SUMPRODUCT untuk membuka fungsi lainnya

Fungsionalitas tipe array dari SUMPRODUCT dapat digunakan untuk membantu fungsi biasa untuk melakukan hal-hal yang luar biasa.

Misalnya, fungsi LEN, yang digunakan untuk menghitung jumlah karakter dalam string teks, hanya dapat digunakan untuk referensi sel tunggal. Tetapi bagaimana jika nilai yang akan dihitung tersebar di beberapa sel, seperti pada kasus di bawah ini?

Fungsi penjumlahan Excel - fungsi LENItu biasa cara menghitung jumlah karakter dalam rentang ini mungkin menggunakan fungsi LEN di sel B1, B2, B3, dan B4 dan kemudian menjumlahkan angka-angka itu.

LEN(A1)

Fungsi penjumlahan Excel - fungsi LENAnda bahkan mungkin berpikir untuk mencapai hal yang sama dengan menggunakan satu entri, seperti ini:

=SUM(LEN(A1),LEN(A2),LEN(A3),LEN(A4))

Fungsi penjumlahan Excel - Fungsi LENTetapi apakah Anda berpikir untuk menggunakan SUMPRODUCT seperti ini:

=SUMPRODUCT(LEN(A1:A4))

hasil penjumlahan excelMenggabungkan SUMPRODUCT dengan LEN memungkinkan Anda memasukkan rentang sel, yang bukan merupakan fitur fungsi LEN standar.

Hasil dari setiap sel ditambahkan karena fungsi ini dibangun ke dalam SUMPRODUCT. Ini menjadikan totalnya menjadi 97 tanpa harus menggunakan Control + Shift + Enter atau Control + Enter.

Belajarlah lagi

Ini hanyalah beberapa contoh bagaimana fungsi SUMPRODUCT Excel dapat membantu Anda mendapatkan lebih banyak dari Excel.

Tinggalkan komentar