Excel實(shí)戰(zhàn)技巧與提高(12).ppt

1、OFFICE系列培訓(xùn)之-,Excel實(shí)戰(zhàn)技巧與提高,李 虎,2009.6,第2頁,序言,Excel用戶水平的5個層次,第3頁,知識結(jié)構(gòu),Excel數(shù)據(jù)處理與操作,Excel函數(shù)與公式,Excel圖表與圖形,Excel VBA與宏,Excel 數(shù)據(jù)分析,第4頁,提綱,一、認(rèn)識Excel 二、數(shù)據(jù)處理 三、函數(shù)與公式 四、高級圖表,第5頁,打開文件,專業(yè)修復(fù)的第三方軟件： Recovery for Excel,第6頁,認(rèn)識界面,標(biāo)題欄,菜單欄,工具欄,行號,列號,標(biāo)簽欄,狀態(tài)欄,?,?,第7頁,自定義工具欄,1、“視圖”-“工具欄”-“自定義” 2、“工具”-“自定義”,？,第8頁,認(rèn)識選項(xiàng),第9

2、頁,認(rèn)識選項(xiàng)（續(xù)）,第10頁,認(rèn)識選項(xiàng)（續(xù)）,第11頁,認(rèn)識選項(xiàng)（續(xù)）,第12頁,提綱,一、認(rèn)識Excel 二、數(shù)據(jù)處理 三、函數(shù)與公式 四、高級圖表,第13頁,數(shù)據(jù)處理,1、自動填充 2、定義名稱 3、數(shù)據(jù)有效性 4、自定義格式 5、條件格式 6、分類匯總 7、合并計(jì)算 8、數(shù)據(jù)透視表,第14頁,1、自動填充, 等差填充 日期填充 文字填充 特定填充,為什么無法填充？,第15頁,2、定義名稱,1、插入名稱。 “插入”-“名稱”-“定義”,2、使用名稱框命名,3、指定名稱。 “插入”-“名稱”-“指定”,第16頁,2、定義名稱（續(xù)）,全局定義 與 局部定義： 1、默認(rèn)情況下，為全局定義 2、局

3、部定義命名方法：工作表名稱+半角感嘆號+名稱 命名規(guī)則： 1、可用數(shù)字與字母組合，但不能以數(shù)字開頭； 2、不能以R、C、r、c作為名稱，如R、C在R1C1引用樣式中表示行、列； 3、名稱中不能包含空格，可用下劃線或點(diǎn)號代替； 4、不能使用除了下?lián)Q線、點(diǎn)號和反斜杠（）意外的其他符號。允許使用問號，但不能作為名稱的開頭； 5、名稱字符不能超過255個； 6、字母不區(qū)分大小寫。,第17頁,3、數(shù)據(jù)有效性,菜單“數(shù)據(jù)”“有效性”,第18頁,3、數(shù)據(jù)有效性（續(xù)）,第19頁,3、數(shù)據(jù)有效性（續(xù)）,停止,不能輸入不符合條件的數(shù)據(jù)，點(diǎn)擊“重試”可重新輸入，點(diǎn)擊“取消”則取消輸入。,警告,可以選擇是否繼續(xù)輸入數(shù)

4、據(jù)，點(diǎn)擊“是”可以強(qiáng)行輸入不符合條件數(shù)據(jù)，點(diǎn)擊“否”可以重新輸入數(shù)據(jù)，點(diǎn)擊“取消”則取消輸入。,信息,可選擇是否輸入數(shù)據(jù)，點(diǎn)擊“確定”可以直接輸入數(shù)據(jù)，點(diǎn)擊“取消”可以取消輸入。,第20頁,4、自定義格式,可為4種類型的數(shù)值指定不同的格式：正數(shù)、負(fù)數(shù)、零值和文本。,“大于條件值”格式；“小于條件值”格式；“等于條件值”格式；文本格式,格式代碼組成結(jié)構(gòu)：,注：沒有特別指定條件值的時(shí)候，默認(rèn)條件值為0,第21頁,4、自定義格式（續(xù)）,#,#0.00；紅色-#,#0.00；綠色G/通用格式；“ “ ”“ ” ”,100#,#0.00；100紅色#,#0.00；綠色G/通用格式；“ * ”,思考：,第

