Excel在財務(wù)管理中的高級運用-8項內(nèi)容－張敦力2014－3－15課件

12023/3/91Excel在財務(wù)管理中的高級運用電郵：電話：1580719210822023/3/92內(nèi)容提要：一、時間價值系數(shù)表的編制二、常用財務(wù)指標計算器的設(shè)計三、貸款償付計劃/租金計劃的編制四、數(shù)據(jù)庫的構(gòu)建和運用五、數(shù)據(jù)的整理和分析六、數(shù)據(jù)透視表的編制七、數(shù)據(jù)關(guān)系的圖形描述八、好好學習、天天向上32023/3/93（一）復利終值系數(shù)（PVIF）（二）復利現(xiàn)值系數(shù)（FVIF）（三）普通年金終值系數(shù)（PVIFA）（四）普通年金現(xiàn)值系數(shù)（FVIFA）一、時間價值系數(shù)表的編制52023/3/951）輸入基本數(shù)據(jù)2）輸入基本計算公式（難點）3）拖拽1.混合引用法62023/3/961）輸入基本數(shù)據(jù)2）在A2中輸入基本計算公式3）選中單元格區(qū)域A2：K124）執(zhí)行運算2.模擬運算表/數(shù)據(jù)表法72023/3/971.通過混合引用拖拽生成2.通過模擬運算表/數(shù)據(jù)表編制（二）復利現(xiàn)值系數(shù)（PVIF）92023/3/992）輸入基本計算公式（如下圖A2）102023/3/910Excel2003：數(shù)據(jù)→模擬運算表Excel2010：數(shù)據(jù)→數(shù)據(jù)工具→假設(shè)分析→模擬運算表112023/3/911在對話框“輸入引用行的單元格”和“輸入引用列的單元格”右側(cè)的空格中分別選定所引用的單元格D1和F15）確定132023/3/913142023/3/914（三）普通年金終值系數(shù)（PVIFA）152023/3/915（四）普通年金現(xiàn)值系數(shù)（FVIFA）172023/3/917如下頁圖，在B10中輸入下列公式即可：=LOOKUP（0,B9:G9,B3:G3）+（-LOOKUP（LOOKUP（0,B9:G9,B3:G3）,B3:G3,B9:G9））/LOOKUP（LOOKUP（0,B9:G9,B3:G3）+1,B3:G3,B4:G4）（一）投資回收期182023/3/918（四）

