[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『区分毎のランク付け（同順位飛ばさない）』（急）
区分毎に大きい順にランク付けする。
ただし、同順位は飛ばさない。
例）
A列-B列-C列
a-10-3
a-30-2
a-30-2
a-50-1
b-40-1
b-20-3
b-40-1
b-30-2
Excel2010 です。
Ａ列の区分は約４０種類、Ｂ列の値は１０００個程です。
データ数量の可変に対応したいです。
掲示板をざくっと見ましたが、応用できなくて．．
かなり解決を急いでおります。
よろしくお願いいたします。
例えば、Ａ列に２０個データがあるとして、
区分なしの場合、以下の式でランク付けできます。
SUMPRODUCT(($A$1:$A$20>=A1)/COUNTIF($A$1:$A$20,$A$1:$A$20))
これの応用ができないかと思っています。
（急）
C1セル =SUM(SIGN(FREQUENCY(IF((A$1:A$20=A1)*(B$1:B$20>=B1),B$1:B$20),$B$1:$B$20)))
※数式の入力は、CtrlとShiftキーを押しながら、Enterで入力確定する必要があります。（つまり、配列数式) その後、下の行にフィルコピー
<結果図> 行 _A_ _B_ _C_ 1 a 10 3 2 a 30 2 3 a 30 2 4 a 50 1 5 b 40 1 6 b 20 3 7 b 40 1 8 b 30 2
(半平太) 2013/07/14(Sun) 12:32
見事にできました！
式の意味は未だチンプンカンプンですが(笑)、これから
学習したいと思います。
これを配列数式を使わずにできるようでしたら、
またお教え願います。
(急)
＞これを配列数式を使わずに
こんなので試したらいけるみたいです。(検証はそちらでやってください） ↓ =SUM(SIGN(FREQUENCY(IF(MMULT((A$1:A$20=A1)*(B$1:B$20>=B1),1),B$1:B$20),$B$1:$B$20)))
(半平太) 2013/07/14(Sun) 14:47
配列数式はＰＣに負担をかけ処理時間が遅くなると
何かで見た覚えがあり、心配していました。
ご提案いただいた両方の式で検証させていただきます。
それにしても、こういう式がすぐに浮かぶとは．．敬服いたします。
本当にありがとうございました。
(急)
＞配列数式はＰＣに負担をかけ処理時間が遅くなる
その話は単純すぎます。
少なくとも今回の２式を比較すれば、 最初の方が負担が少ないと思います。
冒頭の議論は、作業列を使えば１回計算すれば済むところ、 無理に作業列を使わないで算出しようとして同じ計算を 各数式に強制するような時なんかで出てくる話だと思います。
(半平太) 2013/07/14(Sun) 19:15
そうでしたか．．知りませんでした。
何事もうわべだけで理解したつもりになっては
いけないということですね？
自分の苦手な分野は、つい思考停止しちゃって．．
アドバイスありがとうございます！
（急）
半平太さんと考えはそない違いはないです。
=COUNT(0/FREQUENCY((A$1:A$20=A1)*(B$1:B$20>B1)*B$1:B$20,B$1:B$20))
(GobGob)
>これを配列数式を使わずにできるようでしたら、またお教え願います。
素直に作業列を作ればいいのでは？
A B C D 1 a 10 a 3 2 a 30 a 2 3 a 30 2 4 a 50 a 1 5 b 40 b 1 6 b 20 b 3 7 b 40 1 8 b 30 b 2
C列作業列、D列にランク。
C1 =IF(COUNTIFS(A$1:A1,A1,B$1:B1,B1)=1,A1,"") D1 =COUNTIFS(C:C,C1,B:B,">="&B1)
C1:D1 下へコピー。
追記
＞冒頭の議論は、作業列を使えば１回計算すれば済むところ、 ＞無理に作業列を使わないで算出しようとして同じ計算を ＞各数式に強制するような時なんかで出てくる話だと思います。
半平太さん回答で議論されてましたか。 ごもっともですねぇ。
(GobGob)
=COUNT(0/FREQUENCY(．．の「０／」のところがよくわかりません。（涙）
（FREQUENCY関数も未だ理解できてませんが．．（汗））
「ゼロを割る？？．．」
他の人の掲示板でもこの表現を見かけた記憶があるのですが、
よろしければ式の意味をご教授願えませんか？
よろしくお願いします。
(急)
FREQUENCYのヘルプを見れば一発だと思うんだけど・・・
FREQUENCYは頻度分布を配列として返す。
FREQUENCY(データ配列, 区間配列)
データ配列の中に区間配列の数値範囲がどれだけの頻度で発生しているか？を返す。
例
データ 区間 結果 → FREQUENCY関数は「配列」で結果が返る 1 1 2 → 1以下の頻度を返す 1 3 5 → 2〜3の頻度を返す 2 5 2 → 4〜5の頻度を返す 2 7 0 → 6〜7の頻度を返す 3 9 0 → 8〜9の頻度を返す 3 0 → 10以上の頻度を返す 3 4 5
今回の場合は区間はB列、データ範囲が「指定されたA列区分かつそのB列の数値より大きいB列範囲」となるので
A列 B列(区間) データ…(A列範囲=A列)*(B列範囲>B列)*B列 結果 補足：1以上の数値
a 10 {0;30;30;50;0;0;0;0;0;0} {7;2;0;1;0;0;0;0;0} 3
a 30 {0;0;0;50;0;0;0;0;0;0} {9;0;0;1;0;0;0;0;0} 2
a 30 {0;0;0;50;0;0;0;0;0;0} {9;0;0;1;0;0;0;0;0} 2
a 50 {0;0;0;0;0;0;0;0;0;0} {10;0;0;0;0;0;0;0;0} 1
b 40 {0;0;0;0;0;0;0;0;0;0} {10;0;0;0;0;0;0;0;0} 1
b 20 {0;0;0;0;40;0;40;30;0;0} {7;1;0;0;2;0;0;0;0} 3
b 40 {0;0;0;0;0;0;0;0;0;0} {10;0;0;0;0;0;0;0;0} 1
b 30 {0;0;0;0;40;0;40;0;0;0} {8;0;0;0;2;0;0;0;0} 2
0 {0;0;0;0;0;0;0;0;0;0}
0 {0;0;0;0;0;0;0;0;0;0}
こんな結果が返る。んで頻度数なんでこの結果の1以上の数値数をカウントすればいいんだけど COUNT関数は0もカウントしてしまう。
なので 0/FREQUENCY(…) で、FREQUENCYの結果が0の場合はエラーにしてるんですな。 COUNT関数はエラー値はカウントしないので結果 FREQUENCYの1以上の数値をカウント。となるんですわ。
(GobGob)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.