5、22頁,4、自定義格式（續(xù)）,實(shí)戰(zhàn)練習(xí)： 編制設(shè)備編號，不足10位，編號前補(bǔ)0，并在10位編號前統(tǒng)一加“No：”。,第23頁,5、條件格式,格式條件格式,第24頁,5、條件格式（續(xù)）,實(shí)戰(zhàn)：防止與檢查重復(fù)輸入的數(shù)據(jù),1、空表格防止數(shù)據(jù)重復(fù)輸入,2、重復(fù)出現(xiàn)數(shù)據(jù)做標(biāo)示,3、第二次重復(fù)以后才標(biāo)示,第25頁,6、合并計(jì)算,例：統(tǒng)計(jì)報(bào)銷的金額/人次,第26頁,7、分類匯總,可手動創(chuàng)建分級顯示和自動生成分級顯示。,注意： 1、一個工作表內(nèi)在同行方向上或列方向上只能創(chuàng)建一個分級顯示； 2、在同一個分級顯示中，最多允許有8個層次關(guān)系,第27頁,8、數(shù)據(jù)透視表,初步的數(shù)據(jù)統(tǒng)計(jì)與分析,第28頁,提綱,一、認(rèn)識E

6、xcel 二、數(shù)據(jù)處理 三、函數(shù)與公式 四、高級圖表,第29頁,什么是函數(shù)？什么是公式？,公式是以“=”為引導(dǎo)，通過運(yùn)算符按照一定的順序組合進(jìn)行數(shù)據(jù)運(yùn)算處理的等式。 函數(shù)是按照特定的算法執(zhí)行計(jì)算的產(chǎn)生一個或一組結(jié)果的預(yù)定義的特殊公式。,第30頁,解讀函數(shù)的參數(shù),=IF(A10,SUM(B1:G1),” ”),函數(shù)的結(jié)構(gòu),嵌套函數(shù),函數(shù)名稱,以逗號分隔的3個參數(shù),思考：哪些函數(shù)不帶參數(shù)？,第31頁,查看公式計(jì)算結(jié)果,使用公式審核工具分布求值 以=IF(A10,SUM(B1:G1),” ”)為例，執(zhí)行“公式求值”,1,2,第32頁,查看公式計(jì)算結(jié)果（續(xù)）,3,4,5,第33頁,錯誤類型,常見的錯誤

7、值及含義,第34頁,函數(shù)與公式,1、文本/數(shù)字/日期/財(cái)務(wù)類函數(shù)講解 2、實(shí)戰(zhàn)案例綜合應(yīng)用,第35頁,文本處理函數(shù),用途：連接“&”前后的字符、字段,思考：如何把零件號后面的-E去掉？,1、連接符 &,第36頁,文本處理函數(shù),2、大小寫字母轉(zhuǎn)換 Lower 、Upper、 Proper,所有大寫字母轉(zhuǎn)為小寫字母,所有小寫字母轉(zhuǎn)為大寫字母,字符串首字母轉(zhuǎn)為大寫字母,=LOWER (“I LOVE EXCEL”) =“i love excel”,=UPPER (“i love excel”) =“I LOVE EXCEL”,=UPPER (“I 愛 excel”) =“I 愛 Excel”,第37