債券定價（五）債券溢折價攤銷192023/3/919（二）終值現(xiàn)金流量類型計算方法一次性收付款項表達式法、FV函數(shù)法→計算器多次收付款項普通年金表達式法、FV函數(shù)法→計算器預付年金表達式法、FV函數(shù)法→計算器遞延年金表達式法、FV函數(shù)法→計算器無規(guī)律表達式法、FV函數(shù)法→列表212023/3/9211.單變量求解法2.Irr函數(shù)法3.Rate函數(shù)法4.Mirr函數(shù)法5.Xirr函數(shù)法（四）內(nèi)含報酬率（到期收益率/實際利率）（五）有效年利率與名義年利率籌資1000萬元，3年期，到期還本*22年利率付息方式每次利息年利息A銀行7.08%月5.9（1000×7.08%/12）70.8（5.9×12）B銀行7.24%季度18.1（1000×7.24%/4）72.4（18.1×4）C銀行7.29%年72.9（1000×7.29%）72.9*23名義年利率付息方式有效年利率A銀行7.08%月7.31%B銀行7.24%季度7.44%C銀行7.29%年7.29%252023/3/925利用給定的名義年利率和每年的復利期數(shù)，計算有效的年利率。EFFECT(nominal_rate,npery)1.有效利率262023/3/926#NAME？Excel2003:工具→加載宏→分析工具庫Excel2010:文件→選項→加載項→轉(zhuǎn)到→加載宏→分析工具庫可能的問題292023/3/929（一）等額本金還款法本金在整個還款期內(nèi)按借款期限平均償還，利息按期初待償本金和利率計算（二）等額本息還款法每次等額還本付息三、貸款償付計劃/租金計劃的編制302023/3/930企業(yè)借入5年期的長期借款1000萬元，固定年利率6%，本金在每年末按借款期限平均償還，利息按年初待償本金和利率計算。問：每次還款總額為多少？其中有多少是當期承擔的利息？有多少用于償還本金？簡例1（等額本金還款法）：312023/3/931企業(yè)借入5年期的長期借款1000萬元，固定年利率6%，每年末等額還本付息。問：每次還款總額為多少？其中有多少是當期承擔的利息？有多少用于償還本金？簡例2（等額本息還款法）：322023/3/932（一）二/三維數(shù)據(jù)庫（二）應(yīng)收賬款備查賬（三）薪酬管理（人事檔案）（四）存貨管理（進銷存）（五）試題庫與自測四、數(shù)據(jù)庫的構(gòu)建和運用332023/3/9331.一般表格：考勤表/通訊錄/銷售統(tǒng)計表2.財務(wù)報表3.預算報表（一）二/三維數(shù)據(jù)庫為提供以下信息如何設(shè)計報表？每個省的銷售額及銷售數(shù)量每個市的銷售額及銷售數(shù)量每個人的銷售額及銷售數(shù)量不同日期的銷售額及銷售數(shù)量每個省的每個市的銷售額及銷售數(shù)量每個省的每個市的每個人的銷售額及銷售數(shù)量每個省的每個市的每個人的相應(yīng)日期的銷售額及銷售數(shù)量352023/3/935（二）應(yīng)收賬款備查賬362023/3/9361.結(jié)構(gòu)2.根據(jù)身份證號自動生成“出生日期”、“年齡”和“性別”3.薪酬查詢（三）薪酬管理（人事檔案）372023/3/937382023/3/938392023/3/939（四）存貨管理（進銷存）402023/3/940（一）計數(shù)（二）合并計算（三）排序、排位與百分比排位（四）求最值、平均值、方差和標準差五、數(shù)據(jù)的整理和分析（1/2）412023/3/941（五）回歸分析和方差分析（六）列出符合條件的數(shù)據(jù)（七）分別計算和展示每一部門的業(yè)績五、數(shù)據(jù)的整理和分析（2/2）422023/3/942符合特定條件的單元格有多少個符合特定條件的數(shù)據(jù)有多少個

1.單條件計數(shù)2.多條件計數(shù)（一）計數(shù)432023/3/943（1）空白單元格的個數(shù)：countblank（2）非空白單元格的個數(shù)：counta（3）包含數(shù)字的單元格個數(shù)：count（4）特定內(nèi)容的單元格個數(shù)：Countif

frequency1.單條件計數(shù)442023/3/944例：第一行為表頭，A列是“姓名”，B列是“性別”，D列是“年齡”，F(xiàn)列是“工作部門”，H列是“基本工資”。現(xiàn)在要統(tǒng)計：“基本工資”為2000~3000元、年齡為20~30歲的男性的人數(shù)。統(tǒng)計結(jié)果存放在本工作表的其他列。2.多條件計數(shù)452023/3/945（1）COUNTIFS（Excel2010）=COUNTIFS(B2:B301，"男"，D2:D301，">=20"，D2:D301，"<30"，H2:H301，">=2000"，H2:H301，"<3000")2.多條件計數(shù)462023/3/946（2）＝SUM（IF（（B2:B301="男"）*（D2:D301>=20）*（D2:D301<30）*（H2:H301>=2000）*（H2:H301<3000）,1,0））輸入完公式后，按Ctrl+Shift+Enter鍵2.多條件計數(shù)472023/3/947將多個相似格式的工作表或數(shù)據(jù)區(qū)域，按指定的方式進行自動匹配計算。（二）合并計算482023/3/9492023/3/9491.主要目的2.專用術(shù)語3.合并方式4.注意事項（二）合并計算502023/3/9501.單表數(shù)據(jù)匯總（透視表）2.多表數(shù)據(jù)匯總（布局同否？）3.明細與匯總并存（結(jié)果顯示在新表中）4.多表數(shù)據(jù)合并

