第二章 賬簿管理

1、第二章 賬簿管理第二章 賬簿管理在“帳套13”打開“賬簿管理”文件夾新建6個工作表：如下圖本章要點第一節(jié) 總賬13第二節(jié) 現(xiàn)金、銀行日記賬13第三節(jié) 收入、成本、費用明細賬13第四節(jié) 庫存商品、原材料明細賬13第五節(jié) 分類明細賬13第六節(jié) 固定資產明細賬13重點提示：為了讓大家容易理解和掌握從憑證到賬簿由電腦自動登記過程，本章主要使用了的函數(shù)公式，而沒有應用VBA方面的內容。雖然函數(shù)公式較多，但有規(guī)律可循。只要理解了某個公式，很多相同的應用就用復制、粘貼即可。第一節(jié) 總賬13打開總賬13工作表，“sheet1”表頁命名為“總賬”，現(xiàn)以“庫存現(xiàn)金”科目為例，樣式如下：（表1）打印效果如下圖： 2

2、.1.1 設置（表1）表頭內容：總分類賬：合并單元格E1:G2；單元格格式水平對齊為居中，宋體，字號18，雙下劃線，上標會計科目名稱：合并單元格H1:I1,水平對齊為靠右，宋體，字號11會計科目代碼：合并單元格H2:I2，水平對齊為靠右，宋體，字號11單元格K1、K2：直接輸入科目名稱和科目代碼單元格L1:L3 (總賬與分類明細賬核對情況)是為輔助核對而設置的，不在總賬打印范圍內。表格邊框線為最細線2.1.2 設置（表1）表體內容： 憑證日期：格式為日期型，直接輸入。憑證編號：憑證起止號。起號：如單元格B5，公式為“=H:帳套13賬務管理2013年1月zw.xlsx記賬憑證匯總表!$H$5”。

3、含義是：其值從1月份的記賬憑證匯總表的單元格$H$5中取得。 連接符“-”：如單元格C5，直接輸入符號“-” 止號：如單元格D5，公式為“=H:帳套13賬務管理2013年1月zw.xlsx記賬憑證匯總表!$N$5”，含義是：其值從1月份的記賬憑證匯總表的單元格$N$5中取得。那么，2月份的憑證起止號就是從2月份記賬憑證匯總表的“$H$5”“$N$5”取得。以此類推選中區(qū)域B5:D5,單元格右下角出現(xiàn)“十”時，按住鼠標左鍵下拉到適當位置，放開鼠標左鍵，進行復制，然后選中B6:D6，將公式中1月份替換為2月份。這樣就使得2月的憑證起止號從2月份記賬憑證匯總表的單元格H5、N5中自動取值。摘要：直接

4、輸入；復制。借方：單元格F5，格式為數(shù)值型，保留千位分隔符，公式為“=H:帳套13賬務管理2013年1月zw.xlsx明細科目匯總表!$D$3”，含義是：其值是從1月明細科目匯總表的一級科目“庫存現(xiàn)金”的“借方金額”取數(shù)得到的值。參照“憑證編號”同樣方法，可以得到2月“借方”單元格F6的公式。貸方：單元格H5，格式為數(shù)值型，保留千位分隔符，公式為“=H:帳套13賬務管理2013年1月zw.xlsx明細科目匯總表!$E$3”，含義是：其值是從1月明 細科目匯總表的一級科目“庫存現(xiàn)金”的“貸方金額”取數(shù)得到的值。參照“憑證編號”同樣方法，可以得到2月“貸方”單元格H6的公式。借：單元格J4，公式為

