Excel、PowerQuery 和 ChatGPT 终极手册（上）

原文：Ultimate ChatGPT Handbook for Enterprises

译者：飞龙

协议：CC BY-NC-SA 4.0

序言

在不断发展的数据管理和分析领域中，掌握 Excel 的查找功能不仅是一种技能，更是高效数据处理的基石。《使用 Power Query 和 ChatGPT 的终极 Excel》不仅仅是一本书；它是为数据爱好者、Excel 爱好者以及希望充分利用 Excel 查找功能潜力的任何人而设计的指南。

本书通过 Excel 的查找功能展开了一场变革性的旅程，从传统方法到定义当前数据管理时代的开创性技术。每一章都展示了一个新的维度，揭示了谬误，揭开了复杂性，介绍了嵌套函数，并提供了实际数据查找困境的解决方案。

每章结束时，你不仅会掌握重点查找功能，还会学习许多其他嵌套函数，扩展你对 Excel 的知识。

本书分为九章。它们将涵盖从 Excel 基础知识和基本查找功能到在 Excel 中使用人工智能的高级用法。具体内容如下。

第一章：本章将介绍 Excel 的基础知识。你将了解为什么需要发展 Excel 技能以及掌握这些技能的一般步骤。我们将探索 Excel 公式和函数的世界，包括嵌套或组合函数。此外，我们还将介绍 Excel 单元格引用的基础知识和使用 Excel 表格。

第二章：本章将介绍 Excel 中最常用的查找函数 — VLOOKUP。它将突出其挑战，并通过展示如何嵌套其他函数来克服 VLOOKUP 默认设置的挑战，提供解决方法。

第三章：本章将介绍 Excel 用户普遍认为是一种查找函数的函数组合 — INDEX 和 MATCH。我们将探讨这两个函数的工作原理以及它们的组合是第二种最广泛使用的查找数据方法。之后，我们将研究这种组合在何种情况下优于 VLOOKUP 函数。

第四章：LOOKUP 函数与 Excel 本身一样古老。自 1985 年以来一直存在。与其后继者 VLOOKUP 不同，LOOKUP 函数有一个独特的特点 — 它可以处理数组。本章将调查其在 35 年后的实用性，特别是在它是更好选择的领域。

第五章：除了 Excel 分类为查找函数的内容外，在 Excel 中还有不同的查找数据的方法。本章探讨了可以用来查找数据的不同方法和函数。我们将学习一些很少讨论的方法，例如使用 Excel 交集、高级筛选、数据库函数以及使用数据透视表。

第六章：XLOOKUP 于 2019 年发布，是任何函数中最受瞩目的发布。它被称为 Excel 中最强大的查找函数。本章将研究 XLOOKUP 可能成为首选查找函数的场景。

第七章：虽然没有像 XLOOKUP 那样受到广泛关注，但 FILTER 函数弥补了以前查找函数的所有缺点。本章将探讨 FILTER 是终极查找函数的领域。我们还将看看它的缺点以及如何解决。

第八章：Power Query 被誉为 Excel 的终极数据提取、加载和转换插件，但它也可以用于查找数据。本章将探讨如何使用 Power Query 创建解决方案，适用于那些害怕调整函数的初学者。

第九章：本章将讨论如何将人工智能（AI）融入到 Excel 中解决查找问题。我们将探讨诸如 ChatGPT 是什么以及如何充分利用它的话题。通过本章结束时，您将能够理解如何促使和利用 ChatGPT 进行准确和个性化的回应。

下载代码

捆绑包和彩色图片

请点击链接下载

该书的代码捆绑包：

github.com/ava-orange-education/Ultimate-Excel-with-Power-Query-and-ChatGPT

该书的代码捆绑包和图片也托管在

https://rebrand.ly/zlukuby

如果代码有更新，将在现有的 GitHub 存储库上进行更新。

第一章

Excel 环境

介绍

本章将向我们介绍 Excel 的基础知识。你将了解为什么需要发展 Excel 技能以及掌握这些技能的一般步骤。

我们将探索 Excel 公式和函数的世界，包括函数的嵌套或组合。我们还将介绍 Excel 单元格引用的基础知识和使用 Excel 表格。

由于这本书是关于在 Excel 中查找任何内容的 101 种方法，我们将深入探讨 Excel 查找技能的世界以及它们为掌握 Excel 打开的大门。最后，我们将深入研究查找函数的不同分类。

结构

在本章中，我们将讨论以下主题：

• 为什么你应该学习 Excel？

• 如何掌握 Excel

• 介绍 Excel 公式和函数

• 函数的嵌套：当一个不够时

• 介绍 Excel 单元格引用：完整列/行，范围引用，命名引用，绝对引用，相对引用和混合引用

• 介绍 Excel 表格

• 具备查找技能的重要性

• 查找函数的分类：

  • 旧版本：传统数组函数

  • 动态数组函数

  • ���大查询

学习 Excel 的原因

Excel 是全球数百万人用来分析、管理和可视化数据的最强大的电子表格工具之一。它被广泛应用于不同行业，如金融、工程、物流、医学、采矿和运营等。

2018 年由两家全球研究顾问公司（Spiceworks 和 Censuswide）进行的调查显示，超过 69% 的专业人士定期使用 Excel，其中 57% 的人至少每天使用一次。此外，超过 59% 的拥有超过 500 名员工的公司使用 Excel 进行数据分析。

因此，无论你所在的行业如何，如果你的工作涉及数据处理或分析，你很可能会使用 Excel。

在个人层面上，Excel 有助于组织信息：它易于以结构化格式组织和存储数据，创建、检索和简单更新信息。这使得它在维护个人预算、跟踪个人物品，甚至创建每日待办事项清单方面非常有帮助。

发展你的 Excel 能力是在当今数据驱动的商业环境中提升职业的一种方式。它通过提高你在需要数据管理、分析和报告的任务中的效率和效果来增强你的生产力。一个高效的工作者是晋升的有力候选人。

总之，Excel 的普及性和多功能性使得获取和提升与 Excel 相关的技能对任何专业人士都是必要的。

如何掌握 Excel

在过去十多年中使用并培训他人使用 Excel，我们已经确定了加快获取和提升 Excel 技能的六个步骤。

这些是已经被遵循并传授给他人的步骤，取得了令人印象深刻的成果。

步骤 1：热爱错误

从错误中学习是获得任何技能中最被低估的步骤之一。

从错误中学习比仅仅阅读理论和练习而不犯错误获得的要多得多。错误提供宝贵的反馈，帮助你完善技术和提高表现。

因此，热爱错误使你摆脱了对失败的恐惧，并帮助你建立韧性和坚持力。

有能力从错误中学习并热爱错误，我们可以实现在所选择领域获得和改善任何技能的目标。这就是为什么这是掌握 Excel 技能的第一步。

第 2 步：掌握基础知识

掌握基础知识是获得任何技能中最被轻视的步骤之一，因为它看起来乏味和重复。然而，基础知识构成了更高级技术的基础。具有对基础知识的深刻理解，可以更容易地在任何领域取得进步和改善。

掌握基础知识的其他障碍包括渴望迅速取得成果和更看重高级技能，认为它们更令人印象深刻或更重要。

例如，用户想要开始使用嵌套的 INDEX 和 MATCH 函数，而不先了解嵌套函数和使用各个函数的基础知识。

在这本书中，我们将花时间建立这个基础，因为它有助于构建复杂的函数。

第 3 步：逐渐增加负荷

获得和提升技能就像锻炼肌肉一样。你需要通过逐渐增加负荷来挑战自己。

掌握基础知识是必不可少的，但固守基础知识对于掌握 Excel 和任何其他技能是有害的。

掌握需要持续练习，逐渐增加难度，跟踪进展，并寻求专家和同事的反馈和指导。

为了促进持续的增长和改善，本书教授的概念将从基础到复杂逐渐展开。

第 4 步：学会将问题分解成小块

你如何吃掉一头大象？答案是“一口一口地吃”。

换句话说，当面对一个庞大而复杂的任务时，一次性解决所有问题似乎是不可能的。然而，如果你将其分解成更小、更易管理的部分，你可以逐步地一步一步地接近，直到最终实现目标。

例如，你有多容易记住数字“12110081644936251694”？

提示是将其分解成小块并观察模式。（请参见章节末的答案）。

Excel 中的复杂任务需要一些公式，其中包含超过五个嵌套函数。如果你知道每个函数对公式的贡献，理解嵌套函数就很容易。

比喻地说，这就是看到森林中的单棵树的艺术。

第 5 步：教授

对于新手来说，教学是最可怕的事情之一，然而这是提升任何技能的重要步骤。对失败的恐惧和缺乏信心阻止了初学者从教学中获益。

当你教别人时，你必须整理思绪并清晰简洁地表达信息。

向他人解释一个概念需要你完全理解材料并预料到学生可能会有的问题和潜在误解。通过这样做，你可以发现自己知识的空白并填补这些空白，从而提高你的理解能力。

“当你教别人时，你自己也在学习两次”这句谚语表明，教授他人可以是巩固和加深你对某一主题理解的强大方式。

要提升你的 Excel 技能，你需要习惯于教授他人你所学到的一点点知识，这样才能牢记。

步骤 6：参与 Excel 论坛

网络连接使世界变成了一个村庄。你可以从任何地方获取世界级专家、讨论和培训。

参加并参与 Excel 论坛对于两个原因至关重要：

1. 这让你可以接触在线学生，从而练习你的教学。正如前一节所讨论的，教学对于掌握 Excel 至关重要。

2. 这为被动和无意识学习提供了绝佳的机会。在这些论坛中，你会遇到许多课程、技巧和窍门，这些你可能从未想到过。

一些建议的论坛包括 Excel 微软答案、Excel 微软技术社区、Excel Stack Overflow 和 Reddit Excel。

步骤 7：每日有意识学习

有意学习与自发学习不同，因为有目的的学习是有意识地为了特定目的获取知识。

例如，这本书的每一章都涵盖了在 Excel 中查找数据的不同方法。如果你每天设定一个特定的时间来学习每一章，你就会进行有意识的学习。

这涉及设定明确的目标，积极寻找信息，并进行刻意练习以提高自己的能力。这需要愿意在学习过程中投入时间和精力。

每日有意识学习提高了学习者教授他人的信心，从而导致更深入的理解。用户进入学习教授和教授学习的生产力循环：

图 1.1： 生产力循环

这并不是贬低偶然学习（在进行其他活动时无意中学到的知识）或意外学习（偶然学到的知识）的好处的尝试。这些方式也能带来好处，但有意学习的好处胜过其他所有形式的学习。

介绍 Excel 公式和函数

Excel 公式和函数是 Excel 的核心。您必须学习公式和函数才能充分利用 Excel 并提高生产力。

在 Excel 中，公式结合了用于计算、数据操作或从工作表数据生成结果的操作。它可能包含单元格引用、算术运算符、数字或函数。

例如，在图 1.2中，如何使用公式计算每张发票的总付款？

图 1.2： 在 Excel 中使用公式

图中使用的公式包含算术运算符(=，+)和单元格引用(C3，D3，E3，F3，G3)。我们将在本章后面讨论更多单元格引用。

另一方面，函数是预定义的一组指令或公式已经内置，可用于对数据执行各种操作。

例如，在图 1.3中，如何使用函数计算每张发票的总付款？

图 1.3： 在 Excel 中使用 Sum 函数