多張兩列的表合并成一張多列的新表（二）合并計算——主要目的512023/3/9511.源數(shù)據(jù)：源工作表/源區(qū)域→單個、多個工作表或多重工作簿2.目標數(shù)據(jù)：目標工作表/目標區(qū)域→不一定與源數(shù)據(jù)在同一張工作表/工作簿中（二）合并計算——專用術(shù)語522023/3/9523.標志：列標簽/行標簽4.標志位置：首行/最左列5.創(chuàng)建指向源數(shù)據(jù)的鏈接（二）合并計算——專用術(shù)語532023/3/953檢查源數(shù)據(jù)，根據(jù)數(shù)據(jù)結(jié)構(gòu)與布局來確定合適的方式1.按公式進行合并計算2.按位置進行合并計算3.按分類進行合并計算4.從多個合并區(qū)域創(chuàng)建數(shù)據(jù)透視表（二）合并計算——合并方式542023/3/954對于所有類型或排列的數(shù)據(jù)，尤其對布局不同的數(shù)據(jù)，推薦使用公式中的三維引用在合并計算工作表上，復制或輸入待合并計算數(shù)據(jù)的標志單擊用來存放合并計算數(shù)據(jù)的單元格鍵入合并計算公式，通過三維引用，使公式中的引用指向每張工作表中包含待合并數(shù)據(jù)的源單元格1.按公式進行合并計算552023/3/955源數(shù)據(jù)按相同順序排列并使用相同標簽，合并各區(qū)域中相同單元格里的數(shù)據(jù)2.按位置進行合并計算562023/3/956設(shè)置要合并的數(shù)據(jù)為每個區(qū)域命名（也可不命名，在引用時用窗口選定區(qū)域）在“數(shù)據(jù)”菜單中，單擊“合并計算”在“函數(shù)”框中，單擊需要用來對數(shù)據(jù)進行合并的匯總函數(shù)2.按位置進行合并計算572023/3/957單擊“引用位置”框，再單擊要進行合并的第一個區(qū)域的工作表標簽，鍵入?yún)^(qū)域的給定名稱，再單擊“添加”。對每個區(qū)域重復這一步驟如果要在源區(qū)域的數(shù)據(jù)更改的任何時候都自動更新合并表，并且確認以后在合并中不需要包括不同的或附加的區(qū)域，請選中“創(chuàng)建連至源數(shù)據(jù)的鏈接”復選框2.按位置進行合并計算582023/3/958單擊“引用位置”框，再單擊要進行合并的第一個區(qū)域的工作表標簽，鍵入?yún)^(qū)域的給定名稱，再單擊“添加”。對每個區(qū)域重復這一步驟如果要在源區(qū)域的數(shù)據(jù)更改的任何時候都自動更新合并表，并且確認以后在合并中不需要包括不同的或附加的區(qū)域，請選中“創(chuàng)建連至源數(shù)據(jù)的鏈接”復選框2.按位置進行合并計算592023/3/959根據(jù)位置進行合并時，請將“標簽位置”下的復選框設(shè)為空，源數(shù)據(jù)中的行或列標志不被復制到合并中，如果需要合并數(shù)據(jù)的標志，請從源區(qū)域之一進行復制或手工輸入→這是與按分類進行合并的區(qū)別2.按位置進行合并計算602023/3/960源數(shù)據(jù)未按順序排列但使用相同標簽，計劃合并來自包含匹配標志的行或列中的數(shù)據(jù)在“標簽位置”下，選中指示標志在源區(qū)域中位置的復選框：首行、最左列或兩者都選。3.按分類進行合并計算612023/3/9611.確保每個數(shù)據(jù)區(qū)域為列表（包含相關(guān)數(shù)據(jù)的一系列行）格式：第一行中的每一列都具有標志，同一列中包含相似的數(shù)據(jù)，并且在列表中沒有空行或空列。2.根據(jù)位置進行合并時，請確保每個區(qū)域具有相同的布局，請將“標簽位置”下的復選框設(shè)為空（二）合并計算——注意事項622023/3/9623.根據(jù)分類進行合并時，確保要合并的行或列的標志具有相同的拼寫（大小寫、縮寫、空格）。任一與其他源數(shù)據(jù)區(qū)域中的標志不匹配的標志都會導致合并中出現(xiàn)單獨的行或列（二）合并計算——注意事項632023/3/9631.排序2.排位3.百分比排位（三）排序、排位與百分比排位642023/3/964（1）關(guān)鍵詞：主要、次要

