Excelで栄養計算ソフトを自作しよう!第6回:合計行を表示しよう

シェアする

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

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

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

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

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

今回は、栄養計算の結果を表示する合計行を表示してみます。それに合わせて、形式も少し修正しておきましょう。

では、行きます。

前回のおさらい

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

前回は、たんぱく質やなどの、エネルギー以外の栄養素について、食品番号から成文値を検索・表示し、それを重量で乗じることで食品の栄養価を算出しました。

その際に、絶対参照を活用し、数式のコピペだけで簡単に機能を実装することができましたね。

今回は、その続きから。合計行を追加し、1日分の栄養価を算出してみましょう。

少し形式を整えよう

まずは、少し形式を整えていきましょう。

具体的には、

  • 栄養成分の単位の修正
  • 廃棄率の表示
  • 調理前重量・可食部重量の追加

を行っていきます。

栄養成分の単位の修正

まずは栄養成分の単位を修正しましょう。

現在のところ、栄養成分名と栄養成分の単位が入力されている行(2・3行目)は以下のようになっています。

単位をよく見ると、100gあたりの単位になってしまっているのがわかるかと思います。ですので、「/100 g」を削除する必要があります。

これは、置換機能を用いて行います。以下のようにすればOKです。

検索する文字列には「/100 g」と入力し、置換後の文字列には何も入力しません。これで置換すを行うことで/ 100gを削除できます。スラッシュの前には半角スペースが入っていますので、これもしっかりと入力するようにしましょう。

以下のようになればOKです。

廃棄率の表示

今まで空白だった、廃棄率を算出しましょう。

廃棄率についても、いままでと同じようにVLOOKUP関数を使っていきます。

まずはセルE4に入力してみましょう。エネルギーの計算に用いた計算式をコピーして、少し修正すればOKですです。

そうすると、以下のような計算式になるはずです。

=VLOOKUP($A4,本表!$B$9:$BO$2199,MATCH(E$1,本表!$B$5:$BO$5,0),FALSE)

修正点としては、

  • 重量を乗じる箇所を削除
  • 成分名を検索する箇所を一行上に変更

廃棄率は食品の重量によって変化するものではありません。食品が10gだと廃棄率が10%で、200gだと20%になる、なんてことはないのですね。なので、重量を乗じていた箇所は削除します。

次に、成文値名をMATCH関数で検索していましたが、これを一行上にずらします。廃棄率という値が入力されているのが一行上のためですね。なので、検索値も検索する範囲も、一行上にしましょう。

こうすることで、廃棄率も表示できます。下までコピペして、他の行にも表示させておきましょう。

また、成分表の一番右側の項目、「重量変化率」も単位は%です。そのため、計算式から重量を乗じる部分を削除しておきましょう。

調理前重量・可食部重量の追加

せっかく廃棄率を表示したのですから、調理前の重量(廃棄部分がある重量)から調理後の状態(廃棄部分がない重量)に換算できるようにしましょう。

そのために、まずは、廃棄率の手前に調理前重量を入力する列を、廃棄率の後ろに可食部重量を入力する列を挿入しましょう。そうすることで、計算の過程がわかりやすく、可読性が向上します。

まずは、今重量が表示されている列を、廃棄率の後ろ側に移動させましょう。

重量の列を選択し「切り取り」をクリックします。

そして、エネルギーの列を選択し「切り取ったセルの挿入」をクリックします。

重量の列が廃棄率の後ろ(次の列)に移動しましたね。

次は、廃棄率の手前に調理前重量を挿入しましょう。以下のように、廃棄率の列を選択し、右クリックからメニューを開き、挿入をクリックしてください。

挿入した列の項目名は「調理前重量」とでもしましょうか。

以下のような順番になれば正解です。

ここで、単位も追加しておきましょう。調理前重量・重量ともに単位は「g」です。それぞれの列の3行目に「g」を追加してください。

では、調理前重量から重量(栄養計算に使用する重量)を算出してみましょう。調理前重量を入力すると、重量が算出されるようにします。そのため、計算式は重量の列に入力することになりますね。

重量は、調理前重量に可食部率を乗じることで算出できます。可食部率(%)は、100-廃棄率で算出できますので、式は、

調理前重量 × (100-廃棄率) / 100

となります。これにしたがって、セルF4(重量の列の最初の行)に計算式を入力してみましょう。以下のようになります。

=$D4*(100-$E4)/100

これを下方向にもコピーしてすべての行でも使えるようにしておきましょう。

そして、調理前重量を入力することで、重量の値が変化することなどを確認してみてください。

合計行を追加しよう

では、合計行を追加してみましょう。

これはまったく難しくありません。よいのです。

計算式が挿入されているセルの一番下に、合計を意味する関数を挿入すれば良いのです。それはSUM関数を利用することで実装できます。

私の現在のシートの状態はこのようになっており、17行目まで計算式が入っており、18行目からは空白です。

そのため、18行目に合計行を入力します。合計は、SUM関数を用いて算出します。目的のセルを選択した状態で、メニューにある「オート SUM」から「合計」を選択します。

そうすると、以下のようになります。

これで合計を算出することができます。これを右側にもコピペしておきましょう。

そして、合計行であることを示すために、一番左側に合計と入力し、フォントを太字にするなどして書式を整えると以下のようになります。

これで栄養成分の合計を表示することができましたね。

今回はここまでです。

まとめ

今回は、形式を整え、合計を表示するための合計行を作成しました。

次回は、朝・昼・夕毎に合計を表示できるようにしていきます。

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

連載目次