[Excel]献立作成ソフトを作ろう　第16回　献立作成シートの作成(2)

料理栄養計算作業シートの作成

料理栄養計算作業シートの準備

料理の栄養計算をする場合、料理に使用されている材料の情報が必要になります。この材料は多くの場合、複数存在します。なぜなら、目玉焼きを作るだけでも、卵と油と1種類以上の調味料が必要になります(調味料は塩、醤油、マヨネーズ等がありますね)。これを言い換えると、料理番号1つには、1種類以上の材料が関連付いていることになります。なお、献立作成ソフトにおいて、この関連付けを行っているのが”レシピ詳細”シートです。このような関係を1対多の関係と呼びます。そしてExcelは、この1対多の関係を効率的に処理するワークシート関数を持っていません。余談ですが、簡単に処理するワークシート関数はいくつか用意されており、SUMIF関数などがその代表です。しかし、これらの関数は効率的ではなく、計算するセルが増えるとExcelの計算時間が(線形的に)増大し、実用に耐えられなくなってきます。そのため、1対多の関係を効率的に処理する数式をユーザーが組む必要があります。この数式は実現したい内容によって異なりますが、往々にして複雑であり、料理の栄養計算も例外ではありません。

前置きが長くなりましたが、料理の栄養計算は複雑な数式を組むため、これは専用のワークシートに分離します。今回も、レイアウトの完成品(ブランク)のExcelブックを用意しました。下記のリンクから献立作成シートのブランクをダウンロードして下さい。

料理栄養計算作業シート(ブランク).xlsx

ダウンロードしたExcelブックは、第14回で説明した通り警告が表示されます。各自ウィルスへの対応を判断したのち、”編集を有効にする”ボタンを押してください。Excelブックのファイル名は”E01-16-D001_nutrition-calculation-sheet-blank.xlsx”で、シート名は”料理栄養計算作業シート”です。これを作成中の献立作成ソフト(Excelブック)に移動またはコピーしてください。　

料理栄養計算の概要

前述の通り、料理番号1つにつき、1種類以上の材料が関連付いています。例えば、料理番号C1001に対応する”豆腐とわかめの味噌汁”の材料は、水、煮干、豆腐、生わかめ、ねぎ、及び八丁味噌の6種類であり、これらの材料に対応する成分表の牽引番号は990001、1078、293、1018、634、2127、及び1744です。この飛び飛びの牽引番号の栄養成分を1つの数式で合計することは(不可能ではありませんが)非常に困難なので、これらの栄養成分を一度作業表に連続した形で展開します。展開する位置(セル)が分かっていれば、OFFSET関数とSUM関数で合計値を算出することができます。

まとめると、下記のような手順を踏みます。

1. ”献立作成シート”から、栄養計算したい料理の料理番号を取得する。
2. ”レシピ詳細シート”の中から、取得した料理番号を検索する。
3. 検索結果から、”レシピ詳細シート”内での料理番号の位置と、料理番号に対応する材料の数を取得する。
4. 3の情報から、作業表に材料の栄養成分を展開する。
5. 材料の栄養成分を集計し、料理の栄養成分を求める。

料理栄養計算作業シートの作成

料理栄養計算表の作成

それでは、料理栄養計算作業シートの数式を組んでいきましょう。まずは単純に、”献立作成シート”に表示されている料理番号を、参照により”料理栄養計算作業シート”に表示させます。

”料理栄養計算作業シートのB4セルに「=献立作成シート!E14」と入力してEnterを押します。セルの値が「C0001」となれば成功です。

これにより、”料理栄養計算作業シート”内で料理番号を参照する際の数式が短くなる上、数式を組む際の作業性が向上します。また、この手法は数式の外部依存個所を減らすため、ブックやシートの構造変更にも強くなります。そのため、1つのシート内で他のシートのセルを複数回参照する場合は、このように数式を組む癖を付けましょう。

次に、栄養計算する料理番号が、”レシピ詳細”シートの何行目に位置しているかをMATCH関数により取得します。”料理栄養計算作業シート”のC4セルに「=IFERROR(IF(B4="",FALSE,MATCH(B4,レシピ詳細!A:A, 0)),FALSE)」と入力してEnterを押します。料理番号「C0001」は、”レシピ詳細”シートの2～４行目に存在しているため、セルの値が開始行である「2」となれば成功です。なお、指定された料理番号が”レシピ詳細”シートに存在しない場合はFALSEを返していますが、これは後々、無駄な計算を省くためのフラグとして使用します。

次に、料理番号「C0001」が、”レシピ詳細シート”のA2セルからA23セルまでに何個存在しているかを数えます。”料理栄養計算作業シートのD4セルに下記の数式を入力してEnterを押します。

=IF( C4, COUNTIF(OFFSET(レシピ詳細!$A$1, C4, 0, 20, 1), B4) + 1, 0)

