[算表] Excel算式列法問題 -VBA,組合,各點距離

[ma0514 (馬小馬)]

軟體: 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)