エクセルLARGE関数について

エクセルのLARGE(SMALL)で○番目に大きい(小さい)値を表示させようとする時、重複する数字を無視した順位で表示することはできないでしょうか。
例えば、10,9,9,8,7,6,5,・・・とある場合、2番目に大きい数字は9ですが、3番目に大きい数字も9となります。これを8としたいのです。
どうすればよいでしょうか。
よろしくお願い致します。

No.3 ベストアンサー 回答者： maron--5 回答日時： 2007/03/02 04:21

　　　A　　B　　 C　　D　　E

1　　　　　10　　5　　8　　7
2　　　　　9　　 6
3　　　　　8　　 7
4　　　　　7　　 8
5　　　　　6　　 9
6　　　　　5　　10
7
8
9
10　　10
11　　9
12　　9
13　　8
14　　7
15　　6
16　　5
17　　5

◆B列に大きい数字順に表示
B1=MAX(A10:A20)
または、
B1=MAX(A:A)

B2=IF(OR(B1="",B1=MIN(A:A)),"",SMALL(A:A,RANK(B1,A:A,1)-1))
★下にコピー

◆C列に小さい数字順に表示
C1=MIN(A:A)

C2=IF(OR(C1="",C1=MAX(A:A)),"",LARGE(A:A,RANK(C1,A:A)-1))
★下にコピー

◆D1に大きい順から3番目の式
D1=LARGE(IF(FREQUENCY(A10:A17,A10:A17),A10:A17),3)

◆E1に小さい順から3番目の式
E1=SMALL(IF(FREQUENCY(A10:A17,A10:A17),A10:A17,""),3)

★式の中の最後の数字（例では3です）を変更すると、○番目の数字になります

この回答へのお礼

ありがとうございました。
とても助かりました。

お礼日時：2007/03/02 11:55

No.2 回答者： Wendy02 回答日時： 2007/03/02 02:45

こんばんは。

Wendy02です。

以下でみて分かるように、ROWの中だけが、1から始まっています。

　　ROW($A$1:$A$11)

これを、共通参照と呼びますが、ROW は、カウントをしているので、かならず、1から始まるようにします。
なお、横で使うときは、COLUMNを使います。（ROWSのところは、COLUMNS になります）

Large:
=IF(C1>ROWS($A$10:$A$20),0,LARGE(INDEX((MATCH($A$10:$A$20,$A$10:$A$20,0)=ROW($A$1:$A$11))*($A$10:$A$20),,),C1))

Small:
=IF(C1>SUMPRODUCT((MATCH($A$10:$A$20,$A$10:$A$20,0)=ROW($A$1:$A$11))*1),0,SMALL(INDEX((MATCH($A$10:$A$20,$A$10:$A$20,0)=ROW($A$1:$A$11))*($A$10:$A$20),,),SUMPRODUCT((MATCH($A$10:$A$20,$A$10:$A$20,0)<>ROW($A$1:$A$11))*1)+C1))

この回答へのお礼

同じようにやってみましたが、今度はエラーが出てしまいました。
やはり式の意味が理解できていないので、もう少し勉強します。
ありがとうございました。

お礼日時：2007/03/02 11:51

No.1 回答者： Wendy02 回答日時： 2007/03/02 00:28

こんばんは。

たとえば、

A1～A7 まで、数字があるとします。

C1 に、順位を入れるとしたら、

Large 側
=IF(C1>ROWS($A$1:$A$7),0,LARGE(INDEX((MATCH($A$1:$A$7,$A$1:$A$7,0)=ROW($A$1:$A$7))*($A$1:$A$7),,),C1))

Small 側
=IF(C1>SUMPRODUCT((MATCH($A$1:$A$7,$A$1:$A$7,0)=ROW($A$1:$A$7))*1),0,SMALL(INDEX((MATCH($A$1:$A$7,$A$1:$A$7,0)=ROW($A$1:$A$7))*($A$1:$A$7),,),SUMPRODUCT((MATCH($A$1:$A$7,$A$1:$A$7,0)<>ROW($A$1:$A$7))*1)+C1))

順位をオーバーすると、0にすることにしました。ただし、データの中に、0がないこととします。

この回答への補足

早速回答していただきありがとうございました。
A1～A7でやるとうまく行きましたが、途中の行からはじめると0になってしまいます。
式の意味を解読しようとしていますが、よく分かりません。
例えばA10～A20に数字がある場合どうすればいいでしょうか。

補足日時：2007/03/02 01:25