Menggabung teks banyak baris berkriteria

---

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 :

1. sumber data sudah terurut berdasar pembentuk kelompok (dalam hal ini kolom produk)
2. 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 :

1. 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)

     Pada sebuah cell tersebut (cell C6) terjadi proses komputasi secara bertahap yang dapat dijabarkan seperti gambar berikut ini :
     

     

2. 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)

     Pada sebuah cell tersebut (cell F15) terjadi proses komputasi secara bertahap yang dapat dijabarkan seperti gambar berikut ini :
     

     

---

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 :

1. 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

     Pada baris kerja di baris 2,

     • 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).

   • 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

     Pada sebuah cell tersebut (cell C6) terjadi proses komputasi secara bertahap yang dapat dijabarkan seperti gambar berikut ini :
     

     

2. 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)

     Pada hasil di baris 13,

     • 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).

   • 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)

     Pada sebuah cell tersebut (cell F15) terjadi proses komputasi secara bertahap yang dapat dijabarkan seperti gambar berikut ini :
     

     

---

Closing :

Beberapa catatan tentang formula di nomor 1 yang berbunyi :

=IFERROR(LOOKUP(2,1/(A$1:A1=A2),C$1:C1)&", ","")&B2

adalah sebagai berikut :

• Rujukan ke suatu range pada bagian A$1:A1 ataupun C$1:C1
  
  1. 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.
  2. 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.
  3. Itu sebabnya, pada cell C6, rujukan akan berubah menjadi A$1:A5 ataupun C$1:C5.
• Fungsi IFERROR
  
  1. 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.
  2. Fungsi ini memiliki syntax :
     
     IFERROR( formula_utama , nilai_pengganti_error_value )
     Contohnya adalah pada formula di atas di bagian yang berbunyi :
     
     =IFERROR( LOOKUP(2,1/(A$1:A1=A2),C$1:C1)&", " ,"")
     dengan bunyi :
     
     • formula_utama berbunyi LOOKUP(2,1/(A$1:A1=A2),C$1:C1)&", "
     • nilai_pengganti_error_value berbunyi ""
  3. Fungsi ini mulai muncul di Excel 2007
  4. Pada Excel 2003 ke bawah, fungsi ini dapat digantikan dengan formula yang berbentuk :
     
     IF( ISERROR( formula_utama ) , nilai_pengganti_error_value , formula_utama )
     sehingga bagian IFERROR di atas bisa diubah menjadi :
     
     IF( ISERROR( LOOKUP(2,1/(A$1:A1=A2),C$1:C1)&", " ) , "" , LOOKUP(2,1/(A$1:A1=A2),C$1:C1)&", " )
  5. 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
  
  1. Perlu pengubahan pada bagian yang berbunyi :
     
     1/(A$1:A1=A2)
     yang masih berisi ekspresi untuk kondisi berdasar satu kriteria, yang berarti akan menyusun satu kondisi saja, yaitu kondisi berbunyi A$1:A1=A2.
  2. Susunan dasar penyusunan kriteria pada bagian tersebut adalah sebagai berikut :
     
     1 / ( ( kondisi_1 ) * ( kondisi_2 ) * ... * ( kondisi_N ) )
     dengan tanda * (setara logika AND), yang dapat diganti menjadi tanda + (setara logika OR) bila dibutuhkan.

---

File(s) :

Gabung teks banyak baris

---

Coretan terkait :

• LookUp ke kiri atau ke kanan untuk satu kriteria
• LookUp banyak kriteria

---