Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Pro : Excel 2016)
データ/フィルターで、選択後の重複しない項目をカウントしたい
投稿日時: 20/02/25 15:13:26
投稿者: まさゆり

エクエルで、フィルターで選択した後の文字や数値の行数は「 SUBTOTAL 」でカウントできますが、
複数の同じ文字をひとつとして、「A1」に数える方法を教えください
 
よろしくお願いします。
 
例えば
 4行目から9行目までフィルターで絞込した場合
 セルC1に【 =SUBTOTAL(9,(C4:C100)) 】フィルター後の平均値
 セルA1に【 3 】を表示させたい
 
 
   A   B  C
 1  3      20
 2 コード 名前  点数
 3--▼フィルター列-------(絞込)
10 12       15
12 15       22
13 12       25
14 18       18
15 12       20
 

回答
投稿日時: 20/02/25 17:24:48
投稿者: んなっと

 数式
→名前の定義
→名前:
FLT
 参照範囲:
=IF(SUBTOTAL(103,INDIRECT("A"&ROW(!$4:$100))),!$A$4:$A$100,"")
→OK

A1
=COUNT(0/FREQUENCY(FLT,FLT))
 
 
※「B列だけでフィルターをかける」のであれば、もしかしたら下の式もあるかも。
A1
=SUMPRODUCT(SUBTOTAL(103,INDIRECT("A"&ROW(4:100)))/TEXT(COUNTIFS(A4:A100,A4:A100,B4:B100,B4:B100),"[>0]0;;1"))

投稿日時: 20/02/25 19:00:16
投稿者: まさゆり

早速の回答ありがとうございます。
 
A1に、二つ目の
=SUMPRODUCT(SUBTOTAL(103, 
 の式を入れて、絞込を行うと、絞込の程度によって、途中から小数点位置までの数字が出てしまいます
 それぞれの数値をroundupすると合致します。
 しかし、絞込を少なくするにしたがって、15.3=16 15.8=16 ・・・
 と、合致しなくなります。
 
わたくしのやり方に問題があるのでしょうか
 
A1に、一つ目の一連?は、マクロでしょうか
残念ながら、マクロの勉強にとりかかったものの、諸事情により他の分野での尽力する必要性から
断念してしまった次第です。
よって、(数学が好きですので)再度過去本をひっくり返してみます。
 
私の質問に一部不足がありますので、補足いたします。
 AとB列は、関連している。
 絞込は、A、C、E列の複数列です。(現段階では、全体でN列まで)
 
よろしくお願いいたします。
 

回答
投稿日時: 20/02/25 19:12:08
投稿者: んなっと

SUMPRODUCTはフィルターのかけ方に条件が付きます。使わないでください。
名前定義はVBAではありません。
*.xlsxでも使えます。
A列が数値でないとだめですが。

回答
投稿日時: 20/02/25 19:20:07
投稿者: 半平太

別案
 
A1セルに
=COUNT(INDEX(0/(MATCH(IF(MMULT(SUBTOTAL(3,INDIRECT("A"&ROW(A4:A100))),1),A4:A100),A4:A100,0)=ROW(A1:A97)),0))

回答
投稿日時: 20/02/25 19:56:31
投稿者: んなっと

A列の数値に0がなければ、こんなのも。
 
=COUNT(0/FREQUENCY($A$4:$A$100*SUBTOTAL(103,INDIRECT("A"&ROW($4:$100))),$A$4:$A$100*SUBTOTAL(103,INDIRECT("A"&ROW($4:$100)))))-1

回答
投稿日時: 20/02/25 20:18:44
投稿者: んなっと

コードと名前が1対1の対応ではないですが、単純な例で検証しましょう。
 
     A   B   C
 1    5    27.5
 2          
 3 コード 名前 点数
 4   12  あ  15
 5   15  い  10
 6   15  あ  22
 7   12  い  10
 8   30  い  70
 9   12  あ  25
10   18  あ  18
11   12  あ  20
12   17  あ  50
13   18  あ  35
 
    ↓
 
「あ」でフィルター
 
     A   B     C
 1    4    26.42857
 2            
 3 コード 名前   点数
 4   12  あ    15
 6   15  あ    22
 9   12  あ    25
10   18  あ    18
11   12  あ    20
12   17  あ    50
13   18  あ    35
 
「い」でフィルター
 
    A   B   C
1    3     30
2          
3 コード 名前 点数
5   15  い  10
7   12  い  10
8   30  い  70
 
こうなればいいんですよね。

回答
投稿日時: 20/02/25 20:24:48
投稿者: Mike

例に依って、頗る愚直な方法です(*^_^*)
 
  A B C D E  F  G  H  I   J    K  》 CW   CX
 1 3  20   12 15  12 18  12        《
 2 Cd  Vl  0.33 1 0.33 1 0.33 #DIV/0! #DIV/0! 》#DIV/0! #DIV/0!
10 12  15                   《
11 15  22                    》
12 12  25                   《
13 18  18                    》
14 12  20                   《
15
 
1.フィルタA列を選択 ⇒ Ctrl+C ⇒ セル E1 選択 ⇒ Ctrl+Alt+V ⇒“行列の入れ替え”にチェック入れ ⇒ [OK]
2.式 =1/COUNTIF($E1:$CW1,E1) を入力したセル E2 を右方にズズーッと(CX列まで)オートフィル
3.式 =SUBTOTAL(1,C3:C100) および =AGGREGATE(9,6,E2:CW2) をそれぞれ、セル C1 および A1 に入力

回答
投稿日時: 20/02/26 09:48:57
投稿者: んなっと

前述の例で正しい結果を返すためには、
半平太さんの式であれば以下のようになると思います。
 
=COUNT(INDEX(0/(MATCH(INDEX(A4:A100/SUBTOTAL(3,INDIRECT("A"&ROW(A4:A100))),),INDEX(A4:A100/SUBTOTAL(3,INDIRECT("A"&ROW(A4:A100))),),0)=ROW(A1:A97)),0))

回答
投稿日時: 20/02/26 11:03:45
投稿者: 半平太

んなっとさん
 ご指摘ありがとうございます。m(__)m
 
まさゆり さん
 以下、修正版です。
 
A1セルに
=COUNT(0/FREQUENCY(MATCH(IF(MMULT(SUBTOTAL(3,INDIRECT("A"&ROW(A3:A100))),1),A3:A100,A3),A3:A100,0),ROW(A1:A98)))-1

投稿日時: 20/02/26 13:14:21
投稿者: まさゆり

んなっとさんをはじめ皆様にお教えいただいた関数式を
セルにコピペして、数値を数えることができました。
 
エクセル関数の式は、いろいろな方法で求めることができることは知っていますが、
FREQUENCYなど組み合わせた使い方など、皆様の式を参考に、次のステップへ再勉強させていただきます。
 
一部、質問に不足があったことをお詫びしますが、
当方の意図を組んで、的確にお教えいただきまして、
ありがとうございました。