Excel2010常用函数

1、Excel2010 常用函数 SUM(number1,number2,.) 例如，SUM(A1:A5) 将单元格 A1 至 A5 中的所有数字相加，再如，SUM(A1, A3, A5) 将单元格 A1、A3 和 A5 中的数字相加。 SUMIF(range, criteria, sum_range)   例如，假设在含有数字的某一列中，需要让大于 5 的数值相加，请使

2、用以下公式： =SUMIF(B2:B25,">5") SUMIF 函数语法具有以下参数： range  必需。用于条件计算的单元格区域。每个区域中的单元格都必须是数字或名称、数组或包含数字的引用。空值和文本值将被忽略。  criteria  必需。用于确定对哪些单元格求和的条件，其形式可以为数字、表达式、单元格引用、文本或函数。例如，条件可以表示为 32、">32"、B5、32、"32"、"苹果"

3、; 或 TODAY()。  要点  任何文本条件或任何含有逻辑或数学符号的条件都必须使用双引号 (") 括起来。如果条件为数字，则无需使用双引号。 sum_range  可选。要求和的实际单元格（如果要对未在 range 参数中指定的单元格求和）。如果 sum_range 参数被省略，Excel 会对在 range 参数中指定的单元格（即应用条件的单元格）求和。 SUMIFS(sum_range,

4、 criteria_range1, criteria1, criteria_range2, criteria2, .) 对区域中满足多个条件的单元格求和。例如，如果需要对区域 A1:A20 中符合以下条件的单元格的数值求和：B1:B20 中的相应数值大于零 (0) 且 C1:C20 中的相应数值小于 10，则可以使用以下公式： =SUMIFS(A1:A20, B1:B20, ">0", C1

5、:C20, "<10") SUMIFS 函数语法具有以下参数 （参数：为操作、事件、方法、属性、函数或过程提供信息的值。）： sum_range  必需。对一个或多个单元格求和，包括数字或包含数字的名称、区域或单元格引用 （单元格引用：用于表示单元格在工作表上所处位置的坐标集。例如，显示在第 B 列和第 3 行交叉处的单元格，其引用形式为“B3”。）。忽略空白和文本值。  criteria_range1  必需

6、。在其中计算关联条件的第一个区域。  criteria1  必需。条件的形式为数字、表达式、单元格引用或文本，可用来定义将对 criteria_range1 参数中的哪些单元格求和。例如，条件可以表示为 32、">32"、B4、"苹果" 或 "32"。  criteria_range2, criteria2,   可选。附加的区域及其关联条件。最多允许 127 

7、个区域/条件对。 SUMPRODUCT(array1, array2, array3, .) （1）计算工作表内多列中对应值相乘之后的和，即乘积之和。其中，Array1, array2, array3, 为 2 到 30 个数组，其相应元素需要进行相乘并求和。数组参数必须具有相同的维数，否则，函数 SUMPRODUCT 将返回错误值 #VALUE!。该函数将非数值型的数组元素作为 0 处理。（2）SUMPRODUCT函数乘积之和举例ABCD11210公式： =SUMPRODUCT(A1:B3, C1:D3)，说明：两个数组的所有元素对应相乘，然

8、后把乘积相加，即 1*10 + 2*20 + 3*30 + 4*40 + 5*50 + 6*60(结果为910)2023430403565060（3）多条件求和+求个数、使用SUMPRODUCT进行多条件计数  ，计数就是统计满足条件的个数。语法：SUMPRODUCT(条件1)*(条件2)*(条件3)* (条件n)作用：统计同时满足条件1、条件2到条件n的记录的个数。实例：=SUMPRODUCT(A2:A10="男")*(B2:B10="中级职称")，公式解释：统计性别为男且职称为中级职称的职工的人数、使用SUMPRODUCT进行多条件求和 &

9、#160;语法：SUMPRODUCT(条件1)*(条件2)* (条件3) *(条件n)*某区域)  作用：  汇总同时满足条件1、条件2到条件n的记录指定区域的汇总金额。实例： =SUMPRODUCT(A2:A10="男")*(B2:B10="中级职称")*C2:C10)  公式解释：统计性别为男性且职称为中级职称的职工的工资总和（假设C列为工资）SUMPRODUCT(条件1*条件2*条件3.条件N)利用此函数进行多条件计数时，* ：满足所有条件；SUMPRODUCT(条件1+条件2+条件3.+条件N) + ：满足任一条件例

10、子一：=SUMPRODUCT(G1:G3="男")*(E1:E3<=60)这个公式的意思就是要这样来统计，G1:G3满足条件是男的，同时E1:E3的数值小于等于60，满足这两个条件的总人数。首先这是一个数组公式，要按Ctrl+Shift+Enter结束。例子二：多条件求和求男性及格人数：=SUMPRODUCT(C2:C13>=60)*1,(B2:B13="男")*1)例子三：多条件求和汇总三班籍贯为浙男性人数，=SUMPRODUCT(B2:B13="男")*1,(D2:D13="三班")*1,(E2:E

11、13="浙")*1)PRODUCT(number1, number2, .)  PRODUCT 函数使所有以参数形式给出的数字相乘并返回乘积。例如，如果单元格 A1 和 A2 中包含数字，则可以使用公式 =PRODUCT(A1,A2) 将这两个数字相乘。您也可以通过使用乘 (*) 数学运算符（例如 =A1*A2）执行相同的操作。当需要使很多单元格相乘时，PRODUCT 函数很有用。例如，公式 =PRODUCT(A1:A3, C1:C3) 等价于 =A1 *

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

