質問: よろしくお願いします。 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の続きから進んで範囲内なら変数に足していく、 という手順が思い浮かんでるのですが、もっとマシな手順というものはありますでしょうか?
回答: 皆さん、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かな。