Excel財(cái)務(wù)管理建模與應(yīng)用（第2版）課件 第1、2章 Excel在財(cái)務(wù)管理中應(yīng)用的方法；函數(shù)、公式和宏的使用

1

Excel在財(cái)務(wù)管理中應(yīng)用的方法本章主要內(nèi)容應(yīng)用Excel處理財(cái)務(wù)業(yè)務(wù)的步驟準(zhǔn)備工作簿數(shù)據(jù)表分析方式簡介圖形分析方式簡介用PowerPoint展示分析結(jié)果2Excel會(huì)計(jì)應(yīng)用1.1 應(yīng)用Excel處理財(cái)務(wù)業(yè)務(wù)的步驟明確財(cái)務(wù)業(yè)務(wù)目標(biāo)：解決什么問題？確定需采用的方法：哪些Excel工具？利用Excel建立模型。利用Excel自動(dòng)求解。展示計(jì)算結(jié)果。3Excel會(huì)計(jì)應(yīng)用1.2 準(zhǔn)備工作簿1.2.1新建工作簿4Excel會(huì)計(jì)應(yīng)用1.2.2認(rèn)識(shí)工作簿窗口5Excel會(huì)計(jì)應(yīng)用1.2.3保存工作簿6Excel會(huì)計(jì)應(yīng)用1.2.3保存工作簿7Excel會(huì)計(jì)應(yīng)用1.2.4為工作簿添加摘要信息8Excel會(huì)計(jì)應(yīng)用1.2.4為工作簿添加摘要信息9Excel會(huì)計(jì)應(yīng)用1.3 數(shù)據(jù)表分析方式1.3.1 設(shè)計(jì)數(shù)據(jù)表格式10Excel會(huì)計(jì)應(yīng)用1.3.2采集數(shù)據(jù)11Excel會(huì)計(jì)應(yīng)用復(fù)雜數(shù)據(jù)的自動(dòng)填充12Excel會(huì)計(jì)應(yīng)用自定義序列的自動(dòng)填充編輯自定義列表13Excel會(huì)計(jì)應(yīng)用編輯自定義列表14Excel會(huì)計(jì)應(yīng)用用下拉列表快速輸入數(shù)據(jù)在工作表某個(gè)單元格區(qū)域（如A4:A8）里輸入各部門名選取下拉列表單元格區(qū)域，例如B1:B20打開【數(shù)據(jù)】選項(xiàng)卡，單擊【數(shù)據(jù)驗(yàn)證】按鈕，打開“數(shù)據(jù)驗(yàn)證”對話框。單擊【設(shè)置】選項(xiàng)卡，在“允許”下拉列表中選擇“序列”。在“來源”輸入數(shù)據(jù)來源區(qū)域$A$4:$A$8。15Excel會(huì)計(jì)應(yīng)用1.3.3 編輯工作表選定編輯對象【選擇和查找】16Excel會(huì)計(jì)應(yīng)用條件格式17Excel會(huì)計(jì)應(yīng)用1.4 圖形分析方式18Excel會(huì)計(jì)應(yīng)用1.4.1圖表類型1.4.2建立圖表嵌入式圖表圖表工作表19Excel會(huì)計(jì)應(yīng)用1.4.3編輯圖表20Excel會(huì)計(jì)應(yīng)用1.5用PowerPoint展示分析結(jié)果1.5.1展示分析數(shù)據(jù)進(jìn)入PowerPoint編輯狀態(tài)復(fù)制工作表中的單元格區(qū)域生成展示幻燈片1.5.2展示分析圖表21Excel會(huì)計(jì)應(yīng)用2函數(shù)、公式和宏的使用本章主要內(nèi)容函數(shù)的基本結(jié)構(gòu)及函數(shù)的類型如何使用函數(shù)公式的組成公式的輸入方法單元格的引用錄制、運(yùn)行和編輯宏23Excel會(huì)計(jì)應(yīng)用2.1 函數(shù)函數(shù)的基本結(jié)構(gòu)參數(shù)和函數(shù)值的類型函數(shù)的嵌套函數(shù)的類型如何使用函數(shù)24Excel會(huì)計(jì)應(yīng)用2.1.1函數(shù)的基本結(jié)構(gòu)Excel函數(shù)一般由函數(shù)名稱、參數(shù)和括號(hào)組成。函數(shù)的基本結(jié)構(gòu)：函數(shù)名稱（參數(shù)1，參數(shù)2，…，參數(shù)n）比如，求一個(gè)單元格區(qū)域B2和G10之間以及B12和G12之間所有有數(shù)據(jù)的單元格數(shù)據(jù)的平均值=AVERAGE(B2:G10,B12:G12)25Excel會(huì)計(jì)應(yīng)用2.1.2參數(shù)和函數(shù)值的類型26Excel會(huì)計(jì)應(yīng)用函數(shù)中的參數(shù)可以是數(shù)字、文本、邏輯值、單元格引用、名字、錯(cuò)誤值參數(shù)、數(shù)組，也可以是公式或其他函數(shù)。給定的參數(shù)必須能產(chǎn)生有效的值。函數(shù)是參數(shù)按照計(jì)算規(guī)則計(jì)算所得的結(jié)果，也有一定的數(shù)值類型。1.數(shù)字?jǐn)?shù)字可以是正數(shù)或負(fù)數(shù)、整數(shù)或小數(shù)，數(shù)字可以進(jìn)行算術(shù)運(yùn)算，也可以進(jìn)行比較。計(jì)算數(shù)據(jù)20、20.8、40、50這幾個(gè)數(shù)的平均值：=AVERAGE(20,20.8,40,50)參數(shù)和函數(shù)值均是數(shù)字。2.1.2參數(shù)和函數(shù)值的類型27Excel會(huì)計(jì)應(yīng)用2.文本文本是一個(gè)字符串，需要用引號(hào)""括起來。清除語句"Salesvolumeis"中單詞之間的多余的空格：=TRIM("Salesvolumeis")該函數(shù)的參數(shù)和函數(shù)值都是文本類型，其返回結(jié)果是：Salesvolumeis。它去掉了單詞之間多余的空格。2.1.2參數(shù)和函數(shù)值的類型28Excel會(huì)計(jì)應(yīng)用3.邏輯值邏輯值只有2個(gè)值，即TRUE和FALSE，分別代表真和假。邏輯值可以由邏輯運(yùn)算或邏輯函數(shù)所得，例如函數(shù)TRUE()返回邏輯值“真”，函數(shù)FALSE()返回邏輯值“假”。2.1.2參數(shù)和函數(shù)值的類型29Excel會(huì)計(jì)應(yīng)用4.單元格引用單元格引用是將單元格或單元格區(qū)域作為函數(shù)的參數(shù)。5.名稱名稱是為了方便使用或引用而創(chuàng)建的代表單元格、單元格區(qū)域、公式或常量的字符串，名稱只有被定義后才能被使用。2.1.2參數(shù)和函數(shù)值的類型6.錯(cuò)誤值錯(cuò)誤值是由公式不能正確計(jì)算出結(jié)果或公式引用的單元格含有錯(cuò)誤導(dǎo)致的，這時(shí)在工作表單元格中將顯示錯(cuò)誤值。可能的錯(cuò)誤值有：#####錯(cuò)誤、#VALUE!錯(cuò)誤、#DIV/0!錯(cuò)誤、#NAME?錯(cuò)誤、#N/A錯(cuò)誤、#REF!錯(cuò)誤、#NUM!錯(cuò)誤、#NULL!錯(cuò)誤。30Excel會(huì)計(jì)應(yīng)用2.1.2參數(shù)和函數(shù)值的類型31Excel會(huì)計(jì)應(yīng)用7.數(shù)組數(shù)組是一列或若干列數(shù)，在工作表中數(shù)組就是一個(gè)矩形區(qū)域。數(shù)組也可以是用{}括起來的常量。數(shù)組參數(shù)用于數(shù)組公式中。例如利用TREND()函數(shù)對企業(yè)銷售量進(jìn)行直線擬合時(shí)就會(huì)用到數(shù)組參數(shù)。2.1.3函數(shù)的嵌套函數(shù)的嵌套是指在一個(gè)函數(shù)中使用另一函數(shù)的值作為參數(shù)?！纠恳粋€(gè)集團(tuán)公司的3個(gè)子公司1季度的銷售額數(shù)據(jù)保存在區(qū)域A1:C3中，一行代表一個(gè)月的數(shù)據(jù)，要求公司1季度每月的平均銷售額：=AVERAGE(SUM(A1:C1),SUM(A2:C2),SUM(A3:C3))上例中，SUM函數(shù)就是第二級(jí)函數(shù)。32Excel會(huì)計(jì)應(yīng)用2.1.4函數(shù)的類型33Excel會(huì)計(jì)應(yīng)用1.日期與時(shí)間函數(shù)【例9】在單元格中輸入一個(gè)時(shí)間函數(shù)TODAY()，則當(dāng)前的日期會(huì)顯示在單元格中。2種日期系統(tǒng)：1900日期系統(tǒng)和1904日期系統(tǒng)。日期函數(shù)中對年份的處理隨計(jì)算機(jī)系統(tǒng)所采用的日期系統(tǒng)會(huì)有所差異。默認(rèn)情況下，MicrosoftExcelforWindows使用1900日期系統(tǒng)。在計(jì)算機(jī)采用1900日期系統(tǒng)時(shí)，應(yīng)輸入當(dāng)前年份的4位數(shù)字，例如2014。日期和時(shí)間其實(shí)是數(shù)字類型?？梢詫⒁粋€(gè)日期或時(shí)間顯示為數(shù)字，也可以將一個(gè)數(shù)字顯示為日期或時(shí)間。2.1.4函數(shù)的類型2.文本函數(shù)通過文本函數(shù)，可以在公式中處理文字串。例如，可以改變大小寫或確定文字串的長度?？梢詫⑷掌诓迦胛淖执蜻B接在文字串上?！纠?-12】要在工作表的一個(gè)單元格中顯示“截至到今日：????年??月??日的銷售數(shù)據(jù)”。可以用函數(shù)TEXT()將日期值轉(zhuǎn)換成文本，并與其他的字符串拼接起來。34Excel會(huì)計(jì)應(yīng)用2.1.4函數(shù)的類型3.邏輯函數(shù)邏輯函數(shù)可以根據(jù)一定的條件對事件的狀態(tài)進(jìn)行判斷，從而確定事實(shí)的真和假，并據(jù)此返回一定的值，邏輯函數(shù)也能進(jìn)行邏輯運(yùn)算。函數(shù)TRUE()返回邏輯值“真”，函數(shù)FALSE()返回邏輯值“假”。函數(shù)AND()對邏輯參數(shù)進(jìn)行邏輯“與”操作，函數(shù)OR()對邏輯參數(shù)進(jìn)行邏輯“或”操作，函數(shù)NOT()對邏輯參數(shù)進(jìn)行取邏輯“反”操作，這3個(gè)函數(shù)均返回邏輯值。35Excel會(huì)計(jì)應(yīng)用2.1.4函數(shù)的類型36Excel會(huì)計(jì)應(yīng)用4.財(cái)務(wù)函數(shù)財(cái)務(wù)函數(shù)可以進(jìn)行一般的財(cái)務(wù)計(jì)算，如確定貸款的支付額、投資的未來值或凈現(xiàn)值，以及債券或息票的價(jià)值。財(cái)務(wù)函數(shù)中常見的參數(shù)有：未來值(fv)：所有付款發(fā)生后的投資或貸款的價(jià)值。期間數(shù)(nper)：投資的總支付期間數(shù)。付款(pmt)：對于一項(xiàng)投資或貸款的定期支付數(shù)額。現(xiàn)值(pv)：在投資期初的投資或貸款的價(jià)值。例如，貸款的現(xiàn)值為所借入的本金數(shù)額。利率(rate)：投資或貸款的利率或貼現(xiàn)率。類型(type)：付款期間內(nèi)進(jìn)行支付的間隔，如月初或月末。2.1.4函數(shù)的類型5.查找和引用函數(shù)當(dāng)需要在數(shù)據(jù)清單或表格中查找特定數(shù)值，或者需要查找某一單元格的引用時(shí)，可以使用查詢和引用工作表函數(shù)。例如，如果需要在表格中查找與第一列中的值相匹配的數(shù)值，可以使用VLOOKUP()工作表函數(shù)。37Excel會(huì)計(jì)應(yīng)用2.1.4函數(shù)的類型38Excel會(huì)計(jì)應(yīng)用6.統(tǒng)計(jì)函數(shù)統(tǒng)計(jì)工作表函數(shù)用于對數(shù)據(jù)區(qū)域進(jìn)行統(tǒng)計(jì)分析。例如，計(jì)算工作表中一組數(shù)據(jù)的和可使用函數(shù)SUM()，求一組數(shù)據(jù)的平均值可使用函數(shù)AVERAGE()，計(jì)算兩組數(shù)據(jù)的相關(guān)系數(shù)可使用函數(shù)CORREL()，求一組數(shù)據(jù)的最大值可使用函數(shù)MAX()，求一組數(shù)據(jù)的最小值可使用函數(shù)MIN()。2.1.4函數(shù)的類型7.信息函數(shù)信息函數(shù)用于確定存儲(chǔ)在單元格中的數(shù)據(jù)的類型、單元格中數(shù)據(jù)的屬性或操作環(huán)境信息。信息函數(shù)包含CELL()函數(shù)、INFO()函數(shù)、TYPE()函數(shù)、一組IS函數(shù)及其他的一些函數(shù)。39Excel會(huì)計(jì)應(yīng)用2.1.4函數(shù)的類型8.數(shù)據(jù)庫函數(shù)在對數(shù)據(jù)清單中的數(shù)值進(jìn)行分析時(shí)，可以使用數(shù)據(jù)庫函數(shù)。數(shù)據(jù)庫函數(shù)的名稱以字母D開頭。這些函數(shù)均有3個(gè)相同的參數(shù)：database、field和criteria。database：為工作表上包含數(shù)據(jù)清單的區(qū)域。在給定該參數(shù)時(shí)必須包含區(qū)域中作為列標(biāo)志的行。field：為需要匯總的列的標(biāo)志。criteria：為工作表上包含指定條件的區(qū)域。40Excel會(huì)計(jì)應(yīng)用2.1.4函數(shù)的類型【例】有一個(gè)數(shù)據(jù)清單如圖所示，它記錄了公司一季度各商品的銷售額?，F(xiàn)在要計(jì)算一月份銷售收入超過40000的商品的種類，可用函數(shù)：=DCOUNT(A3:D8,"一月份",A11:B12)41Excel會(huì)計(jì)應(yīng)用2.1.5如何使用函數(shù)在單元格中直接輸入函數(shù)【插入函數(shù)】的工具（1）單擊需要輸入函數(shù)的單元格。（2）單擊單元格編輯欄左側(cè)的【插入函數(shù)】按鈕，顯示如圖所示的“插入函數(shù)”對話框。根據(jù)要求選擇函數(shù)類別為【統(tǒng)計(jì)】，42Excel會(huì)計(jì)應(yīng)用2.1.5如何使用函數(shù)（3）單擊【確定】按鈕，顯示如圖的“函數(shù)參數(shù)”對話框（4）在各參數(shù)編輯框中直接輸入?yún)?shù)值，或者單擊參數(shù)編輯框右端的按鈕，選擇輸入?yún)?shù)值。（5）系統(tǒng)計(jì)算后把計(jì)算結(jié)果顯示到單元格中43Excel會(huì)計(jì)應(yīng)用2.2 公式公式是對工作表數(shù)據(jù)進(jìn)行運(yùn)算的方程式。公式可以進(jìn)行數(shù)學(xué)運(yùn)算，例如：加法和乘法，還可以比較工作表數(shù)據(jù)或合并文本。公式可以引用同一工作表中的其他單元格、同一工作簿不同工作表中的單元格，或者其他工作簿的工作表中的單元格。44Excel會(huì)計(jì)應(yīng)用2.2.1公式的組成Excel從等號(hào)“=”開始，根據(jù)運(yùn)算符優(yōu)先次序執(zhí)行計(jì)算，運(yùn)算符優(yōu)先次序相同時(shí)從左到右執(zhí)行計(jì)算?？梢允褂美ㄌ?hào)組合運(yùn)算來控制計(jì)算的順序，用括號(hào)括起來的部分將先執(zhí)行計(jì)算。45Excel會(huì)計(jì)應(yīng)用1.公式中的運(yùn)算符:算術(shù)運(yùn)算符46Excel會(huì)計(jì)應(yīng)用1.公式中的運(yùn)算符:比較運(yùn)算符47Excel會(huì)計(jì)應(yīng)用比較運(yùn)算的結(jié)果是邏輯值48Excel會(huì)計(jì)應(yīng)用1.公式中的運(yùn)算符:引用運(yùn)算符文本運(yùn)算符：“&”，可以連接多個(gè)字符串以產(chǎn)生一長串文本。2.公式中的運(yùn)算次序49Excel會(huì)計(jì)應(yīng)用2.2.2公式的輸入移動(dòng)公式移動(dòng)公式時(shí)，公式中對單元格引用并不改變。例如，C1單元格中有公式：=A1+B1，將該公式移動(dòng)到D2單元格，D2單元格中的公式依然是：=A1+B1。50Excel會(huì)計(jì)應(yīng)用復(fù)制公式復(fù)制公式時(shí)，公式中對單元格的絕對引用不改變。單元格絕對引用的方式是在字母和數(shù)字前加符號(hào)$的引用，如$A$1?！纠緾1單元格中有公式：=$A$1+$B$1，將該公式復(fù)制到D2單元格，D2單元格的公式也是：=$A$1+$B$1。51Excel會(huì)計(jì)應(yīng)用復(fù)制公式用計(jì)算結(jié)果替換公式復(fù)制公式時(shí)，如果要用計(jì)算結(jié)果替換公式，可以在復(fù)制公式后，使用【選擇性粘貼】功能。目標(biāo)單元格中保存的是原單元格公式的計(jì)算結(jié)果，而不是公式本身。52Excel會(huì)計(jì)應(yīng)用2.2.3單元格的引用引用的作用在于標(biāo)識(shí)工作表上的單元格或單元格區(qū)域，并指明公式中所使用數(shù)據(jù)的位置。通過引用，可以在公式中使用工作簿、工作表不同部分的數(shù)據(jù)。引用不同工作簿中的單元格稱為外部引用。引用其它程序中的數(shù)據(jù)稱為遠(yuǎn)程引用。53Excel會(huì)計(jì)應(yīng)用絕對引用與相對引用絕對引用：如果在復(fù)制公式時(shí)希望目標(biāo)單元格的公式依然引用源公式中的單元格，需要使用絕對引用。絕對引用需要在行號(hào)和列號(hào)前加符號(hào)$。例如，要根據(jù)年利率計(jì)算每筆存款的利息，假設(shè)利率存放在單元格A1中，B列存放了若干存款額的數(shù)據(jù)，C列中準(zhǔn)備存放年利息，B列的存款額和C列的利息相對應(yīng)。在計(jì)算利息時(shí)可以使用絕對引用方式引用單元格A1中的利率。54Excel會(huì)計(jì)應(yīng)用絕對引用與相對引用相對引用：在創(chuàng)建公式時(shí)，單元格或單元格區(qū)域的引用相對于包含公式的單元格的相對位置即為相對引用。例如，單元格B1包含公式=A1+5；Excel利用A1單元格中的數(shù)據(jù)加上5得到B1單元格的值，單元格B1其實(shí)保存的是一個(gè)公式“=A1+5”。如果修改A1單元