Milarian kecap konci dina téks

Milarian kecap konci dina téks sumber mangrupikeun salah sahiji tugas anu paling umum nalika damel sareng data. Hayu urang tingali solusina ku sababaraha cara nganggo conto ieu:

Anggap anjeun sareng kuring gaduh daptar kecap konci - nami merek mobil - sareng méja ageung sadaya jinis suku cadang, dimana déskripsi sakapeung tiasa ngandung hiji atanapi sababaraha merek sakaligus, upami suku cadang pas langkung ti hiji. merek mobil. Tugas urang nyaéta milarian sareng ningalikeun sadaya kecap konci anu dideteksi dina sél tatangga ngalangkungan karakter pamisah anu dipasihkeun (contona, koma).

Métode 1. Power Query

Tangtu, mimitina urang ngarobah tabel urang kana dinamis ("pinter") ngagunakeun potong kompas keyboard Ctrl+T atawa paréntah Imah - Format salaku méja (Imah - Format salaku Méja), pasihan nami aranjeunna (contona perangkoи Suku cadang) sareng muatkeun hiji-hiji kana redaktur Power Query ku milih dina tab Data - Ti Table / rentang (Data — Tina Tabél/Rentang). Upami anjeun gaduh vérsi Excel 2010-2013 anu langkung lami, dimana Power Query dipasang salaku tambihan anu misah, teras tombol anu dipikahoyong bakal aya dina tab. Patarosan Kakuatan. Upami anjeun gaduh vérsi anyar Excel 365, teras tombolna Ti Table / Rentang disebut aya ayeuna Kalayan daun (Ti Lambaran).

Saatos ngamuat unggal méja dina Power Query, urang uih deui ka Excel nganggo paréntah Imah — Tutup sareng muat — Tutup sareng muatkeun ka… — Jieun sambungan wungkul (Imah — Tutup & Muat — Tutup & Muat ka… — Ngan ukur nyieun sambungan).

Ayeuna hayu urang ngadamel duplikat pamundut Suku cadangku ngaklik katuhu dina eta tur milih pamundut duplikat (Parios duplikat), teras ganti nami pamundut salinan anu hasilna janten Hasilna sarta kami bakal neruskeun gawé bareng anjeunna.

Logika tindakan nyaéta kieu:

1. Dina tab Advanced Nambahkeun kolom milih tim kolom custom (Tambihkeun kolom - Kolom khusus) jeung asupkeun rumus = Merk. Saatos ngaklik OK urang bakal meunang kolom anyar, dimana dina unggal sél bakal aya tabel nested kalawan daptar kecap konci urang - automaker brands:

   

2. Paké tombol kalawan panah ganda dina lulugu tina kolom ditambahkeun rék dilegakeun sakabéh tabel nested. Dina waktos anu sami, garis sareng déskripsi suku cadang bakal ngalikeun ku sababaraha jumlah merek, sareng urang bakal nampi sadaya kamungkinan pasangan-kombinasi "merk suku cadang":

   

3. Dina tab Advanced Nambahkeun kolom milih tim Kolom kondisional (Kolom kondisional) tur nyetel kaayaan pikeun mariksa lumangsungna kecap konci (merk) dina téks sumber (bagian déskripsi):

   

4. Pikeun ngajantenkeun kasus pamilarian teu peka, tambahkeun argumen katilu sacara manual dina bar rumus Bandingkeun.OrdinalIgnoreCase kana fungsi mariksa kajadian Téks.Ngandung (upami bar rumus henteu katingali, teras tiasa diaktipkeun dina tab tingalian):

   

5. Urang nyaring tabel hasilna, ngan nyésakeun hiji dina kolom panungtungan, nyaéta patandingan jeung cabut kolom nu teu perlu Kajadian.
6. Ngumpulkeun déskripsi idéntik jeung paréntah Kelompok ku tab transformasi (Transformasi - Grup dumasar). Salaku operasi aggregation, pilih Kabéh garis (Sadaya baris). Dina kaluaran, urang nampi kolom sareng tabel, anu ngandung sadaya detil pikeun tiap suku cadang, kalebet merek mobil anu urang peryogikeun:

   

