エクセルVBAでDateAdd関数を使って年月を条件としてSumIfs関数を使う方法

みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。

エクセルVBAで請求データ一覧から請求書を自動で作成するシリーズ、公開してからたくさんの方にご覧いただいています。

特に以下の記事ですが、コメントをたくさんいただいておりまして、ありがたいことです。

【初心者向けエクセルVBA】ファイルのコピーを使って取引先別の請求書を作る

請求データ一覧から請求書を自動で作成するシリーズのVBA講座もいよいよ9回目です！今回はWorksheetのコピーではなく、ファイルをコピーして取引先別の請求書ファイルを作成する方法を解説します。

tonari-it.com

今回は、その中から一つのご質問について回答という形で記事を書いてみようと思います。

質問はコチラ。

「ファイルコピーで得意先別請求書を作る」ですが、当月納品のない得意先もあると仮定して納品のあった得意先のみ請求書ファイルを作成する処理を教えて下さい。

なるほどですね。

確かに、該当データがない場合には請求書ファイルを作らない、という処理が必要ですね。

それにはExcel関数でおなじみですが、エクセルVBAでも使えるSumIfs関数を使って年月の判定をしていきたいと思います。

また、DateAdd関数という日付に追加する便利な関数も紹介しますよ。

では、行ってみましょう！

前回までのおさらいと今回のお題

まずは、前回までのおさらいです。

まず、モジュールレベル変数の宣言ですね。

Option Explicit

Dim wsData As Worksheet '「請求データ」シートを入れるオブジェクト変数
Dim wsInvoice As Worksheet '「請求書ひな形」シートを入れるオブジェクト変数
Dim wsClient As Worksheet '「取引先マスタ」シートを入れるオブジェクト変数
Dim rowsData As Long '「請求データ」の行数
Dim rowsClient As Long '「取引先マスタ」の行数

続いて、ワークシートの初期化用のプロシージャです。シートのセットと各シートの行数の取得をしていますね。

取引先を列挙している「取引先マスタ」というシートと、各社の請求データをまとめている「請求データ」というシートを使用します。

各シートの様子については後述しますので、そちらを参考ください。

Sub シート初期化()

Set wsData = ThisWorkbook.Worksheets("請求データ")
Set wsClient = ThisWorkbook.Worksheets("取引先マスタ")

rowsData = wsData.Cells(Rows.Count, 2).End(xlUp).Row '「請求データ」の最後の行数を取得
rowsClient = wsClient.Cells(Rows.Count, 1).End(xlUp).Row  '「取引先マスタ」の最後の行数を取得

End Sub

そしてこちらがメインの請求書作成処理です。

Sub 請求書作成()

Dim i As Long, j As Long, k As Long, n As Long 'For～Nextカウント用整数型変数
Dim dayData As Date '納品日格納用変数
Dim dayCutoff As Date '締月入力用変数
Dim strClient As String '取引先格納用変数
Dim strFile As String 'コピー先パス＆ファイル名

Call シート初期化

dayCutoff = InputBox("締月を入力してください（例：2015/5）")

