人民郵電出版社-《cl在財(cái)務(wù)中的應(yīng)用》.ppt

1、Excel在財(cái)務(wù)中的應(yīng)用,項(xiàng)目二 Excel在賬務(wù)處理中的應(yīng)用,項(xiàng)目二 Excel在賬務(wù)處理中的應(yīng)用,學(xué)習(xí)目標(biāo),【知識(shí)目標(biāo)】 了解使用Excel實(shí)現(xiàn)賬務(wù)電算化的步驟和方法。 掌握SUMIF函數(shù)和IF函數(shù)的格式。 【能力目標(biāo)】 學(xué)會(huì)使用Excel實(shí)現(xiàn)賬務(wù)處理的操作。 學(xué)會(huì)使用Excel生成總賬、明細(xì)賬等操作。,李娜在熟練掌握Excel基礎(chǔ)操作之后，開始嘗試用Excel建賬。 豐源公司的賬戶期初數(shù)據(jù)如表2-1所示。 該公司2010年9月份的具體業(yè)務(wù)如下：公司為增值稅一般納稅人，增值稅稅率為17%，所得稅稅率為25%。材料核算采用先進(jìn)先出法。原材料月初庫(kù)存量為500噸。 工作情境分析 若要完成豐源公

2、司2010年9月份的賬務(wù)處理流程，需分成以下幾項(xiàng)工作任務(wù)：建賬設(shè)置賬戶輸入期初余額輸入記賬憑證生成總賬生成明細(xì)賬。,工作情境,一、任務(wù)分析 所謂建賬即用Excel建立一個(gè)工作簿，并建立若干張工作表，用以分別存放會(huì)計(jì)科目及其期初余額、記賬憑證，以及根據(jù)記賬憑證自動(dòng)生成的總賬和明細(xì)賬等。 在該任務(wù)中需要明確工作簿與工作表的關(guān)系，用到的操作技能是新建工作簿與工作表、對(duì)工作表進(jìn)行重命名。,任務(wù)一建賬,二、任務(wù)實(shí)施 操作步驟如下。 （1）在工作盤E盤建立“豐源公司”文件夾，在該文件夾下建立“總賬”文件夾。 （2）啟動(dòng)Excel，在總賬文件夾下建立總賬工作簿“1009總賬.xls”。 （3）雙擊工作表Sh

3、eet1標(biāo)簽，輸入一個(gè)新工作表名字“封面” （4）在“封面”工作表中輸入以下信息，如圖2-1所示。,任務(wù)一建賬,（5）將Sheet2命名為“1009會(huì)計(jì)科目及余額表”。 （6）將Sheet3命名為“憑證模板”。 （7）增加工作表Sheet4、Sheet5、Sheet6，分別命名為“1009憑證”、“1009總賬及試算平衡表”和“1009明細(xì)賬”。 以上操作的結(jié)果如圖2-2所示。見數(shù)據(jù)模型。,任務(wù)一建賬,任務(wù)二設(shè)置賬戶,一、任務(wù)分析 設(shè)置賬戶即建立一個(gè)“會(huì)計(jì)科目及余額表”，會(huì)計(jì)科目是會(huì)計(jì)記賬的核心，它主要有三個(gè)方面的功能：一是會(huì)計(jì)分錄的對(duì)象；二是記賬的標(biāo)準(zhǔn)；三是制表的綱目。在日常的會(huì)計(jì)核算中，會(huì)

4、計(jì)科目一般分為一級(jí)科目、二級(jí)科目及明細(xì)科目，其中一級(jí)科目是國(guó)家財(cái)政部統(tǒng)一規(guī)定的。,任務(wù)二設(shè)置賬戶,二、任務(wù)實(shí)施 操作步驟如下。 （1）在工作表“1009會(huì)計(jì)科目及余額表”的A1單元格中輸入“科目編碼”，B1單元格中輸入“科目名稱”，選擇A列，打開格式菜單，選擇單元格命令，打開“單元格格式”對(duì)話框，設(shè)置數(shù)字類型為文本。 （2）在工作表“1009會(huì)計(jì)科目及余額表”的A2至A75單元格中分別輸入新會(huì)計(jì)準(zhǔn)則體系的科目編碼及相應(yīng)的子科目編碼，在B2至B75單元格中分別輸入新會(huì)計(jì)準(zhǔn)則體系的科目名稱及相應(yīng)的子科目名稱。,任務(wù)二設(shè)置賬戶,（3）選擇A1和B1單元格，單擊工具欄上的“填充顏色”按鈕，填上青綠色。