在图 1.3中使用的函数包含算术运算符(=)，单元格范围引用(C3:G3)和内置函数名称(SUM)。

注意

公式和函数都应该以等号(=)开头。

一些用户以加号(+)开头，而其他人则过度使用加号和等号(= +)开头。

我们还可以有一个复杂的公式，它将是操作和函数的组合。

Excel 拥有 500 多个函数，但您只需要学习其中一些才能提高生产力。随着 LAMBDA 函数的引入，这个数字不断增长，使用户有能力创建自己的函数。

这些函数被分类为数学、统计、金融、逻辑、文本、日期和时间、查找和引用、工程等。本书重点介绍查找和引用组以及它们与其他组的关系。

以下是所有函数的最常见特征：

• 组成： 所有 Excel 函数都有一个标准结构，确定它们如何输入到单元格中，即始终以等号开头，然后是函数名称，然后是一个或多个或没有参数，用逗号分隔。

  例如，IF 函数有三个参数，Excel IntelliSense 将概述，而TODAY函数没有参数：

  =IF(Logical_Test, Value_if_True, Value_if_False)

  =Today()

• 参数： 参数是函数用于执行特定计算的值或单元格引用。每个函数需要不同数量和类型的参数才能正常工作。方括号内的任何参数表示它是可选的，因此可以跳过，函数仍然可以正常工作。

• 结果： 每个 Excel 函数都基于输入参数和功能分类返回结果。例如，所有被分类为文本函数的函数将始终返回文本数据类型的结果。

• 兼容： Excel 函数可以很好地协同工作，允许您将一个函数的结果用作另一个函数的输入。这称为嵌套函数，稍后将在本章中介绍。嵌套功能允许您设计复杂的计算和公式。

• 内置： 所有 Excel 函数都是内置的，这意味着它们是 Excel 软件的一部分，不需要单独安装。但是，函数仅在不同版本的 Excel 中可用。例如，您只能在 Office 365 中访问动态数组公式。

• 可访问性： Excel 函数可以通过公式栏（图 1.4）和函数向导轻松访问：

图 1.4： 通过公式栏访问函数

或者，通过直接在单元格中键入函数（图 1.5）：

图 1.5： 通过直接在单元格中键入访问函数

嵌套函数：当一个不够时

在 Excel 中嵌套函数是执行复杂计算和操作数据的终极技能。通过组合多个函数，您可以执行使用单个函数难以或不可能完成的任务。

那么，嵌套函数意味着什么？

嵌套函数在 Excel 中是指使用一个函数的结果作为另一个函数的输入。您可以尽可能多地这样做，以创建执行多个计算的复杂公式。

例如，在图 1.6中，我们如何标记只有在一月到期且金额大于或等于 50,000 的发票为优先级？

您会意识到，在 Excel 中没有单个函数可以帮助您解决前面的任务。这就需要一个嵌套公式，如下所示：

=IF(

AND(MONTH(I3)=1, J3>=50000 ),

“YES”,”NO”)

这个函数是如何工作的？

首先，您需要意识到 IF 函数本身只能处理单个逻辑测试。由于我们的任务有两个分析测试，因此您需要将AND函数嵌套在 IF 函数内部。

其次，您会意识到第一个逻辑测试需要一个月份比较，但您的数据包含日期。因此，您必须将日期转换为月份，将MONTH函数嵌套在AND函数内部。

总之，以下是这三个函数如何一起工作的，从最内部的函数开始：

• MONTH函数返回日期的月份部分。这些结果用于AND函数的第一个逻辑测试中，以检查月份是否为一月（1）。

• AND 函数评估两个逻辑测试（检查月份是否为一月和金额是否大于或等于 50,000），并返回由 IF 函数使用的 TRUE/FALSE 结果，以确定优先级是YES/NO。

  图 1.6： 嵌套函数

解决嵌套函数中的复杂性

让我们学习如何解决嵌套函数中的复杂性：

• 使用括号颜色代码确保所有嵌套函数参数都在括号内。最外层函数的括号始终是黑色；对于嵌套函数，开放和关闭括号颜色相同。

• 另一个选择是确保每次编写函数时，在输入参数之前跟着开放和关闭括号。

• 利用 Excel 的函数智能感知显示您正在处理的函数和参数。

• 使用命名范围和表的结构引用使您的函数更易读。

• 始终使用 F9 快捷键评估每个函数的结果。

嵌套函数规则

以下是在嵌套函数中遵循的一些规则：

• 只有最外层函数前面才应该有一个等号，不应该在所有嵌套函数前面加上等号。

• 所有嵌套函数必须返回与其在函数中替换的参数相同的数据类型。使用我们前面的例子，AND函数必须返回布尔数据类型，因为这是IF函数中逻辑测试参数所需的数据类型。

• 所有嵌套函数必须遵循 Excel 的运算顺序：PEDMAS（括号、指数、乘法、除法、加法、减法）

• 嵌套函数从最内层到最外层进行评估。

介绍 Excel 单元格引用

Excel 中的每个单元格都由列字母和行号的唯一组合称为“单元格地址”来标识。

例如，位于第一列第一行的单元格称为“A1”，位于第二列第三行的单元格称为“B3”。

在 Excel 中创建公式时，可以硬编码数据（例如，SUM(10, 12)）或者引用存储在单元格中的数据（例如，SUM(A1, A2)）。这称为单元格引用。

单元格引用是 Excel 中的一个重要概念，了解如何有效使用它们可以帮助您更高效和准确地操作和分析数据。

单元格引用类型

在 Excel 中观察到以下类型的单元格引用：

• 完整行/完整列引用：如果要选择列 A 中的所有 1,048,576 行，则使用完整列引用（A:A）。否则，如果要选择第一行中所有 16,384 列的数据，则使用完整行引用（1:1）。

  我们强烈反对使用完整行和完整列引用的做法，原因有两个：

  • 可能会有一个无效值远在您视野之外的列/行，影响您的分析。

  • 它可能导致电子表格计算缓慢，因为 Excel 必须检查所有 1,048,576 行或 16,384 列。

• 单元格范围引用：它指的是工作表上的一个单元格块，具有起始和结束单元格地址，而不是完整的列/行引用。它可以是同一列中的块（例如，A1:A26），同一列中的块（例如，A1:K1），或行和列的组合（例如，A1:F26）。

  这使您能够一次对多个单元格执行计算、格式设置或其他操作，而不必逐个执行。

  例如，在图 1.7中，每张发票的总和（=SUM(C3:G3)，一行中的单元格范围），每日总和（=SUM(C3:C7)，一列中的单元格范围）和总体总和（=SUM(C3:G7)，两列和两行中的单元格范围）。

  

  图 1.7： 单元格范围引用

• 命名范围引用：这是一种通过名称而不是使用此处讨论的传统单元格引用来引用电子表格中的一系列单元格的方法。要在 Excel 中创建命名范围，请执行以下步骤：

  • 选择要命名的单元格范围，然后转到“公式”选项卡，单击“定义名称”。或者，您可以使用键盘快捷键Alt + M + M + D。

  • 在新名称弹出窗口中，为您的范围命名并定义其范围（您是否希望在整个工作簿中访问它，还是只在创建它的工作表中访问它）。

  创建后，这些命名范围可以像使用单元格引用一样在公式和函数中使用。例如，如果您已经命名了一个从单元格范围 C3:G3 创建的命名范围Invoice100，您可以在公式中引用它，如下所示：

  =SUM(Invoice100)

  这个公式的工作方式类似于之前讨论的=SUM(C3:G3)。使用命名范围可以使您的公式和函数更容易阅读和理解。

  在创建命名范围时，请考虑以下一些规则：

  • 名称应具有描述性，不带空格或特殊字符，并且不能以数字开头。

  • 避免使用相对单元格引用，为您的命名范围使用绝对单元格引用。相对引用和绝对引用之间的区别将在下一节中讨论。

  • 最后，保持您的名称尽可能简短、简单，并在整个工作簿中保持一致。

• 相对引用、绝对引用和混合单元格引用：所有单元格引用默认为相对引用，即它们相对于公式位置的位置在复制或移动到新单元格时会改变。

  例如，如果您在单元格 C1 中有一个公式，引用单元格范围 A1:B1，并将公式复制到单元格 D1，则引用将更改为 B1:C1。

  另一方面，绝对引用始终指向相同的单元格或范围，无论公式被复制或移动到何处。要确定是否为绝对引用，请检查列字母和行号之前是否有美元符号($)。

  例如，如果您想要使单元格范围 A1:B1 绝对化，最简单的方法是突出显示该范围并按下 F4，这将变为$A$1: $B$1**。当公式被复制或移动时，引用将保持为$A$1: $B$1。

  混合引用包含相对和绝对组件的组合，其中行或列引用是绝对的，而另一个是相对的。

  例如，如果你有一个引用单元格 A1:A10 的公式，并且你希望列是绝对的，但行是相对的，你会使用 $A1:$A10。 当公式被复制或移动时，它将始终引用列 A，但行引用将根据公式的位置而改变。

  否则，如果您希望列是相对的而行是绝对的，请使用 A$1:B$1。 当公式被复制或移动时，它将始终引用第 1 行，但列引用将根据公式的位置而改变。

介绍 Excel 表格

假设你想携带十个笨重的物品；哪种方法更容易？把它们放在一个容器里还是试图把它们排列在你的手上？当然，把它们放在一个容器里是最容易的方法。

Excel 表格只是帮助您轻松组织、分析和简洁呈现数据的容器，通过自动化特定任务节省您的时间。此外，它允许快速对数据进行排序和筛选，这可以帮助您发现数据中的模式或趋势。

此外，Excel 表格还允许您在公式中使用结构化引用，使您的公式更易读且更易维护。例如，不是通过它们的单元格地址引用单元格，您可以使用类似 =SUM(tblSales[Amount]) 的公式，即，在名为 Sales 的表中，总计名为 Amount 的列中的所有数据。

图 1.8： 在公式中使用结构化引用

执行以下步骤创建表格：

• 确保您的数据之间没有空白行或列。

• 使用快捷键 Ctrl + A 选择全部。

• 转到“主页”选项卡，选择“格式为表格”，或使用快捷键 Ctrl + T。将弹出一个窗口（见 图 1.9）显示格式为表格的数据范围。如果您的数据范围中的第一行包含标题，请勾选“我的表格有标题”复选框。否则，Excel 将在您的范围顶部创建一个额外的标题行。

  图 1.9： 在 Excel 中创建表格

• 单击表格的任意位置，转到“表格设计”选项卡，并为您的表格指定一个描述性名称。默认情况下，Excel 给出一个通用的表格（见 图 1.10）：

  图 1.10： 重命名您的表格

• 要将表格转换回范围，请转到“表格设计”选项卡，然后单击“转换为范围”：

  图 1.11： 将表格转换为范围

现在，让我们看看使用表格的原因：

• 它们是数据透视表和图表的重要源数据，因为它们会动态扩展。

• 它们可以快速且轻松地进行格式化。

• 您可以通过单击轻松选择整个表格、列或行。要选择整个表格，请将鼠标悬停在左上角并单击（见图 1.12）。

  图 1.12： 选择整个表格

  要仅选择包含数据的列范围，请将鼠标悬停在列标题上并单击一次（见图 1.13）。

  

  图 1.13： 选择整个列

  将鼠标悬停在表格的左边缘以选择行范围并单击（见图 1.14）。

  

  图 1.14： 选择整个行

