需要倒查表格?这样操作就行

俞木发2017-09-27 15:41应用 标签:表格

我们经常要对Excel文档中的数据进行查询。比如对股民来说,经常会在Excel中收集一些股票的信息数据,一般每只股票代码是和名称一一对应的。对于常规正向查询,如要通过代码查询股票名称,只要在需要查询单元格使用VLOOKUP函数即可快速找到。但很多时候,需要通过名称来反向查询代码(因为股票名称比代码更好记忆),比如要在H2输入股票名称,I2显示出相应代码和内容(图1),这种反方向的查询,可通过如下多种方法实现。

1709A-FFXCX-1

方法1:重组数据 逆向变正向

大家知道,VLOOKUP函数输入查询数据只能位于首列,本例需要输入查询的数据为B列。要实现使用VLOOKUP函数进行查询,我们可以通过数据重组的方法,将需要输入查询的数据列变为首列即可。数据重组借助IF函数实现,在H2输入公式“=VLOOKUP(H2,IF({1,0},B2:B10,A2:A10),2,0)”即可。

公式解释:这里先使用IF({1,0},B2:B10,A2:A10)函数,函数的意思是使用数组{1,0}进行判断,通过“1”返回B2:B10的数值,通过“0”返回A2:A10数值,然后将数组重组,B2:B10变为第一列。这样符合VLOOKUP函数的查询条件,当我们在H2输入股票名称,函数就会在I2显示出对应的代码了(图2)。

1709A-FFXCX-2

方法2:直接使用LOOKUP函数

由于这里是使用输入股票名称来查找代码,因此可以使用“LOOKUP(1,0/(条件),查找数组或区域)”方式进行查找。在I2单元格输入公式“=LOOKUP(1,0/(H2=B2:B10),A2:A10)”。

公式解释:这里条件就是“H2=B2:B10”,H2的数值是从B2:B10(即股票名称列)获得的一组逻辑值,再用0除以这些逻辑值,得到由0和错误值组成的内存数组,最后用1作为查询值,在内存数组中进行查询。查询数组内容为A2:A10中的代码,这样当在H2输入股票名称的时候,在I2就会出现相应的代码(图3)。

1709A-FFXCX-3

同样,对于J2,如果也要显现原来对应的D列(即占总股本比例)数值,可以在J2输入公式“=LOOKUP(1,0/(H2=B2:B10),D2:D10)”,依此类推。这样即使原来股票的数据很多,只要记住股票名称,输入相应的名称后即可取到相应资料(图4)。

1709A-FFXCX-4

方法3:INDEX嵌套MATCH函数

因为这里是在H2输入名称,然后在I2显示代码。因此实际上是要在I2根据H2的内容引用A列的代码数据。因此我们可以使用INDEX函数进行引用,在I2输入公式“=INDEX(A2:A10,MATCH(H2,B2:B10,))”即可。

公式解释:这里我们先使用MATCH找到H2在股票名称列(B2:B10)的相对位置,然后将这个位置数据作为INDEX函数的索引值,在A列中找到对应的代码显示。比如在H2输入特定名称如“佳讯飞鸿”时,MATCH会将其定位到第7行(B7),同时以此为索引,INDEX就会找到B7对应的A7数据(即300213)(图5)。

1709A-FFXCX-5

对于索引的引用也可以使用OFFSET函数,通过MATCH函数获取的行号,OFFSET函数进行偏移引用。同样在I2输入函数“=OFFSET(A2:A10,MATCH(H2,B2:B10,)-1,0,1,1)”即可(图6)。

1709A-FFXCX-6

从以上过程描述可以看到,上述查询实际上只是Excel中一种条件查询技巧,我们可以根据自己的实际需要进行更多的查询。比如可以自定义一个查询数据库,如选择股票名称即实现对代码、市值的精确查询。选中H2,点击“数据→数据验证”,切换到“设置→允许→序列”,来源选择“B2:B10”(图7)。在I2输入公式“=INDEX(A2:G2,MATCH(H2,B2:B10,))”,J2输入公式“=INDEX(G2:G10,MATCH(H2,B2:B10,))”,这样我们在H2展开下拉列表选择相应的股票名称,在I2、J2即可快速显示所需的代码和市值数据了(图8)。

1709A-FFXCX-7

1709A-FFXCX-8