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

2020年4月4日

献立作成ソフトを作ろう第15回目は、献立作成シートの作成を開始します。特に今回は、献立の提供日時に関する部分と、料理検索機能を実装します。

献立提供日時の作成

料理提供日時の方針

 本連載の第1回では、献立作成ソフトの要求仕様を定めました。その中の1つに、「3.登録したレシピから1週間の献立表が作成できること」というのがありましたが、食事をいつ・どれくらいの種類を提供するかは決めていませんでしたね。学校給食なら平日の昼1食だけです。社員食堂も多くは平日の昼だけですが、メニュー数は「Aランチ、Bランチ、麺」などのように複数あります。また、病院や老健施設であれば朝・昼・夕の3食を毎日提供しますし、一部の病院では疾病の展開食が必要です。今回は、複雑さの間を取って朝・昼・夜の3食(1メニュー型)の献立を作成していきます。

料理提供日時の開始日

前回準備した献立作成シートを開いてください。このC2セルに、献立を作成したい週の初めの日付(月曜日)を入力して下さい。今回は、「2020/1/6」と入力します。日付が自動的に「2020年01月06日(月)」となりましたね。これは、C2セルに設定した”セルの書式設定”によるもので、C2セルを含む、日付を入力するセルには、表示形式「yyyy"年"mm"月"dd"日"(aaa)」が設定されています。

ここで、「yyyy」は4桁の西暦、「mm」は2桁の月、「dd」は2桁の日、「aaa」は日本語の曜日に対応します。

 

献立入力欄の日時ラベル作成

献立を入力する際に、献立作成者が日時を確認するための情報(ラベル)を作成します。

まず、B14セルにC2セルの参照「=C2」を入力します。

 

今度は、B32セルに「=B14 + 1」と入力します。C2セルに入力した日付の次の日「2010年01月07日」が表示されます。この数式の挙動を少し説明します。B14セルにはC2セルの参照が入力されているため、数式「=B14 + 1」は「=C2 + 1」と同じ意味になります。C2セルには「2020/1/6」が入力されていますが、これはExcelの内部表現では「43836」という数値です。この数値は、1900年01月00日(1989年12月31日)から2020年01月06日までの経過日数です。言い換えると、Excelは日付を1900年01月00日からの経過日数で表現しているため、日付に数値を加算(又は減算)することで、日付を操作することが出来ます。余談ですが、時間の表現も同様であり、1日は24時間なので24時間は「1」、12時間は「0.5」、1時間は「1/24 」=「0.0416・・・」、1分間は「1/24/60」=「0.000694・・・」となります。さらに余談ですが、加減算で日付を操作する場合、1ヶ月は月により日数が異なりますし、1年間もうるう年があるため、1ヶ月以上の日付の計算はEDATE関数を使用しましょう。

 

閑話休題。B32:B49セルをB122:139セルまでコピーします。オートフィルでも良いですし、オートフィルがやりにくい場合は、B32:B49セルをコピーした後、B50:B139セルを選択して貼り付けてもよいです。

 

B122セルに日曜日の日付「2020年01月12日(日)」が表示されれば成功です。これにより、C2セルに入力した日付を開始日として、1週間分の日付が自動表示されるようになりました。

 

今度は提供する時間帯のラベルを作成していきます。C14セル、C20セル及びC26セルに、それぞれ「朝」、「昼」及び「夕」を入力し、C14:C31セルをC139セルまでコピーします。

 

コピー先の最終セルであるC122セル、C128セル及びC134セルに、それぞれ「朝」、「昼」及び「夕」が表示されていれば成功です。

 

料理検索作業表用日時の作成

今度は、料理検索作業表にも日時を表示させます。これは、料理と提供日時を結びつけるために使用します。

まず、S14セルにB14セルの参照を入力します。

 

次に、S15セルにS14セルの参照を入力し、S15セルをS31セルまで書式なしコピーします。

S14からS31まで全て同じ日付が表示されているので、手順に疑問を持った方もいるかと思います。しかし、1行目(S14セル)と2行目以降(S15:S31セル)の違いが、後のコピーで効いてきますので、まずはそのまま入力して下さい。

今度は、T14:T19セルに「朝」、T20:T25セルに「昼」、T26:T31セルに「夕」と入力して下さい。

 

最後に、S14:T31セルをS139:T139セルまでコピーして下さい。

 

コピー先の最終セルを確認していきます。S122:S139セルが「2020年01月12日(日)」、T122:T127セルが「朝」、T128:T133セルが「昼」、T134:T139セルが「夕」と表示されれば成功です。

 

料理検索機能の実装

今度は、献立作成時に使用する料理検索機能を実装してきます。本項の数式は本連載の第8回と同じ手法であるため、解説は省きます。

まず、料理番号の検索機能を実装します。U14セルに下記の数式を入力して下さい。

=IF(D14="","", MATCH(D14,料理一覧!B:B,0))

現時点ではD14セルが空欄のため、U14セルの表示も空欄となります。

 

次に、料理名の検索機能を実装します。V14セルに下記の数式を入力して下さい。

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

こちらも現時点ではD14セルが空欄のため、V14セルの表示も同様に空欄となります。

 

2つの検索結果を1つのセルに統合します。W14セルに下記の数式を入力して下さい。

=IFERROR(IFERROR(U14, V14), “")

現時点ではU14セルが空欄のため、W14セルの表示も空欄となります。

 

U14:W31セルをU139:W139セルまで書式なしコピーをして下さい。

 

W14:W139セルの検索結果は料理一覧シートの行番号なので、これを料理Noと料理名に変換します。

まずは料理Noの変換を行います。E14セルに下記の数式を入力後、E14セルをE139セルまで書式なしコピーをして下さい。

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

現時点ではW14:W139セルが空欄のため、E14:E139セルの表示も空欄となります。

 

次に、料理名への変換を行います。F14セルに下記の数式を入力後、F14セルをF139セルまで書式なしコピーをして下さい。

=IF(W14 = “", “", INDEX(料理一覧!C:C,W14,1))

現時点ではW14:W139セルが空欄のため、F14:F139セルの表示も空欄となります。

 

これで料理検索機能が完成しました。試しに、D14セルに「ご飯」、D15セルに「味噌汁」、D16セルに「C4001」と入力すると、それぞれ料理名又は料理Noに対応する情報が”料理一覧シート”から取得されます。

 

第16回に進む

第14回に戻る