Power Queryで値を置換する方法【完全一致と部分一致】

Excel

みなさん,こんにちは。
シンノユウキ(shinno1993)です。

食品標準成分表をPower Queryで整形する方法について,連載形式で紹介しています。

前回の記事はこちら

Power Queryで不要な行・列を削除する方法を紹介しました。前回までで複数の食品群に分かれていたシートを結合し,不要な行・列を削除することでデータをテーブル形式にすることができました。

しかし,まだ計算に使用するためにやらなければならないことがあります。食品標準成分表では,資料としての正確性を期すため,数値を特別な文字列や記号で表現している箇所があります。文字列は計算に使用できませんので,何らかの数字で置換する必要があります。

そこで今回はPower Queryで値を置換する方法について紹介します。完全一致の場合と部分一致の場合の2パターンで紹介します。

なお,食品標準成分表で使用される特別な文字列や記号の意味,そしてそれをどのように置換するかについては,下記のようにしたいと思います:

記号意味置換後
未測定0
0最小記載量の1/10未満または検出されない0
Tr最小記載量の1/10以上かつ5/10未満0
(数値)推計値数値
(0)含まれていないと推定される成分0
[数値]原材料の収載値等と配合割合から栄養計算の手法を用いて計算された成分値数値

この置き換え方について,詳細は食品成分表の記号の意味と栄養計算での扱いについてをご確認ください。

では行きましょう!

本記事は連載:「食品標準成分表をPower Queryで整形する方法」の一部です。前回までに行った作業を前提にしますので,連載の初回(食品標準成分表をPower Queryで整形する方法)からご覧いただくことをオススメします。

スポンサーリンク

Power Queryで値を完全一致で置換する方法

置換対象のうち,下記の3つは完全一致を使用して値を置換していきます:

  • *
  • Tr
  • Tr†
  • (Tr)
  • (0) *全角のパーレン

上記の「*」は一部の食品のヨウ素に付された注釈です。標準の値を定めるのが困難ため,この記号が使用されています。注釈を確認し,何らかの値で代替すべきですが,今回は0で置換することとします。

ステップ①:置換対象の列をすべて選択する

まず置換対象となる列をすべて選択します。栄養素の成分値が含まれる列が置換対象ですので,「REFUSE」から「NACL_EQ」までを選択します。

この際,「REFUSE」の列を選択し,「NACL_EQ」までウインドウをスクロール,Shiftキーを押しながら「NACL_EQ」の列を選択すると,間の列も一括で選択できて便利です。

ステップ②:「値の置換」を行う

そして,メニューの「ホーム」→「変換」内にある「値の置換」をクリックします:

値の置換

置換のダイアログが表示されますので,①「検索する値」に上記で示した6つのうちの1つを入力→②「置換後」には0をそれぞれ入力→③「OK」をクリックします。これを6つの文字列でそれぞれ繰り返します:

完全一致による置換

これで完全一致による置換が完了しました!

Power Queryで値を部分一致で置換する方法

次に部分一致による置換を行っていきます。置換対象は下記です:

  • (数字)
  • [数字]
  • 数字†

これらは,それぞれカッコの中や†の前の数字が異なります。そのため,完全一致による置換は現実的でありません。

そこで部分一致を使用します。完全一致が無理なら,カッコを1つ1つ削除(空白で置換)すれば良いだけです。

詳しい方は,「ワイルドカード検索(正規表現による検索)をすれば良いのでは?」と思われた方もいるかもしれません。はい,それができればそれがベストです。しかしPower Queryでは標準でサポートされていません。なので,上記のように地道に置換していきます。

さて,部分一致を使用する場合でも問題は生じてきます。それは,数値型の列は部分一致が不可能ということです。テキストだけの列であれば部分一致による検索が可能ですが,数値の含まれる列ではできません(通常,数値と文字列が混在することはないでしょうし)。困りました。どうすれば良いでしょうか。

こういう時は個々人のひらめきによる力業を使います。基本機能を基本でない使い方によってでカバーします。ただし,乱用は厳禁です(後からツラくなります)。

今回の場合は,まず成分値の列をすべてテキスト型に変換します。そして,部分一致による置換を行った後,数値型に戻すという方法をとります。これで,部分一致による置換が行えて,かつデータ型も計算に使用可能な形に変換できます。

ステップ①:データ型をすべて「テキスト」に変換する

では早速やっていきましょう。まず完全一致と同様に置換対象となるすべての列を選択します。そして,①「変換」→②「データ型:すべて」→③「テキスト」を選択します:

テキスト型への変換

これでデータ型を「テキスト型」に変換できました!

ステップ②:「値の置換」を行う

そして,すべての成分値列を選択した状態で「値の置換」をクリックし,下記の作業を行ってください:

値の置換
  1. 「検索する値」に ( ・ ) ・ [ ・ ]・† を1回ずつ入力します。
  2. 「置換後」には何も入力しません(空白)。
  3. 「セルの内容全体の照合」のチェックは外します(最初から外れていた場合は操作不要です)。
  4. 「OK」をクリックします。

置換の対象となる文字列は5つありますので,①~④の作業を①の「検索する値」を変更しながら繰り返してください。

任意のデータ型に変更する

最後に,テキスト型のデータ型を任意のデータ型に変更します。すべての成分値列を選択したままの状態で,①「変換」→②「データ型:テキスト」→③「10進数」を選択します:

10進数型への変換

これでテキスト型で部分一致による置換を行った後,計算可能なデータ型(10進数)へ変換することができました!

まとめ

今回は,Power Queryで値を置換する方法について,完全一致の場合と部分一致の場合の2パターンで紹介しました。これで,本表単体としては計算可能な形に成形することができました。

しかし実は本表には,別表からいくつかの食品を加える必要があります。そこで次回は,別表にしかない食品を抽出する方法を紹介したいと思います。

連載目次

  1. 食品標準成分表をPower Queryで整形する方法【栄養士向け】
  2. 栄養士のためのPower Query基礎知識【できること・メリット】
  3. 栄養士のためのPower Query超入門【インポート・変換・書き出し】
  4. Power Queryで複数のシートを縦に結合する方法
  5. Power Queryで不要な行・列を削除する方法【データクリーニング】
  6. Power Queryで値を置換する方法【完全一致と部分一致】現在のページ
  7. Power Queryで食品標準成分表の別表のデータを整形する方法
  8. Power Queryで特定シートのみに含まれるレコードを抽出する方法
  9. Power Queryで複数のシートを横に結合する方法【クエリのマージ】
  10. Power Queryでファイルパスを相対パスで指定する方法
タイトルとURLをコピーしました