vlookup函数精确匹配和近似匹配的区别

一、vlookup函数精确匹配和近似匹配的区别

精确匹配和近似匹配的区别如下：

他们最主要的区别在于在于匹配条件是否需要完全匹配。

在近似匹配模式下，vlookup函数会在数据集中查找与指定值最接近的值，并返回该值所在的行的指定列的值。这种匹配模式通常用于查找数值型数据，如价格、数量等。使用近似匹配时，可以不必完全匹配查找条件，而是返回与条件最接近的值。

例如，当我们查找一个价格为100元的商品时，有可能找不到这个价格，但可以返回最接近的价格，如99元或101元。

在精确匹配模式下，lookup函数会在数据集中查找与指定值完全匹配的值，并返回该值所在的行的指定列的值。这种匹配模式通常用于查找文本型数据，如客户姓名、产品名称等。使用精确匹配时，需要完全匹配查找条件，才能返回目标列中对应的值。

1.查找数据集中的数值型数据，如价格、数量等。

2.查找需要返回与条件最接近的值的情况。例如，在查找市场行情时，可能需要返回与指定价格最接近的股票代码或交易量。

3.查找需要考虑容错能力的情况。例如，在查找客户姓名时，可能会出现输入错误或拼写错误的情况，此时近似匹配可以返回与输入最接近的姓名。

1.查找数据集中的文本型数据，如客户姓名、产品名称等。

2.查找需要完全匹配查找条件的情况。例如，在查找客户姓名时，需要完全匹配输入的姓名才能返回目标列中对应的值。

二、VLOOKUP函数,使用精确还是近似匹配

Excel VLOOKUP函数是众多用户喜爱的函数之一，其经典用途是将A表的某列数据对应地写入B表中，类似于数据库中两个表的连接合并操作，在数据处理中应用广泛。

然而，在实际应用中，VLOOKUP函数可能会返回非预期的数据，让人感到困惑。如果数据集很大，不逐一核对结果，往往会导致错误而不知，十分危险。本文将探讨这个问题，并通过实际测试，分析问题原因，并提供解决方案。

函数的第四个参数是可选参数，默认值为1（或TRUE），表示“近似匹配”。如果将该值指定为0（或FALSE），函数执行“精确匹配”操作。

通过一个实例来演示函数功能，表中数据是虚拟的。A表是录取库，包含了考生的录取信息，B表是成绩库，无录取信息。现在要把A表中的录取信息写入B表中，VLOOKUP函数是实现该功能的首选。

在J3单元格输入公式：=VLOOKUP(G3,$B$3:$D$21,3,0)，对照函数语法，几个参数含义为：

“J3”单元格的计算结果是“温州商学院”，符合要求。然后把“J3”单元格的公式拖动到“J9”，就完成了“A表的录取信息写入B表”的操作。

上例中存在一些问题，包括4项返回错误提示“#N/A”（表示“值不可用”），具体分析如下：

参数4如果为“1”或“TRUE”，则表示“近似匹配”模式。仍以上例的相关数据来进行测试，在K3单元格中录入公式：=VLOOKUP(G3,$B$3:$D$21,3,1)，再拖动填充到K19。

运算结果“一塌糊涂”，这哪是模糊匹配，分明就是“迷糊匹配”。那么问题出在哪儿？查函数使用说明，在近似匹配时，“必须按升序排列参数2（即数据区域）中的第一列的值，否则，VLOOKUP可能无法返回正确的值“。

尝试对A表按“姓名”列进行排序后，再看K列的运算结果，发现大多数据已经正确，如下图。

同样，运算结果中也存在一些问题，分析如下：

上述4个结果，不管正确还是错误，均是VLOOKUP执行某种规则所致。下图是网上关于VLOOKUP函数的一些说明，揭开了函数算法实现上的相关规则：

可见，如果不指定参数4，VLOOKUP函数默认使用“近似匹配”，这是一种“不幸”的指定，许多错误来源于此。在这种模式下，如果搜索到了完全匹配值，那就没问题，返回正确值。但如果未搜索到精确值，函数总是返回“大于查找值的值的上一行的值”，这十分拗口。如果对照上例中的4个结果值，逐一分析，可知是完全符合这一规则的：

VLOOKUP在近似匹配时，要求数据区域的第1列是升序排列，然后VLOOKUP总是查找“大于查找值的值”，比如A表中有2个“黄晴一”，VLOOKUP查到第一个，发现相等，再查第2个，还是相等，再查下一个“马佳睿”，大于“黄晴一”（这里是汉字拼音排序），然后函数返回上一个即第2个“黄晴一”所在行的值。再看“贝佳音”，按拼音序排在“蔡云雨”之前，带有前导空格的“贝佳音”之后，正好返回第一行的值，巧合正确。“褚佳怡”也是如此。而“褚文宁、张依炜”也可按此分析，得到的结果虽然不符合预期，但符合VLOOKUP的规则。

VLOOKUP函数这么做的目的是什么？看下个例子就明白了：

上图中，待搜索的值（即参数1）是一个数值，而不是文本。F列应用了近似匹配的VLOOKUP公式，观察运算结果，马上就可看出，正是“我的钱能买到的最大价格的商品是哪个？”的答案，丝毫不差。

