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

會(huì)計(jì)學(xué)1Excel實(shí)戰(zhàn)技巧與提高序言Excel用戶水平的5個(gè)層次剛剛開始接觸基本掌握常用功能熟練使用常用功能+數(shù)據(jù)透視表+公式…+數(shù)組公式+VBA簡單編程Excel技能+專業(yè)知識(shí)+行業(yè)經(jīng)驗(yàn)新手

初級(jí)用戶

中級(jí)用戶

高級(jí)用戶

專家第1頁/共78頁知識(shí)結(jié)構(gòu)Excel數(shù)據(jù)處理與操作Excel函數(shù)與公式Excel圖表與圖形ExcelVBA與宏Excel數(shù)據(jù)分析第2頁/共78頁提綱一、認(rèn)識(shí)Excel

二、數(shù)據(jù)處理三、函數(shù)與公式四、高級(jí)圖表第3頁/共78頁打開文件專業(yè)修復(fù)的第三方軟件：RecoveryforExcel第4頁/共78頁認(rèn)識(shí)界面標(biāo)題欄菜單欄工具欄行號(hào)列號(hào)標(biāo)簽欄狀態(tài)欄??第5頁/共78頁自定義工具欄1、“視圖”----“工具欄”----“自定義”

2、“工具”----“自定義”？第6頁/共78頁認(rèn)識(shí)選項(xiàng)第7頁/共78頁認(rèn)識(shí)選項(xiàng)（續(xù)）第8頁/共78頁認(rèn)識(shí)選項(xiàng)（續(xù)）第9頁/共78頁認(rèn)識(shí)選項(xiàng)（續(xù)）第10頁/共78頁提綱一、認(rèn)識(shí)Excel二、數(shù)據(jù)處理

三、函數(shù)與公式四、高級(jí)圖表第11頁/共78頁數(shù)據(jù)處理1、自動(dòng)填充2、定義名稱3、數(shù)據(jù)有效性4、自定義格式5、條件格式6、分類匯總7、合并計(jì)算8、數(shù)據(jù)透視表第12頁/共78頁1、自動(dòng)填充等差填充日期填充文字填充特定填充為什么無法填充？第13頁/共78頁2、定義名稱1、插入名稱?！安迦搿?“名稱”-“定義”2、使用名稱框命名3、指定名稱?！安迦搿?“名稱”-“指定”第14頁/共78頁2、定義名稱（續(xù)）全局定義與局部定義：1、默認(rèn)情況下，為全局定義2、局部定義命名方法：工作表名稱+半角感嘆號(hào)+名稱命名規(guī)則：1、可用數(shù)字與字母組合，但不能以數(shù)字開頭；2、不能以R、C、r、c作為名稱，如R、C在R1C1引用樣式中表示行、列；3、名稱中不能包含空格，可用下劃線或點(diǎn)號(hào)代替；4、不能使用除了下?lián)Q線、點(diǎn)號(hào)和反斜杠（\）意外的其他符號(hào)。允許使用問號(hào)，但不能作為名稱的開頭；5、名稱字符不能超過255個(gè)；6、字母不區(qū)分大小寫。第15頁/共78頁3、數(shù)據(jù)有效性第16頁/共78頁3、數(shù)據(jù)有效性（續(xù)）停止不能輸入不符合條件的數(shù)據(jù)，點(diǎn)擊“重試”可重新輸入，點(diǎn)擊“取消”則取消輸入。警告可以選擇是否繼續(xù)輸入數(shù)據(jù)，點(diǎn)擊“是”可以強(qiáng)行輸入不符合條件數(shù)據(jù)，點(diǎn)擊“否”可以重新輸入數(shù)據(jù)，點(diǎn)擊“取消”則取消輸入。信息可選擇是否輸入數(shù)據(jù)，點(diǎn)擊“確定”可以直接輸入數(shù)據(jù)，點(diǎn)擊“取消”可以取消輸入。第17頁/共78頁3、數(shù)據(jù)有效性（續(xù)）菜單“數(shù)據(jù)”—“有效性”第18頁/共78頁4、自定義格式

