Excel在財(cái)務(wù)中的應(yīng)用課程資料--第二章制作會(huì)計(jì)科目表及會(huì)計(jì)憑證的

1、第二章 制作會(huì)計(jì)科目表及會(huì)計(jì)憑證的編制 2.1設(shè)計(jì)思路分析 2.2科目及期初余額設(shè)定l2.2.1會(huì)計(jì)科目編碼規(guī)則l會(huì)計(jì)科目一般包括一級(jí)科目、二級(jí)科目和明細(xì)科目 會(huì)計(jì)科目編號(hào)的原則 l系統(tǒng)性原則l一義性原則l簡(jiǎn)潔性原則l可擴(kuò)展性原則 l在我國(guó)一般采用3位數(shù)編號(hào)，并且科目代號(hào)從右到左表示科目的性質(zhì) 如：101現(xiàn)金，102銀行存款 2.2.2輸入科目名稱(chēng)和編號(hào) l步驟1 新建一個(gè)工作簿，在工作表SHEETL中輸入科目表中要包含的項(xiàng)目，并設(shè)置科目表標(biāo)題行的格式。 l步驟2 在科目表中錄入科目代碼、總賬科目和明細(xì)科目的名稱(chēng)。 l步驟3 選定需要設(shè)置數(shù)據(jù)有效性的單元格區(qū)域（即“余額方向”列標(biāo)題下方的單元格

2、），單擊菜單欄中的“數(shù)據(jù)有效性”命令打開(kāi)“數(shù)據(jù)有效性”對(duì)話(huà)框，在“設(shè)置”選項(xiàng)卡中的“允許”下拉列表中選擇“序列”，在“來(lái)源”文本框中輸入下拉列表中需包含的項(xiàng)，不同項(xiàng)之間用英文狀態(tài)下的逗號(hào)分隔。 l步驟4 若要在單擊格后顯示可選擇的輸入提示信息，請(qǐng)?jiān)凇皵?shù)據(jù)有效性”對(duì)話(huà)框中單擊“輸入信息”標(biāo)簽，切換到“輸入信息”選項(xiàng)卡，選擇“選定單元格時(shí)顯示輸入信息”復(fù)選框，然后在“標(biāo)題”文本框和“輸入信息”文本框中輸入具體內(nèi)容。 l步驟5 單擊“出錯(cuò)警告”切換到“出錯(cuò)警告”選項(xiàng)卡中，選擇“輸入無(wú)效數(shù)據(jù)時(shí)顯示出錯(cuò)警告”復(fù)選框，在“樣式”下拉列表中選擇“停止”，然后可根據(jù)需要決定是否鍵入“標(biāo)題”和“錯(cuò)誤信息”。 l

3、步驟6 當(dāng)在設(shè)置了有效性的單元格中鍵入其他值時(shí)，系統(tǒng)會(huì)彈出出錯(cuò)提示話(huà)框，并且阻止無(wú)效數(shù)據(jù)的輸入。 l步驟7 由于科目表中包含總賬科目和明細(xì)科目，為了將它們區(qū)分開(kāi)來(lái)，為明細(xì)科目單元格區(qū)域設(shè)置底紋效果。 l步驟8 在表格中“期初余額（借）”列在側(cè)插入新列，輸入列標(biāo)題為“科目級(jí)次”，然后將在應(yīng)科目的科目次輸入到該列的單元格中。 2.2.3 設(shè)置期初余額及余額平衡試算 l步驟1 選定好科目表中的項(xiàng)目并完成內(nèi)容的輸入之后，接下來(lái)向科目表中添加期初余額。 l步驟2 彈出“單元格式”對(duì)話(huà)框。 l步驟3 接下來(lái)根據(jù)輸入的明細(xì)科目余額計(jì)算總賬余額，在“應(yīng)收賬款”總賬科目以應(yīng)余額所在的單元格F18中輸入公式：=S

