excel常用函数笔记.doc

清洗处理类：3-10 关联匹配类：1-2，11-141）Vlookup()VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)。VLOOKUP(查找值，查找范围，查找列数，精确匹配或者近似匹配）vlookup就是竖直查找，即列查找。通俗的讲，根据查找值参数，在查找范围的第一列搜索查找值，找到该值后，则返回值为：以第一列为准，往后推数查找列数值的这一列所对应的值。以后几乎都使用精确匹配，最后项的参数一定要选择为false。需求：A分销商需要7/31销量数据操作：在I 3 单元格输入：=VLOOKUP(H3,$A$3:$F$19,5,FALSE)分析：H3为我们想查找的型号，即iphone5。为什么要写H3，而不是直接写iphone5，因为方便公式进行拖拽填充，以及保证准确性。$A$3:$F$19为我们需要在此范围内做查找，为什么要加上绝对引用呢，因为下面的ip4和剩余的查找都引用这个区域，即我们的数据源，加上了绝对引用后，就可以对公式进行直接的拖拽。5 从我们的数据源第一列起，我们要查询的7/31号的销量在我引用的第一列（即型号列）后面的第五列。注意这里的列数是从引用范围的第一列做为1，而不是以A列作为第一列，万万注意此处。2）Lookup()1 矢量形式的 LOOKUP矢量形式的 LOOKUP 在一行或一列区域（称为矢量）中查找值，然后返回另一行或一列区域中相同位置处的值。如果要指定其中包含要匹配的值的区域，请使用这种形式的 LOOKUP 函数。矢量形式的语法LOOKUP(lookup_value,lookup_vector,result_vector)Lookup_value 是 LOOKUP 在第一个矢量中搜索到的值。Lookup_value 可以是数字、文本、逻辑值，也可以是代表某个值的名称或引用。 Lookup_vector 是一个仅包含一行或一列的区域。lookup_vector 中的值可以是文本、数字或逻辑值。重要说明：lookup_vector 中的值必须按升序顺序排列。例如，-2、-1、0、1、2 或 A-Z 或 FALSE、TRUE。否则，LOOKUP 返回的值可能不正确。大写和小写文本是等效的。Result_vector 是一个仅包含一行或一列的区域。它的大小必须与 lookup_vector 相同。从A1:A11找D2（3），返回同行的C列的值a注意：如果 LOOKUP 找不到 lookup_value，它会匹配 lookup_vector 中小于或等于 lookup_value 的最大值。 A列找不到6 就返回A列5同行的C列值g 如果 lookup_value 小于 lookup_vector 中的最小值，则 LOOKUP 会返回 #N/A 错误值。2 数组形式的LOOKUP数组形式的 LOOKUP 在数组的第一行或列中查找指定值，然后返回该数组的最后一行或列中相同位置处的值。如果要匹配的值位于数组的第一行或列中，请使用这种形式的 LOOKUP。数组形式的语法LOOKUP(lookup_value,array)Lookup_value 是 LOOKUP 在数组中搜索到的值。Lookup_value 可以是数字、文本、逻辑值，也可以是代表某个值的名称或引用。 如果 LOOKUP 找不到 lookup_value，它会使用该数组中小于或等于 lookup_value 的最大值。如果 lookup_value 小于第一行或列（取决于数组维度）中的最小值，则 LOOKUP 会返回 #N/A 错误值。Array 是一个单元格区域，其中包含要与 lookup_value 进行比较的文本、数字或逻辑值。数组形式的 LOOKUP 与 HLOOKUP 函数和 VLOOKUP 函数相似。其区别是 HLOOKUP 在第一行中搜索 lookup_value，VLOOKUP 在第一列中进行搜索，而 LOOKUP 根据数组的维度进行搜索。如果 array 所覆盖区域的宽度大于高度（列多于行），则 LOOKUP 会在第一行中搜索 lookup_value。如果 array 所覆盖的区域是正方形或者高度大于宽度（行多于列），则 LOOKUP 会在第一列中进行搜索。使用 HLOOKUP 和 VLOOKUP 时，可以向下索引或交叉索引，但 LOOKUP 始终会选择行或列中的最后一个值。重要说明：array 中的值必须按升序顺序排列。例如，-2、-1、0、1、2 或 A-Z 或 FALSE、TRUE。否则，LOOKUP 返回的值可能不正确。大写和小写文本是等效的。从A1:C11 查找D6（4），返回最后一列同样位置的f3）TRIM()TRIM() 去掉字符串的两边空格，类似于python字符串函数str.strip()字符串中间的空格可以用 SUBSTITUTE()SUBSTITUTE(text,old_text,new_text,instance_num) 类似于python的replace()Text 为需要替换其中字符的文本，或对含有文本的单元格的引用。 Old_text 为需要替换的旧文本。 New_text 用于替换 old_text 的文本。 Instance_num 为一数值，用来指定以 new_text 替换第几次出现的 old_text。如果指定了 instance_num，则只有满足要求的 old_text 被替换；否则将用 new_text 替换 TEXT 中出现的所有 old_text。4）CONCATENATE ()CONCATENATE (text1,text2,.) Text1, text2, . 为 1 到 30 个将要合并成单个文本项的文本项。这些文本项可以为文本字符串、数字或对单个单元格的引用。 也可以用 &（和号）运算符代替函数 CONCATENATE 实现文本项的合并。5）Replace()=Replace（指定字符串，哪个位置开始替换，替换几个字符，替换成什么）=REPLACE(abcdefg,1,2,aaa) 结果6）LeftRightMid=Mid(指定字符串，开始位置，截取长度)7）LenLenb返回字符串的长度，在len中，中文计算为一个，在lenb中，中文计算为两个。8）FindFind（要查找的文本，文本所在的单元格，从第几个字符开始查找可选，省略默认为1，从第一个开始查找） 类似于python的str.find()查找某字符串出现的位置，可以指定为第几次出现，与LeftRightMid结合能完成简单的文本提取注意：指定查找起始位置start_num为3，是从第3个字符开始查找，但结果还是从文本开头计算。所以返回的是9区分大小写Find函数是精确查找，区分大小写。Search函数是模糊查找，不区分大小写。9）Search和Find类似，区别是Search大小写不敏感，但支持*通配符search函数的参数find_text可以使用通配符“*”，“？”。通配符星号“*”可代表任何字符串,所以返回1如果参数find_text就是问号或星号，则必须在这两个符号前加上“”符号。10）TextTEXT(value,format_text) Value 为数值、计算结果为数字值的公式，或对包含数字值的单元格的引用。 Format_text 为“单元格格式”对话框中“数字”选项卡上“分类”框中的文本形式的数字格式。 说明 Format_text 不能包含星号 (*)。 通过“格式”菜单调用“单元格”命令，然后在“数字”选项卡上设置单元格的格式，只会更改单元格的格式而不会影响其中的数值。使用函数 TEXT 可以将数值转换为带格式的文本，而其结果将不再作为数字参与计算。 11）index返回表或区域中的值或值的引用。函数INDEX()有两种形式：数组和引用。数组形式通常返回数值或数值数组；引用形式通常返回引用。 INDEX(array,Row_num,column_num) 返回数组中指定单元格或单元格数组的数值。 INDEX(reference,Row_num,column_num,area_num) 返回引用中指定单元格区域的引用。语法 1（数组） INDEX(array,Row_num,column_num) Array 为单元格区域或数组常量。 如果数组只包含一行或一列，则相对应的参数 Row_num 或 column_num 为可选。 如果数组有多行和多列，但只使用 Row_num 或 column_num，函数 INDEX 返回数组中的整行或整列，且返回值也为数组。 Row_num 数组中某行的行序号，函数从该行返回数值。如果省略 Row_num，则必须有 column_num。 Column_num 数组中某列的列序号，函数从该列返回数值。如果省略 column_num，则必须有 Row_num。 如果同时使用 Row_num 和 column_num，函数 INDEX 返回 Row_num 和 column_num 交叉处的单元格的数值。 如果将 Row_num 或 column_num 设置为 0，函数 INDEX 则分别返回整个列或行的数组数值。若要使用以数组形式返回的值，请将 INDEX 函数以数组公式（ 数组公式对一组或多组值执行多重计算，并返回一个或多个结果。数组公式括于大括号 ( ) 中。按 Ctrl+Shift+Enter 可以输入数组公式。）形式输入，对于行以水平单元格区域的形式输入，对于列以垂直单元格区域的形式输入。若要输入数组公式，请按 Ctrl+Shift+Enter。 语法 2（引用） 返回指定的行与列交叉处的单元格引用。如果引用由不连续的选定区域组成，可以选择某一连续区域。 INDEX(reference,Row_num,column_num,area_num) Reference 对一个或多个单元格区域的引用。 如果为引用输入一个不连续的区域，必须用括号括起来。 如果引用中的每个区域只包含一行或一列，则相应的参数 Row_num 或 column_num 分别为可选项。例如，对于单行的引用，可以使用函数 INDEX(reference,column_num)。 Row_num 引用中某行的行序号，函数从该行返回一个引用。 COLUMN_num 引用中某列的列序号，函数从该列返回一个引用。 Area_num 选择引用中的一个区域，并返回该区域中 Row_num 和 column_num 的交叉区域。选中或输入的第一个区域序号为 1，第二个为 2，以此类推。如果省略 area_num，函数 INDEX 使用区域 1。 例如，如果引用描述的单元格为 (A1:B4,D1:E4,G1:H4)，则 area_num 1 为区域 A1:B4，area_num 2 为区域 D1:E4，而 area_num 3 为区域 G1:H4 在通过 reference 和 area_num 选择了特定的区域后，Row_num 和 column_num 将进一步选择指定的单元格：Row_num 1 为区域的首行，column_num 1 为首列，以此类推。函数 INDEX 返回的引用即为 Row_num 和 column_num 的交叉区域。 如果将 Row_num 或 column_num 设置为 0，函数 INDEX 分别返回对整个列或行的引用。 Row_num、column_num 和 area_num 必须指向 reference 中的单元格；否则，函数 INDEX 返回错误值 #REF!。如果省略 Row_num 和 column_num，函数 INDEX 返回由 area_num 所指定的区域。 函数 INDEX 的结果为一个引用，且在其他公式中也被解释为引用。根据公式的需要，函数 INDEX 的返回值可以作为引用或是数值。例如，公式 CELL(width,INDEX(A1:B2,1,2) 等价于公式 CELL(width,B1)。CELL 函数将函数 INDEX 的返回值作为单元格引用。而在另一方面，公式 2*INDEX(A1:B2,1,2) 将函数 INDEX 的返回值解释为 B1 单元格中的数字。 Eg1：=INDEX(A2:C11,3,3)返回区域A2:C11中第3行和第3列交叉处的单元格C4的引用。(15)Eg2：=INDEX(A1:C6,A8:C11),2,2,1) (A1:C6,A8:C11)为两个区域， 2,2,1 指获取第一个区域的第二行第二列交叉处的引用（0.69）Eg3：=SUM(INDEX(A1:C6,A8:C11),0,2,2) 得到第二个区域A8:C11第二列之和（9.35）Eg4：=SUM(B2:INDEX(A2:C6,5,2) 由INDEX(A2:C6,5,2)得到A2:C6区域第五行第二列交叉处的引用（B6），再执行SUM(B2:B6)得到累加和（2.42）12）MatchMATCH(LOOKUP_VALUE, LOOKUP_ARRAY, MATCH_TYPE) 返回在指定方式下与指定数组匹配的数组中元素的相应位置。如果需要找出匹配元素的位置而不是匹配元素本身，则应该使用 MATCH 函数而不是 LOOKUP 函数。Lookup_value 为需要在数据表中查找的数值。 Lookup_value 为需要在 Look_array 中查找的数值。例如，如果要在电话簿中查找某人的电话号码，则应该将姓名作为查找值，但实际上需要的是电话号码。 Lookup_value 可以为数值（数字、文本或逻辑值）或对数字、文本或逻辑值的单元格引用。 Lookup_array 可能包含所要查找的数值的连续单元格区域。lookup_array 应为数组或数组引用。Match_type 为数字 -1、0 或 1。MATCH-type 指明 WPS表格 如何在 lookup_array 中查找 lookup_value。 如果 Match_type 为 1，函数 MATCH 查找小于或等于 lookup_value 的最大数值。lookup_array 必须按升序排列：.、-2、-1、0、1、2、.、A-Z、FALSE、TRUE。 如果 Match_type 为 0，函数 MATCH 查找等于 lookup_value 的第一个数值。lookup_array 可以按任何顺序排列。 如果 Match_type 为 -1，函数 MATCH 查找大于或等于 lookup_value 的最小数值。lookup_array 必须按降序排列：TRUE、FALSE、Z-A、.、2、1、0、-1、-2、.，等等。 如果省略 Match_type，则假设为 1。 说明 函数 MATCH 返回 lookup_array 中目标值的位置，而不是数值本身。例如，MATCH(b,a,b,c,0) 返回 2，即“b”在数组 a,b,c 中的相应位置。 查找文本值时，函数 MATCH 不区分大小写字母。 如果函数 MATCH 查找不成功，则返回错误值 #N/A。 如果 MATCH_type 为 0 且 lookup_value 为文本，lookup_value 可以包含通配符、星号 (*) 和问号 (?)。星号可以匹配任何字符序列；问号可以匹配单个字符。 13）Row / ColumnRow返回单元格所在的行Row(reference)=row() 公式所在行的行号=ROW(D4:E6) 引用中的第一行的行号(4)Column 与之类似 返回列14）OffsetOffset（指定点，偏移多少行，偏移多少列，返回多少行，返回多少列）以指定点为原点建立坐标系，返回距离原点的值或者区域。正数代表向下或向右，负数则相反。选中e1：f1，输入=OFFSET(A1:B1,3,1)，按下CTRL+SHIFT+回车键。得出结果：0.55，15OFFSET(reference,rows,cols,height,width)不写入返回行列数height,width，就默认与reference行列数相同函数 OFFSET 实际上并不移动任何单元格或更改选定区域，它只是返回一个引用。函数 OFFSET 可用于任何需要将引用作为参数的函数。例如，公式 SUM(OFFSET(C2,1,2,3,1) 将计算比单元格 C2 靠下 1 行并靠右 2 列的 3 行 1 列的区域的总值。 逻辑运算类：15-1815）IfIF(LOGICAL_TEST, VALUE_IF_TRUE, VALUE_IF_FALSE)IF(设置的条件，满足条件返回的结果，不满足条件返回的结果)16）AndAND(LOGICAL1, LOGICAL2, .)所有参数的逻辑值为真时，返回 TRUE；只要一个参数的逻辑值为假，即返回 FLASE17）Or18）IS系列常用判断检验，返回的都是布尔数值True和False。常用ISERR，ISERROR，ISNA，ISTEXT，可以和IF嵌套使用。计算统计类：19-2919）SUM/SUNMIF/SUMIFSSUMIFS(SUM_RANGE, CRITERIA_RANGE1, CRITERIA1, CRITERIA_RANGE2, CRITERIA2, )sum_range 对一个或多个单元格求和，包括数值或包含数值的名称、区域或单元格引用。忽略空白和文本值。criteria_range1 在其中计算关联条件的第一个区域。criteria1 条件的形式为数字、表达式、单元格引用或文本，可用来定义将对 criteria_range1 参数中的哪些单元格求和。如，条件可以表示为 41、41、D3、香蕉 或 41。说明仅在 sum_range 参数中的单元格满足所有相应的指定条件时，才对该单元格求和。例如，假设一个公式中包含两个 criteria_range 参数。如果 criteria_range1 的第一个单元格满足 criteria1，而 criteria_range2 的第一个单元格满足 critera2，则 sum_range 的第一个单元格计入总和中。对于指定区域中的其余单元格，依此类推。sum_range 中包含 TRUE 的单元格计算为 1；sum_range 中包含 FALSE 的单元格计算为 0（零）。与 SUMIF 函数中的区域和条件参数不同，SUMIFS 函数中每个 criteria_range 参数包含的行数和列数必须与 sum_range 参数相同。您可以在条件中使用通配符，即问号 (?) 和星号 (*)。问号匹配任一单个字符；星号匹配任一字符序列。若要查找实际的问号或星号，请在字符前键入波形符 ()。=SUMIFS(A2:A7,B2:B7,=花*,C2:C7,2)计算以“花”开头并由销售人员 2 售出的产品的总量 (20)=SUMIF(B2:B7,=花*,A2:A7) 计算以“花”开头总销售量（30）=SUM(A2:A7) 计算以销售产品总量20）SUMPRODUCT在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。SUMPRODUCT（array1, array2, array3, .）数组参数必须具有相同的维数，否则，函数 SUMPRODUCT 将返回错误值 #VALUE!。函数 SUMPRODUCT 将非数值型的数组元素作为 0 处理。=SUMPRODUCT(A2:B4,C2:D4) 两个数组的所有元素对应相乘，然后把乘积相加，即 3*2 + 4*7 + 8*6 + 6*7 + 1*5 + 9*3。(156)=SUMPRODUCT(A2:A4,B2:B4) 相当于计算3*4+8*6+1*9 = 6921）CountCountifCountifsCOUNTIFS(CRITERIA_RANGE1, CRITERIA1, CRITERIA_RANGE2, CRITERIA2)将条件应用于跨多个区域的单元格，并计算符合所有条件的次数。criteria_range1 在其中计算关联条件的第一个区域。 criteria1 条件的形式为数字、表达式、单元格引用或文本，可用来定义将对哪些单元格进行计数。如，条件可以表示为 41、41、D3、香蕉 或 41。criteria_range2, criteria2, . 附加的区域及其关联条件。最多允许 127 个区域/条件对。每个区域的条件一次应用于一个单元格。若所有的第一个单元格都满足其关联条件，则计数增加 1。如果所有的第二个单元格都满足其关联条件，则计数再增加 1，依此类推，直到计算完所有单元格。若条件参数是对空单元格的引用，COUNTIFS 会将该单元格的值视为 0(零)。您可以在条件中使用通配符，即问号 (?) 和星号 (*)。问号匹配任一单个字符；星号匹配任一字符序列。若要查找实际的问号或星号，请在字符前键入波形符 ()。=COUNTIFS(A2:A7,2) 计算 2 到 10 之间（不包括 2 和 10）有几个数包含在单元格 A2 到 A7 中（3）=COUNTIFS(A2:A7,2011-3-4)计算单元格 A2 到 A7 中包含小于 12 的数，同时在单元格 B2 到 B7 中包含晚于2011-3-4的日期的个数（3）=COUNTIF(A2:A7,5) 计算A2:A7单元格内容大于5的个数（4）=COUNT(B2:B4)计算B2:B4包含数字的单元格数量（3）=COUNT(B2:B4,1) 在上面基础上，再加一（4） 函数 COUNT 在计数时，将把数字、日期、或以文本代表的数字计算在内；但是错误值或其他无法转换成数字的文字将被忽略。 如果参数是一个数组或引用，那么只统计数组或引用中的数字；数组或引用中的空白单元格、逻辑值、文字或错误值都将被忽略。如果要统计逻辑值、文字或错误值，请使用函数 CountA，返回非空单元格数22）Max/Min返回数组或引用区域的最大/小值23）RankRANK(number,ref,order)返回一个数字在数字列表中的排位。数字的排位是其大小与列表中其他值的比值（如果列表已排过序，则数字的排位就是它当前的位置）。Number 为需要找到排位的数字。Ref 为数字列表数组或对数字列表的引用。Ref 中的非数值型参数将被忽略。Order 为一数字，指明排位的方式。如果 order 为 0（零）或省略，对数字的排位是基于 ref 为按照降序排列的列表。如果 order 不为零，对数字的排位是基于 ref 为按照升序排列的列表。说明函数 RANK 对重复数的排位相同。但重复数的存在将影响后续数值的排位。例如，在一列整数里，如果整数 10 出现两次，其排位为 5，则 11 的排位为 7（没有排位为 6 的数值）。=RANK(A3, A2:A7, 1) 返回A3在A2:A7中升序排列的排名(2)=RANK(A3, A2:A7) 返回A3在A2:A7中降序排列的排名(5)24）RAND/RANDBETWEEN常用随机抽样，前者返回01之间的随机值，后者可以指定范围。25）Average/Averagea/Averageif/AverageifsAVERAGEA(value1,value2,.) 说明 参数必须为数值、名称、数组或引用。 包含文本的数组或引用参数将作为 0（零）计算。空文本 () 也作为 0（零）计算。如果在平均值的计算中不能包含文本值，请使用函数 AVERAGE。 包含 TRUE 的参数作为 1 计算；包含 FALSE 的参数作为 0 计算。 =AVERAGEA(B2:B7) 对B2:B7的时间数据 按照通用数值 计算平均值AVERAGEIF(range,criteria,average_range)range 要计算平均值的一个或多个单元格，其中包括数字或包含数字的名称、数组或引用。criteria 数字、表达式、单元格引用或文本形式的条件，用于定义要对哪些单元格计算平均值。如，条件可以表示为 41、41、D3、香蕉 或 41。average_range 要计算平均值的实际单元格集。若忽略，则使用 range。=AVERAGEIF(B2:B5,23100) =AVERAGEIF(A2:A5,250100,B2:B5) 求所有财产值大于 250,100 的佣金的平均值 (24,600)AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2,criteria2)=AVERAGEIFS(D2:D5,D2:D5,不及格,D2:D5,80) 计算所有学生最终测验成绩超过 80 分的分数的平均值 (86.5)。标记为“不及格”的分数不计算在内，因为它不是一个数字值。=AVERAGEIFS(D2:D5,D2:D5,80) 一样得出结果（86.5）是因为文本值当作0，不大于8026）Quartile先普及四分位数概念：四分位数（Quartile）是指在统计学中把所有数值由小到大排列并分成四等份，处于三个分割点位置的数值。多应用于统计学中的箱线图绘制。求法：实例1数据总量: 6, 47, 49, 15, 42, 41, 7, 39, 43, 40, 36由小到大排列的结果: 6, 7, 15, 36, 39, 40, 41, 42, 43, 47, 49一共11项Q1 的位置=（11+1） 0.25=3， Q2 的位置=（11+1） 0.5=6， Q3的位置=（11+1） 0.75=9Q1 = 15，Q2 = 40，Q3 = 43实例2数据总量: 7, 15, 36, 39, 40, 41一共6项数列项为偶数项时，四分位数Q2为该组数列的中数，（n+1）/4= 7/4 =1.75，Q1在第一与第二个数字之间，3（n+1）/4= 21/4 =5.25, Q3在第五与第六个数字之间，Q1 = 0.75*15+0.25*7 = 13，Q2 = （36+39）/2= 37.5，Q3 = 0.25*41+0.75*40 = 40.25.QUARTILE(array, quart)返回数据集的四分位数。四分位数通常用于在销售额和测量数据中对总体进行分组。例如，可以使用函数 QUARTILE 求得总体中前 25% 的收入值。Array 需要求得四分位数值的数组或数字型单元格区域。Quart 决定返回哪一个四分位值。注意：若数组为空，则 QUARTILE 返回错误值 #NUM!。若 quart 4，则 QUARTILE 返回错误值 #NUM!。若 quart 不为整数，将被截尾取整。当 quart 分别等于 0、2 和 4 时，函数 MIN、MEDIAN 和 MAX 返回的值与函数 QUARTILE 返回的值相同。=QUARTILE(A1:A4,3) 求出Q3(9.75)27）Stdev 估算样本的标准偏差。标准偏差反映相对于平均值 (mean) 的离散程度。STDEV(number1, number2, .)28）Substotal返回数据清单或数据库中的分类汇总。通常，使用“数据”菜单中的“分类汇总”命令可以容易地创建带有分类汇总的数据清单。一旦创建了分类汇总，就可以通过编辑 SUBTOTAL 函数对该数据清单进行修改。SUBTOTAL（function_num, ref1, ref2,.）Function_num 为 1 到 11 之间的数字，指定使用何种函数在数据清单中进行分类汇总计算。29）Int/RoundInt 向下取整 round四舍五入取整时间序列类：30-3530）Year返回