Help us understand the problem. What is going on with this article?

Excelで日々の売り上げを月ごとに集計する方法

はじめに

この記事では、Excelを使用して日々の売り上げを月ごとに自動集計するシートを作成してみたいと思います。
基本的には、SUMIF関数(作業列が必要)やピボットテーブルで実現できますが、VBAの勉強のため、自作の関数を作成してみます。

Excelのバージョン

使用しているExcelのバージョンは「Excel 2013」になります。

画面イメージ

以下のようなシートを作成しました。
ちなみに、左側の売り上げテーブルには「Amazon」という名前を付けています。

agg_01.png

SUMIF関数を使用した場合の関数

まず、売り上げテーブルに「(月)」という作業列を追加して、日付カラムのデータから何月のデータなのかを取得しています。[E5]セルには以下のような式を入力します。

=MONTH(B5)

そして、SUMIF関数を使用している[H5]セルには以下のような式を入力します。

=SUMIF(Amazon[(月)],[@月別],Amazon[金額])

これで、売り上げテーブルにデータが追加される度に、集計テーブル(左側)が自動的に更新されていきます。
この方法で特に問題はありませんが、1つ気になるのが作業列として「(月)」というカラムを追加しなければいけない、という点です。
それを解決するために以下のような関数を自作してみます。

自作の関数を使用した場合

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

        ' 時系列に並んでいることが分かっている場合には最適化する
        ' そうでない場合には、以下の3行はコメントアウトする
        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という記述をしています。

これには2つの理由があります。

  • 関数の引数にListObject型が指定できなかった。何故かRange型になってしまいます。
  • 関数の引数として売り上げテーブルを渡していないと、テーブルが更新された時に、AggMonthly関数がコールされない。

このような理由により、ダミーの引数を渡しています。

これで、売り上げテーブルが追加される度に、集計テーブル(右側)が自動的に更新されていきます。

ピボットテーブルを使用した場合

ピボットテーブルを使えば、この手の集計は簡単に行えます。
ただし、売り上げテーブルにデータを追加してもリアルタイムでピボットテーブルが更新されません。
意図的に「ピボットテーブルの更新」コマンド(Alt+F5)を実行する必要があるようです。これはちょっと面倒です。
(もしかしてExcel2013だから?)

補足

日々の作業では、「今日の日付」、「ギフト券番号」、「金額」の3つを入力していきますが、私の場合にはペースターというツールを使用して半自動的に入力しています。
例えば、ギフト券取得のメールを受信すると、メールの本文に記載されている「金額」と「ギフト券番号」を連続してコピーしてしまいます。
これで、ペースターのクリップボード履歴にセットされます。
あとは、[B12]セルなどをアクティブにしてから、ペースターのキーストロークの発行タグを使用すると、今日の日付、ギフト券番号、金額を一発で入力できます。

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

MenuItem.txt
Amazonギフト券の集計(ペースター)\tAlt+Ctrl+F9|
[Y1]/[T2]/[D2][!K "TAB"][C1][!K "TAB"][C2]
/E

まず、今日の日付を貼り付けて、[TAB]キーを押して、履歴の1番を貼り付けて、[TAB]キーを押して、履歴の2番を貼り付ける、、、という処理が行われます。
見て頂けると分かると思いますが、このコマンドにはショートカットキーが割り当ててありますので、メニューを表示する必要もありません。

操作イメージを動画にしてみましたので、参考にしてみて下さい。
YouTubeの動画(約30秒)

終わりに

今回のタスクを作成している時に困ったのが以下の点です。

AggMonthly関数の引数として渡している売り上げテーブルを、関数の方でListObject型として直接受け取れないということです。
もし、以下のように記述できたらもっとスマートなのになぁ・・・と思いました。

Function AggMonthly(tbl As ListObject, MonthVal As Integer) As Integer

もしかしたら、Excel2016以降では普通にできるのでしょうか?

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

autumn_nsn
アラフィフのおじさんプログラマーです。 Windows用のソフト開発を20年ほど行ってきました。 C/C++がメインですが、Perl、Python、Excel VBA、PowerShellなども大好きです。 よろしくお願い致します、、、m(-_-)m
https://www.autumn-soft.com/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした