[Excel]献立作成ソフトを作ろう 追補1 材料のキーを牽引番号から食品番号に変更する
はじめに
連載記事「献立作成ソフトを作ろう」は、第20回を以って終了しました。しかし、本連載については時折、読者様からご要望およびご感想を頂くため、解答した内容の中から、多くの人に有用と思われる情報を公開していきます。なお、タイトルの”追補1”は、食品成分表の追補とは何の関係もありません。
今回は、栄養計算に使用する材料のキーを、牽引番号から食品番号に変更する方法です。
連載で作成した”レシピ作成シート”や”料理栄養計算作業シート”では、食品成分表の情報を取得するために、牽引番号を使用しております。しかし、2020年の食品標準成分表の改訂により、牽引番号が変わる可能性があります。そのため、牽引番号を使用する場合、過去に登録したレシピの牽引番号を、5年に1回更新する必要性がでてきます。特に、今年2020年は、5年に1度の食品成分表改定の年です。新しい成分表へ移行する前に、材料のキーを牽引番号から食品番号に変更しておくと良いでしょう。
”成分表”シートの修正
まずは、献立作成ソフトの"成分表"シートを開いてください。最初に、本シートのA：Bセルの表示形式を”文字列”に設定します。
本シートのＡ：Ｂセルを選択し、”ホーム”タブ内の”数値”グループ内にある「▼」マークを押します。ドロップダウンリストが表示されますので、その中から「文字列」を選択します。なお、”成分表”シートのＡ：Ｂセルの表示形式は最初から”文字列”となっているため、この操作は通常不要です。しかし、他のセルをＡ：Ｂセルにコピー＆ペーストすると、その書式は簡単に上書きされてしまいます。そのため、これは保険的な意味合いの強い操作です。
次に、今まで使用していなかったＡ：Ｂセルの空欄を埋めましょう。本連載では材料として「水」、「ごま塩(丸美屋)」、「五香粉」及び「芝麻醤」を追加しましたので、これらを例に入力してみます。Ａ列「食品群」は、文部科学省の分類をそのまま使用します。しかし、「水」はどこにも分類できないため、便宜上「99」とします。その他3つの材料は全て調味料なので「17」ですね。
次にＢ列「食品番号」ですが、これは「食品群番号(2桁)＋3桁の連番」で構成されています。食品群番号は先程入力したので自動的に決定します。その後の3桁の連番は、どんな番号を入力しても、成分表の改訂によって重複してしまう可能性があるため、ここは「アルファベット1文字＋2桁の連番」とします。つまり、「水」は「99A01」、「ごま塩(丸美屋)」は「17A01」、「五香粉」は「17A02」、「芝麻醤」は「」17A03」です。なお、他に重複しない実用的な付番ルールがある場合は、そちらを採用しても構いません。しかし、食品番号の上位2桁は食品群番号にしておくことをお勧めします。理由としては、栄養出納表を作成する際など、この番号があると便利な場面があるためです。
さて、入力したセルの左上に、赤い三角形が表示されていますね(入力しないセルにも表示されていますが)。これは、入力した値が数値ではなく文字列(数字)であることを通知しています。
この違いはとても重要です。例えば、「=MATCH(99, 成分表!A:A, 0)」は「#N/A」を返しますが、「=MATCH(“99", 成分表!A:A, 0)」は「2200」を返します。前者はMATCHの第1引数に数値の99を指定し、後者は文字列の99を指定しています。このように明示すると分かりやすいですが、あるセルに「=99」と入力した場合と、「="99″」と入力した場合は、どちらも「99」と表示され、見た目では区別できません。そして、ユーザーがセルに入力するのは、前述のような数式ではなく「99」という文字です。この文字が数値になるか文字列になるかは、セルに設定された表示形式に依存します。そのため、数値と文字列の違いを理解しないまま作業を続けると、MATCH関数で検索した際、「ちゃんと入力しているのに検索に引っかからない」という事態が発生します。
個人的には、Excelでは「数字だけで構成された文字列」を検索キーとして使用しないことが一番だと思います。しかし、食品成分表の食品番号のように、使わざるを得ない現実もあります。そのため、少なくともExcelを使用する方々には、数値と文字列の違いを正しく認識して欲しいと願っています。
”レシピ詳細”シートの修正
次に、”レシピ詳細”シートに移動します。ここでは、既に多数の牽引番号が入力されているため、これを一括で食品番号に変更します。
まずはG2セルに下記の数式を入力してEnterを押した後、Ｇ2セルを牽引番号が入力されているセル(この例ではＧ344セル)までオートフィルします。これまで何度も使用してきた数式なので解説はしませんが、要はF列の牽引番号から食品番号を取得しています。
=IF(F2="", “", INDEX(成分表!B:B, MATCH(F2, 成分表!C:C,0),1))
オートフィルした範囲をコピーします。
F2セルを右クリックし、コンテキストメニューから”値貼り付け”をクリックします。
これで、牽引番号が食品番号に置換されました。
表ヘッダ(F1セル)の「牽引番号」を「食品番号」に変更し、置換作業に使用したG列を削除します。
本シートの最後の修正は、保険としてF列の表示形式も「文字列」に設定しておきます。操作方法は”成分表”のA:Bセルと同様です。F:Fセルを選択し、”ホーム”タブ内の”数値”グループ内にある「▼」マークを押し、ドロップダウンリストの中から「文字列」を選択します。
”レシピ作成シート”の修正
今度は、”レシピ作成シート”へ移動します。
ここからは修正箇所が多いので、Excelの置換機能を使用します。
まずは、”検索と置換”ダイアログを表示させます。よく使う機能なので、分かる人は「Ctl + H」キーで表示させても構いませんが、一応説明します。
”ホーム”タブを選択し、”編集”グループ内にある”検索と選択”コマンドをクリックすると、ドロップダウンリストが表示されるため、その中の”置換”をクリックします。
”検索と置換”ダイアログの”置換”タブ内にある”検索する文字列”には「牽引番号」、”置換後の文字列”には「食品番号」を入力し、”すべて置換”ボタンを押します。
これで、本シート中の「牽引番号」が「食品番号」に置換されました。
次に、数式を置換していきます。数式の置換は、ソフトウェアの動作に直接影響するため、慎重に行いましょう。
まず、置換すべき数式は、牽引番号への参照である「成分表!C:C」です。本シート内には60セルほどあります。
先程と同様の手順で”検索と置換”ダイアログを表示させて下さい。先程の操作から開いたままであれば、それでも問題ありません。今度は、”検索する文字列”には「成分表!C:C」、”置換後の文字列”には「成分表!B:B」を入力し、”オプション”ボタンを押してください。
”検索と置換”ダイアログのオプションが表示されたかと思います。オプションの中の全てのチェックボックスが外れた状態で、”検索場所”は「シート」、”検索対象”は「数式」になっていれば大丈夫です。オプションの内容を確認したら”すべて置換”ボタンを押します。
置換が完了したメッセージが表示され、置換件数が表示されたかと思います。この件数が、先程の「成分表!C:C」の数(60)と一致していることを確認して下さい。この数が異なると、意図しない数式が変更されている可能性が高いです。もし違っていたら、”元に戻す(Ctrl + Z)”操作を行って、”検索する文字列”やオプションの内容を見直しましょう。
置換に成功すれば、牽引番号の参照「成分表!C:C」が食品番号の参照「成分表!B:B」に変更されています。
本シートの最後の修正は、G10:G29セルの表示形式を「文字列」に設定することです。操作方法は前述と同様で、G10:G29セルを選択し、”ホーム”タブ内の”数値”グループ内にある「▼」マークを押し、ドロップダウンリストの中から「文字列」を選択します。なお、この操作は保険ではなく必須です。このセルの表示形式を「文字列」にしておかないと、食品番号による検索が機能しません。
”料理栄養計算作業シート”の修正
これ以降は殆ど同じ手順です。
”料理栄養計算作業シート”に移動し、Q3セルを「食品番号」に変更します。次いで、「Ctrl + H」で”検索と置換”ダイアログを表示します。先程の操作の直後であれば、”検索する文字列”には「成分表!C:C」、”置換後の文字列”には「成分表!B:B」が入力されており、”オプション”の内容も同じになっているかと思いますので、そのまま”すべて置換”ボタンを押します。
置換件数が2520件となれば成功です。
”成分表項目作成シート”の修正
”成分表項目作成シート”に移動します。本シートのC13:C47セルには、牽引番号への参照「成分表!C:C」が含まれていますが、これは検索キーとして使用しているわけではなく、ユーザーへの情報提供を目的とした表示です。そのため、このセルは置換しないよう注意して下さい。
C13:C47セルを一括置換から守るため、置換する範囲を限定します。本シートの置換対象セルはBU13:BU47セルであるため、これを選択状態にします。これ以降は”成分表項目作成シート”と同様です。「Ctrl + H」で”検索と置換”ダイアログを表示します。”検索する文字列”には「成分表!C:C」、”置換後の文字列”には「成分表!B:B」が入力されており、”オプション”の内容も同じになっているかと思いますので、そのまま”すべて置換”ボタンを押します。
置換件数が35件となれば成功です。
念のためC13:C47セルの内容を確認してみましょう。「成分表!C:C」がそのまま残っているはずです。
終わりに
本連載において、材料の検索キーに「牽引番号」を使用してしまったことは、設計段階での私のミスであり、不徳の致すところです。本連載を参考にして下さった方には大変ご迷惑をお掛け致しました。
さて、一度終了したものの続きを書くことには少し抵抗がありました。しかし、「牽引番号を検索キーにしたこと」はずっと気になっていたため、この記事を書くことで、心のわだかまりが解消されました。また反響があれば、追補という形で情報公開を行いたいと思います。