【パワークエリ】「値の置換」の使い方｜列の値を一括置換する方法【動画あり】

Aさん

パワークエリでも置換したいですが、何のコマンドを使えば良いですか？

森田

その場合、Power Queryエディター上の「値の置換」コマンドを使えば良いですよ！
では、詳細を解説していきますね。

解説動画

この記事の内容は下記の動画「【パワークエリ#6】データクレンジング基本テクニック13選 – 元データを綺麗にするための不備修正の作業を自動化する」でも解説しています。

コメント欄の目次の時間部分をクリックすると該当の解説へジャンプできますよ！

はじめに

この記事はパワークエリの概要を把握していることが前提です。

「値の置換」コマンドとは、Power Queryエディター上で列内の値を新しい値へ置換する機能

「値の置換」コマンドは、クエリの新規作成ステップのうち、Step 2の「取得データを整形/加工（Transform）」に該当する機能です。

機能自体は、ワークシート上の「置換」コマンドや関数の「SUBSTITUTE」と類似しています。

クエリ内の一連の整形作業の中で、同義語の表記ゆれを修正する、あるいは特定の文字を削除したい場合に「値の置換」コマンドを使いましょう。
同義語とは、「りんご」と「アップル」など、同じ意味だが表記が異なる言葉のこと。

「値の置換」コマンドを活用することで、Power Queryエディター上で列内の値を新しい値へ置換できます。

一例として、「商品名」列を対象に「値の置換」コマンドで「林檎」を「りんご」へ置換したものが以下です。

この通り、Power Queryエディター上で置換したステップを記録できました。
「値の置換」コマンドの場合、デフォルトのステップ名は「置き換えられた値」になる（クエリ内で2つ目以降の「置き換えられた値」ステップは連番が付加）。

なお、ワークシート上の「置換」コマンドと違い、「値の置換」コマンドで指定した置換前の文字が検索できない場合、特にエラーメッセージは表示されません。

つまり、意味がないステップが増えてしまうため、きちんと意図通りに置換されたか、Power Queryエディターのプレビューウィンドウ上で確認しましょう。

後は、クエリ新規作成ステップのStep 2とStep 3を行いましょう。

これにより、クエリに記録した置換のステップを自動化できます。
置換のステップを記録後、置換対象の列名を変更すると、クエリ更新時にエラーになるため注意（列名の変更手順は後述）。

クエリの新規作成ステップのStep 2に役立つ各種コマンドや、Step 3の手順の詳細を知りたい方は、パワークエリの概要記事をご参照ください。
【Excel】パワークエリ（Power Query）とは？できることや使い方入門【動画あり】

「値の置換」コマンドの使用手順

「値の置換」コマンドは、以下の手順で使用します。

手順②は、リボン「ホーム」タブか「変換」タブ上でもOK（両方のタブに同じコマンドがある）。
手順④に設定した文字が、手順①で選択した列にあるセルの値に含まれると置換対象となる。
置換前の文字に「空白」を設定したい場合、手順④を省略。
「null」を対象に置換したい場合、手順④で「null」を指定。
手順④に指定した文字を削除したい場合、手順⑤を省略。

【参考】詳細設定オプション

「値の置換」ダイアログに用意されている「詳細設定オプション」を活用すると、応用的な置換を行うことが可能です。

それぞれ順番に解説します。

セルの内容全体の照合

詳細設定オプションの「セルの内容全体の照合」のチェックをONにすることで、設定した置換前の文字がセルの値と完全一致の場合にのみ置換を行うように変更できます。
前述の解説通り、使用手順④に設定する文字が、手順①で選択した列にあるセルの値に含まれると置換対象になることが基本仕様。

実際にこの設定を行う場合、「値の置換」ダイアログを開いたら、前述の手順⑥の前に以下手順を行ってください。

特殊文字を使用した置換

詳細設定オプションの「セルの内容全体の照合」のチェックをONにすることで、置換前後の文字に以下の特殊文字を活用することが可能です。

