여러 사람이 함께 쓰는 엑셀 파일은 입력 방식이 제각각이다. 같은 거래처가 A사, A 사, a사로 들어오면 집계가 갈라지고 뒤늦게 정리하느라 시간이 든다.
데이터 유효성 검사는 입력 단계에서 허용 값을 제한해 이런 오류를 미리 막는다. 이 글에서는 드롭다운 목록, 숫자와 날짜 제한, 중복 입력 방지, 그리고 이 기능의 한계까지 정리한다.
1. 드롭다운 목록 만들기
입력 칸으로 쓸 범위를 선택하고 데이터 탭의 데이터 유효성 검사를 연다. 제한 대상을 목록으로 바꾸고 원본에 항목을 입력하면 셀 옆에 선택 화살표가 생긴다. 항목이 적으면 쉼표로 직접 쓰고, 많거나 자주 바뀌면 별도 범위를 참조한다.
| 방식 | 원본 입력 | 특징 |
|---|---|---|
| 직접 입력 | 충분,보통,부족 | 항목이 적을 때 간편하며 수정은 설정 창에서 한다 |
| 범위 참조 | =$H$2:$H$10 | 목록 범위만 고치면 드롭다운이 갱신된다 |
| 이름 정의 | =거래처목록 | 시트가 달라도 관리하기 쉽다 |
2. 숫자와 날짜 범위 제한
제한 대상에서 정수, 소수점, 날짜, 시간, 텍스트 길이를 고르면 허용 범위를 정할 수 있다. 출고수량 칸에 0이나 음수, 소수가 들어가는 실수를 막을 때 유용하다.
| 제한 대상 | 조건 예 | 막을 수 있는 오류 |
|---|---|---|
| 정수 | 1 이상 9999 이하 | 소수점이나 음수 입력 |
| 날짜 | 이번 달 1일 이후 | 과거 날짜나 잘못된 연도 입력 |
| 텍스트 길이 | 정확히 6자 | 품목 코드 자릿수 오류 |
3. 사용자 지정으로 중복 입력 방지
제한 대상을 사용자 지정으로 바꾸고 수식 칸에 =COUNTIF($A:$A,A2)=1을 입력하면 A열에 이미 있는 코드는 다시 입력할 수 없다. 오류 메시지 탭에서 스타일을 중지로 두면 입력 자체가 거부되고, 경고나 정보로 바꾸면 안내만 보여 주고 입력은 허용한다.
| 스타일 | 동작 | 쓰임 |
|---|---|---|
| 중지 | 잘못된 입력을 거부 | 반드시 지켜야 하는 규칙 |
| 경고 | 진행 여부를 물음 | 예외가 있을 수 있는 규칙 |
| 정보 | 안내만 표시 | 참고용 안내 |
4. 한계와 보완 방법
유효성 검사에는 분명한 한계가 있다. 다른 셀을 복사해 붙여넣으면 검사 규칙이 무시되거나 규칙 자체가 덮어써질 수 있고, 규칙을 만들기 전에 이미 입력된 값은 검사 대상이 아니다. 기존 데이터의 오류는 데이터 유효성 검사 메뉴의 잘못된 데이터 표시 기능으로 확인할 수 있다.
5. 연동 드롭다운 만들기
거래처를 고르면 그 거래처의 품목만 다음 칸 목록에 나오게 하는 연동 드롭다운도 자주 쓴다. 거래처별 품목 목록을 각각 이름으로 정의해 두고, 두 번째 칸의 원본에 INDIRECT 함수로 첫 번째 칸의 값을 이름으로 불러오면 된다. 이름은 공백이 없어야 INDIRECT가 정상적으로 인식한다.
| 단계 | 작업 | 내용 |
|---|---|---|
| 1 | 거래처별 품목 목록 이름 정의 | A사, B사 등 거래처명과 같은 이름으로 범위를 정의 |
| 2 | 첫 번째 칸 드롭다운 | 원본에 거래처 목록 범위 지정 |
| 3 | 두 번째 칸 드롭다운 | 원본에 =INDIRECT(B2) 입력(B2는 첫 번째 칸) |
입력 규칙을 정리해 시트 상단에 짧게 적어 두면 규칙을 모르는 사람도 실수 없이 입력할 수 있다. 규칙은 많을수록 좋은 것이 아니라 오류가 자주 나는 열에 집중해서 거는 것이 관리하기 쉽다.
핵심정리
- 드롭다운 목록은 별도 시트의 범위나 이름 정의를 참조한다.
- 숫자, 날짜, 글자 수 제한으로 형식 오류를 막는다.
- COUNTIF 사용자 지정 수식으로 중복 입력을 방지한다.
- 붙여넣기와 기존 값은 검사되지 않으므로 시트 보호와 잘못된 데이터 표시로 보완한다.
막히는 부분이 있다면 댓글로 남겨 주면 예제로 다루겠다.
이 글의 표와 수식 예제에 쓰인 데이터는 설명을 위해 만든 가상 예시입니다.
