EXCEL公式函數(shù)應(yīng)用培訓(xùn)

EXCEL介紹〔公式函數(shù)篇〕公式函數(shù)的作用概述公式和函數(shù)使得電子表格變得非常有用。尤其是在處理大量數(shù)據(jù)的時(shí)候。數(shù)據(jù)更新后，公式將自動(dòng)更新結(jié)果。函數(shù)可以大大加強(qiáng)公式的功能和進(jìn)行普通運(yùn)算符不能完成的計(jì)算。通常使用的只有十幾個(gè)函數(shù)。目錄Part1引用函數(shù)Part2查找函數(shù)Part3統(tǒng)計(jì)函數(shù)Part4時(shí)間、日期、文本函數(shù)Part5公式相關(guān)：數(shù)據(jù)有效性、條件格式Part6數(shù)組公式Part1引用函數(shù)根底概念認(rèn)識(shí)單元格引用常用引用函數(shù)OFFSET函數(shù)函數(shù)綜合應(yīng)用實(shí)例Partl引用函數(shù)－單元格的引用用某種記號(hào)表示一個(gè)單元格，稱為對(duì)單元格的引用。在Excel中，有如下兩種方法表示一個(gè)單元格：〔1〕A1引用樣式最右下角的單元格——IV65536〔2〕R1C1引用樣式設(shè)置R1C1方式Partl引用函數(shù)－單元格的引用區(qū)域的引用連續(xù)區(qū)域用矩形的對(duì)角單元格的引用，中間加上冒號(hào)“:”來表示例：A1:E4不連續(xù)區(qū)域依次使用各個(gè)單元格及單元格區(qū)域的引用，之間用逗號(hào)“,”連接起來例：A1,E4:J8,F10:L20Partl引用函數(shù)－單元格的引用含義：?jiǎn)卧褡鳛橐粋€(gè)整體以單元格地址的描述形式參與運(yùn)算稱為單元格引用。引用方式：

相對(duì)引用

絕對(duì)引用

