Blair는 데이터 입력과 데이터 검증의 두 영역으로 나뉜 워크 시트를 가지고 있습니다. 데이터 확인 영역은 IF 문을 사용하여 항목을 확인하는 수식으로 구성됩니다. 문제가 발견되면 확인 영역의 셀에 텍스트 메시지가 표시되고, 그렇지 않으면 수식이 공백을 반환합니다. 다음은 일반적인 확인 공식입니다.

=IF(A1<>5,"Value in A1 is not 5")

문제는 데이터 검증 영역이 상당히 클 수있어 문자 메시지 중 하나를 놓치기 쉽다는 것입니다. Blair는 데이터 검증 영역을 검사하고 해당 영역에 다른 메시지가 있으면 단일 메시지를 반환하는 수식을 만드는 방법이 있는지 궁금해했습니다.

이 문제에 접근 할 수있는 방법에는 여러 가지가 있습니다. 데이터 확인 영역이 연속적이면 간단한 배열 수식이 트릭을 수행합니다. 워크 시트의 빈 셀에 다음을 입력합니다.

=SUM((LEN(ValRange)>0)*1)

ValRange가 데이터 유효성 검사 영역의 셀 범위로 바뀌는 지 확인합니다. 또한 Shift + Ctrl + Enter를 눌러 수식을 입력해야합니다 (배열 수식임을 나타냄). 이 수식은 범위에서 길이가 0보다 큰 셀 수를 나타내는 값을 반환합니다. 즉, 메시지가 표시되는 셀 수를 계산합니다.

배열 수식을 사용하지 않으려면 다음 일반 수식을 사용하여 동일한 결과를 얻을 수 있습니다.

=SUMPRODUCT((LEN(ValRange)>0)*1)

결과는 다시 길이가 0보다 큰 셀의 수입니다. 또 다른 방법은 Excel에서 제공하는 일부 COUNT 함수를 사용하는 것입니다.

=COUNTA(ValRange) - COUNTBLANK(ValRange)

이 수식은 범위에있는 셀 수를 계산 한 다음 범위에있는 빈 셀 수를 뺍니다. 결과는 공백이 아니거나 메시지를 표시하는 셀의 수입니다. 다른 공식적인 접근 방식을 사용하여 간단한 예 / 아니오 응답을 결정할 수 있습니다.

=IF(COUNTIF(ValRange,"?*"),"","No ") & "Verification Messages"

ValRange에 메시지가없는 경우 수식은 “확인 메시지 없음”을 반환합니다. 메시지가있는 경우 “No”를 제거하고 “Verification Messages”만 반환합니다.

데이터 확인 영역에 조건부 서식을 적용하는 것도 좋은 생각입니다. 지금까지 설명한 공식은 메시지가 있는지 여부를 알려주지 만 해당 메시지의 위치는 강조하지 않습니다. 조건부 서식은 각 메시지를 정확히 가리킬 수 있습니다. 수식이 포함 된 영역에서 모든 셀을 선택한 다음 조건부 서식을 사용하여 해당 셀의 길이를 확인합니다. 길이가 0보다 크면 빨간색 배경을 표시하도록 셀의 서식을 지정할 수 있습니다. 이렇게하면 워크 시트를 스크롤 할 때 데이터 확인 영역의 메시지를 놓치기가 훨씬 더 어려워집니다.

워크 시트를 완전히 재 설계하고 싶다면 데이터 검증 영역을 없애는 것이 더 강력한 접근 방식입니다.

각 입력 셀에 대한 데이터 유효성 검사를 사용하여 동일한 결과 (데이터 입력 영역에있는 항목 확인)를 얻을 수 있습니다. 적절하게 설정하면 데이터 유효성 검사는 사용자가 각 셀에 허용 가능한 값을 입력했는지 확인하므로 데이터 유효성 검사 영역의 대부분이 필요하지 않습니다.

_ExcelTips_는 비용 효율적인 Microsoft Excel 교육을위한 소스입니다.

이 팁 (2772)은 Microsoft Excel 97, 2000, 2002 및 2003에 적용됩니다.