Address等8个引用函数应用技巧解读Word格式.docx
《Address等8个引用函数应用技巧解读Word格式.docx》由会员分享,可在线阅读,更多相关《Address等8个引用函数应用技巧解读Word格式.docx(12页珍藏版)》请在冰豆网上搜索。
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([单元格地址或单元格区域])。
快速输入月份。
=TEXT(COLUMN()-3,"
0月"
)。
首先利用Column函数获取当前单元格所在的列号,并减去3(修正值,否则从4月开始),然后用Text函数将其设置为月份的格式。
汇总多个列中的销量。
=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([单元格或单元格区域])。
省略参数时默认为当前单元格所在行的行号。
在一列中快速的输入月份。
=TEXT(ROW()-2,"
首先用Row函数获取当前单元格的行号,然后-2(修正值,从1月份开始,否则从3月份开始,根据实际情况调整),最后用Text函数将其设置为月份的形式。
提取最后一次销售额>4000的销售日期。
=TEXT(INDEX(D3:
D9,MAX((E3:
E9>
4000)*ROW(3:
9)-2)),"
m月d日"
首先判断E3:
E9区域的值是否>4000,如果条件成立,则返回行号,并将返回的结果作为Index函数的参数,然后用Index函数返回对应的值,最后用Text函数将其设置为时间格式。
六、Rows函数。
返回单元格区域或数组中包含的行数。
=Rows(单元格或单元格区域)。
计算员工数量。
=ROWS(B3:
B9)。
如果销售员列的单元格非空,则用公式=ROWS(B3:
B9)的计算结果是准确的,但如果有空值,则结果并不准确。
计算销售数据中的报价数量。
=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到了吗?
欢迎在留言去留言讨论哦!