Excel:偏移/转置以创建距离矩阵

Question

我有一个大约90个位置的列表，我需要计算这些位置之间的鹰眼距离。我使用以下公式来计算以英里为单位的距离：

=ACOS(COS(RADIANS(90-$F3))*COS(RADIANS(90-$F$2))+SIN(RADIANS(90-$F3))*SIN(RADIANS(90-$F$2))*COS(RADIANS($G3-$G$2)))*3958.756

​

​

这个想法是，一旦我有了H列的所有公式，我只需要将列拖到右边，并将新位置固定下来，这样当矩阵中的位置与自身匹配时，距离将为零。

我一直在尝试转置和偏移，但没有任何成功，有什么想法吗？

Answer 1

输入

=IF(ROW(A1)>COLUMN(A1),ACOS(COS(RADIANS(90-$F2))*COS(RADIANS(90-INDEX($F$2:$F$5,COLUMN(A1))))+SIN(RADIANS(90-$F2))*SIN(RADIANS(90-INDEX($F$2:$F$5,COLUMN(A1))))*COS(RADIANS($G2-INDEX($G$2:$G$5,COLUMN(A1)))))*3958.756,"")

在H2中(此单元格将为空)，复制/拖动它以填充整个表格H2:K5 (一些随机的lon/lat)：

​

​

在这里，ROW(A1)，COLUMN(A1)是一种使用i,j元素的表索引的懒惰方式。

以防万一，以结构化的形式使用相同的公式：

IF(
  ROW(A1)>COLUMN(A1),
  ACOS(COS(RADIANS(90-$F2))*COS(RADIANS(90-INDEX($F$2:$F$5,COLUMN(A1))))
    +SIN(RADIANS(90-$F2))*SIN(RADIANS(90-INDEX($F$2:$F$5,COLUMN(A1))))
    *COS(RADIANS($G2-INDEX($G$2:$G$5,COLUMN(A1)))))*3958.756,
  ""
)

Answer 2

这是我的建议

=ACOS(COS(RADIANS(90-D$2))*COS(RADIANS(90-$B4))+SIN(RADIANS(90-D$2))*SIN(RADIANS(90-$B4))*COS(RADIANS(D$3-$C4)))*3958.756

​

​