セルの値が「3」となれば成功です。この数式はC列の値がFALSEの場合、単純に0を返しています。こうすることで、COUNTIF関数やOFFSET関数などの計算量が多い関数の実行を防ぎ、処理速度を向上させることができます。これ以降も同じ考え方でIF関数を使用していますが、説明は省略します。

本題に戻ります。この数式について、セルの数はCOUNTIF関数で数えているのは理解できるかと思います。しかし、数式の中に「A2：A23」の表記がないため、混乱した方もいるかもしれません。このA2：A23セルに相当する部分は「OFFSET(レシピ詳細!$A$1, C4, 0, 20, 1)」です。OFFSET関数は第9回で解説していますが、難しい関数なので理解が曖昧な方もいたかと思います。ここでのOOFSET関数の意味は、”A1セルから2行下のセルを基準(左上)として、20行1列のセル参照を作れ”となります。なので、このOFFSET関数はA3：A23セルを返します(A2：A22セルではないので注意)。なお、”2行下”の2はC4セルの値であり、”レシピ詳細”シートA列における料理番号C0001の最初の出現位置です。OFFSET関数の第4引数に指定している「20」は私が適当に設定した数値で、料理1種類における最大材料数 – 1を意味しています。OFFSET関数で生成したA3:A23セルをCOUNTIF関数に渡し、A3:A23セルに含まれる料理番号”C0001”の数を数えます。これにより、料理番号C0001に対応する材料の最大数を求めています。最後に、COUNTIF関数の戻り値に1を加算していますが、この1はA2セルの"C0001″に対応します。

ここまでの一連の計算過程を示します。