可為4種類型的數(shù)值指定不同的格式：正數(shù)、負(fù)數(shù)、零值和文本?！按笥跅l件值”格式；“小于條件值”格式；“等于條件值”格式；文本格式格式代碼組成結(jié)構(gòu)：注：沒有特別指定條件值的時(shí)候，默認(rèn)條件值為0第19頁/共78頁4、自定義格式（續(xù)）#,##0.00；[紅色]-#,##0.00；[綠色]G/通用格式；“*******”顯示為原始數(shù)值說明1,023.401023.4正數(shù)，顯示為帶千分號(hào)、兩位小數(shù)-1,023.00-1023負(fù)數(shù)，顯示為帶千分號(hào)、兩位小數(shù)、紅色00零值，顯示為綠色*******等于文本，顯示為*******[>100]#,##0.00；[<100][紅色]#,##0.00；[綠色]G/通用格式；"“"@"”"思考：第20頁/共78頁4、自定義格式（續(xù)）實(shí)戰(zhàn)練習(xí)：編制設(shè)備編號(hào)，不足10位，編號(hào)前補(bǔ)0，并在10位編號(hào)前統(tǒng)一加“No：”。第21頁/共78頁5、條件格式格式—條件格式第22頁/共78頁5、條件格式（續(xù)）實(shí)戰(zhàn)：防止與檢查重復(fù)輸入的數(shù)據(jù)1、空表格防止數(shù)據(jù)重復(fù)輸入2、重復(fù)出現(xiàn)數(shù)據(jù)做標(biāo)示3、第二次重復(fù)以后才標(biāo)示第23頁/共78頁6、合并計(jì)算例：統(tǒng)計(jì)報(bào)銷的金額/人次第24頁/共78頁7、分類匯總可手動(dòng)創(chuàng)建分級(jí)顯示和自動(dòng)生成分級(jí)顯示。注意：1、一個(gè)工作表內(nèi)在同行方向上或列方向上只能創(chuàng)建一個(gè)分級(jí)顯示；2、在同一個(gè)分級(jí)顯示中，最多允許有8個(gè)層次關(guān)系第25頁/共78頁8、數(shù)據(jù)透視表初步的數(shù)據(jù)統(tǒng)計(jì)與分析第26頁/共78頁提綱一、認(rèn)識(shí)Excel二、數(shù)據(jù)處理三、函數(shù)與公式

四、高級(jí)圖表第27頁/共78頁什么是函數(shù)？什么是公式？

公式是以“=”為引導(dǎo)，通過運(yùn)算符按照一定的順序組合進(jìn)行數(shù)據(jù)運(yùn)算處理的等式。

函數(shù)是按照特定的算法執(zhí)行計(jì)算的產(chǎn)生一個(gè)或一組結(jié)果的預(yù)定義的特殊公式。序號(hào)公式說明1=15*3+20*2包含常量運(yùn)算的公式2=A1*3+A2*2包含單元格引用的公式3=單價(jià)*數(shù)量包含名稱的公式4=SUM(A1*3,A2*2)包含函數(shù)的公式第28頁/共78頁解讀函數(shù)的參數(shù)=IF(A1>0,SUM(B1:G1),””)函數(shù)的結(jié)構(gòu)嵌套函數(shù)函數(shù)名稱以逗號(hào)分隔的3個(gè)參數(shù)思考：哪些函數(shù)不帶參數(shù)？？第29頁/共78頁查看公式計(jì)算結(jié)果使用公式審核工具分布求值

