Excel中如何基于XY坐标中间点拆分起止点连接为子连接？

核心需求 #

基于给定的坐标点列表（A4:C14，含标记、X/Y坐标）和用户输入的起止连接（E4:F10），自动拆分每个起止连接为包含中间点的相邻子连接，输出格式对应J4:K16的样式。

实现公式 #

在目标单元格（如J4）输入以下动态数组公式：

=LET(
    坐标表, A4:C14,
    连接表, E4:F10,
    点标记, INDEX(坐标表,,1),
    X坐标, INDEX(坐标表,,2),
    Y坐标, INDEX(坐标表,,3),
    获取坐标, LAMBDA(标记, XLOOKUP(标记, 点标记, HSTACK(X坐标, Y坐标))),
    处理单条连接, LAMBDA(起点, 终点,
        LET(
            起坐标, 获取坐标(起点),
            终坐标, 获取坐标(终点),
            起X, INDEX(起坐标,,1), 起Y, INDEX(起坐标,,2),
            终X, INDEX(终坐标,,1), 终Y, INDEX(终坐标,,2),
            筛选条件, (X坐标>=MIN(起X,终X))*(X坐标<=MAX(起X,终X))*(Y坐标>=MIN(起Y,终Y))*(Y坐标<=MAX(起Y,终Y)),
            中间点, FILTER(坐标表, 筛选条件),
            排序依据, IF(起X<>终X, X坐标, Y坐标),
            排序方向, IF(起X<>终X, SIGN(终X-起X), SIGN(终Y-起Y)),
            排序后点, SORTBY(中间点, INDEX(中间点,,IF(起X<>终X,2,3)), 排序方向),
            点序列, INDEX(排序后点,,1),
            子连接, HSTACK(DROP(点序列,-1), DROP(点序列,1)),
            子连接
        )
    ),
    所有连接, BYROW(连接表, LAMBDA(r, 处理单条连接(INDEX(r,1), INDEX(r,2)))),
    输出, VSTACK(所有连接),
    输出
)

公式说明 #

1. 数据源定义：坐标表和连接表分别绑定输入的坐标点范围和起止连接范围，简化后续引用。
2. 坐标查找函数：获取坐标用XLOOKUP快速根据点标记匹配对应的X/Y坐标。
3. 单条连接处理：
   • 先获取起止点的坐标，筛选出位于两点X/Y范围之间的所有点；
   • 根据起止点的X/Y变化方向（X不同则按X排序，Y不同则按Y排序），对筛选出的点排序；
   • 从排序后的点序列中生成相邻点的子连接（如A→B→C生成A-B、B-C）。
4. 批量处理与合并：用BYROW遍历所有起止连接，调用处理函数后，用VSTACK合并所有子连接为最终输出列表。

补充调整 #

如果需要严格筛选沿直线的中间点（而非矩形范围内的点），可修改筛选条件为：

筛选条件, (X坐标>=MIN(起X,终X))*(X坐标<=MAX(起X,终X))*(Y坐标>=MIN(起Y,终Y))*(Y坐标<=MAX(起Y,终Y))*((Y坐标-起Y)*(终X-起X)=(X坐标-起X)*(终Y-起Y)),

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