基于家庭坐标计算人口最多家庭的最近邻hserial值

需求 #

从包含住院患者数据的表格中，找到人口最多的家庭，计算该家庭与其他家庭的直线距离（勾股定理：√((x1–x2)²+(y1–y2)²)），排除自身（距离为0）后返回距离最近的家庭的hserial值。

表格数据 #

字段说明 #

• hhcoords：家庭坐标，前三位为x值，后三位为y值
• persnum：单条记录对应的家庭人口数（同一家庭有多条记录，需求和得到总人数）
• hserial：家庭唯一标识序列号

现有公式问题 #

你提供的公式出现#VALUE!错误，原因是错误引用了表头区域Table2[[#Headers],[hserial]]而非数据区域，且逻辑是找最远家庭而非最近邻，不符合需求。

修正后的公式 #

=LET(
    // 获取唯一家庭列表、总人数、对应坐标
    uniqueHH, UNIQUE(Table2[hserial]),
    totalPers, BYROW(uniqueHH, LAMBDA(h, SUMIF(Table2[hserial], h, Table2[persnum]))),
    coords, XLOOKUP(uniqueHH, Table2[hserial], Table2[hhcoords]),
    // 定位人口最多的家庭及其坐标（转为数值避免文本计算）
    targetHH, INDEX(uniqueHH, MATCH(MAX(totalPers), totalPers, 0)),
    targetCoord, XLOOKUP(targetHH, uniqueHH, coords),
    targetX, LEFT(targetCoord, 3)+0,
    targetY, MID(targetCoord, 4, 3)+0,
    // 计算目标家庭与其他家庭的距离，排除自身
    distances, BYROW(uniqueHH, LAMBDA(h,
        IF(h=targetHH, "", SQRT((targetX-LEFT(XLOOKUP(h, uniqueHH, coords),3)+0)^2 + (targetY-MID(XLOOKUP(h, uniqueHH, coords),4,3)+0)^2)
    )),
    // 筛选最小距离对应的家庭
    nearestHH, INDEX(uniqueHH, MATCH(MIN(FILTER(distances, distances<>"")), distances, 0)),
    nearestHH
)

公式逻辑拆解 #

1. 数据预处理：用UNIQUE提取所有家庭，SUMIF计算每个家庭总人数，XLOOKUP匹配对应坐标
2. 定位目标家庭：找到人口最多的家庭，拆分其坐标并转为数值（避免文本运算错误）
3. 距离计算：遍历所有家庭，跳过自身，用勾股定理计算直线距离
4. 获取最近邻：过滤掉自身的空值，找到最小距离对应的家庭hserial

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