有关计算机网站建设的论文,湛江制作网站公司,网站 视觉上,服务器放网站原文#xff1a;Ultimate ChatGPT Handbook for Enterprises 译者#xff1a;飞龙 协议#xff1a;CC BY-NC-SA 4.0 序言
在不断发展的数据管理和分析领域中#xff0c;掌握 Excel 的查找功能不仅是一种技能#xff0c;更是高效数据处理的基石。《使用 Power Query 和 Ch… 原文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 论坛对于两个原因至关重要 这让你可以接触在线学生从而练习你的教学。正如前一节所讨论的教学对于掌握 Excel 至关重要。 这为被动和无意识学习提供了绝佳的机会。在这些论坛中你会遇到许多课程、技巧和窍门这些你可能从未想到过。
一些建议的论坛包括 Excel 微软答案、Excel 微软技术社区、Excel Stack Overflow 和 Reddit Excel。
步骤 7每日有意识学习
有意学习与自发学习不同因为有目的的学习是有意识地为了特定目的获取知识。
例如这本书的每一章都涵盖了在 Excel 中查找数据的不同方法。如果你每天设定一个特定的时间来学习每一章你就会进行有意识的学习。
这涉及设定明确的目标积极寻找信息并进行刻意练习以提高自己的能力。这需要愿意在学习过程中投入时间和精力。
每日有意识学习提高了学习者教授他人的信心从而导致更深入的理解。用户进入学习教授和教授学习的生产力循环 图 1.1 生产力循环
这并不是贬低偶然学习在进行其他活动时无意中学到的知识或意外学习偶然学到的知识的好处的尝试。这些方式也能带来好处但有意学习的好处胜过其他所有形式的学习。
介绍 Excel 公式和函数
Excel 公式和函数是 Excel 的核心。您必须学习公式和函数才能充分利用 Excel 并提高生产力。
在 Excel 中公式结合了用于计算、数据操作或从工作表数据生成结果的操作。它可能包含单元格引用、算术运算符、数字或函数。
例如在图 1.2中如何使用公式计算每张发票的总付款 图 1.2 在 Excel 中使用公式
图中使用的公式包含算术运算符()和单元格引用(C3D3E3F3G3)。我们将在本章后面讨论更多单元格引用。
另一方面函数是预定义的一组指令或公式已经内置可用于对数据执行各种操作。
例如在图 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, J350000 ),
“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(J3M7, N7, IF(J3M6,N6,IF(J3M5,N5,IF(J3M4,N4,$N3))))
VLOOKUP(J3, tblGrades,2)
由于前述限制关于VLOOKUP能做什么或不能做什么有许多神话。
在下一节中我们将探讨VLOOKUP函数与其他函数嵌套时的全部潜力。
打破 VLOOKUP 的神话
神话是特定文化中的人们长期以来一直在讲述的故事。这些故事通常主要涉及根据历史教导事物为何是其现在的样子。这些有时是半真半假或纯粹的谎言。
关于VLOOKUP函数如何运作的许多教导都被半真半假或纯粹的谎言所掩盖给VLOOKUP函数带来了不好的名声。
在本节中我们将通过逐一打破现有的神话来了解VLOOKUP的全部真相。
神话 1VLOOKUP 无法进行左查找
在图 2.4中查找值销售日期不在表的第一列而是在那里有返回值客户。
在这种情况下默认的VLOOKUP函数将无法工作。您必须嵌套 IF 函数如下图所示 图 2.4 VLOOKUP 左查找
关键是使用IF函数创建自定义表数组。
IF函数返回一个两列表销售日期是第一列客户是第二列。
这种列重新排列技巧使VLOOKUP在假定向右查找的同时实际上向左查找。
神话 2VLOOKUP 无法在查找中返回多列
在图 2.5中您必须返回特定客户的销售日期和金额。
默认情况下您一次只能返回一个值因为 VLOOKUP 中的column_index参数只接受单个值。
然而您可以将多个column_index值放在花括号中并返回多个列。
如果您没有 Office 365 订阅请执行以下步骤 高亮显示要返回多个值的单元格。 转到公式栏输入以下公式。 点击Ctrl Shift Enter返回以下值 图 2.5 VLOOKUP 返回多列
神话 3VLOOKUP 无法在查找中使用多个条件
如果您的数据有重复项如图 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( T19U19,
IF({1,0}, tblSalesT[Customer]tblSalesT[Sales Date], tblSalesT[Amount]), 2, 0 ) 图 2.6 VLOOKUP 多条件
神话 4VLOOKUP 无法处理查找范围中的列插入和删除
到目前为止我们一直在函数中硬编码列索引号即包含返回值的列的编号。
当在此列之前插入或删除列时这会导致问题因为它会破坏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 处理插入和删除
神话 5VLOOKUP 无法进行双向查找
在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 双向查找
神话 6VLOOKUP 无法进行部分匹配查找
VLOOKUP 只有近似匹配和精确匹配但也可以进行部分匹配。
如果将查找值与通配符结合起来就可以进行部分匹配如图 2.9所示。
注意 通配符是表示文本字符串中一个或多个字符的特殊字符。最常见的通配符是星号*和问号?。 星号*此通配符表示文本字符串中一个或多个字符。例如“P***”**将找到以字母 P 开头的任何单词*P将找到以字母 P 结尾的任何单词*P*将找到包含字母 P 的任何单词。 问号?问号通配符表示文本字符串中的单个字符。例如搜索词H??t将找到任何以“H”开头以“t”结尾并在中间有两个字符的四字词例如“Heat”或“Host”。 您可以结合星号和问号来定义您的搜索。例如???T* 将搜索 T 为第四个字符但以任意数量的字符结尾的任何字符串例如 MastsCoatingSoothing 等。另一个例子是*****T??? 这将搜索 T 为倒数第四个字符但以任意数量的字符开头的任何字符串例如 BeatingCharactersAssisting 等。 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”无论其是大写还是小写。
神话 7VLOOKUP 无法进行区分大小写的部分匹配查找
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 部分区分大小写匹配
迷思 8VLOOKUP 无法进行区分大小写查找
正如我们从前一节中学到的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。
迷思 9VLOOKUP 无法返回多个结果
默认情况下VLOOKUP 返回第一个 TRUE 值。因此如果您有重复项目并且想要返回所有 TRUE 值您必须像 图 2.13 中所示嵌套 SMALL 和 IF 函数。
VLOOKUP(
SMALL(IF($F$70tbl[Customer],
ROW(tbl[Customer])), ROW(A1)),
IF({1,0},ROW(tbl[Customer]),tbl[Amount]),
2,0)
以下是前述函数的工作方式 IF($F$70tbl[Customer], ROW(tbl[Customer]) 检查我们的标准客户“Carl Jackson”是否在客户列表中并且如果为真则返回找到客户的行号。由于我们有重复的客户此函数将返回多个行号。 我们需要遍历这个行号列表并逐个作为VLOOKUP的查找值返回。我们使用SMALL函数来完成这个任务它会从最小到最大返回行号。请注意ROW(A1)的计算结果为 1当您拖动函数时它会逐渐增加直到完整列表被迭代
SMALL(IF($F$70tbl[Customer],ROW(tbl[Customer])), ROW(A1)) 使用 IF 函数创建一个自定义的两列表数组。第一列包含客户的行号查找值列第二列包含发票金额返回值列。 VLOOKUP 函数使用SMALL函数返回的每个行号并返回相应的金额。 请注意在所有金额都被返回后VLOOKUP函数会返回#NUM错误。 由于这是一个数组函数如果您没有 Office 365 订阅请点击Ctrl Shift Enter。 图 2.13 VLOOKUP 多个结果
神话 10VLOOKUP 无法从后往前查找
默认情况下VLOOKUP从上到下查找值并返回第一个 TRUE 值。
为了从最后到第一个搜索我们将LARGE函数嵌套在VLOOKUP中。
使用上一章的示例要从后往前搜索我们只需要用LARGE函数替换SMALL函数如图 2.14所示 图 2.14 VLOOKUP 多个结果
以下是上述函数的工作原理 IF($F$70tbl[Customer], ROW(tbl[Customer])检查我们的标准客户“卡尔·杰克逊”是否在客户列表中如果是则返回客户被发现的行号。由于我们有重复的客户此函数将返回多个行号。 我们需要遍历这个行号列表并逐个作为VLOOKUP的查找值返回。我们使用LARGE函数来完成这个任务它会从最大到最小返回行号。请注意ROW(A1)的计算结果为 1当您拖动函数时它会逐渐增加直到完整列表被迭代。 使用 IF 函数创建一个自定义的两列表数组。第一列包含客户的行号查找值列第二列包含发票金额返回值列。 VLOOKUP 函数使用 LARGE 函数返回的每个行号并返回相应的金额。 请注意在所有金额都被返回后VLOOKUP函数会返回#NUM错误。 由于这是一个数组函数如果您没有 Office 365 订阅请点击Ctrl Shift Enter。
神话 11VLOOKUP 无法查找前 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。
神话 12VLOOKUP 无法进行反向查找
在 Excel 中反向查找是根据已知结果在表中查找值的一种方法。这是前一节讨论的双向查找的反向见神话 5。
例如如图 2.16所示您正在寻找给患者的医生和相应的会话。 **图 2.16**VLOOKUP 反向查找
VLOOKUP(
MAX(IF(ClientsF86,ROW(Clients))),
CHOOSE({1,2,3},
ROW(Clients),
Doctors”-Morning”,
Doctors”-Afternoon”),
MAX(IF(ClientsF86, COLUMN(Clients)-COLUMN(Doctors)1)),0)
在开始学习公式如何工作之前让我们了解一下我们使用的命名范围 客户 C86:D95 医生 B86:B95
现在前述函数的执行方式如下 IF(ClientsF86, ROW(Clients))检查单元格 F86 中的客户是否在命名范围中的客户数组中。假设没有重复项IF 函数将返回客户的行号否则返回 FALSE 值见图 2.17 **图 2.17**返回客户行 MAX(IF(ClientsF86, ROW(Clients))):MAX 函数忽略FALSE值并返回行号。这成为VLOOKUP函数的查找值。 CHOOSE({1,2,3}, ROW(Clients), Doctors”-Morning”, Doctors”-Afternoon”): CHOOSE创建了一个表数组用于VLOOKUP函数其中客户行号作为查找值列将医生与会话组合为返回值列。 图 2.18 VLOOKUP 自定义表数组 MAX(IF(ClientsF86, COLUMN(Clients)-COLUMN(Doctors)1)) 检查 F86 单元格中的客户是否在命名范围中的客户数组中并返回客户的列号否则返回 FALSE 值。请注意我们必须通过减去医生的列数并加 1 来调整列计数。 使用前述输入VLOOKUP函数可以进行反向查找。
神话 13VLOOKUP 无法进行水平查找
在 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函数快速返回了报价最少的代表。
神话 14VLOOKUP 无法返回多个非连续列
在前一节中我们讨论了神话 2即如何返回多个连续的列。现在让我们进一步讨论如何返回多个非连续的列。
在我们的示例中图 2.22我们必须返回金额和地区列的数据 图 2.22 VLOOKUP 非连续列
VLOOKUP(G113, tblTransact,{4,2},0)
我们必须记住的唯一技巧是将所需的列号放在花括号中。此外如果您没有 Office 365 订阅首先突出显示列转到公式栏编写上述公式最后单击 Ctrl Shift Enter。
如果您希望以行方式返回值则唯一要记住的技巧是在列号之间使用分号;而不是逗号,作为分隔符见图 2.23 图 2.23 VLOOKUP 逐行查找非连续列
神话 15VLOOKUP 无法查找多个非连续数组
动态查找非连续表需要更多的函数嵌套如图 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(G18H18,RegionSales[Product]RegionSales[Customer],0),
MATCH(G20,RegionSales[#Headers],0)) 图 3.7: 动态三向查找
前面的函数执行如下 G18H18返回一个单一的组合条件“ChairsRuby”。 RegionSales[Product]RegionSales[Customer]连接两列并返回一个产品和客户的单向数组我们可以用它来查找组合条件见图 3.8
提示创建单向数组时要遵循与创建组合条件相同的顺序。
在我们的示例中组合条件是Product 和 Customer的连接ChairsRuby这就是我们在创建单一数组时遇到相同顺序的原因。 MATCH(G18H18, RegionSales[Product]RegionSales[Customer],0)返回包含组合条件的自定义单向数组中的行号。 MATCH(G20, RegionSales[#Headers],0)) 返回包含月份条件的表头中的列号。 请记住这是一个数组函数因为我们正在进行条件和列的连接。因此如果您没有 Office 365 订阅请在完成编写时记得单击Ctrl Shift Enter。 图 3.8 单向数组
反向查找单个结果
反向查找是之前讨论的双向查找函数的相反。您从交叉点的数据开始并返回行和列标题。
例如在图 3.10中我们知道考试但我们必须查找导师和大厅
INDEX(Tutors“-”Halls,
SUM((ExamsE33)*ROW(Exams))-ROW(Halls),
SUM((ExamsE33)*COLUMN(Exams))-COLUMN(Tutors))
以下是前述函数的工作原理 导师“-”大厅 返回一个包含每位导师和大厅组合的自定义表。 (ExamsE33)*ROW(Exams) 检查我们的条件考试是否在考试列表中并返回一个TRUE / FALSE数组当乘以考试行时在FALSE处返回 0在TRUE处返回行号见图 3.9。 我们对数组进行求和以从前一步得到单个行号。由于INDEX从标题开始计算行号因此我们需要减去标题的行号SUM((ExamsE33) * ROW(Exams)) - ROW(Halls) 重复之前提到的类似步骤以获取列号SUM((ExamsE33) * 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(E60F60,tblSalesT20[Customer]tblSalesT20[Sales Date],0)) 图 3.14 多条件查找
上述函数的执行如下 E60F60 返回客户和日期的单个组合条件**“Carl Jackson40192.”** tblSalesT20[Customer]tblSalesT20[Sales Date] 返回客户和日期的单个组合数组见图 3.15 图 3.15 组合数组 MATCH(E60F60,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/(LecturersF3), Subjects)
以下是前述函数的工作原理 2 代表一个我们确定不会在查找向量中找到的大值。 LecturersF3 返回一个TRUE/FALSE数组其中 TRUE 表示满足条件即讲师为 Ms Abby否则返回 FALSE见图 4.2): 图 4.2: 检查是否符合条件 1/(LecturersF3) 通过将 1 除以每个值将 TRUE/FALSE 转换为数值等效值见图 4.3): 图 4.3: 将 TRUE/FALSE 转换为数值等效值
注意: 有四种方法将布尔值转换为数值等效值 通过加上零(LecturersF3)0 通过乘以 1*(LecturersF3)1 使用双重一元方法–(LecturersF3) 通过除以 11/(LecturersF3) 我们应该选择最后一种方法因为它是唯一不包含零在结果中的方法在查找向量中包含零可能会破坏升序排列并导致函数返回意外结果或错误。 由于找不到 2并且查找向量中最大的值是 1LOOKUP 函数匹配数组中的最后一个 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并且查找向量中最大的值是 1LOOKUP函数匹配数组中的最后一个 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并且查找向量中的最大值为 1LOOKUP 函数匹配数组中的最后一个 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函数没有精确匹配选项它的所有用途默认为近似匹配。
多项选择题 以下公式的结果是什么 LOOKUP(0, {1,2,3,4,5}, {“A”,“B”,“C”,“D”,“E”})? A B C D E #N/A 以下公式的结果是什么 LOOKUP(“Z”, {“A”,“B”,“C”,“D”,“E”}, {1,2,3,4,5})? 1 2 3 4 5 #N/A 以下公式的结果是什么 LOOKUP(5, {1,2,3,4,6}, {“A”,“B”,“C”,“D”,“E”})? A B C D E #N/A LOOKUP 函数的向量形式和数组形式之间有什么区别 向量形式在一列/行中搜索项目并返回另一列/行中相同位置的项目而数组形式在表格数组的第一列/行中搜索项目并返回表格数组的最后一列/行中相同位置的值。 向量形式在表格数组的第一列/行中搜索项目并返回表格数组的最后一列/行中相同位置的值而数组形式在一列/行中搜索项目并返回另一列/行中相同位置的项目。 向量形式搜索精确匹配而数组形式搜索近似匹配。 向量形式搜索近似匹配而数组形式搜索精确匹配。
答案 f e d a
第五章
其他查找方法和函数
介绍
在本章中我们将讨论可用于查找数据的替代方法和函数。
许多 Excel 用户在面对查找问题时只会使用查找函数他们没有深入了解其他函数的工作原理的机会。
根据数据的大小或任务的复杂性某些替代方法可能比已知的查找函数更有效或更快。
现在让我们探讨不同的方法以帮助我们确定最有效的解决方案。
结构
在本章中我们将讨论查找数据的六种独特替代方法和函数 使用高级筛选来查找列表中的项目/不在列表中的项目 使用 Excel 交集运算符进行双向查找 使用数据库函数查找数值数据 使用SUMIFS、SUMPRODUCT、AGGREGATE和MAX/MAXIFS来查找数值数据 查找图片 查找项目的单元格地址 使用数据透视表查找列表中的唯一项目
使用高级筛选查找列表中的项目/不在列表中的项目
许多 Excel 用户在日常查找任务中必须使用高级筛选选项。与标准筛选不同您只有在将鼠标悬停在其上时才能看到筛选标准高级筛选选项对所有用户都是可见的。
图 5.1展示了使用高级筛选查找所有椅子购买的情况 图 5.1: 使用高级筛选在表中查找项目
以下是要遵循的步骤 点击包含所有数据的表格的任意位置。 转到数据选项卡点击高级筛选选项。 在高级筛选弹出屏幕上见图 5.2 点击复制到另一个位置。 如果您使用的是表格并且已经按照前面的步骤操作列表范围将会自动选择。 选择一个标准范围 — 包括标头和包含标准的单元格都应该被选中。由于高级筛选从标准范围:读取到列表范围:所以标头应该是相同的。 在复制到字段中选择一个单元格来复制筛选后的表格。 最后点击确定。 图 5.2: 选择列表中项目的高级筛选选项
要查找不在表中的项目将标准改为包含“不等于”运算符然后按照前面的步骤操作。 图 5.3: 使用高级筛选查找列表中不在的项目
如果我们想查找唯一值高级筛选也有这个选项。如图 5.4所示假设我们想知道我们种植的独特作物 图 5.4: 使用高级筛选查找列表中的唯一项目
注意 在唯一记录过滤中我们跳过条件范围而是选择仅唯一记录选项。
所有其他步骤如上所述均适用。
使用 Excel 交集运算符进行双向查找
Excel 交集运算符是解决双向查找问题的最少为人知但理想的方法之一即查找两个命名范围的交叉值。
我们只需要记住在这些范围之间加入一个空格字符就成为了交集运算符。
在图 5.5中交集运算符用于返回由客户 Joshua 在西部地区进行的销售 图 5.5 使用 Excel 交集运算进行双向查找
以下是要遵循的步骤 单击包含所有数据的表格的任意位置。 转到公式选项卡在定义名称下点击从选择创建。 从选择创建名称弹出窗口将出现如图 5.6所示。选择从顶部行和左侧列的值创建名称。 图 5.6 创建用于交集的名称 创建名称后您可以使用交集运算符。输入行标题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)
其中 数据库必须是一个数据范围其中每一行被视为一条记录每一列被视为一个字段顶部行包含字段的标识符。 字段是包含要查找数据的列。 条件是确定要查找的记录的范围或一组条件。它必须包含列标题并且标准必须对应于数据库中的字段名称。
例如在图 5.11中我们要查找 3 月销售的钢笔数量 图 5.11 多个 OR 条件结果
DSUM (B65:E78, G66, G67:G68)
其中 数据库包括标题的所有数据范围 ►B65:E78。 字段包含查找数据的列标题 ► 存储在单元格 G66 中的 Mar。 条件包含列标题和作为条件的字段值的范围。有关更多信息请参见图 5.12。 图 5.12 数据库函数中的参数
数据库函数不仅限于简单条件如前面的示例所示您可以创建复杂条件如图 5.13所示 图 5.13 数据库函数中的复杂条件
DSUM(tbl_Orders[#All],”Qty”,F81:I82)
该公式返回大于 6 月 1 日且小于 6 月 30 日的日期的产品椅子的订单并且数量大于 20。
公式的工作原理如下 数据库名为tbl_Orders的表中的所有数据。 字段其标题为Qty的列。 条件包含在单元格范围 F81:182 中的所有信息。
注意条件区域不得包含任何空白行或列。
