|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
关于一对多查找,还是觉得你的公式太过抽象,单条件一对多Index优于Vlookup,多条件一对多查找只能用Index,Vlookup或许可以,但太复杂。
a. 单条件一对多查找
Index方式:
要求在E列根据E1的姓名,查找所有该客户消费金额。
E3=Iferror(Index(B:B,Small(If(A$2:A$11=$E$1,Row($2:$11)),Row(A1))),"")
公式说明:
i. IF(A$2:A$11=$E$1,Row($2:$11))
把所有客户名称和E1进行对比,如果相同则返回行号,否则False。
ii. Small(..,Row(a1))
随着公式的向下复制,逐个提取该客户所在的每一个行号数字
iii. Index()
根据行号提取出B列的消费金额
这里出现的Small函数,语法结构为Small(Array,K),k为返回的数据在数组或数据区域里的位置(从小到大)。
Vlookup方式:
要求把张一所有消费金额全列出来
公式:
{=Vlookup(B$9&Row(A1),If({1,0},$B$2:$B$6&Countif(Indirect("B2:B6"&Row($2:$6)),B$9),$C$2:$C$6),2,)}
公式简析:
i. B$9&Row(A1) 连接序号,公式向下复制时会变成B$9连接1,2,3
ii. 给所有的张一进行编号。要想生成编号,就需要生成一个不断扩充的区域(Indirect("B2:B6"&Row($2:$6)),然后在这个逐行扩充的区域内统计“张一”的个数,在连接上$B$2:$B$6后就可对所有的张一进行编号了。
iii. IF({1,0})把编号后的B列和C组重构成一个两列数组
b. 多条件一对多查找
多条件一对多查找,与单条件原理类似,欲设函数实现,R列结果为“$c$10:$c$209<=c2, $e$10:$e$209<=e2, $g$10:$g$209<=g2”的值,并且支持下拉,可如此设置公式。
M10={Index(B:B,Small(If(($C$10:$C$70<=$C$2)*($E$10:$E$70<=$E$2)*($G$10:$G$70<=$G$2),Row($10:$70),4^8),Row(A1)))&""}
公式解析:
i If(($C$10:$C$70<=$C$2)*($E$10:$E$70<=$E$2)*($G$10:$G$70<=$G$2),
Row($10:$70),4^8)
满足这三个条件所有10-70行的数组,不满足情况下为4^8,即Excel2003版最大行数。
ii Small(If(($C$10:$C$70<=$C$2)*($E$10:$E$70<=$E$2)*($G$10:$G$70<=$G$2),
Row($10:$70),4^8),Row(A1))
把上一步计算出的数值区域中寻找第一个符合条件的情况。如果下拉,查找的就是第二个、第三个符合条件之情况。
iii Index(B:B,Small(If(($C$10:$C$70<=$C$2)*($E$10:$E$70<=$E$2)*
($G$10:$G$70<=$G$2),Row($10:$70),4^8),Row(A1)))
列示结果,生成第一个符合条件金额,&“”是为了过滤零值。
|
|