EXCEL 常用函数.docx

1、EXCEL 常用函数一、常用函数AVERAGE返回参数的平均值（算术平均值）。语法AVERAGE(number1,number2,.)Number1, number2, . 为需要计算平均值的 1 到 30 个参数。说明 参数可以是数字，或者是包含数字的名称、数组或引用。 如果数组或引用参数包含文本、逻辑值或空白单元格，则这些值将被忽略；但包含零值的单元格将计算在内。COUNT返回包含数字以及包含参数列表中的数字的单元格的个数。利用函数 COUNT 可以计算单元格区域或数字数组中数字字段的输入项个数。语法COUNT(value1,value2,.)Value1, value2, . 为包含或引

2、用各种类型数据的参数（1 到 30个），但只有数字类型的数据才被计算。说明 函数 COUNT 在计数时，将把数字、日期、或以文本代表的数字计算在内；但是错误值或其他无法转换成数字的文字将被忽略。 如果参数是一个数组或引用，那么只统计数组或引用中的数字；数组或引用中的空白单元格、逻辑值、文字或错误值都将被忽略。如果要统计逻辑值、文字或错误值，请使用函数 COUNTA。 COUNTIF计算区域中满足给定条件的单元格的个数。语法COUNTIF(range,criteria)Range 为需要计算其中满足条件的单元格数目的单元格区域。Criteria 为确定哪些单元格将被计算在内的条件，其形式可以为数

3、字、表达式或文本。例如，条件可以表示为 32、32、32 或 apples。说明Microsoft Excel 提供其他函数，可用来基于条件分析数据。例如，若要计算基于一个文本字符串或某范围内的一个数值的总和，可使用 SUMIF 工作表函数。若要使公式返回两个基于条件的值之一，例如某指定销售量的销售红利，可使用 IF 工作表函数。IF执行真假值判断，根据逻辑计算的真假值，返回不同结果。可以使用函数 IF 对数值和公式进行条件检测。语法IF(logical_test,value_if_true,value_if_false)Logical_test 表示计算结果为 TRUE 或 FALSE 的任

4、意值或表达式。例如，A10=100 就是一个逻辑表达式，如果单元格 A10 中的值等于 100，表达式即为 TRUE，否则为 FALSE。本参数可使用任何比较运算符。Value_if_true logical_test 为 TRUE 时返回的值。例如，如果本参数为文本字符串“预算内”而且 logical_test 参数值为 TRUE，则 IF 函数将显示文本“预算内”。如果 logical_test 为 TRUE 而 value_if_true 为空，则本参数返回 0（零）。如果要显示 TRUE，则请为本参数使用逻辑值 TRUE。Value_if_true 也可以是其他公式。Value_if_

5、false logical_test 为 FALSE 时返回的值。例如，如果本参数为文本字符串“超出预算”而且 logical_test 参数值为 FALSE，则 IF 函数将显示文本“超出预算”。如果 logical_test 为 FALSE 且忽略了 Value_if_false（即 value_if_true 后没有逗号），则会返回逻辑值 FALSE。如果 logical_test 为 FALSE 且 Value_if_false 为空（即 value_if_true 后有逗号，并紧跟着右括号），则本参数返回 0（零）。Value_if_false 也可以是其他公式。说明 函数 IF 可

6、以嵌套七层，用 value_if_false 及 value_if_true 参数可以构造复杂的检测条件。请参阅下面最后一个示例。 在计算参数 value_if_true 和 value_if_false 后，函数 IF 返回相应语句执行后的返回值。 如果函数 IF 的参数包含数组，则在执行 IF 语句时，数组中的每一个元素都将计算。 Microsoft Excel 还提供了其他一些函数，可依据条件来分析数据。例如，如果要计算单元格区域中某个文本字符串或数字出现的次数，则可使用 COUNTIF 工作表函数。如果要根据单元格区域中的某一文本字符串或数字求和，则可使用 SUMIF 工作表函数。MA

