快速浏览
往期合集:【2023年3月】【2023年4月】【2023年5月】【2023年6月】【2023年7月】
内容提要
大家好,我是冷水泡茶,今天在知乎有一邀请贴:
他的需求可以这样表述:
查找姓名列固定区域非空单元格的值。
这个问题如果简单处理的话,可以加个辅助列,但如果是只想用一个公式来查找,好像还是有点难度,我模拟了一点数据,我们就一起来看看吧:
实现方法一:添加辅助列
1、我们可以在下面的数据表区域的最右侧添加一个辅助列:具体机型
2、把左边具体机型区域的数据取到辅助列中。
3、利用VLOOKUP之类的查找函数查找对应姓名的机型(辅助列)
4、辅助列取数公式,可以有多种方法:
(1)CONCAT函数:因为数据区域只有一列有内容,其余都为空,第一感觉就是把它们都连起来。
=CONCAT(I2:L2)
(2)INDEX+MATCH函数:
=INDEX(I2:L2,1,MATCH(TRUE,INDEX((I2:L2<>""),0),0))
(3)LOOKUP函数:
=LOOKUP(1,1/(I2:L2<>""),I2:L2)
(4)PHONETIC函数:这个函数估计很多人没有用过。它原本用来提取东亚语言中的拼音,如果没有拼音则返回文本。注意,它只提取文本类型的值,其他如数字、公式、错误值它统统忽略。
=PHONETIC(I2:L2)
(5)用“&”符号连接:这个有点麻烦,只适合数据列较少的情况。
=I2&J2&K2&L2
5、最后,用VLOOKUP函数查找结果:
=VLOOKUP(A2,H:M,6,0)
实现方法二:公式法,只用一个公式达到目的
公式的难点在于,要匹配的值所在列是变化的,直接用VLOOKUP之类的查找函数有点难以下手。当我们找到姓名时,还得到对应行的数据区域中某一列查找非空单元格。
公式想了好久,要解决动态的数据区域问题,我想到了OFFSET函数。
OFFSET($H$1,MATCH($A2,$H$2:$H$21,0),1,1,COLUMNS($I:$L))
从姓名列,行向下位移,用MATCH函数查找$A2姓名在数据区域姓名列的位置来获得位移量;列向右位移1,返回区域为1行,n列,n用COLUMNS函数求得。
有了这个动态区域,我们就利用查找函数来查找对应的非空单元格。
1、用INDEX+MATCH,数组公式,Ctrl+Shift+Enter输入。
{=INDEX(OFFSET($H$1,MATCH($A2,$H$2:$H$21,0),1,1,COLUMNS($I:$L)),1,MATCH(TRUE,OFFSET($H$1,MATCH($A2,$H$2:$H$21,0),1,1,COLUMNS($I:$L))<>"",0))}
2、用LOOKUP:
=LOOKUP(1,1/(OFFSET($H$1,MATCH($A2,$H$2:$H$21,0),1,1,COLUMNS($I:$L))<>""),OFFSET($H$1,MATCH($A2,$H$2:$H$21,0),1,1,COLUMNS($I:$L)))
2个公式都很长,其实就是第一种方法中求辅助列的第二、三种方法。
总结
函数公式的综合运用,我们可以通过抽丝剥茧的方式一步一步来分析解决,重点就是把某个函数的参数也使用公式,层层嵌套,最终达成目标。
当然,有时候利用辅助列,可以简化公式的长度,并且也不用那么烧脑,也是一个很好的解决问题的方法。
正文完
喜欢就点个赞、点在看、留个言呗!分享一下更给力!感谢!
联系客服