為什麼這篇座標距離計算excel鄉民發文收入到精華區：因為在座標距離計算excel這個討論話題中，有許多相關的文章在討論，這篇最有參考價值！作者ma0514 (馬小馬)看板Office標題[算表] Excel算式列法問題 -VBA,組合,各...
軟體: excel
版本: office 2003
想請問
如何列公式算出300個座標軸之間各個距離
現在想到的只有 個別算300個座標軸對同一個點的距離
然後算300次
有無更方便的方式可以算出來呢
謝謝
> -------------------------------------------------------------------------- <
作者: JieJuen (David) 看板: Office
這應該是算各個"座標"之間的距離吧?
所以問題在於列出各個需要算的點 (組合)
列出來後參照之加以計算即可~
http://2y.drivehq.com/p/PointDistance.xls
> -------------------------------------------------------------------------- <
作者: chungyuandye (養花種魚數月亮賞星星) 看板: Office
距離矩陣由D1開始
=((INDEX($A$1:$A$6,COLUMN()-3,)-$A1)^2+(INDEX($B$1:$B$6,COLUMN()-3,)-$B1)^2)^0.5
或者
Sub distancematrix()
Dim Fn As Object
Set Fn = Application.WorksheetFunction
Range("A1:B300").Select
nrow = Selection.Rows.Count
ncol = Selection.Columns.Count
For i = 1 To nrow
xtemp1 = Fn.Index(Selection.Value, i)
For j = 1 To nrow
xtemp2 = Fn.Index(Selection.Value, j)
distance = (Fn.Index(Fn.MMult(xtemp1, Fn.Transpose(xtemp1)), 1) - Fn.Index(Fn.MMult(xtemp1, Fn.Transpose(xtemp2)), 1) - Fn.Index(Fn.MMult(xtemp2, Fn.Transpose(xtemp1)), 1) + Fn.Index(Fn.MMult(xtemp2, Fn.Transpose(xtemp2)), 1)) ^ 0.5
Sheets("sheet2").Cells(i, j) = distance
Next
Next
End Sub
> -------------------------------------------------------------------------- <
作者: JieJuen (David) 看板: Office
好文章! 我來註解一下吧 有誤請指正
此篇方法主要以方陣型式表達答案
故限制為最大欄位數 在2003為256，不幸的小於300
在2007則可大顯身手~
儘管如此，仍瑕不掩瑜!
※ 引述《chungyuandye (養花種魚數月亮賞星星)》之銘言：
: 距離矩陣由D1開始
: =((INDEX($A$1:$A$6,COLUMN()-3,)-$A1)^2+(INDEX($B$1:$B$6,COLUMN()-3,)-$B1)^2)^0.5
相較於我的無腦參照
此法已經將組合的規律寫入，故式子精簡~
運用此精簡於巨集中 則類似
Sub distancematrix3()
Dim Fn As Object
Set Fn = Application.WorksheetFunction
Dim v1 As Range, v2 As Range
Set v1 = Range("A1:A6")
Set v2 = Range("B1:B6")
nrow = v1.Rows.Count
For i = 1 To nrow
For j = 1 To nrow
xtemp1 = Fn.Index(v1.Value, i, 1)
ytemp1 = Fn.Index(v2.Value, i, 1)
xtemp2 = Fn.Index(v1.Value, j, 1)
ytemp2 = Fn.Index(v2.Value, j, 1)
distance = ((xtemp1 - xtemp2) ^ 2 + (ytemp1 - ytemp2) ^ 2) ^ 0.5
Sheets("sheet4").Cells(i, j) = distance
Next
Next
End Sub
著眼於distance，比下面巨集之方法容易理解
: 或者
: Sub distancematrix()
: Dim Fn As Object
: Set Fn = Application.WorksheetFunction
: Range("A1:B300").Select
: nrow = Selection.Rows.Count
: ncol = Selection.Columns.Count
^^^^ 此數沒有用到
若用上面巨集之法 在維數多的時候可能可以用到ncol
: For i = 1 To nrow
: xtemp1 = Fn.Index(Selection.Value, i)
: For j = 1 To nrow
: xtemp2 = Fn.Index(Selection.Value, j)
: distance = (Fn.Index(Fn.MMult(xtemp1, Fn.Transpose(xtemp1)), 1) - Fn.Index(Fn.MMult(xtemp1, Fn.Transpose(xtemp2)), 1) - Fn.Index(Fn.MMult(xtemp2, Fn.Transpose(xtemp1)), 1) + Fn.Index(Fn.MMult(xtemp2, Fn.Transpose(xtemp2)), 1)) ^ 0.5
: Sheets("sheet2").Cells(i, j) = distance
: Next
: Next
: End Sub
此法之distance看似十分繁鎖，卻正是妙處所在
運用向量(而非之前的點)為基礎進行運算，
其強大之處在維度增加時將會顯示出來
即 如果改為三維座標，或是代數幾何上的n維座標
巨集所需改的，只是Range("A1:B300")
如 變為Range("A1:C300")
此法改為公式可如下 (同上 距離矩陣由D1開始)
=(MMULT(INDEX($A$1:$C$6,COLUMN()-3,),TRANSPOSE(INDEX($A$1:$C$6,COLUMN()-3,)))
-2*MMULT(INDEX($A$1:$C$6,ROW(),),TRANSPOSE(INDEX($A$1:$C$6,COLUMN()-3,)))
+MMULT(INDEX($A$1:$C$6,ROW(),),TRANSPOSE(INDEX($A$1:$C$6,ROW(),))))^0.5
因其中MMULT均得出1*1距陣
故"似乎"可交換 因此改為 2*交插項
此問題如有任何延伸狀況 會導致"交換律"失敗
煩請告知~ 非常感謝!
若無，原巨集則可再加簡化，更顯其精簡與威力~!
為了比較維數多的情況
以下為三維，使用"點"進行運算(其餘算法類似)
Sub distancematrix2()
Dim Fn As Object
Set Fn = Application.WorksheetFunction
Range("A1:c6").Select
nrow = Selection.Rows.Count
ncol = Selection.Columns.Count
For i = 1 To nrow
xtemp1 = Fn.Index(Selection.Value, i, 1)
ytemp1 = Fn.Index(Selection.Value, i, 2)
ztemp1 = Fn.Index(Selection.Value, i, 3)
For j = 1 To nrow
xtemp2 = Fn.Index(Selection.Value, j, 1)
ytemp2 = Fn.Index(Selection.Value, j, 2)
ztemp2 = Fn.Index(Selection.Value, j, 3)
distance = ((xtemp1 - xtemp2) ^ 2 + (ytemp1 - ytemp2) ^ 2 + (ztemp1 - ztemp2) _
^ 2) ^ 0.5
Sheets("sheet3").Cells(i, j) = distance
Next
Next
End Sub
明顯可看出當維數越多 distance越麻煩
當然，可以再加一個LOOP
(就用到ncol了)
Sub distancematrix4()
Dim Fn As Object
Set Fn = Application.WorksheetFunction
Range("A1:C6").Select
nrow = Selection.Rows.Count
ncol = Selection.Columns.Count
For i = 1 To nrow
For j = 1 To nrow
distance = 0
For k = 1 To ncol
xtemp1 = Fn.Index(Selection.Value, i, k)
xtemp2 = Fn.Index(Selection.Value, j, k)
distance = distance + (xtemp1 - xtemp2) ^ 2
Next
Sheets("sheet5").Cells(i, j) = distance ^ 0.5
Next
Next
End Sub
精簡且可用於多維
但 是以多一層loop做為代價~
大概...就這樣了吧~
------------------------
果然還沒完...漏掉多維的陣列公式 = =||
=SUM((INDEX($A$1:$C$6,COLUMN(A:A),)-$A1:$C1)^2)^0.5
這樣巨集與陣列公式的寫法對照就都有了 (雖然不一定是最好 但就練習練習不錯)
寫法這麼多，可見用方陣真是好處多多!
※ 重新編輯: JieJuen (09/28/2008)