|
最近学着用sumifs函数,遇到两个空格方面的问题,感觉细节的处理真的挺重要。 问题1:单元格文档中空格的影响&"*"
问题2:单元格为空格的影响&""
这是日常登记的表
这是日常登记的基础表,需要按材料名称和收料单位分别统计出对应的汇总数量和汇总金额。即如下图的样子。
按照sumifs公式用法,编辑公式:
=SUMIFS(发料统计!$E$3:$E$119,发料统计!$C$3:$C$119,B5,发料统计!$G$3:$G$119,G3),结果4748.76,完全正确。
接着继续编辑后面的公式,结果"钢材"的"第一项目部"的数量和金额都汇总正确了,但"二项目部"的都不正确。
检查后,公式编辑的没有问题,数据单元格的内容也没有错误等异常情况。
再次检查发现原来基础表里,收料单位部分"二项目部"和"一项目部"没有对齐,检查发现文字前有空格,导致未能汇总进去。
如何消除空格的影响呢?首先想到了trim()函数,但公式里用的是范围,无法使用trim,只得另找办法。
突然想到在某个帖子上见到过匹配单元格里含某个字符的处理。用*代替其他无关字符,加上必须匹配的字符,于是修改公式为:
=SUMIFS(发料统计!$E$3:$E$119,发料统计!$C$3:$C$119, "*" & B5 &"*",发料统计!$G$3:$G$119,"*"& G3 &"*")
空格的影响成功解决,数据汇总全部正确。
但是,这只是统计到基础表里的第119行,那以后增加数据后,公式还要修改也很麻烦,那就直接把119行变大点,先预定个300行吧。
于是手动将公式里的119修改为300,但结果显示报错。原因是空格不能匹配进入到公式里。
头疼不能医脚,既然空格未能匹配进去,那就想办法把空格也匹配,根据在本站搜索到的解决办法,在匹配值位置增加&""代码,修改公式为:
=SUMIFS(发料统计!$E$3:$E$300,发料统计!$C$3:$C$300, "*" & B5 &"*" & "",发料统计!$G$3:$G$300,"*"& G3 & "*" & "")
试着在第120行增加数据,成功的纳入了汇总统计,问题解决。
最后为了便于公式下拉,用钞票留住G3让其别跳槽,改G3为 $G$3,最后公式如下:
=SUMIFS(发料统计!$E$3:$E$303,发料统计!$C$3:$C$303,"*" & B5 & "*" & "",发料统计!$G$3:$G$303,"*" &$G$3 & "*" & "")
至此完成了该sumifs函数的细节处理,公式可以向下拖。比我之前用VBA代码方式简单效果也是一样。
|
|