EXCEL培訓(xùn)-函數(shù)中級版

***2015.7.28EXCEL高效辦公-函數(shù)篇班規(guī)手機(jī)關(guān)機(jī)或靜音狀態(tài)培訓(xùn)過程中不得接聽電話、發(fā)短信服從講師安排，不得遲到函數(shù)版開篇主題分享、提問函數(shù)版開篇主題分享、提問開篇3、EXCEL用戶水平的5個層次4、EXCEL的優(yōu)勢1、案例-sum函數(shù)2、案例-發(fā)貨日報表5、成為EXCEL高手的捷徑6、EXCEL功能概覽導(dǎo)入開篇-案例1、案例-sum函數(shù)設(shè)置范例1、數(shù)組求和：{=SUM((G12:G21>100)*G12:G21)}2、數(shù)據(jù)類型轉(zhuǎn)換求和：=SUM(VALUE(H5))；=SUM(--(H5))；=SUM((H5)*1)；=SUM((H5)/1)3、多條件求和1：{=SUM(((G23:G32<100)+(G23:G32>110))*G23:G32)}=SUM(((G23:G32<100)+(G23:G32=110)+(G23:G32=120))*G23:G32)4、多條件求和2：{=SUM((H34:H43=“部門")*(I34:I43="男")*J34:J43)}5、多工作表求和時表名稱快捷輸入：=SUM(附表一:附表五!B3:B10)6、快速設(shè)置當(dāng)前表以外的所有相同工作表區(qū)域的總和公式：=SUM('*'!B3)7、用SUM函數(shù)計數(shù)：{=SUM((H94:H102="男")*(I94:I102="漢"))}8、求1累加到100之和：{=SUM(ROW(1:100))}9、多個工作表相同區(qū)域求前三名產(chǎn)量之和：{=SUM(LARGE(CHOOSE({1,2,3,4,5},附表一!B3:B10,附表二!B3:B10,附表三!B3:B10,附表四!B3:B10,附表五!B3:B10),ROW(1:3)))}開篇-案例1、案例-運輸金額計算嵌套函數(shù)IF(M3="車",N3,IF(AND(N3>0,N3<=0.75),1,IF(AND(N3>0.75,N3<1.25),1,IF(AND(N3>=1.25,N3<1.75),1.5,IF(AND(N3>=1.75,N3<2.25),2,IF(AND(N3>=2.25,N3<2.75),2.5,IF(AND(N3>=2.75,N3<3.25),3,)&IF(AND(N3>=3.25,N3<3.75),3.5,))))))&IF(AND(N3>=3.75,N3<4.25),4,IF(AND(N3>=4.25,N3<4.75),4.5,IF(AND(N3>=4.75,N3<5.25),5,IF(AND(N3>=5.25,N3<5.75),5.5,IF(AND(N3>=5.75,N3<6.25),6,IF(AND(N3>=6.25,N3<6.75),6.5,)&IF(AND(N3>=6.75,N3<7.25),7,))))))&IF(AND(N3>=7.25,N3<7.75),7.5,IF(AND(N3>=7.75,N3<8.25),8,IF(AND(N3>=8.25,N3<8.75),8.5,IF(AND(N3>=8.75,N3<9.25),9,IF(AND(N3>=9.25),N3,))))))*1開篇-用戶水平2、EXCEL用戶水平的5個層次1、剛開始接觸…………新手2、基本掌握常用功能………初級用戶3、熟練使用常用功能+數(shù)據(jù)透視表+函數(shù)公式+簡單的宏……中級用戶4、……+數(shù)組公式+VBA簡單編程……高級用戶5、EXCEL技能+專業(yè)知識+行業(yè)經(jīng)驗……專家（EXCEL專家也常常是行業(yè)專家）開篇-優(yōu)勢4、EXCEL的優(yōu)勢1、用途極為廣泛，集成了最優(yōu)秀的數(shù)據(jù)計算與分析功能，能助您在職場上取得成功，已經(jīng)成為一種生產(chǎn)工具!2、在同類電子表格處理軟件中處于絕對優(yōu)勢地位，具有良好的兼容性；3、博大精深，一般用戶只使用了5%的功能!很多用戶用不上的原因：一是根本不知道還有其他功能；二是知道有別的功能，但不知道怎么用；三是覺得暫時用不上。開篇-捷徑5、成為EXCEL高手的捷徑1、主動學(xué)習(xí)：寧愿加班加點，也不愿提高自己的工作技能；2、正確的學(xué)習(xí)方法：1）循序漸進(jìn)：從新手到專家各階段的學(xué)習(xí)特點2）善用資源，學(xué)以致用，帶著問題學(xué)習(xí)3）多閱讀，多實踐——組合創(chuàng)新用法4）參加培訓(xùn)、各種交流等不一定要精通VBA才是高手——要充分利用EXCEL內(nèi)置功能開篇-功能6、EXCEL功能概覽統(tǒng)計分析基礎(chǔ)技巧圖形展示表格管理數(shù)據(jù)整理合并轉(zhuǎn)換函數(shù)與宏分類匯總交叉分析方案工具使用控件應(yīng)用圖形種類數(shù)據(jù)鉆取在線演示趨勢分析EXCEL管理報告、文件POWERPOINT文本概念性圖表量化性圖表函數(shù)版開篇主題分享、提問主題函數(shù)篇基本概念函數(shù)通覽常用函數(shù)嵌套函數(shù)一、基本概念運算符相關(guān)公式與函數(shù)函數(shù)語法$相對絕對混合引用內(nèi)置函數(shù)嵌套函數(shù)擴(kuò)展函數(shù)公式與函數(shù)什么是函數(shù)？Excel函數(shù)即是預(yù)先定義，執(zhí)行計算、分析等處理數(shù)據(jù)任務(wù)的特殊公式。函數(shù)語法：由函數(shù)名+左括號+參數(shù)+右括號參數(shù)與參數(shù)之間用逗號表示，以常用的求和函數(shù)SUM為例，它的語法是“SUM(number1,number2,......)”。其中“SUM”稱為函數(shù)名稱，一個函數(shù)只有唯一的一個名稱，它決定了函數(shù)的功能和用途。函數(shù)名稱后緊跟左括號，接著是用逗號分隔的稱為參數(shù)的內(nèi)容，最后用一個右括號表示函數(shù)結(jié)束。參數(shù)是函數(shù)中最復(fù)雜的組成部分，它規(guī)定了函數(shù)的運算對象、順序或結(jié)構(gòu)等。使得用戶可以對某個單元格或區(qū)域進(jìn)行處理，如分析存款利息、確定成績名次、計算三角函數(shù)值等。公式與函數(shù)什么是公式？

函數(shù)與公式既有區(qū)別又互相聯(lián)系。如果說函數(shù)是Excel預(yù)先定義好的特殊公式，公式就是由用戶自行設(shè)計對工作表進(jìn)行計算和處理的計算式。公式求和就是用四則運算符進(jìn)行的，如A1+A2函數(shù)求和的就是SUM,SUMIF,,,,等等，函數(shù)一般都比較不直觀，而且都是有英文代號和括號；運算符、優(yōu)先級與基本運算Excel包含四種類型的運算符：1、算術(shù)運算符2、比較運算符3、文本運算符4、引用運算符運算符-算術(shù)運算符算術(shù)運算符含義（示例）+（加號）加法運算(3+3)–（減號）減法運算(3–1)

負(fù)(–1)*（星號）乘法運算(3*3)/（正斜線）除法運算(3/3)%（百分號）百分比(20%)^（插入符號）乘冪運算(3^2)完成基本的數(shù)學(xué)運算，如加法、減法和乘法，連接數(shù)字和產(chǎn)生數(shù)字結(jié)果等，使用以下算術(shù)運算符：運算符-比較運算符比較運算符含義（示例）=（等號）等于(A1=B1)>（大于號）大于(A1>B1)<（小于號）小于(A1<B1)>=（大于等于號）大于或等于(A1>=B1)<=（小于等于號）小于或等于(A1<=B1)<>（不等號）不相等(A1<>B1)使用下列運算符比較兩個值。當(dāng)用運算符比較兩個值時，結(jié)果是一個邏輯值，不是TRUE就是FALSE。運算符-文本連接運算符文本運算符含義（示例）&（和號）將兩個文本值連接或串起來產(chǎn)生一個連續(xù)的文本值

(“好好”&”學(xué)習(xí)”)使用和號(&)加入或連接一個或更多文本字符串以產(chǎn)生一串文本。運算符-引用運算符使用以下運算符可以將單元格區(qū)域合并計算。引用運算符含義（示例）:（冒號）區(qū)域運算符，產(chǎn)生對包括在兩個引用之間的所有單元格的引用(B5:B15),（逗號）聯(lián)合運算符，將多個引用合并為一個引用(SUM(B5:B15,D5:D15))

（空格）交叉運算符產(chǎn)生對兩個引用共有的單元格的引用。(B7:D7C6:C8)運算符-優(yōu)先級公式中同時用到多個運算符，Excel將按下表所示的順序進(jìn)行運算。次序運算符說明1:（冒號）1

1（單個空格）,（逗號）引用運算符2–負(fù)號（例如–1）3%百分比4^乘冪5*和/乘和除6+和–加和減7&連接兩個文本字符串（連接）8=<><=>=<>比較運算符運算符-優(yōu)先級如果公式中包含相同優(yōu)先級的運算符，例如，公式中同時包含乘法和除法運算符，則Excel將從左到右進(jìn)行計算若要更改求值的順序，請將公式中要先計算的部分用括號（）括起來。注意，excel中的括號僅有（）一種，{}不是運算符，不是括號。$引用相對引用當(dāng)把公式復(fù)制到其他單元格時，行或者列就會改變。因為引用的是當(dāng)前行或者列的實際偏移量。如單元格顯示為A1，向下復(fù)制會自動變成A2，向右復(fù)制向B1。相對引用實際上是引用的單元格的相對位置。$引用相對引用當(dāng)把公式復(fù)制到其他單元格時，行或者列就會改變。因為引用的是當(dāng)前行或者列的實際偏移量。如單元格顯示為A1，向下復(fù)制會自動變成A2，向右復(fù)制向B1。相對引用實際上是引用的單元格的相對位置。$引用絕對引用，當(dāng)復(fù)制公式時，行和列不會改變。因為引用的是單元格的實際地址。單元格顯示為$A$1。此時向下或向右復(fù)制單元格，顯示的內(nèi)容不變，還是$A$1，所引用的對象不變，還是當(dāng)前A1單元格的數(shù)據(jù)。$引用混合引用，行或者列當(dāng)中有一個是絕對引用，另一個是相對引用。單元格顯示為$A1（絕對引用列）或A$1（絕對引用行）。$A1向右復(fù)制，會變?yōu)?A1，所引用的單元格為當(dāng)前表的A1單元格。向下復(fù)制，會變?yōu)?A2，所引用的單元格實際是A2。$引用規(guī)律：加上了絕對地址符“$”的列標(biāo)和行號為絕對地址，在公式向旁邊復(fù)制時不會發(fā)生變化，沒有加上絕對地址符號的列標(biāo)和行號為相對地址，在公式向旁邊復(fù)制時會跟著發(fā)生變化?；旌弦脮r部分地址發(fā)生變化。$在誰的前面就絕對引用誰F4是在四種引用間相互轉(zhuǎn)換的快捷鍵。內(nèi)置、嵌套、擴(kuò)展函數(shù)

按照函數(shù)的來源，Excel函數(shù)可以分為內(nèi)置函數(shù)和擴(kuò)展函數(shù)兩大類。前者只要啟動了Excel，用戶就可以使用它們;而后者必須通過單擊“工具→加載宏”菜單命令加載，然后才能像內(nèi)置函數(shù)那樣使用。函數(shù)是否可以是多重的呢？也就是說一個函數(shù)是否可以是另一個函數(shù)的參數(shù)呢？當(dāng)然可以，這就是嵌套函數(shù)的含義。所謂嵌套函數(shù)，就是指在某些情況下，您可能需要將某函數(shù)作為另一函數(shù)的參數(shù)使用。二、函數(shù)通覽1、日期與時間函數(shù)2、數(shù)學(xué)與三角函數(shù)3、邏輯函數(shù)

4、查找與引用函數(shù)5、數(shù)據(jù)庫函數(shù) 6、文本函數(shù)7、統(tǒng)計函數(shù)

8、財務(wù)函數(shù) 9、工程函數(shù) 10、信息函數(shù)

11、自定義函數(shù)

1、日期時間函數(shù):返回日期序列年份YEAR返回日期序列月份MONTH返回日期序列特定天DATE返回系統(tǒng)今天日期TODAY返回系統(tǒng)今天日期與時間NOW返回對應(yīng)日期的星期數(shù)WEEKDAY計算兩個日期之間的天數(shù)、月數(shù)或年數(shù)DATEDIF2、數(shù)學(xué)函數(shù):條件求和:SUMIF求個數(shù):COUNT求符合條件個數(shù):COUNTIF求和:SUM求算書平均數(shù):AVERAGE四舍五入函數(shù):ROUND排位:RANK3、邏輯函數(shù):將條件按且方式結(jié)合函數(shù)AND將條件按或方式結(jié)合函數(shù)OR將條件值反過來函數(shù)NOT執(zhí)行條件函數(shù)IF4、查找與引用函數(shù):查找表格中的值以列的方式VLOOKUPVlookup(已知條件,范圍,第幾列,FALSE)

文本----條件范圍----條件所在的列列----范圍中對應(yīng)列用數(shù)值表示

FALSE----精確查找（0）6、文本函數(shù):截取函數(shù)LEFTRIGHTMID計算字符長度LEN合并字符函數(shù)CONCATENATE在字符串中查找特定字符FIND比較兩個字符是否完全相符EXACT將數(shù)值轉(zhuǎn)化為文本TEXT將數(shù)值型字符轉(zhuǎn)化為數(shù)值VALUE文本函數(shù)Left根據(jù)所指定的字符數(shù)，LEFT返回文本字符串中第一個字符或前幾個字符，即返回文本左邊的幾個字符。示例：=left（”廣播電視”，2）計算結(jié)果：廣播36文本函數(shù)Right根據(jù)所指定的字符數(shù)返回文本字符串中最后一個或多個字符，即返回文本右邊的幾個字符。示例：=right（”廣播電視”，2）計算結(jié)果：電視37文本函數(shù)Mid返回文本字符串中從指定位置開始的特定數(shù)目的字符，該數(shù)目由用戶指定示例：=mid（“廣播電視”，3,1），結(jié)果：電38文本函數(shù)Len返回文本字符串中的字符數(shù)示例：=len（“廣播電視”）結(jié)果：439文本函數(shù)ReplaceREPLACE使用其他文本字符串并根據(jù)所指定的字符數(shù)替換某文本字符串中的部分文本。=REPLACE(“廣播電視”,3,1,“abc”)，結(jié)果：廣播abc視

40文本函數(shù)Clean刪除文本中不能打印的字符。對從其他應(yīng)用程序中輸入的文本使用本函數(shù)，將刪除其中含有的當(dāng)前操作系統(tǒng)無法打印的字符。例如，可以刪除通常出現(xiàn)在數(shù)據(jù)文件頭部或尾部、無法打印的低級計算機(jī)代碼。如從程序?qū)С鰯?shù)據(jù)中存在的換行符等。41文本函數(shù)FIND用于在第二個文本串中定位第一個文本串，并返回第一個文本串的起始位置的值，該值從第二個文本串的第一個字符算起。示例=FIND(“東”,“焦東陽”)，結(jié)果：2.=FIND(“東”,“東陽市焦東陽”，2)，結(jié)果：5.42文本函數(shù)連字符&連字符不屬于函數(shù)，主要功能為連接兩個文本字符串。為便于掌握，將其放在文本函數(shù)部分講解。示例：=“焦東陽”&“東陽市”結(jié)果：4310、信息函數(shù):查看是否為空白 ISBLANK查看是否為錯誤 ISERROR查看是否為"#N/A"以外的錯誤 ISERR查看是否為"#N/A" ISNA查看數(shù)值是否為偶數(shù) ISEVEN查看數(shù)值是否為奇數(shù) ISODD查看數(shù)值是否為邏輯值