2024 최신 강의 업데이트!
더 간결하게 작성된 VBA코드와 영상 강의는 아래 최신 강의를 참고하세요! (영상 내용은 동일합니다.😉)
색깔 카운트 / 합계 구하기 공식
이번 강의에서는 매크로를 사용하지 않고 찾기 및 바꾸기와 이름정의 범위를 활용해 색깔별 카운트와 색깔별 합계를 구하는 방법을 소개해드렸습니다. 이번 강의에서 다룬 방법은 색깔별 카운트와 합계뿐 아니라 색깔별 평균, 색깔별 최대값을 구할 때에도 똑같이 응용할 수 있습니다.
매크로를 활용하기 위한 전체 명령문과 사용법은 아래 자세히 설명해드렸습니다.
색깔별 카운트
= COUNT( 색깔범위 )
색깔별 합계
= SUM( 색깔범위 )
- 단축키 Ctrl + F 키로 찾기 및 바꾸기 대화상자를 실행합니다. [옵션] 버튼을 눌러 상세 옵션을 활성화합니다.
찾기 및 바꾸기 대화상자에서 '서식'으로 이동합니다.
- [서식]을 서식 찾기로 이동한 뒤, [채우기] (또는 글꼴)에서 색깔별 카운트, 색깔별 합계를 구하기 위한 서식을 지정합니다.
색깔별 카운트, 색깔별 합계를 구하기 위한 서식을 지정합니다.
- [모두 찾기]를 클릭한 뒤, 검색결과에서 Ctrl + A 키를 눌러 검색 결과를 한 번에 선택합니다.
모두 찾기로 검색한 뒤, 검색 결과를 한 번에 선택합니다.
- 이름상자로 이동한 뒤, '내범위'로 이름정의범위를 추가합니다.
선택된 범위를 이름정의범위에 추가합니다.
- 빈 셀로 이동한 뒤, SUM 함수 (또는 COUNT 함수)로 색깔별 카운트, 색깔별 합계를 계산합니다.
함수로 색깔별 카운트, 색깔별 합계를 계산합니다.
매크로로 색깔별 카운트, 색깔별 합계 구하기
아래 VBA 명령문을 엑셀에 복사/붙여넣기 하여 사용자 함수를 추가하면 색깔별 카운트, 색깔별 합계를 손쉽게 구할 수 있습니다.
- 엑셀을 실행한 뒤, 단축키 Alt + F11 키로 매크로 편집기를 실행합니다. [삽입] - [모듈]로 새로운 모듈을 추가합니다.
매크로 편집기 - 삽입 - 모듈로 새로운 모듈을 추가합니다.
- 아래 명령문을 복사한 뒤, 모듈안에 붙여넣기 합니다. (매크로를 포함한 뒤, 파일 저장시에는 반드시 '매크로가 포함된 통합문서(*.xlsm)' 형태로 저장해야합니다.)
Option Explicit
Function SumByBgColor(Rng As Range, Ref As Range)
Dim r As Range: Dim v As Double
For Each r In Rng
If CheckColour(r) = CheckColour(Ref) Then v = Application.WorksheetFunction.Sum(v, r.Value)
Next
SumByBgColor = v
End Function
Function SumByFontColor(Rng As Range, Ref As Range)
Dim r As Range: Dim v As Double
For Each r In Rng
If CheckColour(r, True) = CheckColour(Ref, True) Then v = Application.WorksheetFunction.Sum(v, r.Value)
Next
SumByFontColor = v
End Function
Public Function CheckColour(r As Range, Optional isFont As Boolean = False)
Application.Volatile
If isFont = False Then CheckColour = r.Parent.Evaluate("GetColor(" & r.Address(False, False) & ")") Else CheckColour = r.Parent.Evaluate("GetFontColor(" & r.Address(False, False) & ")")
End Function
Public Function GetColor(r As Range): GetColor = r.DisplayFormat.Interior.Color: End Function
Public Function GetFontColor(r As Range): GetFontColor = r.DisplayFormat.Font.Color: End Function
- VBA 사용자 함수로 배경색이 같을 경우, 또는 글씨색이 같을 경우 합계를 구할 수 있습니다.
=SumByBgColor( 합계범위, 참조셀 )
'참조셀과 배경색이 같을 경우 합계를 구합니다.
=SumByFontColor(합계범위, 참조셀)
'참조셀과 글씨색이 같을 경우 합계를 구합니다.
- 명령문의 아래부분을 필요에 따라 바꾸면 색깔이 같을 경우 카운트, 합계, 최대값, 평균 등으로 손쉽게 계산할 수 있습니다.
'합계 구하기
Application.WorksheetFunction.Sum(v, r.Value)
'개수 구하기
Application.WorksheetFunction.CountA(v, r.Value)
'최대값 구하기
Application.WorksheetFunction.Max(v, r.Value)
'평균 구하기
Application.WorksheetFunction.Average(v, r.Value)
