Fungsi OFFSET dalam Excel

"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....

Fungsi SUBTOTAL dalam Excel

Pada cell A3 ketik rumus =SUBTOTAL(2;C$3:C3) lalu copy & paste sampai cell A7. Ulangi langkah yang sama pada cell E3 sampai E7 tetapi dengan rumus =SUBTOTAL(102;G$3:G3). Pada cell C3 sampai C7 dan cell G3 sampai G7 ketik angka 100 sampai 500. Pada cell C9 ketik rumus =SUBTOTAL(9;C3:C7) sedangkan pada cell G9 ketik rumus =SUBTOTAL(109;G3:G7). Pada kondisi ini kedua tabel tersebut menampilkan angka-angka yang sama.

Lalu klik kanan angka 5 pada heading row, dan klik hide pada pop up menu dan hasilnya adalah seperti gambar berikut.

Syntax fungsi SUBTOTAL dalam Excel adalah SUBTOTAL(function_num; ref1; ref2; ...) dimana terdapat 24 jenis function_num yang terbagi dalam dua kelompok yaitu 1 s/d 12 dan 101 s/d 112. Perbedaan keduanya adalah, function_num 1 s/d 12 tetap menghitung isi cell yang di hidden, sedangkan function_num 101 s/d 112 tidak akan menghitung isi cell yang di hidden.

Rumus-rumus pada contoh di atas adalah rumus bila anda menggunakan setting regional Indonesia, apabila setting regional anda adalah UK atau US maka ";" pada rumus diganti dengan ",". funcion_num=2 dan funcion_num=102 adalah fungsi COUNT yaitu menghitung banyaknya cell yang berisi angka, sedangkan funcion_num=9 dan funcion_num=109 adalah fungsi SUM. Apabila pada kolom C dan kolom G berisi teks, gunakan funcion_num=3 dan funcion_num=103 yaitu fungsi COUNTA. Untuk funcion_num yang lain bisa dilihat pada help Excel.

Mudah-mudahan tulisan ini bermanfaat bagi yang belum pernah menggunakannya... Cara-cara di atas adalah cara yang sering saya gunakan, bila ada cara lain yang lebih singkat, mari kita sharing di sini & saya tunggu komentarnya....