Hai Imam,
1. Rumus B. Prosesnya terasa Terlalu Panjang, karena harus membuat rumus di
record pertama, lalu Copast ke records di bawahnya.
A. Bagaimana coding VBA pada Rumus B. agar dapat mengkalkulasi
SEKALIGUS seperti pada Rumus A, TANPA harus pake Copast-copast-an?? Kalo
bisa, mohon diberi contoh lebih dari 1 cara :D
>> Array formula berdasar jumlah item hasil ada 2 macam, yaitu yang
menghasilkan sebuah nilai dan yang menghasilkan sebuah array.
>> Pada array formula yang menghasilkan sebuah array, bisa langsung dirujuk
ke seluruh cell hasil.
Contoh, jika pada A4:A14 membutuhkan array formula cek apakah kolom B
lebih dari 5 yang menghasilkan TRUE jika >5 dan FALSE jika <=5, maka pada
A4:A14 bisa diblok seluruhnya dan diberi array formula =B4:B14>5 [jangan
lupa, akhiri dengan menekan CTRL SHIFT ENTER]
Contoh ini bisa diotomasi dengan script :
range("a4:a14").FormulaArray="=B4:B14>5"
>> Pada array formula yang menghasilkan sebuah nilai dan record berikutnya
pun juga harus menggunakan array formula yang hanya berbeda rujukannya,
maka tidak bisa diperlakukan sama dengan kasus array formula yang
menghasilkan sebuah array seperti contoh diatas.
>> Opsi pertama otomasi pemasangan array formula untuk setiap sebuah cell
hasil adalah dengan proses copas, yang memiliki alur proses :
1. pasang array formula pada cell pertama
2. copy cell pertama yang telah diberi array formula
3. paste formula ke cell ke-2 dst (cell pertama tidak boleh ikut lagi)
opsi pertama ini relatif bisa bekerja pada berbagai medan data dengan
bentuk formula apapun.
>> Opsi kedua adalah mengubah array formula menjadi bukan array formula
lagi.
Opsi ini tidak dapat dilakukan pada suatu array formula yang tidak
memungkinkan untuk dilakukan penyusunan ulang array formula menjadi bukan
array formula.
Fungsi Index bisa dimanfaatkan untuk mengubah kerja array formula
menjadi bukan array formula lagi. Jangan lupa, tidak berlaku untuk seluruh
susunan komputasi array. Biasanya dapat diketahui dengan uji coba.
Untuk kasus array formula yang berbunyi :
=(MAX(*IF(myNama=B4,myNom-(myNom>5)*11)*
)+10)*10^4+(100-MATCH(B4,myNama,0))*100+(1+(C4<=5))*10+C4
masih bisa diubah menjadi bukan array formula.
-> bagian penyebab array formula adalah :
*IF(myNama=B4,myNom-(myNom>5)*11)*
-> bagian ini akan mengijinkan adanya hasil negatif dan untungnya,
fungsi agregat diluar bagian ini adalah Max, yang artinya, jika hasil
bagian ini dibuat sangat-sangat negatif ketika kondisi myNama=B4 tidak
terpenuhi, maka hasil bagian ini tetap memnuhi kebutuhan.
-> jadi bagian ini bisa diubah menjadi :
*(myNama<>B4)*-10000+myNom-(myNom>5)*11*
-> bunyi array formula *(myNama<>B4)*-10000+myNom-(myNom>5)*11* bisa
diubah dengan fungsi Index agar tidak menjadi array formula berdasar sifat
fungsi Index yang bisa menerima data inputan berupa array dan menghasilkan
sebuah array.
-> penggunaan fungsi Index-nya adalah :
* INDEX( * *(myNama<>B4)*-10000+myNom-(myNom>5)*11* * , 0 )
*
-> jadi, array formula berbunyi :
=(MAX(*IF(myNama=B4,myNom-(myNom>5)*11)*
)+10)*10^4+(100-MATCH(B4,myNama,0))*100+(1+(C4<=5))*10+C4
bisa diubah menjadi :
=(MAX(* INDEX(**(myNama<>B4)*-10000+myNom-(myNom>5)*11**,0) *
)+10)*10^4+(100-MATCH(B4,myNama,0))*100+(1+(C4<=5))*10+C4
-> jadi, script otomasi bisa menggunakan :
.Offset(1, 3).Resize(lrec, 1).Formula= _
"=(MAX(* INDEX(**(myNama<>B4)*-10000+myNom-(myNom>5)*11**,0) *
)+10)*10^4+(100-MATCH(B4,myNama,0))*100+(1+(C4<=5))*10+C4"
B. Sekalian mohon diajarkan juga: bisakah method .copy diganti dengan
.filldown dalam kasus ini?? Bagaimana caranya??
>> Methods AutoFill bisa dimanfaatkan
.Offset(1, 3).Resize(1, 1).FormulaArray = _
"=(MAX(IF(myNama=B4,myNom-(myNom>5)*11))+10)*10^4+(100-MATCH(B4,myNama,0))*100+(1+(C4<=5))*10+C4"
.Offset(1, 3).Resize(1, 1)*.autofill* .Offset(1, 3).Resize(*lrec*,
1)
'2. Rumus A. lookup_value -nya adalah MERUJUK LANGSUNG ke sel B4.
' Coding VBA dengan merujuk langsung seperti itu rentan merepotkan,
misalnya jika pada akhirnya kolom A ingin diDELETE karena tidak diperlukan,
maka code tsb harus diedit kembali. Padahal jika rumusnya langsung ditulis
disheet (Kolom G dan H), pada saat kolom A diDelete, maka rujukan rumusnya
menyesuaikan diri secara otomatis.
' Bisakah lookup_value ini ditulis dengan cara lain, TANPA harus merujuk
langsung seperti itu, agar bisa menyesuaikan diri secara otomatis terhadap
perubahan posisi sel rujukan??
>> Bisa.
>> Sebelum membahas hal ini, ada baiknya menilik kembali tentang tabel yang
baik.
>> Tabel yang baik di Excel adalah tabel yang berkaidah database. Dimulai
dari kolom A, baris 1 sebagai header, blablablabla
>> Satu hal yang menjadi seni menyusun tabel data (yang bisa diolah dalam
berbagai hal) adalah :
1. Seluruh kolom key akan lebih baik jika diletakkan pada sisi kiri
tabel (jadi ada di kolom-kolom pertama).
-> umumnya, sebuah kolom dipilih menjadi kolom key karena akan
selalu dipakai dan selamanya dipakai.
-> Jarang sekali kolom key dibuang (DROP equal Delete Column).
-> umumnya sekedar dihapus isi recordnya (DELETE equal
ClearContents).
-> pada tabel yang baik, kolom key akan tetap. Menambah sebuah
kolom key adalah suatu keputusan yang besar karena akan mempengaruhi hampir
seluruh proses bisnis.
Contoh, kesepakatan untuk menambah kolom GLAccount yang baru agar
bersanding dengan kolom GLAccount yang lama ketika ada proses pembaruan
membutuhkan perencanaan yang matang sampai dengan pengujian ulang seluruh
proses bisinis. Jangankan menambah, mengubah sebuah nilai GLAccount dari
sebuah nilai dengan susunan struktur nilai tertentu menjadi struktur nilai
yang berbeda pun sudah membuat banyak pihak waspada.
2. Blok selanjutnya adalah kolom-kolom nilai
-> kolom-kolom nilai juga diperlakukan seperti kolom-kolom key.
-> penambahan kolom-kolom nilai baru diletakkan dikanan kolom
nilai terkanan
3. Diakhiri dengan kolom-kolom informasi.
-> kolom-kolom informasi ini yang biasanya dibuang ketika sudah
tidak dibutuhkan.
-> kolom-kolom ini relatif bergeser kekanan ketika ada
penambahan kolom nilai.
>> untungnya, hal-hal diatas bukanlah sebuah hukum dasar dalam menyusun
tabel alias sekedar kecenderungan dari sekian banyak tabel dalam banyak
sistem yang diterapkan diberbagai bidang.
>> so... berhubung penyusunan tabel seperti di atas sekedar optional saja,
mari dilanjutkan lagi pembahasan tentang mendinamiskan rujukan.
>> Tadi sudah diawali dengan pernyataan 'Bisa'
>> Tapi dibantu sebuah nama range yang merujuk ke sebuah cell anchor.
>> Pada script yang ada, cell anchor adalah cell B3 yang digunakan untuk
mendefinisikan area tabel data dalam variabel range bernama rng.
>> nama range dibuat saat di worksheet (bukan on the fly saat runtime code
diproses).
>> Jadi, di worksheet, buat nama range yang merujuk ke cell B3 (absolute).
Misal diberi nama *_myAnchor_*
>> baris inisialisasi variabel rng bisa merujuk ke nama range* _myAnchor_*
Set rng = Sheet1.Range("*_myAnchor_*").CurrentRegion
>> Seluruh rujukan cell harus diganti dengan menggunakan properti address
milik cell terkait relatif terhadap variabel bernama rng
>> contoh untuk formula berbunyi :
=100-MATCH(B4,myNama,0)
dalam baris script :
.Offset(1, 2).Resize(lRec, 1).Formula = "=100-MATCH(*B4*,myNama,0)"
>> tahap 1 : memisahkan rujukan cell tetap (dalam hal ini B4) agar dapat
diganti oleh rujukan melalui properti address milik rng
.Offset(1, 2).Resize(lRec, 1).Formula = "=100-MATCH(*" & "B4"
& "*,myNama,0)"
>> tahap 2 : mengganti rujukan tetap dengan properti address relatif
terhadap variabel rng
-> rng merujuk ke
Sheet1.Range("*_myAnchor_*").CurrentRegion
-> B4 adalah sebuah cell alias resize dari variabel rng menjadi sebuah
cell, yaitu .resize(1,1)
-> B4 terletak di 1 baris dibawah baris anchor pada kolom yang sama
alias offset 1 baris ke bawah dan 0 kolom, yaitu .offset(1,0)
-> jadi B4 adalah .resize(1,1).offset(1,0)
-> properti address nya adalah .resize(1,1).offset(1,0).address
-> karena rujukan tidak absolute baris maupun absolute kolom (tidak
ada tanda $ dalam formula di depan baris dan kolom), maka
-> properti address nya
adalah*.resize(1,1).offset(1,0).address(false,false)
*
-> di-substitusikan dalam baris script menggantikan *"B4"*
.Offset(1, 2).Resize(lRec, 1).Formula = "=100-MATCH(*" & **
.resize(1,1).offset(1,0).address(false,false) & "*,myNama,0)"
Kira-kira demikian dongengnya...
Wassalam,
Kid.
2013/5/19 Imam Sinurat <[email protected]>
> **
>
>
>
> Salam Excel
>
> Yth para suhu dan rekan2 excellers,
>
> Dengan mempelajari tuntunan para suhu disini, saya belajar membuat code
> VBA di Module 1.
> Sub FormulaRujukSekaligus() telah dicoba eksekusi, dan berjalan dengan
> baik.
>
> Option Explicit
> Public Sub FormulaRujukSekaligus()
> Dim rng As Range
> Dim lRec As Long
>
> 'init range
> Set rng = Sheet1.Range("B3").CurrentRegion
> lRec = rng.Rows.Count - 1
>
> With rng
> 'beri nama range
> .Offset(1).Resize(lRec, 1).Name = "myNama"
> .Offset(1, 1).Resize(lRec, 1).Name = "myNom"
>
> 'RUMUS A. Masukkan Formula Biasa di Kolom 3 SEKALIGUS
> .Offset(1, 2).Resize(lRec, 1).Formula = "=100-MATCH(B4,myNama,0)"
>
> 'RUMUS B. Masukkan FormulaArray di Sel Pertama Kolom 4, lalu Copast ke
> Semua Records di Bawahnya.
> .Offset(1, 3).Resize(1, 1).FormulaArray =
> "=(MAX(IF(myNama=B4,myNom-(myNom>5)*11))+10)*10^4+(100-MATCH(B4,myNama,0))*100+(1+(C4<=5))*10+C4"
> .Offset(1, 3).Resize(1, 1).Copy
> .Offset(2, 3).Resize(lRec - 1, 1).PasteSpecial xlPasteFormulas
> End With
>
> End Sub
>
> 'Pertanyaan :
> '1. Rumus B. Prosesnya terasa Terlalu Panjang, karena harus membuat rumus
> di record pertama, lalu Copast ke records di bawahnya.
> 'Bagaimana coding VBA pada Rumus B. agar dapat mengkalkulasi SEKALIGUS
> seperti pada Rumus A, TANPA harus pake Copast-copast-an?? Kalo bisa, mohon
> diberi contoh lebih dari 1 cara :D
> 'Sekalian mohon diajarkan juga: bisakah method .copy diganti dengan
> .filldown dalam kasus ini?? Bagaimana caranya??
>
> '2. Rumus A. lookup_value -nya adalah MERUJUK LANGSUNG ke sel B4.
> ' Coding VBA dengan merujuk langsung seperti itu rentan merepotkan,
> misalnya jika pada akhirnya kolom A ingin diDELETE karena tidak diperlukan,
> maka code tsb harus diedit kembali. Padahal jika rumusnya langsung ditulis
> disheet (Kolom G dan H), pada saat kolom A diDelete, maka rujukan rumusnya
> menyesuaikan diri secara otomatis.
> ' Bisakah lookup_value ini ditulis dengan cara lain, TANPA harus merujuk
> langsung seperti itu, agar bisa menyesuaikan diri secara otomatis terhadap
> perubahan posisi sel rujukan??
>
> Mohon berkenan memberi pencerahan, terima kasih sebelumnya.
>
> Salam
> Sinurat
>
>
