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

完成献立表の作成

献立作成フォーム用テストデータの入力

　まずは、献立作成フォームで作成した週間献立を、長期保存可能な形に整形します。基本的な考え方は第11回で作成した”完成レシピ表”と同じです。作成した献立を１つの表にまとめて、別の表へ貼り付け可能な状態に整形します。しかし献立作成フォームでは、1週間の献立を1つの表に入力しているため、提供日時(朝昼夕)間の空白セル(未入力セル)が多く、単純な参照を作るだけではセルの無駄が発生します。そのため、”完成献立表”では、この空白セルを詰める作業が必要になります。空白詰めは、Excelのメニューを使用して手動で削除することもできますが、毎回の作業なので自動化した方が良いでしょう。そのため、空白詰めも数式で実現します。この空白詰めの挙動を確認するためには、空白セルを含む献立データが必要になります。このデータは任意のものでも構いませんが、説明のためにテスト用データを用意しましたので、下記のリンクからExcelブックををダウンロードして下さい。

献立作成フォームテストデータ.xlsx(E01-17-D001_menu-preparation-form-test-data.xlsx)

ダウンロードした”E01-17-D001_menu-preparation-form-test-data.xlsx”の”献立作成フォームテストデータ”シートは、既に必要な範囲（A1:A126)が選択済みとなっていますので、この範囲をそのままコピーして下さい。

 

献立作成ソフトのExcelブックを開き、その”献立作成シート”のD14セルに貼り付けて下さい。今回は貼り付け元と貼り付け先の書式を合わせてあるので、無印の貼り付けでも値貼り付けでも問題ありません。

 

入力された献立データの空白詰め

それでは、献立データの空白詰めを数式によって実現していきます。とは言っても、実は空白詰めを実現する数式は第9回で実装した、MATCH関数とOFFSET関数による成分表の食品検索機能とほぼ同じで、検索キーワードが"*"になっただけです。

Y14セルとY15セルに、それぞれ下記の数式を入力してEnterを押します。セルの値がY14セルは「14」、Y15セルは「15」となれば成功です。これらは空白ではない行数を表しています。最後に、Y15セルをY139セルまで書式なしコピーして、空白詰め作業はほぼ完了です。