8、頁,文本處理函數(shù),3、全角半角函數(shù) Widechar 、Asc,半角字母轉(zhuǎn)為全角字母,全角字母轉(zhuǎn)為半角字母,=WIDECHAR(“Excel”) =“”,=ASC (“我愛 ”) =“我愛Excel”,第38頁,文本處理函數(shù),4、生成A-Z Char Char(65)=A Char(66)=B Char(67)=C ,思考：如何快速填充A-Z序號,Code(“A”)=65,Char(97)=a Char(98)=b Char(99)=c,第39頁,文本處理函數(shù),5、提取字符Left 、 Right 、Mid,注意：MID函數(shù)有3個參數(shù)，而LEFT、RIGHT只有2個參數(shù),為什么是3而不是2？

9、為什么不用Right函數(shù)？,第40頁,數(shù)字計(jì)算函數(shù),1、計(jì)算余數(shù),Mod(3,2)=1 Mod(15,4)=3 Mod(21,7)=0 ,應(yīng)用：身份證號判斷性別？如何判斷數(shù)字的奇偶性？,思考：除2的余數(shù)有多少個？除3呢？,第41頁,數(shù)字計(jì)算函數(shù),2、取整函數(shù) Int Trunc,=Int(3.2) 返回不大于3.2的最大整數(shù)3 =Int(-3.2) 返回不大于-3.2的最大整數(shù)-4,=Trunc(3.2) 返回截去小數(shù)部分后的整數(shù)3 =Trunc(-3.2) 返回截去小數(shù)部分后的整數(shù)-3,第42頁,數(shù)字計(jì)算函數(shù),3、四舍五入Round,區(qū)別,第43頁,數(shù)字計(jì)算函數(shù),3、向上取整Roundup、

10、向下取整Rounddown,第44頁,日期與時(shí)間函數(shù),1、4個基礎(chǔ)日期函數(shù),特殊函數(shù)： Today()，讀取計(jì)算機(jī)設(shè)置的今日日期，無參數(shù),第45頁,日期與時(shí)間函數(shù),實(shí)戰(zhàn)練習(xí)： 快速計(jì)算退休日期（假設(shè)：男60退休、女55退休）,第46頁,日期與時(shí)間函數(shù),2、Datedif函數(shù) 用途：計(jì)算兩個日期之間的天數(shù)、月數(shù)或年數(shù)。,注：此函數(shù)在Excel幫助文件中沒有。結(jié)構(gòu)：Datedif(start_date,end_date,unit),第47頁,日期與時(shí)間函數(shù),2、Datedif函數(shù)(續(xù)),第48頁,財(cái)務(wù)函數(shù),概念解釋：,第49頁,財(cái)務(wù)函數(shù),案例1：分10個月付清年利率為8%的10000元貸款，計(jì)算月

11、支付金額。 =PTM（rate，nper，pv，fv，type） =PTM（8%/12，10，10000） = -1037.03,案例2：需以年利率5%存款15年，達(dá)到存款總額1,500,000，計(jì)算每月存款金額。 =PTM（rate，nper，pv，fv，type） =PTM（5%/12，15，0，1500000） = -5611.9,第50頁,財(cái)務(wù)函數(shù),案例3：某人將10000元投入一項(xiàng)事業(yè)，年回報(bào)率6%，計(jì)算3年后的累積金額。 =FV（rate，nper，pmt，pv， type） =PTM（6%，3，0，-10000） = 11910.16,案例4：兩年后需要大筆支出，計(jì)劃從現(xiàn)在起，每

12、月初存入2000元，年利率2.25%，按月計(jì)息（2.25%/12），計(jì)算兩年后帳戶金額。 =FV（rate，nper，pmt，pv， type） =FV（2.25%/12，24，-2000，0，1） = 49141.34,第51頁,財(cái)務(wù)函數(shù),案例5：某人擬在5年后獲得10000元，投資報(bào)酬率10%，計(jì)算現(xiàn)在應(yīng)該投入的金額。 =PV（rate，nper，pmt，fv， type） =PV（10%，5，0，10000） = -6209.21,案例6：某人余購買一項(xiàng)養(yǎng)老保險(xiǎn)，購買成本為60000元，可在20年內(nèi)每月回報(bào)500元，投資報(bào)酬率8%，計(jì)算是否合適。 =PV（rate，nper，pmt，fv

