フィルター時の、別の列に書いてある数字の表に現れた個別の数字の数量のみを表す式を教えてください。

前回の質問で、回答を教えてもらい、実際にやってみましたが、どうしてもわからないので再度質問いたします。
教えて頂いた式は =SUMPRODUCT(SUBTOTAL(102,OFFSET(基準セル,ROW(セル範囲1),0))*(セル範囲2＝1))
で具体的に入れた式は、
勝手に考えた式 1⇒ =SUMPRODUCT(SUBTOTAL(102,OFFSET(K$1,ROW(9:21),0))*(1))
勝手に考えた式 2⇒ =SUMPRODUCT(SUBTOTAL(102,OFFSET($A$1,ROW(K10:K1000),0))*(1))
です。
式1では、現れた数字が何であろうと、すべて数えた結果となります。

別紙の表でやってみたのですが、別紙の表では答えが10となり、個別の数字1の分は出てきません。
1と2と3の合計値が出ます。このうちの1を指定したいのですが、簡単な方法はないでしょうか？
式2阪神完全に間違っているようで答えは0となります。
申し訳ないですがもしこの式が間違っているのなら、修正内容を教えてください。
よろしくお願いいたします。

No.1 回答者： fujillin 回答日時： 2024/10/31 22:33

こんばんは

これなんでしょうけれど・・
https://oshiete.goo.ne.jp/qa/13951100.html
ろくに説明もせずに、ダメな数式だけを示したところで、どなたにも意味がさっぱりわかるはずはないと思いますよ。

行きがかり上、今回のみ回答はしますけれど、前回も述べたように中途半端なフィルターから関数で求めるのは本末転倒だと思います。
どうせやるのなら、
　・きちんとフィルター操作で抽出する
　・フィルターを用いず、条件から関数で算出する
のどちらかにした方が宜しいと思います。

いろいろな条件で比較したいのでしょうから、後者にしておいた方が、条件を一覧にしておけばそれぞれの結果も一覧にできるので、比較はしやすいものと想像しますけれど・・
また、その方が数式も簡単なものになるでしょう。
条件を入れ替える際に入力するのが面倒なら、プルダウンにでもしておけばそれなり扱いやすいものにできるでしょう。


とは言っても、どうやら中途半端な方法に固執なさっているようなので・・
添付図は、A5:A20を対象の表として、フィルターをかけた際のC列の値別の出現数をカウントするものとしてあります。
　・図の左側が、全データの例
　・図の右側が、Ａ列でAとCのみをフィルターで抽出した場合の例
図では、B2セルに以下の数式を入力し、B2:D2にフィルコピーしてあります。
=SUMPRODUCT(SUBTOTAL(102,OFFSET($C4,ROW(A1:A16),0))*($C5:$C20=B1))

※ 動作確認は、全く同じデータ形式を作成の上で行ってください。
　（当方は、Excel2019で確認しています）
※ 前回も書きましたが、数式の意味を理解せずにいい加減に使ったところで、求める結果は得られることはありません。
※ SUBTOTAL関数には欠点があり、フィルター操作を行っている限りは正しい結果を示しますが、フィルターをかけた状態から「フィルタ解除」で全データを表示した際に再計算をしてくれません。
（非表示だった行が表示されても、表示行としてカウントされません）
この場合に限って、手動で再計算させる必要があります。
（これは複合関数だけでなく、単体の関数でもそのような仕様です）

この回答へのお礼

ありがとうございました。何度もすみませんでした。
同じ表でやってみました。
バッチリでした。
基本がわかってないので、ご迷惑をおかけし、誠に申し訳ありませんでした。
ご忠告通り、正式にはフィルターのない形にして、まとめたいと思いますが表の傾向を、早期につかみたいため検討段階では使わせていただきたいと思っています。
本当にありがとうございました。
今後ともよろしくお願いいたします。

お礼日時：2024/11/01 03:40