Excel中按邮编筛选Top N最近/最远唯一ID的公式需求

适用场景 #

你的数据结构：A列（重复邮编）、B列（唯一ID）、C列（距离，单位米），需要针对指定邮编，筛选出最近的N个ID或最远的N个ID（N取值2-10）。

方案1：Excel 365/2021（支持动态数组） #

这类版本的Excel支持FILTER、SORT、TAKE等动态函数，操作更简洁。

筛选最近的Top N ID #

假设：

• 数据范围：A2:C100
• 目标邮编输入在D2
• 指定的N值输入在E2

一次性返回所有结果（自动溢出）

=TAKE(SORT(FILTER($B$2:$C$100, $A$2:$A$100=$D$2), 2, 1), $E$2, 1)

公式拆解：

1. FILTER($B$2:$C$100, $A$2:$A$100=$D$2)：筛选出目标邮编对应的所有ID和距离
2. SORT(..., 2, 1)：按距离列（第2列）升序排序，最近的ID排在最前
3. TAKE(..., $E$2, 1)：提取前N行的ID列（第1列）

逐行返回（下拉公式）

如果需要逐个单元格输出ID，在F2输入公式后下拉：

=INDEX(SORT(FILTER($B$2:$C$100, $A$2:$A$100=$D$2), 2, 1), ROW(A1), 1)

• ROW(A1)会随下拉自动变为ROW(A2)、ROW(A3)，依次提取第1到第N个ID

筛选最远的Top N ID #

仅需修改SORT的排序方向为降序（参数2）：

一次性返回所有结果

=TAKE(SORT(FILTER($B$2:$C$100, $A$2:$A$100=$D$2), 2, 2), $E$2, 1)

逐行返回

=INDEX(SORT(FILTER($B$2:$C$100, $A$2:$A$100=$D$2), 2, 2), ROW(A1), 1)

方案2：旧版Excel（无动态数组） #

旧版Excel需要用数组公式（输入后按Ctrl+Shift+Enter确认），处理逻辑略有不同。

筛选最近的Top N ID #

在F2输入数组公式后下拉：

=INDEX($B$2:$B$100, MATCH(SMALL(IF($A$2:$A$100=$D$2, $C$2:$C$100), ROW(A1)), IF($A$2:$A$100=$D$2, $C$2:$C$100), 0))

公式拆解：

1. IF($A$2:$A$100=$D$2, $C$2:$C$100)：返回目标邮编对应的所有距离，其他值为FALSE
2. SMALL(..., ROW(A1))：提取第N小的距离（即第N近的）
3. MATCH(..., IF(...), 0)：找到该距离对应的行号
4. INDEX：返回对应行的ID

筛选最远的Top N ID #

把SMALL替换为LARGE（取第N大的距离）：

=INDEX($B$2:$B$100, MATCH(LARGE(IF($A$2:$A$100=$D$2, $C$2:$C$100), ROW(A1)), IF($A$2:$A$100=$D$2, $C$2:$C$100), 0))

注意事项 #

• 确保C列的距离是数值格式，如果是文本格式，公式会失效
• 若存在多个ID距离相同，上述公式会返回第一个匹配的ID；若需包含所有同距离ID，需调整公式处理重复值
• N值（2-10）建议放在单独单元格（如E2），方便随时修改

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