7、X返回一组值中的最大值。语法MAX(number1,number2,.)Number1, number2, . 是要从中找出最大值的 1 到 30 个数字参数。说明 可以将参数指定为数字、空白单元格、逻辑值或数字的文本表达式。如果参数为错误值或不能转换成数字的文本，将产生错误。 如果参数为数组或引用，则只有数组或引用中的数字将被计算。数组或引用中的空白单元格、逻辑值或文本将被忽略。如果逻辑值和文本不能忽略，请使用函数 MAXA 来代替。 如果参数不包含数字，函数 MAX 返回 0（零）MIN返回一组值中的最小值，用法同上。SUM返回某一单元格区域中所有数字之和。语法SUM(number1,nu

8、mber2, .)Number1, number2, . 为 1 到 30 个需要求和的参数。说明 直接键入到参数表中的数字、逻辑值及数字的文本表达式将被计算。 如果参数为数组或引用，只有其中的数字将被计算。数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略。 如果参数为错误值或为不能转换成数字的文本，将会导致错误。 SUMIF根据指定条件对若干单元格求和。语法SUMIF(range,criteria,sum_range)Range 为用于条件判断的单元格区域。Criteria 为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为 32、32、32 或

9、apples。Sum_range 是需要求和的实际单元格。说明 只有在区域中相应的单元格符合条件的情况下，sum_range 中的单元格才求和。 如果忽略了 sum_range，则对区域中的单元格求和。 Microsoft Excel 还提供了其他一些函数，它们可根据条件来分析数据。例如，如果要计算单元格区域内某个文本字符串或数字出现的次数，则可使用 COUNTIF 函数。如果要让公式根据某一条件返回两个数值中的某一值（例如，根据指定销售额返回销售红利），则可使用 IF 函数。RANK返回一个数字在数字列表中的排位。数字的排位是其大小与列表中其他值的比值（如果列表已排过序，则数字的排位就是它当

10、前的位置）。语法RANK(number,ref,order)Number为需要找到排位的数字。Ref 为数字列表数组或对数字列表的引用。Ref 中的非数值型参数将被忽略。Order 为一数字，指明排位的方式。 如果 order 为 0（零）或省略，Microsoft Excel 对数字的排位是基于 ref 为按照降序排列的列表。 如果 order 不为零，Microsoft Excel 对数字的排位是基于 ref 为按照升序排列的列表。 说明 函数 RANK 对重复数的排位相同。但重复数的存在将影响后续数值的排位。例如，在一列按升序排列的整数中，如果整数 10 出现两次，其排位为 5，则 11

11、 的排位为 7（没有排位为 6 的数值）。 由于某些原因，用户可能使用考虑重复数字的排位定义。在前面的示例中，用户可能要将整数 10 的排位改为 5.5。这可通过将下列修正因素添加到按排位返回的值来实现。该修正因素对于按照升序计算排位（顺序 = 非零值）或按照降序计算排位（顺序 = 0 或被忽略）的情况都是正确的。重复数排位的修正因素 =COUNT(ref) + 1 RANK(number, ref, 0) RANK(number, ref, 1)/2。在下列示例中，RANK(A2,A1:A5,1) 等于 3。修正因素是 (5 + 1 2 3)/2 = 0.5，考虑重复数排位的修改排位是 3

12、+ 0.5 = 3.5。如果数字仅在 ref 出现一次，由于不必调整 RANK，因此修正因素为 0。SIN返回给定角度的正弦值。语法SIN(number)Number 为需要求正弦的角度，以弧度表示。说明如果参数的单位是度，则可以乘以 PI()/180 或使用 RADIANS 函数将其转换为弧度。PMT基于固定利率及等额分期付款方式，返回贷款的每期付款额。语法PMT(rate,nper,pv,fv,type)有关函数 PMT 中参数的详细说明，请参阅函数 PV。Rate 贷款利率。Nper 该项贷款的付款总数。Pv 现值，或一系列未来付款的当前值的累积和，也称为本金。Fv 为未来值，或在最后一

