엑셀 - 색깔이 같은 셀만 합계 구하기

Posted by sabper computer : 2007. 10. 31. 11:09
엑셀 - 색깔이 같은 셀만 합계 구하기

엑셀 합계를 구할때 색깔이 같은 셀들의 합계만 구하고 싶을때 메크로 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 정도로 선언하면 그이상도 충분히 계산될 듯 싶어요.
요즘에 엑셀을 다루지 않아 테스트는 안해봤지만....요.
혹시 해보신분들 트랙백이나 댓글 남겨주세요

'computer' 카테고리의 다른 글

바탕화면 꾸미기  (0) 2007.12.05
엑셀 - 색깔이 같은 셀만 합계 구하기  (8) 2007.10.31
효과음은 나오는데 동영상 재생시 사운드카드 오류날경우  (0) 2007.09.13
에버레스트  (0) 2007.06.07
wake on lan  (0) 2007.04.27
리눅스 정복기 #2  (0) 2007.04.26

댓글을 달아 주세요

  1. 2008.10.29 16:03 지연  댓글주소  수정/삭제  댓글쓰기

    굉장히 좋은 정보네요^^
    그런데 제가 사용하는 엑셀에서요..
    자료값에서 문자가 있는것도 있거든요..예를들어 F
    만약에,핑크색 셀들의 합계를 구하려고 하는데
    핑크색 셀중에..F인게 있으니깐..
    합계가 안나오고..오류가 뜨는데..
    혹시..문자가 섞여있을때 가능한 방법도 있나요??
    도와주세요 ㅠㅠ

  2. 2009.02.13 11:38  댓글주소  수정/삭제  댓글쓰기

    좋은 자료 감사합니다.
    좀 전에 엑셀에서 실행해 봤는데,
    두 가진 의문 사항이 있어서 댓글 남깁니다.
    1. 위에 말씀하신 색번호 말고 나머지 색도 번호좀 알 수 있을까요?

    2. 정수는 문제없이 합산이 되는데, 소숫점 이하는 합산에 적용이 안되던데요, 방법이 있을까요?

  3. 2009.09.17 12:39 이철희  댓글주소  수정/삭제  댓글쓰기

    계산값이 대략 32,767이면 계산이 되질 않습니다. 해결방법이 있는지요

  4. 2009.11.12 11:35  댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다

  5. 2011.11.07 14:54 나리  댓글주소  수정/삭제  댓글쓰기

    저도 유용하게 쓴 매크로중 하나여서 감사말씀 먼저드려요^^
    위에분들처럼 정수는 합산되는데 소수점이하가 적용이 안되는데 방법이 없나요??
    댓글이 안달려있어서 또 문의드려요

  6. 2012.07.12 13:59 go  댓글주소  수정/삭제  댓글쓰기

    double로 선언 하니까 계산 되네요 감사 합니다 굉장히 유용하게 쓰겠네요

    모르시는 분들을 위해서 다시 적을께요
    Function Sum_Color(rng As Range, color As Integer, ctype As Variant)
    Dim onerng As Range, sum As Double '함수선언

    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

    '함수 선언 앞에 기존 integer->double로 변경 하면 되네요

  7. 2012.10.24 17:36 김영아  댓글주소  수정/삭제  댓글쓰기

    전 왜 오류가 날까요..? 이름이 잘못되었다고 뜨네요. ㅠ_ㅠ

  8. 2013.08.01 20:05 조익현  댓글주소  수정/삭제  댓글쓰기

    덕분에 궁금한점이 잘 해결되었습니다.
    =sum_color 값이 입력되어 있는 셀을 더블클릭후 엔터키를 쳐야지만 합산이 적용되는데 셀색상이 추가되거나 빠지면 자동으로 합계 변경이 되게 할 수는 없나요?
    엑셀 2007입니다.

 «이전 1 ··· 16 17 18 19 20 21 22 23 24 ··· 120  다음»