핵심 요약
- 명단 두 개를 나란히 두고 한쪽에만 있는 사람을 표시하는 방법입니다.
COUNTIF로 "상대 명단에 몇 번 나오나"를 세고, 0이면IF가 "미연락"이라고 적습니다.ISNA(MATCH())로도 같은 일을 할 수 있습니다.- 예제에서 배정 8명 중 연락 못 한 3명과, 배정 명단에 없는 1명을 찾아냅니다.
이번 달에 연락할 사람 명단을 받았고, 따로 "연락 끝낸 사람" 명단을 적어 왔다고 해 보겠습니다. 두 명단은 순서도 다르고 사람 수도 다릅니다. 눈으로 한 줄씩 대조하면 한두 명은 꼭 빠뜨립니다. 출석부와 과제 제출자 목록을 대조해 과제를 안 낸 학생을 찾을 때, 초대 명단과 참석자 명단을 맞춰 볼 때도 똑같은 문제입니다.
이 글은 엑셀 "목적별 함수 조합" 시리즈의 한 편입니다. 수식 한 줄을 넣고 끌어내리면 엑셀이 대조를 대신 해 줍니다. 두 가지 방식과, 반대 방향 확인까지 차례로 보겠습니다.
예제 표: 배정 명단과 연락 완료 명단
A열에 이번 달 배정 명단(8명), D열에 연락을 끝낸 사람(6명)이 있습니다. 이름은 모두 가상입니다. 맨 윗줄의 A~E는 열 이름, 맨 왼쪽 숫자는 행 번호입니다. C열은 두 명단을 구분하려고 비워 두었습니다.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 배정 명단 | 연락 여부 | 연락 완료 | 배정 확인 | |
| 2 | 김민준 | 박지훈 | |||
| 3 | 이서연 | 김민준 | |||
| 4 | 박지훈 | 윤지아 | |||
| 5 | 최수아 | 한지민 | |||
| 6 | 정도윤 | 정도윤 | |||
| 7 | 강하은 | 이서연 | |||
| 8 | 조현우 | ||||
| 9 | 윤지아 |
할 일은 두 가지입니다. ① A열 사람 중 D열에 없는 사람(연락 못 한 사람)을 B열에 표시하기. ② D열 사람 중 A열에 없는 사람(배정 명단에 없는데 연락한 사람)을 E열에 표시하기.
완성 수식: B2에 넣고 B9까지 채우기
=IF(COUNTIF($D$2:$D$7,A2)=0,"미연락","완료")- B2 셀을 클릭하고 위 수식을 입력한 뒤 Enter를 누릅니다.
완료가 나오면 정상입니다(김민준은 D3에 있습니다). - B2를 다시 클릭하고, 오른쪽 아래 모서리의 작은 네모(채우기 핸들)를 B9까지 끌어내립니다.
$D$2:$D$7은 그대로 있고,A2만 A3, A4…로 바뀌는지 B9를 클릭해 수식 입력줄에서 확인합니다.
| A | B (결과) | |
|---|---|---|
| 2 | 김민준 | 완료 |
| 3 | 이서연 | 완료 |
| 4 | 박지훈 | 완료 |
| 5 | 최수아 | 미연락 |
| 6 | 정도윤 | 완료 |
| 7 | 강하은 | 미연락 |
| 8 | 조현우 | 미연락 |
| 9 | 윤지아 | 완료 |
미연락이 몇 명인지는 빈 칸에 =COUNTIF(B2:B9,"미연락")을 넣으면 3으로 나옵니다. 미연락만 보고 싶으면 B열에 필터를 걸어 "미연락"만 체크하면 됩니다.
반대 방향: 배정 명단에 없는 사람
E2에 아래 수식을 넣고 E7까지 채웁니다. 이번에는 찾는 범위가 A열로 바뀝니다.
=IF(COUNTIF($A$2:$A$9,D2)=0,"명단에 없음","")E5(한지민) 한 칸에만 명단에 없음이 나오고 나머지는 빈칸입니다. 마지막 ""는 "아무것도 적지 말라"는 뜻입니다. 명단에 있는 사람까지 "있음"이라고 적히면 오히려 눈에 안 띄기 때문에 비워 둡니다.
수식 풀어 보기: 안쪽부터 한 겹씩
1단계: COUNTIF — 상대 명단에 몇 번 나오나
COUNTIF($D$2:$D$7, A2)COUNTIF(범위, 조건)는 범위 안에서 조건과 같은 칸이 몇 개인지 셉니다. 여기서는 "D2:D7에 A2와 같은 이름이 몇 개 있나"입니다.
| 행 | 이름 | COUNTIF 결과 | 뜻 |
|---|---|---|---|
| 2 | 김민준 | 1 | 연락 완료 명단에 있음 |
| 5 | 최수아 | 0 | 없음 |
| 7 | 강하은 | 0 | 없음 |
| 8 | 조현우 | 0 | 없음 |
2단계: =0 — 참인지 거짓인지
COUNTIF(…)=0은 "개수가 0인가?"라는 질문입니다. 최수아는 0이므로 TRUE(참), 김민준은 1이므로 FALSE(거짓)가 됩니다.
3단계: IF — 참이면 "미연락", 거짓이면 "완료"
IF(조건, 참일 때, 거짓일 때)가 2단계 결과를 받아 글자를 고릅니다. 최수아는 TRUE라 미연락, 김민준은 FALSE라 완료입니다.
$를 빼먹으면 대조 범위가 따라 내려갑니다. COUNTIF(D2:D7,A2)처럼 $ 없이 쓰고 끌어내리면, B9에서는 범위가 D9:D14로 밀려 빈 칸을 뒤지게 됩니다. 그러면 연락한 사람도 "미연락"으로 나옵니다. 찾는 범위에는 $D$2:$D$7처럼 $를 꼭 붙이세요. 입력 중에 범위를 선택하고 F4를 누르면 $가 붙습니다.
자주 막히는 곳
분명 같은 이름인데 "미연락"으로 나올 때
- 이름 뒤에 공백이 붙어 있습니다. 다른 곳에서 복사해 온 명단에 흔합니다. 검증해 보니 A열에
"박지훈 "(끝에 공백 한 칸)이 있으면 결과가미연락이었습니다. 조건 자리를TRIM(A2)로 바꾸면 앞뒤 공백을 지우고 비교해완료가 됩니다. D열 쪽에 공백이 있으면 D열을 먼저 TRIM으로 정리하세요. - 띄어쓰기·표기가 다릅니다. "김 민준"과 "김민준"은 다른 글자로 봅니다. 영문은 대소문자를 구분하지 않습니다(Microsoft 문서 기준).
- 다른 파일(통합 문서)의 명단을 참조했습니다. Microsoft 문서에 따르면 COUNTIF가 다른 통합 문서를 참조할 때는 그 파일이 열려 있어야 하고, 닫혀 있으면
#VALUE!가 납니다. 같은 파일의 다른 시트라면COUNTIF(연락완료!$A$2:$A$7,A2)처럼 시트 이름을 붙이면 됩니다.
동명이인도 조심해야 합니다. 이름만으로 비교하면 같은 이름의 다른 사람이 연락 완료 명단에 있을 때 "완료"로 나옵니다. 명단이 길다면 이름과 전화번호 뒷자리를 &로 붙인 열을 양쪽에 하나씩 만들어 그 열끼리 비교하는 편이 안전합니다.
다른 방법: ISNA(MATCH), 조건부 서식, FILTER
ISNA와 MATCH로 같은 일 하기
=IF(ISNA(MATCH(A2,$D$2:$D$7,0)),"미연락","완료")MATCH(A2,$D$2:$D$7,0)는 A2가 D2:D7에서 몇 번째인지 알려 줍니다. 김민준은 2, 이서연은 6, 박지훈은 1입니다. 없으면#N/A오류를 냅니다. 마지막0은 "정확히 같은 것만"이라는 뜻입니다.ISNA(…)는 안에 든 값이#N/A이면 TRUE, 아니면 FALSE를 돌려줍니다. 최수아는 TRUE입니다.- IF가 TRUE면 "미연락", FALSE면 "완료"를 적습니다. 결과는 COUNTIF 방식과 8명 모두 같았습니다.
COUNTIF 방식은 "몇 번 나오나"를 보여 주므로 중복 확인도 겸할 수 있고, MATCH 방식은 "몇 번째 줄에 있나"를 바로 알려 주므로 INDEX와 이어서 그 사람의 다른 정보를 가져올 때 편합니다.
색으로 표시하기 — 조건부 서식
B열을 따로 두기 싫다면 A2:A9를 선택하고 [홈] → [조건부 서식] → [새 규칙] → "수식을 사용하여 서식을 지정할 셀 결정"을 고르고, 아래 수식을 넣은 뒤 [서식]에서 채우기 색을 고릅니다.
=COUNTIF($D$2:$D$7,$A2)=02단계의 참·거짓 수식을 그대로 쓴 것입니다. 참인 행(최수아·강하은·조현우)에만 색이 칠해집니다.
미연락자만 뽑아 목록으로 — FILTER (Microsoft 365·엑셀 2021 이후)
=FILTER(A2:A9,COUNTIF(D2:D7,A2:A9)=0,"모두 연락함")한 칸에 넣으면 최수아·강하은·조현우가 아래로 쏟아져 나옵니다(분산). 모두 연락했으면 세 번째 인수의 "모두 연락함"이 표시됩니다. 이 인수를 빼면 결과가 없을 때 #CALC! 오류가 납니다. FILTER는 엑셀 2019 이하에는 없으니, 파일을 주고받을 상대의 버전이 다르면 위의 COUNTIF 방식을 쓰세요. 이 수식은 검증 도구가 FILTER를 지원하지 않아 같은 논리를 따로 계산해 확인했고, 함수 동작은 Microsoft 문서 기준입니다.
자주 묻는 질문
COUNTIF와 ISNA(MATCH) 중 어느 쪽을 써야 하나요?
결과는 같습니다. 처음이라면 읽기 쉬운 COUNTIF 방식을 권합니다. 찾은 사람의 다른 정보(연락처 등)까지 이어서 가져올 계획이면 MATCH 방식이 INDEX와 연결하기 편합니다.
명단이 서로 다른 시트에 있어도 되나요?
됩니다. 범위 앞에 시트 이름과 느낌표를 붙여 COUNTIF(연락완료!$A$2:$A$7,A2)처럼 씁니다. 다른 파일을 참조할 때는 그 파일이 열려 있어야 하며, 닫혀 있으면 #VALUE! 오류가 날 수 있습니다.
같은 이름인데 계속 "미연락"으로 나옵니다.
이름 앞뒤에 보이지 않는 공백이 있는 경우가 많습니다. 조건을 TRIM(A2)로 바꾸거나, 두 명단 모두 TRIM으로 공백을 정리한 뒤 비교해 보세요. 띄어쓰기가 다른 "김 민준"과 "김민준"은 다른 이름으로 봅니다.
함께 보면 좋은 글: 엑셀 COUNTIF COUNTIFS 조건 개수 세기, 엑셀 INDEX MATCH 함수 사용법 총정리, 엑셀 TRIM LEN 함수 공백 제거와 글자 수, 엑셀 조건부 서식 사용법 총정리. 목적별 함수 조합 글 전체는 엑셀 목차에서 볼 수 있습니다.
참고: Microsoft 지원 — COUNTIF 함수, Microsoft 지원 — MATCH 함수, Microsoft 지원 — FILTER 함수
댓글
댓글 남기기