13、例如，如果 value1 到 value7 表示一周的 7 天，那么将 1 到 7 之间的数字用作index_num时，CHOOSE 将返回其中的某一天。index_num    必需。用于指定所选定的数值参数。index_num必须是介于 1 到 254 之间的数字，或是包含 1 到 254 之间的数字的公式或单元格引用。value1, value2, .    Value1 是必需的，后续值是可选的。 1 到 254 个数值参数，CHOOSE 将根据index_num从中选择一个数值或一项要执行的操作。参数可以

14、是数字、单元格引用、定义的名称、公式、函数或文本。如：=CHOOSE(2,A2,A3,A4,A5) 是第二个列表参数的值（单元格 A3 中的值）AVERAGE(number1, number2, .) 公式 =AVERAGE(A1:A20) 将返回这些数字的平均值。再例如：AVERAGE(A1，A5，B4)将单元格A1、A5、B4之和的平均数返回给应用的单元格。  AVERAGEIF(range, criteria, average_range)range 必需。要计算平均值的一个或多个单元格，其中包括数字或包含数字的名称、

15、数组或引用。 criteria 必需。数字、表达式、单元格引用或文本形式的条件，用于定义要对哪些单元格计算平均值。例如，条件可以表示为 32、"32"、">32"、"苹果" 或 B4。average_range可选。要计算平均值的实际单元格集。如果忽略，则使用 range。IF(logical_test, value_if_true, value_if_false) Logical_test表示计算结果为 TRUE 或 FALSE 的任意值或表达式。Value_if_truelogical_test

16、为 TRUE 时返回的值。Value_if_falselogical_test为 FALSE 时返回的值。如果指定条件的计算结果为 TRUE，IF 函数将返回某个值；如果该条件的计算结果为 FALSE，则返回另一个值。例如，如果 A1 大于 10，公式 =IF(A1>10,"大于 10","不大于 10") 将返回“大于 10”，如果 A1 小于等于 10，则返回“不大于 10”。 LEFT

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

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

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

20、;start_num 加上 num_chars 超过了文本的长度，则 MID 只返回至多直到文本末尾的字符。  如果start_num小于 1，则 MID 返回错误值 #VALUE!。如果 num_chars 是负数，则 MID 返回错误值 #VALUE!MIDB(text, start_num, num_bytes)  MID 和 MIDB 函数语法具有下列参数 （参数：为操作、事件、方法、属性

21、、函数或过程提供信息的值。）： Text  必需。包含要提取字符的文本字符串。  Start_num  必需。文本中要提取的第一个字符的位置。文本中第一个字符的 start_num 为 1，依此类推。  Num_chars  必需。指定希望 MID 从文本中返回字符的个数。  Num_bytes  必需。指定希望 MIDB 从文本中返回字符的个数（字节数）。 INDE

22、X(array, row_num, column_num) 返回表格或数组中的元素值，此元素由行号和列号的索引值给定。 MATCH(lookup_value, lookup_array, match_type)  返回行号。可在单元格区域 （区域：工作表上的两个或多个单元格。区域中的单元格可以相邻或不相邻）中搜索指定项，然后返回该项在单元格区域中的相对位置。例如，如果单元格区域 A1:A3 包含值 5、25 和 38，则以下公式：=MATCH(25,A1:

23、A3,0)会返回数字 2，因为值 25 是单元格区域中的第二项。 VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)  是用于在一组数组里查找某一个数据。然后返回所对应需要返回的参数。1、Lookup_value是需要查找的参数.2、table_array是需要查找参数的查找区域。3、col_index_num是lookup_value在table_arry查找到过后返回的对应的参数列。4、  range_lo

24、okup是模糊匹配TRUE（或不填或填1则模糊匹配） /FALSE或填0则精确匹配。VLOOKUP函数的第三个参数是查找返回值所在的列数，如果我们需要查找返回多列时，这个列数值需要一个个的更改，比如返回第2列的，参数设置为2，如果需要返回第3列的，就需要把值改为3。如果有十几列会很麻烦的。那么能不能让第3个参数自动变呢？向后复制时自动变为2，3，4，5。       在EXCEL中有一个函数COLUMN，它可以返回指定单元格的列数，比如       

25、60; =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

26、是按行查找的，VLOOKUP是按列查找的。POWER(number, power) 返回数字乘幂的结果。 POWER 函数语法具有下列参数： Number  必需。底数，可以为任意实数。  Power  必需。指数，底数按该指数次幂乘方。 可以使用“”代替 POWER，以表示基数乘幂运算的幂，例如 52。RANK(number,ref,order)  是返回一列数字的数字排位。数字的排位是其相对于列表中其他值的大小。（如果要对列表进行排序，则数字排位可作为

