6位数值转为日期_「函数011」- 文本型日期导致的SUMIFS求和为0怎么处理

函数实战系列

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

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

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

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

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

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

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

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

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

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

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

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

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

模板设计。

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

技巧分列法

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

辅助列法

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

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

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

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

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

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

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

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

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

具体如图：

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

具体如图：

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

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

具体如图：

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

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

知乎的排版我哭

「函数011」- 文本型日期导致的SUMIFS求和为0怎么处理​mp.weixin.qq.com