内容
正規表現に少しでも精通している場合は、宣伝する必要はありません。 主題に詳しくない場合、正規表現 (Regular Expressions = RegExp = “regexps” = “regulars”) は、特殊文字と規則を使用して、必要な部分文字列がテキスト内で検索され、抽出される言語です。または他のテキストに置き換えます。 これは非常に強力で美しいツールであり、テキストを操作する他のすべての方法よりも桁違いに優れています。
単純なマクロを使用して Excel に正規表現サポートを追加する方法については、既に詳細に説明し、実際の例を多数示しています。この記事をまだ読んでいない場合は、先に進む前に読むことを強くお勧めします。 あなたは多くの新しいことを発見するでしょう、私は保証します🙂
ただし、問題は未解決のままです。Power Query で正規表現を使用する機能を追加するにはどうすればよいでしょうか? もちろん、Power Query はそれ自体で優れており、テキストに対して多くのことを行うことができます (切り取り、接着、クリーニングなど)。
残念ながら、Power Query で RegExps を操作するための組み込み関数はありません。公式の Microsoft ヘルプとテクニカル サポートは、この質問に否定的に答えています。 ただし、この制限を回避する方法があります 🙂
メソッドの本質
主なアイデアは、恥をかかせるのは簡単です。
組み込みの Power Query 機能の一覧には、関数があります。 ウェブページ. Microsoft の公式ヘルプ サイトにあるこの関数の説明は、非常に簡潔です。
翻訳すると、これは次のようになります。「タグが削除された後の完全なドキュメントとその本文の表現だけでなく、コンポーネント構造に分解された HTML ドキュメントのコンテンツを返します。」 まあまあの説明、率直に。
通常、この関数は Web からデータをインポートするときに使用され、たとえばタブで選択したときに自動的に置き換えられます。 且つ Command インターネットから (データ — Web から). 関数に Web ページを引数として渡すと、すべてのタグがクリアされた状態で、そのコンテンツがテーブルの形式で返されます。
ヘルプが言っていないことは、HTML マークアップ言語に加えて function ウェブページ JavaScript スクリプトをサポート、これは現在、インターネット上の Web サイトで広く使用されています。 逆に、JavaScript は常に正規表現を扱うことができ、正規表現用の組み込み関数を備えています。 したがって、Power Query に正規表現を実装するには、Power Query のすべての作業を行う小さな JavaScript プログラムへの引数として Web.Page 関数をフィードする必要があります。
純粋な JavaScript での表示
インターネット上には、JavaScript で正規表現を操作するための詳細なチュートリアルがたくさんあります (たとえば、XNUMX つ、XNUMX つ)。
簡単に言えば、JavaScript コードは次のようになります。
ここに：
- var str = 'ソーセージの請求書 123 と 789 を支払う'; – 変数を作成する STR 分析するソース テキストを割り当てます。
- var パターン = /d+/gi; – 正規表現を作成して変数に入れる パターン.
式はスラッシュ (/) で始まります。
ここでの式自体は、例えば、 d+ 数字の任意のシーケンスを表します。
式の後の分数には、追加の検索パラメーター (修飾子) があります。これらは任意の順序で指定できます。
- g – はグローバル検索を意味します。つまり、一致が見つかった後は、検索を停止せず、テキストの最後まで検索を続けます。 この修飾子が設定されていない場合、スクリプトは最初の一致 (123) のみを返します。
- i – 大文字と小文字を区別せずに検索
- m ・複数行検索（原文が複数行に分かれている場合に使用）
- var result = str.match(pattern).join(';'); – ソース テキストで検索を実行します (STR) 指定された正規表現 (パターン) そして結果を変数に入れます 結果、コマンドを使用してセミコロンでそれらを連結します join
- document.write(結果); – 結果変数の内容を表示する
また、JavaScript のテキスト文字列 (正規表現を除く) は、Power Query や VBA のように引用符ではなく、アポストロフィで囲まれていることに注意してください。
出力では、このスクリプトはソース テキストで見つかったすべての数値を結果として提供します。
123、789
JavaScript の短期講座が終了しました。ありがとうございました。 ロジックを理解していただければ幸いです 🙂
この構造を Power Query に転送する必要があります。
Power Query の正規表現によるテキスト検索と抽出機能
次のことを行います。
1. Excel を開き、タブで新しい空の Power Query を作成します。 データ – データの取得 / リクエストの作成 – 他のソースから – 空のリクエスト (データ — データの取得 / 新しいクエリ — 他のソースから — 空白のクエリ). 古いバージョンの Excel 2010-2013 と Power Query が組み込まれておらず、個別のアドインとしてインストールされている場合、これらはすべてタブに表示されます。 パワークエリそしてではない 且つ.
2. 開いたクエリ エディターの空のウィンドウの右側のパネルで、すぐに将来の関数の名前を入力します (たとえば、 fxRegExpExtract)
3. タブに行きましょう 表示 – 詳細エディター (表示 — 詳細エディター)、空のリクエストの M コード全体を消去し、そこにスーパーファンクションのコードを貼り付けます。
あなたの手に注意してください：
最初の行では、関数に XNUMX つのテキスト引数があることを示しています。 TXT – 分析対象の元のテキスト 正規表現 – 正規表現パターン、 デリム — 結果を表示するための区切り文字。
次に、関数を呼び出します ウェブページ、その引数で上記の JavaScript コードを形成します。 可変引数をコードに貼り付けて置き換えます。
断片：
[データ]{0}[子]{0}[子]{1}[テキスト]{0}
… 必要な結果をテーブルに「フォールスルー」するために必要です。 ポイントは、関数 ウェブページ その結果、Web ページの構造を繰り返すいくつかのネストされたテーブルが生成されます。 この M コードがなければ、関数は次のように出力します。
…そして、単語を数回クリックする必要があります 表、列内のネストされた子テーブルに連続して「フォールスルー」します 子供達:
このすべての引用の代わりに、ネストされたテーブルと列を関数のコードですぐに示します (テキスト) が必要です。
実際、ここにすべての秘密があります。 ボタンを押すことは残っています 終了 ウィンドウ内 高度なエディター、コードを挿入した場所で、最も美味しいものに進むことができます - 仕事で私たちの機能を試してください.
シードの例をいくつか示します。
例 1. 支払いの説明から口座番号と日付を取得する
支払いの説明（目的）が記載された銀行取引明細書があります。ここでは、支払い済みの請求書の番号と日付を別々の列に引き出す必要があります。
テーブルを標準的な方法で Power Query に読み込みます。 データ – テーブル/範囲から (データ — T からできる/R天使）.
次に、関数を使用して計算列を追加します 列の追加 – カスタム関数の呼び出し (列の追加 — カスタム関数の呼び出し) 引数を入力します。
正規表現として (引数 正規表現) 使用するテンプレート:
(日{3,5}|d{2}.d{2}.d{4})
…人間の言葉の意味に翻訳:
3桁から5桁の数字（口座番号）
or
「2 ビット数 – ポイント – 2 ビット数 – ポイント – 4 ビット数」の形式のフラグメント、つまり、DD.MM.YYYY 形式の日付です。
区切り文字として (引数 デリム) セミコロンを入力します。
をクリックした後 OK 私たちの魔法の関数は、正規表現に従ってすべての初期データを分析し、見つかった請求書の番号と日付を含む列を形成します。
コマンドを使用してセミコロンで区切る必要があります ホーム — 列の分割 — 区切り記号による (ホーム — 列の分割 — 区切り記号による) そして、私たちが望んでいたものを手に入れました：
美しさ！
例 2: テキストから電子メール アドレスを抽出する
初期データとして次のテーブルがあるとします。
… そこからメール アドレスを引き出す必要があります (わかりやすくするために、テキスト内でそれらを赤で強調表示しています)。
前の例のように、標準的な方法でテーブルを Power Query に読み込みます。 データ – テーブル/範囲から (データ — T からできる/R天使）.
次に、関数を使用して計算列を追加します 列の追加 – カスタム関数の呼び出し (列の追加 — カスタム関数の呼び出し) 引数を入力します。
電子メール アドレスの解析はより困難な作業であり、それを解決するためのさまざまな程度の悪夢の正規表現がたくさんあります。 私は単純なオプションの XNUMX つを使用しました。理想的ではありませんが、ほとんどの場合、うまく機能します。
[w|.|-]*@w*.[w|.]*
セパレータとして (デリム) セミコロンとスペースを入力できます。
ソフトウェアの制限をクリック OK そして、元のテキスト「porridge」から抽出された電子メール アドレスを含む列を取得します。
マジック！
PS
ことわざにあるように、「これ以上改善できないほど良いものはありません」。 Power Query はそれ自体が優れており、正規表現と組み合わせると、あらゆるテキスト データの処理において、まったく非現実的な能力と柔軟性が得られます。 Microsoft がいつの日か、Power Query と Power BI の更新プログラムに RegExp サポートを追加し、タンバリンを使った上記のすべてのダンスが過去のものになることを願っています。 とりあえず、はい。
また、オンライン エディターでサイト https://regexr.com/ の正規表現を操作すると便利であることも付け加えておきます。 セクションにあります コミュニティのパターン あらゆる機会に対応する既製のレギュラーシーズンが多数あります。 実験 – 正規表現のすべての機能が Power Query で利用できるようになりました!
- 正規表現 (RegExp) とは何ですか? Excel での使用方法
- Power Query でのあいまいテキスト検索
- Power Query を使用してさまざまなファイルからテーブルを組み立てる