サイト内検索

別シート 転記する

VBAで売上データから日付別と店員別商品別にクロス集計をする

VBAで売上データから2つのクロス集計表を作成します。1つ目のクロス集計表は、各店員別に各商品の販売数量と売上が分析できる集計表です。2つ目のクロス集計表は、当月の日別売上高と日別値引額を分析できる集計表です。

まずは、本記事で使用するサンプルファイルの紹介と、VBAコードを実行している1分動画(音声無し)を用意しましたので、宜しければご覧ください。

本記事でご紹介するサンプルファイルとVBAサンプルコードの実演

売上レコードと2つのクロス集計表からなるサンプルファイル

VBAサンプルコードの実行結果を確認するためのダウンロード用ファイルのご紹介です。実際にサンプルファイルをダウンロードしたあとに、標準モジュールを挿入してから、後ほどご紹介するVBAサンプルコードを転記してご利用ください。

VBAサンプルコードの実行結果を実際のExcelシート上で確認できます。

Tips
標準モジュールの挿入方法

ダウンロード用サンプルファイル

実際にVBAサンプルコードを標準モジュールに転記してから、実行させて確認できるダウンロード用ファイルです。

サンプルファイルの各シートについて

サンプルファイルを構成しているシートについて解説します。

「売上データ」シート

「売上データ」シートには、集計元になる対象レコードが入力されています。項目は、「伝票番号」、「日付」、「販売担当者」、「商品①」~「商品⑤」までの各「品名」と「個数」、「値引額」です。

「売上データ」シート
「売上データ」シート

1伝票ごとに一人の店員について、最大5個の商品の販売記録を入力可能です。また1伝票は1回の売買に相当しており、最終列には1回の取引における「値引額」を入力することができます。

「商品別店員別売上詳細」シート

「商品別店員別売上詳細」シートには、5人の販売店員別に各商品を何個売上たかを分析できるクロス集計表が入力されています。さらに、各店員がそれぞれいくら値引したかも集計しています。

店員別商品別販売数量と売上高、値引額が分析できるクロス集計表
店員別商品別販売数量と売上高、値引額が分析できるクロス集計表

また、このクロス集計表は、商品の一覧も兼ねており、その商品名の横には売価が入力されています。

「日付別売上表」シート

「日付別売上表」シートには、ひと月分の日別売上高を、値引額を内訳として集計でいるクロス集計表です。項目は、「商品売上」(値引額除く)、「値引額」、「売上合計」(値引き込み)です。

ひと月分の日別売上高を、値引額を内訳として集計できるクロス集計表
ひと月分の日別売上高を、値引額を内訳として集計できるクロス集計表

そして最後の行には、それぞれの項目を月の合計として算出して表示します。

売上レコードを2つのクロス集計表へ集計させるVBAサンプルコード

「売上データ」シートにある集計元レコードのデータを読み取り、2つのクロス集計表へ同時に集計させるVBAサンプルコードをご紹介します。

 

VBAサンプルコードの構成は、メインプログラムを含む5つのサブプロシージャと1つのファンクションプロシージャです。

VBAサンプルコードの実行結果

VBAサンプルコードを、サンプルファイルの標準モジュールに転記することで、実際にExcelシート上でVBAサンプルコードを実行して結果を確かめることができます。

ここでは、VBAサンプルコードの実行結果について解説します。

「商品別店員別売上詳細」シートの集計結果

5人の店員ごとに各商品別で販売数量と売上の集計結果を表示できるクロス集計表の集計結果です。

VBA実行後の店員別商品別の販売数量と売上集計結果
VBA実行後の店員別商品別の販売数量と売上集計結果

例えば、佐藤さんはお店の中で一番高いスイカを、このひと月の間に81個も売っていることが分かります。その売上高は243,000円で、スイカの単価3,000円×81と一致していますよね。

別の見方として、例えばこのひと月の間に一番値引を行ったのは、鈴木さんの1,710円だったことがExcelシートの20行目で確認できます。

