누락·중복 검증표 만들기: 누락된 행과 중복된 행을 한 시트에서 볼 수 있나요? | 클로드 엑셀왕 17

이 장에서 다루는 것

엑셀왕 TIP 행 수 대신 받아야 할 목록부터

빠진 것을 찾으려면 무엇이 있어야 하는지부터 정합니다. 날짜와 지점(또는 담당자·현장)을 곱한 목록에서 휴무를 빼면 받아야 할 보고 목록이 되고, 그 목록의 칸마다 실제로 들어온 수를 세면 누락과 중복이 한 번에 보입니다.

클로드에서는 클로드 대화창에 파일 올리기

Claude 도움말 ‘파일 업로드’(2026년 10월 31일 확인)에 따르면 채팅에서는 한 파일에 500MB까지, 한 채팅에 파일 20개까지 올릴 수 있고 CSV와 XLSX를 지원합니다. XLSX를 올리려면 코드 실행과 파일 생성 기능이 켜져 있어야 하며, PDF가 아닌 문서에서는 글자만 읽습니다. 파일 생성 기능이 켜져 있으면 요청에 따라 수식이 든 새 엑셀 파일을 만들어 내려받게 해 줍니다.

올린 파일의 내용은 대화에 담깁니다. 지점별 매출과 작성자 이름이 든 보고는 회사 내부 자료이므로 실제 파일을 올리기 전에 회사의 정보 보안 규정과 AI 도구 사용 규정을 먼저 확인합니다. 예제는 가상 자료만 씁니다. 같은 요청을 다시 보내도 결과가 똑같이 나온다는 보장은 없습니다. 이 글의 답변은 재현이라 실제 답과 다를 수 있으니, 받은 파일은 STEP 5의 검산 시트로 확인합니다.

이렇게 말하기

지점 세 곳이 보낸 10월 일일 매출 보고를 모은 파일이야. 빠진 보고와 겹친 보고를 한 시트에서 보고 싶어. 만들기 전에 확인할 것부터 알려 줘.

답에 들어가야 할 내용

· 날짜와 지점을 짝지어 보고가 없는 날을 찾고, 하늘점처럼 같은 요일마다 비는 경우는 휴무일 수 있다고 따로 묻습니다.

· 보고일 칸에서 글자로 적힌 날짜를 찾고, 같은 날·같은 지점 보고가 두 번 이상인 행을 셀 위치와 함께 보여 줍니다.

· 중복 가운데 모든 칸이 같은 행과 금액이 다른 행을 나눠, 금액이 다른 쪽은 어느 것을 쓸지 묻습니다.

이렇게 답하기

하늘점은 매주 월요일이 정기 휴무야. 휴무는 따로 시트로 만들어서 받아야 할 보고에서 빼 줘. 같은 날·같은 지점 보고가 여러 건이면 제출시각이 가장 늦은 것을 써 줘. 역앞점 21일은 지점에 물어보니 다음 날 아침에 보낸 1,248,000원이 맞대. 받은 원본 칸은 고치지 말고 옆에 도움 열을 더해 줘.

엑셀왕 노트 수정본은 지점에 물어보고 정하기

금액이 다른 두 보고는 어느 쪽이 맞는지 파일만 보고는 알 수 없습니다. 이 사례에서는 늦게 보낸 쪽이 맞았지만, 숫자를 잘못 고쳐 보낸 수정본일 수도 있습니다. 두 금액은 36,000원 차이가 나므로, 지점 판매 기록과 대조해 어느 쪽인지 정합니다. ‘늦게 낸 것을 쓴다’는 기준은 확인을 마친 뒤에 수식에 맡깁니다.

이렇게 말하기

10월 날짜를 세로로, 지점을 가로로 놓은 점검표를 만들어 줘. 칸마다 그날 그 지점에서 받은 보고 수를 세고, 0은 빨강, 2 이상은 주황, 휴무는 회색으로 칠해 줘. 누락과 중복만 날짜순으로 모은 목록, 한 날·한 지점에 한 행만 남긴 정리본, 지점별 합계 시트도 만들어 줘.

