ある値に対して3番目に大きな値を、なければ2番目に大きな値、それもなければ1番大きな値を表示させたい

表示させたい値は日付なのですが、タイトルの通り、

★指定の文字（A列の値）に対して日付（B列の値）をとってきたいのですが、
　まず3番目に大きな値（直近の日付）を
　なければ2番目に大きな値を
　それもなければ1番大きな値を表示させたいです。


【例】
指定の文字：E1セルの値【1AA】
に対して、
とってきたい値（F1セル★に表示）：3番目に大きな値（セルA10）の【10/3】

　　A　　　　B　　　　C　　D　 　E　　　F
1　1AA　　10/31　　　　　　　　1AA　　★
2　2AB　　10/31
3　4DD　　10/30
4　3RY　　10/27
5　1AA　　10/25
6　3EO　　10/20
7　445　　10/15
8　4DD　　10/15
9　2AQ　　10/3
10　1AA　　10/3


もし指定の値が【4DD】であれば取ってきたい値は【10/15】になり
【3RY】であれば【10/27】しかないのでそれをとってきたいです。



検索や過去の質問から、下記数式をいれることで
2番目に大きな値をもってくるまではなんとかできたのですが

★=IFERROR(AGGREGATE(14,6,1/(1/((MAX(FILTER(B:B,A:A=E1))<>FILTER(B:B,A:A=E1))*(FILTER(B:B,A:A=E1)))),1),MAX(FILTER(B:B,A:A=E1)))

3番目をもってくる関数を含めることができませんでした。


よく理解できていないせいで混乱してしまっております・・
知識のある方、どのような数式にすればよいか、ご教授いただければ幸いです。

よろしくお願いいたします。

No.3 ベストアンサー 回答者： goomania 回答日時： 2023/10/26 18:22

No.1です。

大変失礼いたしました。
No.２さんの回答の説明を読んで気付きました。
前回投稿の数式は、

>まず3番目に大きな値（直近の日付）を、なければ2番目に大きな値を、
>それもなければ1番大きな値を表示させたい

というご質問者のご希望を

◎3番目が最も小さい日付であり、それより小さい日付はない

と勝手に思い込んで作成してしまいました。

当然、4番目に大きな日付があっても３番目の日付を返すという意味に解釈すべきでした。

F１セルに、

=IFERROR(AGGREGATE(14,6,B:B/(A:A=E1),MIN(3,COUNTIF(A:A,E1))),"")

という数式を入れて、表示形式を日付にするという方法に訂正いたします。

前回の数式はFILTER関数、SORT関数の使えるバージョンのものでしたが、今回の数式は2010以降であれば使用可能です。

誤った、数式を投稿し、ご迷惑をおかけいたしましたことをお詫び申し上げます。

この回答へのお礼

激務のあと病床に伏しておりお返事がかなり遅くなってしまいました、、
誠に申し訳ございません。

そして、いち早く助けていただき、大変助かりました・・・！！
あのあとすぐに、こちらのNo.1様（No.3）の数式を使用させていただきまして、業務が進みました（T-T）
他の方の回答まで細かく見て下さり、ご丁寧に訂正下さって、本当にありがとうございました。

また、ご説明も感謝しております><おかげさまで知識が増えました。勉強になります。

こちらこそ、御礼が大変遅くなってしまったことお詫び申し上げます・・
またご縁がありましたらどうかご助力いただけたら幸いです><本当にありがとうございました。

お礼日時：2023/11/07 13:41

No.2 回答者： fujillin 回答日時： 2023/10/26 16:43

こんにちは

＞まず3番目に大きな値（直近の日付）を
＞なければ2番目に大きな値を
時系列で並べた際に、3番目に新しい日付（なければ2番目、1番目）という意味ですよね？
4個以上存在しても、3番目の日付を取得するという意味と解釈しました。

上記の判断のみで計算してよいものとして・・
（直近かどうかの判断はしていません）

スピル機能を使える環境であれば、Ｆ1セルに
　=INDEX(SORT(FILTER(B:B,A:A=E1,""),1,-1),MIN(COUNTIF(A:A,E1),3))
を入力して、書式を日付にしておけば可能と思います。

使えない環境でも可能ですが、同じことをやるのにそれなりに面倒な式になります。

この回答へのお礼

激務のあと病床に伏しておりお返事がかなり遅くなってしまいました、、
誠に申し訳ございません。

そして、早々に助けていただき、大変助かりましたT-T
おかげさまであの後、業務がスムーズにいきました。
実はNo.2様には何度も助けられておりまして、コメント頂いたときは勝手に安心感を覚えました、、
相変わらず天才です。
いつも当方の意図を正確に細かくくみ取って下さり、ご丁寧にご教授いただき、本当に感謝しております。

またご縁があればぜひぜひぜひよろしくお願いいたします。。<(_ _)>
ありがとうございました><

お礼日時：2023/11/07 12:33

No.1 回答者： goomania 回答日時： 2023/10/26 16:37

ご質問者のEXCELのバージョンが不明ですが、FILTER関数が使用できるようなので、SORT関数も使用できると判断しました。

F1セルに、

=IFERROR(INDEX(SORT(FILTER($B:$C,($B:$B=E1)),2),1,2),"")

というう数式を入れる方法でいかがでしょうか。

E1セルに入れた値に該当するA列の値がないときは「空白」が返ります。