Bagaimana Menggunakan Fungsi SUMPRODUK Excel dalam Model Data - dummies

Video: Rumus SUMPRODUCT pada Rekap Data Penduduk 2024

Fungsi SUMPRODUCT sebenarnya terdaftar di bawah kategori matematika dan trigonometri dari fungsi Excel. Karena tujuan utama SUMPRODUCT adalah menghitung jumlah produk, kebanyakan orang tidak tahu Anda sebenarnya bisa menggunakannya untuk mencari nilai. Sebenarnya, Anda bisa menggunakan fungsi serbaguna ini dengan cukup efektif dalam kebanyakan model data.

SUMPRODUCT basics

Fungsi SUMPRODUCT dirancang untuk melipatgandakan nilai dari dua atau lebih rentang data dan kemudian menambahkan hasilnya bersamaan untuk mengembalikan jumlah produk. Lihatlah gambar berikut untuk melihat skenario khas di mana SUMPRODUCT berguna.

SUMPRODUCT, mendapatkan total penjualan melibatkan mengalikan pric "width =" 458 ">

Tanpa SUMPRODUCT, mendapatkan total penjualan melibatkan penggandaan harga per unit dan kemudian menjumlahkan hasilnya.

Anda melihat analisis umum di mana Anda memerlukan total penjualan untuk tahun 2011 dan 2012. Seperti yang dapat Anda lihat, untuk mendapatkan total penjualan setiap tahun, pertama Anda harus mengalikan Harga dengan jumlah Unit untuk mendapatkan jumlah total untuk masing-masing Region Kemudian Anda harus menghitung hasil tersebut untuk mendapatkan total penjualan setiap tahunnya.

Dengan fungsi SUMPRODUCT, Anda dapat melakukan analisis dua langkah hanya dengan satu rumus. Gambar berikut menunjukkan analisis yang sama dengan rumus SUMPRODUCT. Daripada menggunakan 11 rumus, Anda dapat melakukan analisis yang sama dengan hanya 3!

Fungsi SUMPRODUCT memungkinkan Anda untuk melakukan analisis yang sama dengan hanya "width =" 535 "> > fungsi SUMPRODUCT memungkinkan Anda melakukan analisis yang sama hanya dengan 3 rumus, bukan 11.

Sintaks dari fungsi SUMPRODUCT cukup sederhana:

SUMPRODUCT (

Array1 , Array2 , …) Array

: Array mewakili rentang data. Anda dapat menggunakan dari 2 sampai 255 array dalam formula SUMPRODUCT. Array dikalikan bersama dan kemudian ditambahkan. Satu-satunya aturan keras dan cepat yang harus Anda ingat adalah bahwa semua array harus memiliki jumlah nilai yang sama. Artinya, Anda tidak dapat menggunakan SUMPRODUCT jika range X memiliki 10 nilai dan range Y memiliki 11 nilai. Jika tidak, Anda mendapatkan #VALUE! kesalahan.

Sebuah twist pada fungsi SUMPRODUCT

Hal yang menarik tentang fungsi SUMPRODUCT adalah bahwa hal itu dapat digunakan untuk menyaring nilai. Lihatlah gambar berikut ini:

Fungsi SUMPRODUCT dapat digunakan untuk memfilter data berdasarkan kriteria. "Width =" 535 ">

Fungsi SUMPRODUCT dapat digunakan untuk memfilter data berdasarkan kriteria.

Rumus di sel E12 menarik jumlah total unit hanya untuk wilayah Utara. Sementara itu, sel E13 sedang menarik unit-unit yang masuk ke wilayah Utara pada tahun 2011.

Untuk memahami bagaimana ini bekerja, lihat rumus di sel E12. Rumus itu membaca SUMPRODUCT ((C3: C10 = "North") * (E3: E10)).

Di Excel, TRUE mengevaluasi ke 1 dan FALSE mengevaluasi ke 0. Setiap nilai di kolom C yang sama dengan yang dievaluasi Utara menjadi TRUE atau 1. Bila nilainya tidak di Utara, nilainya akan berubah menjadi FALSE atau 0. Bagian dari formula yang dibaca (C3: C10 = "Utara") menghitung melalui setiap nilai di kisaran C3: C10, menetapkan 1 atau 0 untuk setiap nilai. Lalu secara internal, rumus SUMPRODUCT diterjemahkan menjadi

(1 * E3) + (0 * E4) + (0 * E5) + (0 * E6) + (1 * E7) + (0 * E8) + (0 * E9) + (0 * E10).

Ini memberi Anda jawaban 1628 karena

(1 * 751) + (0 * 483) + (0 * 789) + (0 * 932) + (1 * 877) + (0 * 162) + (0 * 258) + (0 * 517)

sama dengan 1628.

Dengan menerapkan rumus SUMPRODUCT dalam model data

Seperti biasa di Excel, Anda tidak perlu mengkodekan kriteria dengan keras dalam formula Anda. Daripada secara eksplisit menggunakan "Utara" dalam rumus SUMPRODUCT, Anda bisa mereferensi sel yang berisi nilai filter. Anda bisa membayangkan bahwa sel A3 berisi kata Utara, dalam hal ini Anda bisa menggunakan (C3: C10 = A3) dan bukan (C3: C10 = "Utara"). Dengan cara ini, Anda dapat secara dinamis mengubah kriteria filter Anda, dan formula Anda terus berlanjut.

Gambar berikut menunjukkan bagaimana Anda dapat menggunakan konsep ini untuk menarik data ke dalam tabel pementasan berdasarkan beberapa kriteria. Perhatikan bahwa masing-masing formula SUMPRODUCT yang ditampilkan di sini mereferensikan sel B3 dan C3 untuk memfilter pada Account dan Product Line. Sekali lagi, Anda dapat menambahkan daftar drop-down validasi data ke sel B3 dan C3, yang memungkinkan Anda mengubah kriteria dengan mudah.

Fungsi SUMPRODUCT dapat digunakan untuk menarik angka yang diringkas dari fungsi "SUMPRODUCT" d "width =" 535 ">