在Excel中如何使用TEXT函数？

在Excel中如何使用TEXT函数？（通用9篇）

在Excel中如何使用TEXT函数？ 篇1

1.NOW

用途：返回当前日期和时间所对应的序列号，

语法：NOW

参数：无

实例：如果正在使用的是1900日期系统，而且计算机的内部时钟为-1-2812:53，则公式“=NOW()”返回36919.54。

2.TODAY

用途：返回系统当前日期的序列号。

参数：无

语法：TODAY()

实例：公式“=TODAY()”返回2001-8-28(执行公式时的系统时间)。

3.CELL

用途：返回某一引用区域的左上角单元格的格式、位置或内容等信息，该函数主要用于保持与其它电子表格程序的兼容性。

语法：CELL(info_type，reference)

参数：Info_type为一个文本值，指明所需要的单元格信息的类型。Reference表示要获取其有关信息的单元格。如果忽略，则在info_type中所指定的信息将返回给最后更改的单元格。

实例：公式“=CELL(“row”，A20)”返回20，如果A3单元格包含TOTAL，则CELL(“contents”，A3)返回“TOTAL”。

4.ISEVEN

用途：测试参数的奇偶性，如果参数为偶数返回TRUE，否则返回FALSE。

语法：ISEVEN(number)

Number待测试的数值。如果参数值不是整数，则自动截去小数部分取整。

注意：该函数必须加载“分析工具库”方能使用。如果参数number不是数值，ISEVEN函数返回错误值#VALUE!。

实例：公式“=ISEVEN(11)返回FALSE”，=ISEVEN(6)返回TRUE。

5.ISODD

用途：测试参数的奇偶性，如果参数为奇数返回TRUE，否则返回FALSE。

语法：ISODD(number)

参数：Number待测试的数值。如果参数不是整数，则自动截去小数部分取整。

注意：该函数必须加载“分析工具库”方能使用。

实例：公式“=ISODD(19)”返回TRUE，=ISODD(14.5)返回FALSE。

6.AND

用途：所有参数的逻辑值为真时返回TRUE(真);只要有一个参数的逻辑值为假，则返回FALSE(假)。

语法：AND(logical1，logical2，…)。

参数：Logical1，logical2，…为待检验的1～30个逻辑表达式，它们的结论或为TRUE(真)或为FALSE(假)。参数必须是逻辑值或者包含逻辑值的数组或引用，如果数组或引用内含有文字或空白单元格，则忽略它的值。如果指定的单元格区域内包括非逻辑值，AND将返回错误值#VALUE!。

