✒️ 変換ルール表の作成

Excelで「変換ルール表」を作る方法

データクリーニングを効率化する実務テクニックとして、「変換ルール表（対応表）」を作成することは、重要です。

これにより、データクリーニングで、必ず遭遇する「表記ゆれ」の対応も万全です。

例えば、同じ会社名なのに、

のように、複数の書き方が混在していることがあります。

このままでは、

• 集計がズレる
• 検索に引っかからない
• データ分析が壊れる

といった問題が発生します。

そこで実務では、「変換ルール表」を作ってデータを統一していきます。

この記事では、前回までのおさらいも含め、初心者向けに、

• 変換ルール表とは何か
• なぜ必要なのか
• Excelでどう作るのか

を、実例付きでわかりやすく解説します。

👉 「変換ルール表の重要性は理解したが、そもそも、どうやって変換ルール表を作っていけばいいのか？」といった疑問にもお答えしていきます。

まず、変換ルール表とは？（おさらい）

変換ルール表とは、

のように、

「どの値を、どの値へ変換するか」

を一覧化したものです。

いきなり置換してはいけない理由!!

初心者のうちは、

• Ctrl + H で置換
• 手作業で修正

をやりがちです。

ですが実務では危険です。

なぜなら、

• どんな表記ゆれが存在するかわからない
• 同じ修正を何度も繰り返す
• ミスが増える

からです。

そこで重要なのが、

👉 「まず全パターンを洗い出す」

という考え方です。

実データからの候補抜き出し

まったく新しい状態で、変換ルール表を作成する場合、特に深刻なのは、

👉 どのような揺れが存在するかわからない

という問題です。

解決策としては、実際のデータから候補を抜き出すことが、有効です。

では、どうやって、実際のデータから候補の抜き出しを行えばいいのでしょうか？

以降では、Excelを使った簡易な手順を説明します。

STEP1：UNIQUEで候補一覧を作る

まず、元データが Sheet1 のA列にあるとします。

重複を除いた一覧を作る為に、Sheet2 のAセルに次の式を書きます。

=SORT(UNIQUE(Sheet1!A:A&""))

すると…

注：Excelでは、大文字、小文字を区別しない為、例の”ABC”と"abc"は同一に扱われます。

のように、

• 重複なし
• 空白なし
• ソート済み

の一覧が Sheet2 の A 列に自動生成されます。

これが非常に重要です。

なぜこの一覧が重要なのか？

この一覧を見ることで、

• どんな表記ゆれがあるか
• どこを統一すべきか
• どの程度データが荒れているか

が見えるようになります。

つまり、「データの全体像を把握できる」 のです。

STEP2：変換ルール表を作る

UNIQUEで作った Sheet2 の一覧の横 B列に、

「正規化後の値」

を書いていきます。

STEP3：XLOOKUPで自動変換

次に、元データを変換します。

例えば Sheet1 の B列へ変換後データを出したい場合：

Sheet1 の Bセルに次の式を書きます。

=XLOOKUP(A:A&"",Sheet2!A:A,Sheet2!B:B&"","登録なし")

この式は何をしているの？

この式は、A列の値を使って、

変換ルール表となる Sheet2 の「元値列」である A列を検索し、 対応する Sheet2 の「正規化後」の B列を返す

という処理をしています。

見つからなかった場合、式の最後にある："登録なし"を表示するという意味です。

尚、新規で変換ルール表を作成する場合、元データから検索対象を抽出しているので、これが表示されることはありません。

STEP4：変換ルール表の保存

実務では「変換ルール表」は資産になります。これが実務では非常に重要です。

変換ルール表は、一度作って終わりではありません。

実際には、

• 新しい表記ゆれが来たら追加
• 次回案件でも再利用
• チームで共有

されていきます。

つまり、

「変換ルールのデータベース」

になっていきます。

保存方法

1. 「変換ルール表」のシートを選択し、メニューの「ファイル」→「名前を付けて保存」を選択する。
2. 保存画面にて、保存先を選択する。
3. ファイル名を入力後、「Excel ブック (*.xlsx)」を「CSV (コンマ区切り)(*.csv)」に変更して保存する。

