如何修改Excel数组公式使其区分大小写并支持数字求和？

适用公式 #

SUMPRODUCT版本（无需数组输入） #

=SUMPRODUCT(IFERROR(INDEX(values, MATCH(TRUE, EXACT(MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1), INDEX(values,,1)), 0), 2), 0))

数组公式版本（需按Ctrl+Shift+Enter触发） #

{=SUM(IFERROR(INDEX(values, MATCH(TRUE, EXACT(MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1), INDEX(values,,1)), 0), 2), 0))}

公式说明 #

• MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1)：将A2单元格的字符串拆分为单个字符的数组，例如输入Ab2会生成{"A","b","2"}
• EXACT(..., INDEX(values,,1))：通过精确匹配规则（区分大小写），对比每个拆分字符与对应表（values）第一列的所有字符，返回二维逻辑数组
• MATCH(TRUE, ..., 0)：为每个拆分字符找到精确匹配的行号，得到对应值所在的行位置数组
• INDEX(values, ..., 2)：根据行号从对应表第二列取出匹配的数值
• IFERROR(..., 0)：处理无匹配的字符，将#N/A错误转为0（可根据需求调整为其他值）
• SUMPRODUCT/SUM：对所有匹配到的数值求和

验证示例 #

假设values区域为：

• 输入ab：返回1.325+1.5=2.825（正确）
• 输入Ab：返回1.5+1.5=3（正确）
• 输入ab2：返回1.325+1.5+1.5=4.325（正确）
• 输入Ab2：返回1.5+1.5+1.5=4.5（正确）

注意事项 #

• values区域需包含所有需要匹配的字符（大小写、数字），每个字符单独一行
• SUMPRODUCT版本直接回车即可生效，数组公式版本必须按Ctrl+Shift+Enter完成输入
• 如果不需要忽略无匹配字符，可去掉IFERROR(..., 0)部分，此时无匹配字符会返回#N/A错误

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