Zar는 워크 시트의 A 열 값에 조건부 서식을 적용해야하지만 규칙이 무엇인지 알 수 없습니다. A 열 외에도 B 열에서 시작하는 데이터가 있으며 주기적으로 새 데이터 열을 추가합니다. B로 시작하는 모든 홀수 열에 데이터가있는 경우 Zar는 A 열에 하나의 형식을 적용하기를 원합니다. (B는 워크 시트의 데이터 열 1이므로 홀수로 간주합니다.) 다음으로 시작하는 모든 짝수 열에 데이터가있는 경우 C, Zar는 A 열에 다른 형식을 적용하기를 원합니다. B로 시작하는 모든 데이터 열에 데이터가있는 경우 (수에 관계없이) 세 번째 형식을 적용하려고합니다.

Zar는 의심 할 여지없이 B 열과 C 열에 정보가 있는지 확인하는 수식을 쉽게 만들고 그에 따라 서식을 적용 할 수 있습니다. 실제로 다음과 같은 간단한 공식이 트릭을 수행합니다.

=COUNTA(B1:C1)=2 =COUNTA(B1)=1 =COUNTA(C1)=1

첫 번째 수식은 B와 C 모두에 정보가 있으면 True를 반환하고, 두 번째 수식은 B에 정보가 있으면 세 번째 수식은 C에 ​​정보가 있으면 세 번째 수식을 반환합니다. 각 규칙 / 공식에 대해 “Stop If True”를 선택하는 한, 그러면 서식이 제대로 작동합니다.

B와 C 이외의 여러 열에 대한 수식을 만드는 것은 조금 더 어렵습니다. 순서대로 동일한 세 가지 유형의 공식은 다음과 같습니다.

=COUNTA(B1:G1)=6)

=COUNTA(B1,D1,F1)=3 =COUNTA(C1,E1,G1)=3

필요에 따라 수식에 추가 셀 참조를 쉽게 추가 할 수 있습니다. 이러한 접근 방식은 B1 : G1 범위의 모든 셀에 무언가가있는 경우, 모든 홀수 셀 (B1, D1, F1)에 무언가가있는 경우, 모든 짝수 셀 (C1, E1, G1) 안에 뭔가가 있습니다. 범위의 일부 셀에만 값이있는 경우 True를 반환하지 않습니다. 예를 들어 B1, C1 및 E1 셀에 값이 있으면 True를 반환하지 않으며 서식 지정 기준이 충족되지 않습니다.

이 모든 것이 언급 된 제한으로 잘 작동하지만 Zar가 찾고있는 것이 정확히 아닙니다. 그는 계속해서 열에 데이터를 추가하고 조정하면서 매주 사용되는 열 수를 감지하는 공식을 원합니다. 추가 된 데이터를 고려하기 위해 공식을 수동으로 편집 할 필요없이 그에 따라 공식. 즉, H 열에 데이터를 추가하면 추가 된 열을 고려하여 수식이 자동으로 조정되기를 원할 것입니다.

=COUNTA(B1:H1)=7)

=COUNTA(B1,D1,F1,H1)=4 =COUNTA(C1,E1,G1)=3

그것은 분명히 더 복잡한 요구입니다. 문제에 접근하는 가장 좋은 방법은 셀 범위를보고 세 가지 기준 중 하나가 충족되는지 확인할 수있는 사용자 정의 함수 (매크로)를 만드는 것입니다.

다음 매크로를 고려하십시오.

Function CellChk(crng As Range) As String     Dim iNumOdds As Integer     Dim iNumEvens As Integer     Dim iOdds As Integer     Dim iEvens As Integer     Dim iTots As Integer     Dim iTotCells As Integer     Dim rWork As Range     Dim rCell As Range     Dim iLastCol As Integer     Dim sTemp As String

iOdds = 0     iEvens = 0     iTots = 0

' Figure out the real last column in the worksheet and set range     iLastCol = ActiveSheet.Cells.Find(What:="*", _         SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, _         LookIn:=xlFormulas).Column     Set rWork = Range(Cells(crng.Row, 2), Cells(crng.Row, iLastCol))



iTotCells = rWork.Count     iNumOdds = (iTotCells + 1) \ 2    ' Number of odd columns     iNumEvens = iTotCells - iNumOdds  ' Number of even columns

For Each rCell In rWork         If rCell <> "" Then             If ((rCell.Column - 1) Mod 2) = 1 Then                 iOdds = iOdds + 1             Else                 iEvens = iEvens + 1             End If             iTots = iTots + 1         End If     Next rCell

sTemp = ""

If iTots = iTotCells Then         sTemp = "t"

ElseIf iOdds = iNumOdds Then         sTemp = "o"

ElseIf iEvens = iNumEvens Then         sTemp = "e"

End If     CellChk = sTemp End Function

확인하려는 행에 주소를 전달하여 매크로를 사용합니다.

예를 들어 조건부 서식 규칙을 A3 셀에 적용하는 경우 매크로에 B3 또는 C3 주소 (A3를 제외한 모든 주소)를 전달하면 순환 참조가 발생합니다. 매크로는 해당 행에 사용 된 마지막 셀을 찾은 다음 그 안에 무언가가있는 홀수 및 짝수 셀 수를 결정합니다. 매크로는 4 개의 값 중 하나를 반환합니다. 첫 번째 기준이 충족되면 (B 열로 시작하는 행의 모든 ​​셀에 무언가가 있음) “t”가 반환됩니다. 모든 홀수 열 (B가 첫 번째 홀수 열임)에 무언가가 있으면 “o”가 반환됩니다. 모든 짝수 열 (C가 첫 번째 짝수 열임)

그 안에 뭔가가 있으면 “e”가 반환됩니다. 세 가지 기준 중 어느 것도 충족되지 않으면 함수는 아무 것도 반환하지 않습니다.

수식 평가에 의존하는 세 가지 조건부 서식 규칙을 설정해야합니다. 다음은이 매크로와 함께 사용할 수있는 세 가지입니다.

=CellChk(B1)="t")

=CellChk(B1)="o")

=CellChk(B1)="e")

이 예제는 A1 셀에 조건부 서식을 적용하기위한 것입니다. 매크로가 분석 할 올바른 행으로 셀 참조를 조정하십시오. 단일 셀 (이 예에서는 B1)을 지정하더라도 매크로는 실제로 볼 행의 셀 수를 계산합니다.

_ 참고 : _

이 페이지 (또는 ExcelTips 사이트의 다른 페이지)에 설명 된 매크로를 사용하는 방법을 알고 싶다면 유용한 정보가 포함 된 특별 페이지를 준비했습니다.

link : / excelribbon-ExcelTipsMacros [새 브라우저 탭에서 특별 페이지를 열려면 여기를 클릭하세요].

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

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