专栏首页Stone的专栏一篇文章精通 VLOOKUP 函数
原创

一篇文章精通 VLOOKUP 函数

相信不少人看到标题,立即嗤之以鼻,VLOOKUP 谁不会?是的,大家都会,但用的好的人不多。相信我,这篇文章一定可以算得上通俗易懂,又有深度的一篇文章,熟练掌握本文所讲内容,一定会在日常 Excel 处理时如鱼得水。

预备知识

Excel 数组

Excel 用 {a,b,c}{a;b;c} 表示数组。数组的主要作用是用于有多个返回值的公式 (数组公式)或函数。我举一个必须用数组公式的例子。假设有三个字段,是按行放置的,我们想进行转置,像下面这样:

要求用函数来实现,不能用选择性粘贴。正确的答案如下图(注意数组函数最后必须 CTRL+SHIFT+ENTER 三个键同时按下,否则错误

注意地址栏是公式有大括号括起来:{=TRANSPOSE(A2:C2)},这个是 Excel 数组公式的标志。

我们可以把数组简单地理解为:Excel 把工作表的某区域 (Range) 值,按照行列整体在内存中存储,并可以方便的再将数组在工作表的 Range 中进行显示

来看一个操作,印证和理解一下。在 A1:C3 这个区域存放着两笔信息,我们用数组公式将数据放到同样大小的一块区域 E1:G3

将光标放到编辑栏,按下 F9,对,你没有看错,Excel 对数组使用的是域,所以用 F9 来解析,我们发现,编辑栏变成了这样:

地址栏的数据为: {"ID","姓名","地址";"001","Stone","Wuhan";"002","Tom","England"},这是一个二维的数组。我们可以看到,行数据用逗号分隔(水平数组),列数据用分号分割(垂直数组)。刚才的操作可以这样解释:Excel 把 A1:C3 的数据拷贝到数组,然后再把数组的数据拷贝到 E1:G3。那么,这样操作的作用是什么呢?注意,这个是理解后续函数操作的关键:我们之所以要用数组公式把数据放到数组中,就是为了在需要在函数中要用到 Excel Range 的地方,也可以用数组来代替,从而获得某种灵活性。现在不理解,不着急,后面有例子说明。

数组的运算

如果把某个值和一个数组进行运算,那么这个值会和数组中的每一个元素(或称每一项)进行运算。如果将两个数组进行运算,那么数组中的每个对应位置的元素都会执行某种运算。这种运算规律非常像线性代数的矩阵运算。

举个例子:

array_computing.gif

IF 函数高级用法

IF 除了返回一个单值,还可以返回一个数组。此时 IF 函数的语法如下:

IF({1,0}, range1, range2)

理解:因为 {1,0} 表示一个数组,所以每一个元素都会执行运算,首先获取第一个元素 1, IF 函数对不为零的条件,返回 range1;然后再获取第二个元素 0, IF 函数对 0,返回 range2。最后,函数再将 range1 和 range2 组合成一个新的数组。

举例: 运用 IF 函数将 ID姓名 两个字段改变顺序

{=IF({1,0},B1:B3,A1:A3)}

VLOOKUP 基本用法

VLOOKUP 的语法如下:

VLOOKUP(Lookup_value, Table_array, Col_index_num, TRUE/FALSE)
  • Lookup_value: 要查找的值
  • Table_array: 查找的范围。查找的范围中, Lookup_value 对应的列(为了表述方便,后面将对应的列称作关键列)关键列必须处在第一列位置。
  • Col_index_num: 查找的列数。在查找范围中,要找的值从第一列算起,所在列的偏移值。比如从 A 算起,如果要在 B 列中查找某值,则列的偏移值为 2,以此类推。要点:查找的列一定要在 Lookup_value 所在列的右边。
  • 第四个参数,如果为 FALSE 或者 0,表示精确匹配,如果为 TRUE 或者 非零值,表示模糊匹配

文字描述总是难理解一些,可以参考视频 VLOOKUP 函数 来学习。

接下来介绍 VLOOKUP 函数的几个比较高级一点的用法。

查找列在关键列左边

比如我们要根据 ID 来查找姓名,但不巧 姓名ID 的左边:

我们前面讲过使用 IF 函数构造一个数组,通过这个数组来改变列的位置,也讲过函数需要使用 Range 的地方,可以使用数组来代替。将上面两个技术结合,就可以达到目的。

函数如下:

=VLOOKUP(E2,IF({1,0},B1:B3,A1:A3),2,0)

函数理解: IF({1,0},B1:B3,A1:A3) 返回一个数组,数组内容为:

{"ID","姓名";"001","Stone";"002","Tom"}

所以 VLOOKUP 函数利用这个数组,可以在第二列中就开到对应姓名。

多条件查找

比如我们要根据公司姓名两个字段来确定人员对应的补助:

方法是将公司和姓名组合成一个字段,然后再使用 VLOOKUP 函数:

H2 单元格的函数为:

{=VLOOKUP(E2&F2,IF({1,0},A1:A5&B1:B5,C1:C5),2,0)}

注意这里必须使用公式数组,同时按下 CTRL+SHIFT+ENTER

VLOOKUP 和 COLUMNS 函数结合

比如我们要根据补助标准来发放补助,但表的字段太多,补助AJ 列。如果使用 VLOOKUP 函数,需要计算 A 到 AJ 的列数。

这个时候,我们可以通过 columns 函数来帮助我们计算 A 到 AJ 共计多少列。columns 函数的语法如下:

COLUMNS(A:AJ)

结合 VLOOKUP 函数用法如下:

=VLOOKUP(AL2,A:AJ,COLUMNS(A:AJ),0)

自定义 VLOOKUP 函数

如果我们觉得 VLOOKUP 函数复杂,或者想按自己的逻辑来增强 VLOOKUP 函数,我们也可以自定义一个自定义的函数。当然,自定义函数是另一个话题,可以写一系列文章。这里,给出一个并不是太有意义的实现,比如:

VLOOKUPPRO(lookup_value, range1, range2)

根据 lookup_value 在 range1 中找到 lookup_value, 然后返回 range2 对应的值。这样,不用计算列数,一般情况下,我们也用不到模糊查找,我们使用精确查找。

Public Function VLookupPro(lookup_value As Range, table_range1 As Range, table_range2 As Range) As Variant
    Dim result As Variant
    Dim table_array1() As Variant
    Dim table_array2() As Variant
    Dim table_array As Variant

    
    Dim firstRow As Long
    Dim lastRow As Long
    Dim usedRng As Range
    Set usedRng = table_range1.Worksheet.UsedRange
    firstRow = usedRng.Cells(1, 1).Row
    lastRow = usedRng.Cells(usedRng.Rows.Count, 1).Row
    
    ' Populate table_array1 and table_array2
    Dim i As Long
    Dim rowCount As Long
    rowCount = lastRow - firstRow + 1
    ReDim table_array1(1 To rowCount)
    ReDim table_array2(1 To rowCount)
    For i = 1 To rowCount
        table_array1(i) = table_range1.Cells(i, 1).Value
        table_array2(i) = table_range2.Cells(i, 1).Value
    Next
   
    ReDim table_array(1 To UBound(table_array1), 1 To 2)
    For i = 1 To UBound(table_array)
       table_array(i, 1) = table_array1(i)
       table_array(i, 2) = table_array2(i)
    Next
    
    result = Application.WorksheetFunction.VLookup(lookup_value.Value, table_array, 2, False)
    VLookupPro = result
End Function

使用方法示例如下:

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Power Query 系列 (05) - M 语言介绍

    M 语言或者叫 M 查询语言是 Power Query (简称为 PQ) 幕后的英雄。据说 Power Query Editor 可视化操作可以实现 PQ 80...

    StoneWM
  • Power Query 系列 (01) - Power Query 介绍

    Power Query 是微软提供的工具,Excel 2013 版作为插件加载使用,从 Office 2016 版开始,Power Query 的功能集成到 E...

    StoneWM
  • Power Query 系列 (02) - 从文件导入数据

    Power Query (本文和以后也用 PQ 作为 Power Query 的缩写) 数据处理的起点是获取数据,在我所接触过的数据处理工具中,PQ 能处理的数...

    StoneWM
  • 2018年最全的推荐系统干货(ECCV、CVPR、AAAI、ICML)

    最近总有几位关注者希望我们可以分享一些“推荐系统”类的干货,最近正好一不小心看到一篇比较好的博主写的推送,在此我通过自己理解和该博主的内容,为大家带来一次推荐系...

    计算机视觉研究院
  • 2018年最全的推荐系统干货(ECCV、CVPR、AAAI、ICML)

    最近总有几位关注者希望我们可以分享一些“推荐系统”类的干货,最近正好一不小心看到一篇比较好的博主写的推送,在此我通过自己理解和该博主的内容,为大家带来一次推荐系...

    计算机视觉研究院
  • 5. 小项目之Face_detection

    想做的是这么一个东西:识别视频(或者摄像头获得的实时视频)中的人脸,并判断是谁(因为数据采集的原因,找了身边的5个朋友采集了一些数据),如果不是这几个人,标记为...

    和蔼的zhxing
  • Golang leetcode 476. Number Complement.go

    版权声明:原创勿转 https://blog.csdn.net/anakinsun/article/details/89175283...

    anakinsun
  • web框架

    小小咸鱼YwY
  • 006.MongoDB副本集

    复制提供了数据的冗余备份,并在多个服务器上存储数据副本,提高了数据的可用性, 并可以保证数据的安全性。

    木二
  • c语言基础学习05_数组和字符串

    ============================================================================= 涉及...

    黑泽君

扫码关注云+社区

领取腾讯云代金券