栄養計算ソフトのための食品成分表をExcelで作成しよう!

Excel

みなさん、こんにちは。
シンノユウキ(@y_stadio)です。

今回は,「Excelで栄養計算ソフトを自作しよう」の第2回で,

Excelで栄養計算ソフトを自作しよう!
「Excelで栄養計算ソフトを自作しよう」の連載第1回。この連載を通してExcelで栄養計算を自作できるようにしていきます。

こちらの記事の続きです.

栄養計算ソフトを作成するためには,それに最適化された食品成分表が必要になります.食品成分表は,文科省からExcel形式で配布されていますが,これをそのまま栄養計算ソフトの食品成分表として利用することはできません.活用しやすいように,整理する必要があります.

今回は.食品成分表をダウンロードし,それを栄養計算に用いることができるように整えるという作業,すなわち食品成分表のダウンロードとデータクリーニングを行っていきます.

では,行きましょう!

楽したい!という方は

ここでは,手作業にて食品成分表をクリーニングする作業を行っていきます.しかし,それが面倒で,楽したいなーという方は,以下のリンクから,Excelのマクロ機能を用いてクリーニングできる方法を紹介しています.よろしければ参考にしてください.

そのままでは計算に使えない食品成分表をクリーンアップするプログラム
食品成分表が文部科学省のHPで公開されていますが、数字が文字列で入力されていたり、計算に利用でいないハイフンが入力されていたりして、そのままの形では栄養計算に使用する事ができません。 そのため、それをクリーンアップし、栄養計算可が能な...

 

前回のおさらい

まずは前回のおさらいから.

Excelで栄養計算ソフトを自作しよう!
「Excelで栄養計算ソフトを自作しよう」の連載第1回。この連載を通してExcelで栄養計算を自作できるようにしていきます。

前回は,これから作成していく栄養計算ソフトについて,どのようなソフトにしていくか,使い方と完成画面を用いて説明していきました.

また,
・なぜ自作しなければならないのか
・なぜ自作したほうが良いのか
について,そのメリットなどをお伝えしました.

いよいよ今回から実際のソフトの作成に入っていきます.

 

食品成分表をダウンロードしよう!

まずは食品成分表をダウンロードしましょう.

文部科学省のHPからダウンロードできる

少し前だと食品成分表はPDFや紙の状態でしかデータがなく,加工や利用が非常に難しくなっていました.しかし,現在ではエクセルファイルの状態でデータを利用することができます.便利になりましたね.

以下のURLからダウンロードしてください.

一括ダウンロード(Excel:日本語) (Excel:917KB) excel

文部科学省から運営している「日本食品標準成分表2015年版(七訂)について」のページで,それについてのExcelファイルが一括で入手できます.

 

本表のみ利用

日本食品標準成分表には,一般成分が掲載されている成分表の他に,アミノ酸成分表や脂肪酸成分表,炭水化物成分表などがありますが,今回は簡便化のために一般成分のみが掲載されている成分表を利用します.もし,今回利用しない成分表についても栄養計算ソフトに組み入れたいという要望がありましたら,後日オプションとして掲載します.

 

食品成分表について

成分表の中身を確認してみよう

食品成分表がダウンロードできたら,そのファイルの中身を確認してみましょう.

こんな感じのExcelファイルが開けましたか?

たくさんの食品について,エネルギーやたんぱく質などの成分値が記載されています.これを利用することで栄養計算を行います.

結構多くの成分値が掲載されていますよね.一般的な利用をする分には,アミノ酸成分表などは不要なことが多く,この成分表だけで多くのことを行うことができます.

食品数もかなり多く掲載されており,日常食べるような食品のほとんどが掲載されいてます.

このままでは栄養計算に使えない!

ただし,このそのままの状態では栄養計算に利用することはできません.それは以下のような理由によります.すなわち,

  • 文字列で入力されたデータがある
  • 未測定やTrで入力されたデータがある

ことによります.Excelでは,ちゃんとした数字しか計算することができません.

この画像で,セルの左上に緑の三角が付いているセルがありますよね?この画像だと,48.0や10.0,14.0などです.こういったデータは文字列で入力されているため,栄養計算に利用することはできません.そのため,こういったデータは修正する必要があります.

なぜこうなってしまっているのでしょう?それはおそらく,有効数字の関係だと思います.Excelでは,「48.0」と入力しても,しっかりと設定をしておかないと「48」と入力されてしまいます.この2つは似ているようで厳密には異なります.そのため,それを表現するためにこういった表示になってしまっているのでしょう.