For n = 2 To rowsClient

    strFile = ThisWorkbook.Path & "\" & Format(dayCutoff, "yyyymm") & "_" & wsClient.Cells(n, 1).Value & ".xlsx" 'コピー先ファイル名

    FileCopy ThisWorkbook.Path & "\請求書ひな形.xlsx", strFile  'ファイルをコピー

    Workbooks.Open strFile  'コピーにて作成したファイルを開く
    Set wsInvoice = ActiveSheet  '開いたファイルのワークシートをセット

    k = 21 '請求書ひな形シート用カウント変数、スタートは21行目

    For i = 2 To rowsData 'iは請求データ用のカウント変数、最終値の設定にrowsDataを使う

        strClient = wsData.Cells(i, 2).Value '現在の行のクライアント名を取得
        dayData = wsData.Cells(i, 1).Value '現在の行の納品日を取得

        If strClient = wsClient.Cells(n, 1).Value Then
            If Year(dayData) = Year(dayCutoff) And Month(dayData) = Month(dayCutoff) Then '例：年が2015でかつ月が5の場合は処理を実行

                For j = 1 To 3

                    '請求データの2+i行目を請求書ひな形の21+i行目に転記
                    wsInvoice.Cells(k, j).Value = wsData.Cells(i, j + 2).Value

                Next j

                k = k + 1

            End If
        End If
    Next i

    wsInvoice.Rows(k & ":50").Hidden = True 'データがない行を隠す
    wsInvoice.Calculate '「請求書ひな形」シートを再計算する
    wsInvoice.Range("A18").Value = "ご請求金額：" & Format(wsInvoice.Range("D54").Value, "#,##0") & " 円"
    wsInvoice.Range("D15").Value = DateSerial(Year(dayCutoff), Month(dayCutoff) + 1, 0) '請求日
    wsInvoice.Range("D16").Value = DateSerial(Year(dayCutoff), Month(dayCutoff) + 2, 0) 'お支払期限

Next n
End Sub

おおざっぱに言うと、取引先マスタに記載されている取引先の数だけ

1. 「請求書ひな形.xlsx」ファイルをコピーして開く
2. 該当の取引先かつ該当の年月の請求データを開いたファイルに転記

という処理をするという内容です。

ただ、今回ご指摘頂いたような問題があるんですね。

お題：該当月に取引がなかった取引先については請求書を作らない

例えば、「取引先マスタ」がこちらのように、3社記載されているものとします。

そして「請求データ」はこちら。

この例で、2015年5月の請求書を作るべく、Subプロシージャ請求書作成を実行してみると…

「株式会社いろは」は2015年5月のデータがありませんので、請求書を作る必要がないのですが、「ご請求金額：0円」の請求書が作られてしまうんですね。

このような取引先がたくさんあった場合に、いちいち閉じたり消したりが面倒…ということなんです。

VBAでSumifs関数を使った判定をする

まず、請求書を作るべき取引先というのは

• 該当の取引先名、かつ
• 該当の年月の納品日、かつ
• 0円よりも大きい

レコードがあるかどうかで決まりますよね。

Excel関数に詳しい方であれば、SUMIFS関数っぽいことをやればよいのでは？とピンと来るはずです。

SUMIFS関数の書式は

ですね。

これをVBAで使う方法がありますので、利用していきます。

SUMIFS関数で年月で条件をつける

しかしその前に、SUMIFS関数を年月で条件をつける場合はどうするの？という疑問があるかも知れません。

なにせ、日付データは年、月、日で構成されていますからね。

2015/5/1、2015/5/20、2015/5/30は2015年5月のデータ判定して、2015/6/1は2015年5月のデータではないと判定しなければいけません。

このような場合は、条件を一つ増やして

• 条件1：A列が比較年月の1日以降
• 条件2：A列が比較年月の翌月1日より前
• 条件3：B列が該当取引先

とすればOKです。

つまり、ExcelのSUMIFS関数で書くと

というような数式になります。

VBAのSumIfs関数の使い方

VBAでSumIfs関数を使う場合は、WorksheetFunctionというオブジェクトを使い、以下のように書きます。

今回の場合は、条件が若干複雑ですが

If WorksheetFunction.SumIfs( _
    wsData.Range("F:F"), _
    wsData.Range("A:A"), ">=" & dayCutoff, _
    wsData.Range("A:A"), "<" & DateAdd("m", 1, dayCutoff), _
    wsData.Range("B:B"), wsClient.Cells(n, 1).Value _
    ) > 0 Then

    '処理

End If

となります。

DateAdd関数で日付に一定の月を追加する

ちょっと見慣れない関数がありますね。

DateAdd関数は日付に特定の間隔を追加して返すというけっこう便利な関数です。

