Ptt：Excel計算距離矩陣

excel mmult example excel計算距離矩陣
D1=(INDEX($A$1:$A$6,COLUMN()-3,)-$A1)^2+(INDEX($B$1:$B$6,COLUMN()-3,)-$B1)^2

資料量大時，可考慮VBA

Sub distancematrix()
Dim Fn As Object
Set Fn = Application.WorksheetFunction
Range("A1:B6").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

Tags: Excel, Teaching

• 編輯文章
• 此文章網址
• 轉寄此文章
• 目前有0 意見