셀 배경색 기준으로 합계 구하기

안녕하세요.

 

자주 듣는 질문은 아니지만 벌써 여러 번 받은 질문 중 하나를 정리하려고 합니다.

셀 배경색을 기준으로 합계를 구하기 입니다.

 

실습 파일 다운로드

셀 배경색 기준 합계구하기.xlsx

0.01MB

완성 파일 다운로드

셀 배경색 기준 합계구하기.xlsm

0.01MB

 

 

담당자 A, B, C의 담당 항목이 무작위로 배치되어 있어 구분을 셀 배경색으로 했습니다.

담당 항목이 일정한 규칙으로 지정되어 있다면 엑셀 기본 기능이나 함수를 이용해 합계를 구할 수 있지만, 무작위로 선택되어 있어 셀 배경색을 기준으로 결과값을 구해야만 하는 상황입니다.

 

이 경우엔 VBA를 이용해 어렵지 않게 결과값을 구할 수 있습니다.

여러 가지 색상으로 구분되어 있을 때도 사용할 수 있도록 사용자 정의 함수를 만들어 결과값을 구하겠습니다.

 

단축키 <Alt + F11>을 눌러 VBA 편집기를 실행합니다.

[삽입]-[모듈]을 선택합니다.

모듈이 추가된 뒤 다시 [삽입]-[프로시저]를 선택합니다.

[프로시저 추가] 대화상자에서 새로 추가할 사용자 정의 함수 이름으로 SumColor를 적고, [형식]에서 Function을 선택한 뒤 [확인]을 누릅니다.

인수 두 개를 지정합니다.

첫번째 인수 범위는 합을 구할 셀 범위를 지정하고, 두번째 인수 조건셀은 합계를 구할 셀 배경색이 지정된 셀을 선택합니다.

코드를 작성합니다.

Public Function SumColor(범위 As Range, 조건셀 As Range)
    조건색 = 조건셀.Interior.ColorIndex
    
    For Each 비교셀 In 범위
        If 비교셀.Interior.ColorIndex = 조건색 Then
            결과 = 결과 + 비교셀.Value
        End If
    Next 비교셀
    
    SumColor = 결과
End Function

간단하게 사용자 정의 함수가 만들어 졌습니다.

 

이제 VBA 편집기를 닫고, 엑셀로 돌아가 만든 함수를 사용합니다.

[G2] 셀에 커서를 두고 =sum을 입력하면 표시되는 함수 목록에서 SumColor를 더블클릭합니다.

단축키 <Shift + F3>을 눌러 함수 인수 대화상자를 나타냅니다.

범위 인수에는 [B2:D6] 셀 범위를 절대 참조하여 나타내고, 조건셀 인수에는 [F2] 셀을 나타냅니다.

[확인]을 눌려 결과를 봅니다.

[채우기] 기능을 이용해 [G3], [G4] 셀 값도 구합니다.

 

마지막 작업으로 사용자 정의 함수를 포함하여 저장하기 위해 [파일]-[다른 이름으로 저장]을 선택해서 파일 형식을 [Excel 매크로 사용 통합 문서(*.xlsm)]으로 저장합니다.

 

필요하신 분들께 도움이 되길 바랍니다.

 

---

코드를 설명하겠습니다. (2022-11-28 추가)

Public Function SumColor(범위 As Range, 조건셀 As Range)
    조건색 = 조건셀.Interior.ColorIndex
    
    For Each 비교셀 In 범위
        If 비교셀.Interior.ColorIndex = 조건색 Then
            결과 = 결과 + 비교셀.Value
        End If
    Next 비교셀
    
    SumColor = 결과
End Function

 

사용자 지정 함수를 정의합니다.

두 개의 인수를 가지고 있으면 각 인수는 셀 범위나 셀을 참조합니다.

Public Function SumColor(범위 As Range, 조건셀 As Range)
    
End Function

 

조건셀 인수의 배경색을 조건색 변수에 넣습니다.

    조건색 = 조건셀.Interior.ColorIndex

 

범위의 각 셀을 하나씩 비교셀 변수에 넣어 비교셀의 배경색과 조건색 변수에 저장된 배경색을 비교합니다.

For Each ~ Next는 반복문으로 배열이나 컬렉션의 항목을 처음부터 끝까지 하나씩 가져옵니다.

    For Each 비교셀 In 범위
        If 비교셀.Interior.ColorIndex = 조건색 Then
            
        End If
    Next 비교셀

 

결과 변수에 현재 결과 변수에 저장된 값에 비교셀의 값을 더해서 넣습니다.

            결과 = 결과 + 비교셀.Value

 

결과 변수를 함수의 실행 결과 나타냅니다.

    SumColor = 결과