통합 문서 전체의 마스터 조건부 서식:외부 데이터에서 형식 트리거

외부 통합 문서 값을 기반으로 하는 조건부 서식을 사용하면 다른 통합 문서에 저장된 데이터를 기반으로 한 Excel 통합 문서의 셀 서식을 자동으로 지정할 수 있습니다. 이 기능은 비즈니스 환경에서 여러 파일에 대한 동적 보고서, 대시보드 및 데이터 비교를 만드는 데 필수적입니다.

이 튜토리얼에서는 외부 통합 문서 값을 기반으로 조건부 서식을 실행하는 방법을 보여줍니다.

한 파일에서는 실제 분기별 매출을 추적하고 다른 파일에서는 분기별 매출 목표를 추적한다고 가정해 보겠습니다. 실제 시트에서는 외부 파일에서 올바른 목표를 가져와 목표보다 낮은 실제 매출을 강조표시하려고 합니다.

방법 1:외부 참조가 있는 도우미 열

이는 모든 Excel 버전에서 작동하는 가장 안정적인 방법입니다. 도우미 열의 외부 참조와 함께 워크시트 수식을 사용할 수 있습니다. 도우미 열의 값을 기준으로 조건부 서식을 적용합니다.

1단계:통합문서 준비

먼저 위의 샘플 데이터를 사용하여 두 통합 문서를 모두 만들고 저장하세요.

• 'Sales Target.xlsx'를 생성하고 목표 데이터를 입력하세요.
• 바탕화면이나 특정 폴더에 저장하세요.
• 'Actual Sales.xlsx'를 생성하고 실제 판매 데이터를 입력하세요.
• 같은 위치에 저장하세요.

2단계:외부 참조를 사용하여 도우미 열 만들기

• 'Actual Sales.xlsx'에 도우미 열을 추가합니다(G열부터 시작).
• G2 셀을 선택하고 다음 수식을 삽입하세요.

=[SalesTarget.xlsx]Quarterly_Targets!B2

• H2, I2, J2 셀의 수식을 자동 완성하려면 수식을 오른쪽으로 드래그하세요.

• 값을 업데이트하려면 Sales Target.xlsx를 선택하세요. 파일.

• G2:J2 셀을 선택하세요.
• 수식을 아래로 드래그하여 나머지 셀의 수식을 자동 완성하세요.

3단계:도우미 열을 사용하여 조건부 서식 적용

이제 조건부 서식에 내부 참조를 사용하세요.

• 셀 범위(B2:B6)를 선택하세요.
• 홈으로 이동 탭>> 조건부 서식을 선택합니다.>> 새 규칙을 선택합니다. .
• 수식을 사용하여 서식을 지정할 셀 결정을 선택합니다. .
• 다음 수식을 입력하세요:

• 형식을 클릭하세요.>> 연한 빨간색 채우기 색상을 선택합니다.
• 확인을 클릭하세요. .

더 많은 규칙 추가:

필요에 따라 각 분기마다 반복합니다.

2분기:

• 다음 수식을 입력하세요:

• 형식을 클릭하세요.>> 연한 파란색 채우기 색상을 선택하세요.
• 확인을 클릭하세요. .

3분기:

• 다음 수식을 입력하세요:

• 형식을 클릭하세요.>> 밝은 녹색 채우기 색상을 선택합니다.
• 확인을 클릭하세요. .

4분기:

• 다음 수식을 입력하세요:

• 형식을 클릭하세요.>> 연한 보라색 채우기 색상을 선택하세요.
• 확인을 클릭하세요. .

4단계:도우미 열 숨기기(선택사항)

• G:J 열을 선택하세요.
• 마우스 오른쪽 버튼 클릭>> 숨기기 선택 .

데이터에는 외부 통합 문서 값을 기반으로 한 조건부 서식이 표시되지만 Excel에서는 외부 참조 제한을 피하기 위해 내부 도우미 열을 사용합니다.

방법 2:파워 쿼리 솔루션 사용

파워 쿼리는 Excel 365 또는 Excel 2016+ 사용자에게 강력한 솔루션을 제공합니다.

1단계:파워 쿼리로 외부 데이터 가져오기

• 'Actual Sales.xlsx' 통합문서를 엽니다.
• 데이터로 이동 탭>> 데이터 가져오기 선택>> 파일에서를 선택하세요.>> 통합문서에서를 선택하세요. .
• 'Sales Target.xlsx' 파일을 찾아서 선택하세요.
• '분기별_목표' 표를 선택하세요.
• 가져오기를 클릭합니다. .

• 탐색기에서 창>> 데이터 시트를 선택하세요.
• 데이터 변환을 클릭하세요. .

• 파워 쿼리 편집기에서:
  • 필요에 맞게 열 이름을 바꿉니다(Target_Q1, Target_Q2 등).
  • 홈으로 이동 탭>> 닫기 및 로드 .

• • 표 선택>> 새 워크시트를 선택합니다. .
  • 확인을 클릭하세요. .

2단계:조건부 서식 적용

이제 솔루션 1에서처럼 가져온 데이터에 표준 조건부 서식을 사용하되 내부 데이터만 참조하세요.

• 셀 범위(B2:B6)를 선택하세요.
• 홈으로 이동 탭>> 조건부 서식을 선택합니다.>> 새 규칙을 선택합니다. .
• 수식을 사용하여 서식을 지정할 셀 결정을 선택합니다. .
• 다음 수식을 입력하세요:

=B2 <Quarterly_Targets!$B2

• 형식을 클릭하세요.>> 연한 빨간색 채우기 색상을 선택합니다.
• 확인을 클릭하세요. .

• 나머지 분기에 대해 더 많은 규칙을 추가하세요.

