Excel使用技巧及函數(shù)課件

1、 IT Day（第3期） Excel公式函數(shù)應(yīng)用 2005.9.19第1頁(yè)，共46頁(yè)。內(nèi)容提要基礎(chǔ)知識(shí)公式函數(shù)的分類介紹 函數(shù)的綜合運(yùn)用財(cái)務(wù)公式第2頁(yè)，共46頁(yè)。第一部分 Excel基礎(chǔ)知識(shí)Excel用戶界面繪圖層上的對(duì)象工作表及公式的保護(hù)基本操作第3頁(yè)，共46頁(yè)。Excel用戶界面菜單 及 鍵盤(pán)快捷鍵快捷菜單:右擊鼠標(biāo)時(shí)顯示智能標(biāo)記任務(wù)窗格:一般在excel的右側(cè)顯示(幫助)對(duì)話框: 例如”工具”選項(xiàng)”拖放: 工具欄的拖放 單元格的拖放對(duì)象和單元格的選擇: Ctrl Shift Ctrl+Shift+ F8數(shù)據(jù)輸入: 值(包括日期或時(shí)間) 文本 布爾值 公式 第4頁(yè)，共46頁(yè)。繪圖層上的對(duì)象

2、圖形圖示圖表第5頁(yè)，共46頁(yè)。公式及單元格的保護(hù)步驟:1.選中要保護(hù)單元格2.”格式”單元格”3.”單元格”中的”保護(hù)”,清除”鎖定”,按”確定”4.”工具”保護(hù)”保護(hù)單元格”5.輸入密碼,選擇所有用戶權(quán)限,確定第6頁(yè)，共46頁(yè)。保護(hù)工作簿結(jié)構(gòu)當(dāng)一個(gè)工作表被保護(hù)工作簿結(jié)構(gòu)后,就不能添加和刪除工作表了;“工具”保護(hù)”保護(hù)工作簿”選擇了”窗口”,連窗口的大小也無(wú)法修改第7頁(yè)，共46頁(yè)。第二部分 公式公式基礎(chǔ)知識(shí)名稱的引用第8頁(yè)，共46頁(yè)。公式中的運(yùn)算符+ 加號(hào)- 減號(hào)/ 除號(hào)* 乘號(hào)% 百分號(hào)& 文本連接符 求冪= 邏輯比較(等于) 邏輯比較(大于)= 邏輯比較(大于或等于)= 邏輯比較(小于或等

3、于) 邏輯比較(不等于)第9頁(yè)，共46頁(yè)。引用運(yùn)算符: (冒號(hào)) 范圍運(yùn)算符, (逗號(hào)) 聯(lián)合運(yùn)算符 (空格) 交叉運(yùn)算符第10頁(yè)，共46頁(yè)。運(yùn)算符優(yōu)先級(jí)(越上面優(yōu)先級(jí)越高) (括號(hào)可以改變優(yōu)先級(jí))- 負(fù)號(hào)% 百分號(hào) 求冪*和/ 乘和除+和- 加和減& 文本連接符=,=,比較第11頁(yè)，共46頁(yè)。公式舉例100-10 結(jié)果:100-10=100-10 結(jié)果:90=100-&10 結(jié)果: 100-10=100-&10&= & 100-10 結(jié)果：100-10=90216(1/3) 結(jié)果：6=10010 結(jié)果：TRUE=OR(A1100,A2100) 結(jié)果：如果單元格A1或A2的值100，返回TRU

4、E 第12頁(yè)，共46頁(yè)。隱藏公式步驟1.選中要保護(hù)公式的單元格2.”格式”單元格”保護(hù)”3.”隱藏”,確定4.”工具”保護(hù)”保護(hù)工作表” 設(shè)置密碼第13頁(yè)，共46頁(yè)。Excel錯(cuò)誤值#DIV/0! 除數(shù)為0(或除數(shù)為空單元格)#NAME? 不能識(shí)別的名稱或公式#N/A 查找未匹配或不能使用的數(shù)據(jù)#NULL! 不允許交叉但交叉在一起的域#NUM! 與值有關(guān):應(yīng)該正的出現(xiàn)了負(fù)數(shù)#REF! 引用了無(wú)效單元格(已經(jīng)刪除了)#VALUE!公式包括錯(cuò)誤形式的變量對(duì)象第14頁(yè)，共46頁(yè)。其他錯(cuò)誤循環(huán)引用: 例如:在A1中使用公式 =A1+1會(huì)提示出錯(cuò):第15頁(yè)，共46頁(yè)。引用單元格的引用（F4快速切換）相對(duì)

