如何扩展Excel公式获取最近3家门店的名称及距离？

一、计算最近3家门店的距离 #

基于你原有的球面距离计算公式，通过SMALL函数提取距离数组中最小的3个值，即可获取最近3家门店的距离：

1. 最近第1家距离：

=SMALL(ACOS(COS(RADIANS(90-E5))*COS(RADIANS(90-'7E LatLong'!$C$2:$C$650))+SIN(RADIANS(90-E5))*SIN(RADIANS(90-'7E LatLong'!$C$2:$C$650))*COS(RADIANS(F5-'7E LatLong'!$D$2:$D$650)))*6371,1)

2. 最近第2家距离：
   将公式最后一个参数1改为2即可：

=SMALL(ACOS(COS(RADIANS(90-E5))*COS(RADIANS(90-'7E LatLong'!$C$2:$C$650))+SIN(RADIANS(90-E5))*SIN(RADIANS(90-'7E LatLong'!$C$2:$C$650))*COS(RADIANS(F5-'7E LatLong'!$D$2:$D$650)))*6371,2)

3. 最近第3家距离：
   将公式最后一个参数改为3：

=SMALL(ACOS(COS(RADIANS(90-E5))*COS(RADIANS(90-'7E LatLong'!$C$2:$C$650))+SIN(RADIANS(90-E5))*SIN(RADIANS(90-'7E LatLong'!$C$2:$C$650))*COS(RADIANS(F5-'7E LatLong'!$D$2:$D$650)))*6371,3)

如果你使用的是Excel 365/2021及以上版本（支持动态数组），可以直接用以下公式一次性返回3个距离，公式会自动溢出到右侧单元格：

=SMALL(ACOS(COS(RADIANS(90-E5))*COS(RADIANS(90-'7E LatLong'!$C$2:$C$650))+SIN(RADIANS(90-E5))*SIN(RADIANS(90-'7E LatLong'!$C$2:$C$650))*COS(RADIANS(F5-'7E LatLong'!$D$2:$D$650)))*6371,{1,2,3})

二、获取最近3家门店的名称 #

结合SORTBY和INDEX函数，先按距离从小到大排序门店名称，再提取前3个结果：

动态数组版本（Excel 365/2021+）

一次性返回前3家门店名称，自动溢出到右侧单元格：

=INDEX(SORTBY('7E LatLong'!$B$2:$B$650,ACOS(COS(RADIANS(90-E5))*COS(RADIANS(90-'7E LatLong'!$C$2:$C$650))+SIN(RADIANS(90-E5))*SIN(RADIANS(90-'7E LatLong'!$C$2:$C$650))*COS(RADIANS(F5-'7E LatLong'!$D$2:$D$650)))*6371,1),{1,2,3})

旧版Excel（不支持动态数组）

需要分别输入数组公式（输入后按Ctrl+Shift+Enter确认）：

1. 最近第1家名称：

=INDEX('7E LatLong'!$B$2:$B$650,MATCH(SMALL(ACOS(COS(RADIANS(90-E5))*COS(RADIANS(90-'7E LatLong'!$C$2:$C$650))+SIN(RADIANS(90-E5))*SIN(RADIANS(90-'7E LatLong'!$C$2:$C$650))*COS(RADIANS(F5-'7E LatLong'!$D$2:$D$650)))*6371,1),ACOS(COS(RADIANS(90-E5))*COS(RADIANS(90-'7E LatLong'!$C$2:$C$650))+SIN(RADIANS(90-E5))*SIN(RADIANS(90-'7E LatLong'!$C$2:$C$650))*COS(RADIANS(F5-'7E LatLong'!$D$2:$D$650)))*6371,0))

2. 最近第2家名称：
   将公式中两个1改为2后，按Ctrl+Shift+Enter确认：

=INDEX('7E LatLong'!$B$2:$B$650,MATCH(SMALL(ACOS(COS(RADIANS(90-E5))*COS(RADIANS(90-'7E LatLong'!$C$2:$C$650))+SIN(RADIANS(90-E5))*SIN(RADIANS(90-'7E LatLong'!$C$2:$C$650))*COS(RADIANS(F5-'7E LatLong'!$D$2:$D$650)))*6371,2),ACOS(COS(RADIANS(90-E5))*COS(RADIANS(90-'7E LatLong'!$C$2:$C$650))+SIN(RADIANS(90-E5))*SIN(RADIANS(90-'7E LatLong'!$C$2:$C$650))*COS(RADIANS(F5-'7E LatLong'!$D$2:$D$650)))*6371,0))

3. 最近第3家名称：
   将公式中两个2改为3后，按Ctrl+Shift+Enter确认即可。

注意：如果存在距离完全相同的门店，旧版公式可能会返回重复的名称，此时可以用INDEX+SMALL+IF的组合逻辑避免重复，比如：

=INDEX('7E LatLong'!$B$2:$B$650,SMALL(IF(ACOS(COS(RADIANS(90-E5))*COS(RADIANS(90-'7E LatLong'!$C$2:$C$650))+SIN(RADIANS(90-E5))*SIN(RADIANS(90-'7E LatLong'!$C$2:$C$650))*COS(RADIANS(F5-'7E LatLong'!$D$2:$D$650)))*6371<=SMALL(ACOS(COS(RADIANS(90-E5))*COS(RADIANS(90-'7E LatLong'!$C$2:$C$650))+SIN(RADIANS(90-E5))*SIN(RADIANS(90-'7E LatLong'!$C$2:$C$650))*COS(RADIANS(F5-'7E LatLong'!$D$2:$D$650)))*6371,3),ROW('7E LatLong'!$B$2:$B$650)-ROW('7E LatLong'!$B$2)+1),1))

（输入后按Ctrl+Shift+Enter，依次修改最后一个参数为1、2、3）

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