エクセルで空白行がある場合の計算

質問

エクセル２０００です。
Ｂ列は日にち、Ｃ列はＢ行の日にち間隔、Ｈ列はデータ値、Ｉ列は1日平均値です。
行いたいのは、Ｈ列をＣ列で割り、Ｉ列に1日の平均値を出したいのですが
Ｈ列に空白があった場合は空白行の日にち間隔を足し、1日の平均値を出したいです。
　　Ｂ 　　　　　　　Ｃ 　　　　　　　Ｈ 　　　　　Ｉ
日にち　　日にち間隔　　データ値　１日平均値
　　　　　　14　　　　42　　　　　3
　　　　　　3 　　　　15 　　　　　5
　　　　　　2 　　　　空白　　　　空白
　　　　　　2 　　　　空白　　　　空白
　　　　　　1 　　　　10 　　　　　　2・・・・・空白該当Ｃ行も足す（１０÷５＝２）
　　　　　　1 　　　　2 　　　　　　2
　　　　　　3 　　　　空白　　　　空白
　　　　　　4 　　　　21 　　　　　　3・・・・・空白該当Ｃ行も足す（２１÷７＝３）

文字位置がうまくあっていませんが、
初心者のため空白行の処置（関数）方法がよく分かりません。
アドバイスよろしくお願いします。

回答No.8

tom04 · ベストアンサー

No.7です。
補足に
＞最初の行は、日にち間隔が無いため・・・

とありますが、もしC列にデータがない場合は「0」で割ることになりますので、当然エラーになります。

仮にC列の最初の行だけが空白であるならば、2行目以降に数式を入れる方法があると思います。
前回の数式をそのまま利用し、I2セルに数式を入れるとすれば↓のような数式ではどうでしょうか？
C列もしくはH列が空白の場合はI列は空白になるようにしてみました。

C2セルに
=IF(OR(C2="",H2=""),"",IF(H1<>"",H2/C2,H2/(SUM($C$2:C2)-SUM($C$2:INDEX($C$2:C2,LARGE(IF($H$2:H2<>"",ROW($A$1:A1)),2))))))

※　前回同様に配列数式になってしまいますので、Shift+Ctrlキーを押しながらEnterキーで確定してみてください。

こんなんではどうでしょうか？m(_ _)m

お礼

お気づきいただき、親切の補足回答頂きありがとうございます。

アドバイスいただいた式でうまくいきそうです。

回答No.7

tom04

こんばんは！
↓の画像のような配置だとして・・・

I2セルに
=IF(H2="","",IF(H1<>"",H2/C2,H2/(SUM($C$2:C2)-SUM($C$2:INDEX($C$2:C2,LARGE(IF($H$2:H2<>"",ROW($A$1:A1)),2))))))

これは配列数式になってしまいますので、この画面からI2セルにコピー＆ペーストする場合は
I2セルに貼り付け後、数式バー内で一度クリック　→　編集可能になりますので
Shift+Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。

これをオートフィルで下へコピーすると画像のような感じになります。

以上、参考になれば良いのですが・・・m(_ _)m

補足

お礼が大変遅くなりました。

画像までつけていただき、分かりやすいです。

ありがとうございます。

大変参考になります。

最初の行は、日にち間隔が無いためエラー表示しますが
回避方法はありますか？

実際には印刷設定でエラーを横棒にしてますが・・・・
これにお気づきで、
よろしければお願いします。

回答No.6

imogasi

#3ですが、ＶＢＡだけの方法でなく、関数での方法を考えてみた。
例データ
Ｂ－Ｄ列　Ｄ列は下記関数の結果
日にち間隔　　データ値　１日平均値
2 4 2
1
3
4
1 18 2
2 3 1.5
3
1
1 15 3
3
2
1 18 3
ーー
その行より上で、何日間（行）空白があるかの、ユーザー関数を定義すると
標準モジュールに
Function up(a)
Application.Volatile
If a = "" Then
up = ""
Else
up = a.Row - a.End(xlUp).Row '上方向に空白で在るセルも含めての行数
End If
End Function
ーー
Ｄ２に
=IF(C2="","",C2/SUM(OFFSET($B$1:$B$100,ROW()-up(C2),0,up(C2))))
と入れて下方向に式を複写する。
ーー
結果
上記Ｄ列。

