엑셀 참석자 명단 대조 COUNTIF 0 나옴? 신청자 참석여부 자동 표시 초보 체크법
엑셀 참석자 명단 대조를 하다 보면 분명히 이름도 있고 사번도 있는데 COUNTIF 결과가 0으로 나오거나, 참석여부가 전부 미참석으로 표시되는 경우가 있습니다. 특히 교육 신청자 명단과 실제 참석자 명단을 비교할 때 초보자가 가장 많이 헷갈리는 부분은 “어느 범위를 세고, 어떤 값을 찾는지”입니다.
이번 글에서는 신청자 목록 옆에 참석여부를 자동으로 표시하는 가장 기본적인 방법을 다룹니다. 어려운 기능 없이 COUNTIF와 IF만으로 시작하고, 사번 앞자리 0 때문에 결과가 안 맞을 때 TEXT로 비교용 사번을 만드는 방법까지 차근차근 정리해 보겠습니다.
어떤 상황에서 쓰는 수식일까?
예를 들어 사내 교육 신청자 명단과 당일 입장 기록이 따로 있다고 가정하겠습니다. 신청자 명단은 A열부터 D열까지 있고, 실제 참석자 명단은 F열부터 H열까지 있습니다.
여기서 우리가 하고 싶은 일은 간단합니다. 신청자 사번이 실제 참석자 사번 목록에 있으면 D열에 “참석”, 없으면 “미참석”이라고 자동으로 표시하는 것입니다.
| 열 | 위치 | 내용 | 설명 |
|---|---|---|---|
| A열 | A3:A10 | 신청일 | 교육 신청 날짜 |
| B열 | B3:B10 | 사번 | 신청자 사번 |
| C열 | C3:C10 | 이름 | 신청자 이름 |
| D열 | D3:D10 | 참석여부 | 수식 결과 표시 |
| F열 | F3:F9 | 입장시간 | 실제 입장 기록 |
| G열 | G3:G9 | 사번 | 실제 참석자 사번 |
| H열 | H3:H9 | 이름 | 실제 참석자 이름 |
작은 예시 데이터는 아래처럼 놓고 시작해 보겠습니다. 제목 행은 3행이고, 실제 데이터는 4행부터 입력되어 있다고 생각하면 됩니다.
| A 신청일 | B 사번 | C 이름 | D 참석여부 | F 입장시간 | G 사번 | H 이름 |
|---|---|---|---|---|---|---|
| 7/1 | 10001 | 김도현 | 09:02 | 10001 | 김도현 | |
| 7/1 | 10002 | 이서연 | 09:05 | 10003 | 박민준 | |
| 7/1 | 10003 | 박민준 | 09:11 | 10005 | 최지우 | |
| 7/1 | 10004 | 정하늘 | 09:20 | 10006 | 오현우 | |
| 7/1 | 10005 | 최지우 |
COUNTIF는 무엇을 세는 함수일까?
COUNTIF는 “지정한 범위에서 조건에 맞는 값이 몇 개 있는지 세는 함수”입니다. 참석여부 확인에서는 참석자 사번 목록인 G4:G9 범위 안에 신청자 사번 B4가 있는지 세면 됩니다.
COUNTIF의 기본 모양은 아래와 같습니다.
=COUNTIF(찾을범위, 찾을값)여기서 초보자가 가장 많이 헷갈리는 부분은 찾을범위와 찾을값의 방향입니다. 지금은 신청자 B4를 참석자 목록 G4:G9에서 찾는 상황이므로, 찾을범위는 G4:G9이고 찾을값은 B4입니다.
D4 셀에 먼저 넣을 기본 수식
D4 셀을 클릭한 뒤 아래 수식을 입력해 보세요. 그런 다음 D10까지 아래로 복사하면 됩니다.
=IF(COUNTIF($G$4:$G$9,B4)>0,"참석","미참석")수식 뜻을 말로 풀면 이렇습니다. G4부터 G9까지의 실제 참석자 사번 목록에서 B4 사번을 찾아보고, 1개 이상 있으면 참석, 하나도 없으면 미참석이라고 표시합니다.
여기서 $G$4:$G$9처럼 달러 표시가 붙은 이유도 중요합니다. D4 수식을 D5, D6으로 복사할 때 참석자 목록 범위는 계속 G4:G9로 고정되어야 하기 때문입니다. 반대로 B4는 B5, B6처럼 한 줄씩 내려가며 바뀌어야 하므로 달러 표시를 붙이지 않습니다.
왜 분명히 있는데 미참석으로 나올까?
실무 파일에서는 수식이 맞아도 결과가 틀려 보일 때가 많습니다. 대부분은 함수가 틀린 게 아니라, 비교하는 값의 모양이 서로 다르기 때문입니다.
대표적으로 사번 앞에 0이 있는 경우가 있습니다. 예를 들어 신청자 명단에는 00123으로 보이는데, 참석자 명단에는 123으로 들어가 있으면 사람이 보기에는 같은 사번처럼 느껴집니다. 하지만 엑셀에서는 텍스트 00123과 숫자 123을 다르게 처리할 수 있어 COUNTIF 결과가 0으로 나올 수 있습니다.
결과가 이상할 때 먼저 보는 체크리스트
| 확인할 것 | 증상 | 확인 방법 |
|---|---|---|
| 찾을범위 방향 | 전부 미참석 | COUNTIF 첫 번째 칸이 참석자 사번 범위인지 확인 |
| 범위 고정 | 아래로 갈수록 결과가 이상함 | $G$4:$G$9처럼 달러 표시가 있는지 확인 |
| 사번 앞자리 0 | 있는 사번인데 0으로 나옴 | 00123과 123이 섞였는지 확인 |
| 이름으로 비교 | 동명이인 때문에 잘못 표시 | 가능하면 이름보다 사번으로 비교 |
| 빈칸 포함 | 쓸데없는 참석 처리 | 참석자 사번 범위에 빈칸이나 잘못된 값 확인 |
사번 앞자리 0이 섞이면 TEXT로 비교용 사번 만들기
사번이 항상 5자리라고 가정해 보겠습니다. 신청자 사번과 참석자 사번을 둘 다 5자리 텍스트 모양으로 맞춘 다음 비교하면 훨씬 안전합니다.
먼저 E열을 비교용 사번으로 사용하겠습니다. E3 셀에는 “신청비교사번”이라고 입력하고, E4 셀에는 아래 수식을 넣습니다.
=TEXT(B4,"00000")그리고 I열도 비교용 사번으로 사용합니다. I3 셀에는 “참석비교사번”이라고 입력하고, I4 셀에는 아래 수식을 넣습니다.
=TEXT(G4,"00000")각각 아래 행까지 복사합니다. 그러면 123처럼 들어간 사번도 00123처럼 5자리 형태로 맞춰집니다. 이제 D4의 참석여부 수식은 G열이 아니라 I열의 비교용 사번을 기준으로 바꾸면 됩니다.
=IF(COUNTIF($I$4:$I$9,E4)>0,"참석","미참석")이 방식은 초보자에게 특히 좋습니다. 원본 사번을 바로 고치지 않고, 옆에 비교용 열을 만들어 확인할 수 있기 때문입니다. 원본 데이터가 다른 부서에서 받은 파일이라 수정하기 부담스러울 때도 안전합니다.
참석자 수와 미참석자 수까지 바로 집계하기
참석여부가 D열에 표시되면 집계는 훨씬 쉬워집니다. 예를 들어 K3 셀에 “참석”, K4 셀에 “미참석”이라고 적고, L열에 건수를 표시해 보겠습니다.
| K열 | L열 |
|---|---|
| 구분 | 건수 |
| 참석 | |
| 미참석 |
L4 셀에는 참석 인원을 세는 수식을 넣습니다.
=COUNTIF($D$4:$D$10,K4)L5 셀에는 L4 수식을 아래로 복사하면 됩니다. K5에 미참석이라고 적혀 있으므로, 미참석 건수가 자동으로 계산됩니다.
여기서도 $D$4:$D$10은 고정 범위입니다. 집계 기준은 K4, K5처럼 내려가며 바뀌어야 하므로 K4에는 달러 표시를 붙이지 않아도 됩니다.
이름으로 비교하면 안 될까?
사번이 없는 파일이라면 이름으로 비교할 수도 있습니다. 예를 들어 신청자 이름 C4가 실제 참석자 이름 H4:H9에 있는지 확인하려면 아래처럼 쓸 수 있습니다.
=IF(COUNTIF($H$4:$H$9,C4)>0,"참석","미참석")다만 이름 비교는 동명이인이 있으면 위험합니다. 김민수라는 사람이 두 명인데 한 명만 참석했을 경우, 둘 다 참석으로 표시될 수 있습니다. 실무에서는 가능하면 사번, 회원번호, 신청번호처럼 사람마다 고유한 값을 기준으로 비교하는 것이 좋습니다.
초보자가 자주 하는 실수는 여기서 나온다
첫 번째 실수는 범위를 한 칸만 잡는 것입니다. COUNTIF($G$4,B4)처럼 입력하면 G4 한 칸에서만 찾게 됩니다. 참석자 전체 목록을 확인하려면 반드시 $G$4:$G$9처럼 여러 행 범위를 잡아야 합니다.
두 번째 실수는 수식을 복사했을 때 범위가 밀리는 것입니다. D4에서는 G4:G9를 잘 보고 있었는데 D5로 복사하니 G5:G10을 보게 되면, 위쪽 참석자 한 명이 비교 범위에서 빠집니다. 그래서 참석자 목록 범위에는 달러 표시를 붙여 고정합니다.
세 번째 실수는 표시 형식만 바꾸고 값이 바뀌었다고 생각하는 것입니다. 셀 서식에서 00000처럼 보이게 만들었다고 해서 실제 값까지 항상 똑같아지는 것은 아닙니다. 비교가 계속 안 맞는다면 TEXT로 비교용 값을 만들어 양쪽 모양을 맞추는 편이 확인하기 쉽습니다.
적용 전에 확인할 것
신청자 명단과 참석자 명단을 대조할 때는 함수보다 기준 열을 먼저 정하는 것이 중요합니다. 이름보다 사번이 안전하고, 사번도 앞자리 0이 섞여 있다면 비교용 사번을 만들어 맞춘 뒤 COUNTIF로 확인하는 순서가 좋습니다.
처음에는 D4 한 셀에만 수식을 넣고 결과가 맞는지 확인하세요. 그다음 아래로 복사하고, 마지막에 COUNTIF로 참석·미참석 건수를 집계하면 실수가 훨씬 줄어듭니다. 파일이 커져도 원리는 같습니다. “어디에서 찾을지”, “무엇을 찾을지”, “범위가 복사 중에 움직이지 않는지” 이 세 가지만 잡으면 참석자 명단 대조는 어렵지 않습니다.