답에 들어가야 할 내용

· 원본 옆에 도움 열을 더합니다. 글자 날짜를 날짜로 바꾼 열, 날짜와 지점을 이은 키 열, 같은 키의 행 수, 정리본에 쓸지 뺄지 적는 열, 정리본 순번 열입니다.

· 점검표 칸은 휴무면 ‘휴무’, 아니면 원본의 키 열에서 COUNTIF로 그날 그 지점의 보고 수를 셉니다.

· 받아야 할 보고 목록(기대목록 시트)에 상태(정상·누락·중복·휴무)를 붙이고, 누락·중복 시트는 그 목록에서 INDEX·MATCH로 뽑아 옵니다. 정리본은 ‘사용’ 표시가 붙은 행만 순서대로 가져옵니다.

수식 읽기 원본 H64·J64

=TEXT(G64,"yyyy-mm-dd")&"|"&TRIM(B64)

=IF(AND(COUNTIFS($H$2:$H$91,H64,$E$2:$E$91,">"&E64)=0,COUNTIFS($H$1:H63,H64,$E$1:E63,E64)=0),"사용","제외")

H열은 날짜와 지점을 ‘2026-10-21|역앞점’처럼 이은 키입니다. TRIM은 지점 이름 앞뒤의 공백을 지우고, 단어 사이의 공백은 한 칸으로 줄입니다.

J열의 첫 COUNTIFS는 같은 키 가운데 이 행보다 늦게 낸 보고가 없는지, 둘째 COUNTIFS는 이 행 위에 키와 제출시각까지 똑같은 행이 없는지 봅니다. 두 조건이 모두 맞으면 ‘사용’입니다. 한 줄로 읽으면 “같은 날·같은 지점 보고 가운데 가장 늦게 낸 한 건”이고, 64행은 다음 날 09:30에 낸 수정본이라 ‘사용’, 61행은 ‘제외’가 됩니다.

이렇게 말하기

정리본까지 만들었으면 월 합계를 확정하기 전에 내가 확인할 것을 알려 줘. 지점에 보낼 요청은 아직 쓰지 마.

이렇게 말하기

점검이 맞는지 검산 시트에 열다섯 줄로 확인해 줘. 원본 행 수, 받아야 할 칸·휴무 칸·받아야 할 보고, 누락·중복된 날·뺀 행·정리본 행 수, 받은 행 = 정리본 + 뺀 행, 받아야 할 보고 = 정리본 + 누락, 점검표 숫자 합, 정리본 안의 겹침, 휴무일 보고, 받은 그대로와 정리본의 매출 합계를 내가 불러 주는 기대값과 비교해 줘. 결과는 맨 앞 요약 시트에 모아 줘.

답에 들어가야 할 내용

· 행 수와 매출 합계를 미리 따로 센 기대값과 비교합니다(예제 파일에서는 파이썬으로 센 값이며, 내 파일에서는 손으로 세거나 다른 방법으로 구해 넣습니다).

· 두 등식이 맞는지 봅니다. 두 식이 맞으면 점검표에서 빠뜨리거나 두 번 센 보고가 없다는 뜻입니다.

· 차이를 계산해 모두 0이면 종합을 ‘일치’로 표시합니다.

이렇게 말하기

[STEP 1] 지점 세 곳이 보낸 10월 일일 매출 보고를 모은 파일이야. 빠진 보고와 겹친 보고를 한 시트에서 보고 싶어. 만들기 전에 확인할 것부터 알려 줘.

[STEP 2] 하늘점은 매주 월요일이 정기 휴무야. 휴무는 따로 시트로 만들어서 받아야 할 보고에서 빼 줘. 같은 날·같은 지점 보고가 여러 건이면 제출시각이 가장 늦은 것을 써 줘. 역앞점 21일은 지점에 물어보니 다음 날 아침에 보낸 1,248,000원이 맞대. 받은 원본 칸은 고치지 말고 옆에 도움 열을 더해 줘.

