Ikatan teks miturut kahanan

Aku wis nulis babagan carane sampeyan bisa kanthi cepet nempelake teks saka sawetara sel dadi siji lan, sebaliknya, ngurai string teks sing dawa dadi komponen. Saiki ayo goleki tugas sing cedhak, nanging rada rumit - carane nempelake teks saka sawetara sel nalika kondisi tartamtu ditemtokake. 

Contone, kita duwe database pelanggan, ing ngendi siji jeneng perusahaan bisa cocog karo sawetara email karyawan sing beda. Tugas kita yaiku ngumpulake kabeh alamat kanthi jeneng perusahaan lan gabungke (dipisahake karo koma utawa titik koma) kanggo nggawe, contone, mailing list kanggo pelanggan, yaiku entuk output kaya:

Ing tembung liyane, kita butuh alat sing bakal nempel (nyambung) teks miturut kondisi - analog saka fungsi kasebut. SUMMESLI (SUMIF), nanging kanggo teks.

Cara 0. Formula

Ora banget elegan, nanging cara paling gampang. Sampeyan bisa nulis rumus prasaja sing bakal mriksa apa perusahaan ing baris sabanjuré beda saka sadurunge. Yen ora beda-beda, banjur lem alamat sabanjure sing dipisahake karo koma. Yen beda, banjur "ngreset" akumulasi, miwiti maneh:

Kerugian saka pendekatan iki jelas: saka kabeh sel saka kolom tambahan sing dipikolehi, kita mung butuh sing pungkasan kanggo saben perusahaan (kuning). Yen dhaptar kasebut gedhe, mula supaya bisa milih kanthi cepet, sampeyan kudu nambah kolom liyane kanthi nggunakake fungsi kasebut DLSTR (LEN), mriksa dawa strings akumulasi:

Saiki sampeyan bisa nyaring sing gedhe-gedhe lan nyalin alamat gluing perlu kanggo nggunakake luwih.

Cara 1. Makrofungsi gluing kanthi siji kondisi

Yen dhaptar asli ora diurutake miturut perusahaan, rumus prasaja ing ndhuwur ora bisa digunakake, nanging sampeyan bisa kanthi gampang ngubengi fungsi khusus cilik ing VBA. Bukak Visual Basic Editor kanthi mencet trabasan keyboard Alt + F11 utawa nggunakake tombol visual Basic tab pangembang (Pengembang). Ing jendhela sing mbukak, lebokake modul kosong anyar liwat menu Insert - Modul lan salin teks fungsi kita ing kana:

Function MergeIf(TextRange As Range, SearchRange As Range, Condition as String) Dim Delimeter As String, i As Long Delimeter = "," gluings ora padha karo siji liyane - kita metu kanthi kesalahan Yen SearchRange.Count <> TextRange.Count Banjur MergeIf = CVErr(xlErrRef) Exit Function End Yen 'liwat kabeh sel, priksa kondisi lan ngumpulake teks ing variabel OutText Kanggo i = 1 Kanggo SearchRange. Cells.Count If SearchRange.Cells(i) Like Condition Then OutText = OutText & TextRange.Cells(i) & Delimeter Sabanjure aku nampilake asil tanpa delimiter pungkasan MergeIf = Kiri(OutText, Len(OutText) - Len(Delimeter)) End fungsi  

Yen saiki bali menyang Microsoft Excel, banjur ing dhaptar fungsi (tombol fx ing garis rumus utawa tab Formula - Fungsi Sisipake) bakal bisa nemokake fungsi kita Gabung Yen ing kategori Ditetepake pangguna (Ditetepake pangguna). Argumentasi kanggo fungsi kasebut yaiku:

Cara 2. Gabungke teks kanthi kahanan sing ora pas

Yen kita ngganti karakter pisanan ing baris 13 saka macro kita = menyang operator match kira-kira Kaya, banjur bakal bisa kanggo nindakake gluing dening match inexact data dhisikan karo kritéria pilihan. Contone, yen jeneng perusahaan bisa ditulis ing macem-macem varian, banjur kita bisa mriksa lan ngumpulake kabeh karo siji fungsi:

Wildcard standar didhukung:

