Steve는 A 열의 각 셀에 파일 이름이 포함 된 10,000 개 이상의 행이 포함 된 워크 시트를 가지고 있습니다. 이러한 이름은 두 가지 규칙을 따라야하며 Steve는 어떤 이름이 규칙을 위반하는지 발견해야합니다. 파일 이름에 대시가 포함 된 경우 대시 앞뒤에도 공백이 하나 있어야합니다. 두 번째 규칙은 이름에 쉼표가 포함 된 경우 앞에 공백이 없어야하고 뒤에 공백이 하나만 있어야한다는 것입니다.

Steve는 하나 (또는 ​​둘 다)를 위반하는 세포를 어떻게 강조 할 수 있는지 궁금해

이 규칙의.

누군가 워크 시트에서 “강조 표시”하고 싶다고 말할 때마다 대부분의 사람들은 조건부 서식 사용을 생각합니다. 이 경우도 예외는 아닙니다. 조건부 서식을 사용하여 패턴 위반을 쉽게 강조 할 수 있습니다. 조건부 서식 규칙을 개발하는 핵심은 패턴이 위반 될 경우 True를 반환하는 수식을 만드는 것입니다. 이 수식은 두 위반 사항을 모두 확인합니다.

=OR(ISNUMBER(FIND("-",SUBSTITUTE(A1," - ",""))), ISNUMBER(FIND(",",SUBSTITUTE(A1,", ",""))), ISNUMBER(FIND(" ,",A1)))

여기서는 공식을 세 줄로 나눴지만 하나의 완전한 공식으로 간주해야합니다. 공식은 파일 이름에서 올바른 패턴 (공백, 대시, 공백 및 쉼표, 공백)을 제거한 다음 파일 이름에 대시 또는 쉼표가 남아 있는지 확인합니다. 남아 있으면 수식이 True를 반환합니다.

다음과 같은 방식으로 수식을 사용하도록 조건부 서식 규칙을 설정할 수 있습니다.

  1. 확인하려는 모든 파일 이름이 포함 된 셀을 선택하십시오.

  2. 리본의 홈 탭이 표시된 상태에서 스타일 그룹에서 조건부 서식 옵션을 클릭합니다. Excel은 조건부 서식과 관련된 옵션 팔레트를 표시합니다.

  3. 강조 셀 규칙을 선택한 다음 결과 하위 메뉴에서 추가 규칙을 선택합니다. Excel에 새 서식 규칙 대화 상자가 표시됩니다.

(그림 1 참조)

  1. 대화 상자 상단의 규칙 유형 선택 영역에서 수식을 사용하여 서식을 지정할 셀 결정을 선택합니다.

  2. 이 수식이 참인 값 서식 상자에 이미 설명 된 긴 수식을 입력합니다.

  3. 서식을 클릭하여 셀 서식 대화 상자를 표시합니다.

  4. 대화 상자의 컨트롤을 사용하여 패턴을 위반하는 셀을 강조 표시하는 데 사용할 형식을 지정합니다.

  5. 확인을 클릭하여 셀 서식 대화 상자를 닫습니다. 이제 7 단계에서 지정한 서식이 규칙의 미리보기 영역에 나타납니다.

  6. 확인을 클릭하십시오.

1 단계에서 선택한 셀이 A1 셀로 시작하지 않은 경우 5 단계에서 사용한 수식을 수정하여 시작 셀을 반영해야합니다. (수식에서 A1의 세 가지 인스턴스는 모두 시작 셀을 참조하도록 변경해야합니다.)

조건부 서식 규칙에서이 수식을 사용하는 데는 두 가지 큰 “잘못”이 있습니다. 첫째, 이중 공백을 감지하지 않습니다. 예를 들어 파일 이름에 “공백, 공백, 대시, 공백”이 포함되어 있으면 패턴 위반이됩니다. 그러나 수식의 SUBSTITUTE 함수는 “공백, 대시, 공백”을 제거하고 결과 문자열에 추가 공백을 남깁니다. 이 단일 공간은 패턴 위반으로 감지되지 않습니다.

이에 대한 해결책은 훨씬 더 긴 수식이거나 조건부 서식 경로를 모두 우회하고 도우미 열을 사용하기 시작하는 것입니다. 이것은 두 번째 “gotcha”로 바로 들어가고 큰 것입니다 :

조건부 서식을 적용하거나 수식이 포함 된 도우미 열을 추가하면 워크 시트를 다시 계산하는 데 걸리는 시간이 현저하게 증가하는 것을 알 수 있습니다. 워크 시트에 너무 많은 수식을 추가하기 시작하면이 문제를 해결할 수 없습니다.

