最終回は、店舗別のシートに記録された日々の売上金額を品目別に集計して、「月間店別売上集計」シート上に集計金額を表示します。
- 月間の店別品目別売上金額を集計し、シートに集計金額を表示する
- ① 集計用変数の再宣言
- ② 店舗別シートの処理を繰り返す
- ③ 店舗別シートのデータ件数を取得する
- ④ 売上データがある場合に処理を行う
- ⑤ 売上データを日付順に並べ替える
- ⑥ 売上データの対象月先頭行と最終行を取得する
- ⑦ 売上金額を品目別に集計する 「SumIf」関数
- ⑧ 売上金額の集計結果を集計表に表示する
- ⑨ 処理が終了しました。
月間の店別品目別売上金額を集計し、シートに集計金額を表示する
まず、店舗別のシートに記録された日々の売上金額を品目別に集計します。それから、前回「月間店別売上集計」シート上に 作成した、店別品目別売上集計表の各セルに集計金額を格納します。この部分のプログラムソースを下記に示します。
'各店舗の売上データを処理する
wGokeiKingaku = 0
ReDim wKingaku(wHinmokuSu, wTenpoSu)
ReDim wTenpoKingaku(wTenpoSu)
ReDim wHinmokuKingaku(wHinmokuSu)
For wCnt = 1 To wTenpoSu
With Worksheets(wTenpoMei(wCnt))
'売上データ数を取得
wDataSu = 0
wCnt2 = 4
Do Until Len(.Cells(wCnt2, 1)) = 0
wDataSu = wDataSu + 1
wCnt2 = wCnt2 + 1
Loop
'データがある場合に処理
If wDataSu > 0 Then
'データを日付順にソートする
.Range("A5:C" & Format(wDataSu + 3, "#")).Sort Key1:=.Range("A4"), _
Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, SortMethod&:=xlPinYin
'対象月の先頭行と最終行を求める
wSetSento = 0
wSentoGyo = 0
wSaishuGyo = 0
For wCnt2 = 4 To wDataSu + 3
If wSetSento = 0 Then
If .Cells(wCnt2, 1) >= wHiduke_S Then
wSentoGyo = wCnt2
wSetSento = 1
End If
End If
If .Cells(wCnt2, 1) >= wHiduke_S And .Cells(wCnt2, 1) <= wHiduke_E Then
wSaishuGyo = wCnt2
ElseIf .Cells(wCnt2, 1) > wHiduke_E Then
Exit For
End If
Next wCnt2
If wSentoGyo > 0 Then
'対象月の売上データがある場合、品目別に集計する
For wCnt2 = 1 To wHinmokuSu
wKingaku(wCnt2, wCnt) = Application.WorksheetFunction.SumIf( _
.Range("B" & Format(wSentoGyo, "#") & ":B" & Format(wSaishuGyo, "#")), _
wHinmokuCd(wCnt2), _
.Range("H" & Format(wSentoGyo, "#") & ":H" & Format(wSaishuGyo, "#")))
wHinmokuKingaku(wCnt2) = wHinmokuKingaku(wCnt2) + wKingaku(wCnt2, wCnt)
wTenpoKingaku(wCnt) = wTenpoKingaku(wCnt) + wKingaku(wCnt2, wCnt)
wGokeiKingaku = wGokeiKingaku + wKingaku(wCnt2, wCnt)
Next wCnt2
End If
End If
End With
Next wCnt
'集計金額を「月間店別売上集計」シートにセットする
For wCnt = 1 To wHinmokuSu
For wCnt2 = 1 To wTenpoSu
Worksheets("月間店別売上集計").Cells(wCnt + 3, wCnt2 + 2) = wKingaku(wCnt, wCnt2)
Next wCnt2
Next wCnt
'品目別合計金額をセット
For wCnt = 1 To wHinmokuSu
Worksheets("月間店別売上集計").Cells(wCnt + 3, wTenpoSu + 3) = wHinmokuKingaku(wCnt)
Next wCnt
'店舗別合計金額をセット
For wCnt = 1 To wTenpoSu
Worksheets("月間店別売上集計").Cells(wHinmokuSu + 4, wCnt + 2) = wTenpoKingaku(wCnt)
Next wCnt
'全社合計金額
Worksheets("月間店別売上集計").Cells(wHinmokuSu + 4, wTenpoSu + 3) = wGokeiKingaku
以下、プログラムを詳細に見ていきましょう。
① 集計用変数の再宣言
'各店舗の売上データを処理する
wGokeiKingaku = 0
ReDim wKingaku(wHinmokuSu, wTenpoSu)
ReDim wTenpoKingaku(wTenpoSu)
ReDim wHinmokuKingaku(wHinmokuSu)
「マスター」シートから取得した店舗数と品目数を元にして、店別品目別の売上金額集計用
の配列を再宣言します。再宣言の命令「Redim」については、第７回で説明しました。
② 店舗別シートの処理を繰り返す
For wCnt = 1 To wTenpoSu
With Worksheets(wTenpoMei(wCnt))
・・・・・
End With
Next wCnt
第７回で説明した「処理の繰り返し その２」によって、店舗別のシートを順次処理して行きます。
③ 店舗別シートのデータ件数を取得する
'売上データ数を取得
wDataSu = 0
wCnt2 = 4
Do Until Len(.Cells(wCnt2, 1)) = 0
wDataSu = wDataSu + 1
wCnt2 = wCnt2 + 1
Loop
ここからは、各店舗別シートでの処理になります。シートの4行目から売上データが記録されていますが、
1列目(A列)には、売上日が記録されています。これを空のセルまで順次読み込んで、データ件数をカウントします。下図を参照してください。
この処理には、「処理の繰り返し その１」を用います。
④ 売上データがある場合に処理を行う
'データがある場合に処理
If wDataSu > 0 Then
・・・・・
End If
売上データが1件もない場合は、処理を行いません。
⑤ 売上データを日付順に並べ替える
'データを日付順にソートする
.Range("A5:C" & Format(wDataSu + 3, "#")).Sort Key1:=.Range("A4"), _
Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, SortMethod&:=xlPinYin
データを格納しているセルの並べ替えを「ソート」といいます。
並べ替えの命令は、第７回の「セルの範囲を指定して、並べ替える」
で説明しました。
⑥ 売上データの対象月先頭行と最終行を取得する
'対象月の先頭行と最終行を求める
wSetSento = 0
wSentoGyo = 0
wSaishuGyo = 0
For wCnt2 = 4 To wDataSu + 3
If wSetSento = 0 Then
If .Cells(wCnt2, 1) >= wHiduke_S Then
wSentoGyo = wCnt2
wSetSento = 1
End If
End If
If .Cells(wCnt2, 1) >= wHiduke_S And .Cells(wCnt2, 1) <= wHiduke_E Then
wSaishuGyo = wCnt2
ElseIf .Cells(wCnt2, 1) > wHiduke_E Then
Exit For
End If
Next wCnt2
売上データが日付順に並べ替えられたので、次の手順で対象月の先頭行と最終行の位置を取得します。
売上データは、4行目から(データ件数 + 3)行目までです。
●売上の日付が指定月の初日(wHiduke_S)以降になった最初の行の値を先頭行(wSentoGyo)
に格納します。
●売上の日付が指定月の初日(wHiduke_S)と末日(wHiduke_E)の間にある場合、行の値を最終
行(wSaishuGyo)に格納し、末日を越えた時に、処理の繰り返しを脱します。
⑦ 売上金額を品目別に集計する 「SumIf」関数
If wSentoGyo > 0 Then
'対象月の売上データがある場合、品目別に集計する
For wCnt2 = 1 To wHinmokuSu
wKingaku(wCnt2, wCnt) = Application.WorksheetFunction.SumIf( _
.Range("B" & Format(wSentoGyo, "#") & ":B" & Format(wSaishuGyo, "#")), _
wHinmokuCd(wCnt2), _
.Range("H" & Format(wSentoGyo, "#") & ":H" & Format(wSaishuGyo, "#")))
wHinmokuKingaku(wCnt2) = wHinmokuKingaku(wCnt2) + wKingaku(wCnt2, wCnt)
wTenpoKingaku(wCnt) = wTenpoKingaku(wCnt) + wKingaku(wCnt2, wCnt)
wGokeiKingaku = wGokeiKingaku + wKingaku(wCnt2, wCnt)
Next wCnt2
End If
品目の数だけ、処理を繰り返します。
●「SumIf」関数を用いて、売上金額を集計します。
●集計金額をさらに品目別金額合計の配列wHinmokuKingaku()、店舗別金額合計の配列
wTenpoKingaku()、総売上金額の変数wGokeiKingakuにそれぞれ加算します。
構文：
Application.WorksheetFunction.SumIf( 集計キーのセル範囲,
集計キーの値,
集計値のセル範囲 )
第1引数「集計キーのセル範囲」 集計キーの検索セル範囲を指定します。
第2引数「集計キーの値」 検索する集計キーの値をセットします。
第3引数「集計値のセル範囲」 集計対象となる値のセル範囲を指定します。
例：
下図を参照してください。
⑧ 売上金額の集計結果を集計表に表示する
'集計金額を「月間店別売上集計」シートにセットする
For wCnt = 1 To wHinmokuSu
For wCnt2 = 1 To wTenpoSu
Worksheets("月間店別売上集計").Cells(wCnt + 3, wCnt2 + 2) = wKingaku(wCnt, wCnt2)
Next wCnt2
Next wCnt
'品目別合計金額をセット
For wCnt = 1 To wHinmokuSu
Worksheets("月間店別売上集計").Cells(wCnt + 3, wTenpoSu + 3) = wHinmokuKingaku(wCnt)
Next wCnt
'店舗別合計金額をセット
For wCnt = 1 To wTenpoSu
Worksheets("月間店別売上集計").Cells(wHinmokuSu + 4, wCnt + 2) = wTenpoKingaku(wCnt)
Next wCnt
'全社合計金額
Worksheets("月間店別売上集計").Cells(wHinmokuSu + 4, wTenpoSu + 3) = wGokeiKingaku
売上金額の集計結果の各値を、処理の繰り返しを用いて「月間店別売上集計」シート上のセルに格納します。
下図を参照してください。
⑨ 処理が終了しました
これで、一連の処理が終了しました。Excelファイルを保存するときは、別名で保存します。
● 店舗が増えたときは、「マスター」シートの店舗マスターに店舗名を追加し、新しい店舗の
売上データシートを追加します。
● 品目が増えたときは、「マスター」シートの品目マスターに品目コードと品目名を追加する
だけです。
ExcelVBA講座は、今回でひとまず終了です。
ExcelVBA講座Ⅱを予定していますので、お楽しみに！！！