>신청 명단과 제출 명단을 비교해 빠진 번호 찾기

신청한 사람 중 자료를 아직 제출하지 않은 사람을 찾을 때 두 명단을 눈으로 번갈아 읽으면 놓치기 쉽습니다. 신청 번호가 있다면 COUNTIF로 다른 목록에 존재하는지 확인할 수 있습니다. 이름보다 중복 가능성이 낮은 식별값을 사용하는 것이 좋습니다. 아래 수식은 Google 스프레드시트와 Excel에서 똑같이 쓰입니다. 메뉴 이름이 다른 곳만 따로 적었습니다.

작은 두 목록으로 먼저 연습합니다

A2:A4에 P01, P02, P03을 적습니다. D2:D3에는 제출한 번호 P01, P03을 적습니다. B열은 비교 결과를 표시할 빈 열로 둡니다.

1. B2에 =IF(A2="","",IF(COUNTIF($D$2:$D$3,A2)>0,"확인됨","미확인"))을 입력하고 Enter를 누릅니다.
2. B2를 다시 선택하면 셀 오른쪽 아래에 작은 사각형(채우기 핸들)이 보입니다. 이것을 B4까지 아래로 끌어 수식을 복사합니다. Google 스프레드시트에서는 파란색 상자를 끌고, Excel에서는 B2:B4를 선택한 뒤 Ctrl+D를 눌러도 됩니다.
3. P02 행만 미확인으로 나오는지 확인합니다. 수식 안의 $D$2:$D$3처럼 $가 붙은 범위는 아래로 복사해도 바뀌지 않으므로 세 칸이 모두 같은 범위를 봅니다.
4. D4에 P02를 추가합니다. 이때 B열은 아직 D2:D3만 보기 때문에 P02는 여전히 미확인입니다.
5. B2의 수식을 =IF(A2="","",IF(COUNTIF($D$2:$D$4,A2)>0,"확인됨","미확인"))으로 고친 뒤 다시 B4까지 끌어 복사합니다. B2만 고치고 복사를 빠뜨리면 B3·B4는 옛 범위를 그대로 봅니다. 모두 확인됨이 되면 연습이 끝난 것입니다.

결과의 의미를 좁게 해석합니다

‘확인됨’이 제출 내용까지 올바르다는 뜻은 아닙니다. 빈 파일이 제출되었거나 다른 사람 자료일 수 있으므로 내용 검수는 별도입니다. 같은 번호가 여러 번 제출돼도 이 수식은 존재 여부만 표시합니다.

실제 명단에서는 범위를 넉넉하게 잡습니다

제출 명단은 계속 늘어나므로 마지막 행에 맞춰 범위를 고치는 대신 처음부터 여유 있게 잡습니다. 예를 들어 =IF(A2="","",IF(COUNTIF($D$2:$D$500,A2)>0,"확인됨","미확인"))처럼 500행까지 보게 하거나, D열 전체를 보는 $D:$D로 적습니다. D열에 번호 외의 글자(제목 행 등)가 있어도 번호와 같지 않으면 세지 않으므로 결과에 영향이 없습니다.

신청 명단이 길면 B2의 수식을 신청 목록 마지막 행까지 끌어 복사합니다. 중간에 빈 행이 있으면 빈칸으로 표시되므로 명단 끝이 어디인지 먼저 확인하세요. 연락하기 전 미확인 항목 몇 개를 제출 명단에서 Ctrl+F로 직접 검색해 검산하면, 잘못된 범위 때문에 이미 제출한 사람에게 재요청하는 일을 줄일 수 있습니다.

미확인 행만 모아서 봅니다

Google 스프레드시트에서는 제목 행을 포함해 A1:B열 끝까지 선택한 뒤 데이터 > 필터 만들기를 누릅니다. B열 제목에 생긴 필터 아이콘을 누르고 값 목록에서 ‘확인됨’과 빈칸의 체크를 해제한 뒤 확인을 누르면 미확인 행만 남습니다. 다 본 뒤에는 데이터 > 필터 삭제로 원래 화면으로 돌아옵니다.

Excel에서는 범위 안의 셀 하나를 선택하고 데이터 > 필터를 누릅니다. B열 머리글의 화살표를 눌러 ‘미확인’만 체크하고 확인을 누릅니다. 필터를 끄려면 데이터 탭의 필터 단추를 다시 누릅니다. 남은 행을 다른 시트에 복사해 연락 명단으로 쓰고, 원본 시트의 수식은 그대로 둡니다.

형식이 달라지지 않았는지 봅니다

번호 앞뒤 공백이나 앞자리 0 누락이 있으면 실제로 같은 신청이라도 다르게 비교될 수 있습니다. 공백은 TRIM으로 정리합니다. 빈 열(예: E열)의 E2에 =TRIM(D2)를 넣고 아래로 복사하면 앞뒤 공백이 지워진 번호가 나오며, 수식의 비교 범위를 $E:$E로 바꾸면 됩니다. Google 스프레드시트의 TRIM은 앞뒤 공백과 반복 공백을 지우고, Excel의 TRIM은 단어 사이의 공백 하나만 남기고 모두 지웁니다. 복사해 붙인 번호가 숫자로 바뀌어 앞자리 0이 사라졌다면 원본과 대조해 텍스트 형식으로 다시 맞춥니다.

COUNTIF는 대소문자를 구분하지 않으므로 대소문자가 의미 있는 코드는 별도 비교 방법이 필요합니다. 또 조건 안의 *와 ?는 와일드카드로 읽혀 각각 임의의 여러 글자와 한 글자에 일치합니다. 식별번호에 이런 문자가 섞여 있으면 결과가 틀릴 수 있으니, 실제 별표나 물음표를 찾아야 할 때는 앞에 ~를 붙입니다.

2026년 10월 13일 보완: 수식 복사(채우기 핸들)와 범위 수정 순서를 정확히 적고, 실제 명단용 범위 잡기, 미확인 행 필터, TRIM으로 공백 정리, 와일드카드 주의를 더했습니다.