其实,VLOOKUP函数还能扮演很多角色,是不为人知的,而且这都是些狠角色,威力不可小觑!
比如转换数据结构、提取数值、批量合并同类项......
下面结合案例展开讲解,正文会比较长,没时间一气看完的同学,可以分享到朋友圈给自己备份一份。
看完教程还想进一步系统学习的同学,长按下图 识别二维码进知识店铺。
更多不同内容、不同方向的Excel视频课程
长按识别二维码↓获取
(长按识别二维码)
一、VLOOKUP转换数据结构
要求:将一列数据转换为多行多列。
本案例中是将18行1列数据转换为6行3列的数据结构,如下图所示。
这里使用的不是普通公式,而是区域数组公式,写法上需要注意两点:
1、先选中多单元格区域后,再输入公式;
2、输入数组公式以后,同时按Ctrl+Shift+Enter组合键。
选中C2:E7单元格区域,输入以下区域数组公式,按Ctrl+Shift+Enter三键结束输入。
=VLOOKUP('*',TRANSPOSE(A1:A18),((ROW(1:6)-1)*3+COLUMN(A:C)),0)
这样就可以成功的转换数据结构了。
如果要的结果不是6行3列,换成其他行列数,调整公式中的参数即可。
二、VLOOKUP提取数值
要求:从文本混杂字符串中提取出数值。
本案例中的文本混杂字符串在A列,里面有各种各样的数值,如下图所示。
这里用到的也是数组公式,是单个单元格数组公式。
在B2单元格输入以下数组公式,按Ctrl+Shift+Enter三键结束输入。
=VLOOKUP(9E+307,MID(A2,MIN(IF(ISNUMBER(--MID(A2,ROW($1:$99),1)),ROW($1:$99))),ROW($1:$99))*{1,1},2)
这样即可轻松提取数值。
三、VLOOKUP批量合并同类项
要求1:按部门合并其下所包含的人员,并用顿号间隔;
案例场景如下图所示。
要求2:当该企业人员有部门调动时,即A列的部门调整时,F列的结果自动更新。
要求的效果如下动图演示。
解决方案分两步走。
第一步:先创建辅助列,目的是便于后续VLOOKUP调取;
在C2输入公式如下。
=B2&IFERROR('、'&VLOOKUP(A2,A3:C$16,3,),'')
第二步:在F2输入公式如下
=VLOOKUP(E2,$A$2:$C$15,3,)
完成后,效果如下图所示。
联系客服