Excel单单元格多行物料按价格表求和计算总成造价的方法咨询

前置说明 #

现有两张表结构：

• 物料价格表（Table A）：包含material_name（物料名称）、material_price（物料价格）两列
• 总成清单表：每个总成对应单元格内存储该总成所有物料，单种物料占一行，保留换行排版

Excel 环境公式方案 #

适用Excel 365/2021及以上版本 #

假设总成物料存储在当前表B2单元格，直接在对应总价单元格输入以下公式即可：

=SUM(XLOOKUP(TEXTSPLIT(B2,CHAR(10)),TableA[material_name],TableA[material_price],0))

公式逻辑说明：

• TEXTSPLIT(B2,CHAR(10))：按换行符（CHAR(10)为Excel内换行符编码）拆分单元格内的所有物料，生成物料名称数组
• XLOOKUP逐个匹配数组中每个物料对应的价格，匹配不到时返回0避免报错
• SUM对所有匹配到的价格求和得到总成总造价

适用Excel 2019及更早版本 #

旧版Excel没有TEXTSPLIT函数，可改用FILTERXML实现拆分：

=SUM(XLOOKUP(FILTERXML("<t><s>"&SUBSTITUTE(B2,CHAR(10),"</s><s>")&"</s></t>","//s"),TableA[material_name],TableA[material_price],0))

Google Sheets 环境公式方案 #

假设总成物料存储在当前表B2单元格，Table A的物料名在A列、价格在B列，公式如下：

=SUM(ARRAYFORMULA(XLOOKUP(SPLIT(B2,CHAR(10)),TableA!A:A,TableA!B:B,0)))

注意事项 #

• 需保证总成单元格内的物料名称和Table A中的material_name完全一致，无多余空格、特殊符号，否则会出现匹配失败的情况
• 公式不会修改总成单元格的原有内容，换行排版完全保留不影响可读性
• 若同一种物料在总成单元格内出现多次，会自动按出现次数累加价格，无需额外调整规则

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