Excelで食品成分表から食品のエネルギーを算出してみよう!【成分表2020年版対応】

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

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

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

今回から実際に栄養計算を行えるようにしていきます。

まずは食品番号と重量からエネルギーを算出する方法について紹介してます。

では,行きましょう!

スポンサーリンク

前回のおさらい

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

前回はVLOOKUP関数を用いて,食品番号から成分表の食品名を検索しました。

数式は以下のようなものでしたね:

=VLOOKUP(B12,本表!A12:BH2489,3,FALSE)

これを「栄養計算シート」のセルD12に入力しました。

そして,セルB12に食品番号を入力すると,食品名がセルD12に表示される,というところまで行いました。忘れてしまった方は前回の記事を確認しておきましょう。

今回はその続きからです。

まずはエネルギーを検索・表示しよう

今回は,下記の2つを行います。

  1. 食品のエネルギーを検索・表示
  2. 食品の重量に応じてエネルギー値を計算

まずは,食品番号からエネルギー値を検索し,表示してみます。

これには,前回の食品名と同様にVLOOKUP関数を使用します。
食品番号から食品名を検索・表示したのと同じように,食品番号からエネルギーを検索・表示するという流れになります。

【復習】VLOOKUP関数について

本題に入るまえに,VLOOKUP関数についてもう一度確認しておきましょう。
VLOOKUP関数は以下のような構造を持っていました。

これを食品名に当てはめた場合は,下記のようになるのでしたね:

  • 検索値:食品の食品番号(01001など)
  • 範囲:食品成分表の本表シート
  • 列番号:食品名のある列(食品番号がある列から数えて3列目)
  • 検索方法:部分一致では他の値を拾う可能性があるため完全一致→FALSEで

VLOOKUP関数でエネルギーの列を検索しよう

食品名を検索する数式は下記でした:

食品名を検索
=VLOOKUP(B12,本表!A12:BH2489,3,FALSE)

これを少し変更して,エネルギー値を検索・表示してみましょう。

エネルギーの列は,食品番号のある列から数えて5つ目にあります。
そのため,列番号の部分を5に変更すれば良いということになります。

すなわち,数式は以下のようになります:

エネルギー(kJ)を検索
=VLOOKUP(B12,本表!A12:BH2489,5,FALSE)

これを,エネルギー(kJ)を表示したいセルG12に入力しましょう。
以下のような結果になれば成功です:

重量に応じてエネルギー値を計算しよう

上記までで,食品のエネルギーを検索・表示できました。

しかしこれでは,ただ成分表に記載されているエネルギー値を表示しただけです。
実際の栄養計算では,食品の重量に応じて計算しなければなりません。

これを実現するため,数式を修正していきましょう。現在の数式では,成分表に掲載されている100gあたりのエネルギー値が表示されています。この値を入力した任意の重量あたりのエネルギー値に計算します。計算式は下記のようになります:

エネルギー(kJ/g) = 100gあたりのエネルギー値(kJ) / 100(g) × 任意の重量(g)

この計算式をもとに,セルG12の数式を変更してみましょう。
下記のようになります:

=VLOOKUP(B12,本表!A12:BH2489,5,FALSE) / 100 * E12

赤字の部分が追記した箇所です。
E12は計算したい任意の重量が入力されているセルです。試しにE12のセルに重量を入力してみましょう。

最終的に以下のような画面になれば成功です。

これで重量あたりのエネルギー値を計算することができました!

筆者
筆者

試しに,重量の場所を10gとか200gとか別の値にしてみて,きちんと計算されているか確認してみましょう。値もしっかりと変化し,重量に応じて計算できていることがわかるかと思います。

まとめ

今回は下記を行いました:

  1. 食品のエネルギーを検索・表示
  2. 食品の重量に応じてエネルギー値を計算

次回は,エネルギーだけではなく,他の成分もエネルギーと同様に検索・表示し,重量に応じて計算されるようにシートに追記していきます。

では,また次回から頑張りましょう。

次回の記事はこちら:

連載目次

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