tamago8610のブログ

日々の勉強を備忘録としてブログを書いています。

VBAでデータ集計を行う方法

※注意 この記事は、投稿者が勉強用に作成している備忘録のようなものです。 メソッドやプロパティ、変数に代入されている値の説明など省略されている箇所があります。

 

要約

下記、フォーマットのように、集計を行いたい月を選択して、取引先別で平均・合計売上金額を割り出したい場合の解説を行います。

 

 

f:id:tamago8610:20210519152334p:plain

(例)合計と平均を求めるフォーマット

月(選択)のセルには、あらかじめ入力規則で月を選択できるようリストボックスを作ってあります。

 

集計ボタンを押せば、担当ごとの売上金額が自動的に入力されるようにし、あわせて合計・平均欄も入力される仕組みです。

合計・平均欄には、それぞれSUM関数、AVERAGE関数を仕込んであります。

 

下記の写真のような100件くらいあるデータから集計を行うということにしましょう。

f:id:tamago8610:20210519152415p:plain

売上の元データ

 

集計を行う流れ

 該当月が選択されているかを確認する

選択されていなければ、入力を促すメッセージを表示。

選択されていれば、次の処理に進む。

取引先データを最後の行まで取得

担当者データを最後の列まで取得

元データから検索するために最後の行まで検索を行う

該当月で、取引先のデータ、担当者が一致すれば、売上金額を取得する

フォーマットに担当者・取引先別の売上金額を記述する

 

検索する該当月の日数を取得する

月によって、一カ月は何日あるか異なります。

また、1年は365日とは限らず、うるう年というものがあるので、毎年2月は28日まであるともいいきれません。

月の最後の日付を取得する場合、DateSerial関数を使うと便利です。

 

【構文】DateSerial(Year, Month, Day)

※引数Yearに年、Monthに月、Dayに日付を入力します。

 

(例)2017年5月1日を取得する

   DateSerial(2017,5,1)

 

日付を引数にそのまま入れるだけですね。

 

(例)2017年5月1日の前日(4月末日)

DateSerial(2017, 5 + 1 , 1) -1  →2017/04/30

 

これで、4月の末日を取得することができます。

用意する変数

※入力する手間を省くために、用意しておいた方がよい変数も含めます。

  •  元データシートを格納する変数 
  • 検索する日付の開始日を格納する変数
  • 検索する日付の終了日を格納する変数
  • 取引先の最終行を格納する変数
  • 担当の最終列を格納する変数
  • 元データの最終行を格納する変数
  • 取引先データを格納する変数
  • 担当者を格納する変数
  • 売上金額を格納する変数
  • ループ変数3つ

入力するコード

該当月が入力されているかどうか調べ、メッセージを表示させる説明は省略。

 

表示させるデータ件数が多ければ、画面のちらつきを防ぐために  Application.ScreenUpdating = False (画面の更新をしない)
Application.EnableEvents = False (イベントを一時的に無効)

を設定しておいた方が良いでしょう。

 

処理が終われば、ScreenUpdating、EnableEventsをTrueに戻すことを忘れないように。

また、再計算を手動にする設定

Application.Calculation = xlCalculationManual 

も記述しておいた方が、処理が早くなります。

 

処理が終われば再計算を自動に戻しましょう。

値をxlCalculationAutomaticにします。

 

Dim sDate1 As Date '月の始めの日付
Dim sDate2 As Date '月の末日
Dim i, k, j As Long 'i = 行を取得、k = 列を取得、j = データの行を取得
Dim maxRow As Long
Dim maxCol As Long
Dim maxData As Long '元データの最後の行
Dim dDate As Date '比較する日付データ
Dim torihiki As String '取引先データ
Dim tanto As String '担当データ
Dim goukei As Long '売上金額
Dim shtDat As Worksheet 'データシート

sDate1 = DateSerial(Range("C3").Value, Range("D3").Value, 1)
sDate2 = DateSerial(Range("C3").Value, Range("D3").Value + 1, 1) - 1
maxRow = Cells(Rows.Count, 2).End(xlUp).Row
maxCol = Range("XFD6").End(xlToLeft).Column
Set shtDat = Worksheets("データ")
maxData = shtDat.Cells(Rows.Count, 1).End(xlUp).Row

f:id:tamago8610:20210520182506p:plain

(例)データ集計を行っているコード

f:id:tamago8610:20210520183304p:plain

繰り返し処理の流れ

①取引先データを格納するために、始点のセルから最終行まで繰り返す。

②担当者の該当列番号を最後まで繰り返す。

③元データシートを最終行まで繰り返す。