特殊文字を挿入する手順は以下の通りです。
事前に「特殊文字を使用した置換」のチェックをONにしておくこと。

実務では、置換前の文字に含まれるタブや改行を基準に置換したい場合等で活用すると良いでしょう。
以下は「改行」を「改行なしスペース」へ置換した例。

なお、単純に改行を削除したい場合は、「クリーン」コマンドを活用しましょう。

「クリーン」コマンドの詳細を知りたい方は、以下の記事をご参照ください。
【パワークエリ】列内の印刷不可能な文字（改行等）を削除できる「クリーン」の使い方

「置き換えられた値」ステップの変更手順

「値の置換」コマンドで記録した「置き換えられた値」ステップを後から変更する場合、変更したい内容が置換対象の列名か、それ以外かで手順が異なります。

それぞれの手順を順番に解説していきましょう。

置換対象の列

以下のようなケースでクエリがエラーになる可能性があります。

この場合、以下の手順で「置き換えられた値」ステップの対象列を変更しましょう。

手順①のステップ名は「値の置換」ステップのデフォルトの名前（自身でリネームしている場合は別表記）。
数式バーが表示されていない場合、リボン「表示」タブの「数式バー」のチェックをONにすること。

手順③まで終えるとPower Queryエディターのプレビューウィンドウに戻るため、エラーが解消され、無事に置換されたかを確認しましょう。

その他

置換対象の列以外の内容（置換前後の文字、詳細設定オプションの設定）を後から変更したい場合、以下の手順で変更しましょう。

手順①のステップ名は「値の置換」コマンドのデフォルトの名前（自身でリネームしている場合は別表記）。
手順①はステップ名をダブルクリックでもOK。
手順①で起動した「値の置換」ダイアログは、ステップに設定した内容がセットされた状態。

手順③まで終えるとPower Queryエディターのプレビューウィンドウに戻るため、変更が問題なく反映されたかを確認しましょう。

サンプルファイルで練習しよう！

可能であれば、以下のサンプルファイルをダウンロードして、実際に操作練習をしてみてください。

サンプルファイル_PowerQueryデータ整形_値の置換.xlsx

※サンプルファイルのダウンロードには無料メルマガに登録いただく必要があります。
（上記リンクから登録フォームへ遷移します）

ブックを開いたら、「注文テーブル」クエリを編集（Power Queryエディター起動）し、次の手順を実施してください（今までの解説のまとめです）。
ブック起動時、「セキュリティの警告」メッセージが表示された場合は「コンテンツの有効化」をクリック。

本記事の解説と同じ結果になればOKです！

さいごに

いかがでしたでしょうか？

「値の置換」コマンドを活用することで、Power Queryエディター上で列内の値を新しい値へ置換できます。

同義語の表記ゆれの修正等、データクレンジングのオーソドックスな処理の1つのため、ぜひ基本テクニックとして覚えておきましょう！

なお、パワークエリの各種テクニックは、私の拙著でも体系的に解説していますので、こちらも参考にしてみてください。

パワークエリも関数もぜんぶ使う！Excelでできるデータの収集・整形・加工を極めるための本

created by Rinker

• Kindle
• Amazon
• 楽天市場
• Yahooショッピング

ピボットテーブルも関数もぜんぶ使う！Excelでできるデータの集計・分析を極めるための本

created by Rinker

• Kindle
• Amazon
• 楽天市場
• Yahooショッピング

ご参考になれば幸いですm(_ _)m

森田

「値の置換」コマンドに慣れたら、他の表記ゆれの修正に役立つコマンドもセットで覚えると良いです。
たとえば、英数字の表記ゆれを修正する「小文字」・「大文字」コマンドや、余計なスペースや改行を除去する「トリミング」・「クリーン」コマンドです。
それぞれ「値の置換」コマンドよりも操作がシンプルなため、ぜひセットで覚え、状況に応じて使い分けていきましょう！