Paragraf
Nggoleki tembung kunci ing teks sumber minangka salah sawijining tugas sing paling umum nalika nggarap data. Ayo goleki solusi kanthi sawetara cara nggunakake conto ing ngisor iki:
Coba umpamane sampeyan lan aku duwe dhaptar tembung kunci - jeneng merek mobil - lan tabel gedhe kabeh jinis spare parts, ing ngendi deskripsi kadhangkala bisa ngemot siji utawa sawetara merek kasebut bebarengan, yen spare parts pas karo luwih saka siji. merek mobil. Tugas kita yaiku nemokake lan nampilake kabeh tembung kunci sing dideteksi ing sel tetanggan liwat karakter pemisah sing diwenehake (contone, koma).
Cara 1. Power Query
Mesthi, pisanan kita ngowahi tabel dadi dinamis ("pinter") nggunakake trabasan keyboard Ctrl+T utawa dhawuh Ngarep - Format minangka tabel (Ngarep - Format minangka Tabel), menehi jeneng (contone Prangkoи Alat ganti) lan muat siji-siji menyang editor Power Query kanthi milih ing tab Data - Saka Tabel / Range (Data — Saka Tabel/Rentang). Yen sampeyan duwe versi lawas Excel 2010-2013, ing ngendi Power Query diinstal minangka tambahan sing kapisah, banjur tombol sing dikarepake bakal ana ing tab. Pitakonan Daya. Yen sampeyan duwe versi anyar saka Excel 365, banjur tombol Saka Tabel / Range disebut ana saiki Kanthi godhong (Saka Lembar).
Sawise ngemot saben tabel ing Power Query, kita bali menyang Excel kanthi printah Ngarep — Nutup lan mbukak — Nutup lan mbukak kanggo… — Nggawe sambungan mung (Ngarep — Nutup & Muat — Nutup & Muat kanggo… — Mung nggawe sambungan).
Saiki ayo nggawe panjalukan duplikat Alat gantikanthi ngeklik tengen lan milih Panjaluk duplikat (Panjaluk duplikat), banjur ganti jeneng panjalukan salinan sing diasilake dadi asil lan kita bakal terus nggarap dheweke.
Logika tumindak ing ngisor iki:
- Ing tab Lanjut Nambah kolom milih tim Kolom khusus (Tambah kolom - Kolom khusus) lan ketik rumus = Merk. Sawise ngeklik OK kita bakal entuk kolom anyar, ing ngendi ing saben sel bakal ana tabel nested kanthi dhaptar tembung kunci kita - merek automaker:
- Gunakake tombol kanthi panah pindho ing header kolom sing ditambahake kanggo nggedhekake kabeh tabel nested. Ing wektu sing padha, garis kanthi katrangan babagan suku cadang bakal tikel kaping pindho saka jumlah merek, lan kita bakal entuk kabeh kombinasi pasangan "merk spare part":
- Ing tab Lanjut Nambah kolom milih tim Kolom kondisional (Kolom kondisional) lan atur syarat kanggo mriksa kedadeyan tembung kunci (merek) ing teks sumber (deskripsi bagean):
- Kanggo nggawe kasus telusuran ora sensitif, tambahake argumen katelu kanthi manual ing garis rumus Bandingake.OrdinalIgnoreCase menyang fungsi mriksa kedadeyan Teks.Ngandhut (yen garis formula ora katon, banjur bisa diaktifake ing tab review):
- Kita nyaring tabel asil, mung ninggalake siji ing kolom pungkasan, yaiku cocog lan mbusak kolom sing ora perlu. Kedadeyan.
- Nglumpukake deskripsi sing padha karo printah Kelompok dening tab transformasi (Transform - Group dening). Minangka operasi agregasi, pilih Kabeh garis (Kabeh baris). Ing output, kita entuk kolom kanthi tabel, sing ngemot kabeh rincian kanggo saben suku cadang, kalebu merek mobil sing dibutuhake:
- Kanggo ngekstrak biji kanggo saben bagean, tambahake kolom sing diwilang liyane ing tab Nambahake Kolom - Kolom Kustom (Tambah kolom - Kolom khusus) lan gunakake rumus sing kalebu tabel (padha ana ing kolom kita Details) lan jeneng kolom sing diekstrak:
- Kita klik tombol kanthi panah kaping pindho ing header kolom sing diasilake lan pilih printah kasebut Nilai ekstrak (Nilai ekstrak)kanggo ngasilake prangko kanthi karakter pembatas apa wae sing dikarepake:
- Mbusak kolom sing ora perlu Details.
- Kanggo nambah menyang tabel asil bagean sing ilang, sing ora ana merek sing ditemokake ing deskripsi, kita nindakake prosedur kanggo nggabungake pitakon. Result karo panjalukan asli Alat ganti tombol Gabung tab Home (Ngarep - Gabung pitakon). Tipe sambungan - Outer Join Kanan (Sambung njaba tengen):
- Kabeh sing isih ana yaiku mbusak kolom ekstra lan ganti jeneng-pindhah sing isih ana - lan tugas kita wis rampung:
Cara 2. Rumus
Yen sampeyan duwe versi Excel 2016 utawa mengko, masalah kita bisa ditanggulangi kanthi cara sing kompak lan elegan nggunakake fungsi anyar. KOMBIN (TEXTJOIN):
Logika konco rumus iki prasaja:
- fungsi SEARCH (GOLEK) searches kanggo kedadeyan saben account ing siji ing gambaran saiki bagean lan ngasilake salah siji nomer serial saka simbol, miwiti saka kang account ditemokaké, utawa kesalahan #VALUE! yen merek ora ana ing katrangan.
- Banjur nggunakake fungsi IF (Yen) и EOSHIBKA (ISERROR) kita ngganti kasalahan karo senar teks kosong "", lan nomer ordinal saka karakter karo jeneng account piyambak.
- Susunan sel kosong lan merek sing ditemokake dirakit dadi senar siji liwat karakter pemisah sing diwenehake nggunakake fungsi kasebut. KOMBIN (TEXTJOIN).
Perbandingan Kinerja lan Power Query Query Buffering kanggo Speedup
Kanggo tes kinerja, ayo njupuk tabel 100 deskripsi suku cadang minangka data awal. Ing kita entuk asil ing ngisor iki:
- Wektu recalculation dening rumus (Metode 2) - 9 sec. nalika sampeyan pisanan nyalin rumus menyang kabeh kolom lan 2 detik. ing bola-bali (buffering mengaruhi, mbokmenawa).
- Wektu nganyari pitakon Power Query (Metode 1) luwih elek - 110 detik.
Mesthi, akeh gumantung ing hardware PC tartamtu lan versi Office sing diinstal lan nganyari, nanging gambar sakabèhé, aku mikir, jelas.
Kanggo nyepetake pitakon Power Query, ayo buffer tabel goleki Prangko, amarga ora owah ing proses eksekusi query lan ora perlu terus-terusan ngitung maneh (kaya Power Query de facto). Kanggo iki kita nggunakake fungsi Tabel.Buffer saka basa Power Query dibangun ing M.
Kanggo nindakake iki, bukak pitakon asil lan ing tab review penet tombol Editor Lanjut (Deleng - Editor Lanjut). Ing jendhela sing mbukak, tambahake baris kanthi variabel anyar Marky 2, sing bakal dadi versi buffer saka direktori produsen mobil, lan gunakake variabel anyar iki mengko ing printah pitakon ing ngisor iki:
Sawise panyulingan kasebut, kacepetan nganyari panyuwunan kita mundhak meh kaping 7 - nganti 15 detik. Luwih beda 🙂
- Panelusuran teks kabur ing Power Query
- Panggantos teks akeh kanthi rumus
- Panggantos teks akeh ing Power Query kanthi fungsi List.Accumulate