第4章在公式中使用函数

第4章在公式中使用函数

Excel最强大的功能之一在于数据的计算，而计算又依赖于公式和函数。公式是计算、分析数据的等式，它能对数据进行算术运算和逻辑运算。函数则是Excel的内置公式，能够减少编写公式的时间，提高工作效率。因此，全面了解Excel的函数知识对掌握好公式的使用是十分重要的。本章：

1、工作表函数

2、文本函数

3、与时间和日期有关的函数

4、计算与求和

5、与查找有关的函数

6、数据库与列表

7、复杂计算1

4.1Excel函数1、什么是函数Excel中的函数就是一些预定义的公式，可以通过引入到工作表中进行简单或复杂的运算。典型的函数（如SUM）可以有一个或多个参数，并能返回一个计算结果。

函数具有以下功能：

＊

可以简化公式（求和，求平均值等）

＊

实现其他方法无法实现的计算（如最大值，最小值）

＊

保证公式运行，否则无法进行计算

＊

提高编辑任务的速度

＊

允许“有条件地”运行公式，使之具备基本判断能力。

IF(A1<10000,A1*5%,A1*7.5%)

Excel有300多个函数。如果你感觉还不够，还可以通过第三方提供商购买其它专业函数，甚至还可以使用VBA创建自定义函数。22、函数参数的类型

从前面的例子中，我们发现所有的函数都需要使用括号。括号中的内容就是函数的参数。函数的结果取决于参数的使用方法。一个函数可以：

＊

不带参数（必须加上一个空括号）

＊

一个参数

＊

固定数量的参数

＊

不确定数量的参数

＊

可选参数如果一个函数有多个参数，需要使用逗号分隔。函数的参数可以是：名称、整行、整列（B:B）、文字值、表达式（公式中的公式）、其它函数、数组等。例如：3

◆=RAND（）；得到0和1之间的随机数

◆=LARGE（A1:A100，3）；在范围A1：A100中得到第3大数

◆=SUM（A1:A20）=>SUM（Sales）；名称作为参数

◆=SUM（B:B）；整列作为参数

◆=SQRT（225）；文字值作为参数

◆=LEFT（A1，1）；若A1中包含了文本“Budget”，则有结果就是“B”

◆=SQRT（（A1^2）+（A2^2））；表达式作为参数

◆=SIN（RADIAND（B9））；函数RADIAND作为参数，嵌套函数

◆

=OR（A1={1，3，5}）

；数组作为参数

◆

=IF（A1=1，TRUE，IF（A1=3，TRUE,IF（A1=5，TRUE，FALSE）））

；使用了IF嵌套

43、在公式中输入函数的方法有：

＊

手工输入函数

＊

使用“插入函数”对话框输入函数

＊

函数录入的其它技巧

◆

注意：（1）当输入一个函数的时候，Excel一般可以把函数名转换成大写。因此当录入函数时，完全可以一直使用小写。如果当按下Enter键的时候，Excel并没有把文本转换成大写，这时录入的内容并没有被确认为函数，这就意味着拼写有错，或函数无法使用（未安装）。

（2）灵活使用工具栏上的一些函数按钮；让Excel的“插入函数”为你服务。（3）永远记住函数的帮助（或按F1键）。5＊

财务函数＊

日期及时间函数＊

数学及三角函数＊

统计函数＊

查看和引用函数＊

数据库函数＊

文本函数4、Excel中函数的种类：注意：1、财务函数都是与货币有关的，与现值、期值、支付、利率、周期和期限等有关。2、三角函数都采用弧度作为角的单位，使用RADIANS函数可以将角度转换成弧度；3、统计函数可以计算平均值、模数、标准偏差和方差等；4、数据库函数主要用于总结符合某一具体标准的列表。5、文本函数可实现大小写的转换、或抽取某个字符位置上以某个字符开始的字符串等。6注意：1、逻辑函数可以进行简单的判断；2、信息函数可以帮助你确定单元格中数据的类型；3、工程函数可以处理复杂的数字，并且在不同的计数体系和测量体系中进行转换，但必须安装AnalysisToolPak加载宏；＊

