Excel項目綜合管理與財務(wù)知識分析應(yīng)用

1、 學(xué)習(xí)目標學(xué)習(xí)目標 項目綜合實訓(xùn)項目綜合實訓(xùn)工作情境工作情境任務(wù)案例任務(wù)案例【知識目標知識目標】 了解使用了解使用excel實現(xiàn)賬務(wù)電算化的步驟和實現(xiàn)賬務(wù)電算化的步驟和方法。方法。 掌握掌握sumif函數(shù)和函數(shù)和if函數(shù)的格式。函數(shù)的格式。【能力目標能力目標】 學(xué)會使用學(xué)會使用excel實現(xiàn)賬務(wù)處理的操作。實現(xiàn)賬務(wù)處理的操作。 學(xué)會使用學(xué)會使用excel生成總賬、明細賬等操作。生成總賬、明細賬等操作。 李娜在熟練掌握李娜在熟練掌握excel基礎(chǔ)操作之后，開始嘗試用基礎(chǔ)操作之后，開始嘗試用excel建建賬。賬。豐源公司的賬戶期初數(shù)據(jù)如表豐源公司的賬戶期初數(shù)據(jù)如表2-1所示。所示。該公司該公司20

2、10年年9月份的具體業(yè)務(wù)如下：公司為增值稅一般月份的具體業(yè)務(wù)如下：公司為增值稅一般納稅人，增值稅稅率為納稅人，增值稅稅率為17%，所得稅稅率為，所得稅稅率為25%。材料。材料核算采用先進先出法。原材料月初庫存量為核算采用先進先出法。原材料月初庫存量為500噸。噸。工作情境分析工作情境分析若要完成豐源公司若要完成豐源公司2010年年9月份的賬務(wù)處理流程，需分成月份的賬務(wù)處理流程，需分成以下幾項工作任務(wù)：建賬以下幾項工作任務(wù)：建賬設(shè)置賬戶設(shè)置賬戶輸入期初余額輸入期初余額輸入記賬憑證輸入記賬憑證生成總賬生成總賬生成明細賬。生成明細賬。一、任務(wù)分析一、任務(wù)分析所謂建賬即用所謂建賬即用excel建立一個

3、工作簿，并建建立一個工作簿，并建立若干張工作表，用以分別存放會計科目立若干張工作表，用以分別存放會計科目及其期初余額、記賬憑證，以及根據(jù)記賬及其期初余額、記賬憑證，以及根據(jù)記賬憑證自動生成的總賬和明細賬等。憑證自動生成的總賬和明細賬等。在該任務(wù)中需要明確工作簿與工作表的關(guān)在該任務(wù)中需要明確工作簿與工作表的關(guān)系，用到的操作技能是新建工作簿與工作系，用到的操作技能是新建工作簿與工作表、對工作表進行重命名。表、對工作表進行重命名。 二、任務(wù)實施二、任務(wù)實施操作步驟如下。操作步驟如下。（1）在工作盤）在工作盤e盤建立盤建立“豐源公司豐源公司”文件夾，在文件夾，在該文件夾下建立該文件夾下建立“總賬總賬”

4、文件夾。文件夾。（2）啟動）啟動excel，在總賬文件夾下建立總賬工作，在總賬文件夾下建立總賬工作簿簿“1009總賬總賬.xls”。（3）雙擊工作表）雙擊工作表sheet1標簽，輸入一個新工作標簽，輸入一個新工作表名字表名字“封面封面”（4）在）在“封面封面”工作表中輸入以下信息，如圖工作表中輸入以下信息，如圖2-1所示。所示。 （5）將）將sheet2命名為命名為“1009會計科目及余會計科目及余額表額表”。（6）將）將sheet3命名為命名為“憑證模板憑證模板”。（7）增加工作表）增加工作表sheet4、sheet5、sheet6，分別命名為分別命名為“1009憑證憑證”、“1009總賬及

