条件求和问题-总结.xlsVIP

问题 答案 Formula 6.00 截至行 日期 套数 型号 型号 HS31740 HS31700 HS31760 HS31700+31740+27536 HS31700+31740+27753 HS31700+31760+27753 31700+31740+27751 31700+3174031700+31760HS31765 31700+31760+27751 月份 条件求和问题 表一 表二 说明： 问题： 日期、件数列为各型号/套装销售的日期和件数 套数 求表二中黄色区域的每月各型号的销售套数 表一中型号列HS31700+31740+27536，表示31700、31740、27536三种型号套装出售 ，每套中各型号计为一件。 31740 31700 31760 型号 月份 gvntw matsumi wangtx218 wutong9988/zwy gdliyy shuyee wutong9988/wangtx218 全部数据参与计算模式 龙逸凡/czzqb/matsumi wutong9988 shooter_xu 期间累计差异模式 分段数据参与计算模式 Sumproduct或Sum Sumproduct或Sum-Sum Sumif+Offset+Match Sumif+Offset+Countif+Match Sumif+offset+Countif Sumif+Offset+Countif-Sum Sumif+Offset+Match-Sum Sumif+Offset+Frequency-Sum 月份 日期 apolloh 必须从数据表的第一个月开始计算 计算的期间不受限制 计算的月份必须有数据 正常 Formula =GET.CELL(6,!E11)T(NOW()) 截至行 =MATCH(答案!$B12:$B14,日期) 起始行 =IF(MIN(日期)=答案!$B13:$B15-DAY(答案!$B13:$B15),MATCH(答案!$B13:$B15-DAY(答案!$B13:$B15),日期))+1 日期 =OFFSET(问题!$C$4,,,COUNT(问题!$C:$C)) 套数 =OFFSET(日期,,1) 行数 =FREQUENCY(日期,答案!$B16:$B18) 型号 =OFFSET(日期,,-1) 定义名称 其中型号段有3种思路多种解法，日期段也有2种思路N种解法。 以上解法的计算速度，除了受参与计算的数据量多少影响外，还受到所使用函数自身的计算效率的制约。 必须在辅助单元设置上期末截至日期 必须将辅助单元格设置为文本 分段数据参与计算模式+多单元格数组公式 Sumif+Offset+Match+Frequency Sumif+offset+match 此解法先确定每月第一条的记录位置，再根据记录数算出记录所跨的行引用；还应该在前面加上条件判断，当月记录数为0时不执行该计算。 此解法先确定每月最后一条记录位置，再根据记录数倒算出记录所跨的行引用；还应该在前面加上条件判断，当月记录数为0时不执行该计算。 使用Counif需要历遍所有记录才能确定分段引用的地址，所以实际上还是左右数据都参与了每个月的计算。 使用Match能快速定位符合条件的记录位置，不需像Countif那样历遍所有记录才能确定分段引用的地址，所以速度要快。 Search判断型号列是否存在指定条件型号；将日期列的年和月分别同日期条件的年和月进行比较。 Find判断型号列是否存在指定条件型号；将日期列和日期条件分别扣除当月的天数还原为每月的第一天进行比较。 使用Countif需要历遍所有记录才能确定分段引用的地址，所以实际上还是左右数据都参与了每个月的计算。 通过日期列同日期条件的上下限比较，判断其是否处于日期条件的期间，其上限使用日期条件扣除当月天数，不需要引用上行的日期条件。 使用Len+Substitute来判断条件型号是否存在于型号列的某行，并将日期列的每一个日期转换成每月的最后一天同日期条件进行比较。 将日期列的年和月同日期条件的年和月用连字符连起来进行比较。 firelife/yigepure/bjzzr 解法10-13，采用了Sum-Sum或Sumif-Sum的模式，计算期间累计差异，减少了参与计算的数据量，但必须从最早的记录开始计算或者提供期初累计数； 解法14-16，采用Sumif+Offset模式，分段引用需要计算的数据，使得参与计算的数据量大大减少,但应注意处理当某月没有数据出错的情况。 用Countif的多维引用，分别判断每个引用区域（行）是否存在符合条件的型号 用Sumif的多维引用，分别判断每个引用区域（行）是否存在符合条件的型号 用Sum先计算出截至当前行日期条件的