No.3ベストアンサー
- 回答日時:
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
- 回答日時:
ANo3です。
書き忘れましたが、緯度、経度はいずれも度単位の10進数で記されているものと仮定しています。
60進表記(度:分:秒)やその他の記法(方位付きの記法など)の場合は、別途換算が必要になります。
(上記の度単位への換算、または、直接ラジアンへの換算)
No.2
- 回答日時:
単純な考え方として、シート2のC列に入力された地点からの距離を計算しては如何でしょうか。
2点を結ぶ長方形の対角線という事で、それほど難しい計算ではないと思います。
D列にはRANK関数を配置し、EF列にそれぞれAB列を参照としておきます。
一番近いということなので、データが1000件あるのであれば1000番目の値という事ですから
VLOOKUP関数で1000の値を参照しEF列をそれぞれ参照する、というやり方ではダメですか?
下手なVBAを記述してバグ潰しをするより簡単だと思いますが。
的外れな回答であればお許しください(笑)
早速の回答ありがとうございます。
質問の仕方が悪かったようで申し訳ありません。
現在VBAの勉強中でしてVBAでの記述方法が知りたかったため
質問させていただきました。
ありがとうございました。
No.1
- 回答日時:
こんにちは
存在する座標がどのようなものなのか(近接するものかバラけているかなど)によっても、効率的な方法は若干変わってくると思いますが・・・
もっともプリミティブに計算するなら、対称点(シート1)の座標とシート2の座標群との距離を順に計算して、最小値を記憶してゆくようなループで処理すれば可能と思います。
距離計算が計算方法によっては複雑になる可能性があるので(後述)、全部の座標に対して距離計算をするのを省きたければ、計算中の最近点の距離を対称点からの経度差・緯度差に換算しておいて、座標値の差がそれ以上あったら距離計算はスキップするといった前処理を行うことで効率化が計れるかも知れません。
さて、問題の距離計算ですが、正確に計算したければ地球を回転楕円体とするのが妥当と思われますが、私には難しすぎました。(失笑)
一方で、正球体として近似計算する方法でもよいのであれば、計算は格段に簡単になります。
球体で近似した場合の計算の考え方は・・・
1)2点はそれぞれ球体表面にあるので、立体座標化して直線距離を求めます。
2)この直線距離を弦とする弧長を求めれば地表面での距離となります。
球体近似の場合は、地測系の違いは距離計算にはあまり影響ないと言えるでしょう。
似たような計算として、google mapがAPIで地表面の距離計算サービスを提供していますが、以前、上記の球体近似の計算とmapサービスの計算の結果を比較してみたことがあります。
経度1度、緯度1度程度(場所によって異なりますが約100km程度)の距離計算で差が0.1㎜以下でしたので、google mapの計算も(多分)球体近似で求めているのではないかと推測します。
また、対象とする座標群がある地域に集中している(例えば日本国内とか)ことがわかっているなら、球体の半径の値を日本近傍の数値に設定しておくことで、計算精度を上げることが可能と思われます。
(距離を目的としているわけではないようですので、精度はあまり重要ではないかも知れませんね)
きちんと回転楕円体近似で求める方法は私には難しすぎますが、過去の質問の回答の際に多少は調べてみたりもしましたので、ご参考までに。
https://oshiete.goo.ne.jp/qa/9504592.html
早速の回答ありがとうございます。
質問の仕方が悪かったようで申し訳ありません。
シート1の入力座標とシート2の座標群との距離のうち、最小値を出して、
シート2の該当する行の(A●、B●)をシート1の(C1、D1)に表示すれば
いいのでしょうが、現在VBAの勉強中でしてVBAでの記述方法が知りたかった
ため質問させていただきました。
ありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) Excel VBA 最終行を取得しVlookup関数をコピーする方法をコーディングで教えてください。 3 2023/05/11 13:14
- Visual Basic(VBA) 顧客ごとに違う点検案内を作成するマクロ 4 2022/09/16 05:34
- Excel(エクセル) excelで検索した商品の画像(ネットワーク上の)を表示させたい。 3 2023/06/28 00:32
- Visual Basic(VBA) エクセルのマクロで対象ごとにシート分けしてその内容をセルに書き込みたい 9 2022/08/24 13:23
- Excel(エクセル) Excel関数について質問です。 シート1に入力シートがあります。 =A1 =B1などで離れた数字な 5 2022/12/18 13:42
- Excel(エクセル) 関数EXACT(文字列,文字列)とexcelVBA 3 2022/04/14 15:07
- Excel(エクセル) エクセルで、 A1セルに「A」という値、 B1セルに「B」という値が入っています。 どちらも表示形式 5 2023/02/22 23:05
- Excel(エクセル) エクセル関数教えてください 3 2022/06/21 10:22
- Visual Basic(VBA) VBA 検索と入力 Excel ブック ぶぶぶ シート ししし 列V 検索対象の列です 最終行は、お 6 2023/05/17 01:40
- Excel(エクセル) エクセルで一定の数値を超えたとき”一定数”、超えなかったら計算式を入れたい 3 2022/04/05 10:31
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
YUVって?
-
Excelである地点に一番近い座標...
-
彼女とハグをする時胸は当たる...
-
記号の意味 GH、FH
-
Excelについての質問です。 2点...
-
この地図記号は何の意味ですか...
-
道路幅がわかるような、できる...
-
町域とは
-
google map 地図のスケールが分...
-
郵便番号の下四桁が「0000」と...
-
緯度、経度 1分は何km?
-
高校、地理なのですがわからな...
-
[乗換]
-
話す時に顔を必要以上に近づける人
-
住宅地図 古いをみる方法
-
住所が不十分でも届くでしょうか?
-
東西南北の方角がどうしてもわ...
-
昔の住所表記を現在の住所表記...
-
Excelに地図を貼り付けてトリミ...
-
私は学校で4人グループです。...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excelである地点に一番近い座標...
-
YUVって?
-
ノーズR補正計算
-
エクセルを使って円周率を作る方法
-
ある三点を通る球体の半径rの...
-
Excel リストボックスについて...
-
彼女とハグをする時胸は当たる...
-
メルカトル図法での大圏コース...
-
Excelについての質問です。 2点...
-
メルカトル図法
-
緯度、経度 1分は何km?
-
東京(北緯34度41分、東経139度...
-
この地図記号は何の意味ですか...
-
昔の住所表記を現在の住所表記...
-
google map 地図のスケールが分...
-
[乗換]
-
東西南北の方角がどうしてもわ...
-
北海道から沖縄までの距離は何...
-
町域とは
-
縮尺5万分の1の地図上で1センチ...
おすすめ情報
シート1の入力座標とシート2の座標群との距離のうち、最小値を出して、
シート2の該当する行の(A●、B●)をシート1の(C1、D1)に表示した
いのですが、現在VBAの勉強中でしてVBAでの記述方法が分かりません。
VBAの記述について教えていただけますと助かります。
よろしくお願いいたします