excel之常用函数

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列向左移动三列，已经超出工作表，所以报错