VBA 仕入管理９（集計とグラフ）

仕入管理もこれで最終章となります。最後は蓄積したデータを集計し、見直しします。数値の把握にはグラフが効果的です。
期間、抽出条件で仕入れ実績を集計し、グラフ表示します。
定型、定位置に集計することによって、グラフの設定を初回のみとします。
新しくワークシート「集計」を追加してください。

集計のmodule全体
変数の宣言
データ範囲の設定
1. ワークシート「仕入実績」
2. ワークシート「集計」
集計表データの抽出と書き込み
グラフの作成

集計のmodule全体

Sub SpreadT() ‘仕入売上集計

Dim h As Long
Dim k As Long
Dim DTH(1 To 7) As Object
Dim str1 As String
Dim str2 As String

On Error GoTo jump1

‘データ範囲の設定
Set DTH(1) = Worksheets(“仕入実績”).Range(“A2”).CurrentRegion
Set DTH(7) = Worksheets(“集計”)

‘データカラムの設定
Set DTH(2) = DTH(1).Columns(14) ‘MONTH
Set DTH(3) = DTH(1).Columns(7) ‘数量
Set DTH(4) = DTH(1).Columns(11) ‘金額
Set DTH(5) = DTH(1).Columns(4) ‘仕入先
Set DTH(6) = DTH(1).Columns(6) ‘商品

str1 = “*” & DTH(7).Cells(6, 3) & “*” ‘仕入先
str2 = “*” & DTH(7).Cells(7, 3) & “*” ‘商品

h = DTH(7).Cells(4, 3) ‘MONTH
k = DTH(7).Cells(5, 3) ‘MONTH

‘集計表データの抽出と書き込み
Dim f As Integer
f = 11

With Application.WorksheetFunction

Do While h <= k
　DTH(7).Cells(f, 1) = h
　Dim ans(1 To 2) As Long
　ans(1) = .SumIfs(DTH(3), DTH(2), h, DTH(5), str1, DTH(6), str2)
　ans(2) = .SumIfs(DTH(4), DTH(2), h, DTH(5), str1, DTH(6), str2)

　DTH(7).Cells(f, 2) = ans(1)
　DTH(7).Cells(f, 3) = ans(2)

　h = h + 1
　f = f + 1

’12月の次は翌年の1月作成
　If Right(h, 2) > 12 Then
　　h = h + 88
　End If
Loop

End With

DTH(7).Cells(10, 2) = “仕入数量”
DTH(7).Cells(10, 3) = “仕入金額”

‘前のデータが残っていたら一行ずつ消去
Do While DTH(7).Cells(f, 1) <> “”
　DTH(7).Cells(f, 1) = “”
　DTH(7).Cells(f, 2) = “”
　DTH(7).Cells(f, 3) = “”
　DTH(7).Cells(f, 4) = “”
　f = f + 1
Loop

‘グラフの作成
On Error GoTo jump2
　ActiveSheet.ChartObjects(1).Delete‘前のグラフを消去
jump2:

Range(“A10”).Select ‘集計データを選択
ActiveSheet.Shapes.AddChart2.Select ‘グラフを挿入
ActiveChart.ChartType = xlLine ‘「折れ線」

With ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)
　.AxisGroup = 2 ‘第2軸にする
　.ChartType = xlColumnClustered ‘barに変更する
End With

jump1:

Range(“B3”).Select

End Sub

一説ずつ解説していきます。

変数の宣言

Dim h As Long
Dim k As Long
Dim DTH(1 To 7) As Object
Dim str1 As String
Dim str2 As String

On Error GoTo jump1
（中略）
jump1:

使用する変数を宣言します。
On Error GoTo jump1はエラー処理でエラーが発生した場合、jump1:まで処理を飛ばします。

データ範囲の設定

ワークシート「仕入実績」

‘データ範囲の設定
Set DTH(1) = Worksheets(“仕入実績”).Range(“A2”).CurrentRegion
Set DTH(7) = Worksheets(“集計”)

変数の宣言にてDim DTH(1 To 7) As Object、DTH(1)からDTH(7)をObjectとして宣言しています。
ここではワークシート、カラムを代入するために使用しています。
Worksheets(“仕入実績”).Range(“A2”).CurrentRegionではA2セルに接続するデータ範囲をCurrentRegionで取得しています。
ワークシート「仕入実績」の列値を()で指定しています。

ワークシート「集計」

str1 = “*” & DTH(7).Cells(6, 3) & “*” ‘仕入先
str2 = “*” & DTH(7).Cells(7, 3) & “*” ‘商品

h = DTH(7).Cells(4, 3) ‘MONTH
k = DTH(7).Cells(5, 3) ‘MONTH

str1にはDTH(7).Cells(6, 3)、ワークシート「集計」C6セルのテキストを代入します。
str2にはDTH(7).Cells(7, 3)、ワークシート「集計」C7セルのテキストを代入します。

str1には仕入先、“*” &… & “*” を利用して仕入先名称の一部で抽出できるようにしています。
str2には商品、“*” &… & “*” を利用して商品先名称の一部で抽出できるようにしています。

hにはDTH(7).Cells(4, 3)、ワークシート「集計」C4セルのテキストを代入します。
kにはDTH(7).Cells(5, 3)、ワークシート「集計」C5セルのテキストを代入します。

hには集計開始月。
kには集計末月を代入します。

