【Excel】数式の参照範囲を可変対応させるためにはどうすれば良いのか(修正のため再投稿)

表題の件について、ご教示頂きたく存じます

あるBookのSheet1に下のような表があります(「.」は表がズレないようにするためのもので本題とは無関係です)

.......A......B......C......D......E......F　……　
1　通番　名前　4/2　5/1　6/9　
2　001　佐藤....30............20
3　002　鈴木...........10.....20
4　003　高橋....10................
5　004　山本...........30.....10
6　005　渡辺...........40.....20

この通番ごとの数値の合計を、Sheet2に
......A......B......C　　
1　通番　名前　合計
2　001...佐藤....50

上記のように集計したいのですが、Sheet1の表は不定期にデータが追加されていく(F列以降に数値が入れられていく)もので、
通常の範囲指定(「=sum(sheet1!C2:E2)」のような)では対応することができません
そこでこの状況において、【随時追加されていく数値に合わせて合計範囲も変化する式】があればお教え願えますでしょうか
(集計するための数式はSheet2のC列2行目以下に挿入します)

調べた中で、offset関数やcount関数などを組み合わせれば良いのかと試してはみたものの、
表の中に空白セルが存在することや、行ではなく列毎にデータが追加されていくことなどが原因なのか
思うように集計することができませんでした
(通常の範囲指定でも、数値が追加される度に合計範囲を変更するか、
範囲をC列以降に指定するなどすれば良いのかもしれませんが、出来ればそれは避けたいです)

もしこれが不可能なのであれば、その旨だけでもご回答頂けましたら幸いです
不躾かとは存じますが、宜しくお願い申し上げます

※内容の一部を修正したため前回の質問を削除した上で再投稿させて頂きました。申し訳ございませんでした

No.5 回答者： ママチャリ 回答日時： 2020/07/08 18:46

OffsetやCountを使いたいのであれば、こんな感じでイケると思います。

=SUM(OFFSET(Sheet1!C2,0,0,1,COUNT(Sheet1!$1:$1)))

1行目（見出しの行）の数字のセル（日付のセル）をカウントして、その列分だけOFFSETで参照しています（見出しの日付部分には、さすがに空白は無いですよね？）。


ただ、他の方が言っているように、広めに指定しちゃた方が、手っ取り早いと思いますよ。
Excelって、そんなに馬鹿じゃないので、広く取ってもパフォーマンス落ちないと思います。

この回答へのお礼

ご回答ありがとうございました
offset関数を利用した数式の例までご提示頂き心より感謝申し上げます

お礼日時：2020/07/08 21:04

No.4 回答者： tom04 回答日時： 2020/07/08 16:14

No.3です。

細かい検証をしていませんでした。
前回の配列数式の場合、数式を下へフィル＆コピーしても
必ずC2セルからの合計になってしまいますね。
行が下になるたびに　C2・C3・C4・・・のように変化させる必要がありました。

=SUM(INDIRECT("Sheet1!C"&ROW(A2)&":"&ADDRESS(ROW(A2),MAX(IF(Sheet1!2:2<>"",COLUMN(2:2))))))

※　これも配列数式になります。

※　わざわざこんな難しい数式にする必要はないような気がしますが・・・

どうも失礼しました。m(_ _)m

この回答へのお礼

ご回答ありがとうございました
indirect関数を使う手段もあるのですね
たいへん勉強になりました

お礼日時：2020/07/08 21:07

No.3 回答者： tom04 回答日時： 2020/07/08 13:59

こんにちは！

横からお邪魔します。
すでに的確な回答が出ていますので、余計なお世話かもしれませんが・・・
別シート表示のようなので、これ以上データはない！という位の範囲指定をしておけば対応できると思います。

仮にSheet2の2行目にSheet1の2行目を合計を表示したい場合は
=SUM(Sheet1!C2:XFD2)

といった具合に。（XFD列はシートの最終列になります）

どうしても最終列を取得し、その範囲を指定したい！というコトであれば
No.2さんが仰っているように最終列番号の取得が必要になります。

本来であれば通常はやらないと思いますが・・・
Sheet2の表示したいセルに
=SUM(INDIRECT("Sheet1!C2:"&ADDRESS(ROW(A2),MAX(IF(Sheet1!2:2<>"",COLUMN(2:2))))))

配列数式なので、Ctrl+Shift＋Enterで確定！（←必須★）とすれば
Sheet1の各行の最終列までがSUM関数の範囲になります。

※　このINDIRECT関数は同一シートの同じ行に「合計セル」がある場合などには有効です。
　　（列挿入などにも対応できる）

※　実際のところ後者の数式はPCにかなりの負担をかけ計算速度が極端に落ちます。
なので、最初の広い範囲のSUM関数の方をおススメします。m(_ _)m

No.2 回答者： fujillin 回答日時： 2020/07/08 13:28

こんにちは

＞通常の範囲指定でも、数値が追加される度に合計範囲を変更するか、
＞範囲をC列以降に指定するなどすれば良いのかもしれませんが、
＞出来ればそれは避けたいです
最終（入力）列を関数で算出することは可能ですが、「各列を調べて空白でない最終列を調べる」ことになるので、求める最終列以降の列についても（入力がないことを）確認することになります。

結局のところこれは、
＞範囲をC列以降に指定するなどすれば良いのかもしれませんが、
＞出来ればそれは避けたいです
と同じことになりますので、それならば、わざわざ最終列などを求めなくても、No1様のご提案のように集計範囲を十分に取っておく方法のほうが遥かに優れていると思います。

この回答へのお礼

ご回答ありがとうございました
御指摘ごもっともですね、たいへん失礼致しました

お礼日時：2020/07/08 21:09

No.1 回答者： chonami 回答日時： 2020/07/08 11:40

よくわからないのですが列が増えていくだけなら、最初から、C2から100列目（最終カバーできるほど）の列くらいまで範囲に含めておいた

らいいのでは？

この回答へのお礼

迅速なご回答に感謝申し上げます

お礼日時：2020/07/08 21:15