Excel VBA 単位がついた数値を計算する

スポンサーリンク
スポンサーリンク

単位がついた数値を計算する方法

「Excel」では、数値のあとに「円」などの単位を付加すると数値が文字列に変換され、計算するとエラーが返ります。VBAで、計算できる形式で数値に単位をつける方法は、セルの表示形式を設定するRange【レンジ】オブジェクトのNumberFormatLocal【ナンバーフォーマットローカル】プロパティを使用して単位が「円」であれば、「”G/標準””円”””」と指定すれば、数値に文字列の単位が付加しても数値として計算できます。しかし元々、文字列として単位が付加された数値がセルに入力されている場合には、文字列を数値に変換するVal【バル】関数を使用し、数値に変換してから計算する必要がありますので、その方法をご紹介します。

実行例

コード例

Sub 単位付き計算()
Dim 行 As Long
Dim i As Long
Dim 合計1 As Long
Dim 合計2 As Long
行 = Cells(Rows.Count, 1).End(xlUp).Row - 1
For i = 2 To 行
Cells(i, 4) = Val(Cells(i, 2)) * Val(Cells(i, 3))
合計1 = 合計1 + Val(Cells(i, 3))
合計2 = 合計2 + Val(Cells(i, 4))
Next i
Cells(行 + 1, 3) = 合計1
Cells(行 + 1, 4) = 合計2
Range("C" & 行 + 1).NumberFormatLocal = "##,###" & "個"
Range("D2", "D" & i + 1).NumberFormatLocal = "##,###" & "円"
End Sub

コードの解説

2行目【Dim 行 As Long】
A列のデータが入力されている最終行-1の行番号を格納する変数「行」を長整数型(Long)で宣言します。


3行目【Dim i As Long】
繰り返し処理で使用する、セルの2行目から変数「行」行目の行番号を順次格納するカウンター変数「i」を長整数型(Long)で宣言します。


4行目【Dim 合計1 As Long】
C列の販売数量の合計数を格納する変数「合計1」を長整数型(Long)で宣言します。


5行目【Dim 合計2 As Long】
D列の売上額の合計額を格納する変数「合計2」を長整数型(Long)で宣言します。


6行目【行 = Cells(Rows.Count, 1).End(xlUp).Row – 1】
Cells【セルズ】プロパティで引数の、参照する行にRows【ロウズ】プロパティですべての行を参照し、Count【カウント】プロパティでその行数を取得します。つまりA列のセルの最終行を参照して、Range【レンジ】オブジェクトのEnd【エンド】プロパティを使用して上方向にデータが入力されている最終行まで移動してRow【ロウ】プロパティを使用してその行番号を取得します。その取得した行番号に1減算した行番号を変数「行」に代入します。1を減算する理由はサンプルの表で説明すると1減算しない行番号はA列の「合計」と記述されているセルの位置で1減算することによって「合計」と記述されている行番号の1個上つまり、データの最終行番号を表します。


7行目【For i = 2 To 行】
For Next【フォーネクスト】ステートメントを使用して、繰り返し処理の始まりです。条件式は、カウンター変数「i」にセルの1行目の項目行を除いた2行目から、データが入力されている最終行番号が格納されている変数「行」までの値を順次代入してカウンター変数「i」が変数「行」になるまで繰り返します。


8行目【Cells(i, 4) = Val(Cells(i, 2)) * Val(Cells(i, 3))】
文字列を数値に変換するVal【バル】関数を使用して、「円」や「個」の単位がついて文字列になっているB列の単価とC列の販売数量を数値に変換して乗算(掛け算)した結果をD列の売上額のセルに代入します。Val【バル】関数は文字列の左端から数値に変換できる文字列(数字)を計算ができる数値に変換し、数字以外の文字列がみつかった時点で変換を終了します。ここでは、「円」「個」を除いた数字を数値に変換しています。


9行目【合計1 = 合計1 + Val(Cells(i, 3))】
販売個数の合計を計算します。C列の販売数量列のカウンター変数「i」行目のセルのデータをVal【バル】関数で数値に変換し、変数「合計1」に格納されているデータを加算(足し算)して変数「合計1」に再代入します。繰り返し処理の中で結果的には、変数「合計1」の値はC列のデータ(販売数量)の合計が格納されます。


10行目【合計2 = 合計2 + Val(Cells(i, 4))】
販売個数の合計を計算します。D列の売上額列のカウンター変数「i」行目のセルのデータをVal【バル】関数で数値に変換し、変数「合計2」に格納されているデータを加算(足し算)して変数「合計2」に再代入します。繰り返し処理の中で結果的には、変数「合計2」の値はD列のデータ(売上額)の合計が格納されます。


11行目【Next i】
7行目からここまでの処理を変数「行」に格納されているデータが入力されている最終行数分繰り返します。


12行目【 Cells(行 + 1, 3) = 合計1】
変数「合計1」に格納されているC列(販売数量)のデータの合計値をCells【セルズ】プロパティで参照した3列(C)の変数「行」列+1列、つまりデータが入力されている最終行の次の行に代入します。


13行目【Cells(行 + 1, 4) = 合計2】
変数「合計2」に格納されているD列(売上額)のデータの合計値をCells【セルズ】プロパティで参照した4列(D)の変数「行」列+1列、つまりデータが入力されている最終行の次の行に代入します。


14行目【Range(“C” & 行 + 1).NumberFormatLocal = “##,###” & “個”】
Range【レンジ】オブジェクトのNumberFormatLocal【ナンバーフォーマットローカル】プロパティを使用してセルにユーザー定義の書式を設定します。3桁毎にカンマを打ち、最後に文字列の「個」を表記します。設定するセルのRange【レンジ】オブジェクトは、Range【レンジ】プロパティでC列の変数「行」+1行目、つまりC列(販売数量)のデータの最終行の次の行を指定します。


15行目【Range(“D2”, “D” & i + 1).NumberFormatLocal = “##,###” & “円”】
Range【レンジ】オブジェクトのNumberFormatLocal【ナンバーフォーマットローカル】プロパティを使用してセルにユーザー定義の書式を設定します。3桁毎にカンマを打ち、最後に文字列の「円」を表記します。設定するセルのRange【レンジ】オブジェクトは、Range【レンジ】プロパティでD列の2行目からD列の変数「行」+1行目、つまりD列(売上額)のデータの最終行の次の行を指定します。


以上で、単位がついた数値を計算する方法についての解説を終了します。
ありがとうございました。

スポンサーリンク
スポンサーリンク
スポンサーリンク
スポンサーリンク