Excel在財(cái)務(wù)中的應(yīng)用 第3版 課件 項(xiàng)目二 Excel在賬務(wù)處理中的應(yīng)用

項(xiàng)目二Excel在賬務(wù)處理中的應(yīng)用李燕通過完成了對Excel基本內(nèi)容的回顧，以及完成了職工信息表的制作之后，對Excel的操作熟悉了不少，于是她決定利用Excel軟件對萬隆燈具有限公司的財(cái)務(wù)進(jìn)行全面改革，讓財(cái)務(wù)工作更加準(zhǔn)確和便捷。首先需要建立一個(gè)賬套，然后再根據(jù)賬務(wù)處理的流程分別設(shè)置會(huì)計(jì)科目、輸入會(huì)計(jì)憑證、進(jìn)行試算平衡，最終形成科目匯總表。工作情境分析任務(wù)一建立賬套任務(wù)二設(shè)置會(huì)計(jì)科目任務(wù)三輸入記賬憑證任務(wù)四生成科目匯總表任務(wù)五建立總賬匯總表Excel在賬務(wù)處理中的應(yīng)用建立賬套o(hù)1任務(wù)一建立賬套

所謂建賬即用Excel建立一個(gè)工作簿，并建立若干張工作表，用以分別存放會(huì)計(jì)科目及其期初余額、記賬憑證，以及根據(jù)記賬憑證自動(dòng)生成的總賬和明細(xì)賬等。

在該任務(wù)中需要明確工作簿與工作表的關(guān)系，用到的操作技能是新建工作簿與工作表、對工作表進(jìn)行重命名。封面效果圖同理設(shè)置月份序列為1至12利用數(shù)據(jù)驗(yàn)證設(shè)置年份序列為2023、2024、2025，當(dāng)然隨著時(shí)間的推移，該序列應(yīng)結(jié)合工作實(shí)際設(shè)置年份序列。設(shè)置會(huì)計(jì)科目o2

會(huì)計(jì)科目是會(huì)計(jì)記賬的核心，一般分為一級(jí)科目、二級(jí)科目等，本任務(wù)中的一級(jí)科目根據(jù)小公司會(huì)計(jì)準(zhǔn)則中的會(huì)計(jì)科目編號(hào)和名稱進(jìn)行設(shè)置，并在其下設(shè)置必要的明細(xì)科目。為了提高工作效率，通常以“科目編碼”取代“科目名稱”作為輸入會(huì)計(jì)科目的依據(jù)。任務(wù)二設(shè)置會(huì)計(jì)科目1.設(shè)置單元格格式

在A1單元格中輸入“科目編碼”，B1單元格中輸入“科目名稱”。

選擇A列，右擊選擇“設(shè)置單元格格式”命令，在彈出的對話框中，設(shè)置數(shù)字類型為文本。（1）設(shè)置表頭與格式任務(wù)二設(shè)置會(huì)計(jì)科目在A列輸入科目編碼時(shí)，可以先將A列都設(shè)為文本格式，然后再輸入數(shù)字；或者在常規(guī)格式下，先輸入單引號(hào)，然后再輸入數(shù)字。切記不要先輸入數(shù)字再改成文本格式，那樣會(huì)引起后面的查找函數(shù)出現(xiàn)錯(cuò)誤。當(dāng)科目增加時(shí)，可以將A列單元格往下拖動(dòng)，以達(dá)到復(fù)制限制條件的作用。任務(wù)二設(shè)置會(huì)計(jì)科目1.設(shè)置單元格格式（2）利用數(shù)據(jù)驗(yàn)證對單元格進(jìn)行限制

選中A2單元格，單擊“數(shù)據(jù)驗(yàn)證”，限制單元格的編碼是唯一的、不重復(fù)的。設(shè)置好后，在“出錯(cuò)警告”選項(xiàng)卡中輸入錯(cuò)誤信息提示。含義：

COUNTIF函數(shù)用于對指定區(qū)域中符合特定條件的單元格進(jìn)行計(jì)數(shù)。

語法格式：

