食品成分表をクリーンアップするVBAプログラム【2020年版対応】

みなさん，こんにちは。
シンノユウキ（shinno1993）です。

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

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

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

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

なぜ食品成分表をそのまま計算に利用できないのか？
手動でクリーンアップする場合
VBAプログラムを用いてクリーンアップする場合
まとめ

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

はじめに，なぜ食品成分表はそのままの形で計算に利用できないのかを説明したいと思います．それは下記によります：

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

以下のシートをご覧ください．

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

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

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

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

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

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

そのため，

-，Tr，（０），（Tr）については０に
（数字），[数字] については数字に

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

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

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

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

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

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

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

食品成分表の含まれるExcelのブックを開いて下さい．シートが複数入っていても構いません．

手順②：VBEを開く

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

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

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

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

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

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

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

Const START_ROW As Long = 13 '食品が最初に含まれる行番号

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 = Cells(Rows.Count, 2).End(xlUp).Row
    LastCol = Cells(START_ROW, Columns.Count).End(xlToLeft).Column
    
    Dim ary As Variant
    ary = Range(Cells(1, 1), Cells(LastRow, LastCol))
    Columns("A:C").NumberFormatLocal = "0_ "
    
    For i = START_ROW To LastRow
        For j = 1 To LastCol
            If Left(ary(i, j), 1) = "(" Then Cells(i, j).NumberFormatLocal = "(G/標準)"
            If Left(ary(i, j), 1) = "[" Then Cells(i, j).NumberFormatLocal = "(G/標準)"
            If ary(i, j) = "-" Then Cells(i, j).NumberFormatLocal = "-"
            If ary(i, j) = "(Tr)" Then Cells(i, j).NumberFormatLocal = "(""Tr"")"
            If ary(i, j) = "Tr" Then Cells(i, j).NumberFormatLocal = """Tr"""
            If ary(i, j) = "*" Then Cells(i, j).NumberFormatLocal = """*"""
            ary(i, j) = cleanString(ary(i, j))
        Next j
    Next i
    Range(Cells(1, 1), Cells(LastRow, LastCol)) = ary
    
    Application.ScreenUpdating = True
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, "]", "")
    str = Replace(str, "†", "")
    str = Replace(str, "*", "0")
    cleanString = str
End Function

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

なお，クリーニング後も下記のように数字にカッコがついており，「クリーニングできてないのでは？」と思われるかもしれませんが，これはセルの書式設定でカッコがついているように見せているだけです。そのため。数字上は問題なく計算に使用できるはずです。