编写Excel公式核对ORACLE与Excel发票编号并判断支付状态

核心思路 #

先对两边的发票编号做标准化清洗，去除格式差异（如INV前缀、空格、连字符），再判断清洗后的核心编号是否在对方列表中，实现双向匹配。

具体公式实现 #

假设：

• 你的发票跟踪表编号范围为 A2:A100
• ORACLE系统录入的编号范围为 C2:C100

1. 单条记录匹配公式（判断你的表中编号是否在ORACLE存在） #

=IF(
    XLOOKUP(
        TRIM(SUBSTITUTE(SUBSTITUTE(A2,"INV",""),"-","")),
        TRIM(SUBSTITUTE(SUBSTITUTE(C:C,"INV",""),"-","")),
        "PAID",
        "Not Yet Paid",
        0
    ),
    "PAID",
    "Not Yet Paid"
)

清洗逻辑说明：

• SUBSTITUTE(A2,"INV","")：移除所有INV字符
• SUBSTITUTE(..., "-", "")：移除连字符
• TRIM(...)：清除首尾多余空格
• XLOOKUP第5参数设为0，实现精确匹配

2. 双向交叉匹配（确认两边编号互相存在） #

如果需要同时验证你的表编号在ORACLE中存在、且ORACLE编号在你的表中存在，可用：

=IF(
    AND(
        COUNTIF(C:C,"*"&TRIM(SUBSTITUTE(SUBSTITUTE(A2,"INV",""),"-",""))&"*")>0,
        COUNTIF(A:A,"*"&TRIM(SUBSTITUTE(SUBSTITUTE(C2,"INV",""),"-",""))&"*")>0
    ),
    "PAID",
    "Not Yet Paid"
)

3. 正则表达式增强版（适配复杂前缀，Excel 365+支持） #

如果存在更不规则的前缀格式，用正则直接提取核心编号：

=IF(
    XLOOKUP(
        REGEXREPLACE(A2,"^INV\s?-?",""),
        REGEXREPLACE(C:C,"^INV\s?-?",""),
        "PAID",
        "Not Yet Paid",
        0
    ),
    "PAID",
    "Not Yet Paid"
)

说明：REGEXREPLACE(A2,"^INV\s?-?","") 会精准移除开头的INV，以及后续可能的空格或连字符，直接提取核心编号部分。

关于SIGMA和SCAN函数的使用 #

• SIGMA函数：本质是基于LAMBDA的聚合工具，多用于求和类累积计算，完全不适用于当前的匹配判断场景，属于冗余工具。
• SCAN函数：是迭代型函数，用于逐行记录中间计算过程。如果需要批量处理并追踪匹配步骤，SCAN可以实现，但对于单纯返回匹配结果的需求，它会增加不必要的复杂度，远不如XLOOKUP或COUNTIF高效简洁。

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