[Excel]献立作成ソフトを作ろう 第11回 レシピ作成シートの作成(8)

2019年9月16日

献立作成ソフトを作ろう第11回目は、作成したレシピを別シートに保存する形式に整形する表を作成します。併せて、任意の食品を成分表に手入力します。

完成レシピ表の作成

今から作成する表の名前が、冒頭の”作成したレシピを別シートに保存する形式に整形する表”では長いので、本記事では、これを”完成レシピ表”と記載します。完成レシピ表とレシピ入力欄の構造はかなり似ているので、レシピ入力欄を再利用します。まず、B8:G29セルをコピーし、B32セルへ貼り付けます。

 

コピーした表のヘッダー(タイトル部分)を、完成レシピ表用に編集します。編集箇所は下表の通りです。

セルアドレス 編集前 編集後
B32 レシピ入力欄 完成レシピ(下の黄色マーカー部を”レシピ詳細”シートに値貼り付けして下さい。)
B33 No 料理番号
F33 設計分量(g) 単位分量(g)
F34 =C29 1食分
G33 成分検索 牽引番号
G34 (牽引番号又は食品名) 数式を削除

 

編集後、牽引番号ヘッダとなるG33:G34セルを結合します。

 

完成レシピ表のボディとなるB35:G54セルを黄色く塗りつぶした後、
この範囲のセルの数式を削除します。

 

完成レシピ表に、ヘッダに対応する情報への参照を作成していきます。この際、参照先が空欄の場合は参照元も空欄になるような数式を組み、使用時の見栄えを良くします。参照元と参照先の関係は下図の通りです。

 

 

参照元の数式は下表の通りです。

ヘッダ 参照元
セルアドレス
数式 説明
料理番号 B35 =IF(D35&E35="","",$C$3) 手順と材料名の両方が空欄なら空欄。
付番 C35 =C10 参照先が空欄処理を実装しているため参照のみ
手順 D35 =D10&"" 参照先セルが未入力なら空欄
材料名 E35 =E10&"" 同上
単位分量 F35 =IF(E35="", “", K10) 材料名が空欄なら空欄
牽引番号 G35 =I10 参照先が空欄処理を実装しているため参照のみ

特に難しい数式は無いかと思いますが、強いて解説するとしたら、「=D10&""」と「=E10&""」でしょうか?これは、何も入力されていないセルを参照した際に、参照元セル(この場合はD35セルとE35セル)に「0」が表示されるのを防止する効果があります。

この原理について簡単に説明します。なお、読み飛ばしてもあまり困りません。Excelのセルは、内部的に入力値がどのような種類であるかを示すフラグを保持しています(内部処理形式)。Excelの既定の設定(既定の表示形式=”標準")では、この内部処理形式は入力値によって決定します。例として、「1」と入力したら整数型、「あ」と入力したら文字列型、「1/1」と入力したら日付型になります。では、一度も入力されたことが無いセルの内部処理形式は何でしょうか?この場合、未初期化を示すEmptyというフラグを保持します。Emptyフラグを持つセルの値は、Empty値を持っていると言い換えることも出来ます。Empty値を参照した場合、参照元の表示形式に従って適切な内部処理形式に変換され、表示されます。この変換により、Empty値は0に変換されます。しかし、本来Empty値は未処理を示すものであり、0ではありません。意味合いとしては「空」の状態です。そこで、Empty値が自動的に変換される前に、空文字「""」と結合します。すると、Empty値は空文字に変換され、「=""&""」のような式になります。空文字はいくつ結合しても空なので、結果として0が表示されなくなります。

 

本題に戻ります。先程完成レシピ表に入力した数式をコピーします。B35:G35セルをB54:G54セルまで書式なしコピーします。見栄えの問題で、牽引番号の列であるG35:G54セルを中央揃えしておきます。

 

これで完成レシピ表は9割方完成しました。残り1割は便利機能的なものなので、無くても問題ない上、実装に少し手間が掛かるため、解説は次回にします。

完成したレシピをよくよく見ると、牽引番号に抜けがありますね。思い返してみれば、成分検索は第9回で人参の成分を検索したっきりでしたね。なので、人参より下の行の食品の成分を設定してきます。

じゃがいもは運よく材料名から適切な成分が取得されています。次の行は水ですが、成分表に水は収載されていません。それ以降の行は、上から順に、酒、ほんだし、砂糖、みりん、しょうゆ、さやいんげん、サラダ油ですね。これらは実際に使用する食品が取得できればどんなキーワードでも良いのですが、今回は同じ順で、「清酒、「和風だし」、「上白糖」、「本みりん」、「こいくち」、「さやいんげん」、「調合油」と入力することで、適当な成分を取得しました。

食品成分表への食品追加

成分表には水が収載されていません。あまり必要ないのかもしれませんが、せっかくなので成分表に水を追加してみます。

まずは入力欄を準備します。余談ですが、入力欄は見栄えの問題なので、やらなくても問題はありません。余談終了。

成分表シートを開き、成分表の最終行(今回は2199行目)の行番号をクリックし、行全体を選択します。

 

選択したセルを適当な位置まで書式のみコピーします。いつもの書式なしコピーではありませんので注意して下さい。適当な位置とは、自分が将来的に入力する食品の数くらいです。予測できるものでは無いので、迷ったら10行下くらいまでで良いです。

 

入力欄が完成したら、C2200セルに水の牽引番号「990001」、D2200セルに「水」と入力して下さい。成分は必要ならば入力して下さい。入力しなくても、”水分”以外の成分は0とみなしてよいため、問題はありません。H2200セルの”水分”は「100」と入力しておくことで、将来的に役立つ日が来るかもしれません。

ところで、水の牽引番号(990001)ですが、これは私が適当に決めました。食品成分表の牽引番号の付番号ルールは、1から始まるユニークな連番です。そのため、他の食品と重複しなければ何でも良いです。しかし、成分表の改訂により、食品の数はどんどん増えていくため、既に使用されている値に近い値は避けるべきです。また、ある程度規則性があったほうが管理しやすいでしょう。更に、詳細は省きますが、牽引番号は数値であるほうが、本ソフトのパフォーマンスを改善できる可能性があります。これら全ての要件を満たす牽引番号の1つが「990001」だっただけです。要件を満たせば、他の数値に変更しても問題ありません。

 

さて、成分表に水を追加したため、レシピ作成シートに戻って水の情報を取得してみましょう。G14セルに「990001」と入力すると、水の情報が・・・・取得できませんね。この原因は、成分表に入力した「990001」は文字列であり、レシピ作成シートに入力した「990001」が数値であるためです。

成分表のC2200セルを良く見ると、このセルの値が文字列で保存されている旨の警告が出ています。これは、成分表のC列の表示形式が”文字列”になっているからです。

 

そのため、C列の表示形式を”標準”に変更します。C列の列番号をクリックし、C列全体を選択します。この状態で、表示形式リボンにあるコンボボックスを開き(小さい▼をクリック)、”標準”を選択します。

 

これでC2200セルの値が数値に戻ったと勘違いしがちですが、C2200セルはまだ”文字列”のままです。これは、一度”文字列”として保存された値は、再度編集しないと、設定した表示形式に変換されないためです。

そのため、C2200セルを編集状態(セルを選択してF2を押すか、セルをダブルクリック)して、編集を終了すると数値として保存されます。

これで再びレシピ作成シートに戻ると、今回は水の情報が正しく取得されています。

 

第12回に進む

第10回に戻る