☰

수식에서 참조되는 셀 서식 지정

tag-icon • tag-icon microsoft-excel worksheet-function vba
수식에서 참조되는 셀 서식 지정

두 개의 시트가 있는데, 하나는 두 번째 시트에서 데이터를 가져오는 수식이 포함되어 있습니다. 두 번째 시트에서 데이터를 가져온 경우 셀 녹색을 강조 표시하려면 매크로나 일부 조건부 형식을 만들어야 합니다. 시트 2는 원시 데이터이고 시트 1은 데이터를 여러 테이블로 분류한 것입니다.

내 질문을 명확히 하기 위해 원시 데이터 시트와 소득 시트가 있습니다. 소득 시트에는 원시 데이터 시트의 테이블에서 데이터를 가져오는 공식이 있습니다. 내가하려는 것은 다음과 같습니다.손익계산서에서 호출/참조되는 원시 데이터 시트의 모든 셀을 강조 표시합니다.; 그렇게 하면 원시 데이터 시트의 내용이 소득 시트에 사용되지 않았거나 포함되지 않았는지 알 수 있습니다. 원시 데이터 시트에서 데이터를 가져온 손익계산서의 공식은 다음과 같습니다.

=IF(ISERROR(VLOOKUP($A643, 'Raw Data'!$A:$N, 4, FALSE)), "-", VLOOKUP($A643, 'Raw Data'!$A:$N, 4, FALSE))

A 열에는 동일한 열의 원시 데이터 시트에 나타나는 특정 코드가 있습니다. 이 정보가 도움이 되기를 바랍니다. 감사합니다! 아래에서 이 코드를 찾았으나 별도의 시트에서 확인하기 위해 조정하는 방법을 모르겠습니다.

Sub Button5_Click()

'PLEASE EDIT THIS FIRST BIT. 

Dim row As Integer
row = 1    ' THE STARTING ROW IN YOUR EXCEL SHEET

Dim numberOfRows As Integer
numberOfRows = 5    'THE TOTAL NUMBER OF ROWS YOUR WORKSHEET HAS

Dim columnWithFormula As String
columnWithFormula = "E"    ' THE COLUMN WHERE THE FORMULAs ARE (I suspect you are using B if your example is accurate))

Dim colourIndex As Integer
colourIndex = 26     ' WHAT COLOUR TO HIGHLIGHT COLUMNS. GOOGLE VBa COLOR INDEX

'AND STOP EDITING :)

For row = 1 To numberOfRows

If range(columnWithFormula & row).Value <> "" Then

   Dim result As String

   result = range(columnWithFormula & row).Formula

   result = Replace(result, "(", " ")
   result = Replace(result, ")", " ")
   result = Replace(result, "-", " ")
   result = Replace(result, "+", " ")
   result = Replace(result, "*", " ")
   result = Replace(result, "/", " ")
   result = Replace(result, "=", " ")
   result = Replace(result, ",", " ")

   Dim cells() As String
   cells = Split(Trim(result), " ")

   For j = 0 To UBound(cells)
    range(cells(j)).Interior.ColorIndex = colourIndex
   Next j

End If

Next row


End Sub

답변1

VBA 없이 조건부 서식을 사용하는 접근 방식에는 Excel 2013 이상에서 사용할 수 있는 FormulaText() 함수가 포함됩니다.

특정 시트의 이름이 수식에 존재하는지 확인하는 형식으로 조건부 수식을 만듭니다. 예를 들어 시트가 "원시 데이터"라고 불리는 경우 이 수식은 해당 시트를 참조하는 셀을 강조 표시합니다.

=ISNUMBER(SEARCH("raw data",FORMULATEXT(A2)))

여기에 이미지 설명을 입력하세요

이는 시트 이름은 수식에 포함되지 않지만 테이블 이름은 포함되는 구조적 참조를 사용하여 수식이 Excel 표를 참조하는지 확인하는 데에도 적용될 수 있습니다.

=ISNUMBER(SEARCH("RawDataTable",FORMULATEXT(A2)))

다양한 시트에 대해 다양한 조건부 수식을 사용하면 현재 시트에서 생성되지 않은 데이터를 빠르게 강조 표시하는 매우 효과적인 방법이 될 수 있습니다.

참고: 이론적으로 다른 시트에 대한 모든(구조화되지 않은) 참조는 !참조의 시트 이름 뒤에 있는 기호로 식별할 수 있지만 때로는 시트 전체에 걸쳐 적용되는 수식을 만들 때 현재 시트의 셀에 대한 참조도 있을 수 있습니다. 시트 이름을 포함하므로 주의해서 접근해야 합니다.

관련 정보