13、次付款后希望得到的现金余额，如果省略 fv，则假设其值为零，也就是一笔贷款的未来值为零。Type 数字 0 或 1，用以指定各期的付款时间是在期初还是期末。Type 值支付时间0 或省略期末1期初说明 PMT 返回的支付款项包括本金和利息，但不包括税款、保留支付或某些与贷款有关的费用。 应确认所指定的 rate 和 nper 单位的一致性。例如，同样是四年期年利率为 12% 的贷款，如果按月支付，rate 应为 12%/12，nper 应为 4*12；如果按年支付，rate 应为 12%，nper 为 4。二、数学与三角函数INT将数字向下舍入到最接近的整数。语法INT(number)Numb

14、er 需要进行向下舍入取整的实数。MOD返回两数相除的余数。结果的正负号与除数相同。语法Number 为被除数。Divisor 为除数。说明 如果 divisor 为零，函数 MOD 返回错误值 #DIV/0!。 函数 MOD 可以借用函数 INT 来表示: MOD(n, d) = n - d*INT(n/d)PI返回数字 3.14159265358979，即数学常量 pi，精确到小数点后 14 位。语法PI( )RAND返回大于等于 0 及小于 1 的均匀分布随机数，每次计算工作表时都将返回一个新的数值。语法RAND( )说明 若要生成 a 与 b 之间的随机实数，请使用： RAND()*(

15、b-a)+a 如果要使用函数 RAND 生成一随机数，并且使之不随单元格计算而改变，可以在编辑栏中输入“=RAND()”，保持编辑状态，然后按 F9，将公式永久性地改为随机数。 ROUND返回某个数字按指定位数取整后的数字。语法ROUND(number,num_digits)Number 需要进行四舍五入的数字。Num_digits 指定的位数，按此位数进行四舍五入。说明 如果 num_digits 大于 0，则四舍五入到指定的小数位。 如果 num_digits 等于 0，则四舍五入到最接近的整数。 如果 num_digits 小于 0，则在小数点左侧进行四舍五入。 COMBIN计算从给定数

16、目的对象集合中提取若干对象的组合数。利用函数 COMBIN 可以确定一组对象所有可能的组合数。语法COMBIN(number,number_chosen)Number 为对象的总数量。Number chosen 为每一组合中对象的数量。说明 数字参数截尾取整。 如果参数为非数值型，则函数 COMBIN 返回错误值 #VALUE!。 如果 number 0、number_chosen 0 或 number number_chosen，COMBIN 返回错误值 #NUM!。 不论其内部顺序，对象组合是对象整体的任意集合或子集。组合与排列不同，排列数与对象内部顺序有关。 FACT返回数的阶乘，一个数

17、的阶乘等于 1*2*3*.* 该数。语法FACT(number)Number 要计算其阶乘的非负数。如果输入的 Number 不是整数，则截尾取整。POWER返回给定数字的乘幂。语法POWER(number,power)Number 底数，可以为任意实数。Power 指数，底数按该指数次幂乘方。说明可以用“”运算符代替函数 POWER 来表示对底数乘方的幂次，例如 52。PRODUCT将所有以参数形式给出的数字相乘，并返回乘积值。语法PRODUCT(number1,number2,.)Number1, number2, . 为 1 到 30 个需要相乘的数字参数。说明 当参数为数字、逻辑值或数

18、字的文字型表达式时可以被计算；当参数为错误值或是不能转换成数字的文字时，将导致错误。 如果参数为数组或引用，只有其中的数字将被计算。数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略。 SQRT返回正平方根。语法SQRT(number)Number 要计算平方根的数。说明如果参数 Number 为负值，函数 SQRT 返回错误值 #NUM!。SUBTOTAL返回列表或数据库中的分类汇总。通常，使用“数据”菜单中的“分类汇总”命令可以容易地创建带有分类汇总的列表。一旦创建了分类汇总，就可以通过编辑 SUBTOTAL 函数对该列表进行修改。语法SUBTOTAL(function_num, re

