Excel在財(cái)務(wù)管理中的高級(jí)運(yùn)用-8項(xiàng)內(nèi)容－張敦力課件

1、1Excel在財(cái)務(wù)管理中的高級(jí)運(yùn)用主講：張敦力單位：中南財(cái)經(jīng)政法大學(xué)會(huì)計(jì)學(xué)院電郵：電話：158 0719 21082022/7/1512內(nèi)容提要：一、時(shí)間價(jià)值系數(shù)表的編制二、常用財(cái)務(wù)指標(biāo)計(jì)算器的設(shè)計(jì)三、貸款償付計(jì)劃/租金計(jì)劃的編制四、數(shù)據(jù)庫的構(gòu)建和運(yùn)用五、數(shù)據(jù)的整理和分析六、數(shù)據(jù)透視表的編制七、數(shù)據(jù)關(guān)系的圖形描述八、好好學(xué)習(xí)、天天向上2022/7/1523（一）復(fù)利終值系數(shù)（PVIF）（二）復(fù)利現(xiàn)值系數(shù)（FVIF）（三）普通年金終值系數(shù)（PVIFA）（四）普通年金現(xiàn)值系數(shù)（FVIFA）一、時(shí)間價(jià)值系數(shù)表的編制2022/7/1534（一）復(fù)利終值系數(shù)（FVIF）2022/7/15451）輸入基本

2、數(shù)據(jù)2）輸入基本計(jì)算公式（難點(diǎn)）3）拖拽1.混合引用法2022/7/15561）輸入基本數(shù)據(jù)2）在A2中輸入基本計(jì)算公式3）選中單元格區(qū)域A2：K124）執(zhí)行運(yùn)算2.模擬運(yùn)算表/數(shù)據(jù)表法2022/7/15671.通過混合引用拖拽生成2.通過模擬運(yùn)算表/數(shù)據(jù)表編制（二）復(fù)利現(xiàn)值系數(shù)（PVIF）2022/7/15781）輸入基本數(shù)據(jù)（如下圖）2022/7/15892）輸入基本計(jì)算公式（如下圖A2）2022/7/15910Excel 2003：數(shù)據(jù)模擬運(yùn)算表Excel2010： 數(shù)據(jù)數(shù)據(jù)工具假設(shè)分析模擬運(yùn)算表2022/7/151011在對(duì)話框“輸入引用行的單元格”和 “輸入引用列的單元格”右側(cè)的空格

3、中分別選定所引用的單元格D1和F1 5）確定2022/7/1511122022/7/1512132022/7/151314（三）普通年金終值系數(shù)（PVIFA）2022/7/151415（四）普通年金現(xiàn)值系數(shù)（FVIFA）2022/7/151516（一）投資回收期（二）終值（三）現(xiàn)值或凈現(xiàn)值二、常用財(cái)務(wù)指標(biāo)計(jì)算器的設(shè)計(jì)（四）內(nèi)含報(bào)酬率（到期收益率/實(shí)際利率）（五）有效利率與名義利率2022/7/151617如下頁圖，在B10中輸入下列公式即可：=LOOKUP（0,B9:G9,B3:G3）+（-LOOKUP（LOOKUP（0,B9:G9,B3:G3）,B3:G3,B9:G9）/LOOKUP（LOO

4、KUP（0,B9:G9,B3:G3）+1,B3:G3,B4:G4）（一）投資回收期2022/7/151718（四） 債券定價(jià)（五）債券溢折價(jià)攤銷2022/7/151819（二）終值現(xiàn)金流量類型計(jì)算方法一次性收付款項(xiàng)表達(dá)式法、FV函數(shù)法計(jì)算器多次收付款項(xiàng)普通年金表達(dá)式法、FV函數(shù)法計(jì)算器預(yù)付年金表達(dá)式法、FV函數(shù)法計(jì)算器遞延年金表達(dá)式法、FV函數(shù)法計(jì)算器無規(guī)律表達(dá)式法、FV函數(shù)法列表2022/7/1519201.表達(dá)式法列表求解法2.PV函數(shù)法3.NPV函數(shù)法容易出錯(cuò)4.XNPV函數(shù)法（三）現(xiàn)值或凈現(xiàn)值2022/7/1520211.單變量求解法2.Irr函數(shù)法3.Rate函數(shù)法4.Mirr函數(shù)法