合計欄については、各担当ごとのひと月の総販売個数と総売上額がExcelシートの18行目で確認できます。また、商品ごとの総販売個数と総売上額は、Excelシート13, 14列目で確認できます。

今回の売上データは、乱数で作成しましたので各商品、各店員ごとにそれほど開きが無く面白みのないデータですが、実際のデータを分析してみるといろいろと問題が洗い出されて、売上改善策を立てるのに役立てそうですよね。

「日付別売上表」シートの集計結果

日付ごとの値引考慮なしの商品売上額(Excelシート2列目)、値引額(Excelシート3列目)、値引額を差し引いた売上合計(Excelシート4列目)を確認できるクロス集計表のVBAコード実行結果です。

VBA実行後の日付別売上集計結果
VBA実行後の日付別売上集計結果

最終行では、前述の3項目の総合計を算出表示しています。

2つのクロス集計表の集計結果を比較

以上、確認してきました2つのクロス集計表は、元集計データが同じですので集計結果の切り口は違いますが、合計は当然合わなくてはいけません。そこで、これら2つのクロス集計表のVBA実行結果を比較確認してみました(下図)。

2つのクロス集計表のVBA実行結果を比較
2つのクロス集計表のVBA実行結果を比較

まず、図の赤枠で囲った合計値ですが、それぞれ割引総額を除いた売上額です。また青枠はそれぞれ値引総額であり、緑枠はそれぞれ割引額を引いた売上総額になります。

3つの項目の値を比較してみますとすべて一致しているのが確認できますよね。

VBAサンプルコードのアルゴリズム解説

これよりVBAサンプルコードのアルゴリズムについて解説していきます。

シートオブジェクトのCodeNameプロパティ

今回のVBAサンプルコードでは、ワークシートオブジェクトをCodeNameプロパティで扱います。

各サンプルシートのCodeNameプロパティ
各サンプルシートのCodeNameプロパティ

図の上から順に、「日付別売上表」シートのCodeNameプロパティを「Daily」、「売上データ」シートのCodeNameプロパティを「Data」、および「商品別店員別売上詳細」シートのCodeNameプロパティをFruitsとしました。

Tips
複数シートをオブジェクト名(CodeNameプロパティ)で扱う方法

モジュール変数宣言部

2行目:Dim rngFrut As Range
「商品別店員別売上詳細」シートにある商品リスト範囲をセットするためのRangeオブジェクト変数です。このrngFrutは、複数のプロシージャで使いますので、モジュール変数として宣言しました。

「売上集計」サブプロシージャ(メインプログラム)

メインプログラム「売上集計」サブプロシージャの解説になりますが、結構長いので適当に区切りながら解説しています。

プロシージャ変数宣言部

8行目:Dim curSale As Currency
集計元レコード1行分(伝票単位)の売上額を保持する変数です。データ型は通貨型を使用しています。

Tips
通貨型を含むVBA集計業務のデータ型

9行目:Dim datDate As Date
集計元レコードの該当する伝票日付を保持する変数です。データ型は日付型を指定しています。

10行目:Dim strName As String
集計元レコードは、1レコードごとに日付・担当者が分かれています。変数「strName」は、その処理中レコードの店員の名前を保持する変数です。

11行目:Dim curVgen As Currency
集計元レコードの処理中レコードの値引額を保持する変数です。金額を取り扱うのでデータ型を通貨型(Currency)にしています。

13行目:Dim strFrut As String
処理中の商品名を保持する変数です。

14行目:Dim intQty As Integer
処理中の販売個数を保持する変数です。

集計先データ範囲初期化(クリア)処理

VBAコード21~27行目は、下図の通り2つのクロス集計表の集計先範囲を初期化(クリア)しています。VBAコード21~23行目で、日付別売上表の集計先データ範囲を、VBAコード25~27行目で商品別店員別売上詳細シートのデータ範囲をクリアしています。

