Excel数据匹配技巧与函数使用教程

本篇文章主要是结合我之前面试的各种经历和实战开发中遇到的问题解决经验整理的，希望这篇《Excel数据匹配方法与函数教程》对你有很大帮助！欢迎收藏，分享给更多的需要的朋友学习~

答案是：Excel数据匹配需根据场景选择VLOOKUP、INDEX+MATCH或XLOOKUP等方法，结合条件格式、删除重复项、Power Query等工具提升效率。

Excel数据匹配，说白了，就是把散落在不同地方、但又有关联的数据，通过某个共同的“钥匙”（比如一个ID号、一个名字）给找出来，或者把它们关联起来。这事儿在数据处理里简直是家常便饭，我们日常工作中，无论是核对两份清单，还是从一个大表里提取特定信息，都离不开它。核心方法无外乎几种：VLOOKUP、HLOOKUP（虽然现在用得少了）、INDEX+MATCH的黄金组合、以及新时代的XLOOKUP。当然，还有一些辅助工具和更高级的手段，都能帮我们把这些数据“对上号”。

解决方案

在Excel里处理数据匹配，我的经验是，没有一招鲜吃遍天的万能公式，更多的是根据具体场景和你的Excel版本，灵活选择最合适的工具。

最常见的，也是很多初学者第一个接触的，就是VLOOKUP。它的优点是简单直观，公式写起来不复杂。比如，你想根据产品编号，从一个产品信息表里找出对应的价格，VLOOKUP就能很快帮你搞定。但它也有明显的局限性，比如只能从左往右查找，如果你的查找值在数据区域的右边，那就没办法了，得调整数据结构。

接着，如果你需要更灵活的查找方式，或者说你的数据结构不那么规整，INDEX+MATCH组合就显得尤为强大。它不像VLOOKUP那样有方向限制，可以从任意列查找，返回任意列的数据。这就像给了你一个更自由的导航系统，你可以先用MATCH找到目标在哪个位置（第几行），再用INDEX根据这个位置去取你想要的数据。这个组合的潜力远不止此，通过一些变通，甚至能实现多条件查找，这在处理复杂业务逻辑时非常有用。

对于Office 365或Excel 2019及以上版本的用户，XLOOKUP简直是福音。它集VLOOKUP和INDEX+MATCH的优点于一身，语法更简洁，功能更强大。它默认就是精确匹配，支持双向查找，甚至内置了找不到时的错误处理，大大减少了我们写公式时的心智负担。我个人觉得，如果你的Excel版本支持，XLOOKUP绝对是首选，它能让你的匹配工作变得前所未有的轻松。

当然，除了这些函数，还有一些“非函数”的匹配和清理方法。比如，当你需要快速找出两列数据中哪些是重复的，或者哪些是不一致的，条件格式中的“突出显示重复值”就能派上大用场。对于彻底清除重复数据，Excel自带的“删除重复项”功能也相当高效。而对于更复杂、数据量更大的匹配和转换任务，Power Query（在“数据”选项卡下的“获取和转换数据”组里）才是真正的杀手锏。它能让你以图形化界面进行数据清洗、合并、匹配，并且可以记录步骤，实现自动化，这对于需要定期处理大量数据的场景来说，简直是生产力倍增器。

Excel VLOOKUP函数怎么用？它有哪些常见坑点？

VLOOKUP函数，全称是“Vertical Lookup”，垂直查找。顾名思义，它就是在你指定的数据区域里，垂直地查找某个值，然后返回同一行中指定列的数据。它的基本用法是这样的：VLOOKUP(查找值, 查找区域, 返回列序号, [匹配模式])。

举个例子，假设你有一个A列是员工ID，B列是员工姓名，C列是部门。你想根据员工ID（查找值），从这个表格（查找区域）里找到对应的部门。如果员工ID在A列，部门在C列（也就是查找区域的第三列），那么公式可能是=VLOOKUP(A2, A:C, 3, FALSE)。这里的FALSE表示精确匹配，也就是员工ID必须完全一致才能找到。

VLOOKUP用起来虽然简单，但它有几个非常经典的“坑”，不注意就容易掉进去：

• 只能从左往右查找： 这是它最广为人知的限制。你的“查找值”必须在你“查找区域”的第一列。如果你想根据员工姓名（B列）查找员工ID（A列），VLOOKUP就无能为力了。你得调整表格结构，或者换用其他函数。
• 返回列序号是硬编码的： 公式里的3就是指返回查找区域的第三列。如果你的同事不小心在B列和C列之间插入了一列新的数据，那么原来的C列就变成了第四列，你的3就错了，公式会返回错误的数据。这在多人协作或者数据结构经常变动时，是个不小的隐患。
• 精确匹配与模糊匹配： [匹配模式]这个参数，FALSE或0代表精确匹配，TRUE或1代表模糊匹配。很多人在不理解模糊匹配的情况下，默认使用了TRUE，结果导致返回了错误的数据。模糊匹配通常用于查找数值范围，比如根据分数段判断等级，而不是精确查找某个ID。绝大多数情况下，我们都需要精确匹配。
• #N/A错误： 如果VLOOKUP找不到匹配的值，它会返回#N/A错误。这本身不是问题，但如果你的表格里充斥着#N/A，看起来就不太美观，也可能影响后续计算。通常我们会用IFERROR函数来包装它，比如=IFERROR(VLOOKUP(...), "未找到")，这样找不到时就会显示“未找到”而不是错误信息。

