Excel常用函数.docx

1、Excel常用函数Excel2010常用函数Excel2010常用函数SUM(number1,number2,.)例如，SUM(A1:A5)将单元格A1至A5中的所有数字相加，再如，SUM(A1,A3,A5)将单元格A1、A3和A5中的数字相加。SUMIF(range,criteria,sum_range)例如，假设在含有数字的某一列中，需要让大于5的数值相加，请使用以下公式：=SUMIF(B2:B25,5)SUMIF函数语法具有以下参数：range必需。用于条件计算的单元格区域。每个区域中的单元格都必须是数字或名称、数组或包含数字的引用。空值和文本值将被忽略。criteria必需。用于确定对

2、哪些单元格求和的条件，其形式可以为数字、表达式、单元格引用、文本或函数。例如，条件可以表示为32、32、B5、32、32、苹果或TODAY()。要点任何文本条件或任何含有逻辑或数学符号的条件都必须使用双引号()括起来。如果条件为数字，则无需使用双引号。sum_range可选。要求和的实际单元格（如果要对未在range参数中指定的单元格求和）。如果sum_range参数被省略，Excel会对在range参数中指定的单元格（即应用条件的单元格）求和。SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,.)对区域中

3、满足多个条件的单元格求和。例如，如果需要对区域A1:A20中符合以下条件的单元格的数值求和：B1:B20中的相应数值大于零(0)且C1:C20中的相应数值小于10，则可以使用以下公式：=SUMIFS(A1:A20,B1:B20,0,C1:C20,32、B4、苹果或32。criteria_range2,criteria2,可选。附加的区域及其关联条件。最多允许127个区域/条件对。SUMPRODUCT(array1,array2,array3,.)（1）计算工作表内多列中对应值相乘之后的和，即乘积之和。其中，Array1, array2, array3, 为 2 到 30 个数组，其相应元素需要

4、进行相乘并求和。数组参数必须具有相同的维数，否则，函数 SUMPRODUCT 将返回错误值 #VALUE!。该函数将非数值型的数组元素作为 0 处理。（2）SUMPRODUCT函数乘积之和举例ABCD11210公式： =SUMPRODUCT(A1:B3, C1:D3)，说明：两个数组的所有元素对应相乘，然后把乘积相加，即 1*10 + 2*20 + 3*30 + 4*40 + 5*50 + 6*60(结果为910)2023430403565060（3）多条件求和+求个数、使用SUMPRODUCT进行多条件计数 ，计数就是统计满足条件的个数。语法：SUMPRODUCT(条件1)*(条件2)*(条

5、件3)* (条件n)作用：统计同时满足条件1、条件2到条件n的记录的个数。实例：=SUMPRODUCT(A2:A10=男)*(B2:B10=中级职称)，公式解释：统计性别为男且职称为中级职称的职工的人数)*1,(D2:D13=三班)*1,(E2:E13=浙)*1)PRODUCT(number1,number2,.)PRODUCT函数使所有以参数形式给出的数字相乘并返回乘积。例如，如果单元格 A1 和 A2 中包含数字，则可以使用公式=PRODUCT(A1,A2)将这两个数字相乘。您也可以通过使用乘 (*) 数学运算符（例如=A1*A2）执行相同的操作。当需要使很多单元格相乘时，PRODUCT函

6、数很有用。例如，公式=PRODUCT(A1:A3, C1:C3)等价于=A1 * A2 * A3 * C1 * C2 * C3number1必需。要相乘的第一个数字或范围。number2, .可选。要相乘的其他数字或单元格区域，最多可以使用 255 个参数。注意如果参数是一个数组或引用，则只使用其中的数字相乘。数组或引用中的空白单元格、逻辑值和文本将被忽略。CHOOSE(index_num, value1, value2, .)使用index_num返回数值参数列表中的数值。使用 CHOOSE 可以根据索引号从最多 254 个数值中选择一个。例如，如果 value1 到 value7 表示一周

7、的 7 天，那么将 1 到 7 之间的数字用作index_num时，CHOOSE 将返回其中的某一天。index_num必需。用于指定所选定的数值参数。index_num必须是介于 1 到 254 之间的数字，或是包含 1 到 254 之间的数字的公式或单元格引用。value1, value2, .Value1 是必需的，后续值是可选的。 1 到 254 个数值参数，CHOOSE 将根据index_num从中选择一个数值或一项要执行的操作。参数可以是数字、单元格引用、定义的名称、公式、函数或文本。如：=CHOOSE(2,A2,A3,A4,A5) 是第二个列表参数的值（单元格 A3 中的值）AV

