데이터 유효성 검사(3) - 사용자 지정

아. 사용자 지정

사용자 지정은 수식을 이용하여 사용자가 원하는 유효성 조건을 지정하는 것을 말합니다. 정수, 소수점 등 제한 대상별 조건을 이용할 수도 있지만, 이것뿐만 아니라 다양한 조건을 지정할 수 있는 장점이 있습니다.

 

(1) 셀 값과 비교하는 수식

아래와 같이 월별 배정액을 입력한 후 배정액 범위 내에서 집행해야 하는 경우 아래와 같이 사용자 지정 수식을 지정하기 위해 B3셀에서 D3셀을 선택합니다.

 

수식에 =b3<b2라고 입력하면 b3의 값이 b2의 값보다 작아야 한다는 조건을 지정한 것입니다.

 

 위 화면에서 확인 버튼을 누르고, C3셀을 클릭한 후 데이터 유효성 검사 명령을 누르면 오른쪽으로 셀을 이동하였으므로 수식이 상대적 위치에 맞게 =c3<c2로 수정되어 있습니다.

 

따라서, 확인 버튼을 누르고, c3셀에 40이라고 입력하면 에러 메시지가 표시되고,

25라고 입력하면 에러 없이 입력됩니다.

 

(2) 두 가지 이상 조건을 and 또는 or  함수로 연결한 수식

제한 대상으로 정수를 선택하면 최솟값, 최댓값을 지정해야 하는데 이것을 사용자 지정 제한으로 설정하면 아래와 같습니다.

 

먼저 B6셀과 C6셀에 최솟값, 최댓값을 입력한 다음,

제한 대상으로 사용자 지정을 선택하고,

수식에 =AND(B7>=B6,B7<=C6)라고 입력합니다.

 

=AND(B7>=B6,B7<=C6)란 수식은

B7>=B6, 다시 말해 b7셀의 값이 b6셀의 값 이상이어야 한다는 조건과

B7<=C6, 다시 말해 b7셀의 값이 c6셀 이하여야 한다는 조건을 and 함수로 엮었으므로 두 가지 조건을 모두 충족해야 합니다.

 

따라서, B7셀의 값이 B6셀 값 이상이고, C6셀 값이어야 합니다.

그러면 B7셀에 정수 80을 넣어도 되고, 소수 80.5를 넣어도 됩니다.

 

그렇지만 제한 대상을 소수점으로 선택하고 최솟값에 60, 최댓값에 100을 입력하는 것이 훨씬 편리합니다.

 

이외에도 max, min, average 등 다양한 함수를 이용해 수식을 작성할 수 있습니다. 

 

 

(3) 응용 사례

위에서는 배정액 범위 내에 집행하는 것을 하나만 생각해서 만들었는데, 일자별 집행액이 배정액을 초과하지 않도록 하는 것을 만들어 보겠습니다.

 

아래와 같이 표를 만듭니다. 월별 배정액과 집행 내역을 기록할 수 있도록 했고, 행은 한 화면에 보이도록 17행까지만 만들고 A열부터 E열까지는 숨겼습니다.

 

(실패)

조건은 집행액이 배정액보다 작거나 같아야 합니다.

조건에 따라 G4셀과 i4을 선택한 상태에서 데이터 유효성 검사 명령을 누른 후 제한 대상으로 사용자 지정을 선택하고, 수식 입력란에 =g4<=g2라고 입력하고 확인 버튼을 누릅니다.

 

i4셀만 선택한 상태에서 데이터 유효성 검사 명령을 누르면

수식이 =i4<=i2일 줄 알았더니 =g4<=g2라고 되어 있습니다. g2셀을 선택한 후 데이터 유효성 검사 명령을 누른 후 수식을 확인해 봐도 =e4<=e2라고 2열 왼쪽을 참고합니다.

 

따라서, g4셀과 i4셀을 선택한 상태에서 수식에 =i4<=i2라고 입력해야 합니다.

이제 g4셀을 선택하고 수식을 확인해보면 =g4<=g2라고 되어 있습니다.

 

이제 g5셀에 22라고 입력하면 에러가 나와야 하는데, 그대로 입력됩니다. 문제가 있습니다.

 

따라서, 수식으로 된 셀(G4셀)을 기준으로 유효성 조건을 걸 수 없고, 입력하는 셀(G5셀 이하)에 유효성 조건을 걸어야 합니다.

 

 

(성공)

입력 셀에 제한을 걸어야 하므로 g5셀부터 g17셀까지, i5셀부터 i17셀까지 선택한 상태에서

사용자 지정 수식으로  =i4<=i2가 아니라 내려가더라도 행은 변하면 안 되므로 혼합 참조 형식으로 

=i$4<=i$2라고 입력하고 확인 버튼을 누릅니다.

 

그러면 기존에 입력된 22도 에러 메시지가 표시돼야 할 텐데 입력 시에 에러 메시지를 보여주는 것이므로 아직까지는 에러 메시지가 보이지 않고, G5셀에 22라고 다시 입력하고 엔터키를 누르면 에러 메시지가 보입니다. 다시 시도 버튼을 누른 후 10이라고 입력하고, G6셀에 11이라고 입력하면

 

마찬가지로 에러 메시지가 표시됩니다. 다시 시도 버튼을 누르고 6을 입력하면 정상적으로 입력됩니다.

 

따라서, 집행액을 계속 기록하면서 배정액을 초과하지 않도록 할 수 있게 되었습니다.

현재는 17행까지만 유효성 검사가 적용되는데, 유효성 검사 입력 범위를 확장하면 보다 편리하게 사용할 수 있습니다.

 

데이터유효성검사3.xlsx

0.01MB