• tanda bintang (*) - nuduhake nomer sembarang karakter (kalebu ora ana)
• tandha pitakonan (?) - stands for sembarang karakter siji
• tandha pound (#) – singkatan saka siji digit (0-9)

Kanthi gawan, operator Kaya sensitif huruf cilik, yaiku mangertos, contone, "Orion" lan "orion" minangka perusahaan sing beda. Kanggo nglirwakake kasus, sampeyan bisa nambah baris ing awal modul ing editor Visual Basic Opsi Bandhingake Teks, sing bakal ngalih Kaya dadi cilik ora sensitif.

Kanthi cara iki, sampeyan bisa nggawe topeng sing rumit banget kanggo mriksa kahanan, contone:

• ?1##??777RUS – pilihan kabeh piring lisensi saka wilayah 777, diwiwiti karo 1
• LLC * - kabeh perusahaan sing jenenge diwiwiti karo LLC
• ##7## - kabeh produk kanthi kode digital limang digit, sing nomer telu yaiku 7
• ????? – kabeh jeneng limang aksara, etc.
  

Cara 3. Fungsi makro kanggo gluing teks ing rong kondisi

Ing karya bisa uga ana masalah nalika sampeyan kudu ngubungake teks luwih saka siji kondisi. Contone, bayangake yen ing tabel sadurunge, siji kolom liyane karo kutha ditambahake, lan gluing kudu ditindakake ora mung kanggo perusahaan tartamtu, nanging uga kanggo kutha tartamtu. Ing kasus iki, fungsi kita kudu rada dimodernisasi kanthi nambahake sawetara mriksa liyane:

Function MergeIfs(TextRange As Range, SearchRange1 As Range, Condition1 as String, SearchRange2 as Range, Condition2 as String) Dim Delimeter As String, i As Long Delimeter = "," 'karakter pembatas (bisa diganti spasi utawa ; etc.) e.) 'Yen rentang validasi lan gluing ora padha karo siji liyane, metu kanthi kesalahan Yen SearchRange1.Count <> TextRange.Count Utawa SearchRange2.Count <> TextRange.Count Then MergeIfs = CVErr(xlErrRef) Exit Function End If 'liwat kabeh sel, mriksa kabeh kondisi lan ngumpulake teks menyang variabel OutText Kanggo i = 1 Kanggo SearchRange1.Cells.Count Yen SearchRange1.Cells(i) = Condition1 Lan SearchRange2.Cells(i) = Condition2 Banjur OutText = OutText & TextRange.Cells(i) & Delimeter End If Next i 'display results without last delimiter MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) End Function  

Bakal diterapake kanthi cara sing padha - mung argumen sing kudu ditemtokake luwih akeh:

Cara 4. Ngelompokake lan gluing ing Power Query

Sampeyan bisa ngatasi masalah tanpa program ing VBA, yen sampeyan nggunakake free Power Query add-in. Kanggo Excel 2010-2013 bisa diundhuh ing kene, lan ing Excel 2016 wis dibangun kanthi standar. Urutan tumindak bakal kaya ing ngisor iki:

Power Query ora ngerti carane nggarap tabel biasa, mula langkah pisanan yaiku ngowahi meja dadi "pinter". Kanggo nindakake iki, pilih banjur pencet kombinasi Ctrl+T utawa pilih saka tab Ngarep - Format minangka tabel (Ngarep - Format minangka Tabel). Ing tab sing banjur katon Constructor (Desain) sampeyan bisa nyetel jeneng tabel (aku ninggalake standar Tabel 1):

Saiki ayo mbukak tabel kita menyang add-in Power Query. Kanggo nindakake iki, ing tab Data (yen sampeyan duwe Excel 2016) utawa ing tab Power Query (yen sampeyan duwe Excel 2010-2013) klik Saka meja (Data - Saka Tabel):

Ing jendhela editor query sing mbukak, pilih kolom kanthi ngeklik header Company lan pencet tombol ing ndhuwur Group (Grup Miturut). Ketik jeneng kolom anyar lan jinis operasi ing klompok - Kabeh garis (Kabeh Larik):

Klik OK lan entuk mini-tabel nilai diklompokaké kanggo saben perusahaan. Isi tabel katon kanthi jelas yen sampeyan ngeklik ngiwa ing latar mburi putih sel (ora ing teks!) Ing kolom asil:

Saiki ayo nambah siji kolom maneh, ing ngendi, nggunakake fungsi kasebut, kita nempelake isi kolom Alamat ing saben mini-tabel, dipisahake karo koma. Kanggo nindakake iki, ing tab Tambah kolom kita pencet Kolom khusus (Tambah kolom - Kolom khusus) lan ing jendhela sing katon, ketik jeneng kolom anyar lan rumus kopling ing basa M sing dibangun ing Power Query:

Elinga yen kabeh M-fungsi sensitif huruf cilik (ora kaya Excel). Sawise ngeklik OK kita entuk kolom anyar kanthi alamat terpaku:

Iku tetep kanggo mbusak kolom sing wis ora perlu TabelAlamat (klik kanan judul) Mbusak kolom) lan upload asil menyang sheet kanthi ngeklik ing tab Ngarep - Nutup lan download (Ngarep - Tutup lan muat):

Nuansa penting: Ora kaya cara sadurunge (fungsi), tabel saka Power Query ora dianyari kanthi otomatis. Yen ing mangsa ngarep bakal ana owah-owahan ing data sumber, sampeyan kudu klik-tengen ing ngendi wae ing tabel asil banjur pilih printah Nganyari & Simpen (Refresh).

• Cara pamisah string teks dawa dadi bagean
• Sawetara cara kanggo gluing teks saka macem-macem sel dadi siji
• Nggunakake operator Kaya kanggo nyoba teks marang topeng