☰

일치하는 값과 일치하지 않는 값에 대해 두 열을 비교하고 원하는 형식으로 얻으려면 어떻게 해야 합니까?

tag-icon • tag-icon microsoft-excel worksheet-function vba power-query
일치하는 값과 일치하지 않는 값에 대해 두 열을 비교하고 원하는 형식으로 얻으려면 어떻게 해야 합니까?

두 열 사이에서 일치하는 값과 일치하지 않는 값을 찾고 아래 방식으로 형식을 지정하려면 어떻게 해야 합니까?

입력:

| expected | match | forward | backward | actual |
|----------|-------|---------|----------|--------|
| a        |       |         |          | b      |
| b        |       |         |          | c      |
| c        |       |         |          | r      |
| d        |       |         |          | s      |
| e        |       |         |          |        |

산출:

| expected | match | forward | backward | actual |
|----------|-------|---------|----------|--------|
| a        | b     | a       | r        | b      |
| b        | c     | d       | s        | c      |
| c        |       | e       |          | r      |
| d        |       |         |          | s      |
| e        |       |         |          |        |

forward- ( ) 에는 있지만 expected없음actualSQL left outer join

backward- ( ) 에는 있지만 actual없음expectedSQL right outer join

expected내가 쿼리에서 얻은 것입니다 SQL. RDBMS에 열이 없을 때 많은 시나리오가 있으므로 actualExcel을 사용하여 비교해야 합니다. 평소에 사용하는 것과 비교할 수는 있지만 VLOOKUP시간이 많이 걸리고 원하는 형식이 나오지 않습니다. 할 수 있는 해결책을 원합니다중요한 것은위와 같은 형식으로.

나는 제안에 열려 있습니다.

답변1

공들여 나열한 것

정보가 위와 같이 배치되어 있다고 가정하면 C3, D3 및 E3에서 각각 다음 세 가지 수식을 사용할 수 있습니다.

C3
=IFERROR(INDEX(F:F,AGGREGATE(15,6,ROW($B$3:$B$7)/(COUNTIF($B$3:$B$7,$F$3:$F$7)>0),ROW(A1))),"")

D3
=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($B$3:$B$7)/(COUNTIF($C$3:$C$7,$B$3:$B$7)=0),ROW(A1))),"")

E3
=IFERROR(INDEX(F:F,AGGREGATE(15,6,ROW($F$3:$F$7)/((COUNTIF($C$3:$C$7,$F$3:$F$7)=0)*($F$3:$F$7<>"")),ROW(A1))),"")

7행까지 수식을 복사하면 다음과 같은 결과가 나옵니다.

POC

참고: AGGREGATE함수 15에 대한 계산과 같은 배열을 수행합니다. 따라서 AGGREGATE함수 내에서 전체 열 참조를 사용하지 마십시오. 데이터 세트에 가깝게 제한하십시오. 그렇지 않으면 빈 셀에 엄청난 양의 계산을 수행하여 컴퓨터를 수렁에 빠뜨릴 수 있습니다.

답변2

파워 쿼리의 경우 주요 변환은 다음과 같습니다.쿼리 병합. 이는 SQL 조인과 유사하며 유사한 용어를 사용합니다. 첫 번째 단계는예상되는그리고실제열. 에 대한성냥목록에서는 조인 유형 = 내부를 사용하여 쿼리를 작성하겠습니다. 에 대한앞으로Join Type = Left Anti를 사용하여 또 다른 쿼리를 작성한 다음뒤로Right Anti를 사용합니다.

제시된 방식으로 모든 것을 정확하게 모으는 것은 약간 까다로울 수 있지만(정말 필요한 경우) 각 쿼리에 인덱스를 추가하고 인덱스 열에서 병합 조인(조인 유형 = 왼쪽 외부)을 사용하여 얻을 수 있다고 생각합니다. 각 행에 있는 각 쿼리의 단일 결과입니다.

관련 정보