混合引用單元格引用公式與函數(shù)單元格引用單元格引用方式相對(duì)引用、絕對(duì)引用、混合引用的概念使用“$”鎖定引用引用實(shí)例：相對(duì)引用：A1，B5絕對(duì)引用：$A$5，$C$2混合引用：$A3，D$1相對(duì)、絕對(duì)引用舉例利潤(rùn)率恒定，所以用“$”鎖定引用在D2單元格輸入：=B2*C2*$G$1求產(chǎn)品的利潤(rùn)？混合引用舉例－九九乘法表B2公式：=IF(B$1>$A2,"",B$1&"*"&$A2&"="&B$1*$A2)Partl引用函數(shù)－OFFSET函數(shù)OFFSET函數(shù)將源引用根據(jù)指定的行、列偏移產(chǎn)生新的單元格引用。OFFSET函數(shù)語法OFFSET(基點(diǎn),行數(shù),列數(shù),[高度],[寬度])函數(shù)特征基點(diǎn)必須是單元格，不能是數(shù)組；[高度][寬度]用來指定目標(biāo)區(qū)域大小，假設(shè)不寫，那么默認(rèn)與基點(diǎn)區(qū)域相同[高度][寬度]允許負(fù)數(shù)。Partl引用函數(shù)－OFFSET函數(shù)Partl引用函數(shù)－OFFSET函數(shù)一個(gè)疑問：如何統(tǒng)計(jì)最后5天的銷售量的和？=SUM(OFFSET($C$1,COUNTA(C:C)-1,0,-5)

)Part2查找函數(shù)困惑一：手頭上有很多孔內(nèi)無銅的編號(hào)，另外有一個(gè)編號(hào)AR值數(shù)據(jù)表，如何知道這些孔內(nèi)無銅編號(hào)的AR值？困惑二：CCTC新春晚會(huì)中獎(jiǎng)一等獎(jiǎng)是1688、1353、2247，如何知道這幾個(gè)號(hào)碼對(duì)應(yīng)哪個(gè)員工？Part2查找函數(shù)FindingyourMr.Right&Mrs.Right!Part2查找函數(shù)介紹：VLOOKUPVLOOKUP可用于：產(chǎn)品信息查詢、員工信息查詢Part2查找函數(shù)VLOOKUP函數(shù)VLOOKUP(查找值,數(shù)據(jù)表,列序號(hào),查找模式)在C2輸入：=VLOOKUP(A2,$F$2:$G$13,2,false)關(guān)于查找模式：false:精確查找，不必排序；true：近似查找，首列升序VLOOKUP舉例2Part3統(tǒng)計(jì)函數(shù)COUNT家族－COUNT，COUNTA，COUNTIFSUM家族－SUM，SUMIF數(shù)據(jù)庫函數(shù)－DSUM，DCOUNT...Part3統(tǒng)計(jì)函數(shù)－Count家族COUNT(單元格/區(qū)域1，單元格/區(qū)域2...)統(tǒng)計(jì)指定單元格或區(qū)域包含數(shù)字的單元格個(gè)數(shù)。COUNTA(單元格/區(qū)域1，單元格/區(qū)域2...)統(tǒng)計(jì)指定單元格或區(qū)域非空的單元格個(gè)數(shù)。COUNTIF(引用,條件)統(tǒng)計(jì)指定單元格或區(qū)域滿足指定條件的單元格個(gè)數(shù)。COUNTIF介紹條件的形式可以為數(shù)字、表達(dá)式、文本或數(shù)組，只能用單條件不能是復(fù)合條件當(dāng)條件為文本時(shí)可以用通配符*(表示任意多的字符)和?(表示單個(gè)字符)，如果要查找不是通配符的*和?字符，可用~*和~?表示COUNTIF舉例統(tǒng)計(jì)上面數(shù)字區(qū)域的大于20的單元格個(gè)數(shù)?=COUNTIF($A$2:$D$7,“>20”)上例中的條件也可以是引用別的單元格的，如：寫成：=COUNTIF($A$2:$D$7,">"&G1)Countif舉例2 一廠QC統(tǒng)計(jì)加班實(shí)例：(動(dòng)畫演示〕COUNTIF舉例3〔文本〕求區(qū)域A2:D6中以A開頭的單元格數(shù) =COUNTIF(A2:D6,"A*")求區(qū)域A2:D6中以A開頭以6結(jié)束的單元格數(shù) =COUNTIF(A2:D6,"A*6")求區(qū)域A2:D6中第3位為A的單元格數(shù) =COUNTIF(A2:D6,"??A*")談?wù)凷UMIFSUMIF(引用1,條件,引用2)結(jié)果：按引用1中滿足條件的單元格位置求引用2中對(duì)應(yīng)位置的單元格中數(shù)值的總和。sumif舉例開路報(bào)廢面積是多少？3.2.2sumif舉例3.2.2sumif舉例=SUMIF(C2:C7,"開路",E2:E7)sumif舉例綜合應(yīng)用實(shí)例：歌唱比賽的評(píng)分如何快速統(tǒng)計(jì)平均分〔去掉一個(gè)最高分，一個(gè)最低分，再求平均〕=(SUM($B2:$G2)-Max($B2:$G2)-MIN($B2:$G2))/(COUNTA($B2:$G2)-2)數(shù)據(jù)庫函數(shù)dsum→返回?cái)?shù)據(jù)范圍的列中滿足指定條件的數(shù)字之和dsum(數(shù)據(jù)范圍,列標(biāo)題,條件)數(shù)據(jù)庫函數(shù)dsum舉例3.3dsum舉例2dsum舉例=DSUM(A1:E7,E1,C11:D12)數(shù)據(jù)范圍列標(biāo)題條件Part4時(shí)間日期、文本函數(shù)時(shí)間日期函數(shù)：DAY,WeekDay,NOW(),Today()...文本函數(shù)：Left，Right，F(xiàn)ind，Len，...時(shí)間函數(shù)您所看到的日期與Excel的內(nèi)部日期表示是不同的。對(duì)您來說，2005年8月22日是一個(gè)帶有年、月、日的日期。而對(duì)Excel來說，2005年8月22日是序列數(shù)38586。對(duì)于Excel，凌晨12:00點(diǎn)那么是0，而中午12:00點(diǎn)那么是小數(shù)0.5。時(shí)間函數(shù)2Now(),返回當(dāng)前的時(shí)間。Today(),返回當(dāng)前的日期。A1-B1:計(jì)算兩個(gè)日期之前的天數(shù)。Year(A1),moth(A1),day(A1),hour(A1),minute(A1),Second(A1):返回某事件表示的各細(xì)項(xiàng)。文本函數(shù)1Upper:把文本全部轉(zhuǎn)換為大寫。Lower:把文本全部轉(zhuǎn)換為小寫。Proper:把文本轉(zhuǎn)換為“恰當(dāng)”形式〔每一個(gè)單詞的第一個(gè)字母大寫〕。Left:從字符串的開始返回特定數(shù)量的字符。Left(“Add012”,3)=“ADD”Right:從字符串的尾部返回特定數(shù)量的字符。Right(“Add012”,3)=012Mid:從字符串的任意位置開始返回特定數(shù)量的字符。Mid(“Add012”,2,2)=“dd”文本函數(shù)2Trim：除了兩個(gè)單詞之間的單個(gè)空格，可以去掉文本參數(shù)中的所有空格。Clean：從字符串中去掉不能打印的字符。當(dāng)引入某種類型數(shù)據(jù)時(shí)，常會(huì)出現(xiàn)這些“垃圾”字符。Exact：比較兩個(gè)字符串是否完全相同。Len：返回字符串中的字符個(gè)數(shù)。Find:查找某個(gè)字符在文本中的位置。Concatenate：將兩個(gè)單元格中的信息合并到一個(gè)單元格中。使用Left和Find函數(shù)分割名字Left(A2,find(“”,A2)-1)Right(A2,len(A2)-find(“”,A2))Part5公式相關(guān)：條件格式、數(shù)據(jù)有效性條件格式1條件格式是指當(dāng)指定條件為真時(shí)，Excel自動(dòng)應(yīng)用于單元格的格式

