입력 오류, 사람 탓만 할 게 아니었다

명단 파일을 취합하다 보면 날짜 칸에 문자를 입력했거나, 숫자 칸에 음수가 들어가 있는 경우를 심심찮게 마주칩니다. 매번 "이거 다시 확인해주세요"라고 되돌려 보내는 것도 일이고, 애초에 그런 값이 안 들어가게 막아두면 서로 편하겠다는 생각이 들었습니다. 이전 글에서 목록으로 입력을 제한하는 방법을 잠깐 다뤘는데, 오늘은 유효성 검사를 조금 더 깊이 들어가서 정리해보겠습니다.

유효성 검사가 하는 일

유효성 검사는 셀에 입력할 수 있는 값의 종류나 범위를 미리 정해두는 기능입니다. 정해진 조건을 벗어난 값을 입력하려고 하면 경고창이 뜨면서 입력 자체를 막아줍니다. 데이터가 들어오는 입구에서부터 걸러내는 방식이라, 나중에 정리하는 수고를 크게 줄여줍니다.

[데이터] 탭 → [데이터 유효성 검사]에서 설정할 수 있고, '제한 대상' 항목에서 어떤 종류의 값만 허용할지 고를 수 있습니다.

정수 범위로 제한하기

나이, 근무일수, 재고 수량처럼 특정 범위 안의 정수만 입력되어야 하는 칸에 씁니다.

실무 예시: 재직년수는 0 이상 60 이하로만

1. 제한 대상에서 '정수'를 선택합니다.
2. 제한 방법에서 '해당 범위'를 선택합니다.
3. 최소값 0, 최대값 60을 입력합니다.

이렇게 설정하면 마이너스 값이나 60을 넘는 비정상적인 숫자가 입력되는 걸 막을 수 있습니다.

날짜 범위로 제한하기

입사일, 계약일처럼 특정 기간 안의 날짜만 유효한 경우에 씁니다.

실무 예시: 올해 안의 날짜만 입력되게 하기

1. 제한 대상에서 '날짜'를 선택합니다.
2. 제한 방법에서 '해당 범위'를 선택합니다.
3. 시작일과 종료일을 지정합니다. 셀 주소를 참조로 넣어두면, 매년 값을 바꿔줄 필요 없이 기준 셀만 수정하면 됩니다.

목록으로 정해진 값만 선택하게 하기

이전 글에서 다뤘던 방식입니다. 부서명, 직급처럼 정해진 항목 안에서만 골라야 하는 칸에 씁니다.

 

제한 대상: 목록
원본: 영업팀,인사팀,재무팀,개발팀

쉼표로 구분해서 직접 입력하거나, 미리 만들어둔 목록 범위를 참조로 지정할 수도 있습니다. 목록이 자주 바뀐다면, 셀 범위를 참조로 지정해두는 방식이 관리하기 더 편합니다.

사용자 지정 수식으로 조건 만들기

기본 제공되는 옵션(정수, 날짜, 목록 등)으로는 부족한, 조금 더 복잡한 조건이 필요할 때는 '사용자 지정'을 선택해서 수식을 직접 입력할 수 있습니다.

실무 예시: 사번 형식이 맞는지 확인하기

사번이 항상 6자리 숫자여야 한다면 이렇게 씁니다.

 

=AND(LEN(A2)=6, ISNUMBER(VALUE(A2)))

이전 글에서 다룬 LEN과 텍스트 함수들이 여기서도 그대로 쓰입니다. 글자 수가 6자리인지, 그리고 숫자로 변환 가능한 값인지를 동시에 확인해서, 둘 다 만족해야만 입력이 허용됩니다.

실무 예시: 중복 입력 막기

같은 사번이 두 번 입력되는 걸 막고 싶다면 COUNTIF를 활용합니다.

 

=COUNTIF($A$2:$A$100, A2)=1

지정한 범위 안에서 지금 입력하려는 값이 정확히 한 번만 나와야 한다는 조건입니다. 이미 같은 값이 있는 상태에서 또 입력하려고 하면 경고가 뜹니다. 절대참조($)로 범위를 고정해야 한다는 점, 앞서 참조 방식을 다룬 글에서 설명드린 부분이 여기서도 그대로 적용됩니다.

경고 메시지 직접 설정하기

기본 경고창은 "입력하신 값이 잘못되었습니다" 정도로 두루뭉술합니다. [오류 메시지] 탭에서 제목과 내용을 직접 입력해두면, 어떤 값이 왜 안 되는지 구체적으로 알려줄 수 있습니다.

실무 예시

 

제목: 사번 형식 오류
내용: 사번은 숫자 6자리로 입력해주세요.

이렇게 설정해두면, 여러 사람이 같이 쓰는 파일에서 왜 입력이 안 되는지 몰라 헤매는 상황을 줄일 수 있습니다.

입력 전 안내 메시지도 함께 설정하기

[설명 메시지] 탭에서는 셀을 클릭했을 때 미리 안내 문구가 뜨도록 설정할 수 있습니다. "사번은 6자리 숫자로 입력해주세요"라는 문구를 셀 클릭 시점에 미리 보여주면,애초에 잘못 입력할 가능성 자체를 줄여줍니다.

자주 하는 실수 정리

• 이미 입력된 기존 데이터에는 유효성 검사가 소급 적용되지 않는다는 걸 모르는 경우: 유효성 검사는 앞으로 새로 입력하는 값부터 적용됩니다. 기존에 잘못 입력된 값은 별도로 점검해야 합니다.
• 사용자 지정 수식에서 절대참조를 빼먹어서 범위가 밀리는 경우: 특히 중복 확인용 COUNTIF 수식에서 자주 발생합니다.
• 복사-붙여넣기로 유효성 검사가 걸린 셀 내용을 덮어써서 규칙이 통째로 사라지는 경우: 다른 곳에서 값을 복사해서 붙여넣으면, 원본에 유효성 검사가 없었을 경우 규칙 자체가 지워질 수 있습니다. 값만 붙여넣거나, 서식/유효성 유지 옵션을 확인하는 습관이 필요합니다.
• 오류 메시지를 기본값 그대로 둬서 사용자가 원인을 모르는 경우: 협업 파일이라면 메시지를 구체적으로 적어두는 게 서로의 시간을 아껴줍니다.

마치며

유효성 검사는 화려한 기능은 아니지만, 데이터가 처음 입력되는 순간부터 품질을 관리해준다는 점에서 실무에 미치는 영향이 생각보다 큽니다. 특히 여러 사람이 같이 입력하는 공유 파일이라면, 자주 틀리는 항목 한두 개만이라도 유효성 검사를 걸어두시길 추천드립니다.