Daptar eusi
Kuring geus nulis ngeunaan kumaha anjeun bisa gancang lem téks ti sababaraha sél kana hiji jeung, sabalikna, parse string téks panjang kana komponén. Ayeuna hayu urang nempo tugas nutup, tapi rada leuwih kompleks - kumaha lem téks ti sababaraha sél lamun kaayaan nu tangtu geus patepung.
Hayu urang nyebutkeun yén urang boga database konsumén, dimana hiji ngaran parusahaan bisa pakait jeung sababaraha surelek béda ti karyawan na. Tugas kami nyaéta pikeun ngumpulkeun sadaya alamat ku nami perusahaan sareng ngahijikeunana (dipisahkeun ku koma atanapi titik koma) supados ngadamel, contona, milis pikeun palanggan, nyaéta kéngingkeun kaluaran sapertos:
Dina basa sejen, urang peryogi alat nu bakal lem (link) téks nurutkeun kaayaan - analog tina fungsi. SUMMESLI (SUMIF), tapi pikeun téks.
Métode 0. Rumus
Teu pisan elegan, tapi cara panggampangna. Anjeun tiasa nyerat rumus saderhana anu bakal mariksa naha perusahaan di baris salajengna bénten ti anu sateuacana. Upami éta henteu béda, teras lem alamat salajengna anu dipisahkeun ku koma. Upami éta bénten, teras urang "ngareset" akumulasi, mimitian deui:
Karugian tina pendekatan ieu atra: tina sadaya sél kolom tambahan anu diala, urang ngan ukur peryogi anu terakhir pikeun unggal perusahaan (konéng). Upami daptarna ageung, teras supados gancang milih aranjeunna, anjeun kedah nambihan kolom sanés nganggo fungsina DLSTR (LEN), mariksa panjang akumulasi string:
Ayeuna anjeun tiasa nyaring sareng nyalin alamat anu dipikabutuh pikeun panggunaan salajengna.
Métode 1. Macrofunction of gluing ku hiji kaayaan
Upami daptar aslina henteu diurutkeun ku perusahaan, maka rumus saderhana di luhur henteu tiasa dianggo, tapi anjeun tiasa gampang ngurilingan ku fungsi khusus leutik dina VBA. Buka Visual Basic Editor ku mencét potong kompas keyboard Alt + F11 atanapi nganggo tombol visual Basic tab pamekar (Pamekar). Dina jandela nu muka, selapkeun modul kosong anyar ngaliwatan menu nu Selapkeun - Modul sareng salin téks fungsi kami di dinya:
Fungsi MergeIf(TextRange Salaku Range, SearchRange Salaku Range, Kaayaan Salaku String) Taram Delimeter Salaku String, i Salaku Long Delimeter = "," gluings henteu sarua saling - urang kaluar kalawan kasalahan Lamun SearchRange.Count <> TextRange.Count Lajeng MergeIf = CVErr(xlErrRef) Kaluar Fungsi Tungtung Lamun 'ngaliwatan sakabéh sél, pariksa kaayaan sarta ngumpulkeun téks dina variabel OutText Pikeun i = 1 Pikeun SearchRange. Cells.Count Lamun SearchRange.Cells(i) Kawas Kaayaan Lajeng OutText = OutText & TextRange.Cells(i) & Delimeter Salajengna i 'nampilkeun hasil tanpa delimiter panungtungan MergeIf = Kénca (OutText, Len (OutText) - Len (Delimeter)) Tamat fungsi
Upami anjeun ayeuna uih deui ka Microsoft Excel, teras dina daptar fungsi (tombol fx dina bar rumus atanapi tab Rumus - Selapkeun Fungsi) eta bakal mungkin pikeun manggihan fungsi urang Gabung Lamun dina kategori Pangguna Ditetepkeun (Pamaké Ditetepkeun). Argumen pikeun fungsi nyaéta kieu:
Métode 2. Concatenate téks ku kaayaan inexact
Lamun urang ngaganti karakter kahiji dina garis 13th of macro urang = ka operator pertandingan perkiraan siga, teras bakal tiasa ngalaksanakeun gluing ku patandingan anu teu leres tina data awal sareng kriteria pamilihan. Salaku conto, upami nami perusahaan tiasa ditulis dina varian anu béda, maka urang tiasa pariksa sareng ngumpulkeun sadayana nganggo hiji fungsi:
Wildcards standar dirojong:
- tanda bintang (*) - nunjukkeun jumlah naon waé karakter (kaasup henteuna)
- tanda tanya (?) - nangtung pikeun sagala karakter tunggal
- tanda pound (#) - nangtung pikeun hiji digit (0-9)
Sacara standar, operator Kawas sensitip leutik, nyaéta ngartos, contona, "Orion" sareng "orion" salaku perusahaan anu béda. Pikeun malire kasus, Anjeun bisa nambah garis dina pisan awal modul dina editor Visual Basic Pilihan Bandingkeun téks, anu bakal ngagentos Kawas janten merhatikeun kasus.
Ku cara kieu, anjeun tiasa nyusun masker anu rumit pisan pikeun mariksa kaayaan, contona:
- ?1##??777RUS – pilihan sadaya plat lisénsi wewengkon 777, dimimitian ku 1
- LLC * - sadaya perusahaan anu namina dimimitian ku LLC
- ##7## - sadaya produk nganggo kode digital lima digit, dimana angka katilu nyaéta 7
- ???? – sakabéh ngaran lima hurup, jsb.
Métode 3. Fungsi makro pikeun gluing téks dina dua kaayaan
Dina karya meureun aya masalah mun anjeun kudu numbu téks leuwih ti hiji kaayaan. Contona, hayu urang ngabayangkeun yén dina tabel kami saméméhna ditambahkeun hiji kolom deui jeung kota, sarta gluing kudu dilaksanakeun teu ukur keur parusahaan tangtu, tapi ogé pikeun kota. Dina hal ieu, fungsi urang kudu rada dimodernisasi ku nambahkeun rentang cek sejen:
Fungsi MergeIfs(TextRange Salaku Range, SearchRange1 Salaku Range, Condition1 Salaku String, SearchRange2 Salaku Range, Condition2 Salaku String) Taram Delimeter Salaku String, i Salaku Long Delimeter = "," 'karakter delimiter (bisa diganti ku spasi atawa ; jsb) e.) 'Upami rentang validasi sareng gluing henteu sami, kaluar kalayan kasalahan Upami SearchRange1.Count <> TextRange.Count Atawa SearchRange2.Count <> TextRange.Count Lajeng MergeIfs = CVErr(xlErrRef) Kaluar Fungsi Tungtung Lamun 'ngaliwatan sakabéh sél, pariksa sagala kaayaan sarta ngumpulkeun téks kana variabel OutText Pikeun i = 1 Pikeun SearchRange1.Cells.Count Lamun SearchRange1.Cells(i) = Condition1 Jeung SearchRange2.Cells(i) = Condition2 Lajeng OutText = OutText & TextRange.Cells(i) & Delimeter End If Next i 'display results without last delimiter MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) End Function
Éta bakal diterapkeun dina cara anu sami - ngan ukur argumen ayeuna kedah dijelaskeun deui:
Métode 4. Grup sareng gluing dina Power Query
Anjeun tiasa ngabéréskeun masalah tanpa program dina VBA, upami anjeun nganggo tambihan Power Query gratis. Pikeun Excel 2010-2013 tiasa diunduh di dieu, sareng dina Excel 2016 parantos diwangun sacara standar. Runtuyan tindakan bakal kieu:
Power Query henteu terang kumaha damel sareng tabel biasa, janten lengkah munggaran nyaéta ngarobih méja kami janten "pinter". Jang ngalampahkeun ieu, pilih eta terus pencét kombinasi Ctrl+T atawa pilih tina tab Imah - Format salaku méja (Imah - Format salaku Méja). Dina tab nu lajeng nembongan Constructor (Desain) Anjeun tiasa nyetél nami méja (kuring ninggalkeun standar table 1):
Ayeuna hayu urang ngamuat méja urang kana tambihan Power Query. Jang ngalampahkeun ieu, dina tab data (upami anjeun gaduh Excel 2016) atanapi dina tab Power Query (upami anjeun gaduh Excel 2010-2013) klik Tina méja (Data - Tina Tabél):
Dina jandela pangropéa pamundut anu muka, pilih kolom ku ngaklik lulugu pausahaan sareng pencét tombol di luhur kumpulan (Grup Ku). Lebetkeun nami kolom anyar sareng jinis operasi dina grup - Kabéh garis (Sadaya Baris):
Pencét OK sareng kami nampi tabel mini tina nilai dikelompokeun pikeun unggal perusahaan. Eusi tabel jelas katingali upami anjeun kenca-klik dina latar bodas sél (sanés dina téks!) Dina kolom anu hasilna:
Ayeuna hayu urang tambahkeun hiji kolom deui, dimana, ngagunakeun fungsi, lem eusi kolom Alamat dina unggal mini-tabel, dipisahkeun ku koma. Jang ngalampahkeun ieu, dina tab Tambihkeun kolom urang pencét kolom custom (Tambihkeun kolom - Kolom khusus) sareng dina jandela anu nembongan, lebetkeun nami kolom énggal sareng rumus gandeng dina basa M anu diwangun kana Power Query:
Catet yén sadaya M-fungsi sénsitip hurup (teu kawas Excel). Saatos ngaklik OK urang meunang kolom anyar kalawan alamat glued:
Eta tetep ngaleupaskeun kolom geus teu perlu TableAddresses (klik katuhu dina judul) Hapus kolom) sareng unggah hasil kana lambaran ku ngaklik tab Imah - Tutup sareng unduh (Imah - Tutup sareng muat):
Nuansa penting: Teu kawas métode saméméhna (fungsi), tabel ti Power Query teu diropéa otomatis. Upami ka hareupna bakal aya parobihan dina data sumber, maka anjeun kedah klik-katuhu dimana waé dina tabel hasil sareng pilih paréntahna. Apdet & Simpen (Refresh).
- Kumaha ngabagi senar téks panjang kana sababaraha bagian
- Sababaraha cara pikeun lem téks ti sél béda kana hiji
- Ngagunakeun operator Kawas pikeun nguji téks ngalawan topeng