ExcelのSUM関数の集計範囲を可変にしたい

質問

ExcelのSUM関数の集計範囲を可変にしたい

１．状況、問題点
商品毎の売上、利益を管理するリスト表が
あり、その表の行に対する追加、更新処理を
VBAマクロで行っています。
使用環境はExcel2010

リスト表は、帳票としての見易さを考慮して、
同じカテゴリに属する複数の行に対して小計行
を設けカテゴリ別の売上、利益を集計しています

カテゴリに属する行範囲に対してSUM関数を使っ
ているということです

(例１)この表にマクロで行を追加させる際、
あるカテゴリの小計欄で参照している集計
範囲の行の途中に行を挿入する場合は、行
を追加してもSUM関数の集計範囲は自動的に
拡大されます。

　　A　　B　　　 C
　 1 項目　　 売上　
　 2　 田中　　 100
3 小計　　 100
　 4 山田 200
⇒　挿入
　 5　 佐藤 300
　　6　 小計 　 600 式 = SUM(C4:C5)

3行目と4行面の間に行を挿入するとき　
SUM関数の集計範囲は自動的に拡大され
る　　SUM(C2:C4) ⇒　SUM(C2：C5)

(例２)挿入する行が集計行の直ぐ上の場合
SUM関数の集計対象範囲がずれてしまう

　　A　　 B　　　 C
　 1 項目　　売上
　 2　 田中　 100
3 小計　　100
　 4 山田 200
　 5　 佐藤 300
⇒　挿入　
　　6　 小計 600 ※

　　※ 式 SUM(C4:C5) は
　　自動的にはSUMC(C4:C6)とはならない

２．実現したいこと　　　　
　　小計の直前にに行を挿入した場合も自動的に
　　集計範囲が変わるようにしたいのです。

実装方法　案１

(1)インストラクタのネタ帳
　http://www.relief.jp/itnote/archives/003417.php
　に、「表全体の最後の行」※に集計行がある場合として
「合計範囲を自動的に変更する」方法の紹介あり。

　この方法はCOUNTA()で入力済みのセルをカウント。

　 私のケースでは
　・売上、利益などのセルが空白となる場合がある
　・C列は　売上　データを記載する列で
　　なので半角スペースや全角スペース は入らない
　・＃REFやDIV/!0などは入りうる

　 C4：C5 のセル数は

DIV/0!なども含めデータが入っているセル数
　＋ブランクが入っているセル数
は　 COUNTA(C4:C5) + COUNTBlank(C4:C5)
なので　セル C5
　=SUM(C2:INDEX(C4:C5, COUNTA(C4:C5)+COUNTBlank(C4:C5))
　 とするか？　

　※「インストラクタのネタ帳」の方法は、「表全体の行数」を
　もとめる場合に最後の行までを指定できて有効ですが、上の
　ように小計をもとめる場合は、どうしても小計をもとめる範囲
　式に書く必要があり、そこが集計範囲が変わってしまうと役に
　立たないです。

　代案としては、6行目の小計の行番号から3行目の小計の行番号
　を引いて行数を求める方法でしょうか

　この方法の場合、小計の式に　6行目行番号-3行目行番号
　のように具体的に記述してはNGなので　"下"の小計行から"上"
　の小計行を引く　というような間接的な指定の仕方が必要と
　考えています。案１の検討はここまでです。

実装方法　案２
　上のように表自体の集計関数を変える以外に、
　マクロで対応する方法

　行を追加する際に、
a)行の追加がある場合、行の挿入位置が小計
　　の集計範囲の”途中”なら　何もしない、

　b)追加する行が集計行のすぐ上になる場合
つまり変更前の挿入位置＋１
　　が小計行の行番号と等しい時は、追加する行数分
　　SUM関数の集計範囲を広げる

　案１が難しそうなので案２で実装しようと思っています
　Ｑ１　案１と案２の検討結果についてお気づきの点があれあば教えて
　　　　ください
　Ｑ２　可能であれば、案２の実装方法についてヒント程度でもいいので
　　　　教えてください

　以上

回答No.5

kagakusuki · ベストアンサー

>ただこの方法では、小計の所に集計されるのが先頭行から、小計の部分を除くすべての売上になってしまことがわかりました。

>先の例で２番目の小計行では　売上　500となるべきところで　600になってしまいます

　はい、御質問文に有る例では