Y14セル：=MATCH(“*",D14:D9999,0) + ROW(D14) – 1
Y15セル：=IFERROR( MATCH(“*",OFFSET($D$1,Y14, 0, 9999 – Y14, 1), 0) + Y14, “")

この数式の挙動について少し補足します。*(アスタリスク)はワイルドカードの1種で、0文字以上の任意の文字列と一致する、特別な文字でしたね。そのため、この数式は文字列が入力されているセルと一致し、何も入力されていないか、数値が入力されているセルとは一致しません。ここまでの説明で、この数式の挙動に違和感を覚えた方は、非常に良いセンスをお持ちです。*が0文字以上の文字に一致するなら、何も入力されていないセルに一致しても良さそうです。このような予想の根源は、空白セルの判定に「=IF(A1="", TRUE, FALSE)」のような数式が多用され、それらは意図した通りに機能しているという経験則ではないかと筆者は想像します。しかし実際には、*は上図のように空白セルと一致していません。これは、空白セルが本質的には””(長さ0の文字列)ではなく、Empty値だからです。この辺の説明は第11回で少し触れていますが、Empty値は何も入力されていないセルにセットされた特別な値です。そしてこのEmpty値は、比較演算子(< , > , =など)で文字列と比較すると、自動的に""に変換される性質を持ちます。その結果、IF関数での空白セルの判定は意図した通りに機能します。しかし、MATCH関数ではこの自動変換が発生しないため、言い換えればEmptyは文字列ではないため、*(0文字以上の文字列)と一致しなかったという訳です。その証拠に、このMATCH関数は「=""」と入力されたセルと一致します。

 

完成献立表の作成

さて、MATCH-OFFSET検索による空白詰めによって、文字が入力されている行のみを抽出できました。あとはINDEX関数を使用して、必要な値を取得するだけです。完成献立に必要な情報は、提供日(S列)、提供時間帯(T列)、料理番号(E列)です。また、参考情報として料理名(F列)も取得しておきましょう。N14～Q14セルに下表の数式を入力してEnterキーを押してます。なお、O14セルはN14セルを、Q14セルはP14セルを書式なしコピーしてもよいでしょう。入力出来たら、N14：Q14セルをN139：Q139セルまで書式なしコピーします。これで、入力された献立を保存可能な形式に整形できました。

入力セル	数式	値
N14	=IF($Y14 = “", “", INDEX(S:S,$Y14))	2020年01月06日
O14	=IF($Y14 = “", “", INDEX(T:T,$Y14))	朝
P14	=IF($Y14 = “", “", INDEX(E:E,$Y14))	C0001
Q14	=IF($Y14 = “", “", INDEX(F:F,$Y14))	ご飯

 

栄養バランス確認表の作成

”献立作成シート”の仕上げとして、作成した献立の栄養バランスを確認する表を作成します。具体的には、1日毎の栄養成分の合計を表示すると共に、1週間の平均栄養成分を表示します。

まず、F3:F10セルに作成中の献立提供日を表示させます。Excelの日付は内部的には数値で表現されており、1日は数値「1」であることを思い出してください。F3セルに「=C2」、F4セルに「=F3 + 1」を入力してEnterを押します。続いて、F4FセルをF9セルまで書式なしコピーします。F3セルが献立作成開始日(C2セル)と一致し、F5～F9セルがその1~6日後を表示していれば成功です。

 

次に、1日の栄養成分を合計します。G3セルに下記の数式を入力してEnterを押します。セルの値が「1706」となれば成功です。

=SUMIF($S$14:$S$139, $F3, G$14:G$139)

新しい関数が出てきましたね。この関数は名前の通り、SUM関数とIF関数を合わせたような関数で、指定した条件に一致するセルの合計を求めます。下記に、Microsoft社の説明を一部変更して引用します。

説明

指定された検索条件に一致するセルの値を合計します。

構文

SUMIF(範囲, 検索条件, [合計範囲])

引数	説明
範囲	条件によって評価するセル範囲を指定します。各範囲のセルには、数値か、数値を含む名前、配列、または参照が含まれている必要があります。空白と文字列は無視されます。選択した範囲には標準の Excel 形式の日付 (下記の例を参照) が含まれる場合があります。
検索条件	計算の対象となるセルを定義する条件を、数値、式、セル範囲、文字列、または関数で指定します。たとえば、条件を “">32″"、B5、32、""32″"、""apples""、または TODAY() で表すことができます。 重要: 文字列条件や、論理記号または数学記号を含む条件は、二重引用符 (“") で囲む必要があります。条件が数値の場合、二重引用符は不要です。
合計範囲	“範囲" で指定したセル以外のセルを加算する場合は、加算する実際のセルを指定します。"合計範囲" を省略すると、"範囲" で指定したセル (条件が適用されるセル) が加算されます。 “検索条件" には、半角の疑問符 (?) または半角のアスタリスク (*) をワイルドカード文字として使うことができます。ワイルドカード文字の疑問符は任意の 1 文字を表し、アスタリスクは 1 文字以上の任意の文字列を表します。ワイルドカード文字ではなく、通常の文字として疑問符やアスタリスクを検索する場合は、その文字の前に、""~*"" のように半角のチルダ (~) を付けます。

(Microsoft社より一部変更して引用)

今回は2020年01月06日のカロリーを合計したいので、検索条件(第2引数)は「2020年01月06日」が表示されているF3セルになります。次に、合計範囲(第3引数)は、1週間のカロリーが表示されているG14:G139セルになります。そして最後に、このカロリーに対応する日付が表示されているS14:S139を条件比較範囲(第1引数)に指定します。あとは、この数式をオートフィルするので、固定したいセルを絶対参照に変換して完成です。オートフィルは、G3セルをL3セルまで書式なしコピーし、続いてG3：L3セルをG9：L9セルまで書式なしコピーします。

 

これで、1日毎の栄養成分の合計が表示できました。栄養バランス確認表の仕上げとして、1週間の平均栄養成分を表示させましょう。G10セルに「=AVERAGE(G3:G9)」を入力してEnterを押します。セルの値が「2597」となれば成功です。これをG10セルをL10セルまで書式なしコピーします。