Excel在財(cái)會管理中的應(yīng)用（第二版） 主編 于向輝Excel在會計(jì)賬簿中的應(yīng)用新

1、第3章 Excel在會計(jì)賬簿中的應(yīng)用本章學(xué)習(xí)目標(biāo)本章主要講解會計(jì)賬簿的相關(guān)知識以及如何利用Excel設(shè)置和登記會計(jì)賬簿。通過本章的學(xué)習(xí)，掌握以下內(nèi)容： 會計(jì)賬簿的概念、意義、種類和格式 明細(xì)賬的格式設(shè)置和登記方法 總賬的格式設(shè)置和登記方法3.1 會計(jì)賬簿概述3.1.1 會計(jì)賬簿的意義3.1.2 會計(jì)賬簿的種類3.1.1 會計(jì)賬簿的意義 會計(jì)賬簿是全面記錄和反映一個單位經(jīng)濟(jì)業(yè)務(wù)，把大量分散的數(shù)據(jù)或資料進(jìn)行歸類整理，逐步加工成有用會計(jì)信息的簿籍，它是編制會計(jì)報(bào)表的重要依據(jù)。它是由具有一定格式、相互連結(jié)的賬頁所組成，賬頁一旦標(biāo)上會計(jì)科目，它就成為用來記錄和反映該科目所規(guī)定核算內(nèi)容的賬戶。3.1.1

2、會計(jì)賬簿的意義通過設(shè)置和登記會計(jì)賬簿：（1）能夠把大量的、分散的會計(jì)核算資料系統(tǒng)化，為加強(qiáng)經(jīng)濟(jì)核算提供資料。（2）可以為正確計(jì)算成本、經(jīng)營成果和收益分配等提供必要的會計(jì)核算資料。（3）利用會計(jì)賬簿所提供的核算資料，既可以為編制會計(jì)報(bào)表提供主要的依據(jù)，也可以為會計(jì)分析和會計(jì)檢查提供必要的依據(jù)。（4）既有利于會計(jì)核算資料的保存和利用，也有利于會計(jì)核算工作的分工。3.1.2 會計(jì)賬簿的種類1會計(jì)賬簿按用途的分類會計(jì)賬簿按其用途，可以分為序時(shí)賬簿、分類賬簿和備查（輔助）賬簿。2會計(jì)賬簿按外表形式的分類會計(jì)賬簿按其外表形式，可以分為訂本式賬簿、活頁式賬簿和卡片式賬簿。3會計(jì)賬簿按格式的分類會計(jì)賬簿按其格

3、式可分為三欄式賬簿、多欄式賬簿和數(shù)量金額式賬簿。3.2 會計(jì)賬簿的登記3.2.1 現(xiàn)金日記賬的設(shè)置與登記3.2.2 銀行存款日記賬的設(shè)置與登記3.2.3 明細(xì)賬的設(shè)置與登記3.2.4 總賬的格式和登記3.2.1 現(xiàn)金日記賬的設(shè)置與登記現(xiàn)金日記賬是用來逐日反映庫存現(xiàn)金的收入、付出及結(jié)余情況的特種日記賬。它是由單位出納人員根據(jù)審核無誤的現(xiàn)金收、付款憑證和從銀行提現(xiàn)的銀付憑證逐筆進(jìn)行登記的。為了確保賬簿的安全、完整，現(xiàn)金日記賬必須采用訂本式賬簿。現(xiàn)金日記賬的格式一般有“三欄式”、“多欄式”和“收付分頁式”三種。在實(shí)際工作中大多采用的是“三欄式”賬頁格式。 3.2.1.1 建立“登記現(xiàn)金日記賬”工作表

