工作必备的13个数据引用函数技巧解读，通俗易懂、易学易用

在前面的学习中，给大家介绍了查找函数Choose、Lookup、Hlookup、Vlookup、Match、INdex的应用技巧，今天，小编带给大家的是13个引用表中数据的函数应用技巧。

一、Address。

功能：返回指定行号和列号对应的单元格地址。

语法结构：=Address(行号,列号,[引用类型],[引用样式],[外部引用的工作表名称])。

注意事项：

1、参数“引用类型”有4种，当为“1或省略”时，绝对引用行和列；当为2时，绝对引用行，相对引用列；当为3时，相对引用行，绝对引用列；当为4时，相对引用行和列。

2、参数“引用样式”的值有2种，A1和R1C1，表示单元格地址是A1引用样式还是R1C1引用样式，该参数是一个逻辑值，如果为TRUE或省略，Address函数将返回A1引用样式；如果为FALSE，Address函数将返回R1C1引用样式。

3、参数“外部引用的工作表名称”，表示用于指定作为外部引用的工作表的名称，省略时表示不使用任何工作表名。

目的：获取最高“月薪”的位置。

方法：

在目标单元格中输入公式：=ADDRESS(MAX(IF(G3:G12=MAX(G3:G12),ROW(3:12))),7)。

解读：

公式首先使用IF函数判断G3:G12单元格区域中等于该区域最大值的单元格，然后返回最大值对应的行号，其他不是最大值的则返回FALSE，组成一个包含FALSE和最大值行号的数组，最后再使用Max函数从该数组中取出最大值，即最大值所在的行号。最后使用Address函数从第7列和最大值所在行号确定最大值所在的位置。

二、Areas。

功能：返回引用中包含的区域个数，可以是连续的单元格区域或某个单元格。

语法结构：=Areas(单元格或单元格区域的引用)。

注意事项：

当引用多个区域时，每个区域之间必须用逗号分隔，且每个区域都必须用括号括起来。

目的：统计数据表的列数。

方法：

在目标单元格中输入公式：=AREAS((B2,C2,D2,E2,F2,G2))。

解读：

因为公式中使用了多个区域引用，因此需要使用一对括号将所有区域括起来，否则公式会出错哦！

三、Column。

功能：返回单元格或单元格区域首列的行号。

语法结构：=Column([单元格或单元格区域])。

注意事项：

1、参数“单元格或单元格区域”省略时，返回当前单元格所在列列号。

2、参数“单元格或单元格区域”不能同时引用多个区域。

目的：计算员工的总“月薪”。

方法：

在目标单元格中输入公式：=SUM(IF(MOD(COLUMN(A:E),2)=1,A3:E12))，并用Ctrl+Shift+Enter填充。

解读：

由于要求和的值在C、E奇数列，所以先用Column函数获取A至E列的列号，然后使用Mod函数判断各列的列号能否被2整除，用IF函数根据判断结果返回该列包含的数据，否则返回空值，最后使用Sum函数对返回的数组求和。

四、Columns。

功能：返回单元格区域或数组中包含的列数。

语法结构：=Columns(单元格区域或数组)。

目的：计算扣款项数量。

方法：

在目标单元格中输入公式：=COLUMNS(C:H)。

五、Row。

功能：返回单元格或单元格区域首行的行号。

语法结构：=Row([单元格或单元格区域])。

注意事项：

1、参数“单元格或单元格区域”省略时，返回当前单元格所在行的行号。

2、Row函数不能同时引用多个区域。

目的：返回月份。

方法：

在目标单元格中输入公式：=TEXT(ROW()-2,"0月")。

解读：

由于第一个目标单元格在第3行，所以修正（-2）之后才能得到1，然后用Text函数将其设置为“月份”的格式。

六、Rows。

功能：返回数据区域包含的行数。

语法结构：=Rows(单元格区域或数组)。

目的：计算员工数量。

方法：

在目标单元格中输入公式：=ROWS(B3:B12)。

七、Offset。

功能：根据给定的偏移量返回新的引用区域。

语法结构：=Offset(参照引用区域,偏移量行数,偏移量列数,[返回区域的行数],[返回区域的列数])。

注意事项：

1、参数“偏移量行数”是相对于“参照引用区域”的左上角单元格，上（下）偏移的行数。行数可正可负，如果为正数，则表示在起始引用的下方；如果为负数，则表示在起始引用的上方。

2、参数“偏移量列数”是相对于“参照引用区域”的左上角单元格，左（右）偏移的列数。列数可正可负，如果为正数，则表示在起始引用的右侧；如果为负数，则表示在起始引用的左侧。

3、Offset函数通过两个阶段的操作来得到最终的单元格区域。

4、如果省略“偏移量行数”、“偏移量列数”，将其当做0处理，即新基点与原始基点在同一位置上，Offset函数不进行任何偏移操作。

5、省略“偏移量行数”和“偏移量列数”时，要保留他们的逗号分隔。

目的：查询员工的“月薪”。

方法：

在目标单元格中输入公式：=OFFSET(B3,MATCH(J3,B3:B12,0)-1,MATCH(K2,B2:G2,0)-1)。

解读：