また,Trと入力されているデータがあります.こういった記号や記法にはそれぞれ意味があります.それをまとめたのが以下の表です.

参考)http://www.mext.go.jp/component/a_menu/science/detail/__icsFiles/afieldfile/2017/02/16/1365334_1-0101r9_1.pdf

こういった記号や記法で書かれたデータも,栄養計算には用いることができません.そのため,こういった記号や記法で書かれた箇所も修正する必要があります.

 

食品成分表のデータをクリーニングしよう

では,これから食品成分表のデータを直していきましょう.上で紹介した,記号や記法は次のように修正することにします.

  • -,Tr,(0),(Tr)については0に
  • (数字)については数字に

少し乱暴に思えるかもしれませんが,栄養計算を行う上では仕方のない誤差です.市販の栄養計算ソフトでも,このような処理を行っています.これに伴う誤差が生じるということも覚えておきましょう.

では,順番にやっていきましょう.

 

-やTrなどを0に

まずは,-やTrなどを0に,(数字)は数字に修正していきましょう.

置換で-を0に

最初は, - からです.- を0に変換します.これにはExcelの機能,「置換」を使用します.

まずは置換する範囲を選択してください.A9:BP2199までですね.

次に置換を行います.

Ctrl + H で検索と置換画面を開き,
検索する文字列→ -
置換後の文字列→ 0
を入力します.

その後,「セル内容が完全に同一であるものを検索する」にチェックを入れます.これは誤った置換を防ぐため防衛策ですね.

そして,「すべて置換」をクリックします.そうすると,未推定を意味する - が0に置換されます.

Trも0に置換しよう

同様の手順で,Trも0に変更します.置換画面で,
検索する文字列→ Tr
置換後の文字列→ 0
を入力します.その後,置換を行います.

()も取り除こう

次は,()で囲まれた値を変更します.この場合は,()を削除したほうが後々の作業で便利です.手順は先ほどのと同様.置換画面で,
検索する文字列→ (
置換後の文字列→
を入力します.検索する文字列は()を1つずつ入れていきます.

置換後の文字列は空白にしてください.
また,この場合は,「セル内容が完全に同一であるものを検索する」のチェックを外します.

以下の2つの画面になればOKです.

 

これで,大まかなクリーニングが終了しました.

後は文字列を数字に修正する作業が残っているだけです.

文字列を数字に修正しよう

まずは,文字列で入力されたデータを文字列に修正していきます.

文字列で入力されたデータを数字に変換するためには,そのセルにカーソルをあわせて,

表示された注意マークをクリックします.そうすると,次のようなメニューが現れます.

この状態で,「数字に変換する」をクリックすると,

緑の三角が消え,数字に変換されたことがわかります.ただし,これを一つずつやっていくのは大変ですよね.そこで一気に行います.やり方は簡単.全ての範囲を選択した状態で,同じように数値に変換すれば良いのです.

まず,このような感じで全ての範囲を選択します.そして,この状態で,行います.そうすると,選択した範囲全体について,行うことができます.

この作業は非常に時間のかかる作業です.場合によっては1分以上かかる場合も.Excelがエラーを起こさない限りは根気よく待つようにしましょう.

以上で,食品成分表のデータクリーニングは完了です.成分値が栄養計算できる形式に整いました.今回はこれで終了です.

 

まとめ

今回は,食品成分表をダウンロードし,それを栄養計算可能な形式に修正する作業を行いました.まとめとして,行った作業をリスト化しておきます.

  • 食品成分表のダウンロード
  • 「-」を「0」に置換
  • 「Tr」を「0」に置換
  • 「(」を「」(空白)に置換
  • 「)」を「」(空白)に置換
  • 文字列を数値に変換

今回はここまで.次回からソフト作成の具体的な部分に踏み込んでいきます.

 

連載目次

  1. Excelで栄養計算ソフトを自作しよう!
  2. 栄養計算ソフトのための食品成分表をExcelで作成しよう!
  3. VLOOKUP関数で食品成分表から食品名を検索しよう!
  4. Excelで食品成分表から食品のエネルギーを算出してみよう!
  5. Excelで食品成分表から色々な栄養素の成分値を算出してみよう!
  6. SUM関数で算出した栄養素を合計してみよう!
  7. SUMIF関数で食事区分ごとに栄養素を合計しよう!
  8. 【完成】Excelで栄養計算ソフトを作成してみた!