如何基于Lat/Long坐标统计指定加油站5英里内替代站点数量并排序？

我有一份包含加油站Lat/Long（纬度/经度）坐标的列表，需要实现两个核心需求：

• 统计指定标准加油站5英里范围内的替代加油站数量
• 将5英里内的替代站点按距离由近至远排序

目前仅能提取单个站点间的距离，无法完成上述两个需求，尝试的公式均未成功。

现有尝试的公式 #

单个站点距离计算公式 #

之前用于计算指定加油站到替代站点距离的数组公式（旧版Excel需按Ctrl+Shift+Enter触发，Excel 365/2021可直接回车）：

=IFERROR(INDEX(ACOS(COS(RADIANS(90-RD_Stations!$C$3:$C$5000))*COS(RADIANS(90-$R3))+SIN(RADIANS(90-RD_Stations!$C$3:$C$5000))*SIN(RADIANS(90-$R3))*COS(RADIANS(RD_Stations!$D$3:$D$5000-$S3)))*6371,MATCH(SMALL((ABS($R3-RD_Stations!$C$3:$C$5000)^2+ABS($S3-RD_Stations!$D$3:$D$5000)^2)^(0.5),1),(ABS($R3-RD_Stations!$C$3:$C$5000)^2+ABS($S3-RD_Stations!$D$3:$D$5000)^2)^(0.5),0)),0)

注：该公式计算结果为公里距离，需乘以0.621371转换为英里。

未生效的数量统计公式 #

尝试统计5英里内站点数量的公式（无法实现需求）：

=SMALL((ABS($R3-RD_Stations!$C$3:$C$5000)^2+ABS($S3-RD_Stations!$D$3:$D$5000)^2)^(0.5),1)

可行解决方案 #

1. 统计5英里内的站点数量 #

使用COUNTIFS结合球面距离计算逻辑，直接统计符合条件的站点数：

=COUNTIFS(
    RD_Stations!$C$3:$C$5000, "<>",
    RD_Stations!$D$3:$D$5000, "<>",
    (ACOS(COS(RADIANS(90-RD_Stations!$C$3:$C$5000))*COS(RADIANS(90-$R3))+SIN(RADIANS(90-RD_Stations!$C$3:$C$5000))*SIN(RADIANS(90-$R3))*COS(RADIANS(RD_Stations!$D$3:$D$5000-$S3)))*6371*0.621371), "<=5"
)

说明：

• *0.621371将公里距离转换为英里
• 前两个条件用于排除空坐标的无效行
• Excel 365/2021可直接输入，旧版需按Ctrl+Shift+Enter作为数组公式执行

2. 按距离由近至远排序5英里内的站点 #

步骤1：批量计算所有站点的英里距离

在空白列（如T列）输入以下公式，下拉填充至所有站点行，得到每个替代站到目标站的英里距离：

=IFERROR(
    (ACOS(COS(RADIANS(90-RD_Stations!$C3))*COS(RADIANS(90-$R$3))+SIN(RADIANS(90-RD_Stations!$C3))*SIN(RADIANS(90-$R$3))*COS(RADIANS(RD_Stations!$D3-$S$3)))*6371*0.621371),
    ""
)

步骤2：筛选并排序

1. 选中包含加油站信息和距离列的整个数据区域
2. 点击「数据」选项卡→「筛选」，启用列筛选器
3. 在距离列的筛选菜单中选择「数字筛选」→「小于或等于」，输入5
4. 再次点击距离列的筛选箭头，选择「升序」即可完成近到远的排序

简化方案（仅Excel 365适用） #

利用Excel 365的地理数据类型和DISTANCE函数大幅简化操作：

1. 将RD_Stations!$C$3:$D$5000区域设置为「地理」数据类型
2. 将目标加油站的坐标（$R3,$S3）也设置为地理类型，命名为TargetStation
3. 距离公式简化为：

=IFERROR(DISTANCE(RD_Stations!$C3, TargetStation, "mi"), "")

4. 统计数量公式简化为：

=COUNTIFS(RD_Stations!$C$3:$C$5000, "<>", DISTANCE(RD_Stations!$C$3:$C$5000, TargetStation, "mi"), "<=5")

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