範囲を確認して、必要なら合計して配置する

Question

よろしくお願いします。

Excelのブックに、2つのシートがあるとします。
シート1には、A列に日付が251行並んでいます。重複なしの昇順になっており、7日から15日くらいの不定の間隔で8年分ほどの日付です。
シート2には、A列が日付、B列には金額が並んでいます。300行くらいで、こちらのA列の日付は重複ありですが昇順になっています。
どちらのシートの値（日付も金額も）も、ブックを開いた時に常に変動します。

実行したい処理は、シート1のB列に、シート2から、1の日付の範囲にある金額の合計、同じくシート1のC列には、シート2の日付のうち範囲内にあるもっとも下のものを配置していきたいのです。

たとえば、シート1のA列には
A1　2017年12月28日
A2　2018年1月10日
A3　2018年1月18日
A4　2018年1月25日
A5　2018年2月10日
（略）
A250　2025年5月25日
A251　2025年6月8日
A252
というように並んでるとして、
シート2には
2017年11月30日　｜15000
2018年1月3日　｜25000
2018年1月3日　｜65000
2018年3月3日　｜15000
2018年3月6日　｜35000
2018年3月10日　｜25000
（略）
2025年6月30日　｜15000
というように並んでいます。

ここで、シート1のB列に金額を入れていきたいのです。
B1には、シート2の、2017年12月28日以前の金額の合計を、
B2には、シート2の、2017年12月29日から2018年1月10日までの金額の合計を、
B251には、シート2の、2025年5月26日から2025年6月8日までの金額の合計を
B252には、シート2の、2025年6月8日以降の金額の合計を、
そして、
C1には、B1の金額の元になったシート2の日付のうち、もっとも後の方の日付を入れたいです。

この処理を自動で行なうことをめざしています。

シート1の行数は251行固定ですが、シート2の行数は変動します。

両シートとも日付が昇順で並んでいるという点をとっかかりにして、うまくいけばB列に関数を仕込んでおくことで実現できるのではないかとも思うのですが、やはりVBAで処理を書くほうが確実なのか、とグダグダしています。

いちばんExcelに負荷がかからないのはどういった方法でしょうか？

もしVBAで書くとすれば、

• シート1を1行読み取って、ひとつ上の日付との間ということで範囲を確定し
• シート2を順に進みながら日付が範囲内なら金額を変数で合計しておき、
• シート2の日付が範囲から外れたところで、シート1のB列に変数から合計値を、C列にひとつ前の日付を入力し、
• 改めて合計を記録していた変数を0にして、シート1のひとつ下のセルを見て範囲を再設定し、
• シート2の続きから進んで範囲内なら変数に足していく、

という手順が思い浮かんでるのですが、もっとマシな手順というものはありますでしょうか？

Answer 1

皆さん、VBAでということで、私もその方がいいように思いますが、数式でやる方法も考えてみました。

SUMIFS関数
B1に
=SUMIFS(Sheet2!B:B,Sheet2!A:A,"<="&A1)

B2に
=SUMIFS(Sheet2!B:B,Sheet2!A:A,"<="&A2,Sheet2!A:A,">"&A1)
下にオートフィル

これだと、Sheet2が昇順でなくてもOKです。
逆にデータ数が多いと重くなる可能性があります。

昇順ということを活かすなら、MATCH と OFFSET を使って

B1 に
=SUM(OFFSET(Sheet2!$A$1,1,0,MATCH(A1,Sheet2!A:A,1)))

B2 に
=IF(MATCH(A2,Sheet2!A:A,1)>MATCH(A1,Sheet2!A:A,1),SUM(OFFSET(Sheet2!$A$1,MATCH(A1,Sheet2!A:A,1),1,MATCH(A2,Sheet2!A:A,1)-MATCH(A1,Sheet2!A:A,1))),0)
下にオートフィル

うーん、これだけ複雑になるなら、メンテナンス考えたら、やはりVBAですね。

SUMIFS関数で動作速度にストレスがなければそれで，ストレス感じるならVBAかな。

Comments

[ExcelVBAer]

自分もやるなら SUMIFS でしょうね。
SUMIF,COUNTIF 等は処理が重いですけど、300行程度ならいけそうかも。

