パソコン活用研究5番街（Visual Basic、Excel(VBA)、BASIC　プログラミング研究）

各種データの集計

必要に迫られて作ったデータ集計用のマクロを２個紹介します。ちょっとアレンジすれば、いろんな
ケースのデータ集計、整理に応用できるでしょう。

１　前回との差を計算する

これは、おじさんのホームページの各ページのページビューの累計データ（多い順に並んでいる）から、
前回（前月）との差を計算するために作ったものです。差をとることにより、今回（今月）のアクセス数を
割り出させる目的で作りました。ページ数が１００以上になっているので、自分で手計算はかったるい
です絶対やらないでしょう）。こういうものはEXCELにやらせるのが一番です。非常に短いシンプルな
マクロですが、いろんな例に応用できるでしょう。

例えば、得点順に並んでいるテストのデータから、前回との得点差を計算させる、というようなケース
にはほとんど手直しなしで使えます。（図１の例）
データが名簿順に並んでいれば、セルに簡単な計算式を入れて計算させるだけで済みますが、得点順
にデータが並んでいると、そう簡単にはいきません。同一人物を見つけ出して、手計算して１個１個、差を
求めるという気の遠くなる作業が待っています。もしデータが５００もあったらお手上げですね。
そんなときは、マクロを組んでEXCELにやらせましょう。

(図１）


VBAのコードは以下の通りいたってシンプルです。
標準モジュール　Module1はUserForm1を表示するだけです。

UserForm1
①から⑤は、TextBoxに入力された列のデータ（"A"とか"B"とか）をA列なら1, B列なら2になるように
変換しています。これはアスキーコードで大文字のAが65、Bが66、Cが67となっていることをうまく利用
しています。Cellsメソッドを上手に使うためにEXCELでよく使う技のひとつです。
ここではかなり手を抜いたコードにしているので列は"A"から"Z"までしか有効に使えません。
もっと多くの列（AA以降）を使えるようにするには、どうしたらいいかは皆さんで工夫して下さい。

⑥、⑦はデータの入っている終端セルを探す常套手段ですね。詳しくはセル関連基本マクロ１に終端セル
取得方法について記述してありますので、参考にして下さい。

⑧から始まるFor ループでは、キーが一致したら、そこの値の差をとるという作業をしています。

標準モジュール　Module1
Sub Form_show()
UserForm1.Show
End Sub


フォーム　UserForm1
Private Sub CommandButton1_Click()
keya = TextBox1.Text: c1 = Asc(keya) - 64　　　'①
keyb = TextBox2.Text: c2 = Asc(keyb) - 64　　　'②
Vala = TextBox3.Text: c3 = Asc(Vala) - 64　　　　'③
valb = TextBox4.Text: c4 = Asc(valb) - 64　　　　'④
out = TextBox5.Text: c5 = Asc(out) - 64　　　　　'⑤
e = ActiveSheet.Cells(65536, c1).End(xlUp).Row　　'⑥
f = ActiveSheet.Cells(65536, c2).End(xlUp).Row　　'⑦
For i = 1 To f　　　　　　　　　　　　　　　　　　　　　　　'⑧
For j = 1 To e
If Cells(i, c2).Value = Cells(j, c1).Value Then
Cells(i, c5).Value = Cells(i, c4).Value - Cells(j, c3).Value
End If

Next j
Next i

End Sub



２　正規表現で一致したデータを集計する

営業の売り上げデータなどをEXCELで管理することがあるかと思います。ここで紹介するのは、案件毎に
記入された売り上げデータから、営業担当や製品をキーに、tanakaならtanakaだけの、CobolならCobolだけの
売り上げ、利益を抽出し集計するマクロです。

これもEXCELに備わっている各種機能や、セルに計算式を入れていけばできるものですが、営業担当が
複数名ついたり、製品が複数だったりすると結構やっかいなことになります。
そこで、以下の図２のようにまとめられたデータから、担当別、製品別の集計を簡単に出すための汎用的
なマクロを組みました。
図２では、担当者(Name)をキーとし、"kato"が関わっている（単独あるいは、複数で担当）した案件の
売り上げ(Sales)と利益(Margine)を集計させました。利益はほんとうはProfitとすべきでしょうが、ま、
いいじゃないですか




UserForm1
①からの3行で検索する範囲に入力されたデータをCellsメソッドに渡せるように数値データに変換しています。
例えば上記のようにTextBox1に"A2"、TextBox2に"A12"を入力すると、列データと行データに分解し、
c1=1, r1=2, c2=1, r2=12　を得ます。

②変数q にキーワードを代入します。

③このForループでキーワードに合致する項の値を集計します。キーワードと合致するかどうかは
a Like ("*" + q + "*")　という条件式で判定しており、キーワードを含んでいれば合致すると判定
しています。
上記の例では、"kato"そのものだけでなく、"kato"を含む全ての項("takano/kato"など）を集計しています。

以下同様に、３列分の集計ができるようになっています。

Sub user_Form_show()
UserForm1.Show

End Sub


Private Sub CommandButton1_Click()
l1 = Len(TextBox1.Text): l2 = Len(TextBox2.Text)　　　　　　　　　　　　　　　　　　　　　　'①
c1 = Asc(Mid(TextBox1.Text, 1, 1)) - 64: r1 = Val(Mid(TextBox1.Text, 2, l1 - 1))
c2 = Asc(Mid(TextBox2.Text, 1, 1)) - 64: r2 = Val(Mid(TextBox2.Text, 2, l2 - 1))

q = TextBox3.Text　　　　　　　　　　　　　　　　　'②

If TextBox4.Text <> "" Then
c4 = Asc(Mid(TextBox4.Text, 1, 1)) - 64

t = 0
For i = r1 To r2　　　　　　　　　　　　　　　　　　　'③
a = Cells(i, c1).Text
If a Like ("*" + q + "*") Then
t = t + Val(Cells(i, c4).Value)
End If
Next
TextBox7.Text = Str$(t)

End If

If TextBox5.Text <> "" Then
c5 = Asc(Mid(TextBox5.Text, 1, 1)) - 64
t = 0
For i = r1 To r2
a = Cells(i, c1).Text
If a Like ("*" + q + "*") Then
t = t + Cells(i, c5).Value
End If
Next
TextBox8.Text = Str$(t)
End If

If TextBox6.Text <> "" Then
c6 = Asc(Mid(TextBox6.Text, 1, 1)) - 64
t = 0
For i = r1 To r2
a = Cells(i, c1).Text
If a Like ("*" + q + "*") Then
t = t + Cells(i, c6).Value
End If
Next
TextBox9.Text = Str$(t)
End If

End Sub









ＴｏｐＰａｇｅ > Visual BasIc＆Ｅｘｃｅｌ活用研究目次