オートフィルタで抽出された結果で２番目に大きな値

エクセル初心者です。
オートフィルタで抽出された結果の中で２番目に大きな数字を別のセルに
表示させたいと思っています。

担当者　数字　
　Ａ　　　　1　
　Ｂ　　　　2
　Ｃ　　　　3
　Ａ　　　　4

上記のような表にオートフィルタをかけ、担当者Ａを抽出します。
Ａの数字の中で2番目に大きな数字は1ですので、別のセルには1と表示させたいと思います。
SUBTOTAL関数とLARGE関数の組み合わせができればと思いましたが、できそうにございません。
良い方法があればご教示よろしくお願い致します。

No.3 ベストアンサー 回答者： keithin 回答日時： 2011/12/23 19:57

Ｂ列に数字として

２番目は
=LARGE(INDEX(SUBTOTAL(9,INDIRECT("B"&ROW(B1:B99))),),2)
１番は
=LARGE(INDEX(SUBTOTAL(9,INDIRECT("B"&ROW(B1:B99))),),1)
でいいです。

No.4 回答者： yubo 回答日時： 2011/12/23 23:19

こんにちは

オートフィルタ無しでも動作します。
添付画像のような状態だとしてE2セルに以下の関数式を設定します。

｛=IF(COUNTIF($A$2:$A$13,D2)>=2,LARGE(IF($A$2:$A$13=D2,$B$2:$B$13,""),2),"データ無し")｝

尚、担当者Eのように１件しか数字がない場合、データ無しを表示します、その可能性がない場合は下記です。

｛=LARGE(IF($A$2:$A$13=D2,$B$2:$B$13,""),2)｝

最大値が二つの場合その数字、2番目の数字が複数あってもその数字となります。
下方に複写できますが、配列数式ですのでご注意ください。

この回答へのお礼

ありがとうございます。できました！
＞最大値が二つの場合その数字、2番目の数字が複数あってもその数字となります。
今回の場合のLARGE関数は1位の数字が複数ある場合、正確な値が出ないのですね。

お礼日時：2011/12/25 23:29

No.2 回答者： MackyNo1 回答日時： 2011/12/23 14:26

数字データがB2セル以下に入力されているなら、以下のような数式でフィルタした結果の2番目に大きい数を表示できます。

=LARGE(INDEX(SUBTOTAL(3,INDIRECT("B"&ROW(B2:B100)))*B2:B100,),2)

この回答へのお礼

ありがとうございます。INDEX関数、INDIRECT関数を初めてみました。
勉強になりました。

お礼日時：2011/12/25 23:27

No.1 回答者： mike_g 回答日時： 2011/12/23 13:59

「オートフィルタをかけ」た結果から計算する方法は私も知らないけど、「オートフィルタをかけ」た条件を利用すれば計算可能です。

この回答へのお礼

ありがとうございます。
エクセルは難しいですね。どの関数をどう使えば良いのか思いつかないのです。

お礼日時：2011/12/25 23:25