Bagaimana kalau disederhanakan saja dengan fungsi SUMIF,
kita bicarakan khusus keadaan di baris ke 9
Rumus di cell F9
=SUMIF(OFFSET($N$5,0,0,1,$E$3),"Act",OFFSET($N9,0,0,1,$E$3)) +
SUMIF(OFFSET($N$5,0,$E$3,1,48-$E$3),"Budget",OFFSET($N9,0,$E$3,1,48-$E$3))
Rumus itu terdiri dari dua penggalan formula yg dijumlahkan yaitu :
(1) Penjumlahan Kolom-kolom "Act" pada daerah "Bulan Jan" sampai daerah "Bulan X")
=SUMIF(OFFSET($N$5,0,0,1,$E$3),"Act",OFFSET($N9,0,0,1,$E$3))
(2) Penjumlahan Kolom-kolom "Buget" pada daerah "Bulan X" sampai daerah "Bulan Dec")
=SUMIF(OFFSET($N$5,0,$E$3,1,48-$E$3),"Budget",OFFSET($N9,0,$E$3,1,48-$E$3))
Ceritak mengenai SUMIF tentunya sudah pada hafal kan yaaa ??
SUMIF(range,criteria,sum_range)
menjumlah nilai cells Sum_Range, dengan syarat cell di Range = Criteria
Jadi yg perlu dicermati tinggal nilai di Cell $E$3 dan penggunaan fungsi OFFSET
Cell $E$3
E3 berisi formula bantu =MONTH($D$2)*4
maksodnya Nilai Bulan yg tertulis di D2 dikalikan 4
Sedangkan konstanta 4 itu didapat dari: lebar SubTabel pada tiap KolomBulan
(misal "Jan" 'kan terdiri dari 4 kolom yaitu kolom: Budget. Act, Var dan %.)
Berarti nilai di E3 ini berupa variable yg tergantung dari data BULAN yg tertulis di E2
Jika Bulan = Jan maka E3 = 4, Jika Bulan = Feb maka E3 = 8, Maret = 12, Apr = 16 dst
Nantinya angka ini akan bertugas "MELEBARKAN" range yg dirujuk oleh fungsi OFFSET !
Fungsi OFFSET
Fungsi Offset menghasilkan Object Range (Rujukan ke sebuah range)
Pada FORMULA penggalan I
' =OFFSET($N9,0,0,1,$E$3 )
menghasilkan rujukan ke sebuah Range yang letak dan dimensinya adalah
* jika di lihat dari pangkalannya : $N9
* Pojok KiriAtasnya berada di arah Vertikal : 0 (tidak digeser ke atas /ke bawah) artinya ya tetap N9
* Pojok KiriAtasnya berada di arah Horisontal : 0 (tidak digeser ke kanan /ke kiri) artinya ya tetap N9
* Tinggi (jumlah baris) = 1
* Lebar (jumlah kolom) = Nilai di cell E3
Jadi misalnya Bulan laporan = Maret (dimana E3 bernilai 12), maka
Rujukan Range hasil dari fungsi Offset tsb adalah N9:Y9
(hitung saja dari N ke Y pasti = 12 kolom)
Kalau Bulan laporan = Juni (dimana E3 bernilai 24), maka
Rujukan Range hasil dari fungsi Offset tsb adalah N9:AK9
dari N ke AK = 24 kolom)
Penjelasan untuk OFFSET($N9,0,$E$3,1,48-$E$3)) yg ada di penggalan (2) mirip dengan
penjelasan di atas.
Bedanya kalau di formula penggalan (1) range selalu dimulai dari $N9, di sini yg berubah adalah
argument ke3 = PojokKiriAtas (dmulainya rujukan range), artinya hasil rujukan range-nya bisa :
* dimulai dari R9, V9, Z9 dst sesuai Nilai E3; dan
* jumlah kolom dlm range rujukan adalah (48 - nilai E3)
Fungsi ini, percaya/ndakpercaya, akan selalu menghasilkan Rujukan ke sebaris range yg dimulai
dari "BULAN DEPANNYA" sampai "BULAN DESEMBER"
Begitulah 4 bijik Fungsi OFFSET yg ada di dua penggalan Formula tsb akan "mulur" atau 'mungkret"
sesuai data "BULAN" (tepatnya data DATE) yg pak Kusnadi ketikkan di cell E2.
Formula tersebut tentu dapat diganti agar tidak perlu mengunakan cell E3 sbg dummies, yaitu
dengan mengganti setiap TEXT $E$3 dlm formula, dengan: MONTH($D$2)*4
Tentunya masih ada beberapa cara (formula) lain untuk membuat rujukan range dinamis yg bisa
"mulur/mungkret" sesuai kriteria tertentu....
CMIIW
~ctv~
----- Original Message -----From: Kusnadi DSTo: xl-maniaSent: Monday, April 26, 2010 11:17 AMSubject: ]] XL-mania [[ Formula indirect dlm sumproductpara suhu xl-mania....saya punya file Budget & Actual Expenses
contoh pada periode bulan Maret, saya ingin menjulah
Actual = Jan-Maret &
Budget = April-Desemberbesok di bulan April ingin tahu jumlah
Actual = Jan-April &
Budget = Mei-Desember
dst dstFormula/macro yang tepat dan praktis gimana ya...
__._,_.___
+-:: XL-mania ::::::::::::::::::::----------------------------------+
| Horeeee... tembus 14,000 members... |
| http://www.facebook.com/group.php?gid=37671048001&ref=mf |
+-------------------------------------------------------------------+
| DILARANG : MLM, money game, OOT, iklan tanpa izin, SARA, testing, |
| pembicaraan pribadi, one line message, melecehkan, tidak sopan. |
+-------------------------------------------------------------------+
| Buat subjek yang kreatif, jangan : "tanya", "help", "mohon bantu" |
| Usahakan besar attachment < 200 kb. Gunakan winzip jika perlu. |
+-------------------------------------------------------------------+
| Ajak teman-teman Anda bergabung dengan mengirim e-mail kosong ke |
| XL-mania-subscribe@yahoogroups.com atau kirimkan mereka file dari |
| http://groups.yahoo.com/group/XL-mania/files/Promotion/ |
+-------------------------------------------------------------------+
| Berikan testimoni di : http://www.friendster.com/xlmania atau... |
| http://www.xl-mania.com/2008/06/testimoni-xl-mania.html |
+-------------------------------------------------------------------+
| Message lama ada di : |
| http://groups.yahoo.com/group/XL-mania/messages [perlu yahoo id] |
| http://www.mail-archive.com/xl-mania@yahoogroups.com |
+-------------------------------------------------------------------+
| Horeeee... tembus 14,000 members... |
| http://www.facebook.com/group.php?gid=37671048001&ref=mf |
+-------------------------------------------------------------------+
| DILARANG : MLM, money game, OOT, iklan tanpa izin, SARA, testing, |
| pembicaraan pribadi, one line message, melecehkan, tidak sopan. |
+-------------------------------------------------------------------+
| Buat subjek yang kreatif, jangan : "tanya", "help", "mohon bantu" |
| Usahakan besar attachment < 200 kb. Gunakan winzip jika perlu. |
+-------------------------------------------------------------------+
| Ajak teman-teman Anda bergabung dengan mengirim e-mail kosong ke |
| XL-mania-subscribe@yahoogroups.com atau kirimkan mereka file dari |
| http://groups.yahoo.com/group/XL-mania/files/Promotion/ |
+-------------------------------------------------------------------+
| Berikan testimoni di : http://www.friendster.com/xlmania atau... |
| http://www.xl-mania.com/2008/06/testimoni-xl-mania.html |
+-------------------------------------------------------------------+
| Message lama ada di : |
| http://groups.yahoo.com/group/XL-mania/messages [perlu yahoo id] |
| http://www.mail-archive.com/xl-mania@yahoogroups.com |
+-------------------------------------------------------------------+
MARKETPLACE
.
__,_._,___
Nenhum comentário:
Postar um comentário