COUNTIF(range,criteria)。其中：range表示要進(jìn)行計(jì)數(shù)的指定區(qū)域，可以包括數(shù)字、數(shù)組、命名區(qū)域或包含數(shù)字的引用；criteria用于決定要統(tǒng)計(jì)數(shù)字、表達(dá)式、單元格引用或文本字符串。知識(shí)技能2-2COUNTIF(S)函數(shù)1.COUNTIF函數(shù)第一個(gè)等號(hào)表示輸入公式；COUNTIF第一個(gè)參數(shù)是區(qū)域，表示范圍是A列；第二個(gè)參數(shù)是條件（A2單元格的內(nèi)容）。函數(shù)用于檢驗(yàn)A列中輸入的內(nèi)容是否等于A2單元格的內(nèi)容。第二個(gè)等于用于判斷COUNTIF函數(shù)的計(jì)數(shù)結(jié)果，若為1表示A列中A2單元格的內(nèi)容是唯一值。若不是，則返回出錯(cuò)警告“不允許編碼重復(fù)”。“=COUNTIF(A:A,A2)=1”1.COUNTIF函數(shù)知識(shí)技能2-2COUNTIF(S)函數(shù)含義：COUNTIFS函數(shù)是COUNTIF函數(shù)的擴(kuò)展。COUNTIF針對單一條件，而COUNTIFS可以實(shí)現(xiàn)對多個(gè)條件同時(shí)求結(jié)果，用來統(tǒng)計(jì)多個(gè)區(qū)域中滿足給定條件的單元格的個(gè)數(shù)。語法格式：

COUNTIFS(criteria_range1，criteria1，criteria_range2，criteria2，…)。2.COUNTIFS函數(shù)知識(shí)技能2-2COUNTIF(S)函數(shù)其中：criteria_range1表示第一個(gè)需要計(jì)算其中滿足某個(gè)條件的單元格數(shù)目的單元格區(qū)域（簡稱條件區(qū)域），criteria1為第一個(gè)區(qū)域中將被計(jì)算在內(nèi)的條件（簡稱條件），其形式可以為數(shù)字、表達(dá)式或文本。同理，criteria_range2為第二個(gè)條件區(qū)域，criteria2為第二個(gè)條件，依次類推；且成對出現(xiàn)。2.COUNTIFS函數(shù)知識(shí)技能2-2COUNTIF(S)函數(shù)統(tǒng)計(jì)職工中學(xué)歷為研究生、職稱為高級(jí)的有多少人？1.設(shè)置單元格格式單擊“開始”選項(xiàng)卡“字體”組中“填充顏色”下拉按鈕，選擇合適的顏色，美化工作表。

單擊“視圖”選項(xiàng)卡“窗口”組中“凍結(jié)窗格”下拉按鈕，選擇“凍結(jié)首行”命令，將首行內(nèi)容固定在現(xiàn)有位置，不隨行列的翻動(dòng)而隱藏。（3）美化工作表任務(wù)二設(shè)置會(huì)計(jì)科目2.會(huì)計(jì)科目名稱定義在創(chuàng)建比較復(fù)雜的工作簿時(shí)，使用名稱可以方便和有效地管理工作簿，比使用單元格引用更清楚明了，這就是定義名稱的功能。會(huì)計(jì)科目表的內(nèi)容將在后面的工作表中引用，為方便引用可以將工作表部分內(nèi)容定義成“會(huì)計(jì)科目”和“科目代碼”兩個(gè)名稱。任務(wù)二設(shè)置會(huì)計(jì)科目名稱可以是任意字符和數(shù)字的組合，字母不區(qū)分大小寫，但是不能以數(shù)字開頭；名稱不能與單元格引用（例如Z$100或R1C1）相同，不能將字母“C”“c”“R”或“r”用作已定義名稱；名稱中不允許使用空格。知識(shí)技能2-3定義名稱命名規(guī)則：定義名稱的方法：使用編輯欄左端的名稱框；在“公式”功能區(qū)“定義的名稱”組中單擊“定義名稱”按鈕；用行或列標(biāo)志創(chuàng)建名稱。知識(shí)技能2-3定義名稱定義名稱在彈出的對話框中，將“名稱”定義為“會(huì)計(jì)科目”，“范圍”設(shè)為“工作簿”，引用范圍選擇A2:B200區(qū)域。定義“會(huì)計(jì)科目”名稱定義名稱定義“科目代碼”名稱與“會(huì)計(jì)科目”名稱類似，具體如圖。知識(shí)技能2-4COUNT系列函數(shù)含義：

