食品標準成分表をPower Queryで整形する方法【栄養士向け】

Excel

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

食品標準成分表2020年版(八訂)が公表されました。同時に,収載されている食品のそれぞれの成分値もExcelファイルとして公表されました。書籍やPDFだけでなく,Excelファイルでも公表されることにより,食品標準成分表のより一層の活用が期待されます。

しかしこの公表されたデータ,計算用のデータというよりは,それ単体で閲覧しやすいように作成されています。まるで電子書籍として読むことを想定しているかのよう。そのため,計算等に使用するデータとしては,非常に使いづらくなっており,何らかの変換をしないことには計算用のデータとして利用するのは困難です。

そこで,今回から食品標準成分表をPower Query(標準で使えるExcelの機能)を使用し,整形→データとして使える形にする方法について,何記事かに分割して解説します。今回は,食品標準成分表の特徴と,データの整形に使用するPower Queryの概要について紹介します。

では行きましょう!

スポンサーリンク

食品標準成分表をPower Queryで整形する方法

食品標準成分表はExcelファイルとして公表されていますが,そのままの形式では計算等に使用することはできません。そこで,データの整形ツールとして,Excelの機能:Power Queryを使用すると便利です。

食品標準成分表のデータは“データ”として使えない

『食品標準成分表2020年版(八訂)』でデータとして公表されているExcelファイルは,見やすさや資料としての厳密さを重視して作成されているためか,そのままの状態では計算用のデータとして使用できません。

たとえば,以下のような特徴のために,計算時にはデータの変換が必要となります:

  • 最小表示の位の表現に文字列を使用している場合がある:食品標準成分表では,栄養素ごとに表示桁数が決まっています。たとえば「たんぱく質」は,小数点第2位を四捨五入し,小数点第1位まで表示することとされています。ここで,Excelデータとして提供する独特の問題が生じます。Excelでは,たとえば「12.0」を入力する場合,自動的に「12」として,小数点以下の0が省略されてしまいます。「12」と「12.0」は厳密には異なる値です。「12」が入力されるのは困ります。成分表ではこれを避けるため「12.0」を文字列として入力されています。

  • データの一部として文字情報が入力されている:上記以外でも,データの一部として文字情報が入力されています。たとえばTr。これは最小記載量の最小記載量の1/10以上~5/10未満含まれることを示します(参考:食品成分表の記号の意味)。栄養計算時は一般的には0で代替されますが,食品標準成分表の資料としての性質上,0ではなくTrとして入力されてしまっています。

  • 同一食品のデータが複数シートに分割されている:食品標準成分表では一般成分が収載されている「本表」と呼ばれるメインのデータの他に,アミノ酸・脂肪酸・炭水化物などの成分値が収載されている「別表」と呼ばれるデータもあります。これらに収載されている食品は共通していますが,それぞれ別々のExcelファイルとして分割されています。計算時には,これらのデータが1つにまとまっていた方が便利です。

データ整形ツールとしてのPower Query

上記のように,食品標準成分表のデータを計算に使用するためには,データの整形が必要となります。それには,Excelの機能の1つであるPower Queryを使用すると便利です。

通常,こういったデータの整形には,Excel関数やプログラミング言語であるVBA,データベースを操作するSQLなどの知識が必要となります。しかし,これらの習得には,一般的に困難を伴います。

Power Queryを使うことで,上記のような知識がなくても,データの整形が可能になります。複数のソースからデータを読み込み,文字列を一括で代替し,計算可能な形として書き出します。習得に必要な時間も最小限。最新のExcelさえあれば,他に必要なツールもありません。

具体的なPower Queryでできることについては,次回の記事で紹介します。

Power QueryはExcel関数やVBAの単純な代替か?

Power Queryを使うことで,食品標準成分表を計算可能なデータとして,簡単な操作で書き出すことができます。これにより,これまでデータの整形に使用してきたExcel関数やVBAは不要になるのでは?と思われるかもしれません。しかしPower Queryは,それらの単純な代替品ではありません。

Power Queryでは難しい・遅い作業もある

たとえば,Power Queryの標準機能だけでは難しい置換作業もあります。これらはExcel関数やVBAを使用することで解決できます。また,Power Queryの動作はそれほど速くありません。VBAだと1秒もかからずに終わる作業がPower Queryだと数十秒かかることもざらです。そのため,Power Queryを使うからといって,Excel関数やVBAが不要になるというわけではありません。

Excel関数やVBAの習熟者がPower Queryを学ぶメリットあり

しかし逆に,Excel関数やVBAに習熟している人がPower Queryを学ぶメリットがないかといえば,そういう訳でもありません。これまで,数時間かけてコーディングしてきたことが15分程度のマウス操作で済むようになるかもしれません。これは非常に大きなことです。具体的なPower Queryを使うメリットについては,時間の記事で紹介します。

まとめ

今回は食品標準成分表のデータとして特徴や,食品標準成分表を整形するためのツールとしてPower Queryを紹介しました。

Power Queryは従来のExcel関数やVBAとも共存可能な,非常にパワフルなツールです。活用できるようになると,データ活用の幅が広がることと思います。

次回は,Power Queryの基礎知識について紹介します:

連載目次

  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をコピーしました