ＥＸＣＥＬで０を除いた平均値を求めるには？

質問

表記内容の例題として、Ａ１からＡ１０まで数字があります。
　　Ａ　　　
１　10
２　12
３ 0
４ 0
５　15
６　 0
７　10
・　・
・　・
・　・
10　12
上記内容の場合、0を除いた平均値は
SUM(A1:A10)/COUNTIF(A1:A10,"<>0")となるかと思います。しかし、例えばＡ２からＡ４までを非表示にした場合も内容が変わりません。表示されている項目のみで且つ、０を除外した計算式はどのような関数が良いのでしょうか？

よろしくお願い致します。

回答No.2

noname#24921 · ベストアンサー

まず XXwaiwaiXX さんが書かれた計算式では分母が"8"になり、非表示にする前の計算も正しい答えは出ないと思います。８～９行もカウントされるからです。
分母は　COUNTIF(A1:A10,">0") とします。

次に「例えばＡ２からＡ４までを非表示にした場合」と書かれていますが、どのような操作で非表示にするのでしょうか。推定としては次の２つの方法が考えられますが、それについてこう言う方法もありますということで書いてみます。非表示の方法がもし違ってたらごめんなさい。

(1)　Ａ２からＡ４を行ごと（２～４行）非表示にする場合。
　　Ａ　　Ｂ　　　
１　　1　　10
２　　0　　12　　
３　　0　　 0　　
４　　0　　 0
５　　1　　15
６　　1　　 0　　
７　　1　　10
・　　　　　・　　　　　
・　　　　　・
10　　1　　12

上記のようにＡ列の前に新たに列をもうけ例えば非表示にする行には"0"を、また表示させたい列には"1"入れる。合計を表示させるセル(B11)に次の式を入れます。
式　=SUMIF(A1:A10,1,B1:B10)/COUNTI(B1:B10,">0")
これにより２～４行を非表示にしても答えは同じ。

(2)非表示にするのにオートフィルターを用いる場合。

　　Ａ　　Ｂ　　　
１　　a　　b
２　　1　　10
３　　0　　12　　
４　　0　　 0　　
５　　0　　 0
６　　1　　15
７　　1　　 0　　
８　　1　　10
・　　　　　・　　　　　
・　　　　　・
11　　1　　12

上記のように１行目の前に行を１行追加し、列の項目に名前をつける（a,b）表中の任意のセルをクリックし「データ」「フィルター」「オートフィルター」をクリックし、a の矢印をクリックし"1" を選ぶ。
　Ｂ１２　には次の式を入れる
　　=SUBTOTAL(9,B2:B11)/COUNTIF(B2:B11,">0")

お礼

非常に解りやすくご案内頂きありがとうございます。

非表示の方法ですが、１・２の両方を試してみたのですが
肝心な絞込みを理解していませんでした。
=SUMIFと=SUBTOTALの使い方が違うのですね。すいません。
こんな知識レベルで答えを求めていたので非常に助かりました。

もう一度データを整理してみます。

ありがとうございました。

回答No.1

Wendy02

こんにちは。
これは、汎用性のあるユーザー定義関数SUMTOTALです。

使用法は、 'VSUM(集計方法,範囲,ゼロオプション)
で、集計方法は、
1 は　AVERAGE
2 は　COUNT
3 は　COUNT
9 は　SUM
です。

ゼロオプションは、デフォルトはFALSE ですが、TRUE を入れると0を勘定にいれません。また、文字列も、現在の場合、勘定には入れません。（ゼロの扱いはしない、ということです）

なお、とりわけ、質問のみということではなく、汎用性や発展性を考えた関数です。

こういうスタイルをお望みでない場合は、また、ご相談ください。他の方法を考えてみます。

'-----------------------------------------
'<標準モジュール登録>
Function VSUM(集計方法 As Integer, _
　範囲 As Range, _
　Optional ゼロオプション As Boolean) As Double
　'VSUM(集計方法,範囲,ゼロオプション)
　'非表示になっている行は集計しない
　Dim c As Variant, i As Long, Sum As Double, Z As Boolean
　If ゼロオプション = True Then Z = True
　Select Case 集計方法
　　Case 1 '平均
　　　For Each c In 範囲
　　　　If c.EntireRow.Hidden = False Then
　　　　　If Z = True And c.Value <> 0 And VarType(c) = vbDouble Then
　　　　　　Sum = Sum + c.Value
　　　　　　i = i + 1
　　　　　ElseIf Z = False And VarType(c) = vbDouble Then
　　　　　　Sum = Sum + c.Value
　　　　　　i = i + 1
　　　　　End If
　　　　End If
　　　Next
　　　Sum = Sum / i
　　Case 2 'Count
　　　For Each c In 範囲
　　　　If c.EntireRow.Hidden = False Then
　　　　　If VarType(c) = vbDouble Then
　　　　　　If Z = True And c.Value <> 0 Then
　　　　　　　i = i + 1
　　　　　　ElseIf Z = False Then
　　　　　　　i = i + 1
　　　　　　End If
　　　　　End If
　　　　End If
　　　Next
　　　Sum = i
　　Case 3 'Counta
　　　For Each c In 範囲
　　　　If c.EntireRow.Hidden = False Then
　　　　　If Z = True And c.Value <> 0 Then
　　　　　　i = i + 1
　　　　　ElseIf Z = False Then
　　　　　　i = i + 1
　　　　　End If
　　　　End If
　　　Next
　　　Sum = i
　　Case 9 '合計
　　　For Each c In 範囲
　　　　If c.EntireRow.Hidden = False Then
　　　　　If VarType(c) = vbDouble Then
　　　　　　Sum = Sum + c.Value
　　　　　End If
　　　　End If
　　　Next
　End Select
　VSUM = Sum
End Function