像玩游戏一样玩函数
1、定义名称求和——sum求B列数据的和，正常的话我们都会用=SUM(B2:B15)
2、这样也没什么不妥，可当你在总计这里添加一行数据的话，sum不会自动求添加数据的和，这时我们可以定义个动态的行，让它永远都是总计的前一行如图，定义一个叫“笑看”的名称=Sheet1!B15这样的话，只要我们输入下面的公式，你就算在总计插入几行输入数据都会自动添加=SUM(B2:笑看)
3、动态求和——sumB列的数据会不断增加，区域不确定，这时我们可以通过count函数确定有多少个数字，然后通过offset的偏移来得到=SUM(B2:OFFSET(B2,COUNT(B:B)-1,))=SUM(OFFSET(B2,,,COUNT(B:B)))要求：数据必须连续输入，否则出错
4、闲话求和——sumQ：如果我们要求1-10的和怎么办？A：可以通过辅助谱驸扌溺列，在A1—A10分别输入1-10，然后用sum求和=SUM(A1:A10)不过如果数据庞大的话，用这办法涔吴貉馄恐怖行不通，那这时就可以通过row函数来生成1-10这样的序号=SUM(ROW(1:10))但假如你不小心删除掉一行的话，这个公式就出错，显然并不完美，其实我们可以嵌套个indirect函数来处理=SUM(ROW(INDIRECT("1:10")))下面的例子就不考虑万一的情况Q：如果要求1-10奇数跟偶数又该如何？A：其实我们可以利用mod（数据，2），如果是奇数就是1，偶数就是0这个特点来处理奇数：=SUM(MOD(ROW(1:10),2)*ROW(1:10))偶数：=SUM((MOD(ROW(1:10),2)=0)*ROW(1:10))也可以这样写=SUM(MOD(ROW(1:10)-1,2)*ROW(1:10))Q：如果要求1-10的最大，小5个数的和要怎么做呢？A：前几大可以用large，前几小可以用small前5大的和：=SUM(LARGE(ROW(1:10),ROW(1:5)))前5小的和：=SUM(SMALL(ROW(1:10),ROW(1:5)))
5、含错误值求和——sum数据源包含错误值，如果直接利用下面的公式会出错=SUM(A2:A15)其实我们可以加一个判断来处理这个问题，如判断这些是不是数字，就可以解决=SUM(IF(ISNUMBER(A2:A15),A2:A15))
6、含单位求和——sum金额都包含单位，如果直接求和的话，会报错，所以需要把金额的单元替换掉才行=SUM(--SUBSTITUTE(B2:B15,"元",))因为都是包含元，也可以利用总字符数-1这个特点来做=SUM(--LEFT(B2:B15,LEN(B2:B15)-1))不管那一种，提取出来的都是文本，需要进行--运算，转换成数值才行
7、再续求和——sum话说这个函数很强大，除了简单的求和外还可以进行条件求和，下面通过两个简单的糙怍牢莱事例进行说明根据学部，求总平均成绩=SUM(($E$2:$E$16=G2)*$D$2:$D$16)根据学部还有姓名，求总平均成绩=SUM(($E$2:$E$16=G12)*($C$2:$C$16=H12)*$D$2:$D$16)sum函数条件求和语法：sum((条件1）*（条件2）*（条件3）*求和区域），中间可以不断添加条件条件求和下载：http://pan.baidu.com/s/1jGoURMe
8、通配符求和——sum说到通配符，首先想到的是*，但嵌套衡痕贤伎sum函数不太合适，所以就得想取代的函数，find就是不错的选择=SUM(IF(ISNUMBER(FIND("好",A2:A9)),B2:B9))公式中的isnumber也可以换成其他类型的is类函数=SUM(IF(ISERROR(FIND("好",A2:A9)),,B2:B9))如iserror等等
9、含姓名求和——sum因为姓名的字符数不确定，所以得想其他方法。其实可以利用数字是单字节的特点来判定数字的起始位置，然后提取99位来得到，即使后面没有99位的，也会自动忽略，只提取到有数字的。=SUM(--MIDB(A2:A5,SEARCHB("?",A2:A5),99))
10、不同单元格数据累加求和——sum因为是累加，所以单元格的引用一定要一个绝对引用，一个相对引用，这个才能保证区域不断变大=SUM($A$2:A2)
11、同一单元格累加求和（含字母汉字）——sum这种提取就需要把非数字类型的强制转换成0，然后再求和，说到强制转换，text是个不错的选择，0;;;!0就是让大于0的显示本身，文本的强制转换成0=SUM(--TEXT(MID(A2,COLUMN(1:1),1),"0;;;!0"))
12、同一单元格数据累加——sum因为都在同一单元格，所以需要分别把数字提取出来，然后再求和第一种，需要判断有几位数，然后再提取求和=SUM(--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))row(indirect)就是判断有多少位数，然后分别从1提取到n位第二种，就是不管有几位，一概提取出来，因为超出部分提取到的空，所以需要在前面&0，这样才能保证提取到的都是数字=SUM(--(0&MID(A2,COLUMN(1:1),1)))因为数字一般都不会超过15位，也可以这样写=SUM(--(0&MID(A2,ROW($1:$15),1)))
13、包含2或4的编码的数据之求和——sum因为只要包含就可以，我们可以用find来查找单元格是否包含，有包含返回数据的对应值，还有一个问题就是有可能同时包含两个，所以需要特别注意=SUM(((ISNUMBER(FIND(2,A2:A14))+ISNUMBER(FIND(4,A2:A14)))>0)*B2:B14)最简单的方法，就是分别判断，有的朋友可能会想用常量数组{2,4}，这样的应该会更好，可以起到简化公式的目的=SUM((MMULT(1-ISERR(FIND({2,4},A2:A14)),{1;1})>0)*B2:B14)其实如果数据源没有同时包含2或者4的情况还可以有另外的写法=SUM(SUMIF(A2:A14,"*"&{2,4}&"*",B2))一旦有可能包含两个数的话，切忌不能用！！！
14、文本求和——sum数据源的数字是文本，还含有空格符号，其实我们可以想办法让文本数字变成真正的数字，只要运算就行，--得正就是不错的选择，而符号不能运算，会导致错误，这时只需加个判断看是不是数字即可=SUM(IF(ISNUMBER(--A1:A15),--A1:A15))如果是高版本的话，还可以这样写=SUM(IFERROR(--A1:A15,))
15、单条件计数——sum分别求A,B出现的次数，可以提取A列的字母，然后进行判断，让等于C列的值等于1，最后进行求和=SUM(IF(LEFT($A$1:$A$7)=C1,1))公式也可以简写成这样=SUM(--(LEFT($A$1:$A$7)=C1))
16、多条件计数——sum求出各个单位各种等级的人数，其实道理也差不多的，sum((条件1)*(条件2))就可以实现计数=SUM(($A$2:$A$30=$F2)*($C$2:$C$30=LEFT(G$1)))也可以把条件&起来像下面这种的=SUM(--($A$2:$A$30&$C$2:$C$30=$F2&LEFT(G$1)))
17、杂乱无章数据求金额，说明金额都用单位元=SUM(--TEXT(RIGHT(TEXT(MID(SUBSTITUTE("k"&A1,"元",REPT(" ",15)&"k"),COLUMN(1:1),15),),14),"G/通用格式;-G/通用格式;0;!0"))
18、筛选数据条件求和=SUMPRODUCT((B2:B5="材料")*SUBTOTAL(9,OFFSET(C1,ROW(1:4),)))筛选数据非subtotal莫属
19、算成绩，左边是基本成绩，右边是附加成绩=SUM(IF(ISERR(-A1:A4),--(A1:A4&"/1"),A1:A4))说明，这种就是利用分数的计算法则来做的，分数的表示方法，100 20/1就是100+20=120,100 10/2就是100+5=105，只要数据不超过5位都是正确的100 1000000/2这种就不行如下图
20、说明，这种就是利用分数的计算法则来做的，分数的表示方法，100 20/1就是100+20=120,100 10/2就是100+5=105，只要数据不超过5位都是正确的100 1000000/2这种就不行如下图
21、这两个分数看起来感觉没问题，但用type一测试问题就出来了，第一个是数字，第二个是文本
22、下面难度再加大点如果懂得下面的原理，只需把多余的（）替换掉就行=SUM(IF(ISERR(-A1:A4),--SUBSTITUTE(SUBSTITUTE(A1:A4,"("," "),")","/1"),A1:A4))
23、算价格，消费就是一冶嚏型正数，送就是负数=SUM(--(TEXT(RIGHT(TEXT(MID(SUBSTITUTE(SUBSTITUTE(A1,"送",&鳎溻趄酃quot;送-"),"元",REPT(" ",15)),COLUMN(1:1),15),),14),"0;-0;0;!0")))因为送是负数，所以要在送后面加-，就用替换函数替换，价格后面是元，我们也替换掉成足够多得空格，这样方便后续提取数据，正常的话数据都是15位以内，所以用mid(,column(),15)，这个可以保证从1位到最后1位都能够提取到15位，不错过任何数字为了方便用F9键解读公式，把公式改成=SUM(--(TEXT(RIGHT(TEXT(MID(SUBSTITUTE(SUBSTITUTE(A1,"送","送-"),"元",REPT(" ",15)),row($1:$30),15),),14),"0;-0;0;!0")))MID(SUBSTITUTE(SUBSTITUTE(A1,"送","送-"),"元",REPT(" ",15)),row($1:$30),15)就是{"消费满300 ";"费满300 ";"满300 ";"300 ";"00 ";"0 ";" ";" 送";" 送-";" 送-2";" 送-20";" 送-200";" 送-200 ";" 送-200";" 送-200 ";" 送-200 ";" 送-200 ";" 送-200 ";" 送-200 ";"送-200 ";" 送-200 ";"送-200 ";"-200 ";"200 ";"00 ";"0 ";" ";" ";" ";" "}得到这样的数据没有实际意义，关键是在这text精彩的，把所有多余的数字都去除{"消费满300 ";"费满300 ";"满300 ";"";"";"";" ";" 送";" 送-";" 送-2";" 送-20";" 送-200";" 送-200 ";" 送-200";" 送-200 ";" 送-200 ";" 送-200 ";" 送-200 ";" 送-200 ";"送-200 ";" 送-200 ";"送-200 ";"-";"";"";"";" ";" ";" ";" "}现在就只剩下文字跟数字了，再从右边提取14位{"费满300 ";"满300 ";"300 ";"";"";"";" ";" 送";" 送-";" 送-2";" 送-20";" 送-200";" 送-200 ";" 送-200";" 送-200 ";" 送-200 ";" 送-200 ";" 送-200 ";"送-200 ";" 送-200 ";"送-200 ";"-200 ";"-";"";"";"";" ";" ";" ";" "}现在基本明朗了，就只有两个数字，其他都跟文字组合在一起，现在只需用text把文本转换成{"0";"0";"300";"0";"0";"0";"0";"0";"0";"0";"0";"0";"0";"0";"0";"0";"0";"0";"0";"0";"0";"-200";"0";"0";"0";"0";"0";"0";"0";"0"}text提取的数字是文本只需用--将其转换就成功了，假如数据有小数点的话，需要把text的参数设置下就ok，搞个通用公式=SUM(--(TEXT(RIGHT(TEXT(MID(SUBSTITUTE(SUBSTITUTE(A1,"送","送-"),"元",REPT(" ",15)),COLUMN(1:1),15),),14),"G/通用格式;-G/通用格式;0;!0")))
24、其实这个公式如果能够善于利用威力很强，如下图=SUM(--(TEXT(RIGHT(TEXT(MID(SUBSTITUTE(A1,"元",REPT(" ",15)),COLUMN(1:1),15),),14),"G/通用格式;-G/通用格式;0;!0")))
