Excelで2次元分布データのn番目最大値を取得
Excel を使って2次元分布データを扱う際、その中からn番目に大きな値を取得したいケースがあります。
単純に最大値を取得する場合はMAX関数、n番目に大きな値を取得したい場合はLARGE関数を使用すればいいのですが、ではその値に対応する行位置と列位置を取得する場合はどうやればいいでしょうか。
例として、下のような分布の中から3番目に大きなデータを取得する場合はLARGE関数を使用します。
=LARGE(B2:G15,3)
これで “669.5” の値が取得できます。
ここで、その “669.5” に対応する行の値（1.8）と列の値（1.2）を取得する方法を考えます。
この場合、行数あるいは列数が少ない場合はVLOOKUP関数やHLOOKUP関数を組み合わせて使用するという方法があります。
この計算式はここでは省略しますが、これについてはネット上で検索すればすぐに見つかるはずです。
なので、項目数が2～3個など少ないときはそれでいいとして、問題はデータ数がかなり多くなったときです。計算式が相当に長くなってしまい、間違いがないか確認するだけでも大変になってしまいます。
もちろん Excel VBA でコードを書けば解決するんですが、わざわざそこまでしなくても何か計算式があるはず。
個人的にこういうケースでn番目最大値に対応する項目データを算出する必要があり、調べた結果を備忘録として紹介しておきます。
結論から言うと、こういう場合は「INDEX」「MATCH」を組み合わせて使用し、さらに「LAMBDA」関数も併用します。
例として、2次元データを「B2～Q56」、行データを「A2～A56」、列データを「B1～Q1」とします。
S3セルにnの値を入力し、T3セルにn番目に大きな値、U3セルにそれに対応する行データ、V3セルに列データを算出します。
計算式は、それぞれ以下の通りとなります。
T3：=LARGE(B2:Q56,S3)
U3：=INDEX(A2:A56,MATCH(TRUE,BYROW(B2:Q56,LAMBDA(a,COUNTIF(a,T3)))>0,0))
V3：=INDEX(B1:Q1,MATCH(TRUE,BYCOL(B2:Q56,LAMBDA(a,COUNTIF(a,T3)))>0,0))
これで、n番目に大きなデータ値、それに対応する行データと列データが表示されます。S3セルの値を変えるとT3～V3セルの値が変わることが確認できるはずです。n番目に小さなデータ値を取得する場合は、LARGE関数の代わりにSMALL関数を使用することになります。
この計算式のポイントはLAMBDA関数を使用すること。INDEX, MATCH を含めて、これらの関数の詳細についてはネット上で解説が見つかるので参照して下さい。
個人的な備忘録として作成したページですが、何か役に立てば嬉しく思います。