以=IF(A1>0,SUM(B1:G1),””)為例，執(zhí)行“公式求值”12第30頁/共78頁查看公式計(jì)算結(jié)果（續(xù)）345第31頁/共78頁錯(cuò)誤類型常見的錯(cuò)誤值及含義錯(cuò)誤值類型含義#####當(dāng)列寬不夠顯示數(shù)字，或者使用了負(fù)的日期或負(fù)的時(shí)間時(shí)，出現(xiàn)錯(cuò)誤#VALUE!當(dāng)使用的參數(shù)或操作數(shù)類型錯(cuò)誤時(shí)，出現(xiàn)錯(cuò)誤#DIV/0!當(dāng)數(shù)字被零（0）除時(shí)，出現(xiàn)錯(cuò)誤#NAME?當(dāng)Excel未識(shí)別公式中的文本時(shí)，如未加載宏或定義名稱，出現(xiàn)錯(cuò)誤#N/A當(dāng)數(shù)值對(duì)函數(shù)或公式不可用時(shí)，出現(xiàn)錯(cuò)誤#REF!當(dāng)單元格引用無效時(shí)，出現(xiàn)錯(cuò)誤#NUM!公式或函數(shù)中使用無效數(shù)字值時(shí)，出現(xiàn)錯(cuò)誤#NULL!當(dāng)用空格表示兩個(gè)引用單元格之間的相交運(yùn)算符，但指定并不相交的兩個(gè)區(qū)域的交點(diǎn)時(shí)，出現(xiàn)錯(cuò)誤。第32頁/共78頁函數(shù)與公式1、文本/數(shù)字/日期/財(cái)務(wù)類函數(shù)講解2、實(shí)戰(zhàn)案例綜合應(yīng)用第33頁/共78頁文本處理函數(shù)用途：連接“&”前后的字符、字段A列B列C列ABX500120-E=A1&B1ABX500120-EABX500120-EA列B列ABX500120=A1&"-E"ABX500120ABX500120-E思考：如何把零件號(hào)后面的-E去掉？？1、連接符&第34頁/共78頁文本處理函數(shù)2、大小寫字母轉(zhuǎn)換Lower、Upper、Proper所有大寫字母轉(zhuǎn)為小寫字母所有小寫字母轉(zhuǎn)為大寫字母字符串首字母轉(zhuǎn)為大寫字母=LOWER(“ILOVEEXCEL”)=“iloveexcel”=UPPER(“iloveexcel”)=“ILOVEEXCEL”=UPPER(“I愛excel”)=“I愛Excel”第35頁/共78頁文本處理函數(shù)3、全角半角函數(shù)Widechar、Asc半角字母轉(zhuǎn)為全角字母全角字母轉(zhuǎn)為半角字母=WIDECHAR(“Excel”)=“Ｅｘｃｅｌ”=ASC(“我愛Ｅｘｃｅｌ”)=“我愛Excel”第36頁/共78頁文本處理函數(shù)4、生成A-ZCharChar(65)=AChar(66)=BChar(67)=C……思考：如何快速填充A-Z序號(hào)Code(“A”)=65Char(97)=aChar(98)=bChar(99)=c第37頁/共78頁文本處理函數(shù)5、提取字符Left、Right、Mid注意：MID函數(shù)有3個(gè)參數(shù)，而LEFT、RIGHT只有2個(gè)參數(shù)身份證號(hào)地區(qū)出生日期性別

=left(a1,6)=mid(a1,7,6)=right(a1,1)3201027810014923201027810012員工代碼社保號(hào)姓名

=left(a1,10)=mid(a1,11,3)0002337084李虎0002337084李虎0000656246丁世明0000656246丁世明為什么是3而不是2？為什么不用Right函數(shù)？第38頁/共78頁數(shù)字計(jì)算函數(shù)1、計(jì)算余數(shù)Mod(3,2)=1Mod(15,4)=3Mod(21,7)=0……應(yīng)用：身份證號(hào)判斷性別？如何判斷數(shù)字的奇偶性？思考：除2的余數(shù)有多少個(gè)？除3呢？第39頁/共78頁數(shù)字計(jì)算函數(shù)2、取整函數(shù)IntTrunc=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第40頁/共78頁數(shù)字計(jì)算函數(shù)3、四舍五入Round數(shù)值公式顯示結(jié)果123.456=round(A2,0)123123.456=round(A3,2)123.461234.56=round(A4,-2)1200

