나는 오류 확인 스프레드시트를 Excel 데이터 유효성 검사로 교체하고 문제가 발생하기 전에 실수를 잡았다

저는 매주 시간을 내어 도우미 수식을 실행하고 조건부 서식에서 빨간 셀을 찾아내며 지난 며칠 동안 몰래 들어간 오타를 수정하면서 스프레드시트를 정리하곤 했습니다. 정리를 하는 시간은 결코 줄어들지 않았기 때문에 Excel의 데이터 유효성 검사 대화 상자를 더 자세히 살펴보았습니다. 이제는 이미 피벗 테이블을 망쳐버린 뒤 실수를 찾아내는 대신, 누군가 잘못된 값을 입력하려고 할 때 즉시 차단합니다.

데이터 검증은 나중에 찾는 대신 잘못된 입력을 차단합니다

전체 설정은 규칙당 약 30초가 걸립니다

내 이전 설정이 정확히 고장 난 건 아니었다. 데이터 옆에는 조용히 작동하는 IF와 COUNTIF 수식 열이 있었고, 뭔가 이상해 보이면 셀을 빨갛게 바꾸는 조건부 서식 규칙도 있었다. 매주 금요일마다 스크롤하며 빨갛게 표시된 것을 고쳤다. 문제는 이 접근 방식이 사후적이라는 점이다 — 오타를 발견할 때쯤이면 이미 SUMIFS가 깨지거나 피벗 테이블에서 카테고리가 나뉘어 있었다.

데이터 유효성 검사는 그 순서를 뒤집습니다. 이는 데이터 탭에 있으며, 기본 아이디어는 간단합니다: 셀이나 범위에서 무엇이 허용되는지 정확히 Excel에 알려주면, Excel은 그 외의 것은 거부합니다. 규칙을 설정하려면:

1. 보호하려는 범위를 선택하세요.
2. 데이터 > 데이터 유효성 검사로 이동합니다.
3. 허용 드롭다운에서 규칙 유형을 선택하세요. 예: 정수, 소수, 날짜, 시간, 텍스트 길이, 목록 또는 사용자 지정.
4. 매개변수(최소값 및 최대값, 원본 목록 또는 수식)를 설정합니다.
5. 확인을 클릭하세요.

내 판매 시트에서 나는 Units Sold 열에 전체 숫자 규칙을 적용하여 1에서 100 사이의 범위를 강제했다. 해당 열의 실제 값은 7에서 44까지이므로 100을 설정하면 잘못 입력된 1,500과 같은 터무니없는 값이 들어오는 것을 막으면서 여유를 둔다. 그 규칙 하나만으로도 그렇지 않으면 보조 수식과 빨간색 강조 표시가 필요했던 실수를 찾아낼 수 있다.

드롭다운 목록이 내 가장 큰 오타 원인을 없앴다

한 개의 출처 목록, 전체 시트에서 철자 오류 없음

이것은 제가 가장 많이 의지하는 데이터 검증 섹션이며, 제 작업 흐름에 더 일찍 추가했으면 좋았을 부분입니다. 제 판매 스프레드시트에는 제품 카테고리 열이 있는데, 목록 규칙을 추가하기 전에는 같은 열에 'Electronics', 'ELECTRONICS', 'electronics'가 세 개의 별도 값으로 들어 있었습니다. 'Home & Garden'과 'Home and Garden'도 같은 카테고리를 두 개로 나누고 있었습니다.

그 데이터를 기반으로 피벗 테이블을 만들었을 때, 하나의 제품 라인이어야 할 항목이 중복된 행으로 나타났습니다. 제가 이를 고친 방법은 다음과 같습니다:

1. 시트의 다른 곳에 네 가지 허용 값인 전자제품, 의류, 가정 및 정원, 스포츠를 포함한 작은 범위를 설정하세요.
2. 제품 카테고리 열을 선택하세요.
3. 데이터 > 데이터 유효성 검사 > 허용 > 목록로 이동합니다.
4. 소스를 해당 범위로 설정하거나(또는 그것을 엑셀 테이블로 변환하고 이름이 지정된 범위로 참조하여 새 카테고리를 추가할 때 드롭다운이 확장되도록) 하세요.
5. 확인을 클릭하세요.

이제 그 열의 모든 셀에는 작은 화살표가 표시되고, 항목은 그 네 가지 옵션으로 제한됩니다. 피벗 테이블은 다음에 새로 고침했을 때 스스로 정리되었습니다.