2つのクロス集計表の集計先範囲をクリア
2つのクロス集計表の集計先範囲をクリア

そしてクリア処理した後の、状態が下図です。

集計先範囲をクリア処理した後の様子
集計先範囲をクリア処理した後の様子

また商品別店員別売上詳細シートのCells(21, 14)には、下図で示すように数式が入力されていますので、VBAコードでクリア処理する際の対象外にしています。

数式が入力されているセル
数式が入力されているセル

ちなみにこの数式が入力されているセルも、VBAコードでクリア処理して数式を入力することも可能ですが、今回はそうせずに常に数式が入力された状態としています。

商品リスト範囲セット

31行目:Set rngFrut = .Range(.Cells(3, 1), .Cells(17, 1))
商品別店員別売上詳細シートのCells(3, 1)からCells(17, 1)までを、商品リストとしてRangeオブジェクト変数「rngFrut」にセットしています。

商品リストをオブジェクト変数「rngFrut」にセット
商品リストをオブジェクト変数「rngFrut」にセット

Tips
VBAコードのWithの使い方

集計元データの読み取りと集計先クロス集計表への転記処理

これより実際に「売上データ」シート上に入力されている売上レコードを1つ1つ繰り返し処理し、集計先となる2つのクロス集計表へ転記するVBAコードを解説していきます。

35行目:lngERow = .Range("A" & .Rows.Count).End(xlUp).Row
「売上データ」シートの最終レコードが入力されているExcel行番号を求めています。

Tips
VBAで最終レコードの行番号を求める方法

集計元データ繰り返し処理部

VBAコード37~57行目では、For ~ Next文で「売上データ」シートにある集計元レコードの1つ1つを繰り返し処理しています。

38行目:curSale = 0
通貨型変数「curSale」は、集計元レコード1行ごと(1会計伝票ごと)の売上合計を保持する変数です。そのため各行処理前に、毎回保持している値をこのVBAコードでリセットしています。

VBAコード40~42行目は、処理中レコード行(r行)の「日付」(赤)、「販売担当者」(青)、「値引き」(緑)の値を、それぞれの変数に代入している処理です。

処理中レコード行 r の「日付」、「販売担当者」、「値引き」を各変数に代入
処理中レコード行 r の「日付」、「販売担当者」、「値引き」を各変数に代入
1伝票中の売り上げた複数の商品に対する処理

VBAコード45~54行目は、「売上データ」の商品①~商品⑤までのそれぞれの「品名」と「個数」(販売数量)に対する処理を、For ~ Next文で繰り返し処理しています。

VBAコード45~54行目のFor ~ Next文による商品①~商品⑤までの「品名」と「個数」に対する処理
VBAコード45~54行目のFor ~ Next文による商品①~商品⑤までの「品名」と「個数」に対する処理

46行目:If .Cells(r, c) <> "" Then      
ここで「品名」が入力されているセルを調べ、品名が入力されていれば、VBAコード47行目以降を実行するようにしています。 

Tips
VBAコードのIf文の使い方

47行目:strFrut = .Cells(r, c)
48行目:intQty = .Cells(r, c + 1)

商品①~商品⑤の各「品名」と「個数」(販売個数)を、それぞれ保持する変数に代入しています。

商品①~商品⑤の各「品名」と「個数」を変数へ代入
商品①~商品⑤の各「品名」と「個数」を変数へ代入

50行目:curSale = curSale + 果物売上(strFrut, intQty)
対象レコードの売上額を集計するVBAコードです。ここで記載されている「果物売上」は、あとで詳述する「果物売上」ファンクションプロシージャです。このファンクションプロシージャは、引数として商品名と販売個数を渡せば、売上金額が戻ってくる関数です。

果物売上ファンクションプロシージャにより戻ってきた売上金額を集計
果物売上ファンクションプロシージャにより戻ってきた売上金額を集計

