表計算ソフトEXCELを使った栄養計算

「五訂食品成分表」をワークシートに用意しました。このワークシートのデータ全体をvlookup関数の検索範囲に選び、食品コードを検索キー(検索対象)として、対象となる食品を探し出し、その食品の成分値を使って、栄養計算する計算式をvlookup関数を使って作る。


2)五訂食品成分表からの栄養素の検索
 

課題

Yドライブ('aya-svr2' の tue3haifu(Y))に、「食品成分表.xls」というファイルがあり、「食品成分表201X」(香川芳子監修 女子栄養大学出版部)にある食品成分表を入力し、食品番号の順に並べ替えてあります。
  このファイルをXドライブにコピーし、VLOOKUP関数を使って、自動的に栄養計算をおこなうファイルを作りましょう。

実際に以下で「Excel」の課題をおこなっていって、VLOOKUP関数を使った栄養計算の方法をマスターしよう。

[課題]

問1) Y:ドライブの「食品成分表.xls」をX:ドライブにコピーし、その中にあるシート「食品成分表」を学籍番号h.xlsのファイルの方にコピーせよ。
両方のファイルを開き、移動するシートのシート名のところで、右ボタンを押し、メニューから「(シートの)移動またはコピー」を選べばよい。なお、移動先ブック名は必ず移動先のファイルを選ぶこと。


問2) 別シート(シート名:栄養計算方法)に、i)献立(朝・昼・おやつ・夜の別)、ii)料理名、iii)食材、iv)量(g)、v)食品番号、vi)食品成分表における食品名 vii)食品群 viii)4つの食品群、ix)エネルギー点数、x)タンパク質(g)、xi)脂質(g)、 xii)炭水化物(g)、xiii)繊維(g)、 ixv)カルシウム(mg)、 xv)リン(mg)、 xvi)鉄(mg)、 xvii)ナトリウム(mg)、 xviii)カリウム(mg)、 ixx)レチノール当量(μg)、 xx)ビタミンB1(mg)、 xxi)ビタミンB2(mg)、 xxii)ビタミンC(mg)の項目を作れ。なお、1行目は各栄養素が何列目にあるかの数字を書く欄として空けておくこと。


問3) iv)量(g)、v)食品番号を入力すれば、vi)食品成分表における食品名、ix)エネルギー点数からxxii)C(mg)までが自動的に計算出来るように計算式を入力せよ。
  なお、iv)量(g)、v)食品番号のどちらかが入力されていない場合は、空白を出力するようにしておくこと。


問4) 食品番号は5桁で、1000番台は「穀類」(第四群)、2000番台は「いもおよびでんぷん類」(第3群)、・・・、17000番台は「調味量および香辛料」(第四群)であることを使い、v)食品番号から、その食品のvii)食品群とviii)4つの食品群を、関数INTおよびvlookup関数を使って計算せよ。[下表参考] なお、v)食品番号が入力されていない場合は、空白を出力するようにしておくこと。


栄養計算例:「食品成分表201X」資料編(香川芳子監修 女子栄養大学出版部)P.82に載っている香川芳子案「4つの食品群標準量による献立作成」の献立を以下に表記します。この例を使いましょう。
 
「4つの食品群標準量による献立作成」
献立 料理名 食材 g
朝食 ハニートースト 食パン 60
    はちみつ 7
  ほうれん草入り
スクランブルエッグ
トマト漆え
鶏卵 55
    ほうれん草 50
    3
    トマト 50
  果物 オレンジ 100
  牛乳 低脂肪牛乳 200
昼食 ごはん 胚芽精米 68
  肉野菜炒め 豚口ース肉 40
    キャベツ 60
    玉ねぎ 30
    にんじん 20
    きくらげ 2
    緑ピーマン 20
    8
  冷やっこ 絹ごし豆腐 140
    あさつき 5
    しょうが 3
  果物 いちご 110
夕食 ごはん 胚芽精米 90
  みそ汁 なめこ 30
    みつぱ 5
  アジの塩焼き アジ 65
  大根おろし 大根 50
  ひじきの煮物 ひじき(乾) 8
    にんじん 20
    板こんにゃく 20
    3
    砂糖 2
  きゅうりの梅肉あえ きゅうり 50
    しその葉 1
    梅干し 5
間食 焼き芋 さつま芋 60
  ヨーグルト
ジャム漆え
 ヨーグルト
(全脂無糖)
100
    いちごジャム 5
  紅茶 紅茶(浸出液) 150

問5) 上表を、シート名「4つの食品群標準量による献立作成」に、作表しなさい。



問6) 上表の右に、「食品番号」の項目列をつくり、それぞれの食品の「食品番号」を「五訂増補食品成分表」から探し、入力せよ。


問7) 問4)で完成したシート「栄養計算方法」をコピーし、コピーしたシート(シート名:栄養計算)に、シート「4つの食品群標準量による献立作成」のデーターをコピーし、i)番号(1から昇順に番号付け)、ii)献立(朝・昼・おやつ・夜の別)、iii)材料、iv)量(g)、v)食品コードの列を完成させ、他の列の各栄養素が計算されているとを確かめよ。


問8) 「データ」->「集計」を用いて、献立(朝・昼・おやつ・夜の別)別の各栄養素の合計を求めなさい。(シートをコピーしてからする事。シート名:献立別集計)  また、「4つの食品群標準量による献立作成」の摂取栄養素量が算出されていることを確認しなさい。


問9) 食品群ごとに並べ替え、各群別の各栄養素の合計を求めなさい。(シートをコピーしてからする事。シート名:群別集計)