ただ、C1に合計範囲の最後の値を表示したいって要望をかなえるには、
Sheet2の方にも数式を組む必要があるでしょうね（少し面倒ですが。。。）

[hatena19]

C1に
=OFFSET(Sheet2!$AccessA$1,MATCH(A1,Sheet2!A:A,1)-1,0)
下にオートフィルでいけそうかな。

[ExcelVBAer]

いけそうですね！

ただ、今回のデータだと、該当の日付があるかどうか不確定なので、
第３引数は「0：一致」ではなく、「1：不一致」の方が適当なような。

懸念点としては、同じ日付が無い事が前提条件になっちゃうので少し不安。
あと、該当の日付が無かった時に、重複ヒットしちゃうかもですね(汗)

[hatena19]

MATCH の第3引数は 1 になってますよ。OFFSETの第3引数は 0 ですが。

[neet_studier]

ご回答ありがとうございました。
教えていただいた関数を試してみましたが、質問の手順のままのVBAの方がやはり軽快でした。
ただ、ここまで関数を駆使する例というのをなかなか見られないのでとても勉強になりました。

[i_sugiyama_tomo]

hatena19さん、そっか「&」で繋げば行けたんですね、ありがとうございます勉強になりました。なんか間違った情報で質問者の方には申し訳ないです。

[ExcelVBAer]

hatena19さん　引数、失礼しました汗

Answer 2

細かい内容より、大枠ではシート２を日付単位で集計したものをシート１と突合するって事ですよね。
マシな方法と言われると、集計の得意なAccessでやったらって話ですけど、それは置いておきます。

エクセルで集計するとなるとピポットテーブルとか関数とか色々ありますけど、やはりVBAが最強です。
SQLで集計できると尚良いと思いますので、以下参考に。
Excelの集計をSQLでおこなう方法。VLOOKUP()やSUBTOTAL()の限界を超えろ！！

Comments

[neet_studier]

ご回答ありがとうございました。
結局、質問の手順のままのVBAでいくことにしました。
ただ、ご案内のSQLの手法、難しそうですがよく読んでみます。

Answer 3

sumifの条件式には「セルの値」が使えないので、関数で書くとすれば「新しい日付以下かどうか総当たりして抜き出した数値の総和」と「古い日付以下かどうか総当たりして抜き出した数値の総和」の差を取るしかないのでVBAで書いた方がリソース的には良いかと思いまする。

Comments

[neet_studier]

ご回答ありがとうございました。
質問の手順のままのVBAでいくことにしました。

Answer 4

最後の一行くらいしか読んでませんが…。
まずはその思い浮かんだ方法で実装してみては。
実装してみてできないことがあったり、思ったよりも負荷が高い、とか問題があれば、改めて質問してみてください。

言葉を替えて言いますと、
「そこまで考えてんならサッサと手を動かして作ればいいじゃん」
です。
この質問を書き上げる時間で実装できるんじゃないですかね？

Answer 5

VBAでいいんじゃない？　手順もシンプルなのに限るよ。

だって、後で必ず色々と変更があるよね？

数式で作れなくはないけど、
トータルコスト的（予想もあるけど）にはVBAかな。

逆に思いつく「マシ」な方法ってぇのは何があるんでしょうか？

Answer 6

もしVBAで書くとすれば

まずはその手順でVBAで組まれてはいかがでしょうか。

Comments

[ExcelVBAer]

現時点では、「手順」の話しだけで、コードくれって方向性ではないので、いいんじゃぁないかと、自分は今の段階では思ってます。

[m.ts10806]

私もコードくれという話ではないと思ってるので、まず組んでみたらどうでしょうという回答にしました。
コードくれだけなら丸投げ低評価だけしてます。
組んでみて違ってきたら変えればいいしもっと別のやり方を思いつくかもしれない。でもそれは始めてみないと分からないですからね。
机上だけでは何も進まないと思ってます。

[ExcelVBAer]

なるほど。自分もトライ＆エラーは重要だと思います。
何せ、Excel が思い通りの動きをするか、
やってみなきゃ分からない場合もありますから。。。
動く分、フットワークも軽くなりますもんね。

[neet_studier]

まさか「コードまるごと書いて」という主旨だと疑われる可能性など考えてませんでした。
質問の仕方も工夫していきます。