👉 「変換ルール表」が CSV ファイルとして保存されます。

STEP5：変換ルール表の再利用方法

まず、元データが Sheet1 のA列にあるとします。 分かりやすくする為、前回のも元データに、新たに ”DEF” が追加されていることを想定します。

前回同様、重複を除いた一覧を作る為に、Sheet2 のAセルに次の式を書きます。

=SORT(UNIQUE(Sheet1!A:A&""))

すると…

のように、

• 重複なし
• 空白なし
• ソート済み

の一覧が Sheet2 の A 列に自動生成されます。

STEP6：保存済み変換ルール表の反映

まず、保存済みの変換ルール表を現在のブックに読み込みます。

1. メニューの「データ」を選択し、メニューリボンの「テキストまたは CSV から」を押下します。
2. ダイアログボックスで、「変換ルール表」の CSV ファイルを選択し、インポートを押下します。
3. 表示された画面で、正しく CSV ファイルが認識されていることを確認し、「読み込み」」を押下します。

👉 CSV ファイル名で、「変換ルール表」がシートとして、ブックに読み込まれます。

次に、新しく作成した「変換ルール表」の Sheet2 に読み込んだ「変換ルール表」の値を反映します。

1. Sheet2 の Bセルに次の式を書きます。 （ここでは、読み込んだ「変換ルール表」のシート名が、”Book２” となっているものとします。）

=XLOOKUP(A:A&"",Book２!A:A,Book２!B:B&"","登録なし")

下記のようになります。

2. Sheet2 の Bセル全体を選択し、マウスの右クリックで表示されるメニューから「コピー」を選択します。
3. 再度、マウスの右クリックで表示されるメニューから「貼り付けのオプション」の「123」(値貼り付け)を選択して、上書きします。
4. Bセルの値を確認し、「登録なし」となっているもの（今回の場合”DEF”の行）に「正規化後の値」を入力します。

👉 新たな「変換ルール表」が作成されます。

STEP7：XLOOKUPで自動変換

次に、元データを変換します。これは、前回と同様です。

例えば Sheet1 の B列へ変換後データを出したい場合：

Sheet1 の Bセルに次の式を書きます。

=XLOOKUP(A:A&"",Sheet2!A:A,Sheet2!B:B&"","登録なし")

👉 Bセルに「正規化された値」が表示されます。

※　変換テーブルは、「STEP4：変換ルール表の保存」の手順で、CSV ファイルとして保管しておけば、再利用や共有が可能です。

実務でよくある表記ゆれ

• 会社名
• 株式会社
• (株)
• 有限会社
• Inc.
• LLC
• 人名
• 全角スペース
• 半角スペース
• 大文字小文字
• 商品コード
• ハイフンあり/なし
• 全角英数字
• 先頭ゼロ消失

さらに精度を上げる方法

変換前に、

TRIM SUBSTITUTE LOWER UPPER

などを使うと、さらに揺れを減らせます。

例えば：

=TRIM(A2)

→ 余分な空白削除

=UPPER(A2)

→ 大文字統一

まとめ

データクリーニングでは、「まず一覧化する」 ことが非常に重要です。

おすすめの流れは：

• UNIQUEで候補一覧を作る
• 変換ルール表を作る
• XLOOKUPで変換する

です。

特に、

=SORT(UNIQUE(Sheet1!A:A&""))

=XLOOKUP(A:A&"",Sheet2!A:A,Sheet2!B:B&"","登録なし")

は、実務で非常に強力です。

単なるExcel関数ではなく、

「データの全体像を把握するための武器」

として使えるようになると、データクリーニングの効率が大きく変わります。

補足

もし、データ量の多さから、シートの計算に時間がかかり、表示が遅い場合は、”A:A” や ”B:B” のセル範囲を実際に値の存在するセル範囲にすると効果的です。

例：

=SORT(UNIQUE(Sheet1!A1:A8&""))

=XLOOKUP(A1:A8&"",Sheet2!A1:A6,Sheet2!B1:B6&"","登録なし")

添付

今回の説明で使用した Excelシートの動きを確認したい場合は、下記のボタンを押下してください。

変換ルール表の作成シート