VLOOKUP是Excel最有用的函数之一,也是最难理解的函数之一。在本文中,我们通过一个真实的例子来揭开VLOOKUP的神秘面纱。我们将为一个虚构的公司创建一个可用的**模板。
VLOOKUP是一个Excel函数。本文假设读者已经对Excel函数有了初步的了解,并且可以使用SUM、AVERAGE和TODAY等基本函数。在最常见的用法中,VLOOKUP是一个数据库函数,这意味着它可以处理数据库表,或者更简单地说,是Excel工作表中的列表。什么样的事情?嗯,随便什么。您可能有一个工作表,其中包含员工列表、产品列表、客户列表、CD收藏中的CD列表或夜空中的星星列表。其实没关系。
下面是一个列表或数据库的示例。在这种情况下,这是我们虚构的公司销售的产品列表:
通常像这样的列表中的每一项都有某种唯一的标识符。在这种情况下,唯一标识符位于“项目代码”列中。注意:要使VLOOKUP函数处理数据库/列表,该列表必须有一列包含唯一标识符(或“key”或“ID”),并且该列必须是表中的第一列。我们上面的示例数据库满足这个标准。
使用VLOOKUP最困难的部分是准确地理解它的用途。我们先看看能不能弄清楚:
VLOOKUP retrieves information from a database/list based on a supplied instance of the unique identifier.
在上面的示例中,您可以将VLOOKUP函数**到另一个带有项目代码的电子表格中,它将返回原始列表中描述的相应项目的描述、价格或可用性(其“库存”数量)。这些信息中哪一条会传给你?好吧,当你创建公式的时候,你可以决定这个。
如果您只需要数据库中的一条信息,那么用VLOOKUP函数构造一个公式会很麻烦。通常,您会在可重用的电子表格(如模板)中使用这种功能。每次有人输入一个有效的项目代码时,系统都会检索有关相应项目的所有必要信息。
让我们创建一个这样的示例:一个**模板,我们可以在我们虚构的公司中反复使用它。
首先,我们启动Excel,创建一张空白**:
这就是它的工作原理:使用**模板的人员将在“a”列中填写一系列项目代码,系统将从我们的产品数据库中检索每个项目的说明和价格。这些信息将用于计算每个项目的行总数(假设我们输入了有效数量)。
为了简化此示例,我们将在同一工作簿中的单独工作表中找到产品数据库:
实际上,产品数据库更有可能位于单独的工作簿中。它对VLOOKUP函数没有什么影响,VLOOKUP函数并不关心数据库是否位于同一个工作表、不同的工作表或完全不同的工作簿上。
因此,我们创建了产品数据库,如下所示:
为了测试我们将要编写的VLOOKUP公式,我们首先在空白**的A11单元格中输入一个有效的项目代码:
接下来,我们将活动单元格移动到要存储VLOOKUP从数据库检索到的信息的单元格中。有趣的是,这是大多数人错的一步。进一步解释:我们将创建一个VLOOKUP公式,它将检索与单元格A11中的项目代码相对应的描述。当我们得到这个描述时,我们想把它放在哪里?当然是B11细胞。这就是我们写VLOOKUP公式的地方:在B11细胞中。现在选择单元格B11。
我们需要找到Excel提供的所有可用函数的列表,这样我们就可以选择VLOOKUP并在完成公式时获得一些帮助。首先单击“公式”选项卡,然后单击“**函数”即可找到:
出现一个框,允许我们选择Excel中可用的任何函数。
为了找到我们要找的那个,我们可以键入一个搜索词,比如“lookup”(因为我们感兴趣的函数是lookup函数)。系统会返回一个Excel中所有查找相关函数的列表。VLOOKUP是第二个。选择它,然后单击“确定”。
此时会出现函数参数框,提示我们输入完成VLOOKUP函数所需的所有参数(或参数)。您可以将此框视为向我们询问以下问题的函数:
前三个参数以粗体显示,表示它们是强制参数(没有它们,VLOOKUP函数是不完整的,不会返回有效值)。第四个参数不是粗体的,这意味着它是可选的:
我们将自上而下依次完成论点。
我们需要完成的第一个参数是Lookup\u value参数。函数需要我们告诉它在何处找到它应该返回的描述的唯一标识符(本例中是项代码)。我们必须选择前面输入的项目代码(在A11中)。
单击第一个参数右侧的选择器图标:
然后单击一次包含项目代码(A11)的单元格,然后按Enter键:
值“A11”***到第一个参数中。
现在我们需要为Table\u数组参数输入一个值。换句话说,我们需要告诉VLOOKUP在哪里可以找到数据库/列表。单击第二个参数旁边的选择器图标:
现在定位数据库/列表并选择整个列表–不包括标题行。在我们的示例中,数据库位于单独的工作表上,因此我们首先单击该工作表选项卡:
接下来我们选择整个数据库,不包括标题行:
…然后按Enter。表示数据库的单元格范围(在本例中为“产品数据库”!A2:D7“)自动输入到第二个参数中。
现在我们需要输入第三个参数Col_index_num。我们用这个参数来指定要返回给我们的数据库中与A11中的项目代码相关联的信息。在这个特殊的例子中,我们希望将项目的描述返回给我们。如果查看数据库工作表,您会注意到“Description”列是数据库中的第二列。这意味着我们必须在Col\u index\u num框中输入一个值“2”:
需要注意的是,这里没有输入“2”,因为“说明”列位于该工作表的B列中。如果数据库恰好从工作表的K列开始,我们仍然会在该字段中输入“2”,因为“Description”列是我们在指定“Table\u array”时选择的单元格集中的第二列。
最后,我们需要决定是否在最后的VLOOKUP参数Range\u lookup中输入一个值。此参数需要true或false值,否则应留空。当将VLOOKUP与数据库一起使用时(90%的情况下都是这样),决定在这个参数中放入什么的方法可以考虑如下:
If the first column of the database (the column that contains the unique identifiers) is sorted alphabetically/numerically in ascending order, then it’s possible to enter a value of true into this argument, or leave it blank.
If the first column of the database is not sorted, or it’s sorted in descending order, then you must enter a value of false into this argument
由于数据库的第一列没有排序,我们在这个参数中输入false:
就这样!我们已经输入了VLOOKUP返回所需值所需的所有信息。单击“确定”按钮,注意与项目代码“R99245”对应的描述已正确输入单元格B11:
为我们创建的公式如下所示:
如果我们在单元格A11中输入不同的项目代码,我们将开始看到VLOOKUP函数的强大功能:描述单元格将更改以匹配新的项目代码:
我们可以执行一组类似的步骤将项目的价格返回到单元格E11中。请注意,新公式必须在单元格E11中创建。结果如下:
…公式如下:
请注意,这两个公式之间的唯一区别是第三个参数(Col\ u index\ num)已从“2”更改为“3”(因为我们需要从数据库的第三列检索数据)。
如果我们决定购买其中的2件,我们将在D11单元格中输入“2”。然后,我们将在单元格F11中输入一个简单的公式,以得到行总数:
=D11*E11
…看起来像这样…
到目前为止,我们已经学到了很多关于VLOOKUP的知识。事实上,我们已经学到了本文将要学习的所有内容。需要注意的是,VLOOKUP可以在数据库之外的其他环境中使用。这是不太常见的,可能会涵盖在未来如何极客文章。
我们的**模板还不完整。为了完成这项工作,我们将采取以下措施:
如果我们觉得非常聪明,我们会在另一个工作表中创建一个所有客户的数据库,然后使用在单元格F5中输入的客户ID在单元格B6、B7和B8中自动填写客户的姓名和地址。
如果你想练习VLOOKUP,或者只是看到我们的**模板,可以从这里下载。
...仅仅是比较文档,而是查找特定值时,您应该熟悉Excel的vlookup函数。要获得另一种方法,请尝试用Notepad++来比较文件,或者查看这些Mac文件比较工具。 ...
...速度学习,吸收更多。随着时间的推移,你甚至可以掌握VLOOKUP,这是Excel中最重要的功能。 ...
...项目数据 Excel中的随机数据不仅限于数字或日期。使用“VLOOKUP”特性,我们可以创建一个产品列表,命名它,然后从中提取,在另一列中创建一个随机列表。 首先,我们需要创建一个随机事件列表。在本例中,我们将从一个虚...
...数是一种很好的方法。 有许多查找函数,但最常用的是VLOOKUP,所以让我们使用它。 在本例中,我们有一个简单的雇员数据列表。 在另一张工作表上,我们存储了有关员工的培训数据。我们希望搜索并返回员工的年龄以进行分...
VLOOKUP是googlesheets中最容易被误解的函数之一。它允许您通过一个搜索值搜索并链接电子表格中的两组数据。下面是如何使用它。 与microsoftexcel不同,googlesheets中没有VLOOKUP向导来帮助您,因此您必须手动键入公式。 vlookup在googl...
...一个值,甚至替代公式来替换显示的错误。 在本例中,VLOOKUP函数返回了#N/a错误值。 此错误是因为没有要查找的办公室。这是一个合乎逻辑的原因,但是这个错误导致了总计算的问题。 IFERROR函数可以处理任何错误值,包括#REF...