[Excel]献立作成ソフトを作ろう 第10回 レシピ作成シートの作成(7)
献立作成ソフトを作ろう第10回目は、レシピの栄養計算を行います。今回の内容は、これまでの内容を少し応用したものとなっています。
レシピの栄養計算
早速、栄養計算を行っていきましょう。まずは成分表からカロリーを取得します。栄養成分の取得方法は、第8回で成分表から成分表名欄(J10:J29セル)に食品名を取得した方法と同じです。J10セルをコピーしてL10セルに貼り付けます。L10セルの数式が「=IFERROR( INDEX(成分表!F:F, $U10, 1), “")」となっているはずです。コピー元(J10セル)の数式は、「=IFERROR( INDEX(成分表!D:D, $U10, 1), “")」でしたので、コピーによりINDEX関数の第1引数が「成分表!D:D」から「成分表!F:F」に変化しています。「成分表F:F」は、偶然にもカロリーが入力されている列ですので、この数式はそのまま使用できます。
この数式で取得したカロリーを含め、「成分表」の各栄養成分は全て100g当たりの量となっていますので、栄養計算では分量で値を補正します。食品毎の栄養成分は「1食分の分量(g)×(成分表取得値÷100)」で求めることができます。L10セルの数式「=IFERROR( INDEX(成分表!F:F, $U10, 1) , “")」を「=IFERROR( INDEX(成分表!F:F, $U10, 1) * $K10 / 100 , 0)」に変更してEnterキーを押します。セルの値が「139.125」となれば成功です。
栄養計算の数式のベースが完成したので、これを他成分のセルにコピーします。L10セルをP10セルまでコピーして下さい。
ここで、コピーした数式のINDEX関数の第1引数を確認してみましょう。下表のようになっていることが確認できると思います。これは正しいでしょうか？「成分表」を参照して、どの列に目的の成分が入力されているか確認してみましょう。
| 項目 | 数式のセルアドレス |
INDEX関数の第1引数 |
| カロリー | L10 | 成分表!F:F |
| 炭水化物 | M10 | 成分表!G:G |
| タンパク質 | N10 | 成分表!H:H |
| 脂質 | O10 | 成分表!I:I |
| 食塩相当量 | P10 | 成分表!J:J |
「成分表」では、たんぱく質はI列、脂質はK列、炭水化物はQ列、食塩相当量はBE列ですね。今回はついでに、現代人が不足しがちな食物繊維も確認しましょう。これはU列ですね。なお、下図は見易いように一部の列を非表示にしています。
確認の結果、先程コピーして作成したM10:P10セルの数式は、INDEX関数の第1引数が下表のようにズレていたので、これを下記のように修正します。
| 項目 | 数式のセルアドレス | INDEX関数の 第1引数 |
成分表の列番号 |
| カロリー | L10 | 成分表!F:F | 成分表!F:F |
| 炭水化物 | M10 | 成分表!G:G | 成分表!Ｑ:Q |
| タンパク質 | N10 | 成分表!H:H | 成分表!I:I |
| 脂質 | O10 | 成分表!I:I | 成分表!K:K |
| 食塩相当量 | P10 | 成分表!J:J | 成分表!BE:BE |
全ての数式が修正できたら、L10:P10セルL29:P29セルまで書式なしコピーし、L10:P29セルを選択状態のまま中央揃えし、小数点桁数を1桁に設定して下さい。
次に、K30セルをP30セルまでコピーし、各栄養成分の合計を表示します。これで、レシピの栄養成分計算は完成です。
成分検索結果の栄養計算
この調子で、成分検索結果欄(I33:P54)の栄養素も表示できるようにしましょう。栄養計算の方法に違いはありませんので、先程作成したL10:P10セルをL35:P35セルにコピーします。しかし、全ての栄養成分が「0」となってしまいました。この原因は、セル参照の間違いです。
カロリー計算を行っているL35セルの数式を例に説明します。L35セルの数式は「=IFERROR( INDEX(成分表!F:F, $U35, 1) * $K35 / 100, 0)」ですが、太字のセル参照U35(行番号)とK35(分量(g))のセルが空欄となっています。Excelは空欄セルを「0」が入力されているとみなして計算を行うため、計算結果が「0」になってしまったのです。なので、この2箇所を適切な参照に修正すれば、問題は解決します。
U35(行番号)はR35に変更すれば良いですが、K35(分量)は前回(第9回)で成分検索結果欄を作成した際に、分量の部分をセル結合で無くしましたのでありません。その代わりに作成したのがV36セルの“1食分の分量”欄と、V37の“栄養成分換算係数”欄です。“1食分の分量”欄は、成分検索欄の最終入力行の分量を格納するためのセルです。“栄養成分換算係数”欄は、取得した分量を100で割った数値を格納しておくセルで、栄養計算を行う際の途中式です。この2つのセルに数式を入力します。Ｖ36に「=INDEX( K:K , V33)」と入力してEnterを押します。セルの値が「37.5」となれば成功です。
次に、Ｖ37に「=V36 / 100)」と入力してEnterを押します。セルの値が「0.375」となれば成功です。
参照先の準備が出来たので、先程の数式を修正します。L35セルの数式を「=IFERROR( INDEX(成分表!F:F, $U35, 1) *$K35 / 100, 0)」から=IFERROR( INDEX(成分表!F:F, $R35 , 1) * $V$37, 0)」に変更してEnterキーを押して下さい。セルの値が「13.5」となれば成功です。同様に、M35:P35セルの数式も「$U35」を「$R35」に、「$K35 / 100)」を「$V$37」に変更して下さい。修正後の分量のセル参照には、行番号も絶対参照になっていることに注意して下さい。
出来たら、L35:P35セルをL54:P54セルまで書式なしコピーして下さい。L35:P35セルを選択状態のまま中央揃えし、小数点桁数を1桁に設定して下さい。
これで、栄養計算表は全て完成です。
今日はここまで