• Excel 表格使用结构引用，使得创建复杂公式更加容易。结构引用使用表格列名和函数来简化公式，节省时间并减少错误。

具备查找技能的重要性

本书旨在通过建立用户的查找技能来帮助他们掌握 Excel。

那么，为什么查找函数对任何 Excel 用户如此重要呢？

这里是 11 个主要原因：

• 快速、高效和有效的数据检索：Excel 查找函数可以加快在广泛数据集中查找和检索特定数据的速度，跨工作表或工作簿，从而减少手动数据搜索和检索时间。

• 提高整体数据分析准确性：通过使用这些函数，您还可以减少手动数据搜索过程中可能出现的错误，并确保准确访问正确的数据。

• 增强数据分析和报告：查找函数帮助您比较和分析来自不同来源的数据，并将它们整合到一个报告中，减少错误。

• 改善决策：准确及时的信息对于做出明智决策至关重要。此外，从整合数据中获得全局视图对于做出更好的决策至关重要。查找函数在数据整合、节省时间和准确数据检索方面至关重要。

• 提高生产力：使用查找函数，您可以更快速、更准确地工作，从而提高生产力和表现。

• 灵活且易学：Excel 查找函数相对容易学习，一旦理解，就可以以各种方式使用，使其灵活且适应不同情况。

• 减少错误：查找函数通过提供准确可靠的结果来减少数据分析中错误的风险。

• 增强数据可视化：查找函数允许您创建动态报告和图表，随着新数据的添加而自动更新，增强数据的可视化和呈现。

• 更好的数据组织：通过自动化数据检索和组织，查找函数可以帮助保持数据更有条理，更易于管理。

• 允许进行更复杂的计算：使用查找函数，用户可以执行更复杂的计算，否则这些计算将是耗时或无法手动完成的。

• 提高 Excel 熟练度：不同的查找方法整合了 Excel 函数中的许多功能。学习这些方法可以提高您的整体 Excel 熟练度，并使您在使用 Excel 时更加高效和有效。

查找函数分类

Excel 函数可以广泛分为以下三类：

• 动态数组函数（自动将结果溢出到相邻单元格）

• 传统数组函数（如果需要将结果溢出到相邻单元格，则需要使用键盘快捷键Ctrl + Shift + Enter输入它们）

• M函数（用于在 Power Query 中执行数据转换的函数）

在本书中，我们将学习使用来自三个类别的各种方法的查找技巧。

我们将从学习传统的数组函数开始，首先是最流行的VLOOKUP，然后是INDEX和MATCH的流行组合，最后是最古老的查找函数LOOKUP。最后，我们将学习使用数据库函数、聚合函数、数学函数以及一些文本函数来检索数据的独特方法。

稍后，我们将进入节省时间和动态数组函数，首先是最流行的XLOOKUP，然后是方便的FILTER函数。在这个过程中，我们将学习如何嵌套其他动态函数，如VSTACK、HSTACK和SEQUENCE，以创建高效的查找公式。

最后，我们将探讨如何使用 Power Query 表连接、列表函数、分组以及如何在 Power Query 中创建动态条件来查找数据。

结论

Excel 技能对于任何工作职责包括任何形式的数据操作的专业人士都是必不可少的。此外，这些技能可以用于管理个人数据，如预算、跟踪日常习惯和制作待办事项清单。

在所有要学习的 Excel 技能中，学习查找技能对您的生产力和数据分析准确性都会产生即时影响。

在下一章中，我们将深入研究最流行的 Excel 函数之一：VLOOKUP。

问题

你找到记住这个数字“12110081644936251694”的诀窍了吗？

解决方案：

将其分解为这些组 →121 | 100 | 81 | 64 | 49 | 36 | 25 | 16 | 9 | 4

你能看出模式了吗？

这些是从 11 到 2 的数字的平方。如果你将问题分解成小块，问题就变得如此简单。

第二章

VLOOKUP 已死：还是吗？

介绍

本章将向我们介绍最流行的 Excel 查找函数 — VLOOKUP。我们将学习其基本结构和固有弱点，以及如何将其与其他函数嵌套以克服其弱点。

另外，由于这本书是关于精通 Excel，我们将深入研究不同的 Excel 函数。

结构

在本章中，我们将讨论以下主题：

• VLOOKUP 精确匹配和近似匹配

• 打破 VLOOKUP 的神话

  • 神话 1: VLOOKUP 无法进行左侧查找

  • 神话 2: VLOOKUP 无法在查找中返回多列

  • 神话 3: VLOOKUP 无法进行多条件查找

  • 神话 4: VLOOKUP 无法处理在查找范围中插入和删除列

  • 神话 5: VLOOKUP 无法进行双向查找

  • 神话 6: VLOOKUP 无法进行部分匹配查找

  • 神话 7: VLOOKUP 无法进行区分大小写的部分匹配查找

  • 神话 8: VLOOKUP 无法进行区分大小写的查找

  • 神话 9: VLOOKUP 无法返回多个结果

  • 神话 10: VLOOKUP 无法从后往前查找

  • 神话 11: VLOOKUP 无法查找前 N 个或后 N 个值

  • 神话 12: VLOOKUP 无法进行反向查找

  • 神话 13: VLOOKUP 无法进行水平查找

  • 神话 14: VLOOKUP 无法返回多个不连续的列

  • 神话 15: VLOOKUP 无法查找多个不连续的数组

VLOOKUP 精确匹配和近似匹配

正如我们在前一章中所学到的，掌握基础知识是掌握任何技能的第一步。因此，以下是关于 VLOOKUP 的必知内容。

VLOOKUP 允许你在表格的最左列（即 查找值）中搜索特定值，然后从同一行中指定的列（即 列索引号）返回一个值。你可以指定是否要进行近似匹配或精确匹配（即 范围查找）。

默认情况下，VLOOKUP 将进行近似匹配。

语法：

=VLOOKUP(查找值, 表数组, 列索引号, [范围查找])

注意事项：

• 查找值必须在表格的第一列中。在 图 2.1 中，由于发票号是查找值，所有发票都存储在我们表格的第一列中。

• 我们的表格中只有两列是必要的 — 存储查找值的列和存储要返回值的列。在 图 2.1 中，即使我们没有第三列，我们的公式仍然可以工作。

• 由于我们希望对查找值进行精确匹配，我们选择 FALSE 作为查找范围。你可以用零 0 替换 FALSE，如下所示：

  =VLOOKUP(F3, tblSales, 2, 0)

注意：Excel 将布尔值存储为整数，值为0表示FALSE，任何其他值表示 TRUE。当您在单元格中输入TRUE或FALSE时，Excel 会自动将其转换为其等效的整数。

图 2.1： 基本 VLOOKUP 精确匹配

VLOOKUP 近似匹配

默认情况下，VLOOKUP函数设置为返回近似匹配。在处理大量数字和排序数据集，并且可以容忍一些错误时，VLOOKUP非常快速。

然而，大多数搜索需要精确匹配。此外，如果不满足以下要求，近似搜索将返回错误的结果：

• 数据表必须按查找值升序排序。

• 数据表的查找值列中应只有唯一值。

• 数据表查找范围应与查找值具有相同的数据类型。

尽管如此，当分组数据时（例如，为学生分配成绩时；图 2.2），近似匹配是有益的。

图 2.2： VLOOKUP 近似匹配数据分组

在搜索范围内查找值时，近似匹配也适用（例如，根据销售范围给出折扣时；图 2.3）：

图 2.3： 从范围内 VLOOKUP 近似匹配值

注意： 在使用 VLOOKUP 进行近似范围时，您可以跳过最后的范围查找参数，公式仍然有效。

方括号中的任何参数表示是可选的。

=VLOOKUP(查找值, 表数组, 列索引号, [范围查找])

提示：VLOOKUP 近似匹配是嵌套 IF 函数的一个很好的替代，因为计算速度更快。

例如，为了获得与图 2.2中显示的相同结果，您可以使用以下嵌套的 IF 函数或 VLOOKUP：

=IF(J3>=M7, N7, IF(J3>=M6,N6,IF(J3>=M5,N5,IF(J3>=M4,N4,$N3))))

=VLOOKUP(J3, tblGrades,2)

由于前述限制，关于VLOOKUP能做什么或不能做什么有许多神话。

在下一节中，我们将探讨VLOOKUP函数与其他函数嵌套时的全部潜力。

打破 VLOOKUP 的神话

神话是特定文化中的人们长期以来一直在讲述的故事。这些故事通常主要涉及根据历史教导事物为何是其现在的样子。这些有时是半真半假或纯粹的谎言。

关于VLOOKUP函数如何运作的许多教导都被半真半假或纯粹的谎言所掩盖，给VLOOKUP函数带来了不好的名声。

在本节中，我们将通过逐一打破现有的神话来了解VLOOKUP的全部真相。

神话 1：VLOOKUP 无法进行左查找

在图 2.4中，查找值（销售日期）不在表的第一列，而是在那里有返回值（客户）。

在这种情况下，默认的VLOOKUP函数将无法工作。您必须嵌套 IF 函数，如下图所示：

图 2.4： VLOOKUP 左查找

关键是使用IF函数创建自定义表数组。

IF函数返回一个两列表，销售日期是第一列，客户是第二列。

这种列重新排列技巧使VLOOKUP在假定向右查找的同时实际上向左查找。

神话 2：VLOOKUP 无法在查找中返回多列

在图 2.5中，您必须返回特定客户的销售日期和金额。

默认情况下，您一次只能返回一个值，因为 VLOOKUP 中的column_index参数只接受单个值。

然而，您可以将多个column_index值放在花括号中，并返回多个列。

如果您没有 Office 365 订阅，请执行以下步骤：

• 高亮显示要返回多个值的单元格。

• 转到公式栏，输入以下公式。

• 点击Ctrl + Shift + Enter返回以下值：

图 2.5： VLOOKUP 返回多列

神话 3：VLOOKUP 无法在查找中使用多个条件

如果您的数据有重复项，如图 2.6所示，VLOOKUP函数将返回满足条件的第一个值。

例如，如果您查找客户“卡尔·杰克逊”的销售金额，VLOOKUP函数将返回 45,000，因为这是第一次出现。

如果您想查找客户“卡尔·杰克逊”在“2010 年 1 月 14 日”的销售金额怎么办？

默认情况下，VLOOKUP无法查找多个值。您可以按以下方式修改它：

• 使用和号组合多个值。使用我们前面的例子，我们的查找值现在将是Carl Jackson1/14/2010。

• 使用 IF 函数创建一个自定义两列表数组。第一列应包含两列的组合，这两列包含查找值，即按顺序tblSales[Customer]&tblSales[Sales Date]，类似于查找值组合。第二列应包含查找值。

• 使用组合查找值和自定义表数组，在图 2.6中编写您的 VLOOKUP。请记住这是一个数组函数；因此，如果您没有 Office 365，请点击Ctrl + Shift + Enter。

=VLOOKUP( T19&U19,

IF({1,0}, tblSalesT[Customer]&tblSalesT[Sales Date], tblSalesT[Amount]), 2, 0 )

图 2.6： VLOOKUP 多条件

神话 4：VLOOKUP 无法处理查找范围中的列插入和删除

到目前为止，我们一直在函数中硬编码列索引号，即包含返回值的列的编号。

