엑셀 VBA 동일한 셀의 배경색을 구분하여 값을 합산하는 방법

엑셀 VBA 동일한 셀의 배경색을 구분하여 값을 합산하는 방법

 

환경: Microsoft Excel 2010

 

특정 영역에 동일한 색상셀을 구분해서 해당 값을 합치는 방법에 대해 알아 보겠습니다. 기능은 VBA 코드로 구현해야 합니다. 엑셀에서 제공하는 함수로 만들 수는 없습니다. VBA 코드로 어떻게 동일한 색상의 셀 값을 합치는지 알아 보겠습니다. 

 

▼ 먼저 실행할 사용자 정의 함수를 만들기 위해 [개발 도구] 탭 > [Visual Basic] 리본 메뉴를 클릭합니다.  

 

▼ VBA 편집 창에서 왼쪽 탐색기로 갑니다. 탐색기 목록에서 모듈 아래 Module1 이 생성되어 있을 겁니다. 이곳에 함수를 만들어야 합니다. Module1 을 선택하고 오른쪽 에디터로 가서 함수를 작성합니다. 작성한 함수 명은 사용자 정의 함수 명이 됩니다.

 

▼ 함수에 들어갈 소스에 대한 설명을 하겠습니다. 먼저 함수에서 필요한 인수는 2개 입니다. 첫 번째 인수인 colorSell 은 셀 주소를 가리킵니다. 두 번째 인수는 값들의 전체 영역을 포함하고 있습니다. 소스의 For Each 문에서 두 번째 인수로 받은 영역의 셀을 하나씩 꺼낸 후 색깔이 같은지 비교합니다. 비교하는 문장은 다음과 같겠죠.

 

If colorSell.Interior.ColorIndex = r.Interior.ColorIndex Then

 

▼ 비교해서 동일한 색상값이면 셀의 주소를 hap 변수에 저장합니다. 구분자로 “,” 를 주고 문자열을 만든 것은 한번에 합산하기 위해서 입니다.

 

hap = hap & r.Address & ","

 

▼ 이렇게 합치다 보면 마지막에 "," 가 찍힙니다. 아래는 이것을 삭제하는 소스입니다. 

 

hap = Left(hap, Len(hap) - 1)

 

▼ Range 함수를 이용해서 영역으로 캐스팅한 후 rhap 변수에 값을 저장합니다. 마지막으로 Application.Sum 함수를 이용해서 영역에 있는 값들을 합산합니다. 합산한 값을 변수명에 집어 넣으면 함수 반환 값이 되는 것이죠.

 

hap = Left(hap, Len(hap) - 1) Set rhap = Range(hap) 동일한색상_합계 = Application.Sum(rhap)

 

▼ 아래 소스는 전체 함수의 내용입니다.

 

Function 동일한색상_합계(colorSell As Range, rang As Range)         For Each r In rang         If colorSell.Interior.ColorIndex = r.Interior.ColorIndex Then             hap = hap & r.Address & ","         End If     Next     hap = Left(hap, Len(hap) - 1)     Set rhap = Range(hap)     동일한색상_합계 = Application.Sum(rhap)   End Function

 

▼ 사용자 정의 함수가 만들어 졌으면 엑셀 함수 마법사에서 사용할 수 있습니다. 함수 마법사 창에서 범주를 사용자 정의로 바꿉니다. 그럼 새로 만든 함수 명이 보일 겁니다.

 

▼ 함수에서 첫 번째 인수는 합산할 셀의 배경색이 있는 주소가 넘어갑니다. 그러니까 노란색이 있는 셀 값들을 합치고 싶다면 노란색이 들어간 셀 주소를 ColorSell 에 입력해야 합니다. 두 번째는 계산할 영역이 됩니다. 이렇게 해서 동일한 색상을 가진 셀의 값을 합치는 함수를 완성해 보았습니다.