Muatan :
- Kilasan tentang menggabung teks dari banyak baris
- Konsep tentang menggabung teks dari banyak baris
- Contoh kasus data terurut berdasar kriteria penggabungan teks
- Contoh kasus data tidak terurut
Kilasan
Terkadang, sebuah layout output menghendaki untuk menampilkan seluruh nama-nama item dalam sebuah grup data sebagai sebuah teks. Sedangkan sumber datanya berupa sebuah tabel berorientasi vertikal. Misalnya seperti menampilkan nama project disebuah cell, kemudian di cell sebelah kanannya berisi seluruh nama personel yang mengerjakan project tersebut. Padahal sumber data yang ada di sheet lain berupa daftar nama-nama personel beserta nama-nama project yang sedang dikerjakan setiap personel tersebut.Pembahasan kali ini adalah tentang penyederhanaan kasus-kasus yang seperti ini.
Konsep tentang pengkondisian berdasar rentang nilai
Penggabungan sebuah teks dengan teks lain bisa menggunakan fungsi Concatenate atau memanfaatkan operator ampersand ( & ). Seperti formula berbunyi =A2 & B2 atau =Concatenate(A2,B2) yang akan menggabungkan teks di cell A2 dengan teks di cell B2. Sedangkan formula berbunyi =A1 & A2 atau =Concatenate(A1,A2) akan menggabungkan teks di cell A1 dengan teks di cell A2.Proses menggabungkan teks dari suatu data berorientasi vertikal adalah proses menggabungkan teks disebuah baris dengan hasil penggabungan dibaris sebelumnya. Jadi, pada baris terbawah akan didapat penggabungan dari seluruh teks yang ada di baris-baris data sebelumnya. Pada umumnya, di antara setiap teks dari baris data yang berbeda diberi teks pemisah yang sering disebut delimiter. Teks delimiter ini bisa berupa karakter koma, titik koma, dan sebagainya.
Ketika data teks banyak baris yang akan digabungkan adalah suatu data yang membentuk suatu kelompok-kelompok data berdasar kolom tertentu, maka proses penggabungan teks dengan formula seperti yang dicontohkan di atas, membutuhkan pemeriksaan terhadap kesamaan pembentuk kelompok tersebut. Kolom pembentuk kelompok inilah yang menjadi kriteria dalam formula pengkondisian yang digunakan. Data yang sudah terurut berdasar kolom pembentuk kelompok data akan membuat formula penggabungan teks menjadi lebih sederhana. Hal ini disebabkan karena bagian pengkondisian dalam formula berupa membandingkan nilai kolom pembentuk kelompok di baris itu dengan baris sebelumnya. Untuk data yang tidak memungkinkan untuk selalu dipelihara terurut berdasar kolom pembentuk kelompok, dibutuhkan proses pencarian hasil akhir penggabungan teks dari sebuah kelompok berdasar kolom pembentuk kelompok tersebut.
Contoh kasus tentang data produk beserta negara-negara tujuan penjualannya akan ditampilkan dalam sebuah output berupa nama produk dan teks yang berisi kode-kode negara tujuan penjualan. Tabel sumber data (sisi kiri) beserta layout output yang diinginkan (sisi kanan) adalah seperti gambar berikut :
Kasus akan dibahas untuk 2 (dua) kondisi, yaitu :
- sumber data sudah terurut berdasar pembentuk kelompok (dalam hal ini kolom produk)
- sumber data belum terurut berdasar pembentuk kelompok (dalam hal ini kolom produk)
Contoh kasus data terurut berdasar kriteria penggabungan teks
Pada kasus ini, kolom pembentuk kelompok adalah kolom bernama produk. Seluruh kode negara yang ada di kolom negara, yang memiliki nilai kelompok produk yang sama, akan digabungkan menjadi sebuah teks. Kolom produk yang menjadi kolom kriteria pembentuk kelompok data telah terurut. Gambar sumber data yang sudah terurut (sisi kiri) beserta target hasilnya (sisi kanan) adalah sebagai berikut :Langkah-langkah penyusunan output tersebut adalah sebagai berikut :
- Membuat kolom bantu berisi formula penggabung teks antar baris di sisi sumber data
- Kolom bantu diletakkan pada kolom C dengan header bernama helper di cell C1
- Pada cell C2 diberi formula berbunyi :
- =IF(A2=A1,C1&", "&B2,B2)
Pada baris kerja di baris 2, jika (IF) produk di baris 2 (A2) adalah sama dengan (=) produk di baris 1 (A1), maka ambil hasil penggabungan di cell C1 yang kemudian digabungkan dengan teks pemisah berupa koma yang diikuti spasi (&", ") diikuti dengan kode negara di baris 2 (&B2). Sedangkan jika kondisi membandingkan produk tadi tidak terpenuhi, maka ambil kode negara yang ada di baris 2 (B2).
- Copy formula di C2 ke range C3 sampai C7, sehingga tampak seperti gambar berikut :
Pada cell C6 berisi formula berbunyi :
- =IF(A6=A5,C5&", "&B6,B6)
- Menyusun formula pengambil hasil penggabungan teks
- Hasil penggabungan teks adalah nilai-nilai data yang ada di kolom helper pada sumber data
- Nilai hasil yang akan digabung adalah record terakhir dari sebuah produk dan dapat dilakukan oleh fungsi seperti LookUp
- Kriteria produk yang akan diambil teks hasil penggabungan adalah nilai kolom produk yang ada di tabel hasil
- Formula di tabel hasil untuk cell F13 berbunyi :
- =LOOKUP(E13,$A$2:$A$7,$C$2:$C$7)
Pada hasil di baris 13, lakukan lookup data untuk mengambil yang terakhir (LOOKUP) yang memenuhi nilai lookup berupa nilai produk di baris ini (E13) ke daftar produk di sumber data ($A$2:$A$7), kemudian ambil data hasil dari daftar gabungan teks yang ada di kolom helper ($C$2:$C$7).
- Copy formula yang ada di cell F13 ke cell hasil lainnya, yaitu cell F14 sampai F15, sehingga tampak seperti gambar berikut ini :
Pada hasil di cell F15 berisi formula berbunyi :
- =LOOKUP(E15,$A$2:$A$7,$C$2:$C$7)
Contoh kasus data tidak terurut
Kali ini, sumber data pada contoh kasus sebelumnya dalam keadaan tidak berurutan berdasar kolom pembentuk kelompok yang bernama produk. Hasil output yang diinginkan masih sama seperti contoh kasus sebelumnya. Gambar sumber data (sisi kiri) dan tabel hasil (sisi kanan) untuk keadaan sumber data tak terurut ini adalah sebagai berikut :Langkah-langkah penyusunan output untuk kondisi seperti ini adalah sebagai berikut :
- Membuat kolom bantu berisi formula penggabung teks antar baris di sisi sumber data
- Kolom bantu diletakkan pada kolom C dengan header bernama helper di cell C1
- Hasil penggabungan terakhir yang sama nilai produknya dengan baris aktif dikomputasi dengan fungsi LookUp
- Fungsi LookUp bisa menghasilkan error value #N/A jika tidak menemukan produk yang dicari dapa baris-baris sebelumnya, sehingga dibutuhkan perlakuan untuk mengantisipasi munculnya error value
- Area range yang diproses oleh fungsi LookUp merujuk ke satu baris sebelum baris aktif sampai baris header
- Pada cell C2 diberi formula berbunyi :
- =IFERROR(LOOKUP(2,1/(A$1:A1=A2),C$1:C1)&", ","")&B2
- jika proses pencarian hasil gabungan di baris-baris sebelumnya menghasilkan error value (IFERROR), maka ganti dengan Nullstring ("").
- Hasil gabungan dibaris-baris sebelumnya berdasar produk di baris 2 dicari dengan me-lookup (LOOKUP) nilai 2 (2) pada sebuah daftar hasil komputasi terhadap kriteria.
- Komputasi tersebut adalah membagi (/) nilai 1 dengan hasil perbandingan setiap produk dibaris sebelumnya (A$1:A1) adalah sama dengan (=) produk di baris 2 (A2), yang berbunyi 1/(A$1:A1=A2), yang kemudian mengambil nilai kolom helper yang sesuai nilai lookup yang terakhir ditemukan (C$1:C1).
- Kemudian hasil lookup digabungkan dengan teks pemisah antar item data yang berupa karakter koma diikuti oleh spasi (&", ").
- Hasil pemeriksaan proses pencarian gabungan teks terakhir yang sesuai tersebut, digabungkan dengan kode negara dibaris aktif, yaitu di baris 2 (&B2).
Pada baris kerja di baris 2,
- Copy formula di C2 ke range C3 sampai C7, sehingga tampak seperti gambar berikut :
Pada cell C6 berisi formula berbunyi :
- =IFERROR(LOOKUP(2,1/(A$1:A5=A6),C$1:C5)&", ","")&B6
- Menyusun formula pengambil hasil penggabungan teks
- Hasil penggabungan teks adalah nilai-nilai data yang ada di kolom helper pada sumber data
- Nilai hasil yang akan digabung adalah record terakhir dari sebuah produk dan dapat dilakukan oleh fungsi seperti LookUp
- Kriteria produk yang akan diambil teks hasil penggabungan adalah nilai kolom produk yang ada di tabel hasil
- Formula di tabel hasil untuk cell F13 berbunyi :
- =LOOKUP(2,1/($A$2:$A$7=E13),$C$2:$C$7)
- lakukan lookup data untuk mengambil yang terakhir (LOOKUP) yang memenuhi nilai lookup berupa nilai 2 pada sebuah daftar hasil komputasi.
- Komputasi penyusun daftar adalah pembagian (/) nilai 1 dengan perbandingan setiap produk di sumber data ($A$2:$A$7) dengan nilai produk baris ini (E13), yang berupa bagian berbunyi 1/($A$2:$A$7=E13).
- Nilai hasil lookup yang diambil adalah yang terakhir ditemukan yang sesuai nilai lookup dari daftar berupa nilai-nilai di sumber data pada kolom helper ($C$2:$C$7).
Pada hasil di baris 13,
- Copy formula yang ada di cell F13 ke cell hasil lainnya, yaitu cell F14 sampai F15, sehingga tampak seperti gambar berikut ini :
Pada hasil di cell F15 berisi formula berbunyi :
- =LOOKUP(2,1/($A$2:$A$7=E15),$C$2:$C$7)
Closing :
Beberapa catatan tentang formula di nomor 1 yang berbunyi :- =IFERROR(LOOKUP(2,1/(A$1:A1=A2),C$1:C1)&", ","")&B2
- Rujukan ke suatu range pada bagian A$1:A1 ataupun C$1:C1
- Peletakan tanda absolute reference ($)) pada sisi baris saja di range awal (range di kiri karakter titik dua) akan membuat range awal yang dirujuk tetap di baris 1.
- Sedangkan pada range akhir (range di kanan karakter titik dua) yang tidak diberi absolute reference pada sisi barisnya, akan membuat rujukan baris berubah relatif mengikuti cell aktif ketika formula di-copy ke baris selanjutnya.
- Itu sebabnya, pada cell C6, rujukan akan berubah menjadi A$1:A5 ataupun C$1:C5.
- Fungsi IFERROR
- Fungsi ini digunakan untuk mengantisipasi munculnya error value dari suatu ekspresi formula, sehingga dapat mengganti nilai error value yang muncul tersebut dengan suatu nilai lain yang dikehendaki.
- Fungsi ini memiliki syntax :
- IFERROR( formula_utama , nilai_pengganti_error_value )
- =IFERROR( LOOKUP(2,1/(A$1:A1=A2),C$1:C1)&", " ,"")
- formula_utama berbunyi LOOKUP(2,1/(A$1:A1=A2),C$1:C1)&", "
- nilai_pengganti_error_value berbunyi ""
- Fungsi ini mulai muncul di Excel 2007
- Pada Excel 2003 ke bawah, fungsi ini dapat digantikan dengan formula yang berbentuk :
- IF( ISERROR( formula_utama ) , nilai_pengganti_error_value , formula_utama )
- IF( ISERROR( LOOKUP(2,1/(A$1:A1=A2),C$1:C1)&", " ) , "" , LOOKUP(2,1/(A$1:A1=A2),C$1:C1)&", " )
- Antisipasi error value bisa juga dengan memanfaatkan fungsi IF yang diberi kondisi memanfaatkan fungsi-fungsi seperti CountIF (jika berdasar satu kriteria), CountIFs (jika berdasar banyak kriteria), IsNumber(Match()), SumProduct, dan masih banyak lagi.
- Pada kondisi pembentuk kelompok disusun berdasar banyak kolom
- Perlu pengubahan pada bagian yang berbunyi :
- 1/(A$1:A1=A2)
- Susunan dasar penyusunan kriteria pada bagian tersebut adalah sebagai berikut :
- 1 / ( ( kondisi_1 ) * ( kondisi_2 ) * ... * ( kondisi_N ) )
- Perlu pengubahan pada bagian yang berbunyi :
mas tolong bahasanya disederhanakan donk<< bagi pemula kaya saya susah mencerna kalimat kalimatnya,,
BalasHapusmakasih
Boleh tahu bagian mana yang kira-kira sulit dicerna ?
HapusSupaya lebih pas ngubah kalimatnya.
Gan mohon bantuannya buatin rumus ya???
BalasHapussaya punya data misal baris 1 (Kolom A-E) : Merah, Biru, Kuning, Hitam, Cokelat, dan pada baris 2 (Kolom A-E) : +,-, ,+,+.
Bagaimana cara untuk menyatukan baris kedua sehingga hasilnya misal pada cel A3 : Merah + Biru - Hitam + Cokelat +.
Cel C2 tidak muncul karena data yang kosong. Makasih...
A3 = if( len(a2), a2 , "" ) & if( len(b2), " " & b1 & " " & b2 , "" ) & if( len(c2), " " & c1 & " " & c2 , "" ) & dst
Hapus:)
Beberapa hari mencari, ketemu juga akhirnya
HapusTq Mr.Kidh
Alhamdulillah
HapusMoga-moga bermanfaat ya...
minta bantuanya gan,, nt saya isikan pulsa 25 rb hehe..
BalasHapusexcel 2007 misal :
kode nama qty ?? Jumlah dari qty jika kode sama
baris 1 0001 sabun giv 10 ??
baris 2 0002 kecap bangau 15 ??
baris3 0003 rinso 30 ??
baris4 0001 sabun giv 17 ??
baris5 0003 rinso 18 ??
yang saya tanyakan..
jika kode / nama sama maka qty menjumlahkan hasilnya..
Hai Amanah Swalayan,
HapusSepertinya kasus Anda bukan tentang menggabung teks seperti dalam coretan ini, tetapi berupa kalkulasi Sum berkriteria. Fungsi SumIF bisa digunakan dalam kasus Anda.
Data : Range A1:C6 dengan header di A1:C1 bernama {"kode","nama","qty"}
Cara 1 :
dengan pivot table (bisa lihat coretan tentang pivot table yang dimulai dari :
excel-mr-kid.blogspot.co.id/2013/04/pivot-table-1-kenalan.html
Cara 2 :
dengan formula sebagai berikut :
Hasil : Range F1:H4 dengan header di F1:H1 bernama {"kode","nama","total qty"}
1. Formula pembentuk daftar kode (kolom F) yang unique bisa dilihat di coretan :
http://excel-mr-kid.blogspot.co.id/2011/03/formula-penyusun-data-unique-dan.html
bunyi array formula di cells F2 : (hati-hati dengan penempatan tanda $)
=Index($A$2:$A$6,Match(0,CountIF(F$1:F1,$A$2:$A$6),0))
2. Formula pengambil teks nama berdasar kode diletakkan di G2 :
=vLookUp(F2,$A$2:$B$6,2,0)
3. Formula kalkulasi total qty berdasar kode diletakkan di H2 :
=SumIF($A$2:$A$6,F2,$C$2:$C$6)
Hal yang perlu ditambahkan :
1. Penggunaan IFError ketika formula menghasilkan error value di kolom F dan G
2. Mengubah array formula di kolom F bila diinginkan hasilnya terurut Ascending atau Descending dengan menggunakan konsep di coretan yang ada disini :
http://excel-mr-kid.blogspot.co.id/2011/04/formula-penyusun-data-unique-yang.html
Semoga bermanfaat.
Regards,
Kid
Niche Info Admin
BalasHapusDH
BalasHapusMohon bantuanya untuk pembacaan rumus ini di mana terjadi ke bigungan bagi saya dalam membacanya. n saya dapat menggunakanya ke shit yang lain
=IFERROR(IF(E$7="";"NA";IF(E$70;E$7>INDEX(T_EMP[TERMINATION DATE];ROW($B9)-ROW($B$7)));"NE";IF(NOT(ISERROR(MATCH(E$7;L_HOLS;0)));"H";IF(INDEX(L_WKNDVAL;WEEKDAY(E$7;1))=1;"WKND";INDEX(T_LEAVE[STATUS REPORT];SUMPRODUCT(--(T_LEAVE[SETEL POINT]=$B9);--(T_LEAVE[START DATE]<=E$7);--(T_LEAVE[END DATE]>=E$7);ROW(T_LEAVE[STATUS REPORT]))-ROW(T_LEAVE[#Headers])))))));""),
dimana data
Hai Estu,
HapusSaya kira, formula Anda tersebut error dan tidak mungkin bisa di-entry ke dalam cells Excel.
Silakan diperbaiki lebih dulu sampai bisa di-entry ke dalam cells Excel.
Regards,
Kid
Gan mohon bantuannya untuk penggabungan sel langsung terurut.
BalasHapusA1=B, B1=D, C1=A dan D1=C jika empat sel tsb digabungkan maka hasilnya BDAC, bgm formulanya jika hasil penggabungan menjadi ABCD, terimakasih.
hmmm...
Hapusmungkin begini :
=Index(A1:D1,Match(1,Index(Countif(A1:D1,"<="&A1:D1),0),0))
&Index(A1:D1,Match(2,Index(Countif(A1:D1,"<="&A1:D1),0),0))
&Index(A1:D1,Match(3,Index(Countif(A1:D1,"<="&A1:D1),0),0))
&Index(A1:D1,Match(4,Index(Countif(A1:D1,"<="&A1:D1),0),0))
Gan mohon pencerahanya.....
BalasHapusdalam satu kolom terdapat huruf sebagai berikut
baris1 A
baris2 A
baris3 B
baris4 C
baris5 B
pertanyaannya bagaimana formula agar memulai pencarian "A" mulai dari baris ke 5...jika ketemu maka nilai "A"...jika belum ketemu maka akan di cari ke baris ke 4...dan seterusnya cari ke baris sebelumnya sampai berhenti di baris ke 2 dan mengembalikan huruf "A"....( sistem pencarian mulai dari bawah ke atas dan per baris secara otomatis....jika ketemu baru berhenti dan mengembalikan nilai "A")
sebelumnya terima kasih
Hai Amir Huang,
HapusKasus lookup demikian biasanya disebut 'mencari data terakhir berdasar suatu kriteria'.
Fungsi LookUp bisa dimanfaatkan untuk hal ini. Syntax umumnya begini :
=LookUp( 2 , 1/(data_kolom_kriteria=nilai_kriteria) , data_kolom_dibutuhkan_untuk_hasil )
Regards,
Kid
Mohon pencerahan:
BalasHapusSaya punya case untuk
Kolom a misal 1 1 1 2 2
Kolom b misal a b c d e
Saya ingin menggabungkan jadi
Kolom a 1 2
Kolom b 1,b,c d,e
Karena datanya ribuan dan saya tidak bisa menggunakan concatenate... Apakah ada ide?
Hai BDC Crew,
HapusBerikut ini perkiraan susunan data yang Anda sebutkan :
cells A2:A6 berisi 1,1,1,2,2 dengan header di A1 bernama Key1
cells B2:B6 berisi a,b,c,a,d dengan header di B1 bernama Key2
Diinginkan ditempat lain (misal di K1 berisi teks '1 2' dan di L1 berisi teks 'a b c d')
Pada cells C1 diisi teks UKey1 dan D1 diisi teks UKey2. Cells C1 dan D1 adalah header dari kolom bantu.
Pada cells C2 diisi formula (regional setting komputer English) :
=IF(Row()=2,Trim(A2),C1 & IF( IsError( Match( A2,A$1:A1,0 ) )," "&A2,""))
Copy formula C2 ke area range C2:D6.
Di area cells hasil K1 diisi formula :
=Trim( LookUp( "zzz",C:C ) )
Copy formula K1 ke area hasil cells L1.
*** Jika perkiraan susunan data beserta perkiraan nilai hasil di atas tidak sesuai dengan yang Anda maksud, silakan bergabung ke milis Belajar-Excel di YahooGroups dan menyertakan contoh file :
https://groups.yahoo.com/neo/groups/belajar-excel/info
Regards,
Kid
Iya betul casenya seperti itu ada saran langkahnya pak karena terpotong baru setengah pak.. atau mungkin bisa bantu di share ke willy.zhen@gmail.com terima kasih sekali pak
HapusMaksud dari 'terpotong baru setengah' itu bagaimana ya ?
HapusGan, untuk gabungin text seperti di bawah ini, caranya gimana ya ?
BalasHapushasilnya
1 Budi, Rudi, Wawa
2 Angga, Paijo, Sasa
3 Aldi
Data
1 Budi
2 Angga
2 Paijo
1 Rudi
3 aldi
2 sasa
1 wawa
trims
Hai Makaryo,
HapusCara 1 :
sort data berdasar kolom nomor, kemudian gunakan cara yang ada dalam coretan di atas sana.
Cara 2 :
di baris header, buat header kolom berisi nomor-nomor (1 nomor di 1 kolom). Jadi dengan data tersebut ada 3 kolom dengan header berupa angka 1, angka 2, dan angka 3.
Lalu di cells data baris pertama di kolom angka 1 diisi formula seperti di coretan di atas, tetapi diawali kondisi (IF). Susunan formulanya seperti ini : (header di a1:e1, record mulai baris 2)
c2 = IF( $a1=c$1 , c1 & ", " & $b1 , c1 )
Gabungan teks setiap nomor ada di record terakhir pada kolom dengan header angka-angka yang sesuai.
Cara 3 :
Dengan komputasi array membandingkan setiap nomor, sebagai kunci lookup. Misal header di a1:c1 (kolom c adalah hasil), record mulai baris 2 :
c2 = IFError( LookUp( 2 , 1/(a$1:a1=a2) , c$1:c1 ) , "" ) & ", " & b2
Gabungan teks hasil menggunakan formula c2 bagian lookup, dengan :
- bagian a2 diganti dengan nomor yang akan dicari
- range yang dirujuk adalah seluruh record data di kolom terkait
Misal akan mencari gabungan teks bernomor 3 : (misal hasil di cells h3, record dari baris 2 sampai 9)
h3 =LookUp( 2 , 1/(a$1:a$9=3) , c$1:c$9 )
----------------
Perlakuan tambahan terhadap hasil formula pengambil teks gabungan mungkin diperlukan.
----------------
Regards,
Kid
Mohon pencerahnnya, saya ounya data seperti ini
BalasHapusCell a1 log m100 original
Cell a2 m100
Mau menghasilkan nama yang sama di kedua cell itu yaitu m100. Itu kira kira gimana pak fungsinya? Terima kasih
kalau mengubah koma (,) jadi cell selanjutnya gimana ?
BalasHapusbagaimana merubah angka-angka ini agar tanpa tanda "-" , misal 33-26-100-004-0279-0 biar menjadi 332610000402790 tanpa -
BalasHapus