EXCEL统计函数―COUNTA

EXCEL统计函数―COUNTA（精选11篇）

EXCEL统计函数―COUNTA 第1篇

EXCEL2010中，COUNT函数是用来统计EXCEL表中数值数据的数量的，而COUNTA函数则是计算非空单元格的数量的，在COUNTA函数中，数值以外的文本或逻辑值可以做为统计数据的个数，因此，在EXCEL表格的单元格中含有文本或逻辑值时，可以使用COUNTA函数来统计单元格的数量，

需要注意的是，在COUNTA函数中空白单元格会被忽略。

COUNTA函数的格式与COUNT函数一样。

如实例，参会人员数字不变，同时，对没有到会的人员做缺席处理。

EXCEL统计函数―COUNTA 第2篇

(1)函数分解

FREQUENCY函数以一列垂直数组返回某个区域中数据的频率分布。

语法:FREQUENCY(data_array,bins_array)

Data_array为一数组或对一组数值的引用，用来计算频率。如果data_array中不包含任何数值，函数FREQUENCY返回零数组;Bins_array为间隔的数组或对间隔的引用，该间隔用于对data_array中的数值进行分组。如果bins_array中不包含任何数值，函数FREQUENCY返回data_array中元素的个数，

(2)实例分析

首先在工作表中找到空白的I列(或其他列)，自I2单元格开始依次输入20、25、30、35、40...60，分别表示统计年龄小于20、20至25之间、25至30之间等的人数。然后在该列旁边选中相同个数的单元格，例如J2:J10准备存放各年龄段的统计结果。然后在编辑栏输入公式“=FREQUENCY(YEAR(TODAY)-YEAR(E2:E600),I2:I10)”，按下Ctrl+Shift+Enter组合键即可在选中单元格中看到计算结果。其中位于J2单元格中的结果表示年龄小于20岁的职工人数，J3单元格中的数值表示年龄在20至25之间的职工人数等。

Excel统计函数应用解析 第3篇

在介绍统计函数之前,请大家先看一下附表中的函数名称。是不是发现有些函数是很类似的,只是在名称中多了一个字母A,比如,AVERAGE与AVERAGEA;COUNT与COUNTA。基本上,名称中带A的函数在统计时不仅统计数字,而且文本和逻辑值(如TRUE和FALSE)也将计算在内。在下文中笔者将主要介绍不带A的几种常见函数的用法。

一、用于求平均值的统计函数AVERAGE、TRIMMEAN

1、求参数的算术平均值函数AVERAGE

语法形式为AVERAGE(Number1,Number2,)

其中Number1,Number2,为要计算平均值的1~30个参数。这些参数可以是数字,或者是涉及数字的名称、数组或引用。如果数组或单元格引用参数中有文字、逻辑值或空单元格,则忽略其值。但是,如果单元格包含零值则计算在内。

2、求数据集的内部平均值TRIMMEAN

(1)函数TRIMMEAN

先从数据集的头部和尾部除去一定百分比的数据点,然后再求平均值。当希望在分析中剔除一部分数据的计算时,可以使用此函数。比如,我们在计算选手平均分数中常用去掉一个最高分,去掉一个最低分,XX号选手的最后得分,就可以使用该函数来计算。

(2)语法形式为TRIMMEAN(Array,Percent)

其中Array为需要进行筛选并求平均值的数组或数据区域。Percent为计算时所要除去的数据点的比例,例如,如果percent=0.2,在20个数据点的集合中,就要除去4个数据点(200.2),头部除去2个,尾部除去2个。函数TRIMMEAN将除去的数据点数目向下舍为最接近的2的倍数。

3、举例说明:示例中也列举了带A的函数AVERAGEA的求解方法

求选手Annie的参赛分数。在这里,我们先假定已经将该选手的分数进行了从高到底的排序,在后面的介绍中我们将详细了解排序的方法。(参见图1)。

二、用于求单元格个数的统计函数COUNT

语法形式为COUNT(Value1,Value2,)

其中Value1,Value2,为包含或引用各种类型数据的参数(1~30个),但只有数字类型的数据才被计数。函数COUNT在计数时,将把数字、空值、逻辑值、日期或以文字代表的数计算进去;但是错误值或其他无法转化成数字的文字则被忽略。

如果参数是一个数组或引用,那么只统计数组或引用中的数字;数组中或引用的空单元格、逻辑值、文字或错误值都将忽略。如果要统计逻辑值、文字或错误值,应当使用函数COUNTA。

举例说明COUNT函数的用途,示例中也列举了带A的函数COUNTA的用途。仍以上例为例,要计算一共有多少评委参与评分(用函数COUNTA),以及有几个评委给出了有效分数(用函数COUNT)。(参见图2)。

三、求区域中数据的频率分布FREQUENCY

由于函数FREQUENCY返回一个数组,必须以数组公式的形式输入。

语法形式为FREQUENCY(data_array,bins_array)

其中Data_array为一数组或对一组数值的引用,用来计算频率。如果data_array中不包含任何数值,函数FREQUENCY返回零数组。Bins_array为一数组或对数组区域的引用,设定对data_array进行频率计算的分段点。如果bins_array中不包含任何数值,函数FREQUENCY返回data_array元素的数目。

看起来FREQUENCY的用法蛮复杂的,但其用处很大。比如可以计算不同工资段的人员分布,公司员工的年龄分布,学生成绩的分布情况等。这里以具体示例说明其基本的用法。(参见图3)。

