☰

通过 2 个键连接两个表

tag-icon • tag-icon microsoft-excel microsoft-excel-2016
通过 2 个键连接两个表

我正在使用 Microsoft Excel 2016。我在不同的工作表上有 2 个表格。

sheet1.tbl1 {
key1,
key2,
val1,
fun1,
fun2
}

sheet2.tbl2 {
key1,
key2,
val1,
val2
}

我想使用公式或其他方法将值添加到sheet1.tbl1from sheet2.tbl2。具体来说，分别使用 join bykey1, key2和 Bring val1, val2from sheet2.tbl2to fun1, fun2from sheet1.tbl1。两个表中的键都是唯一的，因此第一个匹配即可。

我尝试过配方奶粉

=INDEX(rls, MATCH(tbl1[[key1]:[key2]], tbl2[key1]:[key2]], 0),3)

但它不起作用。

我也不想使用扩展，我想使用内置功能来实现这一点。最好使用表列语法 ( tbl1[[key1]:[key2]])。

答案1

困难在于第二张表中的按键组合。VLOOKUP()例如，这使得它很难使用。但对于INDEX/MATCH或 来说，这并不是什么特别的问题XLOOKUP()。

例如：

=XLOOKUP($A1:$A9&"|"&$B1:$B9,  $H$20:$H$28&"|"&$I$20:$I$28,  J20:J28,,0)

您可以使用联合运算符 组合键&，并用 等意外字符将它们分隔开|。（您可以选择一个字符，然后在键中搜索它以确保它未被使用。）这样就不会发生意外重复。示例：一个密钥对是Add和 ，ress而另一个是Ad和dress。如果中间有一个字符，则会得到Add|ress和 ，Ad|dress而不是 的两个实例Address。

XLOOKUP()对于和MATCH()（在的一半中进行合并）来说，合并非常简单INDEX/MATCH。Both 也可以轻松使用数组来处理这些部分，因此您可以创建一个公式。

在当前版本中，它们会SPILL运行良好。在旧版本（您标记为 2016 版）中，它们不会运行，因此您需要根据需要使用{CSE}输入或复制并粘贴到列中。

对于较新的版本，XLOOKUP()有一个简单而明显（有利于维护）的公式。对于较旧的版本，INDEX/MATCH几乎没有牺牲：

=INDEX($J$20:$J$28,  MATCH($A1:$A9&"|"&$B1:$B9,  $H$20:$H$28&"|"&$I$20:$I$28,,0))

鉴于您需要处理两者，因此请使用此方法。

这两个公式都不喜欢两列目标范围，因此（在这些公式中）J20:K28 不可用。

（但自然地，有人可能知道一个很好的方法来使它可用！）

有一个很好的方法FILTER/FILTER，但您不能将其用于 Excel-2016 用户，因此它目前对您没有帮助。不过，它可能在未来的工作簿中是一种有用的技巧：

=FILTER(FILTER(H20:K28,SORT(A1:A9&"|"&B1:B9)=SORT(H20:H28&"|"&I20:I28)),{0,0,1,1})

内部FILTER()选择数据。当然，键列表几乎肯定不是按相同顺序排列的（或者“=F12、=F13、=F14 等”会接近工作顺序，是吗？）。因此，SORT()在每个键组合数组上使用以将它们按相同顺序排列。如果第二个表中有第一个表中没有的键对，则需要删除它们或选择其他路径。

然后，外部的FILTER工作方式非常类似于INDEX()使用数组常量来选择要输出的列（或行）（您可以使用它INDEX()，但不那么容易。FILTER()使用简单数组常量作为“显示列/不显示列”功能。所以你只输出你想要输出的两列。

它能够提供多维输出，包括SPILL-ing，因此一个单元格中的一个公式即可获得整个输出。

您甚至可以使用FILTERXML()2016 年用户会用到的方法，但尽管这是非常巧妙的用法（尤其是因为该方法将在单个 XPATH 中构建两个列的数组）usually，但在这种情况下，它只是一种非常基本的低级数字运算练习。笨拙……“达到最大值”。并且可能会遇到内部数组字符串长度的问题。

这甚至还没有提到辅助列路由（用于组合键）或其同类，将组合键放入命名范围以使其易于寻址。

INDEX/MATCH我会向您的用户组合推荐该方法。

相关内容