4、UM（F19：F21） l步驟4 輸入公式,按下Enter鍵可得到公式運(yùn)算結(jié)果。 l步驟5 單擊需要凍結(jié)行的下行中的任意單元格，單擊菜單欄中的“窗口凍結(jié)窗格”命令. l步驟6 此時(shí),拖動(dòng)右滾動(dòng)條,會(huì)發(fā)現(xiàn)工作中的第1行到第3行被凍結(jié),始終顯示在屏幕上,同時(shí),第3行的下邊框會(huì)顯示一條細(xì)線(xiàn). l步驟7 在單元格F165中輸入公式:l=SUMIF(SES4:SES164,=1”,F4:F164) l步驟8 合并單元格F2和G2，然后將該單元格填充為黃色，之后輸入公式：=IF（F165=G165，“經(jīng)試算借貸方余額已平衡”，“經(jīng)試算借貸方余額不平衡”） l步驟9 如果借貸方余額不相等，還應(yīng)準(zhǔn)確顯示余額的

5、差額，將單元格F1和G1填充為綠色，在單元格F1中輸入 “借貸方差額”，在單元格G1中輸入公式：=F1165-G165 2.3 制作記賬憑證和填寫(xiě)科目代碼及名稱(chēng) l3.3.1 記賬憑證的基本內(nèi)容和種類(lèi) l記賬憑證應(yīng)具備經(jīng)下基本內(nèi)容：l記賬憑證的名稱(chēng)及填制單位的名稱(chēng)l記賬憑證的填制日期和編號(hào)l經(jīng)濟(jì)業(yè)務(wù)的內(nèi)容摘要l應(yīng)借應(yīng)貸賬戶(hù)的名稱(chēng)，包括一級(jí)賬戶(hù)、二級(jí)賬戶(hù)和明賬戶(hù)的名稱(chēng)及金額。l附件張數(shù)，即所附原始憑證的張數(shù)l會(huì)計(jì)主管人員、審核人員、記賬和填制憑證人員的簽名或蓋章 2.3.2 用EXCEL制作記賬憑證 l步驟1 在工作表SHEET2中創(chuàng)建通用記賬憑證表格l步驟2 在單元格C2中輸入公式：=TODA

6、Y（）l步驟3 在工作SHEET3中創(chuàng)建收款憑證格式，并將該工作表改名為“收款憑證”2.3.2 用EXCEL制作記賬憑證 l步驟4 在默認(rèn)情況下，一個(gè)工作簿只有三個(gè)工作表，此時(shí)需要插入新的工作表。l步驟5 在彈出的“插入”對(duì)話(huà)框中選擇“工作表”，然后單擊“確定”按鈕l步驟6 此時(shí)，新插入的工作表默認(rèn)名稱(chēng)為SHEET1，拖動(dòng)工作表標(biāo)簽。將新工作表移到最右邊 l步驟7 在新插入的工作表中創(chuàng)建付款憑證，并將該工作表名稱(chēng)更改為“付款憑證”l步驟8 用同樣的方法，再次新建工作表，創(chuàng)建“轉(zhuǎn)賬憑證”工作表l步驟9 在“收款憑證”工作表中單擊單元格B3，打開(kāi)“數(shù)據(jù)有效性”對(duì)話(huà)框， 在“允許”下拉列表中選擇“序

7、列”。在“來(lái)源”文本框中輸入“現(xiàn)金，銀行存款” l步驟10 接著，該單元格的右側(cè)會(huì)顯示一個(gè)下拉按鈕，單擊l可從下拉列表中進(jìn)行選擇 2.3.3設(shè)置科目下拉列表 l步驟1 切換到工作表“科目及期初余額設(shè)定”中單擊菜單欄中“插入名稱(chēng)定義”命令，彈出“定義名稱(chēng)”對(duì)話(huà)框l步驟2 接著屏幕上顯示，“定義名稱(chēng)引用位置：”折疊對(duì)話(huà)框，在當(dāng)前工作表中選定需要引用的單元格區(qū)域，這里請(qǐng)選定單元格區(qū)域A4：A164 l步驟3 設(shè)置好引用位置后，再次單擊折疊按鈕，返回“定義名稱(chēng)”對(duì)話(huà)框中，單擊“添加”按鈕將該名稱(chēng)添加到列表框中，然后單擊“確定”按鈕關(guān)閉該對(duì)話(huà)框l步驟4 切換到工作表“通用記賬憑證”中，選定單元格B4，單