以计算某公司的员工年龄分布情况为例说明。在工作表里列出了员工的年龄。这些年龄为28、25、31、21、44、33、22和35,并分别输入到单元格C4:C11。这一列年龄就是data_array。Bins_array是另一列用来对年龄分组的区间值。在本例中,bins_array是指C13:C16单元格,分别含有值25、30、35、和40。以数组形式输入函数FREQUENCY,就可以计算出年龄在25岁以下、26~30岁、31~35岁、36~40岁和40岁以上各区间中的数目。本例中选择了5个垂直相邻的单元格后,即以数组公式输入下面的公式。返回的数组中的元素个数比bins_array(数组)中的元素个数多1。第五个数字1表示大于最高间隔(40)的数值(44)的个数。函数FREQUENCY忽略空白单元格和文本值。

{=FREQUENCY(C4:C11,C13:C16)}等于{2;2;2;1;1}

四、一组用于求数据集的满足不同要求的数值的函数

1、求数据集的最大值MAX与最小值MIN

这两个函数MAX、MIN就是用来求解数据集的极值(即最大值、最小值)。函数的用法非常简单。语法形式为函数(Number1,Number2,),其中Number1,Number2,为需要找出最大数值的1到30个数值。如果要计算数组或引用中的空白单元格、逻辑值或文本将被忽略。因此如果逻辑值和文本不能忽略,请使用带A的函数MAXA或者MINA来代替。

2、求数据集中第K个最大值LARGE与第K个最小值SMALL

这两个函数LARGE、SMALL与MAX、MIN非常相像,区别在于它们返回的不是极值,而是第K个值。语法形式为:函数(array,k),其中Array为需要找到第k个最小值的数组或数字型数据区域。K为返回的数据在数组或数据区域里的位置(如果是LARGE为从大到小排,若为SMALL函数则从小到大排)。

说到这,大家可以想到。如果K=1或者K=n(假定数据集中有n个数据)的时候,是不是就可以返回数据集的最大值或者最小值了呢。

3、求数据集中的中位数MEDIAN

MEDIAN函数返回给定数值集合的中位数。所谓中位数是指在一组数据中居于中间的数,换句话说,在这组数据中,有一半的数据比它大,有一半的数据比它小。

语法形式为MEDIAN(Number1,Number2,)其中Number1,Number2,是需要找出中位数的1到30个数字参数。如果数组或引用参数中包含有文字、逻辑值或空白单元格,则忽略这些值,但是其值为零的单元格会计算在内。

需要注意的是,如果参数集合中包含有偶数个数字,函数MEDIAN将返回位于中间的两个数的平均值。

4、求数据集中出现频率最多的数MODE

MODE函数用来返回在某一数组或数据区域中出现频率最多的数值。跟MEDIAN一样,MODE也是一个位置测量函数。

语法形式为MODE(Number1,Number2,)其中Number1,Number2,是用于众数(众数指在一组数值中出现频率最高的数值)计算的1到30个参数,也可以使用单一数组(即对数组区域的引用)来代替由逗号分隔的参数。

5、以上函数的示例

以某单位年终奖金分配表为例说明。在示例中,我们将利用这些函数求解该单位年终奖金分配中的最高金额、最低金额、平均金额、中间金额、众数金额以及第二高金额等。(参见图4)。

详细的公式写法可从图中清楚的看出,在此不再赘述。

五、用来排位的函数RANK、PERCENTRANK

1、一个数值在一组数值中的排位的函数RANK

数值的排位是与数据清单中其他数值的相对大小,当然如果数据清单已经排过序了,则数值的排位就是它当前的位置。数据清单的排序可以使用Excel提供的排序功能完成。

语法形式为RANK(Number,Ref,Order)其中Number为需要找到排位的数字;Ref为包含一组数字的数组或引用。Order为一数字用来指明排位的方式。

如果Order为0或省略,则Excel将Ref当作按降序排列的数据清单进行排位。

如果Order不为零,Microsoft Excel将Ref当作按升序排列的数据清单进行排位。

需要说明的是,函数RANK对重复数的排位相同。但重复数的存在将影响后续数值的排位。这就好像并列第几的概念。例如,在一列整数里,如果整数10出现两次,其排位为5,则11的排位为7(没有排位为6的数值)。

2、求特定数值在一个数据集中的百分比排位的函数PERCENTRANK

此PERCENTRANK函数可用于查看特定数据在数据集中所处的位置。例如,可以使用函数PERCENTRANK计算某个特定的能力测试得分在所有的能力测试得分中的位置。

语法形式为PERCENTRANK(Array,X,Significance)其中Array为彼此间相对位置确定的数字数组或数字区域。X为数组中需要得到其排位的值。Significance为可选项,表示返回的百分数值的有效位数。如果省略,函数PERCENTRANK保留3位小数。

3、与排名有关的示例

仍以某单位的年终奖金分配为例说明,这里以员工Annie的排名为例说明公式的写法。(参见图5)。

奖金排名的公式写法为:

百分比排名的公式写法为:

以上仅介绍了Excel统计函数中比较常用的几种函数,更多的涉及专业领域的统计函数可参看相关的统计学书籍。

参考文献

[1]周察金.计算机应用基础.[M].高等教育出版社,2000.11.

EXCEL中查找函数的妙用 第4篇

下面介绍几个在实际工作中可能会经常用到的查找函数：MATCH()、LOOKUP()、HLOOKUP()、VLOOKUP(),这些查找函数不仅仅具有查对的功能，同时还能根据查找的结果和参数的设定得到我们需要的数值。特别是这几个函数的配合使用，并以两个逻辑函数IF()和ISERROR()辅助,我们就可以在两个或多个有一定关联的工作簿中动态生成新的数据列。

1．MATCH()

格式：MATCH(lookup_value,lookup_array,match_type)

从英文含义我们可以看出，它是一个匹配的函数，主要功能是按照match_type参数所指定的查找方式，在lookup_array参数所代表的数据组中查找lookup_value参数代表的数值,如果查找成功则返回lookup_value在lookup_array中的位置,不成功返回错误信息#N/A。

