EXCEL计数函数

工作表单元格计数和求和一般情况下，一个计数公式可以返回满足具体筛选条件的确定范围的单元格数据。EXCEL97以后，Microsoft推出了一项功能，名为autoCalculate。这项功能可以在状态栏中显示选中范围的信息。利用此功能可以进行求和、平均值、计数等等。语法：MOD(number,divisor)MOD函数使用大致分为下面两种情况。一、两个异号整数求余1.函数值符号规律(余数的符号)mod(负,正)=正mod(正,负)=负结论：两个整数求余时，其值的符号为除数的符号2.取值规律先将两个整数看作是正数，再作除法运算①能整除时，其值为0②不能整除时，其值=除数×(整商+1)-被除数例：mod(5,-4)=-3二、两个小数求余取值规律：被除数-(整商×除数)之后在第一位小数位进行四舍五入。例：mod(9,1.2)=1即：9除1.2其整商为7;7与除数1.2之积为8.4;8.4四舍五入之后为8;被除数9与8之差为1。故结果为1。例：mod(9,2.4)=0即：9除2.2其整商为4;4与除数2.2这积为8.8;8.8四舍五入之后为9;被除数9与9之差为0。故结果为0。利用mod及条件格式隔行变色7.3基本的计数公式7.3.1统计单元格汇总数量如果要统计一个范围内的单元格总数，可以使用如下公式：=ROWS(数据引用区域)*COLUMNS(数据引用区域)7.3.2统计空单元格的数量=countblank(数据引用区域)7.3.3统计非空单元格的数量=countA(数据引用区域)7.3.4数字单元格计数如果要单统计范围中的数字单元格，可以使用下列公式：=count(data)注意：包含日期或时间的单元格被视为数字单元格，包含逻辑值的单元格不能认为是数字单元格。7.3.5非文本单元格计数{=sum(if(isnontext(data),1))}7.3.6文本单元格计数{=sum(if(istext(data),1))}常见的计数与求和函数Count返回包含数字值范围中单元格的数量CountA返回范围中非空的单元格数量Countblank返回范围中空的单元格数量Countif返回符合确定筛选条件范围中单元格的数量Dcount计算符合确定筛选条件工作表数据库中记录的数量DcountA计算符合确定筛选条件工作表数据库非空记录的数量常见的计数与求和函数Dsum返回符合确定筛选条件的工作表数据库中某个列的和Sum求和Sumif满足筛选条件范围单元格的和Sumproduct乘以两个或两个以上范围中的相应单元格，返回这些产品的总数Sumsq返回参数的平方和，主要用于统计公式Sumx2py2返回两个范围中相应值的平方和,主要用于统计公式Sumxmy2返回两个范围中相应值差的平方和，主要用于统计公式Sumx2my2返回两个范围中相应值平方差的和，主要用于统计公式Count函数COUNT(value1,[value2],...)计算包含数字的单元格及参数列表中数字的个数。注解如果参数为数字、日期、逻辑值或者代表数字的文本（例如，用引号引起的数字，如“1”），则将被计算在内。如果参数为数组或引用，则只计算数组或引用中数字的个数。不会计算数组或引用中的空单元格、逻辑值、文本或错误值。CountA函数为统计非空单元格的个数举例=COUNT(A1:A7)3=COUNT(A4:A7)2=COUNT(A1:A7,TRUE)3=COUNTA(A1:A7)6=COUNTBLACK(A1:A7)1countif(数据区域，条件表达式)返回和A1(值为12)单元格值相等的单元格数量：=Countif(range,12)或=Countif(range,A1)返回包含负值的单元格数量：=Countif(range,”0”)返回包含当前日期的单元格数量=countif(range,today())返回包含大于平均值的单元格数量=countif(range,””&average(range))返回包含3或-3值的单元格数量=countif(range,3)+countif(range,-3)模拟数据表countif一般用法所求类型单元格公式真空单元格=COUNTIF(data1,=)真空+假空单元格=COUNTIF(data1,)非真空单元格=COUNTIF(data1,)文本型单元格=COUNTIF(data1,*)区域内所有单元格=COUNTIF(data1,)数字型单元格=COUNT(data1)模拟数据表countif一般用法实例：假设想让A列中重复的数据用颜色突出显示1.先选中A列2.点菜单:格式-条件格式3.弹出“条件格式”对话框4.对话框“条件1”的左侧，选“公式”5.后面的输入框中，输入公式=countif(A:A,A1)16.点下面的“格式”按钮，为条件为真的单元格设置你想要的格。点击确定结束补充知识利用数据有效性设置不重复数据录入选择范围数据-有效性-设置-自定义输入公式：使用COUNTIF()函数例如：=COUNTIF(A:B,A1)=1表示在A、B两列中录入不重复数据。通常情况下，可以使用此函数进行唯一值的数据录入，比如身份证号码等等。补充知识利用数据有效性设计动态更新的数据列表思路：利用列表，countif函数及数据有效性完成。举例:(见模拟数据表—动态更新列表)RANK返回一个数字在数字列表中的排位。语法：RANK(number,ref,order)Number为需要找到排位的数字。Ref为数字列表数组或对数字列表的引用。Ref中的非数值型参数将被忽略。Order为一数字，指明排位的方式。如果order为0（零）或省略，为按照降序排列的列表。如果order不为零，MicrosoftExcel对数字的排位是基于ref为按照升序排列的列表。参考模拟表rank在双列间排名DCOUNT(database,field,criteria)功能：返回数据库或列表的列中满足指定条件并且包含数字的单元格数目。Database：构成数据清单或数据库的单元格区域Field：指定函数所使用的数据列。数据清单中的数据列必须在第一行具有标志项。Field可以是文本，即两端带引号的标志项，如“产量”；此外，Field也可以是代表数据清单中数据列位置的数字：1表示第一列，2表示第二列，等等。Criteria：为一组包含指定条件的单元格区域。它至少包含一个列标志和列标志下方用于设定条件的单元格。如果省略，函数DCOUNT返回数据库中满足条件criteria的所有记录数。注意事项：1、单列上具有多个条件：如果对于某一列具有两个或多个筛选条件，那么可直接在各行中从上到下依次键入各个条件。例如下面的条件区域显示销售人员列中包含程香宙、张奇或程西的行销售人员程香宙张奇程西2、多列上具有单个条件若要在两列或多列中查找满足单个条件的数据，请在条件区域的同一行中输入所有条件。例如，下面的条件区域将显示所有在类型列中包含农产品、在销售人员列中包含程香宙且销售额1000的数据行。类型销售人员销售额农产品程香宙10003、某一列或另一列上具有单个条件若要找到满足一列条件或另一列条件的数据，请在条件区域的不同行中输入条件。例如，下面的条件区域将显示所有在类型列中包含农产品、在销售人员列中包含程龙或销售额1000的行。类型销售人员销售额农产品程龙10004、满足多列条件若要找到满足多组条件(每一组条件都包含针对多列的条件)的数据行，请在各行中键入条件。例如，下面的条件区域将显示所有在销售人员列中包含程香宙且销售额3000的行，同时也显示程龙销售商的销售额1500的行。销售人员销售额程香宙3000程龙1500使用多筛选条件进行计数1、使用and筛选条件常见的例子是使用公式统计符合一定值范围的数字的数量。例如，统计出包含值大于0小于12的单元格数量。=countif(range，“0”)-countif(range，“12”)可否使用如下公式？=countif(range，“0”)+countif(range，“12”)不能该公式计算的是区域中所有单元格的个数乘2另外一种计算多条件公式的方法：使用数组公式{=sum((range0)*(range12))}注意数组公式最后要以ctrl+shift+enter结束。And多条件举例。要求统计分公司工资大于3000的员工数ABCDE1部门账号姓名性别工资2总公司2001张伟男28003分公司2002张是男24004总公司2003李丽女33005分公司2004吴海琼女31006总公司2005刘明男36007分公司2006王虎男35008总公司2007赵明红女37009分公司2008李俊女310010总公司2009张美女3600①选定A2:A10。定义名称bm②选定E2:E10。定义名称gz③选定F2单元格输入数组公式：{=SUM((bm=分公司)*(gz3000))}应用：查找区域中的n个最大值选中对应n个单元格，输入如下公式：=LARGE(A1:A50,ROW(INDIRECT(1:n)))备注：使用ROW函数创建连续的整数数组。读取行号。例如选中A1:A5.输入数组公式{=row(1:5)}结果会显示1，2，3，4，5.但是如果在数组第一行上插入一行，就会变成2,3,4,5,6.因此我们使用indirect函数纠正。=ROW(INDIRECT(1:10))INDIRECT函数使用文本字符串作为参数（这是区域1:10由双引号括起的原因）。当插入行或移动数组公式时，Excel不会调整文本值。因此，此ROW函数总是生成所需的整数数组。公式分析：从内层的括号开始向外计算：INDIRECT函数返回一组文本值,在这种情况下,为值1到n。ROW函数依次生成包含n个单元格的纵栏式数组。LARGE函数使用单元格A1:A50中的值，并且它计算n次，每次都对应于ROW函数返回的每个引用。练习如何筛选出模拟表练习1中指定条件的数的个数知识点：1、如何随机生成50-70的数2、如何让生成的数值规定不变。技巧：选择随机生成的值所在范围,按住鼠标右键拖动到某一地方，再拖动回去，选择”仅复制值”选项。计算整个A列中含有“F”字符的单元格的个数。=COUNTIF(A:A,*F*)不能使用=COUNTIF(A1:A8,*F*)因为数据增加时，此公式不会自动增加区域已知A2:A52为某公司业务人员销售表。请根据题设条件完成以下内容练习2