5、5.Xirr函數(shù)法（四）內(nèi)含報(bào)酬率（到期收益率/實(shí)際利率）2022/7/1521（五）有效年利率與名義年利率籌資1000萬元，3年期，到期還本年利率付息方式每次利息年利息A銀行7.08%月5.9（10007.08%/12）70.8（5.912）B銀行7.24%季度18.1（10007.24%/4）72.4（18.14）C銀行7.29%年72.9（10007.29%）72.92022/7/1522名義年利率付息方式有效年利率A銀行7.08%月7.31%B銀行7.24%季度7.44%C銀行7.29%年7.29%2022/7/1523242022/7/1524Nominal_rate ：名義利率Np

6、ery ：每年的復(fù)利期數(shù)Effect_rate ：有效利率25利用給定的名義年利率和每年的復(fù)利期數(shù)，計(jì)算有效的年利率。EFFECT(nominal_rate, npery)1.有效利率2022/7/1525262022/7/1526#NAME？Excel2003:工具加載宏分析工具庫Excel2010:文件選項(xiàng)加載項(xiàng)轉(zhuǎn)到加載宏分析工具庫可能的問題27Npery 將被截尾取整。 如果任一參數(shù)為非數(shù)值型，函數(shù) EFFECT 返回錯(cuò)誤值 #VALUE!。 如果 nominal_rate 0 或 npery =20，D2:D301，=2000，H2:H301，=20） *（D2:D301=2000）*

7、（H2:H3013000）,1,0）輸入完公式后，按Ctrl+Shift+Enter鍵2.多條件計(jì)數(shù)472022/7/1547將多個(gè)相似格式的工作表或數(shù)據(jù)區(qū)域，按指定的方式進(jìn)行自動(dòng)匹配計(jì)算。（二）合并計(jì)算2022/7/1548492022/7/15491.主要目的2.專用術(shù)語3.合并方式4.注意事項(xiàng)（二）合并計(jì)算502022/7/15501.單表數(shù)據(jù)匯總（透視表）2.多表數(shù)據(jù)匯總（布局同否？）3. 明細(xì)與匯總并存（結(jié)果顯示在新表中）4.多表數(shù)據(jù)合并多張兩列的表合并成一張多列的新表（二）合并計(jì)算主要目的512022/7/15511.源數(shù)據(jù)：源工作表/源區(qū)域 單個(gè)、多個(gè)工作表或多重工作簿2.目標(biāo)數(shù)

8、據(jù)：目標(biāo)工作表/目標(biāo)區(qū)域不一定與源數(shù)據(jù)在同一張工作表/工作簿中（二）合并計(jì)算專用術(shù)語522022/7/15523.標(biāo)志：列標(biāo)簽/行標(biāo)簽4.標(biāo)志位置：首行/最左列5.創(chuàng)建指向源數(shù)據(jù)的鏈接（二）合并計(jì)算專用術(shù)語532022/7/1553檢查源數(shù)據(jù)，根據(jù)數(shù)據(jù)結(jié)構(gòu)與布局來確定合適的方式1.按公式進(jìn)行合并計(jì)算2.按位置進(jìn)行合并計(jì)算3.按分類進(jìn)行合并計(jì)算4.從多個(gè)合并區(qū)域創(chuàng)建數(shù)據(jù)透視表（二）合并計(jì)算合并方式542022/7/1554對(duì)于所有類型或排列的數(shù)據(jù)，尤其對(duì)布局不同的數(shù)據(jù)，推薦使用公式中的三維引用在合并計(jì)算工作表上，復(fù)制或輸入待合并計(jì)算數(shù)據(jù)的標(biāo)志單擊用來存放合并計(jì)算數(shù)據(jù)的單元格鍵入合并計(jì)算公式，通過

