そのままでは計算に使えない食品成分表をクリーンアップするプログラム

Excel

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

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

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

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

 

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

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

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

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

 

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

 

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

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

そのため、

  • -、Tr、(0)、(Tr)については0に
  • (数字)については数字に

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

 

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

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

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

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

 

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

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

といっても、プログラムの作り方や解説なんかはまた別の機会にしたいと思います。ここでは、単純にコードを示し、それを実行するための方法を紹介するにとどめたいと思います。

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

まずは、食品成分表の含まれるブックを開いてください。食品成分表は、文科省のホームページからダウンロードできます。

一括ダウンロード(Excel:日本語) (Excel:917KB) excel

ダウンロードしたら、そのブックを開いてください。

 

手順②:VBEを開く

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

 

手順③:コードを貼り付けて実行

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

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

 

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

 

これをダブルクリックすると、右側にウインドウが現れます。これに、以下のコードをコピペしてください。

Sub cleanTable()
    On Error GoTo Err
    Const STR_NEW_WS_NAME As String = "本表 クリーンアップ"
    Dim i As Long, j As Long
    Dim LastRow As Long, LastCol As Long
    Dim ws As Worksheet
    Dim flg As Boolean
    
    'すでにシートが存在する場合
    For Each ws In Worksheets
        If ws.Name = STR_NEW_WS_NAME Then
            MsgBox "すでにクリーンアップ済みのシートが存在します"
            Exit Sub
        End If
    Next ws
    
    '本表のシートが存在しない場合
    flg = False
    For Each ws In Worksheets
        If ws.Name = "本表" Then
            flg = True
        End If
    Next ws
    If flg = False Then
        MsgBox "本表シートが存在しません。正しいブックを選択してください。"
    End If
    
    Worksheets("本表").Copy after:=Worksheets(Worksheets.Count)
    ActiveSheet.Name = STR_NEW_WS_NAME
    
    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) = Replace(ary(i, j), "Tr", "0")
            ary(i, j) = Replace(ary(i, j), "-", "0")
            ary(i, j) = Replace(ary(i, j), "(", "")
            ary(i, j) = Replace(ary(i, j), ")", "")
        Next j
    Next i
    
    Range(Cells(1, 1), Cells(LastRow, LastCol)) = ary
    MsgBox "終了しました"
    
    Exit Sub
    
Err:
    MsgBox "予期せぬエラーが発生しました。" & vbCrLf & "処理を中断します。"
End Sub

 

これでプログラムを実行するための手順は整いました。コピペしたコードの上にカーソルを置いて、F5を押してください。コードが実行されます。何事もなければ、クリーンアップ済みの本表シートが新しく挿入されているはずです。

 

まとめ

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