8、ERAGE(number1,number2,.)公式=AVERAGE(A1:A20)将返回这些数字的平均值。再例如：AVERAGE(A1，A5，B4)将单元格A1、A5、B4之和的平均数返回给应用的单元格。AVERAGEIF(range, criteria, average_range)range 必需。要计算平均值的一个或多个单元格，其中包括数字或包含数字的名称、数组或引用。 criteria 必需。数字、表达式、单元格引用或文本形式的条件，用于定义要对哪些单元格计算平均值。例如，条件可以表示为 32、32、32、苹果 或 B4。average_range可选。要计算平均值的实际单元格集。如

9、果忽略，则使用 range。IF(logical_test,value_if_true,value_if_false)Logical_test表示计算结果为 TRUE 或 FALSE 的任意值或表达式。Value_if_truelogical_test为 TRUE 时返回的值。Value_if_falselogical_test为 FALSE 时返回的值。如果指定条件的计算结果为TRUE，IF函数将返回某个值；如果该条件的计算结果为FALSE，则返回另一个值。例如，如果A1大于10，公式=IF(A110,大于10,不大于10)将返回“大于10”，如果A1小于等于10，则返回“不大于10”。LE

10、FT(text,num_chars) Text是包含要提取字符的文本字符串，可以直接输入含有目标文字的单元格名称。Num_chars指定要由LEFT所提取的字符数。Num_chars必须大于或等于0。如果num_chars大于文本长度，则LEFT返回所有文本。如果省略num_chars，则假定其为1。RIGHT(text,num_chars)RIGHTB(text,num_bytes)Text是包含要提取字符的文本字符串，可以直接输入含有目标文字的单元格名称。Num_chars指定希望RIGHT提取的字符数。注意：Num_chars必须大于或等于0。如果num_chars大于文本长度，则RIG

11、HT返回所有文本。如果忽略num_chars，则假定其为1INT(number)Number 需要进行向下舍入取整的实数。MID(text,start_num,num_chars)Text是包含要提取字符的文本字符串，可以直接输入含有目标文字的单元格名称。Start_num是文本中要提取的第一个字符的位置。Num_chars指定希望MID从文本中返回字符的个数。如果start_num大于文本长度，则MID返回空文本()。如果start_num小于文本长度，但start_num加上num_chars超过了文本的长度，则MID只返回至多直到文本末尾的字符。如果start_num小于 1，则 MID

12、 返回错误值 #VALUE!。如果num_chars是负数，则MID返回错误值#VALUE!MIDB(text,start_num,num_bytes)MID和MIDB函数语法具有下列参数（参数：为操作、事件、方法、属性、函数或过程提供信息的值。）：Text必需。包含要提取字符的文本字符串。Start_num必需。文本中要提取的第一个字符的位置。文本中第一个字符的start_num为1，依此类推。Num_chars必需。指定希望MID从文本中返回字符的个数。Num_bytes必需。指定希望MIDB从文本中返回字符的个数（字节数）。INDEX(array,row_num,column_num)返

13、回表格或数组中的元素值，此元素由行号和列号的索引值给定。MATCH(lookup_value,lookup_array,match_type)返回行号。可在单元格区域（区域：工作表上的两个或多个单元格。区域中的单元格可以相邻或不相邻）中搜索指定项，然后返回该项在单元格区域中的相对位置。例如，如果单元格区域A1:A3包含值5、25和38，则以下公式：=MATCH(25,A1:A3,0)会返回数字2，因为值25是单元格区域中的第二项。VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)是用于在一组数组里查找某一个数据。然后返回所对

14、应需要返回的参数。1、Lookup_value是需要查找的参数.2、table_array是需要查找参数的查找区域。3、col_index_num是lookup_value在table_arry查找到过后返回的对应的参数列。4、range_lookup是模糊匹配TRUE（或不填或填1则模糊匹配） /FALSE或填0则精确匹配。VLOOKUP函数的第三个参数是查找返回值所在的列数，如果我们需要查找返回多列时，这个列数值需要一个个的更改，比如返回第2列的，参数设置为2，如果需要返回第3列的，就需要把值改为3。如果有十几列会很麻烦的。那么能不能让第3个参数自动变呢？向后复制时自动变为2，3，4，5。