COUNTA函數(shù)用于

語法格式：

COUNTA(value1,value2

,…)。其中：

value1,value2

,…為所要計(jì)算的值，參數(shù)個(gè)數(shù)為1-255個(gè)。參數(shù)值可以是任何類型，包括空字符("")，但不包括空白單元格。1.COUNTA函數(shù)該COUNTA函數(shù)用于對會(huì)計(jì)科目工作表中A1：B200中非空單元格進(jìn)行計(jì)數(shù)。雖然A1：B200的范圍內(nèi)已超過了255個(gè)值，但對于COUNTA函數(shù)來說，只相當(dāng)于value1一個(gè)參數(shù)，后面還能添加254個(gè)參數(shù)?！癈OUNTA(會(huì)計(jì)科目!$A$1:$B$200)”含義：

COUNT函數(shù)是計(jì)算參數(shù)列表中的數(shù)字項(xiàng)的個(gè)數(shù)，也就是說只有數(shù)字類型的數(shù)據(jù)才被計(jì)數(shù)，錯(cuò)誤值、空值、邏輯值、文字則被忽略。

語法格式：

COUNT(value1,value2

,…)。其中：

value1,value2

,…為所要計(jì)算的值，參數(shù)個(gè)數(shù)為1-255個(gè)。2.COUNT函數(shù)知識(shí)技能2-4COUNT系列函數(shù)含義：COUNTBLANK函數(shù)是計(jì)算指定單元格區(qū)域中空白單元格的個(gè)數(shù)；恰好和COUNTA函數(shù)互補(bǔ)。

語法格式：

COUNTBLANK(range)。該函數(shù)只有一個(gè)參數(shù)range

，代表的是區(qū)域。3.COUNTBLANK函數(shù)知識(shí)技能2-4COUNT系列函數(shù)知識(shí)技能2-5OFFSET函數(shù)含義：OFFSET函數(shù)的功能是以指定的引用為參照系，通過給定偏移量得到新的引用。返回的引用可以為一個(gè)單元格或單元格區(qū)域，并可以指定返回的行數(shù)或列數(shù)。

語法格式：

OFFSET(reference,rows,cols,height,width)。其中，reference為偏移量參照系的引用區(qū)域rows,cols,height,width。如果空缺，代表不偏移。具體來說，reference必須為對單元格或相連單元格區(qū)域的引用；否則，OFFSET函數(shù)返回錯(cuò)誤值“#VALUE!”。

Rows相對于偏移量參照系的左上角單元格，上（負(fù)數(shù)）/下（正數(shù)）偏移的行數(shù)；Cols相對于偏移量參照系的左上角單元格，左（負(fù)數(shù)）/右（正數(shù)）偏移的列數(shù)。Height高度（必須為正數(shù)），即所要返回的引用區(qū)域的行數(shù)；Width寬度（必須為正數(shù)），即所要返回的引用區(qū)域的列數(shù)。知識(shí)技能2-5OFFSET函數(shù)以“會(huì)計(jì)科目”工作表的A2單元格為參照物作為第一個(gè)系數(shù)；因?yàn)椴恍枰乱坪陀乙菩袛?shù)，所以第二個(gè)、第三個(gè)參數(shù)空缺，當(dāng)然也可以填零；第四個(gè)參數(shù)代表高度，是個(gè)變量，會(huì)隨著A列科目數(shù)量的變化而變化，因此需要用COUNTA函數(shù)來確定；第五個(gè)參數(shù)代表的是寬度，因?yàn)橹恍璋褧?huì)計(jì)科目那列定義為名稱即可，所以寬度為1。設(shè)置記賬憑證工作表o3

合并首行單元格，輸入標(biāo)題；并依次輸入憑證號(hào)、摘要、科目代碼、總賬科目、明細(xì)科目、借方金額、貸方金額等要素。任務(wù)三設(shè)置記賬憑證工作表1.設(shè)置標(biāo)題知識(shí)技能2-6鏈接文本函數(shù)含義：CONCATENATE函數(shù)是文本函數(shù)中的一個(gè)，可將最多255個(gè)文本字符串合并為一個(gè)文本字符串。

