平均の出し方

質問

Excel初心者です。
下記のような平均を出す関数を教えてください。

日毎に3列使い、日々の処理数を集計しています。
A列、B列、D列、E列は数値又は空欄。
日計：C列はsum(A:B)
日計：F列はsum(D:E)
月合計計：Q列はsum(C,F・・・)

Z列に処理平均を出したい。
C列とF列の値がゼロの時は平均の対象外にする。
average(C4,F4)とすると、数式が入っているせいか、結果が1.5になってしまいます。
ここにゼロや数式が入っているセルを除いた平均を出し、Z4に3と出るようにしたいです。
範囲が10個以上あるので、範囲指定もうまくできません。

　　A　　　B　　 C　　　　　D　 　　E　　　 F 　・・・　　　　Q　　　　　　　　Z
1　9/1　　　　　　　　　　　9/2　　　　　　　　　　　　　　　9月
2　午前　午後　終日　　午前　午後　終日 　　　　　　月合計　　　処理平均
3　　4　　　1　　　5　　　　1　　　 3　　　4　　　　　　　　　9　　　　　　　4.5
4　　　　　　　　　　　　　　 2　　　 1　　　3　　　　　　　　　3　　　　　　　　3
5　　1　　　5　　　6　　　　　　　　　　　　　　　　　　　　　　6　　　　　　　　6
6　　6　　　1　　　7　　　　5　　　　　　　5　　　　　　　　　12　　　　 　　　6

宜しくお願い致します。

回答No.7

imogasi · ベストアンサー

月合計と何日働いたかを出すために、ユーザー関数を作りました。
ALTキーを押しつつF11キーを押す。VBE画面になります。
続けてALTキーを押しつつI(アイ）キーその後Mキーを押すと標準モジュールの画面になります。そこに下記をコピーし、貼りつけます。
Function skpsum(a, b)
Dim cl As Range
n = 0
s = 0
For Each cl In a
n = n + 1
If n = b Then
s = s + cl
n = 0
End If
Next
skpsum = s
End Function
Function skpcnt(a, b)
Dim cl As Range
n = 0
s = 0
For Each cl In a
n = n + 1
If n = b Then
If cl <> "" Then
s = s + 1
n = 0
End If
End If
Next
skpcnt = s
End Function
これで３列おきに足す関数=skpsum(範囲,飛び数）と
３列おきにノンブランクの件数を出す関数==skpcnt(範囲,飛び数）です。
これで１日当たり平均は
=skpsum(B3:J3,3)/=skpcnt(B3:J3,3)のようにして求まります。適当に全体をROUND関数で丸めてください。

お礼

mz80さんとimogasiさんの回答を組み合わせて、
目的の平均が出せるようになりました。
分かり易いアドバイスありがとうございました。

補足

JaritenCatさん、mz80さん、imogasiさん、丁寧な回答ありがとうございます。
日毎小計がゼロならブランクにする関数と
imogasiさんに作成頂いたユーザ関数を試させて頂こうと思います。
明日、結果をご報告させて頂きます。

回答No.6

imogasi

＞日毎に3列使い
横に日にちを流して記録しているのですね。
ABC　9/1日分
DEF　　２
GHI　　３
JKL　　４
MNO　　５
ですか。
６日以後はどこに入力されるのでしょうか。
A列に戻るのでしょうか。
３０日の月と３１の月の場合、月合計の列（位置）は
第４行目の「4　　　　　　　　　　　　　　 2　　　 1　　　3　　　　　　　　　3　　　　　　　　3
」は何日のデータですか。
９月合計がQ列の多数行に表れるのはなぜ。

補足

補足が遅くなり申し訳ありません。
また説明不足ですみません。

imogasiさんのおっしゃる通り、横に流して日毎に3列使っています。
実際は一ヶ月分のデータをずっと横に流して入力していますが、
私が説明しやすいように、Q列を月合計としてしまいました。

行単位で個人毎の処理数を入れています。
Q列の月合計は個人毎の計です。
第4行目の人は9/1は未処理、9/2は午前1件、午後2件、計3件処理し、
9/3以降は未処理でQ列が3件としました。

Z列にその人の平均(/日)を出すようにしたいと思っています。
第4行目の人は9/1と9/3以降の処理しなかった日は平均の対象外とし、
月合計3 ÷ 処理日数1日 ＝ 3件 となるように表示させたいと思います。

回答No.5

JaritenCat

No.1とNo.2を書いた人です。
本当にすいません問題の意味が理解できてませんでした。
CやFは0の場合もあるんですね。

やはり他の方が書いているように小計が0のときはブランクとするのが一番早いと思います。

小計だけを作業用セルにコピーして範囲で指定できれば、
sum(AA4:AL4)/countif(AA4:AL4,">0") が使えるのですが。。
作業用セルは非表示にしておけば印刷しても見かけは変わらなくなります。

お礼

JaritenCatさんのアドバイス通り、
ブランクとしてカウントするように作成しました。
アドバイスありがとうございました。

回答No.4

mz80

No.３です。すいません。式なんですが3行C列の式は多分
=sum(A3:B3)
なので、
変更後は
=if(sum(A3:B3)=0,"",sum(A3:B3))
というのが、正しいと思います。

お礼

mz80さんとimogasiさんの回答を組み合わせて、目的の平均が出せるようになりました。
分かり易いアドバイスありがとうございました。

回答No.3

mz80

合計欄C列やF列の式を変更して合計がゼロの場合、空白(スペースではなく何も入っていない値)を設定すると言うのはどうでしょうか。ただし、この場合、合計欄C列等にはゼロは、表示できなくなります。
変更はC列であれば
=if(sum(A:B)=0,"",sum(A:B))
という感じです。

回答No.2

JaritenCat

No.1です。
すいません。勘違いしていました。
=sum(C4,F4,・・・・)/count(C4,F4,・・・) ってことです。

回答No.1

JaritenCat

単純に合計値を有効セルの数で割ればいいですか？

数字が入っているセルの数はcountで分かります。
sum(A4:P4)/count(A4:P4) でどうでしょう。