[ 엑셀 VBA ] 같은 색깔의 셀 합계 구하기, 사용자 정의함수 새로고침

엑셀을 사용하다보면

특정 색상의 셀에만 포함된 값의 합계를 구하고 싶을 때가 있습니다.

예를들어, 빨간색으로 표시된 중요한 데이터만 계산하거나,

색으로 구분된 데이터의 비용을 합산해야 하는 경우와 같은 것들입니다.

 

이번 블로그에서는

엑셀에서 특정 색상의 셀에 포함된 값의 합계를 구하는 사용자 정의함수를

간단하게 만들어 보겠습니다.

※ 사용자 정의함수 등록 방법 : 사용자 정의함수 등록하고 다른 엑셀 파일에서 호출하기

 구현내용

 

=SumByColor(기준셀, 계산 범위)

 

아래 그림과 같이 같은 색의 셀이 있는 데이터의 합계만 구하는 함수입니다.

 

 전체코드

 

아래는 전체 코드입니다.

Alt + F11로 VBA 편집기 실행 후 모듈에 추가하시면 됩니다.

Function SumByColor(cellAddress As Range, colorRange As Range) As Double
    Dim targetColor As Long
    Dim cell As Range
    Dim total As Double
    
    targetColor = cellAddress.Interior.Color
    
    total = 0
    For Each cell In colorRange
        If cell.Interior.Color = targetColor Then
            If IsNumeric(cell.Value) Then
                total = total + cell.Value
            End If
        End If
    Next cell

    SumByColor = total
End Function

 

반응형

 코드설명

 

첫번째 인자(cellAddress)에서 색상을 가져와 targetColor 변수에 넣습니다.

targetColor = cellAddress.Interior.Color

 

합계를 저장할 변수를 초기화합니다.

total = 0

 

두번째 인자로 받은 셀의 범위의

각 셀의 색을 확인하고,

첫번째 인자로 받은 셀 주소의 색과 같다면, 더합니다.

    For Each cell In colorRange
        If cell.Interior.Color = targetColor Then
            If IsNumeric(cell.Value) Then
                total = total + cell.Value
            End If
        End If
    Next cell

 

IsNumberic() 함수를 통해 

해당 값이 숫자인지 확인하고, 숫자가 맞다면 계산을 합니다.

 

728x90

 바꾸어야 할 부분

 

IsNumberic() 함수가 있는 조건문은 삭제하고 사용하셔도 좋을 것 같습니다.

If IsNumeric(cell.Value) Then
    total = total + cell.Value
End If

 

위 코드가 있음으로 해서 

숫자인지 확인하고, 숫자인 부분만 정확히 계산할 수 있습니다.

 

하지만 사용자가 잘못 입력했을 경우

잘못 입력한 것을 확인하지 못하고, 잘못 입력된 부분을 계산하지 않기에

엉뚱한 값이 나올 수 있습니다.

 

IsNumberic() 함수로 조건을 작성했던 부분을 삭제한

수정한 코드입니다.

Function SumByColor(cellAddress As Range, colorRange As Range) As Double
    Dim targetColor As Long
    Dim cell As Range
    Dim total As Double
    
    targetColor = cellAddress.Interior.Color
    
    total = 0
    For Each cell In colorRange
        If cell.Interior.Color = targetColor Then
            total = total + cell.Value
        End If
    Next cell
    
    SumByColor = total
End Function

 

 주의할 점

 

이렇게 VBA를 통해 사용자 정의함수를 만들어 사용하게 되면

기본 엑셀 함수와 다르게 

데이터가 바뀌었을 때 자동으로 계산이 되지 않습니다.

 

데이터의 내용이 바뀔 경우

Ctrl + Alt + F9를 눌러 계산을 다시해주셔야 합니다.

여기를 방문하시면 더 많은 엑셀 관련 자료를 확인할 수 있습니다.

엑셀 공부하기
VBA 공부하기