1. =IF( C4, COUNTIF(OFFSET(レシピ詳細!$A$1, C4, 0, 20, 1), B4) + 1, 0)
2. =IF( 2, COUNTIF(OFFSET(レシピ詳細!$A$1, C4, 0, 20, 1), B4) + 1, 0)
3. =IF( TRUE, COUNTIF(OFFSET(レシピ詳細!$A$1, C4, 0, 20, 1), B4) + 1, 0)
4. =IF( TRUE, COUNTIF(OFFSET(レシピ詳細!$A$1, C4, 0, 20, 1), B4) + 1, 0)
5. =COUNTIF(OFFSET(レシピ詳細!$A$1, C4, 0, 20, 1), B4) + 1
6. =COUNTIF(OFFSET(レシピ詳細!$A$1, C4, 0, 20, 1), B4) + 1
7. =COUNTIF(OFFSET(レシピ詳細!$A$1, 2, 0, 20, 1), B4) + 1
8. =COUNTIF(OFFSET(レシピ詳細!$A$1, 2, 0, 20, 1), B4) + 1
9. =COUNTIF(A3:A23, B4) + 1
10. =COUNTIF(A3:A23, B4) + 1
11. =COUNTIF(A3:A23, “C0001") + 1
12. =COUNTIF(A3:A23, “C0001") + 1
13. =2 + 1
14. =2 + 1
15. 3

次に、材料の栄養成分の展開開始位置を求めます。展開先は材料栄養計算表で、本シート(料理栄養計算作業シート)料理栄養計算表の左側にあります。”料理栄養計算作業シートのE4セルに「1」、E5セルに「=D4+E4」と入力してEnterを押します。E5セルの値が「4」となれば成功です。この数式は単純に、料理番号に関連付けられた材料の数を累積していくだけです。

次に、これまで入力した数式をオートフィルします。B4：D4セルをB255：D255セルまで書式なしコピーし、続いてE5セルをE255セルまで書式なしコピーします。

ここまでが、料理栄養計算表の作成は一区切りとなります。

材料栄養計算表の作成

材料の栄養成分の展開先である、材料栄養計算表を作成していきます。

材料の展開には、1から始まる連番が必要になるため、最初にこれをオートフィルで生成します。M4セルに「1」、M5セルに「2」を入力し、M4：M5セルをM2523セルまで書式なしコピーします。

次に、材料を展開する行を確保します。具体的には、材料を３つ使用する料理には３行、材料を６つ使用する料理には６行確保する数式を組みます。N4セルに下記の数式を入力してEnterを押します。

=MATCH(M4, $E$4:$E$255, 1)

セルの値が「1」となれば成功です。この数式は単純なMATCH関数ですが、照合の種類(第3引数)に「１」(検査値以下の最大の値と一致)を指定しています。これを指定すると、検査値(第1引数)を第2引数(検査範囲)の中から検索し、検査値以下の最大値が存在する最大位置を返します。補足として、照合の種類に「１」を指定する場合、検査範囲のデータが昇順に並んでいる必要があります。つまりこのケースでは、検査範囲{1 , 4 , 10 , 10 , … , 10 }の中から検査値「1」以下の数値を検索するので、戻り値は「1」となります。ここで注意が必要なのは、戻り値「1」は検査値と一致した値ではなく、検査範囲($E$4:E$255$)の先頭セルの位置を1とした場合の位置です(ややこしい)。ここの部分をもう少し掘り下げます。検査値(第１引数)は先ほど生成した連番の相対参照「M4」なので、この数式を下側へオートフィルすると検査値が1ずつ増加していきます。イメージしにくい方は、先にこの数式を下方向へオートフィルしても良いでしょう。こうするとMATCH関数は、連番(第1引数)が1～3までは1を返し、4～9までは2を返します。これは、検査範囲($E$4:E$255$)の２行目(E5)が「4」であり、３行目以降(E6～E255)は「10」であるため、4～9は２行目と一致し、結果として2が返されます。

ここまで理解できたでしょうか？

次に、展開する材料の(”レシピ詳細”シートの)行番号を生成します。O4セルに下記の数式を入力してEnterを押します。

=IF(O3=FALSE, FALSE, IF(N4=N3, O3+1, INDEX($C$4:$C$255,N4,1)))

セルの値が「2」となれば成功です。

料理番号に関連付けられた材料の開始行番号は、既にC列で取得済みでしたね。なので、ある料理に関連付けられた材料を展開するために確保した行の先頭行には、C列で取得した材料の開始行番号をそのまま引用します。数式を入力したO4セルが先頭行であるか否かを判定する式は「N4=N3」です。N列には”材料を展開する行を確保する”数式が入力してあり、この数式は、材料の数だけ同じ値(行数)を返すものでしたね。そのため、N列の任意のセルαの値が、そのセルの真上のセルβの値と一致するとき、任意のセルαは先頭行ではありません。逆に、セルαとセルβの値が一致しない場合、セルαは先頭行となります。先頭行では、「INDEX($C$4:$C$255,N4,1)」によりC列から材料の開始行番号を取得します。先頭行以外では、真上のセルの値に１を加算し、連番を生成しています。この実装は、献立作成ソフトの制約事項の１つとなります。すなわちこの数式は、”レシピ詳細シート”の料理番号は、開始行番号から連続して入力されていることを前提とします。

これで、”レシピ詳細シート”から参照すべき行を得ることが出来たため、あとはINDEX関数を駆使して必要な情報を持ってくるだけです。まずは、材料の使用量を取得し、これを100で割ることで、栄養成分を１食分に換算する係数を算出します、P4セルに下記の数式を入力してEnterを押します。セルの値が「0.90」となれば成功です。

=IF(O4=FALSE, 0, IFERROR(INDEX(レシピ詳細!E:E, $O4,1)/100, 0))

次に、材料に対応する成分表の牽引番号を取得します。Q4セルに下記の数式を入力してEnterを押します。セルの値が「85」となれば成功です。

=IF(P4=0, “", INDEX(レシピ詳細!F:F, $O4, 1))

次に、牽引番号から成分表の行番号を取得します。R4セルに下記の数式を入力してEnterを押します。セルの値が「91」となれば成功です。

=IF(P4=0, “", MATCH(Q4, 成分表!C:C, 0))

成分表の行番号から、必要な栄養成分を取得すると共に、先ほど求めた料理1食分の換算係数を乗算します。まずはカロリーを求めます。S4セルに下記の数式を入力してEnterを押します。セルの値が現時点で「322」となれば成功です。

=IF(P4=0, 0, IFERROR(INDEX(成分表!F:F, $R4, 1) * $P4,0))

同じ要領で、たんぱく質、脂質、炭水化物、食塩相当量及び食物繊維を求めますT4～X4セルにそれぞれ、下記の数式を入力します。カロリーを求めた数式からの変更点は、成分表の参照列のみです。

=IF(P4=0, 0, IFERROR(INDEX(成分表!I:I, $R4, 1) * $P4,0))
=IF(P4=0, 0, IFERROR(INDEX(成分表!K:K, $R4, 1) * $P4,0))
=IF(P4=0, 0, IFERROR(INDEX(成分表!Q:Q, $R4, 1) * $P4,0))
=IF(P4=0, 0, IFERROR(INDEX(成分表!BE:BE, $R4, 1) * $P4,0))
=IF(P4=0, 0, IFERROR(INDEX(成分表!U:U, $R4, 1) * $P4,0))

ここまで出来たら、あとはオートフィルで材料栄養計算表を完成させます。N4：X4セルをN2523：X2523セルまで書式なしコピーします。

材料毎の栄養成分が計算できたため、これを合計して料理の栄養成分を求めます。これにはOFFSET関数を使用します。まずはカロリーの合計を求めます。F4セルに下記の数式を入力してEnterを押します。

=IF($D4=0,"", SUM(OFFSET(S$4, $E4 – 1, 0, $D4,1)))

セルの値が「322」となれば成功です。特別説明は不要かと思いますが、材料情報を展開した行の位置情報を使用して、OFFSET関数で展開先の範囲を算出し、これをSUM関数に渡しています。

次に、その他の栄養成分の合計も算出します。とは言っても、単純に先ほどの数式をオートフィルするだけです。F4セルをK4セルまで書式なしコピーし、続けてF4：K4セルをF255：K255セルまで書式なしコピーします。

これで、料理栄養計算表が完成しました。複雑な表でしたので、各数式の関係図を下記に示します。