5、總賬及試算平衡表試算平衡表”和和“1009明細賬明細賬”。以上操作的結(jié)果如圖以上操作的結(jié)果如圖2-2所示。見數(shù)據(jù)模型。所示。見數(shù)據(jù)模型。 一、任務(wù)分析一、任務(wù)分析設(shè)置賬戶即建立一個設(shè)置賬戶即建立一個“會計科目及余額會計科目及余額表表”，會計科目是會計記賬的核心，它主，會計科目是會計記賬的核心，它主要有三個方面的功能：一是會計分錄的對要有三個方面的功能：一是會計分錄的對象；二是記賬的標準；三是制表的綱目。象；二是記賬的標準；三是制表的綱目。在日常的會計核算中，會計科目一般分為在日常的會計核算中，會計科目一般分為一級科目、二級科目及明細科目，其中一一級科目、二級科目及明細科目，其中一級科目是國家財

6、政部統(tǒng)一規(guī)定的。級科目是國家財政部統(tǒng)一規(guī)定的。二、任務(wù)實施操作步驟如下。（1）在工作表“1009會計科目及余額表”的a1單元格中輸入“科目編碼”，b1單元格中輸入“科目名稱”，選擇a列，打開格式菜單，選擇單元格命令，打開“單元格格式”對話框，設(shè)置數(shù)字類型為文本。（2）在工作表“1009會計科目及余額表”的a2至a75單元格中分別輸入新會計準則體系的科目編碼及相應(yīng)的子科目編碼，在b2至b75單元格中分別輸入新會計準則體系的科目名稱及相應(yīng)的子科目名稱。 （3）選擇）選擇a1和和b1單元格，單擊工具欄上單元格，單擊工具欄上的的“填充顏色填充顏色”按鈕，填上青綠色。按鈕，填上青綠色。（4）選擇）選擇a

7、1：b75單元格區(qū)域，單擊工具單元格區(qū)域，單擊工具欄上的欄上的“邊框邊框”按鈕右側(cè)的下拉按鈕，從按鈕右側(cè)的下拉按鈕，從中選擇中選擇“所有框線所有框線”，加上，加上“田田”字型邊字型邊框?？颉＃?）選擇）選擇c2單元格，選擇單元格，選擇“窗口窗口”菜單菜單中中“凍結(jié)窗格凍結(jié)窗格”命令，將命令，將a1和和b1單元格及單元格及其內(nèi)容固定在現(xiàn)有位置，不隨行列的翻動其內(nèi)容固定在現(xiàn)有位置，不隨行列的翻動而隱藏。以上操作的結(jié)果如圖而隱藏。以上操作的結(jié)果如圖2-3所示。所示。一、任務(wù)分析一、任務(wù)分析輸入期初余額即在輸入期初余額即在“1009會計科目及余額會計科目及余額表表”中輸入中輸入2010年年9月各賬戶期

8、初數(shù)據(jù)，并月各賬戶期初數(shù)據(jù)，并實現(xiàn)試算平衡。在輸入期初數(shù)據(jù)時，需注實現(xiàn)試算平衡。在輸入期初數(shù)據(jù)時，需注意總賬科目余額與下級科目余額之間的關(guān)意總賬科目余額與下級科目余額之間的關(guān)系：總賬科目余額系：總賬科目余額=下級科目余額之和。在下級科目余額之和。在進行試算平衡時，要設(shè)置試算平衡公式：進行試算平衡時，要設(shè)置試算平衡公式：借方總賬科目余額之和借方總賬科目余額之和=貸方總賬科目余額貸方總賬科目余額之和。之和。二、任務(wù)實施二、任務(wù)實施操作步驟如下。操作步驟如下。（1）選擇）選擇c1單元格，輸入單元格，輸入“期初借方余期初借方余額額”；選擇；選擇d1單元格，輸入單元格，輸入“期初貸方余期初貸方余額額”。

