Please Enable JavaScript!
Gon[ Enable JavaScript ]

엑셀(Excel) VBA – 셀에 적용된 같은 배경색 데이터 합산하기

엑셀(Excel)/VBA
반응형

엑셀(Excel) VBA – 셀에 적용된 같은 배경색 데이터 합산하기

 

환경 : Microsoft Excel 2010

 

이번예제는 같은 색상의 배경색을 가진 데이터에 대해서 합산을 구하는 기능입니다. 특별한 조건없이 사용자가 필요할 때 색상을 분류해서 입혀놨다면 그 분류 대로 값을 합산할수 있습니다.

 

먼저 엑셀에서 샘플을 위해 아래 처럼 색상을 분류해 놓았습니다. 이렇게 분류해 놓은 색상별로 합계를 구할 것입니다.

 

엑셀(Excel) VBA – 셀에 적용된 같은 배경색 데이터 합산하기

 

합계를 구하는 사용자 정의 함수는 두가지 인수를 받게 됩니다. 합계를 구할 영역과 색상값입니다. 색상값은 바로 옆에 만들어 두고 셀을 참조 했습니다. 3개의 색상에 대해 값을 구할 것입니다.

엑셀(Excel) VBA – 셀에 적용된 같은 배경색 데이터 합산하기

 

 

함수는 첫번째 인수로 받은 Range 개체를 For Each 로 하나씩 꺼내 색상이 맞는지 판단할 것입니다. 배경색 값은 Interior.Color 입니다. 두번째로 넘어온 Range 개체도 Interior.Color 비교하면 되겠죠두개의 값이 같다면 합산을 합니다.

 

Function 색상별합계(ran As range, col As range)

    Dim sumData As Single

   

    For Each k In ran

        If k.Interior.Color = col.Interior.Color Then

            sumData = sumData + k.Value

        End If

    Next

   

    색상별합계 = sumData

End Function

 

위와같이 만든 사용자 정의 함수를 사용해 보겠습니다. 계산값을 추출할 영역은 절대값으로 합니다.

엑셀(Excel) VBA – 셀에 적용된 같은 배경색 데이터 합산하기

 

 

절대값으로 했기 때문에 나머지 두가지 색상에 대한 값은 드래그 해서 수식을 적용합니다색상별로 제대로 값이 구해 진 것 같네요

엑셀(Excel) VBA – 셀에 적용된 같은 배경색 데이터 합산하기

 

 

반응형
Posted by 녹두장군

댓글을 달아 주세요

  1. 최기령 2015.01.22 00:06  댓글주소  수정/삭제  댓글쓰기

    매크로에 함수를 만들고 함수삽입을 통하여 입력을하였는데 #value!로 뜹니다..

    제가 무엇을 잘못한건지.. 매크로에 함수를 만들어 놓는게 맞는거죠??

  2. 2016.04.21 14:32  댓글주소  수정/삭제  댓글쓰기

    배경색이 같으면서 조건 하나를 더 충족하게 하려면 어떻게해야하나요?
    위의 예제에서 영업소 광주가 4개일 때
    광주 3개는 노란색인데, 1개는 파란색이예요
    그럼 광주 노란색 3개의 합을 구하고 싶어요ㅜ

  3. 유경세 2016.08.02 08:37  댓글주소  수정/삭제  댓글쓰기

    제가 찾고있던 기능인데 제대로 되었습니다. 감사합니다. 정말감사합니다.

    vba 기능은 아예 할 줄 몰라서 위에 적어주신내용 그대로 복사해서 넣었더니
    제대로 돌아갑니다.

    저는 거래처 30일결제업체, 45일 결제업체 등 업체들을 색상으로 구분해놓고
    색상별로 합산하여 해당기간에 금액이 얼마나 수금될 것인가(기간내 예상수금액)를 구현하는데에
    사용했습니다. (ex, 8월1일~8월15일 = 빨간색, 8월 16일~8월31일=파란색)

    다만 녹두장군님께서 알려주신 방법대로 하면
    예를들어 a업체가 해당기간보다 앞서 수금을 해줬다면 수금들어왔다는 표시를하고 색상을 없애는데
    해당함수에는 수정된내용이 실시간으로 반영되지 않습니다.
    또는 예정일보다 늦게 수금될 업체들은 위의 예시를 기준으로 빨간색에서 파란색으로 수정을 해야할때도 있는데 색상변경시 마찬가지로 반영되지 않습니다.

    이는 해당 함수칸 클릭 후 F2누른 후 엔터를 쳐야 수정된 내용이 반영됩니다.

    예컨대 sum 함수처럼 값이 변하거나 실시간으로 내용이 반영되도록 구현할 수 있는 방법은 없을까요?

    • Favicon of https://mainia.tistory.com 녹두장군 2016.08.02 18:28 신고  댓글주소  수정/삭제

      실시간 반영을 위해서는 Worksheet 개체에 함수를 구현해야 합니다. Worksheet 에 포함되어 있는 이벤트 함수 중 Worksheet_Change 를 이용하시면 됩니다.

      Private Sub Worksheet_Change(ByVal Target As Range)

      End Sub

  4. 유경세 2016.08.03 08:00  댓글주소  수정/삭제  댓글쓰기

    다시 한 번 감사합니다.
    vba열어서 본문내용에 있는 함수 적고
    그 아래에 댓글로 알려주신거 적었더니
    일부 작동을 하는것 같습니다.

    그렇게 하는게 맞나요 주인장님?

    • Favicon of https://mainia.tistory.com 녹두장군 2016.08.03 08:19 신고  댓글주소  수정/삭제

      네 함수를 직접 적어도 되지만 에디터 상단에 있는 콤보박스를 이용해서 함수를 추가하는 것이 실수를 줄일 수 있습니다. 아래 링크를 참고하시기 바랍니다.

      http://mainia.tistory.com/1336