Address等8个参考函数应用技巧解读

在Excel中，查询函数和引用函数经常被混淆。事实上，它们是不同的。常用的查询函数有：Choose、Lookup、Vlookup、Hlookup、Match、Index等；参考函数有：Address、Areas、Column、Columns、Row、Rows、Offset、Transpose、Indirect、Formulatext、Getpivotdata、Hyperlink 等。

1、地址功能。

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

语法结构：=Address(行号，列号，[返回的引用类型]，[返回的单元格地址样式]，[外部引用工作表名称])。

阐明：

1、返回的引用类型：当该参数省略或者为1时，为行列的绝对引用；为2时，是对行号的绝对引用，对列号的相对引用；当为3时，是对行号的相对引用和对列号的绝对引用；当4 行和列的相对引用。

目的1：返回销售额最高的位置。

方法：

1. 在目标单元格中输入公式：=ADDRESS(MAX(IF(D3:D9=MAX(D3:D9),ROW(3:9))),4)。

2. Ctrl+Shift+Enter 进行填充。

解释：

首先，使用If函数判断D3:D9单元格区域中等于该区域最大值的单元格，然后返回最大值对应的行号。如果不是最大值，则返回False，形成一个包含False和最大值行号的数组。最后使用Max函数获取数组中的最大值，即最大值所在的行号。最后，使用Address函数从第4列开始确定最大值所在的位置和行号。

目的2：返回跨工作表的摘要数据。

方法：

在目标单元格中输入公式：=INDIRECT(ADDRESS(10,4,1,1,'Address'))。

解释：

首先使用Address函数返回工作表“Address”第10行第4列的单元格数据，然后将其作为Indirect函数的参数返回相应的内容。

2.区域功能。

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

语法结构：=Areas（单元格或单元格范围的引用）。

注意：对单元格或单元格区域的引用也可以引用多个区域，但每个区域必须用逗号分隔，并且每个区域必须用括号括起来。

目的：统计分支的数量。

方法：

在目标单元格中输入公式：=AREAS((B2:B9,C2:C9,D2:D9,E2:E9))。

解释：

由于公式中使用了多个范围引用，因此需要使用一对括号将所有范围括起来，否则会出现错误。

3、栏目功能。

功能：返回单元格或单元格区域第一列的列号。

语法结构：=Column([单元格地址或单元格范围])。

目的1：快速输入月份。

方法：

在目标单元格中输入公式：=TEXT(COLUMN()-3,'0month')。

解释：

首先使用Column函数获取当前单元格的列号，减去3（修正值，否则从四月开始），然后使用Text函数将其设置为月份格式。

目的2：汇总多个列的销售额。

方法：

1. 在目标单元格中输入公式：=SUM(IF(MOD(COLUMN(B:I),2),B3:I9))。

2.填写快捷键Ctr+Shift+Enter。

解释：

首先，使用Column函数获取从B列到I列的列号作为Mod函数的参数。由于值列在C、E等奇数列中，因此直接使用If函数来确定Mod函数的返回结果。如果为1，则返回相应的值。数值，否则返回False，最后使用Sum函数计算总和。

4.栏目功能。

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

语法结构：=Ccolumns（单元格区域或数组）。

目的：计算需要扣除的项目数量。

方法：

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

5.行功能。

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

语法：=Row([单元格或单元格范围])。

阐明：

当省略该参数时，默认为当前单元格所在行的行号。

目的1：在列中快速输入月份。

方法：

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

解释：

首先使用Row函数获取当前单元格的行号，然后-2（修正值，从1月开始，否则从3月开始，根据实际情况调整），最后使用Text函数设置为月份的形式。

目的2：提取最后销售金额>4000的销售日期。

方法：

1. 在目标单元格中输入公式：=TEXT(INDEX(D3:D9,MAX((E3:E94000)*ROW(3:9)-2)),'m 月d 日')。

2. Ctrl+Shift+Enter 进行填充。

解释：

首先判断E3:E9区域的值是否>4000。如果条件为真，则返回行号，并将返回结果作为Index函数的参数。然后使用Index函数返回对应的值，最后使用Text函数将其设置为时间格式。

6.行功能。

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

语法结构：=行（单元格或单元格范围）。

目标1：计算员工人数。

方法：

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

解释：

如果销售人员列中的单元格不为空，则使用公式=ROWS(B3:B9)计算结果是准确的，但如果存在空值，则结果不准确。

目的2：计算销售数据中的报价数量。

方法：

在目标单元格中输入公式：=ROWS(3:9)*COLUMNS(B:E)/2。

解释：

公式的意思是：行数乘以列数除以2。由于一半的区域是文本，所以2就是引号的数量。

7. 转置功能。

功能：用于返回行列位置调换后的单元格区域。

语法结构：=Transpose（单元格区域或数组）。

目的：转换销售数据。

方法：

在目标单元格中输入公式：=TRANSPOSE(B2:C9)。

8.间接函数。

功能：返回文本字符串指定的引用。

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

阐明：

1.引用样式：它是一个逻辑值。如果为True 或省略，“单元格引用”将使用A1 样式引用。如果为False，则使用R1C1 样式引用。

2、如果Indirect函数的第一个参数设置为带双引号的单元格引用，则返回双引号内的单元格内容；如果使用不带双引号的单元格引用，则将返回引用中的内容。引用指向的单元格的内容。

目的：统计销售额>8000的员工人数。

方法：

在目标单元格中输入公式：=SUM(COUNTIF(INDIRECT({'c3:c9','e3:e9','g3:g9','i3:i9'}),'8000'))。

解释：

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