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関数の集計範囲を広げる
案１が難しそうなので案２で実装しようと思っています
Ｑ１ 案１と案２の検討結果についてお気づきの点があれあば教えて
ください
Ｑ２ 可能であれば、案２の実装方法についてヒント程度でもいいので
教えてください
以上
補足
ありがとうございます。早速試してみましたが、改ページのチェックボックスが灰色になって操作できませんでした。