Excel 2010中基于发票号右侧8位部分匹配高亮行的技术实现问询

你好，我这边碰到了一个Excel 2010的实际需求，想寻求解决方法：有个客户用的是旧版Excel 2010，需要给Sheet1里的行设置高亮——只要Sheet1 B列的部分发票号，能和Sheet2 A列完整发票号的最后8位匹配上就行。具体场景细节如下：

• Sheet1的B列是不遵循本地官方格式的部分发票号
• Sheet2的A列是符合官方格式的完整发票号，而这些完整号里真正有效的发票编号其实是最后8位
• 举个例子：Sheet1的B51不能匹配像A0001000038132这类完整号，但如果有个完整号的最后8位和B51完全一致（比如A0001000000010，假设最后8位就是B51的值），那这一行就需要被高亮。

具体实现步骤（完全兼容Excel 2010） #

亲测有效的操作步骤，一步步来就行：

1. 打开Sheet1，选中你需要应用高亮规则的整个数据区域（比如从第1行到数据最后一行的所有列，或者只选有数据的行范围）
2. 点击顶部菜单栏的开始选项卡，找到条件格式，下拉选择新建规则
3. 在弹出的「新建格式规则」对话框里，选择「使用公式确定要设置格式的单元格」这个选项
4. 在「为符合此公式的值设置格式」的输入框里，粘贴下面这个公式：

   =SUMPRODUCT(--(RIGHT(Sheet2!$A:$A,8)=$B1))>0
   

   公式简单解释下：
   • RIGHT(Sheet2!$A:$A,8)：提取Sheet2 A列每个完整发票号的最后8位字符
   • --(RIGHT(...)=$B1)：把“最后8位等于当前行B列值”的判断结果转成1（匹配）或0（不匹配）
   • SUMPRODUCT(...)：把所有结果加起来，只要总和大于0，就说明Sheet2里至少有一个匹配的完整发票号
5. 点击「格式」按钮，选择你想要的高亮样式（比如填充色选黄色，或者设置字体颜色），确认后再点击「确定」完成规则设置

额外注意事项 #

• 如果Sheet2的A列存在空白单元格，可能会把Sheet1里B列为空的行也高亮，要是不想这样，可以把公式改成下面这样，排除空白的情况：

  =SUMPRODUCT(--(RIGHT(Sheet2!$A:$A,8)=$B1),--(Sheet2!$A:$A<>""))>0
  

• 确保Sheet1 B列的部分发票号长度是8位，如果有不足的情况，可能需要先给这些值补前导零（比如用TEXT($B1,"00000000")替换公式里的$B1），保证和完整号的最后8位长度一致。

备注：内容来源于stack exchange，提问作者razor7