[Excel]献立作成ソフトを作ろう　第19回　成分表への料理の登録

2020年4月1日2020年4月4日

献立作成ソフトを作ろう第19回目は、”レシピ詳細”シートに登録した料理を、成分表に登録するシートを作成します。これは、本連載の第1回でユーザー要求仕様として挙げた機能です。この機能を以って、URSで規定した全ての機能が実装されます。

1. 成分表項目作成シートのブランクシート準備
2. 料理検索機能の実装
3. 食品群の設定
4. 材料情報取得作業表
5. 材料の栄養成分算出
6. 成分表項目の仕上げ

成分表項目作成シートのブランクシート準備

ここで言う”成分表項目”とは、成分表の個々の食品のことを指しており、筆者が適当に命名したものです。日本食品標準成分表では、この項目のことを”食品”と称していますが、この”食品”という言葉は意味が広すぎるので、便利である反面、誤解や混乱を招きやすい言葉です。そのため、本献立作成ソフトではこれを”成分表項目”と称して、その意味を限定しています。

さて、今回も例によって、”献立表項目作成シート”の、レイアウト完成品(ブランク)のExcelブックを用意しました。下記のリンクからExcelブックをダウンロードして下さい。

成分表項目作成シート(ブランク)(E01-19-D001_food-composition-item-sheet-blank.xlsx)

ダウンロードしたExcelブックは、第14回で説明した通り警告が表示されます。各自ウィルスへの対応を判断したのち、”編集を有効にする”ボタンを押してください。Excelブックのファイル名は”E01-19-D001_food-composition-item-sheet-blank.xlsx”で、シート名は”成分表項目作成シート”です。

今回は移動する前に、シートの列番号を確認してみましょう。”成分表項目作成シート”のF:BNセルを選択して右クリックし、コンテキストメニューの”再表示”を選択します。

非表示部の中を見てみると、11行目の表ヘッダ部に栄養成分名がズラっと並んでいるかと思いますが、これは、”成分表”シートの6行目と同じ文字列(かつ同じ並び順)です。このシートは、”献立作成シート”や”レシピ作成シート”と同様、必要な情報を入力後、黄色マーカー部をコピーして使用します。コピーしたデータは”成分表”シートに値貼り付けして使用しますが、このような使い方をするために、コピー範囲は成分表の構造に合わせる必要があり、どうしても横長になってしまいます。横長のセルを手動でコピーする操作は煩雑です。そのため、適当な列を意図的に非表示にして、セルのコピー操作の簡略化を図っています。このことから推測できるかと思いますが、セルのコピー範囲に非表示セルが含まれていても、表示している状態と同じデータをコピーできます。余談ですが、このシートは非表示状態のまま作成しました。

というわけで、このシートはG:BMセルを非表示にした状態で使用します。そのため、再表示したセルを元に戻します。本ブックを一度閉じて再び開くか、G：BMセルを選択して右クリックし、コンテキストメニューから”非表示”を選択して下さい。

元に戻せたら、成分表項目作成シート”を作成中の献立作成ソフト(Excelブック)に移動またはコピーして下さい。

料理検索機能の実装

まずは、成分表に登録する料理を検索する機能を実装します。この機能は、本連載の第8回と第15回で実装したものと同じですので、解説は省略します。