集計表データの抽出と書き込み

Do While…Loop

Dim f As Integer
f = 11

Do While h <= k
　DTH(7).Cells(f, 1) = h
（中略）
　h = h + 1
　f = f + 1
　DTH(7).Cells(f, 2) = ans(1)
　DTH(7).Cells(f, 3) = ans(2)
Loop

変数fをIntegerで宣言し、数値として使用します。fに11を代入して集計表の書き込み開始位置とします。
Do While…Loopを使って集計表の書き込み位置と、後述する集計月のカウントを行っています。
hには集計開始月、kには集計末月が入り、hに1を足していってkと同じになるまでLoopします。
h <= kとしていますが、h = kでもOKです。

集計関数

With Application.WorksheetFunction

　Dim ans(1 To 2) As Long
　ans(1) = .SumIfs(DTH(3), DTH(2), h, DTH(5), str1, DTH(6), str2)
　ans(2) = .SumIfs(DTH(4), DTH(2), h, DTH(5), str1, DTH(6), str2)

End With

With句

With句を使ってApplication.WorksheetFunction .SumIfsを省略しています。

Application.WorksheetFunction .SumIfs

集計にはFor…NextとIf文を使って抽出もできますが、今回はアプリケーション関数を使用して集計値を計算します。どちらの手法でも結構です。
ans(1) = .SumIfs(DTH(3), DTH(2), h, DTH(5), str1, DTH(6), str2)
ans(2) = .SumIfs(DTH(4), DTH(2), h, DTH(5), str1, DTH(6), str2)
上記２行の違いは、DTH(3)は数量、DTH(4)は金額です。以下の「DTH(2),h, DTH(5), str1, DTH(6), str2」の句は、DTH(2)は仕入月、hは抽出月、DTH(5)は仕入先str1は抽出したい仕入先名称の一部、DTH(6)は商品名、str2は抽出したい商品名称の一部です。

DTH(7).Cells(f, 2) = ans(1)
DTH(7).Cells(f, 3) = ans(2)

変数に代入した数量と金額を集計表に、連続的に書き込んでいきます。

DTH(7).Cells(10, 2) = “仕入数量”
DTH(7).Cells(10, 3) = “仕入金額”

B10セルとC10セルに集計表の項目を書き込みします。

翌年の1月作成

　h = h + 1
　f = f + 1

　If Right(h, 2) > 12 Then ’12月の次は翌年の1月作成
　　h = h + 88
　End If

ここでは日付関数を使わずに足し算で月を取得しています。設定した開始月(h)から１ずつ加算して、(h)の右２桁（月）を取得して12（１２月）を超えた13で88を足すと101となり、年数を加算することができます。例　(202212⇒202301)

後処理

ワークシート「集計」でA列に前のデータが残っていたら消去します。

グラフの作成

‘グラフタイトルの作成
With ActiveSheet.ChartObjects(1).Chart.ChartTitle
　.Text = “仕入集計 ” & DTH(7).Cells(4, 3) & “-” & DTH(7).Cells(5, 3) & ” ” & DTH(7).Cells(6, 3) & ” ” & DTH(7).Cells(7, 3)
　.Font.Size = 14 ‘文字サイズ
End With
‘グラフの作成
On Error GoTo jump2
　ActiveSheet.ChartObjects(1).Delete‘前のグラフを消去
jump2:

ActiveSheet.ChartObjects(1).Delete　で現在表示されているグラフを削除します。
初回グラフの無い状態ではエラーが発生します。
そのためOn Error GoTo jump2を設定しjump2:まで処理をジャンプします。

Range(“A10”).Select ‘集計データを選択
ActiveSheet.Shapes.AddChart2.Select ‘グラフを挿入
ActiveChart.ChartType = xlLine ‘「折れ線」

Range(“A10”).Select 　で元データを選択、A10セルから連続したデータを範囲取得してくれます。

ActiveChart.ChartType = xlLine 　で一列目のデータに「折れ線」グラフを設定します。

With ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)
.AxisGroup = 2 ‘第2軸にする
.ChartType = xlColumnClustered ‘barに変更する
End With
With ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)
　.AxisGroup = 2 ‘第2軸にする
　.ChartType = xlColumnClustered ‘barに変更する
End With

With句を使って二列目を棒グラフに設定しています。

‘グラフタイトルの作成
With ActiveSheet.ChartObjects(1).Chart.ChartTitle
　.Text = “仕入集計 ” & DTH(7).Cells(4, 3) & “-” & DTH(7).Cells(5, 3) & ” ” & DTH(7).Cells(6, 3) & ” ” & DTH(7).Cells(7, 3)
　.Font.Size = 14 ‘文字サイズ
End With

C列の条件文字を使ってグラフタイトルを作成しています。.Font.Sizeフォントサイズを指定します。

Range(“B3”).Select

アクティブセルをB3セルに戻して終了です。

グラフ作成のmoduleはマクロの記録を使用して、必要な項目を変更することが良いと思います。

以上で仕入管理は終了です。
システムの構造は入力フォーム、マスタテーブル、実績記録用のテーブル、注文書などの帳票作成、集計とグラフです。
これらの仕組みは仕入管理に限らず応用が可能です。工夫してみてください。
わかりにくいところがあればお問い合わせください。
できる限り回答申し上げます。

この後の予定は経費申請システムです。基本構造は同じです。どのように応用していくか、ご確認ください。