フィルターをかけた時にCOUNTIFで抽出した数量も適合させたいのですがよい方法はないでしょうか？

数量 16 16 16 ← 計算式＝ COUNTIF(F10:F25,"<>""")
---の数量 7 8 9 ← 計算式＝ COUNTIF(F10:F25,"---")
平均点 46.7 41.3 57.9 ← 計算式＝ F7/（F4-F5）
点数合計 420 330 405 ← 計算式＝ SUBTOTAL（9，F10:F25）

A B C
1回 --- 25 ---
2回 50 --- ---
3回 20 80 30
4回 --- 30 ---
1回 15 --- 40
2回 75 --- ---
3回 80 40 50
4回 --- --- ---
1回 --- 20 70
2回 20 35 ---
3回 30 --- 35
4回 --- 85 ---
1回 --- --- 80
2回 60 15 ---
3回 70 --- 100
4回 --- --- ---
上記のような表があります。
回数ごとに、点数の平均点を出したいのですが、総数や、点数の合計は、フィルター時に計算可能なのですが（SUBTOTAL(9,I10:I25)の式で可能です）COUNTIFで求めたーーー（欠席）の数量が元の表全体のままなので、平均点が出ません。フィルター時にもその条件に合ったCOUNIFの内容を出したいのですがよい方法はないでしょうか？教えてください。
よろしくお願いいたします。

No.2 回答者： goomania 回答日時： 2021/04/19 16:19

No.1さんの回答のように数式内で「回号」を指定しまう方法も当然考えられる訳ですが、ご質問者は「フィルター時ににもその条件にあった･･･」とおっしゃっておられます。

そこで、フィルター時にカウントが変わる方法を考えてみました。
添付画像をご覧ください。
No.1さんのご指摘のように、セル位置がわかりませんので、妄想を交えて、ご質問者の状況を再現してみました。
当然のことながら、全く当たっていない可能性が高いのですが、ご容赦ください。

まず、I列までは使用していることが判っているので、その右隣の列J10からJ25まで、全て「1」を入力しておきます。
さらに、その右隣のK、L、M列にD、E、F列の「---」の数をカウントするためK10に「=(D10="---")*1」という数式を記述し、オートフィルで右方向、下方向にコピーしてK、L、M列を埋めます。
F4には=COUNTIF(F10:F25,"<>""")という数式が入っているはずですが、「=SUBTOTAL(9,$J$10:$J$25)」に変更します。
F5には＝ COUNTIF(F10:F25,"---") という数式が入っているはずですが、「=SUBTOTAL(9,M10:M25)」に変更します。
F6には＝ F7/（F4-F5）という数式が入っているはずです。ここは変更がありません。
F7には=SUBTOTAL(9,F10:F25)という数式が入っているはずです。ここも変更がありません。
さらにI列を集計に使用していると推測しました。D列～F列までを集計してI列に表示します。I10に「=SUM(D10:F10)」という数式が入っており、オートフィル等でI25までコピーされているものとします。
I7には=SUBTOTAL(9,I10:I25)という数式が入っているはずです。ここも変更はありません。
I列の他のセルについて、ご質問者の説明がありません。
一応、見出しの内容に合わせるため、
I4には「=SUM(D4:F4)」、I5には「=SUM(D5:F5)」、I6には「=I7/(I4-I5)」という数式を入れてあります。
F列で記入した数式は、オートフィル等でD列、E列にもコピーするものとします。

こうすると添付画像①のような表が完成します。
これを、例えば「1回」でフィルターをかけると添付画像②のようになり、ご質問者のご希望に叶うものになると思います。

No.1 回答者： fujillin 回答日時： 2021/04/17 12:04

こんにちは

セル位置がまったくわからない（A,B,C ??)上に、計算式との関係も不明です。

まぁ、エスパー的に・・・
例えば、「1回」の平均を求めたいなら
=IFERROR(SUMIF($E10:$E25,"1回",F10:F25)/SUMPRODUCT(($E10:$E25="1回")*ISNUMBER(F10:F25)),"")
とか。

式中の文字の「1回」をセル値にしておけば、「2回」、「3回」もフィルコピーできるものと想像。

※ 当たるも八卦なので、エスパーじゃなかった時は無視してください。

この回答へのお礼

ありがとうございます。
回答いただいた式を、エクセル上においたのですが、反応がありません。
何となくあっているような気もしますのだ、内容を再確認の上、もう一度実施してみます。
もう少し時間をいただき、やってみます。
ありがとうございました。

お礼日時：2021/04/17 18:48