Quantcast

[belajar-excel] Mengelompokkan Deret Kode

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[belajar-excel] Mengelompokkan Deret Kode

andri apriyadi
Dear para Pakar Excel,

Saya punya deretan Kode yang panjang dalam satu kolom dan berharap ada rumus untuk mengotomatisasi agar deret Kode ini dapat berkelompok dengan ada spasi (blank cell) yang memisahkan antara kelompok kode yang satu dengan kelompok kode lainnya.
(Contoh terlampir).
       
Mohon bantuan para master, bagaimana rumus/formulanya agar kode-kode ini berderet sesuai kelompok yang saya inginkan?

Thx before

Regards

Andree

Mengelompokkan Deret Kode.xlsx (17K) Download Attachment
Kid
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [belajar-excel] Mengelompokkan Deret Kode

Kid
Administrator
Hai Andri,

Coba pakai Pivot Table.
Langkahnya :
1. Tambahkan header tabel
2. buat 1 kolom yang berisi nilai grup (misal headernya diberi nama 'Grup'
di cell B1, sedangkan data yang di kolom A diberi nama data), dan B2
menggunakan formula :
=int( a2/100 )
3. copy formula di B2 ke seluruh baris data.
4. blok tabel (dari a1 sampai b baris terkahir data)
5. (optional) buat nama range dengan klik kanan pilih name a range, misal
diberi nama mydata
6. insert -> pivot table
7. pada data source diisi mydata atau blok range data (termasuk header) lalu
ok
8. letakkan (drag) field grup dibagian row
9. letakkan juga field data dibagian row juga, dibawah field grup
10. klik panah kecil -> field setting -> layout -> centang insert blank line
after each item label -> ok

Jika data diubah-ubah, termasuk tambah atau susut recordnya, ubah refers to
di nama range (mydata) dengan sebuah formula offset
=Offset( $a$1,0,0,counta( $a:$a ) )

Setelah itu, tambah dan kurangi data sesukanya dilanjutkan dengan tekan
refresh data atau refresh all.

Kid.

2011/8/8 andri apriyadi <[hidden email]>

> **
>
>
> Dear para Pakar Excel,
>
> Saya punya deretan Kode yang panjang dalam satu kolom dan berharap ada
> rumus untuk mengotomatisasi agar deret Kode ini dapat berkelompok dengan ada
> spasi (blank cell) yang memisahkan antara kelompok kode yang satu dengan
> kelompok kode lainnya.
> (Contoh terlampir).
>
> Mohon bantuan para master, bagaimana rumus/formulanya agar kode-kode ini
> berderet sesuai kelompok yang saya inginkan?
>
> Thx before
>
> Regards
>
> Andree
>
>  
>

re-Mengelompokkan Deret Kode.xlsx (21K) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [belajar-excel] Mengelompokkan Deret Kode

iHaps
In reply to this post by andri apriyadi
Kalau* HARUS dengan formula* kayaknya gak bisa, karena formula* bukan* alat
untuk melakukan aksi (*menginsert baris*, menulis data di baris lain dst)
Formula, katanya, hanya mengembalikan data dan itupun hanya ditampilkan
di cell dimana dia ditulis.

Dengan permainan VBA dan SpinButton mungkin bisa..
Spinbutton untuk mengatur cell (G1) sbg cell kendali (digit ke berapa yg
dianggap
pembedanya).
Sedangkan VBA nya untuk mengotomatiskan penulisan data terkelompok - nya


'----------------
Private Sub CommandButton1_Click()
   ' Haps ( Aug 08, 2011 )
   Dim vDat As Range, vOut As Range
   Dim i As Long, n As Long, Pos As Integer

   Set vDat = Cells(1).CurrentRegion
   Set vOut = Cells(1, 3)
   Pos = Len(vDat(1, 1)) - Me.SpinButton1.Value + 1
   vOut.EntireColumn.ClearContents
   n = 1

   For i = 1 To vDat.Rows.Count
      vOut(n, 1) = vDat(i, 1)
      n = n + 1
      If Not Mid(vDat(i + 1, 1), Pos, 1) = Mid(vDat(i, 1), Pos, 1) _
         Then n = n + 1
   Next i
End Sub
'----------------

cara memainkannya:

1. klik Spinbutton untuk mengubah angka di cell G1
angka ini akan dipakai sebagai penunjuk [POSISI DIGIT KE X] pada data di
kolom A
hanya saja dihitungnya dari belakang (dari kanan)

2. Klik Tombol OK

Sebelum makro menyusun data yg dikelompokkan *berdasarkan perbedaan nilai*
*pada digit Ke X tadi*, dia akan menghapus dulu data di kolom C

Mohon maaf kalau tidak berkenan dengan adanya pemrograman VBA; dihapus
saja..



2011/8/8 andri apriyadi <[hidden email]>
>
> Saya punya deretan Kode yang panjang dalam satu kolom dan berharap ada
rumus untuk mengotomatisasi
> agar deret Kode ini dapat berkelompok dengan ada spasi (blank cell) yang
memisahkan antara kelompok kode
> yang satu dengan kelompok kode lainnya.
> (Contoh terlampir).
>
> Mohon bantuan para master, bagaimana rumus/formulanya agar kode-kode ini
berderet sesuai kelompok
> yang saya inginkan?
> Thx before
>
> Regards
> Andree

ctv_Mengelompokkan Deret Kode.xlsb (35K) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [belajar-excel] Mengelompokkan Deret Kode

iHaps
*Modifikasi coding*
(maaf tadi membaca 'Syaratnya' terlalu cepat...
Pada code baru ini, Nilai SpinButoon dipakai sbg* penentu jumlah Karakter*yang
akan dianggap sebagai *Bagian Pertama* data yg  akan menjadi *dasar
pengelompokan*.

Misal Spinbutton di set = 5, maka *5 Digit pertama* akan menjadi dasar
pengelompokan.
Nilai SPinButton dapat diset dari 0 s/d 7


Private Sub CommandButton1_Click()
   ' Hapsari / Aug 8, 2011 (V2)
   '---------------------------
   Dim vDat As Range, vOut As Range
   Dim i As Long, n As Long, p As Integer
   Dim Part1 As String, Part2 As String

   Set vDat = Cells(1).CurrentRegion
   Set vOut = Cells(1, 3)
   p = Me.SpinButton1.Value
   vOut.EntireColumn.ClearContents
   n = 1

   For i = 1 To vDat.Rows.Count
      Part1 = Left(vDat(i, 1), p)
      Part2 = Left(vDat(i + 1, 1), p)
      vOut(n, 1) = vDat(i, 1)
      n = n + 1
      If Not Part1 = Part2 Then n = n + 1
   Next i

End Sub



2011/8/8 Haps <[hidden email]>

> Kalau* HARUS dengan formula* kayaknya gak bisa, karena formula* bukan*alat
> untuk melakukan aksi (*menginsert baris*, menulis data di baris lain dst)
> Formula, katanya, hanya mengembalikan data dan itupun hanya ditampilkan
> di cell dimana dia ditulis.
>
> Dengan permainan VBA dan SpinButton mungkin bisa..
> Spinbutton untuk mengatur cell (G1) sbg cell kendali (digit ke berapa yg
> dianggap
> pembedanya).
> Sedangkan VBA nya untuk mengotomatiskan penulisan data terkelompok - nya
>
>
> '----------------
> Private Sub CommandButton1_Click()
>    ' Haps ( Aug 08, 2011 )
>    Dim vDat As Range, vOut As Range
>    Dim i As Long, n As Long, Pos As Integer
>
>    Set vDat = Cells(1).CurrentRegion
>    Set vOut = Cells(1, 3)
>    Pos = Len(vDat(1, 1)) - Me.SpinButton1.Value + 1
>    vOut.EntireColumn.ClearContents
>    n = 1
>
>    For i = 1 To vDat.Rows.Count
>       vOut(n, 1) = vDat(i, 1)
>       n = n + 1
>       If Not Mid(vDat(i + 1, 1), Pos, 1) = Mid(vDat(i, 1), Pos, 1) _
>          Then n = n + 1
>    Next i
> End Sub
> '----------------
>
> cara memainkannya:
>
> 1. klik Spinbutton untuk mengubah angka di cell G1
> angka ini akan dipakai sebagai penunjuk [POSISI DIGIT KE X] pada data di
> kolom A
> hanya saja dihitungnya dari belakang (dari kanan)
>
> 2. Klik Tombol OK
>
> Sebelum makro menyusun data yg dikelompokkan *berdasarkan perbedaan nilai*
> *pada digit Ke X tadi*, dia akan menghapus dulu data di kolom C
>
> Mohon maaf kalau tidak berkenan dengan adanya pemrograman VBA; dihapus
> saja..
>
>
> 2011/8/8 andri apriyadi <[hidden email]>
> >
> > Saya punya deretan Kode yang panjang dalam satu kolom dan berharap ada
> rumus untuk mengotomatisasi
> > agar deret Kode ini dapat berkelompok dengan ada spasi (blank cell) yang
> memisahkan antara kelompok kode
> > yang satu dengan kelompok kode lainnya.
> > (Contoh terlampir).
> >
> > Mohon bantuan para master, bagaimana rumus/formulanya agar kode-kode ini
> berderet sesuai kelompok
> > yang saya inginkan?
> > Thx before
> >
> > Regards
> > Andree
>

Mengelompokkan Deret Kode (Haps V2).xlsb (32K) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [belajar-excel] Mengelompokkan Deret Kode

ghozi alkatiri
In reply to this post by andri apriyadi
dengan formula insya Allah bisa

sebelumnya saya mengucapkan terima kasih
kepada bapak summonery, yang secara tidak langsung memberikan
kontribusi dan inspirasi untuk solusi kasus ini dengan jurus ampuhnya  yang  relatif pendek untuk mencari  text
(data) baris  terakhir dari sebuah range
formulanya =LOOKUP(2;1/(RANGE<>"");RANGE)

karena
formula saya sendiri untuk hal ini lebih panjang  walaupun hasilnya sama benarnya tapi kurang praktis. 

dalam
kasus yang sekarang ini formula mencari data baris terakhir sangat dibutuhkan sekali bahkan terulang beberapa kali.
sebagimana dilihat di bawah ini yang berwarna merah.

=IF(E2="";INDEX($A$2:$A$73;MATCH(LOOKUP(2;1/(E$2:E2<>"");E$2:E2);$A$2:$A$73)+1);
IF(AND(LEFT(INDEX($A$2:$A$73;MATCH(LOOKUP(2;1/(E$2:E2<>"");E$2:E2); $A$2:$A$73)+1);4)=LEFT(LOOKUP(2;1/(E$2:E2<>"");E$2:E2);4);MID(INDEX($A$2:$A$73;
MATCH(LOOKUP(2;1/(E$2:E2<>"");E$2:E2);$A$2:$A$73)+1);5;1)=MID(LOOKUP(2;1/(E$2:E2<>"");
E$2:E2);5;1));INDEX($A$2:$A$73;MATCH(LOOKUP(2;1/(E$2:E2<>"");E$2:E2);$A$2:$A$73)+1);""))

data baris pertama  pada solusi diisi manual , formula dimulai dari baris ke dua, dan dicopy ke bawah dst.
wassalam

Ghozi Alkatiri


--- Pada Sen, 8/8/11, Haps <[hidden email]> menulis:

      Kalau HARUS dengan formula kayaknya gak bisa, karena formula bukan alatuntuk melakukan aksi (menginsert baris, menulis data di baris lain dst)

Formula, katanya, hanya mengembalikan data dan itupun hanya ditampilkandi cell dimana dia ditulis.

Dengan permainan VBA dan SpinButton mungkin bisa..

Spinbutton untuk mengatur cell (G1) sbg cell kendali (digit ke berapa yg dianggap pembedanya).

Sedangkan VBA nya untuk mengotomatiskan penulisan data terkelompok - nya

'----------------

Private Sub CommandButton1_Click()   
   ' Haps ( Aug 08, 2011 )
   Dim vDat As Range, vOut As Range   
   Dim i As Long, n As Long, Pos As Integer
   Set vDat = Cells(1).CurrentRegion
   Set vOut = Cells(1, 3)   
   Pos = Len(vDat(1, 1)) - Me.SpinButton1.Value + 1
   vOut.EntireColumn.ClearContents   n = 1

   For i = 1 To vDat.Rows.Count
      vOut(n, 1) = vDat(i, 1)      n = n + 1
      If Not Mid(vDat(i + 1, 1), Pos, 1) = Mid(vDat(i, 1), Pos, 1) _
         Then n = n + 1
   Next iEnd Sub

'----------------


cara memainkannya:
1. klik Spinbutton untuk mengubah angka di cell G1

angka ini akan dipakai sebagai penunjuk [POSISI DIGIT KE X] pada data di kolom A
hanya saja dihitungnya dari belakang (dari kanan)

2. Klik Tombol OK

Sebelum makro menyusun data yg dikelompokkan berdasarkan perbedaan nilaipada
digit Ke X tadi, dia akan menghapus dulu data di kolom C

Mohon maaf kalau tidak berkenan dengan adanya pemrograman VBA; dihapus saja..

GHOZI 08082011 Mengelompokkan Deret Kode.xlsx (27K) Download Attachment
Kid
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [belajar-excel] Mengelompokkan Deret Kode

Kid
Administrator
In reply to this post by Kid
Asumsi, semua kode adalah angka.
Coba juga dengan array formula :
=IF(LEN(D1)>7,--$A$2,IF(LEN(D1)>1,MIN(IF((LEFT($A$2:$A$73,*5*)=LEFT(D1,*5*
))*(--$A$2:$A$73>D1),--$A$2:$A$73)),MIN(IF(--$A$2:$A$73>D1+MAX(D$1:D1),--$A$2:$A$73))))

Misal, grup ingin disusun berdasar jumlah karakter pertama yang dinamis.
Buat sebuah data validation list disuatu cell (misal F1) yang isinya
angka-angka jumlah karakter.
Kemudian ganti yang diwarna biru dengan alamat cell F1 (absolut references).
Menjadi :
=IF(LEN(D1)>7,--$A$2,IF(LEN(D1)>1,MIN(IF((LEFT($A$2:$A$73,*$F$1*)=LEFT(D1,*
$F$1*
))*(--$A$2:$A$73>D1),--$A$2:$A$73)),MIN(IF(--$A$2:$A$73>D1+MAX(D$1:D1),--$A$2:$A$73))))

Karena formula adalah array formula, tulis formula lalu akhiri dengan
menekan Ctrl Shift Enter.
Copy formula ke bawah, sampai bertemu angka 0 berurutan pada dua baris
berimpitan.

Regards.
Kid.

2011/8/8 Mr. Kid <[hidden email]>

> Hai Andri,
>
> Coba pakai Pivot Table.
> Langkahnya :
> 1. Tambahkan header tabel
> 2. buat 1 kolom yang berisi nilai grup (misal headernya diberi nama 'Grup'
> di cell B1, sedangkan data yang di kolom A diberi nama data), dan B2
> menggunakan formula :
> =int( a2/100 )
> 3. copy formula di B2 ke seluruh baris data.
> 4. blok tabel (dari a1 sampai b baris terkahir data)
> 5. (optional) buat nama range dengan klik kanan pilih name a range, misal
> diberi nama mydata
> 6. insert -> pivot table
> 7. pada data source diisi mydata atau blok range data (termasuk header)
> lalu ok
> 8. letakkan (drag) field grup dibagian row
> 9. letakkan juga field data dibagian row juga, dibawah field grup
> 10. klik panah kecil -> field setting -> layout -> centang insert blank
> line after each item label -> ok
>
> Jika data diubah-ubah, termasuk tambah atau susut recordnya, ubah refers to
> di nama range (mydata) dengan sebuah formula offset
> =Offset( $a$1,0,0,counta( $a:$a ) )
>
> Setelah itu, tambah dan kurangi data sesukanya dilanjutkan dengan tekan
> refresh data atau refresh all.
>
> Kid.
>
>
> 2011/8/8 andri apriyadi <[hidden email]>
>
>> **
>>
>>
>> Dear para Pakar Excel,
>>
>> Saya punya deretan Kode yang panjang dalam satu kolom dan berharap ada
>> rumus untuk mengotomatisasi agar deret Kode ini dapat berkelompok dengan ada
>> spasi (blank cell) yang memisahkan antara kelompok kode yang satu dengan
>> kelompok kode lainnya.
>> (Contoh terlampir).
>>
>> Mohon bantuan para master, bagaimana rumus/formulanya agar kode-kode ini
>> berderet sesuai kelompok yang saya inginkan?
>>
>> Thx before
>>
>> Regards
>>
>> Andree
>>
>>  
>>
>
>

re-Mengelompokkan Deret Kode.xlsx (31K) Download Attachment
Loading...