EXCEL常用函数完全综合手册完善版.docx

1、EXCEL常用函数完全综合手册完善版Excel 2003 函数应用完全手册一、函数应用基本( 一) 函数和公式1 .什么是函数 Excel 函数即是预先定义，执行计算、分析等解决数据任务特殊公式。以惯用求和函数SUM 为例，它语法是“SUM(number1,number2,.)”。其中“SUM”称为函数名称，一种函数只有唯一一种名称，它决定了函数功能和用途。函数名称后紧跟左括号，接着是用逗号分隔称为参数内容，最后用一种右括号表达函数结束。 参数是函数中最复杂构成某些，它规定了函数运算对象、顺序或构造等。使得顾客可以对某个单元格或区域进行解决，如分析存款利息、拟定成绩名次、计算三角函数值等。 按

2、照函数来源，Excel 函数可以分为内置函数和扩展函数两大类。前者只要启动了Excel， 顾客就可以使用它们；而后者必要通过单击“工具加载宏”菜单命令加载，然后才干像内置函数那样使用。 2 .什么是公式 函数与公式既有区别又互相联系。如果说前者是Excel 预先定义好特殊公式，后者就是由顾客自行设计对工作表进行计算和解决公式。以公式“=SUM(E1:H1)*A1+26”为例，它要以等号“=”开始，其内部可以涉及函数、引用、运算符和常量。上式中“SUM(E1:H1)”是函数，“A1”则是对单元格A1 引用(使用其中存储数据)，“26”则是常量，“*” 和“+”则是算术运算符(此外尚有比较运算符、

3、文本运算符和引用运算符)。 如果函数要以公式形式浮现，它必要有两个构成某些，一种是函数名称前面等号，另一种则是函数自身。 （ 二） 函数参数函数右边括号中某些称为参数，如果一种函数可以使用各种参数，那么参数与参数之间使用半角逗号进行分隔。参数可以是常量(数字和文本)、逻辑值(例如TRUE 或FALSE)、数组、错误值(例如#N/A)或单元格引用(例如E1:H1)， 甚至可以是另一种或几种函数等。参数类型和位置必要满足函数语法规定，否则将返回错误信息。 1. 常量 常量是直接输入到单元格或公式中数字或文本，或由名称所代表数字或文本值，例如数字“2890.56”、日期“-8-19”和文本“黎明”都

4、是常量。但是公式或由公式计算出成果都不是常量，由于只要公式参数发生了变化，它自身或计算出来成果就会发生变化。2. 逻辑值 逻辑值是比较特殊一类参数，它只有TRUE(真)或FALSE(假)两种类型。例如在公式“=IF(A3=0,A2/A3)”中，“A3=0”就是一种可以返回TRUE(真)或FALSE(假)两种成果参数。当“A3=0”为TRUE(真)时在公式所在单元格中填入“0”，否则在单元格中填入“A2/A3”计算成果。 3. 数组 数组用于可产生各种成果，或可以对存储在行和列中一组参数进行计算公式。Excel 中有常量和区域两类数组。前者放在“”(按下Ctrl+Shift+Enter 组合键自

5、动生成)内部，并且内部各列数值要用逗号“,”隔开，各行数值要用分号“;”隔开。如果你要表达第1 行中56、78、89 和第2 行中90、76、80，就应当建立一种2 行3 列常量数组“56,78,89;90,76,80。 区域数组是一种矩形单元格区域，该区域中单元格共用一种公式。例如公式“=TREND(B1:B3,A1:A3)”作为数组公式使用时，它所引用矩形单元格区域“B1:B3,A1:A3”就是一种区域数组。 4 .错误值使用错误值作为参数重要是信息函数，例如“ERROR.TYPE”函数就是以错误值作为参数。它语法为“ERROR.TYPE(error_val)”， 如果其中参数是#NUM!

6、，则返回数值“6”。 5. 单元格引用单元格引用是函数中最常用参数，引用目在于标记工作表单元格或单元格区域，并指明公式或函数所使用数据位置，便于它们使用工作表各处数据，或者在各种函数中使用同一种单元格数据。还可以引用同一工作簿不同工作表单元格，甚至引用其她工作簿中数据。 依照公式所在单元格位置发生变化时，单元格引用变化状况，咱们可以引用分为相对引用、绝对引用和混合引用三种类型。以存储在F2 单元格中公式“=SUM(A2:E2)”为例，当公式由F2 单元格复制到F3 单元格后来，公式中引用也会变化为“=SUM(A3:E3)”。若公式自F 列向下继续复制，“行标”每增长1 行，公式中行标也自动加1

7、。如果上述公式改为“=SUM($A $3:$E 3)”， 则无论公式复制到何处，其引用位置始终是“A3:E3”区域。混合引用有“绝对列和相对行”，或是“绝对行和相对列”两种形式。前者如“=SUM($A3:$E3)”，后者如“=SUM(A$3:E$3)”。 上面几种实例引用都是同一工作表中数据，如果要分析同一工作簿中多张工作表上数据，就要使用三维引用。如果公式放在工作表Sheet1 C6 单元格，要引用工作表Sheet2 “A1:A6”和Sheet3 “B2:B9”区域进行求和运算，则公式中引用形式为“=SUM(Sheet2!A1:A6,Sheet3!B2:B9)”。也就是说三维引用中不但包括单

8、元格或区域引用，还要在前面加上带“!”工作表名称。 如果你要引用数据来自另一种工作簿，如工作簿Book1 中SUM 函数要绝对引用工作簿Book2 中数据，其公式为“=SUM(Book2Sheet1！SA S1：SA S8,Book2Sheet2！SB S1：SB S9)”，也就是在本来单元格引用前面加上 “Book2Sheet1!”。放在中括号里面是工作簿名称，带“!”则是其中工作表名称。即是跨工作簿引用单元格或区域时，引用对象前面必要用“!”作为工作表分隔符，再用中括号作为工作簿分隔符。但是三维引用要受到较多限制，例如不能使用数组公式等。 提示：上面简介是Excel 默认引用方式，称为“A

9、1引用样式”。如果你要计算处在“宏”内行和列，必要使用“R1C1 引用样式”。在这种引用样式中，Excel使用“R”加“行标”和“C”加“列标”办法批示单元格位置。启用或关闭R1C1 引用样式必要单击“工具选项”菜单命令，打开对话框“常规”选项卡，选中或清除“设立”下“R1C1引用样式”选项。由于这种引用样式很少使用，限于篇幅本文不做进一步简介。 6. 嵌套函数除了上面简介状况外，函数也可以是嵌套，即一种函数是另一种函数参数，例如“=IF(OR(RIGHTB(E2,1)=1，RIGHTB(E2,1)=3，RIGHTB(E2,1)=5，RIGHTB(E2,1)=7，RIGHTB(E2,1)=9)

10、,男,女)”。其中公式中IF 函数使用了嵌套RIGHTB 函数，并将后者返回成果作为IF 逻辑判断根据。 7. 名称和标志为了更加直观地标记单元格或单元格区域，咱们可以给它们赋予一种名称，从而在公式或函数中直接引用。例如“B2:B46”区域存储着学生物理成绩，求解平均分公式普通是“=AVERAGE(B2:B46)”。在给B2:B46 区域命名为“物理分数”后来，该公式就可以变为“=AVERAGE(物理分数)”，从而使公式变得更加直观。 给一种单元格或区域命名办法是：选中要命名单元格或单元格区域，鼠标单击编辑栏顶端“名称框”，在其中输入名称后回车。也可以选中要命名单元格或单元格区域，单击“插入名

11、称定义”菜单命令，在打开“定义名称”对话框中输入名称后拟定即可。如果你要删除已经命名区域，可以按相似办法打开“定义名称”对话框，选中你要删除名称删除即可。 由于Excel 工作表多数带有“列标志”。例如一张成绩登记表首行普通带有“序号”、“姓名”、“数学”、“物理”等“列标志”(也可以称为字段)，如果单击“工具选项”菜单命令，在打开对话框中单击“重新计算”选项卡，选中“工作簿选项”选项组中“接受公式标志”选项，公式就可以直接引用“列标志”了。例如“B2:B46”区域存储着学生物理成绩，而B1 单元格已经输入了“物理”字样，则求物理平均分公式可以写成“=AVERAGE(物理)”。 需要特别阐明是

12、，创立好名称可以被所有工作表引用，并且引用时不需要在名称前面添加工作表名(这就是使用名称重要长处)，因而名称引用事实上是一种绝对引用。但是公式引用“列标志”时限制较多，它只能在当前数据列下方引用，不能跨越工作表引用，但是引用“列标志”公式在一定条件下可以复制。从本质上讲，名称和标志都是单元格引用一种方式。由于它们不是文本，使用时名称和标志都不能添加引号。 （ 三）函数输入办法对Excel 公式而言，函数是其中重要构成某些，因而公式输入可以归结为函数输入问题。 1.“插入函数”对话框“插入函数”对话框是Excel 输入公式重要工具，以公式“=SUM(Sheet2!A1:A6,Sheet3!B2:

13、B9)”为例，Excel输入该公式详细过程是： 一方面选中存储计算成果(即需要应用公式)单元格，单击编辑栏(或工具栏)中“fx”按钮，则表达公式开始“=”出当前单元格和编辑栏，然后在打开“插入函数”对话框中“选取函数”列表找到“SUM”函数。如果你需要函数不在里面，可以打开“或选取类别”下拉列表进行选取。最后单击“拟定”按钮，打开“函数参数”对话框。 对SUM 函数而言，它可以使用从number1 开始直到number30 共30 个参数。对上面公式来说，一方面应当把光标放在对话框“number1”框中，单击工作簿中“Sheet2!”工作表标签，“Sheet2!”即可自动进入其中，接着鼠标拖动

14、选中你要引用区域即可。接着用鼠标单击对话框“number2”框，单击工作簿中“Sheet3!”工作表标签，其2 名称“Sheet3!”即可自动进入其中，再按相似办法选取要引用单元格区域即可。 上述办法最大长处就是引用区域很精确，特别是三维引用时不容易发生工作表或工作簿名称输入错误问题。 2. 编辑栏输入如果你要套用某个现成公式，或者输入某些嵌套关系复杂公式，运用编辑栏输入更快捷。 一方面选中存储计算成果单元格；鼠标单击Excel 编辑栏，按照公式构成顺序依次输入各个某些，公式输入完毕后，单击编辑栏中“输入”(即“”)按钮(或回车)即可。 手工输入时同样可以采用上面简介办法引用区域，以公式“=S

15、UM(Sheet2!A1:A6,Sheet3!B2:B9)”为例，你可以先在编辑栏中输入“=SUM()”，然后将光标插入括号中间，再按上面简介办法操作就可以引用输入公式了。但是分隔引用之间逗号必要用手工输入，而不能像“插入函数”对话框那样自动添加。 在电脑报 年合订本附赠光盘相应文章中，咱们列出了所有Excel 函数，供读者速查。 二、函数速查一览(一)数据库函数1.DAVERAGE 用途：返回数据库或数据清单中满足指定条件列中数值平均值。 语法：DAVERAGE(database，field，criteria) 参数：Database 构成列表或数据库单元格区域。Field指定函数所使用数据

16、列。Criteria为一组包括给定条件单元格区域。 2.DCOUNT 用途：返回数据库或数据清单指定字段中，满足给定条件并且包括数字单元格数目。 语法：DCOUNT(database，field，criteria) 参数：Database 构成列表或数据库单元格区域。Field 指定函数所使用数据列。Criteria为一组包括给定条件单元格区域。 3.DCOUNTA 用途：返回数据库或数据清单指定字段中满足给定条件非空单元格数目。 语法：DCOUNTA(database，field，criteria) 参数：Database 构成列表或数据库单元格区域。Field 指定函数所使用数据列。Cri

17、teria为一组包括给定条件单元格区域。 4.DGET 用途：从数据清单或数据库中提取符合指定条件单个值。 语法：DGET(database，field，criteria) 参数：Database 构成列表或数据库单元格区域。Field 指定函数所使用数据列。Criteria为一组包括给定条件单元格区域。 5.DMAX 用途：返回数据清单或数据库指定列中，满足给定条件单元格中最大数值。 语法：DMAX(database，field，criteria) 参数：Database 构成列表或数据库单元格区域。Field 指定函数所使用数据列。Criteria为一组包括给定条件单元格区域。 6.DMI

18、N 用途：返回数据清单或数据库指定列中满足给定条件单元格中最小数字。 语法：DMIN(database，field，criteria) 参数：Database 构成列表或数据库单元格区域。Field 指定函数所使用数据列。Criteria为一组包括给定条件单元格区域。 7.DPRODUCT 用途：返回数据清单或数据库指定列中，满足给定条件单元格中数值乘积。 语法：DPRODUCT(database，field，criteria) 参数：同上 8.DSTDEV 用途：将列表或数据库列中满足指定条件数字作为一种样本，估算样本总体原则偏差。语法：DSTDEV(database，field，crite

19、ria) 参数：同上 3 9.DSTDEVP 参数：将数据清单或数据库指定列中，满足给定条件单元格中数字作为样本总体，计算总体原则偏差。 语法：DSTDEVP(database，field，criteria) 参数：同上 10.DSUM 用途：返回数据清单或数据库指定列中，满足给定条件单元格中数字之和。语法：DSUM(database，field，criteria) 参数：同上 11.DVAR 用途：将数据清单或数据库指定列中满足给定条件单元格中数字作为一种样本，估算样本总体方差。语法：DVAR(database，field，criteria) 参数：同上 12.DVARP 用途：将数据清单或

20、数据库指定列中满足给定条件单元格中数字作为样本总体，计算总体方差。 语法：DVARP(database，field，criteria) 参数：同上 13.GETPIVOTDATA 用途：返回存储在数据透视表报表中数据。如果报表中汇总数据可见，则可以使用函数GETPIVOTDATA 从数据透视表报表中检索汇总数据。 语法：GETPIVOTDATA(pivot_table，name) 参数：Data_field为包括要检索数据数据字段名称(放在引号中)。Pivot_table在数据透视表中对任何单元格、单元格区域或定义单元格区域引用，该信息用于决定哪个数据数据透视表包括要检索数据。Field1，I

21、tem1，Field2，Item 2为1到14对用于描述检索数据字段名和项名称，可以任意顺序排列。 (二)日期与时间函数1.DATE 用途：返回代表特定日期序列号。 语法：DATE(year，month，day) 参数：year为一到四位，依照使用日期系统解释该参数。默认状况下，Excel for Windows使用1900 日期系统，而Excel for Macintosh 使用1904 日期系统。Month代表每年中月份数字。如果所输入月份不不大于12，将从指定年份一月份执行加法运算。Day代表在该月份中第几天数字。如果 day 不不大于该月份最大天数时，将从指定月份第一天开始往上累加。

22、注意：Excel 按顺序序列号保存日期，这样就可以对其进行计算。如果工作簿使用是1900 日期系统，则Excel 会将1900 年1 月1 日保存为序列号1。同理，会将1998 年1 月1 日保存为序列号35796，由于该日期距离1900 年1 月1 日为35795 天。 实例：如果采用1900 日期系统(Excel默认)，则公式“=DATE(，1，1)”返回36892。 2.DATEVALUE 用途：返回date_text 所示日期序列号。该函数重要用途是将文字表达日期转换成一种序列号。 语法：DATEVALUE(date_text) 参数：Date_text是用Excel 日期格式表达日期

23、文本。在使用1900 日期系统中，date_text 必要是1900 年1 月1 日到9999 年12 月31 日之间一种日期；而在1904 日期系统中，date_text 必要是190 4年1月1日到999 9年12月31日之间一种日期。如果date_text 超过上述范畴，则函数DATEVALUE 返回错误值#VALUE!。 如果省略参数date_text 中年代，则函数DATEVALUE 使用电脑系统内部时钟当前年代，且date_text 中时间信息将被忽视。 实例：公式“=DATEVALUE(/3/5)”返回36955， DATEVALUE(2-26)返回36948。 3.DAY 用途

24、：返回用序列号(整数1 到31)表达某日期天数，用整数 1 到 31 表达。 语法：DAY(serial_number) 参数：Serial_number 是要查找天数日期，它有各种输入方式：带引号文本串(如1998/01/30)、序列号(如1900 日期系统35825 表达 1998 年1 月30 日)，以及其她公式或函数成果(如DATEVALUE(1998/1/30)。 实例：公式“=DAY(/1/27)”返回27，=DAY(35825) 返回30，=DAY(DATEVALUE(/1/25)返回25。 4.DAYS360 用途：按照一年360 天算法(每月30 天，一年共计12 个月)，返

25、回两日期间相差天数。 语法：DAYS360(start_date，end_date，method) 参数：Start_date 和end_date 是用于计算期间天数起止日期。如果start_date 在end_date 之后，则DAYS360 将返回一种负数。日期可以有各种输入方式：带引号文本串(例如：1998/01/30)、序列号(例如：如果使用1900 日期系统，则35825 表达199 8年1月30 日)或其她公式或函数成果(例如，DATEVALUE(1998/1/30)。 Method 是一种逻辑值，它指定了在计算中是采用欧洲办法还是美国办法。若为FALSE 或忽视，则采用美国办法(

26、如果起始日期是一种月31 日，则等于同月30 日。如果终结日期是一种月31 日，并且起始日期早于30 日，则终结日期等于下一种月1 日，否则，终结日期等于本月30 日)。 若为TRUE 则采用欧洲办法(无论是起始日期还是终结日期为一种月 31 号，都将等于本月 30 号)。 实例：公式“=DAYS360(1998/2/1，/2-1)”返回1080。 5.EDATE 用途：返回指定日期(start_date)之前或之后指定月份日期序列号。 语法：EDATE(start_date，months)参数：Start_date参数代表开始日期，它有各种输入方式：带引号文本串(例如：1998/01/30)

27、、序列号(如35825 表达1998 年1 月30 日)或其她公式或函数成果(例如：DATEVALUE(1998/1/30)。Months 为在start_date 之前或之后月份数，将来日期用正数表达，过去日期用负数表达。实例：公式“=EDATE(/3/5，2)”返回37016 即 年5 月5 日，=EDATE(/3/5，-6)返回36774 即 年9月5日。6.EOMONTH 用途：返回start-date 之前或之后指定月份中最后一天序列号。 语法：EOMONTH(start_date，months) 参数：Start_date参数代表开始日期，它有各种输入方式：带引号文本串(如1998

28、/01/30)、序列号(如1900 日期系统中35825)或其她公式或函数成果(如DATEVALUE(1998/1/30)。Month 为start_date 之前或之后月份数，正数表达将来日期，负数表达过去日期。 实例：公式“=EOMONTH(/01/01，2)”返回36981 即200 1年3月31 日，=EOMONTH(/01/01，-6)返回36738 即 年7 月31 日。 7.HOUR 用途：返回时间值小时数。即介于0(12:00 A.M.)到23(11:00 P.M.) 之间一种整数。 语法：HOUR(serial_number) 参数：Serial_number 表达一种时间值

29、，其中包括着要返回小时数。它有各种输入方式：带引号文本串(如6:45 PM)、十进制数(如0.78125 表达6:45PM)或其她公式或函数成果(如TIMEVALUE(6:45 PM)。实例：公式“=HOUR(3:30:30 PM)”返回15，=HOUR(0.5) 返回12 即12:0 0:00 AM，=HOUR(29747.7)返回16。 8.MINUTE 用途：返回时间值中分钟，即介于0到59之间一种整数。 语法：MINUTE(serial_number) 参数：Serial_number 是一种时间值，其中包括着要查找分钟数。时间有各种输入方式：带引号文本串(如6:45 PM)、十进制数

30、(如 0.78125表达6:45 PM)或其她公式或函数成果(如TIMEVALUE(6:45 PM)。 实例：公式“=MINUTE(15:30:00)”返回30， =MINUTE(0.06)返回26，=MINUTE(TIMEVALUE(9:45 PM)返回45。 9.MONTH 用途：返回以序列号表达日期中月份，它是介于 1(一月)和12(十二月)之间整数。 语法：MONTH(serial_number) 参数：Serial_number 表达一种日期值，其中包括着要查找月份。日期有各种输入方式：带引号文本串(如1998/01/30)、序列号(如表达1998 年1 月30 日35825) 或其

31、她公式或函数成果(如DATEVALUE(1998/1/30)等。 实例：公式“=MONTH(/02/24)”返回2，=MONTH(35825)返回1，=MONTH(DATEVALUE(/6/30)返回6。 10.NETWORKDAYS 用途：返回参数start-data 和end-data 之间完整工作日(不涉及周末和专门指定假期)数值。 语法：NETWORKDAYS(start_date，end_date，holidays) 5 参数：Start_date 代表开始日期，End_date 代表终结日；Holidays 是表达不在工作日历中一种或各种日期所构成可选区域，法定假日以及其她非法定假

32、日。此数据清单可以是包括日期单元格区域，也可以是由代表日期序列号所构成数组常量。 函数中日期有各种输入方式：带引号文本串(如1998/01/30)、序列号(如使用1900 日期系统35825)或其她公式或函数成果(如 DATEVALUE(1998/1/30)。 注意：该函数只有加载“分析工具库”后来方能使用。11.NOW 用途：返回当前日期和时间所相应序列号。 语法：NOW() 参数：无 实例：如果正在使用是1900 日期系统，并且计算机内部时钟为 -1-28 12:53，则公式“=NOW()”返回36919.54。 12.SECOND 用途：返回时间值秒数(为0至59 之间一种整数)。 语法：SECOND(serial_number)参数：Serial_number 表达一种时间值，其中包括要查找