Excel常用函数（三）------查找和引用函数

1.ADDRESS

        作用：根据指定行号和列号获得工作表中的某个单元格的地址。（可以使用其他函数（如 ROW 和 COLUMN 函数）为 ADDRESS 函数提供行号和列号参数。）

        语法：ADDRESS(行号, 列号, [引用类型], [引用样式], [工作表])

                     ①引用类型：数值类型。

                                           abs_num                返回的引用类型
                                             1或省略                      绝对值
                                                  2                   绝对行号，相对列标
                                                  3                   相对行号，绝对列标
                                                  4                            相对值

                     ②引用样式：逻辑值，指定是 A1 或 R1C1 引用样式。

                                 A1引用样式：Excel默认，字母加数字的组合。例如B2，A14等。

                               R1C1 引用样：例如 R1C1=A1，R2C1=B1，R1C2=A2

                     ③工作表：文本值，指定要用作外部引用的工作表的名称。

        示例：

                        公式                                说明                                 返回值

              =ADDRESS(2,3,2)        绝对行号，相对列标                        C$2        
         =ADDRESS(2,3,1,FALSE,"EXCEL SHEET")            对另一个工作表的绝对引用            EXCEL SHEET'!R2C3

2.AREA：

        作用：返回引用中的区域（指连续的单元格区域或单个单元格）个数。

        示例：

                 =AREAS(B2:D4)              引用中包含的区域个数                     1
            =AREAS((B2:D4,E5,F6:I9))                                                           3  