書き方は

です。間隔を表す文字列は

文字列	内容
yyyy	年
q	四半期
m	月
d	日
h	時
n	分
s	秒

です。

今回の場合ですが、まずInputBoxで入力した締月がdayCutoffという変数に格納されます。このとき

• 年、月→指定した年、月
• 日→1

として格納されていますね。

ですから、これに一カ月分を追加する、つまり

DateAdd("m", 1, dayCutoff)

とすれば、翌月の1日が取得できるのです。

データがないときは請求書を作らない

以上をまとめますと、For文内の処理は

For n = 2 To rowsClient

    If WorksheetFunction.SumIfs( _
        wsData.Range("F:F"), _
        wsData.Range("A:A"), ">=" & dayCutoff, _
        wsData.Range("A:A"), "<" & DateAdd("m", 1, dayCutoff), _
        wsData.Range("B:B"), wsClient.Cells(n, 1).Value _
        ) > 0 Then

        '中略

    End If
Next n

となります。

これで、不要な請求書は作成されないようになります。

まとめ

以上、エクセルVBAでの年月を条件としたSumIfs関数の使い方を紹介しました。

日付に年や日などを追加できる、DateAdd関数もかなり便利ですので、ぜひ覚えておいてくださいね。

さて、せっかくなのでもう少しシリーズを充実していきます。

次回はオブジェクト名でシートを指定する方法というのをお伝えしたいと思います。

429 Too Many Requests

tonari-it.com

どうぞお楽しみに！

連載目次：データ一覧から請求書を自動で作る

お仕事において特定のデータ一覧から必要な情報を抽出するということは頻繁にありうると思います。ここではデータ一覧から請求書を作るということを目標に、実務で使えるスキルをまっすぐに身に着けることを目的としています。

1. 【初心者向けエクセルVBA】データ一覧から請求書を自動で作る
2. 【初心者向けエクセルVBA】ワークシートをオブジェクト名で取り扱う方法
3. 【初心者向けエクセルVBA】For～Next文で簡潔にプログラムを書く
4. 【初心者向けエクセルVBA】行の数をカウントする＆不要な行を隠す
5. 【初心者向けエクセルVBA】文字列の連結＆Format関数での書式変更
6. 【初心者向けエクセルVBA】セル範囲を一気にまとめてコピーする方法
7. 【初心者向けエクセルVBA】ワークシートのデータのある範囲だけをピッタリ取得する方法
8. 【初心者向けエクセルVBA】セル範囲の平行移動をする方法・リサイズをする方法
9. 【初心者向けエクセルVBA】日付データから年・月・日を取り出す
10. 【初心者向けエクセルVBA】If～Thenを使った条件分岐の超入門
11. 【初心者向けエクセルVBA】For～Next文でセル範囲を一行ずつ移動させる
12. 【初心者向けエクセルVBA】セル範囲のクリア～ClearContentsメソッドとClearメソッド
13. 【初心者向けエクセルVBA】ワークシート・セルを選択する方法の色々について
14. 【初心者向けエクセルVBA】入力ダイアログを表示するInputBoxメソッドの使い方
15. 【初心者向けエクセルVBA】日付データから月末日と翌月末日を自動算出する
16. 【初心者向けエクセルVBA】ワークシートをコピーする方法とそのシート名を変更する方法
17. 【初心者向けエクセルVBA】オブジェクトを変数にセットして取り扱う方法
18. 【初心者向けエクセルVBA】Openメソッドで新たなブックを開く方法
19. 【初心者向けエクセルVBA】現在マクロを書いているブックのフォルダパスを取得する
20. 【初心者向けエクセルVBA】開いたブックとそのワークシートをオブジェクト変数にセットする
21. 【初心者向けエクセルVBA】ワークブックを別名で保存して閉じる方法
22. 【初心者向けエクセルVBA】取引先別に請求書を作成するマクロを作る