ソース テキスト内のキーワードの検索は、データを操作する際の最も一般的なタスクの XNUMX つです。 次の例を使用して、いくつかの方法でその解決策を見てみましょう。
あなたと私がキーワードのリスト (自動車ブランドの名前) と、あらゆる種類のスペアパーツの大きな表を持っていると仮定しましょう。スペアパーツが複数の部品に適合する場合、その説明には一度に XNUMX つまたは複数のブランドが含まれることがあります。車のブランド。 私たちのタスクは、指定された区切り文字 (カンマなど) を使用して、隣接するセルで検出されたすべてのキーワードを検索して表示することです。
方法 1. パワー クエリ
もちろん、最初にキーボード ショートカットを使用してテーブルを動的 (「スマート」) に変更します。 Ctrlキー+T またはコマンド ホーム – 表としてフォーマットする (ホーム — テーブルとしてフォーマット)、それらに名前を付けます（たとえば、 スタンプスи スペアパーツ) タブで選択して Power Query エディターに XNUMX つずつロードします データ – テーブル/範囲から (データ — テーブル/範囲から)。 Power Query が別のアドインとしてインストールされている古いバージョンの Excel 2010 ～ 2013 を使用している場合、目的のボタンがタブ上に表示されます。 パワークエリ。 新しいバージョンの Excel 365 を使用している場合は、ボタン テーブル/範囲から 今そこに呼ばれました 葉付き (シートより).
Power Query で各テーブルをロードした後、次のコマンドを使用して Excel に戻ります。 ホーム — 閉じてロード — 閉じてロード… — 接続のみを作成 (ホーム — 閉じてロード — 閉じて…にロード — 接続の作成のみ).
次に、重複したリクエストを作成しましょう スペアパーツそれを右クリックして選択すると、 重複したリクエスト (重複したクエリ)、結果のコピーリクエストの名前を次のように変更します。 結果 そして私たちは彼と協力し続けます。
アクションのロジックは次のとおりです。
- [詳細設定]タブ 列の追加 チームを選ぶ カスタム列 (列を追加 — カスタム列) そして数式を入力してください = ブランド。 クリックした後 OK 新しい列を取得します。各セルには、キーワード (自動車メーカーのブランド) のリストを含むネストされたテーブルが表示されます。
- 追加された列のヘッダーにある二重矢印のボタンを使用して、ネストされたテーブルをすべて展開します。 同時に、スペアパーツの説明を含む行がブランド数の倍数になり、「スペアパーツとブランド」の可能なすべてのペアの組み合わせが得られます。
- [詳細設定]タブ 列の追加 チームを選ぶ 条件付き列 (条件列) ソース テキスト (部分の説明) 内のキーワード (ブランド) の出現を確認するための条件を設定します。
- 検索で大文字と小文字を区別しないようにするには、数式バーに XNUMX 番目の引数を手動で追加します。 Compare.OrdinalIgnoreCase 発生チェック機能へ Text.Contains (数式バーが表示されていない場合は、タブで有効にすることができます レビュー):
- 結果のテーブルをフィルタリングして、最後の列にあるもの、つまり一致するものだけを残し、不要な列を削除します。 出現.
- コマンドによる同一の説明のグループ化 グループ化する タブ 変換 (変換 — グループ化)。 集計操作として、 すべての行 (すべての行)。 出力では、必要な自動車メーカーのブランドを含む、各スペアパーツのすべての詳細が含まれるテーブルを含む列が得られます。
- 各部品の評定を抽出するには、タブに別の計算列を追加します 列の追加 – カスタム列 (列を追加 — カスタム列) テーブルで構成される数式を使用します (テーブルは列にあります) 詳細) と抽出された列の名前:
- 結果の列のヘッダーにある二重矢印のボタンをクリックし、コマンドを選択します 値の抽出 (値の抽出)任意の区切り文字を含むスタンプを出力するには:
- 不要な列を削除する 詳細.
- 結果のテーブルに、説明にブランドが見つからず、テーブルから消えた部分を追加するには、クエリを結合する手順を実行します。 結果 オリジナルのリクエストで スペアパーツ (Comma Separated Values) ボタンをクリックして、各々のジョブ実行の詳細（開始/停止時間、変数値など）のCSVファイルをダウンロードします。 組み合わせる タブ ホーム (ホーム - クエリのマージ)。 接続タイプ - 外側結合右 (右外部結合):
- 残っているのは、余分な列を削除し、残りの列の名前を変更して移動することだけです。これでタスクは解決されます。
方法 2. 数式
Excel 2016 以降のバージョンをお持ちの場合は、新しい関数を使用する非常にコンパクトでエレガントな方法で問題を解決できます。 組み合わせる (テキスト結合):
この式の背後にあるロジックは単純です。
- 演算 検索 （探す） 部品の現在の説明内で各ブランドの出現を順番に検索し、ブランドが見つかった時点から始まるシンボルのシリアル番号、またはエラー #VALUE! のいずれかを返します。 ブランドが説明にない場合。
- 次に関数を使用する IF （IF） и エオシブカ (エラー) エラーを空のテキスト文字列「」に置き換え、文字の序数をブランド名そのものに置き換えます。
- 空のセルと見つかったブランドの結果の配列は、関数を使用して指定された区切り文字を介して単一の文字列に組み立てられます。 組み合わせる (テキスト結合).
パフォーマンスの比較と高速化のための Power Query クエリ バッファリング
パフォーマンス テストのために、初期データとして 100 個のスペアパーツの説明のテーブルを取得しましょう。 これにより、次の結果が得られます。
- 式による再計算時間 (方法 2) – 9 秒最初に数式を列全体にコピーするときは 2 秒かかります。 繰り返し時 (おそらくバッファリングに影響します)。
- Power Query クエリ (方法 1) の更新時間はさらに短く、110 秒です。
もちろん、特定の PC のハードウェア、インストールされている Office と更新プログラムのバージョンに大きく依存しますが、全体像は明らかだと思います。
Power Query クエリを高速化するには、ルックアップ テーブルをバッファリングしましょう スタンプスこれは、クエリ実行のプロセスでは変化せず、(Power Query が事実上行うように) 常に再計算する必要がないためです。 このために、関数を使用します テーブルバッファ 組み込みの Power Query 言語 M から。
これを行うには、クエリを開きます 結果 そしてタブで レビュー ボタンを押す 高度なエディタ (表示 — 詳細エディター)。 開いたウィンドウで、新しい変数を含む行を追加します。 マーキー2これは automaker ディレクトリのバッファリングされたバージョンになり、この新しい変数は後ほど次のクエリ コマンドで使用します。
このような改良の後、リクエストの更新速度はほぼ 7 倍、最大 15 秒に増加しました。 まったく違うものです 🙂
- Power Query でのあいまいテキスト検索
- 数式によるテキストの一括置換
- List.Accumulate 関数を使用した Power Query での一括テキスト置換