VBAコード47、48行目で品名(青字)と個数(緑字)を保持した変数「strFrut」と「intQty」を、「果物売上」ファンクションプロシージャに渡し、戻り値として得た売上額を加算集計しています。

52行目:Call 商品別店員別売上表へ計上(strName, strFrut, intQty, curVgen, c)
こちらもあとで詳述しますが、サブプロシージャ「商品別店員別売上表へ計上」を呼び出し、商品別店員別売上詳細シートへ商品①~商品⑤の情報を計算して転記しています。

先に処理のイメージを図にしてみましたのでご参考ください。

「商品別店員別売上表へ計上」サブプロシージャの処理イメージ(伝票番号0001終了時)
「商品別店員別売上表へ計上」サブプロシージャの処理イメージ(伝票番号0001終了時)

処理内容は、売上データシート上の商品①~商品⑤の各「品名」、「個数」の情報をもとに、商品別店員別売上詳細シート上の該当する担当者と商品リストの行に、販売数量と計算した売上金額を転記します。

56行目:Call 日付別売上表へ計上(datDate, curSale, curVgen)
集計元データの各レコード繰り返し処理の最後の処理になります。こちらもあとで詳述する「日付別売上表へ計上」サブプロシージャを呼び出し処理しています。

VBAコード56行目は、列方向繰り返し処理(青網掛け部)を終えた直後の処理
VBAコード56行目は、列方向繰り返し処理(青網掛け部)を終えた直後の処理

処理概要は、下図のように引数として日付、1レコード分の売上合計、値引額を渡すと、該当する日付の行に売上と割引を転記し、内部で売上合計(値引額除く)を計算します。

日付別売上集計表への転記処理イメージ
日付別売上集計表への転記処理イメージ

62行目:Call 商品別店員別売上表合計処理
63行目:Call 日付別売上表合計処理

2つのクロス集計表の合計処理になります。詳述はのちほど記載しておりますが、VBAコード62行目前までの処理状況は下図の通り合計欄はまだ空欄です。

合計処理前の状況
合計処理前の状況

上図の状況のあと、VBAコード62, 63行目が実行されることにより、合計欄が集計されます。

「商品別店員別売上表へ計上」サブプロシージャ

売上データシート上の各レコードのデータを読み取った値を受取り、商品別店員別売上詳細シートへ集計させるサブプロシージャです。

69行目:Private Sub 商品別店員別売上表へ計上(ByVal vName As String, ByVal vFrut As String, ByVal vQty As Integer, ByVal vVgen As Currency, ByVal vCol As Integer)
サブプロシージャの宣言部になります。引数は、以下の通り5つあります。

  • 「店員名」
  • 「商品名」
  • 「販売個数」
  • 「値引額」、
  • 処理対象レコードの商品①~商品⑤に対する処理中の列番号

70行目:Dim rngTrgt As Range
75行目:Set rngTrgt = rngFrut.Find(vFrut, lookat:=xlWhole)

商品リストの中の商品と、引数で受け取った「商品名」が一致した時に、そのセルをRangeオブジェクトとして保持するオブジェクト変数をVBAコード70行目で宣言しています。

実際の変数セットは、VBAコード75行目で実行しており、商品リスト範囲(rngRfut)からFindメソッドで引数「vFrut」と一致するセルをrngTrgtにセットしています。

VBAコード79~84行目は、75行目でrngTrgtオブジェクト変数にセットされた中身をチェックして、その結果で処理を分けています。

引数で受け取った商品名が、商品リストの中に見つかったかどうかをチェック
引数で受け取った商品名が、商品リストの中に見つかったかどうかをチェック

上図赤の部分は、商品リストの中に引数で受け取った商品名が無かった場合の条件文です。rngTrgt Is Nothingは、rngTrgtオブジェクト変数の中身が無い、すなわちVBAコード75行目の処理結果が何も得られなかったことを表しています。

VBAコード79行目の条件に合致しない場合、すなわちVBAコード75行目の処理の結果、rngTrgtに値がセットされた場合は、VBAコード83行目(下図青字)が実行されます。

