龙源期刊网 http://www.qikan.com.cn

SUMPRODUCT函数在Excel九宫格日记账目统计中的运用

作者：杨梅

来源：《电脑知识与技术》2012年第20期

摘要：该文介绍了一种以Excel办公软件为平台，以SUMPRODUCT函数为工具，应用于在九宫格日记中进行账目统计的新方法。利用SUMPRODUCT函数对数组元素乘积求和的特点选取不连续单元格进行账目统计，实现日记与记账二者之间的结合。 关键词：Excel；SUMPRODUCT；统计；日记；账目

中图分类号：TP317文献标识码：A文章编号：1009-3044(2012)20-4988-02

“九宫格”日记意为将一天的日记记录区分为9个小方块，每个方块都有独特的记录任务。“九宫格日记”是晨间日记创始人佐藤传发明的。他所倡导的“晨间日记”即每天早上用3分钟写日记近年来非常流行，很多人都通过写晨间日记的方法，培养自己做事的计划性、执行力以及对于未来的梦想，从而改变了命运。

每日规律地进行个人收支记账不仅可以掌握个人或家庭收支情况，合理规划消费和投资同时还可以记录生活、社会变化，特别对于小本经商和创业的人员可以及时了解经营动态。 由于生活的高节奏等原因很少人即写日记又记账，该文通过在九宫格日记中嵌套记账功能的方法将写日记与记账结合起来一次完成。

以往的Excel记账模板形式通常是纸质记账本的电子版形式，以二维表形式组织数据，在表格最底端对一列数据进行统计，形式固定使用不便。使用EXCEL做九宫格日记既可以在日记里放一些照片，同时也可以将日记加密，防止隐私泄露。各类记账软件虽然安装方便却不能根据用户的需求进行个人定制，缺乏灵活性。使用SUMPRODUCT函数进行统计兼具使用方便和可扩展的特性，一般对计算机稍微熟悉的人都可以进行使用或根据需求更改模板[1]。 由于在九宫格日记中记账仅仅是记录每天的收支，若要对账目数据进行统计分析才能更好地利用数据。使用Excel中的SUM? PRODUCT函数可以方便地统计每周、每月或者每季度每年的收支情况。

SUMPRODUCT是Excel中自带的一个数学类的函数，其基本功能是进行数组元素乘积，并返回结果[2]。

通常SUMPRODUCT函数的格式为：SUMPRODUCT(array1,array2,array3, ...)

龙源期刊网 http://www.qikan.com.cn

Array1, array2, array3, ...为2到30个数组，其相应元素需要进行相乘并求和。 SUMPRODUCT函数还有拓展用法，即利用其进行多条件求和，语法为： SUMPRODUCT((条件1)*(条件2)* (条件3) *…(条件n)*某区域)

上式可汇总同时满足条件1、条件2到条件n的记录指定区域的汇总金额。

该文利用SUMPRODUCT函数可以进行多条件求和的特征可以在Excel中选择位置具有规律的一些不连续单元格即就是每日中用于记账的单元格求和。

上式意为：C13=B10*MOD(COLUMN(B10),7=2)+ B11*MOD(COLUMN(B11),7=2)+…… +HC10*MOD(COLUMN(HC10),7=2)+ HD10*MOD(COLUMN(HD10),7=2)

此处使用的是绝对引用，即在指定位置引用单元格。如果公式所在单元格的位置改变，绝对引用保持不变。如果多行或多列地复制公式，绝对引用将不作调整[3]。

每篇日记占7列，第1篇收入合计单元格在b10，第31篇日志的收入合计x的列号计算如下：b对应2，一共26个英文字母， Int((2+30*7)/26)=8Mod((2+30*7), 26)= 4

8对应h,4对应d,所以x为hd，即31号的收入合计在hd10单元格。

从B10到HD10共212个单元格，即处于期间的第10行的所有单元格，然而根据需要仅需要B10,I10,P10…….等等列号相差为6的规律单元格共31个。

①汇总区域，从第1天收入合计B10到第31天收入合计HD10的第10行的全部共212个单元格。

②条件区域，COLUMN()函数将返回参数的列号，对应到相应的数字，即2,3,4……213 MOD函数将列号对7求余，当余数为2时，即当前单元格为B10,I10,P10……等等列号相差为7的规律单元格即就是收入合计单元格的值，此时函数返回值为TRUE,EXCEL经过类型转换将其转换为1与对应数据区域中当前单元格的值相乘，否则就不在收入合计单元格，MOD函数返回值为0，与数据区域当前对应单元格乘积为0，结果不累加入月收入中。 即就是在从B10到HD10的全部单元格中逐一通过②判断当前单元格是否是收入合计单元格，若是则将当前单元格的值进行累加否则不累加。 3.1问题说明

龙源期刊网 http://www.qikan.com.cn

1）该文讨论的是2012年7月一个月共31篇日记的情况，需要其他月份将工作表复制粘贴后修改D4单元格的本月第1天的日期即可，大小月不影响统计结果。

2）做好7月1日日记后其他日记由其复制粘贴而成，仍须修改部分公式3）B1单元格为客户生日

3.2其它函数及公式

除SUMPRODUCT函数外还使用到其他函数及公式，以7月1日日记为例其中部分函数及公式如下：累积日记篇数单元格F1=\第\篇\岁数单元格G1=DATEDIF(B1,(TODAY()),\岁\星期几单元格E4 =SUM(D4)