2つのセルに入力された異なる数値同士を比較して、もっとも近い値のある行

質問

2つのセルに入力された異なる数値同士を比較して、もっとも近い値のある行を検索する。

EXCELに関して表題の件で皆様の助言を何卒よろしくお願いします。
以下のようなサンプルがあるとします。

【A表】
#　　　　　x　　　　　　y
1　　　　　13　　　　　712
2　　　　　22　　　　　1277
3　　　　　22　　　　　1559
4　　　　　22　　　　　2158
5　　　　　30　　　　　692
6　　　　　33　　　　　2294
7　　　　　34　　　　　1205
8　　　　　36　　　　　177
9　　　　　40　　　　　1851
10　　　　　52　　　　　762
・
・
・
・

これに対しての検索するデータが以下です。

【B表】
2#　　　　　2X　　　　　　2Y
2　　　　　17.38　　　　421.735
3　　　　　29.504　　　419.711
4　　　　　12.254　　　419.922
5　　　　　32.509　　　417.048
6　　　　　32.412　　　415.276
7　　　　　32.131　　　413.79
8　　　　　28.439　　　416.012
9　　　　　28.105　　　172.226
10　　　　38.55　　　　262.927
13　　　　58.727　　　10.303
14　　　　50.327　　　11.811
15　　　　35.978　　　104.821
・
・
・
・

A表とB表のそれぞれ　【x　⇔　2x】　【y　⇔　2y】　の部分と比較し
もっとも近い値から上位3つくらいまで一致したA表の行を抽出するための
関数、マクロを作成したいと思っていまして、過去ログ等調べて
色々試してみましたがなかなかうまくいきません。

【x　、　y】と【2x　、　2y】はそれぞれ同じ行同士の物で検索しなければならないというのが
ネックになっているのでしょうか？
あとA表、B表の総項目数は一致していません。

もっとも近い値を検索し、その行の【#】の数値を抽出するという関数は
過去ログから検索して作成することは出来たのですが、
2番目、3番目に近い値を検索し、#の数値を抽出することが出来ません。

言葉足らずで理解に苦しむかと思われますが、何卒皆様のご助力を
よろしくお願い致します。

※本文では分かり辛いので画像添付しました。ご参照下さい。

回答No.2

MackyNo1 · ベストアンサー

＞=MIN(IF(MIN(ABS($G$2:$G$1000-B2))=ABS($G$2:$G$1000-B2),$F$2:$F$1000))

この式で最も近いセルがうまく表示できるということなら、以下の式で2番目、3番目の数値を求めることができます。

=MAX(IF(SMALL(ABS($G$2:$G$1000-B7),2)=ABS($G$2:$G$1000-B7),$F$2:$F$1000))

=MIN(IF(SMALL(ABS($G$2:$G$1000-B7),3)=ABS($G$2:$G$1000-B7),$F$2:$F$1000))

お礼

MackyNo1様
ご返答ありがとうございます。

なるほど、SMALL関数をそこに入れて、頭をMIN→MAXに変更したのですね。
全く思いつきませんでした。大変参考になります。

この式を参考にさせて頂いて進めてみたいと思います。
ありがとうございました。

回答No.4

MackyNo1

>なるほど、SMALL関数をそこに入れて、頭をMIN→MAXに変更したのですね。

少し複雑なので補足すると、基本はすべてMIN関数でできるはずですが、差の絶対値が同じデータが2つある場合は、数式で得られた配列に該当する数値が2つあるので、MINでは同じデータがヒットしてしまいます。

すなわち最小値（一番小さい差）と2番目に小さい差のデータが同じ場合は、いずれもMINで計算すると、2番目のデータは結局最小値のデータと同じ結果を返しますのでMAX関数を使う必要があるわけです。

同様に、差が2番目に小さいデータと3番目のデータが同じ値となった場合は、どちらもMAXまたはMINでは同じ値を返します。
それを避けるために、2番目のデータの表示でMAXを使ったので、3番目のデータの表示にはMIN関数を使用しています。

回答No.3

nag0720

＃１です。

問題は、２つの項目同士を比較して、「近い」ということをどう判定するかでしょう。

補足に書かれた式では、x,yそれぞれで近い行を調べて、それが同じ時としていますが、
懸念されているように、違った場合は困ってしまいます。

通常は、(x,y)を平面座標にプロットして、その距離が近いかどうかを調べる方法でしょう。
(x,y)と(x2,y2)の距離は、√{(x-x2)*(x-x2)+(y-y2)(y-y2)}で計算できます。

この方法でマクロを作成すると、下記のようになります。
（距離は比較するだけなので、ルート計算はしていません）

Sub Test()
Dim i As Integer, j As Integer, r1 As Integer, r2 As Integer
Dim x As Double, y As Double, x2 As Double, y2 As Double
Dim S As Double, S1 As Double, S2 As Double, S3 As Double
Dim n1 As Integer, n2 As Integer, n3 As Integer

r1 = Cells(Rows.Count, 1).End(xlUp).Row ' A表の最終行
r2 = Cells(Rows.Count, 6).End(xlUp).Row ' B表の最終行
For i = 2 To r1
x = Cells(i, 2)
y = Cells(i, 3)
S1 = 0
S2 = 0
S3 = 0
n1 = 0
n2 = 0
n3 = 0
For j = 2 To r2
x2 = Cells(j, 7)
y2 = Cells(j, 8)
S = (x - x2) * (x - x2) + (y - y2) * (y - y2) ' 距離の計算
If S < S1 Or n1 = 0 Then
S3 = S2
S2 = S1
S1 = S
n3 = n2
n2 = n1
n1 = Cells(j, 6)
ElseIf S < S2 Or n2 = 0 Then
S3 = S2
S2 = S
n3 = n2
n2 = Cells(j, 6)
ElseIf S < S3 Or n3 = 0 Then
S3 = S
n3 = Cells(j, 6)
End If
Next
Cells(i, 4) = n1 & "," & n2 & "," & n3 ' D列へ書き込み
Next
End Sub

実際に示されたデータで実行してみると、ほとんどが2,3,4か2,4,3になっていますが、
これは、yの値がどのy2よりも大きいためのようです。

もし、この結果が希望どおりではない場合は、距離の算出式を変えるという方法もあります。

お礼

nag0720様
ご返答ありがとうございます。

恥ずかしながらほとんど理解出来ていないのですが
こういう方法もあったのかと大変勉強になります。

提示して頂きました回答ですと、確かにx,yごとでそれぞれ
簡単にランク付け出来るようにデータが出たので
比較が出来るようになりました。

深夜にも関わらずご返答頂きましてありがとうございました。

回答No.1

nag0720

＞もっとも近い値を検索し、その行の【#】の数値を抽出するという関数は
＞過去ログから検索して作成することは出来たのですが、

その作成したものを提示してくれませんか？

補足

nag0720様
お世話になります。

過去ログではなく他のHPからの流用でした。
試してみたのが以下の式です。
ｘとｙとそれぞれ個別に当てはめて求めて2つの【#】の数値が一致した物を当たりとしました。

添付した画像に対応させた式になっています。
数式の場所はどこでもいいのですが、（仮に）D2セルに

=MIN(IF(MIN(ABS($G$2:$G$1000-B2))=ABS($G$2:$G$1000-B2),$F$2:$F$1000))

これを、{Shift}+{Ctrl}+{Enter}で「配列数式」としました。

個別でやっているのでお互いに最も近い数値を認識する場所がずれてしまうことが
あるのでそれを解消出来なくて困っていました。