9、（2）定義有明細科目的匯總科目的計算公）定義有明細科目的匯總科目的計算公式。在輸入時因為只要求輸入最低級科目式。在輸入時因為只要求輸入最低級科目的余額，上級科目的余額要根據(jù)設(shè)置的公的余額，上級科目的余額要根據(jù)設(shè)置的公式自動進行計算，所以匯總科目的單元格式自動進行計算，所以匯總科目的單元格數(shù)據(jù)是通過其他單元格數(shù)據(jù)加總得出的。數(shù)據(jù)是通過其他單元格數(shù)據(jù)加總得出的。（3）將豐源公司）將豐源公司2010年年9月會計科目（非匯總科目）的月會計科目（非匯總科目）的期初余額輸入該工作表中，輸入結(jié)果如圖期初余額輸入該工作表中，輸入結(jié)果如圖2-4所示。所示。 （4）用格式刷把）用格式刷把c1：d1刷成和刷成和a1

10、相同格式，選擇相同格式，選擇c1：d75，加上邊框。，加上邊框。（5）選擇）選擇a76：b76單元格，單擊工具欄上的單元格，單擊工具欄上的“合并及合并及居中居中”按鈕，在合并后的單元格中輸入按鈕，在合并后的單元格中輸入“合計合計”二字。二字。（6）在）在c76中輸入中輸入“=”，打開名稱框旁邊的下拉按鈕，打開名稱框旁邊的下拉按鈕，選擇選擇sumif函數(shù)，打開編輯函數(shù)參數(shù)的窗口。函數(shù)，打開編輯函數(shù)參數(shù)的窗口。 （7）選擇）選擇c76，在編輯欄單擊，在編輯欄單擊a:a，按，按f4鍵，把相對地鍵，把相對地址切換為絕對地址，回車確認。址切換為絕對地址，回車確認。（8）用填充柄把）用填充柄把c76的公式

11、復(fù)制到的公式復(fù)制到d76，得到貸方余額，得到貸方余額之和也為之和也為10 711 050。 sumif函數(shù)是條件求和函數(shù)。函數(shù)是條件求和函數(shù)。【類型類型】邏輯函數(shù)邏輯函數(shù)【格式格式】sumif（搜索范圍，搜索條件，（搜索范圍，搜索條件，求和范圍）求和范圍）【功能功能】可以根據(jù)其他區(qū)域中的值，通過可以根據(jù)其他區(qū)域中的值，通過使用使用sumif工作表函數(shù)對區(qū)域求和。工作表函數(shù)對區(qū)域求和。例如：例如：c76= sumif（a:a，?，c:c）一、任務(wù)分析一、任務(wù)分析輸入記賬憑證即建立一個輸入記賬憑證即建立一個“1009憑證憑證”工工作表，在此表中輸入所有業(yè)務(wù)憑證。記賬作表，在此表中輸入所有業(yè)務(wù)憑證。

12、記賬憑證清單應(yīng)具有記賬憑證的所有信息，應(yīng)憑證清單應(yīng)具有記賬憑證的所有信息，應(yīng)設(shè)置類別編號、憑證日期、附件、摘要、設(shè)置類別編號、憑證日期、附件、摘要、科目編碼、總賬科目、明細科目、借方金科目編碼、總賬科目、明細科目、借方金額、貸方金額、制單人、審核人、記賬人額、貸方金額、制單人、審核人、記賬人等字段。此外，在輸入過程中要設(shè)置一定等字段。此外，在輸入過程中要設(shè)置一定的數(shù)據(jù)校驗功能，如日期格式、金額格式、的數(shù)據(jù)校驗功能，如日期格式、金額格式、科目編碼和科目名稱的有效性等。科目編碼和科目名稱的有效性等。二、任務(wù)實施二、任務(wù)實施1制作憑證模板制作憑證模板操作步驟如下。操作步驟如下。（1）打開工作表）打開

