常規(guī)函數(shù)學(xué)習(xí)交流

Excel常見函數(shù)學(xué)習(xí)交流制造部唐柳萍/2013年4月2日SUM高級(jí)應(yīng)用IF判斷求解SUMIF條件求和COUNT非空單元格統(tǒng)計(jì)COUNTBLANK空白單元格統(tǒng)計(jì)HLOOKUP應(yīng)用LOOKUP應(yīng)用VLOOKUP應(yīng)用COUNTIF條件單元格統(tǒng)計(jì)目錄COUNTIFS多條件單元格統(tǒng)計(jì)SUM高級(jí)應(yīng)用—條件求和利用SUM與IF制成數(shù)組函數(shù)格式：﹛

SUM(IF(

條件設(shè)置

,結(jié)果數(shù)值區(qū)域

))﹜注意：1.“結(jié)果數(shù)值區(qū)域”可以是條件對(duì)應(yīng)的N個(gè)數(shù)列；2.數(shù)組公式，要CTRL+SHIFT+ENTER結(jié)束，即制成﹛﹜。3.必要時(shí)鎖住單元格，如$B$6：$B$29=$C$31,避免因添加、移動(dòng)、刪除、拖拉單元格造成所選區(qū)域偏移。數(shù)列行數(shù)SUM高級(jí)應(yīng)用—數(shù)組函數(shù)求和用“*”連接多條件數(shù)組函數(shù)求和函數(shù)格式：﹛

SUM(（區(qū)域1=條件1）*（區(qū)域2=條件2）*（區(qū)域3=條件3）*（結(jié)果數(shù)值區(qū)域）)﹜注意：1.“結(jié)果數(shù)值區(qū)域”即同時(shí)滿足條件1、2、3…的數(shù)值的總和；2.最多可以設(shè)置7組條件；條件區(qū)域橫向縱向選擇均可。3.數(shù)組公式，要CTRL+SHIFT+ENTER結(jié)束，即制成﹛﹜。4.必要時(shí)鎖住單元格，如$C$6：$C$29=$F$32,避免因添加、移動(dòng)、刪除、拖拉單元格造成所選區(qū)域偏移。IF條件判斷函數(shù)一般應(yīng)用函數(shù)格式：IF(

條件設(shè)置

,結(jié)果數(shù)值區(qū)域

)；如：IF(C3>60，”及格”,”不及格”)描述為：當(dāng)C3單元格數(shù)值滿足>60時(shí)，顯示”及格”——條件判斷為YES

當(dāng)C3單元格數(shù)值不滿足>60時(shí)，顯示”不及格”——條件判斷為NO注意：1.條件判斷的結(jié)果可以是文本形式；也可以摘選單元格區(qū)域；

2.當(dāng)條件判斷的結(jié)果為文本形式時(shí)，須用英文字符””將文本引用，否則函數(shù)程序無法識(shí)別。

3.以上示例適合應(yīng)用于同時(shí)滿足單一條件的情況。IF多條件判斷函數(shù)一般應(yīng)用函數(shù)格式：IF（AND(條件1，條件2),滿足條件值域，不滿足條件值域)判斷單元格是否同時(shí)滿足N（N<7）個(gè)條件，取值。注意：1.所取數(shù)值必須是單一文本或單元格信息；

2.設(shè)置條件最多不超過7個(gè)；

