Excel VBA(エクセル マクロ) そのJ データのグラフ化

最後の難関への挑戦。
データをマトリックス状に算出し、グラフ化を果たす。

データが集まり、いよいよグラフ化の作業に入る。

グラフは縦軸に日付(月)、横軸に項目として作成するため、表を作成し、その表にもとづいてグラフをあらかじめ作っておく。


ということで、縦軸・・・月、横軸・・・項目の表を作成するプログラムを作る。


Dim s As String, s1 As String
For i = 0 To 5 '日付(月)軸
For j = 0 To 11 '項目軸
s = Format(Worksheets("グラフ").Cells(2 + i, 1).Value, "yy/mm")
s1 = Worksheets("グラフ").Cells(1, 2 + j).Value
With Worksheets("データ").Range("1:65536")
.AutoFilter 1, s
.AutoFilter 12, s1
End With
Worksheets("グラフ").Cells(2 + i, 2 + j).Value = Application.WorksheetFunction.Subtotal(3, Worksheets("データ").Range("A2:A10000"))
Worksheets("データ").Range("1:65536").AutoFilter
Next
Next

あらかじめ、1行目には項目、1列目には日付を入れておく。

ある日付(i)のある項目(j)に対し、シート:データについてオートフィルタをかけ、Subtotalで個数を数え、シート:グラフのCells(2 + i, 2 + j)に記入していく。

FOR文で、iは0〜5、jは0〜11まで変化させて、Cells(2 , 2) 〜 Cells(7 , 13)まで全て値を代入する。

表が完成すれば、Cells(2 , 2) 〜 Cells(7 , 13)を元データとしてグラフ化する。


s、s1にグラフの横軸、縦軸の項目を代入し、オートフィルタの条件とする。
formatをつけているのは、縦軸が「2008/4/13」という値になっているので、オートフィルタがかけれるように「08/04」というように書式を変更している。

s = Format(Worksheets("グラフ").Cells(2 + i, 1).Value, "yy/mm")
s1 = Worksheets("グラフ").Cells(1, 2 + j).Value


データシートの1列についてはs(日付)、12列についてはs1(項目)でオートフィルタをかける。

With Worksheets("データ").Range("1:65536")
.AutoFilter 1, s
.AutoFilter 12, s1
End With


Excelで標準実装されているSubtotal関数をVBAに組み込み(Application.WorksheetFunction.を前につける)、オートフィルタがかかった状態で、データの個数をカウントする。

Worksheets("グラフ").Cells(2 + i, 2 + j).Value = Application.WorksheetFunction.Subtotal(3, Worksheets("データ").Range("A2:A10000"))

subtotal(動作,範囲)関数では、動作を番号で指定できる。

1 101 平均 AVERAGE
2 102 数値の個数 COUNT
3 103 データの個数 COUNTA
4 104 最大値 MAX
5 105 最小値 MIN
6 106 積 PRODUCT
9 109 合計 SUM

1〜9はオートフィルタをかけて非表示にした時はカウントしないが、手動で非表示にした場合はカウントしてしまう。
Excel2003以降(?)はその不具合(?)を解消するため、101〜109をつくり、手動で非表示にした場合もカウントしないようにしている。
詳細はExcelのヘルプを見てください。

今回の場合はオートフィルタのみで非表示にしているため、1〜9を使用し、データの個数を数えたいため3を選択した。
オートフィルタをかける題目(1番上の行)も数えるのかなと思ったが、オートフィルタをONにしているときは数えないみたいだ。


1個の検索が終われば、オートフィルタを解除する。

Worksheets("データ").Range("1:65536").AutoFilter


後は2つのFOR文で、マトリックス上に数を数えて代入していく。


以上でグラフ化は完了。


これでとりあえずは全工程を終了した。

後は細かい作業の自動化が残っているが、大筋はこんな感じであった。

忍者Admaxのテキスト広告で収入UP!

実践!ExcelデータベースTOP