通过将Excel的VLOOKUP函数与COLUMN函数相结合,我们可以创建一个查找公式,使您可以从单个数据库或数据表中返回多个值。
在上图中显示的示例中,查找公式可轻松返回与各种硬件相关的所有值,例如价格,部件号和供应商。
01 10
使用Excel VLOOKUP返回多个值
按照下面列出的步骤创建上图中的查找公式,该查找公式将从单个数据记录中返回多个值。
查找公式要求将COLUMN函数嵌套在VLOOKUP中。
嵌套一个函数需要输入第二个函数作为第一个函数的参数之一。
在本教程中,COLUMN函数将作为VLOOKUP的列索引号参数输入。
本教程的最后一步涉及将查找公式复制到其他列以检索所选零件的附加值。
教程内容
- 输入教程数据
- 为数据表创建一个命名范围
- 启动VLOOKUP功能
- 使用绝对单元格引用输入查找值参数
- 输入表格数组参数
- 输入嵌套的COLUMN函数
- 完成VLOOKUP功能
- 使用填充句柄复制查找公式
- 使用查找公式检索数据
02之10
输入教程数据
为了遵循教程中的步骤,将上图中显示的数据输入到以下单元格中 。
- 将最高范围的数据输入到单元格D1到G1中
- 将第二个范围输入到单元格D4到G10中
本教程中创建的搜索条件和查找公式将输入工作表的第 2 行 。
本教程不包含图像中显示的格式,但这不会影响查找公式的工作方式。
有关格式化选项的信息与上述类似,可在此基本Excel格式教程中找到 。
教程步骤
- 如上图所示将数据输入到单元格D1到G10中
03之10
为数据表创建一个命名范围
命名范围是一种简单的方法来引用公式中的一系列数据。 您可以输入范围的名称,而不是输入数据的单元格引用 。
使用命名范围的第二个优点是,即使将公式复制到工作表中的其他单元格,该范围的单元格引用也不会更改。
因此,范围名称是使用绝对单元格引用来防止复制公式时出现错误的替代方法。
注意:范围名称不包含数据(第4行)的标题或字段名称 ,而仅包含数据本身。
教程步骤
- 突出显示工作表中的单元格D5到G10以选择它们
- 点击位于A列上方的名称框
- 在名称框中键入“表”(不包括引号)
- 按下键盘上的ENTER键
- 单元格D5到G10现在具有“表格”的范围名称。 我们将在本教程后面的章节中使用VLOOKUP 表格数组参数的名称
04年10月
打开VLOOKUP对话框
虽然可以直接将查找公式直接输入到工作表中的单元格中,但许多人发现难以保持语法直观 - 特别是对于复杂的公式,例如本教程中使用的公式。
在这种情况下,另一种方法是使用VLOOKUP 对话框 。 几乎所有的Excel 函数都有一个对话框,允许您在单独的行中输入每个函数的参数。
教程步骤
- 单击工作表的单元格E2 - 显示二维查找公式结果的位置
- 点击功能区的“ 公式”选项卡
- 点击功能区中的查找和参考选项打开功能下拉列表
- 点击列表中的VLOOKUP打开函数的对话框
10的10
使用绝对单元格引用输入查找值参数
在我们的例子中, 查找值是指我们想要查找信息的硬件部分的名称。
查找值的允许类型的数据是:
在这个例子中,我们将输入零件名称所在位置的单元格参考 - 单元格D2。
绝对单元格引用
在本教程后面的步骤中,我们将把单元格E2中的查找公式复制到单元格F2和G2。
通常,在Excel中复制公式时,单元格引用会更改以反映其新位置。
如果发生这种情况,D2 - 查找值的单元格引用 - 将随着公式的复制而更改,从而在单元格F2和G2中创建错误。
为了防止出现错误,我们将把单元格引用D2转换为绝对单元格引用 。
复制公式时绝对单元格引用不会更改。
通过按下键盘上的F4键创建绝对单元格引用。 这样做会在单元格引用周围添加美元符号,例如$ D $ 2
教程步骤
- 点击对话框中的lookup_value行
- 单击单元格D2将此单元格引用添加到lookup_value行。 这是我们将输入我们正在寻找信息的部件名称的单元格
- 在不移动插入点的情况下,按下键盘上的F4键将D2转换为绝对单元格引用$ D $ 2
- 将VLOOKUP功能对话框保留为教程中的下一步
06年10月
输入表格数组参数
表格数组是查找公式搜索以找到我们想要的信息的数据表格 。
表格数组必须至少包含两列数据 。
- 第一列包含查找值参数(本教程中的上一步)
- 第二列和其他列将按查找公式进行搜索,以查找我们指定的信息。
必须将表格数组参数输入为包含数据表的单元格引用的范围或范围名称 。
在本例中,我们将使用教程步骤3中创建的范围名称。
教程步骤
- 点击对话框中的table_array行
- 键入“Table”(不带引号)输入此参数的范围名称
- 将VLOOKUP功能对话框保留为教程中的下一步
07的10
嵌套COLUMN函数
通常,VLOOKUP只返回数据表的一列中的数据,并且该列由列索引号参数设置。
然而,在这个例子中,我们有三列希望返回数据,所以我们需要一种方法来轻松更改列索引号,而无需编辑我们的查找公式。
这是COLUMN函数的功能。通过将它作为列索引号参数输入,它将随着查找公式从教程D2中的单元格D2复制到单元格E2和F2而发生变化。
嵌套函数
因此,COLUMN函数充当VLOOKUP的列索引号 参数 。
这是通过在对话框的Col_index_num行中嵌套VLOOKUP内的COLUMN函数来完成的。
手动输入COLUMN功能
嵌套函数时,Excel不允许我们打开第二个函数的对话框来输入它的参数。
因此,COLUMN函数必须在Col_index_num行中手动输入。
COLUMN函数只有一个参数 - 引用参数是单元格引用。
选择COLUMN函数的参考参数
COLUMN函数的工作是返回作为参考参数给出的列的编号。
换句话说,它将列字母转换为一个数字,列A是第一列,列B是第二列,依此类推。
由于我们想要返回的第一个数据字段是该项目的价格 - 它位于数据表的第二列中 - 我们可以选择列B中任何单元格的单元格引用作为参考参数,以便获得数字2 Col_index_num参数。
教程步骤
- 在VLOOKUP函数对话框中,单击Col_index_num行
- 键入函数名称列后跟一个打开的圆括号“ ( ”
- 单击工作表中的单元格B1 ,输入该单元格引用作为引用参数
- 键入一个结束圆括号“ ) ”来完成COLUMN功能
- 将VLOOKUP功能对话框保留为教程中的下一步
08年10月
输入VLOOKUP范围查找参数
VLOOKUP的Range_lookup参数是一个逻辑值 (仅为TRUE或FALSE),用于指示您是否希望VLOOKUP查找与Lookup_value精确匹配或近似匹配。
- 如果为TRUE或省略了此参数 ,则VLOOKUP返回与Lookup_value完全匹配的值,或者,如果未找到完全匹配,VLOOKUP将返回下一个最大值。 对于要执行此操作的公式,必须按升序对Table_array第一列中的数据进行排序 。
- 如果FALSE,VLOOKUP将仅使用与Lookup_value精确匹配的值。 如果Table_array的第一列中有两个或更多值与查找值匹配,则使用找到的第一个值。 如果找不到完全匹配,则返回#N / A错误。
在本教程中,由于我们正在查找有关特定硬件项目的特定信息,因此我们将Range_lookup设置为False 。
教程步骤
- 点击对话框中的Range_lookup行
- 在这一行中输入False这个词,表示我们希望VLOOKUP为我们正在寻找的数据返回完全匹配
- 单击确定以完成查找公式和关闭对话框
- 由于我们还没有将查找标准输入单元格D2,单元格E2中将出现#N / A错误
- 当我们在本教程的最后一步添加查找条件时,此错误将得到更正
09年10月
使用填充句柄复制查找公式
为此,查找公式必须存在于我们想要的信息的所有字段中。
在本教程中,我们希望它从数据表的第2,3和4列中检索数据,即当我们输入零件名称作为Lookup_value时,价格,零件号和供应商名称。
由于数据在工作表中以规则模式布局,因此我们可以将单元格 E2中的查找公式复制到单元格F2和G2。
在复制公式时,Excel将更新COLUMN函数(B1)中的相对单元格引用以反映公式的新位置。
另外,Excel在复制公式时不会更改绝对单元格引用 $ D $ 2和命名范围 表 。
有多种方法可以在Excel中复制数据,但最简单的方法可能是使用填充处理 。
教程步骤
- 单击单元格E2 - 查找公式所在的位置 - 使其成为活动单元格
- 将鼠标指针放在右下角的黑色方块上。 指针将变为加号“ + ” - 这是填充句柄
- 单击鼠标左键并将填充手柄拖到单元格G2
- 释放鼠标按钮,单元格F3应包含二维查找公式
- 如果正确完成,单元格F2和G2现在应该也包含单元格E2中存在的#N / A错误
10 10
输入查找标准
一旦查找公式已被复制到所需的单元格,它就可以用来从数据表中检索信息。
为此,请将想要检索的项目名称输入到Lookup_value单元格(D2)中,然后按键盘上的ENTER键。
一旦完成,包含查找公式的每个单元格都应该包含关于您正在搜索的硬件项目的不同数据。
教程步骤
- 单击工作表中的单元格D2
- 将Widget键入单元格D2并按下键盘上的ENTER键
- 在单元格E2到G2中应显示以下信息:
- E2 - $ 14.76 - 小部件的价格
- F2 - PN-98769 - 部件的部件号
- G2 - Widgets Inc. - 小部件供应商的名称
- 通过在单元格D2中键入其他部分的名称并观察单元格E2到G2中的结果,进一步测试VLOOKUP数组公式
如果出现错误消息,如#REF! 出现在单元格E2,F2或G2中,此VLOOKUP错误消息列表可帮助您确定问题所在。