Excel2010函数大全

Excel2010函数大全 Excel2007 函数大全 一、函数应用基础 1.函数和公式 1什么是函数 Excel 函数即是预先定义， 执行计算、 分析等处理数据任务的特殊公式。 以常用的求和函数 SUM 为例，它的语法是“SUMnumber1number2......” 。其中“SUM”称为函数名称，一个函数只有唯一的一个名称，它决定了函数的功能和用途。函数名称后紧跟左括号，接着是用逗号分隔的称为参数的，最后用一个右括号示函数结束。 参数是函数中最复杂的组成部分，它规定了函数的运算对象、顺序或结构等。使得用户可以对某个单元格或区域进行处理，如分析存款利息、确定成绩名次、计算三角函数值等。 按照函数的来源，Excel 函数可以分为内置函数和扩展函数两大类。前者只要启动了 Excel，用户就可以使用它们;而后者必须通过单击“工具?加载宏”菜单命令加载，然后才能像内置函数那样使用。 2什么是公式 函数与公式既有区别又互相联系。如果说前者是 Excel 预先定义好的特殊公式，后者就是由用户自行设计对工作表进行计算和处理的公式。以公式“SUME1:H1A126”为例，它要以等号“”开始，其内部可以包括函数、引用、运算符和常量。上式中的“SUME1:H1”是函数， “A1”则是对单元格 A1 的引用使用其中存储的数据， “26”则是常量， “”和“”则是算术运算符另外还有比较运算符、文本运算符和引用运算符。 如果函数要以公式的形式出现，它必须有两个组成部分，一个是函数名称前面的等号，另一个则是函数本身。 2.函数的参数 函数右边括号中的部分称为参数，假如一个函数可以使用多个参数，那么参数与参数之间使用半角逗号进行分隔。参数可以是常量数字和文本、逻辑值例如 TRUE 或 FALSE、数组、错误值例如N/A或单元格引用例如 E1:H1，甚至可以是另一个或几个函数等。参数的类型和位置必须满足函数语法的要求，否则将返回错误信息。 1常量 常量是直接输入到单元格或公式中的数字或文本，或由名称所代表的数字或文本值，例如数字“2890.56”、日期“2003-8-19”和文本“黎明”都是常量。但是公式或由公式计算出的结果都不是常量，因为只要公式的参数发生了变化，它自身或计算出来的结果就会发生变化。 2逻辑值 逻 辑 值 是 比 较 特 殊 的 一 类 参 数 ， 它 只 有 TRUE 真 或 FALSE 假 两 种 类 型 。 例 如 在 公 式 中，“IFA30quotquotA2/A3” “A30” 就是一个可以返回 TRUE真或 FALSE假两种结果的参数。 “A30” 当为 TRUE真时在公式所在单元格中填入“0” ，否则在单元格中填入“A2/A3”的计算结果。 3数组 数组用于可产生多个结果，或可以对存放在行和列中的一组参数进行计算的公式。Excel 中有常量和区域两类数组。前者放在“”按下 CtrlShiftEnter 组合键自动生成内部，而且内部各列的数值要用逗号“”隔开，各行的数值要用分号“”隔开。假如你要表示第 1 行中的 56、78、89 和第 2 行中的 90、76、80，就应该建立一个 2 行 3 列的常量数组“567889907680。 区域数组是一个矩形的单元格区域，该区域中的单元格共用一个公式。例如公式“TRENDB1:B3A1:A3”作为数组公式使用时，它所引用的矩形单元格区域“B1:B3A1:A3”就是一个区域数组。 4错误值 使用错误值作为参数的主要是信息函数，例如“ERROR.TYPE”函数就是以错误值作为参数。它的语法为 “ERROR.TYPEerror_val” ，如果其中的参数是NUM，则返回数值“6” 。 5单元格引用 单元格引用是函数中最常见的参数，引用的目的在于标识工作表单元格或单元格区域，并指明公式或函数所使用的数据的位置， 便于它们使用工作表各处的数据， 或者在多个函数中使用同一个单元格的数据。还可以引用同一工作簿不同工作表的单元格，甚至引用其他工作簿中的数据。 根据公式所在单元格的位置发生变化时， 单元格引用的变化情况，我们可以引用分为相对引用、绝对引用和混合引用三种类型。以存放在 F2 单元格中的公式“SUMA2:E2”为例，当公式由 F2 单元格复制到 F3 单元格以后，公式中的引用也会变化为“SUMA3:E3” 。若公式自 F 列向下继续复制， “行标”每增加 1 行，公式中的行标也自动加 1。 如果上述公式改为“SUMA 3:E ,3” ，则无论公式复制到何处，其引用的位置始终是“A3:E3”区域。 混合引用有“绝对列和相对行” ，或是“绝对行和相对列”两种形式。前者如“SUMA3:E3” ，后者如“SUMA3:E3” 。 上面的几个实例引用的都是同一工作表中的数据，如果要分析同一工作簿中多张工作表上的数据，就要使用三维引用。假如公式放在工作表 Sheet1 的 C6 单元格，要引用工作表 Sheet2 的“A1:A6”和 Sheet3的“B2:B9”区域进行求和运算，则公式中的引用形式为“SUMSheet2A1:A6Sheet3B2:B9” 。也就是说三维引用中不仅包含单元格或区域引用，还要在前面加上带“”的工作表名称。 假如你要引用的数据来自另一个工作簿，如工作簿 Book1 中的 SUM 函数要绝对引用工作簿 Book2 中的数据，其公式为“SUMBook2Sheet1 SA S1: SA S8Book2Sheet2 SB S1: SB S9” ，也就是在原来单元格引用的前面加上“Book2Sheet1” 。放在中括号里面的是工作簿名称，带“”的则是其中的工作表名称。即是跨工作簿引用单元格或区域时，引用对象的前面必须用“”作为工作表分隔符，再用中括号作为工作簿分隔符。不过三维引用的要受到较多的限制，例如不能使用数组公式等。 提示:上面介绍的是 Excel 默认的引用方式，称为“A1 引用样式” 。如果你要计算处在“宏”内的行和列，必须使用“R1C1 引用样式” 。在这种引用样式中，Excel 使用“R”加“行标”和“C”加“列标”的方法指示单元格位置。启用或关闭 R1C1 引用样式必须单击“工具?选项”菜单命令，打开对话框的“常规”选项卡，选中或清除“设置”下的“R1C1 引用样式”选项。由于这种引用样式很少使用，限于篇幅本文不做进一步介绍。 6嵌套函数 除了上面介绍的情况外，函数也可以是嵌套的，即一个函数是另一个函数的参数，例如“ IFORRIGHTBE21quot1quot ， RIGHTBE21quot3quot ， RIGHTBE21quot5quot ， RIGHTBE21quot7quot ，RIGHTBE21quot9quotquot男quotquot女quot”。其中公式中的 IF 函数使用了嵌套的 RIGHTB 函数，并将后者返回的结果作为 IF 的逻辑判断依据。 7名称和标志 为了更加直观地标识单元格或单元格区域，我们可以给它们赋予一个名称，从而在公式或函数中直接引用。例如“B2:B46”区域存放着学生的物理成绩，求解平均分的公式一般是 “AVERAGEB2:B46” 。在给 B2:B46 区域命名为“物理分数”以后，该公式就可以变为“AVERAGE物理分数” ，从而使公式变得更加直观。 给一个单元格或区域命名的方法是:选中要命名的单元格或单元格区域，鼠标单击编辑栏顶端的“名称框” ，在其中输入名称后回车。也可以选中要命名的单元格或单元格区域，单击“插入?名称?定义”菜单命令，在打开的“定义名称”对话框中输入名称后确定即可。如果你要删除已经命名的区域，可以按相同方法打开“定义名称”对话框，选中你要删除的名称删除即可。 由于 Excel 工作表多数带有 。 “列标志” 例如一张成绩统计表的首行通常带有 “序号”、 “姓名”“数学” 、 、“物理”等“列标志”也可以称为字段，如果单击“工具?选项”菜单命令，在打开的对话框中单击“重新计算”选项卡，选中“工作簿选项”选项组中的“接受公式标志”选项，公式就可以直接引用“列标志”了。例如“B2:B46”区域存放着学生的物理成绩，而 B1 单元格已经输入了“物理”字样，则求物理平均分的公式可以写成“AVERAGE物理” 。 需要特别说明的是，创建好的名 称可以被所有工作表引用，而且引用时不需要在名称前面添加工作表名这就是使用名称的主要优点，因此名称引用实际上是一种绝对引用。但是公式引用“列标志”时的限制较多，它只能在当前数据列的下方引用，不能跨越工作表引用，但是引用“列标志”的公式在一定条件下可以复制。从本质上讲，名称和标志都是单元格引用的一种方式。因为它们不是文本，使用时名称和标志都不能添加引号。 3.函数输入方法 对 Excel 公式而言，函数是其中的主要组成部分，因此公式输入可以归结为函数输入的问。 1“插入函数”对话框 “插入函数”对话框是 Excel 输入公式的重要工具，以公式“SUMSheet2A1:A6Sheet3B2:B9”为例，Excel 输入该公式的具体过程是: 首先选中存放计算结果即需要应用公式的单元格，单击编辑栏或工具栏中的“fx”按钮，则表示公式开始的 “”出现在单元格和编辑栏， 然后在打开的“插入函数”对话框中的“选择函数” 列表找到“SUM”函数。如果你需要的函数不在里面，可以打开“或选择类别”下拉列表进行选择。最后单击“确定”按钮，打开“函数参数”对话框。 对 SUM 函数而言，它可以使用从 number1 开始直到 number30 共 30 个参数。对上面的公式来说，首先应当把光标放在对话框的“number1”框中，单击工作簿中的“Sheet2”工作表标签，“Sheet2”即可自动进入其中，接着鼠标拖动选中你要引用的区域即可。接着用鼠标单击对话框的“number2”框，单击工作簿中的“Sheet3”工作表标签，其名称“Sheet3”即可自动进入其中，再按相同方法选择要引用的单元格区域即可。 上述方法的最大优点就是引用的区域很准确，特别是三维引用时不容易发生工作表或工作簿名称输入错误的问题。 2编辑栏输入 如果你要套用某个现成公式，或者输入一些嵌套关系复杂的公式，利用编辑栏输入更加快捷。 首先选中存放计算结果的单元格;鼠标单击 Excel 编辑栏，按照公式的组成顺序依次输入各个部分，公式输入完毕后，单击编辑栏中的“输入”即“?”按钮或回车即可。 手工输入时同样可以采取上面介绍的方法引用区域，以公式“SUMSheet2A1:A6Sheet3B2:B9”为例，你可以先在编辑栏中输入“SUM” ，然后将光标插入括号中间，再按上面介绍的方法操作就可以引用输入公式了。但是分隔引用之间的逗号必须用手工输入，而不能像“插入函数”对话框那样自动添加。 在《电脑报 2003 年合订本附赠光盘》相应文章中，我们列出了所有 Excel 2003 函数，供读者速查。 二日期与时间函数 1.DATE 用途:返回代表特定日期的序列号。 语法:DATEyear，month，day 参数:year 为一到四位，根据使用的日期系统解释该参数。默认情况下，Excel for Windows 使用 1900日期系统，而 Excel for Macintosh 使用 1904 日期系统。Month 代表每年中月份的数字。如果所输入的月份大于 12，将从指定年份的一月份执行加法运算。Day 代表在该月份中第几天的数字。如果 day 大于该月份的最大天数时，将从指定月份的第一天开始往上累加。 注意:Excel 按顺序的序列号保存日期，这样就可以对其进行计算。如果工作簿使用的是 1900 日期系统，则 Excel 会将 1900 年 1 月 1 日保存为序列号 1。同理，会将 1998 年 1 月 1 日保存为序列号 35796，因为该日期距离 1900 年 1 月 1 日为 35795 天。 实例:如果采用 1900 日期系统Excel 默认，则公式“DATE2001，1，1”返回 36892。 2.DATEVALUE 用途:返回 date_text 所表示的日期的序列号。该函数的主要用途是将文字表示的日期转换成一个序列号。 语法:DATEVALUEdate_text 参数: Date_text 是用 Excel 日期格式表示日期的文本。在使用 1900 日期系统中， date_text 必须是 1900年 1 月 1 日到 9999 年 12 月 31 日之间的一个日期;而在 1904 日期系统中，date_text 必须是 1904 年 1 月 1 日到 9999 年 12 月 31 日之间的一个日期。如果 date_text 超出上述范围，则函数 DATEVALUE 返回错误值VALUE。 如果省略参数 date_text 中的年代， 且 则函数 DATEVALUE 使用电脑系统内部时钟的当前年代， date_text中的时间信息将被忽略。 实例:公式“DATEVALUEquot2001/3/5quot”返回 36955，DATEVALUEquot2-26quot返回 36948。 3.DAY 用途:返回用序列号整数 1 到 31表示的某日期的天数，用整数 1 到 31 表示。 语法:DAYserial_number 参数:Serial_number 是要查找的天数日期，它有多种输入方式:带引号的文本串如quot1998/01/30quot、序列号如 1900 日期系统的 35825 表示 的 1998 年 1 月 30 日，以及其他公式或函数的结果如DATEVALUEquot1998/1/30quot。 实例:公式“DAYquot2001/1/27quot”返回 27，DAY35825返回 30， DAYDATEVALUEquot2001/1/25quot返回 25。 4.DAYS360 用途:按照一年 360 天的算法每个月 30 天，一年共计 12 个月，返回两日期间相差的天数。 语法:DAYS360start_date，end_date，method 参数:Start_date 和 end_date 是用于计算期间天数的起止日期。如果 start_date 在 end_date 之后，则DAYS360 将返回一个负数。日期可以有多种输入方式:带引号的文本串例如:quot1998/01/30quot、序列号例如:如果使用 1900 日期系统，则 35825 表示 1998 年 1 月 30 日或其他公式或函数的结果例如，DATEVALUEquot1998/1/30quot。 Method 是一个逻辑值，它指定了在计算中是采用欧洲方法还是美国方法。若为 FALSE 或忽略，则采用美国方法如果起始日期是一个月的 31 日，则等于同月的 30 日。如果终止日期是一个月的 31 日，并且起始日期早于 30 日，则终止日期等于下一个月的 1 日，否则，终止日期等于本月的 30 日。 若为 TRUE则采用欧洲方法无论是起始日期还是终止日期为一鲈碌?31 号，都将等于本月的 30 号。 实例:公式 “DAYS360quot1998/2/1quot，quot2001/2-1quot”返回 1080。 5.EDATE 用途:返回指定日期start_date之前或之后指定月份的日期序列号。 语法:EDATEstart_date，months 参数:Start_date 参数代表开始日期，它有多种输入方式:带引号的文本串例如:quot1998/01/30quot、序列号如 35825 表示 1998 年 1 月 30 日或其他公式或函数的结果例如: DATEVALUEquot1998/1/30quot。 Months 为在 start_date 之前或之后的月份数，未来日期用正数表示，过去日期用负数表示。 实例:公式“EDATEquot2001/3/5quot，2”返回 37016 即 2001 年 5 月 5 日， EDATEquot2001/3/5quot，-6返回36774 即 2000 年 9 月 5 日。 6.EOMONTH 用途:返回 start-date 之前或之后指定月份中最后一天的序列号。 语法:EOMONTHstart_date，months 参数:Start_date 参数代表开始日期， 它有多种输入方式:带引号的文本串如quot1998/01/30quot、序列号如1900 日期系统中的 35825或其他公式或函数的结果如 DATEVALUEquot1998/1/30quot。Month 为 start_date 之前或之后的月份数，正数表示未来日期，负数表示过去日期。 公式 实例: “EOMONTHquot2001/01/01quot， 返回 36981 即 2001 年 3 月 31 日， 2” EOMONTHquot2001/01/01quot，-6返回 36738 即 2000 年 7 月 31 日。 7.HOUR 用途:返回时间值的小时数。即介于 012:00 A.M.到 2311:00 P.M. 之间的一个整数。 语法:HOURserial_number 参数:Serial_number 表示一个时间值，其中包含着要返回的小时数。它有多种输入方式:带引号的文本串如quot6:45 PMquot、十进制数如 0.78125 表示 6:45PM或其他公式或函数的结果如 TIMEVALUEquot6:45PMquot。 实例:公式“HOURquot3:30:30 PMquot”返回 15，HOUR0.5返回 12 即 12:00:00 AM，HOUR29747.7返回 16。 8.MINUTE 用途:返回时间值中的分钟，即介于 0 到 59 之间的一个整数。 语法:MINUTEserial_number 参数:Serial_number 是一个时间值，其中包含着要查找的分钟数。时间有多种输入方式:带引号的文本串如quot6:45 PMquot、十进制数如 0.78125 表示 6:45 PM或其他公式或函数的结果如 TIMEVALUEquot6:45PMquot。 实例: 公式 “MINUTEquot15:30:00quot”返回 30， MINUTE0.06返回 26，MINUTETIMEVALUEquot9:45PMquot返回 45。 9.MONTH 用途:返回以序列号表示的日期中的月份，它是介于 1一月和 12十二月之间的整数。 语法:MONTHserial_number 参数:Serial_number 表示一个日期值，其中包含着要查找的月份。日期有多种输入方式:带引号的文本 串 如 quot1998/01/30quot、序 列号 如表示 1998 年 1 月 30 日的 35825或 其他 公式或 函数 的结果 如DATEVALUEquot1998/1/30quot等。 实 例 : 公 式 “ MONTHquot2001/02/24quot ” 返 回 2 ， MONTH35825 返 回 1 ，MONTHDATEVALUEquot2000/6/30quot返回 6。 10.NETWORKDAYS 用途:返回参数 start-data 和 end-data 之间完整的工作日不包括周末和专门指定的假期数值。 语法:NETWORKDAYSstart_date，end_date，holidays 参数:Start_date 代表开始日期，End_date 代表终止日;Holidays 是表示不在工作日历中的一个或多个日期所构成的可选区域，法定假日以及其他非法定假日。此数据清单可以是包含日期的单元格区域，也可以是由代表日期的序列号所构成的数组常量。 函数中的日期有多种输入方式:带引号的文本串如 quot1998/01/30quot、序列号如使用 1900 日期系统的35825或其他公式或函数的结果如 DATEVALUEquot1998/1/30quot。 注意:该函数只有加载“分析工具库”以后方能使用。 11.NOW 用途:返回当前日期和时间所对应的序列号。 语法:NOW 参数:无 实例:如果正在使用的是 1900 日期系统， 而且计算机的内部时钟为 2001-1-28 12:53， 则公式“NOW”返回 36919.54。 12.SECOND 用途:返回时间值的秒数为 0 至 59 之间的一个整数。 语法:SECONDserial_number 参数:Serial_number 表示一个时间值，其中包含要查找的秒数。关于时间的输入方式见上文的有关内容。 实例:公式“SECONDquot3:30:26 PMquot”返回 26，SECOND0.016返回 2。 13.TIME 用途:返回某一特定时间的小数值， 它返回的小数值从 0 到 0.99999999 之间，代表 0:00:0012:00:00 A.M到 23:59:5911:59:59 P.M 之间的时间。 语法:TIMEhour，minute，second 参数:Hour 是 0 到 23 之间的数，代表小时;Minute 是 0 到 59 之间的数，代表分;Second 是 0 到 59之间的数，代表秒。 实例:公式“TIME12，10，30”返回序列号 0.51，等价于 12:10:30 PM。TIME9，30，10返回序列号 0.40，等价于 9:30:10 AM。TEXTTIME23，18，14，quoth:mm:ss AM/PMquot返回“11:18:14 PM” 。 14.TIMEVALUE 用途:返回用文本串表示的时间小数值。该小数值为从 0 到 0.999999999 的数值，代表从 0:00:0012:00:00 AM 到 23:59:59 11:59:59 PM 之间的时间。 语法:TIMEVALUEtime_text 参数:Time_text 是一个用 Excel 时间格式表示时间的文本串如quot6:45 PMquot和quot18:45quot等。 实例:公式“TIMEVALUEquot3:30 AMquot”返回 0.145833333，TIMEVALUEquot2001/1/26 6:35 AMquot返回 0.274305556。 15.TODAY 用途:返回系统当前日期的序列号。 参数:无 语法:TODAY 实例:公式“TODAY”返回 2001-8-28执行公式时的系统时间。 16.WEEKDAY 用途:返回某日期的星期数。在 默认情况下，它的值为 1星期天到 7星期六之间的一个整数。 语法:WEEKDAYserial_number，return_type 参数:Serial_number 是要返回日期数的日期，它有多种输入方式:带引号的文本串如quot2001/02/26quot、序列号如 35825 表示 1998 年 1 月 30 日或其他公式或函数的结果如 DATEVALUEquot2000/1/30quot。Return_type 为确定返回值类型的数字，数字 1 或省略则 1 至 7 代表星期天到数星期六，数字 2 则 1 至 7 代表星期一到星期天，数字 3 则 0 至 6 代表星期一到星期天。 实例:公式“WEEKDAYquot2001/8/28quot，2”返回 2星期二，WEEKDAYquot2003/02/23quot，3返回 6星期日。 17.WEEKNUM 用途:返回一个数字，该数字代表一年中的第几周。 语法:WEEKNUMserial_num，return_type 参数:Serial_num 代表一周中的日期。应使用 DATE.