实例：如果A1=2、A=6，那么公式“=AND(A1A2)”返回FALSE。如果B4=104，那么公式“=IF(AND(1

7.IF

用途：执行逻辑判断，它可以根据逻辑表达式的真假，返回不同的结果，从而执行数值或公式的条件检测任务。

语法：IF(logical_test，value_if_true，value_if_false)。

参数：Logical_test计算结果为TRUE或FALSE的任何数值或表达式;Value_if_true是Logical_test为TRUE时函数的返回值，如果logical_test为TRUE并且省略了value_if_true，

则返回TRUE。而且Value_if_true可以是一个表达式;Value_if_false是Logical_test为FALSE时函数的返回值。如果logical_test为FALSE并且省略value_if_false，则返回FALSE。Value_if_false也可以是一个表达式。

实例：公式“=IF(C2>=85，“A”，IF(C2>=70，“B”，IF(C2>=60，“C”，IF(C2< 60，“D”))))”，其中第二个IF语句同时也是第一个IF语句的参数。同样，第三个IF语句是第二个IF语句的参数，以此类推。例如，若第一个逻辑判断表达式C2>=85成立，则D2单元格被赋值“A”;如果第一个逻辑判断表达式C2>=85不成立，则计算第二个IF语句“IF(C2& gt;=70”;以此类推直至计算结束，该函数广泛用于需要进行逻辑判断的场合。

8.OR

用途：所有参数中的任意一个逻辑值为真时即返回TRUE(真)。

语法：OR(logical1，logical2，...)

参数：Logical1，logical2，...是需要进行检验的1至30个逻辑表达式，其结论分别为TRUE或FALSE。如果数组或引用的参数包含文本、数字或空白单元格，它们将被忽略。如

果指定的区域中不包含逻辑值，OR函数将返回错误#VALUE!。

实例：如果A1=6、A2=8，则公式“=OR(A1+A2>A2，

A1=A2)”返回TRUE;而公式“=OR(A1>A2，A1=A2)”返回FALSE。

9.CHOOSE

用途：可以根据给定的索引值，从多达29个待选参数中选出相应的值或操作。

语法：CHOOSE(index_num，value1，value2，...)。

参数：Index_num是用来指明待选参数序号的值，它必须是1到29之间的数字、或者是包含数字1到29的公式或单元格引用;Value1，value2，...为1到29个数值参数，可以是数字、单元格，已定义的名称、公式、函数或文本。

实例：公式“=CHOOSE(2，“电脑”，“爱好者”)返回“爱好者”。公式“=SUM(A1:CHOOSE(3，A10，A20，A30))”与公式“=SUM(A1:A30)”等价(因为CHOOSE(3，A10，A20，A30)返回A30)。

10.INDEX

用途：返回表格或区域中的数值或对数值的引用。函数INDEX()有两种形式：数组和引用。数组形式通常返回数值或数值数组;引用形式通常返回引用。

语法：INDEX(array，row_num，column_num)返回数组中

指定的单元格或单元格数组的数值。INDEX(reference，row_num，column_num，area_num)返回引用中指定单元格或单元格区域的引用。

参数：Array为单元格区域或数组常数;Row_num为数组中某行的行序号，函数从该行返回数值。如果省略row_num，则必须有 column_num;Column_num是数组中某列的列序号，函数从该列返回数值。如果省略column_num，则必须有row_num。 Reference是对一个或多个单元格区域的引用，如果为引用输入一个不连续的选定区域，必须用括号括起来。Area_num是选择引用中的一个区域，并返回该区域中row_num和column_num的交叉区域。选中或输入的第一个区域序号为1，第二个为2，以此类推。如果省略area_num，则 INDEX函数使用区域1。

实例：如果A1=68、A2=96、A3=90，则公式“=INDEX(A1:A3，1，1)”返回68，=INDEX(A1:A3，1，1，1)返回68。

11.LOOKUP

用途：返回向量(单行区域或单列区域)或数组中的数值。该函数有两种语法形式：向量和数组，其向量形式是在单行区域或单列区域(向量)中查找数值，然后返回第二个单行区域或单列区域中相同位置的数值;其数组形式在数组的第一行或第一列查找指定的数值，然后返回数组的最后一行或最后一列中相同位置的数值。

语法1(向量形式)：LOOKUP(lookup_value，lookup_vector，result_vector)

语法2(数组形式)：LOOKUP(lookup_value，array)。

参数1(向量形式)：Lookup_value为函数LOOKUP在第一个向量中所要查找的数值。Lookup_value可以为数字、文本、逻辑值或包含数值的名称或引用。Lookup_vector为只包含一行或一列的区域。Lookup_vector的数值可以为文本、数字或逻辑值。

参数 2(数组形式)：Lookup_value为函数LOOKUP在数组中所要查找的数值。Lookup_value可以为数字、文本、逻辑值或包含数值的名称或引用。如果函数LOOKUP找不到lookup_value，则使用数组中小于或等于lookup_value的最大数值。Array为包含文本、数字或逻辑值的单元格区域，它的值用于与lookup_value进行比较。

注意：Lookup_vector的数值必须按升序排列，否则LOOKUP函数不能返回正确的结果，参数中的文本不区分大小写。

实例：如果A1=68、A2=76、A3=85、A4=90，则公式“=LOOKUP(76，A1:A4)”返回2，=LOOKUP(“bump”，{“a”，1;“b”，2;“c”，3})返回2。

12.MATCH

用途：返回在指定方式下与指定数值匹配的数组中元素的相应位置。如果需要找出匹配元素的位置而不是匹配元素本身，则应该使用MATCH函数。

语法：MATCH(lookup_value，lookup_array，match_type)。

参数：Lookup_value为需要在数据表中查找的数值，它可以是数值(或数字、文本或逻辑值)、对数字、文本或逻辑值的单元格引用。 Lookup_array是可能包含所要查找的数值的连续单元格区域，Lookup_array可以是数组或数组引用;Match_type为数字-1、 0或1，它说明Excel如何在lookup_array中查找lookup_value。如果match_type为1，函数MATCH查找小于或等于 lookup_value的最大数值。如果match_type为0，函数MATCH查找等于lookup_value的第一个数值。如果 match_type为-1，函数MATCH查找大于或等于lookup_value的最小数值。

注意：MATCH函数返回lookup_array中目标值的位置，而不是数值本身。如果match_type为0且lookup_value为文本，lookup_value可以包含通配符(“*”和“?”)。星号可以匹配任何字符序列，问号可以匹配单个字符。

实例：如果A1=68、A2=76、A3=85、A4=90，则公式“=MATCH(90，A1:A5，0)”返回3。

13.VLOOKUP

用途：在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。当比较值位于数据表首列时，可以使用函数VLOOKUP代替函数HLOOKUP。

语法：VLOOKUP(lookup_value，table_array，col_index_num，range_lookup)

参数：Lookup_value为需要在数据表第一列中查找的数值，它可以是数值、引用或文字串。Table_array为需要在其中查找数据的数据表，可以使用对区域或区域名称的引用。Col_index_num为table_array中待返回的匹配值的列序号。Col_index_num为1时，返回 table_array第一列中的数值;col_index_num为2，返回table_array第二列中的数值，以此类推。 Range_lookup为一逻辑值，指明函数VLOOKUP返回时是精确匹配还是近似匹配。如果为TRUE或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值;如果range_value为FALSE，函数VLOOKUP将返回精确匹配值。如果找不到，则返回错误值#N/A。

实例：如果A1=23、A2=45、A3=50、A4=65，则公式“=VLOOKUP(50，A1:A4，1，TRUE)”返回50。

14.COUNTIF

用途：统计某一区域中符合条件的单元格数目。

语法：COUNTIF(range，criteria)

参数：range为需要统计的符合条件的单元格数目的区域;Criteria为参与计算的单元格条件，其形式可以为数字、表达式或文本(如36、“>160”和“男”等)。其中数字可以直接写入，表达式和文本必须加引号。

实例：假设A1:A5区域内存放的文本分别为女、男、女、男、女，则公式“=COUNTIF(A1:A5，“女”)”返回3。

15.INT

用途：将任意实数向下取整为最接近的整数。

语法：INT(number)

参数：Number为需要处理的任意一个实数。

实例：如果A1=16.24、A2=-28.389，则公式“=INT(A1)”返回16，=INT(A2)返回-29。

16.RAND

用途：返回一个大于等于0小于1的随机数，每次计算工作表(按F9键)将返回一个新的数值，

语法：RAND()

参数：不需要

注意：如果要生成a，b之间的随机实数，可以使用公式“=RAND()*(b-a)+a”。如果在某一单元格内应用公式“=RAND()”，然后在编辑状态下按住F9键，将会产生一个变化的随机数。

实例：公式“=RAND()*1000”返回一个大于等于0、小于1000的随机数。

17.ROUND

用途：按指定位数四舍五入某个数字。

语法：ROUND(number，num_digits)

参数：Number是需要四舍五入的数字;Num_digits为指定的位数，Number按此位数进行处理。

注意：如果num_digits大于0，则四舍五入到指定的小数

位;如果num_digits等于0，则四舍五入到最接近的整数;如果num_digits小于0，则在小数点左侧按指定位数四舍五入。

实例：如果A1=65.25，则公式“=ROUND(A1，1)”返回65.3;=ROUND(82.149，2)返回82.15;=ROUND(21.5，-1)返回20。

18.SUM

用途：返回某一单元格区域中所有数字之和。

语法：SUM(number1，number2，...)。

参数：Number1，number2，...为1到30个需要求和的数值(包括逻辑值及文本表达式)、区域或引用。

注意：参数表中的数字、逻辑值及数字的文本表达式可以参与计算，其中逻辑值被转换为1、文本被转换为数字。如果参数为数组或引用，只有其中的数字将被计算，数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略。

实例：如果A1=1、A2=2、A3=3，则公式“=SUM(A1：A3)”返回6;=SUM(“3”，2，TRUE)返回6，因为“3”被转换成数字3，而逻辑值TRUE被转换成数字1。

19.SUMIF

用途：根据指定条件对若干单元格、区域或引用求和。

语法：SUMIF(range，criteria，sum_range)

参数：Range为用于条件判断的单元格区域，Criteria是由数字、逻辑表达式等组成的判定条件，Sum_range为需要求和的单元格、区域或引用。

实例：某单位统计工资报表中职称为“中级”的员工工资总额。假设工资总额存放在工作表的F列，员工职称存放在工作表B列。则公式为 “=SUMIF(B1:B1000，“中级”，F1:F1000)”，其中“B1:B1000”为提供逻辑判断依据的单元格区域，“中级”为判断条件，就是仅仅统计B1:B1000区域中职称为“中级”的单元格，F1:F1000为实际求和的单元格区域。

20.AVERAGE

用途：计算所有参数的算术平均值。

语法：AVERAGE(number1，number2，...)。

参数：Number1、number2、...是要计算平均值的1～30个参数。

实例：如果A1：A5区域命名为分数，其中的数值分别为100、70、92、47和82，则公式“=AVERAGE(分数)”返回78.2。

21.COUNT

用途：返回数字参数的个数。它可以统计数组或单元格区域中含有数字的单元格个数。

语法：COUNT(value1，value2，...)。

参数：Value1，value2，...是包含或引用各种类型数据的参数(1～30个)，其中只有数字类型的数据才能被统计。

实例：如果A1=90、A2=人数、A3=″″、A4=54、A5=36，则公式“=COUNT(A1:A5)”返回3。

22.COUNTA

用途：返回参数组中非空值的数目。利用函数COUNTA可以计算数组或单元格区域中数据项的个数。

语法：COUNTA(value1，value2，...)

说明：Value1，value2，...所要计数的值，参数个数为1～30个。在这种情况下的参数可以是任何类型，它们包括空格但不包括空白单元格。如果参数是数组或单元格引用，则数组或引用中的空白单元格将被忽略。如果不需要统计逻辑值、文字或错误值，则应该使用COUNT函数。

实例：如果A1=6.28、A2=3.74，其余单元格为空，则公式“=COUNTA(A1:A7)”的计算结果等于2。

23.COUNTIF

用途：计算区域中满足给定条件的单元格的个数。

语法：COUNTIF(range，criteria)

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

24.LARGE

用途：返回某一数据集中的某个最大值。可以使用LARGE函数查询考试分数集中第一、第二、第三等的得分。

语法：LARGE(array，k)

参数：Array为需要从中查询第k个最大值的数组或数据区域，K为返回值在数组或数据单元格区域里的位置(即名次)。

实例：如果B1=59、B2=70、B3=80、B4=90、B5=89、B6=84、B7=92，，则公式“=LARGE(B1，B7，2)”返回90。

25.MAX

用途：返回数据集中的最大数值。

语法：MAX(number1，number2，...)

参数：Number1，number2，...是需要找出最大数值的1至30个数值。

实例：如果A1=71、A2=83、A3=76、A4=49、A5=92、A6=88、A7=96，则公式“=MAX(A1:A7)”返回96。

26.MEDIAN

用途：返回给定数值集合的中位数(它是在一组数据中居于中间的数。换句话说，在这组数据中，有一半的数据比它大，有一半的数据比它小)。

语法：MEDIAN(number1，number2，...)

参数：Number1，number2，...是需要找出中位数的1到30个数字参数。

实例：MEDIAN(11，12，13，14，15)返回13;MEDIAN(1，2，3，4，5，6)返回3.5，即3与4的平均值。

27.MIN

用途：返回给定参数表中的最小值。

语法：MIN(number1，number2，...)。

参数：Number1，number2，...是要从中找出最小值的1到30个数字参数。

实例：如果A1=71、A2=83、A3=76、A4=49、A5=92、A6=88、A7=96，则公式“=MIN(A1:A7)”返回49;而=MIN(A1:A5，0，-8)返回-8。

28.FIND

用途：FIND用于查找其他文本串(within_text)内的文本串(find_text)，并从within_text的首字符开始返回find_text的起始位置编号。此函数适用于双字节字符，它区分大小写但不允许使用通配符。

语法：FIND(find_text，within_text，start_num)，参数：Find_text是待查找的目标文本;Within_text是包含待查找文本的源文本;Start_num指定从其开始进行查找的字符，即within_text中编号为1的字符。如果忽略start_num，则假设其为1。

实例：如果A1=软件报，则公式“=FIND(“软件”，A1，1)”返回1。

29.LEFT或LEFTB

用途：根据指定的字符数返回文本串中的第一个或前几

个字符。此函数用于双字节字符。

语法：LEFT(text，num_chars)或LEFTB(text，

num_bytes)。

参数：Text是包含要提取字符的文本串;Num_chars指定

函数要提取的字符数，它必须大于或等于0。Num_bytes按字

节数指定由LEFTB提取的字符数。

实例：如果A1=电脑爱好者，则LEFT(A1，2)返回“电

脑”，LEFTB(A1，2)返回“电”。

30.LEN或LENB

用途：LEN返回文本串的字符数。LENB返回文本串中所有

字符的字节数。

语法：LEN(text)或LENB(text)。

参数：Text待要查找其长度的文本。

注意：此函数用于双字节字符，且空格也将作为字符进

行统计。

实例：如果A1=电脑爱好者，则公式“=LEN(A1)”返回

5，=LENB(A1)返回10。

31.MID或MIDB

用途：MID返回文本串中从指定位置开始的特定数目的字

符，该数目由用户指定。MIDB返回文本串中从指定位置开始

的特定数目的字符，该数目由用户指定。MIDB函数可以用于

双字节字符。

语法：MID(text，start_num，num_chars)或MIDB

(text，start_num，num_bytes)。

参数：Text是包含要提取字符的文本串。Start_num是文

本中要提取的第一个字符的位置，文本中第一个字符的

start_num为1，以此类推;Num_chars指定希望MID从文本中

返回字符的个数;Num_bytes指定希望MIDB从文本中按字节返

回字符的个数。

实例：如果a1=电子计算机，则公式“=MID(A1，3，2)”

返回“计算”，=MIDB(A1，3，2)返回“子”。

32.RIGHT或RIGHTB

用途：RIGHT根据所指定的字符数返回文本串中最后一个

或多个字符。RIGHTB根据所指定的字节数返回文本串中最后

一个或多个字符。

语法：RIGHT(text，num_chars)，RIGHTB(text，

num_bytes)。

参数：Text是包含要提取字符的文本串;Num_chars指定

希望RIGHT提取的字符数，它必须大于或等于0。如果

num_chars大于文本长度，则RIGHT返回所有文本。如果忽略

num_chars，则假定其为1。Num_bytes指定欲提取字符的字节

数。

实例：如果A1=学习的革命，则公式“=RIGHT(A1，2)”

返回“革命”，=RIGHTB(A1，2)返回“命”。

33.TEXT

用途：将数值转换为按指定数字格式表示的文本。

语法：TEXT(value，format_text)。

参数：Value是数值、计算结果是数值的公式、或对数值

单元格的引用;Format_text是所要选用的文本型数字格式，

即“单元格格式”对话框“数字”选项卡的“分类”列表框

中显示的格式，它不能包含星号“*”。

注意：使用“单元格格式”对话框的“数字”选项卡设

置单元格格式，只会改变单元格的格式而不会影响其中的数

值。使用函数TEXT可以将数值转换为带格式的文本，而其结

果将不再作为数字参与计算。

实例：如果A1=2986.638，则公式“=TEXT(A5，“#，

##0.00”)”返回2，986.64。

34.VALUE

用途：将表示数字的文字串转换成数字。

语法：VALUE(text)。

参数：Text为带引号的文本，或对需要进行文本转换的

单元格的引用。它可以是Excel可以识别的任意常数、日期或

时间格式。如果Text不属于上述格式，则VALUE函数返回错误

值#VALUE!。

注意：通常不需要在公式中使用VALUE函数，Excel可以

在需要时自动进行转换。VALUE函数主要用于与其他电子表格

程序兼容。

实例：公式“=VALUE(“￥1，000”)”返回1000;=VALUE

(“16:48:00”)-VALUE(“12:00:00”)返回0.2，该序列数等于4

在Excel中如何使用TEXT函数？ 篇2

自2008年3月1日起, 我国个税免征额从1600元/月上调至2000元/月。根据每月应纳税所得额的不同, 采用9级超额累进税率计算, 计算相对烦琐。目前, 大多数财务软件都集成了计算所得税的功能, 无力购买财务软件的企业一般借助于Excel, 少数企业使用手工计算的方法。

二、 在Excel中计算个人所得税通常使用的方法

在Excel中, 通常使用4层IF函数嵌套的方法, 设计逐层判别适用税率的公式。但是对一般用户而言, 公式复杂, 当政策变动时, 不易修改。

少数企业使用Excel中的VBA编辑器, 设计自定义函数或加载宏来辅助个人所得税的计算, 简便高效。但是, 计算过程不透明, 同样存在不易修改的问题。

三、 一个计算所得税的简便函数——定位函数Vlookup

函数Vlookup可以在指定区域实现模糊查找定位, 这个被大多数人忽略的函数正好可以实现所得税9级超额累进税率计算的要求。步骤如下:

(1) 新建一个名为“个人所得税.xls”的Excel工作表, 在工作簿Sheet 1中录入数据 (见表1) 。

(2) 在B13单元格录入公式:= VLOOKUP ( E13, A2:D10, 3) ;

在B14单元格录入公式:= VLOOKUP ( E13, A2:D10, 4) ;

在E13单元格录入公式:= MAX ( E12 – B12, 0) ;

在E14单元格录入公式:= E13 * B13 – B14。

(3) 全部公式正确录入后, E14显示计算结果“55”。即应纳税金额为2 800元时, 应纳所得税55元。修改E12单元格的数值, 可在E14单元格获得相应的计算结果。

(4) 如果政策变动, 可在表中直接修改单元格B12 (起征点) 、单元格A2至A10 (分级间断点) 和单元格C2至C10 (税率间断点) 中的数据, 其他位置不必改动。

此函数简单方便, 在本例中实现了个人所得税单值的计算。

四、 利用综合公式实现批量计算

将B13、E13和B14单元格中的公式, 综合到E14单元格中, 同时修改参数引用方式。对初学者而言, 有一定难度。步骤如下:

(1) 在工作簿Sheet 2中录入数据 (见表2) 。

(2) 在D2单元格录入公式:

(3) 用公式复制功能将D 2单元格中的公式复制到D 3至D 9单元格。

五、利用模拟运算表实现批量计算

模拟运算表为初学者提供了一个替代综合公式实现批量计算的途径。步骤如下:

(1) 回到工作簿Sheet 1中, 保持原有的A 1至D 14单元格中的内容, 录入数据 (见表3) 。

(2) 选中C 18至D 26区域, 然后点击“数据”菜单中的“模拟运算表”, 弹出对话框如图1所示。

(3) 对话框中的第1个参数不添, 第2个参数添$E$12, 然后点击确定。D 19到D 26自动计算出对应的“应缴所得税”。

(4) 右键单击18行的行标, 弹出菜单中选择“隐藏”。

(5) 注意, 使用模拟运算表功能时, 必须保证表1和表3在同一个工作簿中。

六、总结

本文提供的方法, 创新点在于“定位函数”的应用, 难点在于综合公式中“参数引用”。用Excel计算个人所得税有多种方法, 使用者要根据自身的兴趣和水平来选择适合自己的方法。

摘要：计算个人所得税的方法有很多, 本文提供了一套“易学”、“易用”的新方法。应用Excel中的定位函数Vlookup, 结合必要的“参数引用”设置方法和模拟运算表功能, 实现个人所得税的单值计算和批量计算。

关键词：所得税,定位函数,模拟运算表

参考文献

在Excel中如何使用TEXT函数？ 篇3

关键词:ExcelCONCATENATE函数MIDB函数 IF函数

1引言

Excel是一款得到广泛应用的优秀电子表格软件,在Excel的“分析工具库”中包括了许多非常实用的函数,利用这些函数可以帮助我们方便地解决许多Excel实际应用中的难题。但它的疑难和奥妙也一直困扰着无数用户,致使许多用户尽管使用了多年仍不能熟练掌握该软件的某些具体功能,下面详细介绍Excel的几个函数的使用方法和技巧,希望能对使用者有所帮助。

2利用身份证号码获取公民出生日期

在单位上整理人事信息时,常常即需要输入身份证号码,又要输入出生年月日,这样即工作量较大而且易出现错误。由于身份证号码内含有相关信息,我们可以使用Excel函数从身份证号号码中直接提取出生年月日,这样即减少工作量又可提高准确度。

从图1中我们可以发现我国现行的身份证号码的规律,从左起第7—14个字符代表了公民的出生年月日,我们可以使用MIDB函数从身份证号码的特定位置,提取出生年月日,再使用CONCATENATE函数将所提出来的文字合并起来,就可以得到相对应的出生年月日。

在图2中是已经制作好的一张人员信息表,所需的三个MIDB函数操作对象存放在D3单元格中,分别从左起第7个字符开始提取,即第7个字符开始连续取4个字符即得到“年”,第11个字符开始连续取2个字符得到“月”,第13个字符开始连续取2个字符得到“日”,最后由CONCATENATE函数将所有字符合并起来。即得到我们所需要的信息。

从以上分析可以得出公式:=CONCATENATE(MIDB(D3,7,4),"年", MIDB(D3,11,2),"月", MIDB(D3,13,2),"日")

CONCATENATE是将若干个文字项合并至一个文字项中的函数。其语法格式是:CONCATENATE(text1,text2……)。

MIDB从一个文本字符串的指定位置开始,返回指定个数的子字符串。其语法格式是:MIDB(text,start_num, mum_bytex)

用户要注意的是:CONCATENATE函数和MIDB函数的操作对象均为文本,所以存放身份证号码的单元格必须设置为文本格式,然后再输入身份证号。

3用IF函数计算成绩等级

在教师的日常工作中,会遇到要将学生的考试成绩按实际考试分数转换成相应成绩等级的情况。利用IF函数就能够迅速完成对学生的考试分数转换成相应成绩等级的工作。

成绩等级评定一般的标准为:考试分数大于或等于85分为优,分数大于或等于80分为良,分数大于或等于70分为中,分数大于或等于60分为及格,低于60分为不及格,没有参加考试的不划等级。

从图3中可以看出分数存放在D列,计算结果存入E列。用Excel的IF函数计算公式如下:

=IF(D3>=85,"优",IF(D3>=80,"良",IF(D3>=70,"中",IF(D3>=60,"及格",IF(ISNUMBER(D3),"不及格",IF(ISBLANK(D3),"未考试"))))))

上面的公式中主要是利用IF条件函数的嵌套。IF函数是根据对指定条件的逻辑判断的真假结果,返回相对应的内容的条件函数。利用此函数逻辑计算出真假值,会返回不同的结果,从而可达到筛选数据的目的。其语法格式是: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(也就是假或错误)时返回的值。

公式中的第二个IF语句是第一个IF语句的参数,第三个IF语句则是第二个IF语句的参数,以此类推。如果第一个逻辑判断表达式D3>=85为TRUE(真),则E3单元格被填入“优”;如果第一个逻辑判断表达式D3>=85不成立,则计算第二个IF语句IF(D3>=70,以此类推直到计算结束。公式中ISNUMBER函数在D3为空时返回FALSE(假),接着执行最后一个IF語句,否则在D3单元格中填入“不及格”。ISBLANK函数D3为空时返回TRUE(真),D3单元格被填入“未考试”。使用ISNUMBER函数和ISBLANK函数,可防止某个未参加考试的学生(成绩为空时)给他评定为“不及格”的情况。

4结束语

本文通过实例,详细介绍了CONCATENATE函数、MIDB函数以及IF函数的使用技巧。对使用者熟练掌握理解这几个函数有所帮助。

参考文献:

[1]施威铭研究室著,完全掌握OFFICE XP中文版标准教程,中国青年出版社,2002年。

[2][美]John Walkenback著,尚红昕,刘玲等译,Excel应用技巧宝典,电子工业出版社,2006年。

EXCEL中函数的使用 篇4

各位专家、评委好！我说课的题目是《EXCEL中函数的使用》，下面我将从教材、教法、学法以及教学程序设计等方面加以说明。

一．说教材

1．教材分析

本节课是初中信息技术第五章第三节内容。利用公式和函数对表格数据进行计算是Excel中核心内容之一。一方面，它与EXCEL前面所讲的单元格地址紧密联系，且是它的延续；另一方面，对于整个EXCEL的数据处理而言，它又是一个基础。在实际生活当中有很多运算都可以通过EXCEL的公式和函数来解决，实用性很强。

2、教学目标：

知识目标:(1)理解函数的概念(2)掌握函数的输入方法并且能够熟练使用函数进行数据的统计与分析

能力目标: 培养学生自主学习、举一反

三、分析问题、解决问题的能力和技能，获得解决实际问题的经验。

情感目标: 培养并加强学生学习计算机知识的兴趣及主动投入的学习精神，通过对问题的解决，让学生享受成就感，树立自信心。

3、教学重点：

掌握函数的组成及参数定义。

掌握函数的输入方法，运用函数进行数据的统计与分析。

4、教学难点：

函数的组成及参数定义。

5、课时安排：一课时

6、教具准备：多媒体教学

二、学生分析

本课的学习要求学生对数学函数有相对熟悉的基础，学生已经在数学课上学习了函数的相关知识，但是对掌握的情况不完全相同，在学习本课时，他们更想知道函数在Excel中是什么样子，对Excel的表格有怎么样的作用。

三、教学方法

教法：这是节理论性和操作性强都很强的课程，在理解的基础上学生能够动手实践操作才能算是达到教学目标。但这节课里存在着细节性问题，容易引起操作的失败，这就需要老师的引导。因此，在本节课中，主要采取讲练结合的方式进行，1、演示讲解法 为了使学生更快、更容易地学好本节课的内容，我特别设计了几个有针对性的、能够突破教学重点和难点的实例。通过一边讲解教学内容，一边完成相应的课堂实例的操作演示，这样可以给学生留下深刻直观的印象，让学生更快、更容易地掌握新知识。

2、任务驱动法 信息技术教学大纲中明确规定：知识及技能的传授应以完成典型“任务”为主，因而在教学过程中，我通过引导学生完成一系列“任务”来实现对本节课知识点的掌握，获得解决实际问题的经验，强化“实践出真知”的科学思想，同时也可以使学生在完成任务后产生成就感，培养其自信心。

学法：在本课的教学中，要求学生按以下两种方式来完成教学任务：

1.自主探索法：对于每个操作任务，鼓励学生思考探究，独立尝试操作，自行分析问题并寻求解决问题的方法，举一反三，让学生从“学会”转化为“会学”。2.协作学习：指导学生相互学习、相互交流、相互探讨，也可以让自学得好的学生做示范操作，将教师单纯的教变成学生间的学，并形成全体学生相互促进，在解决问题的同时，培养学生的合作意识和协作精神。

四、教学过程

（一）引入：

提问：如果在一个加法或是求平均值运算中参与运算的数据个数很多时，如何快速的计算出结果来？（目的：让学生体会到如果参与运算的数据个数很多时，公式会写得很长，输入起来很麻烦，从而使学生对新课内容产生兴趣，激发学生的求知欲。）给出答案（可以是学生给出，也可以是教师给出），引入新课：《EXCEL中函数的使用》。

（二）新课内容 1．函数的概念

(1)、函数是指用于进行某种目的的计算式子，是系统已经定义好的公式。

(2)、函数的作用：简化公式的输入。

(3)、函数的组成函数由函数名、参数表及括号构成。通常，函数的格式如下：= 函数名（参数1，参数2，„）以sum函数和average函数为例分析函数的组成，特别是参数的设定。2．函数的使用方法

直接输入函数：(1)单击欲输入函数的单元格；(2)向当前的单元格输入函数；(3)按回车键结束。

使用插入函数按钮输入函数：(1)打开Excel工作表，在工作表中选择需要输入函数的单元格；(2)切换至“公式”面板，在“函数库”选项板中单击“插入函数”按钮，弹出“插入函数”对话框，在“搜索函数”文本框中输入自己准备使用的函数名，然后单击“转到”按钮，系统将自动搜索相应函数，在“选择函数”下拉列表框中选择该函数；(3)单击“确定”按钮，弹出“函数参数”对话框，在Numberl右侧的文本框中输入需要计算的单元格区域;(4)单击“确定”按钮，即可统计出数据结果.(三)课堂练习

利用函数完成电脑上已给材料的总成绩以及平均成绩

（四）小结 引导学生小结本节课所学内容，小结本节课的收获。

（五）作业布置

完成“函数.xls”工作簿中“作业”工作表中的相关数据的计算。(六)教学反思

函数式Excel中学习的重点内容，教师通过讲解和演示，学生通过学习和实际操作，对函数的掌握更加深刻，大部分掌握了函数的使用技巧，收到良好的效果。板书

Excel中函数的使用

一、函数的概念

二、函数作用

在Excel中如何使用TEXT函数？ 篇5

举例题设

例如有两个表，分别是：A表

和B表

例子要求在B表的B26到B31单元格中自动填入A表中A列与B表A列相同值(完全相同，不分大小写)所对应的C列的值

步骤详解

1、先选中B表B26，然后选择公式：

弹出函数编辑框：

2、把这4个自定义项全填上就OK，上往下第一个为：

可用鼠标直接选中B表A26，这是返回B26时赖以遵循的搜索项，编辑框中会自动输入语法，

3、第二个自定义项为：

直接鼠标选择A表中整个A:C列，这是搜索范围。如果要圈定一个特定范围，建议用$限定，以防之后复制公式时出错。

4、第三个为：

本例中要返回的值位于上面圈定的搜索范围中第3列，则键入数字3即可。

5、最后一个：

通常都要求精确匹配，所以应填FALSE，也可直接键入数字0，意义是一样的。

6、确定后，可以看到B表B26中有返回值：

7、最后，向下复制公式即可。大功告成!检查一下看，是不是很完美?

注意事项

在第2项步骤中由于是最终是要返回一个列表，所以也可以直接选中A26:A31甚至整个A列(如果没有其他干扰项的话)。

在Excel中如何使用TEXT函数？ 篇6

一、教学目标分析

“EXCEL中函数的使用”是高等教育出版社出版的《计算机应用基础》第四章的内容。EXCEL中的函数很多，功能也非常强大，如能掌握一些常用的函数，将给日常的数据处理带来很大的便利。在本案例中，我将结合学生的生活和学习实际创设一个合适的问题情境，激发学生在活动过程中掌握应用信息技术解决问题的思想与方法，鼓励学生将所学的信息技术积极地应用到生产、生活乃至信息技术革新等各项实践活动中去，让学习成为他们自己的需要。在学习方式上，我强调学生在信息技术学习中的主体性，倡导主动探究学习。通过本节课的学习，应该达到以下目标：

1、知识与技能

通过任务的解决，掌握几个较常用函数（SUM、AVERAGE、MAX、MIN等）的名称、功能与用法，进一步理解单元格的引用。

2、过程与方法

通过任务的解决，学生们不仅学到本节课的知识，更重要的是体会到探索新知的过程和学习方法的培养（如自主探究、小组协作、查看帮助等），这对他们今后的学习将带来正迁移效应。

3、情感、态度与价值观

通过任务的解决，学生获得成就感，增强自信心，并加强学生间的友谊，增强自觉运用信息技术解决一些实际问题的意识。

二、教学内容分析

本节课的教学内容的实践性较强，主要是围绕着Excel函数来展开教学的，其主要内容是Excel函数的名称、功能、用法。教学的重点放在： ① Excel函数的功能； ② Excel函数的用法 教学的难点是： ①函数的单元格区域选择

②Excel函数在实践中的运用与拓展；

－1－

三、学生学习状态分析

在本节课中，学生应采取自主学习和互相协作学习相结合的方法，这样既可以提高学习的效果，也有利于培养学生的合作精神和人际交往能力。Excel作为一种在工作生活中应用十分普遍的软件，操作性比较强，如果能够结合有趣的案例，学生在学习的过程中，一定会表现出浓厚的学习兴趣，学习的积极性比较高，课堂气氛也会比较好。

四、教学过程

教师出示一张学校演讲比赛的得分表，提出任务：谁得了冠军？ 师：大家谈谈处理策略。

学生们利用已有的数学、EXCEL知识与平常的处理经验，得出：求出每人的平均分或总分，谁得分最高谁就是冠军。处理方法有自定义公式或自动求和法。大家统一意见后，开始着手工作。学生独自实践操作解决问题，也可以互相交流，协作讨论各自的方法。教师巡视，并进行个别指导（教导在这一过程中引导学生掌握求和、求平均数函数）。

生：王静同学得了冠军！

设计意图：这一环节目的是复习巩固并深化上一节课的内容：单元格引用、自定义公式、等内容的应用，同时引入自动求和、求平均函数，分析、比较它们之间的优劣及其在统计学上的意义。学生通过分析比较不同的方法，对和种函数的利用取长补短，丰富了处理问题的途径，增长了处理问题的信心，培养了发散思维，并内化为自己解决问题的能力。一波刚平，一波又起。

生：老师，在实际处理中，是不是要去掉每位选手得分中的最高分和最低分？ 师：提得很好。谁能说一说，我们为什么要去掉最高分和最低分呢？

生：使人为等各方面误差因素尽量减少到最低，让得分更合理，符合统计学意义。设计意图：整合数学知识，使学生知其然更知其所以然，从而引出新的任务，激起学生控索的欲望。

师：怎么办？大家能否利用已有的知识进行解决？

设计意图：充分体现”以学生为主体“的教学思想，放手让学生们自己去尝试，培养大家自主探索与实践的能力和不怕困难的精神。

学生们个个摩拳擦掌，想一试高低。但没过多久，他们发现自己的方法很麻烦，有点灰心。为什么呢？原以为用一下公式就可以解决，但遇到这个问题却不灵了。因为每位选手的最低分和最高分所在的单元格不在同一列（没有规律），所以不能利用填充的办法来解决，要一个一个剔去。学生处于无奈和焦噪状态，这时教师适当点拔。

－2－ 师：其实在EXCEL中就提供有求极值（最大值和最小值）的函数MAX（）与MIN（），大家可以试试看它们的功能。

点拔之后，学生有了头绪。有学生打开“粘贴函数”，在里面查找MAX（）与MIN（）并尝试它们的用法与功能；有学生打开EXCEL的帮助系统，在里面查找这两个函数的介绍与范例并实践。不多一会儿，就有学生找到了解决的方法，慢慢地其他学生也陆续解决。

在此过程中，有交流、有协作、有个别指导，体现在教师是课堂的引导者、组织者的角色。学生们不仅掌握了MAX（）与MIN（）的用法，更学到如何自主探索新知识的方法和途径，增强了自主学习的能力与信心。正所谓“授之以鱼，不如授之以渔。” 师：同学们，经过这次统计，谁得了冠军？ 生：张军强！

学生们表情轻松愉悦，有成就感。教师再次搜集、演示学生们的各种处理方法，总结评价各种方法的特点，判断它们的优劣及适用情形，开阔大家的视野。

师：我们现在可以做一个比赛的评分系统了，下一次的校园演讲比赛评分就由你们来处理吧！

生：还要老师多帮助啊！对了，还有亚军、季军呢！

五、教学反思

整堂课在一个一个任务破解中展开，学习气氛既紧张又热烈。学生们既有碰到难题时的困惑，又有体验到解决之后成功的喜悦，最后留下一个“悬念”，为下一节课埋下伏笔。基于真实问题情境的学习，更能吸引学生的注意力与自主学习的愿望，让他们不会感觉为了学习而学习。

1、真正体现以学生为中心，让学生在开放的学习环境中合作交流

无论是设计问题、制定计划、解决问题还是总结评价，都围绕学生进行，学生是活动的主体。学习又是开放的，在确定了需要研究的问题后，学生直接操作活动材料进行活动和学习，无论是学习的内容还是学习的结果，可能会超出教师的预期——学生可能会就一个问题提出许多种解决方案，也可能会提出许多新的、有意义的、需要进一步研究的问题。让学生在这种环境下真正体会到学习不是一个简单的、线性的过程，而是复杂的，有时甚至是难以预测的。

2、与学生的实践能力相结合

学习是基于真实情境的，所创设的问题是学生关注的，且与当前和今后的工作、生活密切相关的问题。这给学生提供了一个运用所学知识的虚拟平台。甚至可以说是实际平台，－3－ 缩短了知识与应用之间的联系时，学生的积极性会被调动起来，他们会尽力学习、掌握相关的信息，并运用所学知识来解决问题，从而提高了实践能力。这有助于加深学生对知识的理解及迁移应用。

3、促进学生掌握基本的学习策略和学习方法

基于问题的学习是学生自主参与学习的过程。学生愿意学习（具有学习的动力）、主动学习（成为学习的主人），并且通过与同伴、教师的合作与交流，通过不断地反思，调整自身习惯的学习方式与方法，在解决问题的过程中获得解决问题的能力，从而更加善于学习，成为独立的学习者。基于真实问题情境中的学习，在实施过程中也会有一些困难，特别是对教师提出了更高的要求，要有娴熟驾驭课堂的能力和良好的教学设计方案。

首先，我们花费更多的时间。我们告诉学生怎么做，可能只要几分钟，而让学生自己去分析，得出方案，找到解决的方法途径（手段、工具）却很费时，有时不能实现当堂课教学目标。如果碰到不配合的学生，更加如此。其次，难于形成合适的问题。提炼一个合适的问题是重要的开始，而这个问题又是以问题情境的形式展现。选择一个合适的问题情境，既要融合所需要学习的知识，又要激发学生的学习兴趣，实在不是一件容易的事，需要教师们共同协作

－4－

Excel函数使用方法实用举例 篇7

一、常用函数的参数介绍

1. ABS函数

函数名称:ABS

主要功能:求出相应数字的绝对值。

使用格式:ABS (number)

参数说明:number代表需要求绝对值的数值或引用的单元格。

2. COLUM N函数

函数名称:COLUMN

主要功能:显示所引用单元格的列标号值。

使用格式:COLUMN (reference)

参数说明:reference为引用的单元格。

3. D AT E函数

函数名称:DATE

主要功能:给出指定数值的日期。

使用格式:DATE (year, month, day)

参数说明:year为指定的年份数值 (小于9999) ;month为指定的月份数值 (可以大于12) ;day为指定的天数。

4. D AY函数

函数名称:DAY

主要功能:求出指定日期或引用单元格中的日期的天数。

使用格式:DAY (serial_number)

参数说明:serial_number代表指定的日期或引用的单元格。

5. M ID函数

函数名称:MID

主要功能:从一个文本字符串的指定位置开始, 截取指定数目的字符。

使用格式:MID (text, start_num, num_chars)

参数说明:text代表一个文本字符串;start_num表示指定的起始位置;num_chars表示要截取的数目。

6. SUBT OT AL函数

函数名称:SUBTOTAL

主要功能:返回列表或数据库中的分类汇总。

使用格式:SUBTOTAL (function_num, ref1, ref2, ...)

参数说明:Function_num为1到11 (包含隐藏值) 或101到111 (忽略隐藏值) 之间的数字, 用来指定使用什么函数在列表中进行分类汇总计算 (如图6) ;ref1, ref2, ……代表要进行分类汇总区域或引用, 不超过29个。

7. 函数名称:SUM

主要功能:计算所有参数数值的和。

使用格式:SUM (Number1, Number2……)

参数说明:Number1、Number2……代表需要计算的值, 可以是具体的数值、引用的单元格 (区域) 、逻辑值等。

8. SUM IF函数

函数名称:SUMIF

主要功能:计算符合指定条件的单元格区域内的数值和。

使用格式:SUMIF (Range, Criteria, Sum_Range)

参数说明:Range代表条件判断的单元格区域;Criteria为指定条件表达式;Sum_Range代表需要计算的数值所在的单元格区域。

9. T EX T函数

函数名称:TEXT

主要功能:根据指定的数值格式将相应的数字转换为文本形式。

使用格式:TEXT (value, format_text)

参数说明:value代表需要转换的数值或引用的单元格;format_text为指定文字形式的数字格式。

二、常用函数应用举例

1. ABS函数

应用举例:如果在B2单元格中输入公式:=ABS (A2) , 则在A2单元格中无论输入正数 (如100) 还是负数 (如-100) , B2中均显示出正数 (如100) 。

特别提醒:如果number参数不是数值, 而是一些字符 (如A等) , 则B2中返回错误值“#VALUE!”。

2. COLUM N函数

应用举例:在C11单元格中输入公式:=COLUMN (B11) , 确认后显示为2 (即B列) 。

特别提醒:如果在B11单元格中输入公式:=COLUMN () , 也显示出2;与之相对应的还有一个返回行标号值的函数——ROW (reference) 。

3. D AT E函数

应用举例:在C20单元格中输入公式:=DATE (2003, 13, 35) , 确认后, 显示出2004-2-4。

特别提醒:由于上述公式中, 月份为13, 多了一个月, 顺延至2004年1月;天数为35, 比2004年1月的实际天数又多了4天, 故又顺延至2004年2月4日。

4. M ID函数

应用举例:假定A47单元格中保存了“我喜欢天极网”的字符串, 我们在C47单元格中输入公式:=MID (A47, 4, 3) , 确认后即显示出“天极网”的字符。

特别提醒:公式中各参数间, 要用英文状态下的逗号“, ”隔开。

5. SUM IF函数

应用举例:如图7所示, 在D64单元格中输入公式:=SUMIF (C2:C63, “男”, D2:D63) , 确认后即可求出“男”生的语文成绩和。

特别提醒:如果把上述公式修改为:=SUMIF (C2:C63, “女”, D2:D63) , 即可求出“女”生的语文成绩和;其中“男”和“女”由于是文本型的, 需要放在英文状态下的双引号 (“男”、“女”) 中。

6. T EX T函数

应用举例:如果B68单元格中保存有数值1280.45, 我们在C68单元格中输入公式:=TEXT (B68, “$0.00”) , 确认后显示为“$1280.45”。

特别提醒:format_text参数可以根据“单元格格式”对话框“数字”标签中的类型进行确定。

7. SUBT OT AL函数

应用举例:如图7所示, 在B64和C64单元格中分别输入公式:=SUBTOTAL (3, C2:C63) 和=SUBTOTAL103, C2:C63) , 并且将61行隐藏起来, 确认后, 前者显示为62 (包括隐藏的行) , 后者显示为61, 不包括隐藏的行。

特特别别提提醒醒::如如果果采采取取自自动动筛筛选选, , 无无论论ffuunncc--ttiioonn__nnuumm参参数数选选用用什什么么类类型型, , SSUUBBTTOOTTAALL函函数数忽忽略略任任何何不不包包括括在在筛筛选选结结果果中中的的行行;;SSUUBBTTOOTTAALL函函数数适适用用于于数数据据列列或或垂垂直直区区域域, , 不不适适用用于于数数据据行行或或水水平平区区域域。。

随随着着企企业业对对数数据据分分析析的的需需求求越越来来越越高高, , EExx--cceell在在很很多多企企业业里里都都是是必必备备的的工工具具。。掌掌握握基基本本的的函函数数会会让让我我们们在在实实用用EExxcceell时时变变的的得得心心应应手手。。

摘要：Excel是Office办公软件的组件之一, 是常用的电子表格软件, 现在的计算机中基本都已安装。它一直以来在统计和财务报表方面得到了广泛的应用, 但是对Excel的一个基本部件——“函数”的使用比较少。

关键词：Excel函数,功能,举例

参考文献

[1]王移芝, 罗四维.大学计算机基础教程.北京:高等教育出版社, 2004.

[2]杨振山, 龚沛曾.大学计算机基础 (第四版) .北京:高等教育出版社, 2004.

[3]冯博琴.大学计算机基础.北京:高等教育出版社, 2004.

在Excel中如何使用TEXT函数？ 篇8

我正在计算一个问卷调查的有效回答，但是数据实在太多了...有办法高效率地数清它们吗?

在这种情况下，您应该使用 COUNTA 函数。COUNTA 是一个计算非空单元格的数目的函数。

很简单的，让我们来试试看。首先，选择用来显示结果的单元格，然后输入“=COUNTA”。

我的操作是否正确?

正确。接下来，输入您想要计数的单元格范围。

选择范围时，不要包括标题或文本。在此图表中，列 A 和行 3 为标题。

如果空白单元格中存在空格，它将会被视为一个非空单元格，所以，操作的时候要细心。

哦，我知道了。问卷调查答案的填写范围为左上角 B4 到右下角 M53，那么…

在这种情况下，输入“=COUNTA(“，然后输入“B4:M53)”，然后按 Enter 键，

输入了函数的那个单元格中显示 450。因此，这就是有效回答的数量。的确计算很方便…

如果更改了公式的范围，“=COUNTA(B4:M53)”，您可以基于季度或客户 ID 来进行统计。函数是非常有用的。我可以使用函数来统计回答中的“A”或“B”的数量吗?

当然可以。统计带有特殊值的单元格的数量，可以使用 COUNTIF 函数。

使用 COUNTIF 函数时，在范围之后，请输入搜索条件。举例来说，如果想要统计输入了“A”的单元格数量，则输入“=COUNTIF(B4:M53,“A”)”。

请牢记，输入搜索条件时，需要将它括在双引号中。

我输入了公式并按了 Enter 键…它显示“220”，那么这就是回答“A”的数量吗?这真是太方便了，我不用担心在统计过程中出错了。

在Excel中如何使用TEXT函数？ 篇9

特定条件的平均分计算

1. AVERAGE与IF结合解决0值问题

我们都知道, 计算平均分一般使用AVERAGE函数就可以实现, 如果考生参加考试考分为0, 单独使用AVERAGE函数计算平均分时0值将参与计算, 影响班级平均分值。当不需要计入0值即排除0分计算全班平均分时, 结合AVERAGE函数与IF条件语句构建数组公式就可以实现。假设工作表中某班语文成绩在D列区域为D3:D88, 则数组公式为:{=AVERAGE (IF ( (D$3D$88) >0, D$3:D$88) ) }。

公式说明:利用IF语句判断区域大于0值数据, 条件为真时返回相应区域数值, 条件为假时则返回逻辑值FALSE, 最后利用AVERAGE函数对生成的数组进行平均值计算。注意逻辑值FALSE不参与平均值计算, 数组公式中的一对大括号不能手工输入, 必须通过按“Ctrl+Shift+Enter”组合键实现输入。公式中使用混合引用实现向右自动填充, 从而完成其他列科目平均分计算, 增加了公式的灵活性。

2.AVERAGE与OFFSET和COUNTA函数结合解决排除“最后5名”考生成绩问题

由于班级排名最后5位学生的成绩对班级整体的平均分影响较大, 就是俗话说的“拖班级后腿”, 有时我们需要排除此种因素, 只计算去掉排名最后的5位学生各科成绩后的班级平均分。逐一删除各班最后5名考生成绩操作比较麻烦, 结合AVERAGE、OFFSET和COUNTA函数就可以保证在全班考生成绩完整的情况下计算出不含“最后5名”的平均分。假设某班成绩表已经按总分从高到低作降序排列, 姓名在C列区域为C3C88, 语文成绩仍在D列区域为D3:D88, 则公式为:=A VERAGE (OFFSET (D$3, , , (COUNTA ($C$3:$C$88) -5) , ) ) 。

公式说明:COUNTA函数计算姓名列不为空值的个数即全班考生总人数, 再减5 (去掉最后5名) , 利用OFFSET函数以D3单元格为基点引用除最后5名学生的语文成绩的区域即D3:D83, 最后利用AVERAGE函数计算该区域的平均分即不含“最后5名”考生的平均分。公式中姓名列使用绝对引用, 同样, D$3单元格使用混合引用, 使公式可以向右自动填充完成其他科目成绩的同样计算。当然, 根据实际情况也不仅仅只限最后5名, 只需对公式稍作修改即可完成。

及格率与优秀率计算

1. 一般情况下的及格率和优秀率计算标准及方法

及格率的计算方法是:满分为150分的学科成绩大于等于90分为及格, 满分为100分的学科成绩大于等于60分为及格。及格率就是及格人数除以总人数所得数值的百分比。

优秀率的计算方法是:满分为150分的学科成绩大于等于120分为优秀, 满分为100分的学科成绩大于等于80分为优秀。优秀率就是优秀人数占总人数的百分比。

一般情况下的及格率和优秀率结合COUNTIF函数和COUNTA函数就可以完成计算。同上, 仍以计算语文学科及格率为例, 及格率公式为:=COUNTIF (D3D88, ">=90") /COUNTA (D3:D88) , 其他科目的及格率和优秀率同理, 只需更改公式中的计算条件即可。另外, 使用百分比显示结果, 请设置“单元格格式” (数字→分类→百分比) , 其他不再赘言。

2. 不含“最后5名”考生的及格率与优秀率

在计算不含“最后5名”考生的及格率与优秀率时, 可以使用S U M函数结合O F F S E T函数和C O U N T A函数构建数组公式, 同上, 仍以计算语文学科为例, 及格率数组公式为:{=S U M (-- (OFFSET (D$3, , , (COUNTA ($C$3:$C$88) -5) , ) >=90) ) / (COUNTA ($C$3:$C$88) -5) }。

公式说明:公式分子部分是先通过OFFSET函数引用一个除最后5名学生的语文成绩区域 (D3:D83) , 用此区域比较出一个大于等于90的数组, 该数组是一个逻辑值数组, 用“-”负号进行两次数学运算, 使逻辑值变为数值 (TRUE为1, FLASE为0) 。最后用SUM函数计算数组之和, 即大于等于90的人数。数组公式分母部分不再说明。同理, 计算优秀率需修改数组公式中的计算条件。

排名及前N名人数统计

1.使用RANK函数进行多班级间的成绩排名

一般我们把一个班级的成绩单独存放在一个工作表中, 而把一个年级的所有班级成绩保存在一个工作薄中。这里为了清晰方便地使用公式引用计算, 我们最好把表名修改为与班级对应的阿拉伯数字, 如工作表名“1“存放高一 (1) 班的成绩。例如, 高一年级有14个班, 则需要建立14个工作表, 这里需要注意, 要确保14个班的成绩存放在连续的工作表中即表名依次从1到14, 且每个工作表中的各科成绩及总分列都在相同区域, 这样才可以使用“引用运算符”, 按工作表的排列顺序将起始表名和终止表名进行连接, 从而实现多班级间总分排名。比如我们需要统计1~7班考生总分在这7个班级中的排序情况, 假设7个工作表中每班总分都在M列且区域为M3:M88, 我们可以在不合并这7个工作表的情况下对总分进行排名, 数组公式为:{=RANK (M3, '1:7'!$M$3:$M$88) }。另外, EXCEL有一个隐藏功能, 可以很方便地生成对除当前工作表之外的左右两侧多个工作表相应区域的引用, 而不用考虑所引用的工作表是否连续。以上述公式为例, 公式修改后为:{=RANK (M3, '*'!$M$3:$M$88) }。

2. 结合RANK、COUNTIF、INDIRECT函数完成前N名人数统计

假如上述公式{=RANK (M3, '1:7'!$M$3:$M$88) }的计算结果都在7个班级工作表中的P3:P88区域, 如图1所示。

进一步计算各班在7个班中总排名中进入前200名的人数分布, 使用COUNTIF函数和INDIRECT函数可以完成, 在此使用辅助单元格 ($A$8) 来引用名次所在列区域 (如上图所示:$P$3:$P$88) 。以计算1班为例, 公式为:=COUNTIF (INDIRECT (C$3&"!"&$A$8) , "<="&$C2) 。

使用INDIRECT函数和辅助单元格的好处在于公式可以向右拖动自动填充计算其他班, 一旦引用区域发生变化只需更改辅助单元格内容即可, 也使公式精短, 易于阅读和修改。

3. 利用SUM和INDIRECT函数进行四校前N名人数统计

我们经常会比较几个学校之间总分人数分布, 就和每年高考后各学校高考上线人数一样。以我校“四校联考”为例, 利用SUM和INDIRECT函数结合辅助单元格构建数组公式就可以统计四校前N名人数分布情况。数组公式为:{=SUM ( (INDIRECT ($G$4) =B$2) * (INDIREC T ($H$4) <=$A3) ) }, 效果图如图2所示。

公式说明: (1) 使用此公式前需要在同工作薄中建立一个名为“高一”的工作表来存放四个学校高一年级的成绩, 且A列为单位代码 (例如:一中单位代码可以使用数字“1”表示, 二中“2”……) , 总分排名在O列, 如有变动则需要更改辅助单元格内容以便公式正确引用相应区域。 (2) 为保证公式能正确引用和向右、向下自动填充, 图中学校名称和“前‘N’名”虽是汉字显示, 其实为数值, 例如, “前10名”实为数字10, 这里需要自定义“单元格格式” (数字→自定义类型) , 使统计表标题清晰且保证公式正确引用。自定义“单元格格式”操作如图3所示。

4. 各班单科优秀生进入四校前N名人数统计

在分析完四校的总体情况后, 我们还要对本校某年级各班各科进入四校前N名人数做进一步详细地分析。如图4所示, 高二文科分数 (以“/”划分文理) 为四校第100名分数, 理科为四校第300名分数, 人数为各班超过相应分数的人数。

以计算1班总分进入人数为例, 单元格C3中公式为:--IF (ISNUMBER (C$2) , COUNTIF (INDIRECT (C$2&"!"&$Q2) , ">="&--R I G H T ($B 4, L E N ($B 4) -F I N D ("/", $B4) ) ) , COUNTIF (INDIRECT (C$2&"!"&$Q2) , ">="&--LEFT ($B4, FIND ("/", $B4) -1) ) ) 。

公式说明: (1) 图中B列总分、语文、数学、英语的文理成绩以“/”划分, 格式是文本, 取“/”左右的数值需要用到LEFT和RIGHT函数, 定位“/”在字符串中位置需使用FIND函数, 取左 (文科) 还是取右 (理科) 分数需要知道该班级是理科班还是文科班, 这里有个技巧, 表中第2行标明“班”的为理科班, 未标明“班”的为文科班。理科班实为自定义的数值 (例如1班—3班和9班、10班) , 文科班为文本型的数字 (4、5、6、7、8) , 文本型数字输入时先输入一个单引号再输入数字。如此一来, 使用IF条件判断语句和ISNUMBER函数就可以依据班级单元格格式自动判断文理, 一旦班级文理发生变化只需更改数值类型即可, 方法简单、公式灵活, 省去反复修改公式这一烦琐工作, 增强了公式的智能性和通用性。 (2) 单元格有阴影部分为理科物理、化学、生物, 没有“/”分隔, 则C7单元格的公式可以简化为:=--IF (ISTEXT (C$2) , 0, COUNTIF (INDIRECT (C$2&"!"&$Q5) , ">="&$B7) ) 。同理, 可以计算出其他科目。 (3) 合计各科总人数时也要首先判断文理, 这里主要使用SUM函数、IF语句和ISNUMBER函数完成。例如, N 3单元格内计算理科总分进入总人数数组公式为:{=SUM (--IF (ISNUMBER ($C$2:$L$2) , $C3:$L3) ) }, 而M3单元格内计算文科总分进入总人数公式则可以为:{=SUM ($C3:$L3) -$N3}, 显示结果仍是自定义数字格式以标明文理。如果不想显示计算结果0值, 在“工具”→“选项”→“视图”中取消“零值”对勾。

5. 第N名成绩计算

临近高考, 对全市高三“第一次大练习”成绩分析更为详细, 其中有一项就是文理各科全市第N名成绩计算。如图5所示, 单元格B3内的公式为:{=INDEX (文科!$D:$J, MAX ( ( (文科!L$2:L$20000) = (MAX ( (文科!L$2:L$2 0 0 0 0<=$A 3) * (文科!L$2:L$2 0 0 0 0) ) ) ) * (R O W (文科!L$2:L$20000) ) ) , COLUMN (A1) ) }。

公式说明: (1) 公式用到INDEX、MAX、ROW、COLUMN四个函数, 使用此公式的前提是要在工作薄内建立一个名为“文科”工作表存放全市文科成绩, 且六科和总分成绩在D至J列, L至R列用RANK函数分别对六科和总分排名, 假设全市文科总考生人数不超过20000人, 即公式中跨表引用数据区域最大行号为20000。 (2) 首先最内层MAX函数找到语文第100名, 如果出现多人并列而没有出现第100名 (这里使用RANK函数排名) 就找小于100的最大名次, 生成的逻辑值数组与ROW函数结合定位到第100名所在的行号, 用COLUMN函数生成列号, 最后用INDEX定位到相应学科的第100名成绩。 (3) 此公式没有使用辅助单元格, 可以向右、向下自动填充完成各科和总分第N名成绩, 如果修改公式引用区域最好使用“Ctrl+H”替换公式中的内容。

FREQUENCY函数统计区间分数段人数

在成绩分析中, 我们通常需要划分数段分析各科不及格人数、及格人数和优秀人数, 如图6所示为语文的各分数段人数分布情况, 公式为:{=FREQUENCY ($D$3:$D$65, RIGHT ($J$3:$J$4, 3) -0.1) }。

公式说明:FREQUENCY函数是向上舍入进行统计的, 该函数按N个段点划分为N+1个区间, 结果生成N+1个统计值。如本例所示, 语文两个段点是90和120, 统计生成三个值, 区间段分别是小于等于89.9为不及格, 大于等于90小于等于119.9的为及格, 大于等于120的为优秀。公式中用RIGHT函数对“区间段”取文本值再减去0.1得出段点数值。注意, FREQUENCY函数返回一个垂直数组, 即一维行数组, 该公式生成三个统计结果, 输入公式时选中从开始的单元格区域L3:L5, 按下“Ctrl+Shift+Enter”输入联合数组公式。另外, 该公式中的第二个参数也可以直接写成间隔数组:{=FREQUENCY ($D$3:$D$65, {89.9, 119.9}) }。

助学金、奖学金的计算方法

根据我校制定的助学金、奖学金发放条例, 依据期中、期末考试后将对全校总分前10名的考生放奖学金, 发放标准是第1名100元, 第2名至第5名是90~60元 (依此递减10元) , 第6名至第10名都是50元。班级助学金只限发给班级前3名, 依次是40元、30元、20元。需要注意的是, 对已经获得年级奖学金的学生将不再发放班级助学金且他的班级名次仍然保留。

这里需要建立一个工作表来存放年级奖学金和班级助学金发放标准, 然后定义两个名称分别是NJ代表年级奖学金标准数据区域和BJ代表班级奖学金标准数据区域。操作方法是“插入”→“名称”→“定义”, 如图7所示。

名称的定义实际上就是对某单元格区域的命名, 能方便我们经常引用一个固定不变的数据区域, 也使公式简单利于修改。

名称定义好后, 就可以对符合助学金与奖学金发放标准的学生进行计算, 年级奖学金和班级助学金都是使用IF判断语句和LOOKUP函数计算的。要首先判断是否已获得年级奖学金, 如果获得显示“已获年级奖”;如果没有, 则进行班级内排名, 以14班的前三名为例, 如图8所示, 公式为:{=IF (N (E787) >0, "已获年级奖", SUM (N (IF ($A$2:$A$1000=A787, $C$2:$C$1000, 0) >C787) ) +1) }。