srcmini有时, 数据科学与深度学习, 大数据或推荐系统无关。有时, 尤其是如果你受雇于像金融这样的行业, 或者过去曾严重依赖Excel的公司中工作, 则很有可能你将不得不大量处理Excel电子表格。我承认自己属于后一个例子。我的雇主非常依赖Excel, 因此, 在我的一些工作职责中, 我不得不学习很多关于Excel的各种功能。
尽管R和Python在许多方面都比Excel有明显的优势, 但是Excel仍然是某些任务(如创建业务报告)的强大工具。在本教程中, 我将重点介绍Excel的内置函数之一, 该函数在同时使用各种Excel表时将大有帮助。
我指的功能是VLOOKUP。 VLOOKUP允许用户关联同一工作表中的不同图纸或同一工作簿中的不同图纸之间的关系。从本质上讲, 它将使用户能够从一列中查找值并从另一列中返回其对应的值。如果你有未排序的大型数据表, 并且需要基于某个列(理想情况下包含唯一键)将它们关联, 则此功能将非常有用。
让我说明使用玩具数据表的同一工作表中各列之间使用VLOOKUP函数的过程如何工作：
在上面的屏幕快照中, 你可以看到一个通用的VLOOKUP表达式, 就像编写业务报告一样。其组成如下：
- IFERROR函数：如果键为空或数据表中不存在键, 则用连字符(-)代替错误。发生错误时, 这只是错误处理的一种好习惯, 它可以使工作表更整洁, 但是, 使VLOOKUP正常工作并不是必需的。
- VLOOKUP参数：
- E2是查找值(在这种情况下, 我们以KOG为例, 但可以是任何键)。此值必须位于要搜索的所选表范围的最左列。
- B1：C18是要查找值的表范围/数组。如图所示, 最左边的列的索引为1。该列也必须是包含要使用的功能的查找值的列。
- 2是要从中检索值的所选表范围的列索引号。在此示例中, 我们要检索描述, 因此选择表数组的第2列。
- FALSE是一个标志, 指示你希望查找值完全匹配。在这种情况下, 我们选择FALSE是因为你想要KOG键的精确匹配。另一个选项是选择TRUE, 这将查找查找值的近似匹配项, 如果你未指定此参数, 则默认为。如果你要匹配键或字符串, 通常不建议选择TRUE, 因为它会产生奇怪的结果。
现在, 在编写该VLOOKUP语句后, 如果在Excel中按ENTER键, 将得到以下结果：
如你所见, VLOOKUP函数返回的正是”刚铎之王”, 这是与KOG键相关的描述。对于你现在在单元格E2中输入的任何键都是如此。例如, 让我们看看如果输入键KIN会发生什么。
北方之王！是的, 如果我们输入希望得到的KIN键, 因为它是对我们亲爱的Jon(Snow)的描述。
但是, VLOOKUP具有比根据键检索单个值更多的用途。让我们假设, 例如, 我们将该表分为两张, 每张放在同一工作簿的另一张纸上。
联接表
如你所见, 现在将数据分为两个不同的工作表(S1和S2), 并且第二个工作表(S2)上的表上的键以不同的顺序排列。对我们来说幸运的是, 关键列对于两个工作表都是相同的, 而不管顺序如何, 因此我们可以使用VLOOKUP在S1中将数据连接在一起(你可以将其视为单列左连接)。实现此目的的语法如下：
这次, 第一个参数(查找值)变为B2, 因为我们要匹配S2上表中的键B2。该表范围现在成为S2中的整个数据表, 其中第一列包含公用键。你可能会注意到表格范围内嵌入了美元符号。这是通过在获取范围后按F4来完成的, 它可以通过以下方式锁定范围：当你在S1中向下拖动公式时, VLOOKUP函数将继续查看原始范围而不会向下移动。由于我们需要描述, 因此列索引号始终为2, 这是S2中的第二列。最后, 我们想要的最后一个参数为FALSE, 因为我们想要键的完全匹配。
最后一步是将公式向下拖动到表的最后一个条目。 S1中的结果表如下：
瞧！就像这样, 我们使用VLOOKUP将数据缝合在一起。在业务设置中, 像这样的联接表是很常见的事情, 而SQL, R或Python也不是高级主管使用的工具。例如, 你可能有两个文件, 其中包含有关合格销售机会的不同详细信息, 可以使用月度销售Excel报表的唯一机会ID关联相关的合格销售机会(在我的情况下为Excel用法的最佳示例)。
使用近似匹配
到目前为止, 你可能已经注意到, 在我的任何VLOOKUP函数中, 我们都忽略了使用TRUE作为最后一个参数(也称为近似匹配)。这是由于我们专注于精确匹配唯一键这一事实。但是, 在处理数值数据时, 可以安全地将TRUE用于VLOOKUP中的最后一个参数。
例如, 让我们假装我们卖鱼, 当客户有一定数量时, 我们会提供以下折扣(如果有人问为什么吃鱼, 那很简单。鱼真棒！)。
这次在单元格F2中的VLOOKUP函数是：
有了该公式后, 折扣将变得最接近表中的阈值。例如, 如果我们购买25条鱼, 则折扣为5％, 如果我们购买70条鱼, 则折扣为10％, 如果我们购买120条鱼, 则折扣为15％。所有这些示例可以在下面看到：
INDEX-MATCH简介
VLOOKUP是关联不同列的强大功能。但是, 还有另一种方法可以做到这一点。 INDEX-MATCH组合。与单个函数VLOOKUP不同, INDEX-MATCH是两个函数的组合, 尽管编写起来可能有些复杂, 但它比标准VLOOKUP具有多个优点。
就本教程而言, 我们将看一个简单的示例, 在该示例中, 我们使用INDEX-MATCH基于公共键将两个表连接在一起, 就像我们之前对VLOOKUP所做的那样, 并说明了语法(请参见下文)。
INDEX-MATCH组合的组件如下：
- 索引参数：
- M2：M18：这是你要检索其值的列范围, 在这种情况下, 我们需要描述列, 因此我们选择该范围。请注意, 我们在选择范围以将其锁定后使用了F4, 因此在拖动公式时它不会移动。
- MATCH参数：
- B2：这是我们要查找的关键。它等于VLOOKUP中的查找值。
- L2：L18：这是包含查找值必须位于的键的列范围。请注意, 我们在这里也使用了F4。
- 0：这与VLOOKUP中的FALSE相同。这是完全匹配的标志。
查看语法, 已经可以实现INDEX-MATCH优于VLOOKUP的主要优势之一。使用INDEX-MATCH, 我们可以获得列抗扰性。也就是说, 如果现在我们决定在上面的右表中的”键”和”描述”之间添加一个附加列, 则没关系, 因为该公式将调整为仍引用”描述”列。你可以在下面看到此内容：
使用VLOOKUP, 这不可能不返回公式, 因为它具有静态列引用。换句话说, 在上面的示例中, 最初, 你将把2作为列索引号来检索Description。但是, 在添加了Fish列之后, VLOOKUP函数现在将在Fish列中返回该值, 因为该值现在是表范围中的第2列。你必须手动转到VLOOKUP函数, 然后将列索引号更改为3, 函数才能再次正常工作。
使用显示的内容, 使用INDEX-MATCH将描述与左侧表中的名称连接在一起, 与使用VLOOKUP的过程相同。我们只需将公式向下拖动, 如下所示：
请注意, 在同一工作簿中的不同工作表之间连接表只是相同的过程。唯一的区别是, 你将必须选择包含公用键的列范围以及要从第二张工作表中检索其值的列范围。
总结
希望你喜欢本教程。这些功能, VLOOKUP和INDEX-MATCH, 对于在业务环境中使用Excel的任何人都非常重要。我鼓励你深入探讨INDEX-MATCH, 因为它具有许多巧妙的功能, 例如区分大小写的匹配(是的, 默认情况下INDEX-MATCH和VLOOKUP不区分大小写)或基于多列的匹配。保持学习;天空才是极限！
如果你想了解有关VLOOKUP的更多信息, 请参加srcmini的电子表格数据分析课程。
评论前必须登录！
注册