語法格式：CONCATENATE(text1,[text2],...

)。其中，text1是必需項(xiàng)，也就是要連接的第一個(gè)文本項(xiàng)。[text2],...為可選項(xiàng)，最多為255項(xiàng)。項(xiàng)與項(xiàng)之間必須用逗號(hào)隔開。1.CONCATENATE函數(shù)知識(shí)技能2-6鏈接文本函數(shù)含義：在CONCATENATE原有基礎(chǔ)上，可實(shí)現(xiàn)多個(gè)范圍或字符串文本的連接。

語法格式：CONCAT(text1,[text2],...

)。2.CONCAT函數(shù)區(qū)別于CONCATENATE函數(shù)的參數(shù)，CONCAT函數(shù)的text參數(shù)除了可以是字符串，還可以是字符串?dāng)?shù)組（比如單元格區(qū)域）。需要說明的是，如果返回的結(jié)果超過32767個(gè)字符，由于單元格限制的原因，CONCAT會(huì)返回#VALUE!錯(cuò)誤。知識(shí)技能2-6鏈接文本函數(shù)含義：PHONETIC函數(shù)可用于提取引用對象的拼音，當(dāng)沒有拼音時(shí)則引用漢字本身；不支持任何公式生成的值，不支持?jǐn)?shù)字、日期、時(shí)間、邏輯值、錯(cuò)誤值等。

語法格式：PHONETIC(reference,...

)。該函數(shù)只有一個(gè)參數(shù)，且必須為單元格引用，如果reference為不相鄰單元格區(qū)域，將返回錯(cuò)誤值#N/A。3.PHONETIC函數(shù)知識(shí)技能2-6鏈接文本函數(shù)含義：從EXCEL2019起，新增TEXTJOIN函數(shù)，可以使用指定的分隔符連接單元格區(qū)域，以及選擇是否忽略空單元格。

語法格式：TEXTJOIN(delimiter,ignore_empty,text1,[text2],...)。4.TEXTJOIN函數(shù)知識(shí)技能2-6鏈接文本函數(shù)其中，delimiter是在每個(gè)文本項(xiàng)間插入的指定分隔符，用英文雙引號(hào)引用，內(nèi)容可以為空。ignore_empty用來控制是否忽略空單元格的，默認(rèn)為True，表示忽略空單元格；也可設(shè)置為False，用以包含空單元格。Text參數(shù)設(shè)置同CONCAT函數(shù)。4.TEXTJOIN函數(shù)任務(wù)三設(shè)置記賬憑證工作表2.設(shè)置科目代碼3.設(shè)置總賬科目任務(wù)三設(shè)置記賬憑證工作表知識(shí)技能2-6VLOOKUP函數(shù)含義：函數(shù)LOOKUP函數(shù)用于從單行單列或從數(shù)組中查找一個(gè)值。對應(yīng)有兩種語法形式：向量形式和數(shù)組形式。1.LOOKUP函數(shù)知識(shí)技能2-7查找函數(shù)（1）向量：用于在單行區(qū)域或單列區(qū)域（向量）中查找數(shù)值，然后返回第二個(gè)單行區(qū)域或單列區(qū)域中相同位置的數(shù)值。語法格式：LOOKUP(lookup_value,lookup_vector,result_vector)，其中，lookup_value是需要查找的值；lookup_vector是查找范圍，需要以升序排序；result_vector是返回值的范圍，且必須與第二個(gè)參數(shù)范圍大小相同。1.LOOKUP函數(shù)（1）向量：1.LOOKUP函數(shù)1.LOOKUP函數(shù)（2）數(shù)組語法格式為：LOOKUP(lookup_value,array),用于從數(shù)組中查找一個(gè)值。第一個(gè)參數(shù)lookup_value同向量形式中的用法，是需要查找的值；第二個(gè)array則是查找的數(shù)組區(qū)間范圍。知識(shí)技能2-7查找函數(shù)（2）數(shù)組：1.LOOKUP函數(shù)含義：VLOOKUP函數(shù)是Excel中的一個(gè)縱向查找函數(shù)，它與LOOKUP函數(shù)和HLOOKUP函數(shù)屬于一類函數(shù)；功能是按列查找，最終返回該列所需查詢序列所對應(yīng)的值。

