《excel计数与求和.ppt》由会员分享，可在线阅读，更多相关《excel计数与求和.ppt（30页珍藏版）》请在三一办公上搜索。
1、,第七章 计数与求和技术主讲:刘彬,工作表单元格计数和求和一般情况下，一个计数公式可以返回满足具体筛选条件的确定范围的单元格数据。EXCEL97以后，Microsoft推出了一项功能，名为autoCalculate。这项功能可以在状态栏中显示选中范围的信息。利用此功能可以进行求和、平均值、计数等等。,7.3基本的计数公式,统计单元格汇总数量如果要统计一个范围内的单元格总数，可以使用如下公式：=ROWS(数据引用区域)*COLUMNS(数据引用区域)7.3.2 统计空单元格的数量=countblank(数据引用区域)7.3.3 统计非空单元格的数量=countA(数据引用区域),数字单元格计数如
2、果要单统计范围中的数字单元格，可以使用下列公式：=count(data)注意：包含日期或时间的单元格被视为数字单元格，包含逻辑值的单元格不能认为是数字单元格。非文本单元格计数=sum(if(isnontext(data),1)文本单元格计数=sum(if(istext(data),1),常见的计数与求和函数,常见的计数与求和函数,Count函数,COUNT(value1,value2,.)计算包含数字的单元格及参数列表中数字的个数。注解如果参数为数字、日期、逻辑值或者代表数字的文本（例如，用引号引起的数字，如“1”），则将被计算在内。如果参数为数组或引用，则只计算数组或引用中数字的个数。不会计
3、算数组或引用中的空单元格、逻辑值、文本或错误值。,CountA函数为统计非空单元格的个数,举例,=COUNT(A1:A7),3,=COUNT(A4:A7),2,=COUNT(A1:A7,TRUE),3,=COUNTA(A1:A7),6,=COUNTBLACK(A1:A7),1,COUNTIF函数,countif(数据区域，条件表达式)返回包含值12的单元格数量：=Countif(range,12)返回包含负值的单元格数量：=Countif(range,”&average(range)返回包含3或-3值的单元格数量=countif(range,3)+countif(range,-3),举例,实例
4、：假设想让A列中重复的数据用颜色突出显示,先选中A列点菜单:格式-条件格式弹出“条件格式”对话框对话框“条件1”的左侧，选“公式”后面的输入框中，输入公式=countif(A:A,A1)1点下面的“格式”按钮，为条件为真的单元格设置你想要的格。点击确定结束,补充知识,利用数据有效性设置不重复数据录入选择范围数据-有效性-设置-自定义输入公式：使用COUNTIF()函数例如：=COUNTIF(A:B,A1)=1 表示在A、B两列中录入不重复数据。通常情况下，可以使用此函数进行唯一值的数据录入，比如身份证号码等等。,补充知识,利用数据有效性设计动态更新的数据列表思路：利用列表，countif函数及
5、数据有效性完成。举例:(见模拟数据表动态更新列表),DCOUNT(database,field,criteria),功能：返回数据库或列表的列中满足指定条件并且包含数字的单元格数目。Database：构成数据清单或数据库的单元格区域。Field：指定函数所使用的数据列。数据清单中的数据列必须在第一行具有标志项。Field 可以是文本，即两端带引号的标志项，如“产量”；此外，Field 也可以是代表数据清单中数据列位置的数字：1 表示第一列，2 表示第二列，等等。Criteria：为一组包含指定条件的单元格区域。它至少包含一个列标志和列标志下方用于设定条件的单元格。如果省略，函数 DCOUNT
6、返回数据库中满足条件 criteria 的所有记录数。,举例,注意事项：,1、单列上具有多个条件：如果对于某一列具有两个或多个筛选条件，那么可直接在各行中从上到下依次键入各个条件。例如下面的条件区域显示销售人员列中包含李明、张奇或程西的行,2、多列上具有单个条件若要在两列或多列中查找满足单个条件的数据，请在条件区域的同一行中输入所有条件。例如，下面的条件区域将显示所有在类型列中包含农产品、在销售人员列中包含李明且销售额100的数据行。,3、某一列或另一列上具有单个条件若要找到满足一列条件或另一列条件的数据，请在条件区域的不同行中输入条件。例如，下面的条件区域将显示所有在类型列中包含日光灯、在销
7、售人员列中包含李明或销售额80的行。,4、满足多列条件若要找到满足多组条件(每一组条件都包含针对多列的条件)的数据行，请在各行中键入条件。例如，下面的条件区域将显示所有在销售人员列中包含李明且销售额100的行，同时也显示程龙销售商的销售额90的行。,使用多筛选条件进行计数,1、使用and筛选条件常见的例子是使用公式统计符合一定值范围的数字的数量。例如，统计出包含值大于0小于12的单元格数量。=countif(range，“0”)-countif(range，“12”)可否使用如下公式？=countif(range，“0”)+countif(range，“12”),不能该公式计算的是区域中所有单
8、元格的个数乘2,另外一种计算多条件公式的方法：使用数组公式=sum(range0)*(range12)注意数组公式最后要以ctrl+shift+enter结束。,And多条件举例。,要求统计分公司工资大于3000的女性员工数量,选定A2:A10。定义名称bm选定E2:E10。定义名称gz选定D2:D10。定义名称xb选定F2单元格 输入数组公式：=SUM(bm=“分公司”)*(xb=“女”)*(gz3000),补充：数组公式,指可以在数组的一项或多项上执行多个计算的公式。数组公式可以返回多个结果，也可返回一个结果。位于多个单元格中的数组公式称为多单元格公式，位于单个单元格中的数组公式称为单个单
9、元格公式。数组公式以Ctrl+Shift+Enter 结束输入。不能人工输入大括号，否则系统会视为一个文本字符串。,数组模拟数据表,注意事项：,1、每次输入或编辑数组公式都要以Ctrl+Shift+Enter结束2、使用多单元格公式时，还需遵循以下原则：输入公式前必须选择保存结果的单元格区域。不能更改数组公式中单个单元格的内容。可以移动或删除整个数组公式，但无法移动或删除其部分内容。不能向多单元格数组公式中插入空白单元格或删除其中的单元格。,数组常量简介,可以通过输入一系列项然后手动用大括号()将该系列项括起来创建数组常量，类似于：=1,2,3,4,5使用逗号分隔各项，将创建水平数组（一行）。
10、使用分号分隔各项，将创建垂直数组（一列）。创建水平常量例如创建新的工作簿。选择单元格 A1 到 E1。在编辑栏中输入下面的公式：=1,2,3,4,5 然后按Ctrl+Shift+Enter结束。,数组常量简介,从现有值创建数组常量在数组工作表上，选择单元格区域。按 F2 切换到编辑模式。按 F9 将单元格引用转换为值。Excel 将这些值转换为数组常量。按 Ctrl+Shift+Enter 输入数组常量作为数组公式。,应用：参考模拟数据表(数组应用),假设sheet1中有如下数据：,应用：查找区域中的n个最大值,选中对应n个单元格，输入如下公式：=LARGE(A1:A50,ROW(INDIRE
11、CT(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个单元格的
12、纵栏式数组。LARGE函数使用单元格A1:A50中的值，并且它计算n次，每次都对应于 ROW 函数返回的每个引用。,补充：PHONETIC()函数,我们在连接字符串时常使用&连接符或CONCATENATE函数，但是遇到字符串较多的情况下往往这两个办法都比较复杂，甚至无法实现。问题：连接A1:A100所有字符串,方法1：=A1&A2&A3&.&A100 或者=CONCATENATE(A1,A2,.,A100),方法2：=PHONETIC(A1:A100)PHONETIC函数可以对除纯数据、日文外的所有字符进行连接。对于连续区域直接使用即可。对于在同一工作表中的不连续区域，可用定义名称的方法连接。,数组常量简介,可以通过输入一系列项然后手动用大括号()将该系列项括起来创建数组常量，类似于：=1,2,3,4,5使用逗号分隔各项，将创建水平数组（一行）。使用分号分隔各项，将创建垂直数组（一列）。创建水平常量例如创建新的工作簿。选择单元格 A1 到 E1。在编辑栏中输入下面的公式：=1,2,3,4,5 然后按Ctrl+Shift+Enter结束。,几个小技巧,1、如何把123456转换成12-34-56格式？2、为什么下列数据用求和公式得不到正确结果？3、利用数组提取字符串的首字符,=text=(“123456”,”0-00-00”),