3.可以用幾個(gè)IF公式疊加制成，如示例。SUMIF條件求和函數(shù)應(yīng)用函數(shù)格式:SUMIF(條件篩選區(qū)域，單一指定條件，求和值域)示例函數(shù)含義：在條件篩選區(qū)域A里查找B22值，然后在求和值域B找出對(duì)于值并求和。注意：AB區(qū)域只能選擇單一且對(duì)應(yīng)的行/列；所有“隕石黑”格式必須一致，否則函數(shù)程序無法識(shí)別，常見錯(cuò)誤為空格不符、錯(cuò)別字、文本格式等。3.必要時(shí)鎖住單元格，如$B$4：$B$21,避免因添加、移動(dòng)、刪除、拖拉單元格造成所選區(qū)域偏移。SUMIFS條件求和函數(shù)應(yīng)用函數(shù)格式:SUMIFS(條件篩選區(qū)域1，條件1,條件篩選區(qū)域2,條件2...,求和值域)示例函數(shù)含義：在條件篩選區(qū)域1中查找A23值，再在條件區(qū)域2中查找B23,然后在求和值域找出對(duì)應(yīng)值并求和。注意：1、2、求和區(qū)域只能選擇單一且對(duì)應(yīng)的行/列；所有“隕石黑”格式必須一致，否則函數(shù)程序無法識(shí)別，常見錯(cuò)誤為空格不符、錯(cuò)別字、文本格式等。3.必要時(shí)鎖住單元格，如$B$4：$B$21,避免因添加、移動(dòng)、刪除、拖拉單元格造成所選區(qū)域偏移。LOOKUP函數(shù)應(yīng)用LOOKUP是向量或數(shù)組中查找信息，并返回指定單元格數(shù)值。函數(shù)格式：LOOKUP（指定條件，條件區(qū)域行/列，值區(qū)域行/列）示例函數(shù)表示：在A區(qū)域查找B22單元格信息，返回B區(qū)域?qū)?yīng)數(shù)值。注意：1.指定條件可以是單元格或文本信息，文本信息須用””將其引用起來。2.如果在A區(qū)域出現(xiàn)多個(gè)“楊麗紅”，計(jì)算機(jī)默認(rèn)返回其中一個(gè)“楊麗紅”對(duì)應(yīng)B區(qū)域的數(shù)值，易產(chǎn)生統(tǒng)計(jì)錯(cuò)誤。HLOOKUP縱向查找函數(shù)應(yīng)用HLOOKUP函數(shù)功能：查找數(shù)組首行，并返回指定行對(duì)應(yīng)單元格的值。函數(shù)格式：HLOOKUP（指定條件，查找區(qū)域，取值所在行數(shù)，0）示例函數(shù)描述為：在A2：P6區(qū)域的第一行（即A2：P2區(qū)域）查找A8“郭建峰”信息，由上至下數(shù)返回第5個(gè)單元格數(shù)值。注意：1.若函數(shù)選中區(qū)域第一行找不到指定信息，則函數(shù)顯示#N/A。2.該函數(shù)默認(rèn)從第一行內(nèi)查找指定條件信息。3.指定條件可以是單元格或文本。文本必須用””將其引用，否則函數(shù)公式無法識(shí)別。4.函數(shù)最后一個(gè)參數(shù)取TRUE或FALSE對(duì)函數(shù)結(jié)果影響不大，為了便于記憶，建議函數(shù)最后一個(gè)參數(shù)統(tǒng)計(jì)為0.12345VLOOKUP橫向查找函數(shù)應(yīng)用12345VLOOKUP函數(shù)功能：在數(shù)組第一列中查找，然后在行之間移動(dòng)以返回單元格中的值。注意：1.模糊查找可以用”?”代表任意單一字符；“*”代表任意多個(gè)字符；各字符、單元格、文本之間用&連接。COUNT/COUNTBLANK函數(shù)應(yīng)用COUNT函數(shù)功能：返回指定單元格區(qū)域包含數(shù)字的單元格的個(gè)數(shù)。函數(shù)格式：COUNT（指定區(qū)域）COUNTBLANK函數(shù)功能：返回指定單元格區(qū)域內(nèi)空白單元格個(gè)數(shù)。函數(shù)格式：COUNTBLANK（指定區(qū)域）注意：1.COUNT忽略空格、文本、錯(cuò)誤（#N/A）;2.COUNTBLANK中即使單元格中含有返回值為空文本("")的公式，該單元格也會(huì)計(jì)算在內(nèi)，但包含零值的單元格不計(jì)算在內(nèi)。COUNTIF函數(shù)應(yīng)用COUNTIF函數(shù)功能：計(jì)算指定區(qū)域中滿足條件單元格的個(gè)數(shù)函數(shù)格式：COUNTIF（指定區(qū)域，設(shè)置條件）COUNTIF函數(shù)的16種常規(guī)公式設(shè)置注：DATA為作者所選的指定區(qū)域1、返加包含值12的單元格數(shù)量=COUNTIF(DATA,12)2、返回包含負(fù)值的單元格數(shù)量=COUNTIF(DATA,"<0")3、返回不等于0的單元格數(shù)量=COUNTIF(DATA,"<>0")4、返回大于5的單元格數(shù)量=COUNTIF(DATA,">5")5、返回等于單元格A1中內(nèi)容的單元格數(shù)量=COUNTIF(DATA,A1)6、返回大于單元格A1中內(nèi)容的單元格數(shù)量=COUNTIF(DATA,“>”&A1)7、返回包含文本內(nèi)容的單元格數(shù)量=COUNTIF(DATA,“*”)8、返回包含三個(gè)字符內(nèi)容的單元格數(shù)量=COUNITF(DATA,“???”)9、返回包含單詞"GOOD"(不分大小寫)內(nèi)容的單元格數(shù)量=COUNTIF(DATA,“GOOD”)10、返回在文本中任何位置包含單詞"GOOD"字符內(nèi)容的單元格數(shù)量=COUNTIF(DATA,“*GOOD*”)11、返回包含以單詞"AB"(不分大小寫)開頭內(nèi)容的單元格數(shù)量=COUNTIF(DATA,“AB*”)12、返回包含當(dāng)前日期的單元格數(shù)量=COUNTIF(DATA,TODAY())13、返回大于平均值的單元格數(shù)量=COUNTIF(DATA,">"&AVERAGE(DATA))14、返回平均值上面超過三個(gè)標(biāo)準(zhǔn)誤差的值的單元格數(shù)量=COUNTIF（DATA，“>"&AVERAGE(DATA)+STDEV(DATA)*3)15、返回包含值為或-3的單元格數(shù)量=COUNTIF(DATA,3)+COUNIF(DATA,-3)16、返回包含值;邏輯值為TRUE的單元格數(shù)量=COUNTIF(DATA,TRUE)淺薄的經(jīng)驗(yàn)總結(jié)，希望對(duì)大家有所參考??函數(shù)應(yīng)用特別提醒事項(xiàng)函數(shù)的提示語

#N/A函數(shù)編制無原則錯(cuò)誤，但是計(jì)算機(jī)無法在你所選中的區(qū)域找到條件值。學(xué)會(huì)鎖住單元格單元格地址是由英文字母與阿拉伯?dāng)?shù)字組成，字母代表橫向坐標(biāo)，數(shù)字代表縱向坐標(biāo)，如，$A1即鎖住橫向坐標(biāo)，用光標(biāo)上下拖拉移動(dòng)時(shí)依次為$A1、$A2、$A3…A列不變；鎖住數(shù)字或同時(shí)鎖住字母、數(shù)字也是一樣的道理