はじめに

この記事では、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]セルなどをアクティブにしてから、ペースターのキーストロークの発行タグを使用すると、今日の日付、ギフト券番号、金額を一発で入力できます。

ちなみに、ペースターのカスタムメニューには以下のように記述しています。

MenuItem.txt

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以降では普通にできるのでしょうか？

それでは、この辺で失礼します。