以前兰色发过lookup函数的专题教程,见识了它强大的倒查、正查、多条件查找....这些都让Vlookup函数望尘莫及。其实,单个函数永远强不过“函数+辅助列”的模式,今天我们就看看比lookup函数更利害的Vlookup函数+辅助列查找。(公式高能,新手请洗把脸再看)
添加辅助列把多列连接起来,然后再用vlookup查找
A2 =B2&C2
H2 =VLOOKUP(F2&G2,A:D,4,0)
【例】把下图中各个城市的供货商查找合并到一起,重复的只列出一次。
D2
=IFERROR(VLOOKUP(A2,A3:D$10,4,0),' ')&' '&IF(SUMPRODUCT((A3:A$10=A2)*(C3:C$10=C2)),'',C2)
注:Sumproduct函数部分是判断重复,如果重复本行就连接空值
F2
=VLOOKUP(E2,A:D,4,0)
例】如下图所示,要求在下面表查找每个产品的入库单价,重复的价格保留一个。
分析:vlookup函数一对多查找的公式已够复杂,如果再去重复值,公式会变得无比复杂。所以今天依旧要借助辅助列来完成。
1、添加辅助列并设置公式:
=C2&SUMPRODUCT((1/COUNTIF(D$2:D2,D$2:D2))*(C$2:C2=C2))
公式说明:
思路:公式的目的生成“产品名称+序号”的结果,以便在下表中用vlookup逐个查找出来
1/Countif():Countif函数统计该产品价格出现的次数,1/()的目的把次数变成分数,例如出现3次就变成1/3,1/3,1/3 这样重复的只算1个。
*(C$2:C2=C2):是加一个限定条件,计算本产品的不重复价格个数。
Sumproduct函数:完成求和(1/3+1/3+1/3=1 )
2、设置查询公式:
=IFERROR(VLOOKUP($B13&COLUMN(A1),$A$1:$D$10,4,0),'')
公式说明:
COLUMN(A1):公式向右复制会自动生成1,2,3...序号
VLookup() :根据“产品名称&生成的序号” 从上表中查找单价
IFERROR(): 错误值显示为空白
兰色说:excel中最利害的不是某个功能或函数,而是各种功能的组合应用。这也是很多同学说为什么很多函数我都学会了,遇到问题还是没思路的原因。另:由于添加除重复功能,今天的公式比网上的一对多公式要复杂,所以同学们如果看不懂,建议先收藏起来。
End.
来自:Excel精英培训
联系客服