9、三維引用，使公式中的引用指向每張工作表中包含待合并數(shù)據(jù)的源單元格1.按公式進(jìn)行合并計(jì)算552022/7/1555源數(shù)據(jù)按相同順序排列并使用相同標(biāo)簽，合并各區(qū)域中相同單元格里的數(shù)據(jù)2.按位置進(jìn)行合并計(jì)算562022/7/1556設(shè)置要合并的數(shù)據(jù)為每個(gè)區(qū)域命名（也可不命名，在引用時(shí)用窗口選定區(qū)域）在“數(shù)據(jù)”菜單中，單擊“合并計(jì)算”在“函數(shù)”框中，單擊需要用來對(duì)數(shù)據(jù)進(jìn)行合并的匯總函數(shù)2.按位置進(jìn)行合并計(jì)算572022/7/1557單擊“引用位置”框，再單擊要進(jìn)行合并的第一個(gè)區(qū)域的工作表標(biāo)簽，鍵入?yún)^(qū)域的給定名稱，再單擊“添加”。對(duì)每個(gè)區(qū)域重復(fù)這一步驟如果要在源區(qū)域的數(shù)據(jù)更改的任何時(shí)候都自動(dòng)更新合并表，

10、并且確認(rèn)以后在合并中不需要包括不同的或附加的區(qū)域，請(qǐng)選中“創(chuàng)建連至源數(shù)據(jù)的鏈接”復(fù)選框2.按位置進(jìn)行合并計(jì)算582022/7/1558單擊“引用位置”框，再單擊要進(jìn)行合并的第一個(gè)區(qū)域的工作表標(biāo)簽，鍵入?yún)^(qū)域的給定名稱，再單擊“添加”。對(duì)每個(gè)區(qū)域重復(fù)這一步驟如果要在源區(qū)域的數(shù)據(jù)更改的任何時(shí)候都自動(dòng)更新合并表，并且確認(rèn)以后在合并中不需要包括不同的或附加的區(qū)域，請(qǐng)選中“創(chuàng)建連至源數(shù)據(jù)的鏈接”復(fù)選框2.按位置進(jìn)行合并計(jì)算592022/7/1559根據(jù)位置進(jìn)行合并時(shí)，請(qǐng)將“標(biāo)簽位置”下的復(fù)選框設(shè)為空，源數(shù)據(jù)中的行或列標(biāo)志不被復(fù)制到合并中，如果需要合并數(shù)據(jù)的標(biāo)志，請(qǐng)從源區(qū)域之一進(jìn)行復(fù)制或手工輸入這是與按分類

11、進(jìn)行合并的區(qū)別2.按位置進(jìn)行合并計(jì)算602022/7/1560源數(shù)據(jù)未按順序排列但使用相同標(biāo)簽，計(jì)劃合并來自包含匹配標(biāo)志的行或列中的數(shù)據(jù)在“標(biāo)簽位置”下，選中指示標(biāo)志在源區(qū)域中位置的復(fù)選框：首行、最左列或兩者都選。3.按分類進(jìn)行合并計(jì)算612022/7/15611.確保每個(gè)數(shù)據(jù)區(qū)域?yàn)榱斜恚ò嚓P(guān)數(shù)據(jù)的一系列行）格式：第一行中的每一列都具有標(biāo)志，同一列中包含相似的數(shù)據(jù)，并且在列表中沒有空行或空列。2.根據(jù)位置進(jìn)行合并時(shí)，請(qǐng)確保每個(gè)區(qū)域具有相同的布局，請(qǐng)將“標(biāo)簽位置”下的復(fù)選框設(shè)為空（二）合并計(jì)算注意事項(xiàng)622022/7/15623. 根據(jù)分類進(jìn)行合并時(shí)，確保要合并的行或列的標(biāo)志具有相同的拼寫（

12、大小寫、縮寫、空格）。任一與其他源數(shù)據(jù)區(qū)域中的標(biāo)志不匹配的標(biāo)志都會(huì)導(dǎo)致合并中出現(xiàn)單獨(dú)的行或列（二）合并計(jì)算注意事項(xiàng)632022/7/15631.排序2.排位3.百分比排位（三）排序、排位與百分比排位642022/7/1564（1）關(guān)鍵詞：主要、次要（是否包括標(biāo)題）（2）依據(jù)：數(shù)值、顏色、圖標(biāo)（3）次序：升、降1.排序：條件652022/7/1565rank函數(shù)（ Rank.EQ）如果多個(gè)值具有相同的排位，則返回該組數(shù)值的最高排位，對(duì)重復(fù)數(shù)的排位相同，但重復(fù)數(shù)的存在將影響后續(xù)數(shù)值的排位2.排位：列出位次662022/7/1566RANK.AVG:如果有一個(gè)以上的值排位相同，則返回平均排位如有4個(gè)

