Excel计算多地点经纬度最短距离时跳过同坐标零值行的公式实现

问题背景 #

• 手头的Excel坐标表列结构：第一列为地点名称，第二列为纬度，第三列为经度，要求最终的距离计算结果放在经度列的右侧相邻列
• 计算规则要求：对4个地点的坐标做跨点两两比对，计算点位间的球面最短距离，必须跳过当前行点位与自身的比对——这类自比对会算出无意义的0值，完全不需要纳入计算范围
• 距离计算采用Haversine球面距离公式，地球半径参数固定取6370.97327862km

参考输入数据 #

原始无格式文本的点位坐标记录如下：

location A  15.20613944 120.5797483
Location B  15.21214563 121.777773
Location C  15.30023000 120.6565657
Location D  15.28993333 120.9067895

此前尝试的初始公式 #

按照Haversine逻辑编写的初始计算式存在无法排除自比对干扰的问题，具体公式如下：

=INDEX(6370.97327862*((2*ASIN(SQRT((SIN((RADIANS(C3)-RADIANS(C3:C6))/2)^2)+COS(RADIANS(C3))*COS(RADIANS(C3:C6))*(SIN((RADIANS(D3)-RADIANS(D3:D6))/2)^2))))),MATCH(MIN(SQRT((C3:C6)^2+(D3:D6)^2)),SQRT((C3:C6)^2+(D3:D6)^2),-1),1)

核心待解决需求 #

调整Excel公式逻辑，在逐行计算每个点位的最短距离时，自动过滤掉当前行对应的坐标值，排除自比对产生的0值干扰，准确输出每个地点到其余异地点位的最短球面距离。

内容的提问来源于stack exchange，提问作者DeadlyKitten999