【友情提醒】 微信公众号“Excel偷懒的技术”有赠书活动，送的都是畅销书，请勿错过！活动链接：【文末赠书】一篇文章搞定按年、季、月、旬、周求和，超级实用，建议收藏备用最近比较忙：练习题062是1月5日在读者群发布让大家练习的，一直没给答案。赶紧抽时间公布一下答案。如需本练习示例文件，请加入“Excel偷懒的技术”读者QQ群下载，最新的QQ群634404633。练习题062：按年、季、月、旬、周求和（函数题）答案注意：请仔细审题，本题要求用函数，而不是用透视表来解答。一、按年求和数据表格如下图，要求用函数求出各年份的金额公式1：用SUMIFS多条件求和这问题可以转化为一个多条件求和，比如：要求2017年的，那么就是对既大于2017年1月1日，又小于2017年12月31日的日期数据进行求和，因而，我们可以使用SUMIFS函数：=SUMIFS($C$2:$C$33,$B$2:$B$33,">=2017/1/1",$B$2:$B$33,"<=2017/12/31")此公式不能直接下拉填充，以计算出2018年、2019年的金额，我们可以用DATE函数来计算日期：=DATE(E2,1,1)将其代入到前面的公式，完整的公式为：=SUMIFS($C$2:$C$33,$B$2:$B$33,">="&DATE(E2,1,1),$B$2:$B$33,"<="&DATE(E2,12,31))公式2：用SUMPRODUCT进行数组运算SUMPRODUCT函数是将数组进行相乘，然后求乘积的和，它可以进行数组运算，而不必按Ctrl+Shift+Enter，因而我们可以先计算出各单元格的年份是否为指定年份（如果是，其结果为true，相当于1，不是的话，其结果为FASLE，相当于0），然后将其与金额相乘，其乘积之和就是年份的金额之和。公式为：=SUMPRODUCT((YEAR($B$2:$B$33)=E2)*$C$2:$C$33)二、按季度求和1、用SUMIFS函数本题就是要求按季度和年度求和，因而也是一个多条件求和，可用SUMIFS，在前面SUMIFS多条件按年求和公式的基础上，修改一下求和条件就是了。一季度是计算1月1日到3月31日，二季度是计算4月1日到6月30日。。。。。假设季度数是N，那么该季度起始月就是3*N-2，季度起始日用DATE函数DATE（2018，3*N-2，1）该季度截止月就是3*N，由于截止月最后一日不固定，有些是30，有些是31，不用简单的用DATE函数，还需套用计算月末最后一天的EOMONTH函数（End+Of+Month），因而其公式为：=SUMIFS($C$2:$C$33,$B$2:$B$33,">="&DATE(F$10,$E11*3-2,1),$B$2:$B$33,"<"&EOMONTH(DATE(F$10,$E11*3,1),0))2、用SUMPRODUCT函数有一个比较碰巧的规律：假设各月份为M，求2的M次方，其结果的字符数，刚好等于月份所在的季度数因而，我们可以用LEN(2^MONTH($B$2:$B$33))来计算B列各月的季度数，用SUMPRODUCT来计算各季度的合计，其公式为：=SUMPRODUCT((LEN(2^MONTH($B$2:$B$33))=$E2)*(YEAR($B$2:$B$33)=F$1)*$C$2:$C$33)三、按月求和只要理解了前面的公式，按月求和就比较简单了，公式分别为：=SUMPRODUCT((MONTH($B$2:$B$14)=E2)*$C$2:$C$14)=SUMIFS($C$2:$C$14,$B$2:$B$14,">="&DATE(2017,E2,1),$B$2:$B$14,"<"&DATE(2017,E2+1,1))=SUMIFS($C$2:$C$14,$B$2:$B$14,">="&DATE(2017,E2,1),$B$2:$B$14,"<="&EOMONTH(DATE(2017,E2,1),0))四、按旬求和公式：上旬1-10日：=SUMIFS($C$2:$C$46,$B$2:$B$46,">="&DATE(2018,$F2,1),$B$2:$B$46,"<"&DATE(2018,$F2,11))中旬11-20日：=SUMIFS($C$2:$C$46,$B$2:$B$46,">="&DATE(2018,$F2,11),$B$2:$B$46,"<"&DATE(2018,$F2,21))下旬：21-月末=SUMIFS($C$2:$C$46,$B$2:$B$46,">"&DATE(2018,$F2,20),$B$2:$B$46,"<="&EOMONTH(DATE(2018,$F2,1),0))读者QQ群的读友031 - 硬币(37125126) 给出的一体化公式：=SUMPRODUCT((MONTH($B$2:$B$46)=$F2)*(TEXT(DAY($B$2:$B$46),"[>20]下旬；[>10]中旬；上旬")=G$1)*$C$2:$C$46)五、按周求和Excel里有个WEEKNUM函数可以计算某天在一年的第几周，可惜的是它不能用于数组公式，要不然，可以用它配合SUMPRODUCT来求和。我们只有另辟蹊径：要计算第几周，可以用一个比较笨的方法，计算与基准日的天数是七的多少整数倍。比如今年2018年的第一周是从1月1日（周一），那么我往前数七天，也就是2017年12月25日，以这天为基准日（12月都是31天，因而实际上都是取上一年的12月25日），然后将日期减基准日，天数再除七取整，就是周数了。=INT((日期-"2017-12-25")/7)扩展：如果周数不是按上面的计算方法，而是按日历上在第几周，比如2017年1月1日，是周日，也就是在本年的第一周，1月2日是周一，应该算是本年的第二周。要按这个计算方法，适当调整基准日就是了，可以用2016年12月19日做基准日：=INT((日期-"2016-12-19")/7)这样计算出来的效果就与下面公式的结果相同：=WEEKNUM(日期,2)因而，计算第几周之和的公式为：1、用SUMPRODUCT函数=SUMPRODUCT((INT(($B$2:$B$19-"2017-12-25")/7)=F2)*$C$2:$C$19)2、用SUMIFS=SUMIFS($C$2:$C$19,$B$2:$B$19,">="&("2018-1-1"+7*(F2-1)),$B$2:$B$19,"<="&("2018-1-7"+7*(F2-1)))上面的公式相对比较复杂，函数较差的朋友可能看不懂，看不懂没关系，将此文收藏，要用的时候，只要修改一下公式中的单元格，就可套用到工作中的表格。如果觉得本文对你有帮助，欢迎转发分享，收藏备用！-------我是分割线，下面是赠书活动-------《“偷懒”的技术：打造财务Excel达人》在当当网办公类畅销榜已经连续第三个月第二名了2017年11月
第2名2017年12月
第2名2018年1月
第2名并且，还是当当网2017年年度畅销榜办公类第2名，Excel第1名！欢庆，当此际！赠书活动一定要有，要给《“偷懒”的技术》的读者、本公众号的粉丝朋友们送来福利：十本畅销图书！分二次赠送，本次五本。下次推送的文章继续赠送五本。中奖者可在下面的五本图书中任选一本（详细介绍见文后）：1、《智能时代财务管理》2、《财务思维：如何成为一个财务高手》3、《EXCEL这么用就对了》4、《谁说菜鸟不会数据分析》5、《PPT进化：如何设计一份惊艳的PPT》赠送活动详细规则请点链接：【文末赠书】一篇文章搞定按年、季、月、旬、周求和，超级实用，建议收藏备用赠品详细介绍：1、《财务思维：如何成为一个财务高手》购买链接：本书作者是知乎大V @蔡千年 ，这是一本凝结了多年财务管理经验、企业财务整合与管理经验、上市公司海外投资与并购思维的书，全书满满的都是干货，本书不象市面上那些教条而枯燥的财务类书籍，本书更象是一位老大哥给财务小弟小妹们介绍经验，从如何规划自己的职业、如何获得晋升、如何构架财务思维等方面一一道来，也用一些实际案例来介绍如何用一种全新的视角来看待和分析企业。另外，针对大部分财务新人们的迷茫，系统地介绍了财务人员必备的素质和技能，最后还介绍了用最小的代价通过CPA考试的复习策略。要是表哥龙逸凡十年前知道这CPA的复习策略，现在就不会只是0.8个CPA了。2、《EXCEL这么用就对了》购买链接：Excel大全（ @方骥 ）是真正的Excel大神，与方大神相比，表哥龙逸凡是半桶水。方骥参与过EH出的很多本Excel图书的写作，这一本《Excel这么用就对了》是他独立写作的作品。书如其名，本书没有华丽的炫技，写的都是多年使用心得提炼出的精华，将最常用的功能最关键的技术，结合制表的思路方法、使用的理念和习惯，写就了本书。内容丰富而精炼，有术更有道！值得每一位想正确、快速掌握Excel的人士阅读3、《智能时代财务管理》购买链接：本书讲述了人工智能时代来临时，财务人将面对怎样的环境变化、组织与模式变革，需要进行怎样的能力与认知的提升，从财务人视角展示了大数据、云计算、区块链、人工智能等新技术的概念及财务应用场景。笔者通过提出人工智能时代财务信息化概念架构，以及大量财务智能应用场景设想，用图文并茂的精彩篇章将读者带入未来财务时空。未来已来，人工智能时代的财务将不再是一项技术工作，而是基于智能场景的创意活动，本书将帮助CFO、中高层企业财务管理者、财务与会计专业人士、高校财务专业教师与学生、管理咨询顾问、财务信息化顾问及工程师等人群打开思想腾飞的那扇窗户。作者是董皓是平安集团的财务副总裁，之前在四大所安永做高级财务经理，对人工智能给财务造成的应用有深刻的洞见，十多年磨一剑，很有前瞻性的一本书。值得一读4、《谁说菜鸟不会数据分析》 （工具篇）购买链接：本书是《谁说菜鸟不会数据分析》（入门篇）的姊妹篇，本书接着细致梳理了数据分析工作的完整流程，基于常用的Excel，精心挑选能够提高效率的常用工具来细致讲解，这些工具涵盖数据处理（Microsoft Access、Query）、数据分析（PowerPivot、Excel数据分析工具库）、数据呈现（水晶易表）和报告自动化（VBA）。5、《PPT进化：如何设计一份惊艳的PPT》购买链接：这是一本能让你的PPT升级进化的书，适合已经掌握入门级操作，希望在审美和思路上有所突破的PPT制作者。这是专业设计师在PPT领域经验的沉淀，书中没有繁杂的操作，只有基于应用场景的解决办法。这不是一本面面俱到的冗长的工具书，而是一张简明藏宝图——指引你少走弯路，理出清晰的叙事逻辑，应用美观的图文混排模式，下载优质的免费商用素材……用惊艳的PPT征服观众的心。作者郦橙就是知乎大V @郦橙锦妖，在北美名校卡内基大学学设计，之前从AOL的5000员工中突颖而出给CEO设计PPT，开课讲过PPT课程，有9000的付费用户，五星级的口碑，这本书是作者设计思维的高度浓缩，看来就能用。}