고전적인 상황: 하나로 병합해야 하는 두 개의 목록이 있습니다. 또한 초기 목록에는 고유한 요소와 일치하는 요소(목록 사이와 내부 모두)가 모두 있을 수 있지만 출력에서는 중복(반복) 없는 목록을 가져와야 합니다.
이러한 일반적인 문제를 해결하는 몇 가지 방법을 전통적으로 살펴보겠습니다. 원시적인 "이마"에서 더 복잡하지만 우아한 것까지.
방법 1: 중복 제거
가장 간단한 방법으로 문제를 해결할 수 있습니다. 두 목록의 요소를 수동으로 하나로 복사한 다음 도구를 결과 집합에 적용합니다. 중복 제거 탭에서 Data (데이터 — 중복 제거):
물론 이 방법은 소스 목록의 데이터가 자주 변경되는 경우 작동하지 않습니다. 각 변경 후에 다시 전체 절차를 반복해야 합니다.
방법 1a. 피벗 테이블
이 방법은 실제로 이전 방법의 논리적 연속입니다. 목록이 그다지 크지 않고 목록의 최대 요소 수를 미리 알고 있는 경우(예: 10개 이하) 직접 링크를 통해 두 테이블을 하나로 결합하고 오른쪽에 열을 추가하고 결과 테이블을 기반으로 요약 테이블을 작성하십시오.
아시다시피 피벗 테이블은 반복을 무시하므로 출력에서 중복 없이 결합된 목록을 얻습니다. Excel에서 두 개 이상의 열이 포함된 요약 테이블을 작성할 수 있기 때문에 보조 열이 1인 경우에만 필요합니다.
원본 목록이 변경되면 새 데이터는 직접 링크를 통해 결합된 테이블로 이동하지만 피벗 테이블은 수동으로 업데이트해야 합니다(오른쪽 클릭 – 업데이트 및 저장). 즉석에서 다시 계산할 필요가 없다면 다른 옵션을 사용하는 것이 좋습니다.
방법 2: 배열 수식
수식으로 문제를 해결할 수 있습니다. 이 경우 결과의 재계산 및 업데이트는 원래 목록이 변경된 직후 자동으로 즉시 발생합니다. 편의와 간결함을 위해 목록에 이름을 지정하겠습니다. 1 목록 и 2 목록사용 이름 관리자 탭 공식 (수식 — 이름 관리자 — 만들기):
이름을 지정한 후 필요한 수식은 다음과 같습니다.
언뜻보기에는 소름 끼치는 것처럼 보이지만 실제로는 모든 것이 그렇게 무섭지 않습니다. Alt+Enter 키 조합을 사용하여 이 수식을 여러 줄로 확장하고 다음과 같이 공백으로 들여쓰기하겠습니다.
여기의 논리는 다음과 같습니다.
- INDEX(List1;MATCH(0;COUNTIF($E$1:E1;List1); 0) 수식은 첫 번째 목록에서 고유한 요소를 모두 선택합니다. 소진되는 즉시 #N/A 오류가 발생하기 시작합니다.
- INDEX(List2;MATCH(0;COUNTIF($E$1:E1;List2); 0)) 수식은 동일한 방식으로 두 번째 목록에서 고유한 요소를 추출합니다.
- 서로 중첩된 두 개의 IFERROR 함수는 list-1의 고유한 출력을 먼저 구현한 다음 list-2의 출력을 차례로 구현합니다.
이것은 배열 수식입니다. 즉, 입력한 후 일반적이지 않은 셀에 입력해야 합니다. 엔터 버튼, 하지만 키보드 단축키를 사용하여 Ctrl 키+변화+엔터 버튼 그런 다음 여백이 있는 자식 셀로 복사(드래그)합니다.
영어 버전의 Excel에서 이 수식은 다음과 같습니다.
=IFERROR(IFERROR(INDEX(목록1, MATCH(0, COUNTIF($E$1:E1, 목록1), 0)), INDEX(목록2, MATCH(0, COUNTIF($E$1:E1, 목록2), 0)) ), "")
이 접근 방식의 단점은 소스 테이블에 많은 수의(수백 개 이상) 요소가 있는 경우 배열 수식이 파일 작업 속도를 현저히 저하시킨다는 것입니다.
방법 3. 파워 쿼리
소스 목록에 수백 또는 수천과 같이 많은 수의 요소가 있는 경우 느린 배열 수식 대신 근본적으로 다른 접근 방식, 즉 파워 쿼리 추가 기능 도구를 사용하는 것이 좋습니다. 이 추가 기능은 기본적으로 Excel 2016에 내장되어 있습니다. Excel 2010 또는 2013이 있는 경우 별도로 다운로드하여 설치할 수 있습니다(무료).
동작 알고리즘은 다음과 같습니다.
- 설치된 애드온의 별도 탭 열기 파워 쿼리 (Excel 2010-2013이 있는 경우) 또는 탭으로 이동합니다. Data (엑셀 2016이 있는 경우).
- 첫 번째 목록을 선택하고 버튼을 누릅니다. 테이블/레인지에서 (범위/표에서). 목록에서 "스마트 테이블"을 만드는 것에 대해 질문을 받았을 때 우리는 다음과 같이 동의합니다.
- 로드된 데이터와 쿼리 이름을 볼 수 있는 쿼리 편집기 창이 열립니다. 표 1 (원하는 경우 자신의 것으로 변경할 수 있습니다.)
- 테이블 헤더(단어 1 목록) 다른 이름으로 이름을 바꿉니다(예: 사람들). 정확히 무엇을 명명하는가는 중요하지 않지만 발명된 이름은 기억해야 하기 때문입니다. 나중에 두 번째 테이블을 가져올 때 다시 사용해야 합니다. 나중에 두 테이블을 병합하는 것은 열 머리글이 일치하는 경우에만 작동합니다.
- 왼쪽 상단 모서리에 있는 드롭다운 목록 확장 닫고 다운로드 선택하고 닫기 및 로드… (닫기 및 로드...):
- 다음 대화 상자(조금 다르게 보일 수 있음 – 놀라지 마십시오)에서 다음을 선택하십시오. 연결만 만드세요 (연결만 생성):
- 두 번째 목록에 대해 전체 절차(2-6번 항목)를 반복합니다. 열 머리글의 이름을 변경할 때 이전 쿼리와 동일한 이름(People)을 사용하는 것이 중요합니다.
- 탭의 Excel 창에서 Data 또는 탭에서 파워 쿼리 왼쪽 메뉴에서 데이터 가져오기 – 요청 결합 – 추가 (데이터 가져오기 - 쿼리 병합 - 추가):
- 표시되는 대화 상자의 드롭다운 목록에서 요청을 선택합니다.
- 결과적으로 두 개의 목록이 서로 연결되는 새 쿼리가 생성됩니다. 버튼으로 중복을 제거해야합니다. 행 삭제 – 중복 제거 (행 삭제 - 중복 삭제):
- 완성된 쿼리는 옵션 패널의 오른쪽에서 이름을 바꿀 수 있으며 정상적인 이름(이것이 실제로 결과 테이블의 이름이 됨)을 제공하고 모든 명령을 사용하여 시트에 업로드할 수 있습니다. 닫고 다운로드 (닫기 및 로드):
앞으로는 원래 목록에 변경 사항이나 추가 사항이 있으면 마우스 오른쪽 버튼을 클릭하여 결과 테이블을 업데이트하는 것으로 충분할 것입니다.
- 파워 쿼리를 사용하여 다른 파일에서 여러 테이블을 수집하는 방법
- 목록에서 고유 항목 추출
- 일치 및 차이점에 대해 두 목록을 서로 비교하는 방법