27、其位置。 RANK 函数语法具有下列参数 （参数：为操作、事件、方法、属性、函数或过程提供信息的值。）： Number   必需。需要找到排位的数字。  Ref   必需。数字列表数组或对数字列表的引用。Ref 中的非数值型值将被忽略。  Order   可选。一数字，指明数字排位的方式。  如果 order 为 0（零）或省略，Microsoft Exce

28、l 对数字的排位是基于 ref 为按照降序排列的列表。 如果 order 不为零，Microsoft Excel 对数字的排位是基于 ref 为按照升序排列的列表。 RANK.EQ(number,ref,order)是回一列数字的数字排位。其大小与列表中其他值相关；如果多个值具有相同的排位，则返回该组值的最高排位。如果要对列表进行排序，则数字排位可作为其位置。Number    必需。要找到其排位的数字。Ref   

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

30、有相同的排位，则将返回平均排位。Number    必需。要找到其排位的数字。Ref    必需。数字列表的数组，对数字列表的引用。 Ref 中的非数字值会被忽略。Order    可选。一个指定数字排位方式的数字。SQRT(number)  number 指需要计算平方根的数 MOD(number, divisor) MOD 函数语法具有下列参数： Number  必需。被除

31、数。  Divisor  必需。除数。 MAX(number1, number2, .) max函数语法具有下列： Number1, number2, .  Number1 是必需的，后续数值是可选的。这些是要从中找出最大值的返回给引用的单元格。 MIN(number1, number2, .) MIN 函数语法具有下列： Number1, number2, . &#

32、160;Number1 是必需的，后续数值是可选的。这些是要从中查找最小值的值的返回给引用的单元格 COUNT(A1:A20) COUNT 函数计算包含数字的单元格以及参数列表中数字的个数。使用函数 COUNT 可以获取区域或数字数组中数字字段的输入项的个数。例如，输入以下公式可以计算区域 A1:A20 中数字的个数： COUNTA(value1, value2, .) COUNTA 函数计算表中不为空的单元格的个数。COUNTIF(range, crit

33、eria)  例如： =COUNTIF(B2:B25,"Nancy") COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2) COUNTIFS 函数语法具有以下参数： criteria_range1  必需。在其中计算关联条件的第一个区域。  criteria1  必需。条件的形式为数字、表达式、单元格引用或文本，可用来定义将对哪些单元格进行

34、计数。例如，条件可以表示为 32、">32"、B4、"苹果" 或 "32"。  criteria_range2, criteria2, .  可选。附加的区域及其关联条件。最多允许 127 个区域/条件对。 要点  每一个附加的区域都必须与参数 criteria_range1 具有相同的行数和列数。这些区域无需彼此相邻。 OFFSET(reference,row

35、s,cols,height,width)Offset函数主要应用在单元格区域的定位和统计方面，一般做数据透视表定义名称都需要用到Offset函数。Offset函数属于查找与引用类的函数。OFFSET函数以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域，并可以指定返回的行数或列数。OFFSET函数的语法是：OFFSET(reference,rows,cols,height,width)，按照中文的说法即是：OFFSET(引用区域,行数,列数,高度,宽度)Reference 作为偏移量参照系的引用区域。Reference 必须为对单元格或相连单元格区域的引用

36、；否则，函数 OFFSET 返回错误值 #VALUE!。Rows 相对于偏移量参照系的左上角单元格，上（下）偏移的行数。如果使用 5 作为参数 Rows，则说明目标引用区域的左上角单元格比 reference 低 5 行。行数可为正数（代表在起始引用的下方）或负数（代表在起始引用的上方）。Cols 相对于偏移量参照系的左上角单元格，左（右）偏移的列数。如果使用 5 作为参数 Cols，则说明目标引用区域的左上角的单元格比 reference 靠右 5 列。列数可为正数（代表在起始引用的右边）或负数（代表在起始引用的左边）。Height 高度，即所要返回的引用区域的行数。Height

37、必须为正数。Width 宽度，即所要返回的引用区域的列数。Width 必须为正数。COLUMN(reference)reference为需要得到其列标的单元格或单元格区域。含义是返回一引用的列号。ROW(reference)reference为需要得到其行号的单元格或单元格区域。含义是返回一个引用的行号。TEXT(value,format_text)使用函数 TEXT 可以将数值转换为带格式的文本，而其结果将不再作为数字参与计算。Value 为数值、计算结果为数字值的公式，或对包含数字值的单元格的引用。Format_text为"单元格格式"对话框中"数字"

38、;选项卡上"分类"框中的文本形式的数字格式。Format_text不能包含星号 (*)。通过"格式"菜单调用"单元格"命令，然后在"数字"选项卡上设置单元格的格式，只会更改单元格的格式而不会影响其中的数值。Format_text参数代码(常用)格式代码数字显示的值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及格