13、數(shù)并列第5，RANK.EQ為4個(gè)5，RANK.AVG4個(gè)為6.5（5+6+7+8）/42.排位：列出位次672022/7/15671.排名前20%獎(jiǎng)勵(lì)20002.排在31%50%的獎(jiǎng)勵(lì)10003.排在5180%獎(jiǎng)勵(lì)500大于該數(shù)據(jù)的數(shù)據(jù)個(gè)數(shù)/（大于該數(shù)據(jù)的數(shù)據(jù)個(gè)數(shù)+小于該數(shù)據(jù)的數(shù)據(jù)個(gè)數(shù)）思考：如何按名次確定獎(jiǎng)金？682022/7/1568（1）借助percentrank函數(shù)1 PERCENTRANK（array，x，significance） （2）排位與百分比排位文件選項(xiàng)加載項(xiàng)轉(zhuǎn)到分析工具庫數(shù)據(jù)分析3.百分比排位692022/7/1569（1）點(diǎn)：原數(shù)據(jù)的序數(shù)（2）排序后的數(shù)據(jù)系列：名稱隨數(shù)

14、據(jù)的標(biāo)志而變化（3）排位：重復(fù)數(shù)據(jù)占用同一位置（4）百分比：按照降序排列3.百分比排位702022/7/15701.最值2.平均值3.方差和標(biāo)準(zhǔn)差4.求協(xié)方差和相關(guān)系數(shù)（四）求最值、平均值、方差和標(biāo)準(zhǔn)差712022/7/1571（1）最大值：max函數(shù)（2）最小值：min函數(shù)（3）第幾大：large函數(shù)（4）第幾?。簊mall函數(shù)2.平均值3.方差4.標(biāo)準(zhǔn)差1.求最值（可能不只1個(gè)）722022/7/1572average函數(shù)DAVERAGE2.求平均值732022/7/1573（1）總體方差：VarP函數(shù)（2）樣本方差：Var函數(shù)（3）總體標(biāo)準(zhǔn)差：StDevp函數(shù)（4）樣本標(biāo)準(zhǔn)差：StDev

15、函數(shù)3.求方差和標(biāo)準(zhǔn)差742022/7/1574（1）協(xié)方差：covar函數(shù)=covar（數(shù)據(jù)系列1，數(shù)據(jù)系列2）（2）相關(guān)系數(shù)：CORREL函數(shù)4.求協(xié)方差和相關(guān)系數(shù)75Excel2003:工具數(shù)據(jù)分析分析工具Excel2010:數(shù)據(jù)數(shù)據(jù)分析分析工具（五）回歸分析和方差分析2022/7/157576查詢、列示、打印符合一個(gè)（或多個(gè)）條件的數(shù)據(jù)項(xiàng)1.中南地區(qū)的銷售情況2.中南地區(qū)男、女銷售代表的銷售情況（六） 列出符合條件的數(shù)據(jù)2022/7/1576771.自動(dòng)篩選2.高級(jí)篩選3.數(shù)據(jù)透視表（六） 列出符合條件的數(shù)據(jù)2022/7/1577782022/7/15781.各地區(qū)的銷售總額2.男、女

16、銷售代表的銷售總額3 .中南地區(qū)男、女銷售代表的銷售總額（七）分別計(jì)算和展示每一部門的業(yè)績792022/7/15791.分類匯總（必須先按匯總的標(biāo)志排序）2.分級(jí)顯示3.數(shù)據(jù)透視表（七）分別計(jì)算和展示每一部門的業(yè)績80（一）數(shù)據(jù)透視表的性質(zhì)（二）數(shù)據(jù)透視表的構(gòu)成要素（三）數(shù)據(jù)透視表的編制（四）多重合并計(jì)算數(shù)據(jù)區(qū)域數(shù)六、數(shù)據(jù)透視表2022/7/1580811.一種交互式報(bào)表2.把繁雜的數(shù)據(jù)簡潔化3.最常用、功能最全的Excel數(shù)據(jù)分析工具之一（一）數(shù)據(jù)透視表的性質(zhì)（1/3）2022/7/158182可以快速分類匯總、比較大量的數(shù)據(jù)可以隨時(shí)選擇其中頁、行和列中的不同元素，以達(dá)到快速查看源數(shù)據(jù)的不同