區(qū)別第41頁/共78頁數(shù)字計(jì)算函數(shù)3、向上取整Roundup、向下取整Rounddown數(shù)值RoundupRounddown公式顯示結(jié)果公式顯示結(jié)果123.456=rounddown(A3,0)123=roundup(A3,0)124123.456=rounddown(A3,2)123.45=roundup(A3,2)123.461234.56=rounddown(A3,-2)1200=roundup(A3,-2)1300第42頁/共78頁日期與時(shí)間函數(shù)1、4個(gè)基礎(chǔ)日期函數(shù)特殊函數(shù)：Today()，讀取計(jì)算機(jī)設(shè)置的今日日期，無參數(shù)日期年月日

=Year(a1)=Month(a1)=Day(a1)2009-6-162009616年月日日期

=DATE(A1,B1,C1)20096162009-6-16第43頁/共78頁日期與時(shí)間函數(shù)實(shí)戰(zhàn)練習(xí)：

快速計(jì)算退休日期（假設(shè)：男60退休、女55退休）姓名性別出生日期部門退休日期艾笑貽女1955-6-20綜合部

安文博男1961-7-31綜合部

白露女1979-5-13綜合部

謝子堅(jiān)男1973-10-19財(cái)務(wù)部

卜娟芳女1978-12-29財(cái)務(wù)部

蔡星媛女1959-7-10財(cái)務(wù)部

倉晟義男1978-11-20信息部

曹昱廷男1965-2-14信息部

第44頁/共78頁日期與時(shí)間函數(shù)2、Datedif函數(shù)用途：計(jì)算兩個(gè)日期之間的天數(shù)、月數(shù)或年數(shù)。注：此函數(shù)在Excel幫助文件中沒有。結(jié)構(gòu)：Datedif(start_date,end_date,unit)unit代碼函數(shù)返回值"y"時(shí)間段中的整年數(shù)"m"時(shí)間段中的整月數(shù)"d"時(shí)間段中的天數(shù)"md"忽略日期中的年和月，計(jì)算天數(shù)差"ym"忽略日期中的年和日，計(jì)算月數(shù)差"yd"忽略日期中的年，計(jì)算天數(shù)差第45頁/共78頁日期與時(shí)間函數(shù)2、Datedif函數(shù)(續(xù))開始日期結(jié)束日期公式顯示結(jié)果2000-6-152009-6-16=datedif(A2,B2,"y")92000-6-172009-6-16=datedif(A3,B3,"y")82008-6-152009-6-16=datedif(A4,B4,"m")122008-6-172009-6-16=datedif(A5,B5,"m")112008-6-152009-6-16=datedif(A6,B6,"d")3662008-6-172009-6-16=datedif(A7,B7,"d")3642008-3-152009-6-16=datedif(A8,B8,"md")12008-3-172009-6-16=datedif(A9,B9,"md")302008-3-152009-6-16=datedif(A10,B10,"ym")32008-6-172009-6-16=datedif(A11,B11,"ym")112008-6-152009-6-16=datedif(A12,B12,"yd")12008-6-172009-6-16=datedif(A13,B13,"yd")364第46頁/共78頁財(cái)務(wù)函數(shù)概念解釋：函數(shù)含義公式rate利率固定值nper周期

FV終值FV(rate,nper,pmt,[pv],[type])PV現(xiàn)值PV(rate,nper,pmt,[fv],[type])PMT期付金額PMT(rate,nper,pv,[fv],[type])type期初or期末期初為1，期末為0。如省略，則為0第47頁/共78頁財(cái)務(wù)函數(shù)案例1：分10個(gè)月付清年利率為8%的10000元貸款，計(jì)算月支付金額。

=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第48頁/共78頁財(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)在起，每月初存入2000元，年利率2.25%，按月計(jì)息（2.25%/12），計(jì)算兩年后帳戶金額。

=FV（rate，nper，pmt，[pv]，[type]）

=FV（2.25%/12，24，-2000，0，1）

=49141.34第49頁/共78頁財(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]，[type]）

=PV（8%/12，20*12，500）

