列内の既存の値を任意の新しい値へ置換する方法[Power Query(パワークエリ)基礎]
AさんAさん

Power Query(パワークエリ)で置換するにはどうした良いですか?

森田森田

その場合、Power Queryエディター上の「値の置換」というコマンドをクリックすれば良いですよ!

具体的な使い方について解説していきますね。

はじめに

本題に入る前に、この記事がおすすめな方を挙げてみます。

  • 作成されたクエリに対して修正や削除などの操作を行いたい方
  • データ整形・加工の工程が複数あり、かつその作業が定期的に発生する方
  • Power Query(パワークエリ)の使い方を習得したい方

前提条件

この記事のテクニックを使うためには、以下の条件を満たす必要があります。

  • ExcelのバージョンはExcel2010以降(Microsoft365含む)が必須
  • Excel2010/2013ユーザーの場合、事前にMicrosoft社公式HPよりPower Query(パワークエリ)のダウンロードが必要(Excel2016以降は標準機能)
  • Power Query(パワークエリ)の概要を理解していること
    →まず、以下の記事で概要を把握することをおすすめします。

元データの値の修正や変更したい場合に「置換」は有効

置換とは、現状の値を別の新しい値へ置き換えることです。

上記の例のように、「グレープ」という文字列を「ぶどう」に変換するなどのイメージですね。

このように置換は、元データの値に誤りがあった場合、または変更する場合に有効です。

ちなみに、Excelワークシート上で置換を行う方法は以下の2パターンあります。

  1. 「検索と置換」ダイアログを利用する
  2. SUBSTITUTE関数を利用する

ただし、他のデータ整形作業とセットで置換を行うなら、Power Query(パワークエリ)で行った方が、一連の作業手順を記録できて自動化できますよ!

では、Power Query(パワークエリ)での置換の手順を確認していきましょう。

Power Query(パワークエリ)での置換の手順

今回の前提として、置換対象のテーブルはブック内にあり、事前にPower Queryエディターに取り込んでいる状態からスタートしますね。(ブック内のデータ取得について復習したい方は下記記事を参照)

また、今回は「グレープ」を「ぶどう」へ置換してみましょう。

置換は以下の2ステップとなります。

STEP1】リボン「ホーム」タブから「値の置換」をクリック

まず、置換対象のデータが含まれる列を選択(①)しましょう。

これは、通常のワークシート上の操作と同じ感覚で問題ありません。

次に、リボン「ホーム」タブをクリック(②)し、「値の置換」をクリック(③)してください。

なお、すでにリボン「ホーム」タブが開かれている場合は、手順②は不要です。

【参考】リボン「変換」タブにも「値の置換」コマンドはある

ちなみに、リボン「変換」タブでも同じコマンドがあるため、こちら経由でも問題ないです。

STEP2】「値の置換」ダイアログへ置換前後の文字列を入力

「値の置換」ダイアログが起動します。

ダイアログ内の[検索する値]には置換前の文字列を入力(④)し、[置換後]には文字通り置換後の文字列を入力(⑤)したらOK」ボタンをクリック(⑥)してください。

「プレビュー」ウィンドウ上で置換が確認できたらOK

「プレビュー」ウィンドウを見ると、無事設定どおりに「グレープ」という文字列が「ぶどう」へ置換されましたね!

あとは、その他の処理を行い、任意の方法でデータを出力すれば完了です。

【参考】置換の内容を変更したい場合は

もし、置換の内容をあとで変更したい場合は、「クエリの設定」ウィンドウの任意のステップをダブルクリック、もしくは歯車マークをクリックしましょう。

すると、再度STEP2の「値の置換」ダイアログが置換前後の文字列が入力された状態で起動します。

あとは、任意の部分を入力し直して、再度「OK」ボタンをクリックすれば良いですね。

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

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

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

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

ファイルを開いたら、置換を行う前に、Power Queryエディターを起動させてくださいね。
ブック起動時、「セキュリティの警告」メッセージが表示された場合は「コンテンツの有効化」をクリックしてください。

  1. リボン「データ」タブをクリック
  2. 「クエリと接続」をクリック
  3. 「商品マスタ(置換)」クエリをダブルクリック

ここまで準備ができたら、次の手順を実施してください。(今までの解説のまとめです)

  1. 置換対象のデータが含まれる列を選択
  2. リボン「ホーム」タブをクリック ※リボン「変換」タブでも可
  3. 「値の置換」をクリック
  4. 置換前の文字列を入力
  5. 置換後の文字列を入力
  6. OK」ボタンをクリック

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

さいごに

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

置換はデータ整形作業の中でもオーソドックスな処理のひとつです。

集計前にきれいなデータへ整えるための事前準備として、置換はよく使いますので、ぜひ本記事をもとにしっかりと身に着けてくださいね。

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

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

森田森田

Excelに不慣れなころ、はじめて置換で一気に複数のデータを変換したときは感動しました!

これを1セルずつ手作業で修正するしかなかったとしたら・・・、絶望以外なかったと思います。。
それ以降は私自身、置換の利用頻度は非常に高いですね。

Power Query(パワークエリ)においても、置換は有効な機能のひとつですので、ぜひぜひご活用ください!