电子表格常用函数.doc

SUMIF(range,criteria,sum_range)Range 为用于条件判断的单元格区域。Criteria 为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为 32、32、32 或 apples。Sum_range 是需要求和的实际单元格。例如，要求，第一列大于10的单元格，与它对应的第二列单元格求和。在求值单元格中输入：=SUMIF(A1:A6，10,B1:B6)SUMIF函数：对符合指定条件的单元格求和语法：SUMIF(range,criteria,sum_range)range是指用于筛选的单元格区域；criteria是筛选的条件，可以是数字、表达式或文本值，如100、100等；sum_range是实际的求和区域；例如：假设A1：A4中的数据是10、20、30、40，B1：B4中的数据是100、200、300、400，那么SUMIF（A1：A4，“15”，B1：B4）的值等于900。那是因为A2、A3、A4中的数据满足条件，所以相对应的对B2、B3、B4中的数据进行计算。语法： SUMIF(引用1,条件,引用2) 结果：按引用1中满足条件的单元格位置求引用2中对应位置的单元格中数值的总和。 说明：引用1和引用2两个参数必须是对区域的引用或由数组所定义的多个区域的引用，不能是直接的数组或由表达式返回的数组；引用2参数可以省略，省略时相当于同引用1参数；条件参数的用法同COUNTIF函数，其形式可以为数字、表达式、文本或数组，只能用单条件不能是复合条件，可以用统配符。对引用2的求和方式同SUM函数，对引用2中的文本、数字型的文本、逻辑值忽略不计算，如果引用2中包含错误值返回错误。if(判断条件,条件成立执行什么,条件不成立执行什么)比如=if(a1b1,a1,b1)先判断a1是不是大于b1,大于b1则在公式所在单元格放入a1的值,否则放入b1的值销售业绩”的评定标准为：“上半年销售合计”值在60万元以上（含60万元）者为“优异”、在4860万元之间（含48万元）者为“优秀”、 在3648万元之间（含36万元）者为“良好”、 在2436万元之间（含24万元）者为“合格”， 24万元以下者为“不合格”假设上半年销售合计在A1单元格，评定在B1单元格，则在B1输入=IF(A1=60,“优异,IF(AND(A1=48,A1=36,A1=24,A130)，结果为：8个。说明：在公式中的 30,1)，按下【Ctrl】+【Shift】+【Enter】三键，完成数组的输入。也可以使用这样的公式：=COUNTIF(D6:D35,&D7)。“&”此符号就是文本粘贴符，后面的是单元格地址，意思就是连接D7单元格的内容。大家可以在工作表中查看D7单元格的内容就是30。如果大于 后面没有函数，就没有比对的目标，所以要用&D7。有一种查看公式中部分内容的方法，就是按下F9键，俗称“抹黑”。 F9键在学习函数与公式中，对我们来说，有很大的帮助作用，帮助我们理解公式。 在本公式中，如果在公式编辑栏选中后面的&D7，按下F9键，可以看出变为了30，就和设计的第一种公式一样的，即：=COUNTIF(D6:D35,30)当然解决一个问题，设计的公式也许不只一种，只要大家根据自己的理解，灵活使用就可以了，得出的答案都是一样的。用同样方法可以得出“单价”小于100的个数，公式为：=COUNTIF(D6:D35,100)，得到正确答案为：30。第二，统计上图数据源中，“营业部”中含“河”字的个数，以及在“商品”这列中是否有键盘。要求解出答案，首先，需要领会COUNTIF 函数中通配符的使用规则，COUNTIF 函数是支持通配符的，在COUNTIF函数中可以引用通配符。其中通配符?号代表单个字符， *号代表多个字符。因此，统计“营业部”中含“河”字的个数，公式为：=COUNTIF(A6:A35,*河)，得到正确答案为：12。另外一种公式写法：=COUNTIF(A6:A35,*河*)，也可以得到答案。其实，通配符*号和find函数差不多，因此还可以这样设计公式：=COUNT(FIND(河,A6:A35)，然后按下【Ctrl】+【Shift】+【Enter】三键，完成数组的输入。在“商品”这列中是否有键盘，可以这样设计公式：=IF(COUNTIF(B6:B35,键盘),是,否)。第三，一次行列出营业部中“天河”、“黄埔河”、“黄埔”、“越秀”、“荔湾”、“超秀”出现的次数。对于一次性统计多单元格出现次数，可以使用数组公式。方法是：先选中L19：L24单元格区域，然后在编辑栏中输入公式=COUNTIF(A6:A35,K19:K24)，然后按下【Ctrl】+【Shift】+【Enter】三键，完成数组的输入。得到答案为：9、3、4、7、6、1次。另外此题，也可以使用普通公式：=COUNTIF($A$6:$A$35,K19)，然后往下拉，复制公式即可。第四，统计“销售日期”列下面的日期总共出现次数。计算数据源中日期总共出现的次数，也需要用到数组公式，这个公式在网上也是个经典例子。公式为：=SUM(1/COUNTIF(C6:C35,C6:C35)，然后三键结束。结果为：30。公式分析：公式中，COUNTIF(C6:C35,C6:C35)的结果为1，大家可以在上面公式中把这部分抹黑，得到结果为1。就是统计C6:C35每一个单元格内容出现的次数。在将公式中1/COUNTIF(C6:C35,C6:C35)部分抹黑，执行公式，得到数组形式的1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1，一共有30个1。1/就是每一个数值占1的百分比，来相加。抹黑查看完公式结果，可以按Esc键返回公式。最后再用SUM 函数合计。此题，还可以有下面两种公式设计，都可以实现结果。第一：=SUM(-(MATCH(C6:C35,C6:C35,)=ROW(C6:C35)-5)，三键结束。第二，设计普通公式：=COUNT(1/FREQUENCY(C6:C35,C6:C35)。有网友说到此题也可以使用公式：=COUNTIF(C6:C35,0)。这个公式实质有点问题，只是算C6：C35区域的数据，在本题中，虽然答案是一样的，是因为日期不重复，如果源数据中有两个相同的日期，结果就不对了。大家可以更改一下源数据里面的日期做个小试验。第五，统计“商品”列中不重复的有哪几个?此题可以理解为提取不重复值，最简单的方法是使用高级筛选，操作步骤：单击菜单“数据”“筛选”“高级筛选”，在“方式”下面选中：将筛选结果复制到其他位置，列表区域为：$B$6:$B$35，复制到：K32，勾选“选择不重复的记录”。说明：高级筛选的不好之处，就是工作表中的源数据更新后，筛选出来的结果是不会更新的。本题也可以使用公式来求解。下面设计的三种公式均能实现最终结果。公式一：=INDEX($B$6:$B$35,MATCH(,COUNTIF($L$31:L31,$B$6:$B$35),)，然后三键结束公式输入，下拉。公式二：=INDEX($B$6:$B$35,SMALL(IF(MATCH($B$6:$B$35,$B$6:$B$35,)=ROW($B$6:$B$35)-5,ROW($B$6:$B$35)-5,1000),ROW(A1)，然后三键结束公式输入，下拉。公式三：=LOOK