函數(shù)超強練習(xí)(帶公式)

,,,,,,,,,,,,

,,,,,,,,,,,,

,,,,,,,,,,,,

,,,,,,,,,,,,

,,,,,,,,,,,,

,,,,,,,,,,,,

,,,,,,,,,,,,

,2-1:,SUMIF函數(shù),,,,,,,,,

,,,,,,,,,,,,

,,1:SUMIF函數(shù)的幫助,,,,,,,,,

,,,,,,,,,,,,

,,,"SUMIF(range,criteria,sum_range)",,,,,,,,

,,,"SUMIF(單元格引用,條件,求和區(qū)域)",,,,,,,,

,,,Range為用于條件判斷的單元格區(qū)域。,,,,,,,,

,,,Criteria為確定哪些單元格將被相加求和的條件，其形式可以為數(shù)字、表達式或文本。,,,,,,,,

,,,Sum_range

是需要求和的實際單元格。,,,,,,,,

,,,主要說明:,,,,,,,,

,,,①:只有在區(qū)域中相應(yīng)的單元格符合條件的情況下，第三個參數(shù)中的單元格才求和。,,,,,,,,

,,,"②:第一個參數(shù)和第三個參數(shù)必須是單元格引用．(有些用OFFSET,INDIRECT等函數(shù)返回的，但同樣也是單元格引用)",,,,,,,,

,,,③:如果忽略了第三個參數(shù)，則對區(qū)域中的單元格求和,,,,,,,,

,,,,,,,,,,,,

,,,月份,銷售數(shù)量,,①,8650,"=SUMIF(D18:D22,""2月份"",E18:E22)",,,

,,,1月份,1500,,,19340,"=SUMIF(E18:E22,"">1800"",E18:E22)",,,

