Excelのwebservice関数で、栄養計算
おさらい：webservice関数とは
Excel2013から搭載された関数で、URLを指定すると接続し、得られた結果を表示します。接続先が、一定の処理をして返すようなしくみになっていれば、セル上には”処理結果”が表示されます。
関数なので、マクロ（VBA）を使わずに、他の処理系の結果を取得できる、優れものです。
webservice関数のデメリット
一方で、接続先（URL）が特に処理結果を返す様になっていなければ、単にhtmlが表示されるだけであったり、処理してもXMLやjsonで戻される場合も多いため、事実上一定の専門知識がないと使うのが難しかった、といえます。
そこで、このシリーズでは、vlookupに近いイメージで利用できる接続先を使って、webservice関数でどんなことができるかを紹介しています。
webservice関数でできること（3）：カロリー栄養計算
文科省の「日本食品標準成分表」
レシピサイトなどで、ある料理の総カロリー量や、主要栄養価が数値化されているのを目にします。
これは、材料ごとに基準となるカロリーや栄養価が公表されているので、これを基に使用する分量に応じた数値を集計しているのです。
この公表された栄養価の代表例（いくつかあります）に、文部科学省が公開している「日本食品標準成分表」があります。Excelのシートになっているので、そのまま印刷して使えます。
主に学校給食用として開発されたようで、材料の表現に少々くせがありますが、定期的に更新されており、信頼性の高い資料といえそうです。
Excelシートであるため再加工ができ、誰でもダウンロードできるのも結構な事なのですが、高度にプロテクト？（名称の各文字の間に見えない文字コードが入っている等）されており、そのままでは使えません。
例えば、下図のように、ビタミンKはExcelの書式で縦書き表示になっていますが、ビタミンB1は各文字の後に改行コードが入っているので、「ビタミン」で検索されません。
これをExcelの機能でcsv出力してメモ帳に貼り付けてみると、改行やスペース文字による間隔調整が少なくないことが解ります。
そのまま使えるなら、裏のシートに貼り付けて、vlookup関数で参照させれば良さそうですが、これを0から整形するのも面倒なので、食品コード→カロリー・栄養素を算出する既存のURLを活用します。
webservice関数の汎用処理環境『castanet.one』に接続
まずは、castanet.oneに栄養データがどのように入っているかを見てみます。最後のパラメータに「&1」～「&10」まで渡すことで、全項目のデータが取得できます。
また、Key2で”title"を指定すると、見出し情報が取得できます（2行目）。
2項目め（B列先頭で取得しているので、上の例ではC列）がいわゆるカロリーで、単位はkcalになります。4列目以降は主要栄養素の含有量で、いずれも100g当たりの量です。
ここで使っているwebservice関数は、以下の様になっています。同じ構成であれば、手持ちのExcel（365）で再現できます。
=WEBSERVICE("http://castanet.one/TT20_TR.php?KBN=shokuhin-seibun2023&Key1=" & $A3 & "&" &B$1)
これを、料理毎に必要な材料（の食品コード）に割り当てれば、それぞれのカロリーと、主要栄養素の量が割り出せます。なお、登録されているのは、100ｇ単位なので、実際の量が30gならば30/100を乗じます。
食品コードを基に他の情報を取得する発想は、vlookup関数と同様ですね。
料理名から材料（食品）に分解する
それならば、最初から料理と材料の対応を作っておけばよい、ということにすぐ気づくでしょう。区分（KBN）をryoriにすれば、料理に対応する材料を一括で取得できます。
下図が、実際に登録されている内容の一例です。
ここではサンプル的に、「肉じゃが」「だし巻き」「ナポリタン」「ポテトコロッケ」「エビチリ」しか登録されていませんが、Key2を切り替えることで、関連する材料のみ取り出せます。
Office365の最新Updateが済んでいれば、Excel2021以降に採用された「TEXTSPLIT関数」が使えるようになっています。
C1に以下の様な式を入れると、勝手に行数分展開してくれます（下記例では、改行コードで分離しています。最近のExcelは、こんな便利になっていたのかと、驚くばかりです。
=TEXTSPLIT(B1,,CHAR(13)&CHAR(10))
展開後の食品コードを使って、先の例のように食品名、カロリー、その他栄養素などの情報を取得すると、下図のようなイメージができあがります。
ここでは、栄養素情報もまとめて表示していますが、カロリー計算だけであれば、2項目めだけ取得して、必要な重量に換算すれば、全体の熱量（カロリー）が算出できます。
本来の、給食メニュー用としての使い方
文科省の「日本食品標準成分表」は、もともと学校給食で必要な要件を満たしているか確認できるように整備されたもののようです。
このため、1食分で提供される料理全体で、必要なカロリーが取れているか、栄養バランスが中期的に採れるようになっているか、といった検証ができます。
他にも、急にある食材が高額になった場合、部分的に安い食材に入れ替えて、要件を満たすかどうか再検証する、といった使い方もできます。
今回のまとめ
今回ご紹介したwebservice関数の活用例では、まず料理から材料（食品情報）を取得後、その材料（食品コード）からカロリーや栄養素情報を取得する、といった2段構えの構成となります。
工場の生産管理などでも応用できますが、半製品があったら、再度部品展開を行うといった動きが必要です。今回のようなTEXLSPLIT関数と組み合わせることで、再帰的な情報取得ができることが、イメージできたのではないでしょうか。