楊梅

摘要：該文介紹了一種以Excel辦公軟件為平臺(tái)，以SUMPRODUCT函數(shù)為工具，應(yīng)用于在九宮格日記中進(jìn)行賬目統(tǒng)計(jì)的新方法。利用SUMPRODUCT函數(shù)對(duì)數(shù)組元素乘積求和的特點(diǎn)選取不連續(xù)單元格進(jìn)行賬目統(tǒng)計(jì)，實(shí)現(xiàn)日記與記賬二者之間的結(jié)合。

關(guān)鍵詞：Excel；SUMPRODUCT；統(tǒng)計(jì)；日記；賬目

中圖分類號(hào)：TP317文獻(xiàn)標(biāo)識(shí)碼：A文章編號(hào)：1009-3044(2012)20-4988-02

“九宮格”日記意為將一天的日記記錄區(qū)分為9個(gè)小方塊，每個(gè)方塊都有獨(dú)特的記錄任務(wù)?！熬艑m格日記”是晨間日記創(chuàng)始人佐藤傳發(fā)明的。他所倡導(dǎo)的“晨間日記”即每天早上用3分鐘寫日記近年來非常流行，很多人都通過寫晨間日記的方法，培養(yǎng)自己做事的計(jì)劃性、執(zhí)行力以及對(duì)于未來的夢(mèng)想，從而改變了命運(yùn)。

每日規(guī)律地進(jìn)行個(gè)人收支記賬不僅可以掌握個(gè)人或家庭收支情況，合理規(guī)劃消費(fèi)和投資同時(shí)還可以記錄生活、社會(huì)變化，特別對(duì)于小本經(jīng)商和創(chuàng)業(yè)的人員可以及時(shí)了解經(jīng)營動(dòng)態(tài)。

由于生活的高節(jié)奏等原因很少人即寫日記又記賬，該文通過在九宮格日記中嵌套記賬功能的方法將寫日記與記賬結(jié)合起來一次完成。

以往的Excel記賬模板形式通常是紙質(zhì)記賬本的電子版形式，以二維表形式組織數(shù)據(jù)，在表格最底端對(duì)一列數(shù)據(jù)進(jìn)行統(tǒng)計(jì)，形式固定使用不便。使用EXCEL做九宮格日記既可以在日記里放一些照片，同時(shí)也可以將日記加密，防止隱私泄露。各類記賬軟件雖然安裝方便卻不能根據(jù)用戶的需求進(jìn)行個(gè)人定制，缺乏靈活性。使用SUMPRODUCT函數(shù)進(jìn)行統(tǒng)計(jì)兼具使用方便和可擴(kuò)展的特性，一般對(duì)計(jì)算機(jī)稍微熟悉的人都可以進(jìn)行使用或根據(jù)需求更改模板[1]。

由于在九宮格日記中記賬僅僅是記錄每天的收支，若要對(duì)賬目數(shù)據(jù)進(jìn)行統(tǒng)計(jì)分析才能更好地利用數(shù)據(jù)。使用Excel中的SUM? PRODUCT函數(shù)可以方便地統(tǒng)計(jì)每周、每月或者每季度每年的收支情況。

SUMPRODUCT是Excel中自帶的一個(gè)數(shù)學(xué)類的函數(shù)，其基本功能是進(jìn)行數(shù)組元素乘積，并返回結(jié)果[2]。

通常SUMPRODUCT函數(shù)的格式為：SUMPRODUCT(array1,array2,array3, ...)

Array1, array2, array3, ...為2到30個(gè)數(shù)組，其相應(yīng)元素需要進(jìn)行相乘并求和。

SUMPRODUCT函數(shù)還有拓展用法，即利用其進(jìn)行多條件求和，語法為：

SUMPRODUCT((條件1)*(條件2)* (條件3) *…(條件n)*某區(qū)域)

上式可匯總同時(shí)滿足條件1、條件2到條件n的記錄指定區(qū)域的匯總金額。

該文利用SUMPRODUCT函數(shù)可以進(jìn)行多條件求和的特征可以在Excel中選擇位置具有規(guī)律的一些不連續(xù)單元格即就是每日中用于記賬的單元格求和。

上式意為：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)

此處使用的是絕對(duì)引用，即在指定位置引用單元格。如果公式所在單元格的位置改變，絕對(duì)引用保持不變。如果多行或多列地復(fù)制公式，絕對(duì)引用將不作調(diào)整[3]。

每篇日記占7列，第1篇收入合計(jì)單元格在b10，第31篇日志的收入合計(jì)x的列號(hào)計(jì)算如下：b對(duì)應(yīng)2，一共26個(gè)英文字母，

Int((2+30*7)/26)=8Mod((2+30*7), 26)= 4

8對(duì)應(yīng)h,4對(duì)應(yīng)d,所以x為hd，即31號(hào)的收入合計(jì)在hd10單元格。

從B10到HD10共212個(gè)單元格，即處于期間的第10行的所有單元格，然而根據(jù)需要僅需要B10,I10,P10…….等等列號(hào)相差為6的規(guī)律單元格共31個(gè)。

①匯總區(qū)域，從第1天收入合計(jì)B10到第31天收入合計(jì)HD10的第10行的全部共212個(gè)單元格。

②條件區(qū)域，COLUMN()函數(shù)將返回參數(shù)的列號(hào)，對(duì)應(yīng)到相應(yīng)的數(shù)字，即2,3,4……213

MOD函數(shù)將列號(hào)對(duì)7求余，當(dāng)余數(shù)為2時(shí)，即當(dāng)前單元格為B10,I10,P10……等等列號(hào)相差為7的規(guī)律單元格即就是收入合計(jì)單元格的值，此時(shí)函數(shù)返回值為TRUE,EXCEL經(jīng)過類型轉(zhuǎn)換將其轉(zhuǎn)換為1與對(duì)應(yīng)數(shù)據(jù)區(qū)域中當(dāng)前單元格的值相乘，否則就不在收入合計(jì)單元格，MOD函數(shù)返回值為0，與數(shù)據(jù)區(qū)域當(dāng)前對(duì)應(yīng)單元格乘積為0，結(jié)果不累加入月收入中。

即就是在從B10到HD10的全部單元格中逐一通過②判斷當(dāng)前單元格是否是收入合計(jì)單元格，若是則將當(dāng)前單元格的值進(jìn)行累加否則不累加。

3.1問題說明

1）該文討論的是2012年7月一個(gè)月共31篇日記的情況，需要其他月份將工作表復(fù)制粘貼后修改D4單元格的本月第1天的日期即可，大小月不影響統(tǒng)計(jì)結(jié)果。

2）做好7月1日日記后其他日記由其復(fù)制粘貼而成，仍須修改部分公式3）B1單元格為客戶生日

3.2其它函數(shù)及公式

除SUMPRODUCT函數(shù)外還使用到其他函數(shù)及公式，以7月1日日記為例其中部分函數(shù)及公式如下：累積日記篇數(shù)單元格F1="第" & D4-$D$4+1 & "篇"歲數(shù)單元格G1=DATEDIF(B1,(TODAY()),"y")&"歲"星期幾單元格E4 =SUM(D4)

收入合計(jì)單元格C10=SUM(C5:C9)支出合計(jì)單元格F10==SUM(G5:G9)月支出單元格F13=SUMPRODUCT($F$10:$HF$10*(MOD(COLUMN($F$10:$HF$10),7)=6))