はじめに
この記事では、Excelを使用して日々の売り上げを月ごとに自動集計するシートを作成してみたいと思います。
基本的には、SUMIF関数（作業列が必要）やピボットテーブルで実現できますが、VBAの勉強のため、自作の関数を作成してみます。
Excelのバージョン
使用しているExcelのバージョンは「Excel 2013」になります。
画面イメージ
以下のようなシートを作成しました。
ちなみに、左側の売り上げテーブルには「Amazon」という名前を付けています。
SUMIF関数を使用した場合の関数
まず、売り上げテーブルに「（月）」という作業列を追加して、日付カラムのデータから何月のデータなのかを取得しています。[E5]セルには以下のような式を入力します。
=MONTH(B5)
そして、SUMIF関数を使用している[H5]セルには以下のような式を入力します。
=SUMIF(Amazon[(月)],[@月別],Amazon[金額])
これで、売り上げテーブルにデータが追加される度に、集計テーブル（左側）が自動的に更新されていきます。
この方法で特に問題はありませんが、１つ気になるのが作業列として「（月）」というカラムを追加しなければいけない、という点です。
それを解決するために以下のような関数を自作してみます。
自作の関数を使用した場合
VBAエディタを起動して、以下のような関数を作成します。
Option Explicit
Public Function AggMonthly(dummy As Object, MonthVal As Integer) As Integer
Dim nTotal As Integer
Dim row As ListRow
Dim tbl As ListObject
Dim dtTemp As Date
' dummyはAmazonテーブルが編集された時に、この関数が呼ばれるための仕掛け
' Application.Volatileの使用は避けたい
Set tbl = ActiveSheet.ListObjects("Amazon")
For Each row In tbl.ListRows
dtTemp = row.Range(1)
' 対象となる月のデータを集計していく
If Month(dtTemp) = MonthVal Then
nTotal = nTotal + row.Range(3)
End If
' 時系列に並んでいることが分かっている場合には最適化する
' そうでない場合には、以下の３行はコメントアウトする
If Month(dtTemp) > MonthVal Then
Exit For
End If
Next row
AggMonthly = nTotal
End Function
そして、[K5]セルには以下のような式を入力します。
=AggMonthly(Amazon,[@月別])
これで、売り上げテーブルにデータが追加される度に、集計テーブル（中央）が自動的に更新されていきます。
コードの解説
AggMonthly関数の中では、Set tbl = ActiveSheet.ListObjects("Amazon")という式で売り上げテーブルを取得していますが、引数でもdummy As Objectという記述をしています。
これには２つの理由があります。
- 関数の引数にListObject型が指定できなかった。何故かRange型になってしまいます。
- 関数の引数として売り上げテーブルを渡していないと、テーブルが更新された時に、AggMonthly関数がコールされない。
このような理由により、ダミーの引数を渡しています。
これで、売り上げテーブルが追加される度に、集計テーブル（右側）が自動的に更新されていきます。
ピボットテーブルを使用した場合
ピボットテーブルを使えば、この手の集計は簡単に行えます。
ただし、売り上げテーブルにデータを追加してもリアルタイムでピボットテーブルが更新されません。
意図的に「ピボットテーブルの更新」コマンド（Alt+F5）を実行する必要があるようです。これはちょっと面倒です。
（もしかしてExcel2013だから？）
補足
日々の作業では、「今日の日付」、「ギフト券番号」、「金額」の３つを入力していきますが、私の場合にはペースターというツールを使用して半自動的に入力しています。
例えば、ギフト券取得のメールを受信すると、メールの本文に記載されている「金額」と「ギフト券番号」を連続してコピーしてしまいます。
これで、ペースターのクリップボード履歴にセットされます。
あとは、[B12]セルなどをアクティブにしてから、ペースターのキーストロークの発行タグを使用すると、今日の日付、ギフト券番号、金額を一発で入力できます。
ちなみに、ペースターのカスタムメニューには以下のように記述しています。
Amazonギフト券の集計（ペースター）\tAlt+Ctrl+F9|
[Y1]/[T2]/[D2][!K "TAB"][C1][!K "TAB"][C2]
/E
まず、今日の日付を貼り付けて、[TAB]キーを押して、履歴の１番を貼り付けて、[TAB]キーを押して、履歴の２番を貼り付ける、、、という処理が行われます。
見て頂けると分かると思いますが、このコマンドにはショートカットキーが割り当ててありますので、メニューを表示する必要もありません。
操作イメージを動画にしてみましたので、参考にしてみて下さい。
YouTubeの動画（約30秒）
終わりに
今回のタスクを作成している時に困ったのが以下の点です。
AggMonthly関数の引数として渡している売り上げテーブルを、関数の方でListObject型として直接受け取れないということです。
もし、以下のように記述できたらもっとスマートなのになぁ・・・と思いました。
Function AggMonthly(tbl As ListObject, MonthVal As Integer) As Integer
もしかしたら、Excel2016以降では普通にできるのでしょうか？
それでは、この辺で失礼します。