空白セルから空白セル間の計算方法

OSはWindows10
Excelは2010を使用しています。

添付図の様な表があり、

A列は品名が、B列にはINDEX関数とMATCH関数を使用し
別シートからA列に関連した数値を参照しています。

今回お聞きしたいのは
現状だとB3~B6の合計値をB7に
B9～B13の合計値をB14に
B16～B21の合計値をB22にSUM関数で出しています。

ただ、A列の増減することがある為
（今はA~Dで一区切りになっているが次はA~Gで一区切りになったり
　A~Cで一区切りになったりする）
増減した場合SUM関数での答えがおかしくなってしまうので、
空白セルから空白セルの区切り間をSUM関数で計算できないかな
と思い質問させて頂きました。

分かる方がいましたら教えてください。

No.3 ベストアンサー 回答者： fujillin 回答日時： 2020/07/23 18:13

こんにちは

ご質問の通りだとB列は2種類の数式を使い分けることになりますが、それでよいのですよね？（うっかり他のセルにフィルコピーはできない）

合計の行のA列には必ず「合計値」と記入があるものと仮定しています。
No2様のアイデアを応用させて頂いて、例えば、ご提示のB7セルに、
　=SUM(B$2:B6)-SUMIF(A$2:A6,"合計値",B$2:B6)*2
を入力し、他の合計行にコピペすることで対応可能と思います。

ご質問の通りに、「直近の空白行以降の合計」という計算も可能ですが、式が複雑になるので、計算方法は変えてあります。
また、現在、合計値以外の行を計算している式と合体させて、B列全体に同じ式を設定することも可能と考えられます。（こちらだとフィルコピー可能）
その場合は、
　=IF(A2="合計値",合計計算の式,現在の参照計算式)
といった要領でできると思います。

この回答へのお礼

回答頂きありがとうございました。
同列に表示できる数式を回答して頂けたので
ベストアンサーとさせていただきます。

お礼日時：2020/07/27 10:26

No.2 回答者： tom04 回答日時： 2020/07/23 16:41

こんにちは！

お示しのように同列（B列）に結果を表示したい！という場合は結構厄介だと思います。
（VBAであれば可能だとは思いますが・・・）

別列表示で良いのであれば、一例です。

↓の画像ではC2セルに
=IF(A2<>"合計値","",SUM(B$1:B1)-SUMIF(A$1:A1,A2,C$1:C1))

という数式を入れフィルハンドルで下へコピーしています。m(_ _)m

この回答へのお礼

回答頂きありがとうございました。
参考にさせていただきます。

お礼日時：2020/07/27 10:27

No.1 回答者： 銀鱗 回答日時： 2020/07/23 16:38

特に問題ないはずなんですけど…。

行を挿入したり、削除しても合計範囲は行の増減に従って変わってくれます。
（まあ、SUM関数が使われている行の直上に行挿入しない限り……ですけど）

・・・
合計を求める範囲をROWS関数で指定し、OFFSET関数に範囲として渡せばよい。
　＝SUM(OFFSET(B3,0,0,ROWS(B3:B7)-1,1))
これで合計するセルの直上に行を挿入されても範囲を指定できる。
他も同様。

※ ROWS関数で「合計」のセルまでの範囲を指定しておくと、範囲中にセルが挿入されることになるので自動的に数式の範囲が広がる。
　「-1」しておけば「合計」セルにSUM関数の範囲が及ばないのでエラーにならずに済む。

ROWS関数、OFFSET関数の使い方を確認しておきましょう。

この回答へのお礼

回答頂きありがとうございました。
参考にさせていただきます。

お礼日時：2020/07/27 10:27