文本函数实战系列,

第十一期-文本型日期导致的SUMIFS求和为0怎么处理

我们来聊一下关于大家比较熟悉的求和。

最近在设计模板,就遇到了问题,想着分享给大家!

直接通过一个图让看一下问题:

按照选择的开始日期、结束日期、商品名称三个条件,求出对应的小时总额!

公式:=SUMIFS(F:F,E:E,J3,A:A,">="&J1,A:A,"<="&J2)

sumifs求和条件为什么求不到数据(SUMIFS求和为0怎么解决)(1)

从图中我们可以看出有对应的数据,但是合计却是0

问题在哪里呢?根据经验判断,我的销售数据是从系统导出的

一般系统数据库设计的时候,经常会把日期使用文本格式存储

但是导出结果为文本型,所以合计求不出来!

既然知道是文本型导致的,那么我们怎么处理呢?

方案1:转为常规日期格式

这种方法适合数据量较少或者自己可以后续加工的情况,不太适合数据量大和

模板设计。

其中我们可以使用技巧一次性换和辅助列的方法。

> 技巧分列法

动画演示:日期列-数据-分列-下一步-下一步-日期-完成

sumifs求和条件为什么求不到数据(SUMIFS求和为0怎么解决)(2)

> 辅助列法

这种方式,可以避免破坏数据源,对数据源有要求的情况下,是不错的选择!

添加辅助列,我们只要使用--原日期,就可以把文本型日期转为标准日期

sumifs求和条件为什么求不到数据(SUMIFS求和为0怎么解决)(3)

同步注意修改原公式,日期列修改为转化后的A列

sumifs求和条件为什么求不到数据(SUMIFS求和为0怎么解决)(4)

方案2:利用文本型标准格式,遍历处理

如果我们的数据由第三方粘贴,一般都是模板的情况,此时辅助列和分列法都不太实用,我们就要思考其他方式,在实在中,我还是找到了不错的解决方案,采用的方式其实就是逐个日期比较。

具体我们看一下处理过程:

公式:=SUMPRODUCT(SUMIFS(E:E,D:D,I3,A:A,TEXT(ROW(INDIRECT(I1&":"&I2)),"yyyy-mm-dd")))

sumifs求和条件为什么求不到数据(SUMIFS求和为0怎么解决)(5)

这里的变化部分,也是最核心的部分就是TEXT(ROW(INDIRECT(I1&":"&I2)),"yyyy-mm-dd"))

其中:ROW(INDIRECT(I1&":"&I2)) 利用日期是特殊的数值这个特性,生成了每天一个日期对应的数值

具体如图:

sumifs求和条件为什么求不到数据(SUMIFS求和为0怎么解决)(6)

通过text这个函数,我们可以把数值格式化为日期格式

具体如图:

sumifs求和条件为什么求不到数据(SUMIFS求和为0怎么解决)(7)

其实相对于,用期间的日期都去找一下,如果有就求和,最后形成了一组值,每天一个

最后通过SUMPRODUCT把区间内容每天的合计再求和

具体如图:

sumifs求和条件为什么求不到数据(SUMIFS求和为0怎么解决)(8)

这里本质上是数组公式,最重要的其实是解决问题的思路,我也是思考了好久才有这种解决方案。

同一个问题,我们在不同的场景下可能需要不同的方案去应对,足够多的知识储备,才能得心应手!

EXCEL办公实战,易办公,早下班,欢迎关注我们,下次不迷路

,