EXCEL在“编辑”菜单中提供的“查找”功能只能进行单一数值的查找，而MATCH()函数可以实现批量数值的查找，所需要的只是在需要查找的首个单元格中定义好该函数，然后拖动该单元格直至到需要的范围，所有需要查对单元格的查找结果会自动完成。

参数解释：

lookup_value——需要查找的值，可以是实际的数字、字符串或逻辑值，也可以是某一单元格的引用，通常使用单元格的引用；

lookup_array——查找范围，可以是定义的数组，也可以是自定义的多个连续单元格的引用，这个引用我们可以通过两种方式实现：

 直接输入连续单元格的地址，通常使用绝对引用地址，如$B$2:$B$50；

 选定一个连续的单元格区域后，通过“插入”菜单中“定义”项的“名称”给这段单元格区域定义一个名称，如NAME1,这样在以后所有使用该单元格区域的时候都可以用定义的名称代表。

match_type，查找的方式，其值为-1、0、1，默认为1。其中-1是在lookup_array查找大于或等于lookup_value的最小数值，要求lookup_array必须按降序排列；0是在lookup_array查找等于lookup_value的第一个数值，不需要排序；1是在lookup_array查找小于或等于lookup_value的最大数值，要求lookup_array必须按升序排列。

函数举例：

例1：有两个工作簿 BOOK1和BOOK2，它们的SHEET1中都具有名为“卡片号”的列，列标为B（假设BOOK1中该列包含数据的单元格为B2:B80，BOOK2中包含数据的单元格为B2:B1000），想查对一下BOOK1中的卡片号是否能够在BOOK2中的卡片号中全部查找到。

按照以上的需求我们可以进行如下的操作：

(1)定义引用的名称，拖动鼠标选中BOOK2中B2:B1000单元格，通过“插入”菜单的“定义”“名称”项，给其定义为NAME1；

(2)在BOOK1中插入一空列，假定列标为H，并在H2单元格中输入如下的公式：

=MATCH(B2,“BOOK2.XLS”!NAME1,0)；

(3)选中H2单元格，将鼠标指针移动至该单元格右下角的黑点处，此时指针变为小黑十字形状，按住左键，拖动鼠标至H80单元格，此时H2到H80单元格会自动显示出B2至B80在BOOK2中定义的NAME1所代表的单元格区域的位置（如果找到），或是#N/A（未找到）；

(4)通过“数据”菜单的“筛选”“自动筛选”功能项，可以查看所有匹配的项（H列的数值大于0），或是所有不匹配的项（H列的数值为#N/A）。

以上1、2步骤可以合并为直接在H2单元格中输入如下的函数：

=MATCH(B2,[BOOK2.XLS]SHEET1!$B$2:$B$1000,0)

需要注意的是，一定要使用$B$2:$B$1000，不能使用B2:B1000，否则在拖动鼠标时，其单元格范围会随着发生变化，产生错误的结果。另外在引用外部工作簿时，如果定义了引用名称，书写格式为：“工作簿名称”！引用名称，如“BOOK2.XLS”!NAME1；如果使用表格区域引用，格式为：[工作簿名称]工作表名称！表格区域，如[BOOK2.XLS]SHEET1!$B$2:$B$1000。

2．LOOKUP()、HLOOKUP()、VLOOKUP()

LOOKUP()、HLOOKUP()、VLOOKUP()函数的功能都是在数组或表格中查找指定的数值，并按照函数参数设定得值返回表格或数组当前列（行）中指定行（列）处的数值。

由于LOOKUP()函数在单行（列）区域查找数值，并返回第二个单行（列）区域中相同位置的数值，或是在数组的第一行（列）中查找数值，返回最后一行（列）相同位置处的数值，其适用范围具有比较大的局限性，在实际的应用中，通常使用更加灵活的HLOOKUP()和VLOOKUP()函数。

HLOOKUP()和VLOOKUP()的作用类似，其区别是HLOOKUP()在表格或数组的首行查找数值，返回表格或数组当前列中指定行的数值，而VLOOKUP()是在表格或数组的首列查找数值，并返回表格或数组当前行中指定列的数值。这里所说的表格是按单元格地址设定的一个表格区域，如A2:E8。

HLOOKUP()函数的格式如下：

HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

参数解释：

lookup_value——需要在表格或数组第一行中查找的数值，可以是数值、字符串或引用；

table_array——需要在其中查找数值的表格区域、数组或是表格区域的引用；

row_index_num——为 table_array 中待返回的匹配值的行序号；

range_lookup——为一逻辑值，为TRUE或省略该值时，要求table_array第一行的数据必须升序排列，否则会得到错误的结果，同时表示待查找内容与查找内容近似匹配就可以了，如果不能精确匹配的话，则函数返回小于lookup_value的最大数值；如果为FALSE，不需要table_array的数值进行排序，并要求精确匹配，如果没有找到则函数返回#N/A。

VLOOKUP（）函数的格式如下：

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

函数的参数中除了col_index_num表示table_array 中待返回的匹配值的列序号外，其他参数的意义和HLOOKUP()相同。

函数举例：

例2：在BOOK2中，需要根据每一行中的资产类别（列标为D），自动的计算出该资产的折旧年限。

我们可以进行如下的操作：

(1)由于在现有的表格当中不存在资产类别和折旧年限的对应关系，因此首先要根据资产分类的情况构造出一个数组，数组的第一行为资产的类别名称，第二行为资产所对应的折旧年限，{“类别1”，“类别2”，“类别3”，“类别4”，“类别5”；4，6，7，8，10}；

(2)在BOOK2的SHEET1中插入一空列，列标为K，在K2单元格中输入如下的公式：

