Excelで栄養計算ソフトを自作しよう!第4回:食品のエネルギーを算出してみよう

シェアする

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

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

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

みなさん、こんにちは。 シンノユウキです。 今回は、「Excelで栄養計算ソフトを自作しよう」の第3回で、 こちら...

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

今回から栄養計算を行っていくわけですが、手始めに、食品のエネルギーの検索・表示から行ってみましょう。

その後、食品番号と重量を入力することでエネルギー値を算出できるようにしていきます。

では、行きましょう。

前回のおさらい

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

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

計算式は、以下のようなものでしたね。

=VLOOKUP(A4,本表!B9:BO2199,3,FALSE)

これを、「栄養計算シート」のセルC4に記述しました。

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

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

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

今回は、

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

の2つを行います。

まずは、単純にエネルギーを検索し、それを算出してみましょう。千里の道も一歩から。最初から多くを行おうとせずに、少しずつ小さなところから始めていきましょう。

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

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

これを食品名に当てはめた場合は、

  • 食品番号を検索値として、
  • 食品成分表の範囲の中から、
  • 食品名のある列を表示したい。
  • 検索方法は、部分一致では他の値を拾う可能性があるため完全一致で。

のようになるのでしたね。

これをエネルギーの表示に利用するということなので、表示したい列を意味する、列番号の部分を変更すると良いでしょう。エネルギーの列は、食品番号のある列から数えて、5つ目にあります。そのため、列番号の部分を5に変更すれば良いということになります。

注意点として、ここで列場号を6としてはいけません。よく間違えてしまうポイントですが、VLOOKUP関数の列場号は、シート上の列番号ではなく、選択している範囲においての列番号を入力する必要があります。そのため、A列(一番左)から数えて何列目かではなく、選択している範囲の一番左(今回の例ではB列)から数えて何列目かということになります。この点はよく間違えてしまうポイントですので、注意しましょう。

すなわち、数式は以下のようになります。

=VLOOKUP(A4,本表!B9:BO2199,5,FALSE)

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

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

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

でもこれでは、ただ成分表に記載されているエネルギー値を表示しただけです。実際の栄養計算の場合は、食品の重量に応じてこの値を変更しなければなりませんよね。成分表の値は食品100gあたりの重量ですから、これを食品の重量に応じて変更させる必要があるのです。

そのためには、成分値を1gあたりの成分値に換算し、それを食品の重量と乗じなければなりません。そのための計算式を入力していきましょう。

まずは仮の値として、重量のセルに50を入力してください。今回の場合はセルD4になります。

こんな感じですね。

では、この値に応じてエネルギー値を計算してみましょう。これはエネルギーを表示するセルF4で行います。結論からお示しすると、計算式は、

=VLOOKUP(A4,本表!B9:BO2199,5,FALSE) / 100 * D4

のようになります。背景色が黄色の部分が、今回追加した部分です。

食品成分表に掲載されている成分値は、100gあたりのものなので、それを100で割ることで、1gあたりの成分値に換算しています。

これを、食品の重量と乗じることで、重量あたりの成分値を算出できるのですね。D4が重量が入力されているセルのアドレスですので、これを入力します。

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

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

まとめ

今回は、成分表からエネルギーを検索・表示し、それを重量に応じて計算されるよう、計算式を追加しました。

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

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

連載目次