（是否包括標題）（2）依據(jù)：數(shù)值、顏色、圖標（3）次序：升、降1.排序：條件652023/3/965rank函數(shù)（Rank.EQ）如果多個值具有相同的排位，則返回該組數(shù)值的最高排位，對重復數(shù)的排位相同，但重復數(shù)的存在將影響后續(xù)數(shù)值的排位2.排位：列出位次662023/3/966RANK.AVG:如果有一個以上的值排位相同，則返回平均排位如有4個數(shù)并列第5，RANK.EQ為4個5，RANK.AVG4個為6.5（5+6+7+8）/42.排位：列出位次672023/3/9671.排名前20%獎勵20002.排在31%～50%的獎勵10003.排在51～80%獎勵500→大于該數(shù)據(jù)的數(shù)據(jù)個數(shù)/（大于該數(shù)據(jù)的數(shù)據(jù)個數(shù)+小于該數(shù)據(jù)的數(shù)據(jù)個數(shù)）思考：如何按名次確定獎金？682023/3/968（1）借助percentrank函數(shù)＝1－PERCENTRANK（array，x，significance）

（2）排位與百分比排位文件→選項→加載項→轉(zhuǎn)到→分析工具庫→數(shù)據(jù)分析3.百分比排位692023/3/969（1）點：原數(shù)據(jù)的序數(shù)（2）排序后的數(shù)據(jù)系列：名稱隨數(shù)據(jù)的標志而變化（3）排位：重復數(shù)據(jù)占用同一位置（4）百分比：按照降序排列3.百分比排位702023/3/9701.最值2.平均值3.方差和標準差4.求協(xié)方差和相關(guān)系數(shù)（四）求最值、平均值、方差和標準差712023/3/971（1）最大值：max函數(shù)（2）最小值：min函數(shù)（3）第幾大：large函數(shù)（4）第幾?。簊mall函數(shù)2.平均值3.方差4.標準差1.求最值（可能不只1個）722023/3/972average函數(shù)DAVERAGE2.求平均值732023/3/973（1）總體方差：VarP函數(shù)（2）樣本方差：Var函數(shù)（3）總體標準差：StDevp函數(shù)（4）樣本標準差：StDev函數(shù)3.求方差和標準差742023/3/974（1）協(xié)方差：covar函數(shù)

