Excel實(shí)用函數(shù)培訓(xùn)

Office系列培訓(xùn)之

——Excel2007/20212021年3月27日V1認(rèn)識(shí)Excel數(shù)據(jù)處置公式與函數(shù)圖表1認(rèn)識(shí)ExcelExcel知識(shí)構(gòu)造Excel用戶的五個(gè)層次Excel2007/2021界面2Excel數(shù)據(jù)處置與操作Excel函數(shù)與公式Excel圖表與圖形ExcelVBA與宏Excel數(shù)據(jù)分析認(rèn)識(shí)ExcelExcel知識(shí)構(gòu)造3認(rèn)識(shí)ExcelExcel知識(shí)構(gòu)造Excel用戶的五個(gè)層次Excel2007/2021界面4剛剛開場接觸新手根本掌握常用功能初級用戶…+數(shù)組公式+VBA簡單編程高級用戶熟練運(yùn)用常用功能+數(shù)據(jù)透視表+公式中級用戶認(rèn)識(shí)ExcelExcel用戶的五個(gè)層次Excel技藝+專業(yè)知識(shí)+行業(yè)閱歷專家5認(rèn)識(shí)ExcelExcel知識(shí)構(gòu)造Excel用戶的五個(gè)層次Excel2007/2021界面6認(rèn)識(shí)ExcelExcel2007/2021界面7認(rèn)識(shí)Excel數(shù)據(jù)處置公式與函數(shù)圖表8數(shù)據(jù)處置自動(dòng)填充定義稱號(hào)數(shù)據(jù)有效性自定義格式條件格式合并計(jì)算分類匯總數(shù)據(jù)透視表9數(shù)據(jù)處置自動(dòng)填充等差填充日期填充文字填充特定填充10數(shù)據(jù)處置自動(dòng)填充定義稱號(hào)數(shù)據(jù)有效性自定義格式條件格式合并計(jì)算分類匯總數(shù)據(jù)透視表11數(shù)據(jù)處置定義稱號(hào)①插入稱號(hào)公式→稱號(hào)管理器→新建→輸入?yún)?shù)②運(yùn)用稱號(hào)框命名選定單元格→點(diǎn)擊稱號(hào)框→定義稱號(hào)12數(shù)據(jù)處置定義稱號(hào)命名規(guī)那么全局定義與部分定義：1、默許情況下，為全局定義2、部分定義命名方法：任務(wù)表稱號(hào)+半角感慨號(hào)+稱號(hào)命名規(guī)那么：1、可用數(shù)字與字母組合，但不能以數(shù)字開頭；2、不能以R、C、r、c作為稱號(hào)，如R、C在R1C1援用款式中表示行、列；3、稱號(hào)中不能包含空格，可用下劃線或點(diǎn)號(hào)替代；4、不能運(yùn)用除了下?lián)Q線、點(diǎn)號(hào)和反斜杠〔\〕不測的其他符號(hào)。允許運(yùn)用問號(hào)，但不能作為稱號(hào)的開頭；5、稱號(hào)字符不能超越255個(gè)；6、字母不區(qū)分大小寫。13數(shù)據(jù)處置自動(dòng)填充定義稱號(hào)數(shù)據(jù)有效性自定義格式條件格式合并計(jì)算分類匯總數(shù)據(jù)透視表14數(shù)據(jù)處置數(shù)據(jù)有效性15數(shù)據(jù)處置數(shù)據(jù)有效性16數(shù)據(jù)處置數(shù)據(jù)有效性停頓不能輸入不符合條件的數(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數(shù)據(jù)處置自動(dòng)填充定義稱號(hào)數(shù)據(jù)有效性自定義格式條件格式合并計(jì)算分類匯總數(shù)據(jù)透視表18數(shù)據(jù)處置自定義格式可為4種類型的數(shù)值指定不同的格式：正數(shù)、負(fù)數(shù)、零值和文本?！按笥跅l件值〞格式；“小于條件值〞格式；“等于條件值〞格式；文本格式注：沒有特別指定條件值的時(shí)候，默許條件值為019數(shù)據(jù)處置自定義格式#,##0.00；[紅色]-#,##0.00；[綠色]G/通用格式；““〞@“〞〞顯示為原始數(shù)值說明1,023.401023.4正數(shù)，顯示為帶千分號(hào)、兩位小數(shù)-1,023.00-1023負(fù)數(shù)，顯示為帶千分號(hào)、兩位小數(shù)、紅色00零值，顯示為綠色“等于”等于文本，顯示為文本兩側(cè)加雙引號(hào)20數(shù)據(jù)處置自定義格式實(shí)戰(zhàn)練習(xí)：編制設(shè)備編號(hào)，缺乏10位，編號(hào)前補(bǔ)0，并在10位編號(hào)前一致加“No：〞。21數(shù)據(jù)處置自動(dòng)填充定義稱號(hào)數(shù)據(jù)有效性自定義格式條件格式合并計(jì)算分類匯總數(shù)據(jù)透視表22數(shù)據(jù)處置條件格式23數(shù)據(jù)處置自動(dòng)填充定義稱號(hào)數(shù)據(jù)有效性自定義格式條件格式合并計(jì)算分類匯總數(shù)據(jù)透視表24數(shù)據(jù)處置合并計(jì)算例：統(tǒng)計(jì)報(bào)銷的金額/人次25數(shù)據(jù)處置自動(dòng)填充定義稱號(hào)數(shù)據(jù)有效性自定義格式條件格式合并計(jì)算分類匯總數(shù)據(jù)透視表26數(shù)據(jù)處置分類匯總可手動(dòng)創(chuàng)建分級顯示和自動(dòng)生成分級顯示。留意：1、一個(gè)任務(wù)表內(nèi)在同行方向上或列方向上只能創(chuàng)建一個(gè)分級顯示；2、在同一個(gè)分級顯示中，最多允許有8個(gè)層次關(guān)系。27數(shù)據(jù)處置自動(dòng)填充定義稱號(hào)數(shù)據(jù)有效性自定義格式條件格式合并計(jì)算分類匯總數(shù)據(jù)透視表28數(shù)據(jù)處置數(shù)據(jù)透視表初步的數(shù)據(jù)統(tǒng)計(jì)與分析29認(rèn)識(shí)Excel數(shù)據(jù)處置公式與函數(shù)圖表30公式與函數(shù)什么是公式？什么是函數(shù)？相對援用與絕對援用解讀函數(shù)的參數(shù)公式審核工具常見錯(cuò)誤類型文本處置函數(shù)數(shù)字計(jì)算函數(shù)日期與時(shí)間函數(shù)常用函數(shù)與簡單數(shù)組公式31公式與函數(shù)什么是公式？什么是函數(shù)？公式是以“=〞為引導(dǎo)，經(jīng)過運(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ù)的公式32公式與函數(shù)什么是公式？什么是函數(shù)？相對援用與絕對援用解讀函數(shù)的參數(shù)公式審核工具常見錯(cuò)誤類型文本處置函數(shù)數(shù)字計(jì)算函數(shù)日期與時(shí)間函數(shù)常用函數(shù)與簡單數(shù)組公式33公式與函數(shù)相對援用與絕對援用公式所在單元格與公式所援用單元格的位置關(guān)系稱為單元格援用的相對性。A1樣式R1C1樣式特征$A$1R1C1絕對引用，向右向下復(fù)制公式不改變引用關(guān)系A(chǔ)$1R1C[*]行絕對列相對混合引用，向下復(fù)制公式不改變引用關(guān)系$A1R[*]C1行相對列絕對混合引用，向右復(fù)制公式不改變引用關(guān)系A(chǔ)1R[*]C[*]相對引用，向右向下復(fù)制公式均改變引用關(guān)系34公式與函數(shù)相對援用與絕對援用①援用其他任務(wù)表的數(shù)據(jù)=任務(wù)表稱號(hào)！目的單元格例：=sheet2!A1②援用已翻開的任務(wù)簿的數(shù)據(jù)=[任務(wù)簿稱號(hào)]任務(wù)表稱號(hào)！目的單元格例：=[Book2.xls]sheet1!$A$1③援用未翻開的任務(wù)簿的數(shù)據(jù)=‘文件途徑[任務(wù)簿稱號(hào)]任務(wù)表稱號(hào)’！目的單元格例：=‘D:\Excel案例[Book2.xls]sheet1’!$A$1練習(xí)：將多張任務(wù)表的數(shù)據(jù)匯總到一張任務(wù)表35公式與函數(shù)什么是公式？什么是函數(shù)？相對援用與絕對援用解讀函數(shù)的參數(shù)公式審核工具常見錯(cuò)誤類型文本處置函數(shù)數(shù)字計(jì)算函數(shù)日期與時(shí)間函數(shù)常用函數(shù)與簡單數(shù)組公式36公式與函數(shù)解讀函數(shù)的參數(shù)=IF(A1>0,SUM(B1:G1),〞〞)函數(shù)的構(gòu)造嵌套函數(shù)函數(shù)稱號(hào)以逗號(hào)分隔的3個(gè)參數(shù)思索：哪些函數(shù)不帶參數(shù)？？37公式與函數(shù)什么是公式？什么是函數(shù)？相對援用與絕對援用解讀函數(shù)的參數(shù)公式審核工具常見錯(cuò)誤類型文本處置函數(shù)數(shù)字計(jì)算函數(shù)日期與時(shí)間函數(shù)常用函數(shù)與簡單數(shù)組公式38公式與函數(shù)公式審核工具 運(yùn)用公式審核工具分布求值 以=IF(A1>0,SUM(B1:G1),〞〞)為例，執(zhí)行“公式求值1239公式與函數(shù)公式審核工具34540公式與函數(shù)什么是公式？什么是函數(shù)？相對援用與絕對援用解讀函數(shù)的參數(shù)公式審核工具常見錯(cuò)誤類型文本處置函數(shù)數(shù)字計(jì)算函數(shù)日期與時(shí)間函數(shù)常用函數(shù)與簡單數(shù)組公式41公式與函數(shù)常見錯(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ù)值對函數(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ò)誤。42公式與函數(shù)什么是公式？什么是函數(shù)？相對援用與絕對援用解讀函數(shù)的參數(shù)公式審核工具常見錯(cuò)誤類型文本處置函數(shù)數(shù)字計(jì)算函數(shù)日期與時(shí)間函數(shù)常用函數(shù)與簡單數(shù)組公式43公式與函數(shù)文本處置函數(shù)銜接符&用途：銜接“&〞前后的字符、字段A列B列C列ABX500120-E=A1&B1ABX500120-EABX500120-EA列B列ABX500120=A1&"-E"ABX500120ABX500120-E44公式與函數(shù)文本處置函數(shù)大小寫字母轉(zhuǎn)換Lower、Upper、Proper 用途 例如一切大寫字母轉(zhuǎn)為小寫字母一切小寫字母轉(zhuǎn)為大寫字母字符串首字母轉(zhuǎn)為大寫字母=LOWER(“ILOVEEXCEL〞)=“iloveexcel〞=UPPER(“iloveexcel〞)=“ILOVEEXCEL〞=UPPER(“I愛excel〞)=“I愛Excel〞45公式與函數(shù)文本處置函數(shù)Char與CodeChar(65)=AChar(66)=BChar(67)=CChar(97)=aChar(98)=bChar(99)=cCode(“A〞)=65思索：如何快速填充A-Z序號(hào)46公式與函數(shù)文本處置函數(shù)提取字符函數(shù)Left、Right、Mid身份證號(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ù)？留意：MID函數(shù)有3個(gè)參數(shù)，而LEFT、RIGHT只需2個(gè)參數(shù)47公式與函數(shù)什么是公式？什么是函數(shù)？相對援用與絕對援用解讀函數(shù)的參數(shù)公式審核工具常見錯(cuò)誤類型文本處置函數(shù)數(shù)字計(jì)算函數(shù)日期與時(shí)間函數(shù)常用函數(shù)與簡單數(shù)組公式48公式與函數(shù)數(shù)字計(jì)算函數(shù)計(jì)算余數(shù)ModMod(3,2)=1Mod(15,4)=3Mod(21,7)=0……運(yùn)用：身份證號(hào)判別性別？如何判別數(shù)字的奇偶性？思索：除2的余數(shù)有多少個(gè)？除3呢？49公式與函數(shù)數(shù)字計(jì)算函數(shù)四舍五入Round數(shù)值公式顯示結(jié)果123.456=round(A2,0)123123.456=round(A3,2)123.461234.56=round(A4,-2)1200區(qū)別50公式與函數(shù)數(shù)字計(jì)算函數(shù)向上取整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)130051公式與函數(shù)什么是公式？什么是函數(shù)？相對援用與絕對援用解讀函數(shù)的參數(shù)公式審核工具常見錯(cuò)誤類型文本處置函數(shù)數(shù)字計(jì)算函數(shù)日期與時(shí)間函數(shù)常用函數(shù)與簡單數(shù)組公式52公式與函數(shù)日期與時(shí)間函數(shù)四個(gè)根底日期函數(shù)日期年月日

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

=DATE(A1,B1,C1)20096162009-6-16特殊函數(shù)：Today()，讀取計(jì)算機(jī)設(shè)置的今日日期，無參數(shù)53公式與函數(shù)日期與時(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信息部

54公式與函數(shù)日期與時(shí)間函數(shù)Datedif函數(shù)用途：計(jì)算兩個(gè)日期之間的天數(shù)、月數(shù)或年數(shù)注：此函數(shù)在Excel協(xié)助文件中沒有。構(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ù)差55公式與函數(shù)日期與時(shí)間函數(shù)Datedif函數(shù)開始日期結(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")36456公式與函數(shù)什么是公式？什么是函數(shù)？相對援用與絕對援用解讀函數(shù)的參數(shù)公式審核工具常見錯(cuò)誤類型文本處置函數(shù)數(shù)字計(jì)算函數(shù)日期與時(shí)間函數(shù)常用函數(shù)與簡單數(shù)組公式57公式與函數(shù)常用函數(shù)與簡單數(shù)組公式If函數(shù)用途：按指定的條件計(jì)算滿足條件選項(xiàng)語法構(gòu)造：=If〔斷定條件，假設(shè)滿足條件，否那么〕例如1：對現(xiàn)庫存小于平安庫存的備注缺件隱患。A列B列C列D列=If（C列<B列,"缺件隱患","正常")件號(hào)安全庫存現(xiàn)庫存?zhèn)渥BX46001010080缺件隱患AFE450030100160正常ALA5001404060正常EUH10025012082缺件隱患58公式與函數(shù)常用函數(shù)與簡單數(shù)組公式If函數(shù)例如2：對月出勤超越22天的員工做“全勤〞備注,低于22天的顯示實(shí)踐出勤天數(shù)。A列B列C列D列=If(C列>=22,"全勤",C列)序號(hào)姓名出勤備注1張三20202李四23全勤3王五21214賈六22全勤=If〔C1>=22,"全勤",C1〕59公式與函數(shù)常用函數(shù)與簡單數(shù)組公式Lookup函數(shù)用途：按列/行的方式查詢目的值。構(gòu)造：Vlookup、Hlookup函數(shù)=Vlookup〔需查找值，范圍，相對列，0〕Lookup函數(shù)=Lookup(需查找值，對應(yīng)值區(qū)域，目的值區(qū)域〕LOOKUP函數(shù)要求分級區(qū)間是升序陳列。60公式與函數(shù)常用函數(shù)與簡單數(shù)組公式條件判別〔Sumif、Cou