🎨 엑셀 셀 색상별·글자색별 합계, 평균 계산 매크로 만들기 (실무 꿀팁)
엑셀을 쓰다 보면, 특정 색으로 표시한 셀들만 따로 합계나 평균을 구하고 싶을 때가 있습니다.
예를 들어, 보고서에서 ‘완료된 항목’을 회색으로 칠하거나, ‘긴급 항목’을 빨간 글씨로 표시했을 때,
이 항목들만 계산하는 기능이 있다면 정말 편하겠죠?
하지만 아쉽게도 엑셀 기본 함수(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) 배경색 합계
값이 즉시 갱신되지 않으면 Ctrl + Alt + F9(전체 재계산) 또는 매크로 ForceFullRecalc를 실행하세요.