Excel のSUBTOTAL関数の応用についてお尋ねします。

質問

Excel のSUBTOTAL関数の応用についてお尋ねします。

SUBTOTAL関数は対象データに小計が多いとき便利ですが、同じ列にAVERAGE値など集計対象外の数値が入っているときには、はじめから終わりまでベタの範囲指定ではうまくいきません。

集計対象外の数値をよけ、コンマで区切ってSUBTOTAL範囲に指定すれば、計算はできますが、それではSUBTOTALを使う意味がなくなってしまいます。

ここで別の関数を組み込んで、一発ベタの範囲指定でやっつける方法はないものでしょうか、教えてください、よろしくお願いします。

回答No.3

MackyNo1 · ベストアンサー

＞AVERAGE値など集計対象外の数値が入っているときには、はじめから終わりまでベタの範囲指定ではうまくいきません。

SUBTOTAL関数で集計する場合に、AVERAGE関数で求めた平均値を集計対象外にするには、「AVERAGE(集計範囲)」の部分を「SUBTOTAL(1,集計範囲)」にします。

このようにしておけば、SUBTOTAL関数で範囲全体を集計しても、これらのセルを除外して集計することができます。

お礼

MackyNo1さん　ありがとうございました。
SUBTOTAL関数の勉強中でよく知らなかったものですから・・・。

回答No.4

KURUMITO

同じ列でAVERAGEk関数をなぜ使うのでしょう。SUBTOTAL関数で平均を求めるには集計方法を1にすればよいのでそれを使われてはどうでしょう。同じ列でAVERAGE関数を使うのではなく、AVERAGE(1,範囲)として使用するのがよいでしょう。

お礼

KURUMITOさん　ありがとうございました。
SUBTOTAL関数の勉強中でよく知らなかったものですから・・・。

回答No.2

tom04

こんにちは！
通常SUBTOTAL関数を使用する場合は、No.1さんが仰っているように
オートフィルタを併用する場合が多いと思います。

↓の画像でE1セルに
=SUBTOTAL(1,B:B)
という数式が入っています。
オートフィルタで「B」を選択すれば「B」の項目の平均が表示されます。
（SUBTOTAL関数の集計方法は検索してもらえれば判ると思うので、ここでは載せません）

他の関数での方法ですが、H1セルに平均値を表示したいデータを入力すれば
I1セルに表示するようにしてみました。
I1セルに
=SUMIF(A:A,H1,B:B)/COUNTIF(A:A,H1)
としています。これでH1セルに「A」と入力で「A」の平均が表示されるはずです。

尚、Excel2007の場合はAVERAGEIF関数が利用できると思います。

以上、この程度で、参考になればよいのですが・・・m(__)m

お礼

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

SUBTOTAL関数には１１とおりの集計方法があって、その中にAVERAGEがあり、それを使っておけば親戚同士いてもいいことになっているのに気付いていませんでした。

回答No.1

aokii

フィルターを使っては。

お礼

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