sum 範囲を横に可変したいです！ 縦ならできたのてすが・・ 写真のような関数の組み方ではうまくいき

sum 範囲を横に可変したいです！
縦ならできたのてすが・・

写真のような関数の組み方ではうまくいきませんでした。

教えてください！

No.4 回答者： enunokokoro 回答日時： 2020/03/22 14:49

> 単にサムの範囲を常に増やしておくと同じですね？？

違いますよ。

質問にある式は、
　=SUM(OFFSET(D5,0,0,1,COUNTA(5:5)-3))
ですよね。
この数式をCOUNTA関数の範囲である5行目以外のセルで使えば、
合計が正しく出せるはずです。（例えばC1セルで計算するなど）

No.2に添付した画像で説明するなら、
　=SUM(OFFSET(D5,0,0,1,5))
と同じ意味なので、この数式をC5セルに記載でも同じ値になります。

問題は、COUNTA(5:5)の範囲にC5セルがあることなのです。
COUNTA(5:5)では、5行目全体を範囲にして値のあるセルの個数を
出すものですが、この範囲内に計算式があることで循環参照の対象
になり、計算が正しくならない場合があるということです。

[オプション:で[反復計算を行う]のチェックを入れれば、循環参照でも
計算してくれますが、PCに負担をかけるので処理が遅くなります。
計算式が増えてくると、遅さに拍車をかけることになります。

計算したいセルが、行全体を指定したときに含まれる場合、行全体を
指定しないで、計算したいセルを除く範囲を指定すべきなのです。
質問にある式の、
　=SUM(OFFSET(D5,0,0,1,COUNTA(5:5)-3))
と
　=SUM(OFFSET(D5,0,0,1,COUNTA(A5:XFD5)-3))
とは同じことは理解できますよね。

これは、計算式のあるセル範囲であるA5:C5セルまでと、D5セルから
最終のXFD5セルまでを指定したことと同じになります。
　=SUM(OFFSET(D5,0,0,1,COUNTA(A5:C5,D5:XFD5)-3))
-3の意味がA5:C5セルを除く範囲の値のある個数なのですから、
　=SUM(OFFSET($D5,0,0,1,COUNTA($D5:$XFD5)))
が成り立つのです。
計算式のあるセルまでを除くことで、循環参照の範囲内にはならない
ことになり、計算する範囲が可変したい範囲と一致するのです。

以上の説明で、SUM関数で計算する範囲が、COUNTAで出した個数
までの範囲ということなるので、常に増やしておくこととは意味が違う
ということは理解できると思います。

ちなみに、No.3の回答者さんが指摘していることも大事です。
可変範囲内に、空白セルがあると無意味だからです。

No.3 回答者： aouze_ya 回答日時： 2020/03/22 09:16

C5に入れる関数　=SUM(OFFSET(D5,,,,50))

C6に入れる関数　=SUM(OFFSET(D6,,,,50))

横方向を可変にする必要がないのではないですか。

可変にするのを、COUNTAで出すようであれば、途中に空白があったら、合計がおかしくなるし、
横方向の終わりをセルに入れた値で可変にする必要があるならば、そのセルの値あるいは値があるセルの位置を見つける関数を探すのが良いのだと思います。

No.2 回答者： enunokokoro 回答日時： 2020/03/22 08:48

COUNTA関数で指定した範囲内に数式を入れているので、循環参照

となることから幅が0になってしまうのです。
https://support.office.com/ja-jp/article/%E5%BE% …

D5セルより右側にあるセル範囲を指定したいのなら、範囲を決めて
おくほうが良いと思います。
　=SUM(OFFSET($D5,0,0,1,COUNTA($D5:$XFD5)))

XFD列は最大列数である16384列目になります。

この回答へのお礼

返答ありがとうございます。このやりかたは、単にサムの範囲を常に増やしておくと同じですね？？

お礼日時：2020/03/22 11:45

No.1 回答者： mike_g 回答日時： 2020/03/22 07:51

「縦ならできた」と仰る例を示してください。

そうすれば貴方が「横に可変したい」ことが、より明確になろうかと。