步驟/方法:
【1】.首先用Excel 的WPS表格設(shè)定一個(gè)適合自己需要的表格模板,下圖左邊是每種物品的“入庫(kù)”、“出庫(kù)”和“結(jié)存”的月匯總,右邊則是每種物品每月按日期記錄的明細(xì)賬。其中左側(cè)的“入庫(kù)”或“出庫(kù)”月匯總填充顏色是和右側(cè)的“出庫(kù)數(shù)”或“入庫(kù)數(shù)”明細(xì)賬填充顏色相同,以方便查看,可以選擇自己喜歡的顏色填充。
【2】.一步一步來(lái)完善表格模板,圖中從H列到BS列共64列,用意是假設(shè)H列和I列為月初第一天,H列為“入庫(kù)數(shù)”,I列為“出庫(kù)數(shù)”;J列和K列為第二天;L列和M列為第三天……以此類推直到BP和BQ列為月末最后一天,而B(niǎo)R為“盤(pán)盈”,BS為“盤(pán)虧”。
【3】.長(zhǎng)表格操作起來(lái)很不方便,就要用到WPS表格的“窗口凍結(jié)”功能來(lái)實(shí)現(xiàn)方便操作。
方法是選,H3單元格,選擇“視圖”選項(xiàng)卡,單擊“凍結(jié)窗格”。
通過(guò)“窗口凍結(jié)”功能,可以看到月匯總區(qū)和相對(duì)應(yīng)的明細(xì)賬分得很清晰明了,包括表頭名稱也很美觀,利用表格的“滾動(dòng)鍵”操作起來(lái)很便。
【4】.隨意地添加一些內(nèi)容和數(shù)據(jù)來(lái)檢測(cè)
一下,分別在月匯總區(qū)和相對(duì)應(yīng)的明細(xì)賬數(shù)據(jù)區(qū)添加有效的內(nèi)容和數(shù)值。
【5】.有了有效的數(shù)據(jù)以后,接下來(lái)要做的就是如何運(yùn)用“函數(shù)公式”來(lái)實(shí)現(xiàn)自動(dòng)統(tǒng)計(jì)一大堆的數(shù)值了。
首先在E3中設(shè)置“本期入庫(kù)數(shù)”的月統(tǒng)計(jì)。
1)輸入公式:=SUMPRODUCT((MOD(H3:BS3),2)=0)*H3:BS3)
公式的作用其實(shí)是每隔N列求和,此處N=2。還可用下面的公式:
=SUMIF(H$1:BS$1,">0",H3:BS3)
或下面的數(shù)組公式,按Ctrl+Shift+Enter結(jié)束:
=SUM(IF(MOD(COLUMN(H3:BS3),2),,H3:BS3))
2)公式中用到的函數(shù)說(shuō)明;
①函數(shù)SUMPRODUCT
意義:在給定的幾組數(shù)組中,將數(shù)組間對(duì)應(yīng)的元素相乘,并返回乘積之和。語(yǔ)法 :
SUMPRODUCT(array1,array2,array3, ...)
參數(shù)array1,array2,array3, ... 為 2 到 30 個(gè)數(shù)組,其相應(yīng)元素需要進(jìn)行相乘并求和。數(shù)組參數(shù)必須具有相同的維數(shù),否則函數(shù) SUMPRODUCT 將返回錯(cuò)誤值“#VALUE!”。
函數(shù) SUMPRODUCT 將非數(shù)值型的數(shù)組元素作為 0 處理。
②函數(shù)MOD
意義:返回兩數(shù)相除的余數(shù)。結(jié)果的正負(fù)號(hào)與除數(shù)相同。 語(yǔ)法:
MOD(number,divisor)
參數(shù) number 為被除數(shù),divisor 為除數(shù)。說(shuō)明:
如果 divisor 為零,函數(shù) MOD 返回錯(cuò)誤值 #Div/0!。 函數(shù) MOD 可以借用函數(shù) INT 來(lái)表示:
MOD(n, d) = n - d*INT(n/d)
【6】.同樣在F3單元格設(shè)置“本期出庫(kù)數(shù)”月統(tǒng)計(jì)的公式:
=SUMPRODUCT((MOD(H3:BS3),2)=1)*H3:BS3)
用意同上。此處也可以下面的公式:
=SUMIF(H$1:BS$1,"",H3:BS3)
或下面的數(shù)組公式,按Ctrl+Shift+Enter結(jié)束:
=SUM(IF(MOD(COLUMN(H3:BS3),2),H3:BS3),)
【7】.想要算出“本期結(jié)存數(shù)量”就要在G3中輸入簡(jiǎn)單的“加,減”就好了,如上圖“D3+E3-F3”,上期結(jié)存數(shù)+本期入庫(kù)數(shù)-本期出庫(kù)數(shù)的結(jié)果就是“本期結(jié)存數(shù)”。
【8】.運(yùn)算公式全部設(shè)定好以后,選中“E3:G3”區(qū)域,點(diǎn)擊“+”號(hào)下拉填充公式,拉到自己需要的位置即可。
【9】.下拉填充公式以后,得到就是一張完整的“自動(dòng)生成月庫(kù)存統(tǒng)計(jì)表”,大功告成。
(本文轉(zhuǎn)摘自WPS論壇,有修改。)