두 명단 대조 작업, 매번 눈으로 하고 계셨나요

신입사원 명단과 교육 참석자 명단을 대조해서 누가 빠졌는지 찾아야 하는 일이 종종 있었습니다. 명단이 열 명 안팎이면 눈으로 훑어봐도 되는데, 백 명, 이백 명 단위로 넘어가면 얘기가 달라집니다. 한 명이라도 놓치면 나중에 "저는 왜 안 챙겨주셨어요"라는 얘기를 듣게 되는데요. 이럴 때 COUNTIFS와 고급 필터를 조합하면 두 명단의 일치, 불일치를 정확하게 뽑아낼 수 있습니다.

COUNTIFS로 다른 시트에 존재하는지 확인하기

가장 간단한 방법부터 보겠습니다. 앞서 다룬 COUNTIFS는 조건에 맞는 개수를 세는 함수인데, 이걸 응용하면 "이 이름이 다른 시트에도 있는지"를 확인할 수 있습니다.

실무 예시: 신입사원 명단에서 교육 미참석자 찾기

전체 신입사원 명단이 A시트에, 교육 참석자 명단이 B시트에 있다고 가정해보겠습니다. A시트 옆 칸에 이런 수식을 넣습니다.

 

=COUNTIF(B시트!A:A, A2)

A2에 있는 이름이 B시트의 A열에 몇 번 나오는지 세어줍니다. 결과가 0이면 그 사람은 B시트(교육 참석자 명단)에 없다는 뜻이니, 미참석자라는 걸 바로 알 수 있습니다. 결과가 1 이상이면 참석했다는 뜻입니다.

이 값을 그대로 두기보다, IF와 조합해서 보기 좋게 만드는 경우가 많습니다.

 

=IF(COUNTIF(B시트!A:A, A2)=0, "미참석", "참석")

이렇게 하면 숫자 대신 "미참석", "참석"이라는 텍스트로 바로 확인할 수 있습니다.

조건이 여러 개라면 COUNTIFS

이름만으로 대조하면 동명이인 때문에 잘못 판단될 수 있습니다. 이름과 사번을 같이 확인하고 싶다면 COUNTIFS로 조건을 두 개 겁니다.

 

=COUNTIFS(B시트!A:A, A2, B시트!B:B, B2)

이름(A열)과 사번(B열)이 둘 다 일치하는 행이 있는지 확인합니다. 이렇게 하면 이름이 같은 사람이 여러 명 있어도 정확하게 구분됩니다.

고급 필터로 조건에 맞는 데이터만 따로 뽑아내기

COUNTIFS는 각 행마다 일치 여부를 표시해주는 방식이라면, 고급 필터는 아예 조건에 맞는 행들만 따로 추려서 새로운 위치에 뽑아주는 기능입니다. 결과를 별도의 명단으로 만들어야 할 때 더 편리합니다.

설정 순서

1. 원본 데이터 범위를 확인합니다.
2. 별도의 영역에 조건을 적어둡니다. 조건 표의 항목명은 원본 데이터의 항목명과 정확히 똑같아야 합니다.
3. [데이터] 탭 → [고급]을 클릭합니다.
4. '다른 장소에 복사'를 선택하고, 목록 범위와 조건 범위, 결과가 나올 위치를 각각 지정합니다.

실무 예시: 미참석자 명단만 따로 뽑기

앞서 COUNTIFS로 "미참석" 여부를 표시해뒀다면, 이 값을 조건으로 걸어서 고급 필터를 실행하면 미참석자만 별도의 표로 뽑아낼 수 있습니다. 조건 표에는 "미참석 여부" 항목명 아래에 "미참석"이라고 적어두고, 이걸 조건 범위로 지정하면 됩니다.

명단이 수백 명이어도 몇 초 만에 미참석자만 깔끔하게 정리된 표가 새로 만들어집니다.

두 방법을 언제 나눠 써야 할까

상황추천 방법

원본 명단은 그대로 두고, 옆에 일치 여부만 표시하고 싶다	COUNTIFS
조건에 맞는 사람들만 따로 뽑아서 별도 명단을 만들고 싶다	고급 필터
조건이 자주 바뀌면서 반복적으로 확인해야 한다	COUNTIFS (수식이라 자동 갱신됨)
한 번 뽑아서 다른 곳에 보고서로 첨부해야 한다	고급 필터

실무에서는 이 두 가지를 같이 쓰는 경우가 많습니다. COUNTIFS로 먼저 일치 여부를 표시해두고, 그 결과를 조건으로 걸어서 고급 필터로 최종 명단만 뽑아내는 식입니다.

자주 하는 실수 정리

• 비교 기준을 이름 하나로만 잡아서 동명이인을 놓치는 경우: 가능하면 사번이나 생년월일처럼 고유한 값을 조건에 하나 더 추가하는 게 안전합니다.
• 조건 표의 항목명이 원본과 미세하게 다른 경우: 고급 필터는 항목명이 정확히 일치해야 조건으로 인식합니다. 띄어쓰기 하나만 달라도 조건이 적용되지 않습니다.
• COUNTIF 범위를 열 전체가 아니라 일부만 지정해서 새로 추가된 데이터가 누락되는 경우: 명단이 계속 늘어나는 상황이라면, 처음부터 열 전체(A:A)로 범위를 잡아두는 게 안전합니다.
• 고급 필터에서 '현재 위치에 필터'와 '다른 장소에 복사'를 헷갈리는 경우: 원본 목록을 그대로 두고 결과만 따로 보고 싶다면 반드시 '다른 장소에 복사'를 선택해야 합니다.

마치며

두 명단을 눈으로 대조하는 작업은 사람이 하기엔 실수가 나올 수밖에 없는 일입니다. COUNTIFS와 고급 필터를 조합하면 몇 백 명 단위의 명단도 몇 초 만에 정확하게 대조할 수 있으니, 정기적으로 명단을 비교하는 업무를 하신다면 꼭 익혀두시길 추천드립니다. 다음 편에서는 기본 함수와 수식만으로는 처리하기 어려운 상황에서 쓰는 배열 수식을 다뤄보겠습니다.