Contents [show]
Mencari kata kunci dalam teks sumber ialah salah satu tugas yang paling biasa apabila bekerja dengan data. Mari kita lihat penyelesaiannya dalam beberapa cara menggunakan contoh berikut:
Katakan anda dan saya mempunyai senarai kata kunci – nama jenama kereta – dan jadual besar bagi semua jenis alat ganti, di mana perihalan kadangkala boleh mengandungi satu atau beberapa jenama sedemikian serentak, jika alat ganti muat lebih daripada satu jenama kereta. Tugas kami adalah untuk mencari dan memaparkan semua kata kunci yang dikesan dalam sel jiran melalui aksara pemisah yang diberikan (contohnya, koma).
Kaedah 1. Power Query
Sudah tentu, mula-mula kami menukar jadual kami menjadi dinamik (“pintar”) menggunakan pintasan papan kekunci Ctrl+T atau perintah Laman Utama – Format sebagai jadual (Laman Utama — Format sebagai Jadual), beri mereka nama (contohnya Setemи Alat ganti) dan muatkan satu demi satu ke dalam editor Power Query dengan memilih pada tab Data – Daripada Jadual/Julat (Data — Daripada Jadual/Julat). Jika anda mempunyai versi Excel 2010-2013 yang lebih lama, di mana Power Query dipasang sebagai tambahan yang berasingan, maka butang yang dikehendaki akan berada pada tab Kuasa Kuasa. Jika anda mempunyai versi baharu Excel 365, maka butang Daripada Jadual/Julat dipanggil ke sana sekarang Dengan daun (Dari Helaian).
Selepas memuatkan setiap jadual dalam Power Query, kami kembali ke Excel dengan arahan Laman Utama — Tutup dan muat — Tutup dan muatkan ke… — Buat sambungan sahaja (Laman Utama — Tutup & Muatkan — Tutup & Muatkan ke… — Buat sambungan sahaja).
Sekarang mari buat permintaan pendua Alat gantidengan mengklik kanan padanya dan memilih Permintaan pendua (Pertanyaan pendua), kemudian namakan semula permintaan salinan yang terhasil kepada Keputusan dan kami akan terus bekerjasama dengannya.
Logik tindakan adalah seperti berikut:
- Pada tab Lanjutan Menambah lajur memilih pasukan Lajur tersuai (Tambah lajur — Lajur tersuai) dan masukkan formula = Jenama. Selepas klik pada OK kami akan mendapat lajur baharu, di mana dalam setiap sel akan terdapat jadual bersarang dengan senarai kata kunci kami – jenama pembuat kereta:
- Gunakan butang dengan anak panah berganda dalam pengepala lajur yang ditambahkan untuk mengembangkan semua jadual bersarang. Pada masa yang sama, garisan dengan perihalan alat ganti akan didarab dengan gandaan bilangan jenama, dan kami akan mendapat semua kemungkinan pasangan gabungan "jenama alat ganti":
- Pada tab Lanjutan Menambah lajur memilih pasukan Lajur bersyarat (Lajur bersyarat) dan tetapkan syarat untuk menyemak kemunculan kata kunci (jenama) dalam teks sumber (huraian bahagian):
- Untuk menjadikan huruf besar carian tidak sensitif, tambahkan argumen ketiga secara manual dalam bar formula Bandingkan.OrdinalIgnoreCase kepada fungsi semak kejadian Teks.Mengandungi (jika bar formula tidak kelihatan, maka ia boleh didayakan pada tab ULASAN):
- Kami menapis jadual yang terhasil, meninggalkan hanya satu dalam lajur terakhir, iaitu padanan dan mengalih keluar lajur yang tidak perlu Kejadian.
- Mengelompokkan perihalan yang sama dengan arahan Kumpulan oleh tab Transformasi (Ubah — Kumpulan mengikut). Sebagai operasi pengagregatan, pilih Semua baris (Semua baris). Pada output, kami mendapat lajur dengan jadual, yang mengandungi semua butiran untuk setiap alat ganti, termasuk jenama pembuat kereta yang kami perlukan:
- Untuk mengekstrak gred bagi setiap bahagian, tambah satu lagi lajur yang dikira pada tab Menambah Lajur – Lajur Tersuai (Tambah lajur — Lajur tersuai) dan gunakan formula yang terdiri daripada jadual (ia terletak dalam lajur kami Butiran) dan nama lajur yang diekstrak:
- Kami mengklik butang dengan anak panah berganda di pengepala lajur yang terhasil dan pilih arahan Nilai ekstrak (Nilai ekstrak)untuk mengeluarkan setem dengan mana-mana aksara pembatas yang anda mahu:
- Mengalih keluar lajur yang tidak perlu Butiran.
- Untuk menambah pada jadual yang terhasil bahagian yang hilang daripadanya, di mana tiada jenama ditemui dalam huraian, kami melaksanakan prosedur untuk menggabungkan pertanyaan Hasilnya dengan permintaan asal Alat ganti butang Gabungkan tab Laman Utama (Laman Utama — Gabungkan pertanyaan). Jenis sambungan - Sambungan Luar Kanan (Sambungan luar kanan):
- Apa yang tinggal ialah mengalih keluar lajur tambahan dan menamakan semula- mengalihkan yang selebihnya – dan tugas kami selesai:
Kaedah 2. Formula
Jika anda mempunyai versi Excel 2016 atau lebih baru, maka masalah kami boleh diselesaikan dengan cara yang sangat padat dan elegan menggunakan fungsi baharu GABUNG (TEXTJOIN):
Logik di sebalik formula ini adalah mudah:
- fungsi CARIAN (CARI) mencari kejadian setiap jenama dalam perihalan semasa bahagian dan mengembalikan sama ada nombor siri simbol, bermula dari mana jenama itu ditemui atau ralat #VALUE! jika jenama tiada dalam keterangan.
- Kemudian menggunakan fungsi IF (JIKA) и EOSHIBKA (ISERROR) kami menggantikan ralat dengan rentetan teks kosong "", dan nombor ordinal aksara dengan nama jenama itu sendiri.
- Susunan sel kosong yang terhasil dan jenama yang ditemui dipasang ke dalam satu rentetan melalui aksara pemisah yang diberikan menggunakan fungsi GABUNG (TEXTJOIN).
Perbandingan Prestasi dan Penimbalan Pertanyaan Kuasa untuk Speedup
Untuk ujian prestasi, mari kita ambil jadual 100 huraian alat ganti sebagai data awal. Di atasnya kami mendapat hasil berikut:
- Masa pengiraan semula mengikut formula (Kaedah 2) – 9 saat. apabila anda mula-mula menyalin formula ke seluruh lajur dan 2 saat. pada berulang (penimbalan menjejaskan, mungkin).
- Masa kemas kini pertanyaan Power Query (Kaedah 1) adalah lebih teruk – 110 saat.
Sudah tentu, banyak bergantung pada perkakasan PC tertentu dan versi Office yang dipasang dan kemas kini, tetapi gambaran keseluruhan, saya fikir, adalah jelas.
Untuk mempercepatkan pertanyaan Power Query, mari kita timbal jadual carian Setem, kerana ia tidak berubah dalam proses pelaksanaan pertanyaan dan tidak perlu sentiasa mengiranya semula (seperti yang dilakukan oleh Power Query secara de facto). Untuk ini kami menggunakan fungsi Jadual.Penimbal daripada bahasa Power Query terbina dalam M.
Untuk melakukan ini, buka pertanyaan Keputusan dan pada tab ULASAN tekan butang Editor Lanjutan (Lihat — Editor Lanjutan). Dalam tetingkap yang terbuka, tambahkan baris dengan pembolehubah baharu Marky 2, yang akan menjadi versi penimbal bagi direktori pembuat kereta kami dan gunakan pembolehubah baharu ini kemudian dalam perintah pertanyaan berikut:
Selepas penambahbaikan sedemikian, kelajuan kemas kini permintaan kami meningkat hampir 7 kali ganda - sehingga 15 saat. Perkara yang agak berbeza 🙂
- Carian teks kabur dalam Power Query
- Penggantian teks pukal dengan formula
- Penggantian teks pukal dalam Power Query dengan fungsi List.Accumulate