|
完成效果： 在熟悉常用函数语法之后，如果要解决实际工作问题，往往需要几个函数通过嵌套组合才能解决问题，这就是所谓的思路。收集的思路越多，越容易想到解决办法。下面针对一对多查询这个问题，介绍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函数返回单元格引用地址。
|
|
|
