第五回、週間献立表の自動化
前回までのレビュー、献立表のレシピ名から食材データを書き出す
■ Sheet２（献立作業指示書）の枠組みを作成しました。
■ 献立表に埋め込んだレシピ名から食材情報を、献立作業指示書（Sheet2）の所定のセルに書き出す処理を作成しました。
詳しくは下のカードから参照してください。
栄養価を表示する
■ 週間献立表にレシピ名を設定するとそのレシピ番号に紐づいた食材情報には食材ごとに栄養価情報も含まれています。
今回は、レシピ番号からその栄養価情報を取り出して週間献立表に取り込む処理を考えます。
■ 前回まででメニュー名を設定して転送ボタンをクリックすると、献立作業指示書が作成されるところまでの処理でした。
■ 今回の処理では同様に転送ボタンをクリックすると下図のように、各食事カテゴリーごとの５種類の栄養価を表示できるようにしました。
大まかな仕組み
■ まずレシピ名の行の右側２０列シフトしたところにあるレシピ番号を取り出す。
■ 次に、recipeKanriとrecipeDataをそれぞれレシピ番号で検索して一致したデータを取り出します。
■ recipeKanriにはレシピに登録したときの食材データにその食材が持つ栄養価情報がくっついているのでその栄養価情報の内、表示に必要な部分だけ取り出す（熱量値、蛋白質量、脂質、炭水化物、塩分量の五種類）
■ 週間献立表に記入した食事カテゴリーごとのレシピに対応した栄養価を合算する（主食メニュー、主菜メニュー、副菜メニュー、汁物メニューなど、それぞれの栄養価の合計を取得）
■ 朝食、昼食、おやつ、夕食の食事カテゴリーごと一日合計の栄養価表示を一食当たり平均を計算して取り出す。
■ 週間献立表の所定のセルに取り出した（取得した）栄養価データを書き出します。
プログラム
Private Sub CommandButton1_Click()
Dim kaisiIti As Variant
Dim x(3) As Variant
Dim asahiruyuu As Variant
Application.ScreenUpdating = False
'前回のデータをクリア（罫線含む）
Call dataClire
'日付転送
Sheet2.Cells(2, "B") = Sheet1.Cells(5, "D").Value
Sheet2.Cells(3, "B") = Format(Sheet1.Cells(5, "D"), "(aaa)")
'朝食
kaisiIti = katrgoriInput(6, 6, 5, 4, 2, "朝")
Call keisenTop(kaisiIti(0)) '食カテゴリーの変わり目に罫線を引く
x(0) = bunkaiEiyou(kaisiIti(1)) '表示用栄養価は文字列が含ま
'れるので、後で合計平均値を計算する必要があるので、
'数値だけの配列に取り出している
'昼食
kaisiIti = katrgoriInput(kaisiIti(0), 12, 7, 5, 2, "昼")
Call keisenTop(kaisiIti(0)) '食カテゴリーの変わり目に罫線を引く
x(1) = bunkaiEiyou(kaisiIti(1))
'おやつ
kaisiIti = katrgoriInput(kaisiIti(0), 20, 1, 6, 2, "おやつ")
Call keisenTop(kaisiIti(0)) '食カテゴリーの変わり目に罫線を引く
x(2) = bunkaiEiyou(kaisiIti(1))
'夕食
kaisiIti = katrgoriInput(kaisiIti(0), 22, 6, 7, 2, "夕")
Call keisenTop(kaisiIti(0)) '食カテゴリーの変わり目に罫線を引く
x(3) = bunkaiEiyou(kaisiIti(1))
'栄養全部集合（各栄養素を合計して平均値として取り出す）
asahiruyuu = eiyouZenbu(x)
'sheet1合計平均の欄に記入
Sheet1.Cells(29, 4) = asahiruyuu
Application.ScreenUpdating = True
End Sub
メインプログラム
こちらは、前回と同じプログラムでメニュー名ごとに食材の詳細データを作業指示書側に送り出す処理をしています。このプログラムの最後のほうに（１０３行目）当りで、食事カテゴリーが切り替わる部分に今回の栄養価を表示させる処理を追加しています。
'第一引数 gyouRow Sheet２（献立指示書）で、データ書き込みの先頭行位置
'第二引数 kubunIti Sheet１（週間献立表）で、C列の食事カテゴリーごとの先頭行位置
'第３引数 kubunSuu Sheet１（週間献立表）で、C列の食事カテゴリーが何行あるか行数を指定
'第４引数 syokusuIti Sheet３（食数表）で、食事帯ごと食数の指定 行 位置
'第５引数 youbiretu Sheet３（食数表）で、食事帯ごと曜日の指定 列 位置
'第６引数 syokujitai Sheet１（週間献立表）で、表示用合成栄養価の表示位置を特定するための食事帯を示す
Private Function katrgoriInput(gyouRow As Variant, kubunIti As Integer _
, kubunSuu As Integer, syokusuIti As Integer _
, youbiretu As Integer, syokujitai As String) As Variant
Dim r As Integer, i As Integer
Dim r2 As Integer, i2 As Integer
Dim resiNumb As Integer
Dim resiData As Variant
Dim resiKanri As Variant
Dim syokukubun As String
Dim syokusuu As Integer
Dim konkubun As String
Dim menumei As String
Dim gyousuu As Integer
Dim jun As Variant
'メニューカテゴリーは最大7個、５列
Dim eiyousum(6, 4) As Variant
Dim goretu As Variant
Dim eiyouhyouji As String
Dim kate As Integer
Dim result(1) As Variant '行位置と合成栄養価の二個の値をを返すための宣言
'
'朝食
'食事区分、献立区分、食数、メニュー名 をsheet2に書き出す
With Sheet1
syokukubun = .Cells(kubunIti, "A").Value
syokusuu = Sheet3.Cells(syokusuIti, youbiretu).Value
Sheet2.Cells(gyouRow, "A") = syokukubun
Sheet2.Cells(gyouRow, "B") = syokusuu
gyousuu = 0
kate = 0 '食事カテゴリごとに食事区分数が違うので区分の繰り返しが終了した時点で次の食事帯に移ると判断する
For r = 0 To kubunSuu - 1
'レシピ番号をキャッチ
resiNumb = Sheet1.Cells(r + kubunIti, "D").Offset(0, 20).Value
'食事区分は最大が昼食７行あるが、データのない空白行が含まれるので注意
If resiNumb <> 0 Then
'詳細データを取り出す
resiData = call_resipiData(resiNumb) 'Sheet1のすべての栄養価データと管理部分のデータも含まれる
'管理データをレシピ番号で探して取り出す(レシピ単位の栄養価を含む)
resiKanri = call_resipiKanri(resiNumb)
'レシピ番号からそのレシピの食材行数が10番目に書いてあるのでそれををキャッチ
gyousuu = resiKanri(10)
'献立区分を転記
konkubun = .Cells(r + kubunIti, "C").Value
Sheet2.Cells(gyouRow, "C") = konkubun
'メニュー名転記
menumei = .Cells(r + kubunIti, "D").Value
Sheet2.Cells(gyouRow, "D") = menumei
'詳細データ転記
'列データ順（別名、使用量、単位、規格、加工、業者,備考）
jun = Array(64, 65, 66, 70, 72, 71, 63)
Dim tobi As Integer
Dim atai As String
Dim gsuu As Integer
Dim tanni As String
tobi = 0
On Error Resume Next
For r2 = 0 To gyousuu - 1
For i2 = 0 To 6 '0～6までは詳細データ、11まで栄養価
'準備量の計算でg数が不明の時があるので場合分けする
If i2 = 2 Then
'単位当たりｇ数が不明の時は1000ｇとみなす（調理用水）
If resiData(r2, 67) = "" Then
gsuu = 1000
Else
gsuu = resiData(r2, 67)
End If
'準備量 転記 ' 使用量ｇ × 食数 ÷ 単位当たりｇ数
Sheet2.Cells(gyouRow + r2, i2 + 5) = resiData(r2, 65) * syokusuu / gsuu
tobi = tobi + 1
If resiData(r2, jun(i2)) = "" Then
If resiData(r2, 65) = "" Then
tanni = ""
Else
tanni = "kg"
End If
Else
tanni = resiData(r2, jun(i2))
End If
'単位 転記
Sheet2.Cells(gyouRow + r2, i2 + 5 + tobi) = tanni
Else
'準備量列以外のデータで、特殊形態あり場合分け
If resiData(r2, jun(i2)) = "" And i2 = 0 Then
atai = resiData(r2, 5)
Else
atai = resiData(r2, jun(i2))
End If
'その他の配列値 転記
Sheet2.Cells(gyouRow + r2, i2 + 5 + tobi) = atai
End If
Next i2
tobi = 0
'栄養価 転記処理
'resiKanriはレシピ単位の管理部分データで0～23まで24個のデータがある
'その内13以降は栄養価になっていて栄養価表示用の5個だけ取り出して転記
' 栄養価はレシピに対して1行だけなので最初の一行だけ書き込む
If r2 = 0 Then
Dim eiyou As Variant
Dim j As Integer
eiyou = Array(13, 15, 17, 19, 24)
For j = 0 To 4
'Sheet2.Cells(gyouRow + r2, j + 13) = resiKanri(eiyou(j))
eiyousum(r, j) = resiKanri(eiyou(j))
Next j
End If
Next r2
gyouRow = gyouRow + gyousuu + 1
End If
Call keisenTop2(gyouRow) '食カテゴリーの変わり目に罫線を引く
Next r
'栄養価合計の５列の各行を合計処理して返る
goretu = goukeisyori(eiyousum)
'表示用栄養価に合成
eiyouhyouji = eiyouMojiretu(goretu)
'合成栄養値を食事帯ごとに書き込む
Call eiyoukinyuu(syokujitai, eiyouhyouji)
End With
'返却
result(0) = gyouRow
result(1) = eiyouhyouji
katrgoriInput = result
End Function
栄養価表示部分のプログラムです。
こちらの１０３行目以降を抜きだした栄養価表示部分のプログラムです。
'栄養価 転記処理
'resiKanriはレシピ単位の管理部分データで0～23まで24個のデータがある
'その内13以降は栄養価になっていて栄養価表示用の5個だけ取り出して転記
' 栄養価はレシピに対して1行だけなので最初の一行だけ書き込む
If r2 = 0 Then
Dim eiyou As Variant
Dim j As Integer
eiyou = Array(13, 15, 17, 19, 24)
For j = 0 To 4
'Sheet2.Cells(gyouRow + r2, j + 13) = resiKanri(eiyou(j))
eiyousum(r, j) = resiKanri(eiyou(j))
Next j
End If
Next r2
gyouRow = gyouRow + gyousuu + 1
End If
Call keisenTop2(gyouRow) '食カテゴリーの変わり目に罫線を引く
Next r
'栄養価合計の５列の各行を合計処理して返る
goretu = goukeisyori(eiyousum)
'表示用栄養価に合成
eiyouhyouji = eiyouMojiretu(goretu)
'合成栄養値を食事帯ごとに書き込む
Call eiyoukinyuu(syokujitai, eiyouhyouji)
End With
■ recipeKanriにはレシピに登録したときの食材データにその食材が持つ１２種類の栄養価情報がくっついているのでその栄養価情報の内、表示に必要な部分だけ取り出します。
熱量値、蛋白質量、脂質、炭水化物、塩分量の五種類だけを選択するため、
８行目 eiyou = Array(13, 15, 17, 19, 24)
配列にあるその５種類の位置情報を指定しています。
次に、11行目でそれをさらに配列に取り出しています。
22行目以降では、その配列を使って一日平均データを計算し更に、数値だけのデータに説明用の単位や区切り文字を付け足して最後にデータをシートに書き込む処理を呼び出しています。
下図は26行目のシートに書き込む処理です。
ダウンロード
■ここまでのファイルをダウンロードできます。下のカードからお願いします。
ファイルは圧縮してあるので必ず解凍してお使いください。
解凍の仕方
■ 本ページからファイルをダウンロードすると、（赤線部分はご使用のPCのユーザー名）
のフォルダーのダウンロードに保存されます。
■ 下図左で①のダウンロードをクリックするとダウンロードした圧縮ファイルが表示されるので、②のファイルを右クリックして表示されるメニューから③のすべて展開をクリックします。
■ 次に、右図のようなファイルの展開場所を指定する画面になるので→の参照ボタンをクリックします。
■ 次に保存フォルダーを指定する画面になるので、赤四角枠のデスクトップを指定します。一番下のフォルダーのところがデスクトップになっているのを確認してフォルダーの選択ボタンをクリックします。
■ 下図下側左のフォルダー選択画面に戻るので、ここでもフォルダーがデスクトップになっているのを確認して→の展開ボタンをクリックします。
■ 下図、右側下のようにファイルがデスクトップに解凍したと思います。
■ 解凍したファイル（フォルダー）を開くと二つのフォルダーと二つのエクセルファイルがあります
上側の「Book1週間献立表」が本ページで扱っているマクロファイルです。ちなみに下の「recipe2」はレシピ作成専用システムです
コメント