Mr. Kid,
Ikut belajar yaa. Jika salah mohon untuk diingatkan.
Kita lihat Jurus Mr Kid dengan formula: {=IFERROR(REPT($E$11;SMALL(IF("
"&$E$11=MID("
"&$B14;ROW($1:$63);LEN($E$11)+1);ROW($1:$63));COLUMNS($C$13:C$13))>0);"")}
Rumus tersebut merupakan rumus array yang akan menghasilkan data array.
ingat untuk membuat formula array menggunakan tombol CSE.
kita mulai dari rumus terdalam MID(" "&$B14;ROW($1:$63);LEN($E$11)+1)
Formula ini memerintah excel untuk membuat data array dari data " "&$B14
yang isinya 01 001 0001 dengan ketentuan dimulai dari 1 sampai 63
(ROW($1:$63) (mohon maaf Mr. Kid ROW($2:$63) saya ubah menjadi
ROW($1:$63), karena apabila menggunakan $2:$63 di komputer saya hasil
tabelnya tidak sesuai harapan), sebanyak 3 karakter dengan perintah
LEN($E$11)+1 karena pada E11 berisi "01" (2 karakter + 1).
01 001 0001 hanya terdiri dari 11 karakter sehingga rumus MID tersebut
hanya menghasilkan 12 data seperti di bawah ini:
01
01
1 0
00
001
01
1 0
00
000
001
01
1
Kemudian menggunakan IF untuk menentukan apakah data tersebut sama
dengan data yang diminta. dengan formula:
IF(" "&$E$11=MID(" "&$B14;ROW($1:$63);LEN($E$11)+1);ROW($1:$63))
Jika data array tersebut sama dengan data yang diminta (" "&$E$11=) maka
tuliskan nomor sesuai barisnya ROW($1:$63).
rumus tersebut menghasilkan data array sebagai berikut:
01 1
01 FALSE
1 0 FALSE
00 FALSE
001 FALSE
01 FALSE
1 0 FALSE
00 FALSE
000 FALSE
001 FALSE
01 FALSE
1 FALSE
Perhatikan bahwa hanya terdapat satu data yang sesuai (nilainya 1).
Kita lanjutkan dengan formula selanjutnya yaitu formula SMALL:
SMALL(IF(" "&$E$11=MID("
"&$B14;ROW($1:$63);LEN($E$11)+1);ROW($1:$63));COLUMNS($C$13:C$13))
Dicari nilai terkecil dari hasil yang diperoleh dengan rumus If tadi.
yaitu yang terkecil ke: 1 (COLUMNS($C$13:C$13))
JIka formula ini dicopy ke kanan maka (COLUMNS($C$13:C$13) akan berubah
menjadi (COLUMNS($C$13:D$13) yang berarti terkecil kedua. dan seterusnya.
Karena hasil fungsi IF hanya mendapatkan 1 angka maka hanya terdapat 1
yang terkecil yaitu 1.
Jadi hasil formula small adalah 1 (Hasil formula small dapat
menghasilkan nilai berapapun sesuai dengan baris yang nilainya sama
dengan kriteria)
Formula selanjutnya adalah REPT.
REPT($E$11;SMALL(IF(" "&$E$11=MID("
"&$B14;ROW($1:$63);LEN($E$11)+1);ROW($1:$63));COLUMNS($C$13:C$13))>0)
Formula REPT ini adalah memerintah excel untuk mengulangi $E$11 yang
berisi 01 sebanyak hasil rumus SMALL.
karena hasil rumus small dapat lebih dari satu maka ditambahkan >0 agar
selalu hasilnya 1, dengan maksud agar hasilnya hanya dituliskan sekali saja.
Jika mau formula REPT dapat diganti dengan formula IF:
IF(SMALL(IF(" "&$E$11=MID("
"&$B14;ROW($1:$63);LEN($E$11)+1);ROW($1:$63));COLUMNS($C$13:C$13))>0;$E$11)
(mohon maaf Mr. Kid saya mengusulkan untuk menggunakan formula IF,
menurut saya lebih mudah dimengerti daripada rept. itu lho yang
menggunakan >0 he he he)
Terakhir sembunyikan apabila terjadi error dengan formula IFERROR
=IFERROR(REPT($E$11;SMALL(IF(" "&$E$11=MID("
"&$B14;ROW($1:$63);LEN($E$11)+1);ROW($1:$63));COLUMNS($C$13:C$13))>0);"")
Untuk kolom jumlah agak susah juga menjelaskan.
=(LEN(B14)+1-LEN(SUBSTITUTE(" "&B14;" "&$E$11;"")))/(LEN($E$11)+1)
Kita mulai dari perintah SUBSTITUTE(" "&B14;" "&$E$11;"")
Pada formula ini komputer diminta untuk mengganti " "&$E$11 yang berisi
01 pada sel B14 dalam hal ini berisi 01 001 0001 dengan spasi kosong.
atau dapat disederhanakan dengan perintah menghapus semua " 01" pada 01
001 0001. hasil dari formula ini adalah 001 0001
Formula selanjutnya LEN(SUBSTITUTE(" "&B14;" "&$E$11;""))
Hallo excel, tolong dihitung berapa panjangnya 001 0001, apakah ada 5 meter?
si excel pun menjawab "eem... hanya ada 9 karakter" Lho.... kok 9?
padahal saya hitung hanya ada 8.
Ternyata Mr. Kid menambahkan " " didepan B14 sehingga 8+1 = 9
Kemudian tolong dihitung berpaha hasil dari panjangnya B14 + 1 dikurangi
hasil rumus substitute.
(LEN(B14)+1-LEN(SUBSTITUTE(" "&B14;" "&$E$11;"")))
11 + 1 - 9 = 3
Maksud dari rumus ini adalah menghitung banyaknya karakter yang sesuai
dengan kriteria pada B14, kemudian dibagi dengan banyaknya kriteria pada E11
Terakhir kali rumus pembagian
=(LEN(B14)+1-LEN(SUBSTITUTE(" "&B14;" "&$E$11;"")))/(LEN($E$11)+1)
Bagaimana jika formula pada jumlah diubah menjadi:
=SUMPRODUCT((LEN(C14:E14)>1)*1)
(maksudnya hitunglah pada c14 sampai e14 jika karakternya lebih dari 1)
(mohon maaf Mr. Kid saya mengusulkan untuk menggunakan formula
Sumproduct, menurut saya lebih mudah dimengerti he he he....)
Mohon maaf jika penjelasannya tidak menjadi lebih jelas.
Mohon kiranya untuk diperbaiki
Wassalam, warahmatullah
NangAgus
On 25/04/2014 9:37, Mr. Kid wrote:
Filenya dulu saja ya... tapi gak pake VBA... (maaf ya... sering
gatelan kalo kena script... jadi nyentuh scriptnya kalo kepepet sajah...)
Moga-moga ada yang bersedia membuatkan dongengnya...
Wassalam,
Kid.
2014-04-25 3:46 GMT+07:00 <[email protected]
<mailto:[email protected]>>:
Kepada penghuni group minta tolong pemecahan pemecahan masalah
ini. karena saya masih belajar dalam hal macro dan vba
Saya mempunyai data di kolom A berupa angka ratusan cell.
bagaimana memecah / split sesuai kriteria (cukup 3 kriteria)
hasilnya. kendala utama memecah angka 0 di depan, seperti kriteria
01 (warna orange), angka 001 dan 0001 angka ikut masuk kreteria
juga (warna kuning). kalau bisa dengan macro atau kode vba krn
otomatis memecah semua data sampai akhir dgn otomatis. dan kalau
bisa juga hasil split otomatis dijumlah sesuai hasil split tiap
cell nya (warna hijau).. sebelumnya terima kasih banyak.....
Gambar terlampir :
