indirect公式 Excel利用INDIRECT函数以及数组公式实现查询

Excel利用INDIRECT函数以及数组公式实现查询――简介

在Excel的使用过程中,尤其是制作某些Excel计算表格的时候,当涉及到需要通过已知参数查表得到其他参数值的时候,一般情况下只能人工查询然后输入到Excel内。本文以暖通噪声计算书为例,通过INDIRECT函数以及数组公式的结合,实现Excel内建数据表的查询功能,使工作真正变得智能化

Excel利用INDIRECT函数以及数组公式实现查询――工具/原料

Microsoft Excel

Excel利用INDIRECT函数以及数组公式实现查询――方法/步骤

Excel利用INDIRECT函数以及数组公式实现查询 1、

现有Excel计算书,需要根据“倍频带中心频率”,查询“直管的气流噪声的修正值”,如图。那么需要在图1的F5单元格输入公式,查询的范围是图2的表。这里设定图2所示的表所在的工作表名称为“数据表”,方便引用。


Excel利用INDIRECT函数以及数组公式实现查询 2、

首先,考虑使用INDIRECT函数,INDIRECT函数可以定位单元格,只需要给出单元格的行号和列号,函数的格式为

indirect公式 Excel利用INDIRECT函数以及数组公式实现查询

=INDIRECT("r"&m&"c"&n,FALSE)

这里的r表示行(ROW),c表示列(COLUMN),m为我们需要确定的行号,n为我们需要确定的列号,&用于连接字符串,"r"&m&"c"&n是定位单元格的固定格式。于是我们的重心就转移到表示m和n了。


Excel利用INDIRECT函数以及数组公式实现查询 3、

首先确定m,也就是要确定我们要找的数据在哪一行。很明显,对于这个具体的问题,行号是确定的,为“数据表”的第9行,因此直接用9替换m就好了


Excel利用INDIRECT函数以及数组公式实现查询 4、

之后我们来确定n,这是本公式的重点。n的确定思路是,将我们输入的频率值(E5单元格)与“数据表”第8行列出的各个频率值(数据表!B8:I8)逐一比较,并筛选出比输入频率大的给定频率值,之后取其中最小的一个值的列号,此列号减一即为我们需要的列号。当然,此处默认的选择原则是,频率大于a但小于b的时候,取a的修正值。


Excel利用INDIRECT函数以及数组公式实现查询 5、

数组公式就派上用场了,我们用

=MIN(IF(E5<数据表!B8:I8,COLUMN(数据表!B8:I8),""))-1

来描述上面的思路。首先,最里面的IF函数,会判断E5与数据表!B8:I8各单元格值的大小,并返回满足小于号条件的单元格的列号,比方我们输入的频率是280,那么IF函数会返回5,6,7,8,9这样一个序列,经过MIN函数的筛选,我们得到了最小的5,减一之后就得到了我们要找的列号4。


Excel利用INDIRECT函数以及数组公式实现查询 6、

当然,仅仅这样还不能称之为数组公式,不在数组公式中的IF函数是没有办法执行多次比较的。所以,我们先将m和n的表达式替换到INDIRECT函数中,得到了如下的式子:

=INDIRECT("r"&9&"c"&(MIN(IF(E5<数据表!B8:I8,COLUMN(数据表!B8:I8),""))-1),FALSE)

写完之后,同时按下Ctrl+Shift+Enter生成数组公式,表现为公式两边出现花括号,注意,此花括号不能自己输进去,那样是没有效果的哦


Excel利用INDIRECT函数以及数组公式实现查询 7、

这样,一个简单的Excel查询就做好了,只要在E5单元格输入已知的频率,就能在F5单元格自动得到要求的修正值,相当的智能哦


Excel利用INDIRECT函数以及数组公式实现查询 8、

当然,我们还可以在这个函数的基础上做出一些优化,比如当“数据表”的顶部新增了一行的时候,m的值是固定值9,而不是引用,就无法满足查询要求了,只能手动去修改,因此我们可以对m的值写一段函数来处理


Excel利用INDIRECT函数以及数组公式实现查询 9、