19、f1, ref2, .)Function_num 为 1 到 11（包含隐藏值）或 101 到 111（忽略隐藏值）之间的数字，指定使用何种函数在列表中进行分类汇总计算。Function_num（包含隐藏值）Function_num（忽略隐藏值）函数1101AVERAGE2102COUNT3103COUNTA4104MAX5105MIN6106PRODUCT7107STDEV8108STDEVP9109SUM10110VAR11111VARPRef1, ref2, 为要进行分类汇总计算的 1 到 29 个区域或引用。说明 如果在 ref1, ref2, 中有其他的分类汇总（嵌套分类汇总），将忽

20、略这些嵌套分类汇总，以避免重复计算。 当 function_num 为从 1 到 11 的常数时，SUBTOTAL 函数将包括通过“格式”菜单的“行”子菜单下面的“隐藏”命令所隐藏的行中的值。当您要分类汇总列表中的隐藏和非隐藏值时，请使用这些常数。当 function_num 为从 101 到 111 的常数时，SUBTOTAL 函数将忽略通过“格式”菜单的“行”子菜单下面的“隐藏”命令所隐藏的行中的值。当您只分类汇总列表中的非隐藏数字时，使用这些常数。 SUBTOTAL 函数忽略任何不包括在筛选结果中的行，不论使用什么 function_num 值。 SUBTOTAL 函数适用于数据列或垂直

21、区域。不适用于数据行或水平区域。例如，当 function_num 大于或等于 101 时需要分类汇总某个水平区域时，例如 SUBTOTAL(109,B2:G2)，则隐藏某一列不影响分类汇总。但是隐藏分类汇总的垂直区域中的某一行就会对其产生影响。 如果所指定的某一引用为三维引用，函数 SUBTOTAL 将返回错误值 #VALUE!。 SUMSQ返回参数的平方和。语法SUMSQ(number1,number2, .)Number1, number2, . 为 1 到 30 个需要求平方和的参数，也可以使用数组或对数组的引用来代替以逗号分隔的参数。TRUNC将数字的小数部分截去，返回整数。语法TR

22、UNC(number,num_digits)Number 需要截尾取整的数字。Num_digits 用于指定取整精度的数字。Num_digits 的默认值为 0。说明函数 TRUNC 和函数 INT 类似，都返回整数。函数 TRUNC 直接去除数字的小数部分，而函数 INT 则是依照给定数的小数部分的值，将其四舍五入到最接近的整数。函数 INT 和函数 TRUNC 在处理负数时有所不同：MODE返回在某一数组或数据区域中出现频率最多的数值。同 MEDIAN 一样，MODE 也是一个位置测量函数。语法MODE(number1,number2,.)Number1, number2, . 是用于众数

23、计算的 1 到 30 个参数，也可以使用单一数组（即对数组区域的引用）来代替由逗号分隔的参数。说明 参数可以是数字，或者是包含数字的名称、数组或引用。 如果数组或引用参数包含文本、逻辑值或空白单元格，则这些值将被忽略；但包含零值的单元格将计算在内。 如果数据集合中不含有重复的数据，则 MODE 数返回错误值 N/A。 在一组数值中，众数是出现频率最高的数值，而中位数是位于中间的值，平均数是平均后的值，所有这些求中函数都不能单独地完全描绘所有数据。例如，假设数据分布在三个区域中，其中一半分布在一个较小数值区中，另外一半分布在两个较大数值区中。函数 AVERAGE 和函数 MEDIAN 可能会返回

