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

相對引用

絕對引用

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

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

Part5公式相關：數(shù)據(jù)有效性、條件格式條件格式2：標出加班時數(shù)大于36小時的員工數(shù)據(jù)有效性Part5公式相關：數(shù)據(jù)有效性、條件格式Part6數(shù)組公式數(shù)組公式是用于建立可以產生多個結果或對可以存放在行和列中的一組參數(shù)進行運算的單個公式。數(shù)組公式概述由于一個單元格內只能儲存一個數(shù)值,所以當結果是一組數(shù)據(jù)時,單元格只返回第一個值,如:如輸入={23,24,25,22}結果返回為：23數(shù)組公式概述要返回指定結果，要多個單元格：=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ù)組,是一個矩形的單元格區(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ù)組公式的輸入同時按下CTRL+SHIFT+ENTER數(shù)組公式的外面會自動加上大括號“{}”予以區(qū)分數(shù)組公式的工作原理=SUM(IF(A2:A6="BB",(B2:B6)*(C2:C6),0))求產品為“BB”的總價？數(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ù)組公式實現(xiàn)歌唱比賽的評分{=AVERAGE(IF((所有評