[STEP 3] 10월 날짜를 세로로, 지점을 가로로 놓은 점검표를 만들어 줘. 칸마다 그날 그 지점에서 받은 보고 수를 세고, 0은 빨강, 2 이상은 주황, 휴무는 회색으로 칠해 줘. 누락과 중복만 날짜순으로 모은 목록, 한 날·한 지점에 한 행만 남긴 정리본, 지점별 합계 시트도 만들어 줘.

[STEP 4] 정리본까지 만들었으면 월 합계를 확정하기 전에 내가 확인할 것을 알려 줘. 지점에 보낼 요청은 아직 쓰지 마.

[STEP 5] 점검이 맞는지 검산 시트에 열다섯 줄로 확인해 줘. 원본 행 수, 받아야 할 칸·휴무 칸·받아야 할 보고, 누락·중복된 날·뺀 행·정리본 행 수, 받은 행 = 정리본 + 뺀 행, 받아야 할 보고 = 정리본 + 누락, 점검표 숫자 합, 정리본 안의 겹침, 휴무일 보고, 받은 그대로와 정리본의 매출 합계를 내가 불러 주는 기대값과 비교해 줘. 결과는 맨 앞 요약 시트에 모아 줘.

이 장의 핵심

행 수로 세면 빠진 날과 겹친 날이 서로를 가립니다. 받아야 할 목록(날짜 × 보낸 곳 − 휴무)을 만든 뒤 칸마다 들어온 수를 세면 0은 누락, 2 이상은 중복으로 보입니다.

[중복 제거]를 모든 열을 선택해 실행하면 셀에 보이는 내용이 모두 같은 행 가운데 첫 행만 남기고 지웁니다. 글자로 적힌 날짜나 금액·제출시각이 다른 수정본은 남으므로, 같은 날·같은 곳을 묶은 키로 셉니다.

휴무 여부와 금액이 다른 수정본은 사람이 지점 일정과 판매 기록으로 확인해 기준을 정하고, 받은 원본 칸은 고치지 않습니다. 받은 행 = 정리본 + 뺀 행, 받아야 할 보고 = 정리본 + 누락, 두 식이 맞는지 본 뒤 합계를 확정합니다.

답 예시

(11월_지점별_일일보고.xlsx 첨부) 11월 지점별 일일 보고를 모은 파일이야. 10월과 같은 기준(하늘점 월요일 휴무, 같은 날 보고가 여럿이면 가장 늦게 낸 것)으로 점검표, 누락·중복 목록, 정리본, 검산 시트를 만들어 줘. 만들기 전에 10월과 다른 표기나 이상한 행이 있으면 셀 위치와 함께 먼저 알려 줘.

행 수가 맞아도 점검표는 만들어야 합니다. 이 파일에는 하늘점 이름이 ‘하늘 점’으로 가운데가 띄어 적힌 행이 셋(11월 4~6일) 있습니다. TRIM은 단어 사이의 한 칸 띄어쓰기는 그대로 두므로 ‘하늘 점’은 남고, 그대로 두면 점검표에 하늘점 4~6일이 누락으로 나오고 검산은 ‘확인 필요’가 됩니다. 도움 열에서 SUBSTITUTE로 가운데 공백까지 지워 ‘하늘점’으로 맞추면 남는 것은 두 건입니다. 본점 11월 11일 누락, 그리고 역앞점 11월 20일 보고 두 번(1,158,000원, 다음 날 아침 1,068,000원)입니다. 하루 빠지고 하루 겹쳐 행 수가 맞아 보였습니다. 다음 날 아침의 금액이 맞다고 확인되면 정리본은 84행, 합계는 받은 보고 기준 109,865,000원입니다.