=HLOOKUP(D2, {“类别1”，“类别2”，“类别3”，“类别4”，“类别5”；4，6，7，8，10},2,FALSE),

(3)选中K2单元格，将鼠标指针移动至该单元格右下角的黑点处，指针变为小黑十字形状时，按住左键，拖动鼠标至K80单元格,折旧年限会自动地显示出来。

例3：按照BOOK1 中SHEET1的卡片号（列标为B）从BOOK2中SHEET1的型号列（列标为E）获取与该卡片号对应的内容，从而在BOOK1的SHEET1中自动的生成一个新列（列标为U）。

解决方法

(1)由于是在列中查找匹配的值，需要返回当前行制定列的值，因此适宜使用VLOOKUP（）函数，确定需要查找的数值为从BOOK1/SHEET1的B2到B80，待查找的范围为BOOK2/SHEET1的B2：E1000,返回匹配值的列序号为4。

(2)在BOOK1/SHEET1的U2单元格，输入如下的公式：

=VLOOKUP（B2,[BOOK2.XLS]SHEET1!$B$2:$E$1000,4,FALSE）

(3)选中U2单元格，将鼠标指针移动至该单元格右下角的黑点处，指针变为小黑十字形状时，按住左键，拖动鼠标至U80单元格,对应的型号会在U列中显示出来。

3．IF（）

格式：IF(logical_test,value_if_true,value_if_false)

IF函数的作用是根据逻辑判断的结果返回相应的值

参数解释：

logical_test——逻辑表达式，

value_if_true——结果为TRUE时，函数返回的值;

value_if_false——结果为FALSE时，函数返回的值;

value_if_true和 value_if_false都可以是其他的公式，也可以是IF函数的嵌套进行多级判断，嵌套不能超过7层。

当逻辑表达式包含有函数的时候，可以使用ISERROR（）函数来判断函数返回的结果是否是错误值（包括#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? 或 #NULL!）。

ISERROR()函数的格式为ISERROR(value),其中参数value为要检测的值。

函数举例：

例4：在例1的基础上，如果找到卡片号，则在BOOK1的SHEET1的N列的相应行处显示“相符”，否则显示“未发现”。

解决办法：

在BOOK1的SHEET1的N2单元格处输入如下的公式：

=IF(ISERROR(MATCH(B2,“BOOK1.XLS”!NAME1,0))=FALSE,“相符”，“未发现”)

在实际的工作中，可能会先判断一下某个单元格中的数值（数字、文本或引用）在另外的一个工作簿中是否存在相应的匹配数值，如果找到匹配值则进行下一步的计算，如果没有可能要以某些值代替。在这种情况下，我们就可以配合使用多个函数来实现这个目的。下面我们通过一个例子进行说明。

例5：从BOOK2/SHEET1中查找与BOOK1/SHEET1的卡片号相匹配的行，并将该行中“购置日期”列（列标为F）的数值显示在BOOK1/SHEET1的G列中相应的单元格中，要求如果没有找到匹配的数值，则在BOOK1/SHEET1的G列的相应单元格中填写“1996/1/1”。

我们可以进行如下的操作：

(1)在G2单元格中输入如下的公式：

=IF(ISERROR(MATCH(B2,[BOOK2.XLS]SHEET1!$B$2:$B$1000,0))=FALSE,VLOOKUP(B2,[BOOK2.XLS]SHEET1!$B$2:$F$1000,5,FALSE),“1996/1/1”)

(2)选中G2单元格，将鼠标指针移动至该单元格右下角的黑点处，指针变为小黑十字形状时，按住左键，拖动鼠标至G80单元格,对应的购置日期会在G列中显示出来。

在上述公式中, ISERROR(MATCH(B2,[BOOK2.XLS]SHEET1!$B$2:$B$1000,0))作为一个逻辑表达式，判断是否在BOOK2/SHEET1的B列中找到匹配的数值，如果结果为FALSE,表明找到匹配的值，进而IF（）函数计算VLOOKUP(B2,[BOOK2.XLS]SHEET1!$B$2:$F$1000,5,FALSE)，也就是将BOOK2/SHEET1中相匹配的行中F列的内容赋值给G2；若为TRUE,表示没有找到,G2的值为1996/1/1,以此类推。

姓 名：孟海江

工作单位：沧州供电公司

通信地址：河北省沧州市北环西路8号沧州供电公司信息中心

邮政编码：061001

联系电话：0317-2195487 , 13513176176

电子信箱：czdl_mhj@sohu.com czm_hj@tom.com

Excel统计函数 第5篇

1、启动Excel

1)点击“开始-所有程序-Microsoft-Microsoft Office Excel ”;

2)出现一个满是格子的空白窗口，这就是一张电子表格了，第一个格子看着边框要粗一些，处于选中状态;

2、Excel窗口

1)点菜单“文件-打开”命令，打开上次的“成绩表”文件;

2)在姓名的下面输入“及格数”，然后把光标移到旁边一格;

3)点一下编辑栏旁边的“fx”，在出来的函数列表里面找到“统计”，在下面找到“COUNTIF”，点“确定”按钮;

4)接下来是数据区域面板，从“78”向下拖到“68”，框选中三个人的语文成绩;

5)切换到英文输入法，在函数面板的第二个文本框中输入“>=60”，然后点“确定”，也就是60分以上算及格;

点“确定”后，单元格中出现统计结果;

EXCEL统计函数―COUNTA 第6篇

COUNTIF函数计算区域中满足给定条件的单元格的个数，

语法：COUNTIF(range,criteria)

Range为需要计算其中满足条件的单元格数目的单元格区域;Criteria为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。

(2)实例分析