　　A　　 B　　　 C
　 1 項目　　売上
　 2　 田中　 100
3 小計　　100
　 4 山田 200
　 5　 佐藤 300
⇒　挿入　
　　6　 小計 600 ※

となっており、「売上　500」にはなっておりませんでしたので、

>小計の所に集計されるのが先頭行から、小計の部分を除くすべての売上

を求めるための方法をお尋ねになっているものと思った次第です。

>売上　500となるべき
>最初の小計行、2番目の小計行をそれぞれの範囲でもとめる

という事でしたら、次の様な関数にして下さい。

=SUM(C$1:INDEX(C:C,ROW()-1))-SUMIF($A$1:INDEX($A:$A,ROW()-1),"小計",C$1:INDEX(C:C,ROW()-1))*2

補足

kagakusukiさん
お世話になります。最初の例がそもそも間違っており
大変申し訳ありません。

アドバイス頂いた式であれば、期待した通りの
結果になりました。

個人的にはこれで問題ないと思うのですが、
この表の管理元が別部署でこちらの都合だけ
で、少々複雑なこの数式への修正を許可して
くれるかるか自信が持てないです。

この式への修正を許可してもらえない場合も
考えてマクロ修正も考えておｋないといけな
さそうです。

この数式は、私が知らなかっただけでベテラン
の方であればときどき目にするもので特に
問題ないでしょうか？

お気づきの点あれば教えていただきたく。

以上

回答No.4

MackyNo1

そのリスト範囲を、ホームタブの「テーブルとして書式設定」してください。

このように設定しておけば、通常のリストでは対応しない行の挿入時の数式の自動挿入だけでなく、データの追加をした場合も、入力規則のリスト範囲やピボットテーブルなどのデータ範囲が自動拡張してくれます。

補足

KackyNo1さん
返信ありがとうございます
試してみました。当方はExcel2007環境ですが
テーブルとして書式設定ー新しいテーブルスタイル

ウィンドウが開く
ここで集計行を開いて　そこで集計行の書式を定義しておけば
行挿入時の自動挿入やデータ追加時の・・・
などを設定できるという点、そんな機能があったとは知りませんでした
非常に参考になりました

ただ、質問の際には思いいたらず説明していませんでしたが
リスト表は、自分以外が管理しており、私の都合で書式を定義したり
できません

なのでこの方法は、私のケースで使えなさそうです。

参考までに教えていただきたいのですが
「テーブルとしての書式設定」機能は、新しくテーブル
を１から作る場合は有用だと思いますが、私のように
誰かがすでに作ってしまったテーブルを前提にして書式を定義
するというようなことが可能でしょうか。

たとえば　集計行も小計　が複数あって　それをまとめて総合計
があるよういうような場合、Excelに小計、総合計などを定義
できるものなのでしょうか？

回答No.3

keithin

エクセルの機能の「小計」を使い、集計行を都度入れ直してやるのも一つの方策です。

ご質問の直接の回答としては、Ｂ６には
=SUM(B$1:INDEX(B:B,ROW()-1))-SUMIF(A$1:INDEX(A:A,ROW()-1),"小計",B$1)*2
といった数式でも計算できます。

補足

keithinさん

返信ありがとうございます。
この方法でも kagakusukiさんと同じで　２番目の売上小計
は 500となるべきところが、600となってしまいます

何か良い方法についてお気づきなら教えてください

回答No.2

kagakusuki

A列に「小計」と入力されている行の所に入力する関数を次の様なものにされると良いと思います。

=SUMIF($A$1:INDEX($A:$A,ROW()-1),"<>小計",C$1:INDEX(C:C,ROW()-1))

補足

kagakuskiさん

　返信有難うございます。試してみました。

　ただこの方法では、小計の所に集計される
　のが先頭行から、小計の部分を除くすべての売上
　になってしまことがわかりました。

　先の例で２番目の小計行では　売上　500となるべき
　ところで　600になってしまいます

　最初の小計行、2番目の小計行をそれぞれの範囲で
　もとめるには修正が必要です。

　時間がありましたら、教えて頂けると大変参考に
　なります。

回答No.1

shintaro-2

表はそのままで
集計をピボットテーブルで行えば済むのでは？

補足

shintaro-2 さん
すいません　ピボットテーブルの機能詳しくないのですが
小計を求められればいいので大がかりすぎる気がします

すでにリストがピボットテーブルで作成されていれば小計行の
追加等もレベルを上げたりするということですね

お気づきの点あればお願いします