5、 （4）選擇A1：B75單元格區(qū)域，單擊工具欄上的“邊框”按鈕右側(cè)的下拉按鈕，從中選擇“所有框線”，加上“田”字型邊框。 （5）選擇C2單元格，選擇“窗口”菜單中“凍結(jié)窗格”命令，將A1和B1單元格及其內(nèi)容固定在現(xiàn)有位置，不隨行列的翻動(dòng)而隱藏。以上操作的結(jié)果如圖2-3所示。,任務(wù)三輸入期初余額,一、任務(wù)分析 輸入期初余額即在“1009會(huì)計(jì)科目及余額表”中輸入2010年9月各賬戶期初數(shù)據(jù)，并實(shí)現(xiàn)試算平衡。在輸入期初數(shù)據(jù)時(shí)，需注意總賬科目余額與下級(jí)科目余額之間的關(guān)系：總賬科目余額=下級(jí)科目余額之和。在進(jìn)行試算平衡時(shí)，要設(shè)置試算平衡公式：借方總賬科目余額之和=貸方總賬科目余額之和。,任務(wù)三輸入期初余

6、額,二、任務(wù)實(shí)施 操作步驟如下。 （1）選擇C1單元格，輸入“期初借方余額”；選擇D1單元格，輸入“期初貸方余額”。 （2）定義有明細(xì)科目的匯總科目的計(jì)算公式。在輸入時(shí)因?yàn)橹灰筝斎胱畹图?jí)科目的余額，上級(jí)科目的余額要根據(jù)設(shè)置的公式自動(dòng)進(jìn)行計(jì)算，所以匯總科目的單元格數(shù)據(jù)是通過其他單元格數(shù)據(jù)加總得出的。,任務(wù)三輸入期初余額,（3）將豐源公司2010年9月會(huì)計(jì)科目（非匯總科目）的期初余額輸入該工作表中，輸入結(jié)果如圖2-4所示。 （4）用格式刷把C1：D1刷成和A1相同格式，選擇C1：D75，加上邊框。 （5）選擇A76：B76單元格，單擊工具欄上的“合并及居中”按鈕，在合并后的單元格中輸入“合計(jì)”二

7、字。 （6）在C76中輸入“=”，打開名稱框旁邊的下拉按鈕，選擇SUMIF函數(shù)，打開編輯函數(shù)參數(shù)的窗口。 （7）選擇C76，在編輯欄單擊A:A，按F4鍵，把相對(duì)地址切換為絕對(duì)地址，回車確認(rèn)。 （8）用填充柄把C76的公式復(fù)制到D76，得到貸方余額之和也為10711050。,任務(wù)三輸入期初余額, SUMIF函數(shù)是條件求和函數(shù)。 【類型】邏輯函數(shù) 【格式】SUMIF（搜索范圍，搜索條件，求和范圍） 【功能】可以根據(jù)其他區(qū)域中的值，通過使用SUMIF工作表函數(shù)對(duì)區(qū)域求和。 例如：C76= SUMIF（A:A，?，C:C）,一、任務(wù)分析 輸入記賬憑證即建立一個(gè)“1009憑證”工作表，在此表中輸入所有業(yè)

8、務(wù)憑證。記賬憑證清單應(yīng)具有記賬憑證的所有信息，應(yīng)設(shè)置類別編號(hào)、憑證日期、附件、摘要、科目編碼、總賬科目、明細(xì)科目、借方金額、貸方金額、制單人、審核人、記賬人等字段。此外，在輸入過程中要設(shè)置一定的數(shù)據(jù)校驗(yàn)功能，如日期格式、金額格式、科目編碼和科目名稱的有效性等。,任務(wù)四輸入、審核記賬憑證及記賬,二、任務(wù)實(shí)施 1制作憑證模板 操作步驟如下。 （1）打開工作表“憑證模板”，設(shè)計(jì)憑證輸入的表頭。 （2）設(shè)置A1:L3單元格邊框?yàn)椤疤铩弊中?，中線為藍(lán)虛線，邊線為藍(lán)實(shí)線。 （3）設(shè)置“類別編號(hào)”、“憑證日期”、“附件”、“摘要”、“科目編碼”等列的數(shù)據(jù)有效性。 （4）設(shè)置“總賬科目”列和“明細(xì)科目”列的取