假设上面使用的人事管理工作表中有599条记录，统计职工中男性和女性人数的方法是:选中单元格D601(或其他用不上的空白单元格)，统计男性职工人数可以在其中输入公式“=“男”&COUNTIF(D2:D600,“男”)&“人””;接着选中单元格D602，在其中输入公式“=“女”&COUNTIF(D2:D227,“女”)&“人””，

回车后即可得到“男399人”、“女200人”。

上式中D2:D600是对“性别”列数据区域的引用，实际使用时必须根据数据个数进行修改。“男”或“女”则是条件判断语句，用来判断区域中符合条件的数据然后进行统计。“&”则是字符连接符，可以在统计结果的前后加上“男”、“人”字样，使其更具有可读性。

EXCEL统计函数―COUNTA 第7篇

COUNTBLANK与COUNT和COUNTA一样，是EXCEL中使用率较高的统计函数，COUNTBLANK函数是用来统计EXCEL2010工作表中空白单元格个数的。空白单元格的意思是没有输入内容的单元格，因此对COUNTBLANK函数来说，要求非常高，单元格中必须是没有任何内容的，某些单元格看上去像是空白的但实际是有内容的是不被COUNTBLANK统计的，如在单元格中有空格或单元格内容是“0”但是在选项中取消了零值的单元格等，

COUNTBLANK函数的格式是：COUNTBLANK(range)。参数range是指定的单元格区域，COUNTBLANK函数一次只能指定一个单元格区域，否则会出错。

EXCEL统计函数―COUNTA 第8篇

下面就以一张某专业10名学生成绩为例,使用函数完成对学生成绩进行统计、分析,并设计一个学生成绩查询,即根据输入的学生姓名或学号,可查询出该学生的成绩信息。

1 成绩统计及课程分析

首先在图1所示的学生成绩统计A3:I12区域中,输入了资源环境与城乡规划管理专业10名学生的学期考试成绩(仅以此专业部分学生的成绩统计为例)。然后分别在J3单元格中创建公式=SUM(E3:I3),在k3单元格中创建公式=ROUND(AVERAGE(E3:I3),1),在L3单元格中创建公式==IF(K3>=90,"优秀",IF(K3>=80,"良好",IF(K3>=70,"中等",IF(K3>=60,"及格","不及格")))),在M3单元格中创建公式=RANK(K3,K$3:K$12),计算高杉同学的成绩总分、平均分、成绩等级和成绩排名,再分别通过拖动J3、K3、L3和M3单元格中的填充柄填充到J4:J12、K4:K12、L4:L12、M4:M12区域中,以计算其他学生的成绩总分、平均分、成绩等级和成绩排名。

在学生统计计算公式中使用了求和SUM、求均值AVERAGE、四舍五入ROUNF、条件判断IF、计算排名RANK等函数。

完成上述成绩统计后,在成绩分析A16:G30区域中,对5门课程成绩进行分析。如图2所示。

分别在B18单元格中创建公式=MAX(E3:E12),在B19单元格中创建公式=MIN(E3:E12),在B20单元格中创建公式=ROUND(AVERAGE(E3:E12),1),在B21单元格中创建公式=ROUND(STDEV(E3:E12),2),在B22单元格中创建公式=COUNTIF(E3:E12,">=60"),在B23单元格中创建公式=B22/COUNT(E3:E12),分别统计分析高等数学这门课程的学生成绩最高分、最低分、平均分、标准差、及格人数和及格率。在B25:B29单元区域中通过创建一个数组公式{=FREQUENCY(E3:E12,$A$25:$A$28)},统计出学生高等数学成绩在0-59,60-69,70-79,80-89以及90分以上5个区间段内的数目。完成高等数学课程成绩分析后,再分别通过拖动B18、B19、B20、B21、B22和B23单元格中的填充柄填充到C18:F18、C19:F19、C20:F20、C21:F21、C22:F22和C23:F23区域中,以计算其他4门课程的最高分、最低分、平均分、标准差、及格人数和及格率。拖动B25:B29单元区域中的填充柄填充到C25:F29区域,计算出其他4门课程成绩分布。在成绩分析计算公式中使用了最大值MAX、最小值MIN、标准偏差STDEV、统计满足条件的单元格个数COUNTIF、计数COUNT、频率函数FREQUENCY等函数。

2 学生成绩查询

在图3所示的成绩查询表中,由输入单元格A3、输出区域A10:M11和提示信息单元格A12组成。若在A3单元格中输入欲查学生的姓名或学号,则在A10:M11区域中自动填充该学生的成绩信息。如果输入正确的学生姓名或学号,在A10:M11区域不仅会显示查询结果,而且在单元格A12中还会显示“对这个结果还满意吧!”。如果没有输入任何内容或输入错误的姓名(如没有此学生、姓名有误等)或学号(如超出学号范围),则在A10:M11区域中显示一行“?”,而且在单元格A12中会显示提示信息“对不起,没有找到这名学生”。

所谓“自动识别”,就是如果满足情况1的条件,就执行命令1;如果是情况2的条件,就执行命令2所以,解决这个问题显然是通过条件判断函数IF来完成的。即:

如果A3中输入的是一个正确的学生姓名或是2009010305201～2009010305210之间的任一学号,则显示正确的查找值。如图3、图4所示。

如果A3中输入的是错误的姓名、其它的汉字和字符,或输入不在学号范围内的数字,则显示一行“?”。如图5所示。

