[Excel]献立作成ソフトを作ろう　第18回　献立の保存と週間献立表の作成

週間献立表の作成

印刷部の日付表示欄の作成

それでは、”週間献立表”シートを作成していきましょう。このシートは、A3:D61セルを印刷範囲として設定する予定です。手始めに、この範囲に日付を表示していきます。”週間献立表”シートのB1セルに「2020/1/6」を入力してEnterを押します。このセルは、献立表の表示開始日時を入力するためのセルです。このシートは、基本的にはこのセルのみがユーザー入力個所になります(応用的には他にもあります)。

次に、献立表のタイトルを作成します。A3セルに「=TEXT(B1,"yyyy年")&"週間献立表"」を入力してEnterを押します。TEXT関数は、セルの書式設定を関数によって実現するものでしたね。言い換えれば、この数式はセルの書式設定に置き換えることができます。しかし、経験則ですが、セルの書式設定を変更できる人は、数式を編集できる人より少ない気がしますので、今回はTEXT関数を使用します。

最後に、献立表の日付表示部を作成します。A6セルにB1セルへの参照「=B1」を入力してEnterを押します。次に、A14セルに「=A6+1」を入力してEnterを押した後、A14：A21セルをA54：A61セルまで書式なしコピーします。

 

レイアウト用 献立内料理番号の生成

本項では、献立表の印刷部に合わせて、献立内料理番号を作業表に生成していきます。この作業表の名前は”献立レイアウト”です。

献立内料理番号とは、献立内(例えば、2020年01月06日の朝食内)の料理を一意に識別するユニークな番号で、その構成要素は料理の提供日と提供時刻(朝・昼・夕)、そして連番です。この番号は、後程INDEX-MATCH検索によって料理名を取得するために使用します。この番号の名前は、週間献立表用に筆者が適当に付けたものです。これだけだと意味が通じないと思いますので、少し補足します。過去の連載では、料理に対応するユニークな番号として、C0001(ご飯に対応)などの”料理番号”を使用していましたね。献立表でも、料理を識別するための番号なら、これまで通り料理番号を使用すれば良いと思うかもしれませんが、献立表はこれまでとは少し事情が違います。献立表は1週間の献立を表示するものであり、７日間の朝食・昼食・夕食の計21食を表示する必要があります。これはつまり、献立表に料理を表示する基準は提供日時になります。例えば、献立表の”2020年01月06日の夕食”には「ご飯・蛤の潮汁・肉じゃが・青菜の芥子和え」を表示したいのであって、「ご飯」や「蛤の潮汁」に”2020年01月06日の夕食”と表示した訳ではありません。そもそも、前回作成した１週間分の献立には、ご飯(C0001)が16回も登場しているので、「ご飯」を基準に提供日時を表示しようとすることに無理があります。回りくどい説明になりましたが、INDEX-MATCH検索で献立表を作成する場合、提供日時と料理を結び付けたユニークな番号が必要です。そしてその番号に、筆者が献立内料理番号という名前を付けたという話です。余談ですが、この番号の名前は”提供日時別料理番号”という名前にしようかと悩みました。しかし、この名前は限定的過ぎて応用が利かないため辞めました。例えば、この献立作成ソフトを改造して「朝・昼・夕」の部分を「Aセット・Bセット・麺類」に変更した場合、”提供日時別料理番号”という名称は使用できません。しかし、これが”献立内料理番号”という名称なら問題ありません。献立とは、”食卓に供する料理の種類や順序”を意味する言葉であり、言い換えれば1食分の料理を意味しています。そのため、今回は”献立内料理番号”という名称を使用しました。なぜこのシートに限定した管理番号の命名理由を長々と説明したかというと、管理番号の、延いては変数の命名は、ソフトウェアにとって重要だからです。

それでは改めて、献立表の印刷部に合わせて、献立内料理番号を生成していきます。この列は、献立表陰田粒の日付切り替え行に合わせて日付が切り替わる数式を組みます。F6セルに「=IF(A6, A6, F5)」を入力してEnterを押します。F5セルはF4セルと結合しており、マウスで選択できないので手入力となります。値がA6セルと同じ「01/06」となれば成功です。なお、このセルに年が表示されていないのは、書式設定によるものです。

 

次に、F6セルに「=IF(A6, 1, G5 + 1)」を入力してEnterを押します。ここでも、G5セルはG4セルと結合しているため手入力が必要です。ちなみに、この”献立内料理枝番”は、献立内料理番号の構成要素である”連番”の部分です。

 

前述の枝番と提供日時を組み合わせて、ますは朝の献立内料理番号を生成します。H6セルに「=$F6 & H$5 & $G6 )」を入力してEnterを押します。値が「43836朝1」となれば成功です。後は、H6セルをJ6セルまで書式なしコピーすることで、昼と夕の献立内料理番号の完成です。ちなみに、献立内料理番号内の”43836”とは、1900年01月01日から2020年01月06日までの日数です。Excelは日付を内部的に数値で管理していたことを理解していれば、こちらの理解も容易かと思います。