3.CHOOSE

        作用：有点类似于SWITCH的用法，根据不同的值返回对应的结果。

        语法：=CHOOSE(索引，区域1，区域2……）

                       索引：为1到254之间的数值。

        示例：=CHOOSE(2,A2,A3,A4,A5)             区域2的值            （单元格 A3 中的值）                
                   =CHOOSE(3,"Wide",115,"world",8)          区域3的值                 world        

4.COLUMN

        作用：返回单元格的列号。

        示例： =COLUMN()                               返回公式所在的列号（如2）            
                    =COLUMN(B13)                                         2            
                    =COLUMN(B2:D2)                         2   3   4(返回一个数组)  

5.COLUMNS

        作用：返回数组或引用的列数。

        示例：=COLUMNS(C1:E4)                                     3          

6.DROP(365)

        作用：从数组的开头或结尾中排除指定数量的行或列。可用于删除 Excel 报表中的页眉和页脚，以仅返回数据。

        语法：=DROP（数组，行，[列]）

        示例：=DROP(A2:C4,2)           删除前 2 行并返回数组的最后一行。                    
                   =DROP(A2:C4,,2)           删除前 2 列并返回数组的最后一列。                    
                   =DROP(A2:C4,-2)           删除最后 2 列并返回数组的第一行。                    
                   =DROP(A2:C4,2,2)          删除前 2 列和行并返回数组的其余部分。 

7.EXPAND （365）

        作用：将数组展开或填充到指定的行和列尺寸。

        语法：=Expand(数组, 展开后的行数, [展开后的列数], [填充空单元格的字符]) 

        示例：=EXPAND(A2:B3,3,3)          将 2X2 数组大小调整为 3X3 数组，并使用 #N/A 填充空元素。（第四个参数如果不填则默认为#N/A）    

                  =EXPAND(A2,3,3, "-")             将 1X1 数组大小调整为 3X3 数组，并使用“-”填充空元素。

8.FILTER  （365）

        作用：根据条件筛选数据。

        语法：=FILTER(数组,条件,[查不到的话的返回值])

        示例：=FILTER(A5:D20,C5:C20=H2,"")     查找A5:D20区域的值，当C5:C20=H2满足的情况下。如果查不到则返回""。  

9.FORMULATEXT （2013）

        作用：用于还原显示单元格中的公式（如果有的话），而不是展示公式的计算结果。

        示例：= FORMULATEXT(D2)     结果为       =TEXTJOIN(";",TRUE,A2,A5,A7,A10) 

10.HLOOKUP 

        作用：在表格的首行或数值数组中搜索值，然后返回表格或数组中指定行的所在列中的值。

        示例：=HLOOKUP("车轴", A1:C4, 2, TRUE)         在首行查找“车轴”，并返回同列（列 A）中第 2 行的值。     

11.HSTACK

        作用：按顺序水平组合数组。

        语法：=HSTACK(数组1，数组2，...)

        示例：=HSTACK(B1:D2,C3:R4,D5:E6)

                        将上面三个范围的数据拼接成一个范围的数组。

12.HYPERLINK 

        作用：创建一个快捷方式，可跳转到当前工作簿中的另一个位置，或打开intranet。

        示例：=HYPERLINK("http://example.microsoft.com/report/budget report.xlsx", "Click for report")    
                     此函数将打开保存在以下位置的工作簿：http://example.microsoft.com/report。 单元格会将 Click for report 显示为跳转文本。

13.IMAGE

        作用：插入图像

        语法：=IMAGE (source、[alt_text]、[大小]、[height]、[width])            
                            第一参数：图片的网址        
                            第二参数：描述图像的辅助功能替代文本        
                            第三参数：图片大小类型        
                            第四参数：图片的高度        
                            第五参数：图片的宽度        

14.INDEX        

        作用：返回表格或区域中的值或值的引用。

        示例：    =INDEX(A2:B3,2,2)          位于区域 A2:B3 中第二行和第二列交叉处的数值。                                 =INDEX({1,2;3,4},0,2)       数组的第一行、第二列中找到的数值。 数组包含第一行中的 1 和 2 以及第二行中的 3 和 4。  

15.INDIRECT 
        作用：返回由文本字符串指定的引用。

        示例：=INDIRECT(A2)       单元格 A2 中的引用值。

16.MATCH

        作用：在单元格中搜索特定的项，然后返回该项在此区域中的相对位置。   

        示例：=MATCH(39,B2:B5,1)   返回单元格区域 B2:B5 中最接近的下个最小值 (38) 的位置。                     =MATCH(41,B2:B5,0)    单元格区域 B2:B5 中值 41 的位置。                        
                        注：第三个参数，   1 或省略：查找小于或等于 查找值（第一个参数） 的最大值                                                            0  ：  查找完全等于 查找值 的第一个值。                    
                                                        -1  ：  查找大于或等于 查找值 的最小值。      

17.OFFSET

        作用：返回对单元格或单元格区域中指定行数和列数的区域的引用。

        示例：=OFFSET(B2,2,1,3,1)      即以单元格B2为基准，向下偏移2行，向右偏移一列，返回三行一列。

18.ROW

        作用：返回引用的行号。
                   无参数。

19.ROWS

        作用：返回引用或数组的行数。
        示例：=ROWS(C1:E4)      返回C1到E4单元格的总行数       4

20.SORT(365)

        作用：对某个区域或数组的内容进行排序。

        语法：=SORT(排序范围,[排序行或列],[升序(1)或降序(-1)],[排序方式(按行或是列)])

        示例：=SORT(A2:C7,3,-1)      排序A2到C7区域，按照第3列排序，降序

21.SORTBY(365)

        作用：对范围或数组的内容进行排序。

        示例：=SORTBY(A3:D10,A3:A10,1,D3:D10,-1)    对A3到D10进行排序，

                                分别按照A3到A10的升序和D3到D10的降序排序。
        注：和SORT函数的区别：SORT函数通过一个且仅一个索引编号对数据集进行排序，

                而SORTBY函数允许按多个条件进行排序，且无需结果中显示这些条件列。

22.TAKE(365)

        作用：从数组的开头或结尾中获取指定数量的连续行或列。

        语法：=TAKE(数组或引用, 要获取的行数,[要获取的列数])

        示例：=TAKE(A2:C4,2)     返回A2到C4的前两行。            
                   =TAKE(A2:C4,,2)    返回A2到C4的前两列。        

23.TOCOL （365）

        作用：返回单列中的数组

        语法：=TOCOL(array, [是否忽略], [按列扫描数组])

                   第二个参数意为是否忽略某些类型的值。不填的话为不忽略任何值。            
                            0       保留所有值（默认）        
                            1       忽略空白        
                            2       忽略错误        
                            3       忽略空白和错误        
                                        第三个参数不填的话为按行扫描。  

24.TOROW(365)

        作用：返回单行中的数组。

        语法和TOCOL函数相同。

        

25.TRANSPOSE 

        作用：将单元格行转列。

        示例：=TRANSPOSE(A1:B4)

26.UNIQUE （365）

        作用：返回列表或范围中的一系列唯一值。（即去重）

        语法：=UNIQUE (array，[如何比较]，[是否仅提取一次])                    
                    第二个参数是boolean值，默认false。                
                        true：按列比较。            
                        false：按行比较。            
                    第三个参数是boolean值，默认false。                
                        true：仅将范围中出现一次的值作为提取对象。            
                        false：将所有的值都作为提取对象。     

27.VLOOKUP

28.VSTACK （365）

        作用：按顺序垂直追加数组，以返回更大的数组。

        语法：=VSTACK(array1,[array2],...)       

29.WRAPCOLS （365）

        作用：将一行或一列数据转换为多行数组。

        语法：=WRAPCOLS(数据区域，转换行数，数据不足要填充的值)

        示例：=WRAPCOLS(A2:A18,3,"填充值")                    
                    将A2到A18的值转换为3行的数据，不足的值用“请充值”字样填充。

30.WRAPROWS （365）
        作用：  将一行或一列数据转换为多列数组。

        语法：=WRAPROWS(数据区域，转换列数，数据不足要填充的值）    

31.XLOOKUP(365)

        作用：搜索区域，返回第一个匹配项。如果找不到，则返回最近似的值。

        语法：=XLOOKUP(查询值, 查询范围, 返回范围, [如果未找到的时候返回的值], [匹配模式], [查询模式])                             
    匹配模式：                        
        0 - 完全匹配。 如果未找到，则返回 #N/A。 这是默认选项。                    
                            
        -1 - 完全匹配。 如果没有找到，则返回下一个较小的项。                    
                            
        1 - 完全匹配。 如果没有找到，则返回下一个较大的项。                    
                            
        2 - 通配符匹配，其中 *, ? 和 ~ 有特殊含义。                    
                            
    查询模式：                        
        1 - 从第一项开始执行搜索。 这是默认选项。                    
                            
        -1 - 从最后一项开始执行反向搜索。                    
                            
        2 - 执行依赖于 lookup_array 按升序排序的二进制搜索。 如果未排序，将返回无效结果。      

32.XMATCH （365）
        作用：在区域中搜索指定项，并返回其相对位置。            
        语法：=XMATCH(查询值, 查询范围, [匹配模式], [查询模式])              
                    后面两个参数和XLOOKUP函数一样。