"bess lek ape njumlah tapi range seng ape dijumlah gak mesti pye rumuse?"
suatu hari seorang teman bertanya pada saya.... dalam bahasa indonesia artinya kira2 "pak kalau mau menjumlah tetapi range yang akan dijumlah berubah2 bagaimana rumusannya?"
yah memang hal tersebut sering diperlukan dalam pekerjaan saya dan teman2 sekantor...
dalam Excel, rumus yang biasa digunakan untuk menjumlah angka dalam range tertentu adalah rumus SUM(range), dimana range adalah range dari data yang akan dijumlah misal "A1:A10" atau "A1:D1" dan sebagainya.
permasalahannya adalah bagaimana bila range tersebut berubah2 atau mungkin dalam istilah kerennya floating range... kasus yang dihadapi teman saya adalah dalam membuat laporan periodik pengadaan material, dia perlu angka2 kumulatif dari pengadaan material dari periode pertama sampai dengan periode tertentu.
untuk menjawab kasus tersebut, saya menggunakan rumus SUM(range) yang akan saya kombinasi dengan OFFSET(reference,rows,cols) sehingga rumusannya menjadi :
SUM(OFFSET(reference,rows,cols):OFFSET(reference,rows,cols))
biar lebih jelas bisa dilihat dalam gambar berikut, contoh data saya buat dengan angka2 bulat biar mudah dipahami. entri data ditempatkan pada sheet Data, sedangkan laporan ditempatkan pada sheet Laporan.
walah panjang banget ya rumusnya hehehe.... tenang saja, jangan bingung dulu... akan saya jelaskan satu persatu langkah2nya... rumus dalam gambar tersebut sengaja saya beri warna biar lebih gampang dipahami.
prinsip dasar rumus OFFSET(reference;rows;cols) adalah menentukan cell awal (reference) misal cell A1, lalu ditentukan pergeseran arah vertikal (rows) dan arah horisontalnya (cols), contoh OFFSET(A1;2;4) berarti dari cell A1 digeser ke bawah 2 baris dan digeser ke kanan 4 kolom sehingga OFFSET(A1;2;4) = cell E3.
nah kalau penggunaan rumus OFFSET itu sesederhana itu, kenapa rumus dalam gambar di atas sedemikian panjang?
MATCH(C6;Data!$C:$C;0)-ROW(Data!$E$2) sebetulnya adalah rumusan untuk menentukan rows dalam rumus OFFSET. Kalau diterjemahkan, rumus MATCH(C6;Data!$C:$C;0) adalah untuk mencari letak Semen @40 kg dalam sheet Data ada di baris ke berapa? dalam contoh Semen @40 kg pada sheet Data ada di baris ke 4. sedangkan ROW(Data!$E$2) adalah untuk menentukan baris dari cell awal, jadi MATCH(C6;Data!$C:$C;0)-ROW(Data!$E$2) = 4 – 2 = 2
cara yang sama kita gunakan untuk menentukan nilai cols yang berdasar pada Periode.
khusus pada kolom F di sheet Laporan, banyak cara lain yang lebih sederhana yaitu VLOOKUP, tetapi saya kurang suka menggunakan VLOOKUP karena untuk data yang banyak, penggunaan VLOOKUP cukup memberatkan kerja komputer kita sehingga menjadi lambat....
setelah rumus2 di atas jadi dengan benar, selanjutnya kita tinggal copy & paste ke bawah dan.... cliiiiiiiiiinnnnng... begitu kita mengganti Periode dalam sheet Laporan, maka angka2nya sudah akan terjumlah dengan benar....
mudah mudahan gampang dipahami dan berguna untuk meningkatkan kenyamanan dalam bekerja....