4、1在Excel中新建一工作簿，將其命名為“現(xiàn)金日記賬的登記”進(jìn)行保存，雙擊Sheet1標(biāo)簽，將其命名為“登記現(xiàn)金日記賬”，對其進(jìn)行內(nèi)容與格式的設(shè)計(jì)。2對工作表進(jìn)行格式設(shè)計(jì)。3設(shè)計(jì)會計(jì)科目表，在“現(xiàn)金日記賬的登記”工作簿中，雙擊Sheet3工作表標(biāo)簽，將其命名為“會計(jì)科目表”。按新會計(jì)準(zhǔn)則設(shè)置會計(jì)科目，也可將在第2章中使用的“會計(jì)科目表”復(fù)制過來。3.2.1.1 建立“登記現(xiàn)金日記賬”工作表4對“登記現(xiàn)金日記賬”工作表進(jìn)行公式和數(shù)據(jù)有效性設(shè)計(jì)（1）單擊A3單元格，輸入公式“=YEAR(TODAY()&年”。（2）設(shè)置輸入月份的數(shù)據(jù)有效性規(guī)則。（3）設(shè)置輸入日期的數(shù)據(jù)有效性規(guī)則。（4）設(shè)置輸入對方

5、科目的數(shù)據(jù)有效性規(guī)則。（5）設(shè)置借、貸方金額的數(shù)據(jù)有效性規(guī)則。（6）設(shè)置余額公式5對表進(jìn)行保護(hù)3.2.1.2 建立“輸出現(xiàn)金日記賬”工作表1在“現(xiàn)金日記賬的登記”工作簿中，雙擊Sheet2標(biāo)簽，將其命名為“輸出現(xiàn)金日記賬”，對其進(jìn)行內(nèi)容與格式的設(shè)計(jì)。2公式設(shè)置這樣，“輸出現(xiàn)金日記賬”工作表的公式便設(shè)置完成了，用戶接下來可以對其進(jìn)行保護(hù)設(shè)置。3.2.2 銀行存款日記賬的設(shè)置與登記銀行存款日記賬是專門用來記錄銀行存款收支業(yè)務(wù)的一種特種日記賬。銀行存款日記賬必須采用訂本式賬簿，其賬頁格式一般采用“收入”（借方）、“支出”（貸方）和“余額”三欄式。銀行存款收入數(shù)額應(yīng)根據(jù)有關(guān)的現(xiàn)金付款憑證登記。 3.2

6、.2 銀行存款日記賬的設(shè)置與登記1建立銀行存款日記賬，將工作表命名為“中國工商銀行”。（假設(shè)該單位的開戶行除中國工商銀行以外，還有中國銀行和中國農(nóng)業(yè)銀行，中國銀行和中國農(nóng)業(yè)銀行的日記賬分別存放在“中國銀行”和“中國農(nóng)業(yè)銀行”工作表中，在此省略。）2建立銀行存款日記賬匯總表。3輸入期初數(shù)據(jù)及設(shè)計(jì)公式SUMIF函數(shù)可以對區(qū)域中符合指定條件的值求和。其語法為：SUMIF(range,criteria,sum_range)。其中：range表示用于條件計(jì)算的單元格區(qū)域。每個區(qū)域中的單元格都必須是數(shù)字或名稱、數(shù)組或包含數(shù)字的引用?？罩岛臀谋局祵⒈缓雎?。Criteria表示用于確定對哪些單元格求和的條件，

7、其形式可以為數(shù)字、表達(dá)式、單元格引用、文本或函數(shù)。任何文本條件或任何含有邏輯或數(shù)學(xué)符號的條件都必須使用雙引號括起來。如果條件為數(shù)字，則無需使用雙引號。sum_range表示要求和的實(shí)際單元格。如果省略sum_range參數(shù)，Excel會對在范圍參數(shù)中指定的單元格求和。使用SUMIF函數(shù)匹配超過255個字符的字符串時(shí)，將返回不正確的結(jié)果#VALUE!。sum_range參數(shù)與range參數(shù)的大小和形狀可以不同。求和的實(shí)際單元格通過以下方法確定：使用sum_range參數(shù)中左上角的單元格作為起始單元格，然后包括與range參數(shù)大小和形狀相對應(yīng)的單元格。3.2.3 明細(xì)賬的設(shè)置與登記3.2.3.1明

8、細(xì)賬的格式明細(xì)賬是按照明細(xì)分類賬戶開設(shè)的，連續(xù)、分類登記某一類經(jīng)濟(jì)業(yè)務(wù)，提供詳細(xì)核算資料的賬簿。它能夠具體、詳細(xì)地反映經(jīng)濟(jì)活動情況，對總分類賬起輔助和補(bǔ)充作用，同時(shí)也為會計(jì)報(bào)表的編制提供必要的明細(xì)資料。明細(xì)賬的格式有三欄式、多欄式、數(shù)量金額式等，但最基本的格式是有“借方”、“貸方”、“余額”三欄的三欄式 。3.2.3 明細(xì)賬的設(shè)置與登記3.2.3.2 數(shù)據(jù)庫（1）數(shù)據(jù)清單的字段名占一行，且必須是第一行（不一定是工作表中的第一行）；（2）每個字段的名稱必須惟一；（3）每一列必須有相同的數(shù)據(jù)類型和格式，且每一列必須相鄰；（4）每條記錄占一行，且必須連續(xù)，中間不能有空行；（5）每個單元格輸入的數(shù)據(jù)前

9、面不能有空格；（6）如果工作表內(nèi)除數(shù)據(jù)清單外還有其他數(shù)據(jù)，則數(shù)據(jù)清單和其他數(shù)據(jù)之間必須有至少一行或一列的空白單元格；（7）避免在一個工作表上建立多個數(shù)據(jù)清單。 3.2.3 明細(xì)賬的設(shè)置與登記3.2.3.3 數(shù)據(jù)庫的生成利用數(shù)據(jù)庫生成明細(xì)賬的基本思路是：首先設(shè)置數(shù)據(jù)庫的結(jié)構(gòu)，其次將所錄入的記賬憑證內(nèi)容通過一定的方法輸入到數(shù)據(jù)庫中，最后對數(shù)據(jù)庫中的數(shù)據(jù)進(jìn)行篩選，生成明細(xì)賬。3.2.3 明細(xì)賬的設(shè)置與登記（1）設(shè)置條件區(qū)域。（2）選擇數(shù)據(jù)區(qū)域。（3）執(zhí)行“高級篩選”命令。在“數(shù)據(jù)”選項(xiàng)卡中單擊中“排序和篩選”數(shù)據(jù)組中的“高級”命令，打開“高級篩選”對話框。在對話框中，提供了兩種篩選方式：“在原有區(qū)域

10、顯示篩選結(jié)果”和“將篩選結(jié)果復(fù)制到其他位置”。如果希望通過在原有數(shù)據(jù)區(qū)域隱藏不符合條件的數(shù)據(jù)行來篩選數(shù)據(jù)清單，則選中“在原有區(qū)域顯示篩選結(jié)果”；如果希望通過將符合條件的數(shù)據(jù)行復(fù)制到工作表的其他位置來篩選數(shù)據(jù)清單，則選中“將篩選結(jié)果復(fù)制到其他位置”。（5）復(fù)制篩選結(jié)果。（6）粘貼篩選結(jié)果。（7）設(shè)置余額計(jì)算公式。（8）設(shè)置余額方向。（9）計(jì)算本期發(fā)生額和期末余額。3.2.3 明細(xì)賬的設(shè)置與登記3.2.3.4 動態(tài)明細(xì)賬的制作方法1設(shè)計(jì)明細(xì)賬2制作“期初余額表”3為明細(xì)賬設(shè)置公式（1）單擊A1單元格，輸入公式“C3&-&C4&明細(xì)賬”。（2）輸入會計(jì)科目編碼INDEX函數(shù)：返回表格或區(qū)域中的值或值

11、的引用。它有兩種形式：數(shù)組形式和引用形式。這里重點(diǎn)介紹引用形式。其語法為：INDEX(reference,row_num,column_num,area_num)，其中：reference表示對一個或多個單元格區(qū)域的引用。如果為引用輸入一個不連續(xù)的區(qū)域，必須將其用括號括起來。如果引用中的每個區(qū)域只包含一行或一列，則相應(yīng)的參數(shù)row_num或column_num分別為可選項(xiàng)。row_num表示引用中某行的行號，函數(shù)從該行返回一個引用。column_num表示引用中某列的列標(biāo)，函數(shù)從該列返回一個引用。area_num表示選擇引用中的一個區(qū)域，以從中返回row_num和column_num的交叉區(qū)域

12、。如果將row_num或column_num設(shè)置為0，函數(shù)INDEX分別返回對整列或整行的引用。Row_num、column_num和area_num必須指向reference中的單元格；否則，函數(shù)INDEX返回錯誤值#REF!。如果省略row_num和column_num，函數(shù)INDEX返回由area_num所指定的引用中的區(qū)域。3.2.3 明細(xì)賬的設(shè)置與登記MATCH函數(shù)：可在單元格區(qū)域中搜索指定項(xiàng)，然后返回該項(xiàng)在單元格區(qū)域中的相對位置。例如，如果單元格區(qū)域A1:A3包含值5、25和38，輸入公式“=MATCH(25,A1:A3,0)”確認(rèn)后會返回?cái)?shù)字2，因?yàn)橹?5是單元格區(qū)域中的第二項(xiàng)。

13、其語法為：MATCH(lookup_value,lookup_array,match_type)其中：lookup_value表示需要在lookup_array中查找的值。lookup_value參數(shù)可以為值（數(shù)字、文本或邏輯值）或?qū)?shù)字、文本或邏輯值的單元格引用。lookup_array表示要搜索的單元格區(qū)域。match_type為數(shù)字-1、0或1，指定Excel如何在lookup_array 中查找lookup_value的值。此參數(shù)的默認(rèn)值為1。lookup_array 參數(shù)中的值必須按降序排列。注意：MATCH函數(shù)會返回lookup_array中匹配值的位置而不是匹配值本身。在查找文本