=covar（數(shù)據(jù)系列1，數(shù)據(jù)系列2）（2）相關(guān)系數(shù)：CORREL函數(shù)4.求協(xié)方差和相關(guān)系數(shù)752023/3/975Excel2003:工具→數(shù)據(jù)分析→分析工具Excel2010:數(shù)據(jù)→數(shù)據(jù)分析→分析工具（五）回歸分析和方差分析762023/3/976查詢、列示、打印符合一個（或多個）條件的數(shù)據(jù)項1.中南地區(qū)的銷售情況2.中南地區(qū)男、女銷售代表的銷售情況（六）列出符合條件的數(shù)據(jù)772023/3/9771.自動篩選2.高級篩選3.數(shù)據(jù)透視表（六）列出符合條件的數(shù)據(jù)782023/3/9781.各地區(qū)的銷售總額2.男、女銷售代表的銷售總額3.中南地區(qū)男、女銷售代表的銷售總額（七）分別計算和展示每一部門的業(yè)績792023/3/9791.分類匯總（必須先按匯總的標志排序）2.分級顯示3.數(shù)據(jù)透視表（七）分別計算和展示每一部門的業(yè)績802023/3/980（一）數(shù)據(jù)透視表的性質(zhì)（二）數(shù)據(jù)透視表的構(gòu)成要素（三）數(shù)據(jù)透視表的編制（四）多重合并計算數(shù)據(jù)區(qū)域數(shù)六、數(shù)據(jù)透視表812023/3/9811.一種交互式報表2.把繁雜的數(shù)據(jù)簡潔化3.最常用、功能最全的Excel數(shù)據(jù)分析工具之一（一）數(shù)據(jù)透視表的性質(zhì)（1/3）822023/3/982可以快速分類匯總、比較大量的數(shù)據(jù)可以隨時選擇其中頁、行和列中的不同元素，以達到快速查看源數(shù)據(jù)的不同統(tǒng)計結(jié)果可以隨意顯示和打印出你所感興趣區(qū)域的明細數(shù)據(jù)。（一）數(shù)據(jù)透視表的性質(zhì)（2/3）832023/3/983一張“數(shù)據(jù)透視表”僅靠鼠標移動字段位置，即可變換出各種類型的報表。（一）數(shù)據(jù)透視表的性質(zhì)（3/3）842023/3/9841.頁字段/報表篩選→頁2.行字段/標簽→行3.列字段/標簽→列4.數(shù)據(jù)項/數(shù)值→值（二）數(shù)據(jù)透視表的構(gòu)成要素可以自定義這些字段設(shè)置以便對其重命名，并將其格式化為需要的格式852023/3/9851.明確目的2.設(shè)定結(jié)構(gòu)3.鼠標移至數(shù)據(jù)源中（定位于數(shù)據(jù)區(qū)域內(nèi)任一單元格）（三）數(shù)據(jù)透視表的編制（1/3）862023/3/9864.插入數(shù)據(jù)透視表5.設(shè)計數(shù)據(jù)透視表6.美化版面（三）數(shù)據(jù)透視表的編制（2/3）872023/3/9875.設(shè)計數(shù)據(jù)透視表（1）根據(jù)目的和結(jié)構(gòu)拖拽頁字段、行字段、列字段和數(shù)據(jù)項（2）調(diào)整頁字段、行字段、列字段、數(shù)據(jù)項（3）重新設(shè)定匯總項882023/3/9882007和2010中數(shù)據(jù)透視表向?qū)П浑[藏在后臺通過快捷鍵調(diào)出使用（Alt+D+P）直接自定義在快速訪問工具欄中（四）多重合并計算數(shù)據(jù)區(qū)域數(shù)892023/3/9892007：Office按鈕——Excel選項——自定義——不在功能區(qū)中的命令——數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А砑印_定2010：文件—選項—快速訪問工具欄——不在功能區(qū)中的命令——數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А砑印_定（四）多重合并計算數(shù)據(jù)區(qū)域數(shù)902023/3/9901.頁：多重合并計算區(qū)域數(shù)據(jù)透視表中的新字段，分析時可以通過拖拽實現(xiàn)數(shù)據(jù)分析的目的頁字段數(shù)目的最大值為4（四）多重合并計算數(shù)據(jù)區(qū)域數(shù)912023/3/9912.行：始終由數(shù)據(jù)源中的第一列組成3.列：是一項高級字段，為數(shù)據(jù)源中的第一列之外其他列的合并打包（四）多重合并計算數(shù)據(jù)區(qū)域數(shù)922023/3/9924.值：數(shù)據(jù)源中除首字段外的所有“項”?！爸怠弊侄文J數(shù)據(jù)源的文本項為零值（四）多重合并計算數(shù)據(jù)區(qū)域數(shù)932023/3/993（一）資金習性示意圖（二）投資組合機會集曲線（三）利潤構(gòu)成分析餅形圖（四）產(chǎn)品銷售對比分析柱形圖七、數(shù)據(jù)關(guān)系的圖形描述942023/3/994（五）動態(tài)圖表（六）主次坐標軸圖表（七）甘特圖七、數(shù)據(jù)關(guān)系的圖形描述952023/3/995（一）資金習性示意圖962023/3/996（二）投資組合機會集曲線972023/3/9