Konten
Saya sudah menulis tentang bagaimana Anda dapat dengan cepat merekatkan teks dari beberapa sel menjadi satu dan, sebaliknya, mengurai string teks panjang menjadi komponen. Sekarang mari kita lihat tugas yang dekat, tetapi sedikit lebih rumit – bagaimana merekatkan teks dari beberapa sel ketika kondisi tertentu terpenuhi.
Katakanlah kita memiliki database pelanggan, di mana satu nama perusahaan dapat berkorespondensi dengan beberapa email karyawan yang berbeda. Tugas kita adalah mengumpulkan semua alamat berdasarkan nama perusahaan dan menggabungkannya (dipisahkan dengan koma atau titik koma) untuk membuat, misalnya, milis untuk pelanggan, yaitu mendapatkan output seperti:
Dengan kata lain, kita membutuhkan alat yang akan merekatkan (menghubungkan) teks sesuai dengan kondisi – analog dari fungsi SUMMELI (SUMIF), tetapi untuk teks.
Metode 0. Rumus
Tidak terlalu elegan, tetapi cara termudah. Anda dapat menulis rumus sederhana yang akan memeriksa apakah perusahaan di baris berikutnya berbeda dari yang sebelumnya. Jika tidak berbeda, maka rekatkan alamat selanjutnya dipisahkan dengan koma. Jika berbeda, maka kami "mengatur ulang" akumulasi, mulai lagi:
Kerugian dari pendekatan ini jelas: dari semua sel kolom tambahan yang diperoleh, kita hanya perlu yang terakhir untuk setiap perusahaan (kuning). Jika daftarnya besar, maka untuk memilihnya dengan cepat, Anda harus menambahkan kolom lain menggunakan fungsi DLSTR (LEN), memeriksa panjang akumulasi string:
Sekarang Anda dapat menyaring yang satu dan menyalin alamat yang diperlukan untuk digunakan lebih lanjut.
Metode 1. Makrofungsi perekatan dengan satu syarat
Jika daftar asli tidak diurutkan berdasarkan perusahaan, maka rumus sederhana di atas tidak berfungsi, tetapi Anda dapat dengan mudah menyiasatinya dengan fungsi kustom kecil di VBA. Buka Editor Visual Basic dengan menekan pintasan keyboard Alt + F11 atau menggunakan tombol Visual Basic tab pembangun (Pengembang). Di jendela yang terbuka, masukkan modul kosong baru melalui menu Sisipkan – Modul dan salin teks fungsi kami di sana:
Fungsi MergeIf(TextRange As Range, SearchRange As Range, Condition As String) Dim Demeter As String, i As Long Delimeter = ", " pengeleman tidak sama satu sama lain - kita keluar dengan error If SearchRange.Count <> TextRange.Count Kemudian MergeIf = CVErr(xlErrRef) Keluar Fungsi Akhir Jika 'menelusuri semua sel, periksa kondisi dan kumpulkan teks dalam variabel OutText For i = 1 To SearchRange. Cells.Count If SearchRange.Cells(i) Like Condition Then OutText = OutText & TextRange.Cells(i) & Delimeter Next i 'menampilkan hasil tanpa pembatas terakhir MergeIf = Left(OutText, Len(OutText) - Len(Delimeter)) End fungsi
Jika Anda sekarang kembali ke Microsoft Excel, maka dalam daftar fungsi (tombol fx di bilah rumus atau tab Rumus – Sisipkan Fungsi) adalah mungkin untuk menemukan fungsi kami GabungkanJika dalam kategori Ditetapkan pengguna (Ditetapkan pengguna). Argumen untuk fungsi tersebut adalah sebagai berikut:
Metode 2. Menggabungkan teks dengan kondisi yang tidak tepat
Jika kita mengganti karakter pertama di baris ke-13 makro kita = ke operator pertandingan perkiraan Seperti, maka dimungkinkan untuk melakukan pengeleman dengan kecocokan yang tidak tepat dari data awal dengan kriteria pemilihan. Misalnya, jika nama perusahaan dapat ditulis dalam varian yang berbeda, maka kami dapat memeriksa dan mengumpulkan semuanya dengan satu fungsi:
Wildcard standar didukung:
- asterisk (*) – menunjukkan sejumlah karakter apa pun (termasuk ketidakhadirannya)
- tanda tanya (?) – singkatan dari sembarang karakter tunggal
- tanda pound (#) – singkatan dari satu digit (0-9)
Secara default, operator Suka peka huruf besar/kecil, yaitu memahami, misalnya, "Orion" dan "orion" sebagai perusahaan yang berbeda. Untuk mengabaikan huruf besar/kecil, Anda dapat menambahkan baris di awal modul di editor Visual Basic Opsi Bandingkan Teks, yang akan mengubah Suka menjadi tidak peka huruf besar-kecil.
Dengan cara ini, Anda dapat membuat topeng yang sangat kompleks untuk memeriksa kondisi, misalnya:
- ?1##??777RUS – pemilihan semua plat nomor wilayah 777, dimulai dengan 1
- LLC* – semua perusahaan yang namanya dimulai dengan LLC
- ##7## – semua produk dengan kode digital lima digit, di mana digit ketiga adalah 7
- ????? – semua nama dari lima huruf, dll.
Metode 3. Fungsi makro untuk menempelkan teks dalam dua kondisi
Dalam pekerjaan mungkin ada masalah ketika Anda perlu menghubungkan teks lebih dari satu syarat. Misalnya, mari kita bayangkan bahwa di tabel sebelumnya, satu kolom lagi dengan kota ditambahkan, dan perekatan harus dilakukan tidak hanya untuk perusahaan tertentu, tetapi juga untuk kota tertentu. Dalam hal ini, fungsi kita harus sedikit dimodernisasi dengan menambahkan pemeriksaan rentang lain ke dalamnya:
Fungsi MergeIfs(TextRange Sebagai Range, SearchRange1 Sebagai Range, Condition1 Sebagai String, SearchRange2 Sebagai Range, Condition2 Sebagai String) Pemisah Dim Sebagai String, i Sebagai Pembatas Panjang = ", " 'karakter pembatas (dapat diganti dengan spasi atau ; dll.) e.) 'jika rentang validasi dan pengeleman tidak sama satu sama lain, keluar dengan kesalahan Jika SearchRange1.Count <> TextRange.Count Atau SearchRange2.Count <> TextRange.Count Kemudian MergeIfs = CVErr(xlErrRef) Keluar Fungsi Akhir Jika 'telusuri semua sel, periksa semua kondisi dan kumpulkan teks ke dalam variabel OutText For i = 1 To SearchRange1.Cells.Count If SearchRange1.Cells(i) = Condition1 And SearchRange2.Cells(i) = Condition2 Then OutText = OutText & TextRange.Cells(i) & Delimeter End If Next i 'menampilkan hasil tanpa pembatas terakhir MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) End Function
Ini akan diterapkan dengan cara yang persis sama – hanya argumen yang sekarang perlu lebih ditentukan:
Metode 4. Mengelompokkan dan menempelkan di Power Query
Anda dapat memecahkan masalah tanpa pemrograman di VBA, jika Anda menggunakan add-in Power Query gratis. Untuk Excel 2010-2013 bisa diunduh di sini, dan di Excel 2016 sudah bawaan. Urutan tindakan akan menjadi sebagai berikut:
Power Query tidak tahu cara bekerja dengan tabel biasa, jadi langkah pertama adalah mengubah tabel kita menjadi tabel "pintar". Untuk melakukan ini, pilih dan tekan kombinasi Ctrl+T atau pilih dari tab Beranda – Format sebagai tabel (Beranda — Format sebagai Tabel). Pada tab yang kemudian muncul Pembina (Rancangan) anda dapat mengatur nama tabel (saya meninggalkan standar Tabel 1):
Sekarang mari muat tabel kita ke add-in Power Query. Untuk melakukan ini, pada tab Data (jika Anda memiliki Excel 2016) atau pada tab Power Query (jika Anda memiliki Excel 2010-2013) klik Dari meja (Data — Dari Tabel):
Di jendela editor kueri yang terbuka, pilih kolom dengan mengklik header Perusahaan dan tekan tombol di atas Kelompok (Kelompokkan Berdasarkan). Masukkan nama kolom baru dan jenis operasi dalam pengelompokan – Semua lini (Semua Baris):
Klik OK dan kami mendapatkan tabel mini nilai yang dikelompokkan untuk setiap perusahaan. Isi tabel terlihat jelas jika Anda mengklik kiri pada latar belakang putih sel (bukan pada teks!) di kolom yang dihasilkan:
Sekarang mari tambahkan satu kolom lagi, di mana, dengan menggunakan fungsinya, kita merekatkan isi kolom Alamat di setiap tabel mini, dipisahkan dengan koma. Untuk melakukan ini, pada tab Tambahkan kolom kita tekan Kolom khusus (Tambahkan kolom — Kolom khusus) dan di jendela yang muncul, masukkan nama kolom baru dan rumus kopling dalam bahasa M yang ada di dalam Power Query:
Perhatikan bahwa semua M-fungsi peka huruf besar-kecil (tidak seperti Excel). Setelah mengklik OK kami mendapatkan kolom baru dengan alamat terpaku:
Tetap menghapus kolom yang sudah tidak perlu TabelAlamat (klik kanan pada judul) Hapus kolom) dan unggah hasilnya ke lembar dengan mengklik tab Beranda — Tutup dan unduh (Beranda — Tutup dan muat):
Nuansa penting: Tidak seperti metode (fungsi) sebelumnya, tabel dari Power Query tidak diperbarui secara otomatis. Jika di masa mendatang akan ada perubahan pada data sumber, maka Anda perlu mengklik kanan di mana saja di tabel hasil dan memilih perintah Perbarui & Simpan (Menyegarkan).
- Cara membagi string teks panjang menjadi beberapa bagian
- Beberapa cara untuk merekatkan teks dari sel yang berbeda menjadi satu
- Menggunakan operator Suka untuk menguji teks dengan topeng