VBA83行目でデータ書込み行intWRowが決まる
VBA83行目でデータ書込み行intWRow(=青枠数字)が決まる

VBAコード87~93行目は、商品別店員別売上表のデータ書込列を判定しています。引数vNameは担当者名であり、そのvNameをSelect文の条件判定に用いることにより、担当者ごとにデータが振り分けられる仕組みです。

担当者名別にクロス集計表への書込み列が決まる
担当者名別にクロス集計表への書込み列が決まる

Tips
VBAコードのSelect分岐構文の基本的な使い方

96行目: .Cells(intWRow, intWCol) = .Cells(intWRow, intWCol) + vQty
書込み行(intWRow)と書込み列(intWCol)が決まりましたので、ここでは該当する担当者別に、販売数量を集計する処理をしています。イメージとしては、以下の図のような感じになります。

各担当者別の販売数量の列に、該当する商品の行へ加算集計する
各担当者別の販売数量の列に、該当する商品の行へ加算集計する

97行目:.Cells(intWRow, intWCol + 1) = .Cells(intWRow, intWCol + 1) + .Cells(intWRow, 2) * vQty
各担当者ごとの商品別売上高を加算集計するVBAコードです。下図の青文字は、各商品の売価が入力されている範囲で、Excelシートの2列目にあります。これとVBAコード96行目でも利用した販売個数「vQty」を乗じることで、商品ごとの売価が計算できます。

各担当者ごとの商品別売上高を加算集計
各担当者ごとの商品別売上高を加算集計

その売価をこのVBAコードで加算集計しています。

VBAコード99~101行目は、商品別店員別売上表20行目の値引額を集計するコードです。メインプログラムで呼び出される際に、引数として渡された処理対象列を、VBAコード99行目のIf文で判定し、列番号が4行目の時だけ値引額を売上別店員別売上表に加算集計します。

「売上データ」シートの4列目の処理のとき、値引額を加算するアルゴリズム
「売上データ」シートの4列目の処理のとき、値引額を加算するアルゴリズム

ここで列番号が4の時にだけ割引額を加算集計している理由ですが、売上データを確認すると「割引額」は1レコードにつき1個ですよね。ところが、「商品別店員別売上表へ計上」プロシージャは、1レコードにつき商品①~商品⑤までの最大5回まで実行される可能性があります。

そうなると、VBAコード99行目のIf文が無ければVBAコード100行目によって、割引額の加算が最大5回まで加算集計されてしまいます。まさに重複計上を起こしてしまうのです。

そこで、考え方の1つとして、「1つのレコードには必ず商品①(処理列番号4)はあるはずだから、列番号4の処理の時だけ同時に値引額の集計もしてしまおう」と考えたわけです。

「果物売上」ファンクションプロシージャ

果物の種類と販売個数を引数として受け取り、売り上げを返すファンクションプロシージャの解説です。

108行目:Private Function 果物売上(ByVal strFrut As String, ByVal intQty As Integer) As Currency
引数strFrutは「商品名」、intQtyは「販売個数」です。戻り値のデータ型は、通貨型(Currency)としました。

VBAコード110行目は、既に解説しましたVBAコード79行目と同じ考え方になります。ここで引数の商品名が商品リストの中にない場合、If文の条件式がTrueとなり、つづくVBA111行目の処理が行われ、戻り値0を返します。

「果物売上」ファンクションプロシージャのアルゴリズム
「果物売上」ファンクションプロシージャのアルゴリズム

一方、引数で渡された商品名が商品リストの中にある場合、VBAコード113行目が実行され、戻り値として売上額(商品の値段×個数)が返されます。

「日付別売上表へ計上」サブプロシージャ

119行目:Private Sub 日付別売上表へ計上(ByVal vDate As Date, ByVal vSale As Currency, ByVal vVgen As Currency)
サブプロシージャの宣言部です。引数は、日付、1レコード分の売上合計、値引額です。

