VLOOKUP関数で食品成分表から食品名を検索しよう!

Excel

みなさん、こんにちは。
シンノユウキ(@y_stadio)です。

今回は連載:「Excelで栄養計算ソフトを自作しよう」の第3回で,

栄養計算ソフトのための食品成分表をExcelで作成しよう!
「Excelで栄養計算ソフトを自作しよう」の連載第2回。今回は、食品成分表をダウンロードし、それを栄養計算に利用できる形式に整えていきます。食品成分表の解説も行っています。

こちらの記事の続きです.

今回の記事では,VLOOKUP関数を用いて,食品番号から栄養素を検索するための仕組みを作成していきます.

では,行きましょう.

前回のおさらい

まずは前回のおさらいからです.

前回は,食品成分表をダウンロードし,それを栄養計算に用いることができるように,データのクリーニングを行いました.

文部科学省のHPから成分表をダウンロードし,置換機能を用いて値をクリーニングしましたね.

今回はその続きからです.

 

栄養計算用のシートを用意しよう!

①シートを追加しよう!

まずは,栄養計算に用いるための専用のシートを用意していきます.前回から使用している,食品成分表が掲載されているExcelブックに,新しいシートを追加してください.シート名は「栄養計算シート」とでもすると良いででしょう.

こんな感じで,シートを追加してください.

 

②項目行を追加しよう

シートを追加したら今度は,項目行を用意しましょう.項目行というのは,栄養計算を行う際に目印になるような行のことで,エネルギーや水分など,栄養素項目が入力されている行のことをここでは指しています.後々,この行を基準として,食品の成分値を算出・計算していくので最初に用意しておきましょう.

シート「本表」の,栄養素項目が表示されている5~8行を選択・コピーし,それを「栄養計算シート」の1行目から貼り付けてください.

ここを選択・コピーして,

 

「栄養計算シート」の最初の行に貼り付けます.

 

③使いやすいように少し加工しよう

ここから,少し項目行を加工していきます.具体的には,以下のようにしていきましょう.

  • 英名が表示されている3行目を削除
  • 重量を入力するための列を食品名の次の列になるように挿入
  • 今回は使用しない食品群の列を削除
  • 食品名の列の3行目:単位を削除

ではやってみましょう.次のようなシートになればOKです.

 

食品名を検索してみよう!

VLOOKUP関数はこうやって使う!

では,ここからVLOOKUP関数を使用していきます.

まずは食品番号から食品名を表示してみましょう.

VLOOKUP関数は以下のように記述します.

この関数を,今回の目的:食品名を検索するに当てはめてみましょう.すなわち,

  • 検索値:食品の食品番号(01001など)
  • 範囲:食品成分表の本表シート
  • 列番号:食品名のある列(食品番号がある列から数えて3列目)
  • 検索方法:部分一致では他の値を拾う可能性があるため完全一致→FALSEで

ということになります.

実際に記述してみよう

では,食品名を表示させるセルに,VLOOKUP関数を記述していきましょう.今回は,「栄養計算シート」のセルC4に記述するようにします.式は以下のようになります:

=VLOOKUP(A4,本表!B9:BO2199,3,FALSE)

  • A4は,食品番号が入力する予定のセル番地です.
  • 本表!B9:BO2199は,成分値が入力されている範囲です.本表シートのセルB9からBO2199までの範囲を示しています.なぜA列から指定しないのかというと,食品群列に入力されている数値が検索の邪魔になってしまうからなのですね.そのため,範囲はB列列の食品番号からになります.
  • 3は,列番号で,先ほどの指定した検索する範囲,すなわち本表!B9:BO2199の3列目に食品名が位置しているため,この値になります.
  • 検索方法は,完全一致を意味するFALSEを指定しています.

こんな画面になりましたか?エラーが出ていますが,問題はありません.食品番号がまだ入力されていないためですので.

ではいよいよ食品番号を入力してみましょう.

セルA4に食品番号を入力してください.

この場合,食品番号の先頭の0は省略されてしまいますが,検索上は問題ありません.文字列から数値に変換した際に成分表の方も先頭の0は削除されていまっているので,問題はないのですね.

例として,食品番号「01001」の「アマランサス 玄穀 」を検索してみました.「01001」の先頭の0は無視されてしまうので,表示上は「1001」になっていますが,問題なく,食品名の場所に「アマランサス 玄穀 」が表示されましたね.

これで,晴れて食品番号から食品名を検索することができました.やってみると以外と簡単ですよね.

今回はここまで.次回からはエネルギーなどの成分値を検索してみましょう.

まとめ

今回は,栄養計算シートを作成し,実際に食品名の検索を行いました.

具体的には以下の2つを行いました.

  • 栄養計算シートを追加し,成分値などの項目行を追加した
  • VLOOKUP関数を用いて食品番号から食品名を表示した

また次回から頑張りましょう.

連載目次

  1. Excelで栄養計算ソフトを自作しよう!
  2. 栄養計算ソフトのための食品成分表をExcelで作成しよう!
  3. VLOOKUP関数で食品成分表から食品名を検索しよう!
  4. Excelで食品成分表から食品のエネルギーを算出してみよう!
  5. Excelで食品成分表から色々な栄養素の成分値を算出してみよう!
  6. SUM関数で算出した栄養素を合計してみよう!
  7. SUMIF関数で食事区分ごとに栄養素を合計しよう!
  8. 【完成】Excelで栄養計算ソフトを作成してみた!