回答No.5

kagakusuki

I2セルに次の関数を入力してから、I2セルをコピーして、I3以下に貼り付けると良いと思います。

=IF(ISNUMBER($D2),$D2/SUM(IF(COUNT($D$1:$D1)=0,$C$1,INDEX($C:$C,MATCH(MAX($D:$D)+1,INDEX($D:$D,1):$D1)+1)):$C2),"")

補足

お礼が大変遅くなりました。

Ｃ2行目がデータが０以下はエラー表示し、１以上だとエラー表示はしません。

実際は3行目からデータが入っていますが、日にちのＢ３行目が最初になるので
Ｃ３行目は日にち間隔がとれません。が
問題はないです。

回答No.4

某HN クロメート(Chromate)（@CoalTar）

作業列を設ければ、難しい関数はいらないです。
D2セル
=IF(C2="","",SUM(B$1:B2)-SUM(D$1:D1))
E2セル
=IF(C2="","",C2/D2)
D2:C2セル下へオートフィル

お礼

お礼が大変遅くなりました。

作業列を設けると以外と簡単に出来るのですね。

参考にさせて頂きます。

ありがとうございます。

回答No.3

imogasi

ＶＢＡに縁が無かっただろうがＶＢＡでやると、人間が電卓でやる場合と同じ理屈で、素直にプログラムが組める。
関数でやる場合の方が、経験を積んでいて、改めて考えて直ぐ出てくる人ばかりではなかろう。
こういう現在行より直前の不定数の空白行をとらえるのって難しい(適当な簡単な関数がない）。
参考までに。プログラムのコメントを読んで見てください。電卓でやる場合の考え方とそっくりです。
例データ
日にち 日にち間隔 データ値 １日平均値
- 14 42
- 3 15
- 2
- 2
- 1 10
- 1 2
- 3
- 4 21
ーー
標準モジュールに
Sub test01()
d = Range("A65536").End(xlUp).Row '最終データ行の割り出し
'MsgBox d
For i = 2 To d '最終行まで毎行繰り返し
If Cells(i, "C") = "" Then 'C列が空白なら
k = k + Cells(i, "B") '間隔だけは足して、変数kに覚えておく
Else
'C列が空白でない場合
k = k + Cells(i, "B") '間隔は空白分行分に加えて出す
Cells(i, "D") = Cells(i, "C") / k '間隔合計でデータを和ってＤ列にセット
k = 0 '空白行の間隔は使ったので０にしておく
End If
Next i
End Sub
ーーー
結果
日にち 日にち間隔 データ値 １日平均値
- 14 42 3
- 3 15 5
- 2
- 2
- 1 10 2
- 1 2 2
- 3
- 4 21 3
ただし
Ｃ列が空白のデータで終わらない（最終行付近が空白でない）ものとする。

お礼

お礼が大変遅くなりました。

ＶＢＡには縁がありませんが、参考にさせて頂きます。
ありがとうございます。

回答No.2

MASUKUBO

１行目は項目名が入力されているとしてI２セルには次の式を入力して下方にオートフィルドラッグします。

=IF(H2="","",IF(ROW(A1)=1,H2/C2,ROUND(H2/SUM(INDEX(C:C,MATCH(10^10,H$1:H1)+1):C2),2)))

式では平均値が小数点以下３位で四捨五入されて小数点以下２位までが表示されるようにしています。

お礼

お礼が大変遅くなりました。

参考にさせて頂きます。
ありがとうございます。

回答No.1

superski

count関数を使うか、
if関数で実数が入力してあるカラムの数を割り出して計算させれば良いかと。

例えば、Iの右隣の列に=if(RC[-1]>0.1,1)を計算させて、
SUMでまとめた数で割ってやればokでしょ。

お礼

お礼が大変遅くなりました。

参考にさせていただきます。