Excel로 다양한 업무를 처리하다 보면 두 개 이상의 열에서 일치하는 항목과 서로 다른 항목을 찾아야 하는 상황이 자주 발생합니다. Excel에서 두 열이나 목록을 비교하는 것 자체는 어렵지 않지만, 방법이 워낙 다양하다 보니 어떤 방식을 선택해야 할지 헷갈릴 수 있습니다. 이 글에서는 Excel에서 열을 비교하고 일치 여부를 판별할 수 있는 대표적인 기법들을 단계별로 정리해 드립니다.
Excel에서 두 열 또는 목록을 비교하는 4가지 방법
예시로 슈퍼마켓의 두 매장(쇼룸)에서 판매하는 품목 이름이 담긴 두 개의 열 데이터를 준비했습니다. 이 두 매장의 데이터를 비교해 보겠습니다.
1. 등호(=) 연산자로 두 열 비교하기
가장 간단한 방법은 등호(=)를 이용해 두 열을 행 단위로 비교하는 것입니다. 항목이 같으면 TRUE, 다르면 FALSE가 표시됩니다.
📌 진행 순서:
- 일치 여부를 표시할 새 열을 오른쪽에 추가합니다.
- D5 셀에 아래 수식을 입력합니다.
=B5=C5
- Enter 키를 누른 뒤 채우기 핸들을 아래로 끌어 나머지 셀에도 적용합니다.
값이 일치하면 TRUE, 일치하지 않으면 FALSE가 표시되는 것을 확인할 수 있습니다.
2. '이동 옵션'의 행 차이 기능으로 두 목록 비교하기
이번에는 행 차이(Row differences) 기능을 활용해 보겠습니다. 이 기능은 두 열을 행 단위로 비교한 후, 값이 다른 두 번째 열의 셀을 자동으로 선택해 줍니다.
📌 진행 순서:
- B5:C9 범위 전체를 선택합니다.
- F5 키를 누릅니다.
- '이동' 대화상자가 나타나면 '옵션(Special)' 버튼을 클릭합니다.
- '이동 옵션' 창에서 '행 차이' 항목을 선택합니다.
- 확인을 클릭합니다.
- 두 번째 열에서 값이 다른 두 개의 셀이 자동으로 선택됩니다.
- '채우기 색' 옵션으로 해당 셀의 배경색을 변경합니다.
- 데이터를 다시 확인해 보겠습니다.
일치하지 않는 두 번째 열의 셀들이 한눈에 들어오게 되었습니다.
3. Excel 함수로 두 열 또는 목록 비교하기
3.1 IF 함수 활용
IF 함수를 사용하면 두 열의 셀을 행 단위로 비교하여 결과를 원하는 문구로 표시할 수 있습니다.
IF 함수는 조건이 충족되는지 검사하여, TRUE일 때 하나의 값을 반환하고 FALSE일 때 다른 값을 반환합니다.
📌 진행 순서:
- D5 셀에 IF 함수 기반의 수식을 입력합니다.
=IF(B5=C5,"Match","Mismatch")
이 수식은 두 셀의 값이 같은지 확인하고, 같으면 Match, 다르면 Mismatch를 표시합니다.
- 채우기 핸들을 아래로 끌어 전체 범위에 적용합니다.
반대로 일치하지 않는 값을 찾고 싶다면 아래 수식을 사용할 수도 있습니다.
=IF(B5<>C5,"Mismatch","Match")
이 경우 조건이 TRUE이면 Mismatch, 그렇지 않으면 Match가 표시됩니다.
3.2 EXACT 함수 활용
두 열에 대소문자만 다른 동일한 데이터가 있을 때는 EXACT 함수를 사용하는 것이 좋습니다.
EXACT 함수는 두 텍스트 문자열이 완전히 동일한지 검사하여 TRUE 또는 FALSE를 반환하며, 대소문자를 구분한다는 특징이 있습니다.
예제 데이터의 6행에는 대소문자만 다른 동일한 데이터가 있습니다. EXACT 함수가 대소문자 차이를 감지하는지 확인해 보겠습니다.
📌 진행 순서:
- D5 셀에 아래 수식을 입력합니다.
=IF(EXACT(B5,C5),"Match","Mismatch")
여기서 IF 함수는 EXACT 함수가 판단한 결과에 따라 알맞은 문구를 표시하는 역할을 합니다.
- 채우기 핸들을 끌어 나머지 셀에 적용합니다.
결과를 보면 대소문자 차이 때문에 D6 셀에 Mismatch가 표시된 것을 확인할 수 있습니다.
3.3 MATCH 함수 활용
이 방법은 첫 번째 열의 각 항목이 두 번째 열에 존재하는지 확인합니다. 두 번째 열에서 일치하는 항목을 찾으면 결과는 TRUE입니다.
여기서는 MATCH 함수를 ISERROR와 IF 함수와 함께 사용합니다.
MATCH 함수는 지정한 값과 일치하는 항목의 배열 내 상대적 위치를 반환합니다.
📌 진행 순서:
- D5 셀에 아래 수식을 입력합니다.
=IF(ISERROR(MATCH($B5,$C$5:$C$10,0)),"No match","Match found")
조건이 참이면 Match found, 거짓이면 No match가 표시됩니다.
- Enter 키를 눌러 수식을 실행합니다.
첫 번째 열의 각 항목이 두 번째 열에서 일치하는 항목을 찾았는지에 따라 결과가 표시되었습니다.
4. 조건부 서식으로 두 열을 비교하고 강조하기
이번에는 조건부 서식을 이용해 두 열을 비교하고, 조건에 맞는 셀을 색상으로 강조하는 방법을 알아보겠습니다.
4.1 두 열에서 일치하는 값 강조하기
📌 진행 순서:
- 먼저 데이터 범위를 선택합니다.
- 홈 탭에서 '조건부 서식' 메뉴로 이동합니다.
- 드롭다운 목록에서 '새 규칙'을 선택합니다.
- '새 서식 규칙' 창이 열리면 규칙 유형에서 '수식을 사용하여 서식을 지정할 셀 결정'을 선택합니다.
- 수식 입력란에 아래 수식을 입력합니다.
=$B5=$C5
- '셀 서식' 창에서 '채우기' 탭을 선택합니다.
- 원하는 색상을 지정합니다.
- 확인을 클릭합니다.
- '미리 보기'에서 서식을 미리 확인한 후 확인을 누릅니다.
- 데이터를 확인해 보겠습니다.
동일한 데이터가 있는 셀들이 지정한 색상으로 강조되었습니다.
4.2 고유 값과 중복 값 강조하기
이번에는 고유한 값과 중복된 값의 셀을 서로 다른 색으로 강조해 보겠습니다.
📌 진행 순서:
- 앞에서와 같은 방법으로 '새 규칙' 옵션을 실행합니다.
- 규칙 유형에서 '고유 또는 중복 값만 있는 셀만 서식 지정'을 선택합니다.
- '중복' 옵션을 선택합니다.
- 서식 색상을 설정한 뒤 확인을 누릅니다.
중복 셀이 강조되었습니다.
- 같은 과정을 반복하되 이번에는 '고유' 옵션을 선택합니다.
- 최종 결과를 확인해 보겠습니다.
중복 데이터와 고유 데이터가 서로 다른 색으로 구분되어 표시되었습니다.
두 열을 비교하고 일치 항목 개수 세기
일치 항목의 개수를 구하려면 SUMPRODUCT 함수와 COUNTIF 함수를 조합하면 됩니다. 이후 ROWS 함수로 전체 행 수를 계산하고, 여기서 일치 개수를 빼면 불일치 개수도 구할 수 있습니다.
SUMPRODUCT 함수는 지정된 범위나 배열의 곱들의 합계를 반환합니다.
📌 진행 순서:
- 먼저 일치(Match)와 불일치(Mismatch)를 표시할 두 개의 행을 추가합니다.
- C11 셀에 SUMPRODUCT와 COUNTIF 함수를 활용한 아래 수식을 입력합니다.
=SUMPRODUCT(COUNTIF(B5:B9,C5:C9))
- Enter 키를 눌러 결과를 확인합니다.
일치하는 행의 개수가 구해졌습니다.
- 이제 C12 셀에 아래 수식을 입력합니다.
=ROWS(B5:C9)-C11
- 다시 Enter 키를 누르면 불일치 개수가 표시됩니다.
두 열을 비교하고 중복 제거하기
이번에는 두 열을 비교한 후 중복 항목을 제거하는 방법을 살펴보겠습니다.
📌 진행 순서:
- 먼저 데이터 범위를 선택합니다.
- '조건부 서식' 메뉴로 이동합니다.
- '셀 강조 규칙'에서 '중복 값'을 선택합니다.
- 중복 항목을 표시할 색상을 선택합니다.
- 중복 데이터가 있는 셀의 색상이 변경된 것을 확인할 수 있습니다.
- Ctrl + Shift + L 키를 눌러 필터 기능을 활성화합니다.
- 두 번째 열의 화살표를 클릭합니다.
- '색 기준 필터'에서 중복 셀의 색상을 선택합니다.
- 중복 값만 표시됩니다. 해당 범위를 선택한 후 마우스 오른쪽 버튼을 누릅니다.
- 컨텍스트 메뉴에서 '내용 지우기'를 선택합니다.
- 중복 값이 데이터에서 제거되었습니다.
- 필터 메뉴로 다시 가서 '모두 선택'을 체크합니다.
- 더 이상 중복 항목이 표시되지 않습니다.
VLOOKUP으로 두 열을 매치하고 세 번째 열에서 값 추출하기
여기서는 두 개의 데이터 세트를 준비했습니다. 첫 번째는 Showroom 1, 두 번째는 Showroom 2의 데이터입니다. 각 데이터 세트의 품목 열을 비교하고, VLOOKUP 함수를 사용해 Showroom 1의 가격 정보를 Showroom 2 테이블로 가져오겠습니다.
📌 진행 순서:
- F5 셀에 VLOOKUP 함수 기반의 아래 수식을 적용합니다.
=VLOOKUP($E5,$B$5:$C$10,2,FALSE)
- Item (S1)과 Item (S2)를 비교한 뒤, 두 번째 테이블에 가격이 추출되었습니다.
Excel에서 세 개 이상의 열 비교하는 방법
앞에서는 두 열 사이의 비교를 살펴봤습니다. 열이 세 개 이상일 때는 아래 방법들을 활용할 수 있습니다.
1. AND 함수 활용
AND 함수는 모든 인수가 TRUE인지 검사하며, 모든 인수가 TRUE일 때만 TRUE를 반환합니다.
이 방법은 모든 조건을 검사한 후 IF 함수에 지정된 문구로 결과를 표시합니다. 수식을 적용하기 전에 Showroom 3이라는 열을 하나 더 추가했습니다.
📌 진행 순서:
- E5 셀에 아래 수식을 입력합니다.
=IF(AND(B5=C5,C5=D5),"Match","Mismatch")
- 이후 채우기 핸들을 아래로 끕니다.
모든 행의 일치 여부가 표시되었습니다.
2. COUNTIF 함수로 비교하기
COUNTIF 함수는 주어진 조건을 만족하는 범위 내 셀의 개수를 계산하고, COUNTA 함수는 범위에서 비어 있지 않은 셀의 개수를 계산합니다.
이 두 함수를 활용해 여러 열을 비교해 보겠습니다.
📌 진행 순서:
- E5 셀에 아래 수식을 복사해 넣습니다.
=IF(COUNTIF(B5:D5,B5)=COUNTA(B5:D5),"Match","Mismatch")
- 채우기 핸들을 아래로 끌어 적용합니다.
전체 행의 비교 결과가 완성되었습니다.
마무리
지금까지 Excel에서 두 열 또는 목록을 비교하는 다양한 방법을 살펴봤습니다. 행 단위 비교부터 함수 활용, 조건부 서식, 일치 개수 계산, 중복 제거, VLOOKUP을 통한 값 추출까지 폭넓게 다뤘습니다. 이 가이드가 실무에 도움이 되기를 바랍니다. 더 궁금한 점이 있다면 댓글로 의견을 남겨주세요.