Excel神技能!单元格多物品价格一键汇总,告别手动计算烦恼

📌 假设sheet 1中A1单元格数据为“笔记本*1、钢笔*3、文件夹*5”（格式为：物品*数量）；sheet 2对应的是物品的价格表，具体如下：

此时如何计算A1物品价格总和？只需输入下列公式，即可求出答案：

=SUMPRODUCT(--TEXTAFTER(TEXTSPLIT(A1,,"、"),"*"),VLOOKUP(TEXTBEFORE(TEXTSPLIT(A1,,"、"),"*"),Sheet2!B:C,2,0))

💭 原理解析

● 首先，TEXTSPLIT函数用于按指定分隔符拆分文本。故TEXTSPLIT(A1,,"、")表示将A1按"、"拆分为数组，得到：{"笔记本*10","钢笔*5","文件夹*3"}；

● 其次，在“--TEXTAFTER(TEXTSPLIT(A1,,"、"),"*")”中，TEXTAFTER用于提取"*"之后的文本，可得到 {"10","5","3"}；而“--”则将其转换为数值，得到 {10,5,3}

● 同理，先用“TEXTBEFORE(...)”提取"*"之前的文本，得到 {"笔记本","钢笔","文件夹"}后，再用VLOOKUP函数匹配物品单价，；

● 最后，SUMPRODUCT计算单价，得到结果64元。

📌 还是以原先的案例为例，Power Query的操作步骤则更为简单：

1.导入数据到Power Query：选中包含物品信息的单元格，点击“数据”选项卡，选择“从表格/区域”（如果数据不在表格中，会提示创建表格），将数据导入到Power Query编辑器中。

2.拆分列：在Power Query编辑器中，选中包含物品信息的列，点击“转换”选项卡，选择“拆分列”，按分隔符“、”拆分列，将每个物品信息拆分到单独的行中。

3.提取物品名称和数量：再次选中拆分后的列，点击“拆分列”，按分隔符“*”拆分列，将物品名称和数量分别拆分到两列中。

4.更改数据类型：将数量列的数据类型更改为数值类型。

5.匹配单价并计算总价：如果有单价对照表，可以将其也导入到Power Query中，然后通过“合并查询”功能匹配每个物品的单价，最后添加一个自定义列，计算每个物品的总价（数量*单价），并通过“分组依据”功能汇总所有物品的总价。

6.加载数据回Excel：完成上述操作后，点击“关闭并上载”，将处理后的数据加载回Excel中。

💭 原理解析

Power Query是Excel中强大的数据处理工具，它可以通过一系列的操作步骤，将复杂的数据转换为我们需要的格式。通过拆分列、提取信息、匹配单价等操作，我们可以轻松地汇总出所有物品的总价。而且Power Query的操作步骤可以保存下来，下次遇到类似的数据时，只需点击刷新即可快速得到结果。

● 数据格式规范：确保单元格中的物品信息格式规范，每个物品都以“物品*数量”的形式记录，并且物品之间用顿号隔开。如果格式不规范，可能会导致公式计算错误。

● 函数版本兼容性：不同版本的Excel对函数的支持可能有所不同。比如TEXTSPLIT函数只有Excel 365及以上版本才有，如果你使用的是旧版本的Excel，可能无法使用该函数。