Alhamdulillah, terima kasih Mr. Kid. Penjelasan yang panjang lebar.
Mudah2an kebaikan Mr. Kid untuk sharing ilmunya semoga di terimaNya.
Amiin.
Wassalam,
Tio
"Mr. Kid" <mr.nm...@gmail.com>
Sent by: belajar-excel@yahoogroups.com
03/19/2013 10:10 PM
Please respond to belajar-excel
To: belajar-excel@yahoogroups.com
cc:
Subject: Re: [belajar-excel] Re: Mencari jumlah hasil dengan 2
kriteria dengan
selain Subtotal
Betul Pak Hasyim,
Mari kita lanjutkan.
="Permintaan pelanggan di " & A2 & " saat ini sudah memiliki status "
& B2 & " sejak kemarin."
Bahasa di atas itu bahasa formula.
Bahasa formula harus dimulai oleh tag karakter sama dengan (=)
Untung di VBA ndak perlu tag. Jadi kita abaikan saja tanda sama dengannya
untuk proses belajar VBA-nya
"Permintaan pelanggan di " & A2 & " saat ini sudah memiliki status "
& B2 & " sejak kemarin."
Kemudian, A2 maupun B2 dalam bunyi bahasa formula di atas adalah sebuah
variabel milik bahasa formula.
Variabel bernama A2 diisi nilai bertipe string Jakarta dan variabel
bernama B2 diisi nilai bertipe string terkirim
Jadi, jika di VBA, kita perlu punya variabel seperti A2 dan B2 itu
(agar mudah dipahami bagi pemula, nanti kalau sudah lebih native dengan
bahasa VB, bisa sesukanya asal gak error)
Cara buat variabel di VB kan pakai DIM, maka dibuatlah varibel seperti ini
:
dim sNilaiA2 as string, sNilaiB2 as string
proses ketik teks Jakarta di A2 bisa disebut :
sNilaiA2 = "Jakarta" 'nah ini bisa dirujuk ke object range A2 dalam
worksheet
sNilaiB2 = "terkirim"
Formula tadi diletakkan di cell bernama G2, alias variabel milik bahasa
formula bernama G2.
Dibuatlah di VB varibel penerima formulanya
dim sFormulaG2 as string
kemudian diisi deh variabel sFormulaG2 dengan bunyi formula tadi :
sFormulaG2 = "Permintaan pelanggan di " & A2 & " saat ini sudah memiliki
status "
& B2 & " sejak kemarin."
karena A2 itu di VB harus jadi bernama sNilaiA2 dan B2 harus jadi
sNilaiB2, maka sFormulaG2 akhirnya (pinalnya) :
sFormulaG2 = "Permintaan pelanggan di " & sNilaiA2 & " saat ini sudah
memiliki
status " & sNilaiB2 & " sejak kemarin."
Sampai disini, harapannya bisa menceritakan kembali (di media nyekrip
masing2) tentang :
1. mendeklarasikan variabel
dim sNilaiA2 as string, sNilaiB2 as string '-> yang baik dan benar
sesuai kaidah bahasa VB yang disempurnakan
dim sNilaiA2 as string
bahasa manusianya :
"kom-kom, tulung sediakan suatui area di memori yang aku pake sebentar
(DIM yang hilang kalau prosedur selesai diproses), dengan luasan untuk
tipe string (kalau rumahkan ada tipe 21,36,dsb, sampai tipe yang
besar-besar), dan kasih patok nama yang bertuliskan sNilaiA2"
Karena sudah pesen area untuk tipe string, berarti hanya bisa diisi string
sebaiknya bukan dengan cara :
dim sNilaiA2, sNilaiB2 as string '-> yang bisa dipakai tapi
karena :
cara ini menghasilkan sNilaiA2 bertipe variant dan sNilaiB2 bertipe string
2. mengisi suatu varibel dengan suatu nilai
sNilaiB2 = "terkirim"
>> yang akan diisi ada dikiri sama dengan
>> nilai yang diisikan ke variabel ada dikanan sama dengan
>> bahasa manusianya : "tulung simpenkan tulisan berbunyi terkirim ke
suatu lokasi yang diberi nama sNilaiB2"
3. mengisikan nilai di suatu varibel kedalam variabel lain (yang di blok
kuning)
sFormulaG2 = "Permintaan pelanggan di " & sNilaiA2 & " saat ini sudah
memiliki
status " & sNilaiB2 & " sejak kemarin."
Sekarang, kita kembali sejenak untuk mengenang cara penulisan rujukan cell
di Excel.
Excel menyediakan cara merujuk A1 (seperti contoh di atas) dan cara R1C1
(pakai nomor baris R1 dan nomor kolom C1)
Kalau model A1, untuk menulis absolute reference menggunakan karakter $
seperti $B$2
di model R1C1 menggunakan kurung siku seperti R[2]C[2] (untuk contoh
$B$2 ya)
Nanti akan dibahas lebih lanjut.
Tahap berikutnya,
kalau nyimpen nulis teks yang berbunyi :
=SUMPRODUCT( (B6:B54=$F$1) * (C6:C54>$G$1) * (C6:C54<$G$1+7) , D6:D54 )
maka dibuatlah variabelnya :
dim sFormulaKu as string
sFormulaku="=SUMPRODUCT( (B6:B54=$F$1) * (C6:C54>$G$1) * (C6:C54<$G$1+7) ,
D6:D54 )"
kalau angka 1 dalam formula diatas (berarti yang nempel di $F$1 dan $G$1)
akan diganti dengan variabel yang bisa diisi sebarangan, misal jadi
2,3,dsb,
maka akan ada kemiripan bahwa si angka 1 itu harus diperlakukan seperti
kata Jakarta atau kata terkirim di contoh lalu.
Bentuk sFormulaKu akan menjadi :
sFormulaku="=SUMPRODUCT( (B6:B54=$F$" & 1 & ") * (C6:C54>$G$" & 1 & ") *
(C6:C54<$G$"
& 1 & "+7) , D6:D54 )"
Berarti, dibutuhkan variabel pengganti 1 yang ada di baris sFormulaKu
diatas.
Karena nanti isinya tipe numerik bulat, maka pesen variabel type Long
misalnya
Karena pengennya nanti angka 1 yang nempel di F akan beda dengan angka 1
yang nempel di G, maka butuh 2 variabel bertipe long
dim lSatunyaF as long, lSatunyaG as long
pura-puranya, variabel diisi dengan angka berikut :
lSatunyaF=5
lSatunyaG=9
(note : cara mengisi lSatunya diatas bisa jadi dari proses loop atau
proses apa saja)
Seperti contoh di atas sana tadi, maka baris :
sFormulaku="=SUMPRODUCT( (B6:B54=$F$" & 1 & ") * (C6:C54>$G$" & 1 & ") *
(C6:C54<$G$"
& 1 & "+7) , D6:D54 )"
bisa diganti menjadi :
sFormulaku="=SUMPRODUCT( (B6:B54=$F$" & lSatunyaF & ") * (C6:C54>$G$" &
lSatunyaG & ")
* (C6:C54<$G$" & lSatunyaG & "+7) , D6:D54 )"
supaya rapi, dibuat jadi banyak baris dengan bantuan underscore :
sFormulaku = _
"=SUMPRODUCT( (B6:B54=$F$" _
& lSatunyaF & _
") * (C6:C54>$G$" _
& lSatunyaG & _
") * (C6:C54<$G$" _
& lSatunyaG & _
"+7) , D6:D54 )"
Sekarang tampak bahwa formula yang tadinya sebagai 1 teks utuh berbunyi :
=SUMPRODUCT( (B6:B54=$F$1) * (C6:C54>$G$1) * (C6:C54<$G$1+7) , D6:D54 )
dipecah-pecah menjadi banyak potongan teks (yang merah), yang diganti
beberapa nilai dalam teks awal dengan suatu nilai dalam variabel
Sampai disini, duduk manis sambil sruput kopi dulu... sembari berimajinasi
si potongan teks berlarian mengejar variabel-variabel yang mengganti angka
1
setelah itu baru mulai lagi....
Kalau formula berbunyi :
=SUMPRODUCT( (B6:B54=$F$1) * (C6:C54>$G$1) * (C6:C54<$G$1+7) , D6:D54 )
yang menggunakan model A1 diubah agar tertulis menggunakan model R1C1,
maka bunyinya akan menjadi :
=SUMPRODUCT( (R[-8]C[-17]:R[40]C[-17]=R1C6) * (R[-8]C[-16]:R[40]C[-16]>R1C7) *
(R[-8]C[-16]:R[40]C[-16]<R1C7+7), R[-8]C[-15]:R[40]C[-15] )
Yang biru adalah angka 1 milik F, dan yang hijau adalah angka 1 milik G
Kira-kira, bunyi sFormulaKu akan seperti apa jika disusun seperti contoh
barusan yang ada variabel lSatunyaF dan lSatunyaG ?
ada yang mau nyoba ?
cruupuuut... ganti saya yang ngupi-ngupi yak..
Wassalam,
Kid.
2013/3/19 <hasyim_mudza...@yahoo.com>
Pak Kid,
Aku tau bunyi formulanya kira2 begini :
="Permintaan pelanggan di "&A2&" saat ini sudah memiliki status "&B2&"
sejak kemarin."
Betul ngga' Pak Kid ?
Salam,
Hasyim
------Original Message------
From: Mr. Kid
Sender: belajar-excel@yahoogroups.com
To: belajar-excel@yahoogroups.com
ReplyTo: belajar-excel@yahoogroups.com
Subject: Re: [belajar-excel] Re: Mencari jumlah hasil dengan 2 kriteria
dengan selain Subtotal
Sent: Mar 19, 2013 20:30
Begini saja deh... Coba di worksheet kosong. 1. pada cell A2 diisi teks
berbunyi Jakarta 2. pada cell B2 diisi teks berbunyi terkirim Pada cell
G2, tolong dibuatkan formula agar bisa menulis kalimat berbunyi :
Permintaan pelanggan di Jakarta saat ini sudah memiliki status terkirim
sejak kemarin. Kata Jakarta dan kata terkirim harus merujuk ke cell A2 dan
B2. Kira-kira bunyi formulanya bagaimana ? Wassalam, Kid.
2013/3/19 <tio.ad...@ptssb.co.id> Dear Mr. Kid, Bagaimana kalau range tujuan
dengan type RC bukan F1.
Kalau di ganti R16C4 berhasil , tapi bagaimana kalau diganti R(16+s)C4.
Kok gak berhasil kenapa yah ? Apa ada yang kurang dengan formula range
saya ? Ini code VBA nya : Dim s, t As Integer Dim rngPerson, rngDate,
rngHrs As Range s = 0 ' s akan dimulai dari 0 sampai 5 t = 0 ' t akan
dimulai dari 0 sampai 5 Set rngPerson = Range(Cells(701, 9), Cells(701,
9).End(xlDown)) Set rngDate = Range(Cells(701, 10), Cells(701,
10).End(xlDown)) Set rngHrs = Range(Cells(701, 11), Cells(701,
11).End(xlDown)) Cells(702, 14).FormulaR1C1 =
"=SumProduct((rngPerson=R(16+s)C4)*(rngDate>=R(153+t)C22)*(rngDate<=R154C22),rngHrs)"
Yang bertanda blue tebal kenapa gak berhasil yah ? Terima kasih, Wassalam,
Tio
"Mr. Kid" <mr.nm...@gmail.com> Sent by: belajar-excel@yahoogroups.com
03/18/2013 03:33 PM Please respond to belajar-excel
To: belajar-excel@yahoogroups.com cc:
Subject: Re: [belajar-excel] Re:
Mencari jumlah hasil dengan 2 kriteria dengan selain Subtotal Coba
diubah menjadi : =SUMPRODUCT( (B6:B54="MARHADI") * (C6:C54>1*"2013-1-1") *
(C6:C54<1*"2013-1-4") , D6:D54 ) Andai inputan nama oleh user di F1,
tanggal awal di G1 bertipe datetime, dan tanggal akhir di H1 bertipe
datetime : =SUMPRODUCT( (B6:B54=$F$1) * (C6:C54>$G$1) * (C6:C54<$H$1) ,
D6:D54 ) Andai inputan nama oleh user di F1, tanggal awal di G1 bertipe
datetime, dan tanggal akhir selalu 7 hari setelah tanggal awal :
=SUMPRODUCT( (B6:B54=$F$1) * (C6:C54>$G$1) * (C6:C54<$G$1+7) , D6:D54 )
Wassalam, Kid. 2013/3/1
Powered by Telkomsel BlackBerry®
------------------------------------
---------------------------------------------------------------------
bergabung ke milis (subscribe), kirim mail kosong ke:
belajar-excel-subscr...@yahoogroups.com
posting ke milis, kirimkan ke:
belajar-excel@yahoogroups.com
berkunjung ke web milis
http://tech.groups.yahoo.com/group/belajar-excel/messages
melihat file archive / mendownload lampiran
http://www.mail-archive.com/belajar-excel@yahoogroups.com/
atau (sejak 25-Apr-2011) bisa juga di :
http://milis-belajar-excel.1048464.n5.nabble.com/
menghubungi moderators & owners: belajar-excel-ow...@yahoogroups.com
keluar dari membership milis (UnSubscribe):
kirim mail kosong ke belajar-excel-unsubscr...@yahoogroups.com
---------------------------------------------------------------------Yahoo!
Groups Links
