图文/兰色幻想(来自excel精英培训微信平台,写于2016年1月15日上午)
兰色曾写过关于Vlookup函数一对多查找的教程,前天一位同学提问,对vlookup查找到的多个结果,怎么把重复的值去掉,只保留一个结果。(兰色在网上还没看有同类公式,同学们要收藏一下了)
【例】如下图所示,要求在下面表查找每个产品的入库单价,重复的价格保留一个。
分析: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中复杂的公式都可以用辅助列来简化,实际工作中不用追求公式的完美而使用过于复杂的单个公式,使用辅助列更利于实用。
点击左下角“阅读原文”查看兰色和小妖录制的数据透视表全套+函数全套+技巧全套+VBA编程全套视频教程
联系客服