質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.75%

  • VBA

    1644questions

    VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

  • Excel

    1405questions

    Excelは、マイクロソフト社が開発しているデータ集計や分析を行う表計算ソフトの一つです。文書作成や表計算、資料作成などの多彩な機能を備えており、統合パッケージであるMicrosoft Officeに含まれています。

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

解決済

回答 6

投稿

  • 評価
  • クリップ 0
  • VIEW 256

neet_studier

score 10

よろしくお願いします。

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の続きから進んで範囲内なら変数に足していく、

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

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 6

checkベストアンサー

+3

皆さん、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かな。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/05/31 15:25

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

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

    キャンセル

  • 2018/05/31 17:23

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

    キャンセル

  • 2018/05/31 18:10 編集

    いけそうですね!

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

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

    キャンセル

  • 2018/05/31 21:06

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

    キャンセル

  • 2018/06/01 06:58

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

    キャンセル

  • 2018/06/01 08:54 編集

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

    キャンセル

  • 2018/06/01 09:21

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

    キャンセル

+3

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

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/06/01 06:59

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

    キャンセル

+2

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

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

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

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

+2

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

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

+2

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/06/01 07:00

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

    キャンセル

0

もしVBAで書くとすれば

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/05/31 10:41

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

    キャンセル

  • 2018/05/31 10:52

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

    キャンセル

  • 2018/05/31 15:08

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

    キャンセル

  • 2018/06/01 07:01

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.75%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る

  • VBA

    1644questions

    VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

  • Excel

    1405questions

    Excelは、マイクロソフト社が開発しているデータ集計や分析を行う表計算ソフトの一つです。文書作成や表計算、資料作成などの多彩な機能を備えており、統合パッケージであるMicrosoft Officeに含まれています。