エクセルLARGE関数について

質問

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

回答No.3

maron--5 · ベストアンサー

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です）を変更すると、○番目の数字になります

お礼

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

回答No.2

Wendy02

こんばんは。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))

お礼

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

回答No.1

Wendy02

こんばんは。

たとえば、

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に数字がある場合どうすればいいでしょうか。