사용자 지정 수식은 내장 옵션으로는 적용할 수 없는 규칙을 강제합니다

하나의 수식으로 중복을 막고, 형식을 강제하며, 논리 오류를 잡을 수 있습니다

데이터 유효성 검사에서 사용자 지정 옵션은 규칙이 복잡할 때 이 기능이 정말 유용해지는 곳입니다. 유효한 항목에 대해서는 TRUE를, 그 외에는 FALSE를 반환하는 수식을 작성하면 나머지는 엑셀이 처리합니다.

내가 자주 사용한다고 느끼는 세 가지 규칙:

• 중복 항목 차단. 주문 ID 열의 경우, =COUNTIF($E$2:$E$1000,E2)=1를 사용하면 같은 ID를 두 번 기록하지 못하게 합니다. 사후에 중복 검사를 실행하는 것보다 훨씬 깔끔합니다.
• 형식을 강제 적용. 시트의 제품 코드가 항상 "PRD-"로 시작해야 한다면, =LEFT(A2,4)="PRD-"는 그렇지 않은 모든 항목을 거부합니다. 협업자 간에 코드를 일관되게 유지하는 데 유용합니다.
• 논리적 오류를 잡기. B 열에 종료 날짜가 있고 A 열에 시작 날짜가 있는 경우, =B2>=A2는 누군가 프로젝트 종료 날짜를 시작일보다 이전으로 입력할 경우 해당 입력을 차단합니다.

이 절충점은 주목할 가치가 있습니다. 사용자 지정 수식은 합격 또는 불합격만 반환하며, Excel은 사용자가 입력한 내용이 왜 거부되었는지 알려주지 않습니다. 바로 그 부분이 다음 섹션에서 다뤄집니다.

입력 메시지와 오류 알림이 규칙을 명확하게 합니다

명확한 지시는 침묵하는 실패보다 항상 더 낫다

데이터 유효성 검사 대화 상자에는 똑같이 중요한 다른 두 개의 탭이 있습니다: 입력 메시지와 오류 알림. 이들은 조용한 규칙을 실제로 공동 작업자가 사용할 수 있는 것으로 바꾸어 줍니다.

입력 메시지 탭은 사용자가 셀을 선택할 때 나타나는 작은 툴팁을 추가합니다. 저는 누군가 입력을 시작하기 전에 무엇이 예상되는지 설명하는 데 사용합니다 — 예를 들어 '1에서 100 사이의 값을 입력하세요' 또는 '드롭다운에서 카테고리를 선택하세요'와 같은 것들입니다. 이 툴팁은 아무것도 차단하지 않으며, 누군가가 저에게 물어야 할 질문에 대한 답변만 제공합니다.

오류 경고 탭이 더 흥미로운 이유는 선택할 수 있는 세 가지 스타일을 제공하기 때문입니다:

• Stop은 항목 전체를 거부합니다. 고유 ID나 카테고리 라벨과 같은 엄격한 규칙에 가장 적합합니다.
• 경고는 사용자가 값을 수락하기 전에 확인하도록 요청합니다. 허용되지만 드문 30% 이상의 할인과 같은 소프트 규칙에 적합합니다.
• 정보는 단지 사용자에게 알리고 어쨌든 입력을 수락합니다.

여기서 할 가치가 있는 또 다른 일은 유용한 경고 문구를 작성하는 것입니다. 엑셀의 기본 메시지는 "이 값은 이 셀에 정의된 데이터 유효성 검사 제한과 일치하지 않습니다."로, 아무에게도 아무 의미가 없습니다. 하지만 "프로젝트 시작일 이후의 날짜를 입력해 주세요"는 더 명확하며 실제로 도움이 됩니다.

내가 다음에 내 검증 규칙을 어디로 가져갈지

동적 배열 위에 검증을 계층화하는 것이 다음 단계입니다

데이터 유효성 검사는 조건부 서식을 완전히 대체할 수는 없습니다 — 규칙 내에 속하지만 이상해 보이는 값을 강조하는 데 여전히 필요합니다. 바뀌는 것은 작업 순서입니다. 제가 다음으로 시도하고 싶은 것은 유효성 검사 규칙을 동적 배열 수식과 결합하는 것입니다. 이렇게 하면 새 카테고리나 영업사원을 추가할 때 드롭다운용 원본 목록이 스스로 확장됩니다.