excel之常用函数
程序员文章站
2022-05-31 23:28:28
...
1、IF
IF(判断条件,满足条件的返回值,不满足条件的返回值)
例：IF(A2>90,"优秀",IF(A2>75,"良好",IF(A2>60,"及格","不及格")))
2、SUMIF（单条件求和）
SUMIF(条件范围,条件,求和范围)
例：SUMIF(B2:B9,"male",A2:A9)，对B2到B9中为male的值对应的A2到A9的值求和。
3、SUMIFS（多条件求和）
SUMIFS（求和范围，条件1范围，条件1，条件2范围，条件2,……条件N范围，条件N）
例：SUMIFS(A2:A9,B2:B9,"male",A2:A9,">80")，对B2到B9的值为male且A2到A9的值大于80的行对应的A2到A9的值进行求和。
4、COUNTIF（单条件计数）
COUNTIF(条件范围，条件)
例：COUNTIF(A2:A9,">80")，满足A2到A9中的值大于80的记录行个数
5、COUNTIFS（多条件计数）
COUNTIFS（条件范围1，条件1，条件范围2，条件2……条件范围N，条件N）
例：COUNTIFS(A2:A9,">80",B2:B9,"male")，，满足A2到A9中的值大于80并且B2到B9中的值为male的记录行个数。
6、LOOKUP（在单行区域或单列区域中查找值）
1）LOOKUP(lookup_value, lookup_vector, [result_vector])：result_vector参数不填时则返回lookup_vector下一列或下一行对应的值
例：LOOKUP(F2,C2:C9,A2:A9)，在C2到C9中查找F2的值，返回该值对应的A2到A9的值
2）LOOKUP(lookup_value, array)：在array的第一列中查找lookup_value的值，找到后返回对应的array中最后一列的值
例：LOOKUP(F2,C2:E9)，在C2到C9列中查找F2的值，返回该数值所在行对应的E2到E9的值
7、VLOOKUP（列查找）
VLOOKUP（查找的值，查找范围，返回查找范围中的第几列，精准匹配（0或者FALSE）还是模糊匹配（1或者TRUE）），最后一个参数可不填，默认为模糊匹配
例：VLOOKUP(F3,A2:C9,3,0)，在A列中（A2到A9单元格）查找F3单元格的值，返回对应的C列单元格的值（因为第三个参数为3，表示返回第三列的值，这里A、B、C分别为1、2、3列），使用精确匹配（因为最后一个参数为0）
注意vlookup是在查找范围的第一个列中查找条件值，这个不能改变，那么当我需要查找C列的某个值返回对应的A列的值时，应该用LOOKUP，如6中的例子。
8、HLOOKUP（行查找）
HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])：在table_array的第一行中查找lookup_value的值，找到后返回该单元格对应的row_index_num行对应的数据，range_lookup的值为精确匹配（0/FALSE）或者模糊匹配（1/TRUE），可不填，默认值为模糊匹配。当range_lookup为模糊匹配时，table_array第一行必须是降序排列的（...-2、-1、0、1、2、...、A-Z、FALSE、TRUE），否则不能返回正确的结果。
例：HLOOKUP(F4,A4:D9,3,0)，在A4、B4、C4、D4这一行中查找F4单元格的值，找到后返回该单元格所在列对应的第三行单元格的值，以精确匹配查找。
9、MATCH（返回区域中某个值的相对位置）
MATCH(lookup_value,lookup_array,[match_type])，在lookup_array中查找lookup_value的值，返回该值的位置，lookup_array可以是一行或者一列。match_type的值为1，0，-1（默认为1），当为1时，查找小于或等于lookup_value的最大值，lookup_array 参数中的值必须以升序排序，例如：...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE；当为0时，查找完全等于lookup_value的第一个值，lookup_array参数中的值可按任何顺序排列；当为-1时，查找大于或等于lookup_value的最小值，lookup_array 参数中的值必须按降序排列，例如：TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ...。
例：MATCH("小西",C2:C9,0)，在C2到C9这列数据中搜索等于“小西”的数据，返回该数据在C2到C9中的相对位置，C2到C9的位置分别为1、2、...8，假设小西在C5的位置，返回4
MATCHMATCH(39,{24,38,40,59},1)，返回2，没有找到39，那么找小于39的最大值为38，返回38对应的位置为2，如果数组中的数据没有按照升序排列，则无法返回正确的值，例如MATCH(39,{24,38,20,40,59},1)返回的是3
MATCH(39,{59,40,38,24},-1)返回2，找到的是40，40的位置是2
10、INDEX（返回区域中某位置的值，正好与match相反，两个经常搭配使用）
数组形式：INDEX(array, row_num, [column_num])
如果array只包含一行或一列，则相对应的参数 Row_num 或 Column_num 为可选参数。
如果同时使用参数 row_num 和 column_num，函数 INDEX 返回 row_num 和 column_num 交叉处的单元格中的值。
如果将 row_num 或 column_num 设置为 0（零），函数 INDEX 则分别返回整个列或行的数组数值。若要使用以数组形式返回的值，请将 INDEX 函数以数组公式形式输入，对于行以水平单元格区域的形式输入，对于列以垂直单元格区域的形式输入。若要输入数组公式，输入公式后请按 Ctrl+Shift+Enter。
例：
A B C
1 a 21 luci
2 b 22 anna
3 c 25 jack
INDEX(A1:C3,2,2) : 22
INDEX(A1:A3,2) : b
INDEX(A1:C1,2) : 21
选择某列三个单元格，然后输入INDEX(A1:C3,0,2)，按ctrl+shift+enter，三个单元格中自动填充21，22，25
引用形式：INDEX(reference, row_num, [column_num], [area_num])，相对数组形式，reference中可以有多个区域，最后一个参数area_num指定取哪个区域的数据。
例：
1 A B C
2 水果 价格 数量
3 苹果 0.69 40
4 香蕉 0.34 38
5 葡萄 2.80 10
6 柠檬 0.55 15
7 柑桔 0.25 25
8 梨 0.59 40
INDEX(A2:C6,2,3) ： 单元格C3的内容（40）
INDEX((A2:C4,A6:C10),2,2,1)：单元格B3的内容（0.34）
INDEX((A2:C4,A6:C10),2,2,2)：单元格B7的内容（0.25）
11、MID（从字符串中提取指定长度的字符）
MID(text, start_num, num_chars)
例：A2单元格数据为Fluid Flow
=MID(A2,1,5) ：从A2单元格的字符串中第1个字符开始，返回5个字符，结果为Fluid。
=MID(A2,7,20)：从A2内字符串中第7个字符开始，返回20个字符。由于要返回的字符数 (20)大于字符串的长度(10)，所以返回从第7个字符开始到结尾的所有字符。结果为Flow。
=MID(A2,20,5)：因为起始位置大于字符串的长度 (10)，所以返回空文本。
12、SUMPRODUCT（在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。）
SUMPRODUCT(array1, [array2], [array3], ...)
数组参数必须具有相同的维数。否则，函数SUMPRODUCT将返回#VALUE!错误值#REF!。
函数SUMPRODUCT将非数值型的数组元素作为 0 处理。
例：=SUMPRODUCT(A2:B4, D2:E4) ：两个数组的所有元素对应相乘，然后把乘积相加，即 3*2 + 4*7 + 8*6 + 6*7 + 1*5 + 9*3 = 156
13、OFFSET
以指定的引用为参照系，通过给定偏移量返回新的引用。返回的引用可以为一个单元格或单元区域。可以指定返回的行数或列数。
OFFSET(reference,rows,cols,height,width)
例：
A B C D
1 36 23 78 76
2 45 64 29 83
3 22 87 91 22
4 52 66 31 44
=OFFSET(B2,2,2,1,1) : 以B2单元格为起点，向下向右各偏移两个单元格，取1行1列即单元格D4的值（44）
=SUM(OFFSET(A2:B4,-1,0,4,2)) 以A2为起点，向上偏移一行到A1，列不偏移（因为参数为0），从A1为起点取4行2列的单元格区域求和，即对区域A1到B4求和（395）
=OFFSET(C3:E5,0,-3,3,3) 返回错误值（#REF），-3表示从C列向左移动三列，已经超出工作表，所以报错
IF(判断条件,满足条件的返回值,不满足条件的返回值)
例：IF(A2>90,"优秀",IF(A2>75,"良好",IF(A2>60,"及格","不及格")))
2、SUMIF（单条件求和）
SUMIF(条件范围,条件,求和范围)
例：SUMIF(B2:B9,"male",A2:A9)，对B2到B9中为male的值对应的A2到A9的值求和。
3、SUMIFS（多条件求和）
SUMIFS（求和范围，条件1范围，条件1，条件2范围，条件2,……条件N范围，条件N）
例：SUMIFS(A2:A9,B2:B9,"male",A2:A9,">80")，对B2到B9的值为male且A2到A9的值大于80的行对应的A2到A9的值进行求和。
4、COUNTIF（单条件计数）
COUNTIF(条件范围，条件)
例：COUNTIF(A2:A9,">80")，满足A2到A9中的值大于80的记录行个数
5、COUNTIFS（多条件计数）
COUNTIFS（条件范围1，条件1，条件范围2，条件2……条件范围N，条件N）
例：COUNTIFS(A2:A9,">80",B2:B9,"male")，，满足A2到A9中的值大于80并且B2到B9中的值为male的记录行个数。
6、LOOKUP（在单行区域或单列区域中查找值）
1）LOOKUP(lookup_value, lookup_vector, [result_vector])：result_vector参数不填时则返回lookup_vector下一列或下一行对应的值
例：LOOKUP(F2,C2:C9,A2:A9)，在C2到C9中查找F2的值，返回该值对应的A2到A9的值
2）LOOKUP(lookup_value, array)：在array的第一列中查找lookup_value的值，找到后返回对应的array中最后一列的值
例：LOOKUP(F2,C2:E9)，在C2到C9列中查找F2的值，返回该数值所在行对应的E2到E9的值
7、VLOOKUP（列查找）
VLOOKUP（查找的值，查找范围，返回查找范围中的第几列，精准匹配（0或者FALSE）还是模糊匹配（1或者TRUE）），最后一个参数可不填，默认为模糊匹配
例：VLOOKUP(F3,A2:C9,3,0)，在A列中（A2到A9单元格）查找F3单元格的值，返回对应的C列单元格的值（因为第三个参数为3，表示返回第三列的值，这里A、B、C分别为1、2、3列），使用精确匹配（因为最后一个参数为0）
注意vlookup是在查找范围的第一个列中查找条件值，这个不能改变，那么当我需要查找C列的某个值返回对应的A列的值时，应该用LOOKUP，如6中的例子。
8、HLOOKUP（行查找）
HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])：在table_array的第一行中查找lookup_value的值，找到后返回该单元格对应的row_index_num行对应的数据，range_lookup的值为精确匹配（0/FALSE）或者模糊匹配（1/TRUE），可不填，默认值为模糊匹配。当range_lookup为模糊匹配时，table_array第一行必须是降序排列的（...-2、-1、0、1、2、...、A-Z、FALSE、TRUE），否则不能返回正确的结果。
例：HLOOKUP(F4,A4:D9,3,0)，在A4、B4、C4、D4这一行中查找F4单元格的值，找到后返回该单元格所在列对应的第三行单元格的值，以精确匹配查找。
9、MATCH（返回区域中某个值的相对位置）
MATCH(lookup_value,lookup_array,[match_type])，在lookup_array中查找lookup_value的值，返回该值的位置，lookup_array可以是一行或者一列。match_type的值为1，0，-1（默认为1），当为1时，查找小于或等于lookup_value的最大值，lookup_array 参数中的值必须以升序排序，例如：...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE；当为0时，查找完全等于lookup_value的第一个值，lookup_array参数中的值可按任何顺序排列；当为-1时，查找大于或等于lookup_value的最小值，lookup_array 参数中的值必须按降序排列，例如：TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ...。
例：MATCH("小西",C2:C9,0)，在C2到C9这列数据中搜索等于“小西”的数据，返回该数据在C2到C9中的相对位置，C2到C9的位置分别为1、2、...8，假设小西在C5的位置，返回4
MATCHMATCH(39,{24,38,40,59},1)，返回2，没有找到39，那么找小于39的最大值为38，返回38对应的位置为2，如果数组中的数据没有按照升序排列，则无法返回正确的值，例如MATCH(39,{24,38,20,40,59},1)返回的是3
MATCH(39,{59,40,38,24},-1)返回2，找到的是40，40的位置是2
10、INDEX（返回区域中某位置的值，正好与match相反，两个经常搭配使用）
数组形式：INDEX(array, row_num, [column_num])
如果array只包含一行或一列，则相对应的参数 Row_num 或 Column_num 为可选参数。
如果同时使用参数 row_num 和 column_num，函数 INDEX 返回 row_num 和 column_num 交叉处的单元格中的值。
如果将 row_num 或 column_num 设置为 0（零），函数 INDEX 则分别返回整个列或行的数组数值。若要使用以数组形式返回的值，请将 INDEX 函数以数组公式形式输入，对于行以水平单元格区域的形式输入，对于列以垂直单元格区域的形式输入。若要输入数组公式，输入公式后请按 Ctrl+Shift+Enter。
例：
A B C
1 a 21 luci
2 b 22 anna
3 c 25 jack
INDEX(A1:C3,2,2) : 22
INDEX(A1:A3,2) : b
INDEX(A1:C1,2) : 21
选择某列三个单元格，然后输入INDEX(A1:C3,0,2)，按ctrl+shift+enter，三个单元格中自动填充21，22，25
引用形式：INDEX(reference, row_num, [column_num], [area_num])，相对数组形式，reference中可以有多个区域，最后一个参数area_num指定取哪个区域的数据。
例：
1 A B C
2 水果 价格 数量
3 苹果 0.69 40
4 香蕉 0.34 38
5 葡萄 2.80 10
6 柠檬 0.55 15
7 柑桔 0.25 25
8 梨 0.59 40
INDEX(A2:C6,2,3) ： 单元格C3的内容（40）
INDEX((A2:C4,A6:C10),2,2,1)：单元格B3的内容（0.34）
INDEX((A2:C4,A6:C10),2,2,2)：单元格B7的内容（0.25）
11、MID（从字符串中提取指定长度的字符）
MID(text, start_num, num_chars)
例：A2单元格数据为Fluid Flow
=MID(A2,1,5) ：从A2单元格的字符串中第1个字符开始，返回5个字符，结果为Fluid。
=MID(A2,7,20)：从A2内字符串中第7个字符开始，返回20个字符。由于要返回的字符数 (20)大于字符串的长度(10)，所以返回从第7个字符开始到结尾的所有字符。结果为Flow。
=MID(A2,20,5)：因为起始位置大于字符串的长度 (10)，所以返回空文本。
12、SUMPRODUCT（在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。）
SUMPRODUCT(array1, [array2], [array3], ...)
数组参数必须具有相同的维数。否则，函数SUMPRODUCT将返回#VALUE!错误值#REF!。
函数SUMPRODUCT将非数值型的数组元素作为 0 处理。
例：=SUMPRODUCT(A2:B4, D2:E4) ：两个数组的所有元素对应相乘，然后把乘积相加，即 3*2 + 4*7 + 8*6 + 6*7 + 1*5 + 9*3 = 156
13、OFFSET
以指定的引用为参照系，通过给定偏移量返回新的引用。返回的引用可以为一个单元格或单元区域。可以指定返回的行数或列数。
OFFSET(reference,rows,cols,height,width)
例：
A B C D
1 36 23 78 76
2 45 64 29 83
3 22 87 91 22
4 52 66 31 44
=OFFSET(B2,2,2,1,1) : 以B2单元格为起点，向下向右各偏移两个单元格，取1行1列即单元格D4的值（44）
=SUM(OFFSET(A2:B4,-1,0,4,2)) 以A2为起点，向上偏移一行到A1，列不偏移（因为参数为0），从A1为起点取4行2列的单元格区域求和，即对区域A1到B4求和（395）
=OFFSET(C3:E5,0,-3,3,3) 返回错误值（#REF），-3表示从C列向左移动三列，已经超出工作表，所以报错