8、擊菜單欄中的“數(shù)據(jù)有效性”命令，彈出“數(shù)據(jù)有效性”對(duì)話(huà)框，對(duì)話(huà)框，從“允許”下拉列表中選擇“序 列”，在“來(lái)源文本框中輸入“=CODE” l步驟5 此時(shí)單元格B4的右側(cè)將會(huì)顯示一個(gè)下拉按鈕，單擊可展開(kāi)科目代碼下拉列表 2.3.4自動(dòng)填入對(duì)應(yīng)科目代碼的科目名稱(chēng) l步驟1 在“通用記賬憑證”工作表中的單元格C4中輸入公式：2.3.4自動(dòng)填入對(duì)應(yīng)科目代碼的科目名稱(chēng) l=IF（LEN（B4）=0，“”，VLOOKUP（B4，科目及期初余額設(shè)定！SAS4：SCS164，2FALSE）l步驟2 在單元格D4中輸入公式：l=IF（LEN（B4）=0，“”，VLOOKUP（B4，科目及期初余額設(shè)定！SAS4：

9、SCS164，3，F(xiàn)ALSE） l步驟3 拖動(dòng)序列填充柄，復(fù)制公式到以下行。EXEEL將自動(dòng)填入對(duì)應(yīng)的總賬和明細(xì)賬科目名稱(chēng)，如果該科目沒(méi)有明細(xì)科目，則明細(xì)科目列中填入0 l步驟4 如果希望沒(méi)有明細(xì)科目的單元格為空，可隱藏零值l步驟5 此時(shí)，記賬憑證中沒(méi)有明細(xì)科目的單元格將顯示為空 2.4在EXCEL中錄入憑證并設(shè)置借貸平衡提示 l3.4.1自動(dòng)按憑證類(lèi)別連續(xù)編號(hào) l步驟1 在工作簿中插入“憑證錄入”工作表，工作表中的項(xiàng)目l步驟2 選定單元格D5，單擊菜單欄中的“數(shù)據(jù) 有效性”命令，彈出“數(shù)據(jù)有效性”對(duì)話(huà)框，切換到“設(shè)置”選項(xiàng)卡中，從“允許”下拉列表中選擇“序列”，在“來(lái)源”文本框中輸入憑證類(lèi)別

10、，如“銀收”、“銀付”、“現(xiàn)收”、“現(xiàn)付”等，輸入的之間用英文狀態(tài)下的逗號(hào) l步驟3 設(shè)置好有效性后，再單擊單元格D5，此時(shí)該單元格的右側(cè)會(huì)顯示一個(gè)下拉按鈕，單擊該按鈕彈出憑證類(lèi)別下拉列表，用戶(hù)只需從列表中選擇當(dāng)前憑證所屬的類(lèi)別即可l步驟4 在單元格E5中輸入公式：=COUNTIF（SDS5：D5，D5）l步驟5 用序列填充的方法向下復(fù)制D5單元格有效性設(shè)置和D5單元格中的公式，此時(shí)，E列中憑證號(hào)會(huì)隨著D列中選定的憑證類(lèi)別連續(xù)編號(hào) 2.4.2設(shè)置賬戶(hù)名稱(chēng)查詢(xún)列表 l步驟1 切換到工作表“科目及期初余額設(shè)定”，在工作表中的H的新增“賬戶(hù)查詢(xún)”欄，在H3單元格中輸入“賬戶(hù)查詢(xún)”，H4單元格中輸入公