我们来分析一下函数的算法，以第7行为例：125元能买哪个？图中用红框标示了相关单元格，函数采用“二分法”查找，A列列出了要进行的3次比较。函数首先取B列9行数据的中间值110（即二分法），然后执行比较125>110？结果为TRUE，说明目标值在110之后。再二分取后半部分的中间值123，比较125>123？结果为TRUE，再二分取150，比较125>150？结果为FALSE。经过这3轮比较，函数已经找到“大于查找值125的值为第9行的150”，满足这个条件后，函数不再继续查找，而是返回该行上一行即第8行的值“4件套”。

在计算机算法中，二分查找也叫“折半查找”，是一种高效的查找方式，但要求元素是有序排列。其时间复杂度为log(2)(n)，n是行数。当n=100000时, log(2)(n)=16.6，可见程序最多执行17次判断就能找到目标值。如果逐一查找的话，最多需要判断10万次。

“近似”不等于“模糊”，但在许多情况下，我们需要的是对文本的“模糊匹配”，该如何操作？

还是用实例1的数据，假设B表中的姓名丢失了第3个字，仅剩前2个，或者说B表的关键字是A表对应关键字的一部分，那就用下述公式：（公式中采用LEFT函数取姓名的前2字来模拟）

公式为：=VLOOKUP("*"& LEFT(TRIM(G3),2)&"*",$B$3:$D$21,3,0)。注意公式中，参数4还是用0，即精确匹配。而模糊查找的功能用参数1两边加通配符*来实现。

G3单元格的内容是“黄晴一”，在去空格后，取左2位，再前后各加上一个星号，变为“*黄晴*”，来模拟2位字符的姓名，再去精确匹配查表。从本例来看，K列公式运行结果都是正确的。

但实际上，这跟数据集内容有关，如果A表数据中同时有“王振”和“王振宇”，当函数用“*王振*”去查找，匹配到的还是第1个，这种结果存在不确定性。

5.大数据集应采用哪种匹配模式？

函数在近似匹配时使用的“二分查找法”十分效率，这对超大数据集的操作十分有用。经测试，在一个近10万条记录的实例中，2种模式用时相差2000多倍。

测试的数据集为：A表有70000行，B表有18000行。连接2表的列是一个11位宽度的文本，关键词均不重复。

首先使用精确匹配模式：=VLOOKUP(A2,[文件1.xlsx]表A!$A$2:$U$70000,7,0)，并双击该单元格拖动柄，计算18000个公式，电脑运算了约1分钟之久。可大致计算一下函数执行的比较次数为：18000*70000/2~6.3亿次。如果对A表第1列进行升序排列，函数仍旧需要这么多时间，可见函数在精确匹配时，并不使用二分查找算法。

然后改用近似匹配模式：=VLOOKUP(A2,[文件1.xlsx]表A!$A$2:$U$70000,7,1)，同样计算18000个公式，电脑几乎马上就完成运算，显示出结果。估算函数执行的比较次数为：18000*log(2)(70000)=18296*16.13~29万，比前面的6.3亿小了2100多倍，前面计算1分钟的话，本次仅需0.028秒。

当然，使用近似匹配方式，还是需要注意“近似”的问题。如果已知B表的记录在A表中都能找到完全匹配项，这无疑是一个好选择。

6.使用VLOOKUP函数的一些注意事项

office的最新版本office365，已经使用XLOOKUP函数来代替VLOOKUP和HLOOKUP，功能更为强大，详见相关文档，不文不再赘述。

三、如何使用近似匹配函数进行文字模糊匹配

答案：在Excel中，可以使用近似匹配函数进行文字的模糊匹配。具体步骤如下：

Excel提供了近似匹配的功能，允许用户在查找数据时考虑到近似或模糊匹配的情况。这对于处理大量数据并找到相关项非常有用。

2.使用VLOOKUP函数进行近似匹配：

近似匹配通常与VLOOKUP函数结合使用。VLOOKUP函数可以根据给定的查找值，在数据表的第一列中查找近似值。需要注意的是，VLOOKUP在进行近似匹配时，查找列必须是按某种排序排列的。

确保数据表中的查找列已排序。例如，如果要在产品名称列中进行模糊匹配，则这些名称应按照字母顺序排序。

使用VLOOKUP函数，并选择“近似匹配”选项。这意味着当查找值不完全匹配时，它会返回最接近但较小的值。

输入或引用要进行匹配的文本，并指定返回哪一列的数据。

根据需要调整其他参数设置，确保函数正常工作。

虽然近似匹配功能在处理某些情况时非常有用，但还是要确保理解其工作原理以及如何影响数据检索的准确性。在某些情况下，模糊匹配可能会返回不正确的结果或产生歧义。因此，在使用此功能之前，最好先测试并验证其准确性。此外，随着Excel版本的更新，某些功能的位置或名称可能会有所变化，请根据具体版本的指南进行操作。

通过这种方式，用户可以在Excel中利用函数实现文字的模糊匹配，从而提高数据处理和检索的效率。