- 최초 작성일: 2026-08-30
- 최종 수정일: 2026-08-30
- 조회수: 274 회
- 작성자: 권현욱 (엑셀러)
- 강의 제목: 입력 오류 막아주는 엑셀 수식 4가지 (ft. 데이터 유효성 검사, COUNTIF 함수)
들어가기 전에
회사에서 발주서나 신청서, 명단 같은 걸 관리하다 보면 겪게 되는 문제가 있습니다. 바로 "입력 오류"입니다. 발주번호를 실수로 중복 입력한다거나, 담당부서를 오타로 잘못 적는다거나, 납품 예정일을 발주일보다 빠르게 잘못 입력하는 것처럼 말이죠. 이런 실수들은 사람이 눈으로 검토하면 늦게 발견되거나 아예 못 보고 넘어가는 경우가 많습니다.
Excel의 데이터 유효성 검사에 수식을 걸어두면, 이런 실수를 입력하는 순간에 막을 수 있습니다. 이번 영상에서는 실무에서 많이 쓰이는 데이터 오류 검증 수식 세 가지를 발주서 예제로 같이 해보고, 마지막에 이 세 가지를 한 번에 합치는 응용 방법까지 소개합니다.
- 최신 글을 불러오는 중...
입력 오류 막아주는 엑셀 수식 4가지 (ft. 데이터 유효성 검사, COUNTIF 함수)
발주서, 신청서, 관리대장을 만들다 보면 담당자가 실수로 값을 잘못 입력하는 상황을 피하기 어렵습니다. 사람이 눈으로 하나하나 검토하는 데는 한계가 있습니다. 데이터 유효성 검사에 수식을 걸어서, 입력하는 순간 오류를 막아주는 방법 네 가지를 정리합니다.
이번 영상에서 습득할 수 있는 주요 내용
- 발주번호 중복 입력 방지: COUNTIF 함수로 같은 값이 두 번 이상 입력되는 것을 원천 차단하는 방법
- 마스터 데이터 기반 값 제한: 다른 시트의 목록을 참조해서, 등록되지 않은 담당부서·제품코드 입력을 막는 방법
- 날짜 논리 검증: 같은 행의 다른 셀과 비교해서 앞뒤가 맞지 않는 날짜 입력을 걸러내는 방법
- 여러 조건을 한 번에? 복합 조건 검증 수식으로 마스터 등록 여부·중복 여부·형식까지 동시에 검사하는 방법 (멤버십 전용 풀 버전)
📊 신간 전자책(PDF) · 287쪽 · 아이엑셀러 단독 출간
'표준 3계층 구조'로 엑셀 대시보드를 체계적으로 만들어보세요!
- 3초 안에 읽히는 화면 설계 감각 습득
- Microsoft Excel MVP의 실전 노하우
- 중간마진 없는 합리적인 가격
1. 발주번호 중복 입력 방지
먼저, 발주번호부터 볼까요. '발주서관리대장' 시트의 A열에 PO-2026-0001부터 순서대로 들어가 있습니다. 발주번호를 수작업으로 입력하다가 번호가 중복되면, 나중에 발주 이력을 조회할 때 문제가 생깁니다. 이 문제를 원천적으로 막아보겠습니다.
- '발주번호'가 들어가는 범위(A2:A51)를 선택합니다.
- [데이터] 탭 > [데이터 유효성 검사]를 클릭합니다.
- [설정] 탭에서 '제한 대상'을 [사용자 지정]으로 선택하고, 수식 란에
=COUNTIF($A:$A, A2)=1을 입력합니다. - [오류 메시지] 탭에서 스타일은 "중지"로, 제목은 "중복된 발주번호", 메시지는 "이미 사용 중인 발주번호입니다. 다른 번호를 입력하세요"라고 입력합니다.
$A:$A는 A열 전체를 범위로 고정한 것이고, A2는 상대참조라서 아래로 내려가면서 A3, A4로 자동으로 바뀝니다. COUNTIF로 지금 입력한 값이 A열에 몇 번 나오는지 카운팅해서, 1번이면 통과, 2번 이상이면 막는 수식입니다.
이 방법은 이미 입력돼 있는 기존 데이터에는 영향을 주지 않고, 앞으로 새로 입력하는 값만 검증한다는 점도 참고하시기 바랍니다.
2. 마스터 데이터에 없는 값 차단
'MASTER' 시트에는 '담당부서'와 '제품코드'가 정리돼 있습니다. 이 두 가지는 마음대로 입력하면 안 되는 정보들이므로, MASTER 시트에 등록된 목록 안에서만 골라야 하는 값들입니다. 담당부서는 5개, 제품코드는 PRD-001부터 PRD-030까지 30개 정도가 정리돼 있습니다.
드롭다운을 활용하는 '목록' 방식의 유효성 검사도 가능하지만, 여기서는 수식으로 검증하는 방법을 씁니다. 수식으로 걸어두면 드롭다운 없이 타이핑으로 입력해도 목록에 있는 값인지 자동으로 체크되고, 다른 시트에 있는 목록을 참조할 때 특히 유용합니다.
- '발주서관리대장' 시트의 담당부서 D열, D2:D51 영역을 선택합니다.
- [데이터 유효성 검사]에서 '제한 대상'을 '사용자 지정'으로 선택하고, 수식
=COUNTIF(MASTER!$A$2:$A$6, D2)=1을 입력합니다. - 오류 메시지 제목은 "등록되지 않은 부서", 메시지는 "MASTER 시트에 등록된 담당부서만 입력 가능합니다"로 입력합니다.
다른 시트를 참조할 때는 시트이름!이 앞에 붙는다는 점에 유의하세요(수식을 작성할 때 다른 시트를 선택하면 엑셀이 자동으로 처리해줍니다).
제품코드 E열도 같은 방식으로 설정합니다. E2:E51 영역에 수식 =COUNTIF(MASTER!$C$2:$C$31, E2)=1을 걸고, 오류 메시지 제목을 "등록되지 않은 제품코드"로 입력합니다. 목록에 없는 "PRD-999"를 입력해보면 바로 걸러지는 걸 확인할 수 있습니다.
3. 발주일자보다 빠른 납품예정일 차단 (날짜 논리 검증)
상식적으로 납품예정일이 발주일자보다 빠를 수는 없습니다. 그런데 수작업으로 날짜를 입력하다 보면 이런 실수가 생길 수도 있습니다.
- 납품예정일이 있는 C열, C2:C51을 선택합니다.
- [데이터 유효성 검사]에서 '사용자 지정'을 선택하고, 수식
=C2>=B2를 입력합니다. - 오류 메시지 제목은 "날짜 오류", 메시지는 "납품예정일은 발주일자보다 빠를 수 없습니다"로 입력합니다.
같은 행의 납품예정일(C2)이 발주일자(B2)보다 크거나 같아야 한다는 아주 단순한 조건입니다. 같은 행의 다른 셀을 참조해서 논리적으로 앞뒤가 맞는지 체크하는 것도 데이터 유효성 검사로 충분히 할 수 있습니다. 테스트해보면, 발주일자보다 빠른 날짜를 입력할 때 바로 오류 메시지가 뜹니다.
4. 복합 조건 검증 [멤버십 회원용]
그런데 여기서, "이 세 가지를 한 셀에서 동시에 검증할 수는 없을까?" 궁금하신 분들 있을 겁니다. 예를 들어, (1) 제품코드 하나에 대해서 "마스터리스트에 존재하면서, (2) 이 발주서 안에서 중복도 아니어야 하고, (3) 자릿수 조건까지 맞아야 한다"처럼 한 셀에 여러 조건을 한 번에 걸고 싶다는 말입니다.
이건 AND 함수 안에 다른 함수들을 겹쳐서 쓰는 방식으로 해결할 수 있습니다. 하지만 조건이 여러 개 얽히면 수식이 꼬이기 쉬우므로 순서를 미리 잘 정해두어야 합니다. 이 복합 조건 검증 수식을 만드는 전체 과정은 멤버십 전용 영상에서 설명합니다.
이 영상은 멤버십 회원 전용 콘텐츠입니다.
복합 조건 검증 수식과 테스트 과정 전체는 멤버십 회원만 시청할 수 있습니다. 아직 회원이 아니시라면 아래 버튼으로 가입하고 지금 바로 확인해보세요.
MVP TIP — 검증 수식 만들 때 헷갈리지 않는 법
1. 조건은 "TRUE면 통과, FALSE면 막힌다"로 먼저 말로 정리하세요
무작정 수식부터 작성하려고 하면 헷갈리기 쉽습니다. 어떤 조건이 통과 기준인지 먼저 문장으로 정리한 다음 수식으로 옮기면 훨씬 수월합니다.
2. 복합 조건은 하나씩 따로 검증한 뒤 AND로 합치세요
AND 안에 들어가는 각 조건은 그 자체로 독립적인 TRUE/FALSE 검증식이어야 합니다. 조건 하나하나를 따로 셀에 걸어서 TRUE가 나오는지 먼저 확인한 다음 AND로 합치는 순서로 작업하면 훨씬 덜 헷갈립니다.
자주 묻는 질문 (FAQ)
Q1. 이미 입력되어 있던 기존 데이터에도 유효성 검사가 적용되나요?
아니요. 데이터 유효성 검사는 규칙을 건 이후에 새로 입력하거나 수정하는 값에 대해서만 작동합니다. 기존에 이미 들어있던 값은 규칙을 어겨도 자동으로 걸러지지 않습니다. 기존 데이터까지 확인하고 싶다면, 조건부 서식으로 위반 데이터를 눈에 띄게 표시하는 방법을 함께 쓰는 걸 추천드립니다.
Q2. 다른 사람이 유효성 검사 규칙을 실수로 지울 수도 있나요?
네, 시트 보호가 되어 있지 않으면 셀을 복사/붙여넣기 하는 과정에서 규칙이 사라지는 경우가 많습니다. 여러 사람이 함께 쓰는 양식이라면 유효성 검사와 함께 시트 보호도 걸어두시는 걸 권장합니다.
Q3. COUNTIF 대신 MATCH 함수를 써도 되나요?
됩니다. =ISNUMBER(MATCH(D2, MASTER!$A$2:$A$6, 0)) 같은 방식으로도 동일한 결과를 얻을 수 있습니다. COUNTIF가 좀 더 직관적이라 이번 영상에서는 COUNTIF로 통일해서 설명드렸습니다.
Q4. 복합 조건 검증에서 조건을 더 추가할 수도 있나요?
네, 이 방식은 조건 개수에 제한이 없습니다. 필요하면 네 번째, 다섯 번째 조건도 AND 안에 계속 이어붙일 수 있습니다. 예를 들어 "담당부서가 구매팀일 때만 이 코드 사용 가능" 같은 조건도 얼마든지 추가할 수 있습니다.
마치며
발주서 관리대장을 예제로 실무에서 자주 발생하는 입력 실수 세 가지를 데이터 유효성 검사 수식으로 막아보는 방법을 알아봤습니다. 중복 방지, 마스터 데이터 검증, 날짜 논리 검증. 이 세 가지 패턴, 예제 파일을 받아서 꼭 직접 한 번씩 테스트해 보시기 바랍니다. 여러 조건을 동시에 검증하고 싶으시다면, 멤버십 회원용 풀 버전에서 복합 조건 검증 수식까지 함께 확인해보세요.