5、：A1 絕對(duì)：$A$1 行絕對(duì)：A$1 列絕對(duì)：$A1引用其他工作表=sheet2!A1引用其他工作簿打開(kāi)的時(shí)候：=book1.xlssheet1!A1 關(guān)閉的時(shí)候：=C:Documents and Settingsusers桌面 book1.xlssheet1!A1第16頁(yè)，共46頁(yè)。名稱的使用步驟:1.選中要?jiǎng)?chuàng)建名稱的單元格或范圍2.”插入”名稱”定義”3.輸入名稱4.只需輸入名稱,就能使用單元格或某一范圍第17頁(yè)，共46頁(yè)。第三部分 函數(shù)函數(shù)的概念(與公式的區(qū)別)函數(shù)的分類常用函數(shù)的介紹函數(shù)的綜合使用第18頁(yè)，共46頁(yè)。函數(shù)的功能(與公式的區(qū)別)簡(jiǎn)化公式 =(A1+A2+A3+A4+A5

6、)/5 =AVERAGE(A1:A5)實(shí)現(xiàn)公式無(wú)法實(shí)現(xiàn)的計(jì)算 =MAX(A1:A5)提高編輯速度 =PROPER(A1)判斷功能第19頁(yè)，共46頁(yè)。函數(shù)的種類財(cái)務(wù)函數(shù) (PMT)日期及時(shí)間函數(shù)數(shù)學(xué)及三角函數(shù)統(tǒng)計(jì)函數(shù)查看和引用函數(shù)數(shù)據(jù)庫(kù)函數(shù)文本函數(shù)邏輯函數(shù)信息函數(shù)工程函數(shù)用戶自定義函數(shù)第20頁(yè)，共46頁(yè)。文本函數(shù)判斷兩個(gè)字符串是否相等EXACT連接多個(gè)文本或單元格&把值文本化TEXT 把貨幣變?yōu)槲谋?DOLLAR重復(fù)REPT刪除多余空格和非打印字符TRIM CLEAN計(jì)算字符串中的字符數(shù)LEN改變大小寫(xiě)UPPERLOWERPROPER第21頁(yè)，共46頁(yè)。文本函數(shù)從字符串中提取字符 LEFTRIG

