☰

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.tbl1가치 를 가져오고 싶습니다 . sheet2.tbl2구체적으로는 각각에 의해 가입 key1, key2하고 from 에서 val1, val2from sheet2.tbl2으로 가져옵니다 . 키는 두 테이블 모두에서 고유하므로 첫 번째 일치가 수행됩니다.fun1, fun2sheet1.tbl1

나는 공식을 시도했다

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

하지만 작동하지 않습니다.

또한 확장 기능을 사용하고 싶지 않고 내장된 기능을 사용하여 확장하고 싶습니다. 그리고 바람직하게는 테이블 열 구문( tbl1[[key1]:[key2]])을 사용합니다.

답변1

어려움은 두 번째 테이블의 키 콤보에 있습니다. 예를 들어 사용하기가 어렵습니다 VLOOKUP(). 그러나 is는 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|dressAddress

결합을 수행하는 것은 XLOOKUP()and 에 대해 간단합니다 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))

두 가지를 모두 처리해야 한다면 그것이 사용되는 것입니다.

두 수식 모두 2열 대상 범위를 좋아하지 않으므로 (이 수식에서는) J20:K28을 사용할 수 없습니다.

(하지만 당연히 누군가는 그것을 사용 가능하게 만드는 좋은 방법을 알고 있을 수도 있습니다!)

사용하는 좋은 방법이 있지만 FILTER/FILTERExcel-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()

-ing을 포함하여 다차원 출력을 제공하는 장점이 있으므로 SPILL하나의 셀에 하나의 수식을 사용하면 전체 출력을 얻을 수 있습니다.

2016년 사용자가 가지고 있는 것을 사용할 수도 있지만 FILTERXML()매우 영리하게 사용하더라도(특히 이 방법이 단일 XPATH에서 두 열의 배열을 모두 구축하므로) usually이 경우에는 매우 기본적인 저수준 수준일 뿐입니다. 숫자 계산 연습. 클루지... "최대한으로." 그리고 내부 배열 문자열 길이에 문제가 발생할 수 있습니다.

이는 결합된 키에 대한 도우미 열 경로 또는 그 사촌인 결합된 키를 명명된 범위에 넣어 쉽게 주소를 지정할 수 있도록 하는 것은 말할 것도 없습니다.

INDEX/MATCH여러 사용자에게 이 방법을 권장합니다 .

관련 정보