11、式：=IF(C4=”,B4,CONCATENATE(B4,”_”,C4)l步驟2 指向單元格H4的右下角，拖動(dòng)序列填充柄復(fù)制公式，計(jì)算出所有的賬戶(hù)查詢(xún) l步驟3 選定單元格區(qū)域H4：H164，單擊菜單欄中的“插入名稱(chēng)定義”命令，彈出“定義名稱(chēng)”對(duì)話(huà)框，在“當(dāng)前工作簿中的名稱(chēng)”文本框中輸入“賬戶(hù)名稱(chēng)”， 單擊“添加”按鈕，然后再“確定”按鈕l步驟4 切換到工作表“憑證錄入”中，選定借方欄中的賬戶(hù)名稱(chēng)單元格區(qū)域，單擊“數(shù)據(jù) 有效性”命令，在“數(shù)據(jù)有效性”對(duì)話(huà)框中的“設(shè)置”選項(xiàng)卡中的“允許”下拉列表中選擇“序列”，在“來(lái)源”文本框中輸入“=賬戶(hù)名稱(chēng)” l步驟5 此后，當(dāng)需要輸入賬戶(hù)名稱(chēng)時(shí)，就可以直接

12、從下拉表中選擇了l步驟6 用同樣的方法為貸方欄中的賬戶(hù)名稱(chēng)設(shè)置下拉列表 2.4.3設(shè)置借貸平衡提示 l步驟1 單擊單元格K5，輸入公式：=IF（H5=J5，“”，“借貸不平”）l步驟2 拖動(dòng)單元格K5右下角的序列填充柄，復(fù)制公式到該列以下的單元格 l步驟3 當(dāng)輸入借方金額，未輸入貸方金額，或者輸入的貸方金額不等于借方金額時(shí)，在該行對(duì)應(yīng)的K列單元格中將顯示“借貸不平！”提示信息 2.5 生成科目匯總表 l匯總記帳憑證是為了簡(jiǎn)化登記總帳的手法，根據(jù)一次性記帳憑證按照一定的方法進(jìn)行歸類(lèi)匯總編制而成的。科目匯總表是一種典型的匯總記帳憑證，是根據(jù)一次性記帳憑證定期整理、匯總各類(lèi)帳戶(hù)的借、貸方發(fā)生額，并據(jù)

13、以登記總帳的一種匯總性記帳憑證2.5 生成科目匯總表 l一般企業(yè)如果企業(yè)業(yè)務(wù)量較多，可以三、五天匯總一次，業(yè)務(wù)量少的也要十天匯總一次。科目匯總表通常包括會(huì)計(jì)科目、記帳、借（貸）方的本期發(fā)生額等內(nèi)容。 2.5.1利用篩選功能匯總發(fā)生額 l步驟1 插入一個(gè)新的工作表，命令為“科目匯總表”2.5.1利用篩選功能匯總發(fā)生額 l步驟2 切換到“憑證錄入”工作表中，選定表格中的標(biāo)題行，單擊菜單欄中“數(shù)據(jù)篩選自動(dòng)篩選”命令l步驟3 此時(shí)，在標(biāo)題行的單元格中會(huì)出現(xiàn)一個(gè)下拉按鈕，單擊“賬戶(hù)名稱(chēng)”單元格的下拉按鈕，從彈出的下拉列表中選擇“科目匯總表”工作表中需要統(tǒng)計(jì)的會(huì)計(jì)科目名稱(chēng)，如“銀行存款” l步驟4 此時(shí)，表格中將只顯示借方賬戶(hù)名稱(chēng)為“銀行存款”的記錄l步驟5 將該結(jié)果填入“科目匯總表”中“銀行存款”科目的本期借方發(fā)生額欄 2.5.2利用公式匯總發(fā)生額 l步驟1 在“科目匯總表”工作表中，選定單元格C5，然后輸入公式：=SUMIF（憑證錄入！SG5：SGS32，SA5，憑證錄入！SH5：SHS32）2.5.2利用公式匯總發(fā)生額 l步驟2 按下來(lái)計(jì)算貸方本期發(fā)生額，在單元格D5中輸入公式：=SUMIF（憑證錄入！SIS5：SIS32，SA5，憑證錄入！SJS5：SJS32）l步驟3 拖動(dòng)單元格D5右下角的序列填充柄，向下復(fù)制