Cellsプロパティー、InStr関数、Mid関数、Val関数、VarType関数について、VBAの備忘録（１）

VBA
2022.02.05
この記事は約7分で読めます。

はじめに

　前にエクセルのVBAで保育園用の栄養計算プログラムを作成しましたが、栄養計算する上で最も基本になるのが食品成分表であり、この表は現在、文部科学省の日本食品標準成分表2020年版（八訂）のページから電子書籍（PDF形式）やデータ（Excel形式）をダウンロードできます。

　食事の献立を作成し、その献立の栄養計算をするために必要な食品成分表がExcelのデータ形式でダウンロードできるわけですが、このデータはそのままでは栄養計算ができる形になっていません。

　つまり、表中には、各栄養素のデータが入力されていますが、それらデータの中には次のような文字列のデータが含まれています。

文字列の形で記入されている栄養素のデータ

  • -　：　分析がされていない未測定のデータ
  • Tr 　：　トレースの略字であり、微量含まれているデータ
  • 栄養素の値、(栄養素の値)、(0)、(Tr)　：　文字列や文献などで推定したデータ

　栄養計算をする上では、上述のような文字列データを数値型に変換する必要があります。

　これら文字列データを検索・置換機能を利用して変換する方法もありますが、ここでは簡単なVBAを利用して変換してみます。

　変換するにあたっての基本的考え方は次の通り。

  • -、Tr、(Tr)、(0)　：　数値「0」に変換
  • （栄養素の値)　：　括弧内の文字列を取り出し、数値に変換
  • 栄養素の値　：　文字列として入力されている値を数値に変換

　ここで利用する、VBAの構文はExcelの各セルを指定するためにRangeプロパティーではなくCellsプロパティーを利用し、括弧内の文字列を取り出すのにMid関数、Mid関数内の「(」と「)」の位置を調べるのにInStr関数、取り出した文字列を数値に変換するのにVal関数、栄養素の値が数値で入力されているかどうかを調べるためにVarType関数を利用します。

　それでは、Cellsプロパティー、Mid関数、InStr関数、Val関数、VarType関数について簡単な説明をし、これらVBAの構文を利用してExcel内の栄養素の文字列を数値に変換するプログラムをコーディングします。

Cellsプロパティー

　Excel内のワークシートのセルを個別に選択できるプロパティーです。

　Excel内のセルをRangeプロパティーと共にCellsプロパティーで示した場合を以下に示します。

　A1セルを指定する場合、Range（”A1”）、Cells（1,1）

　D10セルを指定する場合、Range（”D10”）、Cells（4,10）

　i 行 j 列のセルを指定する場合、Rangeでは指定できない、Cells( i , j )

　と書けます。

　したがって、Excel内のi 行 j 列の各セルを指定する際にはCellsプロパティを利用する方がVBAを使う上で利用しやすいことになります。

Mid関数

　Mid関数は、文字列の決めれらた範囲の文字を取り出すのに利用できます。

　Mid関数の構文は、次の通り。

Mid（文字列、取り出す文字の最初の文字の左から数えた位置（数）、取り出す文字数）
ここで、文字列は「”」で囲う、例えば ”abcdefg”

　例えば、文字列＝”ab(256)cd”とした場合に”256”を取り出すにはMid（”ab(256)cd”, 4 , 3 )とすると、文字列 ”256”を取り出せます。

InStr関数

　Instr関数は、指定した文字列の位置を求めることができる関数です。

　Instr関数の構文は、次の通り。

InStr（文字列、指定した文字列）
ここで、文字列や指定した文字列は「”」で囲う、例えば ”abcdefg”
　例えば、文字列＝”ab(256)cd”とした場合にInStr（”ab(256)cd” , ”(” )とすると、文字列中の”(”がある左から数えた位置「3」が得られ、InStr（”ab(256)cd” , ”(” )とすると、文字列中の”)”がある左から数えた位置「7」が得られます。

Val関数

　Val関数は、文字列を数値に変換できます。

　Val関数の構文は、次の通り。

Val（文字列）
ここで、文字列や指定した文字列は「”」で囲う、例えば ”abcdefg”
　例えば、文字列＝”256”とした場合にVal（”256”)とすると、数値の「256」を取り出すことができます。

VarType関数

　VarType関数は、値が文字列の場合にvbString（８）を返します。

　VarType関数の構文は、次の通り。

VarType（変数）
ここで、変数が文字列の場合に「vbString」を返します。したがって、IF 関数と組み合わせて利用します。

　例えば、変数＝”256”とした場合にIF VarType(変数) = vbString then atai=Val(変数）とすることにより、文字列の値を数値の「256」にすることができます。

食品成分表内の文字列を数値に変換するプログラム

　食品成分表は、本表、アミノ酸成分表、脂肪酸成分表、炭水化物成分表の４つの表があり、それぞれExcelファイルで提供され、そのアフィル内には「表全体」、食品成分群のシートに分かれて収められていますが、利用するのは「表全体」のシートのみを使います。

　上述のことを考慮した上で、作成した食品成分表内の文字列を数値に変換するプログラムは次の通りです。

Sub henkan() '食品成分表内の文字列を数値に変換するプログラム名
Dim i, j, L_kakko, R_kakko As Integer
Dim cellatai As String
    Sheets("表全体").Select
    For i = 13 To 2490 ' i行からデータがある最終行を入力
        For j = 5 To 60 'j列から栄養素のデータがある最終列を入力
            If j = 15 Or j = 18 Then GoTo Label1 ' J=15列とJ-１８列は数値に変換しなくともよいので飛ばす処理
            'セルCells(i.j) の値が"-"、"(0)"、"Tr"、"(Tr)"の場合は数値の0を代入
            If Cells(i, j) = "-" Or Cells(i, j) = "Tr" Or Cells(i, j) = "(Tr)" Or Cells(i, j) = "(0)" Then Cells(i, j).Value = 0
            If Left(Cells(i, j), 1) = "(" Then
                L_kakko = InStr(Cells(i, j), "(")
                R_kakko = InStr(Cells(i, j), ")")
                cellatai = Mid(Cells(i, j), L_kakko + 1, R_kakko - L_kakko - 1) 'セルの括弧内の文字を取り出し
                Cells(i, j).Value = Val(cellatai)  'セル内の文字を数字に変換
            End If
            Cells(i, j).Activate
             'セルCells(i.j) の値が数値型文字列の場合に数値に変換
            If VarType(ActiveCell.Value) = vbString Then Cells(i, j).Value = Val(Cells(i, j))
Label1: Next j
    Next i
End Sub

　このプログラムを走らせて、最も利用する一番大きな表の食品成分表の本表「20201225-mxt_kagsei-mext_01110_012 .xlsx」内の文字列などを全て変換するのに約40分弱掛かりましたので、気長にお待ちくださいね。

おわりに

　 如何だったでしょうか？

　Cellsプロパティー、Mid関数、Instr関数、Val関数、VarType関数、食品成分表内の文字列を数値に変換するプログラムについて簡単な紹介をしてきました。

　食品成分表（八訂）内も文字を数値に変換することにより、食事の献立の栄養計算時に各食品成分の栄養素などを計算で求めるのに利用することができます。

　次回は、この食品成分表をMicrosoft Accessに読み込み食品成分のデータベースとして利用したいと思います。

お楽しみに・・・。

以上です。

コメント

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