实现上述功能的方法是:创建一个公式,公式最外层通过IF函数判断A3单元格中的内容是否符合条件。里层首先借助ISERROR函数[2](若测试值为任何错误值,如:#N/A、#VALUE、#NAME、#REF、#DIV/0等,则返回True)利用它与HLOOKUP按行查找函数(此函数有四个参数,它是对在第二个参数给定的数据区域的最上面的一行中查找与第一个参数给定的值,然后将返回第三个参数指定行的值,第四个参数指定查找是精确匹配还是近似匹配)的组合便可以判断A3单元格中输入的数据是否是正确的。其函数返回值为TRUE,则证明是错误的输入;若为FALSE,则证明是正确的输入。同时又用到MATCH函数(返回在指定方式下与指定数值匹配的数组中元素的相应位置),找到与A3单元格中输入的姓名或学号相匹配的元素所在位置。

最后完整的操作是:选定A11:M11区域,在编辑栏中输入公式=IF(ISERROR(HLOOKUP(成绩统计及分析!A3:M3,成绩统计及分析!A3:M12,IF(ISERROR(MATCH(A3,成绩统计及分析!C3:C12,0)),MATCH(A3,成绩统计及分析!B3:B12,0),MATCH(A3,成绩统计及分析!C3:C12,0)),0)),"?",HLOOKUP(成绩统计及分析!A3:M3,成绩统计及分析!A3:M12,IF(ISERROR(MATCH(A3,成绩统计及分析!C3C12,0)),MATCH(A3,成绩统计及分析!B3:B12,0),MATCH(A3,成绩统计及分析!C3:C12,0)),0))。由于该公式一次性能返回一行的信息,是一个数组公式的形式。因此,在输入完上述公式后,需按组合键Ctrl+Shift+Enter确认,这时在输入公式的两端自动加上了大括号“{}”,相应的计算结果显示在A11:M11区域中。

该公式最外层是的IF函数,是通过“ISERROR(HLOOKUP(成绩统计及分析!A3:M3,成绩统计及分析!A3:M12,IF(ISERROR(MATCH(A3,成绩统计及分析!C3:C12,0)),MATCH(A3,成绩统计及分析!B3:B12,0),MATCH(A3,成绩统计及分析!C3:C12,0)),0)),”判断条件式的真、假。若条件式为真,表明通过HLOOKUP函数未查找到与A3单元格相匹配的元素,则在A11:M11区域中返回一串“?”。其中HLOOKUP函数的第三个参数指定行又是由一个IF函数实现的。否则,通过“HLOOKUP(成绩统计及分析!A3:M3,成绩统计及分析!A3:M12,IF(ISERROR(MATCH(A3,成绩统计及分析!C3:C12,0)),MATCH(A3,成绩统计及分析!B3:B12,0),MATCH(A3,成绩统计及分析!C3:C12,0)),0))”查找到的与A3单元格中的输入值相匹配的元素所在行的信息。

此外,针对查询结果,在A12单元格会给出提示信息,其公式是=IF(ISERROR(MATCH(A3,成绩统计及分析!B3:B12,0)),IF(ISER-ROR(MATCH(A3,成绩统计及分析!C3:C12,0)),"对不起!没有找到这名学生","这个结果还满意吧!"),"对这个结果还满意吧!")。该公式通过两层嵌套的IF函数,判断若在成绩统计及分析表中的B3:B12区域或C3:C12区域中找到与成绩查询表中A3单元格所输入的学生姓名或学号,则显示提示信息“这个结果还满意吧!”。否则显示信息“对不起!没有找到这名学生”。

学会使用Excel函数真的很有趣,它可以帮助你做很多事。

摘要：电子表格软件Excel具有强大的数据处理和分析功能,其中函数是Excel数据计算和处理的核心工具。该文利用Excel中的不同函数功能,实现对学生成绩的统计、课程分析及信息查询。

关键词：Excel,函数,公式

参考文献

[1]谢柏青,张健青,刘新元.Excel教程[M].2版.北京:电子工业出版社,2003:30.

EXCEL统计函数―COUNTA 第9篇

这里假设考勤分为三种情况，分别为“有加班”、“全勤”、“有缺勤”，其中上班日超过20天为“有加班”，20天的为“全勤”，剩下的为“有缺勤”。表格如图1所示，需要在“出勤类型”一列中显示出出勤状况。

图1

将鼠标指针定位到E4单元格，输入公式“=IF(D4>20,“有加班”,IF(D4=20,“全勤”,“有缺勤”))”(图2,不包括外侧引号)，按下回车键后，相应单元格中即显示出对应的“考勤类型”，

然后再在每条记录的对应单元格中输入相应的公式即可。

图2

这样第一次的工作量可能会大些，但以后需要再次使用时，只要更改考勤数据，“考勤类型”就出来了，对于一些固定类型的表格，如教师判定学生的成绩级别，这种方法还是可以带来很大方便的。

小提示:

1.公司中所用的符号均为英文半角，所有的字符、符号之间没有任何空格。

2.IF最多只支持七层嵌套，不过对付日常应用应该足够了。

3.如果只有两种情况，只需要使用“插入”“函数”命令，然后选择“IF”函数，最后输入相应的判别条件及判别结果就可以了。当然和文中一样直接输入IF函数也是可以的。

EXCEL统计函数―COUNTA 第10篇

1 SUM、AND函数在树种统计中的运用

表1是一个简化了的小班登记表, 要将这个表中的树种按造林单位进行统计, 填到表2“树种统计表”中, 我们该用什么函数进行统计呢?当然, 在这个表中, 我们可以进行人工统计, 因为这个表中的小班数较少, 但在实际工作中, 有时一个年度的造林小班有几百个, 树种种类较多, 应用人工统计工作效率低, 而且统计准确度得不到保证, 这个时候使用函数就可以大大提高工作效率。

1.1 第1步

