엑셀 - 색깔이 같은 셀만 합계 구하기
엑셀 합계를 구할때
색깔이 같은 셀들의 합계만 구하고 싶을때 메크로 vb editor을 이용하여 할 수 있다.
보기
위 그림첨럼 vb ediotr의 사용자 정의 함수로 채우기 색이 빨간색인 셀들의 합만 구할 수 있다.
물론 채우기색뿐만 아니라 글자색깔에 별로도 합계를 구할 수 있다.
이러한 조건의 함수를 vb editor을 이용하여 사용자정의 함수로 만들 수 있다.
1. 일단 엑셀에서
도구 -> 메크로 -> Visual Basic Editor 로 이동합니다.
2. 다음 그림과 같이 메뉴 중
삽입 - 모듈 을 클릭합니다.
↓
3.
모듈 창에 다음과 같은 코드를 붙여넣기 합니다.
/------------------------------------------------------------------------------------------/
Function Sum_Color(rng As Range, color As Integer, ctype As Variant)
Dim onerng As Range, sum As Integer
'함수선언
If ctype = 0 Then '타입이 0일 경우(글자색깔별)
sum = 0
For Each onerng In rng '합계를 구할 셀들의 범위지정 (지정범위까지 아래 2행 반복)
If onerng.Font.ColorIndex = color Then '색깔지정 숫자가 같을 경우
sum = sum + onerng.Value '합계 계산
End If
Next
Sum_Color = sum
Else '타입이 0이 아닐경우 (채우기색깔별)
sum = 0
For Each onerng In rng
If onerng.Interior.ColorIndex = color Then
sum = sum + onerng.Value
End If
Next
Sum_Color = sum
End If
End Function
/------------------------------------------------------------------------------------------/
초록색 부분은 코드에 대한 설명이다.
타입이 0일경우 font.colorindex 값(글자색깔에 대한 숫자)을 비교하여 합계를 구하고
타입이 0이 아닐경우 Interior.colorindex 값(채우기색깔에 대한 숫자)을 비교하여 합계를 구한다.
붙여넣기를 모듈창에 완료한 상태입니다.
그런 후 상단의 디스켓모양의 저장하기 버튼을 눌러서 저장합니다. 엑셀파일 형태로 저장됩니다.
엑셀 자체에 저장되는것이 아니라 지금 열려있는 엑셀파일에 저장되는 것입니다.
따라서
다른 엑셀 파일에는 적용이 안됩니다.
4. 이제 방금 정의 한 함수를 이용하여
글자색깔이 같은 셀만 합계를 구해보겠습니다.
위와같이 합계를 구할 데이터를 입력하고 글자색깔별 / 채우기 색깔 별로 합계를 구했습니다.
사용법은 =sum_color(
범위,
색번호,
타입) 입니다
범위는
합계를 구할 셀들의 범위입니다.
색번호는 각 색깔들의 번호입니다. 빨강: 3 / 초록 : 4 / 파랑 : 5 / 노랑 : 6 / 검정 : 0 / 흰색 : 2
타입 0은
글자색깔조건 이고,
타입 1은
채우기 색깔조건 입니다.
위 그림처럼 글자색깔인 빨간색인 셀들의 합들을 구하려면 다음과 같이 수식을 입력합니다.
=sum_color(C4:H4,3,0)
이렇게 하면 글자색깔별 혹은 채우기색깔 별로 합계를 구할 수 있습니다.
추가사항
이포스트 올린지 너무 오래되서 댓글을 못봤었는데 몇년에 보니 문제점이 있었네요..
32,767 까지만 계산되고 소수점이나 그이상 계산 안되는 거는
아마도 sum 변수 integer로 선연되 있어서 그럴거 같네요.
double 정도로 선언하면 그이상도 충분히 계산될 듯 싶어요.
요즘에 엑셀을 다루지 않아 테스트는 안해봤지만....요.
혹시 해보신분들 트랙백이나 댓글 남겨주세요
invalid-file
invalid-file
invalid-file