如何计算Excel中带有单位的数据

如何计算Excel中带有单位的数据

1、带单位的数字求和

如下图，对 C 列的金额求和。

❶ 首先用 SUBSTITUTE 函数将 C 列中的字符 "元" 替换成空字符：

❷ 这时生成的一列数字还是文本型的，不能直接求和，需要在前面加两个负号转换成数值：

❸ 最后就可以用 SUM 函数，对转化后的数值求和，得到最终结果：

2、提取不同单位中的数值

如下图，从 B 列带有不同单位的文本中，提取数值到 C 列：

❶ 首先用 ROW ($1:9) 生成一组由 1 开始递增的序列值：

❷ 接着，把这组序列值放到 LEFT 函数的第二个参数里，让它从左到右按顺序逐步扩大截取范围，把当前 B 列文本中的字符一段段取出来，最终形成一列文本数组：

❸ 最后用 LOOKUP 函数，在这列数组中找到最后一个数值，返回结果：

3、拆分带有多个单位的文本

如下图，从 C 列中拆分带有多个单位的数字到 D:F 列：

❶ 首先用 LEFT 函数从当前 C 列的文本中，提取单位前面的字符：

❷ 然后用 RIGHT 函数，由右至左逐一扩展提取字符的个数，截取字符，生成一列数组：

❸ 接着用 LOOKUP 函数，在这列数组中找到最后一个数值，再填充复制公式：

❹ 最后用 IFNA 函数屏蔽公式中的错误值（#N / A）返回结果：

4、不同单位的数量合并

如下图，将 C:E 列的数值与对应的单位合并成如 F 列的效果：

❶ 先用 TEXT 函数把当前行里的数值统一转成指定的文本格式：如果数值大于 0，就显示“数值+单位”组成的字符；如果不是正数，就直接返回空字符。这样一来，就能先得到一整行文本数组。

❷ 再用 CONCAT 函数将这行文本数组合并成一串字符，得到结果:

5、单位转化

如下图，根据 D 列的箱规，将 C 列的数量转换成如 E 列的效果：

❶ 首先用当前的数量除以箱规，再用 INT 函数取整得到箱数：

❷ 然后 TEXT 函数对箱数进行文本格式设置，如果是正数则数值与单位连接，否则返回空字符。

❸ 再用 MOD 函数根据当前的数量和箱规求余数，得到剩下的件数：

❸ 最后将两个公式用连接符」&「连接在一起，返回最终结果：