理解这些限制和坑点，能帮助我们更好地使用VLOOKUP，或者在必要时选择更合适的替代方案。

为什么说INDEX+MATCH组合比VLOOKUP更灵活强大？

INDEX+MATCH组合，在我看来，是Excel函数中的“瑞士军刀”，它解决了VLOOKUP的诸多痛点，提供了更高的灵活性和更强大的功能。它的核心思想是：MATCH函数负责找到某个值在指定区域中的“位置”（行号或列号），而INDEX函数则根据这个“位置”去“索引”出你需要的数据。

我们先来看看这两个函数各自的作用：

• MATCH(查找值, 查找区域, [匹配模式])：它会返回查找值在查找区域中是第几个。比如MATCH("苹果", {"香蕉", "苹果", "橘子"}, 0)会返回2，因为它在第二个位置。
• INDEX(区域, 行号, [列号])：它会返回区域中指定行号和列号交叉处的值。比如INDEX(A1:C10, 5, 2)会返回A1:C10区域中第5行第2列（也就是B5单元格）的值。

当它们组合起来时，威力就显现了：=INDEX(返回数据所在的列, MATCH(查找值, 查找值所在的列, 0))。

举个例子，如果你的员工姓名在B列，员工ID在A列，部门在C列。你想根据员工姓名（B列）查找员工ID（A列）。用VLOOKUP是做不到的，因为它只能从左往右。但INDEX+MATCH可以：=INDEX(A:A, MATCH("张三", B:B, 0))。这个公式首先用MATCH("张三", B:B, 0)找到“张三”在B列的行号，比如是第5行，然后INDEX(A:A, 5)就会返回A列的第5行，也就是“张三”对应的员工ID。

INDEX+MATCH的优势显而易见：

• 无方向限制： 这是它最核心的优势。查找值可以在返回值的左边、右边、上面或下面，只要你能指定查找列和返回列就行。这极大地解除了VLOOKUP的束缚。
• 对列插入/删除不敏感： VLOOKUP的返回列序号是硬编码的数字，一旦插入或删除列就可能出错。但INDEX+MATCH直接引用的是列范围，比如A:A或C:C，无论你在中间插入多少列，这些引用都不会改变，公式依然有效，维护起来省心多了。
• 实现多条件查找： 虽然这需要配合数组公式（Ctrl+Shift+Enter）或者辅助列，但INDEX+MATCH是实现多条件查找的基础。例如，你想根据“产品名称”和“颜色”两个条件来查找“价格”，INDEX(价格列, MATCH(1, (产品名称列=查找产品名称)*(颜色列=查找颜色), 0))，这就是一个典型的多条件数组公式写法。
• 返回整行或整列： INDEX函数本身就可以返回一整行或一整列，这在某些高级应用中非常有用。

总之，INDEX+MATCH组合提供了比VLOOKUP更强大的灵活性和健壮性，是处理复杂数据匹配场景的首选。虽然公式看起来比VLOOKUP稍长一点，但一旦掌握，你会发现它能解决更多实际问题。

XLOOKUP函数有哪些新特性，它能替代VLOOKUP和INDEX+MATCH吗？

XLOOKUP函数是微软在Office 365和Excel 2019及更高版本中引入的一个全新查找函数，它旨在取代VLOOKUP、HLOOKUP，并在很大程度上简化了INDEX+MATCH的复杂性。可以说，它集大成者，是未来Excel数据查找的主流。

XLOOKUP的基本语法是：XLOOKUP(查找值, 查找区域, 返回区域, [未找到时显示值], [匹配模式], [搜索模式])。

我们来逐一看看它的新特性：

