Kim은 위치 코드를 포함하는 데 사용되는 데이터 열이 있습니다. 이 코드는 A03 또는 B12와 같이 단일 문자와 두 자리 숫자로 구성됩니다.

Kim은이 패턴을 사용하지 않는 열에 입력 된 항목이 어떤 식 으로든 강조 표시되도록 열을 조건부 형식으로 지정하려고합니다.

이 문제에 접근 할 수있는 방법에는 여러 가지가 있습니다. 각 접근 방식은 조건부 서식 규칙 내에서 True 또는 False를 반환하고 조건부 서식을 트리거하는 데 사용할 수있는 수식을 개발하는 데 달려 있습니다. (이 팁에서는 조건부 서식 규칙을 만드는 방법에 대해 설명하지 않고 대신 규칙에서 사용할 수있는 다양한 수식에 중점을 둡니다. 조건부 서식 규칙을 만드는 방법은 다른 _ExcelTips_에서 다룹니다.)

어떤 공식을 조합하든 다음 세 가지를 테스트해야합니다.

  • 문자열에는 정확히 3 개의 문자가 있습니다.

  • 첫 번째 문자는 문자입니다.

  • 두 번째 및 세 번째 문자는 숫자입니다.

셀의 텍스트에 세 문자 만 포함되어 있는지 확인하는 것은 다소 쉽습니다. LEN 함수를 사용하여 수행 할 수 있습니다.

=LEN(A1)=3

첫 번째 문자가 문자인지 알아내는 것도 다소 쉽습니다. 실제로이를 수행 할 수있는 몇 가지 방법이 있습니다. 다음 중 첫 문자가 문자 인 경우 True를 반환합니다.

=AND(CODE(LEFT(A1,1))>64,CODE(LEFT(A1,1))<91)

=AND(LEFT(A1,1)>="A",LEFT(A1,1)<="Z")

첫 번째 위치에 대문자 만 있는지 확인합니다. 소문자도 허용하려면 두 번째 테스트의 변형을 사용할 수 있습니다.

=AND(UPPER(LEFT(A1,1))>="A",UPPER(LEFT(A1,1))<="Z")

대문자와 소문자가 모두 허용되는 경우 (거의 다른 기호와 함께) 다음 테스트 사용을 고려할 수 있습니다.

=NOT(ISNUMBER(LEFT(A1,1)+0))

다음은 두 번째 및 세 번째 문자가 숫자인지 여부에 관계없이 세 번째 테스트를 적용 할 수있는 몇 가지 방법입니다.

=ISNUMBER(VALUE(RIGHT(A1,2)))

=ISNUMBER(--RIGHT(A1,2))

이러한 접근 방식은 마지막 두 문자를 함께 처리합니다. 즉, “1”, “11”및 “111”이 모두 테스트를 통과하고 숫자로 성공적으로 체크 아웃됩니다. 수식이 마지막 두 자리 만 확인하는 경우 문제가 될 수 있지만 첫 번째 확인도 포함한다는 사실 (셀에있는 문자열의 전체 길이에 대해 3이어야 함)은 그렇지 않습니다. 전혀 문제가 없습니다.

이제 트릭은 세 가지 테스트 각각에 대한 선택 접근 방식을 단일 공식으로 결합하는 것입니다. 이것은 AND 함수를 사용하여 수행 할 수 있습니다. 각 테스트에서 가장 짧은 것을 선택하고 다음과 같이 결합합니다.

=AND(LEN(A1)=3, AND(LEFT(A1,1)>="A",LEFT(A1,1)<="Z"), ISNUMBER(--RIGHT(A1,2)))

작성된대로이 수식은 모든 테스트가 통과되면 True를 반환합니다. 즉, 셀에 유효한 패턴이있는 위치 코드가 포함되어 있음을 의미합니다. Kim이 열을 색상 (예 : 녹색)으로 형식화 한 다음 조건부 형식을 사용하여 녹색을 제거하면 조건부 형식으로 잘 작동합니다. 이것은 거꾸로 보일 수 있으며 실제로 패턴이 충족되지 않는 경우에만 형식을 적용하고 싶을 수 있습니다. 이 경우 반환 된 True / False를 반대로하기 위해 NOT 함수에 수식을 넣습니다.

=NOT(AND(LEN(A1)=3, AND(LEFT(A1,1)>="A",LEFT(A1,1)<="Z"), ISNUMBER(--RIGHT(A1,2))))

알 수 있듯이 이와 같은 공식을 사용하는 것은 약간 까다로울 수 있습니다. 원하는 경우 조건부 서식 규칙을 조금 더 짧게 만드는 UDF (사용자 정의 함수)를 만들 수 있습니다. 다음 매크로는 좋은 방법입니다.

Function IsBadPattern(sCell As String) As Boolean     IsBadPattern = Not(sCell Like "[A-Z][0-9][0-9]")

End Function

조건부 서식 규칙에서 UDF를 사용하려면 다음 수식을 사용하기 만하면됩니다.

=IsBadPattern(A1)

참조 된 셀의 문자열이 원하는 패턴과 일치하지 않으면 UDF의 결과는 TRUE가됩니다. 쓰여진대로 첫 번째 문자 위치에 소문자를 사용할 수 없습니다. 소문자를 허용해야하는 경우 UDF를 변경할 필요가 없습니다. 대신 공식을 다음과 같이 변경하십시오.

=IsBadPattern(UPPER(A1))

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

이 팁 (9976)은 Office 365의 Microsoft Excel 2007, 2010, 2013, 2016, 2019 및 Excel에 적용됩니다.