No.5ベストアンサー
- 回答日時:
こんばんは。
#3 のWendy02 です。お目に止まり、どうもありがとうございます。
今、どうして出来ないのか分りません。
最初の質問で、私は読み違えましたが、
----------------------
A列
──────
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 を押すと、その内訳が分ります。
これでミスを発見できました。素晴らしい方法ありがとうございます!
2時間近く格闘してました(TT)
大変丁寧な解説ありがとうございます。
ヘルプやどんな他のページよりも良く理解できました。
大変感謝しております。本当にありがとうございました!
&すみませんでした。
No.4
- 回答日時:
この質問には、関数式で実現するには、関数式の、「原理的な面から根拠付けて」無理があると言えます。
という理由は、関数式を入れる場所(合計を入れる場所)は、関数が探して
そこへ、関数式を埋め込まないといけないのですが、それはできません。関数式は、(A)人間がセル場所を決めて入れるか、(B)複写で規則的に、連続的に!入れるしかありません。
(B)はデータ部に連続した行に複写した式を入れると、データが壊れます。
飛び飛びのセルに式を複写することは、手作業以外ではできません。
一方VBAの場合は条件に合ったセルに、好きな関数式を設定できます。
これは#2、#3のご回答のようなのと、矛盾するものではありません。
別列などを使えば、答えだけは求められます。そこから、B列第4行に、正しい値は入れられても、式の=SUM(A2:A3)のようなのはは入れられないでしょう。
No.3
- 回答日時:
こんにちは。
この式なら、空白行の手前までの範囲を求めます。
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さんの方法でいきたいのですが、成功しません。
教えていただいた式を貼り付けると、B2セル1行だけの合計となります。
色々変えて試しましたが、どうしても成功しません。
offset関数の高さー2というのを変更すると、エラーになります。(-2の場合しか、sum結果が出ません。-2の場合はB2セル1行だけがsum計算対象となります。=sum(offset(a1,1,1,1-2)) と同等)
index関数の結果が「空白行の手前までの範囲」を表していないのではないでしょうか?
ヘルプ等を見たのですが、
(A1:A100=””,,)の
A100=””
という書き方が載ってなくて、修正できずにいます。
どなたか、補足いただければ幸いです。
No.2
- 回答日時:
B列に「合計したいデータ」が入っているとします。
C列に補助的な列を設けます。(C列が邪魔なら、別の列でもいいし、別のシートでも可能です。ただし、以下の式は変わります)
C1に、=IF(ISBLANK(B2),0,C2+1)
これを、C2から下へ必要なだけ(データの行数+1まで)コピーします。この式は、空白セルから上にあるセルの数を数えます。
次に、B列の合計を入れたいところ(たとえばB1)にOFFSET関数を使って範囲を書きます。
B1に、=SUM(OFFSET(B1,1,0,C1,1))
この式は、B1の1つ下から、C1に示される行数の範囲の合計を求めるものです。
あとは、この式を、B列の必要箇所にコピーするだけです。
No.1
- 回答日時:
質問の意味がよく理解できていないのですが、オートSUM機能を使えばいいのではないですか?
メニューバーの「Σ」マークにカーソルを合わせると「オートSUM」と出てくるところがあります。
セルを指定する
→オートSUMをクリック
→合計したいセル範囲をドラッグ
→ENTER
でいいのでは?
ちょうど、質問したばかりで目に留まったので。
誤解してたら、ごめんなさい。
この回答への補足
すみません。手作業ではなくて、計算式が無いのかなと思っております。
行数はそれぞれ変化するので、いちいち手作業しなければできないのかな?と
2,3個だったらいいのですが
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) セルに抜けた番号の代わりに空白を挿入する 4 2023/04/10 20:29
- Excel(エクセル) エクセル 条件に合う日付に入力された時間数の合計したい 4 2022/06/17 22:18
- Excel(エクセル) VBA 特定の列に入っているテキストをコピペ 2 2023/06/14 11:24
- Excel(エクセル) Excel VBA 空白行があるセル範囲に色を付ける 3 2022/06/13 15:58
- Excel(エクセル) エクセルの関数で質問です。 3 2023/02/24 14:07
- Excel(エクセル) 日付で矢印マクロ 4 2023/07/25 16:47
- その他(Microsoft Office) 計算式にはゼロ表示をしたい 6 2022/05/14 09:33
- Visual Basic(VBA) 範囲を指定して別シートにコピペ 2 2022/09/15 07:32
- 会計ソフト・業務用ソフト ExcelのSUM関数と+の違い 2 2022/07/12 18:32
- Excel(エクセル) [オートフィルター]機能について 3 2023/02/04 14:32
このQ&Aを見た人はこんなQ&Aも見ています
-
「環境が人を育てる」って本当?環境によって人格や生き方は本当に変わるのか
環境が人生に与える影響は実際どれほどのものなのか、専門家の田宮由美さんに伺った。
-
エクセル 空白セルまでの合計値を求める方法
Excel(エクセル)
-
空白セルから空白セル間の計算方法
その他(Microsoft Office)
-
excel2000で特定セルから空セルの前までの行数を数えたい
Excel(エクセル)
-
-
4
空白セルまでの合計を求める方法
その他(Microsoft Office)
-
5
空白行から空白行までの合計値とその間の最大値の算出方法について
Excel(エクセル)
-
6
初めて0以外の数値が出てきたときの値を表示(EXCEL)
Excel(エクセル)
-
7
EXCELマクロを使い、空白行ではさまれた範囲の数字を合計
Excel(エクセル)
-
8
SUMIF関数で、「ブランク以外を合計」を指定したい
その他(Microsoft Office)
-
9
文字列の結合を空白行まで実行
Visual Basic(VBA)
関連するカテゴリからQ&Aを探す
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Excelの入力規則で2列表示したい
-
【Excel】での計算式教え...
-
複数の文字列のいずれかが含ま...
-
エクセルで公平にチーム分けす...
-
エクセル 「入力候補を複数表...
-
エクセルで小数を含む数値の抽出
-
列の数字に100をかけたい
-
Excelの文字列を数字に変換する...
-
Excelで、複数条件で抽出した複...
-
エクセルで、数値の境目を数えたい
-
エクセルで特定の文字を含むセ...
-
エクセルで曜日に応じた文字を...
-
Excelで五十音順に並べ替えたい
-
エクセルで表示されている数字...
-
Excel上でのデータ数字が連番で...
-
スペースとスペースの間の文字...
-
エクセルで数式に値を代入して...
-
漢字の含む数値列の検索方法
-
B列に、A列の数字が偶数の場合...
-
割り算の式をコピー。分母を同...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
複数の文字列のいずれかが含ま...
-
Excelの入力規則で2列表示したい
-
数式が入ったセルを含めて、数...
-
SUMに含まれる範囲から特定のセ...
-
列の数字に100をかけたい
-
Excel上でのデータ数字が連番で...
-
エクセル:横長の表を改行して...
-
別のセルに値が入力されたら、...
-
スペースとスペースの間の文字...
-
エクセルでセルの値分の個数の...
-
エクセルで表示されている数字...
-
エクセル:一覧表に存在する文...
-
【EXCEL】指定したセルの値を他...
-
HYPERLINKとADDRESSとMATCHの組...
-
時間を「昼間」と「夜間」に分...
-
エクセルのsumifでかけ算してか...
-
エクセルで曜日に応じた文字を...
-
エクセルで、毎日の走行距離(...
-
エクセルで住所の番地順に並べ...
-
エクセルで1列全部10倍したい
おすすめ情報