13、， type） =PV（8%/12，20*12，500） = -59777.15 不合適,第52頁,函數(shù)與公式,1、文本/數(shù)字/日期/財(cái)務(wù)類函數(shù)講解 2、案例綜合應(yīng)用,第53頁,1、相對單元格與絕對單元格,公式所在單元格與公式所引用單元格的位置關(guān)系稱為單元格引用的相對性。,練習(xí)：制作九九乘法表,第54頁,2、工作表的引用,1、引用其他工作表的數(shù)據(jù) =工作表名稱！目標(biāo)單元格 例：=sheet2!A1 2、引用已打開的工作簿的數(shù)據(jù) =工作簿名稱工作表名稱！目標(biāo)單元格 例： =Book2.xlssheet1!$A$1 3、引用未打開的工作簿的數(shù)據(jù) =文件路徑工作簿名稱工作表名稱！目標(biāo)單元格 例： =

14、D:Excel案例Book2.xlssheet1!$A$1,練習(xí)：將多張工作表的數(shù)據(jù)匯總到一張工作表,第55頁,3、IF函數(shù),用途：按指定的條件計(jì)算滿足條件選項(xiàng),第56頁,IF函數(shù)實(shí)例,例2：對月出勤超過22天的員工做“全勤”備注,低于22天的顯示實(shí)際出勤天數(shù),=If（C1=22, 全勤,C1）,第57頁,4、LOOKUP函數(shù),1. VLOOKUP、HLOOKUP函數(shù) 用途：按列/行的方式查詢目標(biāo)值。 語法格式：=VLOOKUP（需查找值，范圍，相對列，假） 2.LOOKUP函數(shù) 用途：模糊及區(qū)間查詢 語法格式：=LOOKUP(需查找值，對應(yīng)值區(qū)域，目標(biāo)值區(qū)域）,！LOOKUP函數(shù)要求分級區(qū)間

15、是升序排列。,第58頁,5、SUMIF、COUNTIF函數(shù),按照一定的條件進(jìn)行統(tǒng)計(jì)計(jì)算。 語法：=COUNTIF(條件區(qū)域，條件） =SUMIF(條件區(qū)域，條件，值區(qū)域）,第59頁,6、SUMPRODUCT函數(shù),返回相應(yīng)的數(shù)組或乘積之和。 語法：=SUMPRODUCT（(array1）* (array2） * ）,第60頁,7、初級數(shù)組公式,數(shù)組公式是用于建立可以產(chǎn)生多個結(jié)果或?qū)梢源娣旁谛谢蛄兄械囊唤M參數(shù)進(jìn)行運(yùn)算的單個公式。 特點(diǎn)：可以執(zhí)行多重運(yùn)算，返回一組數(shù)據(jù)結(jié)果。,輸入公式后，同時(shí)按【Ctrl+Shift+Enter】，公式就會成為數(shù)組公式。 注：數(shù)組公式的外面會自動加 予以區(qū)分。,第61頁,綜合應(yīng)用,案例分析,第62頁,提綱,一、認(rèn)識Excel 二、數(shù)據(jù)處理 三、函數(shù)與公式 四、高級圖表,第63頁,EXCEL圖表類型,面積圖 XY（散點(diǎn)）圖 條形圖 曲面圖 柱形圖 氣泡圖 折線圖 股價(jià)圖 餅圖 圓柱圖 圓環(huán)圖 圓錐圖 雷達(dá)圖 棱錐圖,共有73個子類型的圖表類型。,第64頁,EXCEL常用圖表類型,1、柱形圖,第65頁,EXCEL常用圖表類型,1-1、柱形圖的變形,第66頁,高級圖表,薪酬云梯圖,第67頁,EXCEL常用圖表類型,2、條形圖,第68頁,高