毎年4月・5月は新規の業務の発注が無いため、
普段はできない作業や勉強をする時間を取る事ができます。
ちょうどそんな時期に、測量部G課長が私にこんな課題を与えて来ました。
「エクセルでこんな様式でさぁ、XとYの座標入れたら
２点間の距離と方向角が出るような表ができんかなぁ？」
↓こんな様式↓
「え？座標から？計算で？？無理無理！
距離なら出せるかもしれんけど角度とか無理(σ´∀`)σΥΟ!!」
と即答w
しかし、G課長はそんな私のスルーにごまかされるはずもなくw
「イヤイヤイヤ！何を言いよんの！
計算で出せるよ!!( ﾟДﾟ)!!」
と切り返していらっしゃったのでぇ・・・
「え～？ 座標から方向角出す計算ってどうやったっけ？」
てな感じで、まずは計算方法の確認。
日常生活でふとそんな話を振られても、
覚えてないもんですよね～(￣▽￣;)
ではここからExcelで作ってみますよ！
まずはセルの書式設定で
まずはセルの書式設定で
小数点以下第3位までが表示されるよう設定しておきます。
そうしたら、D9セル（下図参照）に距離を出す式から入れます！
（セルを結合している場合、セル番地は若い番号が適用されます）
距離は単純に三平方の定理を使うので、こうなります。
距離AB=√(XB-XA)2+(YB-YA)2
(2乗の位置がブログの入力機能上、上付きになりません(￣▽￣;)）
仮にそれぞれの点名をA,B
XY座標をXA,YA,XB,YB
とします。
（なんで三平方の定理を使うのかわからない人は
方眼紙に簡単な数値で座標の絵を
描いてみるとわかる(σ´∀`)σΥΟ）
これをExcelの関数で入力すると↓
XY座標をXA,YA,XB,YB
とします。
（なんで三平方の定理を使うのかわからない人は
方眼紙に簡単な数値で座標の絵を
描いてみるとわかる(σ´∀`)σΥΟ）
これをExcelの関数で入力すると↓
=SQRT((B10-B8)^2+(C10-C8)^2)
=SQRTは数値の正の平方根を返す関数です。
入力したら、あとは必要なところまでドラッグ＆ドロップして、
関係ないD11セルなどに表示された数値は消します。
なお、下部の座標値を入力しない余白部分の距離セルには
そのままだと0.000が表示されてしまいますので、
ファイルのオプションで0を表示させない設定にします。
はい距離終わり！
やっぱり距離はカンタンだねー(｡-∀-)♪
問題は方向角なんですよ、めんどくさいよコレは！(-公- ;)
２点間の座標の方向角を出す計算というのは、
測量の世界では基本的な理論ですが、
（忘れてたくせに↑）
Excelでその解をどの関数を組み合わせて導き出すのか、
というところが非常にめんどくさい！
距離みたいに一発じゃいかないよ。
だって象限によって計算変わるしさ。
（象限→X軸とY軸によって4つに区切られた平面座標）
（象限→X軸とY軸によって4つに区切られた平面座標）
最終的に201-02-14のように
60進法の角度で表示させなきゃならないし。
60進法の角度で表示させなきゃならないし。
もー、やれって言うからやるけどさーヽ(｀Д´)ノ
はい、というワケでやります！
まずは単純に2点間の角度を出す公式を考えてみます。
θ=tan-1(YB-YA)/(XB-XA)
（なんでこの式を使うかわからない人は・・・
Google先生に聞いてくれ!!←投げた)
ここで出る値はラジアンなので、
Google先生に聞いてくれ!!←投げた)
ここで出る値はラジアンなので、
Excelでは角度に変更する関数DEGREESを付けます。
角度を求める核となる関数はコレ↓
=DEGREES(ATAN(YB-YA)/(XB-XA))
ATANは数値のアークタンジェントを返す関数です。
さらに、方向角を出すという事で、
座標の差の符号によって第何象限なのか判断し、出た角度に±0なのか
+180なのか+360なのかという条件を付けなければなりません！
なので、いったん、座標の差のプラスマイナスの組み合わせを
判定するための計算スペースを欄外に設けました。
ここを使って判定し、オレンジで囲ったセルに方向角が出るよう
関数を入力していきます！
それがコレ↓
=IF(OR(AND(I8<0,J8>0),(AND(I8<0,J8<0))),DEGREES(ATAN(J8/I8))+180,
IF(AND(I8>0,J8<0),DEGREES(ATAN(J8/I8))+360,
DEGREES(ATAN(J8/I8))))
長すぎやろ＿|￣|○
どうなってるかと言うと、
青文字→第2象限、第3象限の場合で180足す
赤文字→第4象限の場合で360足す
オレンジ文字→第1象限の場合で何も足さない
ANDでXが+かつYが－の組み合わせを指定し、
第2,第3象限は両方とも+180なのでORでつないでます。
IF関数の構造としては、
のようにしているのですが、IFSの方が良かったのかも・・・
そしてさらに！
まだこれでは終わらないんですよね～！
最後に、10進法で出た解を60進法で表示しなければならないので！
方向角のセルに以下のような式を入れました。
=IFERROR(TEXT(K8*"1:0","[h]-mm-ss"),"")
K8セルが先ほどの長い式を入れたセルです。
60進法にする式は青文字部分ですが、
下部の座標値の余白部分にエラー表示されてしまうのを
回避するためIFERRORを付けています。
TEXT関数の最初の式はK8*"1:0"としていますが、
今考えたらK8/24にした方が
単純で入力しやすかったかなと思いました。
数値を時間で表示する時に使う関数ですね。
もっとスマートな関数のチョイスと
組み合わせがあるかもしれないし、
測量の計算の過程も別のやり方もあると思いますが、
少しでも参考になれば幸いでございます(*´ω` )ﾉ
組み合わせがあるかもしれないし、
測量の計算の過程も別のやり方もあると思いますが、
少しでも参考になれば幸いでございます(*´ω` )ﾉ
（測量計算ソフトで検算したので、多分間違いない。多分w）
っていうかそもそも、こういう帳票を出力するために、
大金出して測量計算システムを導入しているので、
ポチッとワンクリックでシステムからこのような帳票は出るわけですが。
Excelのこの様式でなければいけない!!!
という仕事は多々あるのが現実で(;´Д`A ```
そのたびに、こうした方がいいかも、
ああした方がいいかも・・・Σ(ﾟДﾟ；≡；ﾟдﾟ)
と試行錯誤しており、こういう時VBA(プログラミング言語)を
勉強したいなぁとよく思います。
測量計算×Excelの記事はまた書きたいと思います"φ(･ｪ･o)~
と試行錯誤しており、こういう時VBA(プログラミング言語)を
勉強したいなぁとよく思います。
測量計算×Excelの記事はまた書きたいと思います"φ(･ｪ･o)~