最後に、F6：J6セルをF61：J61セルまで書式なしコピーし、本表は完成です。

 

献立取得作業表の作成

献立詳細シートの行番号取得・生成

献立内料理番号に対応する料理情報を作業表に取得します。この作業表の名前は、”献立取得作業表”です。この表では、”献立詳細”シートに保存された献立データと、それに関連付いた情報データ(料理名及び栄養成分)を取得します。

ところで、”献立詳細”シートに保存された献立データを取得する方法は大きく分けて2種類あります。1つは単純な方法で、”献立詳細”シートの中から、”週間献立表”シートの表示開始日に一致する行を取得した後、この行の行番号から始まる連番を生成し、この連番に対応する行の料理情報を取得する方法です。この方法は、数式と考え方が非常に簡単なので実装が容易ですが、”献立詳細”シートの献立データは日付(A列)が昇順に並んでいることが必須条件です。もう一つは、”献立詳細”シートの献立データの並び順に制約はありませんが、数式が少し複雑なので、今回は単純な方で実装します。

前置きが長くなりましたが、献立データを取得していきましょう。最初に”献立詳細”シートから、表示開始日に対応する最初の行番号を取得します。

L6セルに「=MATCH(B1, 献立詳細!A:A,0)」を入力してEnterを押します。セルの値が「２」となれば成功です。

 

次に、L7セルに「=L6+1」を入力してEnterを押した後、L7セルをL131セルまで書式なしコピーします。

 

献立内料理番号の生成

献立詳細行番号から、INDEX関数を使用して提供日時を取得します。M6セルに「=INDEX(献立詳細!A:A,L6,1)」、N6セルに「=INDEX(献立詳細!B:B,L6,1)」と入力してEnterキーを押します。M６セルとN6セルの値はそれぞれ、「01/06」と「朝」になれば成功です。なお、セルの並び的にM6セルの数式を「=INDEX(献立詳細!A:A,$L6,1)」にしてN6セルへは書式なしコピーでも同じ数式が得られます。

 

献立内料理枝番を生成します。O6セルに「=IF(N6=N5, O5+1, 1)」を入力してEnterを押します。

 

これまでの情報から、献立内料理番号を生成します。Q6セルに「=M6 & N6 & O6」を入力してEnterを押します。

 

料理データの取得

献立内料理番号に関連付ける料理データを作業表に取得していきます。料理番号は先程の提供日時と同様、”献立詳細”シート”からINDEX関数で取得します。この料理番号を、前回作成した”料理栄養計算作業シート”に渡して、栄養成分を取得します。

Q6セルに「=INDEX(献立詳細!C:C,L6,1)」を入力してEnterを押します。セルの値が、料理番号「C0001」となれば成功です。

 

料理名を取得します。R6セルに下記の数式を入力してEnterを押します。

=INDEX(料理一覧!C:C, MATCH(Q6,料理一覧!B:B,0),1)

 

次に、”料理栄養計算作業シート”のB130セルに入力されている「=献立作成シート!E140」を、「=週間献立表!Q6」で上書きし、B130セルをB255セルまで書式なしコピーします。ちなみにこの「=献立作成シート!E140」は、第16回で”料理栄養計算作業シート”を作成した際、B4セルの書式なしコピーで自動設定されたものです。既存の数式を上書きすることが心配や、影響の範囲を理解したい方は、参照先を確認してみて下さい。

 

S6セルに「=料理栄養計算作業シート!F130」を入力してEnterを押します。セルの値がご飯のカロリー「322」となれば成功です。B130セルをB255セルまで書式なしコピーし、表示する全ての栄養成分を取得します。

 

献立取得作業表の仕上げに、M6：X6セルをM131：X131セルまで書式なしコピーして完成です。

 

献立毎の栄養成分の算出

本項では、前項で取得した料理毎の栄養成分を、献立毎(提供日時毎)に集計した後、これを表示するための文字列に変換する作業表を作成します。この作業表の名前は”献立栄養成分表示作業表”です。この作業表では、献立毎の栄養成分を一意に識別する”献立番号”を生成します。献立番号とは、料理の提供日と提供時間で構成されたユニークな番号で、献立内料理番号から連番を除いたものと言い換えることができます。この番号は後程、INDEX-MATCH検索によって献立毎の栄養成分文字列を取得するために使用します。栄養成分文字列とは、「カロリー・タンパク質・脂質・炭水化物・食塩相当・食物繊維」を1つの文字列に結合したものです。

それでは作業表を作成していきます。まずは日付で、提供時間の種類(朝・昼・夕の3種類)の数だけ同じ日付が並ぶ列を作成します。Z6セルに「=$B$1」を入力してEnterを押した後、Z6セルをA8セルまで書式なしコピーします。

 