15、在EXCEL中有一个函数COLUMN，它可以返回指定单元格的列数，比如 =COLUMNS（A1）返回值1 =COLUMNS（B1）返回值2而单元格引用复制时会自动发生变化，即A1随公式向右复制时会变成B1，C1，D1。这样我们用COLUMN函数就可以转换成数字1，2，3，4。HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)LOOKUP函数是Excel等电子表格中的横向查找函数，它与LOOKUP函数和VLOOKUP函数属于一类函数，HLOOKUP是按行查找的，VLOOKUP是按列查找的。POWER(number,power)

16、返回数字乘幂的结果。POWER函数语法具有下列参数：Number必需。底数，可以为任意实数。Power必需。指数，底数按该指数次幂乘方。可以使用“”代替 POWER，以表示基数乘幂运算的幂，例如 52。RANK(number,ref,order)是返回一列数字的数字排位。数字的排位是其相对于列表中其他值的大小。（如果要对列表进行排序，则数字排位可作为其位置。RANK函数语法具有下列参数（参数：为操作、事件、方法、属性、函数或过程提供信息的值。）：Number必需。需要找到排位的数字。Ref必需。数字列表数组或对数字列表的引用。Ref中的非数值型值将被忽略。Order可选。一数字，指明数字排位的

17、方式。如果order为0（零）或省略，MicrosoftExcel对数字的排位是基于ref为按照降序排列的列表。如果order不为零，MicrosoftExcel对数字的排位是基于ref为按照升序排列的列表。RANK.EQ(number,ref,order)是回一列数字的数字排位。其大小与列表中其他值相关；如果多个值具有相同的排位，则返回该组值的最高排位。如果要对列表进行排序，则数字排位可作为其位置。Number必需。要找到其排位的数字。Ref必需。数字列表的数组，对数字列表的引用。 Ref 中的非数字值会被忽略。如果 Order 为 0（零）或省略，Excel 对数字的排位是基于 Ref 为

18、按降序排列的列表。如果 Order 不为零， Excel 对数字的排位是基于 Ref 为按照升序排列的列表。RANK.EQ 赋予重复数相同的排位。但重复数的存在将影响后续数值的排位。例如，在按升序排序的整数列表中，如果数字 10 出现两次，且其排位为 5，则 11 的排位为 7（没有排位为 6 的数值）。RANK.AVG(number,ref,order)是返回一列数字的数字排位：数字的排位是其大小与列表中其他值的比值；如果多个值具有相同的排位，则将返回平均排位。Number必需。要找到其排位的数字。Ref必需。数字列表的数组，对数字列表的引用。 Ref 中的非数字值会被忽略。Order可选。

19、一个指定数字排位方式的数字。SQRT(number)number指需要计算平方根的数MOD(number,divisor)MOD函数语法具有下列参数：Number必需。被除数。Divisor必需。除数。MAX(number1,number2,.)max函数语法具有下列：Number1,number2,.Number1是必需的，后续数值是可选的。这些是要从中找出最大值的返回给引用的单元格。MIN(number1,number2,.)MIN函数语法具有下列：Number1,number2,.Number1是必需的，后续数值是可选的。这些是要从中查找最小值的值的返回给引用的单元格COUNT(A1:A

20、20)COUNT函数计算包含数字的单元格以及参数列表中数字的个数。使用函数COUNT可以获取区域或数字数组中数字字段的输入项的个数。例如，输入以下公式可以计算区域A1:A20中数字的个数：COUNTA(value1,value2,.)COUNTA函数计算表中不为空的单元格的个数。COUNTIF(range,criteria)例如：=COUNTIF(B2:B25,Nancy)COUNTIFS(criteria_range1,criteria1,criteria_range2,criteria2)COUNTIFS函数语法具有以下参数：criteria_range1必需。在其中计算关联条件的第一个区

21、域。criteria1必需。条件的形式为数字、表达式、单元格引用或文本，可用来定义将对哪些单元格进行计数。例如，条件可以表示为32、32、B4、苹果或32。criteria_range2,criteria2,.可选。附加的区域及其关联条件。最多允许127个区域/条件对。要点每一个附加的区域都必须与参数criteria_range1具有相同的行数和列数。这些区域无需彼此相邻。OFFSET(reference,rows,cols,height,width)Offset函数主要应用在单元格区域的定位和统计方面，一般做数据透视表定义名称都需要用到Offset函数。Offset函数属于查找与引用类的函数

