🎨 엑셀 셀 색상별·글자색별 합계, 평균 계산 매크로 만들기 (실무 꿀팁)

티스토리 뷰

카테고리 없음

🎨 엑셀 셀 색상별·글자색별 합계, 평균 계산 매크로 만들기 (실무 꿀팁)

RichFebru 2025. 10. 30. 07:25

엑셀을 쓰다 보면, 특정 색으로 표시한 셀들만 따로 합계나 평균을 구하고 싶을 때가 있습니다.
예를 들어, 보고서에서 ‘완료된 항목’을 회색으로 칠하거나, ‘긴급 항목’을 빨간 글씨로 표시했을 때,
이 항목들만 계산하는 기능이 있다면 정말 편하겠죠?

하지만 아쉽게도 엑셀 기본 함수(SUM, AVERAGE 등) 만으로는 색상 기준 계산이 불가능합니다.
그래서 오늘은 매크로(VBA) 를 이용해 셀 배경색 또는 글자색(Font Color) 에 따라
합계, 평균을 자동으로 구하는 방법을 알아보겠습니다.

🧩 1. 기본 개념 – 색상은 숫자로 인식된다

엑셀에서 셀의 색상은 내부적으로 ColorIndex 또는 RGB 값으로 저장됩니다.
즉, 빨강·파랑 같은 색깔 이름이 아니라, 각 색상에 고유한 숫자가 있다는 뜻입니다.

예를 들어,

노란색 = 6
빨간색 = 3
파란색 = 5

이런 식으로 저장되어 있어요.
따라서 매크로에서는 이 숫자를 읽어서 “같은 색상만 계산” 하도록 지정하면 됩니다.

🧮 2. 색상별 합계 매크로 코드

아래는 VBA(Visual Basic for Applications) 코드입니다.
Alt + F11 → 모듈 삽입 → 아래 코드를 붙여 넣으면 됩니다.

Function SumByColor(CellColor As Range, rRange As Range)
    Dim cSum As Double
    Dim c As Range
    Application.Volatile
    For Each c In rRange
        If c.Interior.Color = CellColor.Interior.Color Then
            cSum = cSum + c.Value
        End If
    Next c
    SumByColor = cSum
End Function

✅ 사용법:

=SumByColor(A1, B2:B20)
→ A1 셀과 같은 색의 셀만 B2:B20 범위에서 찾아 합계 계산.

🌈 3. 글자(Font) 색상별 합계 매크로

배경색이 아니라, 글자색 기준으로 합계를 구하고 싶다면 이렇게 바꿉니다.

Function SumByFontColor(FontColor As Range, rRange As Range)
    Dim cSum As Double
    Dim c As Range
    Application.Volatile
    For Each c In rRange
        If c.Font.Color = FontColor.Font.Color Then
            cSum = cSum + c.Value
        End If
    Next c
    SumByFontColor = cSum
End Function

✅ 사용법:

=SumByFontColor(A1, B2:B20)
→ A1 셀의 글자색과 같은 셀들의 합계를 계산합니다.

📊 4. 색상별 평균 계산 매크로

합계가 된다면 평균도 간단합니다.
합계와 카운트를 나눠주기만 하면 되죠.

Function AvgByColor(CellColor As Range, rRange As Range)
    Dim cSum As Double
    Dim cnt As Long
    Dim c As Range
    Application.Volatile
    For Each c In rRange
        If c.Interior.Color = CellColor.Interior.Color Then
            cSum = cSum + c.Value
            cnt = cnt + 1
        End If
    Next c
    If cnt > 0 Then
        AvgByColor = cSum / cnt
    Else
        AvgByColor = 0
    End If
End Function

✅ 사용법:

=AvgByColor(A1, B2:B20)
→ A1과 같은 색상 셀의 평균을 계산합니다.

💡 5. 자주 쓰는 팁

항목	설명
함수 자동 업데이트	셀 색상이 바뀌면 즉시 반영되지 않을 수 있습니다. Ctrl + Alt + F9 (전체 수식 다시 계산)을 눌러주세요.
조건부서식 색상	조건부서식으로 지정된 색상은 ColorIndex가 변하지 않아 적용되지 않습니다. 이 경우 별도 함수나 조건문을 활용해야 합니다.
시각적 요약	색상별 계산표를 별도 시트에 만들어두면 관리가 훨씬 편리합니다.

🚀 6. 매크로 파일 저장 시 주의사항

매크로가 포함된 엑셀 파일은 반드시
“.xlsm” 형식으로 저장해야 합니다.
그렇지 않으면 함수가 사라지거나 실행되지 않습니다.

📘 마무리

색상은 단순한 시각 효과가 아니라,
보고서의 상태를 구분하는 중요한 ‘데이터’가 될 수 있습니다.
이번 매크로를 이용하면 “색깔만 봐도 정리되는 엑셀”을 완성할 수 있습니다.

특히 업무 보고용, 재고 관리, 일정 현황표, 품질 관리표 등에 유용하게 쓰이죠.
직접 따라 하면서 자신만의 VBA 함수를 만들어보세요.
작은 자동화 하나가 일의 효율을 10배로 높여줍니다. 🚀

사용 빠른 가이드

모듈 가져오기: Excel → Alt+F11 → 삽입>모듈 → 파일>가져오기(Import) → ColorSumAvg.bas 선택
.xlsm로 저장: 샘플 파일을 매크로 사용 통합문서(.xlsm) 로 저장
Data 시트 구성
- B2/B3/B4: 배경색 참조(노랑/빨강/파랑)
- E2/E3/E4: 글자색 참조(빨강/초록/파랑 “Aa”)
- C2:C21: 배경색이 칠해진 숫자 데이터
- D2:D21: 글자색이 적용된 숫자 데이터
예시 수식(이미 입력됨)
- =SumByColor($B$2, $C$2:$C$21) → B2(노랑)과 같은 배경색 합계
- =AvgByColor($B$3, $C$2:$C$21) → B3(빨강)과 같은 배경색 평균
- =CountByColor($B$4, $C$2:$C$21) → B4(파랑)과 같은 배경색 개수
- =SumByFontColor($E$2, $D$2:$D$21) → E2(빨강)과 같은 글자색 합계
- =AvgByFontColor($E$3, $D$2:$D$21) → E3(초록)과 같은 글자색 평균
- =CountByFontColor($E$4, $D$2:$D$21) → E4(파랑)과 같은 글자색 개수
- =SumByRGB(255,0,0, $C$2:$C$21) → 빨강(RGB) 배경색 합계