2분기:

=C2 <Quarterly_Targets!$C2

3분기:

=D2 <Quarterly_Targets!$D2

4분기:

=E2 <Quarterly_Targets!$E2

• 타겟이 변경될 때마다 파워 쿼리를 새로 고칩니다.
  • 마우스 오른쪽 버튼 클릭>> 새로고침 선택 .
• 데이터가 자주 변경되는 경우 자동 새로고침을 예약할 수 있습니다.
• 데이터로 이동 탭>> 쿼리 및 연결을 선택합니다. .
• 검색어를 마우스 오른쪽 버튼으로 클릭합니다.  >> 속성을 선택합니다. .

• 새로고침 간격>> 5 삽입 분.
• 확인을 클릭하세요. .

이 방법은 외부 데이터를 자동으로 새로 고치고 참조 제한을 피합니다.

방법 3:완전 자동화를 위한 VBA 매크로

VBA에 익숙하다면 외부 데이터를 기반으로 조건부 서식을 업데이트하는 매크로를 만들 수 있습니다. 참조 파일이 닫혀 있어도 자동으로 서식을 적용하여 실제와 목표를 비교합니다.

VBA 편집기를 열려면:

• 실제 판매 통합문서를 엽니다.
• 개발자로 이동 탭>> Visual Basic 선택 . 또는 Alt + F11을 누르세요. .
• 프로젝트 창에서 통합문서를 마우스 오른쪽 버튼으로 클릭하고,
• 삽입 을 선택하세요.>> 모듈을 선택합니다. .

• 다음 VBA 코드를 복사하여 붙여넣으세요.

VBA 코드:

Sub HighlightSalesBelowTarget()
 Dim targetFilePath As String
 targetFilePath = "C:\Users\Sales Target.xlsx" ' <--- Update this to your file path
 
 Dim wbTarget As Workbook
 Dim wsTarget As Worksheet
 Dim wsActual As Worksheet
 Dim i As Long, j As Long
 Dim salesValue As Variant, targetValue As Variant
 
 Set wsActual = ThisWorkbook.Sheets("Performance_Data")
 Set wbTarget = Workbooks.Open(targetFilePath, ReadOnly:=True)
 Set wsTarget = wbTarget.Sheets("Quarterly_Targets")
 
 ' Data rows: 2 to 6, columns: 2 (B/Q1) to 5 (E/Q4)
 For i = 2 To 6 ' Rows: products
 For j = 2 To 5 ' Columns: Q1-Q4
 salesValue = wsActual.Cells(i, j).Value
 targetValue = wsTarget.Cells(i, j).Value
 If IsNumeric(salesValue) And IsNumeric(targetValue) Then
 If salesValue < targetValue Then
 wsActual.Cells(i, j).Interior.Color = RGB(255, 199, 206) ' Light red
 Else
 wsActual.Cells(i, j).Interior.Pattern = xlNone ' No color
 End If
 End If
 Next j
 Next i
 wbTarget.Close SaveChanges:=False
 MsgBox "Highlighting complete.", vbInformation
End Sub

• 판매 목표 파일의 전체 경로로 파일 경로를 업데이트하세요.
• 매크로가 대상 통합 문서를 엽니다.
• 각 제품과 분기를 반복해 보세요.
• 판매액이 목표보다 작으면 셀이 연한 빨간색으로 강조표시됩니다.
• 매크로는 대상 통합문서를 자동으로 닫습니다.

저장 및 실행:

• 통합문서를 매크로 지원 파일(.xlsm)로 저장하세요.
• 개발자로 이동 탭>> 매크로 선택 .
• HighlightSalesBelowTarget 선택>> 실행을 클릭하세요. .

출력:

작동하지 않는 것:직접 외부 참조 및 명명된 범위

일부 Excel 버전에서는 "조건부 서식 기준에 대해 다른 통합 문서에 대한 참조를 사용할 수 없습니다."라는 경고를 표시합니다.

• 직접 외부 통합문서 참조 (예:=[Sales_Targets.xlsx]Quarterly_Targets!B2)는 조건부 서식 규칙에서 허용되지 않습니다. Excel에서 오류가 발생합니다.
• 이름이 지정된 범위 외부 통합 문서에 정의된 내용은 다른 통합 문서의 조건부 서식에서 참조할 수 없습니다.
• INDIRECT 또는 유사한 기능을 사용해도 이 컨텍스트에서는 파일 전체에서 작동하지 않습니다.

조건부 서식 규칙에서 외부 값을 사용하는 직접적인 기본 방법은 없습니다.

권장사항

• 대부분의 기업에 해당: 파워 쿼리를 사용하여 외부 데이터를 가져옵니다. 강력하고 새로 고침을 지원하며 하나의 통합 문서 내에 모든 논리를 유지합니다.
• 임시 또는 빠른 확인: 두 파일을 모두 열어두어도 괜찮다면 외부 참조가 있는 도우미 열을 사용하세요.
• 자동화되고 지속적인 솔루션의 경우: 특히 대규모 데이터세트의 경우 자동 자동화 및 서식 지정에 VBA를 사용하세요.

결론

외부 조건부 서식은 동적 파일 간 데이터 시각화를 가능하게 하는 강력한 기능입니다. 시나리오와 편의성에 따라 원하는 방법을 사용할 수 있습니다. 항상 설정을 철저하게 테스트하고 나중에 참조하고 팀 구성원과 협력할 수 있도록 외부 종속성에 대한 명확한 문서를 유지하는 것을 잊지 마세요.

Excel에서는 기본적으로 외부 통합 문서의 값을 기반으로 조건부 서식을 트리거하는 것이 불가능합니다.