食品標準成分表を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で特定シートのみに含まれるレコードを抽出する方法
タイトルとURLをコピーしました