次に、Z9セルに「=Z6+1」を入力してEnterを押した後、Z9セルをZ26セルまで書式なしコピーします。

 

今度は、提供時間帯の繰り返しを作成します。AA6セルに「朝」、AA7セルに「昼」、AA8セルに「夕」を入力してEnterキーを押します。

 

日付と同様、AA9セルに「=AA6」を入力してEnterキーを押した後、AA9セルをAA26セルまで書式なしコピーします。

 

前述の2列を行毎に結合して献立番号を生成します。AB6セルに「=Z6 & AA6」を入力してEnterキーを押します。

 

次に、献立キーを生成します。献立キーは、献立毎の栄養成分を合計する際に、SUMIF関数の第2引数に指定する番号であり、栄養成分の合計範囲を表す条件式です。AC6セルに「=AB6 & “*"」を入力してEnterキーを押します。

 

それでは、献立毎の栄養成分を算出していきます。AC6セルに下記の数式を入力してEnterを押します。セルの値が「474」となれば成功です。

=SUMIF($P$6:$P$131, $AC6, S$6:S$131)

 

AD6セルをAI6セルまで書式なしコピーし、続いてAD6：AI6セルをAD26：AI26セルまで書式なしコピーします。

 

栄養成分の表示文字整形

本項では、前項で取得した栄養成分(数値)を、TEXT関数を使用して人間が読み易い文字列に整形します。

AJ6セルに「=TEXT(AD6,AJ$5) & " “」を入力してEnterを押します。数式の末尾の「& " “」は、後でこのセルに表示された文字列を結合する際の仕切りとして機能します。TEXT関数はセルの書式設定を数式で実現するものでしたね。ここで指定している書式は「熱"："????」です。この書式は、「熱」と「：」はそのままの文字として表示し、「????」は4桁の空白埋め数字で表示します。4桁の理由を考えてみましょう。このセルではカロリーを文字列に変換しています。多くの献立では、1食分のカロリーは600～800kcalですが、時折1000kcalを超えることがあります。表示したい数字の桁数が3桁の時と4桁の時では、表示位置がズレますね。そのため、3桁の時も4桁目に空白を入れておくことで、表示のズレを防止しよう、という訳です。ちなみに、この書式を行儀よく書くと「”熱：”????」となりますが、「熱"："????」でも正しく表示されます。これは、Excelの書式設定にとって「：」は特別な意味を持つ文字ですが、「熱」は特に意味を持たないためです。

 

残りの栄養成分「タンパク質・脂質・炭水化物・食塩相当量・食物繊維」を表示ます。AJ6セルをAO6セルまで書式なしコピーます。いくつかの栄養成分では「"："?0.0」という書式が設定されていますが、この「0」は0埋めとして機能します。

 

続けて、AJ6：AO6セルをAJ26：AO26セルまで書式なしコピーします。

 

最後に、これらの文字列を結合して、印刷部に表示する文字列を生成します。Z6セルに「=AJ6&AK6&AL6& CHAR(10) &AM6&AN6&AO6」を入力してEnterを押した後、AP6セルをAP26セルまで書式なしコピーします。

 

印刷部の仕上げ

本項では、週間献立表の印刷部を完成させます。これまでに生成した情報を集約していく工程になります。

まずは料理名を表示させます。B6セルに下記の数式を入力してEnterを押します。

=IFERROR(INDEX($R$6:$R$131,MATCH(H6,$P$6:$P$131,0)), “")

B6セルをB11セルまで書式なしコピーします。

次に、栄養成分を表示させます。B12セルに下記の数式を入力してEnterを押します。

=IFERROR(INDEX($AP$6:$AP$26,MATCH($F6&H$5,$AB$6:$AB$26,0)), “")

 

これらの数式を全ての献立表示欄にコピーします。B6：B13セルをコピーします。

C6：D13セルを選択します。

Ctrlキーを押しながらB14：D61セルを選択し、選択範囲を右クリックしてコンテキストメニューから数式貼り付けを選択します。

他の献立表示欄への貼り付けが完了しました。実は今回の例では、B6：B13セルをコピーした後、B6：D61セルを選択して数式貼り付けでも同じことができましたが、Ctrlキーによるセルの複数選択を説明するために、前述の手順としました。

 

この週間献立表を印刷する際、1・2行目は印刷したくないので、印刷範囲から除外します。A3：D61セルを選択した状態で、”ページレイアウト”タブの”印刷範囲”コマンドから”印刷範囲の設定”を選択します。

 

印刷プレビューを確認してみましょう。下図のようになっていれば成功です。もし印刷範囲がズレるようであれば、”ページレイアウト”タブの”フォント”コマンドから”ＭＳ　Ｐゴシック”を選択してみて下さい。

 

第19回へ進む

第17回へ戻る

 

 

---