13、工作表“憑證模板憑證模板”，設(shè)計憑證輸入的表頭。，設(shè)計憑證輸入的表頭。（2）設(shè)置）設(shè)置a1:l3單元格邊框為單元格邊框為“田田”字型，中線為藍虛字型，中線為藍虛線，邊線為藍實線。線，邊線為藍實線。（3）設(shè)置）設(shè)置“類別編號類別編號”、“憑證日期憑證日期”、“附件附件”、“摘要摘要”、“科目編碼科目編碼”等列的數(shù)據(jù)有效性。等列的數(shù)據(jù)有效性。（4）設(shè)置）設(shè)置“總賬科目總賬科目”列和列和“明細科目明細科目”列的取值公式。列的取值公式。（5）設(shè)置）設(shè)置“借方金額借方金額”、“貸方金額貸方金額”的格式。的格式。 （6）定義平衡檢查公式。）定義平衡檢查公式。 vlookup函數(shù)用于表格數(shù)組的首列查找值，函

14、數(shù)用于表格數(shù)組的首列查找值，并由此返回表格數(shù)組當前行的對應(yīng)列的值。并由此返回表格數(shù)組當前行的對應(yīng)列的值。【類型類型】查找與引用函數(shù)查找與引用函數(shù)【格式格式】vlookup（lookup_value，table_array，col_index_num，range_lookup）【功能功能】在表格或數(shù)值數(shù)組的首列查找指定的數(shù)在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當前行中指定列處的值，并由此返回表格或數(shù)組當前行中指定列處的數(shù)值。數(shù)值。 left函數(shù)用于基于所指定的子字符數(shù)函數(shù)用于基于所指定的子字符數(shù)返回其母字符串中從左邊數(shù)起的第返回其母字符串中從左邊數(shù)起的第1個或前個或前幾個字符

15、。幾個字符?！绢愋皖愋汀课谋竞瘮?shù)文本函數(shù)【格式格式】left（text，num_chars）【功能功能】基于所指定的字符數(shù)返回文本字基于所指定的字符數(shù)返回文本字符串中的第符串中的第1個或前幾個字符。個或前幾個字符。2輸入記賬憑證輸入記賬憑證操作步驟如下。操作步驟如下。（1）選擇）選擇“憑證模板憑證模板”表，將憑證模板復(fù)表，將憑證模板復(fù)制到制到“1009憑證憑證”中。中。 （2）根據(jù)公司發(fā)生的業(yè)務(wù)，輸入會計分錄。）根據(jù)公司發(fā)生的業(yè)務(wù)，輸入會計分錄。 3審核憑證與過賬審核憑證與過賬由于由于excel本身是一個電子表格軟件，憑證本身是一個電子表格軟件，憑證都放在數(shù)據(jù)清單里面，為了清晰的表明憑都放在數(shù)

16、據(jù)清單里面，為了清晰的表明憑證是否審核或是否記賬，還可以靈活地使證是否審核或是否記賬，還可以靈活地使用為單元格填充顏色的方法來表示是否審用為單元格填充顏色的方法來表示是否審核或是記賬。例如，無填充顏色表示未審核或是記賬。例如，無填充顏色表示未審核，藍色填充表示已經(jīng)審核，黃色填充表核，藍色填充表示已經(jīng)審核，黃色填充表示已經(jīng)記賬。示已經(jīng)記賬。 一、任務(wù)分析一、任務(wù)分析 總分類賬，簡稱總賬，是根據(jù)總分類科目總分類賬，簡稱總賬，是根據(jù)總分類科目（一級科目）開設(shè)賬戶，用來登記全部經(jīng)濟業(yè)務(wù)，（一級科目）開設(shè)賬戶，用來登記全部經(jīng)濟業(yè)務(wù)，進行總分類核算，提供總括核算資料的分類賬簿。進行總分類核算，提供總括核算