利用Match函数定位“员工”所在的行数和“月薪”的列数，并-1修正后作为Offset函数的参数，以B3单元格为基点单元格，以修正后的行数和列数为参数，得到新的目标区域。

八、Transpose。

功能：转置数据区的行列位置。

语法结构：=Transpose(单元格区或数组)。

注意事项：

使用Transpose函数时，必须以数组的形式输入到单元格区域中。

目的：对数据源进行行、列转置。

方法：

选定目标单元格区域，输入公式：=TRANSPOSE(B2:G12)，Ctrl+Shift+Enter填充。

九、Indirect。

功能：返回由文本值指定的引用。

语法结构：=Indirect(单元格引用,[引用样式])。

注意事项：

1、参数“引用样式”为一个逻辑值，如果为TRUE或省略，使用A1引用样式；如果为FALSE，使用R1C1样式。

2、参数“单元格引用”如果设置为带双引号的单元格引用，将返回双引号内的单元格内容；如果使用不带双引号的单元格引用，那么将返回该引用指向的单元格内容。

目的：在多个区域中统计“月薪”≤2000元的人数。

方法：

在目标单元格中输入公式：=SUM(COUNTIF(INDIRECT({"c3:c12","e3:e12"}),"<=2000"))。

解读：

由于Countif函数只能使用一个单元格区域，因此使用Indirect函数以文本的形式同时引用2个不相邻的区域，然后使用Countif函数对该区域进行条件判断，最后使用Sum函数求和。

十、Formulatext。

功能：返回给定引用公式的文本形式。

语法结构：=Formulatext(单元格或单元格区域)。

注意事项：

1、参数“单元格或单元格区域”可以引用当前工作簿或其他已打开工作簿中的工作表中的单元格或单元格区域，如果引用了未打开的工作簿或不存在的工作表，Formulatext函数将返回错误值“#N/A” 。

2、参数“单元格或单元格区域”中不包含公式，或者单元格中的公式超过8192个字符，Formulatext函数将返回错误值“#N/A”。

目的：提取指定单元格的公式文本。

方法：

在目标单元格中输入公式：=FORMULATEXT(F3)。

十一、GetPivotData。

功能：返回存储在数据透视表中的数据。

语法结构：=GetPivotData(检索字段名称,透视表单元格地址或区域,[要检索的数据的字段名],[要检索的数据的项名称]……)。

注意事项：

1、参数“检索字段名称”必须用哪个双引号（""）括起来。

2、参数“透视表单元格地址或区域”，主要改单元格地址或区域位于想使用的数据范围内即可，主要用于确定使用的是哪个数据透视表。

3、参数“要检索的数据的字段名和项名称”表示1-126个要检索的数据的字段名和项名称，必须用双引号（""）括起来，可以以任何顺序排列。

4、如果“透视表单元格地址或区域”中指定的内容并非位于数据透视表区域中，GetPivotData函数将返回错误值“#REF!” ；如果包含两个或两个以上的数据透视表区域，GetPivotData函数将使用最新创建的数据透视表返回所需数据。

5、参数“要检索的数据的字段名”和“要检索的数据的项名称”必须成对出现，一个相关的字段和项不能与另一个相关的字段和项交错，否则返回错误值“#REF！”。

6、如果某个项包含日期，则必须以日期序列号或使用Date函数来标识，这样可以在其他位置打开工作表时值仍然存在。

目的：从数据透视表中提取“婚姻”状态下相应“学历”的总“月薪”。

方法：

在目标单元格中输入公式：=IFERROR(GETPIVOTDATA("求和项:月薪",A2,E2,F2,E3,F3),"")。

解读：

由于相应的“学历”状态下“婚姻”状态只有一项，会返回错误值，所以使用Iferror对有错误项的值进行隐藏。

十二、Hyperlink。

功能：为指定内容创建超链接。

语法结构：=Hyperlink(目标文件的完整路径,[单元格中显示的跳转文本或数字值])。

注意事项：

1、Hyperlink函数除了打开存储在网络服务器、Internet或本地磁盘中的文件外，还可以建立工作簿内容的跳转位置，但是跳转到的目标位置必须是非隐藏的单元格。

2、参数“目标文件的完整路径”，必须用双引号（""）括起来。

3、参数“单元格中显示的跳转文字或数字值”省略时，默认值为参数“目标文件的完整路径”中设置的内容，该内容显示为蓝色带下划线。

目的：打开目标文件的位置。

方法：

在目标单元格中输入公式：=HYPERLINK("I:Excel函数公式（二）20-9-18 工作必备的13个数据引用函数技巧解读","打开")。

解读：

“I:Excel函数公式（二）20-9-18 工作必备的13个数据引用函数技巧解读”为需要打开文件的位置，“打开”为需要显示的字段。

十三、Rtd。

功能：返回支持Com自动化程序的实时数据。

语法结构：=Rtd(Com自动化宏ID名称,宏服务器名称,第一个实时数据项……)。

注意事项：

必须在本地计算机上创建并注册RTD COM自动化加载宏。如果未安装实时数据服务器，在使用RTD函数时将在单元格中显示错误值。

解读：

由于Rtd函数需要创建并注册RTD COM自动化加载宏，所以在此不再赘述。