데이터 유효성 검사 고급: 특정 형식(날짜, 숫자 범위) 외 입력 원천 차단

Byadmin

“100점 만점에 200점을 입력했다고요?”

오류 데이터의 유입을 원천 봉쇄하는 구글 시트의 ‘철벽 수문장’, 고급 유효성 검사 설정법

데이터 분석의 세계에는 아주 오래된 격언이 하나 있습니다. “Garbage In, Garbage Out (쓰레기가 들어가면 쓰레기가 나온다).”

아무리 정교한 함수와 화려한 피벗 테이블을 세팅해 두어도, 기초 데이터 자체가 오염되어 있다면 그 결과물은 신뢰할 수 없습니다. 팀원들에게 “날짜는 꼭 ‘YYYY-MM-DD’ 형식으로 적어주세요”라고 수백 번 말해도, 누군가는 ‘24.1.1’로 적고, 누군가는 ‘1월 1일’이라고 텍스트로 적습니다. 이 작은 차이들이 모여 수식 오류(#VALUE!)를 일으키고, 담당자는 결국 야근을 하며 데이터를 하나씩 수정해야 합니다.

사람의 주의력에 호소하는 것은 한계가 있습니다. 이제 시스템으로 해결해야 합니다. 구글 시트의 ‘데이터 유효성 검사(Data Validation)’ 기능을 활용하면, 애초에 규칙에 맞지 않는 데이터는 입력조차 할 수 없도록 막아버릴 수 있습니다.

오늘은 텍스트 오타를 방지하는 수준을 넘어, 날짜 형식 강제, 숫자 범위 제한, 그리고 정규표현식을 활용한 고급 패턴 검사까지, 무결점 데이터를 위한 방어 시스템 구축법을 상세히 다룹니다.

1. 왜 ‘유효성 검사’가 필수적인가?

엑셀이나 구글 시트를 ‘메모장’처럼 쓰는 습관이 데이터 관리를 망칩니다. 셀 하나에는 하나의 약속된 형식만 들어가야 합니다.

  • 날짜: 텍스트가 아닌 실제 ‘날짜 서식’이어야 계산(DATEDIF 등)이 가능합니다.

  • 숫자: ‘1,000원’이라고 ‘원’ 글자를 붙여 쓰는 순간, 시트는 이것을 숫자가 아닌 문자로 인식하여 합계(SUM)에서 제외해 버립니다.

  • 범위: 할인율에 0.5(50%)가 아닌 50을 입력하면 가격 계산이 엉망이 됩니다.

유효성 검사는 입력 단계에서 이 데이터가 ‘합격’인지 ‘불합격’인지 판별하는 게이트키퍼(Gatekeeper) 역할을 수행합니다.

2. 날짜 형식 강제: 캘린더 피커(Picker) 생성하기

가장 흔한 오류가 바로 날짜 입력입니다. 이를 해결하는 가장 확실한 방법은 사용자가 키보드로 타이핑하는 것을 막고, 마우스로 달력을 찍게 하는 것입니다.

🛠️ 설정 방법

  1. 날짜가 입력될 셀 범위(예: A열)를 선택합니다.

  2. 상단 메뉴에서 [데이터] > **[데이터 확인]**을 클릭합니다.

  3. 우측 사이드바에서 **[규칙 추가]**를 누릅니다.

  4. 기준을 **[유효한 날짜]**로 설정합니다.

  5. **[고급 옵션]**을 열고, ‘데이터가 유효하지 않은 경우’를 **[입력 거부]**로 선택합니다. (중요!)

  6. [완료]를 누릅니다.

[효과 및 검증] 이제 해당 셀을 더블 클릭해 보세요. 작은 미니 달력(Calendar Picker)이 팝업 됩니다. 사용자는 날짜를 고르기만 하면 되므로 형식이 틀릴 일이 없습니다. 만약 누군가 강제로 “2024년 1월”이라고 텍스트를 치면? [입력 거부] 설정 덕분에 오류 창이 뜨며 입력 자체가 취소됩니다.

3. 숫자 범위 제한: “할인율은 100%를 넘을 수 없다”

점수(0~100점), 할인율(0~100%), 재고 수량(음수 불가) 등 특정 범위 내의 숫자만 받아야 할 때가 있습니다.

🛠️ 설정 방법 (예: 점수 입력)

  1. 점수 입력 범위(예: B열)를 선택합니다.

  2. [데이터 확인] > **[규칙 추가]**로 진입합니다.

  3. 기준을 **[보다 큼/보다 작음]**이 아니라 **[사이]**를 선택합니다.

  4. 최솟값에 0, 최댓값에 100을 입력합니다.

  5. 마찬가지로 **[입력 거부]**를 선택합니다.

[효과] 실수로 200점을 입력하거나 오타로 ㄱ 같은 문자를 입력하면 경고창이 뜨며 데이터가 입력되지 않습니다. 이는 평균값이나 합계를 낼 때 통계가 왜곡되는 것을 원천 차단합니다.

4. 고급 기술: 맞춤 수식으로 복잡한 패턴 잡아내기

기본 옵션에 없는 조건(예: 전화번호 형식, 사번 형식)은 어떻게 막을까요? 이때는 **[맞춤 수식]**을 사용해야 합니다.

시나리오 A: “반드시 ‘ID-‘로 시작해야 한다” (사번 관리)

입력 데이터가 특정 접두사로 시작해야 한다면 REGEXMATCH 함수를 활용합니다.

  1. 기준을 **[맞춤 수식]**으로 선택합니다.

  2. 수식 입력칸에 다음을 넣습니다. (A1 셀 기준) =REGEXMATCH(A1, "^ID-")

  3. [입력 거부] 설정 후 완료.

이제 ID-101은 입력되지만, 그냥 101이나 no-101은 입력할 수 없습니다.

시나리오 B: “중복된 값은 입력 금지” (회원 ID 관리)

이미 입력된 ID를 또 입력하는 것을 막으려면 COUNTIF 함수를 씁니다.

  1. 기준을 **[맞춤 수식]**으로 선택합니다.

  2. 수식 입력칸에 다음을 넣습니다. (A열 전체 검사) =COUNTIF(A:A, A1) <= 1

이 설정은 A열 전체에서 지금 입력한 A1 값의 개수가 1개 이하일 때만(즉, 중복이 없을 때만) 입력을 허용한다는 뜻입니다. 중복 데이터를 입력하는 순간 에러가 발생합니다.

5. 사용자 경험(UX) 디테일: 친절한 오류 메시지 심기

무조건 “입력할 수 없습니다”라는 차가운 경고창만 띄우면 사용자는 당황합니다. “도대체 뭘 잘못 입력했다는 거지?”라며 짜증을 낼 수도 있죠.

유효성 검사 설정 하단에 있는 [모양] 섹션에서 **[도움말 텍스트 표시]**를 체크하세요. 그리고 사용자가 이해할 수 있는 가이드를 직접 적어줍니다.

  • 나쁜 예: 입력 값이 유효하지 않습니다.

  • 좋은 예: 점수는 0에서 100 사이의 숫자만 입력 가능합니다.

  • 좋은 예: 날짜는 달력을 더블 클릭하여 선택해 주세요.

이 작은 메시지 하나가 팀원들의 불필요한 질문을 줄여주고 업무 흐름을 매끄럽게 만듭니다.

데이터 품질은 ‘입력’에서 결정됩니다

나중에 잘못된 데이터를 찾아서 고치는 비용(Cleaning Cost)은, 처음부터 올바르게 입력하는 비용보다 10배 이상 비싸다는 연구 결과가 있습니다.

구글 시트의 데이터 유효성 검사는 단순한 기능이 아닙니다. 그것은 데이터의 신뢰도를 지키는 가장 강력한 방화벽입니다. 복잡한 함수를 공부하기 전에, 오늘 당장 여러분의 시트에 이 ‘입력 규칙’부터 세워보세요.

규칙이 서면 데이터가 깨끗해지고, 데이터가 깨끗해지면 분석은 저절로 쉬워집니다.

Similar Posts

답글 남기기

이메일 주소는 공개되지 않습니다. 필수 필드는 *로 표시됩니다