17、資料的分類賬簿。總分類賬所提供的核算資料，是編制會計報表的總分類賬所提供的核算資料，是編制會計報表的主要依據(jù)，任何單位都必須設(shè)置總分類賬。主要依據(jù)，任何單位都必須設(shè)置總分類賬。 試算平衡表是列有總分類賬中所有賬戶及余試算平衡表是列有總分類賬中所有賬戶及余額的簡單表格。這份表格有助于檢查記錄的準確額的簡單表格。這份表格有助于檢查記錄的準確性和編制財務(wù)報表。性和編制財務(wù)報表。 二、任務(wù)實施二、任務(wù)實施1制作總賬及試算平衡表制作總賬及試算平衡表操作步驟如下。操作步驟如下。（1）復(fù)制）復(fù)制“1009會計科目及余額表會計科目及余額表”的的a1:d76區(qū)域。區(qū)域。（2）選中）選中“1009總賬及試算平衡表

18、總賬及試算平衡表”a1單元格，單元格，右擊打開快捷菜單，選擇右擊打開快捷菜單，選擇“選擇性粘貼選擇性粘貼”，打開，打開“選擇性粘貼選擇性粘貼”對話框，選擇單選項對話框，選擇單選項“值和數(shù)字值和數(shù)字格式格式”后單擊后單擊“確定確定”按鈕。按鈕。（3）刪除二級以下科目所在的行，因為此表的）刪除二級以下科目所在的行，因為此表的科目均為總賬科目?？颇烤鶠榭傎~科目。 （4）選擇）選擇e1單元格，輸入單元格，輸入“本期借方發(fā)生額合本期借方發(fā)生額合計計”，選擇，選擇f1單元格，輸入單元格，輸入“本期貸方發(fā)生額合本期貸方發(fā)生額合計計”；選擇；選擇g1單元格，輸入單元格，輸入“期末借方余額期末借方余額”，選擇選

19、擇h1單元格，輸入單元格，輸入“期末貸方余額期末貸方余額”。（5）選擇）選擇a1:h1區(qū)域，將其填充為青綠色。區(qū)域，將其填充為青綠色。（6）選擇）選擇a1:h48區(qū)域，設(shè)置邊框類型為區(qū)域，設(shè)置邊框類型為“所有所有框線框線”。（7）設(shè)置）設(shè)置e2單元格的公式單元格的公式 。（8）設(shè)置）設(shè)置f2單元格的公式單元格的公式 。（9）設(shè)置）設(shè)置g2單元格的公式單元格的公式 。 if函數(shù)用于進行真假值判斷，根據(jù)邏輯計算的真假值，函數(shù)用于進行真假值判斷，根據(jù)邏輯計算的真假值，返回不同結(jié)果。返回不同結(jié)果。【類型類型】邏輯函數(shù)邏輯函數(shù)【格式格式】if（logical_test，value_if_true，val

20、ue_if_false）【功能功能】執(zhí)行真假值判斷，根據(jù)邏輯計算的真假值，返執(zhí)行真假值判斷，根據(jù)邏輯計算的真假值，返回不同的結(jié)果。回不同的結(jié)果。logical_test表示計算值為表示計算值為“真真”或或“假假”的任意值或表的任意值或表達式；達式；value_if_true表示當表示當logical_test為為“真真”時返回的值；時返回的值；value_if_false表示當表示當logical_test為為“假假”時返回的值。時返回的值。（10）設(shè)置）設(shè)置h2單元格的公式單元格的公式 。（11）將）將e2單元格的公式縱向填充至單元格的公式縱向填充至e47單元格。單元格。（12）將）將f2單