5、“=IF(K4=,IF(K4=0,平,IF(K40,借,)”，含義是：如“余額”欄有不等于零的數(shù)值時，其值為“借”；如等于零，其值為“平”；如沒有數(shù)值時，其值為空。然后將公式復制到適當行（12月）即可。因財務制度規(guī)定，資產類和成本類科目的余額方向必須是借方，負債類和所有者權益類科目的余額方向必須是貸方。所以，當科目為負債類或所有者權益類時，將公式“=IF(K4=,IF(K4=0,平,IF(K40,借,)”中的“借”改為“貸”即可。余額： 單元格K4，格式為數(shù)值型，保留千位分隔符,公式為“=H:帳套13賬務管理2013年1月zw.xlsx明細科目匯總表!$C$3”。含義是：其值是從1月明細科目匯

6、總表的一級科目“庫存現(xiàn)金”的“期初余額”取數(shù)得到的值。單元格K5，格式為數(shù)值型，保留千位分隔符,公式為“=IF(D5=0,ROUND(K4+F5-H5,2)”。含義是：如當月沒有憑證記錄時，余額欄就為空；否則就是期初余額加上本月借方金額減去本月貸方金額。這是資產類和成本類科目的余額計算公式。 而負債類和所有者權益類科目的余額計算公式為“=IF(D5=0,ROUND(K4-F5+H5,2)”， 含義是：如當月沒有憑證記錄時，余額欄就為空；否則就是期初余額加上本月貸方金額減去本月借方金額?！袄塾嬚叟f”“壞賬準備”的資產類科目的減方科目，所以其公式同負債類和所有者權益類科目計算公式。然后，復制單元格

7、K5，到需要行即可。本年累計：使用求和公式即可?！?SUM(F5:F16)”2.1.3 其他總賬科目設置方法（如：銀行存款）結果如下：方法：復制“庫存現(xiàn)金”樣式，設置表體內容的公式即可。在本節(jié)中，總賬里每個科目的借貸方數(shù)據(jù)來源是明細科目匯總表中相對應的一級科目余額。2.1.4 總分類賬的L列“總賬與分類明細賬核對情況”是輔助列，不用時隱藏。一般在月底，進行和分類明細賬對賬用。比如，1月底庫存現(xiàn)金總賬（表1）和明細賬核對，單元格L5其公式格式是：“=IF(K5=H:帳套13賬簿管理現(xiàn)金、銀行日記賬13.xlsx庫存現(xiàn)金!$F$23,)”，含義是：庫存現(xiàn)金總賬余額等于明細賬余額，則核對無誤，出現(xiàn)符

8、號“”。否則為不正確，出現(xiàn)符號“”。2.1.5 美化賬簿界面：2.1.5.1 將帳頁以外的行、列不顯示。 選中帳頁最后列的右邊的這一列，同時按Ctrl+Shift+ 組合鍵，這樣就可以快速選中帳頁以外右邊的所有列，然后點擊鼠標右鍵，選擇隱藏即可。選中選中帳頁最后行的下邊的這一行，同時按Ctrl+Shift+ 組合鍵，這樣就可以快速選中帳頁以外下邊的所有行，然后點擊鼠標右鍵，選擇隱藏即可。如下圖2.1.5.2 將帳頁以外的背景色顯示成你喜歡的顏色 選中帳頁最后列的右邊的這一列，同時按Ctrl+Shift+ 組合鍵，這樣就可以快速選中帳頁以外右邊的所有列，然后點鼠標右鍵，選擇填充色即可。選中帳頁最

9、后行的下邊的這一行，同時按Ctrl+Shift+ 組合鍵，這樣就可以快速選中帳頁以外下邊的所有行，然后點擊鼠標右鍵，選擇填充色即可2.1.5.3 帳頁格式：具有零值的單元格中不顯示零值 點擊【office按鈕】-【Excel選項】- 【高級】- 在【在具有零值的單元格中顯示零】前去掉對勾。 在以后所講的帳頁格式設置中，均采用這樣的方式，不在贅述。第二節(jié) 現(xiàn)金、銀行日記賬13這一節(jié)中，現(xiàn)金、銀行日記賬13的借貸方數(shù)據(jù)來源是從憑證記錄表中取來。符合會計原則要求。樣式如下：（2-1-1表）（2-1-2表）（這里的現(xiàn)金日記賬銀行存款日記賬是會計人員的賬簿，不同于第四章資金管理里的現(xiàn)金、銀行日記賬。第四

10、章是專為出納人員進行資金管理而設置的。）打開現(xiàn)金、銀行日記賬13工作表，“sheet1”表頁命名為“庫存現(xiàn)金”，“sheet2”表頁命名為“銀行存款-中行”，“sheet3”表頁命名為“銀行存款-農行”，“sheet4”表頁命名為“銀行存款-建行”。 在設置（2-1-1表）和（2-1-2表）的表體內容前，首先，要在憑證記錄表進行指定設置，然后在現(xiàn)金和銀行日記賬中設置公式，使得當期憑證記錄表有現(xiàn)金或銀行業(yè)務記錄時，庫存現(xiàn)金和銀行日記賬就會自動從憑證記錄表中取數(shù)，將有關記錄分別、全面反映在庫存現(xiàn)金和銀行日記賬中。2.2.1 在憑證記錄表進行指定設置首先，打開文件夾“賬務管理”中的“2013年1月z

11、w.xlsm”工作表，進入“憑證記錄表”頁面。在單元格R2、S2、T2、U2、，輸入科目名稱（根據(jù)賬簿要反映的科目名稱，不論科目級次）。然后，在單元格分別輸入公式R3 公式為“=IF(D3=庫存現(xiàn)金,ROW(),)”S3 公式為“=IF(D3=銀行存款-中國銀行昆明市理工大支行,ROW(),)”T3 公式為“=IF(D3=銀行存款-中國農業(yè)銀行西壩支行,ROW(),)”U3 公式為“=IF(K3=應收票據(jù),ROW(),)”.選中單元格R3、S3、T3、U3、，下拉復制到第600行次即可。這時，上圖的單元格S5、S7、T6，分別出現(xiàn)5、7、6數(shù)字，表示憑證記錄表中第5行和第7行，有“銀行存款-中

12、國銀行昆明理工大支行”業(yè)務記錄；第6行有“銀行存款-中國農業(yè)銀行西壩支行”業(yè)務記錄。為現(xiàn)金、銀行日記賬13借貸方取數(shù)提供條件。在實際應用中，如有新增開設的銀行戶頭，就在已經設置好的科目最后增加指定設置。比如：新增開設的“建設銀行”戶頭，那么就在憑證記錄表最后科目“原材料46色帶”列之后，指定設置。單元格EA3的公式為：“=IF(D3=銀行存款-中國建設銀行昆明建設路支行,ROW(),)”。在這里的科目指定設置不分前后次序，如有新增科目需要在帳頁中單獨反映，就在最后列新增指定設置即可。如下圖（在設置好后進行隱藏R、S、T、 EA 各列）2.2.2 現(xiàn)金、銀行存款13設置界面切換回現(xiàn)金、銀行日記賬

13、13工作表,對“庫存現(xiàn)金”“銀行存款”進行取數(shù)公式設置2.2.2.1 （2-1-1表）為例，“庫存現(xiàn)金”的公式為： 憑證日期：A5 “=IF(ISERROR(SMALL(2013年1月zw.xlsx憑證記錄表!R$1:R$65536,ROW(A1),0,INDEX(2013年1月zw.xlsx憑證記錄表!A$1:A$65536,SMALL(2013年1月zw.xlsx憑證記錄表!R$1:R$65536,ROW(A1)”其含義是：當1月份憑證記錄表R列（即指定設置的庫存現(xiàn)金科目）出現(xiàn)數(shù)值時，則庫存現(xiàn)金表頁的憑證日期就會自動從憑證記錄表中憑證日期欄（A$1:A$65536區(qū)域）取值。且取值結果會自

14、動按順序無空格排列顯示。憑證編號：B5 “=IF(ISERROR(SMALL(2013年1月zw.xlsx憑證記錄表!R$1:R$65536,ROW(A1),INDEX(2013年1月zw.xlsx憑證記錄表!B$1:B$65536,SMALL(2013年1月zw.xlsx憑證記錄表!R$1:R$65536,ROW(A1)”其含義是：當1月份憑證記錄表R列（即指定設置的庫存現(xiàn)金科目）出現(xiàn)數(shù)值時，則庫存現(xiàn)金表頁的憑證編號就會自動從憑證記錄表中憑證編號欄（B$1:B$65536區(qū)域）取值。且取值結果會自動按順序無空格排列顯示。摘要：C5 “=IF(ISERROR(SMALL(2013年1月zw.x

15、lsx憑證記錄表!R$1:R$65536,ROW(A1),INDEX(2013年1月zw.xlsx憑證記錄表!E$1:E$65536,SMALL(2013年1月zw.xlsx憑證記錄表!R$1:R$65536,ROW(A1)” 其含義是：當1月份憑證記錄表R列（即指定設置的庫存現(xiàn)金科目）出現(xiàn)數(shù)值時，則庫存現(xiàn)金表頁的摘要就會自動從憑證記錄表中憑證摘要欄（E$1:E$65536區(qū)域）取值。且取值結果會自動按順序無空格排列顯示。借方金額：D5 “=IF(C5=本月合計,SUMPRODUCT(MONTH($A4:$A$5)=MONTH($A4)*D4:D$5),IF(C5=本年累計,SUMIF($C4

16、:$C$5,本月合計,D$5),IF(ISERROR(SMALL(2013年1月zw.xlsx憑證記錄表!R$1:R$65536,ROW(A1),INDEX(2013年1月zw.xlsx憑證記錄表!I$1:I$65536,SMALL(2013年1月zw.xlsx憑證記錄表!R$1:R$65536,ROW(A1)” 其含義是：當庫存現(xiàn)金表頁C列（摘要欄）出現(xiàn)“本月合計”或“本年累計”時，借方金額等于本月借方發(fā)生額合計數(shù)(自動計算)，或等于本年借方發(fā)生額累計數(shù)（自動計算）。否則就是：當1月份憑證記錄表R列（即指定設置的庫存現(xiàn)金科目）出現(xiàn)數(shù)值時，則庫存現(xiàn)金表頁的借方金額就會自動從憑證記錄表中借方金額

17、欄（I$1:I$65536區(qū)域）取值。且取值結果會自動按順序無空格排列顯示。貸方金額：E5 “=IF(C5=本月合計,SUMPRODUCT(MONTH($A4:$A$5)=MONTH($A4)*E4:E$5),IF(C5=本年累計,SUMIF($C4:$C$5,本月合計,E$5),IF(ISERROR(SMALL(2013年1月zw.xlsx憑證記錄表!R$1:R$65536,ROW(A1),INDEX(2013年1月zw.xlsx憑證記錄表!J$1:J$65536,SMALL(2013年1月zw.xlsx憑證記錄表!R$1:R$65536,ROW(A1)” 其含義是：當庫存現(xiàn)金表頁C列（摘要

18、欄）出現(xiàn)“本月合計”或“本年累計”時，貸方金額等于本月貸方發(fā)生額合計數(shù)（自動計算），或等于本年貸方發(fā)生額累計數(shù)（自動計算）。否則就是：當1月份憑證記錄表R列（即指定設置的庫存現(xiàn)金科目）出現(xiàn)數(shù)值時，則庫存現(xiàn)金表頁的貸方金額就會自動從憑證記錄表中貸方金額欄（J$1:J$65536區(qū)域）取值。且取值結果會自動按順序無空格排列顯示。（借方）余額：F5 “=IF(AND(D5=,E5=),IF(OR(C5=本月合計,C5=本年累計),F4,ROUND(F4+D5-E5,2)” 其含義是:當庫存現(xiàn)金表頁的D列（借方金額）和E列（貸方金額）無數(shù)值時，F(xiàn)列（余額）為空。當C列（摘要）是“本月合計”或“本年累計

19、”時，F(xiàn)列（余額）等于上期余額，否則，就是等于上期余額+本期借方金額-本期貸方金額。選中區(qū)域A5:F5,單元格右下角出現(xiàn)“十”時，按住鼠標左鍵下拉到適當位置300行（根據(jù)業(yè)務量需要），放開鼠標左鍵即可，這樣就使得“庫存現(xiàn)金”帳頁反映出1月所有與現(xiàn)金業(yè)務有關的所用憑證內容。從而自動完成了憑證到賬簿登記過程。結果如下：現(xiàn)在我們看看復制后的公式有何變化：單元格A6的公式為“=IF(ISERROR(SMALL(2013年1月zw.xlsx憑證記錄表!R$1:R$65536,ROW(A2),0,INDEX(2013年1月zw.xlsx憑證記錄表!A$1:A$65536,SMALL(2013年1月zw.x

20、lsx憑證記錄表!R$1:R$65536,ROW(A2)”和單元格A5的公式“=IF(ISERROR(SMALL(2013年1月zw.xlsx憑證記錄表!R$1:R$65536,ROW(A1),0,INDEX(2013年1月zw.xlsx憑證記錄表!A$1:A$65536,SMALL(2013年1月zw.xlsx憑證記錄表!R$1:R$65536,ROW(A1)”進行比較，公式中只有ROW(A1)的A1變成了A2。其他單元格在復制后的變化也同樣只有ROW(A1)的A1變成了A2、A3、A4 。這一點我們在會計期間的次月，進行替換公式將應用到。次月的單元格取數(shù)公式也是從ROW(A1)開始。2.2

21、.2.2 （2-1-2表）為例，“銀行存款-中行”的公式為：憑證日期：A5 “=IF(ISERROR(SMALL(2013年1月zw.xlsx憑證記錄表!S$1:S$65536,ROW(A1),0,INDEX(2013年1月zw.xlsx憑證記錄表!A$1:A$65536,SMALL(2013年1月zw.xlsx憑證記錄表!S$1:S$65536,ROW(A1)”憑證編號：B5 “=IF(ISERROR(SMALL(2013年1月zw.xlsx憑證記錄表!S$1:S$65536,ROW(A1),INDEX(2013年1月zw.xlsx憑證記錄表!B$1:B$65536,SMALL(2013年1

22、月zw.xlsx憑證記錄表!S$1:S$65536,ROW(A1)”摘要：C5 “=IF(ISERROR(SMALL(2013年1月zw.xlsx憑證記錄表!S$1:S$65536,ROW(A1),INDEX(2013年1月zw.xlsx憑證記錄表!E$1:E$65536,SMALL(2013年1月zw.xlsx憑證記錄表!S$1:S$65536,ROW(A1)”借方金額：D5 “=IF(C5=本月合計,SUMPRODUCT(MONTH($A4:$A$5)=MONTH($A4)*D4:D$5),IF(C5=本年累計,SUMIF($C4:$C$5,本月合計,D$5),IF(ISERROR(SMA

23、LL(2013年1月zw.xlsx憑證記錄表!S$1:S$65536,ROW(A1),INDEX(2013年1月zw.xlsx憑證記錄表!I$1:I$65536,SMALL(2013年1月zw.xlsx憑證記錄表!S$1:S$65536,ROW(A1)”貸方金額：E5 “=IF(C5=本月合計,SUMPRODUCT(MONTH($A4:$A$5)=MONTH($A4)*E4:E$5),IF(C5=本年累計,SUMIF($C4:$C$5,本月合計,E$5),IF(ISERROR(SMALL(2013年1月zw.xlsx憑證記錄表!S$1:S$65536,ROW(A1),INDEX(2013年1月

24、zw.xlsx憑證記錄表!J$1:J$65536,SMALL(2013年1月zw.xlsx憑證記錄表!S$1:S$65536,ROW(A1)”（借方）余額：F5 “=IF(AND(D5=,E5=),IF(OR(C5=本月合計,C5=本年累計),F4,ROUND(F4+D5-E5),2)”選中區(qū)域A5:F5,單元格右下角出現(xiàn)“十”時，按住鼠標左鍵下拉到適當位置300行(根據(jù)業(yè)務量需要)，放開鼠標左鍵即可，這樣就使得“銀行存款-中行”帳頁反映出1月所有與中行業(yè)務有關的所用憑證內容。結果如下：2.2.3 “本月合計” “本年累計”設置方法：要求：在財務日常工作中，“本月合計”“本年累計”是每月都會出

25、現(xiàn)，而且字體為紅色，整行有紅色下劃線EXCEL處理后，達到如下結果：當在摘要欄輸入“本月合計”或“本年累計”時，字體自動為紅色，且所在整行會自動出現(xiàn)紅色下劃線，同時整行出現(xiàn)背景色。下面著重介紹設置方法?！窘璺浇痤~】、【貸方金額】的“本月合計”“本年累計”會自動計算，其【（借方）余額】同樣會自動計算。這個功能已經在本節(jié)“借方金額”“貸方金額”的取數(shù)公式中已經設置好，即公式中的前部分，如“=IF(C5=本月合計,SUMPRODUCT(MONTH($A4:$A$5)=MONTH($A4)*D4:D$5),IF(C5=本年累計,SUMIF($C4:$C$5,本月合計,D$5),”；（借方）余額 公式中

26、也已實現(xiàn)自動計算功能。如“=IF(AND(D5=,E5=),IF(OR(C5=本月合計,C5=本年累計),F4,ROUND(F4+D5-E5),2)”下面著重介紹“當在摘要欄輸入“本月合計”或“本年累計”時，字體自動為紅色，且所在整行會自動出現(xiàn)紅色下劃線，同時整行出現(xiàn)背景色”的設置方法，現(xiàn)以銀行存款-建行表頁為例，如圖選中A列，點擊工具欄的【開始】，【條件格式】在【條件格式】下來菜單中選中“新建規(guī)則”， 在【選擇規(guī)則類型】中選中“使用公式確定要設置格式的單元格”，在【為符合此公式的值設置格式】下輸入公式“=or(c1=本月合計,c1=本年累計)”然后，點擊【格式】,邊框的下線顏色選紅色，實線。

27、 如下再將填充色選為淡綠色（可隨個人喜好選擇填充色）確定。 再確定，結果如下圖選中B列，重復以上操作步驟。結果如下選擇C列，重復操作步驟，同時在點擊【格式】時,除邊框下線顏色選紅色，實線外，字體顏色也要選紅色。這是因為本列中會出現(xiàn)文字“本月合計”“本年累計”。如下選擇D列，重復前面操作A列步驟選擇E列，重復操作A列步驟選擇F列，重復操作A列步驟完成以上步驟后，就會得到如下結果這樣一來，當你在本表中C列的任意一行，輸入“本月合計”或“本年累計”時，“本月合計”和“本年累計”字體顏色就會自動為紅色，同時所在行就會自動出現(xiàn)紅色下劃線，且“借方金額”、“貸方金額”、“余額”的本月合計和本年累計數(shù)值也會

28、自動計算出來。如果要查看、編輯或修改、刪除以上新建的單元格條件格式，可以在【條件格式】下來菜單中選中“管理規(guī)則”進行操作，如下圖2.2.4 當會計期間到次月時，將公式進行替換即可?，F(xiàn)以現(xiàn)金日記賬的“庫存現(xiàn)金”為例說明，如下圖（2-1-3表）從（2-1-3表）可看出第23行已經反映完1月份所有有關庫存現(xiàn)金賬務記錄，當在C24單元格輸入“本月合計”時，單元格D24、E24、F24就會自動計算數(shù)值，且“本月合計”字體為紅色，所在行有紅色下劃線和背景色。從第25行起，反映的應該是2月份的庫存現(xiàn)金賬務記錄。由于我們在設置本表格式時已經將各單元的公式復制到了第300行，所以，我們將第25行單元格公式進行替

29、換。首先我們看看替換前的各單元格的公式：A25 “=IF(ISERROR(SMALL(2013年1月zw.xlsx憑證記錄表!R$1:R$65536,ROW(A21),0,INDEX(2013年1月zw.xlsx憑證記錄表!A$1:A$65536,SMALL(2013年1月zw.xlsx憑證記錄表!R$1:R$65536,ROW(A21)”B25 “=IF(ISERROR(SMALL(2013年1月zw.xlsx憑證記錄表!R$1:R$65536,ROW(A21),INDEX(2013年1月zw.xlsx憑證記錄表!B$1:B$65536,SMALL(2013年1月zw.xlsx憑證記錄表!R

30、$1:R$65536,ROW(A21)”C25 “=IF(ISERROR(SMALL(2013年1月zw.xlsx憑證記錄表!R$1:R$65536,ROW(A21),INDEX(2013年1月zw.xlsx憑證記錄表!E$1:E$65536,SMALL(2013年1月zw.xlsx憑證記錄表!R$1:R$65536,ROW(A21)”D25 “=IF(C25=本月合計,SUMPRODUCT(MONTH($A$5:$A24)=MONTH($A24)*D$5:D24),IF(C25=本年累計,SUMIF($C$5:$C24,本月合計,D$5),IF(ISERROR(SMALL(2013年1月zw

31、.xlsx憑證記錄表!R$1:R$65536,ROW(A21),INDEX(2013年1月zw.xlsx憑證記錄表!I$1:I$65536,SMALL(2013年1月zw.xlsx憑證記錄表!R$1:R$65536,ROW(A21)”E25 “=IF(C25=本月合計,SUMPRODUCT(MONTH($A$5:$A24)=MONTH($A24)*E$5:E24),IF(C25=本年累計,SUMIF($C$5:$C24,本月合計,E$5),IF(ISERROR(SMALL(2013年1月zw.xlsx憑證記錄表!R$1:R$65536,ROW(A21),INDEX(2013年1月zw.xlsx

32、憑證記錄表!J$1:J$65536,SMALL(2013年1月zw.xlsx憑證記錄表!R$1:R$65536,ROW(A21)”然后進行公式替換，方法如下：選中區(qū)域A25:E25 ,工具欄“開始”“查找和選擇”“替換”，或按鍵Ctrl+H,出現(xiàn)替換界面。將公式中“1月”【全部替換】成“2月”，“A21”【全部替換】為“A1”.如下：公式中“ROW(A21)”的“21”，表示的是從1月份取數(shù)公式為開始行（即第一行）算起，到此行行數(shù)。 替換后，單元格A25:E25中的數(shù)值將是從2月份的記賬憑證記錄表中提取。單元格公式變?yōu)锳25 “=IF(ISERROR(SMALL(2013年2月zw.xlsx憑

33、證記錄表!R$1:R$65536,ROW(A1),0,INDEX(2013年2月zw.xlsx憑證記錄表!A$1:A$65536,SMALL(2013年2月zw.xlsx憑證記錄表!R$1:R$65536,ROW(A1)”B25 “=IF(ISERROR(SMALL(2013年2月zw.xlsx憑證記錄表!R$1:R$65536,ROW(A1),INDEX(2013年2月zw.xlsx憑證記錄表!B$1:B$65536,SMALL(2013年2月zw.xlsx憑證記錄表!R$1:R$65536,ROW(A1)”C25 “=IF(ISERROR(SMALL(2013年2月zw.xlsx憑證記錄表

34、!R$1:R$65536,ROW(A1),INDEX(2013年2月zw.xlsx憑證記錄表!E$1:E$65536,SMALL(2013年2月zw.xlsx憑證記錄表!R$1:R$65536,ROW(A1)”D25 “=IF(C25=本月合計,SUMPRODUCT(MONTH($A$5:$A24)=MONTH($A24)*D$5:D24),IF(C25=本年累計,SUMIF($C$5:$C24,本月合計,D$5),IF(ISERROR(SMALL(2013年2月zw.xlsx憑證記錄表!R$1:R$65536,ROW(A1),INDEX(2013年2月zw.xlsx憑證記錄表!I$1:I$6

35、5536,SMALL(2013年2月zw.xlsx憑證記錄表!R$1:R$65536,ROW(A1)”E25 “=IF(C25=本月合計,SUMPRODUCT(MONTH($A$5:$A24)=MONTH($A24)*E$5:E24),IF(C25=本年累計,SUMIF($C$5:$C24,本月合計,E$5),IF(ISERROR(SMALL(2013年2月zw.xlsx憑證記錄表!R$1:R$65536,ROW(A1),INDEX(2013年2月zw.xlsx憑證記錄表!J$1:J$65536,SMALL(2013年2月zw.xlsx憑證記錄表!R$1:R$65536,ROW(A1)”選中區(qū)

36、域A25:E25, 單元格右下角出現(xiàn)“十”時，按住鼠標左鍵下拉到適當位置300行.這樣就覆蓋掉第25行到第300行的原公式即可。從（2-1-3表）可知，第38行已經是2月份“庫存現(xiàn)金”記錄的最后一行，那么我們在單元格C39輸入“本月合計”、C40輸入“本年累計”后，其字體會自動為紅色，所在行有背景色和紅色下劃線，且借方金額、貸方金額、等等會自動計算出來。從第41行起就應該是記錄3月份的庫存現(xiàn)金憑證內容了，然后利用替換功能將公式替換：選中區(qū)域A41:E41，將公式中“2月”【全部替換】成“3月”，“A17”【全部替換】為“A1”。最后再復制即可。第三節(jié) 收入、成本、費用明細賬13打開收入、成本、

37、費用明細賬工作表，根據(jù)賬簿需要設置以下表頁，如：sheet1為“主營業(yè)務收入”Sheet2為“主營業(yè)務成本”sheet3為“制造費用”. “生產成本” “管理費用” “銷售費用” “財務費用” “營業(yè)稅金及附加” “所得稅” “其他業(yè)務收入” “其他業(yè)務支出” “營業(yè)外收入” “營業(yè)外支出” “以前年度損益調整” 等等（如果企業(yè)經營產品種類較多，也可以產品單獨設置明細賬，如營業(yè)收入明細賬、成本明細賬、生產成本明細賬、費用明細賬等等。）現(xiàn)以“主營業(yè)務收入”和“管理費用”表頁為例說明2.3.1 主營業(yè)務收入“主營業(yè)務收入”表頁 如下圖（3-1），是以產品種類較少而設置的。（如果企業(yè)經營的產品種類較

38、多，且業(yè)務量發(fā)生頻繁，可以依據(jù)每個產品單設帳頁。）其中產品名稱（主營業(yè)務收入的二級科目）：1、 24s乳癖清膠囊 2、 36s乳癖清膠囊 3、 48s乳癖清膠囊 4、 24s溶栓腦通膠囊 5、 36s溶栓腦通膠囊 6、 24s惠血生膠囊 7、 36s惠血生膠囊 8、 乳果糖口服液主營業(yè)務收入（3-1）接上圖右邊從圖表的樣式可以看出：“主營業(yè)務收入”是含有數(shù)量金額的多欄式明細賬， 從第L列往后是“主營業(yè)務收入”的二級科目，即各個產品的銷售數(shù)據(jù)。其含銷售產品的數(shù)量、單價、金額，是從C列對應取值。（一般C列不用時隱藏）。當每到月底，在D列單元格輸入“本月合計”或“本年累計”時，匯總數(shù)據(jù)就自動計算出來

39、，且有背景色和紅色下劃線?！皢蝺r”欄不將參加匯總。2.3.1.1 “主營業(yè)務收入”表頁格式設置因為我們在前面已經講了很多表樣設置方法和過程，所以這里只講“本月合計”“本年累計”在D列出現(xiàn)時帳表顯示有關設置。（可參考第二節(jié)繼續(xù)學習） 選中A列，點擊工具欄的【開始】，【條件格式】 在【條件格式】下來菜單中選中“新建規(guī)則”， 在【選擇規(guī)則類型】中選中“使用公式確定要設置格式的單元格”，在【為符合此公式的值設置格式】下輸入公式“=or(d1=本月合計,d1=本年累計)”, 然后，點擊【格式】,邊框的下線顏色選紅色，實線。 如下再將填充色選為淡綠色（可隨個人喜好選擇填充色） 確定應用后。然后用同樣的方法

40、分別將除D列以外的其他列進行設置。 選擇D列，重復操作步驟，同時在點擊【格式】時,除邊框下線顏色選紅色，實線外，字體顏色也要選紅色。這是因為本列中會出現(xiàn)文字“本月合計”“本年累計”。如下這樣一來，當你在本表中D列的任意一行，輸入“本月合計”或“本年累計”時，“本月合計”和“本年累計”字體顏色就會自動為紅色，同時所在行就會自動出現(xiàn)紅色下劃線和背景色，且“借方金額”、“貸方金額”、“余額”以及二級明細科目（各產品）的本月合計和本年累計數(shù)值也會自動計算出來。在打印本帳頁時，需自定義頁眉和頁腳，如下 隱藏C列。因為C列是輔助項，是為二級科目取值服務，在打印帳頁時一般進行隱藏。2.3.1.2 “主營業(yè)務

41、收入”表頁取數(shù)公式設置首先，在憑證記錄表表頁我們已經指定設置了“主營業(yè)務收入”在DH列，如單元格 DH3公式“=IF(K3=主營業(yè)務收入,ROW(),)” 然后，在“主營業(yè)務收入”表頁（3-1）中，給各個單元格設置取值公式：憑證日期 A3 “=IF(ISERROR(SMALL(2013年1月zw.xlsx憑證記錄表!DH$1:DH$65536,ROW(A1),0,INDEX(2013年1月zw.xlsx憑證記錄表!A$1:A$65536,SMALL(2013年1月zw.xlsx憑證記錄表!DH$1:DH$65536,ROW(A1)” 其含義是：當1月份憑證記錄表DH列（即指定設置的主營業(yè)務收入

42、科目）出現(xiàn)數(shù)值時，則主營業(yè)務收入表頁的憑證日期就會自動從憑證記錄表中憑證日期欄（A$1:A$65536區(qū)域）取值。且取值結果會自動按順序無空格排列顯示。憑證編號B3 “=IF(ISERROR(SMALL(2013年1月zw.xlsx憑證記錄表!DH$1:DH$65536,ROW(A1),INDEX(2013年1月zw.xlsx憑證記錄表!B$1:B$65536,SMALL(2013年1月zw.xlsx憑證記錄表!DH$1:DH$65536,ROW(A1)” 其含義是：當1月份憑證記錄表DH列（即指定設置的主營業(yè)務收入科目）出現(xiàn)數(shù)值時，則主營業(yè)務收入表頁的憑證編號就會自動從憑證記錄表中憑證編號欄

43、（B$1:B$65536區(qū)域）取值。且取值結果會自動按順序無空格排列顯示?？颇棵QC3 “=IF(ISERROR(SMALL(2013年1月zw.xlsx憑證記錄表!DH$1:DH$65536,ROW(A1),INDEX(2013年1月zw.xlsx憑證記錄表!D$1:D$65536,SMALL(2013年1月zw.xlsx憑證記錄表!DH$1:DH$65536,ROW(A1)” 其含義是：當1月份憑證記錄表DH列（即指定設置的主營業(yè)務收入科目）出現(xiàn)數(shù)值時，則主營業(yè)務收入表頁的科目名稱就會自動從憑證記錄表中憑證科目名稱欄（D$1:D$65536區(qū)域）取值。且取值結果會自動按順序無空格排列顯示。

44、摘要 D3 “=IF(ISERROR(SMALL(2013年1月zw.xlsx憑證記錄表!DH$1:DH$65536,ROW(A1),INDEX(2013年1月zw.xlsx憑證記錄表!E$1:E$65536,SMALL(2013年1月zw.xlsx憑證記錄表!DH$1:DH$65536,ROW(A1)” 其含義是：當1月份憑證記錄表DH列（即指定設置的主營業(yè)務收入科目）出現(xiàn)數(shù)值時，則主營業(yè)務收入表頁的摘要就會自動從憑證記錄表中憑證摘要欄（E$1:E$65536區(qū)域）取值。且取值結果會自動按順序無空格排列顯示。數(shù)量E3 “=IF(D3=本月合計,SUMPRODUCT(MONTH($A2:$A$

45、3)=MONTH($A2)*E2:E$3),IF(D3=本年累計,SUMIF($D2:$D$3,本月合計,E$3),IF(G3=0,0,IF(ISERROR(SMALL(2013年1月zw.xlsx憑證記錄表!DH$1:DH$65536,ROW(A1),INDEX(2013年1月zw.xlsx憑證記錄表!G$1:G$65536,SMALL(2013年1月zw.xlsx憑證記錄表!DH$1:DH$65536,ROW(A1)” 其含義是：當主營業(yè)務收入表頁D列（摘要欄）出現(xiàn)“本月合計”或“本年累計”時，（借方）數(shù)量等于本月（借方）數(shù)量合計數(shù)(自動計算)，或等于本年（借方）數(shù)量累計數(shù)（自動計算）。否

46、則就是：當1月份憑證記錄表DH列（即指定設置的主營業(yè)務收入科目）出現(xiàn)數(shù)值時，則主營業(yè)務收入表頁的（借方）數(shù)量就會自動從憑證記錄表中數(shù)量欄（G$1:G$65536區(qū)域）取值。且取值結果會自動按順序無空格排列顯示。單價F3 “=IF(OR(D3=本月合計,D3=本年累計),0,IF(G3=0,0,IF(ISERROR(SMALL(2013年1月zw.xlsx憑證記錄表!DH$1:DH$65536,ROW(A1),INDEX(2013年1月zw.xlsx憑證記錄表!H$1:H$65536,SMALL(2013年1月zw.xlsx憑證記錄表!DH$1:DH$65536,ROW(A1)” 其含義是：當主

47、營業(yè)務收入表頁D列（摘要欄）出現(xiàn)“本月合計”或“本年累計”時，（借方）單價等于零，即單價不匯總。否則就是：當1月份憑證記錄表DH列（即指定設置的主營業(yè)務收入科目）出現(xiàn)數(shù)值時，則主營業(yè)務收入表頁的（借方）單價就會自動從憑證記錄表中單價欄（H$1:H$65536區(qū)域）取值。且取值結果會自動按順序無空格排列顯示。借方金額G3 “=IF(D3=本月合計,SUMPRODUCT(MONTH($A2:$A$3)=MONTH($A2)*G2:G$3),IF(D3=本年累計,SUMIF($D2:$D$3,本月合計,G$3),IF(ISERROR(SMALL(2013年1月zw.xlsx憑證記錄表!DH$1:DH

48、$65536,ROW(A1),INDEX(2013年1月zw.xlsx憑證記錄表!I$1:I$65536,SMALL(2013年1月zw.xlsx憑證記錄表!DH$1:DH$65536,ROW(A1)” 其含義是：當主營業(yè)務收入表頁D列（摘要欄）出現(xiàn)“本月合計”或“本年累計”時，借方金額等于本月借方發(fā)生額合計數(shù)(自動計算)，或等于本年借方發(fā)生額累計數(shù)（自動計算）。否則就是：當1月份憑證記錄表DH列（即指定設置的主營業(yè)務收入科目）出現(xiàn)數(shù)值時，則主營業(yè)務收入表頁的借方金額就會自動從憑證記錄表中借方金額欄（I$1:I$65536區(qū)域）取值。且取值結果會自動按順序無空格排列顯示。數(shù)量H3 “=IF(D

49、3=本月合計,SUMPRODUCT(MONTH($A2:$A$3)=MONTH($A2)*H2:H$3),IF(D3=本年累計,SUMIF($D2:$D$3,本月合計,H$3),IF(J3=0,0,IF(ISERROR(SMALL(2013年1月zw.xlsx憑證記錄表!DH$1:DH$65536,ROW(A1),INDEX(2013年1月zw.xlsx憑證記錄表!G$1:G$65536,SMALL(2013年1月zw.xlsx憑證記錄表!DH$1:DH$65536,ROW(A1)” 其含義是：當主營業(yè)務收入表頁D列（摘要欄）出現(xiàn)“本月合計”或“本年累計”時，（貸方）數(shù)量等于本月（貸方）數(shù)量合

50、計數(shù)(自動計算)，或等于本年（貸方）數(shù)量累計數(shù)（自動計算）。否則就是：當1月份憑證記錄表DH列（即指定設置的主營業(yè)務收入科目）出現(xiàn)數(shù)值時，則主營業(yè)務收入表頁的（貸方）數(shù)量就會自動從憑證記錄表中數(shù)量欄（G$1:G$65536區(qū)域）取值。且取值結果會自動按順序無空格排列顯示。 單價I3“=IF(OR(D3=本月合計,D3=本年累計),0,IF(J3=0,0,IF(ISERROR(SMALL(2013年1月zw.xlsx憑證記錄表!DH$1:DH$65536,ROW(A1),INDEX(2013年1月zw.xlsx憑證記錄表!H$1:H$65536,SMALL(2013年1月zw.xlsx憑證記錄表

51、!DH$1:DH$65536,ROW(A1)” 其含義是：當主營業(yè)務收入表頁D列（摘要欄）出現(xiàn)“本月合計”或“本年累計”時，（貸方）單價等于零，即單價不匯總。否則就是：當1月份憑證記錄表DH列（即指定設置的主營業(yè)務收入科目）出現(xiàn)數(shù)值時，則主營業(yè)務收入表頁的（貸方）單價就會自動從憑證記錄表中單價欄（H$1:H$65536區(qū)域）取值。且取值結果會自動按順序無空格排列顯示。貸方金額J3 “=IF(D3=本月合計,SUMPRODUCT(MONTH($A2:$A$3)=MONTH($A2)*J2:J$3),IF(D3=本年累計,SUMIF($D2:$D$3,本月合計,J$3),IF(ISERROR(SM

52、ALL(2013年1月zw.xlsx憑證記錄表!DH$1:DH$65536,ROW(A1),INDEX(2013年1月zw.xlsx憑證記錄表!J$1:J$65536,SMALL(2013年1月zw.xlsx憑證記錄表!DH$1:DH$65536,ROW(A1)” 其含義是：當主營業(yè)務收入表頁D列（摘要欄）出現(xiàn)“本月合計”或“本年累計”時，貸方金額等于本月貸方發(fā)生額合計數(shù)(自動計算)，或等于本年貸方發(fā)生額累計數(shù)（自動計算）。否則就是：當1月份憑證記錄表DH列（即指定設置的主營業(yè)務收入科目）出現(xiàn)數(shù)值時，則主營業(yè)務收入表頁的貸方金額就會自動從憑證記錄表中貸方金額欄（J$1:J$65536區(qū)域）取值

53、。且取值結果會自動按順序無空格排列顯示。余額K3 “=IF(AND(G3=,J3=),IF(OR(D3=本月合計,D3=本年累計),K2,ROUND(K2-G3+J3,2)” 其含義是:當主營業(yè)務收入表頁的G列（借方金額）和J列（貸方金額）無數(shù)值時，K列（余額）為空。當D列（摘要）是“本月合計”或“本年累計”時，K列（余額）等于上期余額，否則，就是等于上期余額-本期借方金額+本期貸方金額。從L3到AE3 ，均為“主營業(yè)務收入”的明細科目，可按品名設置為數(shù)量金額式。明細科目1 如（24S乳癖清膠囊）數(shù)量 L3 公式為 “=IF(D3=本月合計,SUMPRODUCT(MONTH($A2:$A$3)=MONTH($A2)*L2:L$3),IF(D3=本年累計,SUMIF($D2:$D$3,本月合計,L$3),IF(C3=主營業(yè)務收入-24S乳癖清膠囊,H3,)” 其含義是：當主營業(yè)務收入表頁D列（摘要欄）出現(xiàn)“本月合計”或“本年累計”時，數(shù)量等于本月（貸方）數(shù)量合計數(shù)(自動計算)，或等于本年（貸方）數(shù)量累計數(shù)（自動計算）。否則就是：當主營業(yè)務收入表頁C列出現(xiàn)“主營業(yè)務收入-24S乳癖清膠囊”時，L3數(shù)量值等于H3數(shù)量。單價 M3公式為 “=IF(OR(D3=本月合計,