9、值公式。 （5）設(shè)置“借方金額”、“貸方金額”的格式。 （6）定義平衡檢查公式。,任務(wù)四輸入、審核記賬憑證及記賬, VLOOKUP函數(shù)用于表格數(shù)組的首列查找值，并由此返回表格數(shù)組當(dāng)前行的對(duì)應(yīng)列的值。 【類型】查找與引用函數(shù) 【格式】VLOOKUP（lookup_value，table_array，col_index_num，range_lookup） 【功能】在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前行中指定列處的數(shù)值。,任務(wù)四輸入、審核記賬憑證及記賬, LEFT函數(shù)用于基于所指定的子字符數(shù)返回其母字符串中從左邊數(shù)起的第1個(gè)或前幾個(gè)字符。 【類型】文本函數(shù) 【格式】LEFT

10、（text，num_chars） 【功能】基于所指定的字符數(shù)返回文本字符串中的第1個(gè)或前幾個(gè)字符。,任務(wù)四輸入、審核記賬憑證及記賬,2輸入記賬憑證 操作步驟如下。 （1）選擇“憑證模板”表，將憑證模板復(fù)制到“1009憑證”中。 （2）根據(jù)公司發(fā)生的業(yè)務(wù)，輸入會(huì)計(jì)分錄。,任務(wù)四輸入、審核記賬憑證及記賬,3審核憑證與過賬 由于Excel本身是一個(gè)電子表格軟件，憑證都放在數(shù)據(jù)清單里面，為了清晰的表明憑證是否審核或是否記賬，還可以靈活地使用為單元格填充顏色的方法來表示是否審核或是記賬。例如，無填充顏色表示未審核，藍(lán)色填充表示已經(jīng)審核，黃色填充表示已經(jīng)記賬。,任務(wù)四輸入、審核記賬憑證及記賬,一、任務(wù)分析

11、 總分類賬，簡(jiǎn)稱總賬，是根據(jù)總分類科目（一級(jí)科目）開設(shè)賬戶，用來登記全部經(jīng)濟(jì)業(yè)務(wù)，進(jìn)行總分類核算，提供總括核算資料的分類賬簿?？偡诸愘~所提供的核算資料，是編制會(huì)計(jì)報(bào)表的主要依據(jù)，任何單位都必須設(shè)置總分類賬。 試算平衡表是列有總分類賬中所有賬戶及余額的簡(jiǎn)單表格。這份表格有助于檢查記錄的準(zhǔn)確性和編制財(cái)務(wù)報(bào)表。,任務(wù)五生成總賬、試算平衡表,二、任務(wù)實(shí)施 1制作總賬及試算平衡表 操作步驟如下。 （1）復(fù)制“1009會(huì)計(jì)科目及余額表”的A1:D76區(qū)域。 （2）選中“1009總賬及試算平衡表”A1單元格，右擊打開快捷菜單，選擇“選擇性粘貼”，打開“選擇性粘貼”對(duì)話框，選擇單選項(xiàng)“值和數(shù)字格式”后單擊“確

12、定”按鈕。 （3）刪除二級(jí)以下科目所在的行，因?yàn)榇吮淼目颇烤鶠榭傎~科目。,任務(wù)五生成總賬、試算平衡表,（4）選擇E1單元格，輸入“本期借方發(fā)生額合計(jì)”，選擇F1單元格，輸入“本期貸方發(fā)生額合計(jì)”；選擇G1單元格，輸入“期末借方余額”，選擇H1單元格，輸入“期末貸方余額”。 （5）選擇A1:H1區(qū)域，將其填充為青綠色。 （6）選擇A1:H48區(qū)域，設(shè)置邊框類型為“所有框線”。 （7）設(shè)置E2單元格的公式 。 （8）設(shè)置F2單元格的公式 。 （9）設(shè)置G2單元格的公式 。,任務(wù)五生成總賬、試算平衡表, IF函數(shù)用于進(jìn)行真假值判斷，根據(jù)邏輯計(jì)算的真假值，返回不同結(jié)果。 【類型】邏輯函數(shù) 【格式】IF

