表示されているセルだけで平均が計算できる？

AVERAGE関数は非表示のセルも計算対象なのは知ってますよね。SUM関数も同じく非表示セルも計算対象です。だから、SUBTOTAL使えばいいんでしょ？ そんなの100年前から知ってたよ。だから？  っていう人。はい、残念でした。

SUBTOTALはオートフィルターにしか対応してません

列幅＝０または行の高さ＝０にしたセルには対応してません。

これをAVERAGE, SUBTOTAL, AVERAGEIFで計算させてみると。

当然ですが、オートフィルターを使ってAだけを表示させると、SUBTOTALの結果は1,217となります。Bの行を書式＞行の高さ＝０にして非表示にするとダメなわけです。

参考までにＡGGREGATE関数を使うと、19行目を行の高さ＝０で非表示にしてもAVERAGEIFと同じ結果を計算してくれます。

ケース２：列の非表示

じゃあ、列に設定したタイトルで同じものだけを対象にして平均を取りたいときはどうすればいいのか。次のサンプルをみてください。列のタイトルが回収となっているセルだけでそれぞれの台の平均を取るにはどうしたらいいでしょうか？　ちなみに数字は適当ですｗ　オートフィルターは使えないので、書式＞列の非表示で"投資"の列を隠します。

AGGREGATEを使えばよさそうなんですが、非表示の行にしか対応していないので今回のように"投資"の列を非表示にした場合はAVERAGEやSUBTOTALと同じ結果を返してしまいます。そこで今回のケースでは、セルタイトルが"回収"だけの列で計算すればいいというポイントに着目して、AVERAGEIFSを使います。

AVERAGEIFS(平均対象範囲、条件範囲１，条件１、[条件範囲２]，[条件２]、.....)

平均対象範囲は横1行、条件範囲１にはタイトル行、条件１は"回収"とすればいいわけです。

=AVERAGEIFS(J14:P14, J$13:P$13, "回収")

結論：

行に対する非表示対応ならSUBTOTALよりもAGGREGATEを使う方がよい。

シート構成にもよるが列の非表示に関してはAVERAGEIFSをうまく使うしかない。