22、。OFFSET函数以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域，并可以指定返回的行数或列数。OFFSET函数的语法是：OFFSET(reference,rows,cols,height,width)，按照中文的说法即是：OFFSET(引用区域,行数,列数,高度,宽度)Reference 作为偏移量参照系的引用区域。Reference 必须为对单元格或相连单元格区域的引用；否则，函数 OFFSET 返回错误值 #VALUE!。Rows 相对于偏移量参照系的左上角单元格，上（下）偏移的行数。如果使用 5 作为参数 Rows，则说明目标引用区域的左上角单元

23、格比 reference 低 5 行。行数可为正数（代表在起始引用的下方）或负数（代表在起始引用的上方）。Cols 相对于偏移量参照系的左上角单元格，左（右）偏移的列数。如果使用 5 作为参数Cols，则说明目标引用区域的左上角的单元格比 reference 靠右 5 列。列数可为正数（代表在起始引用的右边）或负数（代表在起始引用的左边）。Height 高度，即所要返回的引用区域的行数。Height 必须为正数。Width 宽度，即所要返回的引用区域的列数。Width 必须为正数。COLUMN(reference)reference为需要得到其列标的单元格或单元格区域。含义是返回一引用的列号。

24、ROW(reference)reference为需要得到其行号的单元格或单元格区域。含义是返回一个引用的行号。TEXT(value,format_text)使用函数 TEXT 可以将数值转换为带格式的文本，而其结果将不再作为数字参与计算。Value 为数值、计算结果为数字值的公式，或对包含数字值的单元格的引用。Format_text为单元格格式对话框中数字选项卡上分类框中的文本形式的数字格式。Format_text不能包含星号 (*)。通过格式菜单调用单元格命令，然后在数字选项卡上设置单元格的格式，只会更改单元格的格式而不会影响其中的数值。Format_text参数代码(常用)格式代码数字显示

25、的值TEXT(A,B)说明G/通用格式1010常规格式“000.0”10.25“010.3”小数点前面不够三位以0补齐，保留1位小数，不足一位以0补齐#10.0010没用的0一律不显示00.#1.25301.25小数点前不足两位以0补齐，保留两位，不足两位不补位正数;负数;零1正数大于0，显示为“正数”正数;负数;零0零等于0，显示为“零”正数;负数;零-1负数小于0，显示为“负数”0000-00-00198205061982-05-06按所示形式表示日期0000年00月00日198205061982年05月06日dddd2007-12-31Monday显示为英文星期几全称=90优秀;=60及

26、格;不及格90优秀大于等于90，显示为“优秀”=90优秀;=60及格;不及格60及格大于等于60，小于90，显示为“及格”=90优秀;=60及格;不及格59不及格小于60，显示为“不及格”DBNum1$-804G/通用格式125一百二十五中文小写数字DBNum2$-804G/通用格式元整 125壹佰贰拾伍元整中文大写数字，并加入“元整”字尾DBNum3$-804G/通用格式1251百2十5中文小写数字DBNum1$-804G/通用格式19一十九中文小写数字，11-19无设置20DBNum1;DBNum1d19十九中文小写数字，11-显示为十一而不是一十一0.00,K1253612.54K以千为

27、单位#!.0000万元125361.2536万元以万元为单位，保留4位小数以万元为单位，保留4位小数125361.3万元以万元为单位，保留1位小数TIME(hour,minute,second)此函数的参数和英文意思一致WEEKDAY(serial_number，return_type)WEEKDAY 用途：返回某日期的星期数。serial_number是要返回日期数的日期，它有多种输入方式：带引号的文本串(如2001/02/26)、序列号(如35825 表示1998 年1月30 日) 或其他公式或函数的结果(如DATEVALUE(2000/1/30)。return_type为确定返回值类型的

28、数字，数字1 或省略则1 至7 代表星期天到星期六，数字2 则1 至7 代表星期一到星期天，数字3则0至6代表星期一到星期天。在默认情况下，它的值为1(星期天)到7(星期六)之间的一个整数。结合例子：我们在A1单元格中输入一个日期：2011年10月15日。通过之前的WEEKDAY函数的方法就是=WEEKDAY(A1,2)就可以得出结果为6，表示今天是星期六。不过文字方面不能满足我们的需求，我们需要显示出“星期六”。可以写成如下：=TEXT(WEEKDAY(A1),“aaaa”)如果想将中文直接转换成英文，可以将第二个参数改为dddd，如下：=TEXT(WEEKDAY(A1),“dddd”)，aaaa和dddd需要加英文引号“ ”。=CHOOSE(WEEKDAY(2011-1-15,2),星期一,星期二,星期三,星期四,星期五,