Excelで栄養計算ソフトを自作しよう!第2回:食品成分表のダウンロードとクリーニング

シェアする

  • このエントリーをはてなブックマークに追加

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

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

みなさん、こんにちは。 シンノユウキです。 今回から、何回かに分けて、Excelを用いた栄養計算ソフトを自作していきます。 ...

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

栄養計算ソフトを作るにあたっては、それに用いるための食品成分表を用意する必要があります。

今回は、文科省が用意しているものを用いますが、このデータはすぐに栄養計算に用いることができません。数値が文字列で入力されているなど、そのままの状態では利用できないのです。

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

では、行きましょう。

前回のおさらい

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

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

また、なぜ、自作しなければならないのか、またはなぜ自作したほうが良いのか、理由やすることでのメリットをお伝えしました。

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

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

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

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

一昔前だと、食品成分表は紙の状態でしか公表されておらず、それをデータに落とすためには大変な苦労がありました。ひとつひとつ手でデータを入力していくのですね。

しかし、今はExcelデータでダウンロードできる時代です。いい時代になりました。

食品成分表は以下の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に、(数字)は数字に修正しよう

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

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

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

次に置換を行います。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

まとめ

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

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

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

連載目次