Excelで、セル範囲(自分の1行下：空白セルの上)、を求めることできますか？

質問

Excelで、セル範囲(自分の1行下：空白セルの上)、を求めることできますか？

Ａ列
──────
2004年 　　3 ← =sum(A2:A3)
2004/11　　1
2004/12　　2
　　　　　　　← 空行
2005年　　5 ← =sum(A6:A8)
2005/1　　3
2005/2　　2
2005/3　　0
　　　　　　　← 空行

sum()の引数(合計範囲)を求めることできますか？
手作業で"Ａ６：Ａ８"等入力していかないとできないのでしょうか？

回答No.5

Wendy02 · ベストアンサー

こんばんは。

#3 のWendy02 です。お目に止まり、どうもありがとうございます。

今、どうして出来ないのか分りません。

最初の質問で、私は読み違えましたが、

----------------------
Ａ列
──────
2004年 　　3 ← =sum(A2:A3)　→ SUM(B2:B3)
2004/11　　1
2004/12　　2
　　　　　　　← 空行
2005年　　5 ← =sum(A6:A8)　　→ SUM(B6:B8)
2005/1　　3
2005/2　　2
2005/3　　0
　　　　　　　← 空行
----------------------

ということにしていましたが、それは正しいのですか？

2004年の右隣の合計を出すセル「3」がB1 の式だとします。

B1:
=SUM(OFFSET(A1,1,1,MATCH(TRUE,INDEX(A1:A10="",,),0)-2))

