Excelである地点に一番近い座標を検索したい

ある地点（緯度、経度）に一番近い地点を検索するためにエクセルのVBAで
実現するにはどうしたらいいでしょうか？

シート２にA列に緯度、B列に経度で表される点の集合（A1、B1）～
（A1000、B1000）があります。
シート１の（A1、B1）にある（緯度、経度）を入力した場合に、そこ
から距離が一番近い点をシート２の一覧から検索し、シート１の（C1,D1）
に表示させたいのですがどのようにすればよいか教えてください。

ちなみに座標は世界測地系となります。
よろしくお願いいたします。

質問者からの補足コメント

• 

  シート１の入力座標とシート2の座標群との距離のうち、最小値を出して、
  シート２の該当する行の（A●、B●）をシート１の（C1、D1）に表示した
  いのですが、現在VBAの勉強中でしてVBAでの記述方法が分かりません。
  VBAの記述について教えていただけますと助かります。
  よろしくお願いいたします

    補足日時：2017/09/20 07:45

No.3 ベストアンサー 回答者： fujillin 回答日時： 2017/09/20 11:38

ANo1です

＞VBAでの記述方法が知りたかったため質問させていただきました。
コードの記述法は一通りと決まっているわけではありません。
特に、ご質問のような全体的な質問だけを行うと、それを解決するプログラムを10人の人が作成すれば、十人十色で様々なプログラムができることでしょう。

それぞれが同じ答えを出しますが、考え方や実際のコードも様々です。
仮にそのうちの一つを得たところで、それがベストな解法という保証はありませんし、唯一の解法というわけでもありません。
勉強中とのことですが、（考え方は理解できているようですので）まずはご自身でコード化してみて、その上でわからないことを具体的に質問なされば、より適切な回答が得られるものと思います。

「どこから手を付けてよいのかわからない」、「サンプルが見てみたかっただけ」というのであれば、ネットで検索すれば（ご質問の問題とは異なりますが）プログラムのサンプルはいくらでも見つかるはずですし、それについての解説もされていることでしょう。
それを見て勉強になるなら、それを何度か繰り返した方が早いでしょう。

考え方がわかっているのなら、一つの方法として、処理の手順をできるだけ細分化して書いてみるという方法があると思います。
文章の方が書きやすければ文章で、フロー的に書きたければそれでも良いです（いわゆるフローチャート）
細分化してゆくことによって、ほとんどプログラムのコードと同じレベルの文章にすることも可能です。
例えば、ご質問の距離の最小値とその位置（=セルの位置）を求める手順なら、
-------------------------------------------
1）最小値を記憶する変数（例：distance）とそのセル位置を記憶する変数（例：rw)を用意する。
2）distanceには、考えられる大きな値を初期値としてセットする
（例：地球1周分の距離など）

ここまでが、準備処理です。
3）以下の4）、5）を1行目から1000行目までループ
　　4）n行目の座標値から距離を求め、それがdistanceより小さければ
　　　　5）distanceにその距離を記憶し、rwにその行番号を記憶
　　大きな場合は何もしない。
ここまでをループ

6）上記ループを終了すると、distanceに最小値が、rwにはその行位置が代入されているので、それを利用して後の処理を行う
-------------------------------------------
といったように書き表せます。
ここまでは「記述法」というよりは「処理手順を考える」話と言えるでしょう。
このくらいまで細分化しておけばほぼコード化が可能と思いますが、もう少し詳細に書き表せば、そのままコード化ができるようになるでしょう。

上記の1）～5）くらいまでの表現になれば、十人十色とはいえ、ほぼ同様のプログラムになるものと想像できます。
面倒だと思うかも知れませんが、このような手順を踏むことで、だんだん頭の中でもある程度の処理を組み立てられるようになるはずと思います。

細分化した手順を記述できない場合は、「記述法」の問題というよりも、どのようにして処理すればよいのかの考え方がわからないということになりますので、そちらを勉強なさった方が良いでしょう。
（VBAに限らず、プログラムに共通する考え方と言えます）