=-59777.15不合適第50頁/共78頁函數(shù)與公式1、文本/數(shù)字/日期/財(cái)務(wù)類函數(shù)講解2、案例綜合應(yīng)用第51頁/共78頁1、相對(duì)單元格與絕對(duì)單元格

公式所在單元格與公式所引用單元格的位置關(guān)系稱為單元格引用的相對(duì)性。A1樣式R1C1樣式特征$A$1R1C1絕對(duì)引用，向右向下復(fù)制公式不改變引用關(guān)系A(chǔ)$1R1C[*]行絕對(duì)列相對(duì)混合引用，向下復(fù)制公式不改變引用關(guān)系$A1R[*]C1行相對(duì)列絕對(duì)混合引用，向右復(fù)制公式不改變引用關(guān)系A(chǔ)1R[*]C[*]相對(duì)引用，向右向下復(fù)制公式均改變引用關(guān)系練習(xí)：制作九九乘法表第52頁/共78頁2、工作表的引用1、引用其他工作表的數(shù)據(jù)

=工作表名稱！目標(biāo)單元格例：=sheet2!A12、引用已打開的工作簿的數(shù)據(jù)

=[工作簿名稱]工作表名稱！目標(biāo)單元格例：=[Book2.xls]sheet1!$A$13、引用未打開的工作簿的數(shù)據(jù)

=‘文件路徑[工作簿名稱]工作表名稱’！目標(biāo)單元格例：=‘D:\Excel案例[Book2.xls]sheet1’!$A$1練習(xí)：將多張工作表的數(shù)據(jù)匯總到一張工作表第53頁/共78頁3、IF函數(shù)用途：按指定的條件計(jì)算滿足條件選項(xiàng)A列B列C列D列=If（C列<B列,"缺件隱患","正常")件號(hào)安全庫存現(xiàn)庫存?zhèn)渥BX46001010080缺件隱患AFE450030100160正常ALA5001404060正常EUH10025012082缺件隱患第54頁/共78頁IF函數(shù)實(shí)例例2：對(duì)月出勤超過22天的員工做“全勤”備注,低于22天的顯示實(shí)際出勤天數(shù)A列B列C列D列=If(C列>=22,"全勤",C列)序號(hào)姓名出勤備注1張三20202李四23全勤3王五21214賈六22全勤=If（C1>=22,"全勤",C1）第55頁/共78頁4、LOOKUP函數(shù)1.VLOOKUP、HLOOKUP函數(shù)用途：按列/行的方式查詢目標(biāo)值。語法格式：=VLOOKUP（需查找值，范圍，相對(duì)列，假）2.LOOKUP函數(shù)用途：模糊及區(qū)間查詢語法格式：=LOOKUP(需查找值，對(duì)應(yīng)值區(qū)域，目標(biāo)值區(qū)域）！LOOKUP函數(shù)要求分級(jí)區(qū)間是升序排列。第56頁/共78頁5、SUMIF、COUNTIF函數(shù)按照一定的條件進(jìn)行統(tǒng)計(jì)計(jì)算。語法：=COUNTIF(條件區(qū)域，條件）

=SUMIF(條件區(qū)域，條件，值區(qū)域）第57頁/共78頁6、SUMPRODUCT函數(shù)返回相應(yīng)的數(shù)組或乘積之和。

語法：=SUMPRODUCT（(array1）*(array2）*……）第58頁/共78頁7、初級(jí)數(shù)組公式

數(shù)組公式是用于建立可以產(chǎn)生多個(gè)結(jié)果或?qū)梢源娣旁谛谢蛄兄械囊唤M參數(shù)進(jìn)行運(yùn)算的單個(gè)公式。

特點(diǎn)：可以執(zhí)行多重運(yùn)算，返回一組數(shù)據(jù)結(jié)果。輸入公式后，同時(shí)按【Ctrl+Shift+Enter】，公式就會(huì)成為數(shù)組公式。注：數(shù)組公式的外面會(huì)自動(dòng)加{}予以區(qū)分。