|
涉世未深：函数的功能和基本参数介绍，适合初学者。 中流砥柱：注意事项及要点，大量贴合实际应用的案例，适合大部分用户。 立地成佛：多个函数组合，或结合数组的复杂案例。 涉世未深 简介：VLOOKUP在EXCEL诞生之初的1985年就已经存在，它是第一个查询类的函数。VLOOKUP不但入选了官方网站上公示的十大最受欢迎函数，更是应用最广泛的三个函数之一，另外两个是SUM和AVERAGE. 功能：在指定区域中查找指定值，返回与其对应的另外一个值。 示例：在学生成绩中查询小王的语文成绩： =VLOOKUP('小王',A:D,3,FALSE)  VLOOKUP示例 语法结构：VLOOKUP(要查找的值，查找区域，返回值所在的列数，模糊查询或精确查询) 四个参数： 参数一，要查找的值，必选参数。可以直接输入，也可以引用单元格。如示例中要查找“小王”。 参数二，查找区域，必选参数。被查找的值和要返回的值都应该被包含在该区域内。如示例中的A:D列。 Excel笔记：被查找的值必须位于查询区域的第一列。 如示例中“小王”在查询区域的第一列（A列）中，而下图的错误示范中“小王“所在的B列被置于查询区域A:E的第二列，VLOOKUP返回了错误值”#N/A”.  VLOOKUP错误示例 参数三，返回值所在的列数，必选参数。如示例中要返回的语文成绩位于A:D中的第3列。 参数四，TRUE或FALSE，可选参数，未输入则默认为TRUE.TURE表示模糊查询，FALSE表示精确查询。TRUE和FALSE也可以分别用1和0代替。 大多数使用到VLOOKUP的场景中需要向下或向右填充公式，习惯性锁定查询区域可确保查询区域准确。 Excel笔记：建议全锁定（绝对引用）查询区域（即第二参数）。  绝对引用 中流砥柱
通配符* 代表任意一串字符 通配符？代表任意单个字符 Excel笔记：VLOOKUP支持通配符。 案例1，查询客户“王牌汽车“的联系人。 客户信息表中完整的名称是”北京王牌汽车制造厂“，此时需要在”王牌汽车“的前后分别加上一个通配符”*“，用于查询包含关键字”王牌汽车“的项。 =VLOOKUP('*'&D3&'*',A:B,2,0)  VLOOKUP通配符查找
第二参数查询区域锁定的情况下，第三参数设置为不同的值将带回不同的查找值，这一特性极大扩展了VLOOKUP的应用场景和灵活性。 Excel笔记：VLOOKUP第三参数常用动态参数：COLUMN函数，MATCH函数，数组。 案例2，查询学生的考试成绩。 G3=VLOOKUP(F3,A:D,2,0) H3 =VLOOKUP(F3,A:D,3,0) I3 =VLOOKUP(F3,A:D,4,0)  VLOOKUP第三参数 如图所示，根据姓名查询三个科目的成绩，可以分三次输入公式，但显然效率是低下的。仔细观察三个公式的参数会发现：除了第三参数外，其他参数都完全一样，而且第三参数是有规律的递增数列{2,3,4}. 此时对第一个公式做两点修改： 1，第一，二参数全锁定； 2，第三参数修改为COLUMN(B:B) 得到一个新的公式： G3=VLOOKUP($F$3,$A:$D,COLUMN(B:B),0) 向右拖动将公式直接填充到后面两个单元格， COLUMN函数产生{2,3,4}作为第三参数。  COLUMN动态参数 上述案例中查询科目的顺序与原数据的顺序一致，COLUMN函数产生的{2,3,4}作为第三函数刚好匹配，如果查询科目的顺序与原数据不一致呢？ 案例3，错位查询学生成绩。 3个VLOOKUP的第三参数分别是{4,2,3},COLUMN无法产生不规则的数组，需要用到函数Match.  VLOOKUP数组 G3=VLOOKUP($F$3,$A:$D,MATCH(G2,$A$1:$D$1,0),0) G3单元格输入以上公式后向右拖动填充，Match函数将产生{4,2,3}作为VLOOKUP的第3参数。 Match返回G2:I2各个科目在A1:D1中的相对位置，此时即便调整G2:I2各个科目的顺序，VLOOKUP也能返回正确的数据性。  动态参数MATCH 并不是所有场景都需要用函数来实现动态参数，数据结构固定的情况下用数组作为第三参数也是不错的选择。 案例4，隔列求和。 VLOOKUP需要返回黄色四列的数据，第三参数使用数组{3,5,7,9}一次性完成查询后SUM求和即可。 =SUM(VLOOKUP(A12,A3:I9,{3,5,7,9},0))  VLOOKUP隔列求和
第四参数省略，或设置为TRUE，或数字1时表示模糊匹配。 Excel笔记：VLOOKUP模糊匹配状态下，找不到查找值时，返回小于查找值的最大值 案例5，根据学生成绩评定等级。 模糊匹配模式下，在F列找不到小李的成绩（100），则返回小于100的最大值80对应的“良好”。 =VLOOKUP(B2,$E$2:$F$4,2,1)  VLOOKUP模糊匹配模式 VLOOKUP模糊匹配经常应用于按销售额区间计算提成，按重量区间计算快递费，按成绩区间评定等级等场景。无一例外，这些情况都需要整理好区间下限与等级的对应关系，且按升序排列。 Excel笔记：VLOOKUP模糊匹配时查询区域按升序排列，否则将得到错误结果。 案例6，错误示范，根据学生成绩评定等级。 =VLOOKUP(B2,$E$2:$F$4,2,1) 公式正确，但E列查询区域未按升序排列，黄色部分全部是错误结果。  模糊匹配错误示范
VLOOKUP中的LOOKUP是查找的意思，V则是vertical，垂直的意思。直观的解释了它的功能：从上而下的垂直查找。找到第一个查找值时就会停止，不管后续还有没有同样的查找值。 Excel笔记：当查询区域存在多个查找值时，VLOOKUP返回第一个（最上面一个）值。 这一特性在某些场景下也会派上用场。 案例7，查询“桃子”第一次批发价格。 =VLOOKUP('桃子',B:C,2,0) VLOOKUP返回第一个值
Excel笔记：VLOOKUP要求查询数据和被查找值完全一致，包括格式。 案例8，根据代号查询姓名。 =VLOOKUP(D3,A:B,2,0) 公式结果为错误值”#N/A”. 原因：查找值”9529”是数字格式，查询区域是文本格式。 VLOOKUP错误示范 三个解决方案：1. 将查找值设置为文本格式；2. 将查询区域设置为数字格式；3. 公式中连接一个空值。 大部分场景下用户更希望保留原始的数据格式，第3种方案： =VLOOKUP(D3&'',A:B,2,0) D3&””的核心逻辑也是将D3由数字格式转换为文本格式，只是在公式中完成。 VLOOKUP数据格式案例 立地成佛 VLOOKUP第2参数要求查询数据与返回数据处于连续的数据区域，且只能从左往右排列，在数据格式不满足的情况下，常规做法是通过IF函数或CHOOSE函数构建一个数据区域，在高版本的Excel中可以通过溢出功能直观地看出人为构建的数据区域，没有溢出功能的版本比较考验用户的想象力。 Excel笔记：数据结构不支持VLOOKUP时，用IF或CHOOSE构建一个满足要求的数据区域作为第二参数。
案例9，要根据学号查询姓名。 姓名在学号的左边，不符合VLOOKUP第二参数从左往右的要求，此时用IF函数构建一个满足要求的数据区域。 G4= VLOOKUP(F4,IF({1,0},B2:B10,A2:A10),2,0) VLOOKUP反向查询 将第2参数的IF函数置于F2单元格,通过溢出功能可以直观地看出其构建的数据区域中姓名处于学号的右侧，满足VLOOKUP第二参数的要求。 IF构建数据区域
案例10，同名同姓查询。 同姓同名示例 同名同姓的情况，如果不加上性别用于区分是无法对应“小张”的成绩的。其次，科目的顺序也发生了变化。 此时仍然可以采用构建数据区域的方式来处理，IF函数只能构建2列数据的区域，已经无法满足需求，需要用到CHOOSE函数。 F2=CHOOSE({1,2,3},A2:A10&B2:B10,D2:D10,C2:C10) 构建的数据区域中将”姓名”连接”性别”作为查询区域置于首列，后续依次是”语文”成绩和”数学”成绩。 CHOOSE构建数据区域 将CHOOSE函数整体作为VLOOKUP的第2参数，即表示在黄色区域内查询。如允许保留辅助列，也可以直接框选黄色区域作为第2参数。 同时需要注意第1参数需要用“姓名“连接”性别“作为查询条件。 L4=VLOOKUP($J$4&$K$4,CHOOSE({1,2,3},A2:$A$10&$B$2:$B$10,$D$2:$D$10,$C$2:$C$10),COLUMN(B:B),0) VLOOKUP多条件查询
无法构建数据区域的场景下，辅助列成为了最后的救命稻草。 案例11，查询市场部的员工姓名。 数据的前端插入辅助列，A2输入以下公式并向下填充。 =C2&COUNTIF($C$2:C2,C2) F2输入以下公式并下拉填充。 =IFERROR(VLOOKUP($C$2&ROW(1:1),$A$2:$B$10,2,0),'') VLOOKUP一对多查询
地狱级难度，实用性暂且不论，先感受一下来自地狱的气息吧！ 案例12，提取电话号码。 =VLOOKUP(TRUE,IF({1,0},ISNUMBER(--MID(A2,ROW($1:$100),11)),MID(A2,ROW($1:$100),11)),2,0) VLOOKUP提取电话号码 该案例中的MID将数据拆分为100组数据，ISNUMBER判断其中的数字项，IF强行构建数据区域。要进一步理解，同样可以将IF函数单独提取出来直观显示构建的区域。 案例13，按指定数量产生队列。 输入公式： =VLOOKUP(ROW(A1),IF({1,0},IFERROR(SUMIF(OFFSET($B$2,,,ROW($1:$5)-1),'<>')+1,0),$A$2:$A$6&''),2,1)&'' 版本较低的Excel中，输入公式后需按三键Ctrl+Shift+Enter. VLOOKUP产生队列 该案例涉及了两个核心要点：IF构建数据区域作为第二参数；第四参数1表示模糊匹配。 难点在于如何用IF构建一个合理的数据区域。将IF函数单独提列出来（如绿色区域）可以直观地看到数据结构，在此区域运用VLOOKUP模糊匹配模式查找即可。 VLOOKUP及辅助列 开发者恐怕也没有想到在后来的30多年里VLOOKUP会成为制霸一方的查询函数，种种别出心裁的用法并非笔者首创，而是大量用户不断总结积累而来，算是打工人只会的结晶吧。 |
|
|