さて、こんなことばかり書いていても「知りたいことが何もわからない！」とおっしゃるかもしれませんので、少しだけ具体的な例を・・・
実は、上記の1）～5）で細分化していない部分があります。
それは、No1でも触れた、距離計算の部分です。
考え方はNo1にも記しましたが、それをコード化した一例を示してみます。
（以下は高校幾何レベルの計算法なので、もっと要領の良い記述法があると思います）
-------------------------------------------
DefDbl D, P, S

Function spherDistance(ByRef p1 As Range, ByRef p2 As Range) As Double
　Dim s1, s2, dx, dy, dz, p
p = WorksheetFunction.Pi() / 180 '角度変換係数（deg→rad)
Const r = 6378137 '設定半径

　s1 = Cos(p1(1).Value * p)
　s2 = Cos(p2(1).Value * p)
　dx = s2 * Cos(p2(2).Value * p) - s1 * Cos(p1(2).Value * p)
　dy = s2 * Sin(p2(2).Value * p) - s1 * Sin(p1(2).Value * p)
　dz = Sin(p2(1).Value * p) - Sin(p1(1).Value * p)

　spherDistance = 2 * r * WorksheetFunction.Asin(Sqr(dx * dx + dy * dy + dz * dz) / 2)
End Function
-------------------------------------------
2点間の距離計算の部分だけを関数spherDistanceとしたものですが、全部をVBAで処理するなら上記の4）の部分で一度だけしか利用しないので、わざわざ関数化する必要もないでしょう。
（ループ内に直接記述する方が効率的に記述できるので）
敢えて関数化したのは、VBAからも参照できる他に、ANo2様が回答なさっている、エクセルの関数利用での解法にも利用できるからです。
計算に使用している半径はgoogle map(V2の頃）で使われていた、6378137mをとりあえず設定しています。
（記事を検索してみましたが、旧いせいか見つかりませんでした。代わりに、その頃の半径に関する質問と回答を見つけました）
https://groups.google.com/forum/#!topic/google-m …

上記関数の利用方法は、VBAモジュールに記述した上で、spherDistance(座標1, 座標2) で呼び出すと、2点間の距離が返されます。
例えば（A列,B列),(C列,D列）に2点の座標(緯度、経度）があり、これらの距離をE列に求めたければ、E1セルに
　= spherDistance(A1:B1, C1:D1)
の式を入力し、下方にフィルコピーすれば、それぞれの2点間の距離が求められます。
ご質問のケースでは、2点のうちの1点は固定なので、絶対指定をすれば良いことになります。
距離が求められれば、No2様が回答なさっていらっしゃるように、最小値を探してその座標を参照するような仕組みにすることで、関数だけでも解決できますね。

ユーザ関数を利用せずに、通常のエクセル関数だけで距離を求める式を作成してみるなら、
=2*半径*ASIN(SQRT((COS(C1*角換算)*COS(D1*角換算)-COS(A1*角換算)*COS(B1*角換算))*(COS(C1*角換算)*COS(D1*角換算)-COS(A1*角換算)*COS(B1*角換算))+(COS(C1*角換算)*SIN(D1*角換算)-COS(A1*角換算)*SIN(B1*角換算))*(COS(C1*角換算)*SIN(D1*角換算)-COS(A1*角換算)*SIN(B1*角換算))+(SIN(C1*角換算)-SIN(A1*角換算))*(SIN(C1*角換算)-SIN(A1*角換算)))/2)
のように表すことも可能です。これをフィルコピーしても同様の結果が得られます。
（式は長いですが、上記のユーザ関数とまったく同じ内容です）
（式中の「半径」,「角換算」は、名前の定義で半径と角度の換算係数となるように設定しておきます。実際の式を組込むともっと長い式になってしまいますので…）


・・・なんだか話が脱線してしまいました。
上記手順の4）の「距離を求め」のところでspherDistance()と同様な計算を行うか、そのまま関数を利用すれば距離が求められますので、その他の部分は、文章で表した内容をコード化すれば良いことになります。

※ お勉強中とのことなので、敢えて直接の回答を示さない方法をとりました。
　 単に、結果だけを得ても、何の勉強にもならないと考えましたので・・・

No.4 回答者： fujillin 回答日時： 2017/09/20 13:34

ANo3です。

書き忘れましたが、緯度、経度はいずれも度単位の10進数で記されているものと仮定しています。
60進表記（度:分:秒）やその他の記法（方位付きの記法など）の場合は、別途換算が必要になります。
（上記の度単位への換算、または、直接ラジアンへの換算）

この回答へのお礼

丁寧にありがとうございます。
自分で教えていただいたヒントをもとに
自分でやってみます。

お礼日時：2017/09/21 22:38

No.2 回答者： rukaandkaito 回答日時： 2017/09/19 18:37

単純な考え方として、シート２のＣ列に入力された地点からの距離を計算しては如何でしょうか。

２点を結ぶ長方形の対角線という事で、それほど難しい計算ではないと思います。
Ｄ列にはRANK関数を配置し、ＥＦ列にそれぞれＡＢ列を参照としておきます。
一番近いということなので、データが１０００件あるのであれば１０００番目の値という事ですから
VLOOKUP関数で１０００の値を参照しＥＦ列をそれぞれ参照する、というやり方ではダメですか？
下手なＶＢＡを記述してバグ潰しをするより簡単だと思いますが。
的外れな回答であればお許しください(笑)

この回答へのお礼

早速の回答ありがとうございます。
質問の仕方が悪かったようで申し訳ありません。
現在VBAの勉強中でしてVBAでの記述方法が知りたかったため
質問させていただきました。
ありがとうございました。

お礼日時：2017/09/19 19:03

No.1 回答者： fujillin 回答日時： 2017/09/19 17:35

こんにちは

存在する座標がどのようなものなのか（近接するものかバラけているかなど）によっても、効率的な方法は若干変わってくると思いますが・・・


もっともプリミティブに計算するなら、対称点（シート1）の座標とシート2の座標群との距離を順に計算して、最小値を記憶してゆくようなループで処理すれば可能と思います。
距離計算が計算方法によっては複雑になる可能性があるので（後述）、全部の座標に対して距離計算をするのを省きたければ、計算中の最近点の距離を対称点からの経度差・緯度差に換算しておいて、座標値の差がそれ以上あったら距離計算はスキップするといった前処理を行うことで効率化が計れるかも知れません。


さて、問題の距離計算ですが、正確に計算したければ地球を回転楕円体とするのが妥当と思われますが、私には難しすぎました。（失笑）
一方で、正球体として近似計算する方法でもよいのであれば、計算は格段に簡単になります。

球体で近似した場合の計算の考え方は・・・
1）2点はそれぞれ球体表面にあるので、立体座標化して直線距離を求めます。
2）この直線距離を弦とする弧長を求めれば地表面での距離となります。

球体近似の場合は、地測系の違いは距離計算にはあまり影響ないと言えるでしょう。
似たような計算として、google mapがAPIで地表面の距離計算サービスを提供していますが、以前、上記の球体近似の計算とmapサービスの計算の結果を比較してみたことがあります。
経度1度、緯度1度程度（場所によって異なりますが約100km程度）の距離計算で差が0.1㎜以下でしたので、google mapの計算も（多分）球体近似で求めているのではないかと推測します。
また、対象とする座標群がある地域に集中している（例えば日本国内とか）ことがわかっているなら、球体の半径の値を日本近傍の数値に設定しておくことで、計算精度を上げることが可能と思われます。
（距離を目的としているわけではないようですので、精度はあまり重要ではないかも知れませんね）

きちんと回転楕円体近似で求める方法は私には難しすぎますが、過去の質問の回答の際に多少は調べてみたりもしましたので、ご参考までに。
https://oshiete.goo.ne.jp/qa/9504592.html

この回答へのお礼

早速の回答ありがとうございます。
質問の仕方が悪かったようで申し訳ありません。

シート１の入力座標とシート2の座標群との距離のうち、最小値を出して、
シート２の該当する行の（A●、B●）をシート１の（C1、D1）に表示すれば
いいのでしょうが、現在VBAの勉強中でしてVBAでの記述方法が知りたかった
ため質問させていただきました。
ありがとうございました。

お礼日時：2017/09/19 19:05