对于m,由于表头和数据之间的位置关系不太可能变动,我们可以利用表头信息来定位,即“直管道气流噪声倍频带修正值”这段文字的行号加2即可,我们用下面的公式处理:

=ROW(INDEX(数据表!$A:$A,MATCH("直管道气流噪声倍频带修正值",数据表!$A:$A,0)))+2

或者直接用"修正值(dB)"这个名称来定位,更加直观准确:

=ROW(INDEX(数据表!$A:$A,MATCH("修正值(dB)",数据表!$A:$A,0)))


Excel利用INDIRECT函数以及数组公式实现查询 10、

解释一下:

MATCH函数用于返回指定值在指定数组区域中的位置,格式为:

=MATCH(lookup_value, lookup_array, match_type)

比方这里的

MATCH("修正值(dB)",数据表!$A:$A,0)

就是在数据表!$A:$A 中,查找"修正值(dB)"这段文字在数据表!$A:$A 中的位置,即从上到下第几个,注意这个返回的值不是真正意义的行号哦,只是在数据表!$A:$A 中的相对行号

INDEX函数根据位置返回区域中的值或对值的引用,格式为:

INDEX(array,row_num,column_num)

这里由于是单列,所以只需要给出一个行序列就可以了,正好就是MATCH函数的结果

ROW函数用于返回具体某个单元格在整个工作表中的绝对行号

于是最终就返回了我要查询行号了


Excel利用INDIRECT函数以及数组公式实现查询 11、

这样,我们用上面的任意一个公式替换m,即便“数据表”增加行,或者做一些不影响表结构的操作,也不会影响到公式的正确查询,得到的公式为:

=INDIRECT("r"&ROW(INDEX(数据表!$A:$A,MATCH("修正值(dB)",数据表!$A:$A,0)))&"c"&(MIN(IF(E5<数据表!B8:I8,COLUMN(数据表!B8:I8),""))-1),FALSE)


Excel利用INDIRECT函数以及数组公式实现查询 12、

公式太长不便于理解和修改,我们可以将m和n的计算公式放在其他任意两个单元格中完成,INDIRECT函数只需要引用这两个单元格就可以了,这就保证了公式的可读性。


Excel利用INDIRECT函数以及数组公式实现查询 13、

其他可优化的地方就请自行探索了!!!!

Excel利用INDIRECT函数以及数组公式实现查询――注意事项

公式输完一定要同时按下Ctrl+Shift+Enter生成数组公式,才能有效哦

  

爱华网本文地址 » http://www.aihuau.com/a/8104120103/184739.html

更多阅读

Excel数组公式-入门 excel函数公式入门

翻译:远景 | 2009-09-26 03:51:22 | 阅读1694 | 来源什么是数组公式?数组公式昰一种专门用于数组的公式类型。数组公式可以可以产生单个结果,也可以同时分列显示多个结果。数组可以在单个单元格中使用,也可以同时在一批单元格中使用。一

Excel数组和数组函数 excel返回数组的函数

数组是Excel中一个非常重要的概念,如LINEST()是做线性加归的函数,它接受数组作为参数,返回值也是一个数组。为了彻底的弄清楚这样的函数,好好的研究了一下Excel的数组及数组函数,并作了总结。1.Excel中的数组数组(array,繁体版称作阵列),是

数组公式的应用 Excel应用技巧 [2]数组公式的应用

Excel应用技巧:[2]数组公式的应用――简介对于需要同时处理多个单元格的情况,使用数组公式会更适合。相信大家对键盘组合键“Ctrl+ Alt+ Enter”并不陌生吧。下面小编就给大家讲解一下数组公式的具体使用方法。Excel应用技巧:[2]数组

如何使用数组公式 excel数组公式如何使用

excel数组公式如何使用――简介excel除了自带的函数外,还支持数组方式运行的公式,即数组公式。合理的使用数组公式和其他函数嵌套可以解决各种各样实际工作中遇到的难题。本例向大家介绍excel数组公式如何使用。excel数组公式如何使

声明:《indirect公式 Excel利用INDIRECT函数以及数组公式实现查询》为网友抬头的微笑分享!如侵犯到您的合法权益请联系我们删除