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

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