이러한 이유로 셀을 강조 표시하는 매크로를 개발하는 것이 더 적절할 수 있습니다. 그런 다음 패턴을 확인하려는 경우 매크로를 수동으로 실행할 수 있습니다. 이는 정상적인 워크 시트 재 계산 속도가 느려지지 않음을 의미합니다.

다음 매크로는 선택한 셀 범위에서 실행되도록 설계되었습니다.

대시 앞에 두 개의 공백, 대시 뒤에 두 개의 공백, 쉼표 앞에 하나의 공백 또는 쉼표 뒤에 두 개의 공백이 없는지 확인합니다. 그런 다음 파일 이름에서 올바르게 패턴 화 된 대시와 쉼표를 제거하고 대시 또는 쉼표가 남아 있는지 확인합니다. 이러한 조건 중 하나라도 위반이 발견되면 셀은 노란색으로 서식이 지정됩니다.

Sub CheckFilenames1()

Dim bBad As Boolean     Dim c As Range     Dim sTemp1 As String     Dim sTemp2 As String

For Each c In Selection         bBad = False         sTemp1 = c.Text

If Instr(sTemp1, "  -") > 0 Then bBad = True         If Instr(sTemp1, "-  ") > 0 Then bBad = True         If Instr(sTemp1, " ,") > 0 Then bBad = True         If Instr(sTemp1, ",  ") > 0 Then bBad = True         sTemp2 = Replace(sTemp1, " - ", "")

If Instr(sTemp2, "-") > 0 Then bBad = True         sTemp2 = Replace(sTemp1, ", ", "")

If Instr(sTemp2, ",") > 0 Then bBad = True

If bBad Then             c.Interior.Color = vbYellow         Else             c.Interior.Color = xlColorIndexNone         End If     Next c End Sub

매크로를 실행하는 데 시간이 다소 걸릴 수 있지만 다시 한 번, 파일 이름을 확인하려는 경우에만 실행하면됩니다. 매크로가 셀 서식을 “혼합”하지 않도록하려면 원하는 패턴을 위반하는 파일 이름의 오른쪽 열에 일부 텍스트를 삽입하는 버전을 원할 수 있습니다.

Sub CheckFilenames2()

Dim bBad As Boolean     Dim c As Range     Dim sTemp1 As String     Dim sTemp2 As String

For Each c In Selection         bBad = False         sTemp1 = c.Text

If InStr(sTemp1, "  -") > 0 Then bBad = True         If InStr(sTemp1, "-  ") > 0 Then bBad = True         If InStr(sTemp1, " ,") > 0 Then bBad = True         If InStr(sTemp1, ",  ") > 0 Then bBad = True         sTemp2 = Replace(sTemp1, " - ", "")

If InStr(sTemp2, "-") > 0 Then bBad = True         sTemp2 = Replace(sTemp1, ", ", "")

If InStr(sTemp2, ",") > 0 Then bBad = True

If bBad Then c.Offset(0, 1) = "BAD"

Next c End Sub

이 매크로 변형을 실행하면 잘못된 패턴 파일 이름 오른쪽에있는 셀에 “BAD”라는 텍스트가 삽입됩니다. 그런 다음 Excel의 필터링 기능을 사용하여 텍스트가 포함 된 행만 표시 할 수 있습니다.

물론이 모든 것을 한 단계 더 나아가 매크로가 형식이 잘못된 파일 이름을 수정하도록 허용 할 수 있습니다. 다음 매크로는 선택한 모든 셀에서 작동합니다. 각 대시가 단일 공백으로 둘러싸이고 각 쉼표 뒤에 단일 공백 ​​만 오도록합니다.

Sub FixFilenames()

Dim myArry() As String     Dim sTemp As String     Dim c As Range     Dim s As Variant

For Each c In Selection         myArry = Split(c, "-")

sTemp = ""

For Each s In myArry             If sTemp > "" Then                 sTemp = sTemp & " - " & Trim(s)

Else                 sTemp = Trim(s)

End If         Next s         myArry = Split(sTemp, ",")

sTemp = ""

For Each s In myArry             If sTemp > "" Then                 sTemp = sTemp & ", " & Trim(s)

Else                 sTemp = Trim(s)

End If         Next s         c = sTemp     Next c End Sub

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

이 팁 (3015)은 Microsoft Excel 2007, 2010, 2013 및 2016에 적용됩니다.