【VBA】＃N/Aを無視して串刺し平均を出したい

いつもご助言いただきありがとうございます。
VBA初心者です。

複数シート（枚数変動）の特定セルの平均を求める計算式を串刺しで作成しましたが、
＃N/Aを含む場合にそのエラーを無視して、エラーでないセルだけを計算するように
変更したいと考えています。

例えば、計算したいセルの値がそれぞれ「３，５，７，９，＃N/A」であった場合に、
「３，５，７，９」の4つだけを計算して平均を出したい、という内容です。

Sheet数は変動するので、Sheet(1)からマクロ起動用のアクティブシートの手前までを
ws_a = Sheets(1).Name
ws_z = ActiveSheet.Previous.Name
とし、
ThisWorkbook.Worksheets("集計").Cells(1, 1).Formula = _
"=IFERROR(ROUND(AVERAGE(" & ws_a & ":" & ws_z & "!" & "A1),0),"""")"
のように串刺しておりました。

何かうまい方法はありませんでしょうか。
お知恵をお貸しいただけますと幸いです。よろしくお願いいたします。

No.5 ベストアンサー 回答者： tom04 回答日時： 2019/07/16 16:21

こんにちは！

横からお邪魔します。
「集計」シートのA1セルには数式を残さないといけないのでしょうか？
単に結果だけを求めたいのであれば、せっかくVBAを使っているのであれば
結果をそのまま表示させてみてはどうでしょうか？

一例です。

Sub Sample1()
　Dim k As Long, wS As Worksheet
　Dim cnt As Long, myVal
　　For k = 1 To Worksheets.Count
　　　Set wS = Worksheets(k)
　　　　If wS.Name <> "集計" And IsNumeric(wS.Range("A1")) Then
　　　　　myVal = myVal + wS.Range("A1")
　　　　　cnt = cnt + 1
　　　　End If
　　Next k
　　　Worksheets("集計").Range("A1") = WorksheetFunction.Round(myVal / cnt, 0)
End Sub

こんな感じでお望みの結果になると思います。m(_ _)m

No.6 回答者： WindFaller 回答日時： 2019/07/17 09:30

他の方のおっしゃるように、ユーザー定義関数なら、どうでしょうか。

AGGREGATE関数は、串刺しがうまく行かないようです。

できるなら、シートにはユーザー定義関数は置かないで、マクロに組み合わせた方がよいかもしれません。

rName は、　=mySH_AVERAGE("A1") とすればよいです。

'//
Function mySH_AVERAGE(rName As String, Optional n As Integer = 0)　　'n は、四捨五入の桁
　Dim sh As Worksheet
　Dim total As Double, cnt As Long
　For Each sh In Worksheets
　　If sh.Name <> "集計" Then　'除外シート名
　　　If IsNumeric(sh.Range(rName).Value) Then
　　　　total = total + sh.Range(rName).Value
　　　　cnt = cnt + 1
　　　End If
　　End If
　Next
　mySH_AVERAGE = Int((total / cnt) * 10 ^ n + 0.5) / 10 ^ n
End Function

No.4 回答者： d-q-t-p 回答日時： 2019/07/16 15:56

> 意味のないこだわりではないので困っています。

意味のないこだわりだと断言できるから申し上げています。

No.3 回答者： fujillin 回答日時： 2019/07/16 15:41

こんにちは

＞何かうまい方法はありませんでしょうか。
ご提示のVBAの処理が、どのようなタイミングで行われるのかわかりませんが、関数形式で常に値が更新されるようにしたいのであれば、No2様が挙げておられる「ユーザ定義関数」が一番確実ではないでしょうか？
参照シートがどのように決まるのかわからないので、そのあたりの選択をどうするのがよいのかは不明ですが、少なくともエラー値を省くのは簡単だし、平均は「合計値 / 有効対象数」で求めることができます。

No1様のご指摘の、AGGREGATE関数にもほぼ同様の機能がありますが、「計算対象シートが変わる」とのことなので、一旦関数式を設定しておけばおしまいということでもなさそうなので、結局はユーザ定義関数の方が一回設定すれば終わりとなる分だけ簡潔かと思います。
もしも「設定用のマクロ（？）で一度関数を設定すればおしまい」というのであるなら、AGGREGATE関数の方が組込み関数である分だけ便利と言うことになるでしょう。

どうせVBAで関数をいちいち設定するのなら、VBAで直接結果まで計算してセットするようにしても、たいして変わりはないのかも知れません。（どのようなタイミング実行されているのかわかりませんし、この方法もエラーを省くのは自由なので）

No.2 回答者： d-q-t-p 回答日時： 2019/07/16 15:05

エラーが出ないようにした方が早いと思いますけど。

串刺し計算では無理なので強引にワークシート関数でやろうと
思ったら指定シートから指定シートまでのシート名の配列定数
を作って それを基にして合計を出して件数で割るくらいです。

=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&{1,2,3}&"'!A1"),"<1E15"))/SUMPRODUCT(COUNTIF(INDIRECT("'Sheet"&{1,2,3}&"'!A1"),"<1E15"))

> 何かうまい方法はありませんでしょうか。
ワークシート関数で計算しようという意味のないこだわりを捨
ててユーザー定義関数を作ればいいでしょう。

この回答へのお礼

ありがとうございます。
意味のないこだわりではないので困っています。

お礼日時：2019/07/16 15:14

No.1 回答者： mabuterol 回答日時： 2019/07/16 13:34

excel 関数に aggregate という便利な関数があるので、それを使う。

https://dekiru.net/article/4367/

VBA で使うならば worksheetfunction.aggregaate(***) みたいにして使う