14、值時(shí)，MATCH函數(shù)不區(qū)分大小寫字母。如果MATCH函數(shù)找不到指定的數(shù)據(jù)，它會返回錯誤值#N/A。（3）輸入日期3.2.3 明細(xì)賬的設(shè)置與登記（4）輸入期初余額使用VLOOKUP函數(shù)可以搜索某個單元格區(qū)域的第一列，然后返回該區(qū)域相同行上任何單元格中的值。其語法為：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)，其中：lookup_value表示要在表格或區(qū)域的第一列中搜索的值，它可以是值或引用。如果為lookup_value參數(shù)提供的值小于table_array參數(shù)第一列中的最小值，則VLOOKUP將返回錯誤值#N/A。

15、table_array表示含數(shù)據(jù)的單元格區(qū)域?？梢允褂脤^(qū)域或區(qū)域名稱的引用。table_array第一列中的值是由lookup_value搜索的值。這些值可以是文本、數(shù)字或邏輯值。文本不區(qū)分大小寫。col_index_num表示table_array參數(shù)中必須返回的匹配值的列號。col_index_num參數(shù)為1時(shí)，返回table_array第一列中的值；col_index_num為2時(shí)，返回table_array第二列中的值，依此類推。如果col_index_num參數(shù)小于1，則VLOOKUP返回錯誤值#VALUE!。如果大于table_array的列數(shù)，則VLOOKUP返回錯誤值#REF

