Power Query であいまい一致を使用して Excel データをクリーンアップする方法

Excel の標準的な検索は完璧主義なので、タイプミスや余分なスペースがあると失敗します。入れ子になった XLOOKUP、TRIM、LOWER 関数を使用してソリューションをハッキングすることはできますが、脆弱で保守が困難です。 Power Query のあいまい一致は、よりスマートで「十分に近い」方法です。

Power Query は、高度なファジー マッチング アルゴリズムを使用して 2 つの文字列間の類似性スコアを計算し、手動で編集することなく、「Microsoft」と「Mcrosoft」の間のギャップを埋めます。これは、乱雑なエクスポートを数秒でクリーンなマスター データベースにマージできることを意味します。

クエリの作成と編集を含む完全な Power Query エクスペリエンスは、Excel for Microsoft 365 (デスクトップ)、Excel for the web (Business および Enterprise プラン)、Power BI でネイティブに利用できます。

目標: 2 つのテーブルの物語

手動エントリでいっぱいの販売レポート (T_Sales、オレンジ) があり、マスター テーブル (T_Master、緑色) から販売地域を取得する必要があると想像してください。

ただし、T_Sales のタイプミスと異なる命名規則のため、標準の XLOOKUP はほぼすべての検索に対して #N/A を返します。したがって、たとえスペルがわずかに違っていても、Excel が T_Sales 内の名前を調べ、T_Master 内で最も一致する名前を見つけて、地域を返す必要があります。

アルゴリズムが推測できない特定のビジネス短縮表現 (「MSFT」など) を処理するには、From 列ヘッダーと To 列ヘッダーを持つ小さなブリッジ テーブル (T_Abbrev、青色) も必要です。始める前に、このテーブルの準備ができていることを確認してください。

ステップ 1: 接続を使用してデータをインポートする

アルゴリズムを解放する前に、3 つのテーブルすべてを Power Query エンジンに取り込む必要があります。まず、データ範囲が Excel テーブル (Ctrl+T) としてフォーマットされていることを確認します。次に、テーブル内の任意のセルを選択し、[データ]>[テーブル/範囲から]をクリックして、最初のテーブルをインポートします。

ブックが乱雑にならないように、各テーブルを Power Query にインポートした後は、標準の[閉じてロード]ボタンをクリックしないでください。代わりに、下矢印をクリックして「閉じてロード」を選択します。

次に、「データのインポート」ダイアログで「接続の作成のみ」を選択し、「OK」をクリックします。

3 つのテーブルすべてに対してこのプロセスを繰り返すと、[クエリと接続]ペイン (最初の接続を作成すると自動的に開きます) にのみ接続として表示され、マージの準備が整います。

ステップ 2: テーブルをマージしてクリーンアップを開始する

クリーンアップを開始するには、[クエリと接続]ペインで[T_Sales]を右クリックし、[マージ]を選択します。

次に、[マージ]ダイアログで 2 番目のテーブルとして「T_Master」を選択し、両方のテーブル プレビューで「名前」列を選択します。基本的に、T_Sales テーブルと T_Master テーブルを結合することを Excel に指示することになります。Name 列はそれらを接続するものです。

ここで、「あいまい一致を使用してマージを実行する」にチェックを入れて、タイプミスや一貫性のない命名規則が原因で一部の接続が完璧ではないことを Excel に伝えます。内部的には、[大文字と小文字を無視]オプションがデフォルトでオンになっており、追加の手順を行わなくても、「MICROSOFT」が「microsoft」と正しく一致します。結合の種類については、「左外側」を使用してください。これにより、一致する地域情報にタグを付けるだけで、すべての販売トランザクションが確実に保持されます。

ステップ 3: 類似性のしきい値をダイヤルインする

OK、結合するテーブル、接続変数、および一致が正確ではないことを Excel に伝えました。次に、しきい値を設定する必要があります。

[あいまい一致オプション]の横にある矢印をクリックします。

類似性のしきい値は 0.00 ～ 1.00 のスケールで、0.00 は類似性のレベルに関係なくすべての値に一致し、1.00 は正確な値のみに一致します。上のスクリーンショットでは、6 行のうち 3 行が一致していることに注目してください。これは、Power Query のデフォルトの類似性しきい値が 0.80 であるためです。このレベルでは、エンジンは厳密です。「Microsoft Inc」は簡単に検出できますが、「Appel」や「Gogle」などのより攻撃的なタイプミスは検出できません。

この場合、しきい値 0.50 は、無関係な単語を誤って一致させることなく、「Appel」と「Gogle」をキャッチするのに十分な低さです。 6 行のうち 5 行が見つかり、一致しないのは「MSFT」の略語だけです。

ステップ 4: 略語の変換テーブルを適用する

「MSFT」が Microsoft を意味すると認識できるほど賢いアルゴリズムはありません。ここで T_Abbrev テーブルが活躍します。[変換テーブル]ドロップダウン メニューで[T_Abbrev]をクリックすると、エンジンが手動オーバーライドを完全一致として扱うようになります。選択すると、下部の一致カウントが 6 つ中 6 つにジャンプします。

ステップ 5: 最終レポートをクリーンアップして要約する

[OK]をクリックすると、Power Query エディターが表示され、テーブルの結果が表示された新しい列が表示されます。

列ヘッダーの[展開]アイコンをクリックし、T_Master.Name のような不格好なヘッダーを防ぐために[元の列名をプレフィックスとして使用する]チェックボックスをオフにして、[OK]をクリックします。

名前が新しい Name.1 列で標準化され、地域が新しい地域列に追加されていることがわかります。

ここから、元の乱雑な名前列を削除し (列ヘッダーを右クリックして[削除]を選択)、新しい名前列をクリックして左にドラッグしてテーブルの最初の列にし、名前を変更します (列ヘッダーをダブルクリック)。 正式名称。ここで、列ヘッダーのアイコンをクリックして、各列に正しいデータ型が割り当てられていることを確認します。

このプロセスでは個々のトランザクションにタグが付けられるため、同じ会社に対して複数の行が存在することになります。これをクリーンなレポートにするには、[変換]タブで[グループ化]をクリックします。

次に、[グループ化]ダイアログで、上部にある[詳細]ラジオ ボタンを選択します。これにより、複数の列を一度にグループ化できます。この場合、名前列と地域列でグループ化します。これを行うには、最初のグループ化で「正式名」を選択します。次に、「グループの追加」をクリックし、「地域」を選択します。

次に、計算を設定します。[新しい列名]フィールドに、次のように入力します。 総売上高、「操作」フィールドで「合計」を選択し、「列」フィールドで「売上」を選択します。

「OK」をクリックすると、繰り返されるすべての行が集約されます。

結果を確認してすべてのデータが期待どおりに表示されていることを確認したら、[ホーム]タブの[閉じてロード]をクリックします。

これで、美しく要約され、正確に綴られたデータが新しい Excel シートに表示されます。これは、Power Query のあいまい一致を使用して Excel で乱雑なデータセットを結合およびクリーンアップすることの利点を示す使用前と使用後のスクリーンショットです。

---

標準のルックアップは、乱雑なデータの現実に対して厳格すぎます。ファジー マッチングを使用すると、手動のデータ入力を、単なる構文ではなく意図を理解するスケーラブルな自動システムに置き換えることができます。これは、Power Query ツールを活用して乱雑なスプレッドシート データを整理する数多くの方法の 1 つにすぎません。一度始めたら、やめられなくなります。