Konten
Mencari kata kunci dalam teks sumber adalah salah satu tugas paling umum saat bekerja dengan data. Mari kita lihat solusinya dalam beberapa cara menggunakan contoh berikut:
Misalkan Anda dan saya memiliki daftar kata kunci – nama merek mobil – dan tabel besar berisi semua jenis suku cadang, di mana deskripsi terkadang dapat berisi satu atau beberapa merek sekaligus, jika suku cadang tersebut muat lebih dari satu merek mobil. Tugas kami adalah menemukan dan menampilkan semua kata kunci yang terdeteksi di sel tetangga melalui karakter pemisah tertentu (misalnya, koma).
Metode 1. Power Query
Tentu saja, pertama-tama kita ubah tabel kita menjadi dinamis ("pintar") menggunakan pintasan keyboard Ctrl+T atau perintah Beranda – Format sebagai tabel (Beranda — Format sebagai Tabel), beri mereka nama (misalnya Perangkoи Suku cadang) dan muat satu per satu ke editor Power Query dengan memilih di tab Data – Dari Tabel/Rentang (Data — Dari Tabel/Rentang). Jika Anda memiliki versi Excel 2010-2013 yang lebih lama, di mana Power Query diinstal sebagai add-in terpisah, tombol yang diinginkan akan ada di tab Kueri Daya. Jika Anda memiliki versi baru Excel 365, maka tombolnya Dari Tabel/Rentang dipanggil ke sana sekarang Dengan daun (Dari Lembar).
Setelah memuat setiap tabel di Power Query, kami kembali ke Excel dengan perintah Beranda — Tutup dan muat — Tutup dan muat ke… — Buat koneksi saja (Beranda — Tutup & Muat — Tutup & Muat ke… — Hanya buat koneksi).
Sekarang mari buat permintaan duplikat Suku cadangdengan mengklik kanan padanya dan memilih Permintaan duplikat (Kueri duplikat), lalu ganti nama permintaan penyalinan yang dihasilkan menjadi Hasil dan kami akan terus bekerja dengannya.
Logika tindakan adalah sebagai berikut:
- Pada tab Lanjutan Menambahkan kolom pilih tim Kolom khusus (Tambahkan kolom — Kolom khusus) dan masukkan rumusnya = Merek. Setelah mengklik OK kita akan mendapatkan kolom baru, di mana di setiap sel akan ada tabel bersarang dengan daftar kata kunci kita – merek pembuat mobil:
- Gunakan tombol dengan panah ganda di header kolom yang ditambahkan untuk memperluas semua tabel bersarang. Pada saat yang sama, baris dengan deskripsi suku cadang akan dikalikan dengan kelipatan jumlah merek, dan kami akan mendapatkan semua kombinasi pasangan yang mungkin dari "merek suku cadang":
- Pada tab Lanjutan Menambahkan kolom pilih tim kolom bersyarat (kolom bersyarat) dan tetapkan kondisi untuk memeriksa kemunculan kata kunci (merek) di teks sumber (deskripsi bagian):
- Untuk membuat kasus pencarian tidak peka, tambahkan argumen ketiga secara manual di bilah rumus Bandingkan.OrdinalIgnoreCase ke fungsi pemeriksaan kejadian Teks.Berisi (jika bilah rumus tidak terlihat, maka dapat diaktifkan di tab ULASAN):
- Kami memfilter tabel yang dihasilkan, hanya menyisakan satu di kolom terakhir, yaitu cocok dan menghapus kolom yang tidak perlu Kejadian.
- Mengelompokkan deskripsi identik dengan perintah Kelompok oleh tab Transformasi (Transformasi — Kelompokkan menurut). Sebagai operasi agregasi, pilih Semua lini (Semua baris). Di pintu keluar, kami mendapatkan kolom dengan tabel yang berisi semua detail untuk setiap suku cadang, termasuk merek pembuat mobil yang kami butuhkan:
- Untuk mengekstrak nilai setiap bagian, tambahkan kolom kalkulasi lainnya pada tab Menambahkan Kolom – Kolom Kustom (Tambahkan kolom — Kolom khusus) dan gunakan rumus yang terdiri dari tabel (terletak di kolom kami Rincian) dan nama kolom yang diekstraksi:
- Kami mengklik tombol dengan panah ganda di tajuk kolom yang dihasilkan dan memilih perintah Ekstrak nilai (Ekstrak nilai)untuk menampilkan stempel dengan karakter pembatas apa pun yang Anda inginkan:
- Menghapus kolom yang tidak perlu Rincian.
- Untuk menambahkan ke tabel yang dihasilkan bagian-bagian yang hilang darinya, di mana tidak ada merek yang ditemukan dalam deskripsi, kami melakukan prosedur untuk menggabungkan kueri Hasil dengan permintaan asli Suku cadang tombol Menggabungkan tab Beranda (Beranda — Gabungkan kueri). Jenis koneksi – Gabung Luar Kanan (Gabungan luar kanan):
- Yang tersisa hanyalah menghapus kolom tambahan dan mengganti nama-memindahkan yang tersisa – dan tugas kita terpecahkan:
Metode 2. Rumus
Jika Anda memiliki versi Excel 2016 atau lebih baru, masalah kami dapat diselesaikan dengan cara yang sangat ringkas dan elegan menggunakan fungsi baru MENGGABUNGKAN (TEKS BERGABUNG):
Logika di balik rumus ini sederhana:
- fungsi CARI (TEMUKAN) mencari kemunculan setiap merek secara bergiliran dalam deskripsi bagian saat ini dan mengembalikan nomor seri simbol, mulai dari merek ditemukan, atau kesalahan #VALUE! jika merk tidak ada di deskripsi.
- Kemudian menggunakan fungsi IF (JIKA) и EOSHIBKA (KESALAHAN) kami mengganti kesalahan dengan string teks kosong "", dan nomor urut karakter dengan nama merek itu sendiri.
- Larik yang dihasilkan dari sel kosong dan merek yang ditemukan dirangkai menjadi string tunggal melalui karakter pemisah yang diberikan menggunakan fungsi tersebut MENGGABUNGKAN (TEKS BERGABUNG).
Perbandingan Performa dan Power Query Buffering untuk Speedup
Untuk pengujian kinerja, mari kita ambil tabel deskripsi 100 suku cadang sebagai data awal. Di atasnya kami mendapatkan hasil berikut:
- Waktu penghitungan ulang dengan rumus (Metode 2) – 9 detik. saat Anda pertama kali menyalin rumus ke seluruh kolom dan 2 detik. di ulang (buffer mempengaruhi, mungkin).
- Waktu pembaruan kueri Power Query (Metode 1) jauh lebih buruk – 110 detik.
Tentu saja, banyak hal bergantung pada perangkat keras PC tertentu dan versi Office yang terinstal serta pembaruan, tetapi gambaran keseluruhannya, menurut saya, jelas.
Untuk mempercepat kueri Power Query, mari buffer tabel pencarian Perangko, karena tidak berubah dalam proses eksekusi kueri dan tidak perlu menghitung ulang secara terus-menerus (seperti yang dilakukan Power Query secara de facto). Untuk ini kami menggunakan fungsi Tabel.Buffer dari bahasa Power Query bawaan M.
Untuk melakukan ini, buka kueri Hasil dan di tab ULASAN tekan tombolnya Editor Tingkat Lanjut (Lihat — Editor Lanjutan). Di jendela yang terbuka, tambahkan baris dengan variabel baru Markus 2, yang akan menjadi versi buffer dari direktori pembuat mobil kami, dan gunakan variabel baru ini nanti dalam perintah kueri berikut:
Setelah penyempurnaan seperti itu, kecepatan pembaruan permintaan kami meningkat hampir 7 kali lipat – hingga 15 detik. Hal yang cukup berbeda 🙂
- Pencarian teks kabur di Power Query
- Penggantian teks massal dengan rumus
- Penggantian teks massal di Power Query dengan fungsi List.Accumulate