• 职业买手爆料
  • 中立态度甄选
  • 品质消费推荐
Excel函数公式一对多查询的12种公式
发表于 7年前 (2017-09-08)
电脑diy

Excel函数公式一对多查询的12种公式

完成效果:

 Excel函数公式一对多查询的12种公式

在熟悉常用函数语法之后,如果要解决实际工作问题,往往需要几个函数通过嵌套组合才能解决问题,这就是所谓的思路。收集的思路越多,越容易想到解决办法。下面针对一对多查询这个问题,介绍12种公式中运用的一些常用思路。

以下公式均需要按【CTRL SHIFT ENTER】三键结束。

公式1

E2=IFERROR(INDEX(B:B,SMALL(IF(A$1:A$11=D$2,ROW(A$1:A$11)),ROW(A1))),'')

思路分析:

通过条件函数IF判断,只要符合要求的记录,全部返回所在行号,否则返回False。SMALL函数的作用是从小到大逐个返回行号,最后通过INDEX函数返回符合要求的记录。IFERROR函数的作用是当所有记录显示完出错时,公式返回空。以下所有公式的这个信息函数作用是一样的,就不再赘述了。

 

公式2

F2=IFERROR(VLOOKUP(SMALL(IF(A$1:A$11=D$2,ROW(A$1:A$11)),ROW(A1)),IF({1,0},IF(A$1:A$11=D$2,ROW(A$1:A$11)),B$1:B$11),2,),'')

思路分析:

利用IF函数重新构造两列数组,一列是符合条件的行号,一列是符合条件的记录。再利用VLOOKUP函数从小到大行号查找返回符合要求的记录。

 

公式3

G2=IFERROR(VLOOKUP(SMALL(IF(A$1:A$11=D$2,ROW(A$1:A$11)),ROW(A1)),CHOOSE({1,2},IF(A$1:A$11=D$2,ROW(A$1:A$11)),B$1:B$11),2,),'')

思路分析:

思路与公式2思路大致一样,唯一不同的是公式3是通过CHOOSE函数重新构造两列数组。

 

公式4

H2=IFERROR(HLOOKUP(SMALL(IF(A$1:A$11=D$2,ROW(A$1:A$11)),ROW(A1)),TRANSPOSE(IF({1,0},IF(A$1:A$11=D$2,ROW(A$1:A$11)),B$1:B$11)),2,),'')

思路分析:

思路和公式2类似,不过这里是通过HLOOKUP函数查找,查找区域需要TRANSPOSE函数转置一下才能返回正确结果。

 

公式5

I2=IFERROR(HLOOKUP(SMALL(IF(A$1:A$11=D$2,ROW(A$1:A$11)),ROW(A1)),TRANSPOSE(CHOOSE({1,2},IF(A$1:A$11=D$2,ROW(A$1:A$11)),B$1:B$11)),2,),'')

思路分析:

思路和公式4大致一样,唯一不同的是公式5是通过CHOOSE函数重新构造两列数组。

 

公式6

J2=IFERROR(LOOKUP(SMALL(IF(A$1:A$11=D$2,ROW(A$1:A$11)),ROW(A1)),IF(A$1:A$11=D$2,ROW(A$1:A$11)),B$1:B$11),'')

思路分析:

这里利用了LOOKUP函数以下语法:

LOOKUP(lookup_value,  lookup_vector,  [result_vector])

在LOOKUP函数把符合条件的行号从小到大返回结果。

 

公式7

K2=IFERROR(LOOKUP(SMALL(IF(A$1:A$11=D$2,ROW(A$1:A$11)),ROW(A1)),IF({1,0},IF(A$1:A$11=D$2,ROW(A$1:A$11)),B$1:B$11)),'')

思路分析:、

这里利用了LOOKUP函数以下语法:

LOOKUP(lookup_value,  array)

思路是一样的,不过语法结构不同,注意与公式6的区别。

 

公式8

L2=IFERROR(LOOKUP(SMALL(IF(A$1:A$11=D$2,ROW(A$1:A$11)),ROW(A1)),CHOOSE({1,2},IF(A$1:A$11=D$2,ROW(A$1:A$11)),B$1:B$11)),'')

思路分析:

思路与公式7一样,不同的是公式8通过CHOOSE函数重新构造两列数组。

 

公式9

M2 =IFERROR(OFFSET(B$1,SMALL(IF(A$1:A$11=D$2,ROW(A$1:A$11)),ROW(A1))-1,),'')

思路分析:

利用OFFSET函数,以B1单元格为起点,向下偏移相应符合条件行数的记录。

 

公式10

N2 =IFERROR(INDIRECT('B'&SMALL(IF(A$1:A$11=D$2,ROW(A$1:A$11)),ROW(A1))),'')

思路分析:

利用INDIRECT函数A1样式单元格引用。

 

公式11

O2 =IFERROR(INDIRECT('r'&SMALL(IF(A$1:A$11=D$2,ROW(A$1:A$11)),ROW(A1))&'c2',),'')

思路分析:

利用INDIRECT函数R1C1样式单元格引用。

 

公式12

P2 =IFERROR(INDIRECT(ADDRESS(SMALL(IF(A$1:A$11=D$2,ROW(A$1:A$11)),ROW(A1)),2)),'')

思路分析:

与公式10类似,这里是通过ADDRESS函数返回单元格引用地址。

 

 

评论(0)
Hi,您需要填写昵称和邮箱!
姓名 (必填)
邮箱 (必填)
网站

发表优秀评论还可获得奖励哦~~
相关爆料
    没有相关文章!
免费推荐配置
切换注册

登录

忘记密码 ?

您也可以使用第三方帐号快捷登录

Q Q 登 录
微 博 登 录
切换登录

注册

回到顶部