21、元格的公式縱向填充至單元格的公式縱向填充至f47單元格。單元格。（13）將）將g2單元格的公式縱向填充至單元格的公式縱向填充至g47單元格。單元格。（14）將）將h2單元格的公式縱向填充至單元格的公式縱向填充至h47單元格。單元格。（15）選擇）選擇e48，輸入，輸入“=sum（e2:e47）”，并將公式橫向向右填充至并將公式橫向向右填充至h48單元格。單元格。（16）選擇）選擇i2單元格，選擇單元格，選擇“窗口窗口”菜單中菜單中“凍凍結(jié)窗格結(jié)窗格”命令命令 。2重算總賬及試算平衡表重算總賬及試算平衡表 操作步驟如下：操作步驟如下： 選擇選擇“工具工具”菜單中菜單中“選項選項”命令，選命令，選

22、擇擇“重新計算重新計算”選項卡，在選項卡，在“計算計算”選項選項組中選擇組中選擇“手動重算手動重算”單選按鈕和單選按鈕和“保存保存前自動重算前自動重算”復(fù)選框，如圖復(fù)選框，如圖2-15所示。所示。 設(shè)置結(jié)束后，可直接按功能鍵設(shè)置結(jié)束后，可直接按功能鍵f9重算所重算所有工作表或重算活動工作表。從圖有工作表或重算活動工作表。從圖2-14中中可以看出，試算平衡是成功的?？梢钥闯觯囁闫胶馐浅晒Φ?。一、任務(wù)分析一、任務(wù)分析明細分類賬簡稱明細賬，是指根據(jù)總分類科目設(shè)明細分類賬簡稱明細賬，是指根據(jù)總分類科目設(shè)置的，由其所屬的明細分類科目開設(shè)的明細分類置的，由其所屬的明細分類科目開設(shè)的明細分類賬戶組成，用以

23、記錄某一類經(jīng)濟業(yè)務(wù)明細核算資賬戶組成，用以記錄某一類經(jīng)濟業(yè)務(wù)明細核算資料的分類賬簿，能提供有關(guān)經(jīng)濟業(yè)務(wù)的詳細資料。料的分類賬簿，能提供有關(guān)經(jīng)濟業(yè)務(wù)的詳細資料。明細分類賬簿應(yīng)根據(jù)經(jīng)濟業(yè)務(wù)的種類和經(jīng)營管理明細分類賬簿應(yīng)根據(jù)經(jīng)濟業(yè)務(wù)的種類和經(jīng)營管理的要求分別設(shè)置。按其外表形式，明細賬采用活的要求分別設(shè)置。按其外表形式，明細賬采用活頁式賬簿和卡片式賬簿；按其賬頁格式，明細賬頁式賬簿和卡片式賬簿；按其賬頁格式，明細賬一般分為三欄式明細分類賬簿、數(shù)量金額式明細一般分為三欄式明細分類賬簿、數(shù)量金額式明細分類賬簿和多欄式明細分類賬簿分類賬簿和多欄式明細分類賬簿3種。種。 二、任務(wù)實施二、任務(wù)實施操作步驟如下。

24、操作步驟如下。（1）選擇）選擇“1009明細賬明細賬”工作表的工作表的a3單元格。單元格。（2）選擇）選擇“數(shù)據(jù)數(shù)據(jù)”菜單中菜單中“數(shù)據(jù)透視表和數(shù)據(jù)透視圖數(shù)據(jù)透視表和數(shù)據(jù)透視圖”命令，打開命令，打開“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)?shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)?步驟之步驟之1”對話框。對話框。（3）在）在“選定區(qū)域選定區(qū)域”文本框中輸入要匯總的數(shù)據(jù)區(qū)域文本框中輸入要匯總的數(shù)據(jù)區(qū)域“1009憑證！憑證！$a$1:$l$200”。 （4）單擊）單擊“下一步下一步”按鈕，在按鈕，在“數(shù)據(jù)透視表顯示位置數(shù)據(jù)透視表顯示位置”選項中選擇選項中選擇“現(xiàn)有工作表現(xiàn)有工作表”單選按鈕，具體單元格地址單選按鈕，具體單元格地址是是“=$a$