食品成分表をクリーンアップするVBAプログラム

エクセルで栄養計算ソフト自作
この記事は約6分で読めます。

食品成分表が文部科学省のHPで公開されていますが,数字が文字列で入力されていたり,計算に利用でいないハイフンが入力されていたりして,そのままの形では栄養計算に使用する事ができません.

そのため,それをクリーンアップし,栄養計算可が能な形にする必要があります.この作業は,一定の手順を踏むことで誰でも行うことができますが,少し面倒ではあります.

また,プログラムとして手順を残すことで,新しい食品成分表が公表された際でも対応することができます.

そこで,それを簡単にクリーンアップするためのプログラムを作成しました.コピペで実行するだけで,簡単にクリーンアップされた状態の食品成分表が手に入ります.では行きましょう.

なぜ食品成分表をそのまま計算に利用できないのか?

はじめに,なぜ食品成分表はそのままの形で計算に利用できないのかを説明したいと思います.それは,

  • 文字列で入力されている数字があるから
  • Trやカッコなど特別な意味で用いられている文字列があるから

ことによります.以下のシートをご覧ください.

このシートを見るだけでは,とてもそのまま栄養計算に用いることはできなさそうですよね.Trやハイフン,カッコなどはそれぞれ以下のような意味を持ちます.

原材料の収載値等と配合割合から栄養計算の手法を用いて計算により成分値を求める場合で、原材料の成分値に「-」がある成分については、「-」以外の数値を用いて計算を行い、[ ]を付けて数値を示した

記号や記法意味
未推定
0食品成分表の最小記載量の1/10未満または検出されなかった
Tr最小記載量の1/10以上含まれているが5/10未満
(0)文献等により含まれていないと推定
(Tr)文献等により微量に含まれていると推定
(数字)穀類,果実類及びきのこ類の一部の食品で類似食品の収載値から推計や計算
[数字]原材料の収載値等と配合割合から栄養計算の手法を用いて計算により成分値を求める場合で、原材料の成分値に「-」がある成分

[数字]に関しては2018年の追補版から追加されました.

一見そのまま利用できそうなカッコも,Excelにおいては,カッコというのは負の値を持っていると認識されます.そのため,このカッコもどうにかしないと,栄養計算には使用できないということになりますね.なかなか面倒な仕様になっているのです.

本来であれば,Trと(Tr)は区別して栄養計算に用いられるべきです.しかし,栄養計算に食品成分表を用いる場合であれば,そこまで厳密にする意味はなさそうです.かえって煩雑になります.同様に,ハイフンも,本来であれば特別に考慮 すべきかもしれませんが,こと栄養計算に限ってはそこまでの厳密さは必要ないでしょう.

そのため,

  • -,Tr,(0),(Tr)については0に
  • (数字),[数字] については数字に

という具合に一括に処理する方がよさそうです.以下では,このような修正基準にしたがって,修正するための方法をお示ししたいと思います.

手動でクリーンアップする場合

今回のメインはプログラムを用いて一括で修正する方法です.

しかし,手動でも意外と簡単に修正することができますので,ご紹介します.これについては,一度別の記事で紹介していますので,以下のリンクからご覧ください.

VBAプログラムを用いてクリーンアップする場合

ではメインである,プログラムを用いた食品成分表のクリーンアップの方法をご紹介したいと思います.

手順①:食品成分表の含まれるブックを開く

食品成分表の含まれるExcelのブックを開いて下さい.このブックには,たとえば食品成分表の2015年公表の本表や2016年公表の追補など,シートが複数入っていても構いません.

手順②:VBEを開く

次に,コードを入力するための場所を開いてください.VBEと呼ばれる,VBAプログラムを入力するためのエディタです.Alt+F11で開く事ができます.

手順③:標準モジュールを追加

VBEを開くことができたら,標準モジュールを挿入し,そこにコードを貼り付けます.

「挿入」→「標準モジュール」の順にクリックしてください.

すると左側のウインドウに標準モジュールが挿入されたかと思います.

手順④:プログラムをコピペ→実行

「Module1」をダブルクリックすると,コードを入力するためのウインドウが右側に表示されます.これに,以下のコードをコピペしてください:

Sub main()
    Dim ws As Worksheet
    For Each ws In Worksheets
        Call cleanTable(ws)
    Next ws
End Sub

Sub cleanTable(ws As Worksheet)
    Application.ScreenUpdating = False
    Dim i As Long, j As Long
    Dim LastRow As Long, LastCol As Long
    
    ws.Copy after:=Worksheets(Worksheets.Count)
    ActiveSheet.Name = ws.Name & "clean"
    
    LastRow = Range("A10000").End(xlUp).Row
    LastCol = Range("XFD9").End(xlToLeft).Column
    
    Dim ary As Variant
    ary = Range(Cells(1, 1), Cells(LastRow, LastCol))
    Columns("A:C").NumberFormatLocal = "0_ "
    
    For i = 9 To LastRow
        For j = 1 To LastCol
            ary(i, j) = cleanString(ary(i, j))
        Next j
    Next i
    Range(Cells(1, 1), Cells(LastRow, LastCol)) = ary
End Sub

Function cleanString(str)
    str = replace(str, "Tr", "0")
    str = replace(str, "-", "0")
    str = replace(str, "(", "")
    str = replace(str, ")", "")
    str = replace(str, "[", "")
    str = replace(str, "]", "")
    cleanString = str
End Function

そして,元のExcelの画面に戻り,「マクロ」→「main」を実行してください.ブックに含まれる全てのシートがクリーンアップされ,栄養計算に使える形に変更されました!

まとめ

今回は食品成分表をクリーンアップするためのプログラムを紹介しました.手動でも食品成分表のクリーンアップは可能ですが,プログラムとして手順を記録することで,新しい食品成分表が公表された際にも活用できます.ぜひご利用ください.

タイトルとURLをコピーしました