逻辑函数＊

信息函数＊

工程函数＊

用户自定义函数＊

其它函数类＊

AnalysisToolPak函数＊

易失函数7Excel处理数据的能力是最好的。然而在处理文本方面它也有独到之处。正如前面所了解的，Excel可以帮助你输入文本，作为行列的标题、客户名称地址、部件号等等。并可以按照你的要求，使用公式处理包含文本的单元格。本节将介绍很多公式示例，包括如何使用函数处理文本，有些公式还可以实现意想不到的功能。

1、有关文本的概念

当向单元格输入数据的时候，Excel立即会进行处理，确认你输入的是公式、编号（包括日期或时间）或其它内容。所有内容Excel都可以视为文本。

一个单元格最高可以容纳32000个字符。但能显示出来的字符数是有限大的。如有些函数不能处理文本字符串超过255个字符。

Excel可以区分数字和文本。灵活使用“选择性粘贴”命令,可以使Excel把非数字单元格处理成实际值格式。

4.2处理文本8

2、文本函数

Excel具有一个很好的工作表函数类，可以处理文本。为了方便，Excel的“插入函数”对话框将最常用的函数放在文本类中。当然还有一些其它函数也可以处理文本，它们却被放在其它类中。如，ISTEXT函数就在“插入函数”对话框的信息类中。

大多数文本函数不只是局限在文本中使用。换言之，这些函数也可以用在包含值的单元格中。Excel和其它的电子表格不同的是它具有很好的通用性，可以把数字作为文本处理，也可以把文本作为数字处理。