首先, 我们分析树种统计表的第1个条件是要判断这是哪个村, 我们可以使用公式A2:A11=A14进行判断, A2:A11是我们要判断的范围, A14是判断的条件, 我们要判断在A2到A11的范围内有没有广惠村, 在这里A2:A11必须使用绝对引用, 写成$A$2:$A$11, 这样在进行单元格填充时, 判断的单元格范围就不会发生改变, A14使用混合引用, 写成$A14, 当我们向下填充时, 就会自动变换判断的条件。这个条件判断的结果是:{TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

1.2 第2步

第2个条件我们要对树种进行判断, 可以使用公式 ($F$2:$F$11=B$13) 进行判断, 判断结果为{TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}。

1.3 第3步

然后使用AND函数将这2个条件连在一起, 意思是说, 当同时满足等于广惠村和杨树2个条件时, 则判断为真 (TRUE) , 否则返回否 (FALSE) 。公式为:=AND ( ($A$2:$A$11=$A14) , ($F$2:$F$11=B$13) ) , 但在这里, 我们会发现结果会返回一个错误值, 这是因为AND函数不支持数组的运算造成的, 我们可以使用“*”代替“AND”函数进行运算, 就可以解决这个问题。经过判断后, 公式会返回={1;0;0;0;0;0;0;0;0;0}这样一个判断值, 意思是说只有第1条记录完全符合即是广惠村, 又是杨树这一条件。

1.4 第4步

我们用判断的结果乘以小班的面积, 并使用SUM函数进行求和, 就可以得到各村各村种的统计值, 最终的公式为:=SUM ( ($A$2:$A$11=$A14) * ($F$2:$F$11=B$13) * ($D$2:$D$11) ) 。

1.5 第5步

几点注意事项: (1) 公式输入结束后, 我们需要按住SHIFT+CTRL+ENTER 3键结束, 这样电脑就会把这个公式作为一个数组进行运算, 否则会出错。 (2) 公式的录入需要在英文输入状态下录入, 字母不区分大小写。 (3) 如果需要去除单元格中的“0”值, 可以在格式中进行设置, 以美化表格。

2 SUMIF、COUNT函数在进度统计报表中的应用

表3、表4是一个简化了的实时统计报表, 要根据工程进度随时改变统计数字, 如果使用手工统计, 很有可能一不小心就出错, 如果使用数字函数进行统计, 一方面可以减轻工作量, 同时每次只需更新基础数据, 立刻就能得到统计数字。

2.1 第1步先看看点数是如何统计

在这里我们可以使用COUNT函数进行个数的统计, 公式为=COUNT (1/ ($D$2:$D$11=C14) ) , 这个公式的运算会首先进行类型判断, 看看在D2到D11的范围内哪些单元格是村庄绿化, 得到一个逻辑判断序列:{TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE}, 在这个判断中, 我们可以看到第1个和最后1个为TRUE, 问题是如何将这个逻辑判断值转化为数字呢?在电脑中TRUE被认为是“1”, FALSE被认为是“0”, 我们用“1”去除以这个判断值, 就会得到:{1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1}这样一个结果, 我们用1去除以TRUE, 就可得到一个“1”, 用1除以FALSE, 就会得到一个错误值。这时我们再用COUNT函数去统计个数, COUNT函数有一个特性, 就是会忽略错误值, 我们可以利用这一点, 把各个类型的个数统计出来。

2.2 第2步是规划面积的统计

公式为=SUMIF ($D$2:$D$11, C14, $E$2:$E$11) 这个公式分成3个部分, 第1部分$D$2:$D$11为进行条件判断的区域, 第2部分C14为判断的条件, 第3部分$E$11是说如果条件成立, 则将E列的数字进行相加。

2.3 第3部分是建设面积的统计

公式为=SUMIF ($D$2:$D$11, C14, $F$2:$F$11) , 这个公式的含义和上一步是一样的, 只不过求和的区域变成了F列。

公式编好后, 在以后的工作中, 我们就可以根据工程进展情况随时更新表3的基础数据, 表4则进行自动更新, 从而大大提高工作效率。

3 利用LEFT、MID函数从身份证中提取出生年月并计算年龄

森林防火工作是林业工作的一项重要内容, 建立一支护林员队伍是防火工作所必须的, 一个乡镇约有护林员70～80人, 在录入护林员信息时, 我们可以从身份证中提取护林员的出生年月并通过提取的内容计算护林员的年龄, 并且可以每年进行自动更新, 从而减少人工劳动, 使信息的更新做到及时准确。

3.1 第1步

提取出生年月, 公式:=MID (B2, 7, 8) , 这个函数分成3个部分, 第1个“B2”为护林员的身份证号, 第2部份“7”是说从第七位开始提取, 第3部分的“8”是说一共提取8位, 公式写好后向下填充, 就可以得到护林员的出生年月了。

3.2 第2步

通过提取出来的出生年月, 进一步计算护林员的年龄, 公式为=YEAR (NOW () ) - (LEFT (C2, 4) ) , 前一部分YEAR (NOW () ) 指的是今年的年度, (LEFT (C2, 4) 的意思是说将C列中提取出来的护林员年月日从左到右提取前4位, 也就是护林员的出生年份, 而后两者相减得出护林员的年龄。

4 结束

EXCEL统计函数―COUNTA 第11篇

关键词：产品质量,自动判定统计,方法

质量原则是钢铁工业产品产量计算的基本原则之一, 即一切产品的内在质量和外观质量必须符合规定的质量标准或订货合同规定的技术要求, 经过企业的质量管理部门判定合格的, 方可计算产量, 个别专业有特殊规定者除外。

工业产品质量指标统计是质量管理工作的一项重要内容。产品质量判定是质量统计工作的一个组成部分, 是根据产品质量标准、订货合同规定的技术要求将本企业生产出来的实物产品进行划分, 区分出合格品、次品、废品等质量特征, 以此为依据, 计算合格率等各项质量指标。

过去产品实物质量判定统计方法是通过Excel电子表格进行手工判定统计, 这种方法存在的问题是: (1) 过程繁杂。当原材料成分不稳定, 工序生产不顺, 质量波动大的时候更为突出。 (2) 易出现错判误判, 造成数据不够准确。 (3) 速度缓慢, 报出判定结果滞后的情况时有发生, 不适应生产发展的需要。编制计算机应用程序实现自动判定, 可以做到准确、快速, 但编制应用程序, 对大多数基层统计判定 (以下简称统判) 人员来说, 只局限于熟练操作计算机, 更深层次的应用还有待提高, 统判人员没有经过专业编程知识的培训学习, 难以编制出适用于本企业产品质量判定的应用程序。外委专业人员编程, 又存在费用高、需开发商维护, 自行维护困难等不利因素。目前市场上也没有完全能适用于各企业产量质量判定的软件。如何做到既简单操作, 维护方便, 又能做到准确及时, 针对这个问题, 我反复思考实践, 采用现成的Excel函数, 2009年终于开发出完全可以实现计算机自动判定的运算过程。

注:各牌号生铁的含碳量, 均不作报废依据

表2数据是为了说明过程运行中能自动判定出结果而假设。

1 运用过程

现用《炼钢用生铁标准》 (GB/T 717-1998) 为依据, 昆钢生铁技术协议要求, 用Excel办公软件的IF和AND、OR函数实现自动判定过程。

Excel函数IF、AND、OR语法

IF (logical_test, value_if_true, value_if_false)

Logical_test表示计算结果为TRUE或FALSE的任意值或表达式。

Value_if_true logical_test为TRUE时返回的值。

Value_if_false logical_test为FALSE时返回的值。

AND (logical1, logical2, )

Logical1, logical2, 表示待检测的1到3 0个条件值, 各条件值可为T R U E或F A L S E。

OR (logical1, logical2, )

Logical1, logical2, ...为需要进行检验的1到30个条件, 分别为TRUE或FALSE。

判定炼钢用生铁的内在质量主要是化学成分C, Si, Mn, P, S等, 要求有害元素 (P、S等) 少。昆钢在判定时, Mn含量不作要求, P不作级别判定条件, 特类和一类生铁归为一类生铁组, 二三类铁合并为一组 (表1) 。

根据表2化学成分, 对照质量标准和技术协议, 逐个比对Si、P、S化学成分是否在标准规定的区间内, 如果全部落在质量标准或技术协议规定值内判定为合格品, 其中只要有1个化学成分含量超出质量标准或技术协议规定值, 都属于废品。判定过程如下。

(1) 判定各炉生铁的类别 (考虑到版面, 所列表格未采用Excel格式截图, 便于阅读, 下列语句用汉字说明Excel单元格所代表的含义。下同) 。语句如下, 结果请见表2类别栏。

类别=IF (AND (硅<=1.25, 磷<=0.4, 硫>0.001, 硫<=0.03) , "1",

IF (AND (硅<=1.25, 磷<=0.4, 硫>0.03, 硫<=0.05) , "2",

IF (AND (硅<=1.25, 磷<=0.4, 硫>0.05, 硫<=0.07) , "3",

IF (AND (硅<=1.25, OR (磷>0.4, 硫>0.07) ) , "不合格", ) ) ) )

(2) 判定各炉生铁的牌号, 牌号是按化学成分Si为依据, 语句如下, 结果请见表1牌号栏。

牌号=IF (AND (硅>0.01, 硅<=0.45) , "L04",

IF (AND (硅>0.45, 硅<=0.85) , "L08",

I F (A N D (硅>0.8 5, 硅<=1.2 5) , "L10", ) ) )

(3) 统计各类别、牌号和不合格品的重量, 最后一行为纵栏求和。结果请见表3。

一类铁=IF (AND (硅<=1.25, 磷<=0.4, 硫<=0.03) , 重量, )

二三类铁=I F (A N D (硅<=1.2 5, 磷<=0.4, 硫>0.03, 硫<=0.07) , 重量, )

不合格品铁=IF (AND (硅<=1.25, OR (磷>0.4, 硫>0.07) ) , 重量, )

L04铁=IF (AND (磷<=0.4, 硫<=0.07, 硅<=0.45) , 重量, )

L08铁=IF (AND (磷<=0.4, 硫<=0.07, 硅>0.45, 硅<=0.85) , 重量, )

L10铁=IF (AND (磷<=0.4, 硫<=0.07, 硅>0.85, 硅<=1.25) , 重量, )

(4) 计算合格率、一级品率等质量指标。

上述过程计算机自动运行, 统判人员每天只需录入日期、炉罐号、化学成分、重量字段, 核实准确后无需再做其它操作, 各项质量指标 (如:牌号、类别、不合格品、合格率、一级品率、分类别分牌号的平均化学成分等) 自动生成。

同理, 用人造块矿、铸造用生铁、炼钢、钢加工、耐火材料制品、焦炭等产品现行质量标准或技术协议为依据, 根据企业需要进行适当修改, 就可对各产品质量进行判定统计, 为企业服务。

2 结语

通过Excel电子表格进行自动判定, 大大减少统判人员的劳动强度, 提高准确率, 在现有计算机的基础上无须费用投入, 就可快捷实现自动判定统计。而且简单易操作, 维护方便, 手工判定与自动判定具有无法相比的优点。统判人员完全可以根据本企业产品所采用的质量判定标准或技术要求自行编制、维护, 适用于本企业产品质量判定要求。实现高效、准确、及时的效果。通过使用, 反映效果良好。

参考文献

[1]《中国钢铁工业生产统计指标体系解释》钢协 (2003) 183号.