Assalamu'alaikum Wr. Wb. Alhamdulillah ada yang menjabarkannya... Makasih mas Agus...
Untuk formulanya, seharusnya dari 1:60 seperti pada file terbaru yang dilampirkan tempo email. Berubah menjadi 2:63 karena ada proses insert row mulai baris pertama Excel dan sebaris di tengah baris [setelah baris 1 Excel] (lupa baris berapa) Untuk penggunaan IF daripada Rept, silakan disesuaikan dengan kenyamanan masing-masing. Pada file lampiran terakhir tempo email, formula sedikit berubah karena permintaan penanya ternyata harus exact match dengan kriteria alias tidak seperti contoh hasil kerja manual yang dikerjakan penanya. Jadi, melalui file pertama (kriterianya blok yang diawali oleh) yang dijabarkan mas Agus dan file kedua (kriterianya Exact Match) bisa didapatkan contoh pengolahan data teks yang bervariatif. Untuk mengganti formula (Len+1-Len(Substitute))/(len+1) dengan Sumproduct, silakan disesuaikan dengan kenyamanan dan pemahaman masing-masing. Penggunaan formula len bla tersebut dimaksudkan untuk : 1. membantu menyegarkan BeExceller bahwa ada cara hitung jumlah karakter tertentu yang kuatirnya terlupakan jika tidak sering muncul di milis. 2. membiasakan BeExceller agar rujukan formula dirujuk ke data asli (bukan hasil formula), sehingga pada komputer yang prosesornya sudah ber-core banyak sedangkan data yang diproses nanti banyak, maka formula bisa dikerjakan bersama alias tidak menunggu selesainya kalkulasi dari cell lain yang berformula yang dirujuknya. Karena pada kasus ini, cell berformula yang di-rujuk dengan SumProduct adalah cells ber array formula yang bisa jadi akan relatif lebih lambat dikalkulasi oleh excel. Andaikan akan tetap memilih merujuk pada cells berformula (meski array formula) seperti terhadap range c14:e14, maka bisa juga menggunakan formula berbunyi : =Count*IF*( c14:e14 *, *"*?**" ) -> kalo regional setting Indonesian, ganti koma menjadi titik koma ya.. yang bahasa manusianya : "Cel, tulung hitungken ada berapa cell-kah dalam range c14:e14 tersebut *yang **diawali oleh sebuah karakter dan diikuti karakter sebarang*." ;) Sekali lagi, makasih buat mas Agus yang telah menjabarkan bunyi formula yang ada di file pertama tersebut. Malah sekaligus memperbaiki bagian formula yang mengalami pergeseran rujukan plus memberi bonus formula-formula pengganti yang bisa menjadi pengaya bagi BeExceller... Moga-moga ilmunya mas Agus semakin berkembang ya... Moga-moga BeExceller seluruhnya mendapatkan pemahaman ilmu-ilmu baru dengan adanya penjabaran dari mas Agus ini... Moga-moga juga, akan semakin banyak BeExceller yang bersedia membagi ilmunya, walau sekedar menulis penjabaran tentang isi suatu formula dari reply BeExceller lain... Maju terus Belajar-Excel n tetap semangat belajar... Wassalamu'alaikum Wr. Wb. Kid. 2014-04-26 6:46 GMT+07:00 nangAgus <[email protected]>: > > > 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]>: > >> >> >> 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 : >> > > > >