121行目:Dim rngTrgt As Range
124行目:Set rngDate = .Range(.Cells(2, 1), .Cells(32, 1))

VBAコード121行目は、日付別売上表シートの日付範囲をセットするためのオブジェクト変数です。VBA124行目でそのオブジェクト変数に、図の日付入力範囲をセットします。

日付表示範囲をrngDateオブジェクト変数にセット
日付表示範囲をrngDateオブジェクト変数にセット

VBAコード128~136行目は、前でセットした日付範囲の中に、引数で渡された日付が一致するか否かで処理を分けているIf構文になります。VBAコード128行目のIf文の条件式の考え方は、これまで出てきたVBAコード79行目と110行目と同じになります。

If文により日付範囲の中に引数で渡された日付があるかないかで処理を分ける
If文により日付範囲の中に引数で渡された日付があるかないかで処理を分ける

VBAコード132~134行目は、引数と一致した日付をセットしたrngTrgtからのOffsetプロパティによって、「売上額」(値引額控除前)(赤)、「値引額」(青)、「売上額」(値引額控除後)(緑)を加算集計しています。

引数で渡された日付に一致するセルからのOffsetプロパティで集計先のセルを指定する
引数で渡された日付に一致するセルからのOffsetプロパティで集計先のセルを指定する

Tips
RangeオブジェクトのOffsetプロパティの基本的な使い方

「商品別店員別売上表合計処理」サブプロシージャ

商品別店員別売上表の合計欄の集計処理を行うサブプロシージャです。

VBAコード143行目のFor文は、クロス集計表の行方向の繰り返し処理をします。VBAコード144行目のFor文は、クロス集計表の列方向の繰り返し処理をしますが、Step 2が記載されていますので1列置きに処理されます。

商品別に販売数量と売上の合計値を集計する
商品別に販売数量と売上の合計値を集計する

VBAコード145と146行目は、それぞれ商品別の販売数量と売上を加算集計しています。

VBAコード151~153行目は、列方向の繰り返し処理を用いてクロス集計表18行目の合計欄を集計しています。またVBAコード156行目は、青枠で囲った値引額総額を表示するセルを集計します。

店員別販売個数と売上合計集計と値引額総額集計
店員別販売個数と売上合計集計と値引額総額集計

見づらいですが、図の青網掛けで囲った範囲を指定したワークシート関数Sumを使い集計結果を算出しています。

「日付別売上表合計処理」サブプロシージャ

「日付別売上表」シートにあるクロス集計表の合計欄に、合計値を集計して表示します。

VBAコード163~165は、列方向の繰り返し処理を使い、クロス集計表33行目の3つのセル(下図赤枠)の合計値を集計します。

日別売上クロス集計表
日別売上クロス集計表

まとめ

集計元の売上データを2つのクロス集計表に集計させるVBAサンプルコードについてご紹介しました。1つ目のクロス集計表は、集計元の売上データから、店員別に商品ごとの販売個数と売上額を集計し、さらに各店員ごとの総割引額(1か月にいくら値引したのか)も集計したものでした。

この1つ目のクロス集計表により、例えばどの店員がどの商品を1番売上たか、どの商品が一番売り上げが悪かったのかなどの分析に使えそうです。また、あらかじめ店員ごとの割引額を設定しておいて、月末締めの集計時に割引総額がきちんと制限内に抑えられているかなどの管理にも使えそうです。

もう1つのクロス集計表は、日ごとの売上総額と割引額を集計したものでした。このクロス集計表を元に、横軸に日付、縦軸に金額(売上額と値引額の内訳)を設定した棒グラフなどで表示すると、日別や月別の売上推移グラフが簡単に作れそうです。

[スポンサーリンク]



サイト内検索

-別シート, 転記する
-

Copyright© 集計用VBAサンプルコードをやさしく学ぼう , 2020 All Rights Reserved.