13、（Logical_test，Value_if_true，Value_if_false） 【功能】執(zhí)行真假值判斷，根據(jù)邏輯計(jì)算的真假值，返回不同的結(jié)果。 Logical_test表示計(jì)算值為“真”或“假”的任意值或表達(dá)式； Value_if_true表示當(dāng)Logical_test為“真”時(shí)返回的值； Value_if_false表示當(dāng)Logical_test為“假”時(shí)返回的值。,任務(wù)五生成總賬、試算平衡表,（10）設(shè)置H2單元格的公式 。 （11）將E2單元格的公式縱向填充至E47單元格。 （12）將F2單元格的公式縱向填充至F47單元格。 （13）將G2單元格的公式縱向填充至G47單元格。 （

14、14）將H2單元格的公式縱向填充至H47單元格。 （15）選擇E48，輸入“=SUM（E2:E47）”，并將公式橫向向右填充至H48單元格。 （16）選擇I2單元格，選擇“窗口”菜單中“凍結(jié)窗格”命令 。,任務(wù)五生成總賬、試算平衡表,2重算總賬及試算平衡表 操作步驟如下： 選擇“工具”菜單中“選項(xiàng)”命令，選擇“重新計(jì)算”選項(xiàng)卡，在“計(jì)算”選項(xiàng)組中選擇“手動(dòng)重算”單選按鈕和“保存前自動(dòng)重算”復(fù)選框，如圖2-15所示。 設(shè)置結(jié)束后，可直接按功能鍵F9重算所有工作表或重算活動(dòng)工作表。從圖2-14中可以看出，試算平衡是成功的。,任務(wù)五生成總賬、試算平衡表,一、任務(wù)分析 明細(xì)分類賬簡(jiǎn)稱明細(xì)賬，是指根據(jù)總

15、分類科目設(shè)置的，由其所屬的明細(xì)分類科目開設(shè)的明細(xì)分類賬戶組成，用以記錄某一類經(jīng)濟(jì)業(yè)務(wù)明細(xì)核算資料的分類賬簿，能提供有關(guān)經(jīng)濟(jì)業(yè)務(wù)的詳細(xì)資料。明細(xì)分類賬簿應(yīng)根據(jù)經(jīng)濟(jì)業(yè)務(wù)的種類和經(jīng)營(yíng)管理的要求分別設(shè)置。按其外表形式，明細(xì)賬采用活頁(yè)式賬簿和卡片式賬簿；按其賬頁(yè)格式，明細(xì)賬一般分為三欄式明細(xì)分類賬簿、數(shù)量金額式明細(xì)分類賬簿和多欄式明細(xì)分類賬簿3種。,任務(wù)六生成各類明細(xì)賬,二、任務(wù)實(shí)施 操作步驟如下。 （1）選擇“1009明細(xì)賬”工作表的A3單元格。 （2）選擇“數(shù)據(jù)”菜單中“數(shù)據(jù)透視表和數(shù)據(jù)透視圖”命令，打開“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)?步驟之1”對(duì)話框。 （3）在“選定區(qū)域”文本框中輸入要匯總的數(shù)據(jù)區(qū)

16、域“1009憑證！$A$1:$L$200”。 （4）單擊“下一步”按鈕，在“數(shù)據(jù)透視表顯示位置”選項(xiàng)中選擇“現(xiàn)有工作表”單選按鈕，具體單元格地址是“=$A$3” 。 （5）單擊“完成”按鈕，進(jìn)行數(shù)據(jù)透視表的布局設(shè)置。,任務(wù)六生成各類明細(xì)賬,二、任務(wù)實(shí)施 操作步驟如下： （6）選擇“數(shù)據(jù)透視表字段列表”的“憑證日期”字段，再選擇左下角“添加到”按鈕右側(cè)下拉列表框中的“行區(qū)域”選項(xiàng)，然后單擊“添加到”按鈕，將“憑證日期”字段添加到“行區(qū)域”中。 （7）隱藏“憑證日期”、“類別編號(hào)”、“摘要”、“科目編碼”、“總賬科目”字段的統(tǒng)計(jì)單元格，然后用鼠標(biāo)左鍵按住F3單元格的“數(shù)據(jù)”不放，拖動(dòng)到G3單元格的“匯總”中。 （8