語法格式：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)。2.VLOOKUP函數(shù)知識(shí)技能2-7查找函數(shù)其中：lookup_value表示要查找的值，可以是數(shù)值、引用或字符串；table_array表示要查找的區(qū)域，可以是對區(qū)域或區(qū)域名稱的引用；col_index_num表示滿足條件的單元格在數(shù)組區(qū)域table_array中的序列號(hào)，用數(shù)字表示；range_lookup有兩個(gè)選項(xiàng)，如果選FALSE或者輸入0，代表精確匹配；如果選擇TURE或不填，代表模糊匹配。2.VLOOKUP函數(shù)在“萬隆燈具有限公司職工信息表”中查找相關(guān)員工的各項(xiàng)信息。HLOOKUP函數(shù)的用法和VLOOKUP基本一致；功能是按行查找，最終返回該行所需查詢序列所對應(yīng)的值。

語法格式：HLOOKUP

(lookup_value,table_array,row_index_num,range_lookup)。3.HLOOKUP函數(shù)知識(shí)技能2-7查找函數(shù)找到各個(gè)部門全年合計(jì)數(shù)含義：XLOOKUP函數(shù)能通過搜索區(qū)域或數(shù)組，然后返回對應(yīng)于它找到的第一個(gè)匹配項(xiàng)的項(xiàng)。如果不存在匹配項(xiàng)，則XLOOKUP可以返回最接近(匹配)值。4.XLOOKUP函數(shù)知識(shí)技能2-7查找函數(shù)在LOOKUP函數(shù)的基礎(chǔ)上，XLOOKUP函數(shù)多增加了三個(gè)參數(shù)，以實(shí)現(xiàn)更為強(qiáng)大的功能。其中：lookup_value,表示需要查找的值;lookup_array,要搜索的數(shù)組或區(qū)域;return_array,要返回的數(shù)組或區(qū)域；

[if_not_found],

如果找不到有效匹配項(xiàng)，則返回此處設(shè)置的文本；4.XLOOKUP

語法格式：XLOOKUP(lookup_value,lookup_array,return_array,

[if_not_found],

[match_mode],[search_mode])。[match_mode],用于指定匹配類型：默認(rèn)0表示精確匹配，如果未找到，則返回#N/A；-1表示在精確匹配沒有找到查詢值時(shí)，返回下一個(gè)較小的項(xiàng)；1表示在精確匹配沒有找到查詢值時(shí)，返回下一個(gè)較大的項(xiàng)；2表示通配符匹配，其中*,?和~有特殊含義。[search_mode]是搜索模式。1表示從第一個(gè)項(xiàng)目開始執(zhí)行搜索；-1表示從最后一個(gè)項(xiàng)目開始執(zhí)行反向搜索；2表示在查找區(qū)域?yàn)樯虻那疤嵯滤阉鳎?2表示在查詢區(qū)域?yàn)榻敌虻那疤嵯滤阉鳌?.XLOOKUP4.設(shè)置明細(xì)科目任務(wù)三設(shè)置記賬憑證工作表5.輸入會(huì)計(jì)分錄任務(wù)三設(shè)置記賬憑證工作表6.分錄的試算平衡

在輸入每筆分錄后通過查看J4和K4單元格來驗(yàn)證借貸金額是否相等。任務(wù)三設(shè)置記賬憑證工作表設(shè)置“科目匯總表”o4任務(wù)四設(shè)置“科目匯總表”