当在此列之前插入或删除列时，这会导致问题，因为它会破坏VLOOKUP函数。

因此，为了使VLOOKUP处理插入和删除，我们需要使用MATCH函数使列索引号动态化，如图 2.7所示：

=VLOOKUP(E3,tblSale,

MATCH(F2,tblSale[#Headers],0),

FALSE)

注意：MATCH函数查找范围中的值并返回其相对位置。

=MATCH(F2,tblSale[#Headers],0)=2

在我们的情况下，MATCH函数在销售表头中查找存储在单元格 F2 中的文本Amount，并始终返回其位置，使其具有动态性。

图 2.7： VLOOKUP 处理插入和删除

神话 5：VLOOKUP 无法进行双向查找

在VLOOKUP函数中硬编码列索引号几乎不可能进行双向查找。

但是，如果像我们从前一节学到的那样嵌套MATCH函数，VLOOKUP函数可以轻松进行双向查找（见图 2.8）。

我们的任务是从客户列表中查找特定客户（Joshua）的销售额，并查找特定地区（Western）的销售额。

由于客户名单存储在我们的SalesRegion表的第一列中，查找值将是单元格 G38 中的特定客户。至于地区，我们将使用MATCH函数返回特定地区的相对列索引。

=VLOOKUP(G38,SalesRegion,

MATCH(G40, SalesRegion[#Headers],0),0)

注意： MATCH 函数始终返回第一个 TRUE 匹配。

由于我们不能在表头中有重复项，MATCH 函数将始终返回正确的列号。

图 2.8： VLOOKUP 双向查找

神话 6：VLOOKUP 无法进行部分匹配查找

VLOOKUP 只有近似匹配和精确匹配，但也可以进行部分匹配。

如果将查找值与通配符结合起来，就可以进行部分匹配，如图 2.9所示。

注意： 通配符是表示文本字符串中一个或多个字符的特殊字符。最常见的通配符是星号（*）和问号（?）。

• 星号（*）：此通配符表示文本字符串中一个或多个字符。例如，“P***”**将找到以字母 P 开头的任何单词，*P将找到以字母 P 结尾的任何单词，*P*将找到包含字母 P 的任何单词。

• 问号（?）：问号通配符表示文本字符串中的单个字符。例如，搜索词H??t将找到任何以“H”开头，以“t”结尾，并在中间有两个字符的四字词，例如“Heat”或“Host”。

• 您可以结合星号和问号来定义您的搜索。例如，???T* 将搜索 T 为第四个字符但以任意数量的字符结尾的任何字符串，例如 Masts，Coating，Soothing 等。另一个例子是*****T??? 这将搜索 T 为倒数第四个字符但以任意数量的字符开头的任何字符串，例如 Beating，Characters，Assisting 等。

  =VLOOKUP( “*” & E54 & “*”, tblinvoices, 2, 0 )

在下面的示例（图 2.9）中，我们查找包含字母 P 的发票金额。我们知道这一点，因为我们在存储在单元格 E54 中的标准前后放置了一个星号。

因此，P 意味着字母“P”前后可能有许多字符。

请注意，该公式返回第一个TRUE值。在本章后面，我们将学习如何在重复项的情况下返回多个值。

图 2.9： VLOOKUP 部分匹配

假设你想查找第五个字符为字母“P”的发票的值？

在这种情况下，我们将使用问号（?）作为通配符来代表任意单个字符。

=VLOOKUP( “????”&E57&”*”, tblinvoices, 2, 0 )

如图 2.10所示，发票号“220-PU-009”是第一张发票，其中字母“P”是第五个字符。我们知道这是正确的发票，因为我们在存储在单元格 E57 中的标准（P��前面放置了四个问号。

请记住，由于发票不以字母“P”结尾，我们必须在标准后面插入一个星号来代表它后面的任意数量的字符。

图 2.10： VLOOKUP 部分匹配单个字符

注意： 由于VLOOKUP函数不区分大小写，前面的示例将搜索字母“P”，无论其是大写还是小写。

神话 7：VLOOKUP 无法进行区分大小写的部分匹配查找

Excel 有两个流行的区分大小写的函数：FIND 和 EXACT。

由于VLOOKUP不区分大小写，当进行区分大小写的部分匹配查找时，我们必须嵌套前面的函数之一。

如图 2.11所示，我们需要查找一个包含小写字母“p”的发票。

=VLOOKUP(TRUE, IF( {1,0},

ISNUMBER(FIND(K54,tblpay[Invoice’#])),

tblpay[Amount]),2,0)

以下是前述函数的工作原理：

• FIND(K54, tblpay[Invoice’#]) 返回一个数字和错误的数组。数字代表发票号码中小写“p”的位置，错误代表任何缺少小写“p”的发票。

• ISNUMBER(FIND(K54,tblpay[Invoice’#])) 将这个数字和错误的数组转换成一个TRUE/FALSE数组。对于任何数字为 TRUE，对于错误为 FALSE。

* 使用 IF 函数创建一个自定义的两列表数组。第一列包含这个由 TRUE/FALSE（查找值列）组成的数组，第二列包含发票金额（返回值列）。* 由于我们的查找值是 TRUE/FALSE 值，VLOOKUP函数应该查找一个TRUE值并返回第一个TRUE` 值。* 由于这是一个数组函数，如果您没有 Office 365 订阅，请单击 Ctrl + Shift + Enter。

`

图 2.11： VLOOKUP 部分区分大小写匹配

迷思 8：VLOOKUP 无法进行区分大小写查找

正如我们从前一节中学到的，FIND 函数将帮助 VLOOKUP 函数进行部分区分大小写查找。

我们必须使用 EXACT 函数来进行整个区分大小写查找（见 图 2.12）：

=VLOOKUP(TRUE,

IF({1,0},

EXACT(Q54,Payment[Product]),

Payment[Amount]),2,0)

以下是前述函数的操作方式：

• EXACT(Q54, Payment[Product]) 返回一个由 TRUE 和 FALSE 组成的数组。 TRUE 代表产品，它与正确大小写的 Furniture 完全相等，否则为 FALSE。

• 使用 IF 函数创建一个自定义的两列表数组。第一列包含这个由 TRUE/FALSE（查找值列）组成的数组，第二列包含发票金额（返回值列）。

• 由于我们的查找值是 TRUE/FALSE 值，VLOOKUP 函数应该查找一个 TRUE 值并返回第一个 TRUE 值。

• 由于这是一个数组函数，如果您没有 Office 365 订阅，请单击 Ctrl + Shift + Enter。

图 2.12： VLOOKUP 完全区分大小写匹配

注意： 由于 VLOOKUP 不区分大小写，如果没有像前面展示的 EXACT 函数，它将返回产品为家具的第一个实例的金额，即 45,000。

迷思 9：VLOOKUP 无法返回多个结果

默认情况下，VLOOKUP 返回第一个 TRUE 值。因此，如果您有重复项目并且想要返回所有 TRUE 值，您必须像 图 2.13 中所示嵌套 SMALL 和 IF 函数。

=VLOOKUP(

SMALL(IF($F$70=tbl[Customer],

ROW(tbl[Customer])), ROW(A1)),

IF({1,0},ROW(tbl[Customer]),tbl[Amount]),

2,0)

以下是前述函数的工作方式：

• IF($F$70=tbl[Customer], ROW(tbl[Customer]) 检查我们的标准客户“Carl Jackson”是否在客户列表中，并且如果为真，则返回找到客户的行号。由于我们有重复的客户，此函数将返回多个行号。

• 我们需要遍历这个行号列表，并逐个作为VLOOKUP的查找值返回。我们使用SMALL函数来完成这个任务，它会从最小到最大返回行号。请注意，ROW(A1)的计算结果为 1，当您拖动函数时，它会逐渐增加，直到完整列表被迭代：

SMALL(IF($F$70=tbl[Customer],ROW(tbl[Customer])), ROW(A1))

• 使用 IF 函数创建一个自定义的两列表数组。第一列包含客户的行号（查找值列），第二列包含发票金额（返回值列）。

• VLOOKUP 函数使用SMALL函数返回的每个行号，并返回相应的金额。

• 请注意，在所有金额都被返回后，VLOOKUP函数会返回#NUM错误。

• 由于这是一个数组函数，如果您没有 Office 365 订阅，请点击Ctrl + Shift + Enter。

图 2.13： VLOOKUP 多个结果

神话 10：VLOOKUP 无法从后往前查找

默认情况下，VLOOKUP从上到下查找值，并返回第一个 TRUE 值。

为了从最后到第一个搜索，我们将LARGE函数嵌套在VLOOKUP中。

使用上一章的示例，要从后往前搜索，我们只需要用LARGE函数替换SMALL函数，如图 2.14所示：

图 2.14： VLOOKUP 多个结果

以下是上述函数的工作原理：

• IF($F$70=tbl[Customer], ROW(tbl[Customer])检查我们的标准客户“卡尔·杰克逊”是否在客户列表中，如果是，则返回客户被发现的行号。由于我们有重复的客户，此函数将返回多个行号。

• 我们需要遍历这个行号列表，并逐个作为VLOOKUP的查找值返回。我们使用LARGE函数来完成这个任务，它会从最大到最小返回行号。请注意，ROW(A1)的计算结果为 1，当您拖动函数时，它会逐渐增加，直到完整列表被迭代。

• 使用 IF 函数创建一个自定义的两列表数组。第一列包含客户的行号（查找值列），第二列包含发票金额（返回值列）。

• VLOOKUP 函数使用 LARGE 函数返回的每个行号，并返回相应的金额。

• 请注意，在所有金额都被返回后，VLOOKUP函数会返回#NUM错误。

• 由于这是一个数组函数，如果您没有 Office 365 订阅，请点击Ctrl + Shift + Enter。

神话 11：VLOOKUP 无法查找前 N 个或后 N 个值

通过嵌套SMALL或LARGE函数，VLOOKUP可以快速返回未排序列表中的前 N 个或后 N 个金额（见图 2.15）：

图 2.15： VLOOKUP 前三个销售金额

=VLOOKUP(

SMALL(IF(TopSale[Amt]>=LARGE(TopSale[Amt],3),ROW(TopSale[Amt])),ROW(A1)),

IF({1,0},ROW(TopSale[Amt]),TopSale[Amt]),2,0)

以下是前述函数的执行方式：

• TopSale[Amt]>=LARGE(TopSale[Amt],3)检查销售金额是否大于或等于第三大销售金额，并返回一个 TRUE/FALSE 数组。

• IF 函数返回所有销售金额大于或等于第三大销售金额的行号列表。

• 我们需要遍历这个行号列表，并逐个作为VLOOKUP的查找值返回。为此任务，我们使用SMALL函数，它从最小到最大返回行号。注意ROW(A1)评估为 1，当您拖动函数时，它会增加直到完整列表被迭代。

SMALL(IF(TopSale[Amt]>=LARGE(TopSale[Amt],3),ROW(TopSale[Amt])),ROW(A1))

• 使用 IF 函数创建自定义的两列表数组。第一列包含金额的行号（查找值列），第二列包含发票金额（返回值列）。

• VLOOKUP 函数使用 SMALL 函数返回的每个行号，并返回相应的金额。

• 请注意，在所有金额已经返回之后，VLOOKUP函数会返回#NUM错误。

• 由于这是一个数组函数，如果您没有 Office 365 订阅，请单击 Ctrl + Shift + Enter。

神话 12：VLOOKUP 无法进行反向查找

在 Excel 中，反向查找是根据已知结果在表中查找值的一种方法。这是前一节讨论的双向查找的反向（见神话 5）。

例如，如图 2.16所示，您正在寻找给患者的医生和相应的会话。

**图 2.16：**VLOOKUP 反向查找

=VLOOKUP(

MAX(IF(Clients=F86,ROW(Clients))),

CHOOSE({1,2,3},

ROW(Clients),

Doctors&”-Morning”,

Doctors&”-Afternoon”),

MAX(IF(Clients=F86, COLUMN(Clients)-COLUMN(Doctors)+1)),0)

在开始学习公式如何工作之前，让我们了解一下我们使用的命名范围：

• 客户 = C86:D95

• 医生 = B86:B95

现在，前述函数的执行方式如下：

• IF(Clients=F86, ROW(Clients))检查单元格 F86 中的客户是否在命名范围中的客户数组中。假设没有重复项，IF 函数将返回客户的行号；否则，返回 FALSE 值（见图 2.17）：

  **图 2.17：**返回客户行

• MAX(IF(Clients=F86, ROW(Clients)))``:MAX 函数忽略FALSE值并返回行号。这成为VLOOKUP函数的查找值。

• CHOOSE({1,2,3}, ROW(Clients), Doctors&”-Morning”, Doctors&”-Afternoon”): CHOOSE创建了一个表数组，用于VLOOKUP函数，其中客户行号作为查找值列，将医生与会话组合为返回值列。

  图 2.18： VLOOKUP 自定义表数组

• MAX(IF(Clients=F86, COLUMN(Clients)-COLUMN(Doctors)+1)) 检查 F86 单元格中的客户是否在命名范围中的客户数组中，并返回客户的列号；否则，返回 FALSE 值。请注意，我们必须通过减去医生的列数并加 1 来调整列计数。

• 使用前述输入，VLOOKUP函数可以进行反向查找。

神话 13：VLOOKUP 无法进行水平查找

在 Excel 中，许多水平查找都留给了HLOOKUP函数，但这不再是情况，因为VLOOKUP函数也可以进行水平查找，如图 2.19所示。

在以下示例中，您应该查找每个项目报价最低的代表。

图 2.19： VLOOKUP 水平查找

=VLOOKUP(

MIN(C101:E101),

TRANSPOSE(IF({1;0},C101:E101,Reps)),

2,0)

此函数的工作方式如下：

• MIN(C101:E101) 返回每个项目的最低金额。这成为VLOOKUP函数的查找值。

• IF({1;0}, C101:E101, Reps) 返回一个两行表数组，其中第一行包含报价，第二行包含代表（见图 2.20）：

  图 2.20： 两行表数组

• 由于VLOOKUP只能垂直查找数据，我们需要使用TRANSPOSE函数将两行表转置为两列表（见图 2.21）：

  图 2.21： 两列表数组

• 使用最低报价作为查找值和转置表作为表数组，VLOOKUP函数快速返回了报价最少的代表。

神话 14：VLOOKUP 无法返回多个非连续列

在前一节中，我们讨论了神话 2，即如何返回多个连续的列。现在，让我们进一步讨论如何返回多个非连续的列。

在我们的示例中（图 2.22），我们必须返回金额和地区列的数据：

图 2.22： VLOOKUP 非连续列

=VLOOKUP(G113, tblTransact,{4,2},0)

我们必须记住的唯一技巧是将所需的列号放在花括号中。此外，如果您没有 Office 365 订阅，首先突出显示列，转到公式栏，编写上述公式，最后，单击 Ctrl + Shift + Enter。

如果您希望以行方式返回值，则唯一要记住的技巧是在列号之间使用分号（;）而不是逗号（,）作为分隔符（见图 2.23）：

图 2.23： VLOOKUP 逐行查找非连续列

神话 15：VLOOKUP 无法查找多个非连续数组

动态查找非连续表需要更多的函数嵌套，如图 2.24所示：

=VLOOKUP([@Amount],

CHOOSE(MATCH([@Product],{“椅子”,”笔记本电脑”},0),

椅子折扣, 笔记本电脑折扣), 2, TRUE)

该函数的工作原理如下：

• MATCH([@Product],{“椅子”, “笔记本电脑”},0) 动态返回产品在查找数组中的位置，即，椅子 = 1，笔记本电脑 = 2。

• CHOOSE(MATCH([@Product],{“椅子”, “笔记本电脑”},0), 椅子折扣, 笔记本电脑折扣))：CHOOSE函数使用 MATCH 函数返回的位置来确定查找表数组，即，1=椅子折扣，2=笔记本电脑折扣。

• VLOOKUP函数然后检查所选折扣表金额中的销售额，并返回近似折扣。

图 2.24： VLOOKUP 非连续查找表

结论

在本章中，我们学会了超越关于VLOOKUP的常识，并试图发挥其全部潜力。

我们发现，通过使用 IF 函数创建自定义表数组，无需重新排列列即可使VLOOKUP向左查找。此外，通过在花括号中包含所需的返回列，我们可以返回多个列。

此外，您无需费力使用HLOOKUP函数进行水平查找；相反，您可以简单地转置您的数据。

与MATCH函数结合使用时，VLOOKUP执行的任务等同于INDEX + MATCH的组合。

在下一章中，我们将调查为什么许多 Excel 用户更喜欢使用 INDEX 和 MATCH 函数的组合而不是VLOOKUP和MATCH。

需要记住的要点

• 每个 Excel 函数都有其限制，但大多数可以通过嵌套另一个函数来调整。这就是为什么我们在第一章指出每个 Excel 用户需要磨练他们的函数嵌套技能的原因。

• 使用 IF 函数为VLOOKUP函数创建自定义表数组使其更灵活且能够向左查找。然而，IF 函数仅限于返回一个两列表。如果我们需要更多列，我们使用CHOOSE函数。

• VLOOKUP函数默认为近似搜索。这是它最大的弱点之一，任何用户都应该意识到这一点。

• 最后，永远不要在挑战之前接受函数的弱点。`

第三章

INDEX 和 MATCH

介绍

本章将介绍被视为VLOOKUP函数替代品的INDEX和MATCH。这些嵌套函数如此受欢迎，以至于一些用户认为它们是独立的函数。我们将首先分析这两个函数，然后看看它们为何是如此强大的组合。

此外，由于本书是关于精通 Excel，我们将学习如何将它们与其他函数结合使用来解决复杂的查找问题。

结构

在本章中，我们将讨论以下主题：

• INDEX、MATCH 和双向查找

• 三向查找

• 反向查找单个结果

• 反向查找多个结果

• 多条件查找

• 返回多列

• 水平查找

• 查找非连续数组

• 使用通配符进行查找

• 基于文本长度进行查找

• 在列表中查找项目

• 查找唯一值

INDEX、MATCH 和双向查找

双向查找用于通过匹配两个条件找到特定值。当您有一个数据表并且需要检索行和列的交点处的值时，通常会使用它。

INDEX 函数返回给定行和列的交点处的数据。因此，该函数需要一个数据数组，一个可选的行号（如果是单行数组），以及一个可选的列号（如果是单列数组）。

我们将首先查找多行和多列数组，如图 3.1所示。我们试图回答问题，“客户 Gupta 从西部地区购买了多少？”：

图 3.1： 索引多行和多列查找

=INDEX(RegionSales,10,3)

注意： 如果您的数据在表格中（这是范围 A2:D13 的日期），行计数从标题之后开始。

这就是为什么在图 3.1中，Gupta 在第 10 行而不是第 11 行。

我们可以选择您想要的列或行，而不是选择整个表格。

例如，在图 3.2中，由于我们知道我们想要西部地区的销售额，我们可以选择整个列，并仅提供包含我们客户的行号：

=INDEX(RegionSales[Western],10)

注意：如果您的数据在单列中，只需提供行号即可。

您可以将列号输入为 1，但是由于您只选择了一个列——西部地区的数据，因此这是不必要的。

例如，您可以将前述函数重写如下：

=INDEX(RegionSales[Western],10,1)

图 3.2： 索引单列查找

或者，您可以选择包含客户**“Gupta”**的整行，并仅提供列号，如图 3.3所示：

=INDEX( A11:D11, 3 )

图 3.3： 索引单行查找

注意： 如果您的数据在单行中，只需提供列号即可。

您可以将行号输入为 1，但这是不必要的。

例如，您可以将前面的函数重写如下：

=INDEX(A11:D11,1,3)

现在我们已经了解了 INDEX 函数的工作原理，让我们修改MATCH函数。

正如我们在第二章，“VLOOKUP 已死：还是吗”中学到的，MATCH 函数返回数组中项目的相对位置。例如，在图 3.2中，我们可以动态获取行号而不是硬编码。我们使用MATCH函数来实现这一点，如图 3.4所示：

图 3.4: MATCH 函数获取行号

注意: MATCH函数返回第 10 行，但从图中我们可以看到客户在第 11 行。

正如我们之前注意到的，这是因为 MATCH 函数返回查找数组中值的相对位置。由于标题数据不在查找数组内，MATCH函数已忽略它。

知道MATCH函数可以自动化在INDEX函数中硬编码的行号，让我们用它替换行号（见图 3.5）：

=INDEX(RegionSales[Western],

MATCH(F3,RegionSales[Customer],0))

这是编写简单的 INDEX 和 MATCH 组合的最佳方式：

• 仅选择您需要的列/行。

• 使用MATCH函数获取条件行/列而不是硬编码它。

图 3.5: INDEX 和 MATCH 函数

我们不仅限于选择单列或单行数组。我们可以选择完整的表格并嵌套两个 MATCH 函数使我们的解决方案动态化（见图 3.6）：

=INDEX(RegionSales,

MATCH(F3,RegionSales[Customer],0),

MATCH(G3,RegionSales[#Headers],0))

图 3.6: 动态二向查找

三向查找

三向查找公式允许您使用三个条件从表中获取数据（见图 3.7）：

=INDEX(RegionSales,

MATCH(G18&H18,RegionSales[Product]&RegionSales[Customer],0),

MATCH(G20,RegionSales[#Headers],0))

图 3.7: 动态三向查找

前面的函数执行如下：

• G18&H18返回一个单一的组合条件，“ChairsRuby”。

• RegionSales[Product]&RegionSales[Customer]连接两列并返回一个产品和客户的单向数组，我们可以用它来查找组合条件（见图 3.8）：

提示：创建单向数组时，要遵循与创建组合条件相同的顺序。

在我们的示例中，组合条件是Product 和 Customer的连接（ChairsRuby）；这就是我们在创建单一数组时遇到相同顺序的原因。

• MATCH(G18&H18, RegionSales[Product]&RegionSales[Customer],0)返回包含组合条件的自定义单向数组中的行号。

• MATCH(G20, RegionSales[#Headers],0)) 返回包含月份条件的表头中的列号。

• 请记住这是一个数组函数，因为我们正在进行条件和列的连接。因此，如果您没有 Office 365 订阅，请在完成编写时记得单击Ctrl + Shift + Enter。

图 3.8： 单向数组

反向查找单个结果

反向查找是之前讨论的双向查找函数的相反。您从交叉点的数据开始，并返回行和列标题。

例如，在图 3.10中，我们知道考试，但我们必须查找导师和大厅：

=INDEX(Tutors&“-”&Halls,

SUM((Exams=E33)*ROW(Exams))-ROW(Halls),

SUM((Exams=E33)*COLUMN(Exams))-COLUMN(Tutors))

以下是前述函数的工作原理：

• 导师&“-”&大厅 返回一个包含每位导师和大厅组合的自定义表。

• (Exams=E33)*ROW(Exams) 检查我们的条件考试是否在考试列表中，并返回一个TRUE / FALSE数组，当乘以考试行时，在FALSE处返回 0，在TRUE处返回行号（见图 3.9）。

• 我们对数组进行求和，以从前一步得到单个行号。由于INDEX从标题开始计算行号，因此我们需要减去标题的行号：SUM((Exams=E33) * ROW(Exams)) - ROW(Halls)

• 重复之前提到的类似步骤以获取列号：SUM((Exams=E33) * COLUMN(Exams))- COLUMN(Tutors))

• 请记住这是一个数组函数，因为我们创建了自定义导师和会话表。因此，如果您没有 Office 365 订阅，请在完成编写时记得单击Ctrl + Shift + Enter。

图 3.9： 考试行号

提示： 如果您尚未在表数组中包含行或列标题，请在计数中减去它们。

图 3.10： 反向查找

反向查找多个结果

现在，让我们学习如何在数据中存在重复项时返回多个项目。

使用前一节中的示例，假设患者杰克·丹有两个预约。图 3.12展示了如何返回这两个预约：

=INDEX(Doctor&“ ”&session,

MATCH(TRUE,

INDEX(Patients,, LARGE(IF(Patients=$G$47,COLUMN(Patients)-COLUMN(Doctor)),ROW(A1)))=$G$47,0),

LARGE((Patients=$G$47)*COLUMN(Patients)-COLUMN(Doctor),ROW(A1)))

此函数的工作方式如下：

• 医生&“ ”&会话 返回一个包含每位医生和会话组合的自定义表。

• IF(Patients=$G$47, COLUMN(Patients)-COLUMN(Doctor)) 检查我们的条件患者是否在患者列表中，并返回这些条件为TRUE的列号，否则为FALSE（见图 3.11）：

  图 3.11： 检查列

• LARGE(IF(Patients=$G$47,COLUMN(Patients)-COLUMN(Doctor)),ROW(A1)) 返回最大的列号，即 2。我们使用ROW(A1)，返回 1，随着我们向下滚动公式，数字递增。

• INDEX(Patients,,LARGE(IF(Patients=$G$47,COLUMN(Patients)-COLUMN(Doctor)),ROW(A1))) 使用INDEX函数按列过滤数据（见图 3.12）：

  图 3.12： 逐列过滤

• MATCH(TRUE, INDEX(Patients,, LARGE(IF(Patients=$G$47, COLUMN(Patients)-COLUMN(Doctor)), ROW(A1)))=$G$47,0), 检查患者是否在过滤列中，并返回一个TRUE/FALSE数组。MATCH函数返回唯一TRUE值的相对位置，这形成了我们INDEX函数的行号。

• 现在我们有了行号，最后一部分是使用LARGE: LARGE(IF(Patients=$G$47,COLUMN(Patients)-COLUMN(Doctor)), ROW(A1))获取列号。

• 向下填充公式以返回所有预约。

• 请记住这是一个数组函数，因为我们创建了自定义医生和会话表。因此，如果您没有 Office 365 订阅，请在完成编写时记得单击Ctrl + Shift + Enter。

图 3.13： 反向查找多个项目

注意： 当所有预约都返回时，INDEX函数开始返回#NUM错误。

多条件查找

正如我们在前一节关于三向查找中所学到的，进行多条件查找的技巧是使用和号（&）将条件合并为一个（见图 3.14）：

=INDEX(tblSalesT20[Amount],

MATCH(E60&F60,tblSalesT20[Customer]&tblSalesT20[Sales Date],0))

图 3.14： 多条件查找

上述函数的执行如下：

• E60&F60 返回客户和日期的单个组合条件**“Carl Jackson40192.”**

• tblSalesT20[Customer]&tblSalesT20[Sales Date] 返回客户和日期的单个组合数组（见图 3.15）：

  图 3.15： 组合数组

• MATCH(E60&F60,tblSalesT20[Customer]&tblSalesT20[Sales Date],0)) MATCH函数返回组合条件在组合数组中的行号。

• 请记住这是一个数组函数，因为我们创建了自定义组合数组。因此，如果您没有 Office 365 订阅，请在完成编写时记得单击Ctrl + Shift + Enter。

返回多列

默认情况下，INDEX和MATCH组合返回单列。如果我们想要多列，必须修改MATCH函数，如图 3.16所示：

=INDEX(RegionSale21,

MATCH(F75,RegionSale21[Customer],0),

MATCH(G74:H74,RegionSale21[#Headers],0))

图 3.16： 多列

唯一的技巧是使用 MATCH 函数找到多个列的相对位置，MATCH(G74:H74,RegionSale21[#Headers],0))，通过突出显示两个条件(G74:H74)，MATCH函数返回一个列的数组({2,3})，这迫使INDEX函数返回两列。

这也是一个数组函数；因此，如果您没有 Office 365 订阅，请记住在编写完毕时点击Ctrl + Shift + Enter。

水平查找

与 VLOOKUP 函数不同，INDEX函数不限于垂直查找。如果提供正确的列或行号（参见图 3.17），返回数组可以存储在任何方向上：

=INDEX(Suppliers,,

MATCH( MIN(B91:D91),B91:D91,0))

图 3.17： 水平查找

这里唯一的技巧是使用 MIN 函数返回每件物品的最低报价。这个价格成为我们的查找值，用于MATCH函数。MATCH函数然后返回这个最低价格的相对位置。

注意： 由于供应商在单行数组中，我们可以跳过行号，如前面的公式所示。

查找非连续数组

如图 3.18所示，INDEX 函数有两种语法选项：

• 数组选项，期望单个或多个连续的数据数组。

• 参考选项，期望对单元格或非连续单元格范围的引用。以下是关于非连续范围的一些重要注意事项：

  • 范围必须用括号括起来，并用逗号分隔。

  • 范围必须在同一工作表上；否则，函数将导致#VALUE错误。

  • 范围可以是不同长度的，但必须包含引用的行或列号。超出范围的引用将导致#REF错误。

  • 范围在[area_num]参数中被选择的INDEX函数中。

图 3.18： INDEX 参考选项

在图 3.19中给出的示例显示了椅子和笔记本电脑的不同折扣。我们可以使用以下函数查找不同的表：

=INDEX((Chairs_Disc[Disc],Laptops_Disc[Disc]),

MATCH([@Amount],

CHOOSE(IF([@Product]=”Chairs”,1,2),Chairs_Disc[Amt],Laptops_Disc[Amt]),1),,

IF([@Product]=”Chairs”,1,2))

图 3.19： INDEX 非连续数组

前述函数的工作方式如下：

• ( Chairs_Disc[Disc], Laptops_Disc[Disc] )返回椅子和笔记本电脑的两个不连续的折扣列。

• IF([@Product]=”Chairs”,1,2), 如果表上的产品是椅子，则返回 1；否则返回 2。这是CHOOSE函数将确定以选择椅子或笔记本电脑表中的金额列的索引。

• MATCH([@Amount],CHOOSE(IF([@Product]=”Chairs”,1,2),Chairs_Disc[Amt],Laptops_Disc[Amt]),1) MATCH 函数返回折扣表金额列中金额的近似相对位置。

• 由于我们选择的是单列数组，因此可以忽略列索引参数并跳转到区域编号参数。

• IF([@Product]=”Chairs”,1,2), 如果表上的产品是椅子，则返回 1；否则，返回 2。这决定了选择椅子和笔记本电脑两个不连续的折扣列中的哪一个。

通配符查找

正如在上一章中所学到的，特别是在VLOOKUP部分查找部分中，INDEX-MATCH组合也可以使用两个流行的通配符字符（? *）。

例如，在图 3.20中，我们想要查找包含字母“P”的发票的金额：

图 3.20： 使用星号通配符字符的 INDEX

=INDEX(tblinvoicez[Amount],

MATCH(“*”&G117&”*”,tblinvoicez[Invoice ‘#],0))

在使用通配符字符时，请记住以下几点：

• 星号（*）：星号通配符表示文本字符串中的零个或多个字符。

• 问号（?）：问号通配符表示文本字符串中的单个字符。

• 您可以结合星号和问号来进一步定义您的搜索。

以下是前述函数的工作原理：

• “*”&G117&”*” 返回 P，这意味着字母P前后可能有许多字符。

• MATCH 函数返回第一张发票中包含字母P的相对行位置。

• INDEX 函数返回相同行号中的发票金额。

假设您想要查找第五个字符为字母“P”的发票号的值？

在这种情况下，我们将使用问号（?）作为通配符字符，表示任何单个字符（见图 3.21）：

图 3.21： 使用混合通配符字符的 INDEX

=INDEX(tblinvoicez[Amount],

MATCH(“????”&J117&”*”,tblinvoicez[Invoice ‘#],0))

以下是前述函数的工作原理：

• “????”&J117&”*” 返回????P*，这意味着字母“P”前可能有任意四个字符。因此，“P”是第五个字符，但它后面有多个字符。

• MATCH 函数返回第一张发票中发票号第五个字符为字母“P”的相对行位置。

• INDEX 函数返回相同行号中的发票金额。

基于文本长度的查找

在图 3.22中，我们假设所有发票的后订单都可以通过发票号的长度（即 8 个字符）来识别：

图 3.22： 基于长度查找文本

要查找这些后订单发票，我们使用以下公式：

=INDEX(Invoices[Amount],

MATCH( G132,LEN(Invoices[Invoice ‘#]),0))

以下是前述函数的工作原理：

• LEN(Invoices[Invoice ‘#]), the LEN 函数返回一个发票号长度的数组。这成为 MATCH 函数的查找数组参数。

• MATCH 函数返回第一张发票的相对行位置，其长度为 8 个字符。

• INDEX 函数返回相同行号的发票金额。

假设您有多张备货发票，如 图 3.23 所示。让我们学习如何借助 MAX 函数返回最后匹配项：

=INDEX(Invoices[Amount],

MAX(

(LEN(Invoices[Invoice ‘#])=G132)*ROW(Invoices[Invoice ‘#])

-ROW(Invoices[#Headers])

))

图 3.23： 查找最后匹配项

以下是前述函数的工作原理：

• LEN(Invoices[Invoice ‘#]) LEN 函数返回一个发票号长度的数组。

• LEN(Invoices[Invoice ‘#])=G132 检查哪张发票长度等于 8，并返回一个 TRUE/FALSE 数组。

• 通过将 TRUE/FALSE 数组乘以行号来获取行号： (LEN(Invoices[Invoice ‘#])=G132)*ROW(Invoices[Invoice ‘#])

• 通过减去标题行来调整行号计数。

• 要获取最后一行，请使用 MAX 函数。

• INDEX 函数返回 MAX 函数返回的最后匹配行号中的发票金额。

列表中的查找项目

当你理解 COUNTIF 函数时，在列表中查找项目相对容易。

在 图 3.24 中，我们想要查找第一个从我们这里购买的客户，使用单元格 G 中列表中的任何货币。

图 3.24： 列表中的查找项目

=INDEX(Sales_Currency[Customer],

MATCH(1,COUNTIF(List, Sales_Currency[Currency]),0))

以下是前述函数的工作原理：

• COUNTIF(List, Sales_Currency[Currency]) COUNTIF 函数返回一个 1/0 数组，其中 1 是货币在列表中找到的计数，否则为 0。这成为 MATCH 函数的查找数组参数。

• MATCH 函数返回第一张发票的相对行位置，计数为 1。

• INDEX 函数返回相同行号的客户。

如果我们想返回所有项目而不仅仅是第一个匹配项，我们必须修改我们的公式，如 图 3.25 所示：

=INDEX(

Sales_Currency[Customer],

LARGE(

COUNTIF(List, Sales_Currency[Currency])

*ROW(Sales_Currency[Currency])-ROW(Sales_Currency[#Headers]), ROW(A1))

)

以下是前述函数的工作原理：

• COUNTIF(List, Sales_Currency[Currency])

  *ROW(Sales_Currency[Currency]) COUNTIF 函数返回一个 1/0 数组，其中 1 是货币在列表中找到的计数，否则为 0。将此数组乘以行号以返回包含列表中项目的行号列表。记得通过减去标题行号来调整行号以从标题后开始计数。

• 我们需要遍历这个行号码列表，并逐个作为 INDEX 函数的行参数返回。我们使用 LARGE 函数来完成这个任务，它从最大到最小返回行号。注意 ROW(A1) 评估为 1，当你向下拖动函数时，它会增加直到完整列表被迭代。

• INDEX 函数使用 LARGE 函数返回的每行号码，并返回相应的客户。

• 请注意，当所有客户都返回后，INDEX 函数会返回 #VALUE 错误。

图 3.25: 在列表中查找多个项目

如果我们不希望公式返回错误，我们可以将其嵌套在 IFERRROR 函数中，如下所示。IFERROR 函数现在返回空白而不是错误：

=IFERROR(

INDEX(Sales_Currency[Customer],

LARGE(

COUNTIF(List,Sales_Currency[Currency])

*ROW(Sales_Currency[Currency])

-ROW(Sales_Currency[#Headers]),ROW(A1))),

“”)

查找唯一值

掌握 COUNTIF 函数是提升我们查找技能的重要一步。例如，如果你想在 Excel 中查找唯一值，你必须利用 COUNTIF 函数，如 图 3.26 所示：

=INDEX(Sales[Customer],

MATCH(TRUE, COUNTIF(Sales[Currency],Sales[Currency])=1,0))

以下是这个函数的工作原理：

• COUNTIF(Sales[Currency],Sales[Currency]) COUNTIF 函数返回列中每个货币的计数数组。由于我们正在寻找唯一值，我们检查哪个计数等于 1 ► COUNTIF(Sales[Currency],Sales[Currency])=1。这个比较返回一个 TRUE/FALSE 数组，其中 TRUE=唯一，FALSE=重复。

• MATCH 函数返回前述数组中第一个 TRUE 值的相对行位置。

• INDEX 函数返回相同行号的客户。

图 3.26: 查找唯一项目

查找底部 n 值

如果我们首先了解 IF 和 SMALL 函数，查找底部值就相对容易了。例如，在 图 3.27 中，我们查找三个销售额最低的客户：

图 3.27: 查找底部项目

=INDEX(BottomSale[Customer],

SMALL(

IF((BottomSale[Amt]<=SMALL(BottomSale[Amt],3)),

(BottomSale[Amt]<=SMALL(BottomSale[Amt],3))*ROW(BottomSale[Amt])-ROW(BottomSale[#Headers]),

“”),ROW(A1)))

以下是前述函数的工作原理：

• SMALL(BottomSale[Amt],3) SMALL 函数返回第三个最小的金额 — 28,600。下一步是检查哪个金额小于或等于这第三个最小的金额 ► (BottomSale[Amt]<=SMALL(BottomSale[Amt],3))。这个比较返回一个 TRUE/FALSE 数组。

• BottomSale[Amt]<=SMALL(BottomSale[Amt],3))*ROW(BottomSale[Amt]) ])-ROW(BottomSale[#Headers]) 要获得一系列行号，我们将TRUE/FALSE数组与金额的行号相乘。记得调整行号，从标题行号中减去以开始计数。

• 下一步是使用 IF 函数将前面数组中的负值替换为空白：IF((BottomSale[Amt]<=SMALL(BottomSale[Amt],3)), (BottomSale[Amt]<=SMALL(BottomSale[Amt],3))*ROW(BottomSale[Amt])-ROW(BottomSale[#Headers]), “”)

• 我们需要遍历这个行号列表，并逐个作为 INDEX 函数的行参数返回。为此任务，我们使用SMALL函数，它按从最小到最大的顺序返回行号。注意ROW(A1)评估为 1，当您向下拖动函数时，它会增加直到完整列表被迭代。

• INDEX函数使用SMALL函数返回的每个行号，并返回相应的客户。

• 请注意，当所有客户都被返回后，INDEX函数会返回“#NUM”错误。

正如我们在前一节中学到的，如果不希望 INDEX 函数返回错误，可以将其嵌套在IFERROR函数中，如下所示：

=IFERROR(

INDEX(BottomSale[Customer],

SMALL(

IF((BottomSale[Amt]<=SMALL(BottomSale[Amt],3)),

(BottomSale[Amt]<=SMALL(BottomSale[Amt],3))

*ROW(BottomSale[Amt])-ROW(BottomSale[#Headers]),

“”),ROW(A1))),

“”)

结论

本章提供了一个更好的查找方法，即VLOOKUP — INDEX/MATCH组合。

这种组合是更好的选择的主要原因有三点：(i) 它允许我们选择不仅是两列表，还可以是单列/行数组；(ii) 选择单列/行数组的能力使其灵活，不仅可以向左或向右查找，还可以垂直或水平查找；(iii) 由于它不默认进行近似匹配，这种组合更不容易出错。

对于没有 Office365 订阅的用户，INDEX/MATCH组合是最有效的查找方法。

在下一章中，我们将探讨为什么一些 Excel 用户仍然使用最古老的遗留查找函数之一 — LOOKUP 和 HLOOKUP。

需要记住的要点

• 与VLOOKUP函数类似，INDEX/MATCH组合默认返回第一个匹配的单个值。如果我们想要嵌套多个值，可以使用SMALL/LARGE函数。

• 与VLOOKUP函数不同，INDEX/MATCH函数的行数是基于所选数组的，而不是 ROW 函数返回的默认行数。因此，如果您的数据不是从最顶部行开始的，必须调整行数。

第四章

LOOKUP

介绍

在本章中，我们将讨论 LOOKUP 函数，这个函数从 Excel 的最早版本就存在。LOOKUP函数的长寿和持续存在于 Excel 中证明了它作为数据检索和电子表格分析基本工具的价值和广泛应用。

您可以在向量形式和数组形式中都使用LOOKUP函数。

在向量形式中，您在一个列/行中搜索一个项目，并从另一个列/行中的相同位置返回一个项目。

语法如下：

=LOOKUP (lookup_value, lookup_vector, [result_vector])

其中：

• lookup_value：您要查找的值。

• lookup_vector：包含要搜索的值的单列/行（注意：它应按升序排序）。

• result_vector：包含要返回的值的单列/行。

注意

• 如果要指定包含要搜索的值的列/行，则向量形式最为适用。

• 如果未找到查找值，则LOOKUP函数将匹配小于查找值的下一个最大值。

• 如果未找到查找值并且小于查找范围中的最小值，则LOOKUP函数将返回#N/A错误值。

• Lookup_vector和result_vector范围必须具有相同的大小。

在数组形式中，我们在表数组（行和列）的第一列/行中搜索项目，并返回表数组的最后一列/行中与之相同位置的值。

语法如下：

=LOOKUP (lookup_value, array)

其中：

• lookup_value: 您要查找的值。

• Array: 包含要搜索的值的列/行。

注意：

• 第一行/列中的值必须按升序排序。

• 搜索是水平还是垂直取决于行数与列数的比较。如果数组的行数多于列数，则LOOKUP将搜索第一列；否则，它将搜索第一行。

• 如果未找到查找值并且小于查找范围中的最小值，则LOOKUP函数将返回#N/A错误值。

• 如果未找到查找值，则LOOKUP函数将匹配小于查找值的下一个最大值。

结构

在本章中，我们将讨论应该使用LOOKUP函数的五种情况：

• 使用条件查找最后匹配项

• 查找最后一个空单元格

• 查找最后一个负数或文本

• 在数组中查找近似匹配项

• 查找最重复的项目

使用条件查找最后匹配项

LOOKUP函数的一个日常用途是检索最后匹配的值。在这个例子中，我们将使用向量形式。

我们正在尝试找到 Ms Abby 在图 4.1中提供的最后一个科目：

图 4.1： 在列中查找最后匹配项

=LOOKUP(2,1/(Lecturers=F3), Subjects)

以下是前述函数的工作原理：

• 2 代表一个我们确定不会在查找向量中找到的大值。

• Lecturers=F3 返回一个TRUE/FALSE数组，其中 TRUE 表示满足条件，即讲师为 Ms Abby；否则返回 FALSE（见图 4.2):

  图 4.2: 检查是否符合条件

• 1/(Lecturers=F3) 通过将 1 除以每个值，将 TRUE/FALSE 转换为数值等效值（见图 4.3):

  图 4.3: 将 TRUE/FALSE 转换为数值等效值

注意: 有四种方法将布尔值转换为数值等效值：

• 通过加上零：(Lecturers=F3)+0

• 通过乘以 1：*(Lecturers=F3)1

• 使用双重一元方法：–(Lecturers=F3)

• 通过除以 1：1/(Lecturers=F3)

• 我们应该选择最后一种方法，因为它是唯一不包含零在结果中的方法；在查找向量中包含零可能会破坏升序排列并导致函数返回意外结果或错误。

• 由于找不到 2，并且查找向量中最大的值是 1，LOOKUP 函数匹配数组中的最后一个 1，并返回结果向量中相同位置的值。

我们不仅限于使用单一条件。例如，在图 4.4中，我们想知道客户 Luke 购买椅子的最后日期：

图 4.4: 使用多个条件查找最后一个匹配项

=LOOKUP(2,

1/((tbl_Sales[Customer]=H16)*(tbl_Sales[Product]=H18)),

tbl_Sales[Date])

以下是前述函数的工作原理：

• 2 代表一个我们确定不会在查找向量中找到的大值。

• (tbl_Sales[Customer]=H16)*(tbl_Sales[Product]=H18) 返回一个由 1/0 组成的数组，其中 1 表示客户为 Luke 且产品为椅子的行位置。

• 1/((tbl_Sales[Customer]=H16)*(tbl_Sales[Product]=H18)) 我们将前述数组除以 1，以排除所有零。在查找向量中包含零可能会破坏升序排列并导致函数返回意外结果或错误。

• 由于找不到 2，并且查找向量中最大的值是 1，LOOKUP函数匹配数组中的最后一个 1，并返回结果向量中相同位置的值。

查找最后一个空单元格

查找数组中的最后一个空白与前述示例类似，唯一的区别在于逻辑测试。与前述示例类似，我们将使用LOOKUP函数的向量形式进行水平查找。

在图 4.5中，我们查找客户最后一次付款的月份，即最后一个非空月份：

图 4.5: 查找最后一个非空单元格

=LOOKUP(2, 1/(D33:H33<>””), $D$32:$H$32 )

以下是此函数的工作原理：

• 2 表示我们确定不会在查找向量中找到的一个大值。

• D33:H33<>”” 返回一个TRUE/FALSE数组，其中TRUE表示行中的非空单元格。

• 1/(D33:H33<>””) 我们将前面的数组除以 1，将布尔数组转换为其数值等效形式，并排除所有零。在查找向量中包含零可能会破坏升序并导致函数返回意外结果或错误。

• 由于找不到 2，并且查找向量中的最大值为 1，LOOKUP 函数匹配数组中的最后一个 1，并返回结果向量中相同位置的值，即$D$32:$H$32。

查找最后一个负数或文本

LOOKUP函数的向量形式是查找数组中任何最后一个值的最强大函数。我们需要学习的唯一技巧是为查找向量创建复杂的逻辑测试。

例如，在图 4.6中，我们想要查找最后一次出现负温度的日期：

图 4.6： 查找最后一个负数

=LOOKUP(2,1/(tblHarvest[Temp0]<0),tblHarvest[Date])

这个公式的工作方式与前面示例中的其他公式相同，除了逻辑测试。在这个示例中，tblHarvest[Temp0]<0 返回一个TRUE/FALSE数组，其中 TRUE 表示所有小于零的值。

要查找最后一个文本，如图 4.7所示，使用 ISTEXT 函数返回一个 TRUE/FALSE 值数组，其中 TRUE 表示文本函数。

函数的其他方面与之前解释的相同。

=LOOKUP(2,1/ISTEXT(D59:D74),D59:D74)

图 4.7： 查找最后一个文本

在数组中查找近似匹配

在我们之前的所有示例中，我们都使用了LOOKUP函数的向量形式。现在，让我们学习如何使用数组形式在数组中查找近似匹配。

例如，在图 4.8中，我们正在查找使用近似匹配从折扣表中获取折扣百分比。

图 4.8： 在数组中查找近似匹配

=LOOKUP(D78, Discounts)

这是这个函数的工作原理：

• 存储在列 D 中的销售值被用作查找值。

• LOOKUP 函数使用折扣表的第一列作为查找数组，并在结果向量的相同位置返回近似值。

• 请记住，表数组的第一列中的值必须按升序排序。

查找最重复的项目

这是使用LOOKUP函数的数组形式的另一个示例。在图 4.9中，我们正在寻找最频繁的客户：

图 4.9： 查找最重复的项目

=LOOKUP(MODE(MATCH(Customer[Name],Customer[Name],0)),

CHOOSE({1,2},MATCH(Customer[Name],Customer[Name],0),Customer[Name]))

以下是前述函数的工作原理：

• MATCH(Customer[Name], Customer[Name],0) 函数返回一个等于客户数量的数组，其中数组中的每个项目表示数据中客户姓名首次出现的位置（见图 4.10）。

  图 4.10： 客户的位置

• MODE(MATCH(Customer[Name], Customer[Name],0) 函数返回数组中重复次数最多的项目。

• 我们使用CHOOSE函数创建一个两列表格数组，其中第一列是客户的位置，第二列是客户的姓名（见图 4.11）。

  图 4.11： 两列表格数组

• LOOKUP函数使用两列表格的第一列作为查找数组，并返回第二列中与最后匹配值相同位置的客户姓名。

结论

在本章中，我们了解到LOOKUP函数在查找最后一个匹配项时是最佳选择。无论是使用向量形式还是数组形式，只要查找数组数据按升序排序，LOOKUP函数都可以用于垂直或水平查找数据。

由于其限制，仅建议用于近似匹配和查找最后一个匹配项。

在下一章中，我们将探讨其他可用于查找数据的函数，尽管它们不被归类为查找函数。

记住的要点

• Excel 的LOOKUP功能有限，因此不建议日常使用。作为替代方案，考虑使用VLOOKUP或INDEX/MATCH函数。

• LOOKUP函数没有精确匹配选项；它的所有用途默认为近似匹配。

多项选择题

1. 以下公式的结果是什么：

   =LOOKUP(0, {1,2,3,4,5}, {“A”,“B”,“C”,“D”,“E”})?

   1. A

   2. B

   3. C

   4. D

   5. E

   6. #N/A

2. 以下公式的结果是什么：

   =LOOKUP(“Z”, {“A”,“B”,“C”,“D”,“E”}, {1,2,3,4,5})?

   1. 1

   2. 2

   3. 3

   4. 4

   5. 5

   6. #N/A

3. 以下公式的结果是什么：

   =LOOKUP(5, {1,2,3,4,6}, {“A”,“B”,“C”,“D”,“E”})?

   1. A

   2. B

   3. C

   4. D

   5. E

   6. #N/A

4. LOOKUP 函数的向量形式和数组形式之间有什么区别？

   1. 向量形式在一列/行中搜索项目，并返回另一列/行中相同位置的项目，而数组形式在表格数组的第一列/行中搜索项目，并返回表格数组的最后一列/行中相同位置的值。

   2. 向量形式在表格数组的第一列/行中搜索项目，并返回表格数组的最后一列/行中相同位置的值，而数组形式在一列/行中搜索项目，并返回另一列/行中相同位置的项目。

   3. 向量形式搜索精确匹配，而数组形式搜索近似匹配。

   4. 向量形式搜索近似匹配，而数组形式搜索精确匹配。

答案

1. f

2. e

3. d

4. a

第五章

其他查找方法和函数

介绍

在本章中，我们将讨论可用于查找数据的替代方法和函数。

许多 Excel 用户在面对查找问题时只会使用查找函数，他们没有深入了解其他函数的工作原理的机会。

根据数据的大小或任务的复杂性，某些替代方法可能比已知的查找函数更有效或更快。

现在让我们探讨不同的方法，以帮助我们确定最有效的解决方案。

结构

在本章中，我们将讨论查找数据的六种独特替代方法和函数：

• 使用高级筛选来查找列表中的项目/不在列表中的项目

• 使用 Excel 交集运算符进行双向查找

• 使用数据库函数查找数值数据

• 使用SUMIFS、SUMPRODUCT、AGGREGATE和MAX/MAXIFS来查找数值数据

• 查找图片

• 查找项目的单元格地址

• 使用数据透视表查找列表中的唯一项目

使用高级筛选查找列表中的项目/不在列表中的项目

许多 Excel 用户在日常查找任务中必须使用高级筛选选项。与标准筛选不同，您只有在将鼠标悬停在其上时才能看到筛选标准，高级筛选选项对所有用户都是可见的。

图 5.1展示了使用高级筛选查找所有椅子购买的情况：

图 5.1: 使用高级筛选在表中查找项目

以下是要遵循的步骤：

1. 点击包含所有数据的表格的任意位置。

2. 转到数据选项卡，点击高级筛选选项。

3. 在高级筛选弹出屏幕上（见图 5.2）：

   1. 点击复制到另一个位置。

   2. 如果您使用的是表格并且已经按照前面的步骤操作，列表范围将会自动选择。

   3. 选择一个标准范围 — 包括标头和包含标准的单元格都应该被选中。由于高级筛选从标准范围:读取到列表范围:，所以标头应该是相同的。

   4. 在复制到字段中，选择一个单元格来复制筛选后的表格。

   5. 最后，点击确定。

   图 5.2: 选择列表中项目的高级筛选选项

要查找不在表中的项目，将标准改为包含“不等于”运算符（<>），然后按照前面的步骤操作。

图 5.3: 使用高级筛选查找列表中不在的项目

如果我们想查找唯一值，高级筛选也有这个选项。如图 5.4所示，假设我们想知道我们种植的独特作物：

图 5.4: 使用高级筛选查找列表中的唯一项目

注意： 在唯一记录过滤中，我们跳过条件范围，而是选择仅唯一记录选项。

所有其他步骤，如上所述，均适用。

使用 Excel 交集运算符进行双向查找

Excel 交集运算符是解决双向查找问题的最少为人知但理想的方法之一，即查找两个命名范围的交叉值。

我们只需要记住，在这些范围之间加入一个空格字符就成为了交集运算符。

在图 5.5中，交集运算符用于返回由客户 Joshua 在西部地区进行的销售：

图 5.5： 使用 Excel 交集运算进行双向查找

以下是要遵循的步骤：

1. 单击包含所有数据的表格的任意位置。

2. 转到公式选项卡，在定义名称下，点击从选择创建。

3. 从选择创建名称弹出窗口将出现，如图 5.6所示。选择从顶部行和左侧列的值创建名称。

   图 5.6： 创建用于交集的名称

4. 创建名称后，您可以使用交集运算符。输入行标题（Joshua）和列标题（Western），它们之间用空格分隔，这就是交集运算符，如图 5.7所示：

   图 5.7： 使用交集运算

交集运算不仅限于单个条件操作；我们可以用它来处理多个条件，如图 5.8所示。我们想知道 Joshua 在西部和南部地区购买的总金额：

=SUM(Joshua Western:Southern)

图 5.8： 使用多个条件进行交集运算的双向查找

公式的工作原理：

我们将按照相同的步骤创建命名范围。但是，对于这个解决方案，我们将选择两个由冒号分隔的区域。

这将返回两个区域的金额，如图 5.9所示。

最后，将金额相加以获得总额。

图 5.9： 多条件结果

我们还可以查找非相邻列的总值，如图 5.10所示。

在这个例子中，我们想知道 Joshua 在东部或南部地区的总值。

唯一需要记住的技巧是使用加号（+）作为 OR 逻辑的替代。

=Joshua Eastern + Joshua Southern

图 5.10： 多个 OR 条件结果

注意： 交集运算符适用于单元格范围引用和命名范围。

您需要记住的是在这些范围之间包含一个空格。

使用数据库函数查找数字数据

自 Excel 2007 以来，数据库函数一直存在，然而许多用户并不了解它们查找数字数据的能力和潜力。

数据库函数对满足指定条件的记录执行特定计算。

语法如下：

= DSUM(Database, Field, Criteria)

其中：

1. 数据库必须是一个数据范围，其中每一行被视为一条记录，每一列被视为一个字段，顶部行包含字段的标识符。

2. 字段是包含要查找数据的列。

3. 条件是确定要查找的记录的范围或一组条件。它必须包含列标题，并且标准必须对应于数据库中的字段名称。

例如，在图 5.11中，我们要查找 3 月销售的钢笔数量：

图 5.11： 多个 OR 条件结果

=DSUM (B65:E78, G66, G67:G68)

其中：

1. 数据库：包括标题的所有数据范围 ►B65:E78。

2. 字段：包含查找数据的列标题 ► 存储在单元格 G66 中的 Mar。

3. 条件：包含列标题和作为条件的字段值的范围。有关更多信息，请参见图 5.12。

图 5.12： 数据库函数中的参数

数据库函数不仅限于简单条件，如前面的示例所示；您可以创建复杂条件，如图 5.13所示：

图 5.13： 数据库函数中的复杂条件

=DSUM(tbl_Orders[#All],”Qty”,F81:I82)

该公式返回大于 6 月 1 日且小于 6 月 30 日的日期的产品椅子的订单，并且数量大于 20。

公式的工作原理如下：

1. 数据库：名为tbl_Orders的表中的所有数据。

2. 字段：其标题为Qty的列。

3. 条件：包含在单元格范围 F81:182 中的所有信息。

注意：条件区域不得包含任何空白行或列。