确定单元格中是否包含文本=ISTEXT(A1）；若A1包含文本，则返回TRUE=TYPE(A1）；若A1包含文本字符串，则返回2（文本格式的代码号）

使用字符代码

=CODE（“A”）或=CODE（“AbbeyRoad”)；返回参数A的字符代码65

=CHAR(65)；返回字符A

假设单元格A1中包含字符A（大写），下面的公式将返回字符a（小写）。

CHAR(CODE(A1)+32)9在Window系统中，Excel使用标准的ANSI字符集。ANSI字符集包括255个字符，编号1~255。CODE和CHAR函数只能在ANSI字符集下工作。这些函数不能处理双字节的Unicode字符串。

灵活利用“符号”对话框，可以查找到一些特殊的字符，如笑脸符号等。确定是否两个字符相同

=A1=A2；此比较对大小写不敏感

=EXACT(A1，A2）

；准确比较

=EXACT(“ZERO”,“ZERO”)；返回FALSE连接两个或多个单元格

=A1&A2

=A1&“,”&A2

=A1&CHAR(44)&CHAR(32)&A2

；逗号的代码44，空格的代码32

=“ThelargestvalueincolumnCis”&MAX(C:C)

；返回C列最大的值10把格式化的值显示成文本

=“Thenetprofitis”&A41

=“Thenetprofitis”&TEXT(B3，“$#,##0.00”)

；货币化格式

=“报告打印时间”&TEXT(NOW(),“mmmmd,yyyyath:mmam/pm”)

；结果报告打印时间是“当前日期和时间”把格式化货币值显示成文本

=“总价：”&DOLLAR(1287.367,2)11重复字符或字符串

=REPT(“HO”,3)；返回HOHOHO=REPT(“~”,20);返回一条曲线创建一个文本直方图

=REPT(“HO”,3)；返回HOHOHO=REPT(“~”,20);返回一条曲线填充数字=(A1&REPT(“*”,24-LEN(A1)))；

=REPT(“*”,24-LEN(A1))&A1；12删除多余的空格和非打印字符

TRIM；删除数据前后的所有空格CLIAN；删除字符串中的所有非打印字符计算字符串中的字符

LEN(A1)；返回A1中的字符个数，空格也为字符改变文本大小写

UPPER；将文本全部转换成大写LOWER；将文本全部转换成小写PROPER；将文本转换成“适当”的大写（首字母大写）从字符串中提取字符

LEFT；从一个字符串的起始返回确定数量的字符RIGHT；从字符串的结尾返回确定的字符MID；在字符串中间的任意位置上返回确定的字符13替换文本SUBSTITUTE；替换字符串中的具体文本REPLACE；替换字符串中具体位置上的文本

在字符串中进行查找和搜索FIND；查找另一个文本字符串中的字串，返回该字串的起始位置

不支持通配符比较SEARCH；查找另一个文本字符串中的该字串，返回字串的起始位置

它可以进行通配符比较通配符有两个：问号？：匹配任何单独字符星号*：匹配任何字符序列假设A1中包含文本“AnnualProfitFigures”=REPLACE(A1,SEARCH(“Profit”,A1),6,”Loss”)或=SUBSTITUTE（A1,”Profit”,”Loss”)14

3、高级文本公式

计算单元格中具体字符计算单元格中字串的出现频率把数字改成序数词确定列数为列字母从具体路径中提取文件名提取字符串的第一个词替换字符串的最后一个词提取字符串第一个词以外的所有词提取名字的名、中间名和姓删除名字中的称谓计算单元格中词的数量自定义VBA文本函数15

4.3日期和时间函数在Excel中，日期就是一个简单的数字。更准确地说，日期就是一个“序列号”，它记录着1900年1月0日以来的有关天数的数字。这个序列号的1对应着1900年1月1日，序列号2对应着1900年1月2日等等。这个系统可以使用公式处理日期。例如，可以创建一个公式来计算两个日期之间的天数。

Excel2003支持1900年1月0日到9999年12月31日这个范围的日期（序列号=2958465）

正确理解序列号很有意思。例如：每天的分钟数（1440分），每天的秒数（86400秒）等。在使用日期和时间时，应注意一下输入的格式。16

常见日期和时间函数日期和时间数据是Excel能够处理的数据类型之一。日期和时间在Excel中的运算主要是通过日期时间类函数来实现的。常见的日期时间类函数主要有：DATE（）、DAY（）、DATEVALUE（）、YEAR（）和MONTH（）等。DATE（）语法表达式为：DATE（YEAR,MONTH,DAY）其参数可以是具体的数值，也可以是单元格地址的引用。DATEVALUE（）语法表达式为：DATEVALUE（date-text）其参数必须为文本字符串，否则返回错误值#VALUE。例如DATEVALUE（“2010-11-9”）17DAY（）功能是计算一个序列数据所代表的日期在当月的天数。HOUR（）,MINUTE（）它们只有一个参数，表示需要计算的时间。它们的功能是将参数转换为小时或分钟。YEAR（）,MONTH（）它们只有一个参数，表示需要计算的日期。它们的功能是返回日期所代表的年分数和月份数。TODAY（）,NOW（）它们的功能是返回系统当前日期、当前时间。WEEKDAY（）它的功能是返回日期所对应星期数。

日期和时间函数应用见示例工作簿18在使用Excel进行工作时，经常会在工作表中遇到各种各样的计数与求和等数学统计问题。1、基本计数公式计数使用的基本公式都是相对简单的，但体现了Excel计数函数的能力。统计单元格汇总数量统计名为DATA的范围的单元格数量

=ROW(DATA)*COLUMNS(DATA)；行数乘以列数统计空单元格

=COUNTBLANK(DATA)

=COUNTBLANK(A:A)；返回A列空单元格的数量=COUNDBLANK(SHEET1!1:65536)；返回SHEET1工作表空单元格的数量

4.4计数与求和19非空单元格计数=COUNTA(DATA)数字单元格计数

=COUNT(DATA)包含日期和时间的单元格都被视为数字单元格，但包含逻辑值的单元格不能认为是数字单元格。非文本单元格计数

{=SUM(IF(ISNONTEXT(DATA),1))}；数组公式文本单元格计数

{=SUM(IF(ISTEXT(DATA),1))}；数组公式逻辑值计数

{=SUM(IF(ISLOGICAL(DATA),1))}；数组公式20一个范围中的错误值

Excel有3个函数，可以确定单元格中是否存在错误。

ISERROR如果单元格包含任何错误值，将返回TRUE。

ISERR如果单元格包含除了#N/A以外的任何错误值，将返回TRUE。

ISNA如果单元格包含#N/A错误值，将返回TRUE。例如：{=SUM(IF(ISERROR(DATA),1))}；数组公式，返回错误值的总数{=SUM(IF(ISERR(DATA),1))}；数组公式，返回除#N/A错误值的总数=COUNTIF(DATA,“#DIV/0!”)212、高级计数公式这是一些根据不同类型的筛选条件来统计工作表的单元格数量，因此这些公式脚复杂。使用COUNTIF函数进行单元格计数=COUNTIF(DATA,12)；返回包含12的单元格数量

=COUNTIF(DATA,”<0”)=COUNTIF(DATA,”<>0”)=COUNTIF(DATA，A1)=COUNTIF(DATA,”*”)=COUNTIF(DATA,”？？？”)=COUNTIF(DATA,TODAY())22使用多筛选条件进行单元格计数

=COUNTIF(DATA,”>0”)-COUNTIF(DATA,”>12”)；统计出包含值大于0小于等于12的单元格数量。等价于{=SUM((DATA>0)*(DATA<=12))}；”*”号是AND

=COUNTIF(DATA,1)+COUNTIF(DATA,3）+COUNTIF(DATA，5）；统计出包含值1,3,5的单元格数量,”+”号是OR.等价于{=SUM(COUNTIF(DATA,(1,3,5)))}；

也可综合使用OR和AND筛选条件进行计数。当输入数组公式时要按Ctrl+Shift+Enter结束，括号是Excel自动插入的。23出现频率最高项目的计数=MODE(DATA)；返回的是范围DATA中出现频率最高的值。

=COUNTIF(DATA,MODE(DATA))

；返回的是范围DATA中出现频率最高的值的次数

DATE函数只能处理数字值。它可以简单地忽略包含文本的单元格

{=MAX(COUNTIF(DATA,DATA))}

；返回最高出现频率项目（文本或值）的次数。24确定文本出现率计数=COUNTIF(DATA,TEXT)；返回的是范围DATA中出现文本的单元格数量，它不区分大小写。

{=SUM(IF(EXACT(DATA,TEXT),1))}

；返回的是范围DATA中出现文本的单元格数量，它区分大小写。

253、求和公式这些公式可以是非常简单的，也可以是使用多重条件求和的相对复杂的数组公式。范围内所有单元格求和=SUM(DATA)

=SUM(A1:A9,C1:C9,E1:E9,G1:G9)=SUM(A:A)=SUM(SHEET1,1:65536)；对SHEET1中所有值求和，为避免循环引用的错误，这个公式必须放在其它表中。

SUM函数功能很多。它的参数可以是数字值、单元格、范围、数字的文本表述（可以翻译为值）、逻辑值和嵌入函数等。如：=SUM(B1,5,”6”,,SQRT(4),AA1:A5,TRUE)；有效的26累计求和=SUM(H$2:H2)；见示例工作簿

=IF(H2<>””,SUM(H$2:H2,””)N个最大值的和{=SUM(LARGE(DATA,{1,2,3,4,5,6,7,8,9,10}))}若将LARGE换成SMALL可求10个最小值之和。274、使用单个条件求条件和函数：SUMIF(range，criteria，sum-range)功能：根据指定条件对若干单元格求和。其中：

range表示用于条件判断的单元格区域；

criteria表示确定哪些单元格将被相加求和的条件，其形式可以是数字、表达式或文本；

sum-range是需要求和的实际单元格。若忽略了这个参数，函数使用第一个参数确定的范围。

见示例工作表。5、使用多个条件求条件和SUMIF函数不能使用多重条件，需要考虑数组公式。如：{=SUM（(B2:B10,”刹车片“）&（C2:C10,”<=“DATE(2007,3,1)*D2:D10)}288.Sumif函数：条件求和

表达式：Sumif(range,criteria,sum-range)参数range表示用于条件判断的单元格区域；参数criteria表示确定哪些单元格将被相加求和的条件，其形式可以是数字、表达式或文本；参数sum-range是需要求和的实际单元格。

功能：根据指定条件对若干单元格求和。9.

Countif函数：按条件统计表达式：Countif(range,criteria)

参数range表示的是需要计算单元格个数的区域，参数criteria表示确定的条件功能：Countif是统计指定单元格区域中满足条件的单元格的个数。291、数组求和：{=SUM((G12:G21>100)*G12:G21)}[公式说明]：本公式为数组公式，可以对G12:G21区域中大于100的数据进行求和，而排除小于等于100的数据。输入公式时必须按【Ctrl+Shift+Enter】组合键结束，否则无法得到正确结果。[使用注意]：1、公式中“G12:G21>100”部分表示求和条件，后跟实际求和区域“G12:G21”。如果有多个条件，可一并罗列出来。例如求大于100且小于115的数据之和，公式如下:=SUM（(G12:G21>100)*(G12:G21<115)*G12:G21)2、此数组公式只适用于单个区域求和，如果有多个区域，只能用多个SUM求和，然后相加。例如对G12:G21和H12:H21区域中大于100的数汇总，公式如下:=SUM(SUM((G12:G21>100)*G12:G21),SUM((H12:H21>100)*H12:H21))3、对于SUM函数的数组公式，可以用SUMPRODUCT函数来代替，从而将数组公式转换成普通公式。例如本案例的公式用SUMPRODUCT函数后，普通公式如下:=SUMPRODUCT((G12:G21>100)*G12:G21)补充几个求和函数30

2、数据类型转换求和：=SUM(VALUE(H5),H6:H10,J5:J10,L5:L10)；=SUM(--(H5),H6:H10,J5:J10,L5:L10)；=SUM((H5)*1,H6:H10,J5:J10,L5:L10)；=SUM((H5)/1,H6:H10,J5:J10,L5:L10)[公式说明]：SUM函数用于对单元格区域的数据或者逻辑值、表达式进行求和，它有1-255个参数。鉴于本题的特殊性，公式也可以改为"=SUM(区域1)"，函数会忽略区域中的文本。[使用注意]：1、SUM函数有1-255个参数。参数可以是区域，也可以是表达式。如:=SUM(G5,10*25,G6*8)。2、如果是文本型数字参数，SUM函数是可以直接求和的；如果是引用单元格中的文本型数字(单元格的数字前添加半角单引号“'”),则求和时将忽略。例如G5的值是“'95”，那么以下公式的结果大不同。=SUM(10,"95")—结果为105,=SUM(10,G5)—结果为10。3、如果一定要对单元格中文本型数字进行求和，可以将它转换成数值。转换方法包括多种:利用VALUE函数转换、利用"--"转换和"*1"、"/1"方式转换。例如:=SUM(10,VALUE(G5))、=SUM(10,--(G5))、=SUM(10,(G5)*1)313、多条件求和1：{=SUM(((G23:G32<100)+(G23:G32>110))*G23:G32)}[公式说明]：对于两个范围求和，即满足两个条件中任意一个条件都进行求和，这和同时满足两个条件才求和的公式思路不同。"案例2"的使用注意"中已提到，多个条件同时满足才求和需要用"*"连接所有条件，表示同时满足多条件，而本案例中对多个条件用"+"来连接，表示对于多条例中满足任意一个条件就可以进行求和。本公式为数组公式。[使用注意]：1、多条件满足任意条件即求和，在设置公式时对于所有条件需要用“+”连接，如本例中“(G23:G32<100)+(G23:G32>110)”,然后对条件用括号括起来再与实际求和区相乘。如果缺少条件的外括号将得到错误结果。

2、如果不只两个条件，也用同样方式将所有条件罗列出来并相加。例如求区域中小于100或者等于120的数据和，那么数组公式如下:=SUM(((G23:G32<100)+(G23:G32=110)+(G23:G32=120))*G23:G32)324、多条件求和2：{=SUM((H34:H43="一车间")*(I34:I43="男")*J34:J43)}[公式说明]：本公式以H、I两列数据作为限制条件，对J列数据汇总。“(H34:H43="一车间")*(I34:I43="男")”表示同时满足两个条件，如果还有更多条件，也可以同时罗列出来，利用“*”符号连接，然后再与求和区相乘，得到最后的汇总结果。[使用注意]：1、前三个案例是以求和数据自身为条件对目标区域进行求和，本案例以不同的区域作为限制，条件区有两个，对第三区域进行求和。在设置公式时，三个区域的单元格个数必须一致。例如以下公式将产生错误结果:=SUM((H34:H43="一车间")*(I34:I44="男")*J34:J43)、=SUM((H34:H43="一车间")*(I35:I44="男")*J34:J43)。2、多条件求和时，条件录用区域与求和区域可以在不同列，三个区域只需要宽度、高度一致即可。它们所在的列可以不同，起止行也可以不同，甚至三个区域可以分别位于不同的工作表中，仍然可以得到需要的结果。例如条件区在Sheet2工作表，求和区在当前工作表，公式可以修改为:=SUM((Sheet2!H34:H43="一车间")*(Sheet2!I34:I43="男")*J34:J43)。3、本公式可以用另一种简化写法，仍然可以返回正确结果:=SUM((H34:H43&I34:I43="一车间男")*J34:J43)335、多工作表求和时表名称快捷输入：=SUM(附表一:附表五!B3:B10)[公式说明]：对多表相同区域求和，不需要录入每个工作表的求和地址，只需将第一个工作表名和最后一个表名用冒号连接，后跟"!"与单元格地址，以此作为SUM函数的参数即可。其中"!"表示前面的字符为工作表名称。[使用注意]：1、本方法仅适用于每个工作表地址一致的情况，否则只能逐个输入地址再求和。2、多表求和，SUM函数的参数，工作表与单元格地址可以手动输入，也可以通过选择工作表中单元格地址来产生引用。具体步骤如下:输入"=SUM("，然后选择待汇总的第一个工作表B3:B10区域，按住【Shift】键再单击工作表标签中待求和的最后一个工作表名，最后按【Enter】键结果结束即可。3、如果工作表名中包含一些特殊字符，例如"!"、"@"，公式中的工作表名需要用单引号括起来，例如:=SUM('A组:!E组'!B3:B10)346、快速设置当前表以外的所有工作表相同区域的总和公式：=SUM('*'!B3)[公式说明]1、公式中两个单引号中间加"*"表示除当前表以外的所有工作表。2、在输入公式后，程序会自动将"'*'"转换成实际的工作表名。当按下【Enter】键后，公式将变成:=SUM(附表一:附表五!B3)[使用注意]1、"'*'"只能手动输入，而不能用鼠标选择工作表来产生引用。2、公式中B3必须使用相对引用。3、本公式用法和"案例7"是不同的。在本例中虽然也可以用"案例7"的方法按住【Shift】键来选择需要求和的工作表产生引用，但是操作烦琐，特别是总表不在最后或者最前面，而是在待求和的工作表中间时，用"案例7"的方法需要选择五次，而本例公式中的"*"可以自动的找到排除当前表之外的所有工作表数据。也就是说"总表"不管在任何位置，都可以用同样的公式完成求和，而不需手动修改。4、公式中"*"左右的单引号必须是半角符号。357、用SUM函数计数：{=SUM((H94:H102="男")*(I94:I102="汉"))}[公式说明]：SUM函数虽然是求和函数，在很多时候可以用来计数。本例中利用"H94:H101="男""这一表达式返回一串包含TRUE和FALSE的数组，最后用"*1"将逻辑值转换成数值，并用SUM函数汇总。[使用注意]：1、表达式“H94:H101=”男“”得到的是包含逻辑值的数组。SUM函数可以对逻辑参数进行求和，但是包含于数组中的逻辑值却会被忽略。例如以下公式:=SUM(TRUE,1)—结果为2，=SUM({TRUE,1})结果为1。但是通过“*1”将逻辑值转换成数值后就可以直接求和了。例如:=SUM({TRUE,1}*1)—结果为2。2、将逻辑值转换成数值，除了用"*1"之外，也可以改用"--"或者"/1"等手法，但是用VALUE函数却不行，它只能将文本型数字转换成数值。例如:=SUM(--({T