Contents [show]
Saya sudah menulis tentang cara anda boleh melekatkan teks dengan cepat daripada beberapa sel menjadi satu dan, sebaliknya, menghuraikan rentetan teks yang panjang ke dalam komponen. Sekarang mari kita lihat tugasan yang hampir, tetapi lebih kompleks - bagaimana untuk melekatkan teks daripada beberapa sel apabila syarat tertentu dipenuhi.
Katakan kami mempunyai pangkalan data pelanggan, di mana satu nama syarikat boleh sepadan dengan beberapa e-mel berbeza pekerjanya. Tugas kami adalah untuk mengumpulkan semua alamat mengikut nama syarikat dan menggabungkannya (dipisahkan dengan koma atau titik bertitik) untuk membuat, sebagai contoh, senarai mel untuk pelanggan, iaitu mendapatkan output seperti:
Dalam erti kata lain, kita memerlukan alat yang akan melekatkan (menghubungkan) teks mengikut keadaan - analog fungsi SUMMESLI (SUMIF), tetapi untuk teks.
Kaedah 0. Formula
Tidak begitu elegan, tetapi cara yang paling mudah. Anda boleh menulis formula mudah yang akan menyemak sama ada syarikat di baris seterusnya berbeza daripada yang sebelumnya. Jika ia tidak berbeza, kemudian gamkan alamat seterusnya yang dipisahkan dengan koma. Jika ia berbeza, maka kami "set semula" terkumpul, bermula semula:
Kelemahan pendekatan ini adalah jelas: dari semua sel lajur tambahan yang diperoleh, kami hanya memerlukan yang terakhir untuk setiap syarikat (kuning). Jika senarai itu besar, maka untuk memilihnya dengan cepat, anda perlu menambah lajur lain menggunakan fungsi tersebut DLSTR (LEN), menyemak panjang rentetan terkumpul:
Kini anda boleh menapis yang dan menyalin gam alamat yang diperlukan untuk kegunaan selanjutnya.
Kaedah 1. Makrofungsi gam dengan satu syarat
Jika senarai asal tidak diisih mengikut syarikat, maka formula mudah di atas tidak berfungsi, tetapi anda boleh dengan mudah menggunakan fungsi tersuai kecil dalam VBA. Buka Editor Visual Basic dengan menekan pintasan papan kekunci Alt + F11 atau menggunakan butang Visual Basic tab pemaju (Pembangun). Dalam tetingkap yang terbuka, masukkan modul kosong baharu melalui menu Sisipan – Modul dan salin teks fungsi kami di sana:
Function MergeIf(TextRange As Range, SearchRange As Range, Condition As String) Malapkan Delimeter Sebagai String, i As Long Delimeter = ", " gluing tidak sama antara satu sama lain - kami keluar dengan ralat Jika SearchRange.Count <> TextRange.Count Kemudian MergeIf = CVErr(xlErrRef) Keluar Fungsi Tamat Jika 'lalui semua sel, semak keadaan dan kumpulkan teks dalam pembolehubah OutText For i = 1 To SearchRange. Cells.Count If SearchRange.Cells(i) Like Condition Then OutText = OutTeks & TextRange.Cells(i) & Delimeter Seterusnya i 'memaparkan hasil tanpa pembatas terakhir MergeIf = Left(OutTeks, Len(OutText) - Len(Delimeter)) Tamat fungsi
Jika anda kini kembali ke Microsoft Excel, maka dalam senarai fungsi (button fx dalam bar formula atau tab Formula – Fungsi Sisip) ia akan menjadi mungkin untuk mencari fungsi kami GabungJika dalam kategori Pengguna Ditentukan (Ditentukan Pengguna). Argumen untuk fungsi tersebut adalah seperti berikut:
Kaedah 2. Gabungkan teks mengikut keadaan tidak tepat
Jika kita menggantikan aksara pertama dalam baris ke-13 makro kita = kepada pengendali perlawanan anggaran suka, maka adalah mungkin untuk menjalankan pelekatan dengan padanan tidak tepat data awal dengan kriteria pemilihan. Contohnya, jika nama syarikat boleh ditulis dalam varian yang berbeza, maka kami boleh menyemak dan mengumpulkan kesemuanya dengan satu fungsi:
Kad bebas standard disokong:
- asterisk (*) – menandakan sebarang nombor sebarang aksara (termasuk ketiadaannya)
- tanda soal (?) – bermaksud mana-mana aksara tunggal
- tanda paun (#) – bermaksud mana-mana satu digit (0-9)
Secara lalai, pengendali Suka adalah sensitif huruf besar-besaran, iaitu memahami, sebagai contoh, "Orion" dan "orion" sebagai syarikat yang berbeza. Untuk mengabaikan kes, anda boleh menambah baris pada permulaan modul dalam editor Visual Basic Pilihan Bandingkan Teks, yang akan menukar Suka menjadi tidak peka huruf besar-besaran.
Dengan cara ini, anda boleh mengarang topeng yang sangat kompleks untuk memeriksa keadaan, contohnya:
- ?1##??777RUS – pemilihan semua plat lesen wilayah 777, bermula dengan 1
- LLC* – semua syarikat yang namanya bermula dengan LLC
- ##7## – semua produk dengan kod digital lima digit, dengan digit ketiga ialah 7
- ????? – semua nama lima huruf, dsb.
Kaedah 3. Fungsi makro untuk melekatkan teks di bawah dua keadaan
Dalam kerja mungkin terdapat masalah apabila anda perlu memautkan teks lebih daripada satu syarat. Sebagai contoh, mari kita bayangkan bahawa dalam jadual kami sebelumnya, satu lagi lajur dengan bandar telah ditambah, dan pelekatan harus dilakukan bukan sahaja untuk syarikat tertentu, tetapi juga untuk bandar tertentu. Dalam kes ini, fungsi kami perlu dimodenkan sedikit dengan menambahkan semakan julat lain padanya:
Fungsi MergeIfs(TextRange As Range, SearchRange1 As Range, Condition1 As String, SearchRange2 As Range, Condition2 As String) Malapkan Delimeter Sebagai String, i As Long Delimeter = ", " 'aksara pembatas (boleh digantikan dengan ruang atau ; dsb.) e.) 'jika julat pengesahan dan pelekatan tidak sama antara satu sama lain, keluar dengan ralat Jika SearchRange1.Count <> TextRange.Count Atau SearchRange2.Count <> TextRange.Count Then MergeIfs = CVErr(xlErrRef) Keluar Fungsi Tamat Jika 'lalui semua sel, semak semua keadaan dan kumpulkan teks ke dalam pembolehubah OutText Untuk i = 1 Ke SearchRange1.Cells.Count Jika SearchRange1.Cells(i) = Condition1 Dan SearchRange2.Cells(i) = Condition2 Kemudian OutText = OutText & TextRange.Cells(i) & Delimeter End If Next i 'mempamerkan hasil tanpa pembatas terakhir MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) End Function
Ia akan digunakan dengan cara yang sama – hanya hujah yang perlu dinyatakan dengan lebih lanjut:
Kaedah 4. Pengumpulan dan pelekatan dalam Power Query
Anda boleh menyelesaikan masalah tanpa pengaturcaraan dalam VBA, jika anda menggunakan tambahan Power Query percuma. Untuk Excel 2010-2013 ia boleh dimuat turun di sini dan dalam Excel 2016 ia sudah terbina dalam secara lalai. Urutan tindakan adalah seperti berikut:
Power Query tidak tahu cara bekerja dengan jadual biasa, jadi langkah pertama ialah mengubah jadual kami menjadi jadual yang "pintar". Untuk melakukan ini, pilih dan tekan kombinasi Ctrl+T atau pilih daripada tab Laman Utama – Format sebagai jadual (Laman Utama — Format sebagai Jadual). Pada tab yang kemudian muncul Pembina (Reka Bentuk) anda boleh menetapkan nama jadual (saya meninggalkan standard Jadual 1):
Sekarang mari muatkan jadual kami ke dalam tambahan Power Query. Untuk melakukan ini, pada tab Tarikh (jika anda mempunyai Excel 2016) atau pada tab Power Query (jika anda mempunyai Excel 2010-2013) klik Dari meja (Data — Daripada Jadual):
Dalam tetingkap editor pertanyaan yang terbuka, pilih lajur dengan mengklik pada pengepala Syarikat dan tekan butang di atas Kumpulan (Kumpulkan Mengikut). Masukkan nama lajur baharu dan jenis operasi dalam kumpulan – Semua baris (Semua Baris):
Klik OK dan kami mendapat jadual mini nilai terkumpul untuk setiap syarikat. Kandungan jadual boleh dilihat dengan jelas jika anda mengklik kiri pada latar belakang putih sel (bukan pada teks!) dalam lajur yang terhasil:
Sekarang mari kita tambah satu lagi lajur, di mana, menggunakan fungsi itu, kita melekatkan kandungan lajur Alamat dalam setiap jadual mini, dipisahkan dengan koma. Untuk melakukan ini, pada tab Tambah lajur kita tekan Lajur tersuai (Tambah lajur — Lajur tersuai) dan dalam tetingkap yang muncul, masukkan nama lajur baharu dan formula gandingan dalam bahasa M yang terbina dalam Power Query:
Ambil perhatian bahawa semua fungsi M adalah sensitif huruf besar-besaran (tidak seperti Excel). Selepas klik pada OK kami mendapat lajur baharu dengan alamat terpaku:
Ia kekal untuk mengalih keluar lajur yang sudah tidak diperlukan TableAddresses (klik kanan pada tajuk) Padam lajur) dan muat naik hasilnya ke helaian dengan mengklik pada tab Laman Utama — Tutup dan muat turun (Laman Utama — Tutup dan muatkan):
Nuansa penting: Tidak seperti kaedah (fungsi) sebelumnya, jadual daripada Power Query tidak dikemas kini secara automatik. Jika pada masa hadapan terdapat sebarang perubahan dalam data sumber, maka anda perlu klik kanan di mana-mana dalam jadual hasil dan pilih arahan Kemas kini & Simpan (Segarkan semula).
- Cara membahagikan rentetan teks yang panjang kepada bahagian
- Beberapa cara untuk melekatkan teks daripada sel yang berbeza menjadi satu
- Menggunakan pengendali Suka untuk menguji teks terhadap topeng