EXCEL在財務(wù)中的應(yīng)用.pptx

Excel在財務(wù)中的應(yīng)用,我們先來認識Excel軟件最好的老師是誰?,請打開Microsoft Office Excel 任何版本,1,請點擊Excel右上角“?”-“必須聯(lián)網(wǎng)”,2,認識Excel窗體結(jié)構(gòu),系統(tǒng)按鈕,主菜單欄,常用工具欄,編輯欄,格式設(shè)定工具欄,程序標題欄,工作簿控制按鈕,垂直滾動條,水平滾動條,工作表標簽,工作表格區(qū),狀態(tài)欄,行標,列標,工作簿標簽添加按鈕,我們先來解決幾個問題?,當使用您Excel2010以上的版本，給Excel2010以下版本使用者傳文件時，是否會經(jīng)常碰到對方無法打開所傳送的文件?,Q1：,當您在使用Excel想給您的知識產(chǎn)權(quán)如何上鎖保護?,Q2:,如何讓您的下屬在您設(shè)計的圖表里進行機械式的操作?,Q3:,如何給工具欄里增添更多的功能?,Q4:,修改默認文件保存路徑?,Q5:,Excel選項操作,1、錄入按Enter鍵后移動所選內(nèi)容:默認”向下“、其他三個”向上、向右、向左”。,2、滾動條的設(shè)置。,3、顯示行與列標。,4、在單元格顯示公式而非計算結(jié)果。,5、顯示網(wǎng)格線及網(wǎng)格線顏色。,6、默認的字體及字號。,工作簿基本操作,隱藏取消隱藏,工作表標簽顏色,保護工作簿,查看代碼,移動或復(fù)制工作簿,重命名,插入,在工作簿名稱”sheet”處點擊鼠標右鍵。,1,2,3,4,5,6,7,自定義名稱功能,創(chuàng)建定義名稱的方法,一、使用公式欄左邊的名稱框,二、使用“定義名稱”對話框,三、用行或列標志創(chuàng)建名稱,四、定義三維名稱,五、定義外部名稱,六、常量定義名稱,定義名稱的使用方法,減少輸入的工作量,在一個公式中出現(xiàn)多次相同的字段,超出某些公式的嵌套,EXCEL函數(shù)中使用自定義名稱,七、動態(tài)定義名稱,單元格基本操作,文字方向,自動換行,粘貼選項,批注使用,正90度,負90度,0度,橫排自動換行,豎排自動換行,粘貼公式,粘貼數(shù)值,轉(zhuǎn)置,粘貼批注,粘貼格式,在批注中插入圖片,批注隱藏顯示編輯,數(shù)值類型-自定義,提供了許多選項以便將數(shù)字顯示為百分比、貨幣、日期等。如果這些內(nèi)置格式無法滿足您的需要，您可以自定義內(nèi)置數(shù)字格式以便創(chuàng)建自己的數(shù)字格式。,數(shù)值類型,1.巧變文本為數(shù)字,在工作中，發(fā)現(xiàn)一些通過文本文件或其它財務(wù)軟件的數(shù)據(jù)導(dǎo)入Excel中后居然是以文本形式存在的(數(shù)字默認是右對齊，而文本是左對齊的)。,2.將數(shù)字設(shè)為文本格式,Excel都會將數(shù)字保存為數(shù)字數(shù)據(jù)。若要使Excel將類似零件號碼的數(shù)字解釋為文本，首先應(yīng)將空白單元格設(shè)置成文本格式，然后鍵入數(shù)字。,操作:在空白的單元格中填人數(shù)字1，然后選中這個單元格，執(zhí)行“復(fù)制”命令，然后再選中所要轉(zhuǎn)換的范圍，選擇“選擇性粘貼”中的“乘”，它們都變?yōu)閿?shù)字了。,方法是: 1.選擇含有要設(shè)置成文本格式的數(shù)字單元格; 2.單擊“格式單元格”命令，然后單擊“數(shù)字”選項卡（）; 3.在“分類”列表中，單擊“文本”，然后再單擊“確定”; 4.單擊每個單元格，按F2鍵，然后再按Enter鍵重新輸入數(shù)據(jù),文本導(dǎo)入Excel,1、在記事本中輸入格式化文本，每個數(shù)據(jù)項之間會被空格隔開，也可以用逗號、分號、Tab鍵作為分隔符。,2.在Excel中打開剛才保存的文本文件，出現(xiàn)“文本導(dǎo)入向?qū)?3步驟之1”對話框，選擇“分隔符號”，單擊“下一步”;,3.在“文本導(dǎo)人向?qū)?3步驟之2”對話框中選擇文本數(shù)據(jù)項分隔符號，Excel提供了Tab鍵、分號、逗號以及空格等供你選擇。,4.在“文本導(dǎo)人向?qū)?3步驟之3”對話框中，你可以設(shè)置數(shù)據(jù)的類型，單擊“完成”按鈕即可 。,常規(guī)按鍵使用,超級鏈接,超級鏈接是指從一個網(wǎng)頁指向一個目標的連接關(guān)系,這個目標可以是另一個網(wǎng)頁,也可以是相同網(wǎng)頁上的不同位置，還可以是一個圖片,一個電子郵件地址,一個文件,甚至是一個應(yīng)用程序.而在一個網(wǎng)頁中被用來超鏈接的對象,可以是一段文本或者是一個圖片.當瀏覽者單擊已經(jīng)鏈接的文字或圖片后,鏈接目標將顯示在瀏覽器上,并且根據(jù)目標的類型來打開或運行。這是一種跟一般鏈接不一樣的鏈接方式，能帶給我們更方便和快捷的服務(wù),現(xiàn)有文件或網(wǎng)頁,本文檔中的位置,條件格式,開始,樣式,條件格式,突出顯示單元格規(guī)則,項目選取規(guī)則,數(shù)據(jù)條,色階,圖標集,單元格樣式,套用表格格式,Excel提供了自動格式化的功能，它可以根據(jù)預(yù)設(shè)的格式，將我們制作的報表格式化，產(chǎn)生美觀的報表，也就是表格的自動套用。,相對引用絕對引用混合引用,快速填充,快速填充:默認情況下處于打開狀態(tài)并在感知一種模式時自動填充您的數(shù)據(jù),2013版新增功能。,單擊“文件”“選項”。,2.單擊“高級”，確保“自動快速填充”框被選中。,截取功能,合并功能,Ctrl+E,數(shù)據(jù)快速填充,如何啟用？,功能?,快捷操作?,“MID”,“&”,Excel插件的應(yīng)用,圖表的應(yīng)用,動態(tài)列表與數(shù)據(jù)透視表,數(shù)據(jù)分析與處理,與Word，PPT的結(jié)合使用,Vlookup/Hlookup,查找函數(shù) 功能：給出首列的值，返回其他指定列的值。 格式：=VLOOKUP(已知條件，所在區(qū)域，查找的列號，匹配類型) 注意：給出首列的值必須是選擇數(shù)據(jù)區(qū)域里的第一列?？绫硪?，跨工作薄引用,MID,TEXT,SUBSTITUTE,IF,條件函數(shù) 功能：判斷條件是否滿足，如果滿足給出一個值，不滿足給出另一個值。 格式：=IF(判斷的條件，條件成立的值，條件不成立的值) 注意：可以嵌套最多64個IF。,SUMIF/SUMIFS,條件求和 功能：對多個滿足條件的單元格求和?？山Y(jié)合數(shù)組使用。 格式：=SUMIFS(求和的區(qū)域，條件1的區(qū)域，條件1，條件2的區(qū)域，條件2，) 注意：各個參數(shù)的前后順序。,COUNTIF/COUNTIFS,條件計數(shù) 功能：統(tǒng)計滿足條件的單元格數(shù)目。 格式：=COUNTIFS(條件1的區(qū)域，條件1，條件2的區(qū)域，條件2，) 注意：各個參數(shù)的前后順序。,RANK,排位函數(shù) 功能：當前數(shù)據(jù)在一組數(shù)據(jù)中的排名。 格式：=RANK(比較的數(shù)值，所在的區(qū)域，降序或升級) 注意：單元格的引用，區(qū)域使用定義名稱或絕對引用。,DAY,TODAY,NOW,SUBTOTAL,分類匯總 功能：返回列表或數(shù)據(jù)庫中的分類匯總。 格式：=SUBTOTAL(比較的數(shù)值，所在的區(qū)域，降序或升級) 注意：單元格的引用，區(qū)域使用定義名稱或絕對引用,常用的財務(wù)函數(shù),投資計算函數(shù) 折舊計算函數(shù) 償還率計算函數(shù) 債券及其他金融函數(shù),一、Excel在現(xiàn)金核算中的應(yīng)用,手工會計核算下，出納應(yīng)按經(jīng)濟業(yè)務(wù)發(fā)生的時間先后順序，隨時登記日記賬，并做到日清月結(jié)，即每日現(xiàn)金日記賬都應(yīng)結(jié)出結(jié)存額，（每日小計），以便同實有庫存現(xiàn)金進行核對，賬款相符；每月應(yīng)該結(jié)賬，與會計進行賬簿記錄的核對。,Excel在會計核算和管理中的應(yīng)用實務(wù),二、數(shù)據(jù)庫,1、定義“范圍名稱” 實例：同時打開工作簿“日記賬與會計科目”與“公司基本情況”，并進行窗口重排（水平）,下一步,日記賬與會計科目,公司基本情況,窗口重排,切換到“日記賬與會計科目”工作表,上一步,下一步,在此鍵入“name”,打開引用位置,上一步,下一步,點擊返回， 并“確定”,選擇“公司基本情況” 工作表的d2單元,上一步,下一步,輸入“=name”,說明：當輸入=name回車后，H1變成了公司名稱。 這樣做有什么好處呢？ 提示：當更換公司名稱時怎么做？,上一步,2、concatenate()函數(shù) 用途：將幾個單元格的數(shù)據(jù)合并成一個單元格數(shù)據(jù)。 語法：concatenate(text1,text2) 說明：將數(shù)個字符串連成一個字符串。是一個文本類的 函數(shù) 例：打開“日記賬及會計科目”工作表,在F6單元內(nèi)，插入 Concatenate函數(shù),上一步,下一步,上一步,下一步,選中B:E整列 設(shè)置單元格格式,上一步,下一步,會計所需的格式,上一步,3、自動顯示會計科目 錄入科目代碼，自動顯示會計科目 范例：打開“日記賬和會計科目”工作表,先依照上例定義范圍名稱“會計科目”，引用位置為“會計科目！$B$4:$C$146”,下一步,打開“日記賬和會計科目”工作表,選中H6單元， 插入IF函數(shù),上一步,下一步,G6=“”,選擇函數(shù)VLOOLUP(),“”,上一步,下一步,G6,插入名稱粘貼科目名稱,2,上一步,下一步,說明：H6單元格的公式“=IF(G6=”“,”“,VLOOKUP(G6,會計科目,2,1)”，表示當G6單元格為空格時，H6單元格也顯示空格，如果G6單元格為非空格時，則以G6單元格的數(shù)據(jù)去引用“會計科目”這個范圍名稱，找出完全符合G6單元格的數(shù)據(jù)，并回傳第二行的值到H6單元格。,上一步,4、插入批注 將重要事宜提請使用者注意,選中H6單元,下一步,在批注上點擊 鼠標右鍵，可 設(shè)置其格式。 如字體、字號、 顏色、線條等,上一步,5、自動顯示異常信息 有借必有貸，借貸必相等，我們可利用IF（）函數(shù)和SUM（）函數(shù)，提醒借貸是否平衡 范例：打開“日記賬和會計科目”工作表 在C3中輸入函數(shù)： =IF（SUM（I:I）=SUM（J:J）,“”,“借貸不平衡”）,6、凍結(jié)窗口 窗口凍結(jié)窗口 范例：打開“日記賬和會計科目”工作表 激活工作表的第6行，執(zhí)行窗口凍結(jié)窗口,下一步,被固定,上一步,直線折舊法函數(shù)SLN(),SLN（cost,salvage,life） Cost:資產(chǎn)原值 Salvage:資產(chǎn)在折舊期末的價值（資產(chǎn)凈值） Life：折舊期限 例：某企業(yè)購買了一輛價值30000元的卡車，其折舊年限為10年，殘值為7500元，則每年的折舊額為 SLN（30000，7500，10）=2250元,余額遞減法函數(shù)DB(),DB（cost,salvage,life,period,month） Cost:資產(chǎn)原值 Salvage:資產(chǎn)在折舊期末的價值（資產(chǎn)凈值） Life：折舊期限 Period:為需要計算折舊期末的價值，必須使用與life相同的單位 Month:第一年的月份數(shù)，如省略，則假設(shè)為12 例：某企業(yè)購買了一臺新機器。價值為1000000元，使用期限為6年。殘值為100000元。 DB(1000000,100000,6,1)=319000元 其余，略，可用絕對引用公式,雙倍余額遞減法函數(shù)DDB(),DDB（cost,salvage,life,period,factor） Cost:資產(chǎn)原值 Salvage:資產(chǎn)在折舊期末的價值（資產(chǎn)凈值） Life：折舊期限 Period:為需要計算折舊期末的價值，必須使用與life相同的單位 factor:為余額遞減速率。如果factor被省略，則假設(shè)為2 例：某企業(yè)購買了一臺新機器。價值為2400元，使用期限為10年。殘值為300元。 DB(2400,300,10,1)=480元 DB(2400,300,10,10)=22.12元 其余，略，可用絕對引用公式,可變余額遞減法函數(shù)VDB(),VDB（cost,salvage,life,start_period,end_period,factor,no_switch） Cost:資產(chǎn)原值 Salvage:資產(chǎn)在折舊期末的價值（資產(chǎn)凈值） Life：折舊期限 Start_period:為進行折舊計算的起始期次，必須使用與life相同的單位 End_period:為進行折舊計算的截止期次，必須使用與life相同的單位 No_switch為一邏輯值，指定當折舊值大于余額遞減計算值時，是否轉(zhuǎn)到直線折舊法。 TURE，則不轉(zhuǎn)換，F(xiàn)ALSE或省略，則要轉(zhuǎn)換 例：某企業(yè)購買了一臺新機器。價值為2400元，使用期限為10年。殘值為300元。 VDB(2400,300,10,0,1)=480元, VDB(2400,300,120,0,1=40元 VDB(2400,300,120,6,18)=396.31，是第6到18個月的折舊值,年限總和折舊法函數(shù)SYD(),SYD（cost,salvage,life, per） Cost:資產(chǎn)原值 Salvage:資產(chǎn)在折舊期末的價值（資產(chǎn)凈值） Life：折舊期限 Per:期間，其單位與LIFE相同 例：某企業(yè)購買了一臺輛卡車，價值為30000，使用期限為10年。殘值為7500元。 第1年的折舊額為：SYD(3000,7500,10,1)=4090.91元, 第5年的折舊值為：SYD(3000,7500,10,5)=2454.55元,年金函數(shù)PMT(),PMT(rate,nper,pv,fv,type) 其中:pmt_本金和利息; pv_現(xiàn)值;fv_未來值 例1:10個月付清的年利率為8%的10000貸款,則每月支付額 pmt(8%/12,10,10000)=-1037.03 例2:如果需要以按月定額存款方式在18年中存款50000元,假設(shè)存款年利率為6%,則月存款額 Pmt(6%/12,18*12,0,50000)=-129.08 例3:如果以12%的利率貸出5000元,并希望對方在5個月內(nèi)還清,則每月所得款數(shù) Pmt(12%/12,5,-5000)=1030.20 請計算1:如果貸款10萬元買房,年利率為4%,5年還清,每月的還款額為多少?第1年的還款額為多少？ 計算2：甲公司2004年1月1日從工商銀行借入100萬元，年利率為6.4%，每年還款次數(shù)為2次，分別是6月底和年底等額償還，期限為4年。請計算其每次還款的金額。143660.7621,利息函數(shù)IPMT(),IPMT(rate,per,nper,pv,fv,type) 其中:Ipmt_利息; pv_現(xiàn)值;fv_未來值 例1：三年期，本金8000，年利率10%的銀行貸款的第1個月的利息 ipmt(0.1%/12,1,3*12,8000)=-66.67 例2:三年期，本金8000，年利率10%,且按年支付的銀行貸款的第3年的利息 ipmt(0.1%,3,3