16、!。range_lookup是一個邏輯值，指定希望VLOOKUP查找精確匹配值還是近似匹配值。如果range_lookup為TRUE或被省略，則返回精確匹配值或近似匹配值。如果找不到精確匹配值，則返回小于lookup_value的最大值。如果range_lookup參數(shù)為FALSE，VLOOKUP將只查找精確匹配值。如果table_array的第一列中有兩個或更多值與lookup_value匹配，則使用第一個找到的值。如果找不到精確匹配值，則返回錯誤值#N/A。3.2.3 明細(xì)賬的設(shè)置與登記（5）在“記賬憑證數(shù)據(jù)庫”中查找符合條件的記錄OFFSET函數(shù)可以以指定的引用為參照系，通過給定偏移量得

17、到新的引用。返回的引用可以為一個單元格或單元格區(qū)域。并可以指定返回的行數(shù)或列數(shù)。其語法為：OFFSET(reference,rows,cols,height,width)。其中：Reference作為偏移量參照系的引用區(qū)域。Reference必須為對單元格或相連單元格區(qū)域的引用，否則，OFFSET返回錯誤值#VALUE!。Rows表示相對于偏移量參照系的左上角單元格，上（下）偏移的行數(shù)。如果使用2作為參數(shù)Rows，則說明目標(biāo)引用區(qū)域的左上角單元格比reference低2行。行數(shù)可為正數(shù)（代表在起始引用的下方）或負(fù)數(shù)（代表在起始引用的上方）。Cols表示相對于偏移量參照系的左上角單元格，左（右）

18、偏移的列數(shù)。如果使用2作為參數(shù)Cols，則說明目標(biāo)引用區(qū)域的左上角的單元格比reference 靠右2列。列數(shù)可為正數(shù)（代表在起始引用的右邊）或負(fù)數(shù)（代表在起始引用的左邊）。Height表示高度，即所要返回的引用區(qū)域的行數(shù)。其值必須為正數(shù)。Width表示寬度，即所要返回的引用區(qū)域的列數(shù)。其值必須為正數(shù)。如果省略height或width，則假設(shè)其高度或?qū)挾扰creference相同。3.2.4 總賬的格式和登記總賬是指總分類賬簿，也稱總分類賬，是根據(jù)總分類科目開設(shè)賬戶，用來登記全部經(jīng)濟(jì)業(yè)務(wù)，進(jìn)行總分類核算，提供總括核算資料的分類賬簿?？偡诸愘~所提供的核算資料，是編制會計(jì)報(bào)表的主要依據(jù)，任何單位都必須設(shè)置總分類賬?？偡诸愘~一般采用訂本式賬簿?？偡诸愘~的賬頁格式，一般采用“借方”、“貸方”、“余額”在欄式，根據(jù)實(shí)際需要，也可以在“借方”、“貸方”兩