科目匯總主要是根據(jù)分錄憑證記入本期各會(huì)計(jì)科目的借貸方發(fā)生額，再結(jié)合借貸方期初余額，計(jì)算出本期期末借貸方余額。借貸相等就說明金額沒問題，也相當(dāng)于起到了試算平衡的作用。總賬和明細(xì)賬也可以從這里生成。設(shè)置表頭（科目代碼、科目名稱、期初借方金額、期初貸方金額、本期借方發(fā)生額、本期貸方發(fā)生額、期末借方金額、期末貸方金額）；輸入會(huì)計(jì)編碼格式“=會(huì)計(jì)科目!A2”；輸入科目名稱格式“=VLOOKUP(A2,會(huì)計(jì)科目,2,0)”；輸入期初余額（詳見學(xué)習(xí)情境中表2-1）。1.簡單格式設(shè)置及數(shù)據(jù)輸入任務(wù)四設(shè)置“科目匯總表”本期借方發(fā)生額“=IF(LEN(A2)=4,SUMIF('當(dāng)月憑證'!E:E,B2,'當(dāng)月憑證'!G:G),SUMIF('當(dāng)月憑證'!D:D,A2,'當(dāng)月憑證'!G:G))”本期貸方發(fā)生額“=IF(LEN(A2)=4,SUMIF('當(dāng)月憑證'!E:E,B2,'當(dāng)月憑證’!H:H),SUMIF('當(dāng)月憑證'!D:D,A2,'當(dāng)月憑證’!H:H))”2.本期借方和貸方發(fā)生額的函數(shù)設(shè)置任務(wù)四設(shè)置“科目匯總表”其中：range為條件區(qū)域，用于條件判斷的單元格區(qū)域；criteria是求和條件，由數(shù)字、邏輯表達(dá)式等組成的判定條件；sum_range為實(shí)際求和區(qū)域，需要求和的單元格、區(qū)域或引用。省略時(shí)，則條件區(qū)域就是實(shí)際求和區(qū)域。知識(shí)技能2-8SUMIF函數(shù)含義：SUMIF函數(shù)能根據(jù)指定條件對若干單元格、區(qū)域或引用求和。

語法格式：SUMIF(range,criteria,sum_range)。利用SUMIF函數(shù)對總賬科目求和利用SUMIF函數(shù)對明細(xì)科目求和在SUMIF函數(shù)前面加上IF函數(shù)，以判定科目編碼的長度對應(yīng)的究竟是總賬科目還是明細(xì)科目3.計(jì)算期末余額根據(jù)期末借方余額的公式“期末借方余額=期初借方余額+本期借方發(fā)生額-本期貸方發(fā)生額”，可以將G2單元格的公式定義為“=IF（C2-D2+E2-F2>=0,C2-D2+E2-F2,0）”請?jiān)囍?jì)算下期末貸方余額任務(wù)四設(shè)置“科目匯總表”生成“總賬匯總表”o5任務(wù)五總賬匯總表1.創(chuàng)建數(shù)據(jù)透視表單擊“總賬匯總表”工作表數(shù)據(jù)區(qū)域內(nèi)的任意一個(gè)單元格，在“插入”功能區(qū)“表格”組中單擊“數(shù)據(jù)透視表”按鈕。一、利用數(shù)據(jù)透視表制作“總賬匯總表”任務(wù)五總賬匯總表2.生成數(shù)據(jù)透視表內(nèi)容

將“科目編碼”和“科目名稱”拖到“行標(biāo)簽”區(qū)域列表框。

將“期初借方余額”

“期初貸方余額”“本期借方發(fā)生額”“本期貸方發(fā)生額”“期末借方余額”“期末貸方余額”拖到“數(shù)值”區(qū)域列表框；并將計(jì)數(shù)項(xiàng)全部轉(zhuǎn)變?yōu)榍蠛晚?xiàng)。2.生成數(shù)據(jù)透視表內(nèi)容知識(shí)技能2-9數(shù)據(jù)透視表

數(shù)據(jù)透視表是一種交互式的表，可以動(dòng)態(tài)地改變它們的版面布置，以便按照不同方式快速分類匯總大量的數(shù)據(jù)，隨時(shí)選擇其中的頁、行和列中的不同元素，以快速查看源數(shù)據(jù)的不同統(tǒng)計(jì)結(jié)果，也可以重新安排行號(hào)、列標(biāo)和頁字段。每一次改變版面布置時(shí)，數(shù)據(jù)透視表會(huì)立即按照新的布置重新計(jì)算數(shù)據(jù)。另外，如果原始數(shù)據(jù)發(fā)生更改，還可以隨時(shí)更新數(shù)據(jù)透視表的內(nèi)容。創(chuàng)建空白數(shù)據(jù)透視表后，Excel的功能區(qū)中會(huì)出現(xiàn)“數(shù)據(jù)透視表工具”，其下有兩個(gè)選項(xiàng)卡——“分析”和“設(shè)計(jì)”。知識(shí)技能2-9數(shù)據(jù)透視表②“報(bào)表篩選