Address等8个引用函数应用技巧解读.docx

Address等8个引用函数应用技巧解读.docx

《Address等8个引用函数应用技巧解读.docx》由会员分享，可在线阅读，更多相关《Address等8个引用函数应用技巧解读.docx（12页珍藏版）》请在冰豆网上搜索。

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

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

在Excel中，经常将查询函数和引用函数混淆，其实，他们是有区别的，常用的查询函数有：

Choose、Lookup、Vlookup、Hlookup、Match、Index等；而引用函数有：

Address、Areas、Column、Columns、Row、Rows、Offset、Transpose、Indirect，Formulatext、Getpivotdata、Hyperlink等。

一、Address函数。

作用：

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

语法结构：

=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函数的参数，返回对应的内容。

二、Areas函数。

作用：

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

语法结构：

=Areas（单元格或单元格区域的引用）。

说明：

单元格或单元格区域的引用，也可以引用多个区域，但是每个区域之间必须用逗号分隔，且每个区域都必须用括号括起来。

目的：

统计分公司的数量。

方法：

在目标单元格中输入公式：

=AREAS（（B2:

B9,C2:

C9,D2:

D9,E2:

E9））。

解读：

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

三、Column函数。

功能：

返回单元格或单元格区域首列的列号。

语法结构：

=Column（[单元格地址或单元格区域]）。

目的1：

快速输入月份。

方法：

在目标单元格中输入公式：

=TEXT（COLUMN（）-3,"0月"）。

解读：

首先利用Column函数获取当前单元格所在的列号，并减去3（修正值，否则从4月开始），然后用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函数求和。

四、Columns函数。

功能：

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

语法结构：

=Ccolumns（单元格区域或数组）。

目的：

计算需要扣款的项目数量。

方法：

在目标单元格中输入公式：

=COLUMNS（D:

H）。

五、Rows函数。

功能：

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

语法：

=Row（[单元格或单元格区域]）。

说明：

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

目的1：

在一列中快速的输入月份。

方法：

在目标单元格中输入公式：

=TEXT（ROW（）-2,"0月"）。

解读：

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

目的2：

提取最后一次销售额＞4000的销售日期。

方法：

1、在目标单元格中输入公式：

=TEXT（INDEX（D3:

D9,MAX（（E3:

E9>4000）*ROW（3:

9）-2））,"m月d日"）。

2、Ctrl+Shift+Enter填充。

解读：

首先判断E3:

E9区域的值是否＞4000，如果条件成立，则返回行号，并将返回的结果作为Index函数的参数，然后用Index函数返回对应的值，最后用Text函数将其设置为时间格式。

六、Rows函数。

功能：

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

语法结构：

=Rows（单元格或单元格区域）。

目的1：

计算员工数量。

方法：

在目标单元格中输入公式：

=ROWS（B3:

B9）。

解读：

如果销售员列的单元格非空，则用公式=ROWS（B3:

B9）的计算结果是准确的，但如果有空值，则结果并不准确。

目的2：

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

方法：

在目标单元格中输入公式：

=ROWS（3:

9）*COLUMNS（B:

E）/2。

解读：

公式的意思为：

行数乘以列数除以2，因为区域中的一半是文本，所以÷2才是报价的数量。

七、Transpose函数。

功能：

用于返回转置行列位置后的单元格区域。

语法结构：

=Transpose（单元格区域或数组）。

目的：

转换销售数据。

方法：

在目标单元格中输入公式：

=TRANSPOSE（B2:

C9）。

八、Indirect函数。

功能：

返回由文本字符串指定的引用。

语法结构：

=Indirect（单元格引用,[引用样式]）。

说明：

1、引用样式：

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

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

目的：

统计销量＞8000的员工数。

方法：

在目标单元格中输入公式：

=SUM（COUNTIF（INDIRECT（{"c3:

c9","e3:

e9","g3:

g9","i3:

i9"}）,">8000"））。

解读：

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

结束语：

本文结合实际，对常用的引用函数Address等做了详细的介绍，对于应用技巧，你Get到了吗？

欢迎在留言去留言讨论哦！