100行50列の表で、1～40列でフィルターをかけた時41～50列での内容別の数量確認をする方法

B20：AY120の　50列100行　の表で、1～40列で何らかのフィルターをかけた時、41～50列の特定の内容の数量（個数）を一気に見たいのですが、何か良い方法はないでしょうか？
1～40の列にはいろいろな項目に対する内容が書かれていて、41～50の列では、1.2.3.”--”.及び空白となっています。1～40列で何らかのフィルターをかけた時、41～50列の表に出てきた分（フィルターで隠れていない分）の内、1の数量、2の数量、3の数量、および”--”の数量を常時表に出したいのですが何か良い方法がないでしょうか？
51列目の各行に1を入れ、SUBTOTAL(9,VS21:VS10000)の表からフィルターをかけた時の数量を特定し、マクロで出す方法はあるのですが、時間がかかりすぎるので、一瞬で表にできる関数があればありがたいのです。
この表の行が20行目から始まるので、上が19行空いています。そこで41～50列の1～20行を利用して別表を作り、各フィルターをかけた時に41から50列の内容が決まるので、その決まった内容で、各列の1の数量、2の数量、3の数量および--の数量を出したいのです。
COUNTIFS　で出す方法も、フィルターが色々代わるので、その都度関数を変えないとだめなので、困っています。
何か良い方法はないでしょうか？

要は、1から40列でフィルターがかかっているとき、その内容がどうあれ、41～50列の表に出た分だけを計算する式があればよいのですが・・・。
SUBTOTAL(9,VS21:VS10000)のような式で、1.2.3を分けて出す式があればよいのですが・・・

尚、フィルターを掛けた時、41～50の表には、1.2.3--が全て混ざって入っているので、
SUBTOTAL(9,VS21:VS10000)では、わからないのです。

このフィルターをかけた内容を別シートにコピーし、フィルターがかかっていない表にしたうえで、1.2.3を求める方法もできますが、これも時間がかかり困っています。
よろしくお願いいたします。

No.3 ベストアンサー 回答者： fujillin 回答日時： 2024/01/31 18:49

No2です。

連投失礼。
よく考えたら、「作業行」は不要でした。失礼いたしました。
（B1の値はSUBTOTAL関数で求められますので・・）

改めて、
B1セルに以下の式を入力し、B3セルまでフィルコピー。
=IFERROR(SUMPRODUCT((AGGREGATE(15,7,B$20:B$120,ROW(OFFSET($A$1,0,0,SUBTOTAL(102,B$20:B$120))))=ROW(A1))*1),0)

その上で、B1:B3を右方にフィルコピーすれば同様の結果を得られます。

この回答へのお礼

ありがとうございました。
非常に難解ですが、答えはバッチリあっていました。
最高です。以前から　fujillin　さんには何度も難しいのを解決していただいています。
まことに何とお礼申し上げればよいかわからないほどです。
本当に感謝です。
これからこの式の意味を調べて今後に役立てたいと思います。
ありがとうございました。

お礼日時：2024/01/31 20:54

No.2 回答者： fujillin 回答日時： 2024/01/31 17:36

こんにちは

なさりたいことがよくわからないのですけれど・・

＞SUBTOTAL(9,VS21:VS10000)のような式で、
＞1.2.3を分けて出す式があればよいのですが・・・
式の雰囲気等から、各列ごとに指定数と一致する個数を算出したいものと解釈しました。
（合計を算出したい場合は、個数に指定数を乗算すれば得られます）

＞この表の行が20行目から始まるので、上が19行空いています。
であるなら、各列毎に計算してしまえばよさそうに思われます。
例えば、A2:A5に1、2、3、--を入力しておいて、B2:AY5に各列の個数が表示されれば良いということでしょうか？

ご質問文には明示されていませんけれど「フィルター云々」とあるので、非表示行はカウント対象にしないということと解釈しましたが、関数で非表示行を判断可能なのはSUBTOTAL関数とAGGREGATE関数だけですが、これらは基本的には数値計算用の関数ですので、これを用いて算出しようとするといくつかの制約が出ます。
・配列計算になるため、計算が重くなります。
　フィルターを変更すると全セルが再計算になるので、若干時間がかかる
　可能性があります。（PCの性能によります）
・数値以外（ご例示の「--」等）をカウントするには、別計算をする
　必要があります。
　（例えば、--は「-1」としてユーザ定義書式で「0_ ;"--";0;@」として
　　表示上は「--」とするなどとしておけば、同じ式で全部の計算が可能です）

計算の要領の例を以下に。
例えば、B1セルに（仮に1行目を作業行とします）
=SUMPRODUCT((1-ISERROR(AGGREGATE(15,7,B$20:B$120,ROW(A$1:A$101))))*1)
を入力すると、B列の「表示行のうち数値であるものの数」が表示されます。
これを利用して、
B2セルに
=IFERROR(SUMPRODUCT((AGGREGATE(15,7,B$20:B$120,ROW(OFFSET($A$1,0,0,B$1)))=ROW(A1))*1),0)
の式を入力すれば、値が「1」であるもの個数が算出されます。
B2セルをB4セルまでフィルコピーすると、値が1～3までの個数がそれぞれ表示されます。

B1の式をB2以下の式に組み込んで、作業行を無くすことは可能ですが、更に計算が重くなるので、こちらの方法にしてあります。
これらの式を右方にフィルコピーすればそれぞれの列で、「表示行のうちで1、2、3の値の個数」が求められます。

「--」のカウントは上記の数値化をすれば「-1」の数を数えれば良いので同じ要領で可能です。
文字列のままで行いたければ、SUBTOTAL関数でCOUNTAからCOUNTを減ずれば求められるでしょう。
ただし、この式は「表示されている値のあるセル数から数値のセル数を減ずる」という計算になりますので、「--」を特定してカウントしてはいません。
ですので、仮に「aa」やスペースだけのセルがあってもカウントしてしまいます。


関数だとかなりかったるいので、ユーザ定義関数を利用すれば簡単にすることは可能です。
例えば
　=KEISAN(対象セル範囲, カウントしたい値）
のような関数にできますので、シート上の式はとてもスッキリとします。
ただし、質問者様がVBAを扱えるという別の条件が付いてしまいますけれど・・

この回答へのお礼

ありがとうございます。
質問の仕方がまずくて申し訳ありません。
ご記載の解釈内容はその通りですので、一度これでやってみます。
ありがとうございました。

お礼日時：2024/01/31 20:25

No.1 回答者： 河内のおやじ 回答日時： 2024/01/31 14:07

一番簡単な方法は、別シートに=で必要列を持って来ると良いですよ。

簡単にできます。

この回答へのお礼

ありがとうございます。
【＝で持ってくる】というのは、別シートに”＝”を使って計算式を移すようにするということでしょうか？
その時の計算式はどいうすればよいのでしょうか？
ごめんなさい。ちょっと私ではわかりませんでした。
すみません・・。

お礼日時：2024/01/31 14:27