我要评论hi，我是偏爱函数公式，爱用 excel 图表管理仓库的大叔 mr 赵~
我们在工作中，经常会遇到带单位的数据运算，比如对带有单位的数据求和、提取不同单位中的数值，合并不同单位的数量等等。
下面就通过 5 个典型的案例，一起和大家探讨，如何应对处理这些带单位的数据。
1、带单位的数字求和
如下图，对 c 列的金额求和。
❶ 首先用 substitute 函数将 c 列中的字符 "元" 替换成空字符：
=substitute(c3:c8"元""")
❷ 这时生成的一列数字还是文本型的，不能直接求和，需要在前面加两个负号转换成数值：
=--substitute(c3:c8"元""")
❸ 最后就可以用 sum 函数，对转化后的数值求和，得到最终结果：
=sum(--substitute(c3:c8,"元",""))
2、提取不同单位中的数值
如下图，从 b 列带有不同单位的文本中，提取数值到 c 列：
❶ 首先用 row ($1:9) 生成一组由 1 开始递增的序列值：
=row($1:9)
❷ 然后以这组序列值作为 left 函数的第二个参数，由左至右逐一扩展截取当前 b 列文本中的字符，生成一列文本数组：
=left(b3,row($1:9))
❸ 最后用 lookup 函数，在这列数组中找到最后一个数值，返回结果：
3、拆分带有多个单位的文本
如下图，从 c 列中拆分带有多个单位的数字到 d:f 列：
❶ 首先用 left 函数从当前 c 列的文本中，提取单位前面的字符：
=left($c3,find(d$2$c3)-1)
❷ 然后用 right 函数，由右至左逐一扩展提取字符的个数，截取字符，生成一列数组：
=right(left($c3,find(d$2, $c3)-1)row($1:9))
❸ 接着用 lookup 函数，在这列数组中找到最后一个数值，再填充复制公式：
=-lookup( 1, -right( left($c3, find(d$2, $c3) - 1), row($1:9) ))
❹ 最后用 ifna 函数屏蔽公式中的错误值（#n / a）返回结果：
=ifna( -lookup( 1, -right( left($c3, find(d$2, $c3) - 1), row($1:9) ) ), "")
4、不同单位的数量合并
如下图，将 c:e 列的数值与对应的单位合并成如 f 列的效果：
❶ 首先用 text 函数对当前行的数值，进行文本格式化设置，如果是正数则显示数值和单位组成的字符，否则返回空字符，生成一行文本数组。
=text(c3:e3,0&c$2:e$2&"")
❷ 再用 concat 函数将这行文本数组合并成一串字符，得到结果:
=concat(text(c3:e3,0&c$2:e$2&""))
5、单位转化
如下图，根据 d 列的箱规，将 c 列的数量转换成如 e 列的效果：
❶ 首先用当前的数量除以箱规，再用 int 函数取整得到箱数：
=int(c4/d4)
❷ 然后 text 函数对箱数进行文本格式设置，如果是正数则数值与单位连接，否则返回空字符。
=text(int(c4/d4)"0 箱")
❸ 再用 mod 函数根据当前的数量和箱规求余数，得到剩下的件数：
=text(mod(c4,d4),"0 件;;")
❸ 最后将两个公式用连接符」&「连接在一起，返回最终结果：
=text(int(c4 / d4), "0 箱;;") & text(mod(c4, d4), "0 件;;")
好了，今天就分享到这里。
发表评论