☰

透過 2 個鍵連接兩個表

tag-icon • tag-icon microsoft-excel microsoft-excel-2016
透過 2 個鍵連接兩個表

我正在使用 Microsoft Excel 2016。

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

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

sheet1.tbl1我想透過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/MATCHor來說並不是什麼特別的問題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兩者都可以輕鬆地使用這些部分的陣列來處理，因此您可以建立公式。

在當前版本中，它們會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在一個單元格中使用一個公式，然後您就可以獲得整個輸出。

您甚至可以使用FILTERXML()2016 年用戶將擁有的，但儘管這是對它的非常巧妙的使用（特別是因為該方法將在單個 XPATH 中構建兩個列的數組）usually，但在這種情況下，它只是一個非常基本的低階數字運算練習。 Kludgy...「最大限度」。並且可能會遇到內部數組字串長度的問題。

更不用說輔助列路由（對於組合鍵）或其表兄弟，將組合鍵放入命名範圍中以使它們易於尋址。

INDEX/MATCH我會為您的用戶組合推薦該方法。

相關內容