• 双向查找，无方向限制： 这是XLOOKUP最直观的改进。它不像VLOOKUP那样只能从左往右，你可以随意指定查找区域和返回区域，它们可以是同一列，也可以是相邻或不相邻的列，甚至查找区域在返回区域的右边也完全没问题。这彻底解决了VLOOKUP的痛点。
• 默认精确匹配： VLOOKUP默认是模糊匹配（TRUE），导致很多新手出错。XLOOKUP默认就是精确匹配（0），这更符合我们日常查找的习惯，减少了误操作。如果你需要模糊匹配，可以通过[匹配模式]参数来设置。
• 内置错误处理： XLOOKUP在找不到匹配项时，可以直接通过第四个参数[未找到时显示值]来指定显示什么内容，比如"未找到"、""（空字符串）或者其他提示。这省去了我们再用IFERROR包装的麻烦，让公式更简洁。
• 支持通配符匹配： 通过[匹配模式]参数设置为2，XLOOKUP可以直接支持通配符（*代表任意字符，?代表单个字符）进行模糊查找，这在查找包含特定关键词的数据时非常方便。
• 支持反向搜索或二分查找： [搜索模式]参数允许你指定是从第一个匹配项开始搜索，还是从最后一个匹配项开始搜索（反向搜索），甚至支持二分查找（适用于已排序的数据，效率更高）。这在处理重复值或者需要特定顺序查找时非常有用。
• 支持垂直和水平查找： XLOOKUP不仅仅是“Vertical Lookup”，它也能像HLOOKUP一样进行水平查找，真正实现了“万能查找”。

那么，XLOOKUP能替代VLOOKUP和INDEX+MATCH吗？我的答案是：对于支持XLOOKUP的Excel版本，它几乎可以完全替代VLOOKUP和大部分INDEX+MATCH的场景。 它的语法更直观，功能更强大，错误处理更友好，无疑是更优的选择。

然而，考虑到并非所有人都使用最新版本的Excel（尤其是企业环境），VLOOKUP和INDEX+MATCH在很长一段时间内仍然会是重要的工具。如果你需要与使用旧版本Excel的用户共享文件，或者你的工作环境强制使用旧版本，那么掌握VLOOKUP和INDEX+MATCH依然是必要的。但如果你的条件允许，强烈建议拥抱XLOOKUP，它会让你的数据匹配工作变得更高效、更简单。

除了函数，Excel还有哪些实用工具可以帮助数据匹配和清理？

Excel不仅仅是函数的天下，它内置的许多工具也能在数据匹配和清理中发挥巨大作用，甚至在某些场景下比函数更直观、更高效。

• 条件格式： 这是我经常用来做初步数据探索和匹配验证的工具。比如，你想快速找出两列数据中哪些值是相同的，或者哪些是重复的，你可以选中一列数据，然后使用“条件格式”->“突出显示单元格规则”->“重复值”，它会立即用颜色标记出重复项。如果你想比较两列数据的不一致性，可以选中这两列，然后用公式规则来高亮那些不匹配的单元格。这对于一眼看出数据差异，进行初步匹配检查非常有效。

• 删除重复项： 当你从不同来源合并数据，或者数据录入时出现了重复，你需要一份干净、唯一的清单时，“数据”选项卡下的“删除重复项”功能就派上用场了。你可以选择基于哪些列来判断重复，然后Excel会帮你快速删除掉多余的重复行，留下唯一值。这个操作非常直接和高效。

• 文本分列： 有时候，你从外部系统导入的数据，一个单元格里包含了多个信息，比如“姓名-工号”混在一起。在进行匹配前，你可能需要先把这些信息拆分开来。“数据”选项卡下的“文本分列”功能可以根据分隔符（如逗号、空格、横杠）或固定宽度，将一个单元格的内容拆分成多个列，为后续的匹配工作做好数据准备。

• 数据验证： 这是一种“防患于未然”的工具。在数据录入阶段，你可以通过“数据验证”来限制单元格的输入内容，比如只能输入数字、日期，或者只能从一个预设的列表中选择。这样可以从源头上保证数据的一致性和规范性，减少后续匹配时因数据格式不统一而出现的问题。

• Power Query（获取和转换数据）： 这绝对是Excel数据处理的“核武器”，尤其当你需要处理大量、多源、复杂的数据匹配和转换任务时。Power Query允许你以图形化界面连接各种数据源（Excel文件、数据库、网页、CSV等），进行数据清洗、筛选、合并、匹配（比如通过“合并查询”功能），并能记录下所有操作步骤。这意味着你可以构建一个可重复、自动化的数据处理流程。比如，你想将两个大表根据多个字段进行精确匹配合并，Power Query的“合并查询”功能可以轻松实现，并且比写复杂的数组公式更直观、更稳定。它的强大之处在于，一旦设置好，下次有新数据时，只需刷新一下，所有步骤都会自动执行，极大地提升了工作效率。

• 高级筛选： 当你需要根据一个或多个复杂条件来筛选出匹配的数据时，高级筛选比普通筛选提供了更多的灵活性。你可以设置一个独立的条件区域，用逻辑公式来定义筛选规则，从而筛选出符合特定匹配模式的数据。

这些工具各有侧重，但都能在不同的阶段和场景下，帮助我们更有效地进行数据匹配、清理和准备，让我们的数据分析工作更加顺畅。

理论要掌握，实操不能落！以上关于《Excel数据匹配技巧与函数使用教程》的详细介绍，大家都掌握了吧！如果想要继续提升自己的能力，那么就来关注golang学习网公众号吧！