7. Pikeun nimba sasmita pikeun tiap bagian, tambahkeun kolom diitung séjén dina tab Nambahkeun Kolom - Kolom Adat (Tambihkeun kolom - Kolom khusus) sareng nganggo rumus anu diwangun ku méja (anjeunna aya dina kolom kami rincian) sareng nami kolom anu diekstrak:

   

8. Kami klik tombol sareng panah ganda dina lulugu kolom anu hasilna sareng pilih paréntahna nilai nimba (Nilai ekstrak)pikeun kaluaran perangko sareng karakter pembatas naon waé anu anjeun pikahoyong:

   

9. Ngahapus kolom anu teu perlu rincian.
10. Pikeun nambahkeun kana tabel hasilna bagian anu ngiles ti dinya, dimana euweuh merek kapanggih dina déskripsi, urang ngalakukeun prosedur pikeun ngagabungkeun query. hasil kalawan pamundut aslina Suku cadang kancing ngombinasikeun tab imah (Imah - Gabungkeun patarosan). Jenis sambungan - Luar Gabung Katuhu (Gabung luar katuhu):

    

11. Sadaya anu tetep nyaéta ngaleungitkeun kolom tambahan sareng ganti nami-pindahkeun anu sésana - sareng tugas urang parantos direngsekeun:

    
    

Métode 2. Rumus

Upami anjeun gaduh vérsi Excel 2016 atanapi engké, maka masalah urang tiasa direngsekeun ku cara anu kompak sareng elegan ngagunakeun fungsi énggal. GABUNGAN (TEXTJOIN):

Logika balik rumus ieu basajan:

• fungsi neangan (MANGGIH) maluruh lumangsungna unggal brand dina gilirannana dina pedaran ayeuna ngeunaan bagian sarta mulih boh nomer serial lambang, mimitian ti mana brand ieu kapanggih, atawa kasalahan #VALUE! upami merek henteu aya dina katerangan.
• Lajeng ngagunakeun fungsi IF (lamun) и EOSHIBKA (ISERROR) urang ngaganti kasalahan ku string téks kosong "", sarta angka ordinal tina karakter jeung ngaran merk sorangan.
• Asép Sunandar Sunarya sél kosong sarta kapanggih merek dirakit kana string tunggal ngaliwatan karakter separator dibikeun ngagunakeun fungsi. GABUNGAN (TEXTJOIN).

Perbandingan Kinerja sareng Panyangga Query Query pikeun Speedup

Pikeun nguji kinerja, hayu urang nyandak tabel 100 déskripsi suku cadang salaku data awal. Dina éta kami nampi hasil di handap ieu:

• waktos Recalculation ku rumus (Metoda 2) - 9 detik. sawaktos Anjeun pertama nyalin rumus ka sakabéh kolom jeung 2 detik. di ulang (buffering mangaruhan, meureun).
  
• Waktu update query Power Query (Metode 1) langkung parah - 110 detik.
  

Tangtosna, seueur pisan gumantung kana hardware PC tinangtu sareng versi Office anu dipasang sareng apdet, tapi gambaran umumna, sigana mah, jelas.

Pikeun nyepetkeun pamundut Power Query, hayu urang nyangga tabel lookup perangko, sabab teu robah dina prosés palaksanaan query jeung teu perlu terus-terusan recalculate eta (sakumaha Power Query de facto teu). Pikeun ieu kami nganggo fungsi Méja.Panyangga tina basa Power Query diwangun-di M.

Jang ngalampahkeun ieu, buka query Hasilna jeung dina tab tingalian pencét tombol Éditor canggih (Témbongkeun - Éditor Canggih). Dina jandela nu muka, tambahkeun hiji garis kalawan variabel anyar Marky 2, nu bakal jadi versi buffered tina diréktori automaker urang, sarta ngagunakeun variabel anyar ieu engké dina paréntah query handap:

Saatos perbaikan sapertos kitu, laju pembaruan pamundut urang ningkat ampir 7 kali - dugi ka 15 detik. Hal anu rada béda 🙂

• Pilarian téks kabur dina Power Query
• Panggantian téks bulk sareng rumus
• Ngaganti téks bulk dina Power Query kalawan List.Accumulate fungsi