24、位于数据点稀疏处的中间值；而函数 MODE 则会返回位于数据点密集处的较小值。SMALL返回数据集中第 k 个最小值。使用此函数可以返回数据集中特定位置上的数值。语法SMALL(array,k)Array 为需要找到第 k 个最小值的数组或数字型数据区域。K 为返回的数据在数组或数据区域里的位置（从小到大）。说明 如果 array 为空，函数 SMALL 返回错误值 #NUM!。 如果 k 0 或 k 超过了数据点个数，函数 SMALL 返回错误值 #NUM!。 如果 n 为数组中的数据点个数，则 SMALL(array,1) 等于最小值，SMALL(array,n) 等于最大值。 TRIMM

25、EAN返回数据集的内部平均值。函数 TRIMMEAN 先从数据集的头部和尾部除去一定百分比的数据点，然后再求平均值。当希望在分析中剔除一部分数据的计算时，可以使用此函数。语法TRIMMEAN(array,percent)Array 为需要进行整理并求平均值的数组或数值区域。Percent 为计算时所要除去的数据点的比例，例如，如果 percent = 0.2，在 20 个数据点的集合中，就要除去 4 个数据点 (20 x 0.2)：头部除去 2 个，尾部除去 2 个。说明 如果 percent 1，函数 TRIMMEAN 返回错误值 #NUM!。 函数 TRIMMEAN 将除去的数据点数目向下

26、舍入为最接近的 2 的倍数。如果 percent = 0.1，30 个数据点的 10% 等于 3 个数据点。函数 TRIMMEAN 将对称地在数据集的头部和尾部各除去一个数据。 三、字符串函数&将多个单元格字符串连接成一个字符串用法：numb1&numb2&LEFT也应用于：LEFTBLEFT 基于所指定的字符数返回文本字符串中的第一个或前几个字符。LEFTB 基于所指定的字节数返回文本字符串中的第一个或前几个字符。此函数用于双字节字符。语法LEFT(text,num_chars)LEFTB(text,num_bytes)Text 是包含要提取字符的文本字符串。Num_chars 指定要由 L

27、EFT 所提取的字符数。 Num_chars 必须大于或等于 0。 如果 num_chars 大于文本长度，则 LEFT 返回所有文本。 如果省略 num_chars，则假定其为 1。Num_bytes 按字节指定要由 LEFTB 所提取的字符数。EXACT该函数测试两个字符串是否完全相同。如果它们完全相同，则返回 TRUE；否则，返回 FALSE。函数 EXACT 能区分大小写，但忽略格式上的差异。利用函数 EXACT 可以测试输入文档内的文本。语法EXACT(text1,text2)Text1 待比较的第一个字符串。Text2 待比较的第二个字符串。FIND也应用于：FINDBFIND 用

28、于查找其他文本字符串 (within_text) 内的文本字符串 (find_text)，并从 within_text 的首字符开始返回 find_text 的起始位置编号。也可使用 SEARCH 查找其他文本字符串中的某个文本字符串，但是，FIND 和 SEARCH 不同，FIND 区分大小写并且不允许使用通配符。FINDB 用于查找其他文本字符串 (within_text) 内的文本字符串 (find_text)，并基于每个字符所使用的字节数从 within_text 的首字符开始返回 find_text 的起始位置编号。此函数用于双字节字符。也可使用 SEARCHB 查找其他文本字符串中

29、的某个文本字符串。语法FIND(find_text,within_text,start_num)FINDB(find_text,within_text,start_num)Find_text 是要查找的文本。Within_text 是包含要查找文本的文本。Start_num 指定开始进行查找的字符。within_text 中的首字符是编号为 1 的字符。如果忽略 start_num，则假设其为 1。提示使用 start_num 可跳过指定数目的字符。例如，假定使用文本字符串“AYF0093.YoungMensApparel”，如果要查找文本字符串中说明部分的第一个“Y”的编号，则可将 start_num 设置为 8，这样就不会查找文本的序列号部分。FIND 将从第 8 个字符开始查找，而在下一个字符处即可找到 find_text，于是返回编号 9。FIND 总是从 within_text 的起始处返回字符编号，如果 start_num 大于 1，也会对跳过的字符进行计数。说明 如果 find_text 是空文本 ()，则 FIND