17、統(tǒng)計(jì)結(jié)果可以隨意顯示和打印出你所感興趣區(qū)域的明細(xì)數(shù)據(jù)。（一）數(shù)據(jù)透視表的性質(zhì)（2/3）2022/7/158283一張“數(shù)據(jù)透視表”僅靠鼠標(biāo)移動(dòng)字段位置，即可變換出各種類型的報(bào)表。（一）數(shù)據(jù)透視表的性質(zhì)（3/3）2022/7/1583841.頁字段/報(bào)表篩選頁2.行字段/標(biāo)簽行3.列字段/標(biāo)簽列4.數(shù)據(jù)項(xiàng)/數(shù)值值（二）數(shù)據(jù)透視表的構(gòu)成要素可以自定義這些字段設(shè)置以便對(duì)其重命名，并將其格式化為需要的格式2022/7/1584851.明確目的2.設(shè)定結(jié)構(gòu)3.鼠標(biāo)移至數(shù)據(jù)源中（定位于數(shù)據(jù)區(qū)域內(nèi)任一單元格）（三）數(shù)據(jù)透視表的編制（1/3）2022/7/1585864.插入數(shù)據(jù)透視表5.設(shè)計(jì)數(shù)據(jù)透視表6.美

18、化版面（三）數(shù)據(jù)透視表的編制（2/3）2022/7/1586875.設(shè)計(jì)數(shù)據(jù)透視表（1）根據(jù)目的和結(jié)構(gòu)拖拽頁字段、行字段、列字段和數(shù)據(jù)項(xiàng)（2）調(diào)整頁字段、行字段、列字段、數(shù)據(jù)項(xiàng)（3）重新設(shè)定匯總項(xiàng)2022/7/1587882007和2010中數(shù)據(jù)透視表向?qū)П浑[藏在后臺(tái)通過快捷鍵調(diào)出使用（Alt+D+P）直接自定義在快速訪問工具欄中（四）多重合并計(jì)算數(shù)據(jù)區(qū)域數(shù)2022/7/1588892007：Office按鈕Excel選項(xiàng)自定義不在功能區(qū)中的命令數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)砑哟_定2010：文件選項(xiàng)快速訪問工具欄不在功能區(qū)中的命令數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)砑哟_定（四）多重合并計(jì)算數(shù)據(jù)區(qū)域數(shù)20

19、22/7/1589901.頁：多重合并計(jì)算區(qū)域數(shù)據(jù)透視表中的新字段，分析時(shí)可以通過拖拽實(shí)現(xiàn)數(shù)據(jù)分析的目的頁字段數(shù)目的最大值為4（四）多重合并計(jì)算數(shù)據(jù)區(qū)域數(shù)2022/7/1590912.行：始終由數(shù)據(jù)源中的第一列組成3.列：是一項(xiàng)高級(jí)字段，為數(shù)據(jù)源中的第一列之外其他列的合并打包（四）多重合并計(jì)算數(shù)據(jù)區(qū)域數(shù)2022/7/1591924.值：數(shù)據(jù)源中除首字段外的所有“項(xiàng)”。“值”字段默認(rèn)數(shù)據(jù)源的文本項(xiàng)為零值（四）多重合并計(jì)算數(shù)據(jù)區(qū)域數(shù)2022/7/159293（一）資金習(xí)性示意圖（二）投資組合機(jī)會(huì)集曲線（三）利潤構(gòu)成分析餅形圖（四）產(chǎn)品銷售對(duì)比分析柱形圖七、數(shù)據(jù)關(guān)系的圖形描述2022/7/159394（五）動(dòng)態(tài)圖表（六）主次坐標(biāo)軸圖表（七）甘特圖七、數(shù)據(jù)關(guān)系的圖形描述2022/7/159495（一）資金習(xí)性示意圖2022/7/159596（二）投資組合機(jī)會(huì)集曲線2022/7/159697（三）利潤構(gòu)成分析餅形圖2022/7/159798（四）產(chǎn)品銷售對(duì)比分析柱形圖2022/7/159899（五）動(dòng)態(tài)圖表視圖工具欄窗體復(fù)選框 2022/7/1599100（六）主次坐標(biāo)軸圖表當(dāng)二維圖表中不同的數(shù)據(jù)系列的數(shù)值范圍變化很大，或在您具有混合數(shù)據(jù)類型（