COUNTIF函数16种公式设置.docVIP

COUNTIF函数的16种公式设置 1、返加包含值12的单元格数量 =COUNTIF（DATA,12） 2、返回包含负值的单元格数量 =COUNTIF（DATA，0） 3、返回不等于0的单元格数量 =COUNTIF（DATA，0） 4、返回大于5的单元格数量 =COUNTIF（DATA，5） 5、返回等于单元格A1中内容的单元格数量 =COUNTIF（DATA，A1） 6、返回大于单元格A1中内容的单元格数量 =COUNTIF（DATA，“”A1） 7、返回包含文本内容的单元格数量 =COUNTIF（DATA，“*”） 8、返回包含三个字符内容的单元格数量 =COUNITF（DATA，“???”） 9、返回包含单词GOOD(不分大小写)内容的单元格数量 =COUNTIF（DATA，“GOOD”） 10、返回在文本中任何位置包含单词GOOD字符内容的单元格数量 =COUNTIF（DATA，“*GOOD*”） 11、返回包含以单词AB(不分大小写)开头内容的单元格数量 =COUNTIF（DATA，“AB*”） 12、返回包含当前日期的单元格数量 =COUNTIF（DATA，TODAY()） 13、返回大于平均值的单元格数量 =COUNTIF（DATA，AVERAGE(DATA)) 14、返回平均值上面超过三个标准误差的值的单元格数量 =COUNTIF（DATA，“AVERAGE(DATA)+STDEV(DATA)*3) 15、返回包含值为或-3的单元格数量 =COUNTIF（DATA，3）+COUNIF（DATA，-3） 16、返回包含值;逻辑值为TRUE的单元格数量 =COUNTIF（DATA，TRUE） 为数组设置上限、下限 【声明】原创作品，非允勿转。【概念】上、下限是工作中常用的限制数值的需求，即当数值处于下限~上限区间时，取值为数值本身，超过限制时，则取限制值。【为单值设置上下限示例】比如：当一个值超过100时，返回100，否则返回数值本身；当一个值小于20时，返回20，否则返回数值本身。其中，上限为100，下限为20。【解法1】=IF(A1100,100,IF(A120,20,A1))【解法2】=MIN(100,MAX(A1,20))【解法3】=MEDIAN(100,20,--A1)【解法4】=--TEXT(A1,[100]1!0!0;[20]2!0)上述解法，当A1为空单元格时，除了解法2返回下限外，其它都返回0。貌似解法4的公式长度比较长，呵呵。【为数组设置上下限】如果要求将A1:A10返回为设置了上限为100、下限为20的数组时，上述4个解法只有解法1、解法4能胜任，解法2、3使用的MAX、MEDIAN均只能返回单值。比如：=IF(A1:A10100,100,IF(A1:A1020,20,A1:A10))=--TEXT(A1:A10,[100]1!0!0;[20]2!0)相对而言，TEXT的解法变得更短些。【通用式】从解法4可以视为：=--TEXT(数值,[上限]上限;[下限]下限)其中，红色的上限、下限为“条件”，可以直接用文本连接符相连而蓝色的上限、下限为需要返回的值，因数值中常有0出现，需设置占位符!或\，但如果上下限是一个变量，无从得知是否有0时，可以用CHAR(34)上限CHAR(34)将它强制用CHAR(34)返回的引号包含起来，作为“文本”。通用式为：=--TEXT(数值,[上限]CHAR(34)上限CHAR(34);[下限]CHAR(34)下限CHAR(34))在Excel2007中，通用式为：=--TEXT(A1,[B1]CHAR(34)B1CHAR(34);[C1]CHAR(34)C1CHAR(34);G/通用格式) [分享]关于SUMPRODUCT（）函数的两个关键问题 ? 众所周知在EXCEL函数中，“查找与引用”函数甚多，SUMPRODUCT（）并没在查找与引用的列中，但它不但可能进行查找与引用，而且可以多条件查找，这是其它函数不能与之比拟的。下面我谈一下关于该函数在作多条件引用时的使用过程中值得注意的关键两个问题： 1、公式变异；2、数组参数必须具有相同的维数。 一、公式变异： EXCEL帮助中对SUMPRODUCT（）函数的解释是： 从以上解释中不难看出：该函数并未提及有查与引用功能，结果是积之和。它的参数的表达式是几组数组，然而我们在作多条件查找与引用中并非如此（这或许正是该函数的奇妙之处吧）。不用逗号“，”将各数组分开，反而用乘号“*”替之。如：原表达式：SUMPRODUCT(array1,array2,array3, ...) 变异后表达式：SUMPRODUCT(array1*array2*ar