まずは、D4セルに適当な料理検索文字を入力します。今回は適度に材料が多い「チャーハン」を入力しています。次に、料理番号を検索する数式として、BS4セルに「=IF(D4="", “", MATCH(D4, 料理一覧!B:B, 0))」を入力してEnterを押します。

続いて、料理名を検索する数式を入力します。SUBSTITUTE関数は、指定した文字列を別の文字列に変換する関数でしたね。この関数で検索文字列中の空白をワイルドカード(“*")に変換しています。BS5セルに下記の数式を入力してEnterを押します。

=IF(ISNA(BS4), MATCH(“*" & SUBSTITUTE(SUBSTITUTE(D4," “,"*"), “　", “*") & “*", 料理一覧!C:C, 0), “")

二つの検索結果の内、ヒットしたものを表示します。BS6セルに「=IFERROR(IFERROR(BS4, BS5), “")」を入力してEnterを押します。

ヒットした料理の料理番号を表示します。表示位置は”牽引番号”にしておきましょう。これで、他の料理を作成する際は、料理番号を指定することで、料理の栄養成分を取得することが出来ます。C12セルに下記の数式を入力してEnterを押します。

=IF(BS6="", “", INDEX(料理一覧!B:B, BS6, 1))

料理番号から、料理名を表示します。表示位置は”食品名”です。この際、料理名と食品名に異なる名前を付けたいこともあると思いますので、D6セルに別名を指定できるようにします。D12セルに下記の数式を入力してEnterを押します。

=IF(BS6="", “", IF(D6<>"", D6, INDEX(料理一覧!C:C, BS6, 1)))

数式を見てわかる通り、「IF(D6<>"", D6, …」の部分で、別名が入力されていれば、”食品名”には優先的に別名が表示されるようになっています。

食品群の設定

本項では、登録する料理の食品群を設定します。この連載で使用している成分表は第17訂(2015年版)なので、食品群は下記の18種類に分類されていますね。これら食品群は、成分表(のA列)を見る限り、0詰め2桁の連番が割り振られています。料理を成分表項目として登録する際、適切な食品群に設定しておくことで、栄養出納表などを作成するときに役立ちますね。

食品群

01：穀類

02：いも及びでん粉類

03：砂糖及び甘味類

04：豆類

05：種実類

06：野菜類

07：果実類

08：きのこ類

09：藻類

10：魚介類

11：肉類

12：卵類

13：乳類

14：油脂類

15：菓子類

16：し好飲料類

17：調味料及び香辛料類

18：調理加工食品類

今回は入力する内容が決まっているので、データの入力規則を使用します。データの入力規則とは、セルに入力できる値を制限する機能です。今回のように入力したい文字列が決まっている場合は、これのリスト機能を使用することで、ユーザーの入力負担を減らすことが出来る他、開発者側としては、入力セルを参照する数式のエラー処理が単純化できます。ちなみに、データの入力規則はセルをリスト化する以外にも使用できます(例えば1~99の整数のみ入力可など)。便利な機能なので使い方を覚えておきましょう。

まず、入力規則を設定するセルであるD5セルを選択し、”データ”タブの”データの入力規則”コマンドを選択します。

”データの入力規則”ダイアログが表示されますので、(A)”設定”タブの(B)”入力値の種類”ドロップダウンリストのボタンをクリックし、表示されたリストから(C)”リスト”を選択します。

”リスト”を選択すると、”元の値”テキストボックスが表示されますので、右端のボタンをクリックします。

”データの入力規則”ダイアログがテキストボックスとボタンのみになり、ワークシートを選択できるようになりますので、BX13：BX30セルを選択して、ダイアログ右端のボタンをクリックします。セルを選択すると、テキストボックスに選択範囲が自動入力されます。選択範囲を間違えた場合は、テキストボックスをクリックし、セルアドレスをDelキーかBackSpaceキーで削除してから、改めて選択して下さい。

”データの入力規則”ダイアログが元の構造に戻ますので、”元のデータ”テキストボックスに先程選択したセルのアドレスが表示されていることを確認し、”OK”ボタンを選択して下さい。

これで、D5セルに入力規則が設定されました。リストの入力規則が設定されたセルは、選択中に限り右端にボタンが表示されます。これをクリックするか、Alt + ↓キーを押すと、食品群が表示されます。

表示されたリストから、適切な食品群を選択します。今回は五目チャーハンがヒットしているので、「01：穀類」を選択しておきます。

次に、成分表項目に表示する値を設定します。A12セルに「=IF(D5="", “18", LEFT(D5,2))」を入力してEnterを押します。入力規則を設定したD5セルが空欄なら自動的に「18」が表示される数式ですね。18は調理加工食品類ですので、料理を登録する際、迷ったらコレかなと思い、このような数式にしました。D5セルが空欄でない場合、LEFT関数で始めの2文字を取得していますね。食品群が0詰めの2桁で構成されているので、これだけで適切な食品群(番号)を取得することが出来ます。

食品群(番号)が表示できたので、続いて食品番号を表示していきます。成分表を見る限り、食品番号は食品群(番号)と3桁の0詰め連番で構成されています。この規則に従って料理の食品番号を設定しても良いのですが、成分表改定時に食品番号が衝突する可能性があるため、別の付番ルールにしました。余談ですが、最初は現在登録されている食品番号の最大値＋1の番号を自動付番する数式を説明する予定でしたが、前述の理由によりお蔵入りしました。

閑話休題。料理の付番ルールは、単純に食品群(番号)＋料理番号とします。B12セルに「A12 & C12」を入力してEnterを押します。これだけで、重複しない食品番号の完成です。

材料情報取得作業表

本項では、料理番号から料理に使用している材料情報を取得する”材料情報取得作業表”を作成します。本項で使用する数式は第9回で解説したものですので、詳しい解説は省略します。基本的な流れは”レシピ詳細”シートから料理番号が入力されている行番号を取得し、この行番号から材料情報を取得していきます。

まずは”レシピ詳細”シートから、料理番号に対応する最初の行番号を取得します。BR13セルに下記の数式を入力してEnterを押す。

=IFERROR(IF(C12="", FALSE, MATCH(C12, レシピ詳細!A:A, 0)), FALSE)

次に、2つ目以降の行番号を、MATCH-OFFSET検索により取得します。BR14セルに下記の数式を入力してEnterを押した後、BR14セルをBR47セルまで書式なしコピーします。

=IFERROR(IF(BR13=FALSE, FALSE, MATCH($C$12, OFFSET(レシピ詳細!$A$1, BR13, 0, 1048576 – BR13, 1 ), 0) + BR13), FALSE)

取得した行番号から、INDEX関数で1食分の材料使用量を取得します。BS13セルに下記の数式を入力してEnterを押した後、BS13セルをBS47セルまで書式なしコピーします。

=IF(BR13, INDEX( レシピ詳細!E:E, BR13, 1), 0)

次に、使用量の合計を求めておきます。この合計値は、料理の栄養成分を100g当たりに換算する為に使用します。BS48セルに「=SUM(BS13：BS47)」を入力してEnterを押します。

今度は、取得した行番号から、INDEX関数で材料の牽引番号を取得します。BT13セルに下記の数式を入力してEnterを押します。オートフィルは後で一気にやります。

=IF(BR13, INDEX( レシピ詳細!F:F, BR13, 1), “")

取得した牽引番号から、対応する成分表の行番号を取得します。BU13セルに下記の数式を入力してEnterを押します。

=IF(OR(BT13=0, BT13=""), FALSE, MATCH(BT13, 成分表!C:C, 1))

料理の全体使用量に対する材料の構成比を算出しておきます。この構成比とは、料理の栄養成分を成分表の栄養成分に換算するための係数です。もう少し詳しく説明します。料理は1食分という単位で管理されており、個々の料理の質量(材料使用料の合計)は、料理によって異なります。しかし、成分表は栄養成分を100g当たりで表示しているため、料理を成分表に登録する場合は、この換算が必要になります。この換算係数が構成比です。構成比は、材料の個々の栄養成分に乗算して使用します。なお、事前に換算係数を求めておくことで、数式の簡略化と、多少ですが計算速度の向上が期待できます。

それでは、構成比を求めます。BV13セルに下記の数式を入力してEnterを押します。

=IF(BU13=FALSE, 0, BS13/$BS$48)