COUNTIF函数的16种公式设置.doc

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(A11001!0!0;100,100,IF(A1:A101001!0!0;上限上限;&上限&CHAR(34)&上限&CHAR(34)&;&B1&CHAR(34)&B1&CHAR(34)&;&C1&CHAR(34)&C1&CHAR(34)&;G/通用格式)分享关于SUMPRODUCT（）函数的两个关键问题关于SUMPRODUCT（）函数的两个关键问题 众所周知在EXCEL函数中，“查找与引用”函数甚多，SUMPRODUCT（）并没在查找与引用的列中，但它不但可能进行查找与引用，而且可以多条件查找，这是其它函数不能与之比拟的。下面我谈一下关于该函数在作多条件引用时的使用过程中值得注意的关键两个问题：1、公式变异；2、数组参数必须具有相同的维数。一、公式变异： EXCEL帮助中对SUMPRODUCT（）函数的解释是：在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。 从以上解释中不难看出：该函数并未提及有查与引用功能，结果是积之和。它的参数的表达式是几组数组，然而我们在作多条件查找与引用中并非如此（这或许正是该函数的奇妙之处吧）。不用逗号“，”将各数组分开，反而用乘号“*”替之。如：原表达式：SUMPRODUCT(array1,array2,array3, .) 变异后表达式：SUMPRODUCT(array1*array2*array3* .)关于“*”的使用方法请参见坛中相关贴子，这里不再赘述。接下页搜索更多相关主题的帖子: SUMPRO DUCT 函数 嵌入式框架页展示Excel视频教程，包括Excel函数公式、Excel VBA图表应用技巧、Excel教程下载与免费在线学习培训。 活到老，学到老，还有三分没学倒！ UID303817帖子2779精华0经验3009 威望1 阅读权限70性别男在线时间32 小时查看详细资料引用 使用道具 报告 回复 TOP tonycq Excel Home铁杆级积分3125财富5919 ￥技术2 注册时间2007-7-22总积分排名 187 发短消息 加为好友 2楼 大 中 小 发表于 2007-8-10 17:48 只看该作者 2009第6期优秀会员名单揭晓 EH优秀会员奖励计划二、数组参数必须具有相同的维数： 如何理解具有相同的维数呢？我个人认为：若是一维的数组，就是数组间的单元格尺寸相同。如：A1：A10与C2：C11、D2：H2与F2：J2之间的维数是相同的。若是二维的数组，就是维数间的矩形区域尺寸相同(即行与列所围成矩形区域尺寸分别相同,简单地说，行数相当于矩形的“宽或高”，列数相当于矩形的“长”分别相等；这里不能用面积相等，也就是长与宽互换后，虽面积不变，而各边并非分别一一对应相等，公式会出错)。如：都是6行3列（即长为6，即有6行，宽为3，即有3列）；但B2：D7与F4：K6之间就是6行3列与3列6行，看上去面积为18（请允许我姑且这样说），但长与宽就不是分别相等了，故会出错。特别强调两点：1、需要特别注意的是“必须相同”四字，也就是说既不能多也不能少，只能相同。2、一组参数中，行与列的一维数组必须分别相同，即方向也尺寸均相同。但行的维数可以与列的维数不相同，不过在返回它们的二维数组区域时又必须相同，举例说明：可以这样列表达式：=SUMPRODUCT（A1：A10)*（C2：C11）（均是10行1列）=SUMPRODUCT（D2：H2）*（F2：J2）（均是1行5列）=SUMPRODUCT（A1：A10)*（C2：C11）*（D2：H2）*（F2：J2）（只要行/列分别相同，行与列维数可不相同）=SUMPRODUCT（A2：A10)*（B1：E1）*（B2：E10）（区域必须是4列9行）不可以这样列表达式：=SUMPRODUCT（A2：A10)*（B1：E1）*（B2:E9或E11）接下页特别感谢：Gdliyy版主在25、28楼中对文中关于维数理解的指正！此贴子已经被作者于2008-1-9 14:32:18编辑过活到老，学到老，还有三分没学倒！ UID303817帖子2779精华0经验3009 威望1 阅读权限70性别男在线时间32 小时查看详细资料引用 使用道具 报告 回复 TOP tonycq Excel Home铁杆级积分3125财富5919 ￥技术2 注册时间2007-7-22总积分排名 187 发短消息 加为好友 3楼 大 中 小 发表于 2007-8-10 17:49 只看该作者 好帖推荐、申请精华或加分、违规帖举报，欢迎“报告”，报告有奖！三、实例说明： 数据表：（见符件） ZozsN4TE.rar (5.5 KB) ZozsN4TE.rar (5.5 KB)下载次数: 4842007-8-10 17:52实例1：统计：部门：甲板部，职务：大副，性别：男的基础薪金之和？公式表达式：B16=SUMPRODUCT(B2:B11=甲板部)*(C2:C11=大副)*(D2:D11=男)*(E1=基础薪金) 分析： 这是个典型的多条件求值问题，题中共有4个条件，须同时满足。这里必须要求：B2:B11、 C2:C11、 D2:D11三个数组的维数相同，这里都是11行（个），否则出错。在录表达式时，不可以这样：=SUMPRODUCT(B2:B11=甲板部)*(C2:C10=大副)*(D2:D9=男)*(E1=基础薪金)另外，根据乘法交换律的规则（请允许我这样说）：上述公式可以这样：B16=SUMPRODUCT(E1=基础薪金) *(B2:B11=甲板部)*(D2:D11=男) *(C2:C11=大副)实例2：统计：部门：甲板部，职务：大副，性别：男等条件的基础薪金、岗位薪金、高温补贴、交通补贴的各项和？E21=SUMPRODUCT($B$2:$B$12=$B$21)*($C$2:$C$12=$C$21)*($D$2:$D$12=$D$21)*($E$1:$H$1=E$19)*($E$2:$H$12)实例3：分别统计甲板部、轮机部及事务部各种条件之和。E26=SUMPRODUCT($D$2:$D$12=$C26)*($E$1:$H$1=$D26)*($C$2:$C$12=$B26)*($B$2:$B$12=E$25)*($E$2:$H$12)不可以这样列表达式：E26=SUMPRODUCT($D$2:$D$12=$C26)*($E$1:$H$1=$D26)*($C$2:$C$12=$B26)*($B$2:$B$12=E$25)*($E$2:$H$11)顺便提一下，这里谈到的该函数仅是函数公式中多条件统计公式之一，在工作中根据实际情况，因事而用各种公式达到理想的效果。以上所述，不知说清楚没有，写详细没有，正确与否？请各位坛友不吝教，本人将洗耳恭听。此贴子已经被作者于2008-1-9 14:31:33编辑过附件XHJkB0E9.rar (5.5 KB) 同步自动提取不重复项、自动检错、自动分类汇总的模板，分类汇总简单有趣快速请先下载下行的表例附件，打开附件中的表格，体验本模板同步自动提取不重复项、自动检错、自动分类汇总的效果，想按什么汇总全由您作主：表例附件： 同步自动提取不重复项、自动分类汇总、自动检错表例.rar (147.81 KB) 同步自动提取不重复项、自动分类汇总、自动检错表例.rar (147.81 KB)下载次数: 9092010-1-15 14:591、想按供货商汇总，就把“清单”表供货商列整列复制后粘贴到“清单”表B列上，在“汇总表”看看结果吧；依此类推，您还可以分别将产品名称、楼号、供货商属地、位置、收货年月等整列复制后粘贴到B列上进行汇总。2、想按产品名称并区别不同规格汇总，就把“清单”表产品名称列整列复制后粘贴到B列上，规格列整列复制后粘贴到C列上，在“汇总表”看看结果吧；依此类推，您还可以根据需要按楼号并区别不同位置、按产品名称并区别不同收货年月、按供货商属地并区别不同供货商等不同组合要求分别进行汇总。3、想同时看到二种汇总结果吧，好，只需在进行以上1或2点操作同时，再将想要汇总的另一列（如楼号、供货商属地、收货年月等列）整列复制后粘贴到A列上，在“汇总表”看看结果吧；4、除同步自动提取全部不重复项并自动分类汇总外，还能自动检查计算错误，不信？那您就在“清单”表故意制造几个计算错误，您再看看“汇总表”有什么反映：她会告诉您在哪一行出错了哦，这样一来，您就不怕误操作了吧！检查出错误还直接指明出错行号哦，多方便，哈哈！ (试过后认为还行，就请轻抬贵手给个回复并望不吝指教，以便改进和让更多网友看到后或许也能用上，谢谢！)后面有与表例功能相同的各种不同数据关系的模板，自已用着感觉良好，有点爱不释手啊，呵呵 ，所以给大家分享，希望对能用到的有所帮助，也请大家指教。如果您觉得有可能用得上，就请您有空时再往下进一步了解本模板和选择适合您的模板。表格的设计是二难的：记流水帐吧，表格设计简单、排序好进行，但分类汇总就麻烦了；设计分类表格吧，分类汇总简单，但表格设计麻烦了点、排序就更没法直接进行了。本模板很好解决了上述矛盾，采用记流水帐方式做清单，并同步自动得到分类汇总的结果。 本模板可实现对表格快速同步自动提取全部不重复项并自动分类汇总、自动检错，想按啥汇总就按啥汇总！（试过表例您就知道既有趣又实用、快速，想按什么汇总分析都行，有时很需要多角度的分析，这个模板就能办到哦，呵呵）只要下载后面的模板，将您的含有三列数据（数据A、数据B、数据C）的源数据表的数据A整列剪切后整列插入到源数据表的A列、依此：数据B剪切插入到B列、数据C剪切插入到C列；再选取源数据表全部非空列后点复制，在本模板“清单”表选取D列后点粘贴，同样可以对您的数据表进行随心所欲的汇总分析。 上述表例是数据A数据B=数据C关系的表例，如果您需要的不是这种数据关系的，而是属其他数据关系的，请您在后面的模板中查找适合您用的模板。 如果您的数据只有1列或2列，用“A、B、C三数列清单汇总-模板”照样能行，只要用空列作为数据列凑足3列即可。您可以根据您所用表格的最大行数下载附件中相应的模板（当然下载大模板适用范围更大，只是占用磁盘空间多点，行数太多时也会使计算速度减慢，后面有计算时间的实测表供参考）。 模板是按数据关系对应使用的：如果您的表格是数据A数据B=数据C关系的，则应使用“AB=C清单汇总分析-模板”， 是数据A数据B=数据C关系的，则应使用“AB=C清单汇总分析-模板”， 若数据A、数据B、数据C间无关联关系的，则应使用“A、B、C三数列清单汇总-模板”。 由于上传文件大小的原因，没有上传全部模板，如有需要可回帖，可以上传更多清单行的模板。最大有效汇总清单行数为500行的模板：AB=C清单汇总分析-模板500.rar (127.77 KB) AB=C清单汇总分析-模板500.rar (127.77 KB)下载次数: 2542009-12-27 09:32AB=C清单汇总分析-模板500.rar (141.33 KB) AB=C清单汇总分析-模板500.rar (141.33 KB)下载次数: 1782009-12-27 09:32A、B、C三数列清单汇总-模板500.rar (139.67 KB) A、B、C三数列清单汇总-模板500.rar (139.67 KB)下载次数: 1792009-12-27 09:32最大有效汇总清单行数为1000行的模板：AB=C清单汇总分析-模板1000.rar (191.24 KB) AB=C清单汇总分析-模板1000.rar (191.24 KB)下载次数: 1872009-12-27 09:32AB=C清单汇总分析-模板1000.rar (206.71 KB) AB=C清单汇总分析-模板1000.rar (206.71 KB)下载次数: 1532009-12-27 09:32A、B、C三数列清单汇总-模板1000.rar (203.33 KB) A、B、C三数列清单汇总-模板1000.rar (203.33 KB)下载次数: 1852009-12-27 09:32最大有效汇总清单行数为2000行的AB=C清单汇总分析-模板：（以下2个分卷全部下载才行哦）AB=C清单汇总分析-模板2000.part01.rar (238.28 KB) AB=C清单汇总分析-模板2000.part01.rar (238.28 KB)下载次数: 422009-12-27 09:26AB=C清单汇总分析-模板2000.part02.rar (84.5 KB) AB=C清单汇总分析-模板2000.part02.rar (84.5 KB)下载次数: 282009-12-27 09:26最大有效汇总清单行数为2000行的AB=C清单汇总分析-模板：（以下2个分卷全部下载才行哦）AB=C清单汇总分析-模板2000.part01.rar (238.28 KB) AB=C清单汇总分析-模板2000.part01.rar (238.28 KB)下载次数: 262009-12-27 09:26AB=C清单汇总分析-模板2000.part02.rar (116.13 KB) AB=C清单汇总分析-模板2000.part02.rar (116.13 KB)下载次数: 142009-12-27 09:26最大有效汇总清单行数为2000行的A、B、C三数列清单汇总-模板：（以下2个分卷全部下载才行哦）A、B、C三数列清单汇总-模板2000.part01.rar (238.28 KB) A、B、C三数列清单汇总-模板2000.part01.rar (238.28 KB)下载次数: 562009-12-27 09:26A、B、C三数列清单汇总-模板2000.part02.rar (110.26 KB) A、B、C三数列清单汇总-模板2000.part02.rar (110.26 KB)下载次数: 442009-12-27 09:26最大有效汇总清单行数为4000行的AB=C清单汇总分析-模板：（以下3个分卷全部下载才行哦）AB=C清单汇总分析-模板4000.part01.rar (238.42 KB) AB=C清单汇总分析-模板4000.part01.rar (238.42 KB)下载次数: 162009-12-28 11:22AB=C清单汇总分析-模板4000.part02.rar (238.42 KB) AB=C清单汇总分析-模板4000.part02.rar (238.42 KB)下载次数: 132009-12-28 11:22AB=C清单汇总分析-模板4000.part03.rar (99.48 KB) AB=C清单汇总分析-模板4000.part03.rar (99.48 KB)下载次数: 122009-12-28 11:22最大有效汇总清单行数为4000行的AB=C清单汇总分析-模板：（以下3个分卷全部下载才行哦）AB=C清单汇总分析-模板4000.part01.rar (238.42 KB) AB=C清单汇总分析-模板4000.part01.rar (238.42 KB)下载次数: 152009-12-28 11:22AB=C清单汇总分析-模板4000.part02.rar (238.42 KB) AB=C清单汇总分析-模板4000.part02.rar (238.42 KB)下载次数: 132009-12-28 11:22AB=C清单汇总分析-模板4000.part03.rar (127.54 KB) AB=C清单汇总分析-模板4000.part03.rar (127.54 KB)下载次数: 152009-12-28 11:22最大有效汇总清单行数为4000行的A、B、C三数列清单汇总-模板：（以下3个分卷全部下载才行哦）A、B、C三数列清单汇总-模板4000.part01.rar (238.42 KB) A、B、C三数列清单汇总-模板4000.part01.rar (238.42 KB)下载次数: 312009-12-28 11:22A、B、C三数列清单汇总-模板4000.part02.rar (238.42 KB) A、B、C三数列清单汇总-模板4000.part02.rar (238.42 KB)下载次数: 332009-12-28 11:22A、B、C三数列清单汇总-模板4000.part03.rar (117.17 KB) A、B、C三数列清单汇总-模板4000.part03.rar (117.17 KB)下载次数: 342009-12-28 11:22最大有效汇总清单行数为1万行的AB=C清单汇总分析-模板：（以下6个分卷全部下载才行哦）AB=C清单汇总分析-模板.part001.rar (238.42 KB) AB=C清单汇总分析-模板.part001.rar (238.42 KB)下载次数: 932009-12-23 12:45AB=C清单汇总分析-模板.part002.rar (238.42 KB) AB=C清单汇总分析-模板.part002.rar (238.42 KB)下载次数: 542009-12-23 12:45AB=C清单汇总分析-模板.part003.rar (238.42 KB) AB=C清单汇总分析-模板.part003.rar (238.42 KB)下载次数: 462009-12-23 12:45AB=C清单汇总分析-模板.part004.rar (238.42 KB) AB=C清单汇总分析-模板.part004.rar (238.42 KB)下载次数: 532009-12-23 12:45AB=C清单汇总分析-模板.part005.rar (238.42 KB) AB=C清单汇总分析-模板.part005.rar (238.42 KB)下载次数: 522009-12-23 12:45AB=C清单汇总分析-模板.part006.rar (149.54 KB) AB=C清单汇总分析-模板.part006.rar (149.54 KB)下载次数: 492009-12-23 12:45最大有效汇总清单行数为1万行的AB=C清单汇总分析-模板：（以下6个分卷全部下载才行哦）AB=C清单汇总分析-模板.part001.rar (238.42 KB) AB=C清单汇总分析-模板.part001.rar (238.42 KB)下载次数: 772009-12-23 12:45AB=C清单汇总分析-模板.part002.rar (238.42 KB) AB=C清单汇总分析-模板.part002.rar (238.42 KB)下载次数: 682009-12-23 12:45AB=C清单汇总分析-模板.part003.rar (238.42 KB) AB=C清单汇总分析-模板.part003.rar (238.42 KB)下载次数: 642009-12-23 12:45AB=C清单汇总分析-模板.part004.rar (238.42 KB) AB=C清单汇总分析-模板.part004.rar (238.42 KB)下载次数: 622009-12-23 12:45AB=C清单汇总分析-模板.part005.rar (238.42 KB) AB=C清单汇总分析-模板.part005.rar (238.42 KB)下载次数: 602009-12-23 12:45AB=C清单汇总分析-模板.part006.rar (214.83 KB) AB=C清单汇总分析-模板.part006.rar (214.83 KB)下载次数: 592009-12-23 12:45最大有效汇总清单行数为1万行的A、B、C三数列清单汇总-模板：（以下6个分卷全部下载才行哦）A、B、C三数列清单汇总-模板.part001.rar (238.42 KB) A、B、C三数列清单汇总-模板.part001.rar (238.42 KB)下载次数: 1412009-12-23 12:45A、B、C三数列清单汇总-模板.part002.rar (238.42 KB) A、B、C三数列清单汇总-模板.part002.rar (238.42 KB)下载次数: 1032009-12-23 12:45A、B、C三数列清单汇总-模板.part003.rar (238.42 KB) A、B、C三数列清单汇总-模板.part003.rar (238.42 KB)下载次数: 962009-12-23 12:45A、B、C三数列清单汇总-模板.part004.rar (238.42 KB) A、B、C三数列清单汇总-模板.part004.rar (238.42 KB)下载次数: 1032009-12-23 12:45A、B、C三数列清单汇总-模板.part005.rar (238.42 KB) A、B、C三数列清单汇总-模板.part005.rar (238.42 KB