7、HTMID替換文本SUBSTITUTEREPLACE在字符串中查找和搜索FIND/SEARCH第22頁(yè)，共46頁(yè)。文本函數(shù)舉例=rept(A1,3) 結(jié)果:將A1單元格中的內(nèi)容重復(fù)3次=trim(“ Hello World “) 結(jié)果:Hello World=UPPER(LEFT (A1)&RIGHT(LOWER(A1),LEN(A1)-1) 結(jié)果:首字母大寫(xiě),其余小寫(xiě)=SUBSTITUTE(“YEAR 2004”,”2004”,”2005”) 結(jié)果:YEAR 2005=REPLACE(“Apple Chen”,6,”_”) 結(jié)果:Apple_Chen=FIND(“m”,”Mm”,1) 結(jié)果:

8、2=SEARCH (“m”,”Mm”,1) 結(jié)果:1第23頁(yè)，共46頁(yè)。復(fù)合文本函數(shù)計(jì)算A1中出現(xiàn)B的次數(shù)（無(wú)論大小寫(xiě)） =LEN(A1)-LEN(SUBSTITUTE(A1,”B”,”),”b”,”)計(jì)算A1中出現(xiàn)B1中內(nèi)容的頻率(大小寫(xiě)) =(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),UPPER(B1),”)/LEN(B1)提取A1中第一個(gè)詞 =LEFT(A1,FIND(“”,A1)-1)提取A1中除第一個(gè)以外的詞 =RIGHT(A1,LEN(A1)-FIND(“”,A1,1)提取A1中最后一個(gè)詞 =RIGHT(A1,LEN(A1)-FIND(“*”,SBUSTIT

9、UTE(A1,” ”,”*”,LEN(A1)-LEN(SUBSTITUTE(A1,” “,”) =IF(ISERR(FIND(“ ”,A1, RIGHT(A1,LEN(A1)-FIND(“*”,SBUSTITUTE(A1,” ”,”*”,LEN(A1)-LEN(SUBSTITUTE(A1,” “,”)第24頁(yè)，共46頁(yè)。復(fù)合文本函數(shù)從身份證中提取生日 =IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4) 從身份證中提取性別 =IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1),2)=1,男,女) 第25頁(yè)，共46頁(yè)。加載宏錄制“

10、工具”宏”錄制新宏”“工具”宏”宏”執(zhí)行宏”第26頁(yè)，共46頁(yè)。計(jì)數(shù)求和函數(shù)基本函數(shù)函數(shù)舉例高級(jí)計(jì)數(shù)函數(shù)第27頁(yè)，共46頁(yè)?；居?jì)數(shù)求和函數(shù)CountCountaCountblankSumSbutotalSumsqsumproduct第28頁(yè)，共46頁(yè)?；居?jì)數(shù)求和函數(shù)舉例第29頁(yè)，共46頁(yè)。高級(jí)統(tǒng)計(jì)函數(shù)及舉例第30頁(yè)，共46頁(yè)。查找函數(shù)CHOOSEHLOOKUPINDEXLOOKUPMATCHOFFSETVLOOKUP第31頁(yè)，共46頁(yè)。綜合使用MATCH,INDEXMATCH(lookup_value,lookup_array,match_type)INDEX(array,row_num,

11、column_num)INDEX(array,MATCH(lookup_value, lookup_array,match_type)第32頁(yè)，共46頁(yè)。第四部分 財(cái)務(wù)公式基本財(cái)務(wù)公式簡(jiǎn)單貼現(xiàn)復(fù)雜貼現(xiàn)利率轉(zhuǎn)換自定義利率轉(zhuǎn)換本金與利息折舊問(wèn)題第33頁(yè)，共46頁(yè)。Excel基本財(cái)務(wù)函數(shù)現(xiàn)值PV(rate,nper,pmt,fv,type)期值FV(rate,nper,pmt,pv,type)支付PMT(rate,nper,pmt,pv,type)利率RATE(nper,pmt,pv,fv,type,guess)周期NPER(rate,pmt,pv,fv,type)第34頁(yè)，共46頁(yè)。基本財(cái)務(wù)公式的

12、舉例第35頁(yè)，共46頁(yè)。貼現(xiàn)問(wèn)題概念: 可以把貼現(xiàn)看出是反向的累加.他不是把一個(gè)現(xiàn)值累計(jì)成一個(gè)期值,而是要把一個(gè)期值確定出當(dāng)前價(jià)值. 和累計(jì)一樣,可能涉及到3個(gè)貨幣價(jià)值參數(shù):PV,FV,PMT.如果只是涉及到2個(gè)參數(shù),稱為簡(jiǎn)單的貼現(xiàn),如果涉及到3個(gè),我們稱為復(fù)雜的貼現(xiàn)問(wèn)題.第36頁(yè)，共46頁(yè)。 簡(jiǎn)單貼現(xiàn)實(shí)例第37頁(yè)，共46頁(yè)。復(fù)雜貼現(xiàn)實(shí)例第38頁(yè)，共46頁(yè)。利率轉(zhuǎn)換名義利率年度實(shí)際利率階段實(shí)際利率例:月息為1%,年息是多少? =(1+0.01)12-1第39頁(yè)，共46頁(yè)。使用財(cái)務(wù)函數(shù)加載利率轉(zhuǎn)換使用Excel的插入函數(shù)對(duì)話框,將光盤(pán)中的inserestconversion.xla加載,就能使用其中的自定義利率轉(zhuǎn)換函數(shù)了.Excel自帶利率轉(zhuǎn)換共有10種不同的方法,使用復(fù)雜煩瑣,自編函數(shù)可以輕松實(shí)現(xiàn)自帶函數(shù)的使用第40頁(yè)，共46頁(yè)。本金和利息問(wèn)題需要支付利息部分IPMT(rate,per,nper,pv,fv,type)需要支付本金部分PPMT(rate,per,nper,pv,fv,type)連續(xù)階段中: CUMIPMT(rate,nper,pv,start_period,end_period,type) CUMPRINC(rate,nper,pv,start_period,end_pe