Contents [show]
Jika anda sekurang-kurangnya sedikit biasa dengan ungkapan biasa, maka anda tidak perlu mengiklankannya. Jika anda tidak cukup dalam subjek, maka ungkapan biasa (Ungkapan Biasa = RegExp = "regexps" = "biasa") ialah bahasa di mana, menggunakan aksara dan peraturan khas, subrentetan yang diperlukan dicari dalam teks, ia diekstrak atau digantikan dengan teks lain. Ini adalah alat yang sangat berkuasa dan cantik, susunan magnitud yang lebih tinggi daripada semua cara lain untuk bekerja dengan teks.
Saya telah menerangkan secara terperinci dan dengan banyak contoh dari kehidupan bagaimana anda boleh menambah sokongan ekspresi biasa pada Excel menggunakan makro mudah – jika anda belum membaca artikel ini, saya amat mengesyorkan anda membacanya sebelum meneruskan. Anda akan menemui banyak perkara baru, saya jamin 🙂
Walau bagaimanapun, persoalannya masih terbuka - bagaimana untuk menambah keupayaan untuk menggunakan ungkapan biasa dalam Power Query? Power Query, sudah tentu, adalah baik dengan sendirinya dan boleh melakukan banyak perkara dengan teks (memotong, melekat, membersihkan, dll.), tetapi jika anda boleh menyeberanginya dengan kuasa ungkapan biasa, ia hanya akan menjadi bom.
Malangnya, tiada fungsi terbina dalam untuk bekerja dengan RegExps dalam Power Query, dan bantuan rasmi Microsoft serta sokongan teknikal menjawab soalan ini secara negatif. Walau bagaimanapun, terdapat cara untuk mengatasi had ini 🙂
Intipati kaedah
Idea utama adalah mudah untuk memalukan.
Dalam senarai keupayaan Power Query terbina dalam, terdapat fungsi Laman sesawang. Penerangan mengenai fungsi ini pada tapak bantuan rasmi Microsoft adalah sangat ringkas:
Diterjemah, ini akan menjadi: "Mengembalikan kandungan dokumen HTML yang dipecahkan kepada struktur komponennya, serta perwakilan dokumen lengkap dan badannya selepas teg telah dialih keluar." Penerangan begitu-begitu, terus terang.
Biasanya fungsi ini digunakan apabila mengimport data dari web dan digantikan secara automatik, contohnya, apabila kita memilih pada tab Tarikh Perintah Dari Internet (Data — Dari web). Kami memberikan fungsi halaman web sebagai hujah, dan ia mengembalikan kandungannya kepada kami dalam bentuk jadual, setelah mengosongkan semua teg sebelum ini.
Apa yang TIDAK dikatakan bantuan ialah sebagai tambahan kepada bahasa penanda HTML fungsi Laman sesawang menyokong skrip JavaScript, yang kini terdapat di mana-mana di laman web di Internet. Dan JavaScript, seterusnya, sentiasa dapat berfungsi dengan ungkapan biasa dan mempunyai fungsi terbina dalam untuk RegExps! Jadi untuk melaksanakan ungkapan biasa dalam Power Query, kami perlu menyuapkan fungsi Web.Page sebagai hujah kepada program JavaScript kecil yang akan melakukan semua kerja untuk Power Query.
Bagaimana rupanya dalam JavaScript tulen
Terdapat banyak tutorial terperinci tentang bekerja dengan ungkapan biasa dalam JavaScript di Internet (contohnya, satu, dua).
Secara ringkas dan ringkas, kod JavaScript akan kelihatan seperti ini:
Di sini:
- var str = 'Bayar bil 123 dan 789 untuk sosej'; – mencipta pembolehubah str dan berikan teks sumber yang akan kami analisis.
- corak var = /d+/gi; – cipta ungkapan biasa dan letakkan dalam pembolehubah pola.
Ungkapan bermula dengan garis miring (/).
Ungkapan itu sendiri di sini, sebagai contoh, ialah d+ bermaksud sebarang jujukan digit.
Melalui pecahan selepas ungkapan, terdapat parameter carian tambahan (pengubah suai) – ia boleh ditentukan dalam sebarang susunan:
- g – bermaksud carian global, iaitu selepas mencari padanan, anda tidak seharusnya berhenti, tetapi teruskan carian sehingga akhir teks. Jika pengubah suai ini tidak ditetapkan, maka skrip kami hanya akan mengembalikan padanan pertama (123)
- i – cari tanpa mengambil kira kes surat
- m – carian berbilang baris (digunakan apabila teks sumber dibahagikan kepada beberapa baris)
- var result = str.match(pattern).join(';'); – lakukan carian dalam teks sumber (str) dengan ungkapan biasa yang diberikan (pola) dan letakkan hasilnya dalam pembolehubah mengakibatkan, menggabungkannya dengan koma bertitik menggunakan arahan menyertai
- document.write(hasil); – memaparkan kandungan pembolehubah hasil
Juga ambil perhatian bahawa rentetan teks (tidak termasuk ungkapan biasa) dalam JavaScript disertakan dalam apostrof, bukan petikan kerana ia dalam Power Query atau VBA.
Pada output, skrip ini akan memberikan kita sebagai hasilnya semua nombor yang terdapat dalam teks sumber:
123, 789
Kursus pendek JavaScript telah tamat, terima kasih semua. Semoga anda mendapat logiknya 🙂
Ia kekal untuk memindahkan pembinaan ini kepada Power Query.
Cari dan Ekstrak Fungsi Teks mengikut Ungkapan Biasa dalam Power Query
Kami melakukan perkara berikut:
1. Buka Excel dan buat Pertanyaan Kuasa kosong baharu dalam tab Data – Dapatkan data / Buat permintaan – Dari sumber lain – Permintaan kosong (Data — Dapatkan data / Pertanyaan baharu — Daripada sumber lain — Pertanyaan kosong). Jika anda mempunyai versi lama Excel 2010-2013 dan Power Query yang anda tidak mempunyai terbina dalam, tetapi telah dipasang sebagai tambahan yang berasingan, maka semua ini akan berada pada tab Kuasa KuasaDan tidak Tarikh.
2. Dalam tetingkap kosong editor pertanyaan yang terbuka, di panel kanan, segera masukkan nama fungsi masa hadapan kami (contohnya, fxRegExpExtract)
3. Mari pergi ke tab Lihat – Editor Lanjutan (Lihat — Editor Lanjutan), kami memadamkan keseluruhan kod-M permintaan kosong dan tampal kod superfungsi kami di sana:
Perhatikan tangan anda:
Dalam baris pertama, kami mengatakan bahawa fungsi kami akan mempunyai tiga argumen teks: txt – teks asal sedang dianalisis, regex – corak ungkapan biasa, persempadanan — aksara pembatas untuk memaparkan hasil.
Seterusnya kita panggil fungsi Laman sesawang, membentuk kod JavaScript yang diterangkan di atas dalam hujahnya. Kami menampal dan menggantikan argumen pembolehubah kami ke dalam kod.
Serpihan:
[Data]{0}[Kanak-kanak]{0}[Kanak-kanak]{1}[Teks]{0}
… diperlukan untuk "jatuh" ke dalam jadual dengan hasil yang kami perlukan. Intinya ialah fungsi Laman sesawang akibatnya, ia menghasilkan beberapa jadual bersarang yang mengulangi struktur halaman web. Tanpa sekeping kod M ini, fungsi kami akan mengeluarkan ini:
… dan kita perlu mengklik perkataan itu beberapa kali Jadual, berturut-turut "jatuh" ke dalam jadual bersarang kanak-kanak dalam lajur Kanak-kanak:
Daripada semua petikan ini, kami segera menunjukkan dalam kod fungsi kami yang bersarang jadual dan lajur (teks) kita perlu.
Di sini, sebenarnya, adalah semua rahsia. Ia kekal untuk menekan butang Selesai dalam tingkap penyunting lanjutan, di mana kami memasukkan kod kami, dan anda boleh meneruskan ke yang paling lazat – cuba fungsi kami di tempat kerja.
Berikut adalah beberapa contoh benih.
Contoh 1. Mendapatkan semula nombor akaun dan tarikh daripada perihalan pembayaran
Kami mempunyai penyata bank dengan penerangan (tujuan) pembayaran, yang mana anda perlu mengeluarkan nombor dan tarikh invois berbayar ke dalam lajur yang berasingan:
Kami memuatkan jadual ke dalam Power Query dengan cara standard Data – Daripada Jadual/Julat (Data — Daripada Tmampu/Rmalaikat).
Kemudian kami menambah lajur yang dikira dengan fungsi kami melalui Tambah Lajur – Panggilan Fungsi Tersuai (Tambah Lajur — Guna Fungsi Tersuai) dan masukkan hujahnya:
Sebagai ungkapan biasa (argumen regex) templat yang kami gunakan:
(d{3,5}|d{2}.d{2}.d{4})
… diterjemahkan ke dalam bahasa manusia yang bermaksud:
nombor dari 3 hingga 5 digit (nombor akaun)
or
serpihan bentuk "nombor 2-bit - titik - nombor 2-bit - titik - nombor 4-bit", iaitu tarikh dalam borang DD.MM.YYYY.
Sebagai watak pembatas (argumen persempadanan) masukkan koma bertitik.
Selepas mengklik OK fungsi ajaib kami menganalisis semua data awal mengikut ungkapan biasa kami dan membentuk lajur untuk kami dengan nombor dan tarikh invois yang ditemui:
Ia kekal untuk memisahkannya dengan koma bertitik menggunakan arahan Laman Utama — Lajur Pisah — Mengikut Pembatas (Laman Utama — Lajur Pisah — Mengikut pembatas) dan kami mendapat apa yang kami mahu:
Kecantikan!
Contoh 2: Ekstrak alamat e-mel daripada teks
Katakan kita mempunyai jadual berikut sebagai data awal:
... dari mana kita perlu mengeluarkan alamat e-mel yang terdapat di sana (untuk kejelasan, saya menyerlahkannya dengan warna merah dalam teks).
Seperti dalam contoh sebelumnya, kami memuatkan jadual ke dalam Power Query dengan cara standard melalui Data – Daripada Jadual/Julat (Data — Daripada Tmampu/Rmalaikat).
Kemudian kami menambah lajur yang dikira dengan fungsi kami melalui Tambah Lajur – Panggilan Fungsi Tersuai (Tambah Lajur — Guna Fungsi Tersuai) dan masukkan hujahnya:
Menghuraikan alamat e-mel adalah tugas yang lebih sukar dan terdapat sekumpulan ungkapan biasa dengan pelbagai peringkat mimpi ngeri untuk menyelesaikannya. Saya menggunakan salah satu pilihan mudah - tidak sesuai, tetapi agak berkesan dalam kebanyakan kes:
[w|.|-]*@w*.[w|.]*
Sebagai pemisah (persempadanan) anda boleh memasukkan koma bertitik dan ruang.
Klik pada OK dan kami mendapat lajur dengan alamat e-mel yang diekstrak daripada teks asal "bubur":
Sihir!
PS
Seperti kata pepatah: "Tidak ada perkara yang baik yang tidak dapat dijadikan lebih baik." Power Query adalah hebat dengan sendirinya, dan apabila digabungkan dengan ungkapan biasa, ia memberikan kita kuasa dan fleksibiliti yang tidak realistik dalam memproses sebarang data teks. Saya harap Microsoft suatu hari nanti akan menambah sokongan RegExp dalam Power Query dan kemas kini Power BI dan semua tarian di atas dengan tamborin akan menjadi perkara yang telah berlalu. Nah, buat masa ini, ya.
Saya juga ingin menambah bahawa ia adalah mudah untuk bermain dengan ungkapan biasa di tapak https://regexr.com/ – betul-betul dalam editor dalam talian. Terdapat di bahagian Corak Komuniti Terdapat sejumlah besar musim biasa sedia untuk semua majlis. Percubaan – semua kuasa ungkapan biasa kini tersedia untuk anda dalam Power Query!
- Apakah ungkapan biasa (RegExp) dan cara menggunakannya dalam Excel
- Carian teks kabur dalam Power Query
- Memasang jadual daripada fail berbeza menggunakan Power Query