Excel : VLOOKUP/SUMPRODUCT data rentang waktu

Sering kita menemui data yang berbentuk rentang waktu, misalnya data kurs dari Kementerian Keuangan untuk perhitungan pajak ataupun data tarif angkutan yang memperhitungkan waktu (peak time atau normal time). Untuk mengolahnya, tidak cukup menggunakan fungsi VLOOKUP karena akan menghasilkan error #N/A, jadi harus menggunakan fungsi yang lainnya, misalnya fungsi SUMPRODUCT.

Contoh:

Untuk mengetahui berapa Kurs USD pada tanggal 19 Juli 2014, digunakan rumus sbb:

=SUMPRODUCT((I7>=$C$1:$C$283)*(I7<=$D$1:$D$283);$E$1:$E$283)

Lalu, bagaimana rumus SUMPRODUCT tersebut bekerja?

SUMPRODUCT merupakan salah satu fungsi pada Microsoft Excel yang berfungi untuk mengalikan komponen-komponen terkait dalam array yang diberikan dan mengembalikan jumlah dari setiap hasil perkalian tersebut. Untuk pengertian array sendiri adalah sekumpulan variabel yang memiliki tipe data yang sama dan dinyatakan dengan nama yang sama. Pada aktivitas sehari-hari fungsi SUMPRODUCT bisa digunakan untuk menjumlahkan semua pembelian, mengitung (count) pada suatu data dengan kriteria tertentu, dan lain sebagainya. 
Untuk menggunakan fungsi ini sintaksnya adalah sebagai berikut:

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

Keterangan:

• Array1 (diperlukan) merupakan argumen array pertama yang komponen-komponennya akan dikalikan kemudian ditambahkan;
• Array2, Array3, ... (opsional) argumen array 2 hingga ke 255 merupakan komponen-komponen yang akan dikalian kemudian ditambahkan.

Dari contoh diatas, jika kita bedah rumus satu per satu maka akan menjadi

(I2>=$C$1:$C$283)*(I2<=$D$1:$D$283) akan menghasilkan array {0;0;0;0;0;0;0;0;0;0;1;0; dst}

Kemudian array tersebut akan dikalikan dengan array (KURS) pada range $E$1:$E$283 atau dapat ditulis dengan rumus sbb:

{(0*11601)+(0*11868)+(0*12020)+(0*11955)+(0*11806)+(0*11841)+(0*11683)+(0*11513)+(0*11466)+(1*11544)+(0*11558)+ dst ))}

Karena yang match dengan kondisi hanya ada 1, maka rumus tersebut menghasilkan 1*11544 = 11544

Bagaimana jika tabel Kurs tidak hanya terdiri dari valuta USD saja?

Berati kita harus menambahkan 1 kondisi lagi pada rumus SUMPRODUCT sebelumnya menjadi :

=SUMPRODUCT((I6>=$C$2:$C$284)*(I6<=$D$2:$D$284)*($A$2:$A$284=J6);$E$2:$E$284)

Bagaimana, mudah bukan? Semoga bermanfaat.

File contoh : Excel : VLOOKUP/SUMPRODUCT data rentang waktu

Read More