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

★気に入ったらシェアをお願いします!


sum-add

photo credit: jjay69 Office_16 via photopin (license)

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

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

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

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

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

質問はコチラ。

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

なるほどですね。

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

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

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

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

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

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

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

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

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

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

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

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

  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, …)

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

となります。

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

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

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

書き方は

DateAdd(間隔を表す文字列, 値, 日付)

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

文字列 内容
yyyy
q 四半期
m
d
h
n
s

です。

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

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

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

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

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

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

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

となります。

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

まとめ

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

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

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

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

宣言もセットも不要!エクセルVBAでワークシートをオブジェクト名で取り扱う方法
エクセルVBAで頻繁に取り扱うワークシート。実はオブジェクト変数を使わずに「オブジェクト名」を使って操作する方法があります。プロジェクト全体で定数のように使うことができ便利ですので、その使い方をお伝えします。

どうぞお楽しみに!

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

お仕事において特定のデータ一覧から必要な情報を抽出するということは頻繁にありうると思います。ここではデータ一覧から請求書を作るということを目標に、実務で使えるスキルをまっすぐに身に着けることを目的としています。
  1. 【初心者向けエクセルVBA】データ一覧から請求書を自動で作る
  2. 【初心者向けエクセルVBA】For~Next文で簡潔にプログラムを書く
  3. 【初心者向けエクセルVBA】行の数をカウントする&不要な行を隠す
  4. 【初心者向けエクセルVBA】文字列の連結&Format関数での書式変更
  5. 【初心者向けエクセルVBA】If~Thenを使った条件分岐の超入門
  6. 【初心者向けエクセルVBA】Public変数の宣言とSubプロシージャの呼び出し
  7. 【初心者向けエクセルVBA】InputBoxでの日付入力と月末日の自動算出
  8. 【初心者向けエクセルVBA】Worksheetのコピーを活用して複数の請求書を作る
  9. 【初心者向けエクセルVBA】ファイルのコピーを使って取引先別の請求書を作る
  10. エクセルVBAでDateAdd関数を使って年月を条件としてSumIfs関数を使う方法
  11. 宣言もセットも不要!エクセルVBAでワークシートをオブジェクト名で取り扱う方法
  12. エクセルVBAでシートの列の挿入も簡単に対応できちゃう列挙体の使い方