获取数组A2#中唯一关系的Excel公式求解

问题分析 #

你当前使用的公式=FILTER(A2#,LEFT(A2#,4)<RIGHT(A2#,4))仅保留了左4位小于右4位的记录，会遗漏两种关键情况：

• 左4位大于右4位的单向关联（如2222|ABC|GBP|ABC|0001，若不存在对应的反向记录）
• 双向关联中左4位大于右4位的那条（但这类记录需要去重，仅保留一侧即可）

核心需求是：双向关联仅保留一条，所有单向关联无论左右大小都完整保留。

解决方案公式 #

使用Excel动态数组函数组合（需Excel 365/2021及以上版本支持）：

=LET(
    data, A2#,
    left_part, LEFT(data,4),
    right_part, RIGHT(data,4),
    reversed_records, right_part & MID(data,5,LEN(data)-8) & left_part,
    has_reverse, ISNUMBER(XMATCH(reversed_records, data)),
    FILTER(data, left_part<right_part + (left_part>right_part)*NOT(has_reverse))
)

公式拆解说明 #

1. 定义变量简化逻辑：
   • data：直接引用原数组A2#
   • left_part：提取每条记录的前4位
   • right_part：提取每条记录的后4位
2. 生成反向记录：
   reversed_records将当前记录的前后4位交换，中间部分原样保留，用于判断是否存在双向关联的反向记录
3. 判断是否为双向关联：
   has_reverse通过XMATCH检查反向记录是否存在于原数据中，返回TRUE（存在反向）或FALSE（无反向）
4. 精准筛选逻辑：
   • 保留left_part<right_part的记录：覆盖双向关联的其中一侧，以及左小右大的单向关联
   • 保留left_part>right_part且无反向记录的记录：仅保留真正的单向关联（双向关联因存在反向，这类记录会被自动过滤）

效果验证 #

• 双向关联（如0001|...|2222和2222|...|0001）：仅保留0001|...|2222
• 单向关联2222|ABC|GBP|ABC|0001：若不存在反向记录，会被完整保留
• 所有符合要求的唯一关系都会被提取到结果数组中

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