[[20210304152157]] 『カウントの方法』(めいぷる) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『カウントの方法』(めいぷる)

 関数でのカウント方法についてご教示ください。

 以下のような表があります。(58000行ほど)
 D列のname2が同じものについて、A列のID1の種類がいくつあるかをE列にカウントしたいのですが、どのような関数がありますでしょうか。

 sumproductを使うのかと思いましたが、
 =SUMPRODUCT($D$2:$D$10=D2,$A$2:$A$10=D2) では
 「name2が"あああ"でID1が"0001"の件数」となってしまいます。

 なお、表はD列の昇順で並んでおり、A列のID1は7行目のように複数箇所に出てくる可能性がありますが、
7行目の"0001"は「name2が"あああ"であるうちのID1の件数」には含まないでほしいのです。

 E列にカウントの結果が欲しいのですが、name2が同じ行については全て同じ結果が返ると思います。(★の行にのみ表示でも問題ありません)

 	A	B	C	D	E	
 1	ID1	name1	ID2	name2	count(欲しい結果)	
 2	0001	あああ	0001	あああ	5 ★
 3	0002	いいい	0002	あああ	5
 4	0002	いいい	0010	あああ	5
 5	0020	えええ	0020	あああ	5
 6	0050	おおお	0050	あああ	5
 7	0001	あああ	0130	かかき	2 ★
 8	0130	かかき	0130	かかき	2
 9	0003	けけけ	0003	けけけ	1 ★
 10	0003	けけけ	0003	けけけ	1

 関数得意の皆様、どうぞよろしくお願いします。
 (office365ですが、使えない関数もあるようです。先日LET関数を教えて頂きましたが、使えませんでした。)

< 使用 Excel:Office365、使用 OS:Windows10 >


 2行目から6行目は4として(ID1:0002がダブっているので)
 E2セルに
 =COUNT(0/FREQUENCY(IF(D$2:D$58000=D2,MATCH(A$2:A$5800,A$2:A$5800,0),""),ROW($1:$5800)))
 と入力して下へフィルコピーではどうだろうか?
(ねむねむ) 2021/03/04(木) 16:56

 回答ではないのですが

 >office365ですが、使えない関数もあるようです。

 ↓ここから、最新状態に更新できませんか?
 ファイル-その他のオプション-アカウント-製品情報

(マナ) 2021/03/04(木) 17:18


 すまない。
 =COUNT(0/FREQUENCY(IF(D$2:D$58000=D2,MATCH(A$2:A$58000,A$2:A$58000,0),""),ROW($1:$58000))) 
 だった。
 ただ、58000行まで式を入れるととても重くなりそうなので使わないほうがいいかも。
(ねむねむ) 2021/03/04(木) 17:31

 質問だがD列のname2で同じものは最大何個あるのだろうか?
(ねむねむ) 2021/03/04(木) 17:36

 D列で同じものが100個以内の場合。
 E2セルに
 =IF(COUNTIF(D$2:D2,D2)=1,COUNT(0/FREQUENCY(IF(D2:D101=D2,MATCH(A2:A101,A2:A101,0),""),ROW($1:$100))),"")
 と入力して下へフィルコピーではどうだろうか?
 なお、この式は★の行のみ表示となる。
(ねむねむ) 2021/03/04(木) 18:14

 ねむねむ様

 早々にご回答頂きながら返信が遅くなりました。
 最初に回答頂いた式を試したところExcelがフリーズしてしまい、
 PCに負荷がかかったことで、在宅ワークの回線状況が不安定になり
 ネットワークへの接続が断続的になったため、返信できませんでした。

 今朝、二つ目にご提示頂いた式を試したところ、時間はかかりましたが無事期待の結果が得られました。
 ありがとうございました。
 式を見てなんとなく意味は把握できました。数式の検証もしてみたのですが、"COUNT(0/FREQUENCY(IF(D2:D101=D2,MATCH(A2:A101,A2:A101,0),""),〜"の部分で理解に自信の無いところがあります。

 以下、数式の検証で理解した内容ですが、
 ・「D2:D101=D2」でD2と同じ場合にtrueが返る→D2:D6の5個分
 ・FREQUENCYのデータ配列の範囲で「MATCH(A2:A101,A2:A101,0)」は検索値と検索範囲が同一なので、全て結果が返る
 ・「IF(D2:D101=D2,MATCH(A2:A101,A2:A101,0),"")」の結果として、name2が一致するD2:D6のMATCHの結果が"1,1,3,4,5"と返る
 ・「FREQUENCY(IF(D2:D101=D2,MATCH(A2:A101,A2:A101,0),""),ROW($1:$100)))」の結果として、ROW($1:$100)の範囲の最初の5行は"2,0,1,1,1"が帰り、他は"0"となる
 ・「COUNT(0/FREQUENCY」 まず"0/FREQUENCYの結果"としているため、FREQUENCYの結果で2と1が返ったところは0になり、0と""が返ったところは"#DIV/0!"のエラーとなる。
 これをカウントすると、0の部分のみカウントされ、4が返る
  →A列に同一IDがあった場合、FREQUENCYの結果は2以上になるため、"0/"とすることで件数の返った部分を全て"0"に統一しかつ"0件または対象外(空白)"の部分は"#DIV/0!"となるため、
 最後にCOUNTで0の数値部分のみを数えているという理解で正しいですか?

 マナ様
 気にとめて下さりありがとうございます。
 会社のPCで、更新等は全て情シスに管理されておりますため、残念ながら各自での対応は出来ないのです。

(めいぷる) 2021/03/05(金) 11:25


 数式の理解はそれでOK。
 なお、365以前のバージョン(2019もそうかはわからない)の場合は式を確定する際にShiftキーとCtrlキーを押しながら
 Enterキーを押さないと正しい結果が返らない。

(ねむねむ) 2021/03/05(金) 11:43


 あと、最初の式については始め、20行程度の表で試して、あとから58000行に入力に気づいて一応警告を付けたのだが
 環境を不安定にさせてしまったようですまない。
(ねむねむ) 2021/03/05(金) 11:43

 おっと一つだけ。
 >・「FREQUENCY(IF(D2:D101=D2,MATCH(A2:A101,A2:A101,0),""),ROW($1:$100)))」の結果として、ROW($1:$100)の範囲の最初の5行は"2,0,1,1,1"が帰り、他は"0"となる

(ねむねむ) 2021/03/05(金) 11:54


 なので
 >・「COUNT(0/FREQUENCY」 まず"0/FREQUENCYの結果"としているため、FREQUENCYの結果で2と1が返ったところは0になり、0と""が返ったところは"#DIV/0!"のエラーとなる。
 ""が返ることは無いことになる。
(ねむねむ) 2021/03/05(金) 11:54

 あ、あと細かいが0/""の結果は#DIV/0!ではなく#VALUE!のエラーとなる。
(ねむねむ) 2021/03/05(金) 11:56

 ねむねむ様

 数式の理解が合っていたようでよかったです。
 細かい訂正もありがとうございます。
 何度も数式の検証を見てやっと理解できたレベルなので、自分ではまだまだ思いつけないです。
 特に配列を使ったものは難しい・・・

 環境は、もともと大きめのファイルを開けているとすぐ不安定になるので、
 今回の関数だけのせいではないのでお気になさらず。

 今後ともよろしくお願いします。
(めいぷる) 2021/03/05(金) 13:14

コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.