[belajar-excel] Transpose data berdimensi baris dan kolom yang diinginkan

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

[belajar-excel] Transpose data berdimensi baris dan kolom yang diinginkan

sinurat
Dear para pakar, mohon maaf sering2 nanya, maklum masih awam bgt dan lagi getol2-nya belajar excel :)
Simplifikasi persoalan (data yang sebenarnya terdiri dari kumpulan angkapada ribuan baris dan puluhan kolom) :
Data pada range A3:C10, ingin ditranspose menjadi satu kolom, dan sel-sel yang berisi data tertentu (dalam hal ini "xxxx") tidak diikutsertakan, sehingga hasil akhirnya tampil seperti pada kolom F.  Mohon dibantu..
Salam hormatSinurat - Sumut

Transpose data berdimensi baris dan kolom yang diinginkan.xls (19K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [belajar-excel] Transpose data berdimensi baris dan kolom yang diinginkan

o' Seno
Punten... bukan pakar mohon perkenan ikut belajar-menjawab...

*Pertama: *
Perubahan struktur tabel seperti itu bukan TRANSPOSE murni,  jika transpose
(murni) gambarannya seperti ini
[image: Transpose MURNI.PNG]

*Kedua:*
Salah satu *jalan menuju hasil* seperti yg diinginkan tentunya
bertahap-tahap. (pentahapan mempermudah pemahaman dan pencarian jalannya)
Oleh para pakar (para "formula composser" handal) bisanya "tidak sempat"
menggambarkan TAHAP-TAHAP tsb, tetapi langsung menyajikan 1 formula
mandiri, kadang bersifat array formula.
Di dalam formula itu terlibat banyak KOMPONEN formula dan berbagai fungsi
 (ini sering disebut Mega Formula)  yg mengagumkan.

Jika kita ingin belajar, tentunya harus mengurai Mega Formula tsb menjadi
komponen demi komponen, tetapi itupun (jika kurang sobar..) kadang sulit
dan kita gagal mengurainya.
(Dlm praktek pembuatan formula, sebenarnya prosesnya = membuat komponen2
dulu baru kemudian di GABUNG menjadi Mega Formula.)

Berikut ini disajikan *6 tahap* dimana tahap terakhir berhasil  menyajikan
data yg sesuai dengan kehendak pak Sinurat.
[image: 6 tahap menuju Keinginan Pak Sin.PNG]

Rumus tiap tahap
Tahap1:  =MOD(ROW(),3)+1
Tahap2:  =FLOOR(ROW(),3)/3
Tahap3:  =INDEX($A$3:$C$10,I3,H3)
Tahap4:  =IF(J3:J26<>"xxxx",ROW(1:24))
Tahap5:  =SMALL(K3:K26,ROW(1:24))
Tahap6:  =IF(ISNUMBER(L3), INDEX($J$3:$J$26,L3), "")

Tahap terakhir terlihat sederhana karena dibantu oleh kolom2 lain.
Untuk membuat 1 formula mandiri bila memungkinkan) , rumus tahap6 itu tetap
kita pakai tetapi bagian-bagian yg merujuk kolom-bantu, kita ganti dengan
TEXT-FORMULA yg ada di kolom-bantu tsb

Dlm workbook terlampir kita praktekkan juga *penyelesaian dengan 2
Kolom_Bantu*

Rumus KolomBantu_1
konversi 3 kolom menjadi 1 kolom
  =INDEX(A3:C10,FLOOR(ROW(),3)/3,MOD(ROW(),3)+1)

Rumus KolomBantu_2
mencari nomor index ex tabel hasil konversi yg sudah difilter
  =SMALL(IF(INDEX(A3:C10, FLOOR(ROW(),3)/3, MOD(ROW(),3)+1)<>"xxxx",
ROW(1:24)), ROW(1:24))

Rumus Hasil Akhir
meLookUp data ex KolomBantu_1, dengan IndexBaris yg sudah difilter
(kolomBantu_2
  =IF(ISNUMBER(S3),INDEX($R$3:$R$26,S3),"")

Jalan paling logis untuk memahami, tentunya dengan membaca HELP tentang
semua FUNGSI yg dilibatkan dlm formula-formula tsb di atas....  (FLOOR,
MOD, IF, ROW, INDEX, SMALL, ISNUMBER)
Jika dlm membaca HELP tersandung hal yg menyulitkan pemahamannya, barulah
bertanya ke milis..

CMIIW
dan mohon koreksi bila ada yg masih mengsle...

*o'Seno*




*2012/2/14 Imam Sinurat <[hidden email]>
> Dear para pakar, mohon maaf sering2 nanya, maklum masih awam bgt dan lagi
getol2-nya belajar excel :)
> Simplifikasi persoalan (data yang sebenarnya terdiri dari kumpulan angka
> pada ribuan baris dan puluhan kolom) :
> Data pada range A3:C10, ingin ditranspose menjadi satu kolom, dan sel-sel
yang berisi data tertentu *
*> (dalam hal ini "xxxx") tidak diikutsertakan, sehingga hasil akhirnya
tampil seperti pada kolom F.  Mohon dibantu..
> Salam hormat
> Sinurat - Sumut*

6 tahap menuju Keinginan Pak Sin.PNG (56K) Download Attachment
Transpose MURNI.PNG (29K) Download Attachment
Konversi Tabel 3 kolom TO 1 kolom dan MEMFILTERnya (o_Seno).xls (63K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [belajar-excel] Transpose data berdimensi baris dan kolom yang diinginkan

iHaps
In reply to this post by sinurat
Ini mumpung ada kesempatan bernafas, & kebetulan diberi tempat duduk & meja
yg ada PC-nya +koneksi internetnya ( huenake rek..!)..

Dari segi pemikiran algoritma ( cara/jalan menuju selesainya keinginan)
hal spt ini lebih mudah diimplementasikan dengan code VBA

Codingnya Kira kira seperti ini (tidak harus bapak pakai, hanya sebagai
altaernatif lain)

'( ditulis di module sheet - dimana data yg akan diproses berada )

Private Sub CommandButton1_Click()
   ' Transpose_3Col_to_1Col
   ' by iHaps / 14 feb 2012
   '------------------------
   Dim D As Range, H As Range
   Dim c As Integer, r As Long, n As Long
   Set D = Cells(2, 1).CurrentRegion.Offset(1, 0)
   Set H = D(1, D.Columns.Count + 3)
*   For n = 1 To D.Rows.Count*
*      For c = 1 To D.Columns.Count*
*        If Not D(n, c) = "xxxx" Then*
*           If Not D(n, c) = vbNullString Then*
*              r = r + 1: **H(r, 1) = D(n, c)*
*           End If*
*         End If*
*      Next c*
*   Next n*
End Sub

- i -



*2012/2/14 Imam Sinurat <[hidden email]>
> Dear para pakar, mohon maaf sering2 nanya, maklum masih awam bgt dan lagi
getol2-nya belajar excel :)
> Simplifikasi persoalan (data yang sebenarnya terdiri dari kumpulan angka
> pada ribuan baris dan puluhan kolom) :
> Data pada range A3:C10, ingin ditranspose menjadi satu kolom, dan sel-sel
yang berisi data tertentu *
*> (dalam hal ini "xxxx") tidak diikutsertakan, sehingga hasil akhirnya
tampil seperti pada kolom F.  Mohon dibantu..
> Salam hormat
> Sinurat - Sumut*
*
*

Konversi Tabel 3Kolom TO 1Kolom sambil MEMFILTER (Makro - iHaps).xls (43K) Download Attachment