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


sum-add

photo credit: jjay69 Office_16 via photopin (license)

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

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

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

【初心者向けエクセルVBA】ファイルのコピーを使って取引先別の請求書を作る
請求データ一覧から請求書を自動で作成するシリーズのVBA講座もいよいよ9回目です!今回はWorksheetのコピーではなく、ファイルをコピーして取引先別の請求書ファイルを作成する方法を解説します。

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

質問はコチラ。

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

なるほどですね。

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

それには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社記載されているものとします。

エクセルVBAの取引先マスタ

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

エクセルVBAで使う請求データ

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

エクセルVBAで不要な請求書が生成

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

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

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

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

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

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

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

SUMIFS関数の書式は

SUMIFS(合計範囲, 条件範囲1, 条件1, 条件範囲2, 条件2, …)

ですね。

エクセルの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関数で書くと

=SUMIFS(F:F,A:A,”>=”&”2015/5/1″,A:A,”<"&"2015/6/1",B:B,"ABC株式会社")

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

VBAのSumIfs関数の使い方

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

WorksheetFunction.SumIfs(合計範囲, 条件範囲1, 条件1, 条件範囲2, 条件2, …)

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

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関数日付に特定の間隔を追加して返すというけっこう便利な関数です。

書き方は

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関数もかなり便利ですので、ぜひ覚えておいてくださいね。

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

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

【初心者向けエクセルVBA】ワークシートをオブジェクト名で取り扱う方法
初心者向けエクセルVBAで請求書を作成するシリーズ。今回は、エクセルVBAで頻繁に取り扱うワークシートを「オブジェクト名」を使って指定する方法です。オブジェクト名とは何か、またその編集方法もお伝えします。

どうぞお楽しみに!

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

お仕事において特定のデータ一覧から必要な情報を抽出するということは頻繁にありうると思います。ここではデータ一覧から請求書を作るということを目標に、実務で使えるスキルをまっすぐに身に着けることを目的としています。
  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】取引先別に請求書を作成するマクロを作る

コメント

  1. 古川 より:

    業務で必要になり勉強してます。
    請求書作成の連載で大変参考にさせていただいております。
    アップグレードされていく感があり大変頼もしいです。
    追加搭載していくという観点からこの連載は今現在ここが最新でしょうか。

    非常にわかりやすい解説で次にどんどん進んでいきありがとうございます。

    • 古川様

      コメントありがとうございます!

      > 追加搭載していくという観点からこの連載は今現在ここが最新でしょうか。

      そうですね…まだリライト進めてみないとなんとも言えないのですが、もしかしたら途中から分岐したり、方針変えたりはあるかも知れません。
      ゆっくりではありますが、進めていきますので、ぜひフォローを頂ければと思います。よろしくお願いいたします!

  2. 熊谷 より:

    VBAを勉強するために、「VBA超入門編」からずーっとここまで、何とかついてきていますが、ここで何度やってもエラー表示が出てしまい、上手く行かず止まってしまいました。
    この【DateAdd関数を使って年月を条件としてSumIfs関数を使う方法 】で新たに追加された「VBA」を「メインの請求書作成処理」の一連の流れの中に組み込んで頂き、最後の纏めとして、掲載して頂けませんでしょうか?

    指示通りに「for n」と「next n」の間に記載しましたが、どこに入れてもエラーとなってしまいますし、別な指示さえもエラーになるようになっていまい、「メインの請求書作成処理」の全体の記載を見せていただきたいのです。

    ここまできて、どうにもならなくなり、途方に暮れています。
    どうぞよろしくお願いしますm(__)m

  3. 熊谷 より:

    度々すみませんm(__)m
    何度もチャレンジして、クリアできました★

    【For n = 2 To rowsClient】の直ぐ下へ「SamIfs関数」を記載し、
    【Next n】の直ぐ上へ「End If」を記載したら、エラーにならずに、必要な年月分だけ請求書がコピーされました!

    結果から考えると、多分、必要な年月を振り分ける上で、最初に「SamIfs関数」を記載しなければならず、全体に規制をかけなければならないからなのかな。とは思うのですが…。
    何となく理解できましたが、頭の中でしっくりきていません。

    お時間がある時に、何故【For n = 2 To rowsClient】の直ぐ下へ「SamIfs関数」を記載し、【Next n】の直ぐ上へ「End If」を記載する必要があったのか
    解説をして頂けませんでしょうかm(__)m

    どうぞ宜しくお願いします!

    • 熊谷さん

      コメントありがとうございます。
      やっぱりSumifs関数…ニーズありましたか。悩んだのですが。
      いただいたご質問内容については、お答えしかねるのですが、記事のほうをシリーズに合わせて変更することを検討してみたいと思います。

タイトルとURLをコピーしました