Part5公式相關(guān)：數(shù)據(jù)有效性、條件格式條件格式2：標(biāo)出加班時(shí)數(shù)大于36小時(shí)的員工數(shù)據(jù)有效性Part5公式相關(guān)：數(shù)據(jù)有效性、條件格式Part6數(shù)組公式數(shù)組公式是用于建立可以產(chǎn)生多個(gè)結(jié)果或?qū)梢源娣旁谛泻土兄械囊唤M參數(shù)進(jìn)行運(yùn)算的單個(gè)公式。數(shù)組公式概述由于一個(gè)單元格內(nèi)只能儲(chǔ)存一個(gè)數(shù)值,所以當(dāng)結(jié)果是一組數(shù)據(jù)時(shí),單元格只返回第一個(gè)值,如:如輸入={23,24,25,22}結(jié)果返回為：23數(shù)組公式概述要返回指定結(jié)果，要多個(gè)單元格：=INDEX({23,24,25,22},1)返回23=INDEX({23,24,25,22},2)返回24=INDEX({23,24,25,22},3)返回25=INDEX({23,24,25,22},4)返回22或者取共性：=SUM({20,30,40,10})返回100=MAX({20,30,40,10})返回40數(shù)組公式的參數(shù)數(shù)組公式最大的特征就是所引用的參數(shù)是數(shù)組參數(shù),包括區(qū)域數(shù)組和常量數(shù)組。區(qū)域數(shù)組,是一個(gè)矩形的單元格區(qū)域,如$A$1:$D$5常量數(shù)組,是一組給定的常量,如{1,2,3}或{1;2;3}或{1,2,3;1,2,3}數(shù)組公式中的參數(shù)必須為"矩形",如{1,2,3;1,2}就無法引用了數(shù)組公式的輸入同時(shí)按下CTRL+SHIFT+ENTER數(shù)組公式的外面會(huì)自動(dòng)加上大括號(hào)“{}”予以區(qū)分?jǐn)?shù)組公式的工作原理=SUM(IF(A2:A6="BB",(B2:B6)*(C2:C6),0))求產(chǎn)品為“BB”的總價(jià)？數(shù)組公式的工作原理=SUM(IF(A2:A6="BB",(B2:B6)*(C2:C6),0))該公式等效于：=SUM(IF(A2="BB",B2*C2,0)IF(A3="BB",B3*C3,0)IF(A4="BB",B4*C4,0)...)=SUM(0,2*150,0,0,2*300)=900用數(shù)組公式實(shí)現(xiàn)歌唱比賽的評(píng)分{=AVERAGE(IF((所有評(píng)