江苏龙网

  1. 首页 > 人文 > >

Excel办公实战SUMIFS求和为0怎么解决？


文本函数实战系列 ，
【Excel办公实战SUMIFS求和为0怎么解决？】第十一期-文本型日期导致的SUMIFS求和为0怎么处理
我们来聊一下关于大家比较熟悉的求和 。
最近在设计模板 ， 就遇到了问题 ， 想着分享给大家！
直接通过一个图让看一下问题：
按照选择的开始日期、结束日期、商品名称三个条件 ， 求出对应的小时总额！
公式：=SUMIFS(F:F,E:E,J3,A:A,">="&J1,A:A,"<="&J2)
Excel办公实战SUMIFS求和为0怎么解决？
本文插图
从图中我们可以看出有对应的数据 ， 但是合计却是0
问题在哪里呢？根据经验判断 ， 我的销售数据是从系统导出的
一般系统数据库设计的时候 ， 经常会把日期使用文本格式存储
但是导出结果为文本型 ， 所以合计求不出来！
既然知道是文本型导致的 ， 那么我们怎么处理呢？
方案1：转为常规日期格式
这种方法适合数据量较少或者自己可以后续加工的情况 ， 不太适合数据量大和
模板设计 。
其中我们可以使用技巧一次性换和辅助列的方法 。
> 技巧分列法
动画演示：日期列-数据-分列-下一步-下一步-日期-完成
Excel办公实战SUMIFS求和为0怎么解决？
本文插图
> 辅助列法
这种方式 ， 可以避免破坏数据源 ， 对数据源有要求的情况下 ， 是不错的选择！
添加辅助列 ， 我们只要使用--原日期 ， 就可以把文本型日期转为标准日期
Excel办公实战SUMIFS求和为0怎么解决？
本文插图
同步注意修改原公式 ， 日期列修改为转化后的A列
Excel办公实战SUMIFS求和为0怎么解决？
本文插图
方案2：利用文本型标准格式 ， 遍历处理
如果我们的数据由第三方粘贴 ， 一般都是模板的情况 ， 此时辅助列和分列法都不太实用 ， 我们就要思考其他方式 ， 在实在中 ， 我还是找到了不错的解决方案 ， 采用的方式其实就是逐个日期比较 。
具体我们看一下处理过程：
公式：=SUMPRODUCT(SUMIFS(E:E,D:D,I3,A:A,TEXT(ROW(INDIRECT(I1&":"&I2)),"yyyy-mm-dd")))
Excel办公实战SUMIFS求和为0怎么解决？
本文插图
这里的变化部分 ， 也是最核心的部分就是TEXT(ROW(INDIRECT(I1&":"&I2)),"yyyy-mm-dd"))
其中：ROW(INDIRECT(I1&":"&I2)) 利用日期是特殊的数值这个特性 ， 生成了每天一个日期对应的数值
具体如图：
Excel办公实战SUMIFS求和为0怎么解决？
本文插图
通过text这个函数 ， 我们可以把数值格式化为日期格式
具体如图：
Excel办公实战SUMIFS求和为0怎么解决？
本文插图
其实相对于 ， 用期间的日期都去找一下 ， 如果有就求和 ， 最后形成了一组值 ， 每天一个
最后通过SUMPRODUCT把区间内容每天的合计再求和
具体如图：
Excel办公实战SUMIFS求和为0怎么解决？
本文插图
这里本质上是数组公式 ， 最重要的其实是解决问题的思路 ， 我也是思考了好久才有这种解决方案 。
同一个问题 ， 我们在不同的场景下可能需要不同的方案去应对 ， 足够多的知识储备 ， 才能得心应手！


推荐阅读


上一篇：科技小机器人绿厂耳机、手表发布EVA限定版，小米lot生态后院“起火”？

下一篇：iPhoneiPhone 13后置镜头设计草图曝光；第二代小米MIX Alpha或在路上