,,,3月份,2100,,②,19340,第1或第3個參數(shù)E18:E23按F9返回數(shù)組-回車.(出錯）,,,

,,,1月份,2300,,,19340,"=SUMIF(OFFSET(D18:D23,,1),"">1800"",E18:E23)",,,

,,,2月份,1800,,,0,"=SUMIF(INDIRECT(""D18:D""&23),""2月份"",E18:E23)",,,

,,,3月份,8090,,③,0,"=SUMIF(D18:D23,""2月份"")",,,文本相加

,,,2月份,6850,,,19340,"=SUMIF(E18:E23,"">1800"")",,,

,,,,,,,,,,,,

,,2:SUMIF函數(shù)的例子,,,,,,,,,

,,,,,,,,,,,,

,,,①普通的條件,,,,,,,,

,,,,,,,,,,,,

,,,銷售人員,商品名稱,銷售日期,銷售單價,銷售數(shù)量,銷售金額,銷售廠家,,

,,,小李,A,2008/01/21,631,225,"￥141,975",百泰,,

,,,老趙,D,2008/02/03,1148,396,"￥454,608",君力,,

,,,劉三,B,2008/01/05,1759,243,"￥427,437",翠綠,,

,,,劉三,D,2008/02/26,1245,326,"￥405,870",寶亨達,,

,,,小李,B,2008/02/06,841,392,"￥329,672",百泰,,

,,,老趙,B,2008/01/15,861,243,"￥209,223",寶亨達,,

,,,小李,B,2008/03/28,807,244,"￥196,908",百泰,,

,,,小李,A,2008/01/16,1606,346,"￥555,676",翠綠,,

,,,,,,1112.25,,,,,

,,,1:簡單的單條件,,,,,,,,

,,,,,,,,,,,,

,,,判斷內(nèi)容,條件,求值,SUMIF,SUM+IF,SUM,SUMPRODUCT,,

,,,銷售人員,小李,銷售總數(shù)量,1207,1207,1207,1207,,

,,,商品名稱,A,銷售總數(shù)量,571,571,571,571,,"

"

,,,銷售日期,2月份之前,銷售總金額,1334311,1334311,1334311,1334311,,

,,,銷售數(shù)量,<300,銷售總數(shù)量,955,955,955,955,,

,,,銷售單價,>平均單價,銷售總金額,1843591,1843591,1843591,1843591,,

,,,銷售廠家,百泰,銷售總金額,668555,668555,668555,668555,,

,,,,,,,,,,,,

,,,2:SUMIF的缺陷,,,,,,,,

,,,,,,,,,,,,

,,,判斷內(nèi)容,條件,求值,SUMIF,SUM+IF,SUM,SUMPRODUCT,,

,,,銷售單價,>1000,銷售總金額,?,555676,555676,555676,,

,,,銷售人員,小李,,,,,,,

,,,銷售數(shù)量,>300,銷售總金額,？,860478,860478,860478,,

,,,商品名稱,D,,,,,,,

,,,,,,,,,,,,

,,,3:SUMIF的數(shù)組,課外題,,,,,,,

,,,,,,,,,,,,

,,,同區(qū)域內(nèi)的兩個條件（外套SUM）,,,使用一個公式完成下面兩項,,,,,

,,,商品名稱,A,銷售總金額,銷售人員,商品名稱,老趙總銷售數(shù)量,商品A銷售總額,,

,,,商品名稱,B,1860891,小李,B,1207,1163240,,

,,,,,,,,,,,,

,,,,,,,,,,,,

,,,②通配符條件,,,,,,,,

,,,,,,,,,,,,

,,,通配符的簡單介紹,,,,,,,,

,,,,,,,,,,,,

,,,,,,,,,,,,

,,,,*,代表任意多個字符(包括0個),,,,,,

,,,,?,代表單個字符,,,,,,

,,,,,,,,,,,,

,,,通配符簡單介紹,*A*,含有A的字符,,??,只含兩個字符,,,

,,,,A*,以A為開頭的字符,,?A?,3個字符且中間為A,,,

,,,,*A,以A為結(jié)尾的字符,,*A*A*,"含有""A""字>=2個的字符",,,

,,,,,,,,,,,,

,,,,注意:,,,,,,,

,,,,"當(dāng)需要查找的字符中要帶有*,?這兩個通配符字符時,必須在前面加上~輔助查找.",,,,,,,

,,,,"查找字符帶有~這個符號時,有時前面需要加~輔助,有時不用,比如下面*~*,~~的例子",,,,,,,

,,,,*~~*,含有~的字符,,~~,含有兩個~字符(不是一個),,,

,,,,*~*,以*為結(jié)尾的字符,,*~,不是數(shù)值的字符(不是以~結(jié)尾),,,

,,,,*~??*,"含有?的字符,?后面須帶有1個字符",,*~~,以~結(jié)尾的字符,,,

,,,,,,,,,,,,

,,,,迷糊人的地方,,,練習(xí),寫出表達式,,,

,,,,add~大家1,1,17,???*,2個字符以上,,,

,,,,1a321~~sdj,2,4,?A*H,第2個字符為A最后一個字符為H,,,

,,,,大將軍~,3,4,*??B?,"4個字符以上,且倒數(shù)第2個字符為B",,,

,,,,?sddd*,4,6,*~**~**,含有*兩個以上的字符,,,

,,,,dsa~*~?,5,21,*~~~*,查找以~*為結(jié)尾的字符,,,

,,,,~~,6,9,*~~~*~~,查找以~*~為結(jié)尾的字符,,,

,,,,,,,,,,,,

,,,,,,,,,,,,

,,,,,,,,,,,,

,,,示例數(shù)據(jù)區(qū)域,,,,,,,,

,,,,,,,,,,,,

,,,銷售人員,商品名稱,銷售日期,銷售數(shù)量,銷售單價,銷售金額,銷售廠家,,

,,,1組-小李,A-06132A,2008/01/21,276,987,"￥272,412",百泰有限公司,,

,,,銷售員-老趙,D-~5656*,2008/02/03,211,1681,"￥354,691",君力廠家,,

,,,劉三-組長,B-~2222~,2008/01/05,201,1672,"￥336,072",深圳翠綠,,

,,,5組-劉三,D-*5621~,2008/02/26,262,573,"￥150,126",深圳寶亨達,,

,,,小李-A0007,A-~3255*,2008/02/06,372,1166,"￥433,752",深圳百泰,,

,,,銷售員-老趙,D-45415D,2008/01/15,383,1037,"￥397,171",寶亨達廠家,,

,,,小李-組員,A-06652A,2008/03/28,246,1137,"￥279,702",百泰廠家,,

,,,銷售員-小李,B-~~656*,2008/01/16,357,754,"￥269,178",深圳翠綠有限公司,,

,,,,,,,,,,,,

,,,1:簡單的單條件,,,,,,,,

,,,,,,,,,,,,

,,,判斷內(nèi)容,條件,求值,SUMIF,,,,,

,,,銷售人員,小李,銷售總數(shù)量,1251,,,,,

,,,銷售廠家,百泰,銷售總金額,985866,,,,,

,,,商品名稱,A開頭,銷售總金額,985866,,,,,

,,,商品名稱,A開頭A結(jié)束,銷售總金額,552114,,,,,

,,,商品名稱,含有~，且最后*,銷售總數(shù)量,940,,,,,

,,,商品名稱,含~字符>=2,銷售總數(shù)量,558,,,,,

,,,,,,,,,,,,

,,,,,,,,,,,,

,,,2:SUMIF的數(shù)組,課外題,,,,,,,

,,,,,,,,,,,,

,,,判斷內(nèi)容,條件,求值,SUMIF,,,,,

,,,商品名稱,同時含有~*字符,銷售數(shù)量,1202,,,,,

,,,銷售廠家,百泰，君力,銷售金額,1340557,,,,,

,,,,,,,,,,,,

,,,,,,,,,,,,

,2-2:,COUNTIF函數(shù),,,,,,,,,

,,,,,,,,,,,,

,,1:COUNTIF函數(shù)的幫助,,,,,,,,,

,,,,,,,,,,,,

,,,"COUNTIF(range,criteria)",,,,,,,,

,,,"COUNTIF(單元格引用,條件)",,,,,,,,

,,,Range為需要計算其中滿足條件的單元格數(shù)目的單元格區(qū)域。,,,,,,,,

,,,Criteria為確定哪些單元格將被計算在內(nèi)的條件，其形式可以為數(shù)字、表達式或文本。,,,,,,,,

,,,主要說明:,,,,,,,,

,,,①:只有在區(qū)域中相應(yīng)的單元格符合條件的情況下，那么此單元格才會被統(tǒng)計個數(shù)。,,,,,,,,

,,,"②:第一個參數(shù)必須是單元格引用．(有些用OFFSET,INDIRECT等函數(shù)返回的，但同樣也是單元格引用)",,,,,,,,

,,,③:第一個參數(shù)，如果單元格中是數(shù)字且超過15位，統(tǒng)計時會出錯。（當(dāng)然是可以處理的）,,,,,,,,

,,,,,,,,,,,,

,,,,,,,,,,,,

,,2:COUNTIF函數(shù)的例子,,,,,,,,,

,,,,,,,,,,,,

,,,①普通的條件,,,,,,,,

,,,,,,,,,,,,

,,,銷售人員,商品名稱,銷售日期,銷售單價,銷售數(shù)量,銷售金額,銷售廠家,,

,,,小李,A,2008/01/21,1606,311,"￥499,466",百泰,,

,,,老趙,D,2008/02/03,818,306,"￥250,308",君力,,

,,,劉三,D,2008/01/05,671,297,"￥199,287",翠綠,,

,,,劉三,C,2008/02/26,1148,379,"￥435,092",寶亨達,,

,,,小李,A,2008/02/06,1755,264,"￥463,320",百泰,,

,,,老趙,C,2008/01/15,822,246,"￥202,212",寶亨達,,

,,,小李,C,2008/03/28,1204,295,"￥355,180",百泰,,

,,,小李,B,2008/01/16,800,391,"￥312,800",翠綠,,

,,,,,,1103,,,,,

,,,1:簡單的單條件,,,,,,,,

,,,,,,,,,,,,

,,,統(tǒng)計次數(shù),,,,,,,,

,,,判斷內(nèi)容,條件,COUNTIF,SUM+IF,SUM,SUMPRODUCT,,,

,,,銷售人員,小李,4,4,4,4,n(),,

,,,商品名稱,B,1,1,1,1,n(true)=1,,

,,,銷售日期,1月份之后,4,4,4,4,N()可以把邏輯值轉(zhuǎn)化為數(shù)值,,

,,,銷售數(shù)量,<300,4,4,4,4,不用N，*1也可以,,

,,,銷售單價,>平均單價,4,4,4,4,,,

,,,銷售廠家,百泰,3,3,3,3,,,

,,,,,,,,,,,,

,,,2:COUNTIF的缺陷,,,,,,,,

,,,,,,,,,,,,

,,,統(tǒng)計次數(shù),,,,,,,,

,,,判斷內(nèi)容,條件,COUNTIF,SUM+IF,SUM,SUMPRODUCT,,,

,,,銷售單價,>1000,?,3,3,3,,,

,,,銷售人員,小李,,,,,,,

,,,銷售數(shù)量,>300,？,1,1,1,,,

,,,商品名稱,D,,,,,,,

,,,,,,,,,,,,

,,,3:COUNTIF的數(shù)組,課外題,,,,,,,

,,,,,,,,,,,,

,,,,,,,,,,,,

,,,求下面兩個商品名稱的銷售總次數(shù),,,,,,,,

,,,商品名稱,C,銷售總次數(shù),,,,,,

,,,商品名稱,D,5,,,,,,

,,,,,,,,,,,,

,,,,,,,,,,,,

,,,②通配符條件,,,,,,,,

,,,,,,,,,,,,

,,,,,,,,,,,,

,,,示例數(shù)據(jù)區(qū)域,,,,,,,,

,,,,,,,,,,,,

,,,銷售人員,商品名稱,銷售日期,銷售數(shù)量,銷售單價,銷售金額,銷售廠家,,

,,,1組-小李,A-06132A,2008/01/21,338,1779,"￥601,302",百泰有限公司,,

,,,銷售員-老趙,D-~5656*,2008/02/03,363,1795,"￥651,585",君力廠家,,

,,,劉三-組長,B-~2222~,2008/01/05,310,1433,"￥444,230",深圳翠綠,,

,,,5組-劉三,D-*5621~,2008/02/26,350,863,"￥302,050",深圳寶亨達,,

,,,小李-A0007,A-~3255*,2008/02/06,246,1572,"￥386,712",深圳百泰,,

,,,銷售員-老趙,D-05415D,2008/01/15,228,892,"￥203,376",寶亨達廠家,,

,,,小李-組員,A-06652A,2008/03/28,291,1938,"￥563,958",百泰廠家,,

,,,銷售員-小李,B-~~656*,2008/01/16,249,651,"￥162,099",深圳翠綠有限公司,,

,,,,,,,,,,,,

,,,1:簡單的單條件,,,,,,,,

,,,,,,,,,,,,

,,,,,,,,,,,,

,,,判斷內(nèi)容,條件,COUNTIF,,,,,,

,,,銷售人員,小李,4,4,,,,,

,,,銷售廠家,百泰,3,,,,,,

,,,銷售數(shù)量,尾數(shù)是8,,,,,,,

,,,商品名稱,最后第2個字符是2,3,,,,,,

,,,商品名稱,第3個字符是0,3,,,,,,

,,,商品名稱,含有~，且最后*,3,,,,,,

,,,商品名稱,含~字符>=2,0,,,,,,

,,,,,,,,,,,,

,,,2:COUNTIF的數(shù)組,課外題,,,,,,,

,,,,,,,,,,,,

,,,符合條件的總計次數(shù),,,,,,,,

,,,判斷內(nèi)容,條件,COUNTIF,,,,,,

,,,商品名稱,以~或*字符結(jié)尾,,,,,,,

,,,銷售人員,小李，老趙,,,,,,,

,,,,,,,,,,,,

,,,,,,,,,,,,

,,,③簡單的排查,,,,,,,,

,,,,,,,,,,,,

,,,①,,,,,,,,

,,,查找出現(xiàn)次數(shù),,查找出現(xiàn)次數(shù),,,,,,

,,,2,此解法是幫了我們統(tǒng)計出身份證出現(xiàn)的次數(shù),,,

,,,,,,但是也有個弊端，要注意點。,,,

,,,,,,例如：其中要是再加一個5102111976113106215,,,

,,,,,,同樣會被統(tǒng)計上,,,

,,,,,,,,,

,,,,,,,,,

,,,,,,,,,

,,,,,,,,,

,,