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