微信服务号 | 跟李锐学Excel(ID:LiRuiExcel)微信公众号 | Excel函数与公式(ID:ExcelLiRui) 虽然VLOOKUP函数是大家耳熟能详的Excel查找函数,但是其中的很多细节的实用技巧还不为人所知,导致在写公式的时候还存在一些繁琐的操作,浪费了宝贵的时间。 本文结合实际案例介绍VLOOKUP+COLUMN函数组合的巧妙用法,轻松搞定多列数据查询,实现仅用1个公式对多列字段的批量查询。 比如按照姓名查询多个科目的成绩,动图演示如下图所示。 这个案例中有5个科目需要查询,传统方法是写5个公式分别查询对应的科目成绩,改进后可以仅用1个公式批量查询5个科目成绩。 建议你先自己思考2分钟,下文具体介绍传统方法和改进方法。 除了本文内容,还想全面、系统、快速提升Excel技能,少走弯路的同学,请搜索微信公众号“跟李锐学Excel”点击底部菜单的“知识店铺”或下方扫码进入。更多不同内容、不同方向的Excel视频课程 长按识别二维码↓获取 (手机微信扫码▲识别图中二维码) 传统方法查询数学成绩: 由于数学成绩在数据源中位于第2列,所以VLOOKUP函数的第三参数设置为2,示例公式如下。 =VLOOKUP(A13,$A$2:$F$10,2,0) 公式示意图如下所示: 传统方法查询语文成绩: 由于语文成绩在数据源中位于第3列,所以VLOOKUP函数的第三参数设置为3,示例公式如下。 =VLOOKUP(A13,$A$2:$F$10,3,0) 公式示意图如下所示: 传统方法查询英语成绩: 由于数学成绩在数据源中位于第4列,所以VLOOKUP函数的第三参数设置为4,示例公式如下。 =VLOOKUP(A13,$A$2:$F$10,4,0) 公式示意图如下所示: 传统方法查询物理成绩: 由于物理成绩在数据源中位于第5列,所以VLOOKUP函数的第三参数设置为5,示例公式如下。 =VLOOKUP(A13,$A$2:$F$10,5,0) 公式示意图如下所示: 传统方法查询化学成绩: 由于化学成绩在数据源中位于第6列,所以VLOOKUP函数的第三参数设置为6,示例公式如下。 =VLOOKUP(A13,$A$2:$F$10,6,0) 公式示意图如下所示: 这样的方法虽然可以实现5个科目成绩查询,但是需要写5次公式,分别设置VLOOKUP函数的第三参数,修改为对应的列号数字。 改进方法使用COLUMN函数: 改进思路是使用Excel函数自动生成VLOOKUP函数的第三参数,使其随着公式所在位置向右填充的时候,从2依次变成3/4/5/6,这种需求可以用COLUMN函数满足。 COLUMN函数用于返回引用单元格的列号,如果想要生成结果为2,就引用B列的单元格,比如B1单元格。 示例公式如下。 =COLUMN(B1) 公式示意图如下所示: 引用哪个单元格是由需求而定,比如想要生成结果为3,就使用COLUMN函数引用C列单元格,依此类推。 改进方法中COLUMN函数的作用: 按照上面介绍的原理,我们的需要是VLOOKUP函数第三参数分别从2改变到3/4/5/6,当COLUMN函数向右侧填充公式时,会从引用B1单元格依次改变到引用C1、D1、E1、F1单元格。 示例公式如下。 =COLUMN(F1) 公式示意图如下所示: VLOOKUP+COLUMN组合用法: 明白了COLUMN函数的原理,就可以让其配合VLOOKUP函数代替手动输入第三参数了。 示例公式如下。 =VLOOKUP($A13,$A$2:$F$10,COLUMN(B1),0) 公式示意图如下所示: VLOOKUP+COLUMN向右填充公式: 当公式填充至最右侧时,示例公式如下。 =VLOOKUP($A13,$A$2:$F$10,COLUMN(F1),0) 公式示意图如下所示: 仅用1个公式批量查询多列数据: 输入公式后,具体操作过程给大家录了个gif看下。 动图演示如下所示: 这些常用的经典excel函数公式技巧可以帮你在关键时刻解决困扰,有心的人赶快收藏起来吧。 希望这篇文章能帮到你!怕记不住可以发到朋友圈自己标记。 |
|
来自: songmao404 > 《办公技巧》