데이터 유효성 검사로 입력 제한하기

데이터 유효성 검사는 셀이 허용할 입력값에 대한 규칙을 설정합니다. 사용자가 올바른 값을 입력할 것이라고 기대하는 대신 스프레드시트가 입력값을 강제합니다.

셀에 정수만 입력하도록 하거나, 특정 범위의 날짜만 입력하도록 하거나, 승인된 목록의 값만 입력하도록 제한할 수 있습니다.

• 오타가 데이터에 섞이기 전에 찾아냅니다.
• 힌트와 경고 메시지로 사용자를 안내합니다.
• Excel과 Google Sheets 모두에서 사용할 수 있습니다.

이 기능은 프로그램마다 약간 다른 위치에 있습니다.

• Excel: 셀을 선택하고 데이터 탭으로 이동한 다음 데이터 유효성 검사를 클릭합니다.
• Google Sheets: 셀을 선택한 다음 데이터 > 데이터 유효성 검사를 선택하고 규칙 추가를 클릭합니다.

두 프로그램 모두 규칙 유형을 선택하고 제한을 설정하며, 유효하지 않은 데이터를 입력했을 때 어떻게 처리할지 정하는 패널을 엽니다.

일반적인 규칙 중 하나는 셀에 숫자만 입력하도록 제한하는 것입니다. Excel 대화 상자에서 정수 또는 소수를 선택한 다음, 1에서 100 사이와 같은 조건을 설정합니다.

이제 누군가 abc 또는 150을 입력하면 입력값이 거부됩니다.

• 개수와 수량에는 정수를 사용합니다.
• 가격이나 백분율에는 소수를 사용합니다.
• 연산자에는 사이, 초과, 같음 등이 있습니다.

셀에 특정 기간 내의 유효한 날짜만 입력하도록 제한할 수도 있습니다. 날짜 규칙을 선택하고, 예를 들어 오늘부터 연말 사이의 날짜를 설정합니다.

과거 날짜가 의미가 없는 예약표나 마감일 관리에 특히 유용합니다.

• =TODAY()보다 이전 날짜를 거부하여 소급 입력을 차단합니다.
• 시작 날짜와 종료 날짜를 함께 사용하여 프로젝트 기간을 강제할 수 있습니다.

스프레드시트가 모든 입력값을 규칙과 자동으로 비교합니다.

텍스트에도 유효성 검사를 적용할 수 있습니다. Excel은 두 글자 국가 코드나 고정 길이 ID와 같은 필드에 유용한 텍스트 길이 규칙을 제공합니다.

• 주 코드에는 길이가 2와 같도록 요구합니다.
• 짧은 메모에는 길이가 50과 같거나 작도록 요구합니다.

제한을 벗어난 값은 거부되므로 열의 형식이 일정하게 유지되고 이후 수식의 결과도 예측할 수 있습니다.

기본 제공 규칙으로 표현할 수 없는 조건에는 사용자 지정 규칙(Excel) 또는 사용자 지정 수식(Sheets)을 사용합니다. 입력값을 허용하려면 TRUE로 평가되어야 하는 수식을 작성합니다.

예를 들어 셀 A2의 텍스트를 대문자로 강제하려면 다음과 같이 작성합니다.

=EXACT(A2,UPPER(A2))

입력한 값이 모두 대문자가 아니면 수식이 FALSE를 반환하고 입력값이 차단됩니다.

자주 사용하는 사용자 지정 규칙으로 열의 중복 입력을 방지할 수 있습니다. A2:A100을 선택하고 다음 사용자 지정 수식을 사용합니다.

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

이 수식은 방금 입력한 값이 해당 범위에 한 번만 나타나야 한다는 뜻입니다. 이미 값이 있으면 개수가 2가 되어 규칙이 충족되지 않습니다.

• 고유 ID, 이메일 또는 송장 번호에 유용합니다.
• 절대 범위와 상대 참조인 A2에 주목하세요.

Excel에서는 입력 메시지를 표시할 수 있습니다. 셀을 선택했을 때, 입력하기 전에 나타나는 작은 도구 설명입니다.

1에서 100 사이의 정수를 입력하세요와 같이 규칙을 쉬운 말로 설명하는 데 사용합니다.

• 사용자가 입력하기 전에 안내합니다.
• 거부되는 입력과 불편을 줄입니다.

Google Sheets에서는 규칙의 유효성 검사 도움말 텍스트를 통해 비슷한 안내를 표시합니다.

데이터가 규칙을 위반하면 Excel의 오류 경고 탭에서 적용 강도를 결정합니다.

• 중지 — 입력값을 완전히 거부합니다.
• 경고 — 문제를 알리지만 사용자가 계속 진행할 수 있게 합니다.
• 정보 — 사용자에게 알림만 표시합니다.

Google Sheets에도 비슷한 선택지가 있습니다. 입력 거부 또는 경고 표시를 선택할 수 있습니다. 잘못된 데이터를 허용할 수 없다면 중지를, 사용자의 판단을 허용하려면 경고를 선택하세요.

규칙은 언제든지 검토하거나 삭제할 수 있습니다.

• Excel에서는 셀을 선택하고 데이터 유효성 검사를 다시 연 다음, 모두 지우기를 클릭하여 규칙을 제거합니다.
• Excel의 유효하지 않은 데이터 표시 도구는 새로 추가한 규칙을 위반하는 기존 값을 강조 표시합니다.
• Sheets에서는 데이터 유효성 검사를 열고 패널에서 규칙을 삭제합니다.

기존 데이터를 따로 점검하지 않는 한 유효성 검사는 새로 입력한 값만 확인한다는 점을 기억하세요.

다음 수업으로 이어지는 중요한 규칙 유형이 하나 더 있습니다. 바로 목록 규칙입니다.

목록 규칙은 숫자나 날짜를 모두 허용하는 대신 Open, Closed, Pending와 같은 특정 선택지로 셀의 입력값을 제한합니다.

• 드롭다운 메뉴의 기반이 됩니다.
• 선택지를 직접 입력하거나 셀 범위를 지정할 수 있습니다.

다음 수업에서는 이 목록을 보기 좋은 드롭다운으로 만들어 보겠습니다.

열에 이미 해당 열의 다른 곳에 존재하는 값을 거부하도록 하려고 합니다. A2:A100 범위에서 셀 A2에 이 조건을 적용하려면 어떤 사용자 지정 유효성 검사 수식을 사용해야 할까요?

데이터 유효성 검사로 입력을 제어하는 방법을 배웠습니다.

• 데이터 탭(Excel) 또는 데이터 > 데이터 유효성 검사(Sheets)를 통해 엽니다.
• 입력값을 정수, 소수, 날짜 또는 특정 텍스트 길이로 제한합니다.
• 고급 규칙에는 =COUNTIF($A$2:$A$100,A2)=1과 같은 사용자 지정 수식을 작성합니다.
• 입력 메시지를 추가하고 오류에 대해 중지 또는 경고를 선택합니다.

다음에는 명명된 범위를 활용해 사용하기 편한 드롭다운 목록을 만들어 보겠습니다.