SUM(OFFSET(A1,...　　　　　　
　　　　　　↑
　　　　　　起点になるセル
　　　次の　　1 は、2行目、
　　その次の　1 は、2列目
　　つまり、B2 を指しています。

ここで、

　　MATCH(TRUE, は、TRUE の行を探すということです。
　　INDEX(A1:A10="",,)　というのは、="" つまり、空白かどうかということです。
　　　　もし、ここで、="" でないとしたら、この式は成立しません。
　　マスウで、INDEX(A1:A10="",,) の範囲を取って、F9 を押すと、その内訳が分ります。

●ここがポイント
　　空行は、どんなのが入っているか、数字でないとか、文字でないとか、いくつかの条件から引っ張りだしてこなくてはなりません。全角空白が入っている場合は、

INDEX(A1:A10="　",,)
　　　　　　　　↑
　　　　　全角空白が入っていますが、なるべく、空行は何も入れないようにするほうがよいです。

　　A1:A10=""　こういう書き方を、「配列数式」といいます。
　　A1:FALSE, A2:FALSE, A3:FALSE, A4:TRUE ... と続きますが、そのままではエラーになります。それで、INDEX を使うことによって、MATCH で読み取れる引数に変換しているわけです。

※まとめ
　要点は、最初の修正部分だけを気をつければよいです。

=SUM(OFFSET(A1,1,1,MATCH(TRUE,INDEX(A1:A10="",,),0)-2))
　左隣の起点が、A1 だったら、A1。A2だったら、A2 です。
　1,1 は、一つ下がって、一つ右に行くということです。

　A1:A10 は、最初の部分が、文字か数字がある起点になる場所で、そこから最大範囲を作ってあげます。
　検索値は、空白行（空なら、="" となります）

　-2 というのは、
　MATCHで、範囲が空白行までカウントしてしまっているので、まず、それが、-1

　それと、SUMで出す行の部分をカウントしてしまっていますから、それも、-1

　あわせて、「-2」となります。

全てのバージョンで調べていませんので、今のところ、Excel2003 などでは分りません。もしも、うまくいかないようでしたら、

=SUM(OFFSET(A1,1,1,MATCH(TRUE,A1:A10="",0)-2))
と入力して、数式として、一旦式を入力したら、F2を押して、『ShiftとCtrlを押しながらEnterキー』を押して、再確定すれば、同様の結果になるはずです。

お礼

Wendy02さんありがとうございます！完璧あっさり成功しました！！

×=sum(A2:A3)　○→ SUM(B2:B3)
×=sum(A6:A8)　○→ SUM(B6:B8)
そのとおりです。皆さん申し訳ありませんでした。

テストエラーの原因
申し訳ありません。完全に私のアホ過ぎミスです。
sumセルの左隣(A1等)を空セルのまま実行してました。
その結果、INDEX(A1:A10="",,)が最初でTrueを返す→offset(A1,1,1,1-2)
となってしまっていました。

＞F9 を押すと、その内訳が分ります。
これでミスを発見できました。素晴らしい方法ありがとうございます！
２時間近く格闘してました(ＴＴ)

大変丁寧な解説ありがとうございます。
ヘルプやどんな他のページよりも良く理解できました。
大変感謝しております。本当にありがとうございました！
＆すみませんでした。

回答No.4

imogasi

この質問には、関数式で実現するには、関数式の、「原理的な面から根拠付けて」無理があると言えます。
という理由は、関数式を入れる場所（合計を入れる場所）は、関数が探して
そこへ、関数式を埋め込まないといけないのですが、それはできません。関数式は、（A）人間がセル場所を決めて入れるか、（B)複写で規則的に、連続的に！入れるしかありません。
（B)はデータ部に連続した行に複写した式を入れると、データが壊れます。
飛び飛びのセルに式を複写することは、手作業以外ではできません。
一方VBAの場合は条件に合ったセルに、好きな関数式を設定できます。
これは＃２、＃３のご回答のようなのと、矛盾するものではありません。
別列などを使えば、答えだけは求められます。そこから、B列第４行に、正しい値は入れられても、式の＝SUM(A2:A3)のようなのはは入れられないでしょう。

お礼

皆さんありがとうございます！
非常に難しくて、理解に時間が掛かっております。
以上、中間報告

回答No.3

Wendy02

こんにちは。
この式なら、空白行の手前までの範囲を求めます。

B1:
=SUM(OFFSET(A1,1,1,MATCH(TRUE,INDEX(A1:A10="",,),0)-2))

ただし、
INDEX(A1:A10="",,)
　　　↑
　　　ここは、計算行の必要最大行です。
だから、最大行が100行あるのでしたら、A1:A100="" となります。

=SUM(OFFSET(A1,1,1,MATCH(TRUE,INDEX(A1:A100="",,),0)-2))

注:絶対参照式ではなく、相対参照式です。
ですから、B5: に貼り付ける場合は、以下のようになります。単にコピー&ペーストするだけですから、変更する必要はありませんが、下のセルに向かって検索します。

=SUM(OFFSET(A5,1,1,MATCH(TRUE,INDEX(A5:A14="",,),0)-2))

お礼

できればWendy02さんの方法でいきたいのですが、成功しません。
教えていただいた式を貼り付けると、Ｂ２セル１行だけの合計となります。
色々変えて試しましたが、どうしても成功しません。
ｏｆｆｓｅｔ関数の高さー２というのを変更すると、エラーになります。（－２の場合しか、ｓｕｍ結果が出ません。－２の場合はＢ２セル１行だけがｓｕｍ計算対象となります。=sum(offset(a1,1,1,1-2)) と同等）

ｉｎｄｅｘ関数の結果が「空白行の手前までの範囲」を表していないのではないでしょうか？
ヘルプ等を見たのですが、
（Ａ１：Ａ１００＝””，，）の
Ａ１００＝””
という書き方が載ってなくて、修正できずにいます。

どなたか、補足いただければ幸いです。

回答No.2

shkwta

B列に「合計したいデータ」が入っているとします。C列に補助的な列を設けます。
(C列が邪魔なら、別の列でもいいし、別のシートでも可能です。ただし、以下の式は変わります）

C1に、=IF(ISBLANK(B2),0,C2+1)
これを、C2から下へ必要なだけ(データの行数+1まで）コピーします。この式は、空白セルから上にあるセルの数を数えます。

次に、B列の合計を入れたいところ(たとえばB1）にOFFSET関数を使って範囲を書きます。
B1に、=SUM(OFFSET(B1,1,0,C1,1))
この式は、B1の１つ下から、C1に示される行数の範囲の合計を求めるものです。

あとは、この式を、B列の必要箇所にコピーするだけです。

お礼

ありがとうございます。この方法でもできそうです。

回答No.1

champ-eagle

質問の意味がよく理解できていないのですが、オートＳＵＭ機能を使えばいいのではないですか？
メニューバーの「Σ」マークにカーソルを合わせると「オートＳＵＭ」と出てくるところがあります。

　セルを指定する
→オートＳＵＭをクリック
→合計したいセル範囲をドラッグ
→ＥＮＴＥＲ
　でいいのでは？
ちょうど、質問したばかりで目に留まったので。
誤解してたら、ごめんなさい。

補足

すみません。手作業